david.g.johnston@xxxxxxxxx wrote: Thanks for your "how the development of PostgreSQL works generally" comment. I've afraid that my full time job doesn't allow me time to study the PG codebase—and, anyway, its more than thirty years since I regularly wrote C programs so I wouldn't be able now to write a patch for consideration. To the present use-case… I was thinking too narrowly and so I asked the wrong question (for my "initially deferred" constraint trigger example). I had carelessly assumed that such a trigger would fire only at "commit" time. But you reminded me if the "set constraints all immediate" option. Thanks. I'd also used "assert" in my toy trigger to make it end with an error. But I've now realized that the exception that this causes is unhandleable. See my separate email to pgsql-general@xxxxxxxxxxxxxxxxxxxx: The P0004 assert_failure exception assert_failure exception seems to be unhandleable I re-wrote my test, with a proper "raise exception" in the trigger and with "set constraints all immediate" in the PL/pgSQL block that can cause the trigger to fire. I also re-wrote the test as a function that returns the outcome status and allows more choices pf "case statement "legs". See below for the complete, self-contained, code. It now works as I wanted to. So thank you very much, David, for showing me the technique that I needed to get that narrow example to work. Now to your comment "the current limitation has no workaround that I can come up with". Tom has (I think) confirmed that there is no workaround. And I fear that the "set constraints all immediate" approach is unsafe without using "serializable". But when "serializable" is used, sometimes errors don't occur until commit time. Without using "serializable", the following race condition can occur. (Imagine changing the definition of "bad" in my example to: (select exists(select 1 from t where k = 17)) and (select exists(select 1 from t where k = 42)) - Two concurrent sessions both issue "set constraints all immediate" from a PL/pgSQL block as the last executable statement before the top-level PL/pgSQL call completes. - One session inserts "17" and the other inserts "42". - Each executes the constraint test before either commits—and so the test succeeds in each session. So the net effect of these changes violates the data rule that a trigger seeks to enforce. This is really just the same race condition that's used to show why entity-level constraints cannot be safely enforced by ordinary triggers that are not set "initially deferred". So... in the bigger picture, when I want to honor the principle of modular software construction and hide all implementation details behind a PL/pgSQL API, I still have no general solution when, for example, entity level constraints are to be enforced. It seems that what is needed is a commit-time event trigger. Could this be feasible? Presumably, the mechanism must be at work at commit time, down in the implementation, with "initially deferred" constraints (and no programatically issued "set constraints all immediate"). B.t.w., Oracle database doesn't have a commit-time event trigger. But it does have a materialized view flavor that you declare to be refreshed at commit time. Some programmers use that as a workaround to enforce entity-level constraints. -------------------------------------------------------------------------------- -- The complete testcase. create table t(k int primary key); drop function if exists trg_fn(); create function trg_fn() returns trigger language plpgsql as $body$ declare bad constant boolean not null := (select exists(select 1 from t where k = 42)); begin if bad then raise exception using errcode = 'raise_exception', message = '"k = 42" is illegal', hint = 'Use a different value'; end if; return null; end; $body$; create constraint trigger trg after insert on t initially deferred for each row execute function trg_fn(); create function do_insert_outcome(mode in text) returns text language plpgsql as $body$ declare ret text not null := ''; err text not null := ''; begin begin case mode when 'good data' then for j in 10..20 loop insert into t(k) values(j); end loop; when 'unq key violation' then insert into t(k) values(15); when 'fires trigger' then insert into t(k) values(42); end case; set constraints all immediate; ret := 'no error'; exception when others then get stacked diagnostics err = returned_sqlstate; ret := 'error: '||err; end; return ret; end; $body$; \t on \o spool.txt select do_insert_outcome('good data'); select do_insert_outcome('unq key violation'); select do_insert_outcome('fires trigger'); \o \t off It produces this output (in a single-user test): no error error: 23505 error: P0001 |
