Thread: External (asynchronous) notifications of database updates

External (asynchronous) notifications of database updates

From
Milan Krcmar
Date:
Hi List,

I have a(n external) system driven by data in a Postgres database. I am
looking for a functionality, which would asynchronously inform the
system of any updates into the database, so the system could reflect the
updates (without having to poll the database at regular basis).

I have been skimming through various Postres' replication tools,
becasuce replication needs the same functionality in general. All the
tools I've found are based on, yes, triggers. But I don't know how to
cope with transactions - strictly speaking with rollbacks:

As a row-level 'after insert/update/delete' trigger can create
an external notification of the change, it will not see the reverse
change if the current transaction is later rolled back.

I don't know how the various replication tools solve this - do you?

Thanks in advance for any piece of information, now I choose a terrible
hack - when I get a notification, I wait for 10 seconds and then read
the whole table... It works for me now, but might not work tomorrow.

Milan

Re: External (asynchronous) notifications of database updates

From
Michael Fuhr
Date:
On Mon, Jun 20, 2005 at 04:33:43AM +0200, Milan Krcmar wrote:
>
> I have a(n external) system driven by data in a Postgres database. I am
> looking for a functionality, which would asynchronously inform the
> system of any updates into the database, so the system could reflect the
> updates (without having to poll the database at regular basis).
>
> I have been skimming through various Postres' replication tools,
> becasuce replication needs the same functionality in general. All the
> tools I've found are based on, yes, triggers. But I don't know how to
> cope with transactions - strictly speaking with rollbacks:

Have you considered using LISTEN/NOTIFY?  The listener should receive
notifications only if the notifying transaction commits.

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

> As a row-level 'after insert/update/delete' trigger can create
> an external notification of the change, it will not see the reverse
> change if the current transaction is later rolled back.

Right -- that's a problem with using triggers to perform external
events.

> I don't know how the various replication tools solve this - do you?

Not sure; maybe some of the people who work on such tools can
comment.

> Thanks in advance for any piece of information, now I choose a terrible
> hack - when I get a notification, I wait for 10 seconds and then read
> the whole table... It works for me now, but might not work tomorrow.

What do you mean by "notification"?  A trigger-based notification?
Or are you already using LISTEN/NOTIFY?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: External (asynchronous) notifications of database updates

From
Milan Krcmar
Date:
Hello Mike,

thanks a lot for your posting!

> Have you considered using LISTEN/NOTIFY?  The listener should receive
> notifications only if the notifying transaction commits.

Yes. I've seen them in the documentation _but_ missed the information
about the notification being delivered after the notifier successfuly
commits. Great! This is a (very basic) approach to my problem, although
it requires me to create a mechanism exploring what row was exactly
changed and how. I might readopt the triggers to ask them for some help,
but with caution as race-conditions could easily appear. Thank you for
the clue!

> > I don't know how the various replication tools solve this - do you?
>
> Not sure; maybe some of the people who work on such tools can
> comment.

Still an unresolved issue. Those replication tools must have existed
before the LISTEN/NOTIFY mechanism has been introduced to PG. Have they?

> What do you mean by "notification"?  A trigger-based notification?  Or
> are you already using LISTEN/NOTIFY?

I meant a trigger-based notification, let's forget this for now.

-

Milan Krcmar

Re: External (asynchronous) notifications of database updates

From
Michael Fuhr
Date:
On Mon, Jun 20, 2005 at 08:35:46PM +0200, Milan Krcmar wrote:
> > > I don't know how the various replication tools solve this - do you?
> >
> > Not sure; maybe some of the people who work on such tools can
> > comment.
>
> Still an unresolved issue. Those replication tools must have existed
> before the LISTEN/NOTIFY mechanism has been introduced to PG. Have they?

I don't know the history of the replication tools, but the Release
Notes for Postgres95 0.03 from ten years ago mention LISTEN/NOTIFY:

http://www.postgresql.org/docs/8.0/static/release-0-03.html

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/