Thread: Internal error with types changes and prepared statements

From https://github.com/sqlalchemy/sqlalchemy/issues/6842#issuecomment-925131836

The issue can be reproduced in psql with the following commands
(tested with PostgreSQL 12.8):

BEGIN;
CREATE TYPE an_enum AS ENUM ('foo', 'bar', 'baz');
CREATE TABLE foo(id integer, bar an_enum[]);
PREPARE stmt (an_enum[]) AS INSERT INTO foo (bar) VALUES ($1);
EXECUTE stmt ('{foo}');
ROLLBACK;

BEGIN;
CREATE TYPE an_enum AS ENUM ('foo', 'bar', 'baz');
CREATE TABLE foo(id integer, bar an_enum[]);
EXECUTE stmt ('{foo}');
ERROR:  cache lookup failed for type 8072147

\errverbose
ERROR:  XX000: cache lookup failed for type 8072147
LOCATION:  getBaseTypeAndTypmod, lsyscache.c:2355

Note: the issue is caused by Psycopg 3 use of prepared statements. Do
you suggest discarding prepared statements on a rollback on the
connection? I haven't seen documentation describing the interaction
between transactions and prepared statements.

Cheers

-- Daniele

Daniele Varrazzo <daniele.varrazzo@gmail.com> writes:
> BEGIN;
> CREATE TYPE an_enum AS ENUM ('foo', 'bar', 'baz');
> CREATE TABLE foo(id integer, bar an_enum[]);
> PREPARE stmt (an_enum[]) AS INSERT INTO foo (bar) VALUES ($1);
> EXECUTE stmt ('{foo}');
> ROLLBACK;

> BEGIN;
> CREATE TYPE an_enum AS ENUM ('foo', 'bar', 'baz');
> CREATE TABLE foo(id integer, bar an_enum[]);
> EXECUTE stmt ('{foo}');
> ERROR:  cache lookup failed for type 8072147

Hmm.  We have not historically discarded prepared statements at
ROLLBACK.  While this example suggests it'd be safer to do so,
I'm quite afraid that we would break a lot more applications
than we'd fix.

Actually ... you don't need the ROLLBACK anyway.  You can
reproduce this behavior by dropping and recreating the
type/table.

I'm inclined to shrug and say don't do that.  We could perhaps
track all the dependencies of a prepared statement as we do
for views, but it would add a lot of overhead that's not there
today, and for what?  All we'd accomplish is to give a cleaner
error message.  I don't think people should expect the above
to somehow work --- if it did, that would imply assorted
security holes, because the statement would no longer mean
what it meant before.

            regards, tom lane

On Wed, 22 Sept 2021 at 20:27, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> I'm inclined to shrug and say don't do that.  We could perhaps
> track all the dependencies of a prepared statement as we do
> for views, but it would add a lot of overhead that's not there
> today, and for what?  All we'd accomplish is to give a cleaner
> error message.  I don't think people should expect the above
> to somehow work --- if it did, that would imply assorted
> security holes, because the statement would no longer mean
> what it meant before.

That's good for me, it gives a clear idea about what to do.

Thank you very much

-- Daniele

While working on prepared statement support in PgBouncer I ran into
the workaround that Daniele did for this bug: Using DEALLOCATE ALL.

On Fri, 16 Jun 2023 at 14:43, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I don't think people should expect the above
> to somehow work --- if it did, that would imply assorted
> security holes, because the statement would no longer mean
> what it meant before.

I do think that people should expect the above to work. The docs
definitely say that it should work just fine:

> Although the main point of a prepared statement is to avoid
> repeated parse analysis and planning of the statement,
> PostgreSQL will force re-analysis and re-planning of the
> statement before using it whenever database objects used
> in the statement have undergone definitional (DDL) changes
> since the previous use of the prepared statement. Also,
> if the value of search_path changes from one use to the next,
> the statement will be re-parsed using the new search_path.

And it actually works for table DDL just fine. It's just the type DDL
that's the problem (for this example at least), because this works
fine:

CREATE TYPE an_enum AS ENUM ('foo', 'bar', 'baz');
CREATE TABLE foo(id integer, bar an_enum[]);
PREPARE stmt (an_enum[]) AS INSERT INTO foo (bar) VALUES ($1);
EXECUTE stmt ('{foo}');
DROP TABLE foo;
CREATE TABLE foo(id integer, bar an_enum[]);
EXECUTE stmt ('{foo}');

But as soon as the type is dropped and recreated it doesn't anymore
because of the cache lookup failure:

CREATE TYPE an_enum AS ENUM ('foo', 'bar', 'baz');
CREATE TABLE foo(id integer, bar an_enum[]);
PREPARE stmt (an_enum[]) AS INSERT INTO foo (bar) VALUES ($1);
EXECUTE stmt ('{foo}');
DROP TABLE foo;
DROP TYPE an_enum;
CREATE TYPE an_enum AS ENUM ('foo', 'bar', 'baz');
CREATE TABLE foo(id integer, bar an_enum[]);
EXECUTE stmt ('{foo}');
ERROR:  XX000: cache lookup failed for type 25630


> Actually ... you don't need the ROLLBACK anyway.  You can
> reproduce this behavior by dropping and recreating the
> type/table.

So yes and no, you don't need the ROLLBACK. But you do need the DROP TYPE.