I guess this got lost over the weekend and subsequent week (I was on
vacation).
On Fri, Sep 15, 2017 at 04:03:35PM -0500, Nico Williams wrote:
> On Fri, Sep 15, 2017 at 04:07:33PM -0400, Tom Lane wrote:
> > Nico Williams <nico@cryptonector.com> writes:
> > > On Fri, Sep 15, 2017 at 02:19:29PM -0500, Nico Williams wrote:
> > >> On Fri, Sep 15, 2017 at 11:26:08AM -0700, Andres Freund wrote:
> > >>> I think you should also explain why that's a desirable set of
> > >>> semantics.
> >
> > > Now, why is this desirable: atomicity.
> >
> > > The client/user can no longer add statements to the transaction,
> > > therefore now (commit-time) is a good time to run a procedure that
> > > can *atomically* examine the totatility of the changes made by the
> > > user.
> >
> > I do not really understand this claim. The argument for a commit trigger
> > seems to be exactly "I want to be the last thing that happens in the
> > transaction". But as soon as you have two of them, one of them is not
>
> The user defining the triggers is a very different thing from the user
> sending the various statements up to and including the COMMIT. They
> need not be the same.
>
> The DBA/developers/ops own the triggers. The client user may not have
> any trigger creation privilege.
>
> Being in control of the [pre-]commit triggers, I can control the order
> in which they run (by-name, lexical order).
>
> The important thing is that all of the [pre-]commit triggers will run
> after the last statement in the TX send by the client.
>
> Surely there's nothing strange abolut this -- it's already the case for
> [DEFERRED] CONSTRAINT TRIGGERs!
Not to belabor the point, though I'm being redundant: the important
thing is that we have something we can run after the last statement from
the _client_. Trigger order is already what it is, and that's fine
because the dev/dba controls that, but does not control what the
_client_ sends.
I'm using PostgreSQL for an all-SQL application with:
- [WISH] access via the PostgreSQL protocol (unconstrained)
- [CURRENT] access via HTTP via PostgREST (fairly constrained)
This means we don't have full control over the client. Even if we did,
the client authenticates as users, and we can't ensure that the users
aren't connecting separately and sending arbitrary commands.
Incidentally, there's also a need for a connect-time or BEGIN-time
trigger to simplify other trigger functions. I often write trigger
functions that do CREATE TEMP TABLE IF NOT EXISTS -- a handy technique.
But it'd be nice if we could have temp tables (and indexes, and views,
and...) automatically created for each session or transaction. If
nothing else, it would allow statement/row triggers to do less work,
thus run marginally faster.
Nico
--
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers