Horton

Thread

Commits

  1. Check that partitions are not in use when dropping constraints

  1. getting ERROR "relation 16401 has no triggers" with partition foreign key alter

    Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com> — 2019-07-16T07:37:45Z

    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

Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation

  2. Re: getting ERROR "relation 16401 has no triggers" with partition foreign key alter

    Michael Paquier <michael@paquier.xyz> — 2019-07-16T08:05:55Z

    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

  3. Re: getting ERROR "relation 16401 has no triggers" with partition foreign key alter

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-07-16T13:59:52Z

    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

  4. Re: getting ERROR "relation 16401 has no triggers" with partition foreign key alter

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2019-07-17T22:08:47Z

    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

  5. Re: getting ERROR "relation 16401 has no triggers" with partition foreign key alter

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2019-07-17T22:30:37Z

    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

  6. Re: getting ERROR "relation 16401 has no triggers" with partition foreign key alter

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2019-07-17T22:48:07Z

    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

  7. Re: getting ERROR "relation 16401 has no triggers" with partition foreign key alter

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2019-07-22T22:18:29Z

    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

  8. Re: getting ERROR "relation 16401 has no triggers" with partition foreign key alter

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2019-07-23T21:34:29Z

    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

  9. Re: getting ERROR "relation 16401 has no triggers" with partition foreign key alter

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-08-14T18:22:20Z

    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

  10. Re: getting ERROR "relation 16401 has no triggers" with partition foreign key alter

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2019-08-14T18:33:36Z

    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