Обсуждение: Error with DEFAULT VALUE in temp table
Hi everyone,
I found one bug, when you delete temporary table with DEFAULT VALUE. The row about this VALUE in the pg_event_trigger_dropped_objects() contains “False” in the column “is_temporary”. But if you look at column “name_obj”, you see “for pg_temp.table_name”. So PostgreSQL know, that it is temporary.
Cheers
Antoine Violin
I found one bug, when you delete temporary table with DEFAULT VALUE. The row about this VALUE in the pg_event_trigger_dropped_objects() contains “False” in the column “is_temporary”. But if you look at column “name_obj”, you see “for pg_temp.table_name”. So PostgreSQL know, that it is temporary.
Cheers
Antoine Violin
=?UTF-8?B?0JDQvdGC0YPQsNC9INCS0LjQvtC70LjQvQ==?= <violin.antuan@gmail.com> writes:
> I made patch for this problem, I changed event_trigger, for
> definitions of temporality
> DEFAULT VALUE
I looked over this patch. I understand what you want to fix,
and I agree with the general plan of pushing the namespace-lookup
code into a subroutine so we can more easily point it at a different
object for the attrdef case. However, you've done no favors for
future readers of the code:
* "process_catalog_object" is about as generic and uninformative a
name as one could easily think of. I'd suggest something more like
"identify_object_namespace" --- I'm not wedded to that exact choice,
but the name should indicate what the function is trying to do.
* The new function also lacks a header comment, which isn't OK
except maybe for extremely trivial functions with obvious APIs.
Here I think you need to explain what values it outputs, and
you certainly need to explain what the return value means.
* The comment block at lines 1297ff is now kind of dangling,
because you moved half of what it's talking about to somewhere else.
Perhaps some of that text belongs in the new function's header
comment.
* Zero comments in the new code block for "object->classId ==
AttrDefaultRelationId" are not OK either. I'd expect to see
something like "We treat a column default as temp if its table
is temp".
* I wonder if the check for is_objectclass_supported shouldn't
move into the new function too. It's not really a concern
of the outer function where the new function is getting its
information from.
* If I'm reading it correctly, the patch depends on the
assumption that attrdefs aren't supported by the
is_objectclass_supported() infrastructure. I'm not sure
that's right even today, and it sure seems like something
that could get broken by well-intentioned future patches.
Something that isn't the fault of your patch, but could be
improved while we're here:
* It seems rather messy and poorly-thought-out that schemas
themselves are handled in two separate places in the function,
at lines 1283ff and 1367ff. Seems like that could be unified
and also made to look more like the equivalent code for
objects-contained-in-schemas.
Taking my last three comments together, maybe what we want for
the overall structure in EventTriggerSQLDropAddObject is
if (object->classId == NamespaceRelationId)
{
code for the schema case;
}
else if (object->classId == AttrDefaultRelationId)
{
code for the attrdef case;
}
else
{
generic case;
}
where the second and third blocks use this new function.
regards, tom lane
Hi, Tom!
Thank you for working on this. I see you've fixed the patch and
committed it as a0b99fc1220. I tested it a bit and see some side effects
which may be unintentional.
1. SCHEMA lost object_name.
Before:
postgres=# create schema foo;
CREATE SCHEMA
postgres=# drop schema foo;
DROP SCHEMA
postgres=# select * from dropped_objects \gx
-[ RECORD 1 ]---+-------
n | 1
classid | 2615
objid | 16404
objsubid | 0
original | t
normal | f
is_temporary | f
object_type | schema
schema_name |
object_name | foo
object_identity | foo
address_names | {foo}
address_args | {}
After:
postgres=# select * from dropped_objects \gx
-[ RECORD 1 ]---+-------
n | 1
classid | 2615
objid | 16394
objsubid | 0
original | t
normal | f
is_temporary | f
object_type | schema
schema_name |
object_name |
object_identity | foo
address_names | {foo}
address_args | {}
2. DEFAULT VALUE now has schema_name and object_name.
Before:
postgres=# create temp table bar (a int default 0);
CREATE TABLE
postgres=# drop table bar;
DROP TABLE
postgres=# select * from dropped_objects where object_type =
'default value' \gx
-[ RECORD 1 ]---+------------------
n | 4
classid | 2604
objid | 16422
objsubid | 0
original | f
normal | f
is_temporary | f
object_type | default value
schema_name |
object_name |
object_identity | for pg_temp.bar.a
address_names | {pg_temp,bar,a}
address_args | {}
After:
postgres=# select * from dropped_objects where object_type =
'default value' \gx
-[ RECORD 1 ]---+------------------
n | 4
classid | 2604
objid | 16430
objsubid | 0
original | f
normal | f
is_temporary | t
object_type | default value
schema_name | pg_temp
object_name | bar
object_identity | for pg_temp.bar.a
address_names | {pg_temp,bar,a}
address_args | {}
This may be intentional, but doesn't quite match the description for
object_name in the docs:
Name of the object, if the combination of schema and name can be
used as a unique identifier for the object; otherwise NULL.
Also it doesn't match with the record for the column itself:
postgres=# create temp table bar (a int default 0);
CREATE TABLE
postgres=# alter table bar drop column a;
ALTER TABLE
postgres=# select * from dropped_objects \gx
-[ RECORD 1 ]---+------------------
n | 1
classid | 1259
objid | 16435
objsubid | 1
original | t
normal | f
is_temporary | t
object_type | table column
schema_name | pg_temp
object_name |
object_identity | pg_temp.bar.a
address_names | {pg_temp,bar,a}
address_args | {}
-[ RECORD 2 ]---+------------------
n | 2
classid | 2604
objid | 16438
objsubid | 0
original | f
normal | f
is_temporary | t
object_type | default value
schema_name | pg_temp
object_name | bar
object_identity | for pg_temp.bar.a
address_names | {pg_temp,bar,a}
address_args | {}
object_name is null for the table column, but not null for its default
value.
As for schema_name, I'm not sure whether it should be null or not.
Currently schema_name is null for triggers and policy objects, but that
may be accidental.
Best regards,
--
Sergey Shinderuk https://postgrespro.com/
On 12.09.2025 14:01, Sergey Shinderuk wrote:
> object_name is null for the table column, but not null for its default
> value.
>
> As for schema_name, I'm not sure whether it should be null or not.
> Currently schema_name is null for triggers and policy objects, but that
> may be accidental.
Perhaps "default value" should be like "table constraint", which have
schema_name and null object_name.
postgres=# create temp table bar (a int not null default 0);
CREATE TABLE
postgres=# alter table bar drop column a;
ALTER TABLE
postgres=# select * from dropped_objects \gx
-[ RECORD 1 ]---+------------------------------
n | 1
classid | 1259
objid | 16445
objsubid | 1
original | t
normal | f
is_temporary | t
object_type | table column
schema_name | pg_temp
object_name |
object_identity | pg_temp.bar.a
address_names | {pg_temp,bar,a}
address_args | {}
-[ RECORD 2 ]---+------------------------------
n | 2
classid | 2604
objid | 16448
objsubid | 0
original | f
normal | f
is_temporary | t
object_type | default value
schema_name | pg_temp
object_name | bar
object_identity | for pg_temp.bar.a
address_names | {pg_temp,bar,a}
address_args | {}
-[ RECORD 3 ]---+------------------------------
n | 3
classid | 2606
objid | 16449
objsubid | 0
original | f
normal | f
is_temporary | t
object_type | table constraint
schema_name | pg_temp
object_name |
object_identity | bar_a_not_null on pg_temp.bar
address_names | {pg_temp,bar,bar_a_not_null}
address_args | {}
Best regards,
--
Sergey Shinderuk https://postgrespro.com/
Sergey Shinderuk <s.shinderuk@postgrespro.ru> writes:
> Thank you for working on this. I see you've fixed the patch and
> committed it as a0b99fc1220. I tested it a bit and see some side effects
> which may be unintentional.
Many thanks for double-checking!
> 1. SCHEMA lost object_name.
Ugh. I was misled first by assuming that EventTriggerSQLDropAddObject
didn't have *other* pre-existing bugs, and second by overestimating
the test coverage for this function. In particular I thought that
this coding pattern:
if (is_objectclass_supported(object->classId))
{
...
}
else
{
if (object->classId == NamespaceRelationId &&
isTempNamespace(object->objectId))
obj->istemp = true;
}
meant that is_objectclass_supported() doesn't return true for
NamespaceRelationId --- a conclusion I should have realized was silly,
I guess. So that "else" action was unreachable, and the code failed
to set "istemp" true for its own temp schema. But I took it on faith
and supposed that we weren't filling objname for schemas.
I would have spotted the behavior change if event_trigger.sql
made any attempt to verify more than a few of the outputs of
pg_event_trigger_dropped_objects(), but it didn't. So the attached
patch fixes that test script to print all the expected-to-be-stable
outputs.
> 2. DEFAULT VALUE now has schema_name and object_name.
Setting schema_name is expected I think: you can hardly opine that
an object is temp unless it's associated with a temp schema.
You're right that setting object_name to the table name is the
wrong thing, and again I blame that on poor test coverage.
> Currently schema_name is null for triggers and policy objects, but that
> may be accidental.
Double ugh. Triggers and policy objects have this exact same bug.
Fixed (and tested) in the attached.
I'm tempted to wonder if the objectaddress.c ObjectProperty
infrastructure should grow some support for cases like these,
but right now I think it'd be about a wash in terms of the
amount of code added.
regards, tom lane
diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c
index fcdcba009d4..f34868da5ab 100644
--- a/src/backend/commands/event_trigger.c
+++ b/src/backend/commands/event_trigger.c
@@ -30,6 +30,7 @@
#include "catalog/pg_opclass.h"
#include "catalog/pg_opfamily.h"
#include "catalog/pg_parameter_acl.h"
+#include "catalog/pg_policy.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_tablespace.h"
#include "catalog/pg_trigger.h"
@@ -1302,6 +1303,7 @@ EventTriggerSQLDropAddObject(const ObjectAddress *object, bool original, bool no
MemoryContextSwitchTo(oldcxt);
return;
}
+ obj->objname = get_namespace_name(object->objectId);
}
else if (object->classId == AttrDefaultRelationId)
{
@@ -1311,7 +1313,6 @@ EventTriggerSQLDropAddObject(const ObjectAddress *object, bool original, bool no
colobject = GetAttrDefaultColumnAddress(object->objectId);
if (OidIsValid(colobject.objectId))
{
- colobject.objectSubId = 0; /* convert to table reference */
if (!obtain_object_name_namespace(&colobject, obj))
{
pfree(obj);
@@ -1320,6 +1321,90 @@ EventTriggerSQLDropAddObject(const ObjectAddress *object, bool original, bool no
}
}
}
+ else if (object->classId == TriggerRelationId)
+ {
+ /* Similarly, a trigger is temp if its table is temp */
+ /* Sadly, there's no lsyscache.c support for trigger objects */
+ Relation pg_trigger_rel;
+ ScanKeyData skey[1];
+ SysScanDesc sscan;
+ HeapTuple tuple;
+ Oid relid;
+
+ /* Fetch the trigger's table OID the hard way */
+ pg_trigger_rel = table_open(TriggerRelationId, AccessShareLock);
+ ScanKeyInit(&skey[0],
+ Anum_pg_trigger_oid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(object->objectId));
+ sscan = systable_beginscan(pg_trigger_rel, TriggerOidIndexId, true,
+ NULL, 1, skey);
+ tuple = systable_getnext(sscan);
+ if (HeapTupleIsValid(tuple))
+ relid = ((Form_pg_trigger) GETSTRUCT(tuple))->tgrelid;
+ else
+ relid = InvalidOid; /* shouldn't happen */
+ systable_endscan(sscan);
+ table_close(pg_trigger_rel, AccessShareLock);
+ /* Do nothing if we didn't find the trigger */
+ if (OidIsValid(relid))
+ {
+ ObjectAddress relobject;
+
+ relobject.classId = RelationRelationId;
+ relobject.objectId = relid;
+ /* Arbitrarily set objectSubId nonzero so as not to fill objname */
+ relobject.objectSubId = 1;
+ if (!obtain_object_name_namespace(&relobject, obj))
+ {
+ pfree(obj);
+ MemoryContextSwitchTo(oldcxt);
+ return;
+ }
+ }
+ }
+ else if (object->classId == PolicyRelationId)
+ {
+ /* Similarly, a policy is temp if its table is temp */
+ /* Sadly, there's no lsyscache.c support for policy objects */
+ Relation pg_policy_rel;
+ ScanKeyData skey[1];
+ SysScanDesc sscan;
+ HeapTuple tuple;
+ Oid relid;
+
+ /* Fetch the policy's table OID the hard way */
+ pg_policy_rel = table_open(PolicyRelationId, AccessShareLock);
+ ScanKeyInit(&skey[0],
+ Anum_pg_policy_oid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(object->objectId));
+ sscan = systable_beginscan(pg_policy_rel, PolicyOidIndexId, true,
+ NULL, 1, skey);
+ tuple = systable_getnext(sscan);
+ if (HeapTupleIsValid(tuple))
+ relid = ((Form_pg_policy) GETSTRUCT(tuple))->polrelid;
+ else
+ relid = InvalidOid; /* shouldn't happen */
+ systable_endscan(sscan);
+ table_close(pg_policy_rel, AccessShareLock);
+ /* Do nothing if we didn't find the policy */
+ if (OidIsValid(relid))
+ {
+ ObjectAddress relobject;
+
+ relobject.classId = RelationRelationId;
+ relobject.objectId = relid;
+ /* Arbitrarily set objectSubId nonzero so as not to fill objname */
+ relobject.objectSubId = 1;
+ if (!obtain_object_name_namespace(&relobject, obj))
+ {
+ pfree(obj);
+ MemoryContextSwitchTo(oldcxt);
+ return;
+ }
+ }
+ }
else
{
/* Generic handling for all other object classes */
diff --git a/src/test/regress/expected/event_trigger.out b/src/test/regress/expected/event_trigger.out
index 0e090cbc375..16e4530708c 100644
--- a/src/test/regress/expected/event_trigger.out
+++ b/src/test/regress/expected/event_trigger.out
@@ -228,9 +228,15 @@ INSERT INTO undroppable_objs VALUES
('table', 'schema_one.table_three'),
('table', 'audit_tbls.schema_two_table_three');
CREATE TABLE dropped_objects (
- type text,
- schema text,
- object text
+ object_type text,
+ schema_name text,
+ object_name text,
+ object_identity text,
+ address_names text[],
+ address_args text[],
+ is_temporary bool,
+ original bool,
+ normal bool
);
-- This tests errors raised within event triggers; the one in audit_tbls
-- uses 2nd-level recursive invocation via test_evtrig_dropped_objects().
@@ -268,8 +274,12 @@ BEGIN
END IF;
INSERT INTO dropped_objects
- (type, schema, object) VALUES
- (obj.object_type, obj.schema_name, obj.object_identity);
+ (object_type, schema_name, object_name,
+ object_identity, address_names, address_args,
+ is_temporary, original, normal) VALUES
+ (obj.object_type, obj.schema_name, obj.object_name,
+ obj.object_identity, obj.address_names, obj.address_args,
+ obj.is_temporary, obj.original, obj.normal);
END LOOP;
END
$$;
@@ -325,42 +335,44 @@ NOTICE: table "audit_tbls_schema_two_table_three" does not exist, skipping
NOTICE: table "schema_one_table_one" does not exist, skipping
NOTICE: table "schema_one_table two" does not exist, skipping
NOTICE: table "schema_one_table_three" does not exist, skipping
-SELECT * FROM dropped_objects WHERE schema IS NULL OR schema <> 'pg_toast';
- type | schema | object
---------------+------------+-------------------------------------
- table column | schema_one | schema_one.table_one.a
- schema | | schema_two
- table | schema_two | schema_two.table_two
- type | schema_two | schema_two.table_two
- type | schema_two | schema_two.table_two[]
- table | audit_tbls | audit_tbls.schema_two_table_three
- type | audit_tbls | audit_tbls.schema_two_table_three
- type | audit_tbls | audit_tbls.schema_two_table_three[]
- table | schema_two | schema_two.table_three
- type | schema_two | schema_two.table_three
- type | schema_two | schema_two.table_three[]
- function | schema_two | schema_two.add(integer,integer)
- aggregate | schema_two | schema_two.newton(integer)
- schema | | schema_one
- table | schema_one | schema_one.table_one
- type | schema_one | schema_one.table_one
- type | schema_one | schema_one.table_one[]
- table | schema_one | schema_one."table two"
- type | schema_one | schema_one."table two"
- type | schema_one | schema_one."table two"[]
- table | schema_one | schema_one.table_three
- type | schema_one | schema_one.table_three
- type | schema_one | schema_one.table_three[]
+-- exclude TOAST objects because they have unstable names
+SELECT * FROM dropped_objects
+ WHERE schema_name IS NULL OR schema_name <> 'pg_toast';
+ object_type | schema_name | object_name | object_identity |
address_names | address_args | is_temporary | original | normal
+--------------+-------------+-------------------------+-------------------------------------+---------------------------------------+-------------------+--------------+----------+--------
+ table column | schema_one | | schema_one.table_one.a | {schema_one,table_one,a}
| {} | f | t | f
+ schema | | schema_two | schema_two | {schema_two}
| {} | f | t | f
+ table | schema_two | table_two | schema_two.table_two | {schema_two,table_two}
| {} | f | f | t
+ type | schema_two | table_two | schema_two.table_two | {schema_two.table_two}
| {} | f | f | f
+ type | schema_two | _table_two | schema_two.table_two[] | {schema_two.table_two[]}
| {} | f | f | f
+ table | audit_tbls | schema_two_table_three | audit_tbls.schema_two_table_three |
{audit_tbls,schema_two_table_three} | {} | f | t | f
+ type | audit_tbls | schema_two_table_three | audit_tbls.schema_two_table_three |
{audit_tbls.schema_two_table_three} | {} | f | f | f
+ type | audit_tbls | _schema_two_table_three | audit_tbls.schema_two_table_three[] |
{audit_tbls.schema_two_table_three[]}| {} | f | f | f
+ table | schema_two | table_three | schema_two.table_three | {schema_two,table_three}
| {} | f | f | t
+ type | schema_two | table_three | schema_two.table_three | {schema_two.table_three}
| {} | f | f | f
+ type | schema_two | _table_three | schema_two.table_three[] |
{schema_two.table_three[]} | {} | f | f | f
+ function | schema_two | | schema_two.add(integer,integer) | {schema_two,add}
| {integer,integer} | f | f | t
+ aggregate | schema_two | | schema_two.newton(integer) | {schema_two,newton}
| {integer} | f | f | t
+ schema | | schema_one | schema_one | {schema_one}
| {} | f | t | f
+ table | schema_one | table_one | schema_one.table_one | {schema_one,table_one}
| {} | f | f | t
+ type | schema_one | table_one | schema_one.table_one | {schema_one.table_one}
| {} | f | f | f
+ type | schema_one | _table_one | schema_one.table_one[] | {schema_one.table_one[]}
| {} | f | f | f
+ table | schema_one | table two | schema_one."table two" | {schema_one,"table two"}
| {} | f | f | t
+ type | schema_one | table two | schema_one."table two" | {"schema_one.\"table
two\""} | {} | f | f | f
+ type | schema_one | _table two | schema_one."table two"[] | {"schema_one.\"table
two\"[]"} | {} | f | f | f
+ table | schema_one | table_three | schema_one.table_three | {schema_one,table_three}
| {} | f | f | t
+ type | schema_one | table_three | schema_one.table_three | {schema_one.table_three}
| {} | f | f | f
+ type | schema_one | _table_three | schema_one.table_three[] |
{schema_one.table_three[]} | {} | f | f | f
(23 rows)
DROP OWNED BY regress_evt_user;
NOTICE: schema "audit_tbls" does not exist, skipping
-SELECT * FROM dropped_objects WHERE type = 'schema';
- type | schema | object
---------+--------+------------
- schema | | schema_two
- schema | | schema_one
- schema | | audit_tbls
+SELECT * FROM dropped_objects WHERE object_type = 'schema';
+ object_type | schema_name | object_name | object_identity | address_names | address_args | is_temporary | original |
normal
+-------------+-------------+-------------+-----------------+---------------+--------------+--------------+----------+--------
+ schema | | schema_two | schema_two | {schema_two} | {} | f | t |
f
+ schema | | schema_one | schema_one | {schema_one} | {} | f | t |
f
+ schema | | audit_tbls | audit_tbls | {audit_tbls} | {} | f | t |
f
(3 rows)
DROP ROLE regress_evt_user;
@@ -378,9 +390,10 @@ BEGIN
IF NOT r.normal AND NOT r.original THEN
CONTINUE;
END IF;
- RAISE NOTICE 'NORMAL: orig=% normal=% istemp=% type=% identity=% name=% args=%',
+ RAISE NOTICE 'NORMAL: orig=% normal=% istemp=% type=% identity=% schema=% name=% addr=% args=%',
r.original, r.normal, r.is_temporary, r.object_type,
- r.object_identity, r.address_names, r.address_args;
+ r.object_identity, r.schema_name, r.object_name,
+ r.address_names, r.address_args;
END LOOP;
END; $$;
CREATE EVENT TRIGGER regress_event_trigger_report_dropped ON sql_drop
@@ -436,18 +449,18 @@ CREATE TABLE evttrig.part_15_20 PARTITION OF evttrig.part_10_20 (id)
FOR VALUES FROM (15) TO (20);
NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.part_15_20
ALTER TABLE evttrig.two DROP COLUMN col_c;
-NOTICE: NORMAL: orig=t normal=f istemp=f type=table column identity=evttrig.two.col_c name={evttrig,two,col_c}
args={}
-NOTICE: NORMAL: orig=f normal=t istemp=f type=table constraint identity=two_col_c_check on evttrig.two
name={evttrig,two,two_col_c_check}args={}
+NOTICE: NORMAL: orig=t normal=f istemp=f type=table column identity=evttrig.two.col_c schema=evttrig name=<NULL>
addr={evttrig,two,col_c}args={}
+NOTICE: NORMAL: orig=f normal=t istemp=f type=table constraint identity=two_col_c_check on evttrig.two schema=evttrig
name=<NULL>addr={evttrig,two,two_col_c_check} args={}
NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.two
ALTER TABLE evttrig.one ALTER COLUMN col_b DROP DEFAULT;
-NOTICE: NORMAL: orig=t normal=f istemp=f type=default value identity=for evttrig.one.col_b name={evttrig,one,col_b}
args={}
+NOTICE: NORMAL: orig=t normal=f istemp=f type=default value identity=for evttrig.one.col_b schema=evttrig name=<NULL>
addr={evttrig,one,col_b}args={}
NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.one
ALTER TABLE evttrig.one DROP CONSTRAINT one_pkey;
-NOTICE: NORMAL: orig=t normal=f istemp=f type=table constraint identity=one_pkey on evttrig.one
name={evttrig,one,one_pkey}args={}
+NOTICE: NORMAL: orig=t normal=f istemp=f type=table constraint identity=one_pkey on evttrig.one schema=evttrig
name=<NULL>addr={evttrig,one,one_pkey} args={}
NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.one
ALTER TABLE evttrig.one DROP COLUMN col_c;
-NOTICE: NORMAL: orig=t normal=f istemp=f type=table column identity=evttrig.one.col_c name={evttrig,one,col_c}
args={}
-NOTICE: NORMAL: orig=f normal=t istemp=f type=default value identity=for evttrig.one.col_c name={evttrig,one,col_c}
args={}
+NOTICE: NORMAL: orig=t normal=f istemp=f type=table column identity=evttrig.one.col_c schema=evttrig name=<NULL>
addr={evttrig,one,col_c}args={}
+NOTICE: NORMAL: orig=f normal=t istemp=f type=default value identity=for evttrig.one.col_c schema=evttrig name=<NULL>
addr={evttrig,one,col_c}args={}
NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.one
ALTER TABLE evttrig.id ALTER COLUMN col_d SET DATA TYPE bigint;
NOTICE: END: command_tag=ALTER SEQUENCE type=sequence identity=evttrig.id_col_d_seq
@@ -456,26 +469,26 @@ ALTER TABLE evttrig.id ALTER COLUMN col_d DROP IDENTITY,
ALTER COLUMN col_d SET DATA TYPE int;
NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.id
DROP INDEX evttrig.one_idx;
-NOTICE: NORMAL: orig=t normal=f istemp=f type=index identity=evttrig.one_idx name={evttrig,one_idx} args={}
+NOTICE: NORMAL: orig=t normal=f istemp=f type=index identity=evttrig.one_idx schema=evttrig name=one_idx
addr={evttrig,one_idx}args={}
DROP SCHEMA evttrig CASCADE;
NOTICE: drop cascades to 4 other objects
DETAIL: drop cascades to table evttrig.one
drop cascades to table evttrig.two
drop cascades to table evttrig.id
drop cascades to table evttrig.parted
-NOTICE: NORMAL: orig=t normal=f istemp=f type=schema identity=evttrig name={evttrig} args={}
-NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.one name={evttrig,one} args={}
-NOTICE: NORMAL: orig=f normal=t istemp=f type=sequence identity=evttrig.one_col_a_seq name={evttrig,one_col_a_seq}
args={}
-NOTICE: NORMAL: orig=f normal=t istemp=f type=default value identity=for evttrig.one.col_a name={evttrig,one,col_a}
args={}
-NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.two name={evttrig,two} args={}
-NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.id name={evttrig,id} args={}
-NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.parted name={evttrig,parted} args={}
-NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.part_1_10 name={evttrig,part_1_10} args={}
-NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.part_10_20 name={evttrig,part_10_20} args={}
-NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.part_10_15 name={evttrig,part_10_15} args={}
-NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.part_15_20 name={evttrig,part_15_20} args={}
+NOTICE: NORMAL: orig=t normal=f istemp=f type=schema identity=evttrig schema=<NULL> name=evttrig addr={evttrig}
args={}
+NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.one schema=evttrig name=one addr={evttrig,one}
args={}
+NOTICE: NORMAL: orig=f normal=t istemp=f type=sequence identity=evttrig.one_col_a_seq schema=evttrig
name=one_col_a_seqaddr={evttrig,one_col_a_seq} args={}
+NOTICE: NORMAL: orig=f normal=t istemp=f type=default value identity=for evttrig.one.col_a schema=evttrig name=<NULL>
addr={evttrig,one,col_a}args={}
+NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.two schema=evttrig name=two addr={evttrig,two}
args={}
+NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.id schema=evttrig name=id addr={evttrig,id}
args={}
+NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.parted schema=evttrig name=parted
addr={evttrig,parted}args={}
+NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.part_1_10 schema=evttrig name=part_1_10
addr={evttrig,part_1_10}args={}
+NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.part_10_20 schema=evttrig name=part_10_20
addr={evttrig,part_10_20}args={}
+NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.part_10_15 schema=evttrig name=part_10_15
addr={evttrig,part_10_15}args={}
+NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.part_15_20 schema=evttrig name=part_15_20
addr={evttrig,part_15_20}args={}
DROP TABLE a_temp_tbl;
-NOTICE: NORMAL: orig=t normal=f istemp=t type=table identity=pg_temp.a_temp_tbl name={pg_temp,a_temp_tbl} args={}
+NOTICE: NORMAL: orig=t normal=f istemp=t type=table identity=pg_temp.a_temp_tbl schema=pg_temp name=a_temp_tbl
addr={pg_temp,a_temp_tbl}args={}
-- check unfiltered results, too
CREATE OR REPLACE FUNCTION event_trigger_report_dropped()
RETURNS event_trigger
@@ -485,34 +498,61 @@ DECLARE r record;
BEGIN
FOR r IN SELECT * from pg_event_trigger_dropped_objects()
LOOP
- RAISE NOTICE 'DROP: orig=% normal=% istemp=% type=% identity=% name=% args=%',
+ RAISE NOTICE 'DROP: orig=% normal=% istemp=% type=% identity=% schema=% name=% addr=% args=%',
r.original, r.normal, r.is_temporary, r.object_type,
- r.object_identity, r.address_names, r.address_args;
+ r.object_identity, r.schema_name, r.object_name,
+ r.address_names, r.address_args;
END LOOP;
END; $$;
NOTICE: END: command_tag=CREATE FUNCTION type=function identity=public.event_trigger_report_dropped()
+CREATE FUNCTION event_trigger_dummy_trigger()
+ RETURNS trigger
+ LANGUAGE plpgsql
+AS $$
+BEGIN
+ RETURN new;
+END; $$;
+NOTICE: END: command_tag=CREATE FUNCTION type=function identity=public.event_trigger_dummy_trigger()
CREATE TABLE evtrg_nontemp_table (f1 int primary key, f2 int default 42);
NOTICE: END: command_tag=CREATE TABLE type=table identity=public.evtrg_nontemp_table
NOTICE: END: command_tag=CREATE INDEX type=index identity=public.evtrg_nontemp_table_pkey
+CREATE TRIGGER evtrg_nontemp_trig
+ BEFORE INSERT ON evtrg_nontemp_table
+ EXECUTE FUNCTION event_trigger_dummy_trigger();
+NOTICE: END: command_tag=CREATE TRIGGER type=trigger identity=evtrg_nontemp_trig on public.evtrg_nontemp_table
+CREATE POLICY evtrg_nontemp_pol ON evtrg_nontemp_table USING (f2 > 0);
+NOTICE: END: command_tag=CREATE POLICY type=policy identity=evtrg_nontemp_pol on public.evtrg_nontemp_table
DROP TABLE evtrg_nontemp_table;
-NOTICE: DROP: orig=t normal=f istemp=f type=table identity=public.evtrg_nontemp_table
name={public,evtrg_nontemp_table}args={}
-NOTICE: DROP: orig=f normal=f istemp=f type=type identity=public.evtrg_nontemp_table
name={public.evtrg_nontemp_table}args={}
-NOTICE: DROP: orig=f normal=f istemp=f type=type identity=public.evtrg_nontemp_table[]
name={public.evtrg_nontemp_table[]}args={}
-NOTICE: DROP: orig=f normal=f istemp=f type=default value identity=for public.evtrg_nontemp_table.f2
name={public,evtrg_nontemp_table,f2}args={}
-NOTICE: DROP: orig=f normal=f istemp=f type=table constraint identity=evtrg_nontemp_table_f1_not_null on
public.evtrg_nontemp_tablename={public,evtrg_nontemp_table,evtrg_nontemp_table_f1_not_null} args={}
-NOTICE: DROP: orig=f normal=f istemp=f type=table constraint identity=evtrg_nontemp_table_pkey on
public.evtrg_nontemp_tablename={public,evtrg_nontemp_table,evtrg_nontemp_table_pkey} args={}
-NOTICE: DROP: orig=f normal=f istemp=f type=index identity=public.evtrg_nontemp_table_pkey
name={public,evtrg_nontemp_table_pkey}args={}
+NOTICE: DROP: orig=t normal=f istemp=f type=table identity=public.evtrg_nontemp_table schema=public
name=evtrg_nontemp_tableaddr={public,evtrg_nontemp_table} args={}
+NOTICE: DROP: orig=f normal=f istemp=f type=type identity=public.evtrg_nontemp_table schema=public
name=evtrg_nontemp_tableaddr={public.evtrg_nontemp_table} args={}
+NOTICE: DROP: orig=f normal=f istemp=f type=type identity=public.evtrg_nontemp_table[] schema=public
name=_evtrg_nontemp_tableaddr={public.evtrg_nontemp_table[]} args={}
+NOTICE: DROP: orig=f normal=f istemp=f type=default value identity=for public.evtrg_nontemp_table.f2 schema=public
name=<NULL>addr={public,evtrg_nontemp_table,f2} args={}
+NOTICE: DROP: orig=f normal=f istemp=f type=table constraint identity=evtrg_nontemp_table_f1_not_null on
public.evtrg_nontemp_tableschema=public name=<NULL> addr={public,evtrg_nontemp_table,evtrg_nontemp_table_f1_not_null}
args={}
+NOTICE: DROP: orig=f normal=f istemp=f type=table constraint identity=evtrg_nontemp_table_pkey on
public.evtrg_nontemp_tableschema=public name=<NULL> addr={public,evtrg_nontemp_table,evtrg_nontemp_table_pkey} args={}
+NOTICE: DROP: orig=f normal=f istemp=f type=index identity=public.evtrg_nontemp_table_pkey schema=public
name=evtrg_nontemp_table_pkeyaddr={public,evtrg_nontemp_table_pkey} args={}
+NOTICE: DROP: orig=f normal=f istemp=f type=trigger identity=evtrg_nontemp_trig on public.evtrg_nontemp_table
schema=publicname=<NULL> addr={public,evtrg_nontemp_table,evtrg_nontemp_trig} args={}
+NOTICE: DROP: orig=f normal=t istemp=f type=policy identity=evtrg_nontemp_pol on public.evtrg_nontemp_table
schema=publicname=<NULL> addr={public,evtrg_nontemp_table,evtrg_nontemp_pol} args={}
CREATE TEMP TABLE a_temp_tbl (f1 int primary key, f2 int default 42);
NOTICE: END: command_tag=CREATE TABLE type=table identity=pg_temp.a_temp_tbl
NOTICE: END: command_tag=CREATE INDEX type=index identity=pg_temp.a_temp_tbl_pkey
+CREATE TRIGGER a_temp_trig
+ BEFORE INSERT ON a_temp_tbl
+ EXECUTE FUNCTION event_trigger_dummy_trigger();
+NOTICE: END: command_tag=CREATE TRIGGER type=trigger identity=a_temp_trig on pg_temp.a_temp_tbl
+CREATE POLICY a_temp_pol ON a_temp_tbl USING (f2 > 0);
+NOTICE: END: command_tag=CREATE POLICY type=policy identity=a_temp_pol on pg_temp.a_temp_tbl
DROP TABLE a_temp_tbl;
-NOTICE: DROP: orig=t normal=f istemp=t type=table identity=pg_temp.a_temp_tbl name={pg_temp,a_temp_tbl} args={}
-NOTICE: DROP: orig=f normal=f istemp=t type=type identity=pg_temp.a_temp_tbl name={pg_temp.a_temp_tbl} args={}
-NOTICE: DROP: orig=f normal=f istemp=t type=type identity=pg_temp.a_temp_tbl[] name={pg_temp.a_temp_tbl[]} args={}
-NOTICE: DROP: orig=f normal=f istemp=t type=default value identity=for pg_temp.a_temp_tbl.f2
name={pg_temp,a_temp_tbl,f2}args={}
-NOTICE: DROP: orig=f normal=f istemp=t type=table constraint identity=a_temp_tbl_f1_not_null on pg_temp.a_temp_tbl
name={pg_temp,a_temp_tbl,a_temp_tbl_f1_not_null}args={}
-NOTICE: DROP: orig=f normal=f istemp=t type=table constraint identity=a_temp_tbl_pkey on pg_temp.a_temp_tbl
name={pg_temp,a_temp_tbl,a_temp_tbl_pkey}args={}
-NOTICE: DROP: orig=f normal=f istemp=t type=index identity=pg_temp.a_temp_tbl_pkey name={pg_temp,a_temp_tbl_pkey}
args={}
+NOTICE: DROP: orig=t normal=f istemp=t type=table identity=pg_temp.a_temp_tbl schema=pg_temp name=a_temp_tbl
addr={pg_temp,a_temp_tbl}args={}
+NOTICE: DROP: orig=f normal=f istemp=t type=type identity=pg_temp.a_temp_tbl schema=pg_temp name=a_temp_tbl
addr={pg_temp.a_temp_tbl}args={}
+NOTICE: DROP: orig=f normal=f istemp=t type=type identity=pg_temp.a_temp_tbl[] schema=pg_temp name=_a_temp_tbl
addr={pg_temp.a_temp_tbl[]}args={}
+NOTICE: DROP: orig=f normal=f istemp=t type=default value identity=for pg_temp.a_temp_tbl.f2 schema=pg_temp
name=<NULL>addr={pg_temp,a_temp_tbl,f2} args={}
+NOTICE: DROP: orig=f normal=f istemp=t type=table constraint identity=a_temp_tbl_f1_not_null on pg_temp.a_temp_tbl
schema=pg_tempname=<NULL> addr={pg_temp,a_temp_tbl,a_temp_tbl_f1_not_null} args={}
+NOTICE: DROP: orig=f normal=f istemp=t type=table constraint identity=a_temp_tbl_pkey on pg_temp.a_temp_tbl
schema=pg_tempname=<NULL> addr={pg_temp,a_temp_tbl,a_temp_tbl_pkey} args={}
+NOTICE: DROP: orig=f normal=f istemp=t type=index identity=pg_temp.a_temp_tbl_pkey schema=pg_temp
name=a_temp_tbl_pkeyaddr={pg_temp,a_temp_tbl_pkey} args={}
+NOTICE: DROP: orig=f normal=f istemp=t type=trigger identity=a_temp_trig on pg_temp.a_temp_tbl schema=pg_temp
name=<NULL>addr={pg_temp,a_temp_tbl,a_temp_trig} args={}
+NOTICE: DROP: orig=f normal=t istemp=t type=policy identity=a_temp_pol on pg_temp.a_temp_tbl schema=pg_temp
name=<NULL>addr={pg_temp,a_temp_tbl,a_temp_pol} args={}
+DROP FUNCTION event_trigger_dummy_trigger();
+NOTICE: DROP: orig=t normal=f istemp=f type=function identity=public.event_trigger_dummy_trigger() schema=public
name=<NULL>addr={public,event_trigger_dummy_trigger} args={}
-- CREATE OPERATOR CLASS without FAMILY clause should report
-- both CREATE OPERATOR FAMILY and CREATE OPERATOR CLASS
CREATE OPERATOR CLASS evttrigopclass FOR TYPE int USING btree AS STORAGE int;
diff --git a/src/test/regress/sql/event_trigger.sql b/src/test/regress/sql/event_trigger.sql
index ef5978b9697..c613c0cfd43 100644
--- a/src/test/regress/sql/event_trigger.sql
+++ b/src/test/regress/sql/event_trigger.sql
@@ -202,9 +202,15 @@ INSERT INTO undroppable_objs VALUES
('table', 'audit_tbls.schema_two_table_three');
CREATE TABLE dropped_objects (
- type text,
- schema text,
- object text
+ object_type text,
+ schema_name text,
+ object_name text,
+ object_identity text,
+ address_names text[],
+ address_args text[],
+ is_temporary bool,
+ original bool,
+ normal bool
);
-- This tests errors raised within event triggers; the one in audit_tbls
@@ -245,8 +251,12 @@ BEGIN
END IF;
INSERT INTO dropped_objects
- (type, schema, object) VALUES
- (obj.object_type, obj.schema_name, obj.object_identity);
+ (object_type, schema_name, object_name,
+ object_identity, address_names, address_args,
+ is_temporary, original, normal) VALUES
+ (obj.object_type, obj.schema_name, obj.object_name,
+ obj.object_identity, obj.address_names, obj.address_args,
+ obj.is_temporary, obj.original, obj.normal);
END LOOP;
END
$$;
@@ -263,10 +273,12 @@ DROP SCHEMA schema_one, schema_two CASCADE;
DELETE FROM undroppable_objs WHERE object_identity = 'schema_one.table_three';
DROP SCHEMA schema_one, schema_two CASCADE;
-SELECT * FROM dropped_objects WHERE schema IS NULL OR schema <> 'pg_toast';
+-- exclude TOAST objects because they have unstable names
+SELECT * FROM dropped_objects
+ WHERE schema_name IS NULL OR schema_name <> 'pg_toast';
DROP OWNED BY regress_evt_user;
-SELECT * FROM dropped_objects WHERE type = 'schema';
+SELECT * FROM dropped_objects WHERE object_type = 'schema';
DROP ROLE regress_evt_user;
@@ -285,9 +297,10 @@ BEGIN
IF NOT r.normal AND NOT r.original THEN
CONTINUE;
END IF;
- RAISE NOTICE 'NORMAL: orig=% normal=% istemp=% type=% identity=% name=% args=%',
+ RAISE NOTICE 'NORMAL: orig=% normal=% istemp=% type=% identity=% schema=% name=% addr=% args=%',
r.original, r.normal, r.is_temporary, r.object_type,
- r.object_identity, r.address_names, r.address_args;
+ r.object_identity, r.schema_name, r.object_name,
+ r.address_names, r.address_args;
END LOOP;
END; $$;
CREATE EVENT TRIGGER regress_event_trigger_report_dropped ON sql_drop
@@ -346,17 +359,37 @@ DECLARE r record;
BEGIN
FOR r IN SELECT * from pg_event_trigger_dropped_objects()
LOOP
- RAISE NOTICE 'DROP: orig=% normal=% istemp=% type=% identity=% name=% args=%',
+ RAISE NOTICE 'DROP: orig=% normal=% istemp=% type=% identity=% schema=% name=% addr=% args=%',
r.original, r.normal, r.is_temporary, r.object_type,
- r.object_identity, r.address_names, r.address_args;
+ r.object_identity, r.schema_name, r.object_name,
+ r.address_names, r.address_args;
END LOOP;
END; $$;
+CREATE FUNCTION event_trigger_dummy_trigger()
+ RETURNS trigger
+ LANGUAGE plpgsql
+AS $$
+BEGIN
+ RETURN new;
+END; $$;
+
CREATE TABLE evtrg_nontemp_table (f1 int primary key, f2 int default 42);
+CREATE TRIGGER evtrg_nontemp_trig
+ BEFORE INSERT ON evtrg_nontemp_table
+ EXECUTE FUNCTION event_trigger_dummy_trigger();
+CREATE POLICY evtrg_nontemp_pol ON evtrg_nontemp_table USING (f2 > 0);
DROP TABLE evtrg_nontemp_table;
+
CREATE TEMP TABLE a_temp_tbl (f1 int primary key, f2 int default 42);
+CREATE TRIGGER a_temp_trig
+ BEFORE INSERT ON a_temp_tbl
+ EXECUTE FUNCTION event_trigger_dummy_trigger();
+CREATE POLICY a_temp_pol ON a_temp_tbl USING (f2 > 0);
DROP TABLE a_temp_tbl;
+DROP FUNCTION event_trigger_dummy_trigger();
+
-- CREATE OPERATOR CLASS without FAMILY clause should report
-- both CREATE OPERATOR FAMILY and CREATE OPERATOR CLASS
CREATE OPERATOR CLASS evttrigopclass FOR TYPE int USING btree AS STORAGE int;
On 13.09.2025 00:19, Tom Lane wrote:
> Fixed (and tested) in the attached.
Great! Thank you.
> So that "else" action was unreachable, and the code failed
> to set "istemp" true for its own temp schema.
As for dropping my own temp schema, it's still a bit inconsistent (as it
was before):
postgres=# select pg_my_temp_schema()::regnamespace;
pg_my_temp_schema
-------------------
pg_temp_0
(1 row)
postgres=# drop schema pg_temp_0;
DROP SCHEMA
postgres=# select * from dropped_objects where object_type =
'schema' and is_temporary \gx
-[ RECORD 1 ]---+----------
n | 7
classid | 2615
objid | 16398
objsubid | 0
original | t
normal | f
is_temporary | t
object_type | schema
schema_name |
object_name | pg_temp_0
object_identity | pg_temp
address_names | {pg_temp}
address_args | {}
object_identity is pg_temp, but object_name is pg_temp_0. But maybe
that's okay. Anyway, I don't think that dropping my own temp schema
makes sense.
Also I noticed that schema_name for temp functions doesn't match with
object_identity (pg_temp vs pg_temp_1):
postgres=# create function pg_temp.bar(int) returns int as 'select
$1' language sql;
CREATE FUNCTION
postgres=# drop function pg_temp.bar(int);
DROP FUNCTION
postgres=# select * from dropped_objects where object_type =
'function' and is_temporary \gx
-[ RECORD 1 ]---+-----------------------
n | 8
classid | 1255
objid | 16412
objsubid | 0
original | t
normal | f
is_temporary | t
object_type | function
schema_name | pg_temp
object_name |
object_identity | pg_temp_1.bar(integer)
address_names | {pg_temp,bar}
address_args | {integer}
There should be a call to get_namespace_name_or_temp somewhere, I guess.
If you say this should be fixed, I can come up with a patch later. But
maybe it's trivial.
Thanks again!
--
Sergey Shinderuk https://postgrespro.com/