pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedA row-level trigger on a partitioned table is not created on a sub-partition created later
8+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2020-01-02 1fa846f1c9 d732148398 adc9cb6f26 Fix cloning of row triggers to sub-partitions
2019-12-27 27a3b2ad83 b4507a22fe 0a40f33b87 Add pg_dump test for triggers on partitioned tables
* A row-level trigger on a partitioned table is not created on a sub-partition created later
@ 2019-12-05 07:24 Petr Fedorov <petr.fedorov@phystech.edu>
2019-12-18 02:11 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Amit Langote <amitlangote09@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Petr Fedorov @ 2019-12-05 07:24 UTC (permalink / raw)
To: pgsql-bugs
Hello,
According to the documentation
<https://www.postgresql.org/docs/11/sql-createtrigger.html>, creating a
row-level trigger on a partitioned table will cause identical triggers
to be created in all its existing partitions; and any partitions created
or attached later will contain an identical trigger, too.
It does not happen for a sub-partition - partition of partition - when
it is created/attached later. Steps to reproduce (11.6, Centos 7):
create table level1 (id1 integer not null, id2 integer not null, id3
integer not null) partition by list (id2);
create table level2 partition of level1 for values in (1) partition by
list (id3);
create table level3 partition of level2 for values in (1);
create or replace function trigger_func() returns trigger language
'plpgsql' as $body$ begin raise exception 'fired'; return null; end $body$;
create trigger test_trigger after insert on level1 for each row execute
procedure trigger_func();
insert into level1 values (1,1,1); -- fails as expected due to
test_trigger();
drop table level1;
drop function trigger_func();
create table level1 (id1 integer not null, id2 integer not null, id3
integer not null) partition by list (id2);
create or replace function trigger_func() returns trigger language
'plpgsql' as $body$ begin raise exception 'fired'; return null; end $body$;
create trigger test_trigger after insert on level1 for each row execute
procedure trigger_func();
create table level2 partition of level1 for values in (1) partition by
list (id3);
create table level3 partition of level2 for values in (1);
insert into level1 values (1,1,1); -- row inserted
psql \d+ shows that level3 does not have row level trigger while level2
and level1 have.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: A row-level trigger on a partitioned table is not created on a sub-partition created later
2019-12-05 07:24 A row-level trigger on a partitioned table is not created on a sub-partition created later Petr Fedorov <petr.fedorov@phystech.edu>
@ 2019-12-18 02:11 ` Amit Langote <amitlangote09@gmail.com>
2019-12-18 09:56 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 1 reply; 8+ messages in thread
From: Amit Langote @ 2019-12-18 02:11 UTC (permalink / raw)
To: Petr Fedorov <petr.fedorov@phystech.edu>; +Cc: pgsql-bugs; Alvaro Herrera <alvherre@2ndquadrant.com>
Hello,
On Thu, Dec 5, 2019 at 4:24 PM Petr Fedorov <petr.fedorov@phystech.edu> wrote:
> create table level1 (id1 integer not null, id2 integer not null, id3 integer not null) partition by list (id2);
> create or replace function trigger_func() returns trigger language 'plpgsql' as $body$ begin raise exception 'fired'; return null; end $body$;
> create trigger test_trigger after insert on level1 for each row execute procedure trigger_func();
>
> create table level2 partition of level1 for values in (1) partition by list (id3);
> create table level3 partition of level2 for values in (1);
>
> insert into level1 values (1,1,1); -- row inserted
>
> psql \d+ shows that level3 does not have row level trigger while level2 and level1 have.
That is a bug. :(
Alvaro, isn't marking triggers cloned to partitions "internal"
unnecessary? Because the cloned trigger on partition (level2 in above
example) is marked internal, CloneRowTriggersToPartition() skips it
when called on a sub-partition (level3 in above example).
Attached patch to fix that passes make check, although a bit surprised
that it does.
Thanks,
Amit
Attachments:
dont-mark-cloned-triggers-internal.patch (text/plain, 5.1K) download
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index e9955707fa..9d23388100 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -1938,7 +1938,7 @@ index_constraint_create(Relation heapRelation,
(void) CreateTrigger(trigger, NULL, RelationGetRelid(heapRelation),
InvalidOid, conOid, indexRelationId, InvalidOid,
- InvalidOid, NULL, true, false);
+ InvalidOid, NULL, true);
}
/*
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index daa80ec4aa..1295f94f54 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -9853,7 +9853,7 @@ CreateFKCheckTrigger(Oid myRelOid, Oid refRelOid, Constraint *fkconstraint,
fk_trigger->args = NIL;
(void) CreateTrigger(fk_trigger, NULL, myRelOid, refRelOid, constraintOid,
- indexOid, InvalidOid, InvalidOid, NULL, true, false);
+ indexOid, InvalidOid, InvalidOid, NULL, true);
/* Make changes-so-far visible */
CommandCounterIncrement();
@@ -9921,7 +9921,7 @@ createForeignKeyActionTriggers(Relation rel, Oid refRelOid, Constraint *fkconstr
(void) CreateTrigger(fk_trigger, NULL, refRelOid, RelationGetRelid(rel),
constraintOid,
- indexOid, InvalidOid, InvalidOid, NULL, true, false);
+ indexOid, InvalidOid, InvalidOid, NULL, true);
/* Make changes-so-far visible */
CommandCounterIncrement();
@@ -9977,7 +9977,7 @@ createForeignKeyActionTriggers(Relation rel, Oid refRelOid, Constraint *fkconstr
(void) CreateTrigger(fk_trigger, NULL, refRelOid, RelationGetRelid(rel),
constraintOid,
- indexOid, InvalidOid, InvalidOid, NULL, true, false);
+ indexOid, InvalidOid, InvalidOid, NULL, true);
}
/*
@@ -16068,7 +16068,7 @@ CloneRowTriggersToPartition(Relation parent, Relation partition)
CreateTrigger(trigStmt, NULL, RelationGetRelid(partition),
trigForm->tgconstrrelid, InvalidOid, InvalidOid,
trigForm->tgfoid, trigForm->oid, qual,
- false, true);
+ false);
MemoryContextSwitchTo(oldcxt);
MemoryContextReset(perTupCxt);
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index faeea16d21..3eeb00a81f 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -162,7 +162,7 @@ ObjectAddress
CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
Oid relOid, Oid refRelOid, Oid constraintOid, Oid indexOid,
Oid funcoid, Oid parentTriggerOid, Node *whenClause,
- bool isInternal, bool in_partition)
+ bool isInternal)
{
int16 tgtype;
int ncolumns;
@@ -837,11 +837,6 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
/*
* Build the new pg_trigger tuple.
- *
- * When we're creating a trigger in a partition, we mark it as internal,
- * even though we don't do the isInternal magic in this function. This
- * makes the triggers in partitions identical to the ones in the
- * partitioned tables, except that they are marked internal.
*/
memset(nulls, false, sizeof(nulls));
@@ -852,7 +847,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
values[Anum_pg_trigger_tgfoid - 1] = ObjectIdGetDatum(funcoid);
values[Anum_pg_trigger_tgtype - 1] = Int16GetDatum(tgtype);
values[Anum_pg_trigger_tgenabled - 1] = CharGetDatum(TRIGGER_FIRES_ON_ORIGIN);
- values[Anum_pg_trigger_tgisinternal - 1] = BoolGetDatum(isInternal || in_partition);
+ values[Anum_pg_trigger_tgisinternal - 1] = BoolGetDatum(isInternal);
values[Anum_pg_trigger_tgconstrrelid - 1] = ObjectIdGetDatum(constrrelid);
values[Anum_pg_trigger_tgconstrindid - 1] = ObjectIdGetDatum(indexOid);
values[Anum_pg_trigger_tgconstraint - 1] = ObjectIdGetDatum(constraintOid);
@@ -1192,7 +1187,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
partdesc->oids[i], refRelOid,
InvalidOid, indexOnChild,
funcoid, trigoid, qual,
- isInternal, true);
+ isInternal);
table_close(childTbl, NoLock);
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 3a03ca7e2f..678d0e2d74 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1520,7 +1520,7 @@ ProcessUtilitySlow(ParseState *pstate,
address = CreateTrigger((CreateTrigStmt *) parsetree,
queryString, InvalidOid, InvalidOid,
InvalidOid, InvalidOid, InvalidOid,
- InvalidOid, NULL, false, false);
+ InvalidOid, NULL, false);
break;
case T_CreatePLangStmt:
diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h
index a46feeedb0..633d75bd49 100644
--- a/src/include/commands/trigger.h
+++ b/src/include/commands/trigger.h
@@ -160,7 +160,7 @@ extern PGDLLIMPORT int SessionReplicationRole;
extern ObjectAddress CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
Oid relOid, Oid refRelOid, Oid constraintOid, Oid indexOid,
Oid funcoid, Oid parentTriggerOid, Node *whenClause,
- bool isInternal, bool in_partition);
+ bool isInternal);
extern void RemoveTriggerById(Oid trigOid);
extern Oid get_trigger_oid(Oid relid, const char *name, bool missing_ok);
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: A row-level trigger on a partitioned table is not created on a sub-partition created later
2019-12-05 07:24 A row-level trigger on a partitioned table is not created on a sub-partition created later Petr Fedorov <petr.fedorov@phystech.edu>
2019-12-18 02:11 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Amit Langote <amitlangote09@gmail.com>
@ 2019-12-18 09:56 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2019-12-26 08:00 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Amit Langote <amitlangote09@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Alvaro Herrera @ 2019-12-18 09:56 UTC (permalink / raw)
To: Amit Langote <amitlangote09@gmail.com>; +Cc: Petr Fedorov <petr.fedorov@phystech.edu>; pgsql-bugs
On 2019-Dec-18, Amit Langote wrote:
> Hello,
>
> On Thu, Dec 5, 2019 at 4:24 PM Petr Fedorov <petr.fedorov@phystech.edu> wrote:
> > create table level1 (id1 integer not null, id2 integer not null, id3 integer not null) partition by list (id2);
> > create or replace function trigger_func() returns trigger language 'plpgsql' as $body$ begin raise exception 'fired'; return null; end $body$;
> > create trigger test_trigger after insert on level1 for each row execute procedure trigger_func();
> >
> > create table level2 partition of level1 for values in (1) partition by list (id3);
> > create table level3 partition of level2 for values in (1);
> >
> > insert into level1 values (1,1,1); -- row inserted
> >
> > psql \d+ shows that level3 does not have row level trigger while level2 and level1 have.
>
> That is a bug. :(
Ouch.
> Alvaro, isn't marking triggers cloned to partitions "internal"
> unnecessary? Because the cloned trigger on partition (level2 in above
> example) is marked internal, CloneRowTriggersToPartition() skips it
> when called on a sub-partition (level3 in above example).
>
> Attached patch to fix that passes make check, although a bit surprised
> that it does.
IIRC that change would break pg_dump.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: A row-level trigger on a partitioned table is not created on a sub-partition created later
2019-12-05 07:24 A row-level trigger on a partitioned table is not created on a sub-partition created later Petr Fedorov <petr.fedorov@phystech.edu>
2019-12-18 02:11 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Amit Langote <amitlangote09@gmail.com>
2019-12-18 09:56 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Alvaro Herrera <alvherre@2ndquadrant.com>
@ 2019-12-26 08:00 ` Amit Langote <amitlangote09@gmail.com>
2019-12-27 21:37 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 1 reply; 8+ messages in thread
From: Amit Langote @ 2019-12-26 08:00 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: Petr Fedorov <petr.fedorov@phystech.edu>; pgsql-bugs
On Wed, Dec 18, 2019 at 6:56 PM Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> On 2019-Dec-18, Amit Langote wrote:
> > Alvaro, isn't marking triggers cloned to partitions "internal"
> > unnecessary? Because the cloned trigger on partition (level2 in above
> > example) is marked internal, CloneRowTriggersToPartition() skips it
> > when called on a sub-partition (level3 in above example).
> >
> > Attached patch to fix that passes make check, although a bit surprised
> > that it does.
>
> IIRC that change would break pg_dump.
Indeed it does, but looks like partition triggers are not tested that
extensively in pg_dump's suite.
Attached updated patch with pg_dump hacks seems to do the trick for
me. What do you think?
Thanks,
Amit
Attachments:
dont-mark-cloned-triggers-internal_v2.patch (text/plain, 6.9K) download
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 7529bf39b8..f58a958d46 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -1937,7 +1937,7 @@ index_constraint_create(Relation heapRelation,
(void) CreateTrigger(trigger, NULL, RelationGetRelid(heapRelation),
InvalidOid, conOid, indexRelationId, InvalidOid,
- InvalidOid, NULL, true, false);
+ InvalidOid, NULL, true);
}
/*
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index e19772aa90..4ab4615fbe 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -9852,7 +9852,7 @@ CreateFKCheckTrigger(Oid myRelOid, Oid refRelOid, Constraint *fkconstraint,
fk_trigger->args = NIL;
(void) CreateTrigger(fk_trigger, NULL, myRelOid, refRelOid, constraintOid,
- indexOid, InvalidOid, InvalidOid, NULL, true, false);
+ indexOid, InvalidOid, InvalidOid, NULL, true);
/* Make changes-so-far visible */
CommandCounterIncrement();
@@ -9920,7 +9920,7 @@ createForeignKeyActionTriggers(Relation rel, Oid refRelOid, Constraint *fkconstr
(void) CreateTrigger(fk_trigger, NULL, refRelOid, RelationGetRelid(rel),
constraintOid,
- indexOid, InvalidOid, InvalidOid, NULL, true, false);
+ indexOid, InvalidOid, InvalidOid, NULL, true);
/* Make changes-so-far visible */
CommandCounterIncrement();
@@ -9976,7 +9976,7 @@ createForeignKeyActionTriggers(Relation rel, Oid refRelOid, Constraint *fkconstr
(void) CreateTrigger(fk_trigger, NULL, refRelOid, RelationGetRelid(rel),
constraintOid,
- indexOid, InvalidOid, InvalidOid, NULL, true, false);
+ indexOid, InvalidOid, InvalidOid, NULL, true);
}
/*
@@ -16057,7 +16057,7 @@ CloneRowTriggersToPartition(Relation parent, Relation partition)
CreateTrigger(trigStmt, NULL, RelationGetRelid(partition),
trigForm->tgconstrrelid, InvalidOid, InvalidOid,
trigForm->tgfoid, trigForm->oid, qual,
- false, true);
+ false);
MemoryContextSwitchTo(oldcxt);
MemoryContextReset(perTupCxt);
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index b9fca3af3c..a9e46988e8 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -162,7 +162,7 @@ ObjectAddress
CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
Oid relOid, Oid refRelOid, Oid constraintOid, Oid indexOid,
Oid funcoid, Oid parentTriggerOid, Node *whenClause,
- bool isInternal, bool in_partition)
+ bool isInternal)
{
int16 tgtype;
int ncolumns;
@@ -837,11 +837,6 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
/*
* Build the new pg_trigger tuple.
- *
- * When we're creating a trigger in a partition, we mark it as internal,
- * even though we don't do the isInternal magic in this function. This
- * makes the triggers in partitions identical to the ones in the
- * partitioned tables, except that they are marked internal.
*/
memset(nulls, false, sizeof(nulls));
@@ -852,7 +847,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
values[Anum_pg_trigger_tgfoid - 1] = ObjectIdGetDatum(funcoid);
values[Anum_pg_trigger_tgtype - 1] = Int16GetDatum(tgtype);
values[Anum_pg_trigger_tgenabled - 1] = CharGetDatum(TRIGGER_FIRES_ON_ORIGIN);
- values[Anum_pg_trigger_tgisinternal - 1] = BoolGetDatum(isInternal || in_partition);
+ values[Anum_pg_trigger_tgisinternal - 1] = BoolGetDatum(isInternal);
values[Anum_pg_trigger_tgconstrrelid - 1] = ObjectIdGetDatum(constrrelid);
values[Anum_pg_trigger_tgconstrindid - 1] = ObjectIdGetDatum(indexOid);
values[Anum_pg_trigger_tgconstraint - 1] = ObjectIdGetDatum(constraintOid);
@@ -1185,7 +1180,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
partdesc->oids[i], refRelOid,
InvalidOid, indexOnChild,
funcoid, trigoid, qual,
- isInternal, true);
+ isInternal);
table_close(childTbl, NoLock);
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 3a03ca7e2f..678d0e2d74 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1520,7 +1520,7 @@ ProcessUtilitySlow(ParseState *pstate,
address = CreateTrigger((CreateTrigStmt *) parsetree,
queryString, InvalidOid, InvalidOid,
InvalidOid, InvalidOid, InvalidOid,
- InvalidOid, NULL, false, false);
+ InvalidOid, NULL, false);
break;
case T_CreatePLangStmt:
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 08658c8e86..68f9e6639b 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -7616,13 +7616,39 @@ getTriggers(Archive *fout, TableInfo tblinfo[], int numTables)
tbinfo->dobj.name);
resetPQExpBuffer(query);
- if (fout->remoteVersion >= 90000)
+
+ /*
+ * For servers that support triggers on partitioned tables, must
+ * exclude triggers of partitions that are also defined in some
+ * partitioned ancestor.
+ *
+ * NB: think not to use pretty=true in pg_get_triggerdef. It
+ * could result in non-forward-compatible dumps of WHEN clauses
+ * due to under-parenthesization.
+ */
+ if (fout->remoteVersion >= 110000)
+ {
+ appendPQExpBuffer(query,
+ "SELECT tgname, "
+ "tgfoid::pg_catalog.regproc AS tgfname, "
+ "pg_catalog.pg_get_triggerdef(oid, false) AS tgdef, "
+ "tgenabled, tableoid, oid "
+ "FROM pg_catalog.pg_trigger t "
+ "WHERE tgrelid = '%u'::pg_catalog.oid "
+ "AND NOT tgisinternal "
+ "AND NOT EXISTS "
+ " (SELECT 1 FROM pg_trigger "
+ " WHERE tgname = t.tgname "
+ " AND tgrelid IN "
+ " (SELECT inhparent "
+ " FROM pg_inherits, pg_class c"
+ " WHERE inhrelid = t.tgrelid"
+ " AND t.tgrelid = c.oid"
+ " AND c.relispartition))",
+ tbinfo->dobj.catId.oid);
+ }
+ else if (fout->remoteVersion >= 90000)
{
- /*
- * NB: think not to use pretty=true in pg_get_triggerdef. It
- * could result in non-forward-compatible dumps of WHEN clauses
- * due to under-parenthesization.
- */
appendPQExpBuffer(query,
"SELECT tgname, "
"tgfoid::pg_catalog.regproc AS tgfname, "
diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h
index a46feeedb0..633d75bd49 100644
--- a/src/include/commands/trigger.h
+++ b/src/include/commands/trigger.h
@@ -160,7 +160,7 @@ extern PGDLLIMPORT int SessionReplicationRole;
extern ObjectAddress CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
Oid relOid, Oid refRelOid, Oid constraintOid, Oid indexOid,
Oid funcoid, Oid parentTriggerOid, Node *whenClause,
- bool isInternal, bool in_partition);
+ bool isInternal);
extern void RemoveTriggerById(Oid trigOid);
extern Oid get_trigger_oid(Oid relid, const char *name, bool missing_ok);
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: A row-level trigger on a partitioned table is not created on a sub-partition created later
2019-12-05 07:24 A row-level trigger on a partitioned table is not created on a sub-partition created later Petr Fedorov <petr.fedorov@phystech.edu>
2019-12-18 02:11 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Amit Langote <amitlangote09@gmail.com>
2019-12-18 09:56 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Alvaro Herrera <alvherre@2ndquadrant.com>
2019-12-26 08:00 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Amit Langote <amitlangote09@gmail.com>
@ 2019-12-27 21:37 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2019-12-27 22:18 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 1 reply; 8+ messages in thread
From: Alvaro Herrera @ 2019-12-27 21:37 UTC (permalink / raw)
To: Amit Langote <amitlangote09@gmail.com>; +Cc: Petr Fedorov <petr.fedorov@phystech.edu>; pgsql-bugs
On 2019-Dec-26, Amit Langote wrote:
>
> Indeed it does, but looks like partition triggers are not tested that
> extensively in pg_dump's suite.
Hmm, you're right. I added a test to the three branches that would be
broken by your first patch. I hope the buildfarm likes it.
Working on getting your fix pushed now.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: A row-level trigger on a partitioned table is not created on a sub-partition created later
2019-12-05 07:24 A row-level trigger on a partitioned table is not created on a sub-partition created later Petr Fedorov <petr.fedorov@phystech.edu>
2019-12-18 02:11 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Amit Langote <amitlangote09@gmail.com>
2019-12-18 09:56 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Alvaro Herrera <alvherre@2ndquadrant.com>
2019-12-26 08:00 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Amit Langote <amitlangote09@gmail.com>
2019-12-27 21:37 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Alvaro Herrera <alvherre@2ndquadrant.com>
@ 2019-12-27 22:18 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2019-12-31 19:47 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 1 reply; 8+ messages in thread
From: Alvaro Herrera @ 2019-12-27 22:18 UTC (permalink / raw)
To: Amit Langote <amitlangote09@gmail.com>; +Cc: Petr Fedorov <petr.fedorov@phystech.edu>; pgsql-bugs
On 2019-Dec-27, Alvaro Herrera wrote:
> Working on getting your fix pushed now.
Ran out of time for today, but I added this test that reproduces the
issue -- fails without your patch, and works with it.
One thing I just realized is that in next minors' release notes we
should publish a recipe to fix catalogs for existing databases, unless
we go for a fix that doesn't require changing the catalogs -- I don't
know what that would be, though, but maybe it would not be totally
insane to clone even internal triggers in the cases that matter.
(I wonder if a better solution for the master branch would involve a new
pg_trigger column that indicates the parent trigger for a cloned
trigger. That would avoid pg_dump's new subquery. Also: would it be
better to use pg_partition_ancestors instead of the sub-subquery?
... though that doesn't work in pg11 ...)
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachments:
trigtest.patch (text/x-diff, 3.9K) download
commit 8a2318fcbdc501e6b230a22f6a6b1de57f871e74
Author: Alvaro Herrera <alvherre@alvh.no-ip.org>
AuthorDate: Fri Dec 27 18:56:05 2019 -0300
CommitDate: Fri Dec 27 18:59:25 2019 -0300
Add failed test that shows the issue
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index 1e4053ceed..b91fbd0648 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -1981,15 +1981,22 @@ create trigger trg1 after insert on trigpart for each row execute procedure trig
create table trigpart2 partition of trigpart for values from (1000) to (2000);
create table trigpart3 (like trigpart);
alter table trigpart attach partition trigpart3 for values from (2000) to (3000);
+create table trigpart4 partition of trigpart for values from (3000) to (4000) partition by range (a);
+create table trigpart41 partition of trigpart4 for values from (3000) to (3500);
+create table trigpart42 (like trigpart);
+alter table trigpart4 attach partition trigpart42 for values from (3500) to (4000);
select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger
where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;
- tgrelid | tgname | tgfoid
------------+--------+-----------------
- trigpart | trg1 | trigger_nothing
- trigpart1 | trg1 | trigger_nothing
- trigpart2 | trg1 | trigger_nothing
- trigpart3 | trg1 | trigger_nothing
-(4 rows)
+ tgrelid | tgname | tgfoid
+------------+--------+-----------------
+ trigpart | trg1 | trigger_nothing
+ trigpart1 | trg1 | trigger_nothing
+ trigpart2 | trg1 | trigger_nothing
+ trigpart3 | trg1 | trigger_nothing
+ trigpart4 | trg1 | trigger_nothing
+ trigpart41 | trg1 | trigger_nothing
+ trigpart42 | trg1 | trigger_nothing
+(7 rows)
drop trigger trg1 on trigpart1; -- fail
ERROR: cannot drop trigger trg1 on table trigpart1 because trigger trg1 on table trigpart requires it
@@ -2003,12 +2010,15 @@ HINT: You can drop trigger trg1 on table trigpart instead.
drop table trigpart2; -- ok, trigger should be gone in that partition
select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger
where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;
- tgrelid | tgname | tgfoid
------------+--------+-----------------
- trigpart | trg1 | trigger_nothing
- trigpart1 | trg1 | trigger_nothing
- trigpart3 | trg1 | trigger_nothing
-(3 rows)
+ tgrelid | tgname | tgfoid
+------------+--------+-----------------
+ trigpart | trg1 | trigger_nothing
+ trigpart1 | trg1 | trigger_nothing
+ trigpart3 | trg1 | trigger_nothing
+ trigpart4 | trg1 | trigger_nothing
+ trigpart41 | trg1 | trigger_nothing
+ trigpart42 | trg1 | trigger_nothing
+(6 rows)
drop trigger trg1 on trigpart; -- ok, all gone
select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index c21b6c124e..7cd835449c 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -1366,6 +1366,10 @@ create trigger trg1 after insert on trigpart for each row execute procedure trig
create table trigpart2 partition of trigpart for values from (1000) to (2000);
create table trigpart3 (like trigpart);
alter table trigpart attach partition trigpart3 for values from (2000) to (3000);
+create table trigpart4 partition of trigpart for values from (3000) to (4000) partition by range (a);
+create table trigpart41 partition of trigpart4 for values from (3000) to (3500);
+create table trigpart42 (like trigpart);
+alter table trigpart4 attach partition trigpart42 for values from (3500) to (4000);
select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger
where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;
drop trigger trg1 on trigpart1; -- fail
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: A row-level trigger on a partitioned table is not created on a sub-partition created later
2019-12-05 07:24 A row-level trigger on a partitioned table is not created on a sub-partition created later Petr Fedorov <petr.fedorov@phystech.edu>
2019-12-18 02:11 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Amit Langote <amitlangote09@gmail.com>
2019-12-18 09:56 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Alvaro Herrera <alvherre@2ndquadrant.com>
2019-12-26 08:00 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Amit Langote <amitlangote09@gmail.com>
2019-12-27 21:37 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Alvaro Herrera <alvherre@2ndquadrant.com>
2019-12-27 22:18 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Alvaro Herrera <alvherre@2ndquadrant.com>
@ 2019-12-31 19:47 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2020-01-02 08:06 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Amit Langote <amitlangote09@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Alvaro Herrera @ 2019-12-31 19:47 UTC (permalink / raw)
To: Amit Langote <amitlangote09@gmail.com>; +Cc: Petr Fedorov <petr.fedorov@phystech.edu>; pgsql-bugs
On 2019-Dec-27, Alvaro Herrera wrote:
> One thing I just realized is that in next minors' release notes we
> should publish a recipe to fix catalogs for existing databases, unless
> we go for a fix that doesn't require changing the catalogs -- I don't
> know what that would be, though, but maybe it would not be totally
> insane to clone even internal triggers in the cases that matter.
So, the more I thought about this, the more it seemed that marking those
triggers as not internal was the wrong thing to do. So I instead made
it clone the internal triggers that seem to matter. This fixes the
originally reported problem, and passes the test case I propose. Also,
pg_dump continues to work unchanged, and existing database don't need
tweaking (excepting those that might already be missing triggers).
It would be better to fix master afterwards, by adding a
pg_trigger.trgparentid column, which seems like it would be a better
answer going forward.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachments:
0001-Add-failed-test-that-shows-the-issue.patch (text/x-diff, 0B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: A row-level trigger on a partitioned table is not created on a sub-partition created later
2019-12-05 07:24 A row-level trigger on a partitioned table is not created on a sub-partition created later Petr Fedorov <petr.fedorov@phystech.edu>
2019-12-18 02:11 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Amit Langote <amitlangote09@gmail.com>
2019-12-18 09:56 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Alvaro Herrera <alvherre@2ndquadrant.com>
2019-12-26 08:00 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Amit Langote <amitlangote09@gmail.com>
2019-12-27 21:37 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Alvaro Herrera <alvherre@2ndquadrant.com>
2019-12-27 22:18 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Alvaro Herrera <alvherre@2ndquadrant.com>
2019-12-31 19:47 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Alvaro Herrera <alvherre@2ndquadrant.com>
@ 2020-01-02 08:06 ` Amit Langote <amitlangote09@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Amit Langote @ 2020-01-02 08:06 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: Petr Fedorov <petr.fedorov@phystech.edu>; pgsql-bugs
Sorry, away from work past few days.
On Wed, Jan 1, 2020 at 4:48 AM Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> On 2019-Dec-27, Alvaro Herrera wrote:
> > One thing I just realized is that in next minors' release notes we
> > should publish a recipe to fix catalogs for existing databases, unless
> > we go for a fix that doesn't require changing the catalogs -- I don't
> > know what that would be, though, but maybe it would not be totally
> > insane to clone even internal triggers in the cases that matter.
On second thought, I agree that not having to have to fix catalog
state for existing users would be good.
> So, the more I thought about this, the more it seemed that marking those
> triggers as not internal was the wrong thing to do. So I instead made
> it clone the internal triggers that seem to matter. This fixes the
> originally reported problem, and passes the test case I propose. Also,, away
> pg_dump continues to work unchanged, and existing database don't need
> tweaking (excepting those that might already be missing triggers).
0002 seems fine as a solution although some comments:
+/*
+ * doesTriggerDependOnAnotherTrigger
+ * Checks pg_depend for what the name says
+ *
+ * This is an ugly hack to cope with a catalog deficiency.
+ * Keep away from children. Do not stare with naked eyes. Do not propagate.
+ */
+static bool
+doesTriggerDependOnAnotherTrigger(Oid trigger_oid)
Instead of making this too generic sounding, would it be better to
name this to make clear what hack this is part of, say,
isPartitionTrigger?
> It would be better to fix master afterwards, by adding a
> pg_trigger.trgparentid column, which seems like it would be a better
> answer going forward.
Agreed.
Thanks,
Amit
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2020-01-02 08:06 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-12-05 07:24 A row-level trigger on a partitioned table is not created on a sub-partition created later Petr Fedorov <petr.fedorov@phystech.edu>
2019-12-18 02:11 ` Amit Langote <amitlangote09@gmail.com>
2019-12-18 09:56 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2019-12-26 08:00 ` Amit Langote <amitlangote09@gmail.com>
2019-12-27 21:37 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2019-12-27 22:18 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2019-12-31 19:47 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2020-01-02 08:06 ` Amit Langote <amitlangote09@gmail.com>
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