Re: View deleted records in a table

On 03/26/2016 10:32 AM, Boyan Botev wrote:

        I want to view deleted records in table from a week ago in order to
        troubleshoot a data issue. The table has not been vacuumed yet.
        I was


    Are you sure?
    In other words do you have autovacuum turned off?

Autovacuum is on. The table is fairly static and the last vacuum analyze
was a month ago as part of an upgrade. pg_stat_user_tables does not show
any autovac counts or autovac timestamps since then. Based on that info
I assume any records deleted/updated last week should still be there.



        trying to use the pageinspect v1.4 extension but can't seem to
        convert
        the t_data to a readable record. Is there an easy way for me to
        get a


    What is t_data?

t_data is a column with the record data returned by function
heap_page_items from the extension pageinspect, assuming I have
understood the documentation correctly. I was told the extension may
allow me to view invisible/deleted records. It's my first time using it
and I am having trouble getting what I need. I was hoping someone else
has had a similar issue and figured out a solution.


    What version of Postgres?


I am running version 9.5.0.



        SQL to produce all system columns like xmin, xmax along with the
        visible
        and invisible records of the table in a readable form.

        I was hoping to find a setting similar to what Netezza has "set
        show_deleted_records=1;" to easily turn this visibility on or
        off, but
        it seems that feature was shot down several years ago. As a DBA
        I want
        to say that a feature like this is indispensible when
        troubleshooting
        data problems in large tables and environments where restores
        may not
        always be an option.


    That means keeping deleted records around for some indefinite period
    of time, which means table bloat.


Not necessarily. I am only interested in records since the last vacuum
run. Nothing extra in terms of storage is needed.

Aah, that is a different problem. On a heavily used table your time frame may be very short.

I have not tried, but:

http://www.depesz.com/2012/04/04/lets-talk-dirty/


https://github.com/omniti-labs/pgtreats/tree/master/contrib/pg_dirtyread

Thanks,
Boyan

--
Adrian Klaver
adrian.klaver@aklaver.com