Re: Using CTID system column as a "temporary" primary key - Mailing list pgsql-general

From Sebastien Flaesch
Subject Re: Using CTID system column as a "temporary" primary key
Date
Msg-id DBAP191MB128920D3A4B5DFB03ED61D1BB0889@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM
Whole thread Raw
In response to Re: Using CTID system column as a "temporary" primary key  (Sebastien Flaesch <sebastien.flaesch@4js.com>)
List pgsql-general
How large can the string representation of a CTID (TID type) be?

This page mentions 6 bytes for t_ctid / ItemPointerData...
=> how can I deduce the max size of its string representation?


Seb

From: Sebastien Flaesch <sebastien.flaesch@4js.com>
Sent: Tuesday, March 28, 2023 11:57 AM
To: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Re: Using CTID system column as a "temporary" primary key
 

EXTERNAL: Do not click links or open attachments if you do not recognize the sender.

I mean Oracle's ROWID of course, not ROWNUM.
ROWNUM is temporary in the context of the SELECT, so it cannot be used in subsequent SQL statements.
Seb

From: Sebastien Flaesch <sebastien.flaesch@4js.com>
Sent: Tuesday, March 28, 2023 11:28 AM
To: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Using CTID system column as a "temporary" primary key
 

EXTERNAL: Do not click links or open attachments if you do not recognize the sender.

Hello!

We are looking for an equivalent of Informix ROWID or Oracle's ROWNUM.

Is the CTID a good choice?

I assume it must be used in a specific context, and of course not considered as permanent primary key.

I understand that if the row is updated, the CTID may change.

Where can we find details about the validity and lifetime of the value such column?

Will CTID be supported long term or is there any plan to remove it or hide it some day?

Of course, one should use a real primary key definition. However, we have legacy code to adapt to PostgreSQL, and in some cases, tables have a composite primary key. A first SELECT uses that primary key, but it also fetches the ROWID, and will use that one in a subsequent SELECT, UPDATE or DELETE, instead of carrying the composite pkey values.

Seb

pgsql-general by date:

Previous
From: Geoff Winkless
Date:
Subject: Re: Using CTID system column as a "temporary" primary key
Next
From: Sebastien Flaesch
Date:
Subject: Re: Using CTID system column as a "temporary" primary key