RowID

1. Objective

IvorySQL provides Oracle-compatible RowID functionality. RowID is a pseudo-column automatically generated by the database when a table is created, returning the address of each row in the database.

RowID should have the following characteristics:

1. Logically identifies each row with a unique value

2. Allows quick querying and modification of other columns in the table via ROWID, but cannot be inserted or modified itself

3. Users can control whether this feature is enabled

2. Enable the functionality

IvorySQL provides multiple ways to enable the RowID functionality.

2.1. Enable the functionality through GUC parameters

In IvorySQL’s Oracle-compatible mode, the RowID functionality can be enabled by setting ivorysql.default_with_rowids to on. The default value for this parameter is off. Once enabled, tables created will automatically include a RowID column, which can be viewed using \d+ table_name.

ivorysql=# show ivorysql.default_with_rowids;
 ivorysql.default_with_rowids
------------------------------
 off
(1 row)

ivorysql=# create table t(a int);
CREATE TABLE
ivorysql=# \d+ t
                                                      Table "public.t"
 Column |      Type       | Collation | Nullable | Default | Invisible | Storage | Compression | Stats target | Description
--------+-----------------+-----------+----------+---------+-----------+---------+-------------+--------------+-------------
 a      | pg_catalog.int4 |           |          |         |           | plain   |             |              |
Access method: heap

2.2. Enable the functionality by adding the `WITH ROWID` option in the table creation statement.

Users can choose to include this option for tables that require it; without the WITH ROWID option, a regular table will be created.

ivorysql=# create table t2(a int) with rowid;
CREATE TABLE
ivorysql=# \d+ t2
                                                     Table "public.t2"
 Column |      Type       | Collation | Nullable | Default | Invisible | Storage | Compression | Stats target | Description
--------+-----------------+-----------+----------+---------+-----------+---------+-------------+--------------+-------------
 a      | pg_catalog.int4 |           |          |         |           | plain   |             |              |
Indexes:
    "t2_16432_rowid_idx" btree (rowid)
Access method: heap
Has ROWID: yes

2.3. Enable the functionality by executing the command `ALTER TABLE … SET WITH ROWID` on an existing table.

This approach allows a regular table to add a ROWID column using the ALTER command when ROWID functionality is needed. The ROWID can also be removed using the ALTER TABLE … SET WITHOUT ROWID command.

ivorysql=# create table t3(a int);
CREATE TABLE
ivorysql=# alter table t3 set with rowid;
ALTER TABLE
ivorysql=# \d+ t3;
                                                     Table "public.t3"
 Column |      Type       | Collation | Nullable | Default | Invisible | Storage | Compression | Stats target | Description
--------+-----------------+-----------+----------+---------+-----------+---------+-------------+--------------+-------------
 a      | pg_catalog.int4 |           |          |         |           | plain   |             |              |
Access method: heap
Has ROWID: yes