Re: Review: pre-commit triggers

On 11/19/2013 10:58 AM, Robert Haas wrote:
> On Mon, Nov 18, 2013 at 9:39 AM, Ian Lawrence Barwick <barwick(at)gmail(dot)com> wrote:
>> postgres=# BEGIN ;
>> BEGIN
>> postgres=*# INSERT INTO foo (id) VALUES (1);
>> INSERT 0 1
>> postgres=*# COMMIT ;
>> NOTICE: Pre-commit trigger called
>> ERROR: relation "bar" does not exist
>> LINE 1: SELECT foo FROM bar
>> ^
>> QUERY: SELECT foo FROM bar
>> CONTEXT: PL/pgSQL function pre_commit_trigger() line 4 at EXECUTE statement
>> postgres=#
>>
>> I'd expect this to lead to a failed transaction block,
>> or at least some sort of notice that the transaction itself
>> has been rolled back.
> Ending up in a failed transaction block would be wrong. If the user
> does a BEGIN, a bunch of stuff, and a COMMIT, they're entitled to
> assume without checking that they are no longer in a transaction
> block. The COMMIT may have actually performed a ROLLBACK, but one way
> or the other the transaction block will have ended. This is important
> for things like psql <
> my-dumb-script-with-several-begin-commit-blocks.
>
> It is a little less clear whether it's best for the COMMIT to return
> an ERROR message or something else, but I think the ERROR is probably
> the best solution. There is already commit-time code that can fail
> today, so there should be precedent here. And I suspect anything
> other than ERROR will be really messy to implement.
>

OK, you've convinced me.

cheers

andrew