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

> On Mar 28, 2023, at 03:39, Sebastien Flaesch <sebastien.flaesch@4js.com> 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.

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

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.klaver@aklaver.com

On Wed, 2023-03-29 at 14:23 +0000, Sebastien Flaesch wrote:
> From: Laurenz Albe <laurenz.albe@cybertec.at>
> > 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

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.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

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.klaver@aklaver.com

> On Mar 29, 2023, at 12:11, Sebastien Flaesch <sebastien.flaesch@4js.com> 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
willrapidly become unusable if it tried to adopt them all.  This one in particular seems particularly hacky and
misguided(as well as non-standard-compliant). 

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.klaver@aklaver.com

> On 29 Mar 2023, at 21:11, Sebastien Flaesch <sebastien.flaesch@4js.com> 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
meanzero 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-specificfeatures. 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.

On Wed, 29 Mar 2023 at 21:11, Sebastien Flaesch
<sebastien.flaesch@4js.com> 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.

On Thu, 30 Mar 2023 at 10:01, Dominique Devienne <ddevienne@gmail.com> 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.

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

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.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"