Search Postgresql Archives

Re: lifetime of the old CTID

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tue, 2022-07-05 at 12:22 +0200, Matthias Apitz wrote:
> El día Dienstag, Juli 05, 2022 a las 10:40:40 +0200, Laurenz Albe escribió:
> > On Tue, 2022-07-05 at 09:51 +0200, Matthias Apitz wrote:
> > > We're using the SQL function currtid2() to get the new CTID of a row
> > > when this was UPDATEd.
> > > 
> > > Investigating cases of failing updates, it turns out that the old CTID
> > > has only a limited lifetime; one can check this with SQL:
> > > 
> > > sisis=# select ctid, d01gsi from d01buch where d01gsi = '0240564';
> > >     ctid    |           d01gsi            
> > > ------------+-----------------------------
> > >  (29036,11) | 0240564
> > > 
> > > now I update the row and afterwards pick up the new CTID based on the
> > > old one (29036,11):
> > > 
> > > sisis=# UPDATE d01buch set d01ort='Wolfratshausen' where d01gsi='0240564';
> > > UPDATE 1
> > > sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> > >  currtid2  
> > > -----------
> > >  (29036,7)
> > > 
> > > Now I go and pick up a coffe in our kitchen and check again:
> > > 
> > > sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> > >   currtid2  
> > > ------------
> > >  (29036,11)
> > > 
> > > i.e. the function now only returns it argument. and not the new CTID
> > > anymore.
> > > 
> > > Why is this? And what triggers exactly that the old CTID can't be used
> > > anymore?
> > 
> > Another explanation could be that the HOT chain was pruned while you were away.
> 
> I've read now about HOT and understand that autovacuum will prune the
> HOT chain. But also a simple SELECT seems to remove it:
> 
> sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
>  currtid2  
> -----------
>  (29036,7)
> 
> 
> sisis=# select ctid, d01gsi from d01buch where d01gsi = '0240564';
>    ctid    |           d01gsi            
> -----------+-----------------------------
>  (29036,7) | 0240564                    
> 
> 
> sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
>   currtid2  
> ------------
>  (29036,11)

Right.  Heap-Only tuples can also vanish without autovacuum; that is why I
suspected it might have been that.

Yours,
Laurenz Albe





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux