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;

Search Discussions

  • Kevin Grittner at Jan 20, 2013 at 5:19 pm

    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
  • Alejandro Carrillo at Jan 20, 2013 at 7:22 pm
    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

  • Kevin Grittner at Jan 20, 2013 at 8:08 pm

    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
  • Tom Lane at Jan 20, 2013 at 8:30 pm

    "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 at Jan 21, 2013 at 1:36 am
    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

  • Kevin Grittner at Jan 21, 2013 at 4:11 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedJan 20, '13 at 1:46p
activeJan 21, '13 at 4:11p
posts7
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase