Re: Internal error with types changes and prepared statements

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(at)sss(dot)pgh(dot)pa(dot)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.