Re: Using CTID system column as a "temporary" primary key
On 2023-03-29 12:15:09 -0700, Adrian Klaver wrote: > On 3/29/23 09:43, Peter J. Holzer wrote: > > On 2023-03-29 07:59:54 -0700, Adrian Klaver wrote: > > > On 3/29/23 07:19, Sebastien Flaesch wrote: > > > > 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. > > > > > > Yes it has: > > [...] > > > insert into seq_test values(default, 'test'); > > > > Default is not the same as zero. > > It accomplishes the same thing, No. As has been pointed out by others, default is keyword. Unlike 0 (or NULL) you can't bind it, You can argue that that would be a bad idea anyway (and in most - maybe all - cases I'd agree with you), but I consider that a pretty fundamental difference. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: Using CTID system column as a "temporary" primary key
On Thu, 2023-03-30 at 14:32 +0200, Dominique Devienne wrote: > I fail to see that myself, sorry. You can bind NULL, you can bind values, so > why > wouldn't you be able to bind DEFAULT too? I see that more as a failing to the > binding API myself :) That doesn't work because DEFAULT is not a value like NULL, it is a keyword. > But I guess it can be worked around with something like > `... values(coalesce($1, default), ..)` > and abusing NULL to mean DEFAULT on a case-by-case bases. > Assuming default can be used in this way (didn't try), of course. That won't work either, because DEFAULT is a keyword and cannot used in expressions. For what you want, I can think of two approaches: 1. Use two prepared INSERT statements with different column lists, one with the column in question and the other without. Then use the appropriate statement, depending on whether you want the default value or not. 2. Don't use a default value, but a BEFORE INSERT trigger. If you insert some magical value like -1, the trigger replaces the value with some default. The second solution is somewhat uglier (personal hudgement) and slower. Yours, Laurenz Albe
Re: Using CTID system column as a "temporary" primary key
On Thu, Mar 30, 2023 at 11:42 AM Francisco Olarte wrote: > On Thu, 30 Mar 2023 at 10:01, Dominique Devienne > wrote: > > BTW, default and 0 are not the same thing. You cannot bind "default" in > place of > > an integer-valued prepared-statement placeholder, in a binary mode > insert. So it is > > definitely not the same thing. > > IMNSHO if you need to select between default and explicit in an insert > via binding you have a design problem, and down this path lies madness. > I fail to see that myself, sorry. You can bind NULL, you can bind values, so why wouldn't you be able to bind DEFAULT too? I see that more as a failing to the binding API myself :) But I guess it can be worked around with something like `... values(coalesce($1, default), ..)` and abusing NULL to mean DEFAULT on a case-by-case bases. Assuming default can be used in this way (didn't try), of course.
Re: Using CTID system column as a "temporary" primary key
On Thu, 30 Mar 2023 at 10:01, Dominique Devienne wrote: >> 2) 0 can be a valid sequence value: > Of course. Yet, as above, if that is opt-in as specified in the `create > table` DDL somehow, then why not? > BTW, default and 0 are not the same thing. You cannot bind "default" in place > of > an integer-valued prepared-statement placeholder, in a binary mode insert. So > it is > definitely not the same thing. IMNSHO if you need to select between default and explicit in an insert via binding you have a design problem, and down this path lies madness. > So while I can accept that not implementing that particular informix > compatibility wart > is a perfectly valid position, for impl and maintenance cost, the arguments > I've read so > far can be "easily" side-stepped from a technical perspective I suspect. FWIW. Do not forget the runtime costs, once you start piling informix warts over oracle warts over access warts over sybase warts over mysql warts over sql server warts it adds up. I do not think postgres target should be compatibilty with (select sql_engine order by random limit n). Normally it tries to follow standards, and do something reasonable when not possible, but this informix wart sounds particularly worthless to implement. Beside your use case I do not think it would serve for anything else than encouraging people to use an ill dessigned informix feature. Francisco Olarte.
Re: Using CTID system column as a "temporary" primary key
On Wed, Mar 29, 2023 at 9:23 PM Adrian Klaver wrote: > On 3/29/23 12:11, Sebastien Flaesch wrote: > > Oh the use of default keyword is new to me, thanks for that. > > > > But to make PostgreSQL more Informix-compatible, > > zero should have been considered as well. Perhaps. > 1) Why? Down the road to compatibility with some undetermined group of > databases lies mayhem. > Sure. Unless it's opt-in, see below. > 2) 0 can be a valid sequence value: > Of course. Yet, as above, if that is opt-in as specified in the `create table` DDL somehow, then why not? BTW, default and 0 are not the same thing. You cannot bind "default" in place of an integer-valued prepared-statement placeholder, in a binary mode insert. So it is definitely not the same thing. So while I can accept that not implementing that particular informix compatibility wart is a perfectly valid position, for impl and maintenance cost, the arguments I've read so far can be "easily" side-stepped from a technical perspective I suspect. FWIW.
Re: Using CTID system column as a "temporary" primary key
I understand and agree. Anyway, we suggest our customers to use sequences instead of serials. Seb From: Alban Hertroys Sent: Wednesday, March 29, 2023 10:15 PM To: Sebastien Flaesch Cc: Adrian Klaver ; Kirk Wolak ; Geoff Winkless ; pgsql-general 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 29 Mar 2023, at 21:11, Sebastien Flaesch wrote: > > Oh the use of default keyword is new to me, thanks for that. > > But to make PostgreSQL more Informix-compatible, zero should have been > considered as well. …No, I’m not going to be humble about this opinion… Postgres does a sane thing here. It’s Informix that you should be complaining about. Zero is not a sane value to specify special behaviour, it could mean zero and be just as valid. By consequence, Informix probably forbids zero as a sequence value, but that is an artefact (and a limitation) of its implementation, not a feature. The core of your problem however, is that you’re trying to get database-agnostic behaviour by relying on database-specific features. That is not going to work, you’ve just been lucky enough to get away with it until now. There’s really only one realistic answer here: Fix your design. Regards, Alban Hertroys -- There is always an exception to always.
Re: Using CTID system column as a "temporary" primary key
On Wed, 29 Mar 2023 at 21:11, Sebastien Flaesch wrote: > Oh the use of default keyword is new to me, thanks for that. > But to make PostgreSQL more Informix-compatible, zero should have been > considered as well. NONONO please! Someone already pointed a sequence can generate zero, but even without that some of us may need to insert 0 ( i.e. because we deleted the row with the zero-id and want recreate it or just because, even if the default-generating sequence does not spit zeroes, the PHB wants us to insert is employee-record with ID=0 or other reasons). AFAIK serial just creates an integer column with a default, doc (8.1) says its range is from 1, but I'm not even sure this is enforced, when I've looked at the description of a serial column I do not remember seeing anything more then the default, so you could probably insert negatives. I , and I suspect others, would prefer to be able to insert any int than copying a hacky ( and I suspect non standard ) trick from informix. Just write to informix and suggest them to implement DEFAULT on inserts, it is much better ;-> . Being more informix-compatible may sound as a feature to yoy, to me it sounds like a misfeature, like needing DUAL to be more oracle-compatible. Francisco Olarte.
Re: Using CTID system column as a "temporary" primary key
> On 29 Mar 2023, at 21:11, Sebastien Flaesch wrote: > > Oh the use of default keyword is new to me, thanks for that. > > But to make PostgreSQL more Informix-compatible, zero should have been > considered as well. …No, I’m not going to be humble about this opinion… Postgres does a sane thing here. It’s Informix that you should be complaining about. Zero is not a sane value to specify special behaviour, it could mean zero and be just as valid. By consequence, Informix probably forbids zero as a sequence value, but that is an artefact (and a limitation) of its implementation, not a feature. The core of your problem however, is that you’re trying to get database-agnostic behaviour by relying on database-specific features. That is not going to work, you’ve just been lucky enough to get away with it until now. There’s really only one realistic answer here: Fix your design. Regards, Alban Hertroys -- There is always an exception to always.
Re: Using CTID system column as a "temporary" primary key
On 3/29/23 12:11, Sebastien Flaesch wrote: Oh the use of default keyword is new to me, thanks for that. But to make PostgreSQL more Informix-compatible, zero should have been considered as well. 1) Why? Down the road to compatibility with some undetermined group of databases lies mayhem. 2) 0 can be a valid sequence value: test(5432)=# create sequence zero_test start 0 minvalue 0; CREATE SEQUENCE test(5432)=# select * from zero_test ; last_value | log_cnt | is_called +-+--- 0 | 0 | f Then what do you do? -- Adrian Klaver adrian.kla...@aklaver.com
Re: Using CTID system column as a "temporary" primary key
> On Mar 29, 2023, at 12:11, Sebastien Flaesch > wrote: > But to make PostgreSQL more Informix-compatible, zero should have been > considered as well. There is an infinite family of strange features that various databases have (DUAL from Oracle, anyone?); PostgreSQL will rapidly become unusable if it tried to adopt them all. This one in particular seems particularly hacky and misguided (as well as non-standard-compliant).
Re: Using CTID system column as a "temporary" primary key
On 3/29/23 09:43, Peter J. Holzer wrote: On 2023-03-29 07:59:54 -0700, Adrian Klaver wrote: On 3/29/23 07:19, Sebastien Flaesch wrote: 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. Yes it has: [...] insert into seq_test values(default, 'test'); Default is not the same as zero. It accomplishes the same thing, a place holder value can be used to fire the sequence without column qualifying the insert/update. Furthermore it works over all columns. So I would say it has been considered by Postgres as a way to 'to get a new generated serial'. hp -- Adrian Klaver adrian.kla...@aklaver.com
Re: Using CTID system column as a "temporary" primary key
Oh the use of default keyword is new to me, thanks for that.
But to make PostgreSQL more Informix-compatible, zero should have been
considered as well.
Informix:
sf@toro:/tmp$ dbaccess test1 -
Database selected.
> create table mytable ( pkey serial not null primary key, name varchar(50) );
Table created.
> insert into mytable values ( 0, 'a' );
1 row(s) inserted.
> select * from mytable;
pkey name
1 a
1 row(s) retrieved.
PostgreSQL:
sf@toro:/tmp$ psql test1 --host=localhost --port=5436 --user=pgsuser
psql (14.1)
Type "help" for help.
test1=> create table mytable ( pkey serial not null primary key, name
varchar(50) );
CREATE TABLE
test1=> insert into mytable values ( 0, 'a' );
INSERT 0 1
test1=> select * from mytable;
pkey | name
--+---
0 | a
(1 row)
So, I would rather say : no, using zero was not considered.
😉
Seb
From: Adrian Klaver
Sent: Wednesday, March 29, 2023 4:59 PM
To: Sebastien Flaesch ; Kirk Wolak
Cc: Geoff Winkless ; pgsql-general
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 3/29/23 07:19, Sebastien Flaesch wrote:
> Hello Kirk,
>
> 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.
Yes it has:
\d seq_test
Table "public.seq_test"
Column | Type| Collation | Nullable |
Default
+---+---+--+--
id | integer | | not null |
nextval('seq_test_id_seq'::regclass)
fld_1 | character varying | | |
Indexes:
"seq_test_pkey" PRIMARY KEY, btree (id)
insert into seq_test values(default, 'test');
select * from seq_test;
id | fld_1
+---
1 | test
>
> 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
>
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: Using CTID system column as a "temporary" primary key
Ok... sounds not good all in all. Appreciate your help! Thanks! From: Laurenz Albe Sent: Wednesday, March 29, 2023 5:53 PM To: Sebastien Flaesch ; Kirk Wolak Cc: Geoff Winkless ; pgsql-general 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 Wed, 2023-03-29 at 14:23 +, Sebastien Flaesch wrote: > From: Laurenz Albe > > It is safe to assume that the CTID is stable within a single transaction > > only if you use REPEATABLE READ or better transaction isolation level. > > > > With READ COMMITTED, you see updated rows (and consequently changed CTID) > > within a single transaction. And if you use SELECT ... FOR UPDATE, you > > could even see a changed CTID within a single statement. > > > > So don't use CTID to identify rows unless you use REPEATABLE READ or better. > > Thanks for the advice about REPEATABLE READ isolation level! ... but that is only useful in a read-only scenario. If you try to UPDATE the row in a REPEATABLE READ transaction, you will get a serialization error if there was a concurrent update. In short: don't use the CTID to identify a row. Yours, Laurenz Albe
Re: Using CTID system column as a "temporary" primary key
On 2023-03-29 07:59:54 -0700, Adrian Klaver wrote: > On 3/29/23 07:19, Sebastien Flaesch wrote: > > 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. > > Yes it has: [...] > insert into seq_test values(default, 'test'); Default is not the same as zero. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: Using CTID system column as a "temporary" primary key
On Wed, 2023-03-29 at 14:23 +, Sebastien Flaesch wrote: > From: Laurenz Albe > > It is safe to assume that the CTID is stable within a single transaction > > only if you use REPEATABLE READ or better transaction isolation level. > > > > With READ COMMITTED, you see updated rows (and consequently changed CTID) > > within a single transaction. And if you use SELECT ... FOR UPDATE, you > > could even see a changed CTID within a single statement. > > > > So don't use CTID to identify rows unless you use REPEATABLE READ or better. > > Thanks for the advice about REPEATABLE READ isolation level! ... but that is only useful in a read-only scenario. If you try to UPDATE the row in a REPEATABLE READ transaction, you will get a serialization error if there was a concurrent update. In short: don't use the CTID to identify a row. Yours, Laurenz Albe
Re: Using CTID system column as a "temporary" primary key
On 3/29/23 07:19, Sebastien Flaesch wrote:
Hello Kirk,
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.
Yes it has:
\d seq_test
Table "public.seq_test"
Column | Type| Collation | Nullable |
Default
+---+---+--+--
id | integer | | not null |
nextval('seq_test_id_seq'::regclass)
fld_1 | character varying | | |
Indexes:
"seq_test_pkey" PRIMARY KEY, btree (id)
insert into seq_test values(default, 'test');
select * from seq_test;
id | fld_1
+---
1 | test
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
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: Using CTID system column as a "temporary" primary key
Laurent, Thanks for the advice about REPEATABLE READ isolation level! Seb From: Laurenz Albe Sent: Wednesday, March 29, 2023 1:08 PM To: Kirk Wolak ; Sebastien Flaesch Cc: Geoff Winkless ; pgsql-general 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, 2023-03-28 at 14:24 -0400, Kirk Wolak wrote: > > 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. It is safe to assume that the CTID is stable within a single transaction only if you use REPEATABLE READ or better transaction isolation level. With READ COMMITTED, you see updated rows (and consequently changed CTID) within a single transaction. And if you use SELECT ... FOR UPDATE, you could even see a changed CTID within a single statement. So don't use CTID to identify rows unless you use REPEATABLE READ or better. Yours, Laurenz Albe
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 Sent: Tuesday, March 28, 2023 8:24 PM To: Sebastien Flaesch Cc: Geoff Winkless ; pgsql-general 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 mailto:sebastien.flae...@4js.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
Re: Using CTID system column as a "temporary" primary key
On Tue, 2023-03-28 at 14:24 -0400, Kirk Wolak wrote: > > 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. It is safe to assume that the CTID is stable within a single transaction only if you use REPEATABLE READ or better transaction isolation level. With READ COMMITTED, you see updated rows (and consequently changed CTID) within a single transaction. And if you use SELECT ... FOR UPDATE, you could even see a changed CTID within a single statement. So don't use CTID to identify rows unless you use REPEATABLE READ or better. Yours, Laurenz Albe
Re: Using CTID system column as a "temporary" primary key
On Tue, Mar 28, 2023 at 6:40 AM Sebastien Flaesch 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
Re: Using CTID system column as a "temporary" primary key
Note that VACUUM FULL and CLUSTER can update ctids. I don't believe regular VACUUM can, so you should be safe from autovacuum interfering in this scheme, but the ctid colum documentation [1] states "A primary key should be used to identify logical rows," so this is not exactly intended usage. Thanks, Maciek [1]: https://www.postgresql.org/docs/current/ddl-system-columns.html
Re: Using CTID system column as a "temporary" primary key
CAST seems to do the job so that's a good solution here. Seb From: Christophe Pettus Sent: Tuesday, March 28, 2023 2:39 PM To: Sebastien Flaesch Cc: Geoff Winkless ; pgsql-general 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 Mar 28, 2023, at 03:39, Sebastien Flaesch > wrote: > Do I have to cast() ? Yes: select * from t where ctid='(0,1)'::tid; The string representation can be up to 17 characters: 10 for the page number, 4 for the tuple number, and three for the delimiters. Remember that updating a row changes its CTID.
Re: Using CTID system column as a "temporary" primary key
> On Mar 28, 2023, at 03:39, Sebastien Flaesch > wrote: > Do I have to cast() ? Yes: select * from t where ctid='(0,1)'::tid; The string representation can be up to 17 characters: 10 for the page number, 4 for the tuple number, and three for the delimiters. Remember that updating a row changes its CTID.
Re: Using CTID system column as a "temporary" primary key
Hi Geoff, Your remark makes total sense, and this is what should be done. However, we have to deal with quite complex legacy 4GL code that we prefer to not touch, and we can adapt the SQL statements on the fly with our solution (kind of Java compiler/runtime system). Next question: How can I UPDATE or DELETE a row, with the CTID column? When I bind a string parameter, I get this error: SQLSTATE = 42883 MESSAGE: operator does not exist: tid = character varying Do I have to cast() ? Seb From: Geoff Winkless Sent: Tuesday, March 28, 2023 12:20 PM To: Sebastien Flaesch Cc: pgsql-general 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, 28 Mar 2023 at 10:28, Sebastien Flaesch mailto:sebastien.flae...@4js.com>> wrote: Is the CTID a good choice? 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
Re: Using CTID system column as a "temporary" primary key
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? https://www.postgresql.org/docs/14/storage-page-layout.html Seb From: Sebastien Flaesch Sent: Tuesday, March 28, 2023 11:57 AM To: pgsql-general 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 Sent: Tuesday, March 28, 2023 11:28 AM To: pgsql-general 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
Re: Using CTID system column as a "temporary" primary key
On Tue, 28 Mar 2023 at 10:28, Sebastien Flaesch wrote: > Is the CTID a good choice? > 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
Re: Using CTID system column as a "temporary" primary key
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 Sent: Tuesday, March 28, 2023 11:28 AM To: pgsql-general 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
