Thread: pgmemcache
I was wondering if anyone on the list has a successful installation of pgmemcache running that uses LISTEN/NOTIFY to signal a successfully completed transaction, i.e., to get around the fact that TRIGGERS are transaction unaware. Or perhaps any other information regarding a successful deployment of pgmemcache. The information available on the web/groups is pretty scant. Any information would be greatly appreciated!
On Tue, Apr 04, 2006 at 12:24:42AM -0700, C Storm wrote: > I was wondering if anyone on the list has a successful installation of > pgmemcache running > that uses LISTEN/NOTIFY to signal a successfully completed transaction, > i.e., to get around the fact > that TRIGGERS are transaction unaware. Or perhaps any other > information regarding a successful > deployment of pgmemcache. The problem with attempting that is that you'd have a window between transaction commit and when the cache was invalidated. If that's acceptable then it shouldn't be too difficult to set something up using LISTEN/NOTIFY like you describe. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
It would be nice to have ON COMMIT triggers for this use.
However you can emulate ON COMMIT triggers with a modification of the
memcache update process :
- A standard trigger sends the data to update to memcache
- The trigger also sends the PID
- Instead of being used immediately, this data is kept in a buffer
- Notify is issued
On commit :
- postgres sends the NOTIFY signal
- the memcache updater reads the NOTIFY (which embeds the PID I believe)
; it finds the buffered data sent above and uses it to update memcached
On rollback :
- Interesting problem ;)))
OK, it's a kludge. When can we get ON COMMIT triggers ?
> On Tue, Apr 04, 2006 at 12:24:42AM -0700, C Storm wrote:
>> I was wondering if anyone on the list has a successful installation of
>> pgmemcache running
>> that uses LISTEN/NOTIFY to signal a successfully completed transaction,
>> i.e., to get around the fact
>> that TRIGGERS are transaction unaware. Or perhaps any other
>> information regarding a successful
>> deployment of pgmemcache.
>
> The problem with attempting that is that you'd have a window between
> transaction commit and when the cache was invalidated. If that's
> acceptable then it shouldn't be too difficult to set something up using
> LISTEN/NOTIFY like you describe.
PFC, > It would be nice to have ON COMMIT triggers for this use. > > However you can emulate ON COMMIT triggers with a modification of the > memcache update process : Well, I'm back in touch with the GORDA project so possibly we can have BEFORE COMMIT triggers after all. BTW, it's important to note that AFTER COMMIT triggers are logically impossible, so please use BEFORE COMMIT so that it's clear what we're talking about. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco
On Wed, Apr 12, 2006 at 04:03:43PM -0700, Josh Berkus wrote: > PFC, > > > It would be nice to have ON COMMIT triggers for this use. > > > > However you can emulate ON COMMIT triggers with a modification of the > > memcache update process : > > Well, I'm back in touch with the GORDA project so possibly we can have > BEFORE COMMIT triggers after all. > > BTW, it's important to note that AFTER COMMIT triggers are logically > impossible, so please use BEFORE COMMIT so that it's clear what we're > talking about. Why are AFTER COMMIT triggers impossible? ISTM they would be useful as a means to indicate to some external process if a transaction succeeded or not. And for some things you might only want to fire the trigger after you knew that the transaction had committed. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
"Jim C. Nasby" <jnasby@pervasive.com> writes:
> Why are AFTER COMMIT triggers impossible?
What happens if such a trigger gets an error? You can't un-commit.
regards, tom lane
Hi, Tom, Tom Lane wrote: >>Why are AFTER COMMIT triggers impossible? > > What happens if such a trigger gets an error? You can't un-commit. Then it must be specified that those triggers are in their own transaction, and cannot abort the transaction. Or use the 2-phase-commit infrastructure for them. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
Christian Storm <christian.storm@gmail.com> writes:
> Not sure if I follow why this is a problem. Seems like it would be
> beneficial to have both BEFORE and AFTER COMMIT triggers.
> With the BEFORE COMMIT trigger you would have the ability to 'un-
> commit' (rollback) the transaction. With
> the AFTER COMMIT trigger you wouldn't have that option because the
> commit has already been successful. However,
> with an AFTER COMMIT you would be able to trigger other downstream
> events that rely on a transaction successfully committing.
An AFTER COMMIT trigger would have to be in a separate transaction.
What happens if there's more than one, and one of them fails? Even
more to the point, if it's a separate transaction, don't you have
to fire all these triggers again when you commit that transaction?
The idea seems circular.
regards, tom lane
On Apr 13, 2006, at 12:38 PM, Tom Lane wrote: > Christian Storm <christian.storm@gmail.com> writes: >> Not sure if I follow why this is a problem. Seems like it would be >> beneficial to have both BEFORE and AFTER COMMIT triggers. >> With the BEFORE COMMIT trigger you would have the ability to 'un- >> commit' (rollback) the transaction. With >> the AFTER COMMIT trigger you wouldn't have that option because the >> commit has already been successful. However, >> with an AFTER COMMIT you would be able to trigger other downstream >> events that rely on a transaction successfully committing. > > An AFTER COMMIT trigger would have to be in a separate transaction. > What happens if there's more than one, and one of them fails? Even > more to the point, if it's a separate transaction, don't you have > to fire all these triggers again when you commit that transaction? > The idea seems circular. I suspect that in reality you'd probably want each on-commit trigger to be it's own transaction, but it depends on what you're doing. Also, I can't see any use for them where you'd actually be interacting with the database, only if you were calling something externally via a function. One example would be sending an email out when a certain table changes; in many cases it's better to let the change happen even if the email can't be sent, and you'd rather not send an email if the transaction just ends up rolling back for some reason. And yes, you'd have to ensure you didn't code yourself up a trigger loop. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
> An AFTER COMMIT trigger would have to be in a separate transaction.
I guess AFTER COMMIT triggers would be like a NOTIFY, but more powerful.
While NOTIFY can't transmit information to another process, this trigger
could, and the other process could then view the results of the commited
transaction.
Also, implementing a long process (something involving network
roundtrips, for instance) in a BEFORE COMMIT trigger would delay the
transaction and any locks it holds with no benefit.
> What happens if there's more than one, and one of them fails?
Each one in its own transaction ?
> Even more to the point, if it's a separate transaction, don't you have
> to fire all these triggers again when you commit that transaction?
> The idea seems circular.
I guess AFTER COMMIT triggers are most useful when coupled to a trigger
on a modification to a table. So, the "before / after commit" could be an
attribute of an AFTER INSERT/UPDATE/DELETE trigger. If the AFTER COMMIT
trigger doesn't do any modifications to the target table, there will be no
infinite loop.
The before/after commit could also be implemented not via triggers, but
via deferred actions, by telling postgres to execute a specific query just
before/after the transaction commits. This could be used to implement the
triggers, but would also be more generic : a trigger on INSERT could then
defer a call to memcache update once the transaction is commited. It gets
lisp-ish, but it would be really cool?
Tom Lane <tgl@sss.pgh.pa.us> writes: > Christian Storm <christian.storm@gmail.com> writes: > > Not sure if I follow why this is a problem. Seems like it would be > > beneficial to have both BEFORE and AFTER COMMIT triggers. > > With the BEFORE COMMIT trigger you would have the ability to 'un- > > commit' (rollback) the transaction. With > > the AFTER COMMIT trigger you wouldn't have that option because the > > commit has already been successful. However, > > with an AFTER COMMIT you would be able to trigger other downstream > > events that rely on a transaction successfully committing. > > An AFTER COMMIT trigger would have to be in a separate transaction. > What happens if there's more than one, and one of them fails? Even > more to the point, if it's a separate transaction, don't you have > to fire all these triggers again when you commit that transaction? > The idea seems circular. Maybe it just means they would have to be limited to not making any database modifications. Ie, all they can do is notify the outside world that the transaction committed. Presumably if you wanted to make any database modifications you would just do it in the transaction anyways since they wouldn't show up until the transaction commits. -- greg
PFC <lists@peufeu.com> writes:
> I guess AFTER COMMIT triggers would be like a NOTIFY, but more powerful.
I'll let you in on a secret: NOTIFY is actually a before-commit
operation. This is good enough because it never, or hardly ever,
fails. I would argue that anything you want to do in an AFTER COMMIT
trigger could just as well be done in a BEFORE COMMIT trigger; if that's
not reliable enough then you need to fix your trigger.
regards, tom lane
Not sure if I follow why this is a problem. Seems like it would be beneficial to have both BEFORE and AFTER COMMIT triggers. With the BEFORE COMMIT trigger you would have the ability to 'un- commit' (rollback) the transaction. With the AFTER COMMIT trigger you wouldn't have that option because the commit has already been successful. However, with an AFTER COMMIT you would be able to trigger other downstream events that rely on a transaction successfully committing. If the trigger fails it is the triggers problem, it isn't the commit's problem, i.e., you wouldn't want to 'un-commit'. If the trigger gets an error it has to gracefully deal with that error programatically. Where have I gone astray with this logic? On Apr 12, 2006, at 5:35 PM, Tom Lane wrote: > "Jim C. Nasby" <jnasby@pervasive.com> writes: >> Why are AFTER COMMIT triggers impossible? > > What happens if such a trigger gets an error? You can't un-commit. > > regards, tom lane
> I'll let you in on a secret: NOTIFY is actually a before-commit > operation. This is good enough because it never, or hardly ever, > fails. I would argue that anything you want to do in an AFTER COMMIT > trigger could just as well be done in a BEFORE COMMIT trigger; if > that's > not reliable enough then you need to fix your trigger. So, looping back to pgmemcache. It doesn't seem like it's about the trigger failing; it's about the transaction failing. How could I 'fix my trigger' so that if the transaction fails the cache wouldn't be updated? > An AFTER COMMIT trigger would have to be in a separate transaction. Agreed. Each trigger being in its own transaction. > What happens if there's more than one, and one of them fails? Each AFTER COMMIT trigger would be in its own transaction. So if it fails, let it fail. It isn't the original transactions fault. If you want it to bundled with the original transaction then do a BEFORE COMMIT. If there is more than one, so be it. One may fail, another may not. If that is not okay or the intended behavior, then create a new trigger than combines them into one transaction so that they are coupled. > Even > more to the point, if it's a separate transaction, don't you have > to fire all these triggers again when you commit that transaction? > The idea seems circular. Why would it have to do this? Not sure if I follow. In my mind, the only way this would happen is if the AFTER COMMIT trigger acted upon the very same table it is triggered off of. Then you could have a circular reference. However, this is true for the BEFORE COMMIT situation too. I don't see the difference. What am I missing? Cheers, Christian