Re: Using CTID system column as a "temporary" primary key

Hello Kirk,

We are pushing our customers to use only "pure" SQL without writing triggers or stored procedures, to not be stuck with a specific DB vendor.

We have a quite good vision of what is SQL portable and what is not SQL portable.

Concurrent data access is one these topic, especially when using old-style Informix pessimistic locking where you declare a cursor FOR UPDATE, fetch the row to set an exclusive lock, until the end user has finished to modify the record in the form, then do the UPDATE and close the cursor or commit the TX to release the lock. Involves all concepts of concurrent data access (isolation level, lock wait mode, locks and locking granularity, transactions) - best solution I found so far is: Committed read isolation level, wait for locks to the released (with timeout like 10 seconds), do short transaction to hold locks only for a fraction of seconds.

For sure the application code needs to be modified.

Adding a ROWID BIGSERIAL is an option we consider, but then it has other constraints.

INSERT statements must not use the serial column, so you have to list all columns of the table and provide only the values of the non-serial columns. With Informix you could just specific a zero to get a new generated serial, but seems this has never been considered with PostgreSQL.

SELECT * FROM table will return all column, user-defined ROWID included...
This is not the case with Informix or Oracle ROWID columns.
So, either you specify all columns except user-def ROWID or you add the rowid field to the program variable structure that receives the row.

...

Seb
________________________________
From: Kirk Wolak <wolakk(at)gmail(dot)com>
Sent: Tuesday, March 28, 2023 8:24 PM
To: Sebastien Flaesch <sebastien(dot)flaesch(at)4js(dot)com>
Cc: Geoff Winkless <pgsqladmin(at)geoff(dot)dj>; pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)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.

On Tue, Mar 28, 2023 at 6:40 AM Sebastien Flaesch <sebastien(dot)flaesch(at)4js(dot)com<mailto:sebastien(dot)flaesch(at)4js(dot)com>> wrote:
...

I think if you're honest with yourself you already know the answer to this question. The only real solution is to update the legacy code to use the primary key, or (if that's not possible) change the table definition to add your own indexed BIGSERIAL value called "ROWID" to the rows and use that instead (assuming it will be large enough).

Geoff

I have to second this... Why not, during conversion, create a ROWID BIGSERIAL column in the PG only version. (And if not large enough, it's easy enough to use a NUMERIC field, and a sequence)
Then the code would have access to this field, and when it uses it to delete/update it should work.

I cringe at the thought of using CTID. And while it's probably "safe enough" inside a single transaction. I doubt that there is much "testing" of this concept.

Having been through this process (Oracle to PG), I wonder how far you are into the process... Because Packages/Package Variables, Global Temp Tables, and Autonomous Transactions all consumed significant time in our process, as well as variable/field naming problems... If you pull off converting this to PG without changing the source. Let me know...

Regards, Kirk