Обсуждение: Parameter setting in multi-statement command; I got bit today

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

Jerry Sievers <gsievers19@comcast.net> writes:
> 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.

This is not a true statement in general.

> 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.

I believe what's happening there is that the timeout is applied to each
submitted query string, not individual statements within such a string.

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Jerry Sievers <gsievers19@comcast.net> writes:
>
>> 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.
>
> This is not a true statement in general.
>
>> 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.
>
> I believe what's happening there is that the timeout is applied to each
> submitted query string, not individual statements within such a string.

Ok, fair enough.

But anyway, then in such a case...

set statement_timeout to '2s'; select foo();  --sent to backend as single string

The foo runs in whatever context was established before and the
statement_timeout setting not actually solid until end of that string
execution.

New statement_timeout setting in effect going forward.

I was naively expecting that the setting was processed in lexical
order in the string and the new setting effective for statements
further out in same string.

What's interesting, is that I just tried ...

set search_path to foo; select * from foo;  --single call to cur.execute()

Huh?!  That did work.  Schema foo not in search_path so the select
should have failed unless the immediatly following path setting, in
same command string, was processed and applied to statements in same
string.

In fact, in the very next multi-statement command that I tried, I said
"reset search_path; select..." and this raised an exception.

Doh!!

The defective code ran fine for months by coincidence since in this
DB, OLAP workloads are quite rare and there is generally nothing
holding a lock on the relevant table for long enough to be a problem.

Well, I'll have to scan several other home spun administrative
utilities to see if this same snake in the grass could be elsewhere
too.  Creature of habit, ya know :-)

Thanks!


>             regards, tom lane
>

--
Jerry Sievers