On Wed, Jun 7, 2017 at 3:00 PM, Peter Geoghegan <p...@bowt.ie> wrote: > My assumption would be that since you have as many as two > statement-level triggers firing that could reference transition tables > when ON CONFLICT DO UPDATE is used (one AFTER UPDATE statement level > trigger, and another AFTER INSERT statement level trigger), there'd be > separation at that level. You'd see updated tuples with one, and > inserted within the other. While INSERT ON CONFLICT DO UPDATE is > essentially one statement, it behaves as two statements in certain > limited ways.
BTW, as you probably already know INSERT ON CONFLICT DO UPDATE is unable to affect the same row version a second time -- if we go on to update a row that the same upsert statement itself actually originally inserted, then a cardinality violation error is raised (recall that upsert does not actually use MVCC semantics to detect conflicting tuples). This differs from the UPDATE situation (I should really say the UPDATE ... FROM situation), where the implementation has always silently not updated the row a second time (to avoid the halloween problem). This cardinality violation restriction is a useful one for us to have imposed, because there is now no question about the same row appearing more than once. We know that a given row (any tuple from a single update chain) cannot appear once for an INSERT statement level trigger firing (appearing in the transition table there), while also making a second appearance for the UPDATE statement level trigger. -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers