PostgreSQL 9.3 – Standard_Confirming_Strings Option Not Affecting Remote Machine

configurationpostgresql-9.3remotestorage

I am updating a program at work that communicates with a postgreSQL 9.3 database. I need to turn off an option in the postgresql.conf file called standard_conforming_strings, so that I can store backslashes properly in the database.

During installation, I turn off the standard_conforming_strings option in postgresql.conf automatically on the server machine. However, client installations of this program simply consist of shortcuts that point to the executable on the server machine. The client machines don't have postgres installed at all.

If data containing a backslash is stored on the server machine, the data saves correctly, meaning the standard_conforming_strings option was successfully turned off. However, when information is saved from a client machine, backslashes are stored incorrectly (as '\134' instead of '\').

Only the server machine has postgres installed, so it is the only machine with a postgresql.conf file where the standard_conforming_strings option can be turned on or off. I know this option is being disabled correctly on the server machine, but for some reason, when data is saved on the client machine, backslashes are not saved correctly, even though the data is being saved to the same database.

If both these computers are saving to the same database, they should refer to the same postgresql.conf file, shouldn't they? Data should be stored in the same manner on both machines. What is going wrong here?

Any help or suggestions would be greatly appreciated!

Edit: As requested, I've enabled logging of statements on postgres and this is what showed up in the log file when I tried to save data containing backslashes:

2014-06-02 13:44:01 PDT LOG:  statement: SELECT * FROM tblapp;
2014-06-02 13:44:38 PDT WARNING:  nonstandard use of escape in a string literal at character 135
2014-06-02 13:44:38 PDT HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
2014-06-02 13:44:38 PDT LOG:  statement: BEGIN;DELETE FROM tblapp WHERE app_key='ClientApp_ArchivePath';INSERT INTO tblapp (app_key,app_value) VALUES ('ClientApp_ArchivePath','C:\134Users\134Stephane\134Documents\134Archive');
2014-06-02 13:44:38 PDT LOG:  statement: COMMIT

The data shows up like this in pgAdmin:

ClientApp_ArchivePath | C:\Users\Stephane\Documents\Archive

Note: I did this on a machine that is saving backslashes correctly. I do not currently have access to a machine that is saving them incorrectly, but hopefully I will soon and I will go through the same process and post the results here.

Best Answer

I am updating a program at work that communicates with a postgreSQL 9.3 database. I need to turn off an option in the postgresql.conf file called standard_conforming_strings, so that I can store backslashes properly in the database.

These two things are completely unrelated. You may want to change the setting, but you don't need to in order to "store backslashes properly". I very strongly recommend that you do not change standard_conforming_strings - it's a backward compatibility setting that will get removed from PostgreSQL at some point in the future. Not only that, it's better to follow the standard where possible.

With standard_conforming_strings on, you can store backslashes just like anything else:

CREATE TABLE x(blah text);

SET standard_conforming_strings = on;

INSERT INTO x(blah) VALUES ('back\slash');

SELECT blah FROM x;

will produce back\slash.

If you turn standard_conforming_strings off you have to write 'back\slash' to get the same effect. The same is true for E'' strings: E'back\slash'.

During installation, I turn off the standard_conforming_strings option in postgresql.conf automatically on the server machine. However, client installations of this program simply consist of shortcuts that point to the executable on the server machine. The client machines don't have postgres installed at all.

standard_conforming_strings is a server side setting. It can also be assigned by clients, but usually isn't, and the default is set server-side.

You don't need to mess with clients.

If data containing a backslash is stored on the server machine, the data saves correctly, meaning the standard_conforming_strings option was successfully turned off.

Again, your first statement doesn't lead to the second at all. Your assumption is invalid.

However, when information is saved from a client machine, backslashes are stored incorrectly (as '\134' instead of '\').

According to what client? Show the actual SQL, provide info on what the client app is, show the DDL for the table, etc.

If you're in doubt about the value of standard_conforming_strings for a session, use SHOW standard_conforming_strings to view the setting.

Related Solutions

Sql-server – Slow remote SELECT statement due to long “client processing time”, but fast locally

Your problem is definitely network related, based on your info. As such, it has to be dealt with with network professionals (I am not the one).

Things that might help:

Faster NIC cards (on SQL server).
Adding of allocated/specific NIC card/subnet between the servers (web-server and SQL Server).

Is the web-server in the same sub-net as the SQL server?

Are there routers/bridges etc. between them?

Not many possible changes on SQL server:

Output data is being sent by SQL Server with proprietary MS "TDS protocol".
Default size of the TDS buffer is 4 KB. See in MSDB: "network packet size Option"
Compressing the data (with SQL Server or an external application) - depends upon nature of data.

You are using a default size: see your stats: "TDS packets received from server 1216" (4MB/1K=4KB). Yes, size of the TDS buffer can be changed: see in google: "TDS protocol batch size"

Good discussion on the topic: "does sql's network packet size really determine round trip traffic?"

However, changing the TDS package size will (inevitably) have unpredictable effects and should only be used in production in exceptional cases.

Changing of architecture or introduction of caching of data on mid-tier would also help.

PostgreSQL on Windows – Common Issues and Solutions

The PSQL Console in pgadmin3 consists of opening a terminal and running psql --username yourname [other options] dbname inside it for you, with yourname and dbname being taken from what you've used to log in pgadmin3.

This console runs the psql interpreter which expects either psql meta-commands starting with backslash, or plain SQL commands.

When typing this:

`psql -U postgres -d postgres -f dump.sql`

it's taken as an unfinished SQL sentence for the interpreter, so it's just waiting for the rest. That's why nothing happens as you wrote. This command is out of context.

What you want is:

\i /path/to/dump.sql

\i means 'include' and is the meta-command to feed a file as a stream of SQL commands to the interpreter. You'll see the output of these commands on the terminal and when finished , type \q to end the session.

Best Answer

Related Solutions

Sql-server – Slow remote SELECT statement due to long “client processing time”, but fast locally

PostgreSQL on Windows – Common Issues and Solutions

Related Question