current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
add comment

2 Answers

up vote 0 down vote

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.

share|improve this answer
    
With standard_conforming_strings turned on, any text I save in a multi-line text field will store backslashes, carraige returns and line feeds as '\134', '\r' and '\n' respectively. With the setting turned off, all these symbols save correctly. The 'client' that was receiving the error is just one of our machines that we are using for testing. It accesses the program executable found on the server, which communicates with the database (also on the server). The weird thing is, over the weekend, the problem seemed to right itself. Both client and server machines are storing characters correctly –  user2437443 May 20 at 20:21
    
However, we don't like having weird errors that appear and then fix themselves. I want to know what was causing this in the first place. PS. thanks for the quick response –  user2437443 May 20 at 20:42
    
Sounds like your application is broken, then. Enable log_statement = all and show the actual SQL that the app is running that causes the issue, and the table definition. –  Craig Ringer May 22 at 8:04
    
Sorry for the delayed response, as this error fixed itself, it kind of got pushed aside for a while. However, now it is happening to one of our clients and we would very much like to find a solution soon. I enabled logging and edited my question above, please take a look and let me know if you have any ideas. –  user2437443 Jun 2 at 21:04
    
Your app's sending incorrect queries. You're getting \134 in your strings because you put it there: C:\134Users\134Stephane` etc. Fix your app so it sends E'C:\134Users\134Stephane\...'` strings, or doesn't escape backslashes. If you can't do either, you can SET standard_conforming_strings = off but do not rely on this working forever, the option will likely get removed from PostgreSQL at some point. –  Craig Ringer Jun 3 at 2:28
show 1 more comment
up vote 0 down vote accepted

So it was actually my application that was escaping characters. The old version of the program ran with postgres 8.0, which would expect escaped characters, but postgres 9.3 is fine with unescaped characters.

I changed my app so it would no longer escape characters, removed any reference to the standard_conforming_string setting in postgres.conf and voila!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.