LISTEN considered dangerous

On Tue, Aug 01, 2006 at 07:16:39PM +0200, Flemming Frandsen wrote:

This way we could even have wildcard listens, imagine doing a listen %
and getting all the generated events:)

That'd be awesome. Along with a data field in the listen
structure, please :-)

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

On 8/1/06, Flemming Frandsen <ff@partyticket.net> wrote:

I have an application that does aggresive caching of data pulled from
the database, it even keeps the objects cached between transactions.

Normally this works very well and when the cache is warmed up about 90%
of the database time is saved.

However that leaves the problem of how to notice that my cached objects
have turned stale, luckily pg has the listen/notify feature so I have
triggers on all tables that do a notify, as you do.

However that just doesn't work, because listen is broken, allow me to
illustrate, here A and B are two clients:

A: BEGIN
A: SELECT * FROM foo and cache the result.
A: LISTEN foochange
B: BEGIN
B: update foo
B: NOTIFY foochange
B: COMMIT
A: COMMIT

From the docs:.

NOTIFY interacts with SQL transactions in some important ways.
Firstly, if a NOTIFY is executed inside a transaction, the notify
events are not delivered until and unless the transaction is
committed. This is appropriate, since if the transaction is aborted,
all the commands within it have had no effect, including NOTIFY. But
it can be disconcerting if one is expecting the notification events to
be delivered immediately.

"Ian Harding" <harding.ian@gmail.com> writes:

However that just doesn't work, because listen is broken, allow me to
illustrate, here A and B are two clients:

A: BEGIN
A: SELECT * FROM foo and cache the result.
A: LISTEN foochange
B: BEGIN
B: update foo
B: NOTIFY foochange
B: COMMIT
A: COMMIT

From the docs:.

NOTIFY interacts with SQL transactions in some important ways.
Firstly, if a NOTIFY is executed inside a transaction, the notify
events are not delivered until and unless the transaction is
committed. This is appropriate, since if the transaction is aborted,
all the commands within it have had no effect, including NOTIFY. But
it can be disconcerting if one is expecting the notification events to
be delivered immediately.

Note that he's not complaining about when the NOTIFY takes effect. He's
complaining about when the LISTEN takes effect.

I haven't used LISTEN/NOTIFY myself yet and I do indeed find the behaviour he
shows somewhat surprising. Normally in read-committed mode uncommitted
transactions are affected by other transactions when they commit. In this case
the uncommitted LISTEN is not being affected by the committed NOTIFY.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

On Tue, Aug 01, 2006 at 07:50:19PM -0400, Gregory Stark wrote:

However that just doesn't work, because listen is broken, allow me to
illustrate, here A and B are two clients:

A: BEGIN
A: SELECT * FROM foo and cache the result.
A: LISTEN foochange
B: BEGIN
B: update foo
B: NOTIFY foochange
B: COMMIT
A: COMMIT

I haven't used LISTEN/NOTIFY myself yet and I do indeed find the behaviour he
shows somewhat surprising. Normally in read-committed mode uncommitted
transactions are affected by other transactions when they commit. In this case
the uncommitted LISTEN is not being affected by the committed NOTIFY.

Eh? At the point the LISTEN is run, the NOTIFY hasn't committed, so a
row is inserted. At the time the NOTIFY is committed, the LISTEN hasn't
committed yet so won't be visible. Only LISTEN is stored, not NOTIFY so
there's nothing wrong with the read-committed semantics.

It's slightly surprising though. I havn't seen anyone else complain
about this before though. The only way to fix this is to make the
LISTEN completely atransactional, so NOTIFY can see uncomitted LISTENs
also.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Ian Harding wrote:

NOTIFY interacts with SQL transactions in some important ways.
Firstly, if a NOTIFY is executed inside a transaction, the notify
events are not delivered until and unless the transaction is
committed. This is appropriate, since if the transaction is aborted,
all the commands within it have had no effect, including NOTIFY. But
it can be disconcerting if one is expecting the notification events to
be delivered immediately.

Yes, that's very nice, but it doesn't have *anything* to do with what I
posted about.

I'm bothered by listen listening from the end of the transaction in
stead of the start of the transaction.

--
Regards Flemming Frandsen - http://dion.swamp.dk - YAPH

Martijn van Oosterhout <kleptog@svana.org> writes:

It's slightly surprising though. I havn't seen anyone else complain
about this before though. The only way to fix this is to make the
LISTEN completely atransactional, so NOTIFY can see uncomitted LISTENs
also.

There isn't anything very desirable about the table-based approach to
NOTIFY :-( I've previously proposed switching to an implementation
similar to sinval messaging, which would completely eliminate the need
for notifiers to be aware of who is listening. However, I'm unconvinced
that the OP's complaint is valid. I would still expect any
reimplementation of notify messaging to honor the principle that a
LISTEN doesn't take effect till you commit. Otherwise, what of

BEGIN;
LISTEN foo;
ROLLBACK;

? If I get some events for foo after this I'd surely think it was
broken.

regards, tom lane

Martijn van Oosterhout wrote:

A: BEGIN
A: SELECT * FROM foo and cache the result.
A: LISTEN foochange

B: BEGIN
B: update foo
B: NOTIFY foochange
B: COMMIT

A: COMMIT

Eh? At the point the LISTEN is run, the NOTIFY hasn't committed, so a
row is inserted. At the time the NOTIFY is committed, the LISTEN hasn't
committed yet so won't be visible. Only LISTEN is stored, not NOTIFY so
there's nothing wrong with the read-committed semantics.

No, that's all well and good it behaves as you'd expect when you know
how it's implemented, that doesn't make it more usable though.

It's slightly surprising though. I havn't seen anyone else complain
about this before though.

I'm likely to do that:)

The only way to fix this is to make the
LISTEN completely atransactional, so NOTIFY can see uncomitted LISTENs
also.

Not at all.

The listen should simply listen for events issued at the start of the
transaction it's executed in.

To do that without timetravel we'd need to store all events from all
transactions, but that's not too bad if it's implemented right.

--
Regards Flemming Frandsen - http://dion.swamp.dk - YAPH

Tom Lane wrote:

However, I'm unconvinced that the OP's complaint is valid.

I'm unconvinced that I've stated the problem clearly enough.

I would still expect any
reimplementation of notify messaging to honor the principle that a
LISTEN doesn't take effect till you commit.

Naturally, the listen should not do anything at all when followed by a
rollback.

However if you start listening in a transaction then you should get all
events that have happened after the snapshot that the transaction
represents (the start of the transaction).

That means that events that happen after the start of the transaction
have to be returned in the next transaction.

--
Regards Flemming Frandsen - http://dion.swamp.dk - YAPH

In article <44D0478D.3060201@partyticket.net>,
Flemming Frandsen <ff@partyticket.net> writes:

I would still expect any
reimplementation of notify messaging to honor the principle that a
LISTEN doesn't take effect till you commit.

Naturally, the listen should not do anything at all when followed by a
rollback.

However if you start listening in a transaction then you should get
all events that have happened after the snapshot that the transaction
represents (the start of the transaction).

Here you're contradicting yourself. In your second paragraph you
state that LISTEN should get events unless later cancelled by a
ROLLBACK. How should LISTEN know if its transaction will commit or
abort?

Flemming Frandsen <ff@partyticket.net> writes:

The listen should simply listen for events issued at the start of the
transaction it's executed in.

BEGIN;
SELECT sleep(1000000000);
LISTEN foo;

No, I don't think so.

regards, tom lane

Tom Lane wrote:

Flemming Frandsen <ff@partyticket.net> writes:

The listen should simply listen for events issued at the start of the
transaction it's executed in.

BEGIN;
SELECT sleep(1000000000);
LISTEN foo;

No, I don't think so.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

I was just about to say that if someone was to do the following they
would get the previously commited state of foo after the sleep not how
foo looked before the sleep otherwise every begin would need an entire
DB snapshot to be taken? Seems like it should be no different. I have
been following this thread as I use LISTEN but not in as intensive way
as the op and for me its working fine...

BEGIN
select sleep(100000);
select * from foo;

On 8/2/06, Flemming Frandsen <ff@partyticket.net> wrote:

Ian Harding wrote:

NOTIFY interacts with SQL transactions in some important ways.
Firstly, if a NOTIFY is executed inside a transaction, the notify
events are not delivered until and unless the transaction is
committed. This is appropriate, since if the transaction is aborted,
all the commands within it have had no effect, including NOTIFY. But
it can be disconcerting if one is expecting the notification events to
be delivered immediately.

Yes, that's very nice, but it doesn't have *anything* to do with what I
posted about.

Quite true, but it does indicate, to me at least, the fact that this
is a SQL command and doesn't take effect until committed.

From what I read in the docs, I would expect the NOTIFY signals to be
like phone calls, if your phone's not plugged in (LISTEN not
committed) you miss the call. That's the way it works apparently.

I'm bothered by listen listening from the end of the transaction in
stead of the start of the transaction.

What seems to be needed is an answering service that will record your
NOTIFY events, in case you decide to plug in the phone and retrieve
them.

- Ian

Tom Lane <tgl@sss.pgh.pa.us> writes:

I would still expect any reimplementation of notify messaging to honor the
principle that a LISTEN doesn't take effect till you commit. Otherwise, what
of

BEGIN;
LISTEN foo;
ROLLBACK;

? If I get some events for foo after this I'd surely think it was
broken.

Well the rollback would roll back any consequences of the notify handler
firing too. So the transactional nature would still be maintained.

I'm not sure why you're imagining that the listen would still be in effect
after the rollback. rollback would return the connection state to the same
state it was in before the transaction.

I Think of GUC variables like enable_* as a good analogy. If you fiddle with
them their effects are felt immediately by your transaction. You don't have to
commit to make those changes take effect. If you roll back your changes their
original state is restored.

--
greg

On Wed, Aug 02, 2006 at 09:15:46AM -0400, Oisin Glynn wrote:

I was just about to say that if someone was to do the following they
would get the previously commited state of foo after the sleep not how
foo looked before the sleep otherwise every begin would need an entire
DB snapshot to be taken? Seems like it should be no different. I have
been following this thread as I use LISTEN but not in as intensive way
as the op and for me its working fine...

BEGIN
select sleep(100000);
select * from foo;

Well, it depends on the transaction mode, read-committed and
serialisable behave differently here. In the case of serializable, you
will get what was before the sleep(), that's kind of the point.

The whole point of MVCC is that taking a snapshot doesn't actually cost
anything.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

On Aug 2, 2006, at 2:07 AM, Tom Lane wrote:

that the OP's complaint is valid. I would still expect any
reimplementation of notify messaging to honor the principle that a
LISTEN doesn't take effect till you commit. Otherwise, what of

Well, it would break our usage of LISTEN/NOTIFY if they did not honor
transactions, so back-compatibility is in my book the most important
reason not to change that behavior.

# ff@partyticket.net / 2006-08-02 07:57:55 +0200:

I'm bothered by listen listening from the end of the transaction in
stead of the start of the transaction.

Sorry if this isn't what you're after, instead just a question:

Why don't you issue the LISTEN in a separate transaction before
going on?

LISTEN foo;
BEGIN;
SELECT ...

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991

On 2 Aug 2006, Harald Fuchs wrote:

all events that have happened after the snapshot that the transaction
represents (the start of the transaction).

Here you're contradicting yourself. In your second paragraph you
state that LISTEN should get events unless later cancelled by a
ROLLBACK. How should LISTEN know if its transaction will commit or
abort?

No, I'm not contradicting myself.

What I'm saying is that if you listen for an event then that event should
be delivered in the next transaction, if it happens after the start of the
transaction that you listen in.

The reason for this is that while you are in a transaction you see the
world as it existed at the time the transaction was started.

So if you decide that you want to be told about something then that wish
has to be in effect from the time of the start of the transaction, because
otherwise the state of the delivered events is not consistent with the
state of the rest of the data.

On Wed, 2 Aug 2006, Tom Lane wrote:

Flemming Frandsen <ff@partyticket.net> writes:

The listen should simply listen for events issued at the start of the
transaction it's executed in.

BEGIN;
SELECT sleep(1000000000);
LISTEN foo;

No, I don't think so.

And why would that be a problem?

There is no reason to assume that there would be any overhead in storing a
list of outstanding events for your connection compared to today.

What would happen in your example is that all the other connections leave
this slow transaction behind, but in stead of storing all the events for
all the transactions you can simply merge them all into one lump of events
that are waiting to be delivered to that slow client.

On Wed, 2 Aug 2006, Roman Neuhauser wrote:

I'm bothered by listen listening from the end of the transaction in
stead of the start of the transaction.

Sorry if this isn't what you're after, instead just a question:

Why don't you issue the LISTEN in a separate transaction before
going on?

LISTEN foo;
BEGIN;
SELECT ...

Well that's exactly what I do, but in stead of doing LISTEN foo I do
155x LISTEN foo before committing.

It's not too bad as it only has to happen once pr. connection, but it
creates a huge amount of churn in the pg_listeners table and there is an
extra delay, simply because of the 155 roundtrips and the extra commit.

I think I might put the listen stuff in a stored procedure to cut down on
the roundtrips, but it's still not ideal.

Many of my connections don't need the full 155 listens, so what I'd really
like to be able to do is:

select * from foo;
listen foochanges;

and actually get events if foo is updated compared to the state that
the select read.