[YSQL][postgres_fdw] Triggers don't work immediately

[def-]

Jira Link: DB-1301

Description

Adding a trigger with postgres_fdw doesn't always work immediately, inserting a pg_sleep seems to help. Similar to https://github.com/yugabyte/yugabyte-db/issues/11554 , but there is no error in this case, the query still succeeds but without the trigger being called.

CREATE EXTENSION postgres_fdw;

CREATE SERVER testserver1 FOREIGN DATA WRAPPER postgres_fdw;
DO $d$
    BEGIN
        EXECUTE $$CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
            OPTIONS (dbname '$$||current_database()||$$',
                     port '$$||current_setting('port')||$$'
            )$$;
        EXECUTE $$CREATE SERVER loopback2 FOREIGN DATA WRAPPER postgres_fdw
            OPTIONS (dbname '$$||current_database()||$$',
                     port '$$||current_setting('port')||$$'
            )$$;
    END;
$d$;

CREATE USER MAPPING FOR public SERVER testserver1
OPTIONS (user 'value', password 'value');
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback2;

create table itrtest (a int, b text) partition by list (a);
create table loct1 (a int check (a in (1)), b text);
create foreign table remp1 (a int check (a in (1)), b text) server loopback options (table_name 'loct1');
create table loct2 (a int check (a in (2)), b text);
create foreign table remp2 (b text, a int check (a in (2))) server loopback options (table_name 'loct2');
alter table itrtest attach partition remp1 for values in (1);
alter table itrtest attach partition remp2 for values in (2);

create function br_insert_trigfunc() returns trigger as $$
begin
new.b := new.b || ' triggered !';
return new;
end
$$ language plpgsql;
create trigger loct1_br_insert_trigger before insert on loct1
for each row execute procedure br_insert_trigfunc();
create trigger loct2_br_insert_trigger before insert on loct2
for each row execute procedure br_insert_trigfunc();

insert into itrtest values (1, 'foo') returning *;
insert into itrtest values (2, 'qux') returning *;
insert into itrtest values (1, 'test1'), (2, 'test2') returning *;
with result as (insert into itrtest values (1, 'test1'), (2, 'test2') returning *) select * from result;

[def-]

In the regress tests this occasionally returned:

 a |        b
---+-----------------
 1 | foo
(1 row)

INSERT 0 1
 a |        b
---+-----------------
 2 | qux
(1 row)

INSERT 0 1
 a |         b
---+-------------------
 1 | test1
 2 | test2
(2 rows)

INSERT 0 2
 a |         b
---+-------------------
 1 | test1
 2 | test2
(2 rows)

While the expected is:

 a |        b
---+-----------------
 1 | foo triggered !
(1 row)

INSERT 0 1
 a |        b
---+-----------------
 2 | qux triggered !
(1 row)

INSERT 0 1
 a |         b
---+-------------------
 1 | test1 triggered !
 2 | test2 triggered !
(2 rows)

INSERT 0 2
 a |         b
---+-------------------
 1 | test1 triggered !
 2 | test2 triggered !
(2 rows)

Worked around by adding pg_sleep(1) after creating the triggers.

[def-]

There is also this test, same problem apparently, trigger sometimes already exists immediately, sometimes not:

delete from rem2;

create trigger trig_null before insert on loc2
    for each row execute procedure trig_null();

-- Nothing happens
copy rem2 from stdin;
1   foo
2   bar
\.
select * from rem2 order by f1;

Expected return:

 f1 | f2
----+----
(0 rows)

Actual return is sporadically:

 f1 | f2
----+-----
  1 | foo
  2 | bar
(2 rows)