Steve Singer <ssinger at ca.afilias.info> writes:
> On 10-12-04 01:38 PM, Simon Riggs wrote:
>> On Fri, 2010-12-03 at 12:26 -0500, Christopher Browne wrote:
>>> === Commit timestamps ===
>>> * per Jan Wieck
>>> * Requires PostgreSQL extension
>>> * Eliminates need for periodic generation of SYNC events
>>> * Simplifies queries for searching for sl_log_* data
>>> * Enables carryover of commit times to subscribers
>>
>> It should be possible to do this using LISTEN/NOTIFY.
>> Now we can pass a payload with the NOTIFY, we just need to pass the
>> transaction id. That can be handled automatically by trigger.
>
> What happens if the server crashes before the process listening does
> what it needs to?  I don't think the notification queue gets
> persisted.

Not anymore, no, and that could well present a problem.

>> What we need is commit order, not commit timestamp, yes?
>> NOTIFY happens in commit sequence. It doesn't provide the commit
>> timestamp, but perhaps that can be provided by the LISTENer, if you
>> really need it.
>
> If all one wants to do is replace sync generation then you just need
> the ordering not the timestamps.  Personally I don't see a huge amount
> of motivation to eliminate sync generation.

The value to it isn't in the elimination of the sync thread, which,
after all, only has a little over a hundred lines of code, many rather
short.

Instead, it is in eliminating the need to use the SYNC artifact to
divide up the work, which has several effects...

 a) If the slons all fall over for the weekend, then there's guaranteed
    to be One Huge SYNC when the origin's slon comes back up.
    
    Note that we already have some tooling as belt-and-suspenders to guard
    against this - see
    <http://git.postgresql.org/gitweb?p=slony1-engine.git;a=blob;f=tools/generate_syncs.sh>.

    If we already have "guard code," that cuts down the already not huge
    or awful risk of this case, the "commit times eliminates SYNC
    events" feature isn't terribly worthwhile on this count.

 b) If there's some Very Large Update (e.g. - the UPDATE FOO SET A=B
    WHERE ID IN ([large set of IDs]), we're liable to get stuck with
    SYNCs that process more work than they really need to.

    If we had commit timestamps, then we could stop processing (and do a
    COMMIT, and switch to a new event) immediately upon reaching the end
    of the big transaction.

    I'll amiably go along with the thought that this doesn't really save
    us from *all* that much work.

 c) On a busy cluster with a lot of concurrent transactions on separate
    connections, the sequence sl_action_seq is being continually updated
    by each backend.

    If we had commit timestamps, we could eliminate sl_action_seq, in
    favor of using an in-memory variable existing independently in each
    backend that is reset to 0 each time a new transaction starts.

    Thus, no more fighting over the sequence.

    We don't know for certain that it's a bottleneck that's biting
    people heavily, but frankly, I don't think that's a factor that has
    been readily measured.
-- 
(format nil "~S@~S" "cbbrowne" "afilias.info")
Christopher Browne
"Bother,"  said Pooh,  "Eeyore, ready  two photon  torpedoes  and lock
phasers on the Heffalump, Piglet, meet me in transporter room three"