Postgres 11

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?.

-- find a collisions for integer hash function
SELECT array_agg(i)
FROM   generate_series(1,(2^20)::int4) i
GROUP  BY hashint4(i)
HAVING count(*) > 1
LIMIT  2;

ERROR:  out of memory
DETAIL:  Failed on request of size 8 in memory context "ExprContext".


CREATE TABLE exclude_hast_test(
  id serial
, i  int
, EXCLUDE USING hash(i WITH =)
);

CREATE TABLE


-- hash collision 1
INSERT INTO exclude_hast_test(i) VALUES (213182),(1034649);

INSERT 0 2


-- hash collision 2
INSERT INTO exclude_hast_test(i) VALUES (229405);
INSERT INTO exclude_hast_test(i) VALUES (817996);

INSERT 0 1



INSERT 0 1


INSERT INTO exclude_hast_test(i) VALUES (NULL),(NULL);

INSERT 0 2


SELECT *, hashint4(i) FROM exclude_hast_test;


id
i
hashint4


1
213182
1463380867

2
1034649
1463380867

3
229405
-1292445360

4
817996
-1292445360

5
null
null

6
null
null



SELECT 6


-- true collision
INSERT INTO exclude_hast_test(i) VALUES (229405);

ERROR:  conflicting key value violates exclusion constraint "exclude_hast_test_i_excl"
DETAIL:  Key (i)=(229405) conflicts with existing key (i)=(229405).

id	i	hashint4
1	213182	1463380867
2	1034649	1463380867
3	229405	-1292445360
4	817996	-1292445360
5	null	null
6	null	null