Re: LISTEN/NOTIFY and notification timing guarantees

Tom Lane wrote:

> We could adopt the historical policy of sending self-notifies
> pre-commit, but that doesn't seem tremendously appetizing from the
> standpoint of transactional integrity.

But one traditional aspect of transactional integrity is that a
transaction always sees *its own* uncommitted work. Wouldn't the
historical policy of PostgreSQL self-notifies be consistent with
that?

-Kevin

On Tue, Feb 16, 2010 at 1:31 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Tom Lane  wrote:
>> We could adopt the historical policy of sending self-notifies
>> pre-commit, but that doesn't seem tremendously appetizing from the
>> standpoint of transactional integrity.
>
> But one traditional aspect of transactional integrity is that a
> transaction always sees *its own* uncommitted work.

True but notifications aren't sent until the transaction commits
anyway. At the time when an application receives its self-notifies, it
has already committed the transaction so there is no uncommitted work
anymore.

> Wouldn't the
> historical policy of PostgreSQL self-notifies be consistent with
> that?

No. The policy is also to not see the committed work if for some
reason the transaction had to roll back during commit. In this case
we'd also expect getting no notification from this transaction at all
and this is what is violated here.

Joachim

Joachim Wieland <joe(at)mcknight(dot)de> writes:
> On Tue, Feb 16, 2010 at 1:31 PM, Kevin Grittner
> <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>> Tom Lane wrote:
>>> We could adopt the historical policy of sending self-notifies
>>> pre-commit, but that doesn't seem tremendously appetizing from the
>>> standpoint of transactional integrity.
>>
>> But one traditional aspect of transactional integrity is that a
>> transaction always sees *its own* uncommitted work.

> True but notifications aren't sent until the transaction commits
> anyway. At the time when an application receives its self-notifies, it
> has already committed the transaction so there is no uncommitted work
> anymore.

Right. The application's view is that it sends COMMIT and gets any
self-notifies back as part of the response to that. What is worrisome
is that the notifies come out just before the actual commit and so it's
still (barely) possible for the transaction to abort. In which case it
should not have sent the notifies, and indeed did not send them as far
as any other client is concerned. We really ought to try to make a
similar guarantee for self-notifies.

After sleeping on it I'm fairly convinced that we should approach it
like this:

1. No special data path for self-notifies; we expect to pull them back
out of the queue just like anything else.

2. Add an extra lock to serialize writers to the queue, so that messages
are guaranteed to be added to the queue in commit order. As long as
notify-sending is nearly the last thing in the pre-commit sequence,
this doesn't seem to me to be a huge concurrency hit (certainly no worse
than the existing implementation) and the improved semantics guarantee
seems worth it.

3. When a transaction has sent notifies, perform an extra
ProcessIncomingNotifies scan after finishing up post-commit work
(so that an error wouldn't result in PANIC) but before we issue
ReadyForQuery to the frontend. This will mean that what the client
sees is

CommandComplete message for COMMIT (or NOTIFY)
NotificationResponse messages, including self-notifies
ReadyForQuery

where the notifies are guaranteed to arrive in commit order.
This compares to the historical behavior of

NotificationResponse messages for self-notifies
CommandComplete message for COMMIT (or NOTIFY)
ReadyForQuery
NotificationResponse messages for other transactions

where there's no particular guarantee about ordering of notifies
from different transactions. At least for users of libpq, postponing
the self-notifies till after CommandComplete won't make any difference,
because libpq reads to the ReadyForQuery message before deciding the
query is done.

regards, tom lane

On Tue, 2010-02-16 at 10:38 -0500, Tom Lane wrote:
> 2. Add an extra lock to serialize writers to the queue, so that messages
> are guaranteed to be added to the queue in commit order.

I assume this is a heavyweight lock, correct?

Regards,
Jeff Davis

On Tue, Feb 16, 2010 at 10:38 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 2. Add an extra lock to serialize writers to the queue, so that messages
> are guaranteed to be added to the queue in commit order.  As long as

fwiw, I think you're definitely on the right track. IMO, any scenario
where an issued notification ends up being deferred for an indefinite
period of time without alerting the issuer should be avoided if at all
possible. Just to clarify though, does your proposal block all
notifiers if any uncommitted transaction issued a notify?

merlin

Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> On Tue, 2010-02-16 at 10:38 -0500, Tom Lane wrote:
>> 2. Add an extra lock to serialize writers to the queue, so that messages
>> are guaranteed to be added to the queue in commit order.

> I assume this is a heavyweight lock, correct?

Yeah, that seems the easiest way to do it. I think an LWLock could be
made to work, but releasing it on error might be a bit funky.

regards, tom lane

Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> On Tue, Feb 16, 2010 at 10:38 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> 2. Add an extra lock to serialize writers to the queue, so that messages
>> are guaranteed to be added to the queue in commit order. As long as

> fwiw, I think you're definitely on the right track. IMO, any scenario
> where an issued notification ends up being deferred for an indefinite
> period of time without alerting the issuer should be avoided if at all
> possible. Just to clarify though, does your proposal block all
> notifiers if any uncommitted transaction issued a notify?

It will block other notifiers until the transaction releases its locks,
which should happen pretty promptly --- there are no user-accessible
reasons for it to wait.

regards, tom lane

tgl(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) writes:
> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
>> On Tue, Feb 16, 2010 at 10:38 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> 2. Add an extra lock to serialize writers to the queue, so that messages
>>> are guaranteed to be added to the queue in commit order.  As long as
>
>> fwiw, I think you're definitely on the right track. IMO, any scenario
>> where an issued notification ends up being deferred for an indefinite
>> period of time without alerting the issuer should be avoided if at all
>> possible. Just to clarify though, does your proposal block all
>> notifiers if any uncommitted transaction issued a notify?
>
> It will block other notifiers until the transaction releases its locks,
> which should happen pretty promptly --- there are no user-accessible
> reasons for it to wait.

I have heard of reasons to want to be able to have some actions run at
COMMIT time.

You probably recall Jan's proposal of a commit time timestamp. The
particular implementation may have fallen by the wayside, but the
reasons to want such things do continue to be. Indeed an "on commit"
trigger hook would be a mighty valuable thing to support things like
(but not restricted to) commit timestamps.

It's conceivable that "clustering issues" might introduce some somewhat
more "user-accessible" hooks that could cost something here. Certainly
not true today, but plausibly foreseeable...
--
select 'cbbrowne' || '@' || 'linuxfinances.info';
http://www3.sympatico.ca/cbbrowne/lsf.html
Beauty is the first test: there is no permanent place in the world for
ugly mathematics.