Re: Re: [COMMITTERS] pgsql: Ensure age() returns a stable value rather than the latest value

On 12 May 2012 17:59, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>> On 12 May 2012 15:55, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>>>> Case (2) is more complex than described. If we use XID always, then
>>>> the so-say stable value could change mid way through a scan when the
>>>> XID is assigned and would provide neither a stable, sensible nor a
>>>> backwards compatible response.
>
>>> No, that's entirely wrong.  The original behavior of the function
>>> for case 2, which I am proposing we revert to,  is that it would
>>> forcibly assign an XID when the transaction didn't already have one.
>>> Subsequently, that value would be stable for the duraction of the xact.
>
>> As you said yourself, assigning an XID is exactly the same as using
>> ReadNewTransactionId(). There is no difference in behaviour for case
>> 2.
>
> What I said was that there is no difference in behavior with respect to
> the value returned by age() itself.  There *is* a difference in overall
> behavior if the transaction calling age() does something that requires
> an XID later on; which is more or less what I thought you were pointing
> out in the first snippet quoted above.  Consider a transaction
> consisting of
>
>        INSERT INTO log_table SELECT ..., age(xmin), ... FROM some_table;
>
> In all previous releases of PG, the rows inserted into log_table will
> have xmin equal to the reference XID used by the age() calculation,
> so that it would be possible to cross-check those rows against the xmins
> of the source rows in some_table.  With the behavior you're arguing for,
> this stops working, because (at least for the first row) age() is
> executed before an XID has been acquired by the INSERT.
>
> Now it's entirely likely that there is nobody out there relying on such
> a thing, but nonetheless this is a compatibility break, and an
> unnecessary one IMO.  You haven't shown any convincing reason why we
> need to change the behavior of age() on master servers at all.
>
> To put it a different way: the argument I was trying to illustrate by
> breaking down the different cases is that "use XID on master and capture
> ReadNewTransactionId on slave" is close enough to being the same
> behavior for each that it shouldn't be a problem.  The value that a hot
> standby transaction will get is indistinguishable from what it would
> have gotten if running on the master, and the fact that the underlying
> implementation is entirely different could only be detected by a
> transaction that could write, which the HS transaction cannot.  So
> I think we should do that, and thereby not create any change at all
> in the behavior of age() on master servers.

As of my last commit, each of these three transactions return exactly
the same answers at each point that they always did:

SELECT age(relfrozenxid) from pg_class;

INSERT INTO foo
SELECT age(relfrozenxid) from pg_class;

BEGIN;
SELECT age(relfrozenxid) from pg_class;
SELECT txid_current();
SELECT age(relfrozenxid) from pg_class;
COMMIT;

but now the first works on Hot Standby too. Isn't that what we want?

I have done as you requested and ensured no change of behaviour occurs.

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