ctid & updates

ctid & updates

Post by Joshua b. Jor » Wed, 05 Jun 2002 03:10:34



1#
I saw a post from Jan Wieck about how ctid can be used for a fast update.
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?

2#
Also, is ctid unique for each row? I would guess so since it looks like
(and I'm guessing) that ctid is a page/row offset. So there should be only
one thing at each ctid address. Is that correct that ctid is unique to a
table? 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
assume a user-side program could use that data to see how much unused,
yet allocated space there is in a table?

That might be the "You don't have to be a PostgreSQL hacker" version of
looking for compressible (via relocation) tables.

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly

 
 
 

ctid & updates

Post by Jan Wie » Wed, 05 Jun 2002 05:05:50



> 1#
> I saw a post from Jan Wieck about how ctid can be used for a fast update.
> 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?

    That's  one  of  the details I'm still thinking about. And in
    the case of a cursor using a junk attribute  it  gets  worse,
    because there is no easy way to push that new value back into
    the cursor's result set.

    But I doubt that this would become a real world problem ever.
    People  who  need to update one and the same DB row again and
    again during the same transaction are  spaghetti-code-script-
    kiddies  who know for sure that "a cursor's that thingy on ya
    screen that ya move withe mouse", so we're pretty  safe  here
    :-p

Quote:> 2#
> Also, is ctid unique for each row?

    Yes, per table.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command