blog
Understanding System Columns in PostgreSQL
So you sit with your hands over a keyboard and think “what fun I can have to make my lifetime even curiouser?..” Well – create a table of course!
vao=# create table nocol();
CREATE TABLE
vao=# select * from nocol;
--
(0 rows)What fun is it about a table with no data?.. Absolutely none! But I can easily fix it:
vao=# insert into nocol default values;
INSERT 0 1It looks weird and quite stupid to have a table with no columns and one row. Not to mention it is not clear what “default values” there were inserted… Well – reading few lines from docs reveals that “All columns will be filled with their default values.” Yet I have no columns! Well – I surely have some:
vao=# select attname, attnum, atttypid::regtype, attisdropped::text from pg_attribute where attrelid = 'nocol'::regclass;
attname | attnum | atttypid | attisdropped
----------+--------+----------+--------------
tableoid | -7 | oid | false
cmax | -6 | cid | false
xmax | -5 | xid | false
cmin | -4 | cid | false
xmin | -3 | xid | false
ctid | -1 | tid | false
(6 rows)So these six are definitely not the ALTER TABLE DROP COLUMN zombies because attisdropped is false. Also I see that the type name of those columns end up with “id”. Reading the bottom section of Object Identifier Types will give the idea. Another funny observation is – the -2 is missing! I wonder where I could have lost it – I just created a table after all! Hm, what object identifier is missing in my table? By definition I mean. I have tuple, command and xact ids. Well unless some “global over whole db identifier”, like oid?.. Checking is easy – I will create table with OIDS:
vao=# create table nocol_withoid() with oids;
CREATE TABLE
vao=# select attname, attnum, atttypid::regtype, attisdropped::text from pg_attribute where attrelid = 'nocol_withoid'::regclass;
attname | attnum | atttypid | attisdropped
----------+--------+----------+--------------
tableoid | -7 | oid | false
cmax | -6 | cid | false
xmax | -5 | xid | false
cmin | -4 | cid | false
xmin | -3 | xid | false
oid | -2 | oid | false
ctid | -1 | tid | false
(7 rows)Voila! So the missing -2 is missing indeed and we like it. Spending oids for used data rows would be a bad idea, so I’ll keep playing with a table without OIDS.
What I have? I have 6 attributes after creating “no column table” with (oids=false). Should I use system columns? If so, why they are kind of hidden? Well – I would assume they are not so broadly advertised, because the usage is not intuitive and behaviour can change in future. For instance after seeing tuple id (ctid) some might think “ah – this is sort of internal PK” (and it kind of is):
vao=# select ctid from nocol;
ctid
-------
(0,1)
(1 row)First digits (zero) stand for the page number and the second (one) stand for the tuple number. They are sequential:
vao=# insert into nocol default values;
INSERT 0 1
vao=# select ctid from nocol;
ctid
-------
(0,1)
(0,2)
(2 rows)But this sequence won’t help you to define even which row arrived after which:
vao=# alter table nocol add column i int;
ALTER TABLE
vao=# update nocol set i = substring(ctid::text from 4 for 1)::int;
UPDATE 2
vao=# select i, ctid from nocol;
i | ctid
---+-------
1 | (0,3)
2 | (0,4)
(2 rows)Here I added a column (to identify my rows) and filled it with initial tuple number (mind both rows were physically moved)
vao=# delete from nocol where ctid = '(0,3)';
DELETE 1
vao=# vacuum nocol;
VACUUM
vao=# insert into nocol default values;
INSERT 0 1
vao=# select i, ctid from nocol;
i | ctid
---+-------
| (0,1)
2 | (0,4)
(2 rows)Aha! (said with rising intonation) – here I deleted one of my rows, let out the vacuum on the poor table and inserted a new row. The result – the later added row is in the first page first tuple, because Postgres wisely decided to save the space and reuse the freed up space.
So the idea to use ctid for getting the sequence of rows introduced looks bad. Up to some level – if you work in one transaction the sequence remains – newly affected rows on same table will have “larger” ctid. Of course after vacuum (autovacuum) or if you’re lucky enough to have HOT updates earlier or just released gaps will be reused – breaking the sequential order. But fear not – there were six hidden attributes, not one!
vao=# select i, ctid, xmin from nocol;
i | ctid | xmin
---+-------+-------
| (0,1) | 26211
2 | (0,4) | 26209
(2 rows)If I check the xmin, I will see that the transaction id that introduced the last inserted row is (+2) higher (+1 was the deleted row). So for sequential row identifier I might use totally different attribute! Of course it’s not this simple, otherwise such usage would be encouraged. The xmin column before 9.4 was actually overwritten to protect from xid wraparound. Why so complicated? The MVCC in Postgres is very smart and methods around it get better over time. Of course it brings complexity. Alas. Some people even want to avoid system columns. Double alas. Because system columns are cool and well documented. The very top attribute (remember I skip oids) is tableoid:
vao=# select i, tableoid from nocol;
i | tableoid
---+----------
| 253952
2 | 253952
(2 rows)It looks useless having SAME value in every row – doesn’t it? And yet a while ago it was very popular attribute – when we were all building partitioning using rules and inherited tables. How would you debug which table the row is coming from if not with tableoid? So when you use rules, views (same rules) or UNION the tableoid attribute helps you identify the source:
vao=# insert into nocol_withoid default values;
INSERT 253967 1
vao=# select ctid, tableoid from nocol union select ctid, tableoid from nocol_withoid ;
ctid | tableoid
-------+----------
(0,1) | 253952
(0,1) | 253961
(0,4) | 253952
(3 rows)Wow what was that? I have got so much used to see INSERT 0 1 that my psql output looked weird! Ah – true – I created a table with oids and just desperately pointlessly used one (253967) identifier! Well – not completely pointlessly (though desperately) – the select returns two rows with same ctid (0,1) – not surprising – I’m selecting from two tables and then adding results one to another, so the chance to have the same ctid is not that low. The last thing to mention is I can again use object identifier types to show it pretty:
vao=# select ctid, tableoid::regclass from nocol union select ctid, tableoid from nocol_withoid ;
ctid | tableoid
-------+---------------
(0,1) | nocol
(0,1) | nocol_withoid
(0,4) | nocol
(3 rows)Aha! (said with rising intonation) – So that’s the way to clearly pin the data source here!
Finally another very popular and interesting usage – defining which row was inserted and which upserted:
vao=# update nocol set i = 0 where i is null;
UPDATE 1
vao=# alter table nocol alter COLUMN i set not null;
ALTER TABLE
vao=# alter table nocol add constraint pk primary key (i);
ALTER TABLENow that we have a PK, I can use ON CONFLICT directive:
vao=# insert into nocol values(0),(-1) on conflict(i) do update set i = extract(epoch from now()) returning i, xmax;
i | xmax
------------+-----------
1534433974 | 26281
-1 | 0
(2 rows)Why so happy? Because I can tell (with some confidentiality) that row with xmax not equal to zero that it was updated. And don’t think it’s obvious – it looks so just because I used unixtime for PK, so it looks really different from one digit values. Imagine you do such ON CONFLICT twist on big set and there is no logical way to identify which value had conflict and which – not. xmax helped tonnes of DBAs in hard times. And the best description of how it works I would recommend here – just as I would recommend all three discussion participants (Abelisto, Erwin and Laurenz) to be read on other postgres tag questions and answers on SO.
That’s it.
tableoid, xmax, xmin and ctid are good friends of any DBA. Not to insult cmax, cmin and oid – the are just as good friends too! But this is enough for a small review and I want to get my hands off the keyboard now.
