Thread: Event-driven programming?

Hello all.

Is it possible for PostgreSQL to notify me of a successful transaction commit?

Here's an example of what I'm thinking of:
- I write a function (it doesn't matter what language it's in:
PL/pgSQL, PL/Java, etc)
- I register that function as a "post-commit" callback function
- when a client commits a transaction, the function gets called, and
the database passes the function some general information as to the
content of the transaction

Note how similar this process is to writing triggers.  The only
problem I have with triggers is that events get generated per-table.
I'd like to get notifications based on transactions, not table
changes.

What I'd like to be able to do with this event is to notify any
applications of this change, so they can update their cached view of
the database.

So, is this possible?  Or am I wishing for the sky? ;-)

Thanks for any help you can provide.

JDG

--
Jay Dickon Glanville

On Wed, Sep 12, 2007 at 12:36:22PM -0400, Jay Dickon Glanville wrote:
> Hello all.
>
> Is it possible for PostgreSQL to notify me of a successful transaction commit?

There is the LISTEN/NOTIFY mechanism. It's not automatic but I think it
does most of what oyu want.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

--- Jay Dickon Glanville <dickon.glanville@gmail.com> wrote:

> Is it possible for PostgreSQL to notify me of a successful transaction commit?

I've haven't used it yet, but will LISTEN and NOTIFY work for you?

http://www.postgresql.org/docs/8.2/static/sql-listen.html
http://www.postgresql.org/docs/8.2/static/sql-notify.html

Regards,
Richard Broersma Jr.

2007/9/12, Jay Dickon Glanville <dickon.glanville@gmail.com>:
> Hello all.
>
> Is it possible for PostgreSQL to notify me of a successful transaction commit?
>
> Here's an example of what I'm thinking of:
> - I write a function (it doesn't matter what language it's in:
> PL/pgSQL, PL/Java, etc)
> - I register that function as a "post-commit" callback function
> - when a client commits a transaction, the function gets called, and
> the database passes the function some general information as to the
> content of the transaction
>
> Note how similar this process is to writing triggers.  The only
> problem I have with triggers is that events get generated per-table.
> I'd like to get notifications based on transactions, not table
> changes.
>
> What I'd like to be able to do with this event is to notify any
> applications of this change, so they can update their cached view of
> the database.
>
> So, is this possible?  Or am I wishing for the sky? ;-)
>
> Thanks for any help you can provide.
>
> JDG
>
>
On stored procedure level you can use dbms_alert functionality from
orafce  library.

http://www.pgsql.cz/index.php/Oracle_functionality_%28en%29#DBMS_ALERT

Pavel Stehule

On Sep 12, 2007, at 11:51 AM, Richard Broersma Jr wrote:

> --- Jay Dickon Glanville <dickon.glanville@gmail.com> wrote:
>
>> Is it possible for PostgreSQL to notify me of a successful
>> transaction commit?
>
> I've haven't used it yet, but will LISTEN and NOTIFY work for you?
>
> http://www.postgresql.org/docs/8.2/static/sql-listen.html
> http://www.postgresql.org/docs/8.2/static/sql-notify.html
>

I don't see how he could wire that up (or, anything) for per-
transaction notifications.  I'll also point out that the solution for
his use-case, keeping an up-to-date application level cache of data,
is normally implemented at the application layer, often using
something like memcached.  However, if he can narrow his "events"
down to specific tables, then he can use the LISTEN/NOTIFY mechanism
with triggers on those tables to good effect.

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

On 9/12/07, Martijn van Oosterhout <kleptog@svana.org> wrote:
> On Wed, Sep 12, 2007 at 12:36:22PM -0400, Jay Dickon Glanville wrote:
> > Hello all.
> >
> > Is it possible for PostgreSQL to notify me of a successful transaction commit?
>
> There is the LISTEN/NOTIFY mechanism. It's not automatic but I think it
> does most of what oyu want.

Thanks for the suggestion Martijn, but I get the impression from the
documentation that LISTEN/NOTIFY is for client to client
communication.  This type of communication isn't what I'm looking for.
 I want server to client communication, where the server is notifying
me on successful commit of transaction.

Thanks again.

JDG

On Wed, 2007-09-12 at 13:10 -0400, Jay Dickon Glanville wrote:
> On 9/12/07, Martijn van Oosterhout <kleptog@svana.org> wrote:
> > On Wed, Sep 12, 2007 at 12:36:22PM -0400, Jay Dickon Glanville wrote:
> > > Hello all.
> > >
> > > Is it possible for PostgreSQL to notify me of a successful transaction commit?
> >
> > There is the LISTEN/NOTIFY mechanism. It's not automatic but I think it
> > does most of what oyu want.
>
> Thanks for the suggestion Martijn, but I get the impression from the
> documentation that LISTEN/NOTIFY is for client to client
> communication.  This type of communication isn't what I'm looking for.
>  I want server to client communication, where the server is notifying
> me on successful commit of transaction.
>

LISTEN/NOTIFY may be closer to what you want than you think. If you have
a trigger that issues a "NOTIFY foo" for any INSERT/UPDATE/DELETE, it
will only generate one notification per transaction, and only as part of
a successful COMMIT.

Then, your application can check for this notification periodically or
perhaps before returning cached data.

LISTEN/NOTIFY are well suited for cache invalidation.

Regards,
    Jeff Davis

Jay Dickon Glanville wrote:
>
> What I'd like to be able to do with this event is to notify any
> applications of this change, so they can update their cached view of
> the database.
>
> So, is this possible?  Or am I wishing for the sky? ;-)

You're wishing for these, I think:

http://pgfoundry.org/projects/pgmemcache/
http://www.danga.com/memcached/


--
   Richard Huxton
   Archonet Ltd

Pavel Stehule wrote:
> 2007/9/12, Jay Dickon Glanville <dickon.glanville@gmail.com>:
>> - I write a function (it doesn't matter what language it's in:
>> PL/pgSQL, PL/Java, etc)
>> - I register that function as a "post-commit" callback function
>> - when a client commits a transaction, the function gets called, and
>> the database passes the function some general information as to the
>> content of the transaction
>>
>> Note how similar this process is to writing triggers.  The only
>> problem I have with triggers is that events get generated per-table.
>> I'd like to get notifications based on transactions, not table
>> changes.
>>
>> What I'd like to be able to do with this event is to notify any
>> applications of this change, so they can update their cached view of
>> the database.

Although I'm happy to use triggers as-is (not per transaction, etc) I've
also wondered about firing events from the database.  I'm curious to
know if anyone has attempted to write a trigger that will open a socket
and send an event packet to an application server on the network.

I've considered using a message queue like JMS to manage events on my
network and have PostgreSQL fire off UDP messages to a socket server
that would insert jobs into the message queue as triggers get fired in
the database.  Doing this would be an alternative to storing the queue
as a database table and having to use polling to constantly check the
database for events in the queue.

I am interested what anybody might contribute to this thread.  Let us
know what you tried whether it worked or not, it might be useful.

-- Dante

dickon.glanville@gmail.com ("Jay Dickon Glanville") writes:
> On 9/12/07, Martijn van Oosterhout <kleptog@svana.org> wrote:
>> On Wed, Sep 12, 2007 at 12:36:22PM -0400, Jay Dickon Glanville wrote:
>> > Hello all.
>> >
>> > Is it possible for PostgreSQL to notify me of a successful transaction commit?
>>
>> There is the LISTEN/NOTIFY mechanism. It's not automatic but I think it
>> does most of what oyu want.
>
> Thanks for the suggestion Martijn, but I get the impression from the
> documentation that LISTEN/NOTIFY is for client to client
> communication.  This type of communication isn't what I'm looking for.
>  I want server to client communication, where the server is notifying
> me on successful commit of transaction.

It may be closer to what you want than you think.

The server *does* notify listeners upon successful commit of a
transaction, and the timing is indeed right; the time of the
notification is COMMIT time, not before.
--
output = ("cbbrowne" "@" "cbbrowne.com")
http://cbbrowne.com/info/nonrdbms.html
And me, with this terrible pain in all the diodes down my left side...
-- Marvin the Paranoid Android

Hi

PgQ might be the answer for you.  Each transaction shouöd push event into queue and then you can write conusmers that notify each of the applications that need to react to this.

Extract from documentation:

PgQ is Postgres based event processing system. It is part of SkyTools package
that contains several useful implementations on this engine.

SkyTools is scripting framework for Postgres databases written in Python that
provides several utilities and implements common database handling logic.

Event - atomic piece of data created by Producers. In PgQ event is one record
in one of tables that services that queue. Event record contains some system fields
for PgQ and several data fileds filled by Producers. PgQ is neither checking nor
enforcing event type. Event type is someting that consumer and produser must agree on.
PgQ guarantees that each event is seen at least once but it is up to consumer to
make sure that event is processed no more than once if that is needed.

Batch - PgQ is designed for efficiency and high throughput so events are grouped
into batches for bulk processing. Creating these batches is one of main tasks of
PgQadm and there are several parameters for each queue that can be use to tune
size and frequency of batches. Consumerss receive events in these batches and depending
on business requirements process events separately or also in batches.

Queue - Event are stored in queue tables i.e queues. Several producers can write into
same queeu and several consumers can read from the queue. Events are kept in queue
until all the consumers have seen them. We use table rotation to decrease
hard disk io. Queue can contain any number of event types it is up to Producer and
Consumer to agree on what types of events are passed and how they are encoded
For example Londiste producer side can produce events for more tables tan consumer
side needs so consumer subscribes only to those tables it needs and events for
other tables are ignores.

Producer - applicatione that pushes event into queue. Prodecer can be written in any
langaage that is able to run stored procedures in Postgres.

Consumer - application that reads events from queue. Consumers can be written in any
language that can interact with Postgres. SkyTools package contains several useful
consumers written in Python that can be used as they are or as good starting points
to write more complex consumers.

regards,
Asko

On Sep 12, 3:05 pm, da...@lorenso.com ("D. Dante Lorenso") wrote:
> Pavel Stehule wrote:
> > 2007/9/12, Jay Dickon Glanville <dickon.glanvi...@gmail.com>:
> >> - I write a function (it doesn't matter what language it's in:
> >> PL/pgSQL, PL/Java, etc)
> >> - I register that function as a "post-commit" callback function
> >> - when a client commits a transaction, the function gets called, and
> >> the database passes the function some general information as to the
> >> content of the transaction
>
> >> Note how similar this process is to writing triggers.  The only
> >> problem I have with triggers is that events get generated per-table.
> >> I'd like to get notifications based on transactions, not table
> >> changes.
>
> >> What I'd like to be able to do with this event is to notify any
> >> applications of this change, so they can update their cached view of
> >> the database.
>
> Although I'm happy to use triggers as-is (not per transaction, etc) I've
> also wondered about firing events from the database.  I'm curious to
> know if anyone has attempted to write a trigger that will open a socket
> and send an event packet to an application server on the network.
>
> I've considered using a message queue like JMS to manage events on my
> network and have PostgreSQL fire off UDP messages to a socket server
> that would insert jobs into the message queue as triggers get fired in
> the database.  Doing this would be an alternative to storing the queue
> as a database table and having to use polling to constantly check the
> database for events in the queue.
>
> I am interested what anybody might contribute to this thread.  Let us
> know what you tried whether it worked or not, it might be useful.
>
> -- Dante

Depending on your reliability requirements UDP may not be a great
choice.

But, since you asked about what's been tried, my (successful so far)
production setup is along the lines of:

1. process A accepts multiple data flows, inserts "work to be done"
items into a table in batches and calls NOTIFY.
2. process B LISTENs for notifications (with a blocking read on the
socket connection to Postgres) and takes them as a signal to look for
"work items to be done". It also checks every N minutes of idle time
for "work items to be done" in case the NOTIFY/LISTEN mechanism is
broken (haven't seen that situation yet).

As for recovery, process B looks for work items on startup, then drops
into the LISTEN / blocking_read mode.