Re: View deleted records in a table

Thanks, Adrian! That worked great for what I needed. I greatly appreciate
your help. Do you know if there is a way to also display system columns
like xmin, xmax with this extension. I can see the need for that in some
future investigation.
Thanks,
Boyan

On Sat, Mar 26, 2016 at 1:45 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 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(dot)klaver(at)aklaver(dot)com
>