Re: Event-driven programming?

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 9/12/07, Jay Dickon Glanville <dickon(dot)glanville(at)gmail(dot)com> wrote:
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>