pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Internal error with types changes and prepared statements
4+ messages / 3 participants
[nested] [flat]

* Internal error with types changes and prepared statements
@ 2021-09-22 18:13  Daniele Varrazzo <daniele.varrazzo@gmail.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Daniele Varrazzo @ 2021-09-22 18:13 UTC (permalink / raw)
  To: pgsql-bugs



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Internal error with types changes and prepared statements
@ 2021-09-22 18:27  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Daniele Varrazzo <daniele.varrazzo@gmail.com>
  0 siblings, 2 replies; 4+ messages in thread

From: Tom Lane @ 2021-09-22 18:27 UTC (permalink / raw)
  To: Daniele Varrazzo <daniele.varrazzo@gmail.com>; +Cc: pgsql-bugs

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





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Internal error with types changes and prepared statements
@ 2021-09-22 18:32  Daniele Varrazzo <daniele.varrazzo@gmail.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 4+ messages in thread

From: Daniele Varrazzo @ 2021-09-22 18:32 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs

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





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Internal error with types changes and prepared statements
@ 2023-06-16 13:00  Jelte Fennema <me@jeltef.nl>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 4+ messages in thread

From: Jelte Fennema @ 2023-06-16 13:00 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Daniele Varrazzo <daniele.varrazzo@gmail.com>; pgsql-bugs

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.





^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2023-06-16 13:00 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-09-22 18:13 Internal error with types changes and prepared statements Daniele Varrazzo <daniele.varrazzo@gmail.com>
2021-09-22 18:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-09-22 18:32   ` Daniele Varrazzo <daniele.varrazzo@gmail.com>
2023-06-16 13:00   ` Jelte Fennema <me@jeltef.nl>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox