Re: [OT] Dilemma about OS <-> Postgres interaction - Mailing list pgsql-general

Harald Fuchs wrote:
> In article <20040617232536.GB17879@campbell-lange.net>,
> Rory Campbell-Lange <rory@campbell-lange.net> writes:
>
>
>>I should have mentioned that we need the messages sent very soon after
>>they have landed in the 'inbox'; otherwise cron would definitely be the
>>way to go -- including an @reboot line.
>
>
> This rules out a cronjob.  How about writing a pl/perlu function which
> sends the mail and calling that function by an AFTER INSERT trigger?
>

Use of triggers has one drawback. What happens if the insert is rolled
back when the transaction attempts to commit?

I've mentioned this before. It would be *very* beneficial if it was
possible for a Pl/<lang> module to subscribe to transactional events.
That in turn would enable a solution like this to defer the actual
sending until the transaction is succesfully commited and to remove any
stray messages should it be rolled back.

Awaiting a more full-blown 2 phase commit solution I would settle for
the ability to subscribe to four events (actually, this would work
pretty good with 2pc as well).

1. "Commit issued". This event is sent before any of the normal commit
code is executed. In essence, it's similar to any other normal SQL
command executed within the transaction itself. An event handler is
allowed to make changes to the database. A change will schedule a new
"Commit issued" event to be sent once all event handlers have been
called. This process repeats until no event handler makes a change. The
mechanism will need a loop counter and a configurable threshold.

A commit can be prevented during "Commit issued" using normal error
handling.

2. "Commit start". From now on, no changes are permitted. The users view
of the data will not change. This is where deferred constraints,
transaction wide rule checking etc. can be executed. A handler is
allowed to terminate the commit using normal error handling (a normal
return from the handler is equivalent to a "vote commit" in a 2pc
environment).

3. "Commit complete". The commit is at it's final stage. From the
database perspective, the commit is complete and cannot be rolled back.
A handler can use this to attempt to send prepared emails etc. A failure
will not have any effect on stored data. Normally, the handler would
perform a minimum of processing here (since it cannot fail) and do most
of the work at "Commit start".

4. "Rollback complete". This notification tells the handler that
everything has been rolled back.

AFAICS, only two things are required from the PostgreSQL backend. A
simple event sending mechanism to be triggered before and after the
(complete) process of committing or rolling back, and the ability to
track attempts to make a change to the database. The latter of course to
log the fact that a change has occured  (for "Commit issued") or to
prevent it from happening (all other handlers). I'm sure this
functionality is there already. I'd be happy to write a patch if someone
could point out where I find it.

Kind regards,

Thomas Hallgren