Обсуждение: How to safely compare transaction id?
Hi, I want to compare the record's transaction id in sql statements or PL/pgSQL stored procedure. Are there any system function or operator can safely(transaction id wraparound safed) compare the transaction id? Thanks!
On 1/11/08, alphax <alphax@vip.163.com> wrote: > I want to compare the record's transaction id in sql statements or > PL/pgSQL stored procedure. Are there any system function or operator can > safely(transaction id wraparound safed) compare the transaction id? In 8.3 there are txid functions that export 8byte wraparound-safe transaction id, that can be safely stored in user tables: http://developer.postgresql.org/pgdocs/postgres/functions-info.html#FUNCTIONS-TXID-SNAPSHOT For 8.2 and below you can get them as external module from Skytools package: http://pgfoundry.org/projects/skytools -- marko
Marko Kreen wrote: > On 1/11/08, alphax <alphax@vip.163.com> wrote: >> I want to compare the record's transaction id in sql statements or >> PL/pgSQL stored procedure. Are there any system function or operator can >> safely(transaction id wraparound safed) compare the transaction id? > > In 8.3 there are txid functions that export 8byte wraparound-safe > transaction id, that can be safely stored in user tables: > > http://developer.postgresql.org/pgdocs/postgres/functions-info.html#FUNCTIONS-TXID-SNAPSHOT > > > For 8.2 and below you can get them as external module from > Skytools package: http://pgfoundry.org/projects/skytools > 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? 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"?
On 1/12/08, alphax <alphax@vip.163.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
Marko Kreen wroted: >> 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. Thanks, I just done some simple tested, ////////////////////////////////////////////////////////////////////////// // initialize ////////////////////////////////////////////////////////////////////// create table test (last_update_tx_id); insert into test(txid_current()); ///////////////////////////////////////////////////////////////////// // Every time I do an update, I found the last_update_tx_id is equal to the xmin //////////////////////// begin update test set last_update_tx_id = txid_current(); commit; select *, test.xmin from test; ////////////////////////////////////////////////////////////////////////// So, it seems the system column "cmin" is the logical "current version" of that record, isn't it?
alphax <alphax@vip.163.com> writes: > So, it seems the system column "cmin" is the logical "current version" > of that record, isn't it? No. Have you read http://www.postgresql.org/docs/8.2/static/ddl-system-columns.html regards, tom lane
On 1/11/08, alphax <alphax@vip.163.com> wrote: > 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. I'm not sure I understand, but maybe this thread will help? http://archives.postgresql.org/pgsql-general/2007-10/msg00503.php
Tom Lane wrote: > alphax <alphax@vip.163.com> writes: >> So, it seems the system column "cmin" is the logical "current version" >> of that record, isn't it? > > No. Have you read > http://www.postgresql.org/docs/8.2/static/ddl-system-columns.html > Yes, I do. But I don't understand the actual meaning. Trevor Talbot wrote: > On 1/11/08, alphax <alphax@vip.163.com> wrote: > >> 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. > > I'm not sure I understand, but maybe this thread will help? > http://archives.postgresql.org/pgsql-general/2007-10/msg00503.php > Thanks, I had readed that message thread. Actually, my application is similar to > "XMIN changes when a (writing) transaction commits successfully". > We don't care *how* it changes, just *that* it does so. If I do: type TCompareResult = (PRECEDING, SAME, SUCCEEDING); 1) T1 BEGIN T1; 2) T1 READ table.xmin INTO :xmin_value 3) T1 COMMIT T1; 4) Other application(NOT Include VACUUM) do something 5) T2 BEGIN SERIALIZABLE T2; 6) T2 READ table.xmin INTO :xmin_value2 7) T2 TCompareResult compareResult := WRAPAROUND_SAFE_TID_COMPARE (xmin_value, xmin_value2) 8) Other transaction(INCLUDE VACUUM) do some thing 9) T2 READ table.xmin INTO :xmin_value3 10)T2 COMMIT T2; I must ensure: A. In the time point 7), If compareResult is SAME, the record in table is not changed by other committed transaction. if compareResult is PRECEDING, the record is changed. This is meaning of "current version (value) of record" I said. B. In the time point 9), xmin_value3 must equal to previous readed xmin_value2. I have qualms about this because the document say:| "xmax |is the identity (transaction ID) of the deleting transaction, or zero for an undeleted row version." I dont known in which condiation my transaction can see a deleted record. Can someone tell me? I must known which system column(xmin I assume here) can indicate the changing of a logical row, If there is not such a column, I need to create an user column to do this job. I have take notice of the Tome Lane and others say in the thread "XMIN semantic at peril", my understand is xmin is what I want, is it right? But if the VACUUM is participate in time point 4) like below case, the record change detection would be failed. It is a big problem in my application. 4.1)...(long time) 4.2) other transaction do some thing with table(change xmin by there transaction id) and commit. 4.X)...(long time) 4.X+1) VACUUM replace table.xmin to |FrozenXID. Thanks for your help! |