Re: Prepared statements fail after schema changes with surprising error

Поиск
Список
Период
Сортировка
От Peter van Hardenberg
Тема Re: Prepared statements fail after schema changes with surprising error
Дата
Msg-id CAAcg=kWGFaFWP_kvBf3mLjeT4LSsG+Ny65gxFkYvmTzEYqy6Gg@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Prepared statements fail after schema changes with surprising error  (Peter van Hardenberg <pvh@pvh.ca>)
Ответы Re: Prepared statements fail after schema changes with surprising error
Список pgsql-hackers
Дерево обсуждения
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@pvh.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@sss.pgh.pa.us> wrote:
Peter Geoghegan <peter.geoghegan86@gmail.com> writes:
> On 22 January 2013 00:00, Tom Lane <tgl@sss.pgh.pa.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

В списке pgsql-hackers по дате отправления: