System Columns in Tables

Every table in a Yellowbrick database contains four system columns that contain metadata:

ROWTXID: the transaction ID that created a row (BIGINT)
ROWUPDATED: a Boolean value that indicates whether a row has been modified
ROWUNIQUE: a monotonically increasing 64-bit number that uniquely identifies a row in a table (BIGINT)
ROWID: numeric values that identify rows, but may not always be unique

The first three columns are used internally to support backup and restore operations and database replication. The values in the ROWUNIQUE column are immutable; this column is a reliable means of determining unique rows in tables.

Important: ROWID values are not immutable and may change over time. Therefore, they are useful only within the scope of a single SQL statement, and not within transaction blocks. It is possible for a given row to have a different row ID in between different statements within the same transaction. Also, there is no direct correlation between row IDs and the order in which rows were added to the table; recently inserted rows may have IDs that are lower than IDs for previously inserted rows.

Values in all four system columns cannot be modified; however, they can be selected (and inserted into other tables if needed).

Examples with ROWUNIQUE, ROWTXID, and ROWUPDATED

Select the unique value for each row in the awayteam table:

premdb=# select rowunique, * from awayteam order by 1;
 rowunique | atid |          name           
-----------+------+-------------------------
         1 |   90 | Swindon Town
         2 |   91 | Tottenham Hotspur
         3 |   92 | Watford
         4 |   93 | West Bromwich Albion
         5 |   94 | West Ham United
         6 |   95 | Wigan Athletic
         7 |   96 | Wimbledon
         8 |   97 | Wolverhampton Wanderers
         9 |   98 | [NULL]
        10 |   99 | [NULL]
        11 |  100 | [NULL]
        12 |   51 | Arsenal
        13 |   52 | Aston Villa
        14 |   53 | Barnsley
...

Run queries that select ROWUNIQUE, ROWTXID, and ROWUPDATED from the newmatchstats table:

premdb=# select rowunique, rowtxid, rowupdated from newmatchstats order by 1 limit 5;
 rowunique | rowtxid | rowupdated 
-----------+---------+------------
         1 |   38656 | t
         2 |   38656 | t
         3 |   38656 | t
         4 |   38656 | t
         5 |   38656 | t
(5 rows)
 
premdb=# select count(*) from newmatchstats where rowupdated='f';
 count  
--------
 765934
(1 row)
 
premdb=# select count(distinct(rowtxid)) from newmatchstats;
 count 
-------
    90
(1 row)
 
premdb=# create table newmatchstats_rowunique as select rowunique as rownumber from newmatchstats;
SELECT 774540

Examples with ROWID Functions

Row IDs can be decomposed into information that is useful for monitoring database activity. For example, based on a row ID, you can find out which worker processed a particular row or where it is physically stored. Rows that have not yet been flushed to the column store also have valid row IDs, but they are displayed as negative values. You cannot select row IDs from external tables.

The following system functions provide ways to query and extract information from row IDs:

sys.worker_id_from_rowid(rowid): Return the worker ID for a row.
sys.filesys_number_from_rowid(rowid): Return the file system number (SSD) for a row.
sys.inode_number_from_rowid(rowid): Return the inode number (shard) for a row.
sys.row_number_from_row_id(rowid): Return the row number (unique per worker, SSD, and shard).

These functions accept either the string rowid as their input or a specific rowid value. In most cases you will use the string rowid to return the specific row ID for each row in a set of rows. (If you specify an individual row ID as the input to the function, make sure it is valid; the system does not detect row IDs that are not valid or do not exist.)

For example, the following query returns specific row IDs and row numbers for a set of rows in the match table:

premdb=# select rowid, sys.row_number_from_rowid(rowid),* from match where seasonid=21 limit 10;
      rowid       | row_number_from_rowid | seasonid |      matchday       | htid | atid | ftscore | htscore 
------------------+-----------------------+----------+---------------------+------+------+---------+---------
 1126011584388774 |                  7846 |       21 | 2013-02-23 00:00:00 |    2 |   52 | 2-1     | 1-0
 1126011584388775 |                  7847 |       21 | 2012-09-29 00:00:00 |    2 |   63 | 1-2     | 1-1
 1126011584388776 |                  7848 |       21 | 2013-04-16 00:00:00 |    2 |   67 | 0-0     | 0-0
 1126011584388777 |                  7849 |       21 | 2012-11-10 00:00:00 |    2 |   68 | 3-3     | 2-2
 1126011584388778 |                  7850 |       21 | 2013-01-30 00:00:00 |    2 |   73 | 2-2     | 0-1
 1126011584388779 |                  7851 |       21 | 2013-01-13 00:00:00 |    2 |   74 | 0-2     | 0-2
 1126011584388780 |                  7852 |       21 | 2013-04-28 00:00:00 |    2 |   75 | 1-1     | 1-1
 1126011584388781 |                  7853 |       21 | 2012-12-29 00:00:00 |    2 |   77 | 7-3     | 1-1
 1126011584388782 |                  7854 |       21 | 2013-04-13 00:00:00 |    2 |   78 | 3-1     | 0-0
 1126011584388783 |                  7855 |       21 | 2012-10-27 00:00:00 |    2 |   82 | 1-0     | 0-0
(10 rows)

The following example finds out where (on which worker node) the rows in the matchstats table are stored:

yellowbrick_test=# select sys.worker_id_from_rowid(rowid), count(*) 
from matchstats group by sys.worker_id_from_rowid(rowid) order by 1;
 worker_id_from_rowid | count 
----------------------+-------
                    0 | 24437
                    1 | 24244
                    2 | 24761
                    3 | 24361
                    4 | 24000
                    5 | 24348
                    6 | 24497
                    7 | 24270
                    8 | 24600
                    9 | 24665
                   10 | 24409
                   11 | 24359
                   12 | 24528
                   13 | 24176
                   14 | 24590
(15 rows)

To return the UUID for each worker instead of its logical ID, use the sys.worker_uuid function:

yellowbrick_test=# select sys.worker_uuid(sys.worker_id_from_rowid(rowid)), count(*) 
from crdm_giftcard group by sys.worker_id_from_rowid(rowid) order by sys.worker_id_from_rowid(rowid);
             worker_uuid              | count 
--------------------------------------+-------
 00000000-0000-0000-0000-38b8ebd00154 | 24437
 00000000-0000-0000-0000-38b8ebd000dc | 24244
 00000000-0000-0000-0000-38b8ebd002c6 | 24761
 00000000-0000-0000-0000-38b8ebd00069 | 24361
 00000000-0000-0000-0000-38b8ebd00041 | 24000
 00000000-0000-0000-0000-38b8ebd00091 | 24348
 00000000-0000-0000-0000-38b8ebd000aa | 24497
 00000000-0000-0000-0000-38b8ebd003c5 | 24270
 00000000-0000-0000-0000-38b8ebd0009b | 24600
 00000000-0000-0000-0000-38b8ebd00997 | 24665
 00000000-0000-0000-0000-38b8ebd002bc | 24409
 00000000-0000-0000-0000-38b8ebd003a2 | 24359
 00000000-0000-0000-0000-38b8ebd00028 | 24528
 00000000-0000-0000-0000-38b8ebd00212 | 24176
 00000000-0000-0000-0000-38b8ebd0025d | 24590
(15 rows)