Thread: [SQLObject] PostgreSQL warnings filling up the logs with PickleCol
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Tom C. <su...@ic...> - 2012-05-31 06:24:17
|
Hi all, I am getting a warning in the Postgres log for every time a PickleCol is written to the database. Since my app uses a pickled col extensively in one of the more frequently used tables, the Postgres logs are filled with these warnings: ---------------------- WARNING: nonstandard use of \\ in a string literal at character 50 HINT: Use the escape string syntax for backslashes, e.g., E'\\'. ---------------------- This is with SQLObject v1.3.0, but it has been there from at least v0.12.4 from which I am upgrading to 1.3.0 now. PostgresSQL is v8.4.11 on Debian Squeeze. An example test case to illustrate the problem is inline below. Any help on how to solve this so I can get my log files cleaned up, in order to debug another connection problem, would be greatly appreciated. Thanks, Tom ------------------------------- from sqlobject import * db = dict(user='tomc', passw='', host='', dbname='tomc') dbURI = "postgres://%(user)s:%(passw)s@%(host)s/%(dbname)s" % db connection = connectionForURI(dbURI) sqlhub.processConnection = connection # Default pickled data - try to use as many object types as possible for tests defaultConfig = dict(ports=2, remote=False, logger=None, services=['web', 'database'], dns=dict(hostname=None, domain='foo.bar') ) # Define the sampe table with a pickled column class Server(SQLObject): name = StringCol() config = PickleCol(notNull=True, default=defaultConfig) # Drop and create table Server.dropTable(ifExists=True) Server.createTable() # Turn on SQLObejct debugging connection.debug=True # Add test records while tailing the postgresql log files s1 = Server(name='foo') ------------------------------- |
From: Oleg B. <ph...@ph...> - 2012-05-31 07:44:15
Attachments:
pgconnection.py.patch
|
Hello! On Thu, May 31, 2012 at 08:08:42AM +0200, Tom Coetser <su...@ic...> wrote: > I am getting a warning in the Postgres log for every time a PickleCol is > written to the database. Since my app uses a pickled col extensively in one of > the more frequently used tables, the Postgres logs are filled with these > warnings: > > ---------------------- > WARNING: nonstandard use of \\ in a string literal at character 50 > HINT: Use the escape string syntax for backslashes, e.g., E'\\'. > ---------------------- Thank you for an example of a good bug report! An error message and a short test case - exactly what I need. Can you apply and test the attached patch? > Any help on how to solve this so I can get my log files cleaned up, in order > to debug another connection problem, would be greatly appreciated. What log files? /var/log/postgresql/*.log? Oleg. -- Oleg Broytman http://phdru.name/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Tom C. <su...@ic...> - 2012-05-31 08:31:53
|
Hi Oleg, Thanks for the quick reply. On Thursday 31 May 2012 09:44:05 Oleg Broytman wrote: > Hello! > > On Thu, May 31, 2012 at 08:08:42AM +0200, Tom Coetser <su...@ic...> wrote: > > I am getting a warning in the Postgres log for every time a PickleCol is > > written to the database. Since my app uses a pickled col extensively in > > one of the more frequently used tables, the Postgres logs are filled > > with these warnings: > > > > ---------------------- > > WARNING: nonstandard use of \\ in a string literal at character 50 > > HINT: Use the escape string syntax for backslashes, e.g., E'\\'. > > ---------------------- > > Thank you for an example of a good bug report! An error message and a > short test case - exactly what I need. > Can you apply and test the attached patch? Thanks, seems to solve the problem nicely. I assume this will go into the next release for 1.3, correct? > > Any help on how to solve this so I can get my log files cleaned up, in > > order to debug another connection problem, would be greatly appreciated. > > What log files? /var/log/postgresql/*.log? Yes, but specifically /var/log/postgresql/postgresql-8.4-main.log on Debian Squeeze. Again, thanks for the quick reply and fix. Much appreciated. /Tom |
From: Oleg B. <ph...@ph...> - 2012-05-31 08:43:25
|
On Thu, May 31, 2012 at 10:31:33AM +0200, Tom Coetser <su...@ic...> wrote: > Thanks, seems to solve the problem nicely. I assume this will go into the next > release for 1.3, correct? It will go to the repository today, but formal releases will be much later. I'll be on vacation (and completely off-line) until June 20. > > > Any help on how to solve this so I can get my log files cleaned up, in > > > order to debug another connection problem, would be greatly appreciated. > > > > What log files? /var/log/postgresql/*.log? > > Yes, but specifically /var/log/postgresql/postgresql-8.4-main.log on Debian > Squeeze. /etc/init.d/postgresql stop rm /var/log/postgresql/* # or rm *.gz && gzip *.log /etc/init.d/postgresql start Oleg. -- Oleg Broytman http://phdru.name/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Oleg B. <ph...@ph...> - 2012-05-31 08:57:22
|
On Thu, May 31, 2012 at 11:44:05AM +0400, Oleg Broytman <ph...@ph...> wrote: > Can you apply and test the attached patch? Please be warned - the patch causes failures of test_blob, test_picklecol and test_validation. Seems I have to investigate it deeper. Oleg. -- Oleg Broytman http://phdru.name/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Tom C. <su...@ic...> - 2012-05-31 09:35:51
|
Hi Oleg, On Thursday 31 May 2012 10:57:11 Oleg Broytman wrote: > On Thu, May 31, 2012 at 11:44:05AM +0400, Oleg Broytman <ph...@ph...> wrote: > > Can you apply and test the attached patch? > > Please be warned - the patch causes failures of test_blob, > test_picklecol and test_validation. Seems I have to investigate it > deeper. Thanks for letting me know. So far unit tests on my app seems to be OK with the patch, but I'll wait to hear more from your side before I deploy the change. Thanks, Tom |
From: Oleg B. <ph...@ph...> - 2012-05-31 10:01:30
|
On Thu, May 31, 2012 at 11:35:31AM +0200, Tom Coetser <su...@ic...> wrote: > On Thursday 31 May 2012 10:57:11 Oleg Broytman wrote: > > On Thu, May 31, 2012 at 11:44:05AM +0400, Oleg Broytman <ph...@ph...> > wrote: > > > Can you apply and test the attached patch? > > > > Please be warned - the patch causes failures of test_blob, > > test_picklecol and test_validation. Seems I have to investigate it > > deeper. > > Thanks for letting me know. So far unit tests on my app seems to be OK with > the patch, but I'll wait to hear more from your side before I deploy the > change. I use utf-8 encoding, and SQLObject tests generates binary data. The reason for failures is that the patch makes SQLObject produce invalid utf-8 sequences. If your databases and client/server encoding is ascii or latin1 - I think the patch is ok. Oleg. -- Oleg Broytman http://phdru.name/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Oleg B. <ph...@ph...> - 2012-05-31 14:16:55
Attachments:
pgconnection.py.patch
|
Ok, found the problem - str() removes one level of backslashes so I have to double backslashes once again. See the attached patch - it passes all tests. Oleg. -- Oleg Broytman http://phdru.name/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Tom C. <su...@ic...> - 2012-06-01 08:06:33
|
Hey Oleg, On Thursday 31 May 2012 16:16:41 Oleg Broytman wrote: > Ok, found the problem - str() removes one level of backslashes so I have > to double backslashes once again. See the attached patch - it passes all > tests. When I use the new patch on my test case, after adding some extended characters to the pickle column and setting the encoding to utf-8 (see below), i get this error: ------------------------ $ python pickleTest.py 1/QueryIns: INSERT INTO server (id, config, name) VALUES (1, E'\\\\200\\\\002}q\\\\001(U\\\\005otherq\\\\002X\\\\010\\\\000\\\\000\\\\000f\\\\303\\\\266\\\\303\\\\266barq\\\\003U\\\\006remoteq\\\\004\\\\211U\\\\003dnsq\\\\005}q\\\\006(U\\\\006domainq\\\\007U\\\\007foo.barq\\\\010U\\\\010hostnameq\\\\011NuU\\\\010servicesq\\\\012]q\\\\013(U\\\\003webq\\\\014U\\\\010databaseq\\\\015eU\\\\006loggerq\\\\016NU\\\\005portsq\\\\017K\\\\002u.'::bytea, 'foo') 1/COMMIT : auto 1/QueryOne: SELECT name, config FROM server WHERE ((server.id) = (1)) 1/QueryR : SELECT name, config FROM server WHERE ((server.id) = (1)) 1/COMMIT : auto Traceback (most recent call last): File "pickleTest.py", line 37, in <module> s1 = Server(name='foo') File "/usr/lib/pymodules/python2.6/sqlobject/main.py", line 1223, in __init__ self._create(id, **kw) File "/usr/lib/pymodules/python2.6/sqlobject/main.py", line 1271, in _create self._SO_finishCreate(id) File "/usr/lib/pymodules/python2.6/sqlobject/main.py", line 1298, in _SO_finishCreate self._init(id) File "/usr/lib/pymodules/python2.6/sqlobject/main.py", line 936, in _init self._SO_selectInit(selectResults) File "/usr/lib/pymodules/python2.6/sqlobject/main.py", line 1154, in _SO_selectInit colValue = col.to_python(colValue, self._SO_validatorState) File "/usr/lib/pymodules/python2.6/formencode/api.py", line 413, in to_python value = tp(value, state) File "/usr/lib/pymodules/python2.6/formencode/compound.py", line 57, in _to_python to_python) File "/usr/lib/pymodules/python2.6/formencode/compound.py", line 118, in attempt_convert value = validate(validator, value, state) File "/usr/lib/pymodules/python2.6/formencode/compound.py", line 15, in to_python return validator.to_python(value, state) File "/usr/lib/pymodules/python2.6/sqlobject/col.py", line 1491, in to_python return pickle.loads(value) cPickle.UnpicklingError: invalid load key, '\'. ------------------------ I also get these errors on my current app with the new patch. With the previous patch both my app unit tests and the test app below runs fine. I wish I had some more time to spend on this and try to debug the problem, but I am swamped at the moment :-( For the time being I am using the first patch you sent on my production system and all seems fine so far. If you can look into this further, it would be much appreciated, but otherwise, no problem. You have already been a great help! Thanks, Tom Updated test case with encoding and unicode pickle data: ---------------------------------------------------------------- # -*- coding: utf-8 -*- from sqlobject import * db = dict(user='tomc', passw='', host='', dbname='tomc') dbURI = "postgres://%(user)s:%(passw)s@%(host)s/%(dbname)s" % db connection = connectionForURI(dbURI) sqlhub.processConnection = connection # Default pickled data - try to use as many object types as possible for tests defaultConfig = dict(ports=2, remote=False, logger=None, services=['web', 'database'], dns=dict(hostname=None, domain='foo.bar'), other=u'fööbar' ) # Define the sampe table with a pickled column class Server(SQLObject): name = StringCol() config = PickleCol(notNull=True, default=defaultConfig) # Drop and create table Server.dropTable(ifExists=True) Server.createTable() # Turn on SQLObejct debugging connection.debug=True # Add test records while tailing the postgresql log files s1 = Server(name='foo') ---------------------------------------------------------------- |
From: Oleg B. <ph...@ph...> - 2012-06-01 13:19:17
|
Hi! On Fri, Jun 01, 2012 at 10:06:15AM +0200, Tom Coetser <su...@ic...> wrote: > When I use the new patch on my test case, after adding some extended > characters to the pickle column and setting the encoding to utf-8 (see below), > i get this error: I cannot promise I'll debug this before vacation. I'm busy packing my sacks. Sorry. Oleg. -- Oleg Broytman http://phdru.name/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Oleg B. <ph...@ph...> - 2012-06-21 07:52:04
|
Hi! I've come back. On Fri, Jun 01, 2012 at 10:06:15AM +0200, Tom Coetser <su...@ic...> wrote: > On Thursday 31 May 2012 16:16:41 Oleg Broytman wrote: > > Ok, found the problem - str() removes one level of backslashes so I have > > to double backslashes once again. See the attached patch - it passes all > > tests. > > When I use the new patch on my test case, after adding some extended > characters to the pickle column and setting the encoding to utf-8 (see below), > i get this error: > > ------------------------ > $ python pickleTest.py > 1/QueryIns: INSERT INTO server (id, config, name) VALUES (1, > E'\\\\200\\\\002}q\\\\001(U\\\\005otherq\\\\002X\\\\010\\\\000\\\\000\\\\000f\\\\303\\\\266\\\\303\\\\266barq\\\\003U\\\\006remoteq\\\\004\\\\211U\\\\003dnsq\\\\005}q\\\\006(U\\\\006domainq\\\\007U\\\\007foo.barq\\\\010U\\\\010hostnameq\\\\011NuU\\\\010servicesq\\\\012]q\\\\013(U\\\\003webq\\\\014U\\\\010databaseq\\\\015eU\\\\006loggerq\\\\016NU\\\\005portsq\\\\017K\\\\002u.'::bytea, > 'foo') Very strange. It seems I have to redouble backslashes in my case but not in yours. Puzzled... Oleg. -- Oleg Broytman http://phdru.name/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Oleg B. <ph...@ph...> - 2012-06-22 19:42:36
|
Tom, can I see the values for the following configuration parameters: grep 'standard_conforming_strings\|escape_string_warning\|backslash_quote' /etc/postgresql/9.1/main/postgresql.conf Mine are: #backslash_quote = safe_encoding # on, off, or safe_encoding #escape_string_warning = on #standard_conforming_strings = on I'm trying to understand the difference in our setup... Oleg. -- Oleg Broytman http://phdru.name/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Tom C. <su...@ic...> - 2012-06-25 04:49:14
|
Hi Oleg, On Friday 22 June 2012 21:42:28 Oleg Broytman wrote: > Tom, can I see the values for the following configuration parameters: > > grep 'standard_conforming_strings\|escape_string_warning\|backslash_quote' > /etc/postgresql/9.1/main/postgresql.conf > > Mine are: > > #backslash_quote = safe_encoding # on, off, or safe_encoding > #escape_string_warning = on > #standard_conforming_strings = on I am still running PostgreSQL 8.4 : $ grep 'standard_conforming_strings\|escape_string_warning\|backslash_quote' /etc/postgresql/8.4/main/postgresql.conf #backslash_quote = safe_encoding # on, off, or safe_encoding #escape_string_warning = on #standard_conforming_strings = off > I'm trying to understand the difference in our setup... Could it be the PostgreSQL version? Thanks, Tom |
From: Oleg B. <ph...@ph...> - 2012-06-25 18:50:45
|
Hi! On Mon, Jun 25, 2012 at 06:49:03AM +0200, Tom Coetser <su...@ic...> wrote: > On Friday 22 June 2012 21:42:28 Oleg Broytman wrote: > > Tom, can I see the values for the following configuration parameters: > > > > grep 'standard_conforming_strings\|escape_string_warning\|backslash_quote' > > /etc/postgresql/9.1/main/postgresql.conf > > > > Mine are: > > > > #backslash_quote = safe_encoding # on, off, or safe_encoding > > #escape_string_warning = on > > #standard_conforming_strings = on > > I am still running PostgreSQL 8.4 : > > $ grep 'standard_conforming_strings\|escape_string_warning\|backslash_quote' > /etc/postgresql/8.4/main/postgresql.conf > > #backslash_quote = safe_encoding # on, off, or safe_encoding > #escape_string_warning = on > #standard_conforming_strings = off Thanks. Everything is default. Ok. > > I'm trying to understand the difference in our setup... > > Could it be the PostgreSQL version? I am not sure. The problem seems to be I need to double backslashes when I use E'' escaped strings and that backslash doubling takes place long before SQLObject passes the string to the backend. Without backslash doubling Postgres report problems with encodings - all encodings I tried: latin1, koi8-r, utf-8. Without E'' escaped strings - i.e., with plain ''-quoted strings with backslashes - I do not need to double backslashes, and Pg doesn't generate warnings about E'' escapes. It seems in your case backslashes must not be doubled, and E'' escapes suppress warnings. Yes, it could be a difference in Pg versions. So for now my resolution is: I'm removing the patch from SQLObject because without it SQLObject works fine with different versions of Pg; to suppress warnings about E'' escapes try to set escape_string_warning = off and restart Postgres. I'm saving the patch to a file for future pondering. Oleg. -- Oleg Broytman http://phdru.name/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |