diff options
Diffstat (limited to 'src/test/regress/sql')
50 files changed, 1029 insertions, 1010 deletions
diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql index 7cf86465e9..2f5d0e00f3 100644 --- a/src/test/regress/sql/aggregates.sql +++ b/src/test/regress/sql/aggregates.sql @@ -2,9 +2,24 @@ -- AGGREGATES -- +-- directory paths are passed to us in environment variables +\getenv abs_srcdir PG_ABS_SRCDIR + -- avoid bit-exact output here because operations may not be bit-exact. SET extra_float_digits = 0; +-- prepare some test data +CREATE TABLE aggtest ( + a int2, + b float4 +); + +\set filename :abs_srcdir '/data/agg.data' +COPY aggtest FROM :'filename'; + +ANALYZE aggtest; + + SELECT avg(four) AS avg_1 FROM onek; SELECT avg(a) AS avg_32 FROM aggtest WHERE a < 100; diff --git a/src/test/regress/sql/alter_generic.sql b/src/test/regress/sql/alter_generic.sql index 8c5d0e5e1f..de58d268d3 100644 --- a/src/test/regress/sql/alter_generic.sql +++ b/src/test/regress/sql/alter_generic.sql @@ -2,6 +2,17 @@ -- Test for ALTER some_object {RENAME TO, OWNER TO, SET SCHEMA} -- +-- directory paths and dlsuffix are passed to us in environment variables +\getenv libdir PG_LIBDIR +\getenv dlsuffix PG_DLSUFFIX + +\set regresslib :libdir '/regress' :dlsuffix + +CREATE FUNCTION test_opclass_options_func(internal) + RETURNS void + AS :'regresslib', 'test_opclass_options_func' + LANGUAGE C; + -- Clean up in case a prior regression run failed SET client_min_messages TO 'warning'; diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index 3ab7f392be..f774faf856 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -2,6 +2,9 @@ -- ARRAYS -- +-- directory paths are passed to us in environment variables +\getenv abs_srcdir PG_ABS_SRCDIR + CREATE TABLE arrtest ( a int2[], b int4[][][], @@ -12,6 +15,16 @@ CREATE TABLE arrtest ( g varchar(5)[] ); +CREATE TABLE array_op_test ( + seqno int4, + i int4[], + t text[] +); + +\set filename :abs_srcdir '/data/array.data' +COPY array_op_test FROM :'filename'; +ANALYZE array_op_test; + -- -- only the 'e' array is 0-based, the others are 1-based. -- @@ -153,6 +166,10 @@ UPDATE arrtest_s SET a[:] = '{23, 24, 25}'; -- fail, too small INSERT INTO arrtest_s VALUES(NULL, NULL); UPDATE arrtest_s SET a[:] = '{11, 12, 13, 14, 15}'; -- fail, no good with null +-- we want to work with a point_tbl that includes a null +CREATE TEMP TABLE point_tbl AS SELECT * FROM public.point_tbl; +INSERT INTO POINT_TBL(f1) VALUES (NULL); + -- check with fixed-length-array type, such as point SELECT f1[0:1] FROM POINT_TBL; SELECT f1[0:] FROM POINT_TBL; diff --git a/src/test/regress/sql/btree_index.sql b/src/test/regress/sql/btree_index.sql index c34502249f..239f4a4755 100644 --- a/src/test/regress/sql/btree_index.sql +++ b/src/test/regress/sql/btree_index.sql @@ -1,5 +1,64 @@ -- -- BTREE_INDEX +-- + +-- directory paths are passed to us in environment variables +\getenv abs_srcdir PG_ABS_SRCDIR + +CREATE TABLE bt_i4_heap ( + seqno int4, + random int4 +); + +CREATE TABLE bt_name_heap ( + seqno name, + random int4 +); + +CREATE TABLE bt_txt_heap ( + seqno text, + random int4 +); + +CREATE TABLE bt_f8_heap ( + seqno float8, + random int4 +); + +\set filename :abs_srcdir '/data/desc.data' +COPY bt_i4_heap FROM :'filename'; + +\set filename :abs_srcdir '/data/hash.data' +COPY bt_name_heap FROM :'filename'; + +\set filename :abs_srcdir '/data/desc.data' +COPY bt_txt_heap FROM :'filename'; + +\set filename :abs_srcdir '/data/hash.data' +COPY bt_f8_heap FROM :'filename'; + +ANALYZE bt_i4_heap; +ANALYZE bt_name_heap; +ANALYZE bt_txt_heap; +ANALYZE bt_f8_heap; + +-- +-- BTREE ascending/descending cases +-- +-- we load int4/text from pure descending data (each key is a new +-- low key) and name/f8 from pure ascending data (each key is a new +-- high key). we had a bug where new low keys would sometimes be +-- "lost". +-- +CREATE INDEX bt_i4_index ON bt_i4_heap USING btree (seqno int4_ops); + +CREATE INDEX bt_name_index ON bt_name_heap USING btree (seqno name_ops); + +CREATE INDEX bt_txt_index ON bt_txt_heap USING btree (seqno text_ops); + +CREATE INDEX bt_f8_index ON bt_f8_heap USING btree (seqno float8_ops); + +-- -- test retrieval of min/max keys for each index -- diff --git a/src/test/regress/sql/char.sql b/src/test/regress/sql/char.sql index 79e6e565ba..9c83c45e34 100644 --- a/src/test/regress/sql/char.sql +++ b/src/test/regress/sql/char.sql @@ -9,9 +9,10 @@ SELECT char 'c' = char 'c' AS true; -- -- Build a table for testing +-- (This temporarily hides the table created in test_setup.sql) -- -CREATE TABLE CHAR_TBL(f1 char); +CREATE TEMP TABLE CHAR_TBL(f1 char); INSERT INTO CHAR_TBL (f1) VALUES ('a'); @@ -63,13 +64,10 @@ DROP TABLE CHAR_TBL; -- -- Now test longer arrays of char -- +-- This char_tbl was already created and filled in test_setup.sql. +-- Here we just try to insert bad values. +-- -CREATE TABLE CHAR_TBL(f1 char(4)); - -INSERT INTO CHAR_TBL (f1) VALUES ('a'); -INSERT INTO CHAR_TBL (f1) VALUES ('ab'); -INSERT INTO CHAR_TBL (f1) VALUES ('abcd'); INSERT INTO CHAR_TBL (f1) VALUES ('abcde'); -INSERT INTO CHAR_TBL (f1) VALUES ('abcd '); SELECT * FROM CHAR_TBL; diff --git a/src/test/regress/sql/conversion.sql b/src/test/regress/sql/conversion.sql index e178e2479b..9a65fca91f 100644 --- a/src/test/regress/sql/conversion.sql +++ b/src/test/regress/sql/conversion.sql @@ -1,6 +1,17 @@ -- -- create user defined conversion -- + +-- directory paths and dlsuffix are passed to us in environment variables +\getenv libdir PG_LIBDIR +\getenv dlsuffix PG_DLSUFFIX + +\set regresslib :libdir '/regress' :dlsuffix + +CREATE FUNCTION test_enc_conversion(bytea, name, name, bool, validlen OUT int, result OUT bytea) + AS :'regresslib', 'test_enc_conversion' + LANGUAGE C STRICT; + CREATE USER regress_conversion_user WITH NOCREATEDB NOCREATEROLE; SET SESSION AUTHORIZATION regress_conversion_user; CREATE CONVERSION myconv FOR 'LATIN1' TO 'UTF8' FROM iso8859_1_to_utf8; diff --git a/src/test/regress/sql/copy.sql b/src/test/regress/sql/copy.sql index 016fedf675..eb302773ab 100644 --- a/src/test/regress/sql/copy.sql +++ b/src/test/regress/sql/copy.sql @@ -6,112 +6,6 @@ \getenv abs_srcdir PG_ABS_SRCDIR \getenv abs_builddir PG_ABS_BUILDDIR --- CLASS POPULATION --- (any resemblance to real life is purely coincidental) --- -\set filename :abs_srcdir '/data/agg.data' -COPY aggtest FROM :'filename'; - -\set filename :abs_srcdir '/data/onek.data' -COPY onek FROM :'filename'; - -\set filename :abs_builddir '/results/onek.data' -COPY onek TO :'filename'; - -DELETE FROM onek; - -COPY onek FROM :'filename'; - -\set filename :abs_srcdir '/data/tenk.data' -COPY tenk1 FROM :'filename'; - -\set filename :abs_srcdir '/data/rect.data' -COPY slow_emp4000 FROM :'filename'; - -\set filename :abs_srcdir '/data/person.data' -COPY person FROM :'filename'; - -\set filename :abs_srcdir '/data/emp.data' -COPY emp FROM :'filename'; - -\set filename :abs_srcdir '/data/student.data' -COPY student FROM :'filename'; - -\set filename :abs_srcdir '/data/stud_emp.data' -COPY stud_emp FROM :'filename'; - -\set filename :abs_srcdir '/data/streets.data' -COPY road FROM :'filename'; - -\set filename :abs_srcdir '/data/real_city.data' -COPY real_city FROM :'filename'; - -\set filename :abs_srcdir '/data/hash.data' -COPY hash_i4_heap FROM :'filename'; - -COPY hash_name_heap FROM :'filename'; - -COPY hash_txt_heap FROM :'filename'; - -COPY hash_f8_heap FROM :'filename'; - -\set filename :abs_srcdir '/data/tsearch.data' -COPY test_tsvector FROM :'filename'; - -\set filename :abs_srcdir '/data/jsonb.data' -COPY testjsonb FROM :'filename'; - --- the data in this file has a lot of duplicates in the index key --- fields, leading to long bucket chains and lots of table expansion. --- this is therefore a stress test of the bucket overflow code (unlike --- the data in hash.data, which has unique index keys). --- --- \set filename :abs_srcdir '/data/hashovfl.data' --- COPY hash_ovfl_heap FROM :'filename'; - -\set filename :abs_srcdir '/data/desc.data' -COPY bt_i4_heap FROM :'filename'; - -\set filename :abs_srcdir '/data/hash.data' -COPY bt_name_heap FROM :'filename'; - -\set filename :abs_srcdir '/data/desc.data' -COPY bt_txt_heap FROM :'filename'; - -\set filename :abs_srcdir '/data/hash.data' -COPY bt_f8_heap FROM :'filename'; - -\set filename :abs_srcdir '/data/array.data' -COPY array_op_test FROM :'filename'; - -\set filename :abs_srcdir '/data/array.data' -COPY array_index_op_test FROM :'filename'; - --- analyze all the data we just loaded, to ensure plan consistency --- in later tests - -ANALYZE aggtest; -ANALYZE onek; -ANALYZE tenk1; -ANALYZE slow_emp4000; -ANALYZE person; -ANALYZE emp; -ANALYZE student; -ANALYZE stud_emp; -ANALYZE road; -ANALYZE real_city; -ANALYZE hash_i4_heap; -ANALYZE hash_name_heap; -ANALYZE hash_txt_heap; -ANALYZE hash_f8_heap; -ANALYZE test_tsvector; -ANALYZE bt_i4_heap; -ANALYZE bt_name_heap; -ANALYZE bt_txt_heap; -ANALYZE bt_f8_heap; -ANALYZE array_op_test; -ANALYZE array_index_op_test; - --- test copying in CSV mode with various styles --- of embedded line ending characters diff --git a/src/test/regress/sql/create_function_0.sql b/src/test/regress/sql/create_function_0.sql index c5224742f9..54da95f249 100644 --- a/src/test/regress/sql/create_function_0.sql +++ b/src/test/regress/sql/create_function_0.sql @@ -1,98 +1,25 @@ -- -- CREATE_FUNCTION_0 -- +-- This script used to create C functions for other scripts to use. +-- But to get rid of the ordering dependencies that caused, such +-- functions are now made either in test_setup.sql or in the specific +-- test script that needs them. All that remains here is error cases. -- directory path and dlsuffix are passed to us in environment variables \getenv libdir PG_LIBDIR \getenv dlsuffix PG_DLSUFFIX -\set autoinclib :libdir '/autoinc' :dlsuffix -\set refintlib :libdir '/refint' :dlsuffix \set regresslib :libdir '/regress' :dlsuffix --- Create a bunch of C functions that will be used by later tests: - -CREATE FUNCTION check_primary_key () - RETURNS trigger - AS :'refintlib' - LANGUAGE C; - -CREATE FUNCTION check_foreign_key () - RETURNS trigger - AS :'refintlib' - LANGUAGE C; - -CREATE FUNCTION autoinc () - RETURNS trigger - AS :'autoinclib' - LANGUAGE C; - -CREATE FUNCTION trigger_return_old () - RETURNS trigger - AS :'regresslib' - LANGUAGE C; - -CREATE FUNCTION ttdummy () - RETURNS trigger - AS :'regresslib' - LANGUAGE C; - -CREATE FUNCTION set_ttdummy (int4) - RETURNS int4 - AS :'regresslib' - LANGUAGE C STRICT; - -CREATE FUNCTION make_tuple_indirect (record) - RETURNS record - AS :'regresslib' - LANGUAGE C STRICT; - -CREATE FUNCTION test_atomic_ops() - RETURNS bool - AS :'regresslib' - LANGUAGE C; - -CREATE FUNCTION test_fdw_handler() - RETURNS fdw_handler - AS :'regresslib', 'test_fdw_handler' - LANGUAGE C; - -CREATE FUNCTION test_support_func(internal) - RETURNS internal - AS :'regresslib', 'test_support_func' - LANGUAGE C STRICT; - -CREATE FUNCTION test_opclass_options_func(internal) - RETURNS void - AS :'regresslib', 'test_opclass_options_func' - LANGUAGE C; - -CREATE FUNCTION test_enc_conversion(bytea, name, name, bool, validlen OUT int, result OUT bytea) - AS :'regresslib', 'test_enc_conversion' - LANGUAGE C STRICT; - -CREATE FUNCTION binary_coercible(oid, oid) - RETURNS bool - AS :'regresslib', 'binary_coercible' - LANGUAGE C STRICT STABLE PARALLEL SAFE; +-- +-- Check LOAD command. (The alternative of implicitly loading the library +-- is checked in many other test scripts.) +-- +LOAD :'regresslib'; -- Things that shouldn't work: -CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL - AS 'SELECT ''not an integer'';'; - -CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL - AS 'not even SQL'; - -CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL - AS 'SELECT 1, 2, 3;'; - -CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL - AS 'SELECT $2;'; - -CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL - AS 'a', 'b'; - CREATE FUNCTION test1 (int) RETURNS int LANGUAGE C AS 'nosuchfile'; diff --git a/src/test/regress/sql/create_function_1.sql b/src/test/regress/sql/create_function_1.sql deleted file mode 100644 index 34cc7c6efc..0000000000 --- a/src/test/regress/sql/create_function_1.sql +++ /dev/null @@ -1,36 +0,0 @@ --- --- CREATE_FUNCTION_1 --- - --- directory path and dlsuffix are passed to us in environment variables -\getenv libdir PG_LIBDIR -\getenv dlsuffix PG_DLSUFFIX - -\set regresslib :libdir '/regress' :dlsuffix - --- Create C functions needed by create_type.sql - -CREATE FUNCTION widget_in(cstring) - RETURNS widget - AS :'regresslib' - LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION widget_out(widget) - RETURNS cstring - AS :'regresslib' - LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION int44in(cstring) - RETURNS city_budget - AS :'regresslib' - LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION int44out(city_budget) - RETURNS cstring - AS :'regresslib' - LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION test_canonicalize_path(text) - RETURNS text - AS :'regresslib' - LANGUAGE C STRICT IMMUTABLE; diff --git a/src/test/regress/sql/create_function_2.sql b/src/test/regress/sql/create_function_2.sql deleted file mode 100644 index 67510aed23..0000000000 --- a/src/test/regress/sql/create_function_2.sql +++ /dev/null @@ -1,96 +0,0 @@ --- --- CREATE_FUNCTION_2 --- - --- directory path and dlsuffix are passed to us in environment variables -\getenv libdir PG_LIBDIR -\getenv dlsuffix PG_DLSUFFIX - -\set regresslib :libdir '/regress' :dlsuffix - - -CREATE FUNCTION hobbies(person) - RETURNS setof hobbies_r - AS 'select * from hobbies_r where person = $1.name' - LANGUAGE SQL; - - -CREATE FUNCTION hobby_construct(text, text) - RETURNS hobbies_r - AS 'select $1 as name, $2 as hobby' - LANGUAGE SQL; - - -CREATE FUNCTION hobby_construct_named(name text, hobby text) - RETURNS hobbies_r - AS 'select name, hobby' - LANGUAGE SQL; - - -CREATE FUNCTION hobbies_by_name(hobbies_r.name%TYPE) - RETURNS hobbies_r.person%TYPE - AS 'select person from hobbies_r where name = $1' - LANGUAGE SQL; - - -CREATE FUNCTION equipment(hobbies_r) - RETURNS setof equipment_r - AS 'select * from equipment_r where hobby = $1.name' - LANGUAGE SQL; - - -CREATE FUNCTION equipment_named(hobby hobbies_r) - RETURNS setof equipment_r - AS 'select * from equipment_r where equipment_r.hobby = equipment_named.hobby.name' - LANGUAGE SQL; - -CREATE FUNCTION equipment_named_ambiguous_1a(hobby hobbies_r) - RETURNS setof equipment_r - AS 'select * from equipment_r where hobby = equipment_named_ambiguous_1a.hobby.name' - LANGUAGE SQL; - -CREATE FUNCTION equipment_named_ambiguous_1b(hobby hobbies_r) - RETURNS setof equipment_r - AS 'select * from equipment_r where equipment_r.hobby = hobby.name' - LANGUAGE SQL; - -CREATE FUNCTION equipment_named_ambiguous_1c(hobby hobbies_r) - RETURNS setof equipment_r - AS 'select * from equipment_r where hobby = hobby.name' - LANGUAGE SQL; - -CREATE FUNCTION equipment_named_ambiguous_2a(hobby text) - RETURNS setof equipment_r - AS 'select * from equipment_r where hobby = equipment_named_ambiguous_2a.hobby' - LANGUAGE SQL; - -CREATE FUNCTION equipment_named_ambiguous_2b(hobby text) - RETURNS setof equipment_r - AS 'select * from equipment_r where equipment_r.hobby = hobby' - LANGUAGE SQL; - - -CREATE FUNCTION pt_in_widget(point, widget) - RETURNS bool - AS :'regresslib' - LANGUAGE C STRICT; - -CREATE FUNCTION overpaid(emp) - RETURNS bool - AS :'regresslib' - LANGUAGE C STRICT; - -CREATE FUNCTION interpt_pp(path, path) - RETURNS point - AS :'regresslib' - LANGUAGE C STRICT; - -CREATE FUNCTION reverse_name(name) - RETURNS name - AS :'regresslib' - LANGUAGE C STRICT; - --- --- Function dynamic loading --- -LOAD :'regresslib'; diff --git a/src/test/regress/sql/create_function_3.sql b/src/test/regress/sql/create_function_3.sql index 7edd757b8f..254f444d94 100644 --- a/src/test/regress/sql/create_function_3.sql +++ b/src/test/regress/sql/create_function_3.sql @@ -385,6 +385,23 @@ CREATE FUNCTION voidtest5(a int) RETURNS SETOF VOID LANGUAGE SQL AS $$ SELECT generate_series(1, a) $$ STABLE; SELECT * FROM voidtest5(3); +-- Things that shouldn't work: + +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL + AS 'SELECT ''not an integer'';'; + +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL + AS 'not even SQL'; + +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL + AS 'SELECT 1, 2, 3;'; + +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL + AS 'SELECT $2;'; + +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL + AS 'a', 'b'; + -- Cleanup DROP SCHEMA temp_func_test CASCADE; DROP USER regress_unpriv_user; diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql index 9003950a1f..a06c98074b 100644 --- a/src/test/regress/sql/create_index.sql +++ b/src/test/regress/sql/create_index.sql @@ -3,6 +3,9 @@ -- Create ancillary data structures (i.e. indices) -- +-- directory paths are passed to us in environment variables +\getenv abs_srcdir PG_ABS_SRCDIR + -- -- BTREE -- @@ -44,22 +47,6 @@ COMMENT ON INDEX six IS 'good index'; COMMENT ON INDEX six IS NULL; -- --- BTREE ascending/descending cases --- --- we load int4/text from pure descending data (each key is a new --- low key) and name/f8 from pure ascending data (each key is a new --- high key). we had a bug where new low keys would sometimes be --- "lost". --- -CREATE INDEX bt_i4_index ON bt_i4_heap USING btree (seqno int4_ops); - -CREATE INDEX bt_name_index ON bt_name_heap USING btree (seqno name_ops); - -CREATE INDEX bt_txt_index ON bt_txt_heap USING btree (seqno text_ops); - -CREATE INDEX bt_f8_index ON bt_f8_heap USING btree (seqno float8_ops); - --- -- BTREE partial indices -- CREATE INDEX onek2_u1_prtl ON onek2 USING btree(unique1 int4_ops) @@ -74,12 +61,27 @@ CREATE INDEX onek2_stu1_prtl ON onek2 USING btree(stringu1 name_ops) -- -- GiST (rtree-equivalent opclasses only) -- -CREATE INDEX grect2ind ON fast_emp4000 USING gist (home_base); -CREATE INDEX gpolygonind ON polygon_tbl USING gist (f1); +CREATE TABLE slow_emp4000 ( + home_base box +); + +CREATE TABLE fast_emp4000 ( + home_base box +); + +\set filename :abs_srcdir '/data/rect.data' +COPY slow_emp4000 FROM :'filename'; + +INSERT INTO fast_emp4000 SELECT * FROM slow_emp4000; -CREATE INDEX gcircleind ON circle_tbl USING gist (f1); +ANALYZE slow_emp4000; +ANALYZE fast_emp4000; +CREATE INDEX grect2ind ON fast_emp4000 USING gist (home_base); + +-- we want to work with a point_tbl that includes a null +CREATE TEMP TABLE point_tbl AS SELECT * FROM public.point_tbl; INSERT INTO POINT_TBL(f1) VALUES (NULL); CREATE INDEX gpointind ON point_tbl USING gist (f1); @@ -114,12 +116,6 @@ SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; -SELECT * FROM polygon_tbl WHERE f1 @> '((1,1),(2,2),(2,1))'::polygon - ORDER BY (poly_center(f1))[0]; - -SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) - ORDER BY area(f1); - SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon; SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle; @@ -176,18 +172,6 @@ SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; EXPLAIN (COSTS OFF) -SELECT * FROM polygon_tbl WHERE f1 @> '((1,1),(2,2),(2,1))'::polygon - ORDER BY (poly_center(f1))[0]; -SELECT * FROM polygon_tbl WHERE f1 @> '((1,1),(2,2),(2,1))'::polygon - ORDER BY (poly_center(f1))[0]; - -EXPLAIN (COSTS OFF) -SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) - ORDER BY area(f1); -SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) - ORDER BY area(f1); - -EXPLAIN (COSTS OFF) SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon; SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon; @@ -274,6 +258,21 @@ RESET enable_bitmapscan; -- Note: GIN currently supports only bitmap scans, not plain indexscans -- +CREATE TABLE array_index_op_test ( + seqno int4, + i int4[], + t text[] +); + +\set filename :abs_srcdir '/data/array.data' +COPY array_index_op_test FROM :'filename'; +ANALYZE array_index_op_test; + +SELECT * FROM array_index_op_test WHERE i = '{NULL}' ORDER BY seqno; +SELECT * FROM array_index_op_test WHERE i @> '{NULL}' ORDER BY seqno; +SELECT * FROM array_index_op_test WHERE i && '{NULL}' ORDER BY seqno; +SELECT * FROM array_index_op_test WHERE i <@ '{NULL}' ORDER BY seqno; + SET enable_seqscan = OFF; SET enable_indexscan = OFF; SET enable_bitmapscan = ON; @@ -295,10 +294,6 @@ SELECT * FROM array_index_op_test WHERE i = '{}' ORDER BY seqno; SELECT * FROM array_index_op_test WHERE i @> '{}' ORDER BY seqno; SELECT * FROM array_index_op_test WHERE i && '{}' ORDER BY seqno; SELECT * FROM array_index_op_test WHERE i <@ '{}' ORDER BY seqno; -SELECT * FROM array_op_test WHERE i = '{NULL}' ORDER BY seqno; -SELECT * FROM array_op_test WHERE i @> '{NULL}' ORDER BY seqno; -SELECT * FROM array_op_test WHERE i && '{NULL}' ORDER BY seqno; -SELECT * FROM array_op_test WHERE i <@ '{NULL}' ORDER BY seqno; CREATE INDEX textarrayidx ON array_index_op_test USING gin (t); @@ -331,8 +326,6 @@ SELECT * FROM array_index_op_test WHERE t && '{AAAAAAA80240}' ORDER BY seqno; SELECT * FROM array_index_op_test WHERE i @> '{32}' AND t && '{AAAAAAA80240}' ORDER BY seqno; SELECT * FROM array_index_op_test WHERE i && '{32}' AND t @> '{AAAAAAA80240}' ORDER BY seqno; SELECT * FROM array_index_op_test WHERE t = '{}' ORDER BY seqno; -SELECT * FROM array_op_test WHERE i = '{NULL}' ORDER BY seqno; -SELECT * FROM array_op_test WHERE i <@ '{NULL}' ORDER BY seqno; RESET enable_seqscan; RESET enable_indexscan; @@ -362,14 +355,6 @@ CREATE INDEX gin_relopts_test ON array_index_op_test USING gin (i) -- -- HASH -- -CREATE INDEX hash_i4_index ON hash_i4_heap USING hash (random int4_ops); - -CREATE INDEX hash_name_index ON hash_name_heap USING hash (random name_ops); - -CREATE INDEX hash_txt_index ON hash_txt_heap USING hash (random text_ops); - -CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops) WITH (fillfactor=60); - CREATE UNLOGGED TABLE unlogged_hash_table (id int4); CREATE INDEX unlogged_hash_index ON unlogged_hash_table USING hash (id int4_ops); DROP TABLE unlogged_hash_table; @@ -485,15 +470,6 @@ ALTER TABLE covering_index_heap ADD CONSTRAINT covering_pkey PRIMARY KEY USING I covering_pkey; DROP TABLE covering_index_heap; - --- --- Also try building functional, expressional, and partial indexes on --- tables that already contain data. --- -create unique index hash_f8_index_1 on hash_f8_heap(abs(random)); -create unique index hash_f8_index_2 on hash_f8_heap((seqno + 1), random); -create unique index hash_f8_index_3 on hash_f8_heap(random) where seqno > 1000; - -- -- Try some concurrent index builds -- @@ -770,8 +746,6 @@ SELECT count(*) FROM dupindexcols -- Check ordering of =ANY indexqual results (bug in 9.2.0) -- -vacuum tenk1; -- ensure we get consistent plans here - explain (costs off) SELECT unique1 FROM tenk1 WHERE unique1 IN (1,42,7) diff --git a/src/test/regress/sql/create_misc.sql b/src/test/regress/sql/create_misc.sql index c7d0d064c3..6fb9fdab4c 100644 --- a/src/test/regress/sql/create_misc.sql +++ b/src/test/regress/sql/create_misc.sql @@ -2,63 +2,37 @@ -- CREATE_MISC -- --- CLASS POPULATION --- (any resemblance to real life is purely coincidental) -- +-- a is the type root +-- b and c inherit from a (one-level single inheritance) +-- d inherits from b and c (two-level multiple inheritance) +-- e inherits from c (two-level single inheritance) +-- f inherits from e (three-level single inheritance) +-- +CREATE TABLE a_star ( + class char, + a int4 +); -INSERT INTO tenk2 SELECT * FROM tenk1; - -CREATE TABLE onek2 AS SELECT * FROM onek; - -INSERT INTO fast_emp4000 SELECT * FROM slow_emp4000; - -SELECT * - INTO TABLE Bprime - FROM tenk1 - WHERE unique2 < 1000; - -INSERT INTO hobbies_r (name, person) - SELECT 'posthacking', p.name - FROM person* p - WHERE p.name = 'mike' or p.name = 'jeff'; - -INSERT INTO hobbies_r (name, person) - SELECT 'basketball', p.name - FROM person p - WHERE p.name = 'joe' or p.name = 'sally'; - -INSERT INTO hobbies_r (name) VALUES ('skywalking'); - -INSERT INTO equipment_r (name, hobby) VALUES ('advil', 'posthacking'); - -INSERT INTO equipment_r (name, hobby) VALUES ('peet''s coffee', 'posthacking'); - -INSERT INTO equipment_r (name, hobby) VALUES ('hightops', 'basketball'); - -INSERT INTO equipment_r (name, hobby) VALUES ('guts', 'skywalking'); - -INSERT INTO city VALUES -('Podunk', '(1,2),(3,4)', '100,127,1000'), -('Gotham', '(1000,34),(1100,334)', '123456,127,-1000,6789'); -TABLE city; +CREATE TABLE b_star ( + b text +) INHERITS (a_star); -SELECT * - INTO TABLE ramp - FROM road - WHERE name ~ '.*Ramp'; +CREATE TABLE c_star ( + c name +) INHERITS (a_star); -INSERT INTO ihighway - SELECT * - FROM road - WHERE name ~ 'I- .*'; +CREATE TABLE d_star ( + d float8 +) INHERITS (b_star, c_star); -INSERT INTO shighway - SELECT * - FROM road - WHERE name ~ 'State Hwy.*'; +CREATE TABLE e_star ( + e int2 +) INHERITS (c_star); -UPDATE shighway - SET surface = 'asphalt'; +CREATE TABLE f_star ( + f polygon +) INHERITS (e_star); INSERT INTO a_star (class, a) VALUES ('a', 1); @@ -200,18 +174,85 @@ ANALYZE d_star; ANALYZE e_star; ANALYZE f_star; - -- --- for internal portal (cursor) tests +-- inheritance stress test -- -CREATE TABLE iportaltest ( - i int4, - d float4, - p polygon -); +SELECT * FROM a_star*; + +SELECT * + FROM b_star* x + WHERE x.b = text 'bumble' or x.a < 3; + +SELECT class, a + FROM c_star* x + WHERE x.c ~ text 'hi'; + +SELECT class, b, c + FROM d_star* x + WHERE x.a < 100; + +SELECT class, c FROM e_star* x WHERE x.c NOTNULL; + +SELECT * FROM f_star* x WHERE x.c ISNULL; + +-- grouping and aggregation on inherited sets have been busted in the past... + +SELECT sum(a) FROM a_star*; + +SELECT class, sum(a) FROM a_star* GROUP BY class ORDER BY class; + + +ALTER TABLE f_star RENAME COLUMN f TO ff; + +ALTER TABLE e_star* RENAME COLUMN e TO ee; + +ALTER TABLE d_star* RENAME COLUMN d TO dd; + +ALTER TABLE c_star* RENAME COLUMN c TO cc; + +ALTER TABLE b_star* RENAME COLUMN b TO bb; + +ALTER TABLE a_star* RENAME COLUMN a TO aa; + +SELECT class, aa + FROM a_star* x + WHERE aa ISNULL; + +-- As of Postgres 7.1, ALTER implicitly recurses, +-- so this should be same as ALTER a_star* + +ALTER TABLE a_star RENAME COLUMN aa TO foo; + +SELECT class, foo + FROM a_star* x + WHERE x.foo >= 2; + +ALTER TABLE a_star RENAME COLUMN foo TO aa; + +SELECT * + from a_star* + WHERE aa < 1000; + +ALTER TABLE f_star ADD COLUMN f int4; + +UPDATE f_star SET f = 10; + +ALTER TABLE e_star* ADD COLUMN e int4; + +--UPDATE e_star* SET e = 42; + +SELECT * FROM e_star*; + +ALTER TABLE a_star* ADD COLUMN a text; + +-- That ALTER TABLE should have added TOAST tables. +SELECT relname, reltoastrelid <> 0 AS has_toast_table + FROM pg_class + WHERE oid::regclass IN ('a_star', 'c_star') + ORDER BY 1; -INSERT INTO iportaltest (i, d, p) - VALUES (1, 3.567, '(3.0,1.0),(4.0,2.0)'::polygon); +--UPDATE b_star* +-- SET a = text 'gazpacho' +-- WHERE aa > 4; -INSERT INTO iportaltest (i, d, p) - VALUES (2, 89.05, '(4.0,2.0),(3.0,1.0)'::polygon); +SELECT class, aa, a FROM a_star*; diff --git a/src/test/regress/sql/create_operator.sql b/src/test/regress/sql/create_operator.sql index 4ff2c0ff21..f53e24db3c 100644 --- a/src/test/regress/sql/create_operator.sql +++ b/src/test/regress/sql/create_operator.sql @@ -9,14 +9,6 @@ CREATE OPERATOR ## ( commutator = ## ); -CREATE OPERATOR <% ( - leftarg = point, - rightarg = widget, - procedure = pt_in_widget, - commutator = >% , - negator = >=% -); - CREATE OPERATOR @#@ ( rightarg = int8, -- prefix procedure = factorial @@ -28,8 +20,7 @@ CREATE OPERATOR #%# ( ); -- Test operator created above -SELECT point '(1,2)' <% widget '(0,0,3)' AS t, - point '(1,2)' <% widget '(0,0,1)' AS f; +SELECT @#@ 24; -- Test comments COMMENT ON OPERATOR ###### (NONE, int4) IS 'bad prefix'; diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql index cc41f58ba2..37dac6b5fb 100644 --- a/src/test/regress/sql/create_table.sql +++ b/src/test/regress/sql/create_table.sql @@ -2,240 +2,7 @@ -- CREATE_TABLE -- --- --- CLASS DEFINITIONS --- -CREATE TABLE hobbies_r ( - name text, - person text -); - -CREATE TABLE equipment_r ( - name text, - hobby text -); - -CREATE TABLE onek ( - unique1 int4, - unique2 int4, - two int4, - four int4, - ten int4, - twenty int4, - hundred int4, - thousand int4, - twothousand int4, - fivethous int4, - tenthous int4, - odd int4, - even int4, - stringu1 name, - stringu2 name, - string4 name -); - -CREATE TABLE tenk1 ( - unique1 int4, - unique2 int4, - two int4, - four int4, - ten int4, - twenty int4, - hundred int4, - thousand int4, - twothousand int4, - fivethous int4, - tenthous int4, - odd int4, - even int4, - stringu1 name, - stringu2 name, - string4 name -); - -CREATE TABLE tenk2 ( - unique1 int4, - unique2 int4, - two int4, - four int4, - ten int4, - twenty int4, - hundred int4, - thousand int4, - twothousand int4, - fivethous int4, - tenthous int4, - odd int4, - even int4, - stringu1 name, - stringu2 name, - string4 name -); - - -CREATE TABLE person ( - name text, - age int4, - location point -); - - -CREATE TABLE emp ( - salary int4, - manager name -) INHERITS (person); - - -CREATE TABLE student ( - gpa float8 -) INHERITS (person); - - -CREATE TABLE stud_emp ( - percent int4 -) INHERITS (emp, student); - - -CREATE TABLE city ( - name name, - location box, - budget city_budget -); - -CREATE TABLE dept ( - dname name, - mgrname text -); - -CREATE TABLE slow_emp4000 ( - home_base box -); - -CREATE TABLE fast_emp4000 ( - home_base box -); - -CREATE TABLE road ( - name text, - thepath path -); - -CREATE TABLE ihighway () INHERITS (road); - -CREATE TABLE shighway ( - surface text -) INHERITS (road); - -CREATE TABLE real_city ( - pop int4, - cname text, - outline path -); - --- --- test the "star" operators a bit more thoroughly -- this time, --- throw in lots of NULL fields... --- --- a is the type root --- b and c inherit from a (one-level single inheritance) --- d inherits from b and c (two-level multiple inheritance) --- e inherits from c (two-level single inheritance) --- f inherits from e (three-level single inheritance) --- -CREATE TABLE a_star ( - class char, - a int4 -); - -CREATE TABLE b_star ( - b text -) INHERITS (a_star); - -CREATE TABLE c_star ( - c name -) INHERITS (a_star); - -CREATE TABLE d_star ( - d float8 -) INHERITS (b_star, c_star); - -CREATE TABLE e_star ( - e int2 -) INHERITS (c_star); - -CREATE TABLE f_star ( - f polygon -) INHERITS (e_star); - -CREATE TABLE aggtest ( - a int2, - b float4 -); - -CREATE TABLE hash_i4_heap ( - seqno int4, - random int4 -); - -CREATE TABLE hash_name_heap ( - seqno int4, - random name -); - -CREATE TABLE hash_txt_heap ( - seqno int4, - random text -); - -CREATE TABLE hash_f8_heap ( - seqno int4, - random float8 -); - --- don't include the hash_ovfl_heap stuff in the distribution --- the data set is too large for what it's worth --- --- CREATE TABLE hash_ovfl_heap ( --- x int4, --- y int4 --- ); - -CREATE TABLE bt_i4_heap ( - seqno int4, - random int4 -); - -CREATE TABLE bt_name_heap ( - seqno name, - random int4 -); - -CREATE TABLE bt_txt_heap ( - seqno text, - random int4 -); - -CREATE TABLE bt_f8_heap ( - seqno float8, - random int4 -); - -CREATE TABLE array_op_test ( - seqno int4, - i int4[], - t text[] -); - -CREATE TABLE array_index_op_test ( - seqno int4, - i int4[], - t text[] -); - -CREATE TABLE testjsonb ( - j jsonb -); - +-- Error cases CREATE TABLE unknowntab ( u unknown -- fail ); @@ -244,15 +11,6 @@ CREATE TYPE unknown_comptype AS ( u unknown -- fail ); -CREATE TABLE IF NOT EXISTS test_tsvector( - t text, - a tsvector -); - -CREATE TABLE IF NOT EXISTS test_tsvector( - t text -); - -- invalid: non-lowercase quoted reloptions identifiers CREATE TABLE tas_case WITH ("Fillfactor" = 10) AS SELECT 1 a; diff --git a/src/test/regress/sql/create_type.sql b/src/test/regress/sql/create_type.sql index a32a9e6795..c6fc4f9029 100644 --- a/src/test/regress/sql/create_type.sql +++ b/src/test/regress/sql/create_type.sql @@ -2,11 +2,36 @@ -- CREATE_TYPE -- +-- directory path and dlsuffix are passed to us in environment variables +\getenv libdir PG_LIBDIR +\getenv dlsuffix PG_DLSUFFIX + +\set regresslib :libdir '/regress' :dlsuffix + -- --- Note: widget_in/out were created in create_function_1, without any --- prior shell-type creation. These commands therefore complete a test --- of the "old style" approach of making the functions first. +-- Test the "old style" approach of making the I/O functions first, +-- with no explicit shell type creation. -- +CREATE FUNCTION widget_in(cstring) + RETURNS widget + AS :'regresslib' + LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION widget_out(widget) + RETURNS cstring + AS :'regresslib' + LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION int44in(cstring) + RETURNS city_budget + AS :'regresslib' + LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION int44out(city_budget) + RETURNS cstring + AS :'regresslib' + LANGUAGE C STRICT IMMUTABLE; + CREATE TYPE widget ( internallength = 24, input = widget_in, @@ -167,6 +192,37 @@ select format_type('bpchar'::regtype, null); -- this behavior difference is intentional select format_type('bpchar'::regtype, -1); +-- Test creation of an operator over a user-defined type + +CREATE FUNCTION pt_in_widget(point, widget) + RETURNS bool + AS :'regresslib' + LANGUAGE C STRICT; + +CREATE OPERATOR <% ( + leftarg = point, + rightarg = widget, + procedure = pt_in_widget, + commutator = >% , + negator = >=% +); + +SELECT point '(1,2)' <% widget '(0,0,3)' AS t, + point '(1,2)' <% widget '(0,0,1)' AS f; + +-- exercise city_budget type +CREATE TABLE city ( + name name, + location box, + budget city_budget +); + +INSERT INTO city VALUES +('Podunk', '(1,2),(3,4)', '100,127,1000'), +('Gotham', '(1000,34),(1100,334)', '123456,127,-1000,6789'); + +TABLE city; + -- -- Test CREATE/ALTER TYPE using a type that's compatible with varchar, -- so we can re-use those support functions diff --git a/src/test/regress/sql/create_view.sql b/src/test/regress/sql/create_view.sql index 82df4b7cac..6bb5b8df5e 100644 --- a/src/test/regress/sql/create_view.sql +++ b/src/test/regress/sql/create_view.sql @@ -4,16 +4,43 @@ -- (this also tests the query rewrite system) -- +-- directory paths and dlsuffix are passed to us in environment variables +\getenv abs_srcdir PG_ABS_SRCDIR +\getenv libdir PG_LIBDIR +\getenv dlsuffix PG_DLSUFFIX + +\set regresslib :libdir '/regress' :dlsuffix + +CREATE FUNCTION interpt_pp(path, path) + RETURNS point + AS :'regresslib' + LANGUAGE C STRICT; + +CREATE TABLE real_city ( + pop int4, + cname text, + outline path +); + +\set filename :abs_srcdir '/data/real_city.data' +COPY real_city FROM :'filename'; +ANALYZE real_city; + +SELECT * + INTO TABLE ramp + FROM ONLY road + WHERE name ~ '.*Ramp'; + CREATE VIEW street AS SELECT r.name, r.thepath, c.cname AS cname FROM ONLY road r, real_city c - WHERE c.outline ## r.thepath; + WHERE c.outline ?# r.thepath; CREATE VIEW iexit AS SELECT ih.name, ih.thepath, interpt_pp(ih.thepath, r.thepath) AS exit FROM ihighway ih, ramp r - WHERE ih.thepath ## r.thepath; + WHERE ih.thepath ?# r.thepath; CREATE VIEW toyemp AS SELECT name, age, location, 12*salary AS annualsal diff --git a/src/test/regress/sql/errors.sql b/src/test/regress/sql/errors.sql index a7fcf72dd4..52474e858d 100644 --- a/src/test/regress/sql/errors.sql +++ b/src/test/regress/sql/errors.sql @@ -77,7 +77,7 @@ alter table nonesuch rename to newnonesuch; alter table nonesuch rename to stud_emp; -- conflict -alter table stud_emp rename to aggtest; +alter table stud_emp rename to student; -- self-conflict alter table stud_emp rename to stud_emp; diff --git a/src/test/regress/sql/expressions.sql b/src/test/regress/sql/expressions.sql index 755370f358..0e163cc0d7 100644 --- a/src/test/regress/sql/expressions.sql +++ b/src/test/regress/sql/expressions.sql @@ -3,7 +3,7 @@ -- -- --- Tests for SQLVAlueFunction +-- Tests for SQLValueFunction -- @@ -39,35 +39,6 @@ RESET search_path; -- --- Tests for BETWEEN --- - -explain (costs off) -select count(*) from date_tbl - where f1 between '1997-01-01' and '1998-01-01'; -select count(*) from date_tbl - where f1 between '1997-01-01' and '1998-01-01'; - -explain (costs off) -select count(*) from date_tbl - where f1 not between '1997-01-01' and '1998-01-01'; -select count(*) from date_tbl - where f1 not between '1997-01-01' and '1998-01-01'; - -explain (costs off) -select count(*) from date_tbl - where f1 between symmetric '1997-01-01' and '1998-01-01'; -select count(*) from date_tbl - where f1 between symmetric '1997-01-01' and '1998-01-01'; - -explain (costs off) -select count(*) from date_tbl - where f1 not between symmetric '1997-01-01' and '1998-01-01'; -select count(*) from date_tbl - where f1 not between symmetric '1997-01-01' and '1998-01-01'; - - --- -- Test parsing of a no-op cast to a type with unspecified typmod -- begin; diff --git a/src/test/regress/sql/float8.sql b/src/test/regress/sql/float8.sql index 97f0c3bb2f..03c134b078 100644 --- a/src/test/regress/sql/float8.sql +++ b/src/test/regress/sql/float8.sql @@ -2,7 +2,12 @@ -- FLOAT8 -- -CREATE TABLE FLOAT8_TBL(f1 float8); +-- +-- Build a table for testing +-- (This temporarily hides the table created in test_setup.sql) +-- + +CREATE TEMP TABLE FLOAT8_TBL(f1 float8); INSERT INTO FLOAT8_TBL(f1) VALUES (' 0.0 '); INSERT INTO FLOAT8_TBL(f1) VALUES ('1004.30 '); @@ -229,20 +234,9 @@ INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400'); INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400'); --- maintain external table consistency across platforms --- delete all values and reinsert well-behaved ones - -DELETE FROM FLOAT8_TBL; - -INSERT INTO FLOAT8_TBL(f1) VALUES ('0.0'); - -INSERT INTO FLOAT8_TBL(f1) VALUES ('-34.84'); - -INSERT INTO FLOAT8_TBL(f1) VALUES ('-1004.30'); - -INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e+200'); +DROP TABLE FLOAT8_TBL; -INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e-200'); +-- Check the float8 values exported for use by other tests SELECT * FROM FLOAT8_TBL; diff --git a/src/test/regress/sql/foreign_data.sql b/src/test/regress/sql/foreign_data.sql index a65f4ffdca..9dfff66f54 100644 --- a/src/test/regress/sql/foreign_data.sql +++ b/src/test/regress/sql/foreign_data.sql @@ -2,6 +2,17 @@ -- Test foreign-data wrapper and server management. -- +-- directory paths and dlsuffix are passed to us in environment variables +\getenv libdir PG_LIBDIR +\getenv dlsuffix PG_DLSUFFIX + +\set regresslib :libdir '/regress' :dlsuffix + +CREATE FUNCTION test_fdw_handler() + RETURNS fdw_handler + AS :'regresslib', 'test_fdw_handler' + LANGUAGE C; + -- Clean up in case a prior regression run failed -- Suppress NOTICE messages when roles don't exist diff --git a/src/test/regress/sql/geometry.sql b/src/test/regress/sql/geometry.sql index 0aa06bcc9d..8928d04aa3 100644 --- a/src/test/regress/sql/geometry.sql +++ b/src/test/regress/sql/geometry.sql @@ -497,3 +497,29 @@ SELECT c.f1, p.f1, c.f1 / p.f1 FROM CIRCLE_TBL c, POINT_TBL p WHERE p.f1 ~= '(0, -- Distance to polygon SELECT c.f1, p.f1, c.f1 <-> p.f1 FROM CIRCLE_TBL c, POLYGON_TBL p; + +-- Check index behavior for circles + +CREATE INDEX gcircleind ON circle_tbl USING gist (f1); + +SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) + ORDER BY area(f1); + +EXPLAIN (COSTS OFF) +SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) + ORDER BY area(f1); +SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) + ORDER BY area(f1); + +-- Check index behavior for polygons + +CREATE INDEX gpolygonind ON polygon_tbl USING gist (f1); + +SELECT * FROM polygon_tbl WHERE f1 @> '((1,1),(2,2),(2,1))'::polygon + ORDER BY (poly_center(f1))[0]; + +EXPLAIN (COSTS OFF) +SELECT * FROM polygon_tbl WHERE f1 @> '((1,1),(2,2),(2,1))'::polygon + ORDER BY (poly_center(f1))[0]; +SELECT * FROM polygon_tbl WHERE f1 @> '((1,1),(2,2),(2,1))'::polygon + ORDER BY (poly_center(f1))[0]; diff --git a/src/test/regress/sql/hash_index.sql b/src/test/regress/sql/hash_index.sql index 4d1aa020a9..527024f710 100644 --- a/src/test/regress/sql/hash_index.sql +++ b/src/test/regress/sql/hash_index.sql @@ -1,8 +1,70 @@ -- -- HASH_INDEX --- grep 843938989 hash.data -- +-- directory paths are passed to us in environment variables +\getenv abs_srcdir PG_ABS_SRCDIR + +CREATE TABLE hash_i4_heap ( + seqno int4, + random int4 +); + +CREATE TABLE hash_name_heap ( + seqno int4, + random name +); + +CREATE TABLE hash_txt_heap ( + seqno int4, + random text +); + +CREATE TABLE hash_f8_heap ( + seqno int4, + random float8 +); + +\set filename :abs_srcdir '/data/hash.data' +COPY hash_i4_heap FROM :'filename'; +COPY hash_name_heap FROM :'filename'; +COPY hash_txt_heap FROM :'filename'; +COPY hash_f8_heap FROM :'filename'; + +-- the data in this file has a lot of duplicates in the index key +-- fields, leading to long bucket chains and lots of table expansion. +-- this is therefore a stress test of the bucket overflow code (unlike +-- the data in hash.data, which has unique index keys). +-- +-- \set filename :abs_srcdir '/data/hashovfl.data' +-- COPY hash_ovfl_heap FROM :'filename'; + +ANALYZE hash_i4_heap; +ANALYZE hash_name_heap; +ANALYZE hash_txt_heap; +ANALYZE hash_f8_heap; + +CREATE INDEX hash_i4_index ON hash_i4_heap USING hash (random int4_ops); + +CREATE INDEX hash_name_index ON hash_name_heap USING hash (random name_ops); + +CREATE INDEX hash_txt_index ON hash_txt_heap USING hash (random text_ops); + +CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops) + WITH (fillfactor=60); + +-- +-- Also try building functional, expressional, and partial indexes on +-- tables that already contain data. +-- +create unique index hash_f8_index_1 on hash_f8_heap(abs(random)); +create unique index hash_f8_index_2 on hash_f8_heap((seqno + 1), random); +create unique index hash_f8_index_3 on hash_f8_heap(random) where seqno > 1000; + +-- +-- hash index +-- grep 843938989 hash.data +-- SELECT * FROM hash_i4_heap WHERE hash_i4_heap.random = 843938989; diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql index 78091112ca..2724a2bbc7 100644 --- a/src/test/regress/sql/horology.sql +++ b/src/test/regress/sql/horology.sql @@ -307,6 +307,34 @@ SELECT '2020-10-05'::timestamptz >= '4714-11-24 BC'::timestamp as t; RESET TimeZone; -- +-- Tests for BETWEEN +-- + +explain (costs off) +select count(*) from date_tbl + where f1 between '1997-01-01' and '1998-01-01'; +select count(*) from date_tbl + where f1 between '1997-01-01' and '1998-01-01'; + +explain (costs off) +select count(*) from date_tbl + where f1 not between '1997-01-01' and '1998-01-01'; +select count(*) from date_tbl + where f1 not between '1997-01-01' and '1998-01-01'; + +explain (costs off) +select count(*) from date_tbl + where f1 between symmetric '1997-01-01' and '1998-01-01'; +select count(*) from date_tbl + where f1 between symmetric '1997-01-01' and '1998-01-01'; + +explain (costs off) +select count(*) from date_tbl + where f1 not between symmetric '1997-01-01' and '1998-01-01'; +select count(*) from date_tbl + where f1 not between symmetric '1997-01-01' and '1998-01-01'; + +-- -- Formats -- diff --git a/src/test/regress/sql/indirect_toast.sql b/src/test/regress/sql/indirect_toast.sql index 9156a44b7d..3e2f6c0237 100644 --- a/src/test/regress/sql/indirect_toast.sql +++ b/src/test/regress/sql/indirect_toast.sql @@ -2,6 +2,17 @@ -- Tests for external toast datums -- +-- directory paths and dlsuffix are passed to us in environment variables +\getenv libdir PG_LIBDIR +\getenv dlsuffix PG_DLSUFFIX + +\set regresslib :libdir '/regress' :dlsuffix + +CREATE FUNCTION make_tuple_indirect (record) + RETURNS record + AS :'regresslib' + LANGUAGE C STRICT; + -- Other compression algorithms may cause the compressed data to be stored -- inline. pglz guarantees that the data is externalized, so stick to it. SET default_toast_compression = 'pglz'; diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql index bfaa8a3b27..bdcffd0314 100644 --- a/src/test/regress/sql/insert.sql +++ b/src/test/regress/sql/insert.sql @@ -250,33 +250,6 @@ select tableoid::regclass::text, a, min(b) as min_b, max(b) as max_b from list_p -- direct partition inserts should check hash partition bound constraint --- Use hand-rolled hash functions and operator classes to get predictable --- result on different machines. The hash function for int4 simply returns --- the sum of the values passed to it and the one for text returns the length --- of the non-empty string value passed to it or 0. - -create or replace function part_hashint4_noop(value int4, seed int8) -returns int8 as $$ -select value + seed; -$$ language sql immutable; - -create operator class part_test_int4_ops -for type int4 -using hash as -operator 1 =, -function 2 part_hashint4_noop(int4, int8); - -create or replace function part_hashtext_length(value text, seed int8) -RETURNS int8 AS $$ -select length(coalesce(value, ''))::int8 -$$ language sql immutable; - -create operator class part_test_text_ops -for type text -using hash as -operator 1 =, -function 2 part_hashtext_length(text, int8); - create table hash_parted ( a int ) partition by hash (a part_test_int4_ops); diff --git a/src/test/regress/sql/int2.sql b/src/test/regress/sql/int2.sql index 613b344704..8e8d33892d 100644 --- a/src/test/regress/sql/int2.sql +++ b/src/test/regress/sql/int2.sql @@ -2,22 +2,10 @@ -- INT2 -- -CREATE TABLE INT2_TBL(f1 int2); - -INSERT INTO INT2_TBL(f1) VALUES ('0 '); - -INSERT INTO INT2_TBL(f1) VALUES (' 1234 '); - -INSERT INTO INT2_TBL(f1) VALUES (' -1234'); +-- int2_tbl was already created and filled in test_setup.sql. +-- Here we just try to insert bad values. INSERT INTO INT2_TBL(f1) VALUES ('34.5'); - --- largest and smallest values -INSERT INTO INT2_TBL(f1) VALUES ('32767'); - -INSERT INTO INT2_TBL(f1) VALUES ('-32767'); - --- bad input values -- should give errors INSERT INTO INT2_TBL(f1) VALUES ('100000'); INSERT INTO INT2_TBL(f1) VALUES ('asdf'); INSERT INTO INT2_TBL(f1) VALUES (' '); diff --git a/src/test/regress/sql/int4.sql b/src/test/regress/sql/int4.sql index 55ec07a147..f19077f3da 100644 --- a/src/test/regress/sql/int4.sql +++ b/src/test/regress/sql/int4.sql @@ -2,22 +2,10 @@ -- INT4 -- -CREATE TABLE INT4_TBL(f1 int4); - -INSERT INTO INT4_TBL(f1) VALUES (' 0 '); - -INSERT INTO INT4_TBL(f1) VALUES ('123456 '); - -INSERT INTO INT4_TBL(f1) VALUES (' -123456'); +-- int4_tbl was already created and filled in test_setup.sql. +-- Here we just try to insert bad values. INSERT INTO INT4_TBL(f1) VALUES ('34.5'); - --- largest and smallest values -INSERT INTO INT4_TBL(f1) VALUES ('2147483647'); - -INSERT INTO INT4_TBL(f1) VALUES ('-2147483647'); - --- bad input values -- should give errors INSERT INTO INT4_TBL(f1) VALUES ('1000000000000'); INSERT INTO INT4_TBL(f1) VALUES ('asdf'); INSERT INTO INT4_TBL(f1) VALUES (' '); diff --git a/src/test/regress/sql/int8.sql b/src/test/regress/sql/int8.sql index 32940b4daa..38b771964d 100644 --- a/src/test/regress/sql/int8.sql +++ b/src/test/regress/sql/int8.sql @@ -2,15 +2,10 @@ -- INT8 -- Test int8 64-bit integers. -- -CREATE TABLE INT8_TBL(q1 int8, q2 int8); -INSERT INTO INT8_TBL VALUES(' 123 ',' 456'); -INSERT INTO INT8_TBL VALUES('123 ','4567890123456789'); -INSERT INTO INT8_TBL VALUES('4567890123456789','123'); -INSERT INTO INT8_TBL VALUES(+4567890123456789,'4567890123456789'); -INSERT INTO INT8_TBL VALUES('+4567890123456789','-4567890123456789'); +-- int8_tbl was already created and filled in test_setup.sql. +-- Here we just try to insert bad values. --- bad inputs INSERT INTO INT8_TBL(q1) VALUES (' '); INSERT INTO INT8_TBL(q1) VALUES ('xxx'); INSERT INTO INT8_TBL(q1) VALUES ('3908203590239580293850293850329485'); diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index 52240fea7e..6dd01b022e 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -391,11 +391,11 @@ rollback; -- explain (costs off) select aa, bb, unique1, unique1 - from tenk1 right join b on aa = unique1 + from tenk1 right join b_star on aa = unique1 where bb < bb and bb is null; select aa, bb, unique1, unique1 - from tenk1 right join b on aa = unique1 + from tenk1 right join b_star on aa = unique1 where bb < bb and bb is null; -- diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql index 5016f29c15..8d25966267 100644 --- a/src/test/regress/sql/jsonb.sql +++ b/src/test/regress/sql/jsonb.sql @@ -1,3 +1,13 @@ +-- directory paths are passed to us in environment variables +\getenv abs_srcdir PG_ABS_SRCDIR + +CREATE TABLE testjsonb ( + j jsonb +); + +\set filename :abs_srcdir '/data/jsonb.data' +COPY testjsonb FROM :'filename'; + -- Strings. SELECT '""'::jsonb; -- OK. SELECT $$''$$::jsonb; -- ERROR, single quotes are not allowed diff --git a/src/test/regress/sql/lock.sql b/src/test/regress/sql/lock.sql index 05bdb8ad4c..b867e0f994 100644 --- a/src/test/regress/sql/lock.sql +++ b/src/test/regress/sql/lock.sql @@ -2,6 +2,12 @@ -- Test the LOCK statement -- +-- directory paths and dlsuffix are passed to us in environment variables +\getenv libdir PG_LIBDIR +\getenv dlsuffix PG_DLSUFFIX + +\set regresslib :libdir '/regress' :dlsuffix + -- Setup CREATE SCHEMA lock_schema1; SET search_path = lock_schema1; @@ -136,4 +142,10 @@ DROP ROLE regress_rol_lock1; -- atomic ops tests RESET search_path; + +CREATE FUNCTION test_atomic_ops() + RETURNS bool + AS :'regresslib' + LANGUAGE C; + SELECT test_atomic_ops(); diff --git a/src/test/regress/sql/misc.sql b/src/test/regress/sql/misc.sql index a1e2f779ba..165a2e175f 100644 --- a/src/test/regress/sql/misc.sql +++ b/src/test/regress/sql/misc.sql @@ -2,9 +2,23 @@ -- MISC -- --- directory paths are passed to us in environment variables +-- directory paths and dlsuffix are passed to us in environment variables \getenv abs_srcdir PG_ABS_SRCDIR \getenv abs_builddir PG_ABS_BUILDDIR +\getenv libdir PG_LIBDIR +\getenv dlsuffix PG_DLSUFFIX + +\set regresslib :libdir '/regress' :dlsuffix + +CREATE FUNCTION overpaid(emp) + RETURNS bool + AS :'regresslib' + LANGUAGE C STRICT; + +CREATE FUNCTION reverse_name(name) + RETURNS name + AS :'regresslib' + LANGUAGE C STRICT; -- -- BTREE @@ -31,6 +45,10 @@ UPDATE onek -- systems. This non-func update stuff needs to be examined -- more closely. - jolly (2/22/96) -- +SELECT two, stringu1, ten, string4 + INTO TABLE tmp + FROM onek; + UPDATE tmp SET stringu1 = reverse_name(onek.stringu1) FROM onek @@ -58,127 +76,116 @@ DROP TABLE tmp; \set filename :abs_builddir '/results/onek.data' COPY onek TO :'filename'; -DELETE FROM onek; - -COPY onek FROM :'filename'; +CREATE TEMP TABLE onek_copy (LIKE onek); -SELECT unique1 FROM onek WHERE unique1 < 2 ORDER BY unique1; +COPY onek_copy FROM :'filename'; -DELETE FROM onek2; +SELECT * FROM onek EXCEPT ALL SELECT * FROM onek_copy; -COPY onek2 FROM :'filename'; - -SELECT unique1 FROM onek2 WHERE unique1 < 2 ORDER BY unique1; +SELECT * FROM onek_copy EXCEPT ALL SELECT * FROM onek; \set filename :abs_builddir '/results/stud_emp.data' COPY BINARY stud_emp TO :'filename'; -DELETE FROM stud_emp; - -COPY BINARY stud_emp FROM :'filename'; +CREATE TEMP TABLE stud_emp_copy (LIKE stud_emp); -SELECT * FROM stud_emp; - --- COPY aggtest FROM stdin; --- 56 7.8 --- 100 99.097 --- 0 0.09561 --- 42 324.78 --- . --- COPY aggtest TO stdout; +COPY BINARY stud_emp_copy FROM :'filename'; +SELECT * FROM stud_emp_copy; -- --- inheritance stress test +-- test data for postquel functions -- -SELECT * FROM a_star*; - -SELECT * - FROM b_star* x - WHERE x.b = text 'bumble' or x.a < 3; - -SELECT class, a - FROM c_star* x - WHERE x.c ~ text 'hi'; - -SELECT class, b, c - FROM d_star* x - WHERE x.a < 100; - -SELECT class, c FROM e_star* x WHERE x.c NOTNULL; - -SELECT * FROM f_star* x WHERE x.c ISNULL; - --- grouping and aggregation on inherited sets have been busted in the past... - -SELECT sum(a) FROM a_star*; - -SELECT class, sum(a) FROM a_star* GROUP BY class ORDER BY class; - - -ALTER TABLE f_star RENAME COLUMN f TO ff; -ALTER TABLE e_star* RENAME COLUMN e TO ee; +CREATE TABLE hobbies_r ( + name text, + person text +); -ALTER TABLE d_star* RENAME COLUMN d TO dd; +CREATE TABLE equipment_r ( + name text, + hobby text +); -ALTER TABLE c_star* RENAME COLUMN c TO cc; +INSERT INTO hobbies_r (name, person) + SELECT 'posthacking', p.name + FROM person* p + WHERE p.name = 'mike' or p.name = 'jeff'; -ALTER TABLE b_star* RENAME COLUMN b TO bb; +INSERT INTO hobbies_r (name, person) + SELECT 'basketball', p.name + FROM person p + WHERE p.name = 'joe' or p.name = 'sally'; -ALTER TABLE a_star* RENAME COLUMN a TO aa; +INSERT INTO hobbies_r (name) VALUES ('skywalking'); -SELECT class, aa - FROM a_star* x - WHERE aa ISNULL; +INSERT INTO equipment_r (name, hobby) VALUES ('advil', 'posthacking'); --- As of Postgres 7.1, ALTER implicitly recurses, --- so this should be same as ALTER a_star* +INSERT INTO equipment_r (name, hobby) VALUES ('peet''s coffee', 'posthacking'); -ALTER TABLE a_star RENAME COLUMN aa TO foo; +INSERT INTO equipment_r (name, hobby) VALUES ('hightops', 'basketball'); -SELECT class, foo - FROM a_star* x - WHERE x.foo >= 2; - -ALTER TABLE a_star RENAME COLUMN foo TO aa; - -SELECT * - from a_star* - WHERE aa < 1000; - -ALTER TABLE f_star ADD COLUMN f int4; - -UPDATE f_star SET f = 10; - -ALTER TABLE e_star* ADD COLUMN e int4; - ---UPDATE e_star* SET e = 42; - -SELECT * FROM e_star*; - -ALTER TABLE a_star* ADD COLUMN a text; - --- That ALTER TABLE should have added TOAST tables. -SELECT relname, reltoastrelid <> 0 AS has_toast_table - FROM pg_class - WHERE oid::regclass IN ('a_star', 'c_star') - ORDER BY 1; - ---UPDATE b_star* --- SET a = text 'gazpacho' --- WHERE aa > 4; - -SELECT class, aa, a FROM a_star*; - - --- --- versions --- +INSERT INTO equipment_r (name, hobby) VALUES ('guts', 'skywalking'); -- -- postquel functions -- + +CREATE FUNCTION hobbies(person) + RETURNS setof hobbies_r + AS 'select * from hobbies_r where person = $1.name' + LANGUAGE SQL; + +CREATE FUNCTION hobby_construct(text, text) + RETURNS hobbies_r + AS 'select $1 as name, $2 as hobby' + LANGUAGE SQL; + +CREATE FUNCTION hobby_construct_named(name text, hobby text) + RETURNS hobbies_r + AS 'select name, hobby' + LANGUAGE SQL; + +CREATE FUNCTION hobbies_by_name(hobbies_r.name%TYPE) + RETURNS hobbies_r.person%TYPE + AS 'select person from hobbies_r where name = $1' + LANGUAGE SQL; + +CREATE FUNCTION equipment(hobbies_r) + RETURNS setof equipment_r + AS 'select * from equipment_r where hobby = $1.name' + LANGUAGE SQL; + +CREATE FUNCTION equipment_named(hobby hobbies_r) + RETURNS setof equipment_r + AS 'select * from equipment_r where equipment_r.hobby = equipment_named.hobby.name' + LANGUAGE SQL; + +CREATE FUNCTION equipment_named_ambiguous_1a(hobby hobbies_r) + RETURNS setof equipment_r + AS 'select * from equipment_r where hobby = equipment_named_ambiguous_1a.hobby.name' + LANGUAGE SQL; + +CREATE FUNCTION equipment_named_ambiguous_1b(hobby hobbies_r) + RETURNS setof equipment_r + AS 'select * from equipment_r where equipment_r.hobby = hobby.name' + LANGUAGE SQL; + +CREATE FUNCTION equipment_named_ambiguous_1c(hobby hobbies_r) + RETURNS setof equipment_r + AS 'select * from equipment_r where hobby = hobby.name' + LANGUAGE SQL; + +CREATE FUNCTION equipment_named_ambiguous_2a(hobby text) + RETURNS setof equipment_r + AS 'select * from equipment_r where hobby = equipment_named_ambiguous_2a.hobby' + LANGUAGE SQL; + +CREATE FUNCTION equipment_named_ambiguous_2b(hobby text) + RETURNS setof equipment_r + AS 'select * from equipment_r where equipment_r.hobby = hobby' + LANGUAGE SQL; + -- -- mike does post_hacking, -- joe and sally play basketball, and diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql index cfaba456e1..3db3f8bade 100644 --- a/src/test/regress/sql/misc_functions.sql +++ b/src/test/regress/sql/misc_functions.sql @@ -1,3 +1,9 @@ +-- directory paths and dlsuffix are passed to us in environment variables +\getenv libdir PG_LIBDIR +\getenv dlsuffix PG_DLSUFFIX + +\set regresslib :libdir '/regress' :dlsuffix + -- -- num_nulls() -- @@ -34,6 +40,11 @@ SELECT num_nulls(); -- canonicalize_path() -- +CREATE FUNCTION test_canonicalize_path(text) + RETURNS text + AS :'regresslib' + LANGUAGE C STRICT IMMUTABLE; + SELECT test_canonicalize_path('/'); SELECT test_canonicalize_path('/./abc/def/'); SELECT test_canonicalize_path('/./../abc/def'); @@ -156,6 +167,11 @@ SELECT * FROM tenk1 a JOIN tenk1 b ON a.unique1 = b.unique1 WHERE my_int_eq(a.unique2, 42); -- With support function that knows it's int4eq, we get a different plan +CREATE FUNCTION test_support_func(internal) + RETURNS internal + AS :'regresslib', 'test_support_func' + LANGUAGE C STRICT; + ALTER FUNCTION my_int_eq(int, int) SUPPORT test_support_func; EXPLAIN (COSTS OFF) diff --git a/src/test/regress/sql/point.sql b/src/test/regress/sql/point.sql index f85379192c..435ff4b9b6 100644 --- a/src/test/regress/sql/point.sql +++ b/src/test/regress/sql/point.sql @@ -5,31 +5,11 @@ -- avoid bit-exact output here because operations may not be bit-exact. SET extra_float_digits = 0; -CREATE TABLE POINT_TBL(f1 point); +-- point_tbl was already created and filled in test_setup.sql. +-- Here we just try to insert bad values. -INSERT INTO POINT_TBL(f1) VALUES ('(0.0,0.0)'); - -INSERT INTO POINT_TBL(f1) VALUES ('(-10.0,0.0)'); - -INSERT INTO POINT_TBL(f1) VALUES ('(-3.0,4.0)'); - -INSERT INTO POINT_TBL(f1) VALUES ('(5.1, 34.5)'); - -INSERT INTO POINT_TBL(f1) VALUES ('(-5.0,-12.0)'); - -INSERT INTO POINT_TBL(f1) VALUES ('(1e-300,-1e-300)'); -- To underflow - -INSERT INTO POINT_TBL(f1) VALUES ('(1e+300,Inf)'); -- To overflow - -INSERT INTO POINT_TBL(f1) VALUES ('(Inf,1e+300)'); -- Transposed - -INSERT INTO POINT_TBL(f1) VALUES (' ( Nan , NaN ) '); - --- bad format points INSERT INTO POINT_TBL(f1) VALUES ('asdfasdf'); -INSERT INTO POINT_TBL(f1) VALUES ('10.0,10.0'); - INSERT INTO POINT_TBL(f1) VALUES ('(10.0 10.0)'); INSERT INTO POINT_TBL(f1) VALUES ('(10.0, 10.0) x'); diff --git a/src/test/regress/sql/rangetypes.sql b/src/test/regress/sql/rangetypes.sql index a2d411d0da..1a10f67f19 100644 --- a/src/test/regress/sql/rangetypes.sql +++ b/src/test/regress/sql/rangetypes.sql @@ -1,9 +1,8 @@ -- Tests for range data types. -create type textrange as range (subtype=text, collation="C"); - -- -- test input parser +-- (type textrange was already made in test_setup.sql) -- -- negative tests; should fail @@ -424,13 +423,12 @@ set timezone to default; -- -- Test user-defined range of floats +-- (type float8range was already made in test_setup.sql) -- --should fail -create type float8range as range (subtype=float8, subtype_diff=float4mi); +create type bogus_float8range as range (subtype=float8, subtype_diff=float4mi); ---should succeed -create type float8range as range (subtype=float8, subtype_diff=float8mi); select '[123.001, 5.e9)'::float8range @> 888.882::float8; create table float8range_test(f8r float8range, i int); insert into float8range_test values(float8range(-100.00007, '1.111113e9'), 42); diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql index b732833e63..8bdab6dec3 100644 --- a/src/test/regress/sql/rules.sql +++ b/src/test/regress/sql/rules.sql @@ -776,11 +776,11 @@ drop table cchild; \a\t SELECT viewname, definition FROM pg_views -WHERE schemaname IN ('pg_catalog', 'public') +WHERE schemaname = 'pg_catalog' ORDER BY viewname; SELECT tablename, rulename, definition FROM pg_rules -WHERE schemaname IN ('pg_catalog', 'public') +WHERE schemaname = 'pg_catalog' ORDER BY tablename, rulename; -- restore normal output mode diff --git a/src/test/regress/sql/sanity_check.sql b/src/test/regress/sql/sanity_check.sql index a4ec00309f..162e5324b5 100644 --- a/src/test/regress/sql/sanity_check.sql +++ b/src/test/regress/sql/sanity_check.sql @@ -1,25 +1,7 @@ VACUUM; -- --- sanity check, if we don't have indices the test will take years to --- complete. But skip TOAST relations (since they will have varying --- names depending on the current OID counter) as well as temp tables --- of other backends (to avoid timing-dependent behavior). --- - --- temporarily disable fancy output, so catalog changes create less diff noise -\a\t - -SELECT relname, relhasindex - FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = relnamespace - WHERE relkind IN ('r', 'p') AND (nspname ~ '^pg_temp_') IS NOT TRUE - ORDER BY relname; - --- restore normal output mode -\a\t - --- --- another sanity check: every system catalog that has OIDs should have +-- Sanity check: every system catalog that has OIDs should have -- a unique index on OID. This ensures that the OIDs will be unique, -- even after the OID counter wraps around. -- We exclude non-system tables from the check by looking at nspname. diff --git a/src/test/regress/sql/select.sql b/src/test/regress/sql/select.sql index b5929b2eca..aa3d035204 100644 --- a/src/test/regress/sql/select.sql +++ b/src/test/regress/sql/select.sql @@ -90,11 +90,6 @@ RESET enable_seqscan; RESET enable_bitmapscan; RESET enable_sort; - -SELECT two, stringu1, ten, string4 - INTO TABLE tmp - FROM onek; - -- -- awk '{print $1,$2;}' person.data | -- awk '{if(NF!=2){print $3,$2;}else{print;}}' - emp.data | diff --git a/src/test/regress/sql/select_distinct.sql b/src/test/regress/sql/select_distinct.sql index 1bfe59c26f..f27ff714f8 100644 --- a/src/test/regress/sql/select_distinct.sql +++ b/src/test/regress/sql/select_distinct.sql @@ -5,24 +5,24 @@ -- -- awk '{print $3;}' onek.data | sort -n | uniq -- -SELECT DISTINCT two FROM tmp ORDER BY 1; +SELECT DISTINCT two FROM onek ORDER BY 1; -- -- awk '{print $5;}' onek.data | sort -n | uniq -- -SELECT DISTINCT ten FROM tmp ORDER BY 1; +SELECT DISTINCT ten FROM onek ORDER BY 1; -- -- awk '{print $16;}' onek.data | sort -d | uniq -- -SELECT DISTINCT string4 FROM tmp ORDER BY 1; +SELECT DISTINCT string4 FROM onek ORDER BY 1; -- -- awk '{print $3,$16,$5;}' onek.data | sort -d | uniq | -- sort +0n -1 +1d -2 +2n -3 -- SELECT DISTINCT two, string4, ten - FROM tmp + FROM onek ORDER BY two using <, string4 using <, ten using <; -- diff --git a/src/test/regress/sql/select_distinct_on.sql b/src/test/regress/sql/select_distinct_on.sql index d18733d274..0920bd64b9 100644 --- a/src/test/regress/sql/select_distinct_on.sql +++ b/src/test/regress/sql/select_distinct_on.sql @@ -3,16 +3,16 @@ -- SELECT DISTINCT ON (string4) string4, two, ten - FROM tmp + FROM onek ORDER BY string4 using <, two using >, ten using <; -- this will fail due to conflict of ordering requirements SELECT DISTINCT ON (string4, ten) string4, two, ten - FROM tmp + FROM onek ORDER BY string4 using <, two using <, ten using <; SELECT DISTINCT ON (string4, ten) string4, ten, two - FROM tmp + FROM onek ORDER BY string4 using <, ten using >, two using <; -- bug #5049: early 8.4.x chokes on volatile DISTINCT ON clauses diff --git a/src/test/regress/sql/select_into.sql b/src/test/regress/sql/select_into.sql index 7e903c339a..689c448cc2 100644 --- a/src/test/regress/sql/select_into.sql +++ b/src/test/regress/sql/select_into.sql @@ -110,11 +110,11 @@ DROP TABLE easi, easi2; -- -- Disallowed uses of SELECT ... INTO. All should fail -- -DECLARE foo CURSOR FOR SELECT 1 INTO b; +DECLARE foo CURSOR FOR SELECT 1 INTO int4_tbl; COPY (SELECT 1 INTO frak UNION SELECT 2) TO 'blob'; SELECT * FROM (SELECT 1 INTO f) bar; -CREATE VIEW foo AS SELECT 1 INTO b; -INSERT INTO b SELECT 1 INTO f; +CREATE VIEW foo AS SELECT 1 INTO int4_tbl; +INSERT INTO int4_tbl SELECT 1 INTO f; -- Test CREATE TABLE AS ... IF NOT EXISTS CREATE TABLE ctas_ine_tbl AS SELECT 1; diff --git a/src/test/regress/sql/test_setup.sql b/src/test/regress/sql/test_setup.sql index bea7a7e265..d0a73c473d 100644 --- a/src/test/regress/sql/test_setup.sql +++ b/src/test/regress/sql/test_setup.sql @@ -1,3 +1,269 @@ +-- +-- TEST_SETUP --- prepare environment expected by regression test scripts +-- + +-- directory paths and dlsuffix are passed to us in environment variables +\getenv abs_srcdir PG_ABS_SRCDIR +\getenv libdir PG_LIBDIR +\getenv dlsuffix PG_DLSUFFIX + +\set regresslib :libdir '/regress' :dlsuffix + +-- -- Postgres formerly made the public schema read/write by default, -- and most of the core regression tests still expect that. +-- GRANT ALL ON SCHEMA public TO public; + +-- +-- These tables have traditionally been referenced by many tests, +-- so create and populate them. Insert only non-error values here. +-- (Some subsequent tests try to insert erroneous values. That's okay +-- because the table won't actually change. Do not change the contents +-- of these tables in later tests, as it may affect other tests.) +-- + +CREATE TABLE CHAR_TBL(f1 char(4)); + +INSERT INTO CHAR_TBL (f1) VALUES + ('a'), + ('ab'), + ('abcd'), + ('abcd '); +VACUUM CHAR_TBL; + +CREATE TABLE FLOAT8_TBL(f1 float8); + +INSERT INTO FLOAT8_TBL(f1) VALUES + ('0.0'), + ('-34.84'), + ('-1004.30'), + ('-1.2345678901234e+200'), + ('-1.2345678901234e-200'); +VACUUM FLOAT8_TBL; + +CREATE TABLE INT2_TBL(f1 int2); + +INSERT INTO INT2_TBL(f1) VALUES + ('0 '), + (' 1234 '), + (' -1234'), + ('32767'), -- largest and smallest values + ('-32767'); +VACUUM INT2_TBL; + +CREATE TABLE INT4_TBL(f1 int4); + +INSERT INTO INT4_TBL(f1) VALUES + (' 0 '), + ('123456 '), + (' -123456'), + ('2147483647'), -- largest and smallest values + ('-2147483647'); +VACUUM INT4_TBL; + +CREATE TABLE INT8_TBL(q1 int8, q2 int8); + +INSERT INTO INT8_TBL VALUES + (' 123 ',' 456'), + ('123 ','4567890123456789'), + ('4567890123456789','123'), + (+4567890123456789,'4567890123456789'), + ('+4567890123456789','-4567890123456789'); +VACUUM INT8_TBL; + +CREATE TABLE POINT_TBL(f1 point); + +INSERT INTO POINT_TBL(f1) VALUES + ('(0.0,0.0)'), + ('(-10.0,0.0)'), + ('(-3.0,4.0)'), + ('(5.1, 34.5)'), + ('(-5.0,-12.0)'), + ('(1e-300,-1e-300)'), -- To underflow + ('(1e+300,Inf)'), -- To overflow + ('(Inf,1e+300)'), -- Transposed + (' ( Nan , NaN ) '), + ('10.0,10.0'); +-- We intentionally don't vacuum point_tbl here; geometry depends on that + +CREATE TABLE TEXT_TBL (f1 text); + +INSERT INTO TEXT_TBL VALUES + ('doh!'), + ('hi de ho neighbor'); +VACUUM TEXT_TBL; + +CREATE TABLE VARCHAR_TBL(f1 varchar(4)); + +INSERT INTO VARCHAR_TBL (f1) VALUES + ('a'), + ('ab'), + ('abcd'), + ('abcd '); +VACUUM VARCHAR_TBL; + +CREATE TABLE onek ( + unique1 int4, + unique2 int4, + two int4, + four int4, + ten int4, + twenty int4, + hundred int4, + thousand int4, + twothousand int4, + fivethous int4, + tenthous int4, + odd int4, + even int4, + stringu1 name, + stringu2 name, + string4 name +); + +\set filename :abs_srcdir '/data/onek.data' +COPY onek FROM :'filename'; +VACUUM ANALYZE onek; + +CREATE TABLE onek2 AS SELECT * FROM onek; +VACUUM ANALYZE onek2; + +CREATE TABLE tenk1 ( + unique1 int4, + unique2 int4, + two int4, + four int4, + ten int4, + twenty int4, + hundred int4, + thousand int4, + twothousand int4, + fivethous int4, + tenthous int4, + odd int4, + even int4, + stringu1 name, + stringu2 name, + string4 name +); + +\set filename :abs_srcdir '/data/tenk.data' +COPY tenk1 FROM :'filename'; +VACUUM ANALYZE tenk1; + +CREATE TABLE tenk2 AS SELECT * FROM tenk1; +VACUUM ANALYZE tenk2; + +CREATE TABLE person ( + name text, + age int4, + location point +); + +\set filename :abs_srcdir '/data/person.data' +COPY person FROM :'filename'; +VACUUM ANALYZE person; + +CREATE TABLE emp ( + salary int4, + manager name +) INHERITS (person); + +\set filename :abs_srcdir '/data/emp.data' +COPY emp FROM :'filename'; +VACUUM ANALYZE emp; + +CREATE TABLE student ( + gpa float8 +) INHERITS (person); + +\set filename :abs_srcdir '/data/student.data' +COPY student FROM :'filename'; +VACUUM ANALYZE student; + +CREATE TABLE stud_emp ( + percent int4 +) INHERITS (emp, student); + +\set filename :abs_srcdir '/data/stud_emp.data' +COPY stud_emp FROM :'filename'; +VACUUM ANALYZE stud_emp; + +CREATE TABLE road ( + name text, + thepath path +); + +\set filename :abs_srcdir '/data/streets.data' +COPY road FROM :'filename'; +VACUUM ANALYZE road; + +CREATE TABLE ihighway () INHERITS (road); + +INSERT INTO ihighway + SELECT * + FROM ONLY road + WHERE name ~ 'I- .*'; +VACUUM ANALYZE ihighway; + +CREATE TABLE shighway ( + surface text +) INHERITS (road); + +INSERT INTO shighway + SELECT *, 'asphalt' + FROM ONLY road + WHERE name ~ 'State Hwy.*'; +VACUUM ANALYZE shighway; + +-- +-- We must have some enum type in the database for opr_sanity and type_sanity. +-- + +create type stoplight as enum ('red', 'yellow', 'green'); + +-- +-- Also create some non-built-in range types. +-- + +create type float8range as range (subtype = float8, subtype_diff = float8mi); + +create type textrange as range (subtype = text, collation = "C"); + +-- +-- Create some C functions that will be used by various tests. +-- + +CREATE FUNCTION binary_coercible(oid, oid) + RETURNS bool + AS :'regresslib', 'binary_coercible' + LANGUAGE C STRICT STABLE PARALLEL SAFE; + +CREATE FUNCTION ttdummy () + RETURNS trigger + AS :'regresslib' + LANGUAGE C; + +-- Use hand-rolled hash functions and operator classes to get predictable +-- result on different machines. The hash function for int4 simply returns +-- the sum of the values passed to it and the one for text returns the length +-- of the non-empty string value passed to it or 0. + +create function part_hashint4_noop(value int4, seed int8) + returns int8 as $$ + select value + seed; + $$ language sql strict immutable parallel safe; + +create operator class part_test_int4_ops for type int4 using hash as + operator 1 =, + function 2 part_hashint4_noop(int4, int8); + +create function part_hashtext_length(value text, seed int8) + returns int8 as $$ + select length(coalesce(value, ''))::int8 + $$ language sql strict immutable parallel safe; + +create operator class part_test_text_ops for type text using hash as + operator 1 =, + function 2 part_hashtext_length(text, int8); diff --git a/src/test/regress/sql/text.sql b/src/test/regress/sql/text.sql index 56eee69abc..540e551254 100644 --- a/src/test/regress/sql/text.sql +++ b/src/test/regress/sql/text.sql @@ -6,11 +6,7 @@ SELECT text 'this is a text string' = text 'this is a text string' AS true; SELECT text 'this is a text string' = text 'this is a text strin' AS false; -CREATE TABLE TEXT_TBL (f1 text); - -INSERT INTO TEXT_TBL VALUES ('doh!'); -INSERT INTO TEXT_TBL VALUES ('hi de ho neighbor'); - +-- text_tbl was already created and filled in test_setup.sql. SELECT * FROM TEXT_TBL; -- As of 8.3 we have removed most implicit casts to text, so that for example diff --git a/src/test/regress/sql/transactions.sql b/src/test/regress/sql/transactions.sql index 8886280c0a..0a716b506b 100644 --- a/src/test/regress/sql/transactions.sql +++ b/src/test/regress/sql/transactions.sql @@ -4,10 +4,12 @@ BEGIN; -SELECT * - INTO TABLE xacttest - FROM aggtest; - +CREATE TABLE xacttest (a smallint, b real); +INSERT INTO xacttest VALUES + (56, 7.8), + (100, 99.097), + (0, 0.09561), + (42, 324.78); INSERT INTO xacttest (a, b) VALUES (777, 777.777); END; @@ -20,10 +22,10 @@ BEGIN; CREATE TABLE disappear (a int4); -DELETE FROM aggtest; +DELETE FROM xacttest; -- should be empty -SELECT * FROM aggtest; +SELECT * FROM xacttest; ABORT; @@ -31,7 +33,7 @@ ABORT; SELECT oid FROM pg_class WHERE relname = 'disappear'; -- should have members again -SELECT * FROM aggtest; +SELECT * FROM xacttest; -- Read-only tests diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql index 2657d127d5..4cc096265d 100644 --- a/src/test/regress/sql/triggers.sql +++ b/src/test/regress/sql/triggers.sql @@ -2,6 +2,39 @@ -- TRIGGERS -- +-- directory paths and dlsuffix are passed to us in environment variables +\getenv libdir PG_LIBDIR +\getenv dlsuffix PG_DLSUFFIX + +\set autoinclib :libdir '/autoinc' :dlsuffix +\set refintlib :libdir '/refint' :dlsuffix +\set regresslib :libdir '/regress' :dlsuffix + +CREATE FUNCTION autoinc () + RETURNS trigger + AS :'autoinclib' + LANGUAGE C; + +CREATE FUNCTION check_primary_key () + RETURNS trigger + AS :'refintlib' + LANGUAGE C; + +CREATE FUNCTION check_foreign_key () + RETURNS trigger + AS :'refintlib' + LANGUAGE C; + +CREATE FUNCTION trigger_return_old () + RETURNS trigger + AS :'regresslib' + LANGUAGE C; + +CREATE FUNCTION set_ttdummy (int4) + RETURNS int4 + AS :'regresslib' + LANGUAGE C STRICT; + create table pkeys (pkey1 int4 not null, pkey2 text not null); create table fkeys (fkey1 int4, fkey2 text, fkey3 int); create table fkeys2 (fkey21 int4, fkey22 text, pkey23 int not null); diff --git a/src/test/regress/sql/tsearch.sql b/src/test/regress/sql/tsearch.sql index d929210998..0fa8ac4682 100644 --- a/src/test/regress/sql/tsearch.sql +++ b/src/test/regress/sql/tsearch.sql @@ -1,3 +1,6 @@ +-- directory paths are passed to us in environment variables +\getenv abs_srcdir PG_ABS_SRCDIR + -- -- Sanity checks for text search catalogs -- @@ -39,6 +42,17 @@ RIGHT JOIN pg_ts_config_map AS m WHERE tt.cfgid IS NULL OR tt.tokid IS NULL; +-- Load some test data +CREATE TABLE test_tsvector( + t text, + a tsvector +); + +\set filename :abs_srcdir '/data/tsearch.data' +COPY test_tsvector FROM :'filename'; + +ANALYZE test_tsvector; + -- test basic text search behavior without indexes, then with SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; diff --git a/src/test/regress/sql/type_sanity.sql b/src/test/regress/sql/type_sanity.sql index 8281076423..cdde47290f 100644 --- a/src/test/regress/sql/type_sanity.sql +++ b/src/test/regress/sql/type_sanity.sql @@ -553,29 +553,24 @@ CREATE TABLE tab_core_types AS SELECT 'n'::information_schema.sql_identifier, 'now'::information_schema.time_stamp, 'YES'::information_schema.yes_or_no, - 'venus'::planets, - 'i16'::insenum, '(1,2)'::int4range, '{(1,2)}'::int4multirange, '(3,4)'::int8range, '{(3,4)}'::int8multirange, - '(1,2)'::float8range, '{(1,2)}'::float8multirange, '(3,4)'::numrange, '{(3,4)}'::nummultirange, - '(a,b)'::textrange, '{(a,b)}'::textmultirange, - '(12.34, 56.78)'::cashrange, '{(12.34, 56.78)}'::cashmultirange, '(2020-01-02, 2021-02-03)'::daterange, '{(2020-01-02, 2021-02-03)}'::datemultirange, '(2020-01-02 03:04:05, 2021-02-03 06:07:08)'::tsrange, '{(2020-01-02 03:04:05, 2021-02-03 06:07:08)}'::tsmultirange, '(2020-01-02 03:04:05, 2021-02-03 06:07:08)'::tstzrange, - '{(2020-01-02 03:04:05, 2021-02-03 06:07:08)}'::tstzmultirange, - arrayrange(ARRAY[1,2], ARRAY[2,1]), - arraymultirange(arrayrange(ARRAY[1,2], ARRAY[2,1])); + '{(2020-01-02 03:04:05, 2021-02-03 06:07:08)}'::tstzmultirange; -- Sanity check on the previous table, checking that all core types are -- included in this table. -SELECT oid, typname, typtype, typelem, typarray, typarray +SELECT oid, typname, typtype, typelem, typarray FROM pg_type t - WHERE typtype NOT IN ('p', 'c') AND - -- reg* types cannot be pg_upgraded, so discard them. + WHERE oid < 16384 AND + -- Exclude pseudotypes and composite types. + typtype NOT IN ('p', 'c') AND + -- These reg* types cannot be pg_upgraded, so discard them. oid != ALL(ARRAY['regproc', 'regprocedure', 'regoper', 'regoperator', 'regconfig', 'regdictionary', 'regnamespace', 'regcollation']::regtype[]) AND diff --git a/src/test/regress/sql/varchar.sql b/src/test/regress/sql/varchar.sql index 35e24b84d3..a970821426 100644 --- a/src/test/regress/sql/varchar.sql +++ b/src/test/regress/sql/varchar.sql @@ -2,7 +2,12 @@ -- VARCHAR -- -CREATE TABLE VARCHAR_TBL(f1 varchar(1)); +-- +-- Build a table for testing +-- (This temporarily hides the table created in test_setup.sql) +-- + +CREATE TEMP TABLE VARCHAR_TBL(f1 varchar(1)); INSERT INTO VARCHAR_TBL (f1) VALUES ('a'); @@ -54,13 +59,10 @@ DROP TABLE VARCHAR_TBL; -- -- Now test longer arrays of char -- +-- This varchar_tbl was already created and filled in test_setup.sql. +-- Here we just try to insert bad values. +-- -CREATE TABLE VARCHAR_TBL(f1 varchar(4)); - -INSERT INTO VARCHAR_TBL (f1) VALUES ('a'); -INSERT INTO VARCHAR_TBL (f1) VALUES ('ab'); -INSERT INTO VARCHAR_TBL (f1) VALUES ('abcd'); INSERT INTO VARCHAR_TBL (f1) VALUES ('abcde'); -INSERT INTO VARCHAR_TBL (f1) VALUES ('abcd '); SELECT * FROM VARCHAR_TBL; diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql index 7ff9de97a5..e482177557 100644 --- a/src/test/regress/sql/with.sql +++ b/src/test/regress/sql/with.sql @@ -1401,7 +1401,7 @@ SELECT * FROM parent; EXPLAIN (VERBOSE, COSTS OFF) WITH wcte AS ( INSERT INTO int8_tbl VALUES ( 42, 47 ) RETURNING q2 ) -DELETE FROM a USING wcte WHERE aa = q2; +DELETE FROM a_star USING wcte WHERE aa = q2; -- error cases |