Re: not(t_xmax = 0)

Alejandro Carrillo wrote:
> De: Kevin Grittner <kgrittn(at)mail(dot)com>

>> Please give a high-level description of what you are trying to
>> accomplish and why.

> I try to do a function that let know which rows are deleted in a
> table using the xmax !=0

That's not something you're going to have much luck with by using
plpgsql to try to parse heap pages. If you explain why you want to
do this, it might help in suggesting alternative techniques. Maybe
there is already a tool written that you could use.

-Kevin

"Kevin Grittner" <kgrittn(at)mail(dot)com> writes:
> Alejandro Carrillo wrote:
>> I try to do a function that let know which rows are deleted in a
>> table using the xmax !=0

> That's not something you're going to have much luck with by using
> plpgsql to try to parse heap pages.

No, because xmax being nonzero doesn't prove much: the row might be
updated/deleted, or it might only have been locked by SELECT FOR UPDATE,
or it might be perfectly fine because the updating transaction rolled
back. You could get a limited amount of information by checking the
hint bits in t_infomask, but if the hint bits aren't set you won't know
what the row's state is.

You'd really need to do this in C using the tqual.c functions for the
results to be trustworthy.

I'm a bit surprised that contrib/pageinspect doesn't have a mechanism
for pulling out only committed-good heap tuples...

regards, tom lane

this function didn't work to know if a row can surely dead?

http://doxygen.postgresql.org/tqual_8c_source.html#l01236

>________________________________
> De: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
>Para: Kevin Grittner <kgrittn(at)mail(dot)com>
>CC: Alejandro Carrillo <fasterzip(at)yahoo(dot)es>; pgsql-general(at)postgresql(dot)org
>Enviado: Domingo 20 de enero de 2013 15:30
>Asunto: Re: [GENERAL] not(t_xmax = 0)
>
>"Kevin Grittner" <kgrittn(at)mail(dot)com> writes:
>> Alejandro Carrillo wrote:
>>> I try to do a function that let know which rows are deleted in a
>>> table using the xmax !=0
>
>> That's not something you're going to have much luck with by using
>> plpgsql to try to parse heap pages.
>
>No, because xmax being nonzero doesn't prove much: the row might be
>updated/deleted, or it might only have been locked by SELECT FOR UPDATE,
>or it might be perfectly fine because the updating transaction rolled
>back.  You could get a limited amount of information by checking the
>hint bits in t_infomask, but if the hint bits aren't set you won't know
>what the row's state is.
>
>You'd really need to do this in C using the tqual.c functions for the
>results to be trustworthy.
>
>I'm a bit surprised that contrib/pageinspect doesn't have a mechanism
>for pulling out only committed-good heap tuples...
>
>            regards, tom lane
>
>
>