Re: [PATCH] Allow queries in WHEN expression of FOR EACH STATEMENT triggers

Hi Joe,

This is my review of your patch
On Fri, Jul 17, 2020 at 1:22 AM Joe Wildish <joe(at)lateraljoin(dot)com> wrote:

> Hi hackers,
>
> Attached is a patch for supporting queries in the WHEN expression of
> statement triggers.

- Currently, <literal>WHEN</literal> expressions cannot contain

- subqueries.

subqueries in row trigger's is not supported in your patch so the the
documentation have to reflect it

+ </literal>UPDATE</literal> triggers are able to refer to both
</literal>OLD</literal>

+ and <literal>NEW</literal>

Opening and ending tag mismatch on UPDATE and OLD literal so documentation
build fails and please update the documentation on server programming
section too

+ /*

+ * Plan the statement. No need to rewrite as it can only refer to the

+ * transition tables OLD and NEW, and the relation which is being

+ * triggered upon.

+ */

+ stmt = pg_plan_query(query, trigger->tgqual, 0, NULL);

+ dest = CreateDestReceiver(DestTuplestore);

+ store = tuplestore_begin_heap(false, false, work_mem);

+ tupdesc = CreateTemplateTupleDesc(1);

+ whenslot = MakeSingleTupleTableSlot(tupdesc, &TTSOpsMinimalTuple);

Instead of planning every time the trigger fire I suggest to store plan or
prepared statement node so planning time can be saved

There are server crash on the following sequence of command

CREATE TABLE main_table (a int unique, b int);

CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS '

BEGIN

RAISE NOTICE ''trigger_func(%) called: action = %, when = %, level = %'',
TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;

RETURN NULL;

END;';

INSERT INTO main_table DEFAULT VALUES;

CREATE TRIGGER after_insert AFTER INSERT ON main_table

REFERENCING NEW TABLE AS NEW FOR EACH STATEMENT

WHEN (500 <= ANY(SELECT b FROM NEW union SELECT a FROM main_table))

EXECUTE PROCEDURE trigger_func('after_insert');

INSERT INTO main_table (a, b) VALUES

(101, 498),

(102, 499);

server crashed

regards

Surafel