diff options
| author | Tom Lane <tgl@sss.pgh.pa.us> | 2022-02-08 15:30:38 -0500 |
|---|---|---|
| committer | Tom Lane <tgl@sss.pgh.pa.us> | 2022-02-08 15:30:38 -0500 |
| commit | cc50080a828dd4791b43539f5a0f976e535d147c (patch) | |
| tree | 787184da35163d8be525b7f84af85083e50d152a /src/test/regress/sql | |
| parent | ba15f16107bea8a93edc505f3013cd7df4ac90fc (diff) | |
| download | postgresql-cc50080a828dd4791b43539f5a0f976e535d147c.tar.gz | |
Rearrange core regression tests to reduce cross-script dependencies.
The idea behind this patch is to make it possible to run individual
test scripts without running the entire core test suite. Making all
the scripts completely independent would involve a massive rewrite,
and would probably be worse for coverage of things like concurrent DDL.
So this patch just does what seems practical with limited changes.
The net effect is that any test script can be run after running
limited earlier dependencies:
* all scripts depend on test_setup
* many scripts depend on create_index
* other dependencies are few in number, and are documented in
the parallel_schedule file.
To accomplish this, I chose a small number of commonly-used tables
and moved their creation and filling into test_setup. Later scripts
are expected not to modify these tables' data contents, for fear of
affecting other scripts' results. Also, our former habit of declaring
all C functions in one place is now gone in favor of declaring them
where they're used, if that's just one script, or in test_setup if
necessary.
There's more that could be done to remove some of the remaining
inter-script dependencies, but significantly more-invasive changes
would be needed, and at least for now it doesn't seem worth it.
Discussion: https://postgr.es/m/1114748.1640383217@sss.pgh.pa.us
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 |