Thread: Need "InBetween" (not just Before and After) Trigger

Knowledgeable Postgres hackers,

I want a trigger to happen after constraint checking but before row insertion.

insert_row
{
    db_check_constraints();
    if (constraints satisfied) {
        attempt_action()
        if (action_successful) {
            return allow_insertion
        }
    }
    return disallow_insertion
}

Doing the constraint checking in C is error prone and difficult to maintain.
That is Postgres' job anyway.

If I use a "before" trigger, the constraints are checked after I've already
performed my action.  Thus I have performed my action yet no row is inserted
(which is inconsistent).

If I use an "after" trigger, the constraints are checked first.  If the row is
inserted, I attempt to perform my action.  If my action fails, then I have an
inserted row but no action, which is also inconsistent.

The only solution I've developed involves a duplicate table and a transaction.

BEGIN
    insert into duplicate table with identical constraints
    insert into real table with trigger
COMMIT

By inserting to the duplicate table without a trigger, I get constraint
checking performed by the db.  Then I insert into the real table with the
"before" trigger.  If my action fails, everything fails.  Obviously I prefer a
solution not involving duplicate tables or such hacks.

Thank you,

    Matt

On Sat, Jan 13, 2001 at 01:23:27PM -0800,
  Matt Doucleff <matt@digitalfountain.com> wrote:
>
> If I use a "before" trigger, the constraints are checked after I've already
> performed my action.  Thus I have performed my action yet no row is inserted
> (which is inconsistent).
>
> If I use an "after" trigger, the constraints are checked first.  If the row is
> inserted, I attempt to perform my action.  If my action fails, then I have an
> inserted row but no action, which is also inconsistent.
>
> The only solution I've developed involves a duplicate table and a transaction.

I think what you want to do is defer constraint checking until the end of
a transaction. You can make a constraint deferable and make the default for
it to be checked at the end of a transaction instead of immediately after
a change.

Matt Doucleff <matt@digitalfountain.com> writes:
> I want a trigger to happen after constraint checking but before row
> insertion.

In other words, you'd like your trigger to be able to violate the
constraints?

This does *not* strike me as a good idea.

> If I use a "before" trigger, the constraints are checked after I've
> already performed my action.  Thus I have performed my action yet no
> row is inserted (which is inconsistent).

> If I use an "after" trigger, the constraints are checked first.  If
> the row is inserted, I attempt to perform my action.  If my action
> fails, then I have an inserted row but no action, which is also
> inconsistent.

Seems to me this is all irrelevant, because in either case, the first
action will be rolled back if the second one fails.  There might be
some marginal amount of time wasted, which you could minimize by
arranging things so that the more-likely-to-fail action is done first.
But I see no correctness issue.

            regards, tom lane

Tom Lane wrote:
>
> Matt Doucleff <matt@digitalfountain.com> writes:
> > I want a trigger to happen after constraint checking but before row
> > insertion.
>
> In other words, you'd like your trigger to be able to violate the
> constraints?
>
> This does *not* strike me as a good idea.
>
> > If I use a "before" trigger, the constraints are checked after I've
> > already performed my action.  Thus I have performed my action yet no
> > row is inserted (which is inconsistent).
>
> > If I use an "after" trigger, the constraints are checked first.  If
> > the row is inserted, I attempt to perform my action.  If my action
> > fails, then I have an inserted row but no action, which is also
> > inconsistent.
>
> Seems to me this is all irrelevant, because in either case, the first
> action will be rolled back if the second one fails.  There might be
> some marginal amount of time wasted, which you could minimize by
> arranging things so that the more-likely-to-fail action is done first.
> But I see no correctness issue.
>
>                         regards, tom lane

Tom,

My trigger is guaranteed to never change the row's data.  It is a "const trigger."  I use it solely for side-effects.
Thusmy goal is not to violate constraints.  My goal is to invoke the side-effect only if the data satisfies the
constraints. However, if my side-effect action fails, I still want the ability to abort the transaction.  However, I
seewhy an "InBetween" trigger is dangerous in general. 

For example, say I want to create a file when a row is inserted, and modify that file when the row is modified.  If a
transactionfails, I need to undo my changes to the filesystem.  I am not interested in modifying row data from a
trigger,but don't want to create the file if the new row's data violates constraints. 

You are right regarding the inevitability of rollback due to subsequent operations failing.  And I understand why the
trigger'stiming is just an optimization.  Thus, my question becomes, how can I get a "rollback trigger" and undo the
side-effectwhen subsequent operations fail?  I.e., if two rows are inserted in a transaction and I create the first
filebut the second row violates constraints, I need to be able to remove the new file during rollback. 

Thank you for you help,

    Matt

Matt Doucleff <matt@digitalfountain.com> writes:
> For example, say I want to create a file when a row is inserted, and
> modify that file when the row is modified.  If a transaction fails, I
> need to undo my changes to the filesystem.

Unless you want to implement your own undo log, I'd suggest rethinking
this.  What you really want is a trigger fired at commit time, ie a
deferred trigger, so that you don't have to worry about rolling back
your outside actions.  I believe we have that facility for foreign key
triggers, not sure if it's available for general use yet ...

            regards, tom lane

On Mon, 15 Jan 2001, Tom Lane wrote:

> Matt Doucleff <matt@digitalfountain.com> writes:
> > For example, say I want to create a file when a row is inserted, and
> > modify that file when the row is modified.  If a transaction fails, I
> > need to undo my changes to the filesystem.
>
> Unless you want to implement your own undo log, I'd suggest rethinking
> this.  What you really want is a trigger fired at commit time, ie a
> deferred trigger, so that you don't have to worry about rolling back
> your outside actions.  I believe we have that facility for foreign key
> triggers, not sure if it's available for general use yet ...

Sort of, but it still isn't sufficient.  There's no way to say for certain
that a later deferred trigger won't send an exception that will roll back
the transaction.  What he'd really need would be something that occurs
after the deferred trigger queue and after the transaction has committed
that cannot cause the transaction to rollback (I'm not sure what an
error condition raised in such a state would do...)

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> Sort of, but it still isn't sufficient.  There's no way to say for certain
> that a later deferred trigger won't send an exception that will roll back
> the transaction.  What he'd really need would be something that occurs
> after the deferred trigger queue and after the transaction has committed
> that cannot cause the transaction to rollback (I'm not sure what an
> error condition raised in such a state would do...)

Good point.  One possibility is to do the external action outside the
database, perhaps in a daemon process that's listening for NOTIFYs sent
by the trigger.  (NOTIFY events are delivered only if the transaction
commits.)  You'd need some additional tables to record the actions the
daemon is supposed to take, since NOTIFY alone can't carry much of an
information payload.

            regards, tom lane