CODEWITHSUNDEEP
amazon-redshift
learner
learner

Reputation: 877

How to check in redshift when was the last date the table was modified?

I have a table in redshift, for which I want to check if any records were inserted today? How to do this?

This shows something, but, can't seem to make it work.

     SELECT pg_xact_commit_timestamp(xmin), * FROM  <schema-name>.<tablename>;

Upvotes: 0

Views: 3895

Answers (1)

Bill Weiner
Bill Weiner

Reputation: 11082

I do not believe there is anyway to reliably do this.

A table on Redshift is distributed across all the nodes and slices, then divided into columns, then these columns are divided into blocks of 1MB in size. So you see a table is made up of a large number of blocks distributed around the cluster. The last modification time is the latest time that any of these blocks have been modified. There isn't one modification time for a table. If there exists modification times for every block in the database, and I don't think there is, this isn't available to the user.

Even if this was possible the data wouldn't be reliable as blocks are being rewritten by vacuum processes frequently. You really what the time of last "real to the business" data change. This is a business process specific definition so I'd recommend that a business process solution is where you want to go. Add a "record update" column to your table(s) and make its default be getdate(). This will it will have the time that the record was inserted (updates will need to set the column to getdate() in the update SQL explicitly).

Upvotes: 1

Related Questions