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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sat, Mar 26, 2016 at 12:23 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

I want to view deleted records in table from a week ago i

The accepted method for handling deleted (or updated) records is to create
a TRIGGER (or RULE) on the table(s) that writes the record to a history
file after it has been deleted (or updated).

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

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.

Thanks,
Boyan

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

That works only if I know I will need the deleted/updated records for a
table ahead of time. I need this feature/ability as a way to perform "data
forensics" on a random table that someone has done something to. Next time
it may be another table. Basically I need a way to remove the visibility
map and show all records along with xmin and xmax alongside so I can tell
what records were there and what exactly happened.

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.klaver@aklaver.com>
wrote:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general