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

1 files changed, 104 insertions, 97 deletions

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