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