Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

create table demo (
  id bigint generated always as identity primary key,
  value int not null,
  constraint unique_value exclude using hash(value with =)
);

CREATE TABLE


insert into demo (value)
  select unnest(array_agg(i))
  from generate_series(1,(2^16)::int4) i
  group by hashint4(i)
  having count(*) > 1
  limit  2;

INSERT 0 2


select
  opc.opcname as operator_class_name,
  amproc.amproc as access_method_procedure
from
  pg_class t
  join pg_index ix on t.oid = ix.indrelid
  join pg_class i on i.oid = ix.indexrelid
  join pg_attribute a on a.attrelid = t.oid and a.attnum = any(ix.indkey)
  join pg_opclass opc on opc.oid = ix.indclass[0]
  join pg_amproc amproc on amproc.amprocfamily = opc.opcfamily
where
  i.relname = 'unique_value' and amproc.amprocnum = 1; -- HASHSTANDARD_PROC = 1


operator_class_name
access_method_procedure


int4_ops
hashint8

int4_ops
hashint2

int4_ops
hashint4



SELECT 3


select *, hashint4(value), hashint8(value) from demo;


id
value
hashint4
hashint8


1
13362
-178094052
-178094052

2
41950
-178094052
-178094052



SELECT 2


insert into demo (value)
  select unnest(array_agg(i))
  from generate_series(1,(2^16)::int4) i
  group by hashint4(i)
  having count(*) > 1
  limit  1;

ERROR:  conflicting key value violates exclusion constraint "unique_value"
DETAIL:  Key (value)=(13362) conflicts with existing key (value)=(13362).


insert into demo (value)
  select unnest(array_agg(i))
  from generate_series(1,(2^16)::int4) i
  group by hashint4(i)
  having count(*) > 1
  limit  1
on conflict (value) do nothing;

ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification


insert into demo (value)
  select unnest(array_agg(i))
  from generate_series(1,(2^16)::int4) i
  group by hashint4(i)
  having count(*) > 1
  limit  1
on conflict on constraint unique_value do nothing;

INSERT 0 0