Error tables declared with oids are not supported

My site is no longer loading with a recent upgrade for PostgreSQL.

WITH OIDs has actually been deprecated since 2005. There was a mailing list
announcement from 2018 that has some details:
https://www.postgresql.org/message-id/E1gPG3j-0003cy-03%40gemulon.postgresql.org

== ERROR-REPORT (DEBUGGING ENABLED) ==
== (When you copy this debug output to a forum or other places, make sure to remove your username/passwords, as they may be contained within function calls) ==

#0  errorToExceptionHandler(2, pg_query(): Query failed: ERROR:  tables declared WITH OIDS are not supported, /srv/www/serendipity/include/db/postgres.inc.php, 224)
#1  pg_query(Resource id #21, SET default_with_oids = true) called at [/srv/www/serendipity/include/db/postgres.inc.php:224]
#2  serendipity_db_query(SET default_with_oids = true, 1, both, , , , 1) called at [/srv/www/serendipity/include/db/postgres.inc.php:218]
#3  serendipity_db_query(SELECT name, value
                                        FROM serendipity_config
                                        WHERE authorid = 0) called at [/srv/www/serendipity/include/functions_config.inc.php:314]
#4  serendipity_load_configuration() called at [/srv/www/serendipity/serendipity_config.inc.php:339]
#5  include(/srv/www/serendipity/serendipity_config.inc.php) called at [/srv/www/serendipity/serendipity_admin.php:9]

Warning: pg_query(): Query failed: ERROR: tables declared WITH OIDS are not supported in /srv/www/serendipity/include/db/postgres.inc.php on line 224.

 == ERROR-REPORT (DEBUGGING ENABLED) == 

 == (When you copy this debug output to a forum or other places, make sure to remove your username/passwords, as they may be contained within function calls) == 

#0  errorToExceptionHandler(1, Uncaught ErrorException: Warning: pg_query(): Query failed: ERROR:  tables declared WITH OIDS are not supported in /srv/www/serendipity/include/db/postgres.inc.php:224
Stack trace:
#0 [internal function]: errorToExceptionHandler()
#1 /srv/www/serendipity/include/db/postgres.inc.php(224): pg_query()
#2 /srv/www/serendipity/include/db/postgres.inc.php(218): serendipity_db_query()
#3 /srv/www/serendipity/include/functions_config.inc.php(314): serendipity_db_query()
#4 /srv/www/serendipity/serendipity_config.inc.php(339): serendipity_load_configuration()
#5 /srv/www/serendipity/serendipity_admin.php(9): include('...')
#6 {main}
  thrown, /srv/www/serendipity/include/db/postgres.inc.php, 224) called at [/srv/www/serendipity/include/compat.inc.php:254]
#1  fatalErrorShutdownHandler()

Fatal Error: Uncaught ErrorException: Warning: pg_query(): Query failed: ERROR:  tables declared WITH OIDS are not supported in /srv/www/serendipity/include/db/postgres.inc.php:224
Stack trace:
#0 [internal function]: errorToExceptionHandler()
#1 /srv/www/serendipity/include/db/postgres.inc.php(224): pg_query()
#2 /srv/www/serendipity/include/db/postgres.inc.php(218): serendipity_db_query()
#3 /srv/www/serendipity/include/functions_config.inc.php(314): serendipity_db_query()
#4 /srv/www/serendipity/serendipity_config.inc.php(339): serendipity_load_configuration()
#5 /srv/www/serendipity/serendipity_admin.php(9): include('...')
#6 {main}
  thrown in /srv/www/serendipity/include/db/postgres.inc.php on line 224.

Once unpublished, all posts by rafaelbernard will become hidden and only accessible to themselves.

If rafaelbernard is not suspended, they can still re-publish their posts from their dashboard.

Note:

Once unpublished, this post will become invisible to the public and only accessible to Rafael Bernard Araújo.

They can still re-publish the post if they are not suspended.

Thanks for keeping DEV Community 👩‍💻👨‍💻 safe. Here is what you can do to flag rafaelbernard:

Make all posts by rafaelbernard less visible

rafaelbernard consistently posts content that violates DEV Community 👩‍💻👨‍💻’s
code of conduct because it is harassing, offensive or spammy.

PostgreSQL online editor

Write, Run & Share PostgreSQL queries online using OneCompiler’s PostgreSQL online editor and compiler for free. It’s one of the robust, feature-rich online editor and compiler for PostgreSQL. Getting started with the OneCompiler’s PostgreSQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose database as ‘PostgreSQL’ and start writing queries to learn and test online without worrying about tedious process of installation.

About PostgreSQL

PostgreSQL is a open source relational database system and is also knows as Postgres.

Key Features:

• Postgres is not only free and open-source but also it is highly extensible.
• Custom Data types and funtions from various programming languaues can be introduced and the good part is compiling entire database is not required.
• ACID(Atomicity, Consistency, Isolation, Durability) compliant.
• First DBMS which implemented Multi-version concurrency control (MVCC) feature.
• It’s the default database server for MacOS.
• It supports all major operating systems like Linux, Windows, OpenBSD,FreeBSD etc.

Syntax help

1. CREATE

CREATE command is used to create a table, schema or an index.

Syntax:

         CREATE TABLE table_name (
                column1 datatype,
                column2 datatype,
                ....);

2. ALTER

ALTER command is used to add, modify or delete columns or constraints from the database table.

Syntax

ALTER TABLE Table_name ADD column_name datatype;

3. TRUNCATE:

TRUNCATE command is used to delete the data present in the table but this will not delete the table.

Syntax

TRUNCATE table table_name;

4. DROP

DROP command is used to delete the table along with its data.

Syntax

DROP TABLE table_name;

5. RENAME

RENAME command is used to rename the table name.

Syntax

ALTER TABLE table_name1 RENAME to new_table_name1; 

6. INSERT

INSERT Statement is used to insert new records into the database table.

Syntax

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

7. SELECT

Select statement is used to select data from database tables.

Syntax:

SELECT column1, column2, ...
FROM table_name; 

8. UPDATE

UPDATE statement is used to modify the existing values of records present in the database table.

Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition; 

9. DELETE

DELETE statement is used to delete the existing records present in the database table.

Syntax

DELETE FROM table_name where condition;

Let’s go into details:

tableoid

The tableoid is the OID from the table where the row is originally coming from. In a simple table, this is just the OID of the table itself.

oid=# CREATE TABLE show_oid (id INT);
CREATE TABLE
oid=# SELECT 'public.show_oid'::regclass::oid;
  oid  
-------
 79824
(1 row)

oid=# INSERT INTO show_oid VALUES (1), (2);
INSERT 0 2
oid=# SELECT tableoid, id FROM show_oid;
 tableoid | id 
----------+----
    79824 |  1
    79824 |  2
(2 rows)

All rows are coming from the table with the oid = 79824.

This changes once partitioning is used. In PostgreSQL, a partition is an inherited table. Simply said, the data lives in a child table (the partition), and when the parent table is queried, PostgreSQL also searches all child tables for data. Here is an example with partitions. First let’s create a parent table, and partition it by range:

oid=# DROP TABLE IF EXISTS show_oid;
DROP TABLE
oid=# CREATE TABLE show_oid (id INT) PARTITION BY RANGE (id);
CREATE TABLE
oid=# CREATE TABLE show_oid_p1 PARTITION OF show_oid
oid-#    FOR VALUES FROM (1) TO (10);
CREATE TABLE
oid=# CREATE TABLE show_oid_p2 PARTITION OF show_oid
oid-#    FOR VALUES FROM (10) TO (20);
CREATE TABLE
oid=# CREATE TABLE show_oid_p3 PARTITION OF show_oid
oid-#    FOR VALUES FROM (20) TO (30);
CREATE TABLE
oid=# SELECT 'public.show_oid'::regclass::oid AS show_oid,
oid-#        'public.show_oid_p1'::regclass::oid AS show_oid_p1,
oid-#        'public.show_oid_p2'::regclass::oid AS show_oid_p2,
oid-#        'public.show_oid_p3'::regclass::oid AS show_oid_p3;
 show_oid | show_oid_p1 | show_oid_p2 | show_oid_p3 
----------+-------------+-------------+-------------
    79827 |       79830 |       79833 |       79836
(1 row)

We can see that each partition has a different oid. Now let’s insert some data:

oid=# INSERT INTO show_oid VALUES (1), (2), (9), (10), (11), (19), (20), (21), (29);
INSERT 0 9
oid=# SELECT tableoid, id FROM show_oid ORDER BY tableoid, id;
 tableoid | id 
----------+----
    79830 |  1
    79830 |  2
    79830 |  9
    79833 | 10
    79833 | 11
    79833 | 19
    79836 | 20
    79836 | 21
    79836 | 29
(9 rows)

Even though the data is selected from the parent table (show_oid, oid = 79827), there is not a single row returning data from this table. All the data comes from one of the partitions. During inserting the data, PostgreSQL moved the data into the partition, according to the partition key. The parent table stays empty:

oid=# SELECT tableoid, id FROM ONLY show_oid ORDER BY tableoid, id;
 tableoid | id 
----------+----
(0 rows)

The ONLY keyword here tells PostgreSQL not to scan any inherited tables. Since the parent table is empty, no rows are returned.

xmin + xmax

The xmin and xmax columns are holding transaction IDs. xmin shows when the row becomes visible (minimum required transaction ID), xmax shows when the row is no longer visible (maximum valid transaction ID). In a relational database like PostgreSQL, there can be many ongoing transactions at the same time. Every transaction has it’s own transaction ID, which can be queried:

oid=# SELECT txid_current();
 txid_current 
--------------
         7034
(1 row)

oid=# SELECT txid_current();
 txid_current 
--------------
         7035
(1 row)

The example above is wasting two transaction IDs: every command in PostgreSQL is wrapped in a transaction. If you don’t start a transaction, PostgreSQL will automatically wrap every command in a single transaction. That is the reason why the transaction ID in the example is increasing with every call of the txid_current() function.

Back to our problem: every transaction can change data. The transaction isolation level specifies which transaction can see which changes. Once data is committed, every later transaction can always see the changes — that part is easy. For ongoing transactions, the transaction ID specifies which changes become visible at which point. Let’s look at an example. First we need a table:

oid=# CREATE TABLE show_xmin_xmax (id INT PRIMARY KEY);
CREATE TABLE

Then we start a transaction and insert data:

oid=# BEGIN;
BEGIN
oid=# SELECT txid_current();
 txid_current 
--------------
         7058
(1 row)

oid=# INSERT INTO show_xmin_xmax VALUES (1), (2);
INSERT 0 2
oid=# SELECT xmin, xmax, id FROM show_xmin_xmax ORDER BY id;
 xmin | xmax | id 
------+------+----
 7058 |    0 |  1
 7058 |    0 |  2
(2 rows)

oid=# COMMIT;
COMMIT

The transaction ID is 7058, and this value appears in the xmin column. The xmax column is 0, because this data is not deleted (or updated).

oid=# BEGIN;
BEGIN
oid=# SELECT txid_current();
 txid_current 
--------------
         7059
(1 row)

oid=# SELECT xmin, xmax, id FROM show_xmin_xmax ORDER BY id;
 xmin | xmax | id 
------+------+----
 7058 |    0 |  1
 7058 |    0 |  2
(2 rows)

oid=# UPDATE show_xmin_xmax SET id = 3 WHERE id = 1;
UPDATE 1
oid=# SELECT xmin, xmax, id FROM show_xmin_xmax ORDER BY id;
 xmin | xmax | id 
------+------+----
 7058 |    0 |  2
 7059 |    0 |  3
(2 rows)

oid=# COMMIT;
COMMIT

Once the data is updated, a new row version appears, with xmin = 7059. What we can’t see here is that the old row (id = 1) is also still in the table, however the xmax value is now set to 7059 as well, and becomes invisible to the current and any newer transaction. Right now we can’t see this deleted row, however it is still there. Later in this blog post we learn how to make this data visible.

There is however a trick to make the changes visible when deleting data:

oid=# BEGIN;
BEGIN
oid=# SELECT txid_current();
 txid_current 
--------------
         7060
(1 row)

oid=# SELECT xmin, xmax, id FROM show_xmin_xmax ORDER BY id;
 xmin | xmax | id 
------+------+----
 7058 |    0 |  2
 7059 |    0 |  3
(2 rows)

oid=# DELETE FROM show_xmin_xmax WHERE id = 2 RETURNING xmin, xmax, *;
 xmin | xmax | id 
------+------+----
 7058 | 7060 |  2
(1 row)

DELETE 1
oid=# SELECT xmin, xmax, id FROM show_xmin_xmax ORDER BY id;
 xmin | xmax | id 
------+------+----
 7059 |    0 |  3
(1 row)

oid=# COMMIT;
COMMIT

The RETURNING clause for DELETE can also return the xmin and xmax columns. As we can see in this example, the returnes data has xmax set to 7060 — the current transaction ID. Afterwards the deleted data is no longer visible for regular queries.

cmin + cmax

Like xmin and xmax, cmin and cmax store transaction information. However these two columns store the command sequence number inside the transaction. Imagine you start a transaction and execute multiple commands: every time a command changes data, this changes are stored with an increased cmin and cmax information. This is useful to trace back which changes happened at which point during a transaction, and it helps replaying changes in the correct order. Example table:

oid=# CREATE TABLE show_cmin_cmax (id INT PRIMARY KEY);
CREATE TABLE

Let’s insert some data, but this time using multiple commands in a transaction:

oid=# BEGIN;
BEGIN
oid=# SELECT txid_current();
 txid_current 
--------------
         7042
(1 row)

oid=# INSERT INTO show_cmin_cmax VALUES (1), (2);
INSERT 0 2
oid=# INSERT INTO show_cmin_cmax VALUES (3), (4);
INSERT 0 2
oid=# INSERT INTO show_cmin_cmax VALUES (5), (6);
INSERT 0 2
oid=# SELECT xmin, xmax, cmin, cmax, id FROM show_cmin_cmax order by id;
 xmin | xmax | cmin | cmax | id 
------+------+------+------+----
 7042 |    0 |    0 |    0 |  1
 7042 |    0 |    0 |    0 |  2
 7042 |    0 |    1 |    1 |  3
 7042 |    0 |    1 |    1 |  4
 7042 |    0 |    2 |    2 |  5
 7042 |    0 |    2 |    2 |  6
(6 rows)

oid=# COMMIT;
COMMIT

We can see that xmin for all rows is 7042, however cmin and cmax are increased with every INSERT. Data which was inserted using the same INSERT command has the same cmin and cmax number. Let’s update some data:

oid=# BEGIN;
BEGIN
oid=# SELECT txid_current();
 txid_current 
--------------
         7043
(1 row)

oid=# SELECT xmin, xmax, cmin, cmax, id FROM show_cmin_cmax order by id;
 xmin | xmax | cmin | cmax | id 
------+------+------+------+----
 7042 |    0 |    0 |    0 |  1
 7042 |    0 |    0 |    0 |  2
 7042 |    0 |    1 |    1 |  3
 7042 |    0 |    1 |    1 |  4
 7042 |    0 |    2 |    2 |  5
 7042 |    0 |    2 |    2 |  6
(6 rows)

oid=# UPDATE show_cmin_cmax SET id = 10 WHERE id = 1;
UPDATE 1
oid=# UPDATE show_cmin_cmax SET id = 20 WHERE id = 2;
UPDATE 1
oid=# SELECT xmin, xmax, cmin, cmax, id FROM show_cmin_cmax order by id;
 xmin | xmax | cmin | cmax | id 
------+------+------+------+----
 7042 |    0 |    1 |    1 |  3
 7042 |    0 |    1 |    1 |  4
 7042 |    0 |    2 |    2 |  5
 7042 |    0 |    2 |    2 |  6
 7043 |    0 |    0 |    0 | 10
 7043 |    0 |    1 |    1 | 20
(6 rows)

oid=# COMMIT;
COMMIT

We can see how the transaction ID in xmin changes, because the UPDATE happens in a new transaction (7043). And the cmin and cmax again start at zero. Let’s also try the trick with DELETE and RETURNING:

oid=# BEGIN;
BEGIN
oid=# SELECT txid_current();
 txid_current 
--------------
         7044
(1 row)

oid=# SELECT xmin, xmax, cmin, cmax, id FROM show_cmin_cmax order by id;
 xmin | xmax | cmin | cmax | id 
------+------+------+------+----
 7042 |    0 |    1 |    1 |  3
 7042 |    0 |    1 |    1 |  4
 7042 |    0 |    2 |    2 |  5
 7042 |    0 |    2 |    2 |  6
 7043 |    0 |    0 |    0 | 10
 7043 |    0 |    1 |    1 | 20
(6 rows)

oid=# DELETE FROM show_cmin_cmax WHERE id = 3 RETURNING xmin, xmax, cmin, cmax, *;
 xmin | xmax | cmin | cmax | id 
------+------+------+------+----
 7042 | 7044 |    0 |    0 |  3
(1 row)

DELETE 1
oid=# DELETE FROM show_cmin_cmax WHERE id = 4 RETURNING xmin, xmax, cmin, cmax, *;
 xmin | xmax | cmin | cmax | id 
------+------+------+------+----
 7042 | 7044 |    1 |    1 |  4
(1 row)

DELETE 1
oid=# SELECT xmin, xmax, cmin, cmax, id FROM show_cmin_cmax order by id;
 xmin | xmax | cmin | cmax | id 
------+------+------+------+----
 7042 |    0 |    2 |    2 |  5
 7042 |    0 |    2 |    2 |  6
 7043 |    0 |    0 |    0 | 10
 7043 |    0 |    1 |    1 | 20
(4 rows)

oid=# COMMIT;
COMMIT

Because both deletes happen in the same transaction (7044), both returned rows show this transaction ID as xmax. cmin and cmax is increasing for every command.

ctid

The ctid column consists of two parts: the page or block in the table, and the position in this page. Let’s show at a slightly more complex example:

oid=# CREATE TABLE show_ctid (id INT PRIMARY KEY, data TEXT);
CREATE TABLE
oid=# ALTER TABLE show_ctid ALTER COLUMN data SET STORAGE plain;
ALTER TABLE
oid=# INSERT INTO show_ctid VALUES (1, 'Lorem'), (2, 'ipsum'), (3, 'dolor'), (4, 'sit'), (5, 'amet');
INSERT 0 5

This table has an integer as primary key, and a text field as data field. The first 5 words from Lorem ipsum are used as text. The storage type for the column is changed to «plain», to keep the data uncompressed and in the table itself, and don’t «outsource» it into a TOAST table.

oid=# d+ show_ctid
                                 Table "public.show_ctid"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------+-----------+----------+---------+---------+--------------+-------------
 id     | integer |           | not null |         | plain   |              | 
 data   | text    |           |          |         | plain   |              | 
Indexes:
    "show_ctid_pkey" PRIMARY KEY, btree (id)
Access method: heap

Let’s look at the ctid:

oid=# SELECT ctid, id, data FROM show_ctid order by id;
 ctid  | id | data  
-------+----+-------
 (0,1) |  1 | Lorem
 (0,2) |  2 | ipsum
 (0,3) |  3 | dolor
 (0,4) |  4 | sit
 (0,5) |  5 | amet
(5 rows)

Page 0, positions 1 to 5. But that’s not really helpful, isn’t it? Luckily there is a very useful extension which allows us to peek into the details: pageinspect. It comes in PostgreSQL contrib, and should be available on every system. You need to install it:

oid=# CREATE EXTENSION IF NOT EXISTS pageinspect;
CREATE EXTENSION

Now we can use the get_raw_page() function to select the page, and decode it using heap_page_items():

oid=# SELECT *
oid-#   FROM heap_page_items(get_raw_page('show_ctid', 0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |         t_data         
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------------------
  1 |   8152 |        1 |     34 |   7049 |      0 |        0 | (0,1)  |           2 |       2306 |     24 |        |       | x010000000d4c6f72656d
  2 |   8112 |        1 |     34 |   7049 |      0 |        0 | (0,2)  |           2 |       2306 |     24 |        |       | x020000000d697073756d
  3 |   8072 |        1 |     34 |   7049 |      0 |        0 | (0,3)  |           2 |       2306 |     24 |        |       | x030000000d646f6c6f72
  4 |   8040 |        1 |     32 |   7049 |      0 |        0 | (0,4)  |           2 |       2306 |     24 |        |       | x0400000009736974
  5 |   8000 |        1 |     33 |   7049 |      0 |        0 | (0,5)  |           2 |       2306 |     24 |        |       | x050000000b616d6574
(5 rows)

There is t_ctid, which is the position in the page. There are a couple more details, described here. And there is t_data, which holds the row data. For decoding the data, we need to have a bit of knowledge what this data is. First of all, the data is a bytea, and is therefore hex-encoded. This can be decoded:

oid=# SELECT *,
oid-#        encode(t_data, 'escape') AS decoded
oid-#   FROM heap_page_items(get_raw_page('show_ctid', 0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |         t_data         |           decoded           
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------------------+-----------------------------
  1 |   8152 |        1 |     34 |   7049 |      0 |        0 | (0,1)  |           2 |       2306 |     24 |        |       | x010000000d4c6f72656d | x01000000rLorem
  2 |   8112 |        1 |     34 |   7049 |      0 |        0 | (0,2)  |           2 |       2306 |     24 |        |       | x020000000d697073756d | x02000000ripsum
  3 |   8072 |        1 |     34 |   7049 |      0 |        0 | (0,3)  |           2 |       2306 |     24 |        |       | x030000000d646f6c6f72 | x03000000rdolor
  4 |   8040 |        1 |     32 |   7049 |      0 |        0 | (0,4)  |           2 |       2306 |     24 |        |       | x0400000009736974     | x04000000        sit
  5 |   8000 |        1 |     33 |   7049 |      0 |        0 | (0,5)  |           2 |       2306 |     24 |        |       | x050000000b616d6574   | x05000000x0Bamet
(5 rows)

That makes a bit more sense, but not much more. For PostgreSQL, the entire t_data field is the data from each column combined. And since the table structure is known, there is no separator required. Let’s have a look at the table structure again:

1. id INT
2. data TEXT

Integers are 4 bytes wide, which means we need to look at the first 4 bytes:

• 01000000 -> 01 00 00 00
• 02000000 -> 02 00 00 00
• 03000000 -> 03 00 00 00
• 04000000 -> 04 00 00 00

This system is little-endian, which means that the least significant byte is stored at the smallest (first) address. These 4 numbers are the primary key in the id field, expanded to 32 bit (4 bytes), because it’s an integer.

But what about this part here: «rLorem«? That’s a bit more complicated.

It’s a text field (variable length). The text in PostgreSQL is a C struct: a length field vl_len, which is — usually — 4 bytes long. And a data field vl_dat, which can hold up to 1 GB of data. Specifying the length allows PostgreSQL to store arbitrary data, including the famous 00 in C. However it’s more complicated than that, and vl_len holds more information. We need to look at this field in a bit representation:

oid=# SELECT *,
oid-#        encode(t_data, 'escape') AS decoded,
oid-#        ('x' || encode(SUBSTR(t_data, 5, 1), 'hex'))::BIT(8) AS bits
oid-#   FROM heap_page_items(get_raw_page('show_ctid', 0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |         t_data         |           decoded           |   bits   
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------------------+-----------------------------+----------
  1 |   8152 |        1 |     34 |   7049 |      0 |        0 | (0,1)  |           2 |       2306 |     24 |        |       | x010000000d4c6f72656d | x01000000rLorem     | 00001101
  2 |   8112 |        1 |     34 |   7049 |      0 |        0 | (0,2)  |           2 |       2306 |     24 |        |       | x020000000d697073756d | x02000000ripsum     | 00001101
  3 |   8072 |        1 |     34 |   7049 |      0 |        0 | (0,3)  |           2 |       2306 |     24 |        |       | x030000000d646f6c6f72 | x03000000rdolor     | 00001101
  4 |   8040 |        1 |     32 |   7049 |      0 |        0 | (0,4)  |           2 |       2306 |     24 |        |       | x0400000009736974     | x04000000        sit | 00001001
  5 |   8000 |        1 |     33 |   7049 |      0 |        0 | (0,5)  |           2 |       2306 |     24 |        |       | x050000000b616d6574   | x05000000x0Bamet    | 00001011
(5 rows)

When the lowest order bit is set (on little-endian), the length field is only the remaining 7 bits, not the entire 4 bytes. It also means that the remaining data is up to 127 bytes long (2^7 — 1 = 127), the total length includes the byte for the length. This is a shortcut to store short values (up to 127 bytes) more efficiently.

If the lowest bit is not set, the second lowest bit is used to store more information. This leaves 30 bits (4 bytes = 32 bits, minus 2 bits status) for the length information, or 2^30 — 1 = 1073741823 bytes for uncompressed data. Toast data can be compressed, and can therefore store more than roughly the 1 GB.

In our case, the lowest bit is set for all 5 rows, and we need to look at the remaining bits:

1. 0000110 -> 6
2. 0000110 -> 6
3. 0000110 -> 6
4. 0000100 -> 4
5. 0000101 -> 5

6 bytes («Lorem» = 5 bytes, plus length byte), 4 bytes («sit» = 3 bytes, plus length byte) and 5 bytes («Bamet» = 4 bytes, plus length byte).

Shorter values will always go into the one byte form, to save space. Decoding of this information is usually done by C macros.

Back to xmin and xmax

Earlier we looked into the show_xmin_xmax table, updated and deleted some data, but could not verify that the deleted data is still in the table. pageinspect can help with that as well:

oid=# SELECT *,
oid-#        encode(t_data, 'escape') AS decoded
oid-#   FROM heap_page_items(get_raw_page('show_xmin_xmax', 0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |   t_data   |     decoded      
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------+------------------
  1 |   8160 |        1 |     28 |   7058 |   7059 |        0 | (0,3)  |        8193 |       1280 |     24 |        |       | x01000000 | x01000000
  2 |   8128 |        1 |     28 |   7058 |   7060 |        0 | (0,2)  |        8193 |        256 |     24 |        |       | x02000000 | x02000000
  3 |   8096 |        1 |     28 |   7059 |      0 |        0 | (0,3)  |           1 |      10496 |     24 |        |       | x03000000 | x03000000
(3 rows)

We see 3 entries in the table:

• The deleted entry (id = 1) is at the first position, and xmax is 7059
• The second entry is id = 2, also deleted, by transaction 7060
• The third entry is the result of the UPDATE operation, which marked the id = 1 row as deleted, and at the same time inserted a new row with id = 3, this operation has xmin = 7059

Let’s run a VACUUM on the table:

oid=# VACUUM show_xmin_xmax;
VACUUM

And look at the table again:

oid=# SELECT *,
       encode(t_data, 'escape') AS decoded
  FROM heap_page_items(get_raw_page('show_xmin_xmax', 0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |   t_data   |     decoded      
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------+------------------
  1 |      0 |        0 |      0 |        |        |          |        |             |            |        |        |       |            | 
  2 |      0 |        0 |      0 |        |        |          |        |             |            |        |        |       |            | 
  3 |   8160 |        1 |     28 |   7059 |      0 |        0 | (0,3)  |           1 |      10496 |     24 |        |       | x03000000 | x03000000
(3 rows)

The first two entries are gone, the space is empty and can be re-used by future DML operations. However PostgreSQL does not remove the space, or give it back to the operating system. Let’s insert a new row:

oid=# BEGIN;
BEGIN
oid=# SELECT txid_current();
 txid_current 
--------------
         7061
(1 row)

oid=# INSERT INTO show_xmin_xmax VALUES (5);
INSERT 0 1
oid=# SELECT xmin, xmax, id FROM show_xmin_xmax ORDER BY id;
 xmin | xmax | id 
------+------+----
 7059 |    0 |  3
 7061 |    0 |  5
(2 rows)

oid=# COMMIT;
COMMIT

Just one row, Vasili.

oid=# SELECT *,
       encode(t_data, 'escape') AS decoded
  FROM heap_page_items(get_raw_page('show_xmin_xmax', 0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |   t_data   |     decoded      
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------+------------------
  1 |   8128 |        1 |     28 |   7061 |      0 |        0 | (0,1)  |           1 |       2048 |     24 |        |       | x05000000 | x05000000
  2 |      0 |        0 |      0 |        |        |          |        |             |            |        |        |       |            | 
  3 |   8160 |        1 |     28 |   7059 |      0 |        0 | (0,3)  |           1 |      10496 |     24 |        |       | x03000000 | x03000000
(3 rows)

The database did not append this new row at the end of the page, or wrote it into another page. Instead it re-used the available space, and wrote it into ctid = (0, 1).

oid

The last of the columns is oid. These days, having an oid for rows is no longer supported. In a more recent version of PostgreSQL, trying to add an OID field raises an error:
 

oid=# CREATE TABLE with_oid () WITH (OIDS=TRUE);
ERROR:  tables declared WITH OIDS are not supported

In versions up to 11, this was still supported, but not encouraged. What it means is that every table with OIDS=TRUE has an additional column named «oid«, which is populated by a different transaction id — increasing the need to run VACUUM to prevent a Transaction ID Wraparound.

Summary

Every PostgreSQL table has a couple additional columns, which are hidden by default, but can be shown when included in a query. Knowing the details of each column is helpful, as it gives very good insight into the table structure and the way PostgreSQL organizes the data.

CREATE TABLE (

...

) WITH ( OIDS = FALSE );

3 ответов: