pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_event_trigger_ddl_commands fails with cache lookup failed
6+ messages / 3 participants
[nested] [flat]
* pg_event_trigger_ddl_commands fails with cache lookup failed
@ 2021-02-13 13:59 Sven Klemm <sven@timescale.com>
2021-02-27 22:32 ` Re: pg_event_trigger_ddl_commands fails with cache lookup failed Sven Klemm <sven@timescale.com>
0 siblings, 1 reply; 6+ messages in thread
From: Sven Klemm @ 2021-02-13 13:59 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
Hello,
when creating an event trigger for ddl_command_end that calls
pg_event_trigger_ddl_commands certain statements will cause the
trigger to fail with a cache lookup error. The error happens on 12.5
and 13.2 i didnt test any other versions.
trg=# ALTER TABLE t ALTER COLUMN f1 DROP IDENTITY, ALTER COLUMN f1 SET
DATA TYPE bigint;
ERROR: XX000: cache lookup failed for relation 13476892
CONTEXT: PL/pgSQL function ddl_end() line 5 at FOR over SELECT rows
LOCATION: getRelationTypeDescription, objectaddress.c:4178
To reproduce:
CREATE OR REPLACE FUNCTION ddl_end()
RETURNS event_trigger AS $$
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT * FROM pg_event_trigger_ddl_commands()
LOOP
RAISE NOTICE 'ddl_end: % %', r.command_tag, r.object_type;
END LOOP;
END;
$$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER ddl_end ON ddl_command_end EXECUTE PROCEDURE ddl_end();
CREATE TABLE t(f1 int NOT NULL GENERATED ALWAYS AS IDENTITY);
ALTER TABLE t ALTER COLUMN f1 DROP IDENTITY, ALTER COLUMN f1 SET DATA
TYPE bigint;
--
Regards, Sven Klemm
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_event_trigger_ddl_commands fails with cache lookup failed
2021-02-13 13:59 pg_event_trigger_ddl_commands fails with cache lookup failed Sven Klemm <sven@timescale.com>
@ 2021-02-27 22:32 ` Sven Klemm <sven@timescale.com>
2021-02-28 00:20 ` Re: pg_event_trigger_ddl_commands fails with cache lookup failed Tom Lane <tgl@sss.pgh.pa.us>
2021-02-28 16:16 ` Re: pg_event_trigger_ddl_commands fails with cache lookup failed Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 2 replies; 6+ messages in thread
From: Sven Klemm @ 2021-02-27 22:32 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
Hello,
> trg=# ALTER TABLE t ALTER COLUMN f1 DROP IDENTITY, ALTER COLUMN f1 SET
> DATA TYPE bigint;
> ERROR: XX000: cache lookup failed for relation 13476892
> CONTEXT: PL/pgSQL function ddl_end() line 5 at FOR over SELECT rows
> LOCATION: getRelationTypeDescription, objectaddress.c:4178
Is this expected behaviour or something that should be fixed?
The documentation for pg_event_trigger_ddl_commands doesnt mention
that it could fail for certain statements.
Is catching the exception the only way to deal with this in caller or
is there another way to handle this gracefully?
--
Regards, Sven Klemm
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_event_trigger_ddl_commands fails with cache lookup failed
2021-02-13 13:59 pg_event_trigger_ddl_commands fails with cache lookup failed Sven Klemm <sven@timescale.com>
2021-02-27 22:32 ` Re: pg_event_trigger_ddl_commands fails with cache lookup failed Sven Klemm <sven@timescale.com>
@ 2021-02-28 00:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-02-28 00:30 ` Re: pg_event_trigger_ddl_commands fails with cache lookup failed Sven Klemm <sven@timescale.com>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2021-02-28 00:20 UTC (permalink / raw)
To: Sven Klemm <sven@timescale.com>; +Cc: pgsql-bugs@lists.postgresql.org
Sven Klemm <sven@timescale.com> writes:
>> trg=# ALTER TABLE t ALTER COLUMN f1 DROP IDENTITY, ALTER COLUMN f1 SET
>> DATA TYPE bigint;
>> ERROR: XX000: cache lookup failed for relation 13476892
>> CONTEXT: PL/pgSQL function ddl_end() line 5 at FOR over SELECT rows
>> LOCATION: getRelationTypeDescription, objectaddress.c:4178
This is not what I'd call an adequate bug report. I guessed at what you
might have started from, but it works for me:
regression=# create table t (f1 int generated always as identity);
CREATE TABLE
regression=# ALTER TABLE t ALTER COLUMN f1 DROP IDENTITY, ALTER COLUMN f1 SET
DATA TYPE bigint;
ALTER TABLE
Having said that, this seems like it's in the same ball park as some
not-very-long-ago bug fixes in ALTER TABLE's sequence of operations.
Maybe you just need to update.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_event_trigger_ddl_commands fails with cache lookup failed
2021-02-13 13:59 pg_event_trigger_ddl_commands fails with cache lookup failed Sven Klemm <sven@timescale.com>
2021-02-27 22:32 ` Re: pg_event_trigger_ddl_commands fails with cache lookup failed Sven Klemm <sven@timescale.com>
2021-02-28 00:20 ` Re: pg_event_trigger_ddl_commands fails with cache lookup failed Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-02-28 00:30 ` Sven Klemm <sven@timescale.com>
2021-02-28 00:35 ` Re: pg_event_trigger_ddl_commands fails with cache lookup failed Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Sven Klemm @ 2021-02-28 00:30 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
Oh sorry, the steps to reproduce were in the initial mail and i didnt
add them again.
This fails on master too. The drop identity drops the sequence which means
pg_event_trigger_ddl_commands cannot lookup details for the sequence.
This still fails on master.
To reproduce:
CREATE OR REPLACE FUNCTION ddl_end()
RETURNS event_trigger AS $$
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT * FROM pg_event_trigger_ddl_commands()
LOOP
RAISE NOTICE 'ddl_end: % %', r.command_tag, r.object_type;
END LOOP;
END;
$$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER ddl_end ON ddl_command_end EXECUTE PROCEDURE ddl_end();
CREATE TABLE t(f1 int NOT NULL GENERATED ALWAYS AS IDENTITY);
ALTER TABLE t ALTER COLUMN f1 DROP IDENTITY, ALTER COLUMN f1 SET DATA
TYPE bigint;
On Sun, Feb 28, 2021 at 1:21 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Sven Klemm <sven@timescale.com> writes:
> >> trg=# ALTER TABLE t ALTER COLUMN f1 DROP IDENTITY, ALTER COLUMN f1 SET
> >> DATA TYPE bigint;
> >> ERROR: XX000: cache lookup failed for relation 13476892
> >> CONTEXT: PL/pgSQL function ddl_end() line 5 at FOR over SELECT rows
> >> LOCATION: getRelationTypeDescription, objectaddress.c:4178
>
> This is not what I'd call an adequate bug report. I guessed at what you
> might have started from, but it works for me:
>
> regression=# create table t (f1 int generated always as identity);
> CREATE TABLE
> regression=# ALTER TABLE t ALTER COLUMN f1 DROP IDENTITY, ALTER COLUMN f1 SET
> DATA TYPE bigint;
> ALTER TABLE
>
> Having said that, this seems like it's in the same ball park as some
> not-very-long-ago bug fixes in ALTER TABLE's sequence of operations.
> Maybe you just need to update.
>
> regards, tom lane
--
Regards, Sven Klemm
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_event_trigger_ddl_commands fails with cache lookup failed
2021-02-13 13:59 pg_event_trigger_ddl_commands fails with cache lookup failed Sven Klemm <sven@timescale.com>
2021-02-27 22:32 ` Re: pg_event_trigger_ddl_commands fails with cache lookup failed Sven Klemm <sven@timescale.com>
2021-02-28 00:20 ` Re: pg_event_trigger_ddl_commands fails with cache lookup failed Tom Lane <tgl@sss.pgh.pa.us>
2021-02-28 00:30 ` Re: pg_event_trigger_ddl_commands fails with cache lookup failed Sven Klemm <sven@timescale.com>
@ 2021-02-28 00:35 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2021-02-28 00:35 UTC (permalink / raw)
To: Sven Klemm <sven@timescale.com>; +Cc: pgsql-bugs@lists.postgresql.org
Sven Klemm <sven@timescale.com> writes:
> Oh sorry, the steps to reproduce were in the initial mail and i didnt
> add them again.
> This fails on master too. The drop identity drops the sequence which means
> pg_event_trigger_ddl_commands cannot lookup details for the sequence.
> This still fails on master.
Hm. I don't know much about the event trigger stuff, but I wonder how it
ever copes with *any* object-dropping commands. Seems like the issue
would exist in all such cases.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_event_trigger_ddl_commands fails with cache lookup failed
2021-02-13 13:59 pg_event_trigger_ddl_commands fails with cache lookup failed Sven Klemm <sven@timescale.com>
2021-02-27 22:32 ` Re: pg_event_trigger_ddl_commands fails with cache lookup failed Sven Klemm <sven@timescale.com>
@ 2021-02-28 16:16 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
1 sibling, 0 replies; 6+ messages in thread
From: Alvaro Herrera @ 2021-02-28 16:16 UTC (permalink / raw)
To: Sven Klemm <sven@timescale.com>; +Cc: pgsql-bugs@lists.postgresql.org
On 2021-Feb-27, Sven Klemm wrote:
> Hello,
>
> > trg=# ALTER TABLE t ALTER COLUMN f1 DROP IDENTITY, ALTER COLUMN f1 SET
> > DATA TYPE bigint;
> > ERROR: XX000: cache lookup failed for relation 13476892
> > CONTEXT: PL/pgSQL function ddl_end() line 5 at FOR over SELECT rows
> > LOCATION: getRelationTypeDescription, objectaddress.c:4178
>
> Is this expected behaviour or something that should be fixed?
> The documentation for pg_event_trigger_ddl_commands doesnt mention
> that it could fail for certain statements.
Yeah, this is certainly a bug. The code should likely ignore the
sub-command. I'll have a look.
--
Álvaro Herrera Valdivia, Chile
"After a quick R of TFM, all I can say is HOLY CR** THAT IS COOL! PostgreSQL was
amazing when I first started using it at 7.2, and I'm continually astounded by
learning new features and techniques made available by the continuing work of
the development team."
Berend Tober, http://archives.postgresql.org/pgsql-hackers/2007-08/msg01009.php
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2021-02-28 16:16 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-02-13 13:59 pg_event_trigger_ddl_commands fails with cache lookup failed Sven Klemm <sven@timescale.com>
2021-02-27 22:32 ` Sven Klemm <sven@timescale.com>
2021-02-28 00:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-02-28 00:30 ` Sven Klemm <sven@timescale.com>
2021-02-28 00:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-02-28 16:16 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
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