#311 plpgsql: failed to send COMMIT command to node

[Michael Paquier]

create temp table master(f1 int primary key);
create temp table slave(f1 int references master deferrable);

insert into master values(1);
insert into slave values(1);
insert into slave values(2); -- fails?

create function trap_foreign_key(int) returns int as $$
begin
begin
insert into slave values($1);
exception
when foreign_key_violation then
raise notice 'caught foreign_key_violation';
return 0;
end;
return 1;
end$$ language plpgsql;

select trap_foreign_key(1);
select trap_foreign_key(2); -- Error here
-- NOTICE: caught foreign_key_violation
-- ERROR: failed to send COMMIT command to node

This happens after catching an exception on a plpsql function.
Strangely it is easy to reproduce with psql but does not appear all the time with regressions.

[Michael Paquier]

Moving that to 1.1 bucket

[Koichi Suzuki]

Depending on the environment. Only reproducible on Michael's machine, but not on the buildfarm.

[Koichi Suzuki]

Q3

[Koichi Suzuki]

This is a random failure nature. Regarding the resource and severity, we change the group to 1.2 dev.

[Amit Khandekar]

I suspect this issue can possibly be due to sourceforge bug id #57. Postgres-XC does not currently handle sub-transactions. pl/pgsql exception block does not work correctly since it is implemented using subtransactions.