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

On Tue, Mar 28, 2023 at 6:40 AM Sebastien Flaesch <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