Re: [BUGS] BUG #14808: V10-beta4, backend abort

On Tue, Sep 12, 2017 at 5:03 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I wrote:
>> Note the "uniquely identified" bit --- you aren't allowed to have multiple
>> SCs for the same table and same kind of event, at least up to the bit
>> about column lists.  I've not fully wrapped my head around the column list
>> part of it, but certainly all effects of a particular RI constraint will
>> have the same column list.

Aside from the RI case, the other user visible change in behaviour
will be for statements that update the same table via multiple
ModifyTable nodes (wCTEs).  Our regression test has:

with wcte as (insert into table1 values (42)) insert into table2 values ('hello world');

... which demonstrates the fix for the original complaint that table1
and table2 earlier tried to use the same transition table (and
crashed).  A new variant inserting into table1 twice would show the
difference.  Today we get:

postgres=# with wcte as (insert into table1 values (42))            insert into table1 values (43);
NOTICE:  trigger = table1_trig, new table = (43,)
NOTICE:  trigger = table1_trig, new table = (42,)
INSERT 0 1

Presumably with your change there will be a single transition table
for inserts into table, holding both (42,) and (43,).  But will we
fire the trigger once or twice?  There is something fishy about making
it fire twice but show the same tuples to both invocations (for
example, it might break Kevin's proposed counting algorithm which this
feature is intended to support), but firing only once requires some
new inter-node co-ordination.

> In other words, UPDATE triggers with different column lists potentially
> need to see different transition tables, and any given row that was
> updated might need to appear in some subset of those tables.
>
> This seems like rather a mess to implement.  I wonder whether I'm
> reading it right, and whether other DBMSes actually implement it
> like that.

I guess the alternative is storing extra per-tuple metadata,
transferring more work to the reader.

The DB2 documentation has this example[1]:
CREATE TRIGGER REORDER    AFTER UPDATE OF ON_HAND, MAX_STOCKED ON PARTS    REFERENCING NEW_TABLE AS NTABLE    FOR EACH
STATEMENTMODE DB2SQL      BEGIN ATOMIC        SELECT ISSUE_SHIP_REQUEST(MAX_STOCKED - ON_HAND, PARTNO)          FROM
NTABLE       WHERE (ON_HAND < 0.10 * MAX_STOCKED);    END
 

I can't find any explicit discussion of whether this trigger could
ever see a transition row that results from an update that didn't name
ON_HAND or MAX_STOCKED.  I don't have DB2 access and I'm not sure how
I'd test that...  maybe with a self-referencing fk declared ON UPDATE
CASCADE?

Thanks to prodding from Peter Geoghegan we tackled the question of
whether the <trigger action time> clause controls just when the
trigger fires or also which transition tuples it sees.  By looking at
some wording relating to MERGE we concluded it must be both,
culminating in commit 8c55244a which separates the UPDATEs and INSERTs
resulting from INSERT ... ON CONFLICT DO UPDATE.  That had the
annoying result that we had to ban the use of (non-standard) "OR" in
<trigger action time> when transition tables are in play.  This FOR
UPDATE OF ... transition business seems sort of similar, but would
create arbitrarily many transition tables and require the executor to
write into all of them, or perhaps store extra meta data along with
captured rows for later filtering during scan.

> I think that what might be a good short-term solution is to refuse
> creation of column-specific triggers with transition tables (ie,
> if you ask for a transition table then you can only say AFTER UPDATE
> not AFTER UPDATE OF columnList).  Then, all TT-using triggers are
> interested in all modified rows and we don't have to distinguish
> different column lists for the purposes of transition tables.
> Then the problem reduces to one TCS per target table and event type,
> which doesn't seem too hard to do.

+1

Presumably would-be authors of triggers-with-transition-tables that
would fire only AFTER UPDATE OF foo already have to deal with the
possibility that you updated foo to the same value.  So I don't think
too much is lost, except perhaps some efficiency.

Thinking a bit harder about whether you might have semantic (rather
than performance) reasons to use AFTER UPDATE OF ... with subset TTs,
it occurs to me that there may be implications for transition tables
with inheritance.  We decided to disallow transition tables on
non-partition inheritance children anyway (see 501ed02c), but DB2
supports the equivalent.  It has a system of inheritance ("typed
tables", possibly conforming to SQL:1999 though I've never looked into
that) but has different rules about when row triggers and statement
triggers fire when you run DML statements on a table hierarchy.  Don't
quote me but it's something like our rules plus (1) row triggers of
all supertables of an affected table also fire (unless created with
CREATE TRIGGER ... ONLY), and (2) statement triggers of affected
subtables also fire.  Implementing that for our transition tables
would probably require more tuplestores and/or dynamic tuple
conversion and filtering during later scan.  Perhaps AFTER UPDATE OF
column_that_only_this_child_and_its_children_have would fire for
direct and subtable updates but not via-the-supertable updates.  That
is currently completely irrelevant due to our set of supported
features: different firing rules, and prohibition on children with
transition tables.  Some related topics might return soon when people
get more experience with partitions and start wanting to declare row
triggers on partitioned tables (perhaps including foreign key checks)
or implement Kevin's clever batch-mode foreign key check concept.

[1] https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/sqlref/src/tpc/db2z_sql_createtrigger.html

-- 
Thomas Munro
http://www.enterprisedb.com


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs