Re: ctid & updates (or speedy updates/deletes) - Mailing list pgsql-general
From | Joshua b. Jore |
---|---|
Subject | Re: ctid & updates (or speedy updates/deletes) |
Date | |
Msg-id | Pine.BSO.4.44.0206031939050.21627-100000@kitten.greentechnologist.org Whole thread Raw |
In response to | Re: ctid & updates (Tom Lane <tgl@sss.pgh.pa.us>) |
List | pgsql-general |
Cool thanks Tom. This is awesome - as long as I don't mind being tied to PostgreSQL then this sort of thing is a nifty thing to make things go faster. Josh For the archives and others who are just reading this (I know I often just follow interesting threads to get ideas) I plan to use the tid two ways: fast access for updates to a row, detecting updates. I'll do something like UPDATE ... WHERE ctid = '(23,22)'::tid to do a really fast update. The number of modified rows will be either one (there is only one row per tid address) or zero (the row was altered since I took the row's tid). My application will notice the change and react accordingly - say get a new copy of the row from the table and see what the user wants to do. This addresses the issue of multiple users sending updates to the same row. Functions from src/backend/utils/adt/tid.c Both functions return the current tid for a row indicated by a tid. From my (very inexpert) reading of the source this might take any previous tid and get the current one. currtid takes the table OID and currtid2 takes the table name currtid(OID,TID) RETURNS TID currtid2(TEXT,TID) RETURNS TID An example: CREATE TABLE a ( j INTEGER ); INSERT INTO a (j) VALUES (0); INSERT INTO a (j) VALUES (1); SELECT tableoid,ctid,oid,* FROM a; tableoid | oid | ctid | i ----------+----------+-------+--- 63993118 | 63993128 | (0,1) | 0 63993118 | 63993129 | (0,2) | 1 /* * ctid starts counting from page zero, row 1 */ UPDATE a SET i = 0 WHERE i = 0; SELECT tableoid,ctid,oid,* FROM a; tableoid | oid | ctid | i ----------+----------+-------+--- 63993118 | 63993129 | (0,2) | 1 63993118 | 63993128 | (0,3) | 0 /* * ctid incremented on an update (since an update is really just a sneaky * sort of insert) */ SELECT currtid2('a'::text, '(0,1)'::tid) currtid2 ---------- (0,3) /* * locate the current row based on the old address */ UPDATE a SET i = 0 WHERE i = 0; SELECT tableoid,ctid,oid,* FROM a; tableoid | oid | ctid | i ----------+----------+-------+--- 63993118 | 63993129 | (0,2) | 1 63993118 | 63993128 | (0,4) | 0 /* * ctid incremented again, as expected */ SELECT currtid2('a'::text, '(0,1)'::tid) currtid2 ---------- (0,4) /* * currtid2 still finds the current row by using the old * tid which is two revisions old. Maybe this just sticks * around until the table is vacuumed. Make sure to not * try to use the tid over any vacuum operation since that * just moves everything around anyway. */ So if I tried to do an UPDATE operation where the row is located by ctid and nothing was updated then either the table was vacuumed or the row was obsoleted by another unknown update. The new ctid is available but it might be better to check with the user and get the new values or something like that. Joshua b. Jore ; http://www.greentechnologist.org ; 1121 1233 1311 200 1201 1302 1211 200 1201 1303 200 1300 1233 1313 1211 1302 1212 1311 1230 200 1201 1303 200 1321 1233 1311 1302 200 1211 1232 1211 1231 1321 200 1310 1220 1221 1232 1223 1303 200 1321 1233 1311 200 1201 1302 1211 232 200 1112 1233 1310 1211 200 1013 1302 1211 1211 1232 201 22 On Mon, 3 Jun 2002, Tom Lane wrote: > "Joshua b. Jore" <josh@greentechnologist.org> writes: > > I noticed that ctid changes on update (as expected since it's really a new > > row). Is there anyway to get the new ctid from the update so later > > updates to the row can continue to use ctid to zero in on the row > > location? > > There's a function called something like currtid that takes the > CTID of the possibly-obsoleted row and returns the CTID of its latest > updated version. I believe this is exported because the ODBC driver > uses it, so it's unlikely to go away, even though AFAIR it's not > documented anywhere. A risk of using it is that CTID of an updated > row cannot be trusted for very long --- once VACUUM has come by, > you might find that CTID reassigned to some other row entirely. > > > Can anything interesting be done with the empty space? Is there any > > way to find the maximum ctid and look for quantities of empty space? > > I don't think CTID gives you any useful hint about the amount of free > space available on a page. > > regards, tom lane >
pgsql-general by date: