pg_event_trigger_ddl_commands fails with cache lookup failed

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

pg_event_trigger_ddl_commands fails with cache lookup failed

Sven Klemm-3
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


Reply | Threaded
Open this post in threaded view
|

Re: pg_event_trigger_ddl_commands fails with cache lookup failed

Sven Klemm-3
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


Reply | Threaded
Open this post in threaded view
|

Re: pg_event_trigger_ddl_commands fails with cache lookup failed

Tom Lane-2
Sven Klemm <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: pg_event_trigger_ddl_commands fails with cache lookup failed

Sven Klemm-3
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 <[hidden email]> wrote:

>
> Sven Klemm <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: pg_event_trigger_ddl_commands fails with cache lookup failed

Tom Lane-2
Sven Klemm <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: pg_event_trigger_ddl_commands fails with cache lookup failed

Álvaro Herrera
In reply to this post by Sven Klemm-3
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