diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/brin.out | 179 | ||||
| -rw-r--r-- | src/test/regress/expected/opr_sanity.out | 14 | ||||
| -rw-r--r-- | src/test/regress/output/misc.source | 4 | ||||
| -rw-r--r-- | src/test/regress/parallel_schedule | 2 | ||||
| -rw-r--r-- | src/test/regress/serial_schedule | 1 | ||||
| -rw-r--r-- | src/test/regress/sql/brin.sql | 184 | ||||
| -rw-r--r-- | src/test/regress/sql/opr_sanity.sql | 7 |
7 files changed, 384 insertions, 7 deletions
diff --git a/src/test/regress/expected/brin.out b/src/test/regress/expected/brin.out new file mode 100644 index 00000000000..f8be27e5e30 --- /dev/null +++ b/src/test/regress/expected/brin.out @@ -0,0 +1,179 @@ +SET synchronous_commit = 0; +CREATE TABLE brintest (byteacol bytea, + charcol "char", + namecol name, + int8col bigint, + int2col smallint, + int4col integer, + textcol text, + oidcol oid, + tidcol tid, + float4col real, + float8col double precision, + macaddrcol macaddr, + inetcol inet, + bpcharcol character, + datecol date, + timecol time without time zone, + timestampcol timestamp without time zone, + timestamptzcol timestamp with time zone, + intervalcol interval, + timetzcol time with time zone, + bitcol bit(10), + varbitcol bit varying(16), + numericcol numeric, + uuidcol uuid, + lsncol pg_lsn +) WITH (fillfactor=50); +INSERT INTO brintest SELECT + repeat(stringu1, 42)::bytea, + substr(stringu1, 1, 1)::"char", + stringu1::name, 142857 * tenthous, + thousand, + twothousand, + repeat(stringu1, 42), + unique1::oid, + format('(%s,%s)', tenthous, twenty)::tid, + (four + 1.0)/(hundred+1), + odd::float8 / (tenthous + 1), + format('%s:00:%s:00:%s:00', to_hex(odd), to_hex(even), to_hex(hundred))::macaddr, + inet '10.2.3.4' + tenthous, + substr(stringu1, 1, 1)::bpchar, + date '1995-08-15' + tenthous, + time '01:20:30' + thousand * interval '18.5 second', + timestamp '1942-07-23 03:05:09' + tenthous * interval '36.38 hours', + timestamptz '1972-10-10 03:00' + thousand * interval '1 hour', + justify_days(justify_hours(tenthous * interval '12 minutes')), + timetz '01:30:20' + hundred * interval '15 seconds', + thousand::bit(10), + tenthous::bit(16)::varbit, + tenthous::numeric(36,30) * fivethous * even / (hundred + 1), + format('%s%s-%s-%s-%s-%s%s%s', to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'))::uuid, + format('%s/%s%s', odd, even, tenthous)::pg_lsn +FROM tenk1; +CREATE INDEX brinidx ON brintest USING brin ( + byteacol, + charcol, + namecol, + int8col, + int2col, + int4col, + textcol, + oidcol, + tidcol, + float4col, + float8col, + macaddrcol, + inetcol, + bpcharcol, + datecol, + timecol, + timestampcol, + timestamptzcol, + intervalcol, + timetzcol, + bitcol, + varbitcol, + numericcol, + uuidcol, + lsncol +) with (pages_per_range = 1); +CREATE TABLE brinopers (colname name, op text[], value text[], + check (cardinality(op) = cardinality(value))); +INSERT INTO brinopers VALUES ('byteacol', '{>, >=, =, <=, <}', '{ZZAAAA, ZZAAAA, AAAAAA, AAAAAA, AAAAAA}'); +INSERT INTO brinopers VALUES ('charcol', '{>, >=, =, <=, <}', '{Z, Z, A, A, A}'); +INSERT INTO brinopers VALUES ('namecol', '{>, >=, =, <=, <}', '{ZZAAAA, ZZAAAA, AAAAAA, AAAAAA, AAAAAA}'); +INSERT INTO brinopers VALUES ('int8col', '{>, >=, =, <=, <}', '{1428427143, 1428427143, 0, 0, 0}'); +INSERT INTO brinopers VALUES ('int2col', '{>, >=, =, <=, <}', '{999, 999, 0, 0, 0}'); +INSERT INTO brinopers VALUES ('int4col', '{>, >=, =, <=, <}', '{1999, 1999, 0, 0, 0}'); +INSERT INTO brinopers VALUES ('textcol', '{>, >=, =, <=, <}', '{ZZAAAA, ZZAAAA, AAAAA, AAAAA, AAAAA}'); +INSERT INTO brinopers VALUES ('oidcol', '{>, >=, =, <=, <}', '{9999, 9999, 0, 0, 0}'); +INSERT INTO brinopers VALUES ('tidcol', '{>, >=, =, <=, <}', '{"(9999,19)", "(9999,19)", "(0,0)", "(0,0)", "(0,0)"}'); +INSERT INTO brinopers VALUES ('float4col', '{>, >=, =, <=, <}', '{1, 1, 0.0103093, 0.0103093, 0.0103093}'); +INSERT INTO brinopers VALUES ('float8col', '{>, >=, =, <=, <}', '{1.98, 1.98, 0, 0, 0}'); +INSERT INTO brinopers VALUES ('inetcol', '{>, >=, =, <=, <}', '{10.2.42.19, 10.2.42.19, 10.2.3.4, 10.2.3.4, 10.2.3.4}'); +INSERT INTO brinopers VALUES ('bpcharcol', '{>, >=, =, <=, <}', '{Z, Z, A, A, A}'); +INSERT INTO brinopers VALUES ('datecol', '{>, >=, =, <=, <}', '{2022-12-30, 2022-12-30, 1995-08-15, 1995-08-15, 1995-08-15}'); +INSERT INTO brinopers VALUES ('timecol', '{>, >=, =, <=, <}', '{06:28:31.5, 06:28:31.5, 01:20:30, 01:20:30, 01:20:30}'); +INSERT INTO brinopers VALUES ('timestampcol', '{>, >=, =, <=, <}', '{1984-01-20 22:42:21, 1984-01-20 22:42:21, 1942-07-23 03:05:09, 1942-07-23 03:05:09, 1942-07-23 03:05:09}'); +INSERT INTO brinopers VALUES ('timestamptzcol', '{>, >=, =, <=, <}', '{1972-11-20 19:00:00-03, 1972-11-20 19:00:00-03, 1972-10-10 03:00:00-04, 1972-10-10 03:00:00-04, 1972-10-10 03:00:00-04}'); +INSERT INTO brinopers VALUES ('intervalcol', '{>, >=, =, <=, <}', '{2 mons 23 days 07:48:00, 2 mons 23 days 07:48:00, 00:00:00, 00:00:00, 00:00:00}'); +INSERT INTO brinopers VALUES ('timetzcol', '{>, >=, =, <=, <}', '{01:55:05-03, 01:55:05-03, 01:30:20-03, 01:30:20-03, 01:30:20-03}'); +INSERT INTO brinopers VALUES ('numericcol', '{>, >=, =, <=, <}', '{99470151.9, 99470151.9, 0.00, 0.01, 0.01}'); +INSERT INTO brinopers VALUES ('macaddrcol', '{>, >=, =, <=, <}', '{ff:fe:00:00:00:00, ff:fe:00:00:00:00, 00:00:01:00:00:00, 00:00:01:00:00:00, 00:00:01:00:00:00}'); +INSERT INTO brinopers VALUES ('bitcol', '{>, >=, =, <=, <}', '{1111111000, 1111111000, 0000000010, 0000000010, 0000000010}'); +INSERT INTO brinopers VALUES ('varbitcol', '{>, >=, =, <=, <}', '{1111111111111000, 1111111111111000, 0000000000000100, 0000000000000100, 0000000000000100}'); +INSERT INTO brinopers VALUES ('uuidcol', '{>, >=, =, <=, <}', '{99989998-9998-9998-9998-999899989998, 99989998-9998-9998-9998-999899989998, 00040004-0004-0004-0004-000400040004, 00040004-0004-0004-0004-000400040004, 00040004-0004-0004-0004-000400040005}'); +INSERT INTO brinopers VALUES ('lsncol', '{>, >=, =, <=, <}', '{198/1999799, 198/1999799, 30/312815, 0/1200, 0/1200}'); +DO $x$ +DECLARE + r record; + tabname text; + tabname_ss text; + count int; + query text; + plan text; +BEGIN + FOR r IN SELECT row_number() OVER (), colname, oper, value[ordinality] FROM brinopers, unnest(op) WITH ORDINALITY AS oper LOOP + tabname := format('qry_%s', r.row_number); + tabname_ss := tabname || '_ss'; + query = format($y$INSERT INTO %s SELECT ctid FROM brintest WHERE %s %s %L $y$, + tabname, r.colname, r.oper, r.value); + -- run the query using the brin index + SET enable_seqscan = 0; + SET enable_bitmapscan = 1; + EXECUTE format('create temp table %s (tid tid) /* ON COMMIT DROP*/', tabname); + EXECUTE query; + + -- run the query using a seqscan + SET enable_seqscan = 1; + SET enable_bitmapscan = 0; + query = format($y$INSERT INTO %s SELECT ctid FROM brintest WHERE %s %s %L $y$, + tabname_ss, r.colname, r.oper, r.value); + EXECUTE format('create temp table %s (tid tid) /* ON COMMIT DROP */', tabname_ss); + EXECUTE query; + + -- make sure both return the same results + EXECUTE format('SELECT * from %s EXCEPT ALL SELECT * FROM %s', tabname, tabname_ss); + GET DIAGNOSTICS count = ROW_COUNT; + IF count <> 0 THEN RAISE EXCEPTION 'something not right in %: count %', r, count; END IF; + EXECUTE format('SELECT * from %s EXCEPT ALL SELECT * FROM %s', tabname_ss, tabname); + GET DIAGNOSTICS count = ROW_COUNT; + IF count <> 0 THEN RAISE EXCEPTION 'something not right in %: count %', r, count; END IF; + end loop; +end; +$x$; +INSERT INTO brintest SELECT + repeat(stringu1, 42)::bytea, + substr(stringu1, 1, 1)::"char", + stringu1::name, 142857 * tenthous, + thousand, + twothousand, + repeat(stringu1, 42), + unique1::oid, + format('(%s,%s)', tenthous, twenty)::tid, + (four + 1.0)/(hundred+1), + odd::float8 / (tenthous + 1), + format('%s:00:%s:00:%s:00', to_hex(odd), to_hex(even), to_hex(hundred))::macaddr, + inet '10.2.3.4' + tenthous, + substr(stringu1, 1, 1)::bpchar, + date '1995-08-15' + tenthous, + time '01:20:30' + thousand * interval '18.5 second', + timestamp '1942-07-23 03:05:09' + tenthous * interval '36.38 hours', + timestamptz '1972-10-10 03:00' + thousand * interval '1 hour', + justify_days(justify_hours(tenthous * interval '12 minutes')), + timetz '01:30:20' + hundred * interval '15 seconds', + thousand::bit(10), + tenthous::bit(16)::varbit, + tenthous::numeric(36,30) * fivethous * even / (hundred + 1), + format('%s%s-%s-%s-%s-%s%s%s', to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'))::uuid, + format('%s/%s%s', odd, even, tenthous)::pg_lsn +FROM tenk1; +SELECT brin_summarize_new_values('brinidx'::regclass); + brin_summarize_new_values +--------------------------- + 2000 +(1 row) + +UPDATE brintest SET int8col = int8col * int4col; +SET synchronous_commit = 1; diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out index 992522ea3f1..9870bfaa018 100644 --- a/src/test/regress/expected/opr_sanity.out +++ b/src/test/regress/expected/opr_sanity.out @@ -1658,6 +1658,11 @@ ORDER BY 1, 2, 3; 2742 | 9 | ? 2742 | 10 | ?| 2742 | 11 | ?& + 3580 | 1 | < + 3580 | 2 | <= + 3580 | 3 | = + 3580 | 4 | >= + 3580 | 5 | > 4000 | 1 | << 4000 | 1 | ~<~ 4000 | 2 | &< @@ -1680,7 +1685,7 @@ ORDER BY 1, 2, 3; 4000 | 15 | > 4000 | 16 | @> 4000 | 18 | = -(80 rows) +(85 rows) -- Check that all opclass search operators have selectivity estimators. -- This is not absolutely required, but it seems a reasonable thing @@ -1842,11 +1847,13 @@ WHERE NOT ( -- GIN has six support functions. 1-3 are mandatory, 5 is optional, and -- at least one of 4 and 6 must be given. -- SP-GiST has five support functions, all mandatory + -- BRIN has four mandatory support functions, and a bunch of optionals amname = 'btree' AND procnums @> '{1}' OR amname = 'hash' AND procnums = '{1}' OR amname = 'gist' AND procnums @> '{1, 2, 3, 4, 5, 6, 7}' OR amname = 'gin' AND (procnums @> '{1, 2, 3}' AND (procnums && '{4, 6}')) OR - amname = 'spgist' AND procnums = '{1, 2, 3, 4, 5}' + amname = 'spgist' AND procnums = '{1, 2, 3, 4, 5}' OR + amname = 'brin' AND procnums @> '{1, 2, 3, 4}' ); amname | opfname | amproclefttype | amprocrighttype | procnums --------+---------+----------------+-----------------+---------- @@ -1867,7 +1874,8 @@ WHERE NOT ( amname = 'hash' AND procnums = '{1}' OR amname = 'gist' AND procnums @> '{1, 2, 3, 4, 5, 6, 7}' OR amname = 'gin' AND (procnums @> '{1, 2, 3}' AND (procnums && '{4, 6}')) OR - amname = 'spgist' AND procnums = '{1, 2, 3, 4, 5}' + amname = 'spgist' AND procnums = '{1, 2, 3, 4, 5}' OR + amname = 'brin' AND procnums @> '{1, 2, 3, 4}' ); amname | opcname | procnums --------+---------+---------- diff --git a/src/test/regress/output/misc.source b/src/test/regress/output/misc.source index 7015bfda2c2..06606081ad3 100644 --- a/src/test/regress/output/misc.source +++ b/src/test/regress/output/misc.source @@ -591,6 +591,8 @@ SELECT user_relns() AS user_relns bb box_tbl bprime + brinopers + brintest bt_f8_heap bt_i4_heap bt_name_heap @@ -698,7 +700,7 @@ SELECT user_relns() AS user_relns tvvmv varchar_tbl xacttest -(120 rows) +(122 rows) SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer'))); name diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 9902dbeb39c..d4f02e5703a 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -83,7 +83,7 @@ test: select_into select_distinct select_distinct_on select_implicit select_havi # ---------- # Another group of parallel tests # ---------- -test: privileges security_label collate matview lock replica_identity rowsecurity +test: brin privileges security_label collate matview lock replica_identity rowsecurity # ---------- # Another group of parallel tests diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 2902a05dfb6..b1e44b3bf30 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -106,6 +106,7 @@ test: alter_generic test: misc test: psql test: async +test: brin test: rules test: event_trigger test: select_views diff --git a/src/test/regress/sql/brin.sql b/src/test/regress/sql/brin.sql new file mode 100644 index 00000000000..244652f4c40 --- /dev/null +++ b/src/test/regress/sql/brin.sql @@ -0,0 +1,184 @@ +SET synchronous_commit = 0; + +CREATE TABLE brintest (byteacol bytea, + charcol "char", + namecol name, + int8col bigint, + int2col smallint, + int4col integer, + textcol text, + oidcol oid, + tidcol tid, + float4col real, + float8col double precision, + macaddrcol macaddr, + inetcol inet, + bpcharcol character, + datecol date, + timecol time without time zone, + timestampcol timestamp without time zone, + timestamptzcol timestamp with time zone, + intervalcol interval, + timetzcol time with time zone, + bitcol bit(10), + varbitcol bit varying(16), + numericcol numeric, + uuidcol uuid, + lsncol pg_lsn +) WITH (fillfactor=50); + +INSERT INTO brintest SELECT + repeat(stringu1, 42)::bytea, + substr(stringu1, 1, 1)::"char", + stringu1::name, 142857 * tenthous, + thousand, + twothousand, + repeat(stringu1, 42), + unique1::oid, + format('(%s,%s)', tenthous, twenty)::tid, + (four + 1.0)/(hundred+1), + odd::float8 / (tenthous + 1), + format('%s:00:%s:00:%s:00', to_hex(odd), to_hex(even), to_hex(hundred))::macaddr, + inet '10.2.3.4' + tenthous, + substr(stringu1, 1, 1)::bpchar, + date '1995-08-15' + tenthous, + time '01:20:30' + thousand * interval '18.5 second', + timestamp '1942-07-23 03:05:09' + tenthous * interval '36.38 hours', + timestamptz '1972-10-10 03:00' + thousand * interval '1 hour', + justify_days(justify_hours(tenthous * interval '12 minutes')), + timetz '01:30:20' + hundred * interval '15 seconds', + thousand::bit(10), + tenthous::bit(16)::varbit, + tenthous::numeric(36,30) * fivethous * even / (hundred + 1), + format('%s%s-%s-%s-%s-%s%s%s', to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'))::uuid, + format('%s/%s%s', odd, even, tenthous)::pg_lsn +FROM tenk1; + +CREATE INDEX brinidx ON brintest USING brin ( + byteacol, + charcol, + namecol, + int8col, + int2col, + int4col, + textcol, + oidcol, + tidcol, + float4col, + float8col, + macaddrcol, + inetcol, + bpcharcol, + datecol, + timecol, + timestampcol, + timestamptzcol, + intervalcol, + timetzcol, + bitcol, + varbitcol, + numericcol, + uuidcol, + lsncol +) with (pages_per_range = 1); + +CREATE TABLE brinopers (colname name, op text[], value text[], + check (cardinality(op) = cardinality(value))); + +INSERT INTO brinopers VALUES ('byteacol', '{>, >=, =, <=, <}', '{ZZAAAA, ZZAAAA, AAAAAA, AAAAAA, AAAAAA}'); +INSERT INTO brinopers VALUES ('charcol', '{>, >=, =, <=, <}', '{Z, Z, A, A, A}'); +INSERT INTO brinopers VALUES ('namecol', '{>, >=, =, <=, <}', '{ZZAAAA, ZZAAAA, AAAAAA, AAAAAA, AAAAAA}'); +INSERT INTO brinopers VALUES ('int8col', '{>, >=, =, <=, <}', '{1428427143, 1428427143, 0, 0, 0}'); +INSERT INTO brinopers VALUES ('int2col', '{>, >=, =, <=, <}', '{999, 999, 0, 0, 0}'); +INSERT INTO brinopers VALUES ('int4col', '{>, >=, =, <=, <}', '{1999, 1999, 0, 0, 0}'); +INSERT INTO brinopers VALUES ('textcol', '{>, >=, =, <=, <}', '{ZZAAAA, ZZAAAA, AAAAA, AAAAA, AAAAA}'); +INSERT INTO brinopers VALUES ('oidcol', '{>, >=, =, <=, <}', '{9999, 9999, 0, 0, 0}'); +INSERT INTO brinopers VALUES ('tidcol', '{>, >=, =, <=, <}', '{"(9999,19)", "(9999,19)", "(0,0)", "(0,0)", "(0,0)"}'); +INSERT INTO brinopers VALUES ('float4col', '{>, >=, =, <=, <}', '{1, 1, 0.0103093, 0.0103093, 0.0103093}'); +INSERT INTO brinopers VALUES ('float8col', '{>, >=, =, <=, <}', '{1.98, 1.98, 0, 0, 0}'); +INSERT INTO brinopers VALUES ('inetcol', '{>, >=, =, <=, <}', '{10.2.42.19, 10.2.42.19, 10.2.3.4, 10.2.3.4, 10.2.3.4}'); +INSERT INTO brinopers VALUES ('bpcharcol', '{>, >=, =, <=, <}', '{Z, Z, A, A, A}'); +INSERT INTO brinopers VALUES ('datecol', '{>, >=, =, <=, <}', '{2022-12-30, 2022-12-30, 1995-08-15, 1995-08-15, 1995-08-15}'); +INSERT INTO brinopers VALUES ('timecol', '{>, >=, =, <=, <}', '{06:28:31.5, 06:28:31.5, 01:20:30, 01:20:30, 01:20:30}'); +INSERT INTO brinopers VALUES ('timestampcol', '{>, >=, =, <=, <}', '{1984-01-20 22:42:21, 1984-01-20 22:42:21, 1942-07-23 03:05:09, 1942-07-23 03:05:09, 1942-07-23 03:05:09}'); +INSERT INTO brinopers VALUES ('timestamptzcol', '{>, >=, =, <=, <}', '{1972-11-20 19:00:00-03, 1972-11-20 19:00:00-03, 1972-10-10 03:00:00-04, 1972-10-10 03:00:00-04, 1972-10-10 03:00:00-04}'); +INSERT INTO brinopers VALUES ('intervalcol', '{>, >=, =, <=, <}', '{2 mons 23 days 07:48:00, 2 mons 23 days 07:48:00, 00:00:00, 00:00:00, 00:00:00}'); +INSERT INTO brinopers VALUES ('timetzcol', '{>, >=, =, <=, <}', '{01:55:05-03, 01:55:05-03, 01:30:20-03, 01:30:20-03, 01:30:20-03}'); +INSERT INTO brinopers VALUES ('numericcol', '{>, >=, =, <=, <}', '{99470151.9, 99470151.9, 0.00, 0.01, 0.01}'); +INSERT INTO brinopers VALUES ('macaddrcol', '{>, >=, =, <=, <}', '{ff:fe:00:00:00:00, ff:fe:00:00:00:00, 00:00:01:00:00:00, 00:00:01:00:00:00, 00:00:01:00:00:00}'); +INSERT INTO brinopers VALUES ('bitcol', '{>, >=, =, <=, <}', '{1111111000, 1111111000, 0000000010, 0000000010, 0000000010}'); +INSERT INTO brinopers VALUES ('varbitcol', '{>, >=, =, <=, <}', '{1111111111111000, 1111111111111000, 0000000000000100, 0000000000000100, 0000000000000100}'); +INSERT INTO brinopers VALUES ('uuidcol', '{>, >=, =, <=, <}', '{99989998-9998-9998-9998-999899989998, 99989998-9998-9998-9998-999899989998, 00040004-0004-0004-0004-000400040004, 00040004-0004-0004-0004-000400040004, 00040004-0004-0004-0004-000400040005}'); +INSERT INTO brinopers VALUES ('lsncol', '{>, >=, =, <=, <}', '{198/1999799, 198/1999799, 30/312815, 0/1200, 0/1200}'); + +DO $x$ +DECLARE + r record; + tabname text; + tabname_ss text; + count int; + query text; + plan text; +BEGIN + FOR r IN SELECT row_number() OVER (), colname, oper, value[ordinality] FROM brinopers, unnest(op) WITH ORDINALITY AS oper LOOP + tabname := format('qry_%s', r.row_number); + tabname_ss := tabname || '_ss'; + query = format($y$INSERT INTO %s SELECT ctid FROM brintest WHERE %s %s %L $y$, + tabname, r.colname, r.oper, r.value); + -- run the query using the brin index + SET enable_seqscan = 0; + SET enable_bitmapscan = 1; + EXECUTE format('create temp table %s (tid tid) /* ON COMMIT DROP*/', tabname); + EXECUTE query; + + -- run the query using a seqscan + SET enable_seqscan = 1; + SET enable_bitmapscan = 0; + query = format($y$INSERT INTO %s SELECT ctid FROM brintest WHERE %s %s %L $y$, + tabname_ss, r.colname, r.oper, r.value); + EXECUTE format('create temp table %s (tid tid) /* ON COMMIT DROP */', tabname_ss); + EXECUTE query; + + -- make sure both return the same results + EXECUTE format('SELECT * from %s EXCEPT ALL SELECT * FROM %s', tabname, tabname_ss); + GET DIAGNOSTICS count = ROW_COUNT; + IF count <> 0 THEN RAISE EXCEPTION 'something not right in %: count %', r, count; END IF; + EXECUTE format('SELECT * from %s EXCEPT ALL SELECT * FROM %s', tabname_ss, tabname); + GET DIAGNOSTICS count = ROW_COUNT; + IF count <> 0 THEN RAISE EXCEPTION 'something not right in %: count %', r, count; END IF; + end loop; +end; +$x$; + +INSERT INTO brintest SELECT + repeat(stringu1, 42)::bytea, + substr(stringu1, 1, 1)::"char", + stringu1::name, 142857 * tenthous, + thousand, + twothousand, + repeat(stringu1, 42), + unique1::oid, + format('(%s,%s)', tenthous, twenty)::tid, + (four + 1.0)/(hundred+1), + odd::float8 / (tenthous + 1), + format('%s:00:%s:00:%s:00', to_hex(odd), to_hex(even), to_hex(hundred))::macaddr, + inet '10.2.3.4' + tenthous, + substr(stringu1, 1, 1)::bpchar, + date '1995-08-15' + tenthous, + time '01:20:30' + thousand * interval '18.5 second', + timestamp '1942-07-23 03:05:09' + tenthous * interval '36.38 hours', + timestamptz '1972-10-10 03:00' + thousand * interval '1 hour', + justify_days(justify_hours(tenthous * interval '12 minutes')), + timetz '01:30:20' + hundred * interval '15 seconds', + thousand::bit(10), + tenthous::bit(16)::varbit, + tenthous::numeric(36,30) * fivethous * even / (hundred + 1), + format('%s%s-%s-%s-%s-%s%s%s', to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'))::uuid, + format('%s/%s%s', odd, even, tenthous)::pg_lsn +FROM tenk1; + +SELECT brin_summarize_new_values('brinidx'::regclass); + +UPDATE brintest SET int8col = int8col * int4col; + +SET synchronous_commit = 1; diff --git a/src/test/regress/sql/opr_sanity.sql b/src/test/regress/sql/opr_sanity.sql index b394c300769..7159a8377ee 100644 --- a/src/test/regress/sql/opr_sanity.sql +++ b/src/test/regress/sql/opr_sanity.sql @@ -1195,11 +1195,13 @@ WHERE NOT ( -- GIN has six support functions. 1-3 are mandatory, 5 is optional, and -- at least one of 4 and 6 must be given. -- SP-GiST has five support functions, all mandatory + -- BRIN has four mandatory support functions, and a bunch of optionals amname = 'btree' AND procnums @> '{1}' OR amname = 'hash' AND procnums = '{1}' OR amname = 'gist' AND procnums @> '{1, 2, 3, 4, 5, 6, 7}' OR amname = 'gin' AND (procnums @> '{1, 2, 3}' AND (procnums && '{4, 6}')) OR - amname = 'spgist' AND procnums = '{1, 2, 3, 4, 5}' + amname = 'spgist' AND procnums = '{1, 2, 3, 4, 5}' OR + amname = 'brin' AND procnums @> '{1, 2, 3, 4}' ); -- Also, check if there are any pg_opclass entries that don't seem to have @@ -1218,7 +1220,8 @@ WHERE NOT ( amname = 'hash' AND procnums = '{1}' OR amname = 'gist' AND procnums @> '{1, 2, 3, 4, 5, 6, 7}' OR amname = 'gin' AND (procnums @> '{1, 2, 3}' AND (procnums && '{4, 6}')) OR - amname = 'spgist' AND procnums = '{1, 2, 3, 4, 5}' + amname = 'spgist' AND procnums = '{1, 2, 3, 4, 5}' OR + amname = 'brin' AND procnums @> '{1, 2, 3, 4}' ); -- Unfortunately, we can't check the amproc link very well because the |