#!/usr/local/bin/perl -w

# example script to use DBD::Pg to listen for async notifications

# by andrew at supernews.net, but don't bother emailing me about it;
# try #PostgreSQL on irc.freenode.net but read all the relevent
# manpages first.

use DBI;

# name of database and notification to listen for

my $DBNAME = "test";
my $NOTIFY = "MyNotify";

# maximum time to wait for a notification before re-checking for
# work to do

my $TIMEOUT = 30;

# connect to the database (assumes no username/password needed)
# see DBI and DBD::Pg docs for other connect options

# Earlier versions of DBD::Pg need AutoCommit to be on for this,
# because they keep a transaction open at all times. More recent
# versions (1.32 on) might work with autocommit off.

my $dbh = DBI->connect("dbi:Pg:dbname=$DBNAME", "", "",
		       { RaiseError => 1, AutoCommit => 1 });

# issue the LISTEN command. Note that the parameter to LISTEN is an
# SQL identifier _not_ a string; without double-quotes it gets
# case-folded. The listener and the notifier have to agree on the
# event name.

$dbh->do(qq{listen "$NOTIFY";});

# this example exits only on errors.

while (1)
{
    # As a general rule you should keep track of the actual work that
    # your listening daemon will do in tables, since notifications can
    # be lost if the listening daemon isn't running

    # do your application-specific work here, making sure you don't
    # leave any open transactions when done (notifications are not
    # delivered while a transaction is in progress)

    # Having done any available work, look for any pending
    # notifications, and sleep if there are none

    my $notifies = $dbh->func('pg_notifies');
    if (!$notifies)
    {
	# No notifications received. So sleep waiting for data on the
	# backend connection.
	my $fd = $dbh->func('getfd');
	my $rfds = '';
	vec($rfds,$fd,1) = 1;
	my $n = select($rfds, undef, undef, $TIMEOUT);

	# we don't really care whether the select saw data or timed out;
	# just check for notifications again

	$notifies = $dbh->func('pg_notifies');
    }

    # If we got a notification, then display it for the purposes of this
    # example. Since you should normally track work for the listener to
    # do in tables rather than relying on notifications, it usually makes
    # sense to clear _all_ pending notifications at this point (since the
    # next thing we will do is loop back to the application-specific work)

    while ($notifies)
    {
	# the result from pg_notifies is a ref to a two-element array,
	# with the notification name and the sender's backend PID.
	my ($n,$p) = @$notifies;
	print STDERR "Notification: $n $p\n";

	# read more notifications, until there are none outstanding
	$notifies = $dbh->func('pg_notifies');
    }
}

__END__