summaryrefslogtreecommitdiffstats
path: root/src/test/regress/output
diff options
context:
space:
mode:
Diffstat (limited to 'src/test/regress/output')
-rw-r--r--src/test/regress/output/constraints.source738
-rw-r--r--src/test/regress/output/copy.source167
-rw-r--r--src/test/regress/output/create_function_1.source103
-rw-r--r--src/test/regress/output/create_function_2.source69
-rw-r--r--src/test/regress/output/largeobject.source481
-rw-r--r--src/test/regress/output/largeobject_1.source481
-rw-r--r--src/test/regress/output/misc.source692
-rw-r--r--src/test/regress/output/tablespace.source753
8 files changed, 3484 insertions, 0 deletions
diff --git a/src/test/regress/output/constraints.source b/src/test/regress/output/constraints.source
new file mode 100644
index 0000000..b727c61
--- /dev/null
+++ b/src/test/regress/output/constraints.source
@@ -0,0 +1,738 @@
+--
+-- CONSTRAINTS
+-- Constraints can be specified with:
+-- - DEFAULT clause
+-- - CHECK clauses
+-- - PRIMARY KEY clauses
+-- - UNIQUE clauses
+-- - EXCLUDE clauses
+--
+--
+-- DEFAULT syntax
+--
+CREATE TABLE DEFAULT_TBL (i int DEFAULT 100,
+ x text DEFAULT 'vadim', f float8 DEFAULT 123.456);
+INSERT INTO DEFAULT_TBL VALUES (1, 'thomas', 57.0613);
+INSERT INTO DEFAULT_TBL VALUES (1, 'bruce');
+INSERT INTO DEFAULT_TBL (i, f) VALUES (2, 987.654);
+INSERT INTO DEFAULT_TBL (x) VALUES ('marc');
+INSERT INTO DEFAULT_TBL VALUES (3, null, 1.0);
+SELECT '' AS five, * FROM DEFAULT_TBL;
+ five | i | x | f
+------+-----+--------+---------
+ | 1 | thomas | 57.0613
+ | 1 | bruce | 123.456
+ | 2 | vadim | 987.654
+ | 100 | marc | 123.456
+ | 3 | | 1
+(5 rows)
+
+CREATE SEQUENCE DEFAULT_SEQ;
+CREATE TABLE DEFAULTEXPR_TBL (i1 int DEFAULT 100 + (200-199) * 2,
+ i2 int DEFAULT nextval('default_seq'));
+INSERT INTO DEFAULTEXPR_TBL VALUES (-1, -2);
+INSERT INTO DEFAULTEXPR_TBL (i1) VALUES (-3);
+INSERT INTO DEFAULTEXPR_TBL (i2) VALUES (-4);
+INSERT INTO DEFAULTEXPR_TBL (i2) VALUES (NULL);
+SELECT '' AS four, * FROM DEFAULTEXPR_TBL;
+ four | i1 | i2
+------+-----+----
+ | -1 | -2
+ | -3 | 1
+ | 102 | -4
+ | 102 |
+(4 rows)
+
+-- syntax errors
+-- test for extraneous comma
+CREATE TABLE error_tbl (i int DEFAULT (100, ));
+ERROR: syntax error at or near ")"
+LINE 1: CREATE TABLE error_tbl (i int DEFAULT (100, ));
+ ^
+-- this will fail because gram.y uses b_expr not a_expr for defaults,
+-- to avoid a shift/reduce conflict that arises from NOT NULL being
+-- part of the column definition syntax:
+CREATE TABLE error_tbl (b1 bool DEFAULT 1 IN (1, 2));
+ERROR: syntax error at or near "IN"
+LINE 1: CREATE TABLE error_tbl (b1 bool DEFAULT 1 IN (1, 2));
+ ^
+-- this should work, however:
+CREATE TABLE error_tbl (b1 bool DEFAULT (1 IN (1, 2)));
+DROP TABLE error_tbl;
+--
+-- CHECK syntax
+--
+CREATE TABLE CHECK_TBL (x int,
+ CONSTRAINT CHECK_CON CHECK (x > 3));
+INSERT INTO CHECK_TBL VALUES (5);
+INSERT INTO CHECK_TBL VALUES (4);
+INSERT INTO CHECK_TBL VALUES (3);
+ERROR: new row for relation "check_tbl" violates check constraint "check_con"
+DETAIL: Failing row contains (3).
+INSERT INTO CHECK_TBL VALUES (2);
+ERROR: new row for relation "check_tbl" violates check constraint "check_con"
+DETAIL: Failing row contains (2).
+INSERT INTO CHECK_TBL VALUES (6);
+INSERT INTO CHECK_TBL VALUES (1);
+ERROR: new row for relation "check_tbl" violates check constraint "check_con"
+DETAIL: Failing row contains (1).
+SELECT '' AS three, * FROM CHECK_TBL;
+ three | x
+-------+---
+ | 5
+ | 4
+ | 6
+(3 rows)
+
+CREATE SEQUENCE CHECK_SEQ;
+CREATE TABLE CHECK2_TBL (x int, y text, z int,
+ CONSTRAINT SEQUENCE_CON
+ CHECK (x > 3 and y <> 'check failed' and z < 8));
+INSERT INTO CHECK2_TBL VALUES (4, 'check ok', -2);
+INSERT INTO CHECK2_TBL VALUES (1, 'x check failed', -2);
+ERROR: new row for relation "check2_tbl" violates check constraint "sequence_con"
+DETAIL: Failing row contains (1, x check failed, -2).
+INSERT INTO CHECK2_TBL VALUES (5, 'z check failed', 10);
+ERROR: new row for relation "check2_tbl" violates check constraint "sequence_con"
+DETAIL: Failing row contains (5, z check failed, 10).
+INSERT INTO CHECK2_TBL VALUES (0, 'check failed', -2);
+ERROR: new row for relation "check2_tbl" violates check constraint "sequence_con"
+DETAIL: Failing row contains (0, check failed, -2).
+INSERT INTO CHECK2_TBL VALUES (6, 'check failed', 11);
+ERROR: new row for relation "check2_tbl" violates check constraint "sequence_con"
+DETAIL: Failing row contains (6, check failed, 11).
+INSERT INTO CHECK2_TBL VALUES (7, 'check ok', 7);
+SELECT '' AS two, * from CHECK2_TBL;
+ two | x | y | z
+-----+---+----------+----
+ | 4 | check ok | -2
+ | 7 | check ok | 7
+(2 rows)
+
+--
+-- Check constraints on INSERT
+--
+CREATE SEQUENCE INSERT_SEQ;
+CREATE TABLE INSERT_TBL (x INT DEFAULT nextval('insert_seq'),
+ y TEXT DEFAULT '-NULL-',
+ z INT DEFAULT -1 * currval('insert_seq'),
+ CONSTRAINT INSERT_TBL_CON CHECK (x >= 3 AND y <> 'check failed' AND x < 8),
+ CHECK (x + z = 0));
+INSERT INTO INSERT_TBL(x,z) VALUES (2, -2);
+ERROR: new row for relation "insert_tbl" violates check constraint "insert_tbl_con"
+DETAIL: Failing row contains (2, -NULL-, -2).
+SELECT '' AS zero, * FROM INSERT_TBL;
+ zero | x | y | z
+------+---+---+---
+(0 rows)
+
+SELECT 'one' AS one, nextval('insert_seq');
+ one | nextval
+-----+---------
+ one | 1
+(1 row)
+
+INSERT INTO INSERT_TBL(y) VALUES ('Y');
+ERROR: new row for relation "insert_tbl" violates check constraint "insert_tbl_con"
+DETAIL: Failing row contains (2, Y, -2).
+INSERT INTO INSERT_TBL(y) VALUES ('Y');
+INSERT INTO INSERT_TBL(x,z) VALUES (1, -2);
+ERROR: new row for relation "insert_tbl" violates check constraint "insert_tbl_check"
+DETAIL: Failing row contains (1, -NULL-, -2).
+INSERT INTO INSERT_TBL(z,x) VALUES (-7, 7);
+INSERT INTO INSERT_TBL VALUES (5, 'check failed', -5);
+ERROR: new row for relation "insert_tbl" violates check constraint "insert_tbl_con"
+DETAIL: Failing row contains (5, check failed, -5).
+INSERT INTO INSERT_TBL VALUES (7, '!check failed', -7);
+INSERT INTO INSERT_TBL(y) VALUES ('-!NULL-');
+SELECT '' AS four, * FROM INSERT_TBL;
+ four | x | y | z
+------+---+---------------+----
+ | 3 | Y | -3
+ | 7 | -NULL- | -7
+ | 7 | !check failed | -7
+ | 4 | -!NULL- | -4
+(4 rows)
+
+INSERT INTO INSERT_TBL(y,z) VALUES ('check failed', 4);
+ERROR: new row for relation "insert_tbl" violates check constraint "insert_tbl_check"
+DETAIL: Failing row contains (5, check failed, 4).
+INSERT INTO INSERT_TBL(x,y) VALUES (5, 'check failed');
+ERROR: new row for relation "insert_tbl" violates check constraint "insert_tbl_con"
+DETAIL: Failing row contains (5, check failed, -5).
+INSERT INTO INSERT_TBL(x,y) VALUES (5, '!check failed');
+INSERT INTO INSERT_TBL(y) VALUES ('-!NULL-');
+SELECT '' AS six, * FROM INSERT_TBL;
+ six | x | y | z
+-----+---+---------------+----
+ | 3 | Y | -3
+ | 7 | -NULL- | -7
+ | 7 | !check failed | -7
+ | 4 | -!NULL- | -4
+ | 5 | !check failed | -5
+ | 6 | -!NULL- | -6
+(6 rows)
+
+SELECT 'seven' AS one, nextval('insert_seq');
+ one | nextval
+-------+---------
+ seven | 7
+(1 row)
+
+INSERT INTO INSERT_TBL(y) VALUES ('Y');
+ERROR: new row for relation "insert_tbl" violates check constraint "insert_tbl_con"
+DETAIL: Failing row contains (8, Y, -8).
+SELECT 'eight' AS one, currval('insert_seq');
+ one | currval
+-------+---------
+ eight | 8
+(1 row)
+
+-- According to SQL, it is OK to insert a record that gives rise to NULL
+-- constraint-condition results. Postgres used to reject this, but it
+-- was wrong:
+INSERT INTO INSERT_TBL VALUES (null, null, null);
+SELECT '' AS nine, * FROM INSERT_TBL;
+ nine | x | y | z
+------+---+---------------+----
+ | 3 | Y | -3
+ | 7 | -NULL- | -7
+ | 7 | !check failed | -7
+ | 4 | -!NULL- | -4
+ | 5 | !check failed | -5
+ | 6 | -!NULL- | -6
+ | | |
+(7 rows)
+
+--
+-- Check constraints on system columns
+--
+CREATE TABLE SYS_COL_CHECK_TBL (city text, state text, is_capital bool,
+ altitude int,
+ CHECK (NOT (is_capital AND tableoid::regclass::text = 'sys_col_check_tbl')));
+INSERT INTO SYS_COL_CHECK_TBL VALUES ('Seattle', 'Washington', false, 100);
+INSERT INTO SYS_COL_CHECK_TBL VALUES ('Olympia', 'Washington', true, 100);
+ERROR: new row for relation "sys_col_check_tbl" violates check constraint "sys_col_check_tbl_check"
+DETAIL: Failing row contains (Olympia, Washington, t, 100).
+SELECT *, tableoid::regclass::text FROM SYS_COL_CHECK_TBL;
+ city | state | is_capital | altitude | tableoid
+---------+------------+------------+----------+-------------------
+ Seattle | Washington | f | 100 | sys_col_check_tbl
+(1 row)
+
+DROP TABLE SYS_COL_CHECK_TBL;
+--
+-- Check constraints on system columns other then TableOid should return error
+--
+CREATE TABLE SYS_COL_CHECK_TBL (city text, state text, is_capital bool,
+ altitude int,
+ CHECK (NOT (is_capital AND ctid::text = 'sys_col_check_tbl')));
+ERROR: system column "ctid" reference in check constraint is invalid
+LINE 3: CHECK (NOT (is_capital AND ctid::text = 'sys_col_check...
+ ^
+--
+-- Check inheritance of defaults and constraints
+--
+CREATE TABLE INSERT_CHILD (cx INT default 42,
+ cy INT CHECK (cy > x))
+ INHERITS (INSERT_TBL);
+INSERT INTO INSERT_CHILD(x,z,cy) VALUES (7,-7,11);
+INSERT INTO INSERT_CHILD(x,z,cy) VALUES (7,-7,6);
+ERROR: new row for relation "insert_child" violates check constraint "insert_child_check"
+DETAIL: Failing row contains (7, -NULL-, -7, 42, 6).
+INSERT INTO INSERT_CHILD(x,z,cy) VALUES (6,-7,7);
+ERROR: new row for relation "insert_child" violates check constraint "insert_tbl_check"
+DETAIL: Failing row contains (6, -NULL-, -7, 42, 7).
+INSERT INTO INSERT_CHILD(x,y,z,cy) VALUES (6,'check failed',-6,7);
+ERROR: new row for relation "insert_child" violates check constraint "insert_tbl_con"
+DETAIL: Failing row contains (6, check failed, -6, 42, 7).
+SELECT * FROM INSERT_CHILD;
+ x | y | z | cx | cy
+---+--------+----+----+----
+ 7 | -NULL- | -7 | 42 | 11
+(1 row)
+
+DROP TABLE INSERT_CHILD;
+--
+-- Check NO INHERIT type of constraints and inheritance
+--
+CREATE TABLE ATACC1 (TEST INT
+ CHECK (TEST > 0) NO INHERIT);
+CREATE TABLE ATACC2 (TEST2 INT) INHERITS (ATACC1);
+-- check constraint is not there on child
+INSERT INTO ATACC2 (TEST) VALUES (-3);
+-- check constraint is there on parent
+INSERT INTO ATACC1 (TEST) VALUES (-3);
+ERROR: new row for relation "atacc1" violates check constraint "atacc1_test_check"
+DETAIL: Failing row contains (-3).
+DROP TABLE ATACC1 CASCADE;
+NOTICE: drop cascades to table atacc2
+CREATE TABLE ATACC1 (TEST INT, TEST2 INT
+ CHECK (TEST > 0), CHECK (TEST2 > 10) NO INHERIT);
+CREATE TABLE ATACC2 () INHERITS (ATACC1);
+-- check constraint is there on child
+INSERT INTO ATACC2 (TEST) VALUES (-3);
+ERROR: new row for relation "atacc2" violates check constraint "atacc1_test_check"
+DETAIL: Failing row contains (-3, null).
+-- check constraint is there on parent
+INSERT INTO ATACC1 (TEST) VALUES (-3);
+ERROR: new row for relation "atacc1" violates check constraint "atacc1_test_check"
+DETAIL: Failing row contains (-3, null).
+-- check constraint is not there on child
+INSERT INTO ATACC2 (TEST2) VALUES (3);
+-- check constraint is there on parent
+INSERT INTO ATACC1 (TEST2) VALUES (3);
+ERROR: new row for relation "atacc1" violates check constraint "atacc1_test2_check"
+DETAIL: Failing row contains (null, 3).
+DROP TABLE ATACC1 CASCADE;
+NOTICE: drop cascades to table atacc2
+--
+-- Check constraints on INSERT INTO
+--
+DELETE FROM INSERT_TBL;
+ALTER SEQUENCE INSERT_SEQ RESTART WITH 4;
+CREATE TEMP TABLE tmp (xd INT, yd TEXT, zd INT);
+INSERT INTO tmp VALUES (null, 'Y', null);
+INSERT INTO tmp VALUES (5, '!check failed', null);
+INSERT INTO tmp VALUES (null, 'try again', null);
+INSERT INTO INSERT_TBL(y) select yd from tmp;
+SELECT '' AS three, * FROM INSERT_TBL;
+ three | x | y | z
+-------+---+---------------+----
+ | 4 | Y | -4
+ | 5 | !check failed | -5
+ | 6 | try again | -6
+(3 rows)
+
+INSERT INTO INSERT_TBL SELECT * FROM tmp WHERE yd = 'try again';
+INSERT INTO INSERT_TBL(y,z) SELECT yd, -7 FROM tmp WHERE yd = 'try again';
+INSERT INTO INSERT_TBL(y,z) SELECT yd, -8 FROM tmp WHERE yd = 'try again';
+ERROR: new row for relation "insert_tbl" violates check constraint "insert_tbl_con"
+DETAIL: Failing row contains (8, try again, -8).
+SELECT '' AS four, * FROM INSERT_TBL;
+ four | x | y | z
+------+---+---------------+----
+ | 4 | Y | -4
+ | 5 | !check failed | -5
+ | 6 | try again | -6
+ | | try again |
+ | 7 | try again | -7
+(5 rows)
+
+DROP TABLE tmp;
+--
+-- Check constraints on UPDATE
+--
+UPDATE INSERT_TBL SET x = NULL WHERE x = 5;
+UPDATE INSERT_TBL SET x = 6 WHERE x = 6;
+UPDATE INSERT_TBL SET x = -z, z = -x;
+UPDATE INSERT_TBL SET x = z, z = x;
+ERROR: new row for relation "insert_tbl" violates check constraint "insert_tbl_con"
+DETAIL: Failing row contains (-4, Y, 4).
+SELECT * FROM INSERT_TBL;
+ x | y | z
+---+---------------+----
+ 4 | Y | -4
+ | try again |
+ 7 | try again | -7
+ 5 | !check failed |
+ 6 | try again | -6
+(5 rows)
+
+-- DROP TABLE INSERT_TBL;
+--
+-- Check constraints on COPY FROM
+--
+CREATE TABLE COPY_TBL (x INT, y TEXT, z INT,
+ CONSTRAINT COPY_CON
+ CHECK (x > 3 AND y <> 'check failed' AND x < 7 ));
+COPY COPY_TBL FROM '@abs_srcdir@/data/constro.data';
+SELECT '' AS two, * FROM COPY_TBL;
+ two | x | y | z
+-----+---+---------------+---
+ | 4 | !check failed | 5
+ | 6 | OK | 4
+(2 rows)
+
+COPY COPY_TBL FROM '@abs_srcdir@/data/constrf.data';
+ERROR: new row for relation "copy_tbl" violates check constraint "copy_con"
+DETAIL: Failing row contains (7, check failed, 6).
+CONTEXT: COPY copy_tbl, line 2: "7 check failed 6"
+SELECT * FROM COPY_TBL;
+ x | y | z
+---+---------------+---
+ 4 | !check failed | 5
+ 6 | OK | 4
+(2 rows)
+
+--
+-- Primary keys
+--
+CREATE TABLE PRIMARY_TBL (i int PRIMARY KEY, t text);
+INSERT INTO PRIMARY_TBL VALUES (1, 'one');
+INSERT INTO PRIMARY_TBL VALUES (2, 'two');
+INSERT INTO PRIMARY_TBL VALUES (1, 'three');
+ERROR: duplicate key value violates unique constraint "primary_tbl_pkey"
+DETAIL: Key (i)=(1) already exists.
+INSERT INTO PRIMARY_TBL VALUES (4, 'three');
+INSERT INTO PRIMARY_TBL VALUES (5, 'one');
+INSERT INTO PRIMARY_TBL (t) VALUES ('six');
+ERROR: null value in column "i" of relation "primary_tbl" violates not-null constraint
+DETAIL: Failing row contains (null, six).
+SELECT '' AS four, * FROM PRIMARY_TBL;
+ four | i | t
+------+---+-------
+ | 1 | one
+ | 2 | two
+ | 4 | three
+ | 5 | one
+(4 rows)
+
+DROP TABLE PRIMARY_TBL;
+CREATE TABLE PRIMARY_TBL (i int, t text,
+ PRIMARY KEY(i,t));
+INSERT INTO PRIMARY_TBL VALUES (1, 'one');
+INSERT INTO PRIMARY_TBL VALUES (2, 'two');
+INSERT INTO PRIMARY_TBL VALUES (1, 'three');
+INSERT INTO PRIMARY_TBL VALUES (4, 'three');
+INSERT INTO PRIMARY_TBL VALUES (5, 'one');
+INSERT INTO PRIMARY_TBL (t) VALUES ('six');
+ERROR: null value in column "i" of relation "primary_tbl" violates not-null constraint
+DETAIL: Failing row contains (null, six).
+SELECT '' AS three, * FROM PRIMARY_TBL;
+ three | i | t
+-------+---+-------
+ | 1 | one
+ | 2 | two
+ | 1 | three
+ | 4 | three
+ | 5 | one
+(5 rows)
+
+DROP TABLE PRIMARY_TBL;
+--
+-- Unique keys
+--
+CREATE TABLE UNIQUE_TBL (i int UNIQUE, t text);
+INSERT INTO UNIQUE_TBL VALUES (1, 'one');
+INSERT INTO UNIQUE_TBL VALUES (2, 'two');
+INSERT INTO UNIQUE_TBL VALUES (1, 'three');
+ERROR: duplicate key value violates unique constraint "unique_tbl_i_key"
+DETAIL: Key (i)=(1) already exists.
+INSERT INTO UNIQUE_TBL VALUES (4, 'four');
+INSERT INTO UNIQUE_TBL VALUES (5, 'one');
+INSERT INTO UNIQUE_TBL (t) VALUES ('six');
+INSERT INTO UNIQUE_TBL (t) VALUES ('seven');
+INSERT INTO UNIQUE_TBL VALUES (5, 'five-upsert-insert') ON CONFLICT (i) DO UPDATE SET t = 'five-upsert-update';
+INSERT INTO UNIQUE_TBL VALUES (6, 'six-upsert-insert') ON CONFLICT (i) DO UPDATE SET t = 'six-upsert-update';
+-- should fail
+INSERT INTO UNIQUE_TBL VALUES (1, 'a'), (2, 'b'), (2, 'b') ON CONFLICT (i) DO UPDATE SET t = 'fails';
+ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time
+HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.
+SELECT '' AS five, * FROM UNIQUE_TBL;
+ five | i | t
+------+---+--------------------
+ | 1 | one
+ | 2 | two
+ | 4 | four
+ | | six
+ | | seven
+ | 5 | five-upsert-update
+ | 6 | six-upsert-insert
+(7 rows)
+
+DROP TABLE UNIQUE_TBL;
+CREATE TABLE UNIQUE_TBL (i int, t text,
+ UNIQUE(i,t));
+INSERT INTO UNIQUE_TBL VALUES (1, 'one');
+INSERT INTO UNIQUE_TBL VALUES (2, 'two');
+INSERT INTO UNIQUE_TBL VALUES (1, 'three');
+INSERT INTO UNIQUE_TBL VALUES (1, 'one');
+ERROR: duplicate key value violates unique constraint "unique_tbl_i_t_key"
+DETAIL: Key (i, t)=(1, one) already exists.
+INSERT INTO UNIQUE_TBL VALUES (5, 'one');
+INSERT INTO UNIQUE_TBL (t) VALUES ('six');
+SELECT '' AS five, * FROM UNIQUE_TBL;
+ five | i | t
+------+---+-------
+ | 1 | one
+ | 2 | two
+ | 1 | three
+ | 5 | one
+ | | six
+(5 rows)
+
+DROP TABLE UNIQUE_TBL;
+--
+-- Deferrable unique constraints
+--
+CREATE TABLE unique_tbl (i int UNIQUE DEFERRABLE, t text);
+INSERT INTO unique_tbl VALUES (0, 'one');
+INSERT INTO unique_tbl VALUES (1, 'two');
+INSERT INTO unique_tbl VALUES (2, 'tree');
+INSERT INTO unique_tbl VALUES (3, 'four');
+INSERT INTO unique_tbl VALUES (4, 'five');
+BEGIN;
+-- default is immediate so this should fail right away
+UPDATE unique_tbl SET i = 1 WHERE i = 0;
+ERROR: duplicate key value violates unique constraint "unique_tbl_i_key"
+DETAIL: Key (i)=(1) already exists.
+ROLLBACK;
+-- check is done at end of statement, so this should succeed
+UPDATE unique_tbl SET i = i+1;
+SELECT * FROM unique_tbl;
+ i | t
+---+------
+ 1 | one
+ 2 | two
+ 3 | tree
+ 4 | four
+ 5 | five
+(5 rows)
+
+-- explicitly defer the constraint
+BEGIN;
+SET CONSTRAINTS unique_tbl_i_key DEFERRED;
+INSERT INTO unique_tbl VALUES (3, 'three');
+DELETE FROM unique_tbl WHERE t = 'tree'; -- makes constraint valid again
+COMMIT; -- should succeed
+SELECT * FROM unique_tbl;
+ i | t
+---+-------
+ 1 | one
+ 2 | two
+ 4 | four
+ 5 | five
+ 3 | three
+(5 rows)
+
+-- try adding an initially deferred constraint
+ALTER TABLE unique_tbl DROP CONSTRAINT unique_tbl_i_key;
+ALTER TABLE unique_tbl ADD CONSTRAINT unique_tbl_i_key
+ UNIQUE (i) DEFERRABLE INITIALLY DEFERRED;
+BEGIN;
+INSERT INTO unique_tbl VALUES (1, 'five');
+INSERT INTO unique_tbl VALUES (5, 'one');
+UPDATE unique_tbl SET i = 4 WHERE i = 2;
+UPDATE unique_tbl SET i = 2 WHERE i = 4 AND t = 'four';
+DELETE FROM unique_tbl WHERE i = 1 AND t = 'one';
+DELETE FROM unique_tbl WHERE i = 5 AND t = 'five';
+COMMIT;
+SELECT * FROM unique_tbl;
+ i | t
+---+-------
+ 3 | three
+ 1 | five
+ 5 | one
+ 4 | two
+ 2 | four
+(5 rows)
+
+-- should fail at commit-time
+BEGIN;
+INSERT INTO unique_tbl VALUES (3, 'Three'); -- should succeed for now
+COMMIT; -- should fail
+ERROR: duplicate key value violates unique constraint "unique_tbl_i_key"
+DETAIL: Key (i)=(3) already exists.
+-- make constraint check immediate
+BEGIN;
+SET CONSTRAINTS ALL IMMEDIATE;
+INSERT INTO unique_tbl VALUES (3, 'Three'); -- should fail
+ERROR: duplicate key value violates unique constraint "unique_tbl_i_key"
+DETAIL: Key (i)=(3) already exists.
+COMMIT;
+-- forced check when SET CONSTRAINTS is called
+BEGIN;
+SET CONSTRAINTS ALL DEFERRED;
+INSERT INTO unique_tbl VALUES (3, 'Three'); -- should succeed for now
+SET CONSTRAINTS ALL IMMEDIATE; -- should fail
+ERROR: duplicate key value violates unique constraint "unique_tbl_i_key"
+DETAIL: Key (i)=(3) already exists.
+COMMIT;
+-- test deferrable UNIQUE with a partitioned table
+CREATE TABLE parted_uniq_tbl (i int UNIQUE DEFERRABLE) partition by range (i);
+CREATE TABLE parted_uniq_tbl_1 PARTITION OF parted_uniq_tbl FOR VALUES FROM (0) TO (10);
+CREATE TABLE parted_uniq_tbl_2 PARTITION OF parted_uniq_tbl FOR VALUES FROM (20) TO (30);
+SELECT conname, conrelid::regclass FROM pg_constraint
+ WHERE conname LIKE 'parted_uniq%' ORDER BY conname;
+ conname | conrelid
+-------------------------+-------------------
+ parted_uniq_tbl_1_i_key | parted_uniq_tbl_1
+ parted_uniq_tbl_2_i_key | parted_uniq_tbl_2
+ parted_uniq_tbl_i_key | parted_uniq_tbl
+(3 rows)
+
+BEGIN;
+INSERT INTO parted_uniq_tbl VALUES (1);
+SAVEPOINT f;
+INSERT INTO parted_uniq_tbl VALUES (1); -- unique violation
+ERROR: duplicate key value violates unique constraint "parted_uniq_tbl_1_i_key"
+DETAIL: Key (i)=(1) already exists.
+ROLLBACK TO f;
+SET CONSTRAINTS parted_uniq_tbl_i_key DEFERRED;
+INSERT INTO parted_uniq_tbl VALUES (1); -- OK now, fail at commit
+COMMIT;
+ERROR: duplicate key value violates unique constraint "parted_uniq_tbl_1_i_key"
+DETAIL: Key (i)=(1) already exists.
+DROP TABLE parted_uniq_tbl;
+-- test a HOT update that invalidates the conflicting tuple.
+-- the trigger should still fire and catch the violation
+BEGIN;
+INSERT INTO unique_tbl VALUES (3, 'Three'); -- should succeed for now
+UPDATE unique_tbl SET t = 'THREE' WHERE i = 3 AND t = 'Three';
+COMMIT; -- should fail
+ERROR: duplicate key value violates unique constraint "unique_tbl_i_key"
+DETAIL: Key (i)=(3) already exists.
+SELECT * FROM unique_tbl;
+ i | t
+---+-------
+ 3 | three
+ 1 | five
+ 5 | one
+ 4 | two
+ 2 | four
+(5 rows)
+
+-- test a HOT update that modifies the newly inserted tuple,
+-- but should succeed because we then remove the other conflicting tuple.
+BEGIN;
+INSERT INTO unique_tbl VALUES(3, 'tree'); -- should succeed for now
+UPDATE unique_tbl SET t = 'threex' WHERE t = 'tree';
+DELETE FROM unique_tbl WHERE t = 'three';
+SELECT * FROM unique_tbl;
+ i | t
+---+--------
+ 1 | five
+ 5 | one
+ 4 | two
+ 2 | four
+ 3 | threex
+(5 rows)
+
+COMMIT;
+SELECT * FROM unique_tbl;
+ i | t
+---+--------
+ 1 | five
+ 5 | one
+ 4 | two
+ 2 | four
+ 3 | threex
+(5 rows)
+
+DROP TABLE unique_tbl;
+--
+-- EXCLUDE constraints
+--
+CREATE TABLE circles (
+ c1 CIRCLE,
+ c2 TEXT,
+ EXCLUDE USING gist
+ (c1 WITH &&, (c2::circle) WITH &&)
+ WHERE (circle_center(c1) <> '(0,0)')
+);
+-- these should succeed because they don't match the index predicate
+INSERT INTO circles VALUES('<(0,0), 5>', '<(0,0), 5>');
+INSERT INTO circles VALUES('<(0,0), 5>', '<(0,0), 4>');
+-- succeed
+INSERT INTO circles VALUES('<(10,10), 10>', '<(0,0), 5>');
+-- fail, overlaps
+INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 4>');
+ERROR: conflicting key value violates exclusion constraint "circles_c1_c2_excl"
+DETAIL: Key (c1, (c2::circle))=(<(20,20),10>, <(0,0),4>) conflicts with existing key (c1, (c2::circle))=(<(10,10),10>, <(0,0),5>).
+-- succeed, because violation is ignored
+INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 4>')
+ ON CONFLICT ON CONSTRAINT circles_c1_c2_excl DO NOTHING;
+-- fail, because DO UPDATE variant requires unique index
+INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 4>')
+ ON CONFLICT ON CONSTRAINT circles_c1_c2_excl DO UPDATE SET c2 = EXCLUDED.c2;
+ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints
+-- succeed because c1 doesn't overlap
+INSERT INTO circles VALUES('<(20,20), 1>', '<(0,0), 5>');
+-- succeed because c2 doesn't overlap
+INSERT INTO circles VALUES('<(20,20), 10>', '<(10,10), 5>');
+-- should fail on existing data without the WHERE clause
+ALTER TABLE circles ADD EXCLUDE USING gist
+ (c1 WITH &&, (c2::circle) WITH &&);
+ERROR: could not create exclusion constraint "circles_c1_c2_excl1"
+DETAIL: Key (c1, (c2::circle))=(<(0,0),5>, <(0,0),5>) conflicts with key (c1, (c2::circle))=(<(0,0),5>, <(0,0),4>).
+-- try reindexing an existing constraint
+REINDEX INDEX circles_c1_c2_excl;
+DROP TABLE circles;
+-- Check deferred exclusion constraint
+CREATE TABLE deferred_excl (
+ f1 int,
+ f2 int,
+ CONSTRAINT deferred_excl_con EXCLUDE (f1 WITH =) INITIALLY DEFERRED
+);
+INSERT INTO deferred_excl VALUES(1);
+INSERT INTO deferred_excl VALUES(2);
+INSERT INTO deferred_excl VALUES(1); -- fail
+ERROR: conflicting key value violates exclusion constraint "deferred_excl_con"
+DETAIL: Key (f1)=(1) conflicts with existing key (f1)=(1).
+INSERT INTO deferred_excl VALUES(1) ON CONFLICT ON CONSTRAINT deferred_excl_con DO NOTHING; -- fail
+ERROR: ON CONFLICT does not support deferrable unique constraints/exclusion constraints as arbiters
+BEGIN;
+INSERT INTO deferred_excl VALUES(2); -- no fail here
+COMMIT; -- should fail here
+ERROR: conflicting key value violates exclusion constraint "deferred_excl_con"
+DETAIL: Key (f1)=(2) conflicts with existing key (f1)=(2).
+BEGIN;
+INSERT INTO deferred_excl VALUES(3);
+INSERT INTO deferred_excl VALUES(3); -- no fail here
+COMMIT; -- should fail here
+ERROR: conflicting key value violates exclusion constraint "deferred_excl_con"
+DETAIL: Key (f1)=(3) conflicts with existing key (f1)=(3).
+-- bug #13148: deferred constraint versus HOT update
+BEGIN;
+INSERT INTO deferred_excl VALUES(2, 1); -- no fail here
+DELETE FROM deferred_excl WHERE f1 = 2 AND f2 IS NULL; -- remove old row
+UPDATE deferred_excl SET f2 = 2 WHERE f1 = 2;
+COMMIT; -- should not fail
+SELECT * FROM deferred_excl;
+ f1 | f2
+----+----
+ 1 |
+ 2 | 2
+(2 rows)
+
+ALTER TABLE deferred_excl DROP CONSTRAINT deferred_excl_con;
+-- This should fail, but worth testing because of HOT updates
+UPDATE deferred_excl SET f1 = 3;
+ALTER TABLE deferred_excl ADD EXCLUDE (f1 WITH =);
+ERROR: could not create exclusion constraint "deferred_excl_f1_excl"
+DETAIL: Key (f1)=(3) conflicts with key (f1)=(3).
+DROP TABLE deferred_excl;
+-- Comments
+-- Setup a low-level role to enforce non-superuser checks.
+CREATE ROLE regress_constraint_comments;
+SET SESSION AUTHORIZATION regress_constraint_comments;
+CREATE TABLE constraint_comments_tbl (a int CONSTRAINT the_constraint CHECK (a > 0));
+CREATE DOMAIN constraint_comments_dom AS int CONSTRAINT the_constraint CHECK (value > 0);
+COMMENT ON CONSTRAINT the_constraint ON constraint_comments_tbl IS 'yes, the comment';
+COMMENT ON CONSTRAINT the_constraint ON DOMAIN constraint_comments_dom IS 'yes, another comment';
+-- no such constraint
+COMMENT ON CONSTRAINT no_constraint ON constraint_comments_tbl IS 'yes, the comment';
+ERROR: constraint "no_constraint" for table "constraint_comments_tbl" does not exist
+COMMENT ON CONSTRAINT no_constraint ON DOMAIN constraint_comments_dom IS 'yes, another comment';
+ERROR: constraint "no_constraint" for domain constraint_comments_dom does not exist
+-- no such table/domain
+COMMENT ON CONSTRAINT the_constraint ON no_comments_tbl IS 'bad comment';
+ERROR: relation "no_comments_tbl" does not exist
+COMMENT ON CONSTRAINT the_constraint ON DOMAIN no_comments_dom IS 'another bad comment';
+ERROR: type "no_comments_dom" does not exist
+COMMENT ON CONSTRAINT the_constraint ON constraint_comments_tbl IS NULL;
+COMMENT ON CONSTRAINT the_constraint ON DOMAIN constraint_comments_dom IS NULL;
+-- unauthorized user
+RESET SESSION AUTHORIZATION;
+CREATE ROLE regress_constraint_comments_noaccess;
+SET SESSION AUTHORIZATION regress_constraint_comments_noaccess;
+COMMENT ON CONSTRAINT the_constraint ON constraint_comments_tbl IS 'no, the comment';
+ERROR: must be owner of relation constraint_comments_tbl
+COMMENT ON CONSTRAINT the_constraint ON DOMAIN constraint_comments_dom IS 'no, another comment';
+ERROR: must be owner of type constraint_comments_dom
+RESET SESSION AUTHORIZATION;
+DROP TABLE constraint_comments_tbl;
+DROP DOMAIN constraint_comments_dom;
+DROP ROLE regress_constraint_comments;
+DROP ROLE regress_constraint_comments_noaccess;
diff --git a/src/test/regress/output/copy.source b/src/test/regress/output/copy.source
new file mode 100644
index 0000000..938d355
--- /dev/null
+++ b/src/test/regress/output/copy.source
@@ -0,0 +1,167 @@
+--
+-- COPY
+--
+-- CLASS POPULATION
+-- (any resemblance to real life is purely coincidental)
+--
+COPY aggtest FROM '@abs_srcdir@/data/agg.data';
+COPY onek FROM '@abs_srcdir@/data/onek.data';
+COPY onek TO '@abs_builddir@/results/onek.data';
+DELETE FROM onek;
+COPY onek FROM '@abs_builddir@/results/onek.data';
+COPY tenk1 FROM '@abs_srcdir@/data/tenk.data';
+COPY slow_emp4000 FROM '@abs_srcdir@/data/rect.data';
+COPY person FROM '@abs_srcdir@/data/person.data';
+COPY emp FROM '@abs_srcdir@/data/emp.data';
+COPY student FROM '@abs_srcdir@/data/student.data';
+COPY stud_emp FROM '@abs_srcdir@/data/stud_emp.data';
+COPY road FROM '@abs_srcdir@/data/streets.data';
+COPY real_city FROM '@abs_srcdir@/data/real_city.data';
+COPY hash_i4_heap FROM '@abs_srcdir@/data/hash.data';
+COPY hash_name_heap FROM '@abs_srcdir@/data/hash.data';
+COPY hash_txt_heap FROM '@abs_srcdir@/data/hash.data';
+COPY hash_f8_heap FROM '@abs_srcdir@/data/hash.data';
+COPY test_tsvector FROM '@abs_srcdir@/data/tsearch.data';
+COPY testjsonb FROM '@abs_srcdir@/data/jsonb.data';
+-- 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).
+--
+-- COPY hash_ovfl_heap FROM '@abs_srcdir@/data/hashovfl.data';
+COPY bt_i4_heap FROM '@abs_srcdir@/data/desc.data';
+COPY bt_name_heap FROM '@abs_srcdir@/data/hash.data';
+COPY bt_txt_heap FROM '@abs_srcdir@/data/desc.data';
+COPY bt_f8_heap FROM '@abs_srcdir@/data/hash.data';
+COPY array_op_test FROM '@abs_srcdir@/data/array.data';
+COPY array_index_op_test FROM '@abs_srcdir@/data/array.data';
+-- 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
+create temp table copytest (
+ style text,
+ test text,
+ filler int);
+insert into copytest values('DOS',E'abc\r\ndef',1);
+insert into copytest values('Unix',E'abc\ndef',2);
+insert into copytest values('Mac',E'abc\rdef',3);
+insert into copytest values(E'esc\\ape',E'a\\r\\\r\\\n\\nb',4);
+copy copytest to '@abs_builddir@/results/copytest.csv' csv;
+create temp table copytest2 (like copytest);
+copy copytest2 from '@abs_builddir@/results/copytest.csv' csv;
+select * from copytest except select * from copytest2;
+ style | test | filler
+-------+------+--------
+(0 rows)
+
+truncate copytest2;
+--- same test but with an escape char different from quote char
+copy copytest to '@abs_builddir@/results/copytest.csv' csv quote '''' escape E'\\';
+copy copytest2 from '@abs_builddir@/results/copytest.csv' csv quote '''' escape E'\\';
+select * from copytest except select * from copytest2;
+ style | test | filler
+-------+------+--------
+(0 rows)
+
+-- test header line feature
+create temp table copytest3 (
+ c1 int,
+ "col with , comma" text,
+ "col with "" quote" int);
+copy copytest3 from stdin csv header;
+copy copytest3 to stdout csv header;
+c1,"col with , comma","col with "" quote"
+1,a,1
+2,b,2
+-- test copy from with a partitioned table
+create table parted_copytest (
+ a int,
+ b int,
+ c text
+) partition by list (b);
+create table parted_copytest_a1 (c text, b int, a int);
+create table parted_copytest_a2 (a int, c text, b int);
+alter table parted_copytest attach partition parted_copytest_a1 for values in(1);
+alter table parted_copytest attach partition parted_copytest_a2 for values in(2);
+-- We must insert enough rows to trigger multi-inserts. These are only
+-- enabled adaptively when there are few enough partition changes.
+insert into parted_copytest select x,1,'One' from generate_series(1,1000) x;
+insert into parted_copytest select x,2,'Two' from generate_series(1001,1010) x;
+insert into parted_copytest select x,1,'One' from generate_series(1011,1020) x;
+copy (select * from parted_copytest order by a) to '@abs_builddir@/results/parted_copytest.csv';
+truncate parted_copytest;
+copy parted_copytest from '@abs_builddir@/results/parted_copytest.csv';
+-- Ensure COPY FREEZE errors for partitioned tables.
+begin;
+truncate parted_copytest;
+copy parted_copytest from '@abs_builddir@/results/parted_copytest.csv' (freeze);
+ERROR: cannot perform COPY FREEZE on a partitioned table
+rollback;
+select tableoid::regclass,count(*),sum(a) from parted_copytest
+group by tableoid order by tableoid::regclass::name;
+ tableoid | count | sum
+--------------------+-------+--------
+ parted_copytest_a1 | 1010 | 510655
+ parted_copytest_a2 | 10 | 10055
+(2 rows)
+
+truncate parted_copytest;
+-- create before insert row trigger on parted_copytest_a2
+create function part_ins_func() returns trigger language plpgsql as $$
+begin
+ return new;
+end;
+$$;
+create trigger part_ins_trig
+ before insert on parted_copytest_a2
+ for each row
+ execute procedure part_ins_func();
+copy parted_copytest from '@abs_builddir@/results/parted_copytest.csv';
+select tableoid::regclass,count(*),sum(a) from parted_copytest
+group by tableoid order by tableoid::regclass::name;
+ tableoid | count | sum
+--------------------+-------+--------
+ parted_copytest_a1 | 1010 | 510655
+ parted_copytest_a2 | 10 | 10055
+(2 rows)
+
+truncate table parted_copytest;
+create index on parted_copytest (b);
+drop trigger part_ins_trig on parted_copytest_a2;
+copy parted_copytest from stdin;
+-- Ensure index entries were properly added during the copy.
+select * from parted_copytest where b = 1;
+ a | b | c
+---+---+------
+ 1 | 1 | str1
+(1 row)
+
+select * from parted_copytest where b = 2;
+ a | b | c
+---+---+------
+ 2 | 2 | str2
+(1 row)
+
+drop table parted_copytest;
diff --git a/src/test/regress/output/create_function_1.source b/src/test/regress/output/create_function_1.source
new file mode 100644
index 0000000..4d78fa1
--- /dev/null
+++ b/src/test/regress/output/create_function_1.source
@@ -0,0 +1,103 @@
+--
+-- CREATE_FUNCTION_1
+--
+CREATE FUNCTION widget_in(cstring)
+ RETURNS widget
+ AS '@libdir@/regress@DLSUFFIX@'
+ LANGUAGE C STRICT IMMUTABLE;
+NOTICE: type "widget" is not yet defined
+DETAIL: Creating a shell type definition.
+CREATE FUNCTION widget_out(widget)
+ RETURNS cstring
+ AS '@libdir@/regress@DLSUFFIX@'
+ LANGUAGE C STRICT IMMUTABLE;
+NOTICE: argument type widget is only a shell
+CREATE FUNCTION int44in(cstring)
+ RETURNS city_budget
+ AS '@libdir@/regress@DLSUFFIX@'
+ LANGUAGE C STRICT IMMUTABLE;
+NOTICE: type "city_budget" is not yet defined
+DETAIL: Creating a shell type definition.
+CREATE FUNCTION int44out(city_budget)
+ RETURNS cstring
+ AS '@libdir@/regress@DLSUFFIX@'
+ LANGUAGE C STRICT IMMUTABLE;
+NOTICE: argument type city_budget is only a shell
+CREATE FUNCTION check_primary_key ()
+ RETURNS trigger
+ AS '@libdir@/refint@DLSUFFIX@'
+ LANGUAGE C;
+CREATE FUNCTION check_foreign_key ()
+ RETURNS trigger
+ AS '@libdir@/refint@DLSUFFIX@'
+ LANGUAGE C;
+CREATE FUNCTION autoinc ()
+ RETURNS trigger
+ AS '@libdir@/autoinc@DLSUFFIX@'
+ LANGUAGE C;
+CREATE FUNCTION trigger_return_old ()
+ RETURNS trigger
+ AS '@libdir@/regress@DLSUFFIX@'
+ LANGUAGE C;
+CREATE FUNCTION ttdummy ()
+ RETURNS trigger
+ AS '@libdir@/regress@DLSUFFIX@'
+ LANGUAGE C;
+CREATE FUNCTION set_ttdummy (int4)
+ RETURNS int4
+ AS '@libdir@/regress@DLSUFFIX@'
+ LANGUAGE C STRICT;
+CREATE FUNCTION make_tuple_indirect (record)
+ RETURNS record
+ AS '@libdir@/regress@DLSUFFIX@'
+ LANGUAGE C STRICT;
+CREATE FUNCTION test_atomic_ops()
+ RETURNS bool
+ AS '@libdir@/regress@DLSUFFIX@'
+ LANGUAGE C;
+-- Tests creating a FDW handler
+CREATE FUNCTION test_fdw_handler()
+ RETURNS fdw_handler
+ AS '@libdir@/regress@DLSUFFIX@', 'test_fdw_handler'
+ LANGUAGE C;
+CREATE FUNCTION test_support_func(internal)
+ RETURNS internal
+ AS '@libdir@/regress@DLSUFFIX@', 'test_support_func'
+ LANGUAGE C STRICT;
+CREATE FUNCTION test_opclass_options_func(internal)
+ RETURNS void
+ AS '@libdir@/regress@DLSUFFIX@', 'test_opclass_options_func'
+ LANGUAGE C;
+-- Things that shouldn't work:
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
+ AS 'SELECT ''not an integer'';';
+ERROR: return type mismatch in function declared to return integer
+DETAIL: Actual return type is text.
+CONTEXT: SQL function "test1"
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
+ AS 'not even SQL';
+ERROR: syntax error at or near "not"
+LINE 2: AS 'not even SQL';
+ ^
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
+ AS 'SELECT 1, 2, 3;';
+ERROR: return type mismatch in function declared to return integer
+DETAIL: Final statement must return exactly one column.
+CONTEXT: SQL function "test1"
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
+ AS 'SELECT $2;';
+ERROR: there is no parameter $2
+LINE 2: AS 'SELECT $2;';
+ ^
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
+ AS 'a', 'b';
+ERROR: only one AS item needed for language "sql"
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE C
+ AS 'nosuchfile';
+ERROR: could not access file "nosuchfile": No such file or directory
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE C
+ AS '@libdir@/regress@DLSUFFIX@', 'nosuchsymbol';
+ERROR: could not find function "nosuchsymbol" in file "@libdir@/regress@DLSUFFIX@"
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE internal
+ AS 'nosuch';
+ERROR: there is no built-in function named "nosuch"
diff --git a/src/test/regress/output/create_function_2.source b/src/test/regress/output/create_function_2.source
new file mode 100644
index 0000000..ac9a7f5
--- /dev/null
+++ b/src/test/regress/output/create_function_2.source
@@ -0,0 +1,69 @@
+--
+-- CREATE_FUNCTION_2
+--
+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;
+NOTICE: type reference hobbies_r.name%TYPE converted to text
+NOTICE: type reference hobbies_r.person%TYPE converted to text
+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 '@libdir@/regress@DLSUFFIX@'
+ LANGUAGE C STRICT;
+CREATE FUNCTION overpaid(emp)
+ RETURNS bool
+ AS '@libdir@/regress@DLSUFFIX@'
+ LANGUAGE C STRICT;
+CREATE FUNCTION interpt_pp(path, path)
+ RETURNS point
+ AS '@libdir@/regress@DLSUFFIX@'
+ LANGUAGE C STRICT;
+CREATE FUNCTION reverse_name(name)
+ RETURNS name
+ AS '@libdir@/regress@DLSUFFIX@'
+ LANGUAGE C STRICT;
+--
+-- Function dynamic loading
+--
+LOAD '@libdir@/regress@DLSUFFIX@';
diff --git a/src/test/regress/output/largeobject.source b/src/test/regress/output/largeobject.source
new file mode 100644
index 0000000..91090f0
--- /dev/null
+++ b/src/test/regress/output/largeobject.source
@@ -0,0 +1,481 @@
+--
+-- Test large object support
+--
+-- ensure consistent test output regardless of the default bytea format
+SET bytea_output TO escape;
+-- Load a file
+CREATE TABLE lotest_stash_values (loid oid, fd integer);
+-- lo_creat(mode integer) returns oid
+-- The mode arg to lo_creat is unused, some vestigal holdover from ancient times
+-- returns the large object id
+INSERT INTO lotest_stash_values (loid) SELECT lo_creat(42);
+-- Test ALTER LARGE OBJECT
+CREATE ROLE regress_lo_user;
+DO $$
+ BEGIN
+ EXECUTE 'ALTER LARGE OBJECT ' || (select loid from lotest_stash_values)
+ || ' OWNER TO regress_lo_user';
+ END
+$$;
+SELECT
+ rol.rolname
+FROM
+ lotest_stash_values s
+ JOIN pg_largeobject_metadata lo ON s.loid = lo.oid
+ JOIN pg_authid rol ON lo.lomowner = rol.oid;
+ rolname
+-----------------
+ regress_lo_user
+(1 row)
+
+-- NOTE: large objects require transactions
+BEGIN;
+-- lo_open(lobjId oid, mode integer) returns integer
+-- The mode parameter to lo_open uses two constants:
+-- INV_READ = 0x20000
+-- INV_WRITE = 0x40000
+-- The return value is a file descriptor-like value which remains valid for the
+-- transaction.
+UPDATE lotest_stash_values SET fd = lo_open(loid, CAST(x'20000' | x'40000' AS integer));
+-- loread/lowrite names are wonky, different from other functions which are lo_*
+-- lowrite(fd integer, data bytea) returns integer
+-- the integer is the number of bytes written
+SELECT lowrite(fd, '
+I wandered lonely as a cloud
+That floats on high o''er vales and hills,
+When all at once I saw a crowd,
+A host, of golden daffodils;
+Beside the lake, beneath the trees,
+Fluttering and dancing in the breeze.
+
+Continuous as the stars that shine
+And twinkle on the milky way,
+They stretched in never-ending line
+Along the margin of a bay:
+Ten thousand saw I at a glance,
+Tossing their heads in sprightly dance.
+
+The waves beside them danced; but they
+Out-did the sparkling waves in glee:
+A poet could not but be gay,
+In such a jocund company:
+I gazed--and gazed--but little thought
+What wealth the show to me had brought:
+
+For oft, when on my couch I lie
+In vacant or in pensive mood,
+They flash upon that inward eye
+Which is the bliss of solitude;
+And then my heart with pleasure fills,
+And dances with the daffodils.
+
+ -- William Wordsworth
+') FROM lotest_stash_values;
+ lowrite
+---------
+ 848
+(1 row)
+
+-- lo_close(fd integer) returns integer
+-- return value is 0 for success, or <0 for error (actually only -1, but...)
+SELECT lo_close(fd) FROM lotest_stash_values;
+ lo_close
+----------
+ 0
+(1 row)
+
+END;
+-- Copy to another large object.
+-- Note: we intentionally don't remove the object created here;
+-- it's left behind to help test pg_dump.
+SELECT lo_from_bytea(0, lo_get(loid)) AS newloid FROM lotest_stash_values
+\gset
+-- Add a comment to it, as well, for pg_dump/pg_upgrade testing.
+COMMENT ON LARGE OBJECT :newloid IS 'I Wandered Lonely as a Cloud';
+-- Read out a portion
+BEGIN;
+UPDATE lotest_stash_values SET fd=lo_open(loid, CAST(x'20000' | x'40000' AS integer));
+-- lo_lseek(fd integer, offset integer, whence integer) returns integer
+-- offset is in bytes, whence is one of three values:
+-- SEEK_SET (= 0) meaning relative to beginning
+-- SEEK_CUR (= 1) meaning relative to current position
+-- SEEK_END (= 2) meaning relative to end (offset better be negative)
+-- returns current position in file
+SELECT lo_lseek(fd, 104, 0) FROM lotest_stash_values;
+ lo_lseek
+----------
+ 104
+(1 row)
+
+-- loread/lowrite names are wonky, different from other functions which are lo_*
+-- loread(fd integer, len integer) returns bytea
+SELECT loread(fd, 28) FROM lotest_stash_values;
+ loread
+------------------------------
+ A host, of golden daffodils;
+(1 row)
+
+SELECT lo_lseek(fd, -19, 1) FROM lotest_stash_values;
+ lo_lseek
+----------
+ 113
+(1 row)
+
+SELECT lowrite(fd, 'n') FROM lotest_stash_values;
+ lowrite
+---------
+ 1
+(1 row)
+
+SELECT lo_tell(fd) FROM lotest_stash_values;
+ lo_tell
+---------
+ 114
+(1 row)
+
+SELECT lo_lseek(fd, -744, 2) FROM lotest_stash_values;
+ lo_lseek
+----------
+ 104
+(1 row)
+
+SELECT loread(fd, 28) FROM lotest_stash_values;
+ loread
+------------------------------
+ A host, on golden daffodils;
+(1 row)
+
+SELECT lo_close(fd) FROM lotest_stash_values;
+ lo_close
+----------
+ 0
+(1 row)
+
+END;
+-- Test resource management
+BEGIN;
+SELECT lo_open(loid, x'40000'::int) from lotest_stash_values;
+ lo_open
+---------
+ 0
+(1 row)
+
+ABORT;
+-- Test truncation.
+BEGIN;
+UPDATE lotest_stash_values SET fd=lo_open(loid, CAST(x'20000' | x'40000' AS integer));
+SELECT lo_truncate(fd, 11) FROM lotest_stash_values;
+ lo_truncate
+-------------
+ 0
+(1 row)
+
+SELECT loread(fd, 15) FROM lotest_stash_values;
+ loread
+----------------
+ \012I wandered
+(1 row)
+
+SELECT lo_truncate(fd, 10000) FROM lotest_stash_values;
+ lo_truncate
+-------------
+ 0
+(1 row)
+
+SELECT loread(fd, 10) FROM lotest_stash_values;
+ loread
+------------------------------------------
+ \000\000\000\000\000\000\000\000\000\000
+(1 row)
+
+SELECT lo_lseek(fd, 0, 2) FROM lotest_stash_values;
+ lo_lseek
+----------
+ 10000
+(1 row)
+
+SELECT lo_tell(fd) FROM lotest_stash_values;
+ lo_tell
+---------
+ 10000
+(1 row)
+
+SELECT lo_truncate(fd, 5000) FROM lotest_stash_values;
+ lo_truncate
+-------------
+ 0
+(1 row)
+
+SELECT lo_lseek(fd, 0, 2) FROM lotest_stash_values;
+ lo_lseek
+----------
+ 5000
+(1 row)
+
+SELECT lo_tell(fd) FROM lotest_stash_values;
+ lo_tell
+---------
+ 5000
+(1 row)
+
+SELECT lo_close(fd) FROM lotest_stash_values;
+ lo_close
+----------
+ 0
+(1 row)
+
+END;
+-- Test 64-bit large object functions.
+BEGIN;
+UPDATE lotest_stash_values SET fd = lo_open(loid, CAST(x'20000' | x'40000' AS integer));
+SELECT lo_lseek64(fd, 4294967296, 0) FROM lotest_stash_values;
+ lo_lseek64
+------------
+ 4294967296
+(1 row)
+
+SELECT lowrite(fd, 'offset:4GB') FROM lotest_stash_values;
+ lowrite
+---------
+ 10
+(1 row)
+
+SELECT lo_tell64(fd) FROM lotest_stash_values;
+ lo_tell64
+------------
+ 4294967306
+(1 row)
+
+SELECT lo_lseek64(fd, -10, 1) FROM lotest_stash_values;
+ lo_lseek64
+------------
+ 4294967296
+(1 row)
+
+SELECT lo_tell64(fd) FROM lotest_stash_values;
+ lo_tell64
+------------
+ 4294967296
+(1 row)
+
+SELECT loread(fd, 10) FROM lotest_stash_values;
+ loread
+------------
+ offset:4GB
+(1 row)
+
+SELECT lo_truncate64(fd, 5000000000) FROM lotest_stash_values;
+ lo_truncate64
+---------------
+ 0
+(1 row)
+
+SELECT lo_lseek64(fd, 0, 2) FROM lotest_stash_values;
+ lo_lseek64
+------------
+ 5000000000
+(1 row)
+
+SELECT lo_tell64(fd) FROM lotest_stash_values;
+ lo_tell64
+------------
+ 5000000000
+(1 row)
+
+SELECT lo_truncate64(fd, 3000000000) FROM lotest_stash_values;
+ lo_truncate64
+---------------
+ 0
+(1 row)
+
+SELECT lo_lseek64(fd, 0, 2) FROM lotest_stash_values;
+ lo_lseek64
+------------
+ 3000000000
+(1 row)
+
+SELECT lo_tell64(fd) FROM lotest_stash_values;
+ lo_tell64
+------------
+ 3000000000
+(1 row)
+
+SELECT lo_close(fd) FROM lotest_stash_values;
+ lo_close
+----------
+ 0
+(1 row)
+
+END;
+-- lo_unlink(lobjId oid) returns integer
+-- return value appears to always be 1
+SELECT lo_unlink(loid) from lotest_stash_values;
+ lo_unlink
+-----------
+ 1
+(1 row)
+
+TRUNCATE lotest_stash_values;
+INSERT INTO lotest_stash_values (loid) SELECT lo_import('@abs_srcdir@/data/tenk.data');
+BEGIN;
+UPDATE lotest_stash_values SET fd=lo_open(loid, CAST(x'20000' | x'40000' AS integer));
+-- verify length of large object
+SELECT lo_lseek(fd, 0, 2) FROM lotest_stash_values;
+ lo_lseek
+----------
+ 670800
+(1 row)
+
+-- with the default BLCKSZ, LOBLKSIZE = 2048, so this positions us for a block
+-- edge case
+SELECT lo_lseek(fd, 2030, 0) FROM lotest_stash_values;
+ lo_lseek
+----------
+ 2030
+(1 row)
+
+-- this should get half of the value from page 0 and half from page 1 of the
+-- large object
+SELECT loread(fd, 36) FROM lotest_stash_values;
+ loread
+-----------------------------------------------------------------
+ AAA\011FBAAAA\011VVVVxx\0122513\01132\0111\0111\0113\01113\0111
+(1 row)
+
+SELECT lo_tell(fd) FROM lotest_stash_values;
+ lo_tell
+---------
+ 2066
+(1 row)
+
+SELECT lo_lseek(fd, -26, 1) FROM lotest_stash_values;
+ lo_lseek
+----------
+ 2040
+(1 row)
+
+SELECT lowrite(fd, 'abcdefghijklmnop') FROM lotest_stash_values;
+ lowrite
+---------
+ 16
+(1 row)
+
+SELECT lo_lseek(fd, 2030, 0) FROM lotest_stash_values;
+ lo_lseek
+----------
+ 2030
+(1 row)
+
+SELECT loread(fd, 36) FROM lotest_stash_values;
+ loread
+-----------------------------------------------------
+ AAA\011FBAAAAabcdefghijklmnop1\0111\0113\01113\0111
+(1 row)
+
+SELECT lo_close(fd) FROM lotest_stash_values;
+ lo_close
+----------
+ 0
+(1 row)
+
+END;
+SELECT lo_export(loid, '@abs_builddir@/results/lotest.txt') FROM lotest_stash_values;
+ lo_export
+-----------
+ 1
+(1 row)
+
+\lo_import '@abs_builddir@/results/lotest.txt'
+\set newloid :LASTOID
+-- just make sure \lo_export does not barf
+\lo_export :newloid '@abs_builddir@/results/lotest2.txt'
+-- This is a hack to test that export/import are reversible
+-- This uses knowledge about the inner workings of large object mechanism
+-- which should not be used outside it. This makes it a HACK
+SELECT pageno, data FROM pg_largeobject WHERE loid = (SELECT loid from lotest_stash_values)
+EXCEPT
+SELECT pageno, data FROM pg_largeobject WHERE loid = :newloid;
+ pageno | data
+--------+------
+(0 rows)
+
+SELECT lo_unlink(loid) FROM lotest_stash_values;
+ lo_unlink
+-----------
+ 1
+(1 row)
+
+TRUNCATE lotest_stash_values;
+\lo_unlink :newloid
+\lo_import '@abs_builddir@/results/lotest.txt'
+\set newloid_1 :LASTOID
+SELECT lo_from_bytea(0, lo_get(:newloid_1)) AS newloid_2
+\gset
+SELECT md5(lo_get(:newloid_1)) = md5(lo_get(:newloid_2));
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT lo_get(:newloid_1, 0, 20);
+ lo_get
+-------------------------------------------
+ 8800\0110\0110\0110\0110\0110\0110\011800
+(1 row)
+
+SELECT lo_get(:newloid_1, 10, 20);
+ lo_get
+-------------------------------------------
+ \0110\0110\0110\011800\011800\0113800\011
+(1 row)
+
+SELECT lo_put(:newloid_1, 5, decode('afafafaf', 'hex'));
+ lo_put
+--------
+
+(1 row)
+
+SELECT lo_get(:newloid_1, 0, 20);
+ lo_get
+-------------------------------------------------
+ 8800\011\257\257\257\2570\0110\0110\0110\011800
+(1 row)
+
+SELECT lo_put(:newloid_1, 4294967310, 'foo');
+ lo_put
+--------
+
+(1 row)
+
+SELECT lo_get(:newloid_1);
+ERROR: large object read request is too large
+SELECT lo_get(:newloid_1, 4294967294, 100);
+ lo_get
+---------------------------------------------------------------------
+ \000\000\000\000\000\000\000\000\000\000\000\000\000\000\000\000foo
+(1 row)
+
+\lo_unlink :newloid_1
+\lo_unlink :newloid_2
+-- This object is left in the database for pg_dump test purposes
+SELECT lo_from_bytea(0, E'\\xdeadbeef') AS newloid
+\gset
+SET bytea_output TO hex;
+SELECT lo_get(:newloid);
+ lo_get
+------------
+ \xdeadbeef
+(1 row)
+
+-- Create one more object that we leave behind for testing pg_dump/pg_upgrade;
+-- this one intentionally has an OID in the system range
+SELECT lo_create(2121);
+ lo_create
+-----------
+ 2121
+(1 row)
+
+COMMENT ON LARGE OBJECT 2121 IS 'testing comments';
+-- Clean up
+DROP TABLE lotest_stash_values;
+DROP ROLE regress_lo_user;
diff --git a/src/test/regress/output/largeobject_1.source b/src/test/regress/output/largeobject_1.source
new file mode 100644
index 0000000..ec01f3e
--- /dev/null
+++ b/src/test/regress/output/largeobject_1.source
@@ -0,0 +1,481 @@
+--
+-- Test large object support
+--
+-- ensure consistent test output regardless of the default bytea format
+SET bytea_output TO escape;
+-- Load a file
+CREATE TABLE lotest_stash_values (loid oid, fd integer);
+-- lo_creat(mode integer) returns oid
+-- The mode arg to lo_creat is unused, some vestigal holdover from ancient times
+-- returns the large object id
+INSERT INTO lotest_stash_values (loid) SELECT lo_creat(42);
+-- Test ALTER LARGE OBJECT
+CREATE ROLE regress_lo_user;
+DO $$
+ BEGIN
+ EXECUTE 'ALTER LARGE OBJECT ' || (select loid from lotest_stash_values)
+ || ' OWNER TO regress_lo_user';
+ END
+$$;
+SELECT
+ rol.rolname
+FROM
+ lotest_stash_values s
+ JOIN pg_largeobject_metadata lo ON s.loid = lo.oid
+ JOIN pg_authid rol ON lo.lomowner = rol.oid;
+ rolname
+-----------------
+ regress_lo_user
+(1 row)
+
+-- NOTE: large objects require transactions
+BEGIN;
+-- lo_open(lobjId oid, mode integer) returns integer
+-- The mode parameter to lo_open uses two constants:
+-- INV_READ = 0x20000
+-- INV_WRITE = 0x40000
+-- The return value is a file descriptor-like value which remains valid for the
+-- transaction.
+UPDATE lotest_stash_values SET fd = lo_open(loid, CAST(x'20000' | x'40000' AS integer));
+-- loread/lowrite names are wonky, different from other functions which are lo_*
+-- lowrite(fd integer, data bytea) returns integer
+-- the integer is the number of bytes written
+SELECT lowrite(fd, '
+I wandered lonely as a cloud
+That floats on high o''er vales and hills,
+When all at once I saw a crowd,
+A host, of golden daffodils;
+Beside the lake, beneath the trees,
+Fluttering and dancing in the breeze.
+
+Continuous as the stars that shine
+And twinkle on the milky way,
+They stretched in never-ending line
+Along the margin of a bay:
+Ten thousand saw I at a glance,
+Tossing their heads in sprightly dance.
+
+The waves beside them danced; but they
+Out-did the sparkling waves in glee:
+A poet could not but be gay,
+In such a jocund company:
+I gazed--and gazed--but little thought
+What wealth the show to me had brought:
+
+For oft, when on my couch I lie
+In vacant or in pensive mood,
+They flash upon that inward eye
+Which is the bliss of solitude;
+And then my heart with pleasure fills,
+And dances with the daffodils.
+
+ -- William Wordsworth
+') FROM lotest_stash_values;
+ lowrite
+---------
+ 848
+(1 row)
+
+-- lo_close(fd integer) returns integer
+-- return value is 0 for success, or <0 for error (actually only -1, but...)
+SELECT lo_close(fd) FROM lotest_stash_values;
+ lo_close
+----------
+ 0
+(1 row)
+
+END;
+-- Copy to another large object.
+-- Note: we intentionally don't remove the object created here;
+-- it's left behind to help test pg_dump.
+SELECT lo_from_bytea(0, lo_get(loid)) AS newloid FROM lotest_stash_values
+\gset
+-- Add a comment to it, as well, for pg_dump/pg_upgrade testing.
+COMMENT ON LARGE OBJECT :newloid IS 'I Wandered Lonely as a Cloud';
+-- Read out a portion
+BEGIN;
+UPDATE lotest_stash_values SET fd=lo_open(loid, CAST(x'20000' | x'40000' AS integer));
+-- lo_lseek(fd integer, offset integer, whence integer) returns integer
+-- offset is in bytes, whence is one of three values:
+-- SEEK_SET (= 0) meaning relative to beginning
+-- SEEK_CUR (= 1) meaning relative to current position
+-- SEEK_END (= 2) meaning relative to end (offset better be negative)
+-- returns current position in file
+SELECT lo_lseek(fd, 104, 0) FROM lotest_stash_values;
+ lo_lseek
+----------
+ 104
+(1 row)
+
+-- loread/lowrite names are wonky, different from other functions which are lo_*
+-- loread(fd integer, len integer) returns bytea
+SELECT loread(fd, 28) FROM lotest_stash_values;
+ loread
+------------------------------
+ A host, of golden daffodils;
+(1 row)
+
+SELECT lo_lseek(fd, -19, 1) FROM lotest_stash_values;
+ lo_lseek
+----------
+ 113
+(1 row)
+
+SELECT lowrite(fd, 'n') FROM lotest_stash_values;
+ lowrite
+---------
+ 1
+(1 row)
+
+SELECT lo_tell(fd) FROM lotest_stash_values;
+ lo_tell
+---------
+ 114
+(1 row)
+
+SELECT lo_lseek(fd, -744, 2) FROM lotest_stash_values;
+ lo_lseek
+----------
+ 104
+(1 row)
+
+SELECT loread(fd, 28) FROM lotest_stash_values;
+ loread
+------------------------------
+ A host, on golden daffodils;
+(1 row)
+
+SELECT lo_close(fd) FROM lotest_stash_values;
+ lo_close
+----------
+ 0
+(1 row)
+
+END;
+-- Test resource management
+BEGIN;
+SELECT lo_open(loid, x'40000'::int) from lotest_stash_values;
+ lo_open
+---------
+ 0
+(1 row)
+
+ABORT;
+-- Test truncation.
+BEGIN;
+UPDATE lotest_stash_values SET fd=lo_open(loid, CAST(x'20000' | x'40000' AS integer));
+SELECT lo_truncate(fd, 11) FROM lotest_stash_values;
+ lo_truncate
+-------------
+ 0
+(1 row)
+
+SELECT loread(fd, 15) FROM lotest_stash_values;
+ loread
+----------------
+ \012I wandered
+(1 row)
+
+SELECT lo_truncate(fd, 10000) FROM lotest_stash_values;
+ lo_truncate
+-------------
+ 0
+(1 row)
+
+SELECT loread(fd, 10) FROM lotest_stash_values;
+ loread
+------------------------------------------
+ \000\000\000\000\000\000\000\000\000\000
+(1 row)
+
+SELECT lo_lseek(fd, 0, 2) FROM lotest_stash_values;
+ lo_lseek
+----------
+ 10000
+(1 row)
+
+SELECT lo_tell(fd) FROM lotest_stash_values;
+ lo_tell
+---------
+ 10000
+(1 row)
+
+SELECT lo_truncate(fd, 5000) FROM lotest_stash_values;
+ lo_truncate
+-------------
+ 0
+(1 row)
+
+SELECT lo_lseek(fd, 0, 2) FROM lotest_stash_values;
+ lo_lseek
+----------
+ 5000
+(1 row)
+
+SELECT lo_tell(fd) FROM lotest_stash_values;
+ lo_tell
+---------
+ 5000
+(1 row)
+
+SELECT lo_close(fd) FROM lotest_stash_values;
+ lo_close
+----------
+ 0
+(1 row)
+
+END;
+-- Test 64-bit large object functions.
+BEGIN;
+UPDATE lotest_stash_values SET fd = lo_open(loid, CAST(x'20000' | x'40000' AS integer));
+SELECT lo_lseek64(fd, 4294967296, 0) FROM lotest_stash_values;
+ lo_lseek64
+------------
+ 4294967296
+(1 row)
+
+SELECT lowrite(fd, 'offset:4GB') FROM lotest_stash_values;
+ lowrite
+---------
+ 10
+(1 row)
+
+SELECT lo_tell64(fd) FROM lotest_stash_values;
+ lo_tell64
+------------
+ 4294967306
+(1 row)
+
+SELECT lo_lseek64(fd, -10, 1) FROM lotest_stash_values;
+ lo_lseek64
+------------
+ 4294967296
+(1 row)
+
+SELECT lo_tell64(fd) FROM lotest_stash_values;
+ lo_tell64
+------------
+ 4294967296
+(1 row)
+
+SELECT loread(fd, 10) FROM lotest_stash_values;
+ loread
+------------
+ offset:4GB
+(1 row)
+
+SELECT lo_truncate64(fd, 5000000000) FROM lotest_stash_values;
+ lo_truncate64
+---------------
+ 0
+(1 row)
+
+SELECT lo_lseek64(fd, 0, 2) FROM lotest_stash_values;
+ lo_lseek64
+------------
+ 5000000000
+(1 row)
+
+SELECT lo_tell64(fd) FROM lotest_stash_values;
+ lo_tell64
+------------
+ 5000000000
+(1 row)
+
+SELECT lo_truncate64(fd, 3000000000) FROM lotest_stash_values;
+ lo_truncate64
+---------------
+ 0
+(1 row)
+
+SELECT lo_lseek64(fd, 0, 2) FROM lotest_stash_values;
+ lo_lseek64
+------------
+ 3000000000
+(1 row)
+
+SELECT lo_tell64(fd) FROM lotest_stash_values;
+ lo_tell64
+------------
+ 3000000000
+(1 row)
+
+SELECT lo_close(fd) FROM lotest_stash_values;
+ lo_close
+----------
+ 0
+(1 row)
+
+END;
+-- lo_unlink(lobjId oid) returns integer
+-- return value appears to always be 1
+SELECT lo_unlink(loid) from lotest_stash_values;
+ lo_unlink
+-----------
+ 1
+(1 row)
+
+TRUNCATE lotest_stash_values;
+INSERT INTO lotest_stash_values (loid) SELECT lo_import('@abs_srcdir@/data/tenk.data');
+BEGIN;
+UPDATE lotest_stash_values SET fd=lo_open(loid, CAST(x'20000' | x'40000' AS integer));
+-- verify length of large object
+SELECT lo_lseek(fd, 0, 2) FROM lotest_stash_values;
+ lo_lseek
+----------
+ 680800
+(1 row)
+
+-- with the default BLCKSZ, LOBLKSIZE = 2048, so this positions us for a block
+-- edge case
+SELECT lo_lseek(fd, 2030, 0) FROM lotest_stash_values;
+ lo_lseek
+----------
+ 2030
+(1 row)
+
+-- this should get half of the value from page 0 and half from page 1 of the
+-- large object
+SELECT loread(fd, 36) FROM lotest_stash_values;
+ loread
+--------------------------------------------------------------
+ 44\011144\0111144\0114144\0119144\01188\01189\011SNAAAA\011F
+(1 row)
+
+SELECT lo_tell(fd) FROM lotest_stash_values;
+ lo_tell
+---------
+ 2066
+(1 row)
+
+SELECT lo_lseek(fd, -26, 1) FROM lotest_stash_values;
+ lo_lseek
+----------
+ 2040
+(1 row)
+
+SELECT lowrite(fd, 'abcdefghijklmnop') FROM lotest_stash_values;
+ lowrite
+---------
+ 16
+(1 row)
+
+SELECT lo_lseek(fd, 2030, 0) FROM lotest_stash_values;
+ lo_lseek
+----------
+ 2030
+(1 row)
+
+SELECT loread(fd, 36) FROM lotest_stash_values;
+ loread
+--------------------------------------------------
+ 44\011144\011114abcdefghijklmnop9\011SNAAAA\011F
+(1 row)
+
+SELECT lo_close(fd) FROM lotest_stash_values;
+ lo_close
+----------
+ 0
+(1 row)
+
+END;
+SELECT lo_export(loid, '@abs_builddir@/results/lotest.txt') FROM lotest_stash_values;
+ lo_export
+-----------
+ 1
+(1 row)
+
+\lo_import '@abs_builddir@/results/lotest.txt'
+\set newloid :LASTOID
+-- just make sure \lo_export does not barf
+\lo_export :newloid '@abs_builddir@/results/lotest2.txt'
+-- This is a hack to test that export/import are reversible
+-- This uses knowledge about the inner workings of large object mechanism
+-- which should not be used outside it. This makes it a HACK
+SELECT pageno, data FROM pg_largeobject WHERE loid = (SELECT loid from lotest_stash_values)
+EXCEPT
+SELECT pageno, data FROM pg_largeobject WHERE loid = :newloid;
+ pageno | data
+--------+------
+(0 rows)
+
+SELECT lo_unlink(loid) FROM lotest_stash_values;
+ lo_unlink
+-----------
+ 1
+(1 row)
+
+TRUNCATE lotest_stash_values;
+\lo_unlink :newloid
+\lo_import '@abs_builddir@/results/lotest.txt'
+\set newloid_1 :LASTOID
+SELECT lo_from_bytea(0, lo_get(:newloid_1)) AS newloid_2
+\gset
+SELECT md5(lo_get(:newloid_1)) = md5(lo_get(:newloid_2));
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT lo_get(:newloid_1, 0, 20);
+ lo_get
+-------------------------------------------
+ 8800\0110\0110\0110\0110\0110\0110\011800
+(1 row)
+
+SELECT lo_get(:newloid_1, 10, 20);
+ lo_get
+-------------------------------------------
+ \0110\0110\0110\011800\011800\0113800\011
+(1 row)
+
+SELECT lo_put(:newloid_1, 5, decode('afafafaf', 'hex'));
+ lo_put
+--------
+
+(1 row)
+
+SELECT lo_get(:newloid_1, 0, 20);
+ lo_get
+-------------------------------------------------
+ 8800\011\257\257\257\2570\0110\0110\0110\011800
+(1 row)
+
+SELECT lo_put(:newloid_1, 4294967310, 'foo');
+ lo_put
+--------
+
+(1 row)
+
+SELECT lo_get(:newloid_1);
+ERROR: large object read request is too large
+SELECT lo_get(:newloid_1, 4294967294, 100);
+ lo_get
+---------------------------------------------------------------------
+ \000\000\000\000\000\000\000\000\000\000\000\000\000\000\000\000foo
+(1 row)
+
+\lo_unlink :newloid_1
+\lo_unlink :newloid_2
+-- This object is left in the database for pg_dump test purposes
+SELECT lo_from_bytea(0, E'\\xdeadbeef') AS newloid
+\gset
+SET bytea_output TO hex;
+SELECT lo_get(:newloid);
+ lo_get
+------------
+ \xdeadbeef
+(1 row)
+
+-- Create one more object that we leave behind for testing pg_dump/pg_upgrade;
+-- this one intentionally has an OID in the system range
+SELECT lo_create(2121);
+ lo_create
+-----------
+ 2121
+(1 row)
+
+COMMENT ON LARGE OBJECT 2121 IS 'testing comments';
+-- Clean up
+DROP TABLE lotest_stash_values;
+DROP ROLE regress_lo_user;
diff --git a/src/test/regress/output/misc.source b/src/test/regress/output/misc.source
new file mode 100644
index 0000000..b9595cc
--- /dev/null
+++ b/src/test/regress/output/misc.source
@@ -0,0 +1,692 @@
+--
+-- MISC
+--
+--
+-- BTREE
+--
+UPDATE onek
+ SET unique1 = onek.unique1 + 1;
+UPDATE onek
+ SET unique1 = onek.unique1 - 1;
+--
+-- BTREE partial
+--
+-- UPDATE onek2
+-- SET unique1 = onek2.unique1 + 1;
+--UPDATE onek2
+-- SET unique1 = onek2.unique1 - 1;
+--
+-- BTREE shutting out non-functional updates
+--
+-- the following two tests seem to take a long time on some
+-- systems. This non-func update stuff needs to be examined
+-- more closely. - jolly (2/22/96)
+--
+UPDATE tmp
+ SET stringu1 = reverse_name(onek.stringu1)
+ FROM onek
+ WHERE onek.stringu1 = 'JBAAAA' and
+ onek.stringu1 = tmp.stringu1;
+UPDATE tmp
+ SET stringu1 = reverse_name(onek2.stringu1)
+ FROM onek2
+ WHERE onek2.stringu1 = 'JCAAAA' and
+ onek2.stringu1 = tmp.stringu1;
+DROP TABLE tmp;
+--UPDATE person*
+-- SET age = age + 1;
+--UPDATE person*
+-- SET age = age + 3
+-- WHERE name = 'linda';
+--
+-- copy
+--
+COPY onek TO '@abs_builddir@/results/onek.data';
+DELETE FROM onek;
+COPY onek FROM '@abs_builddir@/results/onek.data';
+SELECT unique1 FROM onek WHERE unique1 < 2 ORDER BY unique1;
+ unique1
+---------
+ 0
+ 1
+(2 rows)
+
+DELETE FROM onek2;
+COPY onek2 FROM '@abs_builddir@/results/onek.data';
+SELECT unique1 FROM onek2 WHERE unique1 < 2 ORDER BY unique1;
+ unique1
+---------
+ 0
+ 1
+(2 rows)
+
+COPY BINARY stud_emp TO '@abs_builddir@/results/stud_emp.data';
+DELETE FROM stud_emp;
+COPY BINARY stud_emp FROM '@abs_builddir@/results/stud_emp.data';
+SELECT * FROM stud_emp;
+ name | age | location | salary | manager | gpa | percent
+-------+-----+------------+--------+---------+-----+---------
+ jeff | 23 | (8,7.7) | 600 | sharon | 3.5 |
+ cim | 30 | (10.5,4.7) | 400 | | 3.4 |
+ linda | 19 | (0.9,6.1) | 100 | | 2.9 |
+(3 rows)
+
+-- COPY aggtest FROM stdin;
+-- 56 7.8
+-- 100 99.097
+-- 0 0.09561
+-- 42 324.78
+-- .
+-- COPY aggtest TO stdout;
+--
+-- inheritance stress test
+--
+SELECT * FROM a_star*;
+ class | a
+-------+----
+ a | 1
+ a | 2
+ a |
+ b | 3
+ b | 4
+ b |
+ b |
+ c | 5
+ c | 6
+ c |
+ c |
+ d | 7
+ d | 8
+ d | 9
+ d | 10
+ d |
+ d | 11
+ d | 12
+ d | 13
+ d |
+ d |
+ d |
+ d | 14
+ d |
+ d |
+ d |
+ d |
+ e | 15
+ e | 16
+ e | 17
+ e |
+ e | 18
+ e |
+ e |
+ f | 19
+ f | 20
+ f | 21
+ f | 22
+ f |
+ f | 24
+ f | 25
+ f | 26
+ f |
+ f |
+ f |
+ f | 27
+ f |
+ f |
+ f |
+ f |
+(50 rows)
+
+SELECT *
+ FROM b_star* x
+ WHERE x.b = text 'bumble' or x.a < 3;
+ class | a | b
+-------+---+--------
+ b | | bumble
+(1 row)
+
+SELECT class, a
+ FROM c_star* x
+ WHERE x.c ~ text 'hi';
+ class | a
+-------+----
+ c | 5
+ c |
+ d | 7
+ d | 8
+ d | 10
+ d |
+ d | 12
+ d |
+ d |
+ d |
+ e | 15
+ e | 16
+ e |
+ e |
+ f | 19
+ f | 20
+ f | 21
+ f |
+ f | 24
+ f |
+ f |
+ f |
+(22 rows)
+
+SELECT class, b, c
+ FROM d_star* x
+ WHERE x.a < 100;
+ class | b | c
+-------+---------+------------
+ d | grumble | hi sunita
+ d | stumble | hi koko
+ d | rumble |
+ d | | hi kristin
+ d | fumble |
+ d | | hi avi
+ d | |
+ d | |
+(8 rows)
+
+SELECT class, c FROM e_star* x WHERE x.c NOTNULL;
+ class | c
+-------+-------------
+ e | hi carol
+ e | hi bob
+ e | hi michelle
+ e | hi elisa
+ f | hi claire
+ f | hi mike
+ f | hi marcel
+ f | hi keith
+ f | hi marc
+ f | hi allison
+ f | hi jeff
+ f | hi carl
+(12 rows)
+
+SELECT * FROM f_star* x WHERE x.c ISNULL;
+ class | a | c | e | f
+-------+----+---+-----+-------------------------------------------
+ f | 22 | | -7 | ((111,555),(222,666),(333,777),(444,888))
+ f | 25 | | -9 |
+ f | 26 | | | ((11111,33333),(22222,44444))
+ f | | | -11 | ((1111111,3333333),(2222222,4444444))
+ f | 27 | | |
+ f | | | -12 |
+ f | | | | ((11111111,33333333),(22222222,44444444))
+ f | | | |
+(8 rows)
+
+-- grouping and aggregation on inherited sets have been busted in the past...
+SELECT sum(a) FROM a_star*;
+ sum
+-----
+ 355
+(1 row)
+
+SELECT class, sum(a) FROM a_star* GROUP BY class ORDER BY class;
+ class | sum
+-------+-----
+ a | 3
+ b | 7
+ c | 11
+ d | 84
+ e | 66
+ f | 184
+(6 rows)
+
+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;
+ class | aa
+-------+----
+ a |
+ b |
+ b |
+ c |
+ c |
+ d |
+ d |
+ d |
+ d |
+ d |
+ d |
+ d |
+ d |
+ e |
+ e |
+ e |
+ f |
+ f |
+ f |
+ f |
+ f |
+ f |
+ f |
+ f |
+(24 rows)
+
+-- 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;
+ class | foo
+-------+-----
+ a | 2
+ b | 3
+ b | 4
+ c | 5
+ c | 6
+ d | 7
+ d | 8
+ d | 9
+ d | 10
+ d | 11
+ d | 12
+ d | 13
+ d | 14
+ e | 15
+ e | 16
+ e | 17
+ e | 18
+ f | 19
+ f | 20
+ f | 21
+ f | 22
+ f | 24
+ f | 25
+ f | 26
+ f | 27
+(25 rows)
+
+ALTER TABLE a_star RENAME COLUMN foo TO aa;
+SELECT *
+ from a_star*
+ WHERE aa < 1000;
+ class | aa
+-------+----
+ a | 1
+ a | 2
+ b | 3
+ b | 4
+ c | 5
+ c | 6
+ d | 7
+ d | 8
+ d | 9
+ d | 10
+ d | 11
+ d | 12
+ d | 13
+ d | 14
+ e | 15
+ e | 16
+ e | 17
+ e | 18
+ f | 19
+ f | 20
+ f | 21
+ f | 22
+ f | 24
+ f | 25
+ f | 26
+ f | 27
+(26 rows)
+
+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*;
+ class | aa | cc | ee | e
+-------+----+-------------+-----+---
+ e | 15 | hi carol | -1 |
+ e | 16 | hi bob | |
+ e | 17 | | -2 |
+ e | | hi michelle | -3 |
+ e | 18 | | |
+ e | | hi elisa | |
+ e | | | -4 |
+ f | 19 | hi claire | -5 |
+ f | 20 | hi mike | -6 |
+ f | 21 | hi marcel | |
+ f | 22 | | -7 |
+ f | | hi keith | -8 |
+ f | 24 | hi marc | |
+ f | 25 | | -9 |
+ f | 26 | | |
+ f | | hi allison | -10 |
+ f | | hi jeff | |
+ f | | | -11 |
+ f | 27 | | |
+ f | | hi carl | |
+ f | | | -12 |
+ f | | | |
+ f | | | |
+(23 rows)
+
+ALTER TABLE a_star* ADD COLUMN a text;
+NOTICE: merging definition of column "a" for child "d_star"
+-- 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;
+ relname | has_toast_table
+---------+-----------------
+ a_star | t
+ c_star | t
+(2 rows)
+
+--UPDATE b_star*
+-- SET a = text 'gazpacho'
+-- WHERE aa > 4;
+SELECT class, aa, a FROM a_star*;
+ class | aa | a
+-------+----+---
+ a | 1 |
+ a | 2 |
+ a | |
+ b | 3 |
+ b | 4 |
+ b | |
+ b | |
+ c | 5 |
+ c | 6 |
+ c | |
+ c | |
+ d | 7 |
+ d | 8 |
+ d | 9 |
+ d | 10 |
+ d | |
+ d | 11 |
+ d | 12 |
+ d | 13 |
+ d | |
+ d | |
+ d | |
+ d | 14 |
+ d | |
+ d | |
+ d | |
+ d | |
+ e | 15 |
+ e | 16 |
+ e | 17 |
+ e | |
+ e | 18 |
+ e | |
+ e | |
+ f | 19 |
+ f | 20 |
+ f | 21 |
+ f | 22 |
+ f | |
+ f | 24 |
+ f | 25 |
+ f | 26 |
+ f | |
+ f | |
+ f | |
+ f | 27 |
+ f | |
+ f | |
+ f | |
+ f | |
+(50 rows)
+
+--
+-- versions
+--
+--
+-- postquel functions
+--
+--
+-- mike does post_hacking,
+-- joe and sally play basketball, and
+-- everyone else does nothing.
+--
+SELECT p.name, name(p.hobbies) FROM ONLY person p;
+ name | name
+-------+-------------
+ mike | posthacking
+ joe | basketball
+ sally | basketball
+(3 rows)
+
+--
+-- as above, but jeff also does post_hacking.
+--
+SELECT p.name, name(p.hobbies) FROM person* p;
+ name | name
+-------+-------------
+ mike | posthacking
+ joe | basketball
+ sally | basketball
+ jeff | posthacking
+(4 rows)
+
+--
+-- the next two queries demonstrate how functions generate bogus duplicates.
+-- this is a "feature" ..
+--
+SELECT DISTINCT hobbies_r.name, name(hobbies_r.equipment) FROM hobbies_r
+ ORDER BY 1,2;
+ name | name
+-------------+---------------
+ basketball | hightops
+ posthacking | advil
+ posthacking | peet's coffee
+ skywalking | guts
+(4 rows)
+
+SELECT hobbies_r.name, (hobbies_r.equipment).name FROM hobbies_r;
+ name | name
+-------------+---------------
+ posthacking | advil
+ posthacking | peet's coffee
+ posthacking | advil
+ posthacking | peet's coffee
+ basketball | hightops
+ basketball | hightops
+ skywalking | guts
+(7 rows)
+
+--
+-- mike needs advil and peet's coffee,
+-- joe and sally need hightops, and
+-- everyone else is fine.
+--
+SELECT p.name, name(p.hobbies), name(equipment(p.hobbies)) FROM ONLY person p;
+ name | name | name
+-------+-------------+---------------
+ mike | posthacking | advil
+ mike | posthacking | peet's coffee
+ joe | basketball | hightops
+ sally | basketball | hightops
+(4 rows)
+
+--
+-- as above, but jeff needs advil and peet's coffee as well.
+--
+SELECT p.name, name(p.hobbies), name(equipment(p.hobbies)) FROM person* p;
+ name | name | name
+-------+-------------+---------------
+ mike | posthacking | advil
+ mike | posthacking | peet's coffee
+ joe | basketball | hightops
+ sally | basketball | hightops
+ jeff | posthacking | advil
+ jeff | posthacking | peet's coffee
+(6 rows)
+
+--
+-- just like the last two, but make sure that the target list fixup and
+-- unflattening is being done correctly.
+--
+SELECT name(equipment(p.hobbies)), p.name, name(p.hobbies) FROM ONLY person p;
+ name | name | name
+---------------+-------+-------------
+ advil | mike | posthacking
+ peet's coffee | mike | posthacking
+ hightops | joe | basketball
+ hightops | sally | basketball
+(4 rows)
+
+SELECT (p.hobbies).equipment.name, p.name, name(p.hobbies) FROM person* p;
+ name | name | name
+---------------+-------+-------------
+ advil | mike | posthacking
+ peet's coffee | mike | posthacking
+ hightops | joe | basketball
+ hightops | sally | basketball
+ advil | jeff | posthacking
+ peet's coffee | jeff | posthacking
+(6 rows)
+
+SELECT (p.hobbies).equipment.name, name(p.hobbies), p.name FROM ONLY person p;
+ name | name | name
+---------------+-------------+-------
+ advil | posthacking | mike
+ peet's coffee | posthacking | mike
+ hightops | basketball | joe
+ hightops | basketball | sally
+(4 rows)
+
+SELECT name(equipment(p.hobbies)), name(p.hobbies), p.name FROM person* p;
+ name | name | name
+---------------+-------------+-------
+ advil | posthacking | mike
+ peet's coffee | posthacking | mike
+ hightops | basketball | joe
+ hightops | basketball | sally
+ advil | posthacking | jeff
+ peet's coffee | posthacking | jeff
+(6 rows)
+
+SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer')));
+ name
+------
+ guts
+(1 row)
+
+SELECT name(equipment(hobby_construct_named(text 'skywalking', text 'mer')));
+ name
+------
+ guts
+(1 row)
+
+SELECT name(equipment_named(hobby_construct_named(text 'skywalking', text 'mer')));
+ name
+------
+ guts
+(1 row)
+
+SELECT name(equipment_named_ambiguous_1a(hobby_construct_named(text 'skywalking', text 'mer')));
+ name
+------
+ guts
+(1 row)
+
+SELECT name(equipment_named_ambiguous_1b(hobby_construct_named(text 'skywalking', text 'mer')));
+ name
+------
+ guts
+(1 row)
+
+SELECT name(equipment_named_ambiguous_1c(hobby_construct_named(text 'skywalking', text 'mer')));
+ name
+------
+ guts
+(1 row)
+
+SELECT name(equipment_named_ambiguous_2a(text 'skywalking'));
+ name
+------
+ guts
+(1 row)
+
+SELECT name(equipment_named_ambiguous_2b(text 'skywalking'));
+ name
+---------------
+ advil
+ peet's coffee
+ hightops
+ guts
+(4 rows)
+
+SELECT hobbies_by_name('basketball');
+ hobbies_by_name
+-----------------
+ joe
+(1 row)
+
+SELECT name, overpaid(emp.*) FROM emp;
+ name | overpaid
+--------+----------
+ sharon | t
+ sam | t
+ bill | t
+ jeff | f
+ cim | f
+ linda | f
+(6 rows)
+
+--
+-- Try a few cases with SQL-spec row constructor expressions
+--
+SELECT * FROM equipment(ROW('skywalking', 'mer'));
+ name | hobby
+------+------------
+ guts | skywalking
+(1 row)
+
+SELECT name(equipment(ROW('skywalking', 'mer')));
+ name
+------
+ guts
+(1 row)
+
+SELECT *, name(equipment(h.*)) FROM hobbies_r h;
+ name | person | name
+-------------+--------+---------------
+ posthacking | mike | advil
+ posthacking | mike | peet's coffee
+ posthacking | jeff | advil
+ posthacking | jeff | peet's coffee
+ basketball | joe | hightops
+ basketball | sally | hightops
+ skywalking | | guts
+(7 rows)
+
+SELECT *, (equipment(CAST((h.*) AS hobbies_r))).name FROM hobbies_r h;
+ name | person | name
+-------------+--------+---------------
+ posthacking | mike | advil
+ posthacking | mike | peet's coffee
+ posthacking | jeff | advil
+ posthacking | jeff | peet's coffee
+ basketball | joe | hightops
+ basketball | sally | hightops
+ skywalking | | guts
+(7 rows)
+
+--
+-- functional joins
+--
+--
+-- instance rules
+--
+--
+-- rewrite rules
+--
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
new file mode 100644
index 0000000..94c5f02
--- /dev/null
+++ b/src/test/regress/output/tablespace.source
@@ -0,0 +1,753 @@
+-- create a tablespace using WITH clause
+CREATE TABLESPACE regress_tblspacewith LOCATION '@testtablespace@' WITH (some_nonexistent_parameter = true); -- fail
+ERROR: unrecognized parameter "some_nonexistent_parameter"
+CREATE TABLESPACE regress_tblspacewith LOCATION '@testtablespace@' WITH (random_page_cost = 3.0); -- ok
+-- check to see the parameter was used
+SELECT spcoptions FROM pg_tablespace WHERE spcname = 'regress_tblspacewith';
+ spcoptions
+------------------------
+ {random_page_cost=3.0}
+(1 row)
+
+-- drop the tablespace so we can re-use the location
+DROP TABLESPACE regress_tblspacewith;
+-- create a tablespace we can use
+CREATE TABLESPACE regress_tblspace LOCATION '@testtablespace@';
+-- try setting and resetting some properties for the new tablespace
+ALTER TABLESPACE regress_tblspace SET (random_page_cost = 1.0, seq_page_cost = 1.1);
+ALTER TABLESPACE regress_tblspace SET (some_nonexistent_parameter = true); -- fail
+ERROR: unrecognized parameter "some_nonexistent_parameter"
+ALTER TABLESPACE regress_tblspace RESET (random_page_cost = 2.0); -- fail
+ERROR: RESET must not include values for parameters
+ALTER TABLESPACE regress_tblspace RESET (random_page_cost, effective_io_concurrency); -- ok
+-- create a schema we can use
+CREATE SCHEMA testschema;
+-- try a table
+CREATE TABLE testschema.foo (i int) TABLESPACE regress_tblspace;
+SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
+ where c.reltablespace = t.oid AND c.relname = 'foo';
+ relname | spcname
+---------+------------------
+ foo | regress_tblspace
+(1 row)
+
+INSERT INTO testschema.foo VALUES(1);
+INSERT INTO testschema.foo VALUES(2);
+-- tables from dynamic sources
+CREATE TABLE testschema.asselect TABLESPACE regress_tblspace AS SELECT 1;
+SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
+ where c.reltablespace = t.oid AND c.relname = 'asselect';
+ relname | spcname
+----------+------------------
+ asselect | regress_tblspace
+(1 row)
+
+PREPARE selectsource(int) AS SELECT $1;
+CREATE TABLE testschema.asexecute TABLESPACE regress_tblspace
+ AS EXECUTE selectsource(2);
+SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
+ where c.reltablespace = t.oid AND c.relname = 'asexecute';
+ relname | spcname
+-----------+------------------
+ asexecute | regress_tblspace
+(1 row)
+
+-- index
+CREATE INDEX foo_idx on testschema.foo(i) TABLESPACE regress_tblspace;
+SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
+ where c.reltablespace = t.oid AND c.relname = 'foo_idx';
+ relname | spcname
+---------+------------------
+ foo_idx | regress_tblspace
+(1 row)
+
+-- check \d output
+\d testschema.foo
+ Table "testschema.foo"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ i | integer | | |
+Indexes:
+ "foo_idx" btree (i), tablespace "regress_tblspace"
+Tablespace: "regress_tblspace"
+
+\d testschema.foo_idx
+ Index "testschema.foo_idx"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ i | integer | yes | i
+btree, for table "testschema.foo"
+Tablespace: "regress_tblspace"
+
+--
+-- partitioned table
+--
+CREATE TABLE testschema.part (a int) PARTITION BY LIST (a);
+SET default_tablespace TO pg_global;
+CREATE TABLE testschema.part_1 PARTITION OF testschema.part FOR VALUES IN (1);
+ERROR: only shared relations can be placed in pg_global tablespace
+RESET default_tablespace;
+CREATE TABLE testschema.part_1 PARTITION OF testschema.part FOR VALUES IN (1);
+SET default_tablespace TO regress_tblspace;
+CREATE TABLE testschema.part_2 PARTITION OF testschema.part FOR VALUES IN (2);
+SET default_tablespace TO pg_global;
+CREATE TABLE testschema.part_3 PARTITION OF testschema.part FOR VALUES IN (3);
+ERROR: only shared relations can be placed in pg_global tablespace
+ALTER TABLE testschema.part SET TABLESPACE regress_tblspace;
+CREATE TABLE testschema.part_3 PARTITION OF testschema.part FOR VALUES IN (3);
+CREATE TABLE testschema.part_4 PARTITION OF testschema.part FOR VALUES IN (4)
+ TABLESPACE pg_default;
+CREATE TABLE testschema.part_56 PARTITION OF testschema.part FOR VALUES IN (5, 6)
+ PARTITION BY LIST (a);
+ALTER TABLE testschema.part SET TABLESPACE pg_default;
+CREATE TABLE testschema.part_78 PARTITION OF testschema.part FOR VALUES IN (7, 8)
+ PARTITION BY LIST (a);
+ERROR: only shared relations can be placed in pg_global tablespace
+CREATE TABLE testschema.part_910 PARTITION OF testschema.part FOR VALUES IN (9, 10)
+ PARTITION BY LIST (a) TABLESPACE regress_tblspace;
+RESET default_tablespace;
+CREATE TABLE testschema.part_78 PARTITION OF testschema.part FOR VALUES IN (7, 8)
+ PARTITION BY LIST (a);
+SELECT relname, spcname FROM pg_catalog.pg_class c
+ JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid)
+ LEFT JOIN pg_catalog.pg_tablespace t ON c.reltablespace = t.oid
+ where c.relname LIKE 'part%' AND n.nspname = 'testschema' order by relname;
+ relname | spcname
+----------+------------------
+ part |
+ part_1 |
+ part_2 | regress_tblspace
+ part_3 | regress_tblspace
+ part_4 |
+ part_56 | regress_tblspace
+ part_78 |
+ part_910 | regress_tblspace
+(8 rows)
+
+RESET default_tablespace;
+DROP TABLE testschema.part;
+-- partitioned index
+CREATE TABLE testschema.part (a int) PARTITION BY LIST (a);
+CREATE TABLE testschema.part1 PARTITION OF testschema.part FOR VALUES IN (1);
+CREATE INDEX part_a_idx ON testschema.part (a) TABLESPACE regress_tblspace;
+CREATE TABLE testschema.part2 PARTITION OF testschema.part FOR VALUES IN (2);
+SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
+ where c.reltablespace = t.oid AND c.relname LIKE 'part%_idx';
+ relname | spcname
+-------------+------------------
+ part1_a_idx | regress_tblspace
+ part2_a_idx | regress_tblspace
+ part_a_idx | regress_tblspace
+(3 rows)
+
+\d testschema.part
+ Partitioned table "testschema.part"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+Partition key: LIST (a)
+Indexes:
+ "part_a_idx" btree (a), tablespace "regress_tblspace"
+Number of partitions: 2 (Use \d+ to list them.)
+
+\d+ testschema.part
+ Partitioned table "testschema.part"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | integer | | | | plain | |
+Partition key: LIST (a)
+Indexes:
+ "part_a_idx" btree (a), tablespace "regress_tblspace"
+Partitions: testschema.part1 FOR VALUES IN (1),
+ testschema.part2 FOR VALUES IN (2)
+
+\d testschema.part1
+ Table "testschema.part1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+Partition of: testschema.part FOR VALUES IN (1)
+Indexes:
+ "part1_a_idx" btree (a), tablespace "regress_tblspace"
+
+\d+ testschema.part1
+ Table "testschema.part1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | integer | | | | plain | |
+Partition of: testschema.part FOR VALUES IN (1)
+Partition constraint: ((a IS NOT NULL) AND (a = 1))
+Indexes:
+ "part1_a_idx" btree (a), tablespace "regress_tblspace"
+
+\d testschema.part_a_idx
+Partitioned index "testschema.part_a_idx"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ a | integer | yes | a
+btree, for table "testschema.part"
+Number of partitions: 2 (Use \d+ to list them.)
+Tablespace: "regress_tblspace"
+
+\d+ testschema.part_a_idx
+ Partitioned index "testschema.part_a_idx"
+ Column | Type | Key? | Definition | Storage | Stats target
+--------+---------+------+------------+---------+--------------
+ a | integer | yes | a | plain |
+btree, for table "testschema.part"
+Partitions: testschema.part1_a_idx,
+ testschema.part2_a_idx
+Tablespace: "regress_tblspace"
+
+-- partitioned rels cannot specify the default tablespace. These fail:
+CREATE TABLE testschema.dflt (a int PRIMARY KEY) PARTITION BY LIST (a) TABLESPACE pg_default;
+ERROR: cannot specify default tablespace for partitioned relations
+CREATE TABLE testschema.dflt (a int PRIMARY KEY USING INDEX TABLESPACE pg_default) PARTITION BY LIST (a);
+ERROR: cannot specify default tablespace for partitioned relations
+SET default_tablespace TO 'pg_default';
+CREATE TABLE testschema.dflt (a int PRIMARY KEY) PARTITION BY LIST (a) TABLESPACE regress_tblspace;
+ERROR: cannot specify default tablespace for partitioned relations
+CREATE TABLE testschema.dflt (a int PRIMARY KEY USING INDEX TABLESPACE regress_tblspace) PARTITION BY LIST (a);
+ERROR: cannot specify default tablespace for partitioned relations
+-- but these work:
+CREATE TABLE testschema.dflt (a int PRIMARY KEY USING INDEX TABLESPACE regress_tblspace) PARTITION BY LIST (a) TABLESPACE regress_tblspace;
+SET default_tablespace TO '';
+CREATE TABLE testschema.dflt2 (a int PRIMARY KEY) PARTITION BY LIST (a);
+DROP TABLE testschema.dflt, testschema.dflt2;
+-- check that default_tablespace doesn't affect ALTER TABLE index rebuilds
+CREATE TABLE testschema.test_default_tab(id bigint) TABLESPACE regress_tblspace;
+INSERT INTO testschema.test_default_tab VALUES (1);
+CREATE INDEX test_index1 on testschema.test_default_tab (id);
+CREATE INDEX test_index2 on testschema.test_default_tab (id) TABLESPACE regress_tblspace;
+ALTER TABLE testschema.test_default_tab ADD CONSTRAINT test_index3 PRIMARY KEY (id);
+ALTER TABLE testschema.test_default_tab ADD CONSTRAINT test_index4 UNIQUE (id) USING INDEX TABLESPACE regress_tblspace;
+\d testschema.test_index1
+ Index "testschema.test_index1"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+btree, for table "testschema.test_default_tab"
+
+\d testschema.test_index2
+ Index "testschema.test_index2"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+btree, for table "testschema.test_default_tab"
+Tablespace: "regress_tblspace"
+
+\d testschema.test_index3
+ Index "testschema.test_index3"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+primary key, btree, for table "testschema.test_default_tab"
+
+\d testschema.test_index4
+ Index "testschema.test_index4"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+unique, btree, for table "testschema.test_default_tab"
+Tablespace: "regress_tblspace"
+
+-- use a custom tablespace for default_tablespace
+SET default_tablespace TO regress_tblspace;
+-- tablespace should not change if no rewrite
+ALTER TABLE testschema.test_default_tab ALTER id TYPE bigint;
+\d testschema.test_index1
+ Index "testschema.test_index1"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+btree, for table "testschema.test_default_tab"
+
+\d testschema.test_index2
+ Index "testschema.test_index2"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+btree, for table "testschema.test_default_tab"
+Tablespace: "regress_tblspace"
+
+\d testschema.test_index3
+ Index "testschema.test_index3"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+primary key, btree, for table "testschema.test_default_tab"
+
+\d testschema.test_index4
+ Index "testschema.test_index4"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+unique, btree, for table "testschema.test_default_tab"
+Tablespace: "regress_tblspace"
+
+SELECT * FROM testschema.test_default_tab;
+ id
+----
+ 1
+(1 row)
+
+-- tablespace should not change even if there is an index rewrite
+ALTER TABLE testschema.test_default_tab ALTER id TYPE int;
+\d testschema.test_index1
+ Index "testschema.test_index1"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ id | integer | yes | id
+btree, for table "testschema.test_default_tab"
+
+\d testschema.test_index2
+ Index "testschema.test_index2"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ id | integer | yes | id
+btree, for table "testschema.test_default_tab"
+Tablespace: "regress_tblspace"
+
+\d testschema.test_index3
+ Index "testschema.test_index3"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ id | integer | yes | id
+primary key, btree, for table "testschema.test_default_tab"
+
+\d testschema.test_index4
+ Index "testschema.test_index4"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ id | integer | yes | id
+unique, btree, for table "testschema.test_default_tab"
+Tablespace: "regress_tblspace"
+
+SELECT * FROM testschema.test_default_tab;
+ id
+----
+ 1
+(1 row)
+
+-- now use the default tablespace for default_tablespace
+SET default_tablespace TO '';
+-- tablespace should not change if no rewrite
+ALTER TABLE testschema.test_default_tab ALTER id TYPE int;
+\d testschema.test_index1
+ Index "testschema.test_index1"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ id | integer | yes | id
+btree, for table "testschema.test_default_tab"
+
+\d testschema.test_index2
+ Index "testschema.test_index2"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ id | integer | yes | id
+btree, for table "testschema.test_default_tab"
+Tablespace: "regress_tblspace"
+
+\d testschema.test_index3
+ Index "testschema.test_index3"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ id | integer | yes | id
+primary key, btree, for table "testschema.test_default_tab"
+
+\d testschema.test_index4
+ Index "testschema.test_index4"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ id | integer | yes | id
+unique, btree, for table "testschema.test_default_tab"
+Tablespace: "regress_tblspace"
+
+-- tablespace should not change even if there is an index rewrite
+ALTER TABLE testschema.test_default_tab ALTER id TYPE bigint;
+\d testschema.test_index1
+ Index "testschema.test_index1"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+btree, for table "testschema.test_default_tab"
+
+\d testschema.test_index2
+ Index "testschema.test_index2"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+btree, for table "testschema.test_default_tab"
+Tablespace: "regress_tblspace"
+
+\d testschema.test_index3
+ Index "testschema.test_index3"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+primary key, btree, for table "testschema.test_default_tab"
+
+\d testschema.test_index4
+ Index "testschema.test_index4"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+unique, btree, for table "testschema.test_default_tab"
+Tablespace: "regress_tblspace"
+
+DROP TABLE testschema.test_default_tab;
+-- check that default_tablespace doesn't affect ALTER TABLE index rebuilds
+-- (this time with a partitioned table)
+CREATE TABLE testschema.test_default_tab_p(id bigint, val bigint)
+ PARTITION BY LIST (id) TABLESPACE regress_tblspace;
+CREATE TABLE testschema.test_default_tab_p1 PARTITION OF testschema.test_default_tab_p
+ FOR VALUES IN (1);
+INSERT INTO testschema.test_default_tab_p VALUES (1);
+CREATE INDEX test_index1 on testschema.test_default_tab_p (val);
+CREATE INDEX test_index2 on testschema.test_default_tab_p (val) TABLESPACE regress_tblspace;
+ALTER TABLE testschema.test_default_tab_p ADD CONSTRAINT test_index3 PRIMARY KEY (id);
+ALTER TABLE testschema.test_default_tab_p ADD CONSTRAINT test_index4 UNIQUE (id) USING INDEX TABLESPACE regress_tblspace;
+\d testschema.test_index1
+Partitioned index "testschema.test_index1"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ val | bigint | yes | val
+btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
+
+\d testschema.test_index2
+Partitioned index "testschema.test_index2"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ val | bigint | yes | val
+btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
+Tablespace: "regress_tblspace"
+
+\d testschema.test_index3
+Partitioned index "testschema.test_index3"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+primary key, btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
+
+\d testschema.test_index4
+Partitioned index "testschema.test_index4"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+unique, btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
+Tablespace: "regress_tblspace"
+
+-- use a custom tablespace for default_tablespace
+SET default_tablespace TO regress_tblspace;
+-- tablespace should not change if no rewrite
+ALTER TABLE testschema.test_default_tab_p ALTER val TYPE bigint;
+\d testschema.test_index1
+Partitioned index "testschema.test_index1"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ val | bigint | yes | val
+btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
+
+\d testschema.test_index2
+Partitioned index "testschema.test_index2"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ val | bigint | yes | val
+btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
+Tablespace: "regress_tblspace"
+
+\d testschema.test_index3
+Partitioned index "testschema.test_index3"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+primary key, btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
+
+\d testschema.test_index4
+Partitioned index "testschema.test_index4"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+unique, btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
+Tablespace: "regress_tblspace"
+
+SELECT * FROM testschema.test_default_tab_p;
+ id | val
+----+-----
+ 1 |
+(1 row)
+
+-- tablespace should not change even if there is an index rewrite
+ALTER TABLE testschema.test_default_tab_p ALTER val TYPE int;
+\d testschema.test_index1
+Partitioned index "testschema.test_index1"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ val | integer | yes | val
+btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
+
+\d testschema.test_index2
+Partitioned index "testschema.test_index2"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ val | integer | yes | val
+btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
+Tablespace: "regress_tblspace"
+
+\d testschema.test_index3
+Partitioned index "testschema.test_index3"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+primary key, btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
+
+\d testschema.test_index4
+Partitioned index "testschema.test_index4"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+unique, btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
+Tablespace: "regress_tblspace"
+
+SELECT * FROM testschema.test_default_tab_p;
+ id | val
+----+-----
+ 1 |
+(1 row)
+
+-- now use the default tablespace for default_tablespace
+SET default_tablespace TO '';
+-- tablespace should not change if no rewrite
+ALTER TABLE testschema.test_default_tab_p ALTER val TYPE int;
+\d testschema.test_index1
+Partitioned index "testschema.test_index1"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ val | integer | yes | val
+btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
+
+\d testschema.test_index2
+Partitioned index "testschema.test_index2"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ val | integer | yes | val
+btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
+Tablespace: "regress_tblspace"
+
+\d testschema.test_index3
+Partitioned index "testschema.test_index3"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+primary key, btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
+
+\d testschema.test_index4
+Partitioned index "testschema.test_index4"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+unique, btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
+Tablespace: "regress_tblspace"
+
+-- tablespace should not change even if there is an index rewrite
+ALTER TABLE testschema.test_default_tab_p ALTER val TYPE bigint;
+\d testschema.test_index1
+Partitioned index "testschema.test_index1"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ val | bigint | yes | val
+btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
+
+\d testschema.test_index2
+Partitioned index "testschema.test_index2"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ val | bigint | yes | val
+btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
+Tablespace: "regress_tblspace"
+
+\d testschema.test_index3
+Partitioned index "testschema.test_index3"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+primary key, btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
+
+\d testschema.test_index4
+Partitioned index "testschema.test_index4"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+unique, btree, for table "testschema.test_default_tab_p"
+Number of partitions: 1 (Use \d+ to list them.)
+Tablespace: "regress_tblspace"
+
+DROP TABLE testschema.test_default_tab_p;
+-- check that default_tablespace affects index additions in ALTER TABLE
+CREATE TABLE testschema.test_tab(id int) TABLESPACE regress_tblspace;
+INSERT INTO testschema.test_tab VALUES (1);
+SET default_tablespace TO regress_tblspace;
+ALTER TABLE testschema.test_tab ADD CONSTRAINT test_tab_unique UNIQUE (id);
+SET default_tablespace TO '';
+ALTER TABLE testschema.test_tab ADD CONSTRAINT test_tab_pkey PRIMARY KEY (id);
+\d testschema.test_tab_unique
+ Index "testschema.test_tab_unique"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ id | integer | yes | id
+unique, btree, for table "testschema.test_tab"
+Tablespace: "regress_tblspace"
+
+\d testschema.test_tab_pkey
+ Index "testschema.test_tab_pkey"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ id | integer | yes | id
+primary key, btree, for table "testschema.test_tab"
+
+SELECT * FROM testschema.test_tab;
+ id
+----
+ 1
+(1 row)
+
+DROP TABLE testschema.test_tab;
+-- check that default_tablespace is handled correctly by multi-command
+-- ALTER TABLE that includes a tablespace-preserving rewrite
+CREATE TABLE testschema.test_tab(a int, b int, c int);
+SET default_tablespace TO regress_tblspace;
+ALTER TABLE testschema.test_tab ADD CONSTRAINT test_tab_unique UNIQUE (a);
+CREATE INDEX test_tab_a_idx ON testschema.test_tab (a);
+SET default_tablespace TO '';
+CREATE INDEX test_tab_b_idx ON testschema.test_tab (b);
+\d testschema.test_tab_unique
+ Index "testschema.test_tab_unique"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ a | integer | yes | a
+unique, btree, for table "testschema.test_tab"
+Tablespace: "regress_tblspace"
+
+\d testschema.test_tab_a_idx
+ Index "testschema.test_tab_a_idx"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ a | integer | yes | a
+btree, for table "testschema.test_tab"
+Tablespace: "regress_tblspace"
+
+\d testschema.test_tab_b_idx
+ Index "testschema.test_tab_b_idx"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ b | integer | yes | b
+btree, for table "testschema.test_tab"
+
+ALTER TABLE testschema.test_tab ALTER b TYPE bigint, ADD UNIQUE (c);
+\d testschema.test_tab_unique
+ Index "testschema.test_tab_unique"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ a | integer | yes | a
+unique, btree, for table "testschema.test_tab"
+Tablespace: "regress_tblspace"
+
+\d testschema.test_tab_a_idx
+ Index "testschema.test_tab_a_idx"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ a | integer | yes | a
+btree, for table "testschema.test_tab"
+Tablespace: "regress_tblspace"
+
+\d testschema.test_tab_b_idx
+ Index "testschema.test_tab_b_idx"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ b | bigint | yes | b
+btree, for table "testschema.test_tab"
+
+DROP TABLE testschema.test_tab;
+-- let's try moving a table from one place to another
+CREATE TABLE testschema.atable AS VALUES (1), (2);
+CREATE UNIQUE INDEX anindex ON testschema.atable(column1);
+ALTER TABLE testschema.atable SET TABLESPACE regress_tblspace;
+ALTER INDEX testschema.anindex SET TABLESPACE regress_tblspace;
+ALTER INDEX testschema.part_a_idx SET TABLESPACE pg_global;
+ERROR: only shared relations can be placed in pg_global tablespace
+ALTER INDEX testschema.part_a_idx SET TABLESPACE pg_default;
+ALTER INDEX testschema.part_a_idx SET TABLESPACE regress_tblspace;
+INSERT INTO testschema.atable VALUES(3); -- ok
+INSERT INTO testschema.atable VALUES(1); -- fail (checks index)
+ERROR: duplicate key value violates unique constraint "anindex"
+DETAIL: Key (column1)=(1) already exists.
+SELECT COUNT(*) FROM testschema.atable; -- checks heap
+ count
+-------
+ 3
+(1 row)
+
+-- Will fail with bad path
+CREATE TABLESPACE regress_badspace LOCATION '/no/such/location';
+ERROR: directory "/no/such/location" does not exist
+-- No such tablespace
+CREATE TABLE bar (i int) TABLESPACE regress_nosuchspace;
+ERROR: tablespace "regress_nosuchspace" does not exist
+-- Fail, in use for some partitioned object
+DROP TABLESPACE regress_tblspace;
+ERROR: tablespace "regress_tblspace" cannot be dropped because some objects depend on it
+DETAIL: tablespace for index testschema.part_a_idx
+ALTER INDEX testschema.part_a_idx SET TABLESPACE pg_default;
+-- Fail, not empty
+DROP TABLESPACE regress_tblspace;
+ERROR: tablespace "regress_tblspace" is not empty
+CREATE ROLE regress_tablespace_user1 login;
+CREATE ROLE regress_tablespace_user2 login;
+GRANT USAGE ON SCHEMA testschema TO regress_tablespace_user2;
+ALTER TABLESPACE regress_tblspace OWNER TO regress_tablespace_user1;
+CREATE TABLE testschema.tablespace_acl (c int);
+-- new owner lacks permission to create this index from scratch
+CREATE INDEX k ON testschema.tablespace_acl (c) TABLESPACE regress_tblspace;
+ALTER TABLE testschema.tablespace_acl OWNER TO regress_tablespace_user2;
+SET SESSION ROLE regress_tablespace_user2;
+CREATE TABLE tablespace_table (i int) TABLESPACE regress_tblspace; -- fail
+ERROR: permission denied for tablespace regress_tblspace
+ALTER TABLE testschema.tablespace_acl ALTER c TYPE bigint;
+RESET ROLE;
+ALTER TABLESPACE regress_tblspace RENAME TO regress_tblspace_renamed;
+ALTER TABLE ALL IN TABLESPACE regress_tblspace_renamed SET TABLESPACE pg_default;
+ALTER INDEX ALL IN TABLESPACE regress_tblspace_renamed SET TABLESPACE pg_default;
+-- Should show notice that nothing was done
+ALTER TABLE ALL IN TABLESPACE regress_tblspace_renamed SET TABLESPACE pg_default;
+NOTICE: no matching relations in tablespace "regress_tblspace_renamed" found
+-- Should succeed
+DROP TABLESPACE regress_tblspace_renamed;
+DROP SCHEMA testschema CASCADE;
+NOTICE: drop cascades to 6 other objects
+DETAIL: drop cascades to table testschema.foo
+drop cascades to table testschema.asselect
+drop cascades to table testschema.asexecute
+drop cascades to table testschema.part
+drop cascades to table testschema.atable
+drop cascades to table testschema.tablespace_acl
+DROP ROLE regress_tablespace_user1;
+DROP ROLE regress_tablespace_user2;
generated by cgit v1.2.3 (git 2.39.1) at 2025-01-28 22:56:31 +0000