Re: Using LISTEN/NOTIFY with ecpg

On Mon, 2003-02-24 at 08:25, Christoph Haller wrote:
> >
> > Does anyone have any examples of using LISTEN/NOTIFY with ecpg? Is
> that
> > a supported functionality? If not, is there a way to work around it?
> >
> No, I have no example and I don't know if it's supported at all.
> But the libpq interface offers Asynchronous Notification
> via the LISTEN and NOTIFY commands. See there.
> And it's no problem at all to use libpq functions from within embedded
> SQL
> programs. You have to link with -lpq anyway, so I'm pretty positive just
> using
> PQnotifies should work. Furthermore, there is an example program at
> Example 1-2. libpq Example Program 2
> Test of the asynchronous notification interface
>

Thanks, it looks like that's what I'm going to have to do. However,
running "EXEC SQL NOTIFY listener" doesn't appear to work. If I run the
above from one connection, and on another connection run
PQnotifies(conn);, the notification is not received. Using PQexec(conn,
"NOTIFY listener") *does* work. That means for every
update/insert/delete accomplished by ECPG, I also need a PGconn object
with which to send a NOTIFY, and to LISTEN for NOTIFYs after executing
queries. That's a somewhat undesirable situation, of course.

I don't know what ECPG is doing with the EXEC SQL NOTIFY listener;. It
doesn't appear to be setting it on the connection object ECPG uses.
EXEC SQL LISTEN listener; *does* work, but the results are only piped to
ECPGlog and are not available for application consumption.

So in one application, I can do:
EXEC SQL LISTEN listener;

And in another instance I can do:
result = PQexec(conn, "NOTIFY listener");

and ECPGlog outputs something like:
[25423]: ECPGexecute line 90: ASYNC NOTIFY of 'gnucash' from
backend pid '26191' received

If I do either:
EXEC SQL LISTEN listener;
or
result = PQexec(conn, "LISTEN listener");
<some other stuff>
note = PQnotifies(conn);

and then do in another instance:
EXEC SQL NOTIFY listener;

the first instance never receives the notification. ECPG doesn't log
the ASYNC notification, and PQnotifies() returns NULL.

The fact of LISTEN not being consumable by an ECPG application sounds
like an RFE, to me, since in some cases the notification is already
going to ECPGlog().

The fact that NOTIFY apparently accomplishes nothing in ECPG seems to me
like a bug. If I EXEC SQL NOTIFY listener; on a db, then a listening
libpq application ought to be able to receive that NOTIFY event.

So, there we are. I don't know exactly what to do about the latter
situation. I don't really have time to track it down at this particular
time--I need to get some work done on my own application. If someone
provides a patch, though, I'd be happy to test it...

--
Matthew Vanecek
perl -e 'print $i=pack(c5,(41*2),sqrt(7056),(unpack(c,H)-2),oct(115),10);'
********************************************************************************
For 93 million miles, there is nothing between the sun and my shadow except me.
I'm always getting in the way of something...