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
master, 13 and 12 I didnt test any previous versions.
trg=# ALTER TABLE t ALTER COLUMN f1 SET DATA TYPE bigint, ALTER COLUMN
f1 DROP IDENTITY;
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
For the ALTER DATA TYPE we create a command to adjust the sequence
which gets recorded in the event trigger commandlist, which leads to
the described failure when the sequence is dropped as part of another
ALTER TABLE subcommand and information about the sequence can no
longer be looked up.
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;
I tried really hard to look for a different way to detect this error
earlier but since the subcommands are processed independently i
couldnt come up with a non-invasive version. Someone more familiar
with this code might have an idea for a better solution.
Any thoughts?
https://www.postgresql.org/message-id/CAMCrgp39V7JQA_Gc+JaEZV3ALOU1ZG=pwyk3odptq7f6z0j...@mail.gmail.com
--
Regards, Sven Klemm
From 4faf761528c735bd808e7ea4d9f356710e3a8ed0 Mon Sep 17 00:00:00 2001
From: Sven Klemm <sven@timescale.com>
Date: Sat, 17 Apr 2021 21:54:03 +0200
Subject: [PATCH v1] Fix pg_event_trigger_ddl_commands
If an object is modified that is dropped in the same
command we might end up in a position where we
generated a message but can no longer look up the
object information because the object got dropped.
E.g. ALTER TABLE ALTER COLUMN, DROP IDENTITY
---
src/backend/commands/event_trigger.c | 13 +++++++++++--
1 file changed, 11 insertions(+), 2 deletions(-)
diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c
index 5bde507c75..f7b2c30f82 100644
--- a/src/backend/commands/event_trigger.c
+++ b/src/backend/commands/event_trigger.c
@@ -1936,8 +1936,17 @@ pg_event_trigger_ddl_commands(PG_FUNCTION_ARGS)
else if (cmd->type == SCT_AlterTSConfig)
addr = cmd->d.atscfg.address;
- type = getObjectTypeDescription(&addr, false);
- identity = getObjectIdentity(&addr, false);
+ /*
+ * If an object is modified that is dropped in the same
+ * command we might end up in a position where we
+ * generated a message but can no longer look up the
+ * object information because the object got dropped.
+ * E.g. ALTER TABLE ALTER COLUMN, DROP IDENTITY
+ */
+ type = getObjectTypeDescription(&addr, true);
+ identity = getObjectIdentity(&addr, true);
+ if (!identity)
+ continue;
/*
* Obtain schema name, if any ("pg_temp" if a temp
--
2.30.0
