Re: BUG #14431: ERROR: XX000: relation 5022917 has no triggers ("should not happen")

On 11/24/2016 12:49 PM, benjie@jemjie.com wrote:
> The following bug has been logged on the website:
>
> Bug reference:      14431
> Logged by:          Benjie Gillam
> Email address:      benjie@jemjie.com
> PostgreSQL version: 9.5.2
> Operating system:   OS X 10.11.5
> Description:
>
> I've triggered an error that "should not happen", took me a while to figure
> out what I'd done.
>
> I've managed to build a small test case that reproduces the issue (it
> relates to `deferrable initially deferred`):
>
> https://gist.github.com/benjie/9d4f8df41ebf7a0fb78788c00c75561a

For the archives, the test case at that link is:

<>
begin;
  create table a(foo varchar not null primary key);
  create table b(foo varchar not null primary key, constraint qux
foreign key (foo) references a deferrable initially deferred);
  create function bar() returns trigger as $$
  begin
    if TG_OP = 'INSERT' then
      insert into b(foo) values(new.foo);
    elsif TG_OP = 'DELETE' then
      delete from b where foo = old.foo;
    end if;
    return new;
  end;
  $$ language plpgsql;

  create trigger bar after insert or delete on a for each row execute
procedure bar();
commit;

insert into a values('baz');

begin;
  delete from a where foo = 'baz';
  drop trigger bar on a;
  drop function bar();
  alter table b drop constraint qux;
commit;
</>

I can reproduce it.

> I don't think it's a particularly important issue. Here's the relevant line
> of code:
>
>
https://github.com/postgres/postgres/blob/6fa391be4e83139cd134d5ccfc1499809bb8c98c/src/backend/commands/trigger.c#L4027-L4029


--
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support