Re: Parameter setting in multi-statement command; I got bit today

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Jerry Sievers <gsievers19(at)comcast(dot)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