Thread: LISTEN considered dangerous
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 When A continues with an other transaction it will never get the event from B and thus will keep using the cached foo data, clearly this is not what you'd want. The workaround is to commit after the listen, but that too is broken because then you'd commit all the changes up to that point, also not a desirable situation. The only real way to work around the problem is to LISTEN to every single object that could ever be interesting to cache and commit right after connecting the first time. The reason for this is that LISTEN is implemented by inserting into a table that's under transaction control (well naturally), so the actual listening doesn't start until the transaction has been committed. I'm quite lucky I didn't get any corrupted data from this gotcha, but I did get som annoyed users, so let this be a warning to other pg users. The correct behaviour would be to start listening at the begining of the transaction, when committed, IMHO. To allow this the entire implementation needs to change so all events from all transactions are stored until all connections with earlier started transactions have started new transactions. This way we could even have wildcard listens, imagine doing a listen % and getting all the generated events:) -- Regards Flemming Frandsen - http://dion.swamp.dk - YAPH
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/ > From each according to his ability. To each according to his ability to litigate.
Attachment
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;
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/ > From each according to his ability. To each according to his ability to litigate.
Attachment
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.
Attachment
# 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.
On Thu, Aug 03, 2006 at 12:43:47AM +0200, Flemming Frandsen wrote: > 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. Err, yes there would. Think about it: for that example to work, the server would have to store every notify that happened until your transaction completed. That could be thousands, considering you can defer indefinitly. And at the end of your transaction it has to go through the list and throw away 99% of them because they're not for "foo" but for something else. Currently NOTIFY requires no storage at all, so what you're suggesting is fairly expensive, since the cost would be applied for every transaction, even ones that don't use LISTEN. The solution is to do the LISTEN first, outside the transaction. The SELECT could be outside the transaction also, but you havn't told enough to know if that's feasable. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
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
On Thu, 3 Aug 2006, Martijn van Oosterhout wrote: > > 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. > > Err, yes there would. Think about it: for that example to work, the > server would have to store every notify that happened until your > transaction completed. That's exactly the same problem as exists today, although one transaction earlier. If someone listens to a bunch of events, does a commit and THEN waits around forever then you still have to store all the events from that point on. The only difference is that it would have to happen from the start of the transaction in stead of from the end of the transaction. > That could be thousands, considering you can defer indefinitly. Well, if the system still discards duplicate events then there is a limit on how many events you can get. Neighbouring transactions don't need to be kept around so they can be merged into one. In the end you end up with storing just one instance of the unique events destined for each connection along with one instance of the unique events generated during each transaction. > And at the end of your transaction it has to go through the list and > throw away 99% of them because they're not for "foo" but for something else. Well, my application would probably be insterested in every single event, but filtering for interesting events is really not a problem. > Currently NOTIFY requires no storage at all, so what you're suggesting > is fairly expensive, since the cost would be applied for every transaction, > even ones that don't use LISTEN. Well, in my experience all transactions use listen, or none of them do. You rarely have a situation where different clients access the same database and even more rarely different clients that use different methods to access it. Either you need events when working with a given database instance or you don't. > The solution is to do the LISTEN first, outside the transaction. Yes, this is the workaround I use.
On Fri, Aug 04, 2006 at 10:24:43AM +0200, Flemming Frandsen wrote: > On Thu, 3 Aug 2006, Martijn van Oosterhout wrote: > > Err, yes there would. Think about it: for that example to work, the > > server would have to store every notify that happened until your > > transaction completed. > > That's exactly the same problem as exists today, although one transaction > earlier. > > If someone listens to a bunch of events, does a commit and THEN waits > around forever then you still have to store all the events from that point > on. Well no, because at the point the transaction commits, you *know* what LISTENs they want. If another process does a NOTIFY, a signal is sent your process, which sets a flag to sent the message to the client. So by waiting after the listen, it'll run with the flag set until the next command. The difference being that what you want requires the server to store the (notify string,backend) pairs for every transaction until commit, whereas currently the server only has to store that for backends that actually did a LISTEN. > The only difference is that it would have to happen from the start of the > transaction in stead of from the end of the transaction. You could compromise and suggest that NOTIFY look at uncommitted records too. That would change the window to from the moment the LISTEN is executed, even if the transaction isn't committed. That wouldn't be too expensive. > > Currently NOTIFY requires no storage at all, so what you're suggesting > > is fairly expensive, since the cost would be applied for every transaction, > > even ones that don't use LISTEN. > > Well, in my experience all transactions use listen, or none of them do. > > You rarely have a situation where different clients access the same > database and even more rarely different clients that use different methods > to access it. Really? Even pg_dump cares? Or your maintainence scripts (VACUUM/ANALYZE)? I'd have to disagree though. In most of the systems I've worked with the database is in the center of the system. It'd be access by CGI scripts, cron job, batch jobs started by a person, load triggered by emails, etc. These may all use very different methods of accessing the database. Even if an application used LISTEN/NOTIFY, I can't imagine any bulk load/store being interested. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
On Fri, 4 Aug 2006, Martijn van Oosterhout wrote: > Really? Even pg_dump cares? Or your maintainence scripts > (VACUUM/ANALYZE)? Ok, those clients don't, but you rarely have many vacuum/pg_dump processes going on at the same time, so storing the events for them and throwing them away is not that big of a deal imho. > I'd have to disagree though. In most of the systems I've worked with > the database is in the center of the system. It'd be access by CGI > scripts, cron job, batch jobs started by a person, load triggered by > emails, etc. These may all use very different methods of accessing the > database. Even if an application used LISTEN/NOTIFY, I can't imagine > any bulk load/store being interested. Hmm, maybe you are right:) Maybe a new implementation should be able to do both. That way you could set the timetravel option on the begin statement: begin listen now So transactions that like to get all events they listen for during the transaction can and everybody else will get only events that happen after they commit.