Re: BUG #13846: INSERT ON CONFLICT consumes sequencersonconflicts

All things are relative, to how much data, and how big the bank. And having built gigantic (billions of billions of fact records, hundreds of dimensions, millions of members) data warehouse and olap solutions, you really do try to keep stuff small because it does have an impact on cost, size, and performance.

On a more conceptual note, if a sequence-defaulted column is used to resolve a conflict, i.e. it’s in the conflict_target, than by definition there will never be a conflict!

So anything about ‘fragility’ and ‘peek-ahead’ and ‘BEFORE triggers’ not working just doesn’t make any sense. If a sequence is used to resolve a conflict, there will never be a conflict, and if it isn’t, there’s no need to get the next sequenced value before being able to determine conflicting records meaning no concurrency issues; you would still only need to get the next sequential value only for an actual insert.

From: Peter Geoghegan
Sent: Wednesday, January 6, 2016 2:13 PM
To: Paul
Cc: Andres Freund; pgsql-bugs
Subject: Re: [BUGS] BUG #13846: INSERT ON CONFLICT consumes sequencersonconflicts

On Wed, Jan 6, 2016 at 1:00 PM, Paul <paul(at)salesintel(dot)com> wrote:
> My only point is, there’s another great capability in Postgres, for doing
> efficient concurrent UPSERTS, but that one of the most common and prime use
> cases for it is going to cost more money from having to use 64bit numbers
> everywhere for no good reason.

Not really; at least, it won't increase the size of a plain BIGSERIAL
primary key index (which will generally not be composite):

postgres=# create table foo as select (random() * 100000000)::int4
pkey from generate_series(1, 100000);
SELECT 100000
postgres=# create index on foo (pkey);
CREATE INDEX

postgres=# \dt+ foo
List of relations
Schema │ Name │ Type │ Owner │ Size │ Description
────────┼──────┼───────┼───────┼─────────┼─────────────
public │ foo │ table │ pg │ 3544 kB │
(1 row)

postgres=# \di+ foo_pkey_idx
List of relations
Schema │ Name │ Type │ Owner │ Table │ Size │ Description
────────┼──────────────┼───────┼───────┼───────┼─────────┼─────────────
public │ foo_pkey_idx │ index │ pg │ foo │ 2208 kB │
(1 row)

postgres=# alter table foo alter column pkey type int8;
ALTER TABLE

postgres=# \di+ foo_pkey_idx
List of relations
Schema │ Name │ Type │ Owner │ Table │ Size │ Description
────────┼──────────────┼───────┼───────┼───────┼─────────┼─────────────
public │ foo_pkey_idx │ index │ pg │ foo │ 2208 kB │
(1 row)

The unchanged size of the index foo_pkey_idx seen here due to
alignment considerations. Granted, the heap might still be a bit
larger than it would otherwise be, because it will usually be
"composite", but I think the "cost" of your using int8 will not break
the bank.

--
Peter Geoghegan