Обсуждение: Using xmin to identify last modified rows
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, Trying to identify last modified (updated or inserted) rows in a table, I thought I could use xmin. I tried is to get some lines sorted by xmin. When doing it on a slonified database, I had no problem getting these lines. But, trying the same query on a non slonified DB, I got an error, as there is no ordering operator for xid. I think that in the slon case, the query uses the implicit cast xid->xxid, and then the operator to sort xxid. What would be the best way to get last modified rows? What I tried : db=# select id_table, date_table, code_table from tb_table order by xmin desc limit 10; ERROR: could not identify an ordering operator for type xid ASTUCE : Use an explicit ordering operator or modify the query. Thanks in advance. Best regards, - -- Stéphane Schildknecht PostgreSQLFr - http://www.postgresql.fr Dalibo - http://www.dalibo.com -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJpTLoA+REPKWGI0ERAiCaAKCOSFQp/RtWFaLScwXLpqXQJKGzLgCgsNUn jXCUCSBBXVP7WEIn/M0Pklc= =PN5v -----END PGP SIGNATURE-----
"Stéphane A. Schildknecht" <stephane.schildknecht@postgresqlfr.org> writes: > But, trying the same query on a non slonified DB, I got an error, as there is > no ordering operator for xid. > > I think that in the slon case, the query uses the implicit cast xid->xxid, and > then the operator to sort xxid. You could order by age(xmin) instead > What would be the best way to get last modified rows? 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. You probably want to put a timestamp column on your tables and manage the date you put in their according to a policy you control. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support!
On Wed, Feb 25, 2009 at 5:21 AM, Gregory Stark <stark@enterprisedb.com> 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 -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Gregory Stark a écrit : > "Stéphane A. Schildknecht" <stephane.schildknecht@postgresqlfr.org> writes: (...) > > You could order by age(xmin) instead > >> What would be the best way to get last modified rows? > > 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. > > You probably want to put a timestamp column on your tables and manage the date > you put in their according to a policy you control. > > Gregory, Thanks for the answer. A timestamp would surely be a better idea. BTW, I don't have hand on the schema yet and was just looking for a quick way to get some last modified rows. Regards, - -- Stéphane Schildknecht PostgreSQLFr - http://www.postgresql.fr Dalibo - http://www.dalibo.com -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJpV6qA+REPKWGI0ERAhq/AJwNt845SDujYmFhe4aTqI30QBBC9gCg4vcH edlSZti3KDtozJ82Od0nErQ= =z1lm -----END PGP SIGNATURE-----
Stéphane A. Schildknecht wrote: > Trying to identify last modified (updated or inserted) rows in a table, I > thought I could use xmin. > > I tried is to get some lines sorted by xmin. > > When doing it on a slonified database, I had no problem getting these lines. > > But, trying the same query on a non slonified DB, I got an error, as there is > no ordering operator for xid. > > I think that in the slon case, the query uses the implicit cast xid->xxid, and > then the operator to sort xxid. > > What would be the best way to get last modified rows? > > What I tried : > db=# select id_table, date_table, code_table from tb_table order by xmin desc limit 10; > ERROR: could not identify an ordering operator for type xid > ASTUCE : Use an explicit ordering operator or modify the query. What about: test=> SELECT xmin, * FROM mausi ORDER BY xmin::text::bigint; xmin | id | val -------+----+---------------- 14005 | 1 | test\test/test 14040 | 3 | mamma (2 rows) Yours, Laurenz Albe
Richard Broersma <richard.broersma@gmail.com> writes: > On Wed, Feb 25, 2009 at 5:21 AM, Gregory Stark <stark@enterprisedb.com> 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!
Richard Broersma <richard.broersma@gmail.com> writes: > On Wed, Feb 25, 2009 at 5:21 AM, Gregory Stark <stark@enterprisedb.com> 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 Those statements are all still true, but notice that nowhere do they suggest doing anything except simple equality comparisons on XIDs. The OP was looking for ordering, which is a lot trickier, especially if you might be dealing with old (frozen) tuples. regards, tom lane