Which rows will commit?

[Deleted User] · January 2018

This blog post was authored by Jim Knicely.

Did you ever update a bunch of rows in a table, then forget which ones you changed? Fearing you might have updated an incorrect record, you might have to roll back and start again. Or, in Vertica you can first check which records have been modified prior to executing a COMMIT statement!

Example:

Say I have the following table data:

dbadmin=> SELECT * FROM what_will_be_committed ORDER BY primary_key_column;
primary_key_column | some_data
--------------------+-----------
                  1 | TEST1
                  2 | TEST2
                  3 | TEST3
                  4 | TEST4
                  5 | TEST5
                  6 | TEST6
                  7 | TEST7
                  8 | TEST8
                  9 | TEST9
                 10 | TEST10
(10 rows)

Now I update a few rows:

dbadmin=> UPDATE what_will_be_committed SET some_data = 'TEST5 - UPDATED' WHERE primary_key_column = 5;
OUTPUT
--------
      1
(1 row)

dbadmin=> UPDATE what_will_be_committed SET some_data = 'TEST9 - UPDATED' WHERE primary_key_column = 9;
OUTPUT
--------
      1
(1 row)

Having a particularly short short-term memory, I may have forgotten which records I updated. Luckily there is an easy way to list the rows that have been modified since the last COMMIT:

dbadmin=> SELECT primary_key_column FROM what_will_be_committed WHERE epoch IS NULL;
primary_key_column
--------------------
                  5
                  9
(2 rows)

Now I’m reminded that rows 5 and 9 will be committed by the next COMMIT statement.

Have fun!