Just FYI... maybe I'm the only Pg veteran who didn't know this but;
Parameter settings in a multi-statement command are not in effect for
later statements in same command. They will take effect on later
commands however as seen below.
The 2 seconds statement timeout does nothing to prevent the sleep(10)
from completing.
Platform was Python 2.6 and EDB 8.2. In the real world, we discovered
this because, I was using such an approach to set a 15 second timeout
so that the next statement, an exclusive lock request would abort is
couldn't be obtained after 15 secs.
This was to avoid contention in situation where a long-running report
was holding a lock.
This behavior is quite likely documented somewhere but if so, not
apparent to me.
Silly example follows...
$ python
Python 2.6.6 (r266:84292, Sep 15 2010, 16:22:56)
[GCC 4.4.5] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>import pgdb
>conn = pgdb.connect()
>cur = conn.cursor()
>cur.execute("set statement_timeout to '2s'; select pg_sleep(10)")
# first invocation of this succeeds due to the 2s timeout not being
effective yet.
>cur.execute("select pg_sleep(10)")
# A 2s delay here and then exception
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/usr/lib/python2.6/dist-packages/pgdb.py", line 259, in execute
self.executemany(operation, (params,))
File "/usr/lib/python2.6/dist-packages/pgdb.py", line 289, in executemany
raise DatabaseError("error '%s' in '%s'" % (msg, sql))
pg.DatabaseError: error 'ERROR: canceling statement due to statement timeout
' in 'set statement_timeout to '2s'; select pg_sleep(10)'
It is tedious and I guess mostly unnecessary to do cur.execute("foo")
for each statement and as such, a clever guy might adopt the habit
of piling all sorts of code into a single execute().
Along these lines, I'd be curious to know of other good reasong for
*not* combining statements like this.
Thanks>
--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 732.216.7255