How do I write a Postgres SQL command based on metadata of the tables themselves?

Question

I want to retrieve all the records that were modified in the past hour of running the command. It is purely a meta data command (with no regard to the content of any tabular column data). I want to identify tables and rows of those tables that had some DML modification with a specified temporal duration.

Does Postgres need special configuration to track such detailed meta data before I run a command to return the info I am looking for?

What query would I run to get the results I am looking for?

Answer 1

Postgres 9.5 introduced a feature to record commit timestamps. The manual:

track_commit_timestamp (bool)

Record commit time of transactions. This parameter can only be set in postgresql.conf file or on the server command line. The default value is off.

Some functions to work with it were added, too.

Once the DB server is restarted with this setting activated, Postgres tracks commit timestamps. Then, for example, to get all rows from a table that were changed in any way during the last 4 hours:

SELECT * FROM tbl
WHERE pg_xact_commit_timestamp(xmin) >= now() - interval '4 hours';

Related:

• How list all tables with data changes in the last 24 hours?
• Atomically set SERIAL value when committing transaction