Re: How to safely compare transaction id?

On 1/12/08, alphax <alphax(at)vip(dot)163(dot)com> wrote:
> Thanks. Actually, I want to compares the system columns(xmin, xmax,
> ctid) with the tid returned by txid functions declared in
>
> http://developer.postgresql.org/pgdocs/postgres/functions-info.html#FUNCTIONS-TXID-SNAPSHOT
>
>
> I want to determines a given record which visible to current transaction
> whether or not be updated after some time point, that time point is
> indicated by aother transaction id started and committed in past time.
> How can I safely do that?

You cannot compare txids with each other and determine visibility,
you need snapshots for that.

Eg. PgQ (generic queue) is implemented in following way:

- Current txid is stored with data rows.
- Periodically txid_snapshot is stored to separate table (pgq.tick).
- later when reading data, 2 snapshots are taken from pgq.tick,
and from them are txids determined that were committed between
those.
- The data rows for the txids are fetched from data tables then.

AFAIK this is only way how to implement robust and high-performance
queue in otherwise generic RDBMS.

> By the way, Can I think that the value of system column "ctid" of an
> record is the logical "current version" of that record, and used to
> compare with txid returned by "FUNCTIONS-TXID-SNAPSHOT"?

No, it is just physical location of the row.

--
marko