Обсуждение: View deleted records in a table

On 03/26/2016 09:08 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?

> 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?

What version of Postgres?

> 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.

>
> Thanks in advance for any help,
> Boyan
>


--
Adrian Klaver
adrian.klaver@aklaver.com

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

On 03/26/2016 12:47 PM, Boyan Botev wrote:
> 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.

You are not the only one:

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

"I would definitely add information about xmin/xmax to outputted columns
– this is required to be able to tell which row is latest, and which was
previous"


I would suggest filing an issue here:

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

> Thanks,
> Boyan
>

--
Adrian Klaver
adrian.klaver@aklaver.com