Обсуждение: not(t_xmax = 0)
Hi,
I need to filter xid != 0. I tried this "not(t_xmax = 0)" but I dont sure that this work ok
How I do it?
Create or replace function obtener_info_reg(tabla varchar, buscareliminado boolean, curs refcursor) returns void as
$$
declare
query_string varchar;
strwhere varchar;
strsql varchar;
blocksize integer := 8192;
begin
query_string:='delete from result_pag';
EXECUTE query_string;
for p in 0..pg_relpages(tabla)-1 loop
query_string:= '';
strsql:='insert into result_pag SELECT '||p||' page,*,
('||p||'*'|| blocksize ||') + lp_off posini_reg,
('||p||'*'|| blocksize ||') + lp_off + 17 posini_infomask,
(t_infomask-1024) t_infomask_old, to_hex(t_infomask) t_infomask_hex,
to_hex(t_infomask-1024) t_infomask_old
FROM heap_page_items(get_raw_page(''' || tabla || ''', '||p||'))';
strwhere:='';
if buscareliminado= true then
--add where t_max != 0 to query
strwhere:= ' where not(t_xmax = 0)';
end if;
query_string:= query_string || strsql || strwhere;
EXECUTE query_string;
end loop;
OPEN curs FOR EXECUTE 'select * from result_pag ';
end;
$$ language plpgsql;
Alejandro Carrillo wrote: > I need to filter xid != 0. I tried this "not(t_xmax = 0)" but I > dont sure that this work ok > > How I do it? Please give a high-level description of what you are trying to accomplish and why. I didn't find it at all clear from your sample function, -Kevin
I try to do a function that let know which rows are deleted in a table using the xmax !=0
De: Kevin Grittner <kgrittn@mail.com>
Para: Alejandro Carrillo <fasterzip@yahoo.es>; pgsql-general@postgresql.org
Enviado: Domingo 20 de enero de 2013 12:19
Asunto: Re: [GENERAL] not(t_xmax = 0)
Alejandro Carrillo wrote:
> I need to filter xid != 0. I tried this "not(t_xmax = 0)" but I
> dont sure that this work ok
>
> How I do it?
Please give a high-level description of what you are trying to
accomplish and why. I didn't find it at all clear from your sample
function,
-Kevin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Alejandro Carrillo wrote: > De: Kevin Grittner <kgrittn@mail.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@mail.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@sss.pgh.pa.us>
Para: Kevin Grittner <kgrittn@mail.com>
CC: Alejandro Carrillo <fasterzip@yahoo.es>; pgsql-general@postgresql.org
Enviado: Domingo 20 de enero de 2013 15:30
Asunto: Re: [GENERAL] not(t_xmax = 0)
"Kevin Grittner" <kgrittn@mail.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
Alejandro Carrillo wrote: > this function didn't work to know if a row can surely dead? > > http://doxygen.postgresql.org/tqual_8c_source.html#l01236 Sure, as long as you call it after HeapTupleSatisfiesMVCC(), as the comment specifies. Also note that not all deleted or updated tuples will be retained for very long; HOT pruning and autovacuum can clean up dead tuples at unpredictable times. But if you want to know which among the old tuples that are still in the heap will go away when the next vacuum against the table runs, that would be one way to do it. -Kevin