Re: tracking commit timestamps

On 2013-12-10 11:56:45 +0200, Heikki Linnakangas wrote:
> Speaking of the functionality this does offer, it seems pretty limited. A
> commit timestamp is nice, but it isn't very interesting on its own. You
> really also want to know what the transaction did, who ran it, etc. ISTM
> some kind of a auditing or log-parsing system that could tell you all that
> would be much more useful, but this patch doesn't get us any closer to
> that.

It's useful for last-update-wins for async multimaster. Currently
several userspace solutions try to approximate it by inserting a
timestamps into a column when a row is inserted or updated, but that is
quite limiting because either the number is out of date wrt. the commit
and/or it will differ between the rows.

I don't see how you could get an accurate timestamp in a significantly
different way?

> Does this handle XID wraparound correctly? SLRU has a maximum of 64k
> segments with 32 SLRU pages each. With 12 bytes per each commit entry,
> that's not enough to hold the timestamp and "commit extra data" of the whole
> 2^31 XID range: (8192 * 32 * 65536) / 12 = 1431655765. And that's with the
> default page size, with smaller pages you run into the limit quicker.
>
> It would be nice to teach SLRU machinery how to deal with more than 64k
> segments. SSI code in twophase.c ran into the same limit, and all you get is
> a warning there.

Yea, 9.3 is already running afoul of that, because of the changed size
for the multixact member pages. Came up just yesterday in the course of
#8673.

(gdb) p/x (1L<<32)/(MULTIXACT_MEMBERS_PER_PAGE * SLRU_PAGES_PER_SEGMENT)
$10 = 0x14078

Is this limitation actually documented anywhere? And is there anything
that needs to be changed but SlruScanDirectory()?

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services