Обсуждение: getting ERROR "relation 16401 has no triggers" with partition foreignkey alter
getting ERROR "relation 16401 has no triggers" with partition foreignkey alter
От
Rajkumar Raghuwanshi
Дата:
Hi,
I am getting ERROR: relation 16401 has no triggers error while executing below query.
postgres=# create table tbl1(f1 int primary key);
CREATE TABLE
postgres=# create table tbl2(f1 int references tbl1 deferrable initially deferred) partition by range(f1);
CREATE TABLE
postgres=# create table tbl2_p1 partition of tbl2 for values from (minvalue) to (maxvalue);
CREATE TABLE
postgres=# insert into tbl1 values(1);
INSERT 0 1
postgres=# begin;
BEGIN
postgres=# insert into tbl2 values(1);
INSERT 0 1
postgres=# alter table tbl2 drop constraint tbl2_f1_fkey;
ALTER TABLE
postgres=# commit;
ERROR: relation 16395 has no triggers
CREATE TABLE
postgres=# create table tbl2(f1 int references tbl1 deferrable initially deferred) partition by range(f1);
CREATE TABLE
postgres=# create table tbl2_p1 partition of tbl2 for values from (minvalue) to (maxvalue);
CREATE TABLE
postgres=# insert into tbl1 values(1);
INSERT 0 1
postgres=# begin;
BEGIN
postgres=# insert into tbl2 values(1);
INSERT 0 1
postgres=# alter table tbl2 drop constraint tbl2_f1_fkey;
ALTER TABLE
postgres=# commit;
ERROR: relation 16395 has no triggers
Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation
Re: getting ERROR "relation 16401 has no triggers" with partitionforeign key alter
От
Michael Paquier
Дата:
On Tue, Jul 16, 2019 at 01:07:45PM +0530, Rajkumar Raghuwanshi wrote: > I am getting ERROR: relation 16401 has no triggers error while executing > below query. Indeed, confirmed. I can reproduce that down to v11, so that's not an open item. I have added an entry in the section for older issues though. -- Michael
Вложения
Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com> writes:
> I am getting ERROR: relation 16401 has no triggers error while executing
> below query.
Yeah, I can reproduce that back to v11. If you try the same scenario
with a non-partitioned table you get
ERROR: 55006: cannot ALTER TABLE "tbl2" because it has pending trigger events
LOCATION: CheckTableNotInUse, tablecmds.c:3436
but that test evidently fails to detect pending events for a partition
child table.
regards, tom lane
Re: getting ERROR "relation 16401 has no triggers" with partitionforeign key alter
От
Alvaro Herrera
Дата:
On 2019-Jul-16, Tom Lane wrote: > Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com> writes: > > I am getting ERROR: relation 16401 has no triggers error while executing > > below query. > > Yeah, I can reproduce that back to v11. If you try the same scenario > with a non-partitioned table you get > > ERROR: 55006: cannot ALTER TABLE "tbl2" because it has pending trigger events > LOCATION: CheckTableNotInUse, tablecmds.c:3436 > > but that test evidently fails to detect pending events for a partition > child table. Ah, yeah. So the problem is that when dropping an FK, ATExecDropConstraint does not recurse itself, but instead relies on the dependency mechanism, which obviously does not run CheckTableNotInUse on the partitions. I think we should just run CheckTableNotInUse for each partition in ATExecDropConstraint. Trying that out now. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: getting ERROR "relation 16401 has no triggers" with partitionforeign key alter
От
Alvaro Herrera
Дата:
On 2019-Jul-17, Alvaro Herrera wrote: > I think we should just run CheckTableNotInUse for each partition in > ATExecDropConstraint. Trying that out now. Actually, that doesn't fix this problem, because the partitioned side is the *referencing* side, and ATExecDropConstraint is obsessed about the *referenced* side only and assumes that the calling code has already dealt with the referencing side checks. I'm trying a fix for that now. I wonder if there are other AT subcommands that are similarly broken, because many of them skip the CheckTableNotInUse for the partitions. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: getting ERROR "relation 16401 has no triggers" with partitionforeign key alter
От
Alvaro Herrera
Дата:
On 2019-Jul-17, Alvaro Herrera wrote: > Actually, that doesn't fix this problem, because the partitioned side is > the *referencing* side, and ATExecDropConstraint is obsessed about the > *referenced* side only and assumes that the calling code has already > dealt with the referencing side checks. I'm trying a fix for that now. Yeah, the attached patch fixes Rajkumar's reproducer. > I wonder if there are other AT subcommands that are similarly broken, > because many of them skip the CheckTableNotInUse for the partitions. I suppose the question here is where else do we need to call the new ATRecurseCheckNotInUse function (which needs a comment). I thought about doing the recursion in CheckTableNotInUse itself, but I didn't feel comfortable with assuming that all callers are OK with that. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Вложения
Re: getting ERROR "relation 16401 has no triggers" with partitionforeign key alter
От
Alvaro Herrera
Дата:
On 2019-Jul-17, Alvaro Herrera wrote: > On 2019-Jul-17, Alvaro Herrera wrote: > > I wonder if there are other AT subcommands that are similarly broken, > > because many of them skip the CheckTableNotInUse for the partitions. > > I suppose the question here is where else do we need to call the new > ATRecurseCheckNotInUse function (which needs a comment). I decided to rename the new function to ATCheckPartitionsNotInUse, and make it a no-op for legacy inheritance. This seems quite specific to partitioned tables (as opposed to legacy inheritance behavior). After looking at the code some more, I think calling the new function in the Prep phase is correct. The attached patch is pretty much final form for this bugfix. I decided to unwrap a couple of error messages (I did get bitten while grepping because of this), and reordered one of the new Identity command cases in ATPrepCmd since it appeared in inconsistent order in that one place of four. I looked at all the other AT subcommand cases that might require the same treatment, and didn't find anything -- either the recursion is done at Prep time, which checks already, or contains the proper check at Exec time right after opening the partition rel. (I think it would be better to do the check during the Prep phase, to avoid wasting work in case a partition happens to be used. However, that's not critical and not for this commit to fix IMO.) Separately from that, there's AT_SetLogged / AT_SetUnlogged which look pretty dubious ... I'm not sure that recursion is handled correctly there. Maybe it's considered okay to have a partitioned table with unlogged partitions, and vice versa? I also noticed that AT_AlterConstraint does not handle recursion at all, and it also has this comment: * Currently only works for Foreign Key constraints. * Foreign keys do not inherit, so we purposely ignore the * recursion bit here, but we keep the API the same for when * other constraint types are supported. which sounds to oppose reality. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Вложения
Re: getting ERROR "relation 16401 has no triggers" with partitionforeign key alter
От
Alvaro Herrera
Дата:
On 2019-Jul-22, Alvaro Herrera wrote: > After looking at the code some more, I think calling the new function in > the Prep phase is correct. The attached patch is pretty much final form > for this bugfix. I decided to unwrap a couple of error messages (I did > get bitten while grepping because of this), and reordered one of the new > Identity command cases in ATPrepCmd since it appeared in inconsistent > order in that one place of four. Pushed to all three branches. Thanks for reporting -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Alvaro Herrera <alvherre@2ndquadrant.com> writes: > On 2019-Jul-22, Alvaro Herrera wrote: >> After looking at the code some more, I think calling the new function in >> the Prep phase is correct. The attached patch is pretty much final form >> for this bugfix. I decided to unwrap a couple of error messages (I did >> get bitten while grepping because of this), and reordered one of the new >> Identity command cases in ATPrepCmd since it appeared in inconsistent >> order in that one place of four. > Pushed to all three branches. This is still listed as a live issue in https://wiki.postgresql.org/wiki/PostgreSQL_12_Open_Items#Live_issues Should that be closed now? regards, tom lane
Re: getting ERROR "relation 16401 has no triggers" with partitionforeign key alter
От
Alvaro Herrera
Дата:
On 2019-Aug-14, Tom Lane wrote: > Alvaro Herrera <alvherre@2ndquadrant.com> writes: > > On 2019-Jul-22, Alvaro Herrera wrote: > >> After looking at the code some more, I think calling the new function in > >> the Prep phase is correct. The attached patch is pretty much final form > >> for this bugfix. I decided to unwrap a couple of error messages (I did > >> get bitten while grepping because of this), and reordered one of the new > >> Identity command cases in ATPrepCmd since it appeared in inconsistent > >> order in that one place of four. > > > Pushed to all three branches. > > This is still listed as a live issue in > > https://wiki.postgresql.org/wiki/PostgreSQL_12_Open_Items#Live_issues > > Should that be closed now? Yep, done, thanks! -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services