Richard Broersma <richard.broersma@xxxxxxxxx> writes: > On Wed, Feb 25, 2009 at 5:21 AM, Gregory Stark <stark@xxxxxxxxxxxxxxxx> wrote: > >> I'm not sure using xmin is such a great idea really. It's handy for ad-hoc >> queries but there are all kinds of cases where it might not give you the >> results you expect. > > > Its been a while since the following emails were written. Has the > treatment of xmin changed since then, or is using a timestamp a better > practice? > > http://archives.postgresql.org/pgsql-novice/2007-02/msg00079.php > http://archives.postgresql.org/pgsql-hackers/2004-02/msg00654.php Well those emails aren't discussing evaluating when records were updated or deciding which were updated more recently than others. All they say is that in Postgres if ctid and xmin both match then you're looking at the same version of the same record. For a gui table editor or ODBC driver that's an important thing to know. If what you want to do is find records which have been updated for something like a "Recently updated pages" it's unlikely that the desired behaviour will exactly match how Postgres works. You're better off deciding the policy you want and writing code to implement that. Some examples of how xmin might not do what you expect: The order in which transactions *start* will determine the ordering, not the order in which they commit. If you look at records you've updated in the same transaction it's even possible to see records which come from the "future". If any records have frozen you lose any record of what order they were created. Another example is that it's impossible to ignore "trivial" updates -- any update will update xmin no matter how trivial, even if no columns are updated. Worse, in the future there may be changes to database internals which change when xmin is updated which won't match your desired policy. For example if we decide to replace VACUUM FULL with something which does no-op updates instead of moving tuples then you'll find records spontaneously appearing to have been recently updated. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
