Re: preserving forensic information when we freeze

Robert Haas <robertmhaas@gmail.com> wrote:

> Meh.  Who are all of these people who are fetching xmin, xmax, cmin,
> and cmax in complex queries, and why are they doing that?  If those
> columns are just for forensic purposes, then whatever performance
> disadvantages the function-based approach has don't really matter.  If
> people are using them as integral parts of their application, then
> that's more of a concern, but how are those people not getting broken
> every release or three anyway?  We keep inventing things like
> combo-cids and multi-xacts and multi-xacts that also contain an update
> and now freezing without changing xmin, and if you're actually relying
> on those columns for anything but forensics your application is
> presumably going to break when we change whatever part it depends on.
> Is anyone really doing that, and if so, why?

I've seen an ORM use xmin for a sort of optimistic concurrency
control scheme.  Let's say that you bring up an address by primary
key, and change an incorrect apartment number.  At the same time,
someone else is entering a change of address, to a whole new
location where a totally different apartment number is supplied.
The developers want to prevent portions of the two addresses from
being intermingled, they don't want to hold a database transaction
open for the time the user has the window up, they want to avoid
blocking as much as possible, and they don't want to store an extra
column with a version number or timestamp just to do that -- so
they use xmin.  When they go to rewrite the row they use the PK
values plus the xmin in the UPDATE statement's WHERE clause.  If
the row is not found, the user gets an error message.  Currently
that does mean that the users can get a spurious error message when
there is a tuple freeze while the window is open, so the current
changes are probably good news for those using this approach.

Other than that and ctid (for similar reasons) I have not seen
system columns used in applications or application frameworks.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company