Re: Prepared statements fail after schema changes with surprising error

Okay - I've narrowed it down to an interaction with schema recreation.
Here's a minimal test-case I created by paring back the restore from the
pg_restore output until I only had the essence remaining:

-- setup
drop table z1;
create table z1 (f1 int);
insert into z1 values (1);
prepare sz1 as select * from z1;
select 'executing first prepared statement';
execute sz1;

-- remainder of minimized pg_restore SQL output
DROP TABLE public.z1;
DROP SCHEMA public;
CREATE SCHEMA public;
CREATE TABLE z1 (
f1 integer
);

-- proof of regression
select 'executing second prepared statement';
execute sz1;
select 'selecting from z1 to prove it exists';
select * from z1;

On Mon, Jan 21, 2013 at 10:45 PM, Peter van Hardenberg <pvh(at)pvh(dot)ca> wrote:

> Hm - I'm still able to recreate the test the user's running using
> pg_dump/pg_restore. I'm still working to see if I can minimize the
> test-case, but this is against 9.2.2. Would you prefer I test against HEAD?
>
> regression=# create table z1 (f1 int);
> CREATE TABLE
> regression=# prepare sz1 as select * from z1;
> PREPARE
> regression=# insert into z1 values (1);
> INSERT 0 1
> regression=# execute sz1;
> f1
> ----
> 1
> (1 row)
>
> # In another terminal window
> $ pg_dump -F c regression > test.dump
> $ pg_restore --clean --no-acl --no-owner -d regression test.dump
> ERROR: cannot drop the currently open database
> STATEMENT: DROP DATABASE regression;
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 2185; 1262 16384 DATABASE
> regression pvh
> pg_restore: [archiver (db)] could not execute query: ERROR: cannot drop
> the currently open database
> Command was: DROP DATABASE regression;
>
> WARNING: errors ignored on restore: 1
> $
>
> # back in the same backend
>
> regression=# execute sz1;
> ERROR: relation "z1" does not exist
> regression=# select * from z1;
> f1
> ----
> 1
> (1 row)
>
> regression=#
>
> On Mon, Jan 21, 2013 at 5:31 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Peter Geoghegan <peter(dot)geoghegan86(at)gmail(dot)com> writes:
>> > On 22 January 2013 00:00, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> >> Works for me ...
>>
>> > That's what I thought. But looking at RangeVarGetRelidExtended() and
>> > recomputeNamespacePath(), do you suppose that the problem could be
>> > that access privileges used by the app differed for a schema (or, more
>> > accurately, two physically distinct namespaces with the same nspname)
>> > between executions of the prepared query?
>>
>> What I'm suspicious of is that Peter is complaining about an old
>> version, or that there's some other critical piece of information he
>> left out. I don't plan to speculate about causes without a concrete
>> test case.
>>
>> regards, tom lane
>>
>
>
>
> --
> Peter van Hardenberg
> San Francisco, California
> "Everything was beautiful, and nothing hurt." -- Kurt Vonnegut
>

--
Peter van Hardenberg
San Francisco, California
"Everything was beautiful, and nothing hurt." -- Kurt Vonnegut