diff options
Diffstat (limited to 'src/test')
48 files changed, 427 insertions, 1250 deletions
diff --git a/src/test/modules/test_ddl_deparse/expected/create_table.out b/src/test/modules/test_ddl_deparse/expected/create_table.out index d27a7752570..2d7dfd533e4 100644 --- a/src/test/modules/test_ddl_deparse/expected/create_table.out +++ b/src/test/modules/test_ddl_deparse/expected/create_table.out @@ -98,7 +98,7 @@ NOTICE: DDL test: type simple, tag CREATE INDEX CREATE TABLE emp ( salary int4, manager name -) INHERITS (person) WITH OIDS; +) INHERITS (person); NOTICE: DDL test: type simple, tag CREATE TABLE CREATE TABLE student ( gpa float8 diff --git a/src/test/modules/test_ddl_deparse/sql/create_table.sql b/src/test/modules/test_ddl_deparse/sql/create_table.sql index 5e784527297..dd3a908638d 100644 --- a/src/test/modules/test_ddl_deparse/sql/create_table.sql +++ b/src/test/modules/test_ddl_deparse/sql/create_table.sql @@ -86,7 +86,7 @@ CREATE TABLE person ( CREATE TABLE emp ( salary int4, manager name -) INHERITS (person) WITH OIDS; +) INHERITS (person); CREATE TABLE student ( diff --git a/src/test/modules/test_ddl_deparse/test_ddl_deparse.c b/src/test/modules/test_ddl_deparse/test_ddl_deparse.c index 82a51eb3039..0f73db499ec 100644 --- a/src/test/modules/test_ddl_deparse/test_ddl_deparse.c +++ b/src/test/modules/test_ddl_deparse/test_ddl_deparse.c @@ -195,12 +195,6 @@ get_altertable_subcmdtypes(PG_FUNCTION_ARGS) case AT_SetUnLogged: strtype = "SET UNLOGGED"; break; - case AT_AddOids: - strtype = "ADD OIDS"; - break; - case AT_AddOidsRecurse: - strtype = "ADD OIDS (and recurse)"; - break; case AT_DropOids: strtype = "DROP OIDS"; break; diff --git a/src/test/modules/test_predtest/test_predtest.c b/src/test/modules/test_predtest/test_predtest.c index 51320ade2e5..1e699e6556e 100644 --- a/src/test/modules/test_predtest/test_predtest.c +++ b/src/test/modules/test_predtest/test_predtest.c @@ -185,7 +185,7 @@ test_predtest(PG_FUNCTION_ARGS) if (SPI_finish() != SPI_OK_FINISH) elog(ERROR, "SPI_finish failed"); - tupdesc = CreateTemplateTupleDesc(8, false); + tupdesc = CreateTemplateTupleDesc(8); TupleDescInitEntry(tupdesc, (AttrNumber) 1, "strong_implied_by", BOOLOID, -1, 0); TupleDescInitEntry(tupdesc, (AttrNumber) 2, diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index 0aa13b3cec2..38ede0a4730 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -849,7 +849,7 @@ ERROR: check constraint "foo" is violated by some row drop table atacc2; drop table atacc1; -- test unique constraint adding -create table atacc1 ( test int ) with oids; +create table atacc1 ( test int ) ; -- add a unique constraint alter table atacc1 add constraint atacc_test1 unique (test); -- insert first value @@ -860,8 +860,6 @@ ERROR: duplicate key value violates unique constraint "atacc_test1" DETAIL: Key (test)=(2) already exists. -- should succeed insert into atacc1 (test) values (4); --- try adding a unique oid constraint -alter table atacc1 add constraint atacc_oid1 unique(oid); -- try to create duplicates via alter table using - should fail alter table atacc1 alter column test type integer using 0; ERROR: could not create unique index "atacc_test1" @@ -910,7 +908,7 @@ ERROR: duplicate key value violates unique constraint "atacc1_test_key" DETAIL: Key (test)=(3) already exists. drop table atacc1; -- test primary key constraint adding -create table atacc1 ( test int ) with oids; +create table atacc1 ( id serial, test int) ; -- add a primary key constraint alter table atacc1 add constraint atacc_test1 primary key (test); -- insert first value @@ -924,14 +922,14 @@ insert into atacc1 (test) values (4); -- inserting NULL should fail insert into atacc1 (test) values(NULL); ERROR: null value in column "test" violates not-null constraint -DETAIL: Failing row contains (null). +DETAIL: Failing row contains (4, null). -- try adding a second primary key (should fail) -alter table atacc1 add constraint atacc_oid1 primary key(oid); +alter table atacc1 add constraint atacc_oid1 primary key(id); ERROR: multiple primary keys for table "atacc1" are not allowed -- drop first primary key constraint alter table atacc1 drop constraint atacc_test1 restrict; -- try adding a primary key on oid (should succeed) -alter table atacc1 add constraint atacc_oid1 primary key(oid); +alter table atacc1 add constraint atacc_oid1 primary key(id); drop table atacc1; -- let's do one where the primary key constraint fails when added create table atacc1 ( test int ); @@ -1021,7 +1019,7 @@ alter table non_existent alter column bar drop not null; ERROR: relation "non_existent" does not exist -- test setting columns to null and not null and vice versa -- test checking for null values and primary key -create table atacc1 (test int not null) with oids; +create table atacc1 (test int not null); alter table atacc1 add constraint "atacc1_pkey" primary key (test); alter table atacc1 alter column test drop not null; ERROR: column "test" is in a primary key @@ -1037,11 +1035,6 @@ alter table atacc1 alter bar set not null; ERROR: column "bar" of relation "atacc1" does not exist alter table atacc1 alter bar drop not null; ERROR: column "bar" of relation "atacc1" does not exist --- try altering the oid column, should fail -alter table atacc1 alter oid set not null; -ERROR: cannot alter system column "oid" -alter table atacc1 alter oid drop not null; -ERROR: cannot alter system column "oid" -- try creating a view and altering that, should fail create view myview as select * from atacc1; alter table myview alter column test drop not null; @@ -1147,7 +1140,7 @@ ERROR: permission denied: "pg_class" is a system catalog alter table nosuchtable drop column bar; ERROR: relation "nosuchtable" does not exist -- test dropping columns -create table atacc1 (a int4 not null, b int4, c int4 not null, d int4) with oids; +create table atacc1 (a int4 not null, b int4, c int4 not null, d int4); insert into atacc1 values (1, 2, 3, 4); alter table atacc1 drop a; alter table atacc1 drop a; @@ -1292,8 +1285,13 @@ delete from atacc1; -- try dropping a non-existent column, should fail alter table atacc1 drop bar; ERROR: column "bar" of relation "atacc1" does not exist --- try dropping the oid column, should succeed -alter table atacc1 drop oid; +-- try removing an oid column, should succeed (as it's nonexistant) +alter table atacc1 SET WITHOUT OIDS; +-- try adding an oid column, should fail (not supported) +alter table atacc1 SET WITH OIDS; +ERROR: syntax error at or near "WITH" +LINE 1: alter table atacc1 SET WITH OIDS; + ^ -- try dropping the xmin column, should fail alter table atacc1 drop xmin; ERROR: cannot drop system column "xmin" @@ -1691,131 +1689,6 @@ order by attrelid::regclass::text, attnum; depth2 | c | 1 | f (3 rows) --- --- Test the ALTER TABLE SET WITH/WITHOUT OIDS command --- -create table altstartwith (col integer) with oids; -insert into altstartwith values (1); -select oid > 0, * from altstartwith; - ?column? | col -----------+----- - t | 1 -(1 row) - -alter table altstartwith set without oids; -select oid > 0, * from altstartwith; -- fails -ERROR: column "oid" does not exist -LINE 1: select oid > 0, * from altstartwith; - ^ -select * from altstartwith; - col ------ - 1 -(1 row) - -alter table altstartwith set with oids; -select oid > 0, * from altstartwith; - ?column? | col -----------+----- - t | 1 -(1 row) - -drop table altstartwith; --- Check inheritance cases -create table altwithoid (col integer) with oids; --- Inherits parents oid column anyway -create table altinhoid () inherits (altwithoid) without oids; -insert into altinhoid values (1); -select oid > 0, * from altwithoid; - ?column? | col -----------+----- - t | 1 -(1 row) - -select oid > 0, * from altinhoid; - ?column? | col -----------+----- - t | 1 -(1 row) - -alter table altwithoid set without oids; -select oid > 0, * from altwithoid; -- fails -ERROR: column "oid" does not exist -LINE 1: select oid > 0, * from altwithoid; - ^ -select oid > 0, * from altinhoid; -- fails -ERROR: column "oid" does not exist -LINE 1: select oid > 0, * from altinhoid; - ^ -select * from altwithoid; - col ------ - 1 -(1 row) - -select * from altinhoid; - col ------ - 1 -(1 row) - -alter table altwithoid set with oids; -select oid > 0, * from altwithoid; - ?column? | col -----------+----- - t | 1 -(1 row) - -select oid > 0, * from altinhoid; - ?column? | col -----------+----- - t | 1 -(1 row) - -drop table altwithoid cascade; -NOTICE: drop cascades to table altinhoid -create table altwithoid (col integer) without oids; --- child can have local oid column -create table altinhoid () inherits (altwithoid) with oids; -insert into altinhoid values (1); -select oid > 0, * from altwithoid; -- fails -ERROR: column "oid" does not exist -LINE 1: select oid > 0, * from altwithoid; - ^ -select oid > 0, * from altinhoid; - ?column? | col -----------+----- - t | 1 -(1 row) - -alter table altwithoid set with oids; -NOTICE: merging definition of column "oid" for child "altinhoid" -select oid > 0, * from altwithoid; - ?column? | col -----------+----- - t | 1 -(1 row) - -select oid > 0, * from altinhoid; - ?column? | col -----------+----- - t | 1 -(1 row) - --- the child's local definition should remain -alter table altwithoid set without oids; -select oid > 0, * from altwithoid; -- fails -ERROR: column "oid" does not exist -LINE 1: select oid > 0, * from altwithoid; - ^ -select oid > 0, * from altinhoid; - ?column? | col -----------+----- - t | 1 -(1 row) - -drop table altwithoid cascade; -NOTICE: drop cascades to table altinhoid -- test renumbering of child-table columns in inherited operations create table p1 (f1 int); create table c1 (f2 text, f3 int) inherits (p1); @@ -2916,7 +2789,7 @@ CREATE TABLE tt3 (y numeric(8,2), x int); -- wrong column order CREATE TABLE tt4 (x int); -- too few columns CREATE TABLE tt5 (x int, y numeric(8,2), z int); -- too few columns CREATE TABLE tt6 () INHERITS (tt0); -- can't have a parent -CREATE TABLE tt7 (x int, q text, y numeric(8,2)) WITH OIDS; +CREATE TABLE tt7 (x int, q text, y numeric(8,2)); ALTER TABLE tt7 DROP q; -- OK ALTER TABLE tt0 OF tt_t0; ALTER TABLE tt1 OF tt_t0; @@ -3441,16 +3314,6 @@ ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); ERROR: cannot attach a typed table as partition DROP TYPE mytype CASCADE; NOTICE: drop cascades to table fail_part --- check existence (or non-existence) of oid column -ALTER TABLE list_parted SET WITH OIDS; -CREATE TABLE fail_part (a int); -ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); -ERROR: cannot attach table "fail_part" without OIDs as partition of table "list_parted" with OIDs -ALTER TABLE list_parted SET WITHOUT OIDS; -ALTER TABLE fail_part SET WITH OIDS; -ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); -ERROR: cannot attach table "fail_part" with OIDs as partition of table "list_parted" without OIDs -DROP TABLE fail_part; -- check that the table being attached has only columns present in the parent CREATE TABLE fail_part (like list_parted, c int); ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out index eb9e4b97741..19bb5384110 100644 --- a/src/test/regress/expected/copy2.out +++ b/src/test/regress/expected/copy2.out @@ -4,7 +4,7 @@ CREATE TEMP TABLE x ( c text not null default 'stuff', d text, e text -) WITH OIDS; +) ; CREATE FUNCTION fn_x_before () RETURNS TRIGGER AS ' BEGIN NEW.e := ''before trigger fired''::text; @@ -46,7 +46,7 @@ COPY x from stdin; ERROR: extra data after last expected column CONTEXT: COPY x, line 1: "2002 232 40 50 60 70 80" -- various COPY options: delimiters, oids, NULL string, encoding -COPY x (b, c, d, e) from stdin with oids delimiter ',' null 'x'; +COPY x (b, c, d, e) from stdin delimiter ',' null 'x'; COPY x from stdin WITH DELIMITER AS ';' NULL AS ''; COPY x from stdin WITH DELIMITER AS ':' NULL AS E'\\X' ENCODING 'sql_ascii'; -- check results of copy in @@ -80,18 +80,6 @@ SELECT * FROM x; 5 | 5 | stuff | test_5 | after trigger fired (25 rows) --- COPY w/ oids on a table w/o oids should fail -CREATE TABLE no_oids ( - a int, - b int -) WITHOUT OIDS; -INSERT INTO no_oids (a, b) VALUES (5, 10); -INSERT INTO no_oids (a, b) VALUES (20, 30); --- should fail -COPY no_oids FROM stdin WITH OIDS; -ERROR: table "no_oids" does not have OIDs -COPY no_oids TO stdout WITH OIDS; -ERROR: table "no_oids" does not have OIDs -- check copy out COPY x TO stdout; 9999 \N \\N NN before trigger fired diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out index 297535bb8fd..46deb55c679 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -2779,19 +2779,17 @@ DROP TABLE cwi_test; -- -- Check handling of indexes on system columns -- -CREATE TABLE oid_table (a INT) WITH OIDS; --- An index on the OID column should be allowed -CREATE INDEX ON oid_table (oid); --- Other system columns cannot be indexed -CREATE INDEX ON oid_table (ctid); -ERROR: index creation on system columns is not supported +CREATE TABLE syscol_table (a INT); +-- System columns cannot be indexed +CREATE INDEX ON syscolcol_table (ctid); +ERROR: relation "syscolcol_table" does not exist -- nor used in expressions -CREATE INDEX ON oid_table ((ctid >= '(1000,0)')); +CREATE INDEX ON syscol_table ((ctid >= '(1000,0)')); ERROR: index creation on system columns is not supported -- nor used in predicates -CREATE INDEX ON oid_table (a) WHERE ctid >= '(1000,0)'; +CREATE INDEX ON syscol_table (a) WHERE ctid >= '(1000,0)'; ERROR: index creation on system columns is not supported -DROP TABLE oid_table; +DROP TABLE syscol_table; -- -- Tests for IS NULL/IS NOT NULL with b-tree indexes -- diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out index f474f0d73e1..e92748c1ea0 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -47,7 +47,7 @@ CREATE TABLE tenk1 ( stringu1 name, stringu2 name, string4 name -) WITH OIDS; +); CREATE TABLE tenk2 ( unique1 int4, unique2 int4, @@ -74,7 +74,7 @@ CREATE TABLE person ( CREATE TABLE emp ( salary int4, manager name -) INHERITS (person) WITH OIDS; +) INHERITS (person); CREATE TABLE student ( gpa float8 ) INHERITS (person); @@ -218,8 +218,6 @@ NOTICE: relation "test_tsvector" already exists, skipping -- invalid: non-lowercase quoted reloptions identifiers CREATE TABLE tas_case WITH ("Fillfactor" = 10) AS SELECT 1 a; ERROR: unrecognized parameter "Fillfactor" -CREATE TABLE tas_case (a text) WITH ("Oids" = true); -ERROR: unrecognized parameter "Oids" CREATE UNLOGGED TABLE unlogged1 (a int primary key); -- OK CREATE TEMPORARY TABLE unlogged2 (a int primary key); -- OK SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged\d' ORDER BY relname; @@ -263,9 +261,18 @@ ERROR: relation "as_select1" already exists CREATE TABLE IF NOT EXISTS as_select1 AS SELECT * FROM pg_class WHERE relkind = 'r'; NOTICE: relation "as_select1" already exists, skipping DROP TABLE as_select1; --- check that the oid column is added before the primary key is checked -CREATE TABLE oid_pk (f1 INT, PRIMARY KEY(oid)) WITH OIDS; -DROP TABLE oid_pk; +-- check that tables with oids cannot be created anymore +CREATE TABLE withoid() WITH OIDS; +ERROR: syntax error at or near "OIDS" +LINE 1: CREATE TABLE withoid() WITH OIDS; + ^ +CREATE TABLE withoid() WITH (oids); +ERROR: tables declared WITH OIDS are not supported +CREATE TABLE withoid() WITH (oids = true); +ERROR: tables declared WITH OIDS are not supported +-- but explicitly not adding oids is still supported +CREATE TEMP TABLE withoutoid() WITHOUT OIDS; DROP TABLE withoutoid; +CREATE TEMP TABLE withoutoid() WITH (oids = false); DROP TABLE withoutoid; -- -- Partitioned tables -- @@ -575,29 +582,6 @@ CREATE TEMP TABLE temp_parted ( CREATE TABLE fail_part PARTITION OF temp_parted FOR VALUES IN ('a'); ERROR: cannot create a permanent relation as partition of temporary relation "temp_parted" DROP TABLE temp_parted; --- cannot create a table with oids as partition of table without oids -CREATE TABLE no_oids_parted ( - a int -) PARTITION BY RANGE (a) WITHOUT OIDS; -CREATE TABLE fail_part PARTITION OF no_oids_parted FOR VALUES FROM (1) TO (10) WITH OIDS; -ERROR: cannot create table with OIDs as partition of table without OIDs -DROP TABLE no_oids_parted; --- If the partitioned table has oids, then the partition must have them. --- If the WITHOUT OIDS option is specified for partition, it is overridden. -CREATE TABLE oids_parted ( - a int -) PARTITION BY RANGE (a) WITH OIDS; -CREATE TABLE part_forced_oids PARTITION OF oids_parted FOR VALUES FROM (1) TO (10) WITHOUT OIDS; -\d+ part_forced_oids - Table "public.part_forced_oids" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | -Partition of: oids_parted FOR VALUES FROM (1) TO (10) -Partition constraint: ((a IS NOT NULL) AND (a >= 1) AND (a < 10)) -Has OIDs: yes - -DROP TABLE oids_parted, part_forced_oids; -- check for partition bound overlap and other invalid specifications CREATE TABLE list_parted2 ( a varchar diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out index 8d4543bfe8d..b5822112706 100644 --- a/src/test/regress/expected/create_table_like.out +++ b/src/test/regress/expected/create_table_like.out @@ -285,37 +285,3 @@ DROP TYPE ctlty1; DROP VIEW ctlv1; DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12; NOTICE: table "ctlt10" does not exist, skipping -/* LIKE WITH OIDS */ -CREATE TABLE has_oid (x INTEGER) WITH OIDS; -CREATE TABLE no_oid (y INTEGER); -CREATE TABLE like_test (z INTEGER, LIKE has_oid); -SELECT oid FROM like_test; - oid ------ -(0 rows) - -CREATE TABLE like_test2 (z INTEGER, LIKE no_oid); -SELECT oid FROM like_test2; -- fail -ERROR: column "oid" does not exist -LINE 1: SELECT oid FROM like_test2; - ^ -CREATE TABLE like_test3 (z INTEGER, LIKE has_oid, LIKE no_oid); -SELECT oid FROM like_test3; - oid ------ -(0 rows) - -CREATE TABLE like_test4 (z INTEGER, PRIMARY KEY(oid), LIKE has_oid); -SELECT oid FROM like_test4; - oid ------ -(0 rows) - -CREATE TABLE like_test5 (z INTEGER, LIKE no_oid) WITH OIDS; -SELECT oid FROM like_test5; - oid ------ -(0 rows) - -DROP TABLE has_oid, no_oid, like_test, like_test2, like_test3, - like_test4, like_test5; diff --git a/src/test/regress/expected/enum.out b/src/test/regress/expected/enum.out index 4f839ce0279..dffff88928e 100644 --- a/src/test/regress/expected/enum.out +++ b/src/test/regress/expected/enum.out @@ -685,7 +685,7 @@ SELECT COUNT(*) FROM pg_type WHERE typname = 'rainbow'; SELECT * FROM pg_enum WHERE NOT EXISTS (SELECT 1 FROM pg_type WHERE pg_type.oid = enumtypid); - enumtypid | enumsortorder | enumlabel ------------+---------------+----------- + oid | enumtypid | enumsortorder | enumlabel +-----+-----------+---------------+----------- (0 rows) diff --git a/src/test/regress/expected/errors.out b/src/test/regress/expected/errors.out index ce473a03efd..7dd8a5b33e4 100644 --- a/src/test/regress/expected/errors.out +++ b/src/test/regress/expected/errors.out @@ -103,8 +103,8 @@ ERROR: column "nonesuchatt" does not exist alter table emp rename column salary to manager; ERROR: column "manager" of relation "stud_emp" already exists -- conflict -alter table emp rename column salary to oid; -ERROR: column name "oid" conflicts with a system column name +alter table emp rename column salary to ctid; +ERROR: column name "ctid" conflicts with a system column name -- -- TRANSACTION STUFF -- not in a xact diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out index b16c8e2c247..4d82d3a7e84 100644 --- a/src/test/regress/expected/foreign_data.out +++ b/src/test/regress/expected/foreign_data.out @@ -30,8 +30,8 @@ SELECT srvname, srvoptions FROM pg_foreign_server; (0 rows) SELECT * FROM pg_user_mapping; - umuser | umserver | umoptions ---------+----------+----------- + oid | umuser | umserver | umoptions +-----+--------+----------+----------- (0 rows) -- CREATE FOREIGN DATA WRAPPER @@ -684,10 +684,6 @@ LINE 1: CREATE FOREIGN TABLE ft1 (); ^ CREATE FOREIGN TABLE ft1 () SERVER no_server; -- ERROR ERROR: server "no_server" does not exist -CREATE FOREIGN TABLE ft1 () SERVER s0 WITH OIDS; -- ERROR -ERROR: syntax error at or near "WITH" -LINE 1: CREATE FOREIGN TABLE ft1 () SERVER s0 WITH OIDS; - ^ CREATE FOREIGN TABLE ft1 ( c1 integer OPTIONS ("param 1" 'val1') PRIMARY KEY, c2 text OPTIONS (param2 'val2', param3 'val3'), @@ -822,7 +818,6 @@ ALTER FOREIGN TABLE ft1 DROP CONSTRAINT no_const; -- ERROR ERROR: constraint "no_const" of relation "ft1" does not exist ALTER FOREIGN TABLE ft1 DROP CONSTRAINT IF EXISTS no_const; NOTICE: constraint "no_const" of relation "ft1" does not exist, skipping -ALTER FOREIGN TABLE ft1 SET WITH OIDS; ALTER FOREIGN TABLE ft1 OWNER TO regress_test_role; ALTER FOREIGN TABLE ft1 OPTIONS (DROP delimiter, SET quote '~', ADD escape '@'); ALTER FOREIGN TABLE ft1 DROP COLUMN no_column; -- ERROR @@ -1725,63 +1720,6 @@ Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 --- OID system column -ALTER TABLE fd_pt1 SET WITH OIDS; -\d+ fd_pt1 - Table "public.fd_pt1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | 10000 | - c2 | text | | | | extended | | - c3 | date | | | | plain | | -Check constraints: - "fd_pt1chk3" CHECK (c2 <> ''::text) -Child tables: ft2 -Has OIDs: yes - -\d+ ft2 - Foreign table "public.ft2" - Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description ---------+---------+-----------+----------+---------+-------------+----------+--------------+------------- - c1 | integer | | not null | | | plain | | - c2 | text | | | | | extended | | - c3 | date | | | | | plain | | -Check constraints: - "fd_pt1chk2" CHECK (c2 <> ''::text) - "fd_pt1chk3" CHECK (c2 <> ''::text) -Server: s0 -FDW options: (delimiter ',', quote '"', "be quoted" 'value') -Inherits: fd_pt1 -Has OIDs: yes - -ALTER TABLE ft2 SET WITHOUT OIDS; -- ERROR -ERROR: cannot drop inherited column "oid" -ALTER TABLE fd_pt1 SET WITHOUT OIDS; -\d+ fd_pt1 - Table "public.fd_pt1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | 10000 | - c2 | text | | | | extended | | - c3 | date | | | | plain | | -Check constraints: - "fd_pt1chk3" CHECK (c2 <> ''::text) -Child tables: ft2 - -\d+ ft2 - Foreign table "public.ft2" - Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description ---------+---------+-----------+----------+---------+-------------+----------+--------------+------------- - c1 | integer | | not null | | | plain | | - c2 | text | | | | | extended | | - c3 | date | | | | | plain | | -Check constraints: - "fd_pt1chk2" CHECK (c2 <> ''::text) - "fd_pt1chk3" CHECK (c2 <> ''::text) -Server: s0 -FDW options: (delimiter ',', quote '"', "be quoted" 'value') -Inherits: fd_pt1 - -- changes name of an attribute recursively ALTER TABLE fd_pt1 RENAME COLUMN c1 TO f1; ALTER TABLE fd_pt1 RENAME COLUMN c2 TO f2; @@ -2097,7 +2035,7 @@ SELECT srvname, srvoptions FROM pg_foreign_server; (0 rows) SELECT * FROM pg_user_mapping; - umuser | umserver | umoptions ---------+----------+----------- + oid | umuser | umserver | umoptions +-----+--------+----------+----------- (0 rows) diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index 1e00c849f38..f259d075359 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -673,55 +673,6 @@ select * from d; 32 | one | two | three (1 row) --- check that oid column is handled properly during alter table inherit -create table oid_parent (a int) with oids; -create table oid_child () inherits (oid_parent); -select attinhcount, attislocal from pg_attribute - where attrelid = 'oid_child'::regclass and attname = 'oid'; - attinhcount | attislocal --------------+------------ - 1 | f -(1 row) - -drop table oid_child; -create table oid_child (a int) without oids; -alter table oid_child inherit oid_parent; -- fail -ERROR: table "oid_child" without OIDs cannot inherit from table "oid_parent" with OIDs -alter table oid_child set with oids; -select attinhcount, attislocal from pg_attribute - where attrelid = 'oid_child'::regclass and attname = 'oid'; - attinhcount | attislocal --------------+------------ - 0 | t -(1 row) - -alter table oid_child inherit oid_parent; -select attinhcount, attislocal from pg_attribute - where attrelid = 'oid_child'::regclass and attname = 'oid'; - attinhcount | attislocal --------------+------------ - 1 | t -(1 row) - -alter table oid_child set without oids; -- fail -ERROR: cannot drop inherited column "oid" -alter table oid_parent set without oids; -select attinhcount, attislocal from pg_attribute - where attrelid = 'oid_child'::regclass and attname = 'oid'; - attinhcount | attislocal --------------+------------ - 0 | t -(1 row) - -alter table oid_child set without oids; -select attinhcount, attislocal from pg_attribute - where attrelid = 'oid_child'::regclass and attname = 'oid'; - attinhcount | attislocal --------------+------------ -(0 rows) - -drop table oid_parent cascade; -NOTICE: drop cascades to table oid_child -- Test non-inheritable parent constraints create table p1(ff1 int); alter table p1 add constraint p1chk check (ff1 > 0) no inherit; diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out index 6b841c7850e..12ae6c5fa5f 100644 --- a/src/test/regress/expected/insert_conflict.out +++ b/src/test/regress/expected/insert_conflict.out @@ -443,18 +443,14 @@ drop table insertconflicttest; -- -- Verify that EXCLUDED does not allow system column references. These -- do not make sense because EXCLUDED isn't an already stored tuple --- (and thus doesn't have a ctid, oids are not assigned yet, etc). +-- (and thus doesn't have a ctid etc). -- -create table syscolconflicttest(key int4, data text) WITH OIDS; +create table syscolconflicttest(key int4, data text); insert into syscolconflicttest values (1); insert into syscolconflicttest values (1) on conflict (key) do update set data = excluded.ctid::text; ERROR: column excluded.ctid does not exist LINE 1: ...values (1) on conflict (key) do update set data = excluded.c... ^ -insert into syscolconflicttest values (1) on conflict (key) do update set data = excluded.oid::text; -ERROR: column excluded.oid does not exist -LINE 1: ...values (1) on conflict (key) do update set data = excluded.o... - ^ drop table syscolconflicttest; -- -- Previous tests all managed to not test any expressions requiring @@ -620,65 +616,6 @@ insert into excluded values(1, '2') on conflict (key) do update set data = 3 RET -- clean up drop table excluded; --- Check tables w/o oids are handled correctly -create table testoids(key int primary key, data text) without oids; --- first without oids -insert into testoids values(1, '1') on conflict (key) do update set data = excluded.data RETURNING *; - key | data ------+------ - 1 | 1 -(1 row) - -insert into testoids values(1, '2') on conflict (key) do update set data = excluded.data RETURNING *; - key | data ------+------ - 1 | 2 -(1 row) - --- add oids -alter table testoids set with oids; --- update existing row, that didn't have an oid -insert into testoids values(1, '3') on conflict (key) do update set data = excluded.data RETURNING *; - key | data ------+------ - 1 | 3 -(1 row) - --- insert a new row -insert into testoids values(2, '1') on conflict (key) do update set data = excluded.data RETURNING *; - key | data ------+------ - 2 | 1 -(1 row) - --- and update it -insert into testoids values(2, '2') on conflict (key) do update set data = excluded.data RETURNING *; - key | data ------+------ - 2 | 2 -(1 row) - --- remove oids again, test -alter table testoids set without oids; -insert into testoids values(1, '4') on conflict (key) do update set data = excluded.data RETURNING *; - key | data ------+------ - 1 | 4 -(1 row) - -insert into testoids values(3, '1') on conflict (key) do update set data = excluded.data RETURNING *; - key | data ------+------ - 3 | 1 -(1 row) - -insert into testoids values(3, '2') on conflict (key) do update set data = excluded.data RETURNING *; - key | data ------+------ - 3 | 2 -(1 row) - -DROP TABLE testoids; -- check that references to columns after dropped columns are handled correctly create table dropcol(key int primary key, drop1 int, keep1 text, drop2 numeric, keep2 float); insert into dropcol(key, drop1, keep1, drop2, keep2) values(1, 1, '1', '1', 1); diff --git a/src/test/regress/expected/misc_sanity.out b/src/test/regress/expected/misc_sanity.out index 2d3522b500d..1d4b000acf0 100644 --- a/src/test/regress/expected/misc_sanity.out +++ b/src/test/regress/expected/misc_sanity.out @@ -52,7 +52,10 @@ declare relnm text; begin for relnm, reloid, shared in select relname, oid, relisshared from pg_class - where relhasoids and oid < 16384 order by 1 + where EXISTS( + SELECT * FROM pg_attribute + WHERE attrelid = pg_class.oid AND attname = 'oid') + and relkind = 'r' and oid < 16384 order by 1 loop execute 'select min(oid) from ' || relnm into lowoid; continue when lowoid is null or lowoid >= 16384; diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out index c073a5ac3fa..6072f6bdb1f 100644 --- a/src/test/regress/expected/opr_sanity.out +++ b/src/test/regress/expected/opr_sanity.out @@ -794,8 +794,8 @@ SELECT * FROM pg_cast c WHERE castsource = 0 OR casttarget = 0 OR castcontext NOT IN ('e', 'a', 'i') OR castmethod NOT IN ('f', 'b' ,'i'); - castsource | casttarget | castfunc | castcontext | castmethod -------------+------------+----------+-------------+------------ + oid | castsource | casttarget | castfunc | castcontext | castmethod +-----+------------+------------+----------+-------------+------------ (0 rows) -- Check that castfunc is nonzero only for cast methods that need a function, @@ -804,8 +804,8 @@ SELECT * FROM pg_cast c WHERE (castmethod = 'f' AND castfunc = 0) OR (castmethod IN ('b', 'i') AND castfunc <> 0); - castsource | casttarget | castfunc | castcontext | castmethod -------------+------------+----------+-------------+------------ + oid | castsource | casttarget | castfunc | castcontext | castmethod +-----+------------+------------+----------+-------------+------------ (0 rows) -- Look for casts to/from the same type that aren't length coercion functions. @@ -814,15 +814,15 @@ WHERE (castmethod = 'f' AND castfunc = 0) SELECT * FROM pg_cast c WHERE castsource = casttarget AND castfunc = 0; - castsource | casttarget | castfunc | castcontext | castmethod -------------+------------+----------+-------------+------------ + oid | castsource | casttarget | castfunc | castcontext | castmethod +-----+------------+------------+----------+-------------+------------ (0 rows) SELECT c.* FROM pg_cast c, pg_proc p WHERE c.castfunc = p.oid AND p.pronargs < 2 AND castsource = casttarget; - castsource | casttarget | castfunc | castcontext | castmethod -------------+------------+----------+-------------+------------ + oid | castsource | casttarget | castfunc | castcontext | castmethod +-----+------------+------------+----------+-------------+------------ (0 rows) -- Look for cast functions that don't have the right signature. The @@ -840,8 +840,8 @@ WHERE c.castfunc = p.oid AND OR (c.castsource = 'character'::regtype AND p.proargtypes[0] = 'text'::regtype)) OR NOT binary_coercible(p.prorettype, c.casttarget)); - castsource | casttarget | castfunc | castcontext | castmethod -------------+------------+----------+-------------+------------ + oid | castsource | casttarget | castfunc | castcontext | castmethod +-----+------------+------------+----------+-------------+------------ (0 rows) SELECT c.* @@ -849,8 +849,8 @@ FROM pg_cast c, pg_proc p WHERE c.castfunc = p.oid AND ((p.pronargs > 1 AND p.proargtypes[1] != 'int4'::regtype) OR (p.pronargs > 2 AND p.proargtypes[2] != 'bool'::regtype)); - castsource | casttarget | castfunc | castcontext | castmethod -------------+------------+----------+-------------+------------ + oid | castsource | casttarget | castfunc | castcontext | castmethod +-----+------------+------------+----------+-------------+------------ (0 rows) -- Look for binary compatible casts that do not have the reverse diff --git a/src/test/regress/expected/prepare.out b/src/test/regress/expected/prepare.out index 7016e82bd42..d07c0cc9c91 100644 --- a/src/test/regress/expected/prepare.out +++ b/src/test/regress/expected/prepare.out @@ -64,11 +64,11 @@ EXECUTE q2('postgres'); postgres | f | t (1 row) -PREPARE q3(text, int, float, boolean, oid, smallint) AS +PREPARE q3(text, int, float, boolean, smallint) AS SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR - ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int) + ten = $3::bigint OR true = $4 OR odd = $5::int) ORDER BY unique1; -EXECUTE q3('AAAAxx', 5::smallint, 10.5::float, false, 500::oid, 4::bigint); +EXECUTE q3('AAAAxx', 5::smallint, 10.5::float, false, 4::bigint); unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 2 | 2716 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | MAEAAA | AAAAxx @@ -105,13 +105,13 @@ EXECUTE q3('AAAAxx', 5::smallint, 10.5::float, false, 500::oid, 4::bigint); -- too few params EXECUTE q3('bool'); ERROR: wrong number of parameters for prepared statement "q3" -DETAIL: Expected 6 parameters but got 1. +DETAIL: Expected 5 parameters but got 1. -- too many params -EXECUTE q3('bytea', 5::smallint, 10.5::float, false, 500::oid, 4::bigint, true); +EXECUTE q3('bytea', 5::smallint, 10.5::float, false, 4::bigint, true); ERROR: wrong number of parameters for prepared statement "q3" -DETAIL: Expected 6 parameters but got 7. +DETAIL: Expected 5 parameters but got 6. -- wrong param types -EXECUTE q3(5::smallint, 10.5::float, false, 500::oid, 4::bigint, 'bytea'); +EXECUTE q3(5::smallint, 10.5::float, false, 4::bigint, 'bytea'); ERROR: parameter $3 of type boolean cannot be coerced to the expected type double precision HINT: You will need to rewrite or cast the expression. -- invalid type @@ -152,22 +152,22 @@ PREPARE q7(unknown) AS SELECT * FROM road WHERE thepath = $1; SELECT name, statement, parameter_types FROM pg_prepared_statements ORDER BY name; - name | statement | parameter_types -------+---------------------------------------------------------------------+-------------------------------------------------------- - q2 | PREPARE q2(text) AS +| {text} - | SELECT datname, datistemplate, datallowconn +| - | FROM pg_database WHERE datname = $1; | - q3 | PREPARE q3(text, int, float, boolean, oid, smallint) AS +| {text,integer,"double precision",boolean,oid,smallint} - | SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR +| - | ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int)+| - | ORDER BY unique1; | - q5 | PREPARE q5(int, text) AS +| {integer,text} - | SELECT * FROM tenk1 WHERE unique1 = $1 OR stringu1 = $2 +| - | ORDER BY unique1; | - q6 | PREPARE q6 AS +| {integer,name} - | SELECT * FROM tenk1 WHERE unique1 = $1 AND stringu1 = $2; | - q7 | PREPARE q7(unknown) AS +| {path} - | SELECT * FROM road WHERE thepath = $1; | + name | statement | parameter_types +------+------------------------------------------------------------------+---------------------------------------------------- + q2 | PREPARE q2(text) AS +| {text} + | SELECT datname, datistemplate, datallowconn +| + | FROM pg_database WHERE datname = $1; | + q3 | PREPARE q3(text, int, float, boolean, smallint) AS +| {text,integer,"double precision",boolean,smallint} + | SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR+| + | ten = $3::bigint OR true = $4 OR odd = $5::int) +| + | ORDER BY unique1; | + q5 | PREPARE q5(int, text) AS +| {integer,text} + | SELECT * FROM tenk1 WHERE unique1 = $1 OR stringu1 = $2 +| + | ORDER BY unique1; | + q6 | PREPARE q6 AS +| {integer,name} + | SELECT * FROM tenk1 WHERE unique1 = $1 AND stringu1 = $2; | + q7 | PREPARE q7(unknown) AS +| {path} + | SELECT * FROM road WHERE thepath = $1; | (5 rows) -- test DEALLOCATE ALL; diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out index 3af92ed1a83..83b3196b1df 100644 --- a/src/test/regress/expected/privileges.out +++ b/src/test/regress/expected/privileges.out @@ -587,10 +587,10 @@ ERROR: permission denied for table atest5 DELETE FROM atest5 WHERE two = 2; -- ok -- check inheritance cases SET SESSION AUTHORIZATION regress_priv_user1; -CREATE TABLE atestp1 (f1 int, f2 int) WITH OIDS; -CREATE TABLE atestp2 (fx int, fy int) WITH OIDS; +CREATE TABLE atestp1 (f1 int, f2 int); +CREATE TABLE atestp2 (fx int, fy int); CREATE TABLE atestc (fz int) INHERITS (atestp1, atestp2); -GRANT SELECT(fx,fy,oid) ON atestp2 TO regress_priv_user2; +GRANT SELECT(fx,fy,tableoid) ON atestp2 TO regress_priv_user2; GRANT SELECT(fx) ON atestc TO regress_priv_user2; SET SESSION AUTHORIZATION regress_priv_user2; SELECT fx FROM atestp2; -- ok @@ -608,15 +608,15 @@ SELECT atestp2 FROM atestp2; -- ok --------- (0 rows) -SELECT oid FROM atestp2; -- ok - oid ------ +SELECT tableoid FROM atestp2; -- ok + tableoid +---------- (0 rows) SELECT fy FROM atestc; -- fail ERROR: permission denied for table atestc SET SESSION AUTHORIZATION regress_priv_user1; -GRANT SELECT(fy,oid) ON atestc TO regress_priv_user2; +GRANT SELECT(fy,tableoid) ON atestc TO regress_priv_user2; SET SESSION AUTHORIZATION regress_priv_user2; SELECT fx FROM atestp2; -- still ok fx @@ -633,9 +633,9 @@ SELECT atestp2 FROM atestp2; -- ok --------- (0 rows) -SELECT oid FROM atestp2; -- ok - oid ------ +SELECT tableoid FROM atestp2; -- ok + tableoid +---------- (0 rows) -- privileges on functions, languages @@ -1720,8 +1720,8 @@ DROP SCHEMA testns5 CASCADE; SELECT d.* -- check that entries went away FROM pg_default_acl d LEFT JOIN pg_namespace n ON defaclnamespace = n.oid WHERE nspname IS NULL AND defaclnamespace != 0; - defaclrole | defaclnamespace | defaclobjtype | defaclacl -------------+-----------------+---------------+----------- + oid | defaclrole | defaclnamespace | defaclobjtype | defaclacl +-----+------------+-----------------+---------------+----------- (0 rows) -- Grant on all objects of given type in a schema diff --git a/src/test/regress/expected/reloptions.out b/src/test/regress/expected/reloptions.out index df3c99d1eb1..f90c267c87e 100644 --- a/src/test/regress/expected/reloptions.out +++ b/src/test/regress/expected/reloptions.out @@ -86,15 +86,6 @@ SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass AND -- RESET fails if a value is specified ALTER TABLE reloptions_test RESET (fillfactor=12); ERROR: RESET must not include values for parameters --- The OIDS option is not stored as reloption -DROP TABLE reloptions_test; -CREATE TABLE reloptions_test(i INT) WITH (fillfactor=20, oids=true); -SELECT reloptions, relhasoids FROM pg_class WHERE oid = 'reloptions_test'::regclass; - reloptions | relhasoids ------------------+------------ - {fillfactor=20} | t -(1 row) - -- Test toast.* options DROP TABLE reloptions_test; CREATE TABLE reloptions_test (s VARCHAR) diff --git a/src/test/regress/expected/replica_identity.out b/src/test/regress/expected/replica_identity.out index 67c34a92a4e..175ecd28794 100644 --- a/src/test/regress/expected/replica_identity.out +++ b/src/test/regress/expected/replica_identity.out @@ -5,11 +5,10 @@ CREATE TABLE test_replica_identity ( nonkey text, CONSTRAINT test_replica_identity_unique_defer UNIQUE (keya, keyb) DEFERRABLE, CONSTRAINT test_replica_identity_unique_nondefer UNIQUE (keya, keyb) -) WITH OIDS; +) ; CREATE TABLE test_replica_identity_othertable (id serial primary key); CREATE INDEX test_replica_identity_keyab ON test_replica_identity (keya, keyb); CREATE UNIQUE INDEX test_replica_identity_keyab_key ON test_replica_identity (keya, keyb); -CREATE UNIQUE INDEX test_replica_identity_oid_idx ON test_replica_identity (oid); CREATE UNIQUE INDEX test_replica_identity_nonkey ON test_replica_identity (keya, nonkey); CREATE INDEX test_replica_identity_hash ON test_replica_identity USING hash (nonkey); CREATE UNIQUE INDEX test_replica_identity_expr ON test_replica_identity (keya, keyb, (3)); @@ -88,15 +87,12 @@ Indexes: "test_replica_identity_expr" UNIQUE, btree (keya, keyb, (3)) "test_replica_identity_keyab_key" UNIQUE, btree (keya, keyb) "test_replica_identity_nonkey" UNIQUE, btree (keya, nonkey) - "test_replica_identity_oid_idx" UNIQUE, btree (oid) "test_replica_identity_partial" UNIQUE, btree (keya, keyb) WHERE keyb <> '3'::text "test_replica_identity_unique_defer" UNIQUE CONSTRAINT, btree (keya, keyb) DEFERRABLE "test_replica_identity_unique_nondefer" UNIQUE CONSTRAINT, btree (keya, keyb) "test_replica_identity_hash" hash (nonkey) "test_replica_identity_keyab" btree (keya, keyb) --- succeed, oid unique index -ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_oid_idx; -- succeed, nondeferrable unique constraint over nonnullable cols ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_unique_nondefer; -- succeed unique index over nonnullable cols @@ -121,7 +117,6 @@ Indexes: "test_replica_identity_expr" UNIQUE, btree (keya, keyb, (3)) "test_replica_identity_keyab_key" UNIQUE, btree (keya, keyb) REPLICA IDENTITY "test_replica_identity_nonkey" UNIQUE, btree (keya, nonkey) - "test_replica_identity_oid_idx" UNIQUE, btree (oid) "test_replica_identity_partial" UNIQUE, btree (keya, keyb) WHERE keyb <> '3'::text "test_replica_identity_unique_defer" UNIQUE CONSTRAINT, btree (keya, keyb) DEFERRABLE "test_replica_identity_unique_nondefer" UNIQUE CONSTRAINT, btree (keya, keyb) @@ -170,14 +165,12 @@ Indexes: "test_replica_identity_expr" UNIQUE, btree (keya, keyb, (3)) "test_replica_identity_keyab_key" UNIQUE, btree (keya, keyb) "test_replica_identity_nonkey" UNIQUE, btree (keya, nonkey) - "test_replica_identity_oid_idx" UNIQUE, btree (oid) "test_replica_identity_partial" UNIQUE, btree (keya, keyb) WHERE keyb <> '3'::text "test_replica_identity_unique_defer" UNIQUE CONSTRAINT, btree (keya, keyb) DEFERRABLE "test_replica_identity_unique_nondefer" UNIQUE CONSTRAINT, btree (keya, keyb) "test_replica_identity_hash" hash (nonkey) "test_replica_identity_keyab" btree (keya, keyb) Replica Identity: FULL -Has OIDs: yes ALTER TABLE test_replica_identity REPLICA IDENTITY NOTHING; SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; diff --git a/src/test/regress/expected/roleattributes.out b/src/test/regress/expected/roleattributes.out index 570aa5f8343..5e6969b173e 100644 --- a/src/test/regress/expected/roleattributes.out +++ b/src/test/regress/expected/roleattributes.out @@ -1,27 +1,27 @@ -- default for superuser is false CREATE ROLE regress_test_def_superuser; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_def_superuser'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_superuser'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil ----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- regress_test_def_superuser | f | t | f | f | f | f | f | -1 | | (1 row) CREATE ROLE regress_test_superuser WITH SUPERUSER; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_superuser'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil ------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- regress_test_superuser | t | t | f | f | f | f | f | -1 | | (1 row) ALTER ROLE regress_test_superuser WITH NOSUPERUSER; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_superuser'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil ------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- regress_test_superuser | f | t | f | f | f | f | f | -1 | | (1 row) ALTER ROLE regress_test_superuser WITH SUPERUSER; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_superuser'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil ------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- regress_test_superuser | t | t | f | f | f | f | f | -1 | | @@ -29,28 +29,28 @@ SELECT * FROM pg_authid WHERE rolname = 'regress_test_superuser'; -- default for inherit is true CREATE ROLE regress_test_def_inherit; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_def_inherit'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_inherit'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil --------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- regress_test_def_inherit | f | t | f | f | f | f | f | -1 | | (1 row) CREATE ROLE regress_test_inherit WITH NOINHERIT; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_inherit'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil ----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- regress_test_inherit | f | f | f | f | f | f | f | -1 | | (1 row) ALTER ROLE regress_test_inherit WITH INHERIT; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_inherit'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil ----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- regress_test_inherit | f | t | f | f | f | f | f | -1 | | (1 row) ALTER ROLE regress_test_inherit WITH NOINHERIT; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_inherit'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil ----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- regress_test_inherit | f | f | f | f | f | f | f | -1 | | @@ -58,28 +58,28 @@ SELECT * FROM pg_authid WHERE rolname = 'regress_test_inherit'; -- default for create role is false CREATE ROLE regress_test_def_createrole; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_def_createrole'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_createrole'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil -----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- regress_test_def_createrole | f | t | f | f | f | f | f | -1 | | (1 row) CREATE ROLE regress_test_createrole WITH CREATEROLE; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_createrole'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil -------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- regress_test_createrole | f | t | t | f | f | f | f | -1 | | (1 row) ALTER ROLE regress_test_createrole WITH NOCREATEROLE; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_createrole'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil -------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- regress_test_createrole | f | t | f | f | f | f | f | -1 | | (1 row) ALTER ROLE regress_test_createrole WITH CREATEROLE; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_createrole'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil -------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- regress_test_createrole | f | t | t | f | f | f | f | -1 | | @@ -87,28 +87,28 @@ SELECT * FROM pg_authid WHERE rolname = 'regress_test_createrole'; -- default for create database is false CREATE ROLE regress_test_def_createdb; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_def_createdb'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_createdb'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil ---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- regress_test_def_createdb | f | t | f | f | f | f | f | -1 | | (1 row) CREATE ROLE regress_test_createdb WITH CREATEDB; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_createdb'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil -----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- regress_test_createdb | f | t | f | t | f | f | f | -1 | | (1 row) ALTER ROLE regress_test_createdb WITH NOCREATEDB; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_createdb'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil -----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- regress_test_createdb | f | t | f | f | f | f | f | -1 | | (1 row) ALTER ROLE regress_test_createdb WITH CREATEDB; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_createdb'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil -----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- regress_test_createdb | f | t | f | t | f | f | f | -1 | | @@ -116,28 +116,28 @@ SELECT * FROM pg_authid WHERE rolname = 'regress_test_createdb'; -- default for can login is false for role CREATE ROLE regress_test_def_role_canlogin; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_def_role_canlogin'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_role_canlogin'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil --------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- regress_test_def_role_canlogin | f | t | f | f | f | f | f | -1 | | (1 row) CREATE ROLE regress_test_role_canlogin WITH LOGIN; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_role_canlogin'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil ----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- regress_test_role_canlogin | f | t | f | f | t | f | f | -1 | | (1 row) ALTER ROLE regress_test_role_canlogin WITH NOLOGIN; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_role_canlogin'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil ----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- regress_test_role_canlogin | f | t | f | f | f | f | f | -1 | | (1 row) ALTER ROLE regress_test_role_canlogin WITH LOGIN; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_role_canlogin'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil ----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- regress_test_role_canlogin | f | t | f | f | t | f | f | -1 | | @@ -145,28 +145,28 @@ SELECT * FROM pg_authid WHERE rolname = 'regress_test_role_canlogin'; -- default for can login is true for user CREATE USER regress_test_def_user_canlogin; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_def_user_canlogin'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_user_canlogin'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil --------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- regress_test_def_user_canlogin | f | t | f | f | t | f | f | -1 | | (1 row) CREATE USER regress_test_user_canlogin WITH NOLOGIN; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_user_canlogin'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil ----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- regress_test_user_canlogin | f | t | f | f | f | f | f | -1 | | (1 row) ALTER USER regress_test_user_canlogin WITH LOGIN; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_user_canlogin'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil ----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- regress_test_user_canlogin | f | t | f | f | t | f | f | -1 | | (1 row) ALTER USER regress_test_user_canlogin WITH NOLOGIN; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_user_canlogin'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil ----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- regress_test_user_canlogin | f | t | f | f | f | f | f | -1 | | @@ -174,28 +174,28 @@ SELECT * FROM pg_authid WHERE rolname = 'regress_test_user_canlogin'; -- default for replication is false CREATE ROLE regress_test_def_replication; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_def_replication'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_replication'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil ------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- regress_test_def_replication | f | t | f | f | f | f | f | -1 | | (1 row) CREATE ROLE regress_test_replication WITH REPLICATION; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_replication'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil --------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- regress_test_replication | f | t | f | f | f | t | f | -1 | | (1 row) ALTER ROLE regress_test_replication WITH NOREPLICATION; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_replication'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil --------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- regress_test_replication | f | t | f | f | f | f | f | -1 | | (1 row) ALTER ROLE regress_test_replication WITH REPLICATION; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_replication'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil --------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- regress_test_replication | f | t | f | f | f | t | f | -1 | | @@ -203,28 +203,28 @@ SELECT * FROM pg_authid WHERE rolname = 'regress_test_replication'; -- default for bypassrls is false CREATE ROLE regress_test_def_bypassrls; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_def_bypassrls'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_bypassrls'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil ----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- regress_test_def_bypassrls | f | t | f | f | f | f | f | -1 | | (1 row) CREATE ROLE regress_test_bypassrls WITH BYPASSRLS; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_bypassrls'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil ------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- regress_test_bypassrls | f | t | f | f | f | f | t | -1 | | (1 row) ALTER ROLE regress_test_bypassrls WITH NOBYPASSRLS; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_bypassrls'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil ------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- regress_test_bypassrls | f | t | f | f | f | f | f | -1 | | (1 row) ALTER ROLE regress_test_bypassrls WITH BYPASSRLS; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_bypassrls'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil ------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- regress_test_bypassrls | f | t | f | f | f | f | t | -1 | | diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out index 6f77f5c8a46..1d12b01068b 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -632,30 +632,30 @@ SELECT * FROM category; -- SET SESSION AUTHORIZATION regress_rls_alice; SET row_security TO ON; -CREATE TABLE t1 (a int, junk1 text, b text) WITH OIDS; +CREATE TABLE t1 (id int not null primary key, a int, junk1 text, b text); ALTER TABLE t1 DROP COLUMN junk1; -- just a disturbing factor GRANT ALL ON t1 TO public; -COPY t1 FROM stdin WITH (oids); +COPY t1 FROM stdin WITH ; CREATE TABLE t2 (c float) INHERITS (t1); GRANT ALL ON t2 TO public; -COPY t2 FROM stdin WITH (oids); -CREATE TABLE t3 (c text, b text, a int) WITH OIDS; +COPY t2 FROM stdin; +CREATE TABLE t3 (id int not null primary key, c text, b text, a int); ALTER TABLE t3 INHERIT t1; GRANT ALL ON t3 TO public; -COPY t3(a,b,c) FROM stdin WITH (oids); +COPY t3(id, a,b,c) FROM stdin; CREATE POLICY p1 ON t1 FOR ALL TO PUBLIC USING (a % 2 = 0); -- be even number CREATE POLICY p2 ON t2 FOR ALL TO PUBLIC USING (a % 2 = 1); -- be odd number ALTER TABLE t1 ENABLE ROW LEVEL SECURITY; ALTER TABLE t2 ENABLE ROW LEVEL SECURITY; SET SESSION AUTHORIZATION regress_rls_bob; SELECT * FROM t1; - a | b ----+----- - 2 | bbb - 4 | dad - 2 | bcd - 4 | def - 2 | yyy + id | a | b +-----+---+----- + 102 | 2 | bbb + 104 | 4 | dad + 202 | 2 | bcd + 204 | 4 | def + 302 | 2 | yyy (5 rows) EXPLAIN (COSTS OFF) SELECT * FROM t1; @@ -676,13 +676,13 @@ NOTICE: f_leak => dad NOTICE: f_leak => bcd NOTICE: f_leak => def NOTICE: f_leak => yyy - a | b ----+----- - 2 | bbb - 4 | dad - 2 | bcd - 4 | def - 2 | yyy + id | a | b +-----+---+----- + 102 | 2 | bbb + 104 | 4 | dad + 202 | 2 | bcd + 204 | 4 | def + 302 | 2 | yyy (5 rows) EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b); @@ -698,14 +698,14 @@ EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b); (7 rows) -- reference to system column -SELECT oid, * FROM t1; - oid | a | b ------+---+----- - 102 | 2 | bbb - 104 | 4 | dad - 202 | 2 | bcd - 204 | 4 | def - 302 | 2 | yyy +SELECT tableoid::regclass, * FROM t1; + tableoid | id | a | b +----------+-----+---+----- + t1 | 102 | 2 | bbb + t1 | 104 | 4 | dad + t2 | 202 | 2 | bcd + t2 | 204 | 4 | def + t3 | 302 | 2 | yyy (5 rows) EXPLAIN (COSTS OFF) SELECT *, t1 FROM t1; @@ -722,13 +722,13 @@ EXPLAIN (COSTS OFF) SELECT *, t1 FROM t1; -- reference to whole-row reference SELECT *, t1 FROM t1; - a | b | t1 ----+-----+--------- - 2 | bbb | (2,bbb) - 4 | dad | (4,dad) - 2 | bcd | (2,bcd) - 4 | def | (4,def) - 2 | yyy | (2,yyy) + id | a | b | t1 +-----+---+-----+------------- + 102 | 2 | bbb | (102,2,bbb) + 104 | 4 | dad | (104,4,dad) + 202 | 2 | bcd | (202,2,bcd) + 204 | 4 | def | (204,4,def) + 302 | 2 | yyy | (302,2,yyy) (5 rows) EXPLAIN (COSTS OFF) SELECT *, t1 FROM t1; @@ -745,13 +745,13 @@ EXPLAIN (COSTS OFF) SELECT *, t1 FROM t1; -- for share/update lock SELECT * FROM t1 FOR SHARE; - a | b ----+----- - 2 | bbb - 4 | dad - 2 | bcd - 4 | def - 2 | yyy + id | a | b +-----+---+----- + 102 | 2 | bbb + 104 | 4 | dad + 202 | 2 | bcd + 204 | 4 | def + 302 | 2 | yyy (5 rows) EXPLAIN (COSTS OFF) SELECT * FROM t1 FOR SHARE; @@ -773,13 +773,13 @@ NOTICE: f_leak => dad NOTICE: f_leak => bcd NOTICE: f_leak => def NOTICE: f_leak => yyy - a | b ----+----- - 2 | bbb - 4 | dad - 2 | bcd - 4 | def - 2 | yyy + id | a | b +-----+---+----- + 102 | 2 | bbb + 104 | 4 | dad + 202 | 2 | bcd + 204 | 4 | def + 302 | 2 | yyy (5 rows) EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b) FOR SHARE; @@ -796,17 +796,17 @@ EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b) FOR SHARE; (8 rows) -- union all query -SELECT a, b, oid FROM t2 UNION ALL SELECT a, b, oid FROM t3; - a | b | oid ----+-----+----- - 1 | abc | 201 - 3 | cde | 203 - 1 | xxx | 301 - 2 | yyy | 302 - 3 | zzz | 303 +SELECT a, b, tableoid::regclass FROM t2 UNION ALL SELECT a, b, tableoid::regclass FROM t3; + a | b | tableoid +---+-----+---------- + 1 | abc | t2 + 3 | cde | t2 + 1 | xxx | t3 + 2 | yyy | t3 + 3 | zzz | t3 (5 rows) -EXPLAIN (COSTS OFF) SELECT a, b, oid FROM t2 UNION ALL SELECT a, b, oid FROM t3; +EXPLAIN (COSTS OFF) SELECT a, b, tableoid::regclass FROM t2 UNION ALL SELECT a, b, tableoid::regclass FROM t3; QUERY PLAN ------------------------------- Append @@ -830,19 +830,19 @@ NOTICE: f_leak => def NOTICE: f_leak => xxx NOTICE: f_leak => yyy NOTICE: f_leak => zzz - a | b ----+----- - 1 | aba - 2 | bbb - 3 | ccc - 4 | dad - 1 | abc - 2 | bcd - 3 | cde - 4 | def - 1 | xxx - 2 | yyy - 3 | zzz + id | a | b +-----+---+----- + 101 | 1 | aba + 102 | 2 | bbb + 103 | 3 | ccc + 104 | 4 | dad + 201 | 1 | abc + 202 | 2 | bcd + 203 | 3 | cde + 204 | 4 | def + 301 | 1 | xxx + 302 | 2 | yyy + 303 | 3 | zzz (11 rows) EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b); @@ -872,19 +872,19 @@ NOTICE: f_leak => def NOTICE: f_leak => xxx NOTICE: f_leak => yyy NOTICE: f_leak => zzz - a | b ----+----- - 1 | aba - 2 | bbb - 3 | ccc - 4 | dad - 1 | abc - 2 | bcd - 3 | cde - 4 | def - 1 | xxx - 2 | yyy - 3 | zzz + id | a | b +-----+---+----- + 101 | 1 | aba + 102 | 2 | bbb + 103 | 3 | ccc + 104 | 4 | dad + 201 | 1 | abc + 202 | 2 | bcd + 203 | 3 | cde + 204 | 4 | def + 301 | 1 | xxx + 302 | 2 | yyy + 303 | 3 | zzz (11 rows) EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b); @@ -1500,11 +1500,11 @@ ERROR: infinite recursion detected in policy for relation "s1" -- prepared statement with regress_rls_alice privilege PREPARE p1(int) AS SELECT * FROM t1 WHERE a <= $1; EXECUTE p1(2); - a | b ----+----- - 2 | bbb - 2 | bcd - 2 | yyy + id | a | b +-----+---+----- + 102 | 2 | bbb + 202 | 2 | bcd + 302 | 2 | yyy (3 rows) EXPLAIN (COSTS OFF) EXECUTE p1(2); @@ -1534,19 +1534,19 @@ NOTICE: f_leak => def NOTICE: f_leak => xxx NOTICE: f_leak => yyy NOTICE: f_leak => zzz - a | b ----+----- - 1 | aba - 2 | bbb - 3 | ccc - 4 | dad - 1 | abc - 2 | bcd - 3 | cde - 4 | def - 1 | xxx - 2 | yyy - 3 | zzz + id | a | b +-----+---+----- + 101 | 1 | aba + 102 | 2 | bbb + 103 | 3 | ccc + 104 | 4 | dad + 201 | 1 | abc + 202 | 2 | bcd + 203 | 3 | cde + 204 | 4 | def + 301 | 1 | xxx + 302 | 2 | yyy + 303 | 3 | zzz (11 rows) EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b); @@ -1563,14 +1563,14 @@ EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b); -- plan cache should be invalidated EXECUTE p1(2); - a | b ----+----- - 1 | aba - 2 | bbb - 1 | abc - 2 | bcd - 1 | xxx - 2 | yyy + id | a | b +-----+---+----- + 101 | 1 | aba + 102 | 2 | bbb + 201 | 1 | abc + 202 | 2 | bcd + 301 | 1 | xxx + 302 | 2 | yyy (6 rows) EXPLAIN (COSTS OFF) EXECUTE p1(2); @@ -1587,11 +1587,11 @@ EXPLAIN (COSTS OFF) EXECUTE p1(2); PREPARE p2(int) AS SELECT * FROM t1 WHERE a = $1; EXECUTE p2(2); - a | b ----+----- - 2 | bbb - 2 | bcd - 2 | yyy + id | a | b +-----+---+----- + 102 | 2 | bbb + 202 | 2 | bcd + 302 | 2 | yyy (3 rows) EXPLAIN (COSTS OFF) EXECUTE p2(2); @@ -1610,11 +1610,11 @@ EXPLAIN (COSTS OFF) EXECUTE p2(2); SET SESSION AUTHORIZATION regress_rls_bob; SET row_security TO ON; EXECUTE p2(2); - a | b ----+----- - 2 | bbb - 2 | bcd - 2 | yyy + id | a | b +-----+---+----- + 102 | 2 | bbb + 202 | 2 | bcd + 302 | 2 | yyy (3 rows) EXPLAIN (COSTS OFF) EXECUTE p2(2); @@ -1666,13 +1666,13 @@ UPDATE only t1 SET b = b || '_updt' WHERE f_leak(b); NOTICE: f_leak => bbbbbb NOTICE: f_leak => daddad -- returning clause with system column -UPDATE only t1 SET b = b WHERE f_leak(b) RETURNING oid, *, t1; +UPDATE only t1 SET b = b WHERE f_leak(b) RETURNING tableoid::regclass, *, t1; NOTICE: f_leak => bbbbbb_updt NOTICE: f_leak => daddad_updt - oid | a | b | t1 ------+---+-------------+----------------- - 102 | 2 | bbbbbb_updt | (2,bbbbbb_updt) - 104 | 4 | daddad_updt | (4,daddad_updt) + tableoid | id | a | b | t1 +----------+-----+---+-------------+--------------------- + t1 | 102 | 2 | bbbbbb_updt | (102,2,bbbbbb_updt) + t1 | 104 | 4 | daddad_updt | (104,4,daddad_updt) (2 rows) UPDATE t1 SET b = b WHERE f_leak(b) RETURNING *; @@ -1681,28 +1681,28 @@ NOTICE: f_leak => daddad_updt NOTICE: f_leak => bcdbcd NOTICE: f_leak => defdef NOTICE: f_leak => yyyyyy - a | b ----+------------- - 2 | bbbbbb_updt - 4 | daddad_updt - 2 | bcdbcd - 4 | defdef - 2 | yyyyyy + id | a | b +-----+---+------------- + 102 | 2 | bbbbbb_updt + 104 | 4 | daddad_updt + 202 | 2 | bcdbcd + 204 | 4 | defdef + 302 | 2 | yyyyyy (5 rows) -UPDATE t1 SET b = b WHERE f_leak(b) RETURNING oid, *, t1; +UPDATE t1 SET b = b WHERE f_leak(b) RETURNING tableoid::regclass, *, t1; NOTICE: f_leak => bbbbbb_updt NOTICE: f_leak => daddad_updt NOTICE: f_leak => bcdbcd NOTICE: f_leak => defdef NOTICE: f_leak => yyyyyy - oid | a | b | t1 ------+---+-------------+----------------- - 102 | 2 | bbbbbb_updt | (2,bbbbbb_updt) - 104 | 4 | daddad_updt | (4,daddad_updt) - 202 | 2 | bcdbcd | (2,bcdbcd) - 204 | 4 | defdef | (4,defdef) - 302 | 2 | yyyyyy | (2,yyyyyy) + tableoid | id | a | b | t1 +----------+-----+---+-------------+--------------------- + t1 | 102 | 2 | bbbbbb_updt | (102,2,bbbbbb_updt) + t1 | 104 | 4 | daddad_updt | (104,4,daddad_updt) + t2 | 202 | 2 | bcdbcd | (202,2,bcdbcd) + t2 | 204 | 4 | defdef | (204,4,defdef) + t3 | 302 | 2 | yyyyyy | (302,2,yyyyyy) (5 rows) -- updates with from clause @@ -1789,9 +1789,9 @@ WHERE t2_1.a = 3 AND t2_2.a = t2_1.a AND t2_2.b = t2_1.b AND f_leak(t2_1.b) AND f_leak(t2_2.b) RETURNING *, t2_1, t2_2; NOTICE: f_leak => cde NOTICE: f_leak => cde - a | b | c | a | b | c | t2_1 | t2_2 ----+-----+-----+---+-----+-----+-------------+------------- - 3 | cde | 3.3 | 3 | cde | 3.3 | (3,cde,3.3) | (3,cde,3.3) + id | a | b | c | id | a | b | c | t2_1 | t2_2 +-----+---+-----+-----+-----+---+-----+-----+-----------------+----------------- + 203 | 3 | cde | 3.3 | 203 | 3 | cde | 3.3 | (203,3,cde,3.3) | (203,3,cde,3.3) (1 row) EXPLAIN (COSTS OFF) UPDATE t1 t1_1 SET b = t1_2.b FROM t1 t1_2 @@ -1847,28 +1847,28 @@ NOTICE: f_leak => defdef NOTICE: f_leak => defdef NOTICE: f_leak => daddad_updt NOTICE: f_leak => defdef - a | b | a | b | t1_1 | t1_2 ----+-------------+---+-------------+-----------------+----------------- - 4 | daddad_updt | 4 | daddad_updt | (4,daddad_updt) | (4,daddad_updt) - 4 | defdef | 4 | defdef | (4,defdef) | (4,defdef) + id | a | b | id | a | b | t1_1 | t1_2 +-----+---+-------------+-----+---+-------------+---------------------+--------------------- + 104 | 4 | daddad_updt | 104 | 4 | daddad_updt | (104,4,daddad_updt) | (104,4,daddad_updt) + 204 | 4 | defdef | 204 | 4 | defdef | (204,4,defdef) | (204,4,defdef) (2 rows) RESET SESSION AUTHORIZATION; SET row_security TO OFF; SELECT * FROM t1 ORDER BY a,b; - a | b ----+------------- - 1 | aba - 1 | abc - 1 | xxx - 2 | bbbbbb_updt - 2 | bcdbcd - 2 | yyyyyy - 3 | ccc - 3 | cde - 3 | zzz - 4 | daddad_updt - 4 | defdef + id | a | b +-----+---+------------- + 101 | 1 | aba + 201 | 1 | abc + 301 | 1 | xxx + 102 | 2 | bbbbbb_updt + 202 | 2 | bcdbcd + 302 | 2 | yyyyyy + 103 | 3 | ccc + 203 | 3 | cde + 303 | 3 | zzz + 104 | 4 | daddad_updt + 204 | 4 | defdef (11 rows) SET SESSION AUTHORIZATION regress_rls_bob; @@ -1896,24 +1896,24 @@ EXPLAIN (COSTS OFF) DELETE FROM t1 WHERE f_leak(b); Filter: (((a % 2) = 0) AND f_leak(b)) (10 rows) -DELETE FROM only t1 WHERE f_leak(b) RETURNING oid, *, t1; +DELETE FROM only t1 WHERE f_leak(b) RETURNING tableoid::regclass, *, t1; NOTICE: f_leak => bbbbbb_updt NOTICE: f_leak => daddad_updt - oid | a | b | t1 ------+---+-------------+----------------- - 102 | 2 | bbbbbb_updt | (2,bbbbbb_updt) - 104 | 4 | daddad_updt | (4,daddad_updt) + tableoid | id | a | b | t1 +----------+-----+---+-------------+--------------------- + t1 | 102 | 2 | bbbbbb_updt | (102,2,bbbbbb_updt) + t1 | 104 | 4 | daddad_updt | (104,4,daddad_updt) (2 rows) -DELETE FROM t1 WHERE f_leak(b) RETURNING oid, *, t1; +DELETE FROM t1 WHERE f_leak(b) RETURNING tableoid::regclass, *, t1; NOTICE: f_leak => bcdbcd NOTICE: f_leak => defdef NOTICE: f_leak => yyyyyy - oid | a | b | t1 ------+---+--------+------------ - 202 | 2 | bcdbcd | (2,bcdbcd) - 204 | 4 | defdef | (4,defdef) - 302 | 2 | yyyyyy | (2,yyyyyy) + tableoid | id | a | b | t1 +----------+-----+---+--------+---------------- + t2 | 202 | 2 | bcdbcd | (202,2,bcdbcd) + t2 | 204 | 4 | defdef | (204,4,defdef) + t3 | 302 | 2 | yyyyyy | (302,2,yyyyyy) (3 rows) -- diff --git a/src/test/regress/expected/rowtypes.out b/src/test/regress/expected/rowtypes.out index 30053d07dff..d6a1a3331e3 100644 --- a/src/test/regress/expected/rowtypes.out +++ b/src/test/regress/expected/rowtypes.out @@ -1067,7 +1067,7 @@ select r, r is null as isnull, r is not null as isnotnull from r; -- -- Tests for component access / FieldSelect -- -CREATE TABLE compositetable(a text, b text) WITH OIDS; +CREATE TABLE compositetable(a text, b text); INSERT INTO compositetable(a, b) VALUES('fa', 'fb'); -- composite type columns can't directly be accessed (error) SELECT d.a FROM (SELECT compositetable AS d FROM compositetable) s; @@ -1081,10 +1081,10 @@ SELECT (d).a, (d).b FROM (SELECT compositetable AS d FROM compositetable) s; fa | fb (1 row) --- oids can't be accessed in composite types (error) -SELECT (d).oid FROM (SELECT compositetable AS d FROM compositetable) s; -ERROR: column "oid" not found in data type compositetable -LINE 1: SELECT (d).oid FROM (SELECT compositetable AS d FROM composi... +-- system columns can't be accessed in composite types (error) +SELECT (d).ctid FROM (SELECT compositetable AS d FROM compositetable) s; +ERROR: column "ctid" not found in data type compositetable +LINE 1: SELECT (d).ctid FROM (SELECT compositetable AS d FROM compos... ^ -- accessing non-existing column in NULL datum errors out SELECT (NULL::compositetable).nonexistant; diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out index 9c7a60c092d..c77060d36c1 100644 --- a/src/test/regress/expected/sanity_check.out +++ b/src/test/regress/expected/sanity_check.out @@ -213,12 +213,12 @@ varchar_tbl|f -- We exclude non-system tables from the check by looking at nspname. -- SELECT relname, nspname -FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = relnamespace -WHERE relhasoids - AND ((nspname ~ '^pg_') IS NOT FALSE) - AND NOT EXISTS (SELECT 1 FROM pg_index i WHERE indrelid = c.oid - AND indkey[0] = -2 AND indnatts = 1 - AND indisunique AND indimmediate); + FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = relnamespace JOIN pg_attribute a ON (attrelid = c.oid AND attname = 'oid') + WHERE relkind = 'r' and c.oid < 16384 + AND ((nspname ~ '^pg_') IS NOT FALSE) + AND NOT EXISTS (SELECT 1 FROM pg_index i WHERE indrelid = c.oid + AND indkey[0] = a.attnum AND indnatts = 1 + AND indisunique AND indimmediate); relname | nspname ---------+--------- (0 rows) diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out index 7d59de98ebd..c62f88c169a 100644 --- a/src/test/regress/expected/triggers.out +++ b/src/test/regress/expected/triggers.out @@ -441,11 +441,11 @@ DROP TRIGGER delete_a ON main_table; DROP TRIGGER insert_when ON main_table; DROP TRIGGER delete_when ON main_table; -- Test WHEN condition accessing system columns. -create table table_with_oids(a int) with oids; +create table table_with_oids(a int); insert into table_with_oids values (1); create trigger oid_unchanged_trig after update on table_with_oids for each row - when (new.oid = old.oid AND new.oid <> 0) + when (new.tableoid = old.tableoid AND new.tableoid <> 0) execute procedure trigger_func('after_upd_oid_unchanged'); update table_with_oids set a = a + 1; NOTICE: trigger_func(after_upd_oid_unchanged) called: action = UPDATE, when = AFTER, level = ROW @@ -834,18 +834,10 @@ CREATE TABLE min_updates_test ( f1 text, f2 int, f3 int); -CREATE TABLE min_updates_test_oids ( - f1 text, - f2 int, - f3 int) WITH OIDS; INSERT INTO min_updates_test VALUES ('a',1,2),('b','2',null); -INSERT INTO min_updates_test_oids VALUES ('a',1,2),('b','2',null); CREATE TRIGGER z_min_update BEFORE UPDATE ON min_updates_test FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger(); -CREATE TRIGGER z_min_update -BEFORE UPDATE ON min_updates_test_oids -FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger(); \set QUIET false UPDATE min_updates_test SET f1 = f1; UPDATE 0 @@ -853,12 +845,6 @@ UPDATE min_updates_test SET f2 = f2 + 1; UPDATE 2 UPDATE min_updates_test SET f3 = 2 WHERE f3 is null; UPDATE 1 -UPDATE min_updates_test_oids SET f1 = f1; -UPDATE 0 -UPDATE min_updates_test_oids SET f2 = f2 + 1; -UPDATE 2 -UPDATE min_updates_test_oids SET f3 = 2 WHERE f3 is null; -UPDATE 1 \set QUIET true SELECT * FROM min_updates_test; f1 | f2 | f3 @@ -867,15 +853,7 @@ SELECT * FROM min_updates_test; b | 3 | 2 (2 rows) -SELECT * FROM min_updates_test_oids; - f1 | f2 | f3 -----+----+---- - a | 2 | 2 - b | 3 | 2 -(2 rows) - DROP TABLE min_updates_test; -DROP TABLE min_updates_test_oids; -- -- Test triggers on views -- diff --git a/src/test/regress/expected/without_oid.out b/src/test/regress/expected/without_oid.out deleted file mode 100644 index cb2c0c01371..00000000000 --- a/src/test/regress/expected/without_oid.out +++ /dev/null @@ -1,103 +0,0 @@ --- --- WITHOUT OID --- --- --- This test tries to verify that WITHOUT OIDS actually saves space. --- On machines where MAXALIGN is 8, WITHOUT OIDS may or may not save any --- space, depending on the size of the tuple header + null bitmap. --- As of 8.3 we need a null bitmap of 8 or less bits for the difference --- to appear. --- -CREATE TABLE wi (i INT, - n1 int, n2 int, n3 int, n4 int, - n5 int, n6 int, n7 int) WITH OIDS; -CREATE TABLE wo (i INT, - n1 int, n2 int, n3 int, n4 int, - n5 int, n6 int, n7 int) WITHOUT OIDS; -INSERT INTO wi VALUES (1); -- 1 -INSERT INTO wo SELECT i FROM wi; -- 1 -INSERT INTO wo SELECT i+1 FROM wi; -- 1+1=2 -INSERT INTO wi SELECT i+1 FROM wo; -- 1+2=3 -INSERT INTO wi SELECT i+3 FROM wi; -- 3+3=6 -INSERT INTO wo SELECT i+2 FROM wi; -- 2+6=8 -INSERT INTO wo SELECT i+8 FROM wo; -- 8+8=16 -INSERT INTO wi SELECT i+6 FROM wo; -- 6+16=22 -INSERT INTO wi SELECT i+22 FROM wi; -- 22+22=44 -INSERT INTO wo SELECT i+16 FROM wi; -- 16+44=60 -INSERT INTO wo SELECT i+60 FROM wo; -- 60+60=120 -INSERT INTO wi SELECT i+44 FROM wo; -- 44+120=164 -INSERT INTO wi SELECT i+164 FROM wi; -- 164+164=328 -INSERT INTO wo SELECT i+120 FROM wi; -- 120+328=448 -INSERT INTO wo SELECT i+448 FROM wo; -- 448+448=896 -INSERT INTO wi SELECT i+328 FROM wo; -- 328+896=1224 -INSERT INTO wi SELECT i+1224 FROM wi; -- 1224+1224=2448 -INSERT INTO wo SELECT i+896 FROM wi; -- 896+2448=3344 -INSERT INTO wo SELECT i+3344 FROM wo; -- 3344+3344=6688 -INSERT INTO wi SELECT i+2448 FROM wo; -- 2448+6688=9136 -INSERT INTO wo SELECT i+6688 FROM wi WHERE i<=2448; -- 6688+2448=9136 -SELECT count(oid) FROM wi; - count -------- - 9136 -(1 row) - --- should fail -SELECT count(oid) FROM wo; -ERROR: column "oid" does not exist -LINE 1: SELECT count(oid) FROM wo; - ^ -VACUUM ANALYZE wi; -VACUUM ANALYZE wo; -SELECT min(relpages) < max(relpages), min(reltuples) - max(reltuples) - FROM pg_class - WHERE relname IN ('wi', 'wo'); - ?column? | ?column? -----------+---------- - t | 0 -(1 row) - -DROP TABLE wi; -DROP TABLE wo; --- --- WITH / WITHOUT OIDS in CREATE TABLE AS --- -CREATE TABLE create_table_test ( - a int, - b int -); -COPY create_table_test FROM stdin; -CREATE TABLE create_table_test2 WITH OIDS AS - SELECT a + b AS c1, a - b AS c2 FROM create_table_test; -CREATE TABLE create_table_test3 WITHOUT OIDS AS - SELECT a + b AS c1, a - b AS c2 FROM create_table_test; -SELECT count(oid) FROM create_table_test2; - count -------- - 2 -(1 row) - --- should fail -SELECT count(oid) FROM create_table_test3; -ERROR: column "oid" does not exist -LINE 1: SELECT count(oid) FROM create_table_test3; - ^ -PREPARE table_source(int) AS - SELECT a + b AS c1, a - b AS c2, $1 AS c3 FROM create_table_test; -CREATE TABLE execute_with WITH OIDS AS EXECUTE table_source(1); -CREATE TABLE execute_without WITHOUT OIDS AS EXECUTE table_source(2); -SELECT count(oid) FROM execute_with; - count -------- - 2 -(1 row) - --- should fail -SELECT count(oid) FROM execute_without; -ERROR: column "oid" does not exist -LINE 1: SELECT count(oid) FROM execute_without; - ^ -DROP TABLE create_table_test; -DROP TABLE create_table_test2; -DROP TABLE create_table_test3; -DROP TABLE execute_with; -DROP TABLE execute_without; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 289c658483c..cc0bbf5db9f 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -111,7 +111,7 @@ test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combo # NB: temp.sql does a reconnect which transiently uses 2 connections, # so keep this parallel group to at most 19 tests # ---------- -test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml +test: plancache limit plpgsql copy2 temp domain rangefuncs prepare conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml # ---------- # Another group of parallel tests diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index bc43b18c628..0c10c7100c6 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -166,7 +166,6 @@ test: temp test: domain test: rangefuncs test: prepare -test: without_oid test: conversion test: truncate test: alter_table diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index 0352536fa5a..137f2d8ed03 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -626,7 +626,7 @@ drop table atacc1; -- test unique constraint adding -create table atacc1 ( test int ) with oids; +create table atacc1 ( test int ) ; -- add a unique constraint alter table atacc1 add constraint atacc_test1 unique (test); -- insert first value @@ -635,8 +635,6 @@ insert into atacc1 (test) values (2); insert into atacc1 (test) values (2); -- should succeed insert into atacc1 (test) values (4); --- try adding a unique oid constraint -alter table atacc1 add constraint atacc_oid1 unique(oid); -- try to create duplicates via alter table using - should fail alter table atacc1 alter column test type integer using 0; drop table atacc1; @@ -682,7 +680,7 @@ drop table atacc1; -- test primary key constraint adding -create table atacc1 ( test int ) with oids; +create table atacc1 ( id serial, test int) ; -- add a primary key constraint alter table atacc1 add constraint atacc_test1 primary key (test); -- insert first value @@ -694,11 +692,11 @@ insert into atacc1 (test) values (4); -- inserting NULL should fail insert into atacc1 (test) values(NULL); -- try adding a second primary key (should fail) -alter table atacc1 add constraint atacc_oid1 primary key(oid); +alter table atacc1 add constraint atacc_oid1 primary key(id); -- drop first primary key constraint alter table atacc1 drop constraint atacc_test1 restrict; -- try adding a primary key on oid (should succeed) -alter table atacc1 add constraint atacc_oid1 primary key(oid); +alter table atacc1 add constraint atacc_oid1 primary key(id); drop table atacc1; -- let's do one where the primary key constraint fails when added @@ -775,7 +773,7 @@ alter table non_existent alter column bar drop not null; -- test setting columns to null and not null and vice versa -- test checking for null values and primary key -create table atacc1 (test int not null) with oids; +create table atacc1 (test int not null); alter table atacc1 add constraint "atacc1_pkey" primary key (test); alter table atacc1 alter column test drop not null; alter table atacc1 drop constraint "atacc1_pkey"; @@ -789,10 +787,6 @@ alter table atacc1 alter test set not null; alter table atacc1 alter bar set not null; alter table atacc1 alter bar drop not null; --- try altering the oid column, should fail -alter table atacc1 alter oid set not null; -alter table atacc1 alter oid drop not null; - -- try creating a view and altering that, should fail create view myview as select * from atacc1; alter table myview alter column test drop not null; @@ -872,7 +866,7 @@ alter table pg_class drop column relname; alter table nosuchtable drop column bar; -- test dropping columns -create table atacc1 (a int4 not null, b int4, c int4 not null, d int4) with oids; +create table atacc1 (a int4 not null, b int4, c int4 not null, d int4); insert into atacc1 values (1, 2, 3, 4); alter table atacc1 drop a; alter table atacc1 drop a; @@ -922,8 +916,11 @@ delete from atacc1; -- try dropping a non-existent column, should fail alter table atacc1 drop bar; --- try dropping the oid column, should succeed -alter table atacc1 drop oid; +-- try removing an oid column, should succeed (as it's nonexistant) +alter table atacc1 SET WITHOUT OIDS; + +-- try adding an oid column, should fail (not supported) +alter table atacc1 SET WITH OIDS; -- try dropping the xmin column, should fail alter table atacc1 drop xmin; @@ -1183,74 +1180,6 @@ from pg_attribute where attnum > 0 and attrelid::regclass in ('depth0', 'depth1', 'depth2') order by attrelid::regclass::text, attnum; --- --- Test the ALTER TABLE SET WITH/WITHOUT OIDS command --- -create table altstartwith (col integer) with oids; - -insert into altstartwith values (1); - -select oid > 0, * from altstartwith; - -alter table altstartwith set without oids; - -select oid > 0, * from altstartwith; -- fails -select * from altstartwith; - -alter table altstartwith set with oids; - -select oid > 0, * from altstartwith; - -drop table altstartwith; - --- Check inheritance cases -create table altwithoid (col integer) with oids; - --- Inherits parents oid column anyway -create table altinhoid () inherits (altwithoid) without oids; - -insert into altinhoid values (1); - -select oid > 0, * from altwithoid; -select oid > 0, * from altinhoid; - -alter table altwithoid set without oids; - -select oid > 0, * from altwithoid; -- fails -select oid > 0, * from altinhoid; -- fails -select * from altwithoid; -select * from altinhoid; - -alter table altwithoid set with oids; - -select oid > 0, * from altwithoid; -select oid > 0, * from altinhoid; - -drop table altwithoid cascade; - -create table altwithoid (col integer) without oids; - --- child can have local oid column -create table altinhoid () inherits (altwithoid) with oids; - -insert into altinhoid values (1); - -select oid > 0, * from altwithoid; -- fails -select oid > 0, * from altinhoid; - -alter table altwithoid set with oids; - -select oid > 0, * from altwithoid; -select oid > 0, * from altinhoid; - --- the child's local definition should remain -alter table altwithoid set without oids; - -select oid > 0, * from altwithoid; -- fails -select oid > 0, * from altinhoid; - -drop table altwithoid cascade; - -- test renumbering of child-table columns in inherited operations create table p1 (f1 int); @@ -1803,7 +1732,7 @@ CREATE TABLE tt3 (y numeric(8,2), x int); -- wrong column order CREATE TABLE tt4 (x int); -- too few columns CREATE TABLE tt5 (x int, y numeric(8,2), z int); -- too few columns CREATE TABLE tt6 () INHERITS (tt0); -- can't have a parent -CREATE TABLE tt7 (x int, q text, y numeric(8,2)) WITH OIDS; +CREATE TABLE tt7 (x int, q text, y numeric(8,2)); ALTER TABLE tt7 DROP q; -- OK ALTER TABLE tt0 OF tt_t0; @@ -2136,16 +2065,6 @@ CREATE TABLE fail_part OF mytype; ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); DROP TYPE mytype CASCADE; --- check existence (or non-existence) of oid column -ALTER TABLE list_parted SET WITH OIDS; -CREATE TABLE fail_part (a int); -ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); - -ALTER TABLE list_parted SET WITHOUT OIDS; -ALTER TABLE fail_part SET WITH OIDS; -ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); -DROP TABLE fail_part; - -- check that the table being attached has only columns present in the parent CREATE TABLE fail_part (like list_parted, c int); ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql index f3a6d228fae..e36df8858eb 100644 --- a/src/test/regress/sql/copy2.sql +++ b/src/test/regress/sql/copy2.sql @@ -4,7 +4,7 @@ CREATE TEMP TABLE x ( c text not null default 'stuff', d text, e text -) WITH OIDS; +) ; CREATE FUNCTION fn_x_before () RETURNS TRIGGER AS ' BEGIN @@ -73,10 +73,10 @@ COPY x from stdin; \. -- various COPY options: delimiters, oids, NULL string, encoding -COPY x (b, c, d, e) from stdin with oids delimiter ',' null 'x'; -500000,x,45,80,90 -500001,x,\x,\\x,\\\x -500002,x,\,,\\\,,\\ +COPY x (b, c, d, e) from stdin delimiter ',' null 'x'; +x,45,80,90 +x,\x,\\x,\\\x +x,\,,\\\,,\\ \. COPY x from stdin WITH DELIMITER AS ';' NULL AS ''; @@ -98,19 +98,6 @@ COPY x from stdin WITH DELIMITER AS ':' NULL AS E'\\X' ENCODING 'sql_ascii'; -- check results of copy in SELECT * FROM x; --- COPY w/ oids on a table w/o oids should fail -CREATE TABLE no_oids ( - a int, - b int -) WITHOUT OIDS; - -INSERT INTO no_oids (a, b) VALUES (5, 10); -INSERT INTO no_oids (a, b) VALUES (20, 30); - --- should fail -COPY no_oids FROM stdin WITH OIDS; -COPY no_oids TO stdout WITH OIDS; - -- check copy out COPY x TO stdout; COPY x (c, e) TO stdout; diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql index be7f261871e..59da6b65926 100644 --- a/src/test/regress/sql/create_index.sql +++ b/src/test/regress/sql/create_index.sql @@ -951,21 +951,18 @@ DROP TABLE cwi_test; -- -- Check handling of indexes on system columns -- -CREATE TABLE oid_table (a INT) WITH OIDS; +CREATE TABLE syscol_table (a INT); --- An index on the OID column should be allowed -CREATE INDEX ON oid_table (oid); - --- Other system columns cannot be indexed -CREATE INDEX ON oid_table (ctid); +-- System columns cannot be indexed +CREATE INDEX ON syscolcol_table (ctid); -- nor used in expressions -CREATE INDEX ON oid_table ((ctid >= '(1000,0)')); +CREATE INDEX ON syscol_table ((ctid >= '(1000,0)')); -- nor used in predicates -CREATE INDEX ON oid_table (a) WHERE ctid >= '(1000,0)'; +CREATE INDEX ON syscol_table (a) WHERE ctid >= '(1000,0)'; -DROP TABLE oid_table; +DROP TABLE syscol_table; -- -- Tests for IS NULL/IS NOT NULL with b-tree indexes diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql index 2af4455ecf8..90cc1a578f3 100644 --- a/src/test/regress/sql/create_table.sql +++ b/src/test/regress/sql/create_table.sql @@ -51,7 +51,7 @@ CREATE TABLE tenk1 ( stringu1 name, stringu2 name, string4 name -) WITH OIDS; +); CREATE TABLE tenk2 ( unique1 int4, @@ -83,7 +83,7 @@ CREATE TABLE person ( CREATE TABLE emp ( salary int4, manager name -) INHERITS (person) WITH OIDS; +) INHERITS (person); CREATE TABLE student ( @@ -255,7 +255,6 @@ CREATE TABLE IF NOT EXISTS test_tsvector( -- invalid: non-lowercase quoted reloptions identifiers CREATE TABLE tas_case WITH ("Fillfactor" = 10) AS SELECT 1 a; -CREATE TABLE tas_case (a text) WITH ("Oids" = true); CREATE UNLOGGED TABLE unlogged1 (a int primary key); -- OK CREATE TEMPORARY TABLE unlogged2 (a int primary key); -- OK @@ -278,9 +277,14 @@ CREATE TABLE as_select1 AS SELECT * FROM pg_class WHERE relkind = 'r'; CREATE TABLE IF NOT EXISTS as_select1 AS SELECT * FROM pg_class WHERE relkind = 'r'; DROP TABLE as_select1; --- check that the oid column is added before the primary key is checked -CREATE TABLE oid_pk (f1 INT, PRIMARY KEY(oid)) WITH OIDS; -DROP TABLE oid_pk; +-- check that tables with oids cannot be created anymore +CREATE TABLE withoid() WITH OIDS; +CREATE TABLE withoid() WITH (oids); +CREATE TABLE withoid() WITH (oids = true); + +-- but explicitly not adding oids is still supported +CREATE TEMP TABLE withoutoid() WITHOUT OIDS; DROP TABLE withoutoid; +CREATE TEMP TABLE withoutoid() WITH (oids = false); DROP TABLE withoutoid; -- -- Partitioned tables @@ -520,22 +524,6 @@ CREATE TEMP TABLE temp_parted ( CREATE TABLE fail_part PARTITION OF temp_parted FOR VALUES IN ('a'); DROP TABLE temp_parted; --- cannot create a table with oids as partition of table without oids -CREATE TABLE no_oids_parted ( - a int -) PARTITION BY RANGE (a) WITHOUT OIDS; -CREATE TABLE fail_part PARTITION OF no_oids_parted FOR VALUES FROM (1) TO (10) WITH OIDS; -DROP TABLE no_oids_parted; - --- If the partitioned table has oids, then the partition must have them. --- If the WITHOUT OIDS option is specified for partition, it is overridden. -CREATE TABLE oids_parted ( - a int -) PARTITION BY RANGE (a) WITH OIDS; -CREATE TABLE part_forced_oids PARTITION OF oids_parted FOR VALUES FROM (1) TO (10) WITHOUT OIDS; -\d+ part_forced_oids -DROP TABLE oids_parted, part_forced_oids; - -- check for partition bound overlap and other invalid specifications CREATE TABLE list_parted2 ( diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql index 42cad6826b0..65c3880792b 100644 --- a/src/test/regress/sql/create_table_like.sql +++ b/src/test/regress/sql/create_table_like.sql @@ -136,19 +136,3 @@ DROP SEQUENCE ctlseq1; DROP TYPE ctlty1; DROP VIEW ctlv1; DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12; - -/* LIKE WITH OIDS */ -CREATE TABLE has_oid (x INTEGER) WITH OIDS; -CREATE TABLE no_oid (y INTEGER); -CREATE TABLE like_test (z INTEGER, LIKE has_oid); -SELECT oid FROM like_test; -CREATE TABLE like_test2 (z INTEGER, LIKE no_oid); -SELECT oid FROM like_test2; -- fail -CREATE TABLE like_test3 (z INTEGER, LIKE has_oid, LIKE no_oid); -SELECT oid FROM like_test3; -CREATE TABLE like_test4 (z INTEGER, PRIMARY KEY(oid), LIKE has_oid); -SELECT oid FROM like_test4; -CREATE TABLE like_test5 (z INTEGER, LIKE no_oid) WITH OIDS; -SELECT oid FROM like_test5; -DROP TABLE has_oid, no_oid, like_test, like_test2, like_test3, - like_test4, like_test5; diff --git a/src/test/regress/sql/errors.sql b/src/test/regress/sql/errors.sql index 14bc723a52a..792c29c64b5 100644 --- a/src/test/regress/sql/errors.sql +++ b/src/test/regress/sql/errors.sql @@ -91,7 +91,7 @@ alter table emp rename column nonesuchatt to newnonesuchatt; alter table emp rename column salary to manager; -- conflict -alter table emp rename column salary to oid; +alter table emp rename column salary to ctid; -- diff --git a/src/test/regress/sql/foreign_data.sql b/src/test/regress/sql/foreign_data.sql index dab9b62900e..d6fb3fae4e1 100644 --- a/src/test/regress/sql/foreign_data.sql +++ b/src/test/regress/sql/foreign_data.sql @@ -283,7 +283,6 @@ CREATE SCHEMA foreign_schema; CREATE SERVER s0 FOREIGN DATA WRAPPER dummy; CREATE FOREIGN TABLE ft1 (); -- ERROR CREATE FOREIGN TABLE ft1 () SERVER no_server; -- ERROR -CREATE FOREIGN TABLE ft1 () SERVER s0 WITH OIDS; -- ERROR CREATE FOREIGN TABLE ft1 ( c1 integer OPTIONS ("param 1" 'val1') PRIMARY KEY, c2 text OPTIONS (param2 'val2', param3 'val3'), @@ -362,7 +361,6 @@ ALTER FOREIGN TABLE ft1 ALTER CONSTRAINT ft1_c9_check DEFERRABLE; -- ERROR ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c9_check; ALTER FOREIGN TABLE ft1 DROP CONSTRAINT no_const; -- ERROR ALTER FOREIGN TABLE ft1 DROP CONSTRAINT IF EXISTS no_const; -ALTER FOREIGN TABLE ft1 SET WITH OIDS; ALTER FOREIGN TABLE ft1 OWNER TO regress_test_role; ALTER FOREIGN TABLE ft1 OPTIONS (DROP delimiter, SET quote '~', ADD escape '@'); ALTER FOREIGN TABLE ft1 DROP COLUMN no_column; -- ERROR @@ -693,15 +691,6 @@ ALTER TABLE fd_pt1 VALIDATE CONSTRAINT fd_pt1chk3; \d+ fd_pt1 \d+ ft2 --- OID system column -ALTER TABLE fd_pt1 SET WITH OIDS; -\d+ fd_pt1 -\d+ ft2 -ALTER TABLE ft2 SET WITHOUT OIDS; -- ERROR -ALTER TABLE fd_pt1 SET WITHOUT OIDS; -\d+ fd_pt1 -\d+ ft2 - -- changes name of an attribute recursively ALTER TABLE fd_pt1 RENAME COLUMN c1 TO f1; ALTER TABLE fd_pt1 RENAME COLUMN c2 TO f2; diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql index afc72f47bc8..425052c1f45 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -190,32 +190,6 @@ insert into d values('test','one','two','three'); alter table a alter column aa type integer using bit_length(aa); select * from d; --- check that oid column is handled properly during alter table inherit -create table oid_parent (a int) with oids; - -create table oid_child () inherits (oid_parent); -select attinhcount, attislocal from pg_attribute - where attrelid = 'oid_child'::regclass and attname = 'oid'; -drop table oid_child; - -create table oid_child (a int) without oids; -alter table oid_child inherit oid_parent; -- fail -alter table oid_child set with oids; -select attinhcount, attislocal from pg_attribute - where attrelid = 'oid_child'::regclass and attname = 'oid'; -alter table oid_child inherit oid_parent; -select attinhcount, attislocal from pg_attribute - where attrelid = 'oid_child'::regclass and attname = 'oid'; -alter table oid_child set without oids; -- fail -alter table oid_parent set without oids; -select attinhcount, attislocal from pg_attribute - where attrelid = 'oid_child'::regclass and attname = 'oid'; -alter table oid_child set without oids; -select attinhcount, attislocal from pg_attribute - where attrelid = 'oid_child'::regclass and attname = 'oid'; - -drop table oid_parent cascade; - -- Test non-inheritable parent constraints create table p1(ff1 int); alter table p1 add constraint p1chk check (ff1 > 0) no inherit; diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql index fe6dcfaa06b..961cffd2091 100644 --- a/src/test/regress/sql/insert_conflict.sql +++ b/src/test/regress/sql/insert_conflict.sql @@ -253,12 +253,11 @@ drop table insertconflicttest; -- -- Verify that EXCLUDED does not allow system column references. These -- do not make sense because EXCLUDED isn't an already stored tuple --- (and thus doesn't have a ctid, oids are not assigned yet, etc). +-- (and thus doesn't have a ctid etc). -- -create table syscolconflicttest(key int4, data text) WITH OIDS; +create table syscolconflicttest(key int4, data text); insert into syscolconflicttest values (1); insert into syscolconflicttest values (1) on conflict (key) do update set data = excluded.ctid::text; -insert into syscolconflicttest values (1) on conflict (key) do update set data = excluded.oid::text; drop table syscolconflicttest; -- @@ -373,28 +372,6 @@ insert into excluded values(1, '2') on conflict (key) do update set data = 3 RET drop table excluded; --- Check tables w/o oids are handled correctly -create table testoids(key int primary key, data text) without oids; --- first without oids -insert into testoids values(1, '1') on conflict (key) do update set data = excluded.data RETURNING *; -insert into testoids values(1, '2') on conflict (key) do update set data = excluded.data RETURNING *; --- add oids -alter table testoids set with oids; --- update existing row, that didn't have an oid -insert into testoids values(1, '3') on conflict (key) do update set data = excluded.data RETURNING *; --- insert a new row -insert into testoids values(2, '1') on conflict (key) do update set data = excluded.data RETURNING *; --- and update it -insert into testoids values(2, '2') on conflict (key) do update set data = excluded.data RETURNING *; --- remove oids again, test -alter table testoids set without oids; -insert into testoids values(1, '4') on conflict (key) do update set data = excluded.data RETURNING *; -insert into testoids values(3, '1') on conflict (key) do update set data = excluded.data RETURNING *; -insert into testoids values(3, '2') on conflict (key) do update set data = excluded.data RETURNING *; - -DROP TABLE testoids; - - -- check that references to columns after dropped columns are handled correctly create table dropcol(key int primary key, drop1 int, keep1 text, drop2 numeric, keep2 float); insert into dropcol(key, drop1, keep1, drop2, keep2) values(1, 1, '1', '1', 1); diff --git a/src/test/regress/sql/misc_sanity.sql b/src/test/regress/sql/misc_sanity.sql index f2af5e37508..3ce32e47252 100644 --- a/src/test/regress/sql/misc_sanity.sql +++ b/src/test/regress/sql/misc_sanity.sql @@ -54,7 +54,10 @@ declare relnm text; begin for relnm, reloid, shared in select relname, oid, relisshared from pg_class - where relhasoids and oid < 16384 order by 1 + where EXISTS( + SELECT * FROM pg_attribute + WHERE attrelid = pg_class.oid AND attname = 'oid') + and relkind = 'r' and oid < 16384 order by 1 loop execute 'select min(oid) from ' || relnm into lowoid; continue when lowoid is null or lowoid >= 16384; diff --git a/src/test/regress/sql/prepare.sql b/src/test/regress/sql/prepare.sql index 25f814b4667..7fe8c8d7f55 100644 --- a/src/test/regress/sql/prepare.sql +++ b/src/test/regress/sql/prepare.sql @@ -36,21 +36,21 @@ PREPARE q2(text) AS EXECUTE q2('postgres'); -PREPARE q3(text, int, float, boolean, oid, smallint) AS +PREPARE q3(text, int, float, boolean, smallint) AS SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR - ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int) + ten = $3::bigint OR true = $4 OR odd = $5::int) ORDER BY unique1; -EXECUTE q3('AAAAxx', 5::smallint, 10.5::float, false, 500::oid, 4::bigint); +EXECUTE q3('AAAAxx', 5::smallint, 10.5::float, false, 4::bigint); -- too few params EXECUTE q3('bool'); -- too many params -EXECUTE q3('bytea', 5::smallint, 10.5::float, false, 500::oid, 4::bigint, true); +EXECUTE q3('bytea', 5::smallint, 10.5::float, false, 4::bigint, true); -- wrong param types -EXECUTE q3(5::smallint, 10.5::float, false, 500::oid, 4::bigint, 'bytea'); +EXECUTE q3(5::smallint, 10.5::float, false, 4::bigint, 'bytea'); -- invalid type PREPARE q4(nonexistenttype) AS SELECT $1; diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql index e3e69302a25..ac2c3df3a2d 100644 --- a/src/test/regress/sql/privileges.sql +++ b/src/test/regress/sql/privileges.sql @@ -407,27 +407,27 @@ DELETE FROM atest5 WHERE two = 2; -- ok -- check inheritance cases SET SESSION AUTHORIZATION regress_priv_user1; -CREATE TABLE atestp1 (f1 int, f2 int) WITH OIDS; -CREATE TABLE atestp2 (fx int, fy int) WITH OIDS; +CREATE TABLE atestp1 (f1 int, f2 int); +CREATE TABLE atestp2 (fx int, fy int); CREATE TABLE atestc (fz int) INHERITS (atestp1, atestp2); -GRANT SELECT(fx,fy,oid) ON atestp2 TO regress_priv_user2; +GRANT SELECT(fx,fy,tableoid) ON atestp2 TO regress_priv_user2; GRANT SELECT(fx) ON atestc TO regress_priv_user2; SET SESSION AUTHORIZATION regress_priv_user2; SELECT fx FROM atestp2; -- ok SELECT fy FROM atestp2; -- ok SELECT atestp2 FROM atestp2; -- ok -SELECT oid FROM atestp2; -- ok +SELECT tableoid FROM atestp2; -- ok SELECT fy FROM atestc; -- fail SET SESSION AUTHORIZATION regress_priv_user1; -GRANT SELECT(fy,oid) ON atestc TO regress_priv_user2; +GRANT SELECT(fy,tableoid) ON atestc TO regress_priv_user2; SET SESSION AUTHORIZATION regress_priv_user2; SELECT fx FROM atestp2; -- still ok SELECT fy FROM atestp2; -- ok SELECT atestp2 FROM atestp2; -- ok -SELECT oid FROM atestp2; -- ok +SELECT tableoid FROM atestp2; -- ok -- privileges on functions, languages diff --git a/src/test/regress/sql/reloptions.sql b/src/test/regress/sql/reloptions.sql index 37fbf41f7d5..44fcd8c4145 100644 --- a/src/test/regress/sql/reloptions.sql +++ b/src/test/regress/sql/reloptions.sql @@ -52,11 +52,6 @@ SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass AND -- RESET fails if a value is specified ALTER TABLE reloptions_test RESET (fillfactor=12); --- The OIDS option is not stored as reloption -DROP TABLE reloptions_test; -CREATE TABLE reloptions_test(i INT) WITH (fillfactor=20, oids=true); -SELECT reloptions, relhasoids FROM pg_class WHERE oid = 'reloptions_test'::regclass; - -- Test toast.* options DROP TABLE reloptions_test; diff --git a/src/test/regress/sql/replica_identity.sql b/src/test/regress/sql/replica_identity.sql index 3d2171c7336..b08a3623b8c 100644 --- a/src/test/regress/sql/replica_identity.sql +++ b/src/test/regress/sql/replica_identity.sql @@ -5,13 +5,12 @@ CREATE TABLE test_replica_identity ( nonkey text, CONSTRAINT test_replica_identity_unique_defer UNIQUE (keya, keyb) DEFERRABLE, CONSTRAINT test_replica_identity_unique_nondefer UNIQUE (keya, keyb) -) WITH OIDS; +) ; CREATE TABLE test_replica_identity_othertable (id serial primary key); CREATE INDEX test_replica_identity_keyab ON test_replica_identity (keya, keyb); CREATE UNIQUE INDEX test_replica_identity_keyab_key ON test_replica_identity (keya, keyb); -CREATE UNIQUE INDEX test_replica_identity_oid_idx ON test_replica_identity (oid); CREATE UNIQUE INDEX test_replica_identity_nonkey ON test_replica_identity (keya, nonkey); CREATE INDEX test_replica_identity_hash ON test_replica_identity USING hash (nonkey); CREATE UNIQUE INDEX test_replica_identity_expr ON test_replica_identity (keya, keyb, (3)); @@ -53,9 +52,6 @@ ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_iden SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; \d test_replica_identity --- succeed, oid unique index -ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_oid_idx; - -- succeed, nondeferrable unique constraint over nonnullable cols ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_unique_nondefer; diff --git a/src/test/regress/sql/roleattributes.sql b/src/test/regress/sql/roleattributes.sql index 1b034d752fb..c961b2d7303 100644 --- a/src/test/regress/sql/roleattributes.sql +++ b/src/test/regress/sql/roleattributes.sql @@ -1,82 +1,83 @@ -- default for superuser is false CREATE ROLE regress_test_def_superuser; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_def_superuser'; + +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_superuser'; CREATE ROLE regress_test_superuser WITH SUPERUSER; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_superuser'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser'; ALTER ROLE regress_test_superuser WITH NOSUPERUSER; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_superuser'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser'; ALTER ROLE regress_test_superuser WITH SUPERUSER; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_superuser'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser'; -- default for inherit is true CREATE ROLE regress_test_def_inherit; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_def_inherit'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_inherit'; CREATE ROLE regress_test_inherit WITH NOINHERIT; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_inherit'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit'; ALTER ROLE regress_test_inherit WITH INHERIT; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_inherit'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit'; ALTER ROLE regress_test_inherit WITH NOINHERIT; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_inherit'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit'; -- default for create role is false CREATE ROLE regress_test_def_createrole; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_def_createrole'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_createrole'; CREATE ROLE regress_test_createrole WITH CREATEROLE; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_createrole'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole'; ALTER ROLE regress_test_createrole WITH NOCREATEROLE; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_createrole'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole'; ALTER ROLE regress_test_createrole WITH CREATEROLE; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_createrole'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole'; -- default for create database is false CREATE ROLE regress_test_def_createdb; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_def_createdb'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_createdb'; CREATE ROLE regress_test_createdb WITH CREATEDB; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_createdb'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb'; ALTER ROLE regress_test_createdb WITH NOCREATEDB; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_createdb'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb'; ALTER ROLE regress_test_createdb WITH CREATEDB; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_createdb'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb'; -- default for can login is false for role CREATE ROLE regress_test_def_role_canlogin; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_def_role_canlogin'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_role_canlogin'; CREATE ROLE regress_test_role_canlogin WITH LOGIN; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_role_canlogin'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin'; ALTER ROLE regress_test_role_canlogin WITH NOLOGIN; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_role_canlogin'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin'; ALTER ROLE regress_test_role_canlogin WITH LOGIN; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_role_canlogin'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin'; -- default for can login is true for user CREATE USER regress_test_def_user_canlogin; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_def_user_canlogin'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_user_canlogin'; CREATE USER regress_test_user_canlogin WITH NOLOGIN; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_user_canlogin'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin'; ALTER USER regress_test_user_canlogin WITH LOGIN; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_user_canlogin'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin'; ALTER USER regress_test_user_canlogin WITH NOLOGIN; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_user_canlogin'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin'; -- default for replication is false CREATE ROLE regress_test_def_replication; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_def_replication'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_replication'; CREATE ROLE regress_test_replication WITH REPLICATION; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_replication'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication'; ALTER ROLE regress_test_replication WITH NOREPLICATION; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_replication'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication'; ALTER ROLE regress_test_replication WITH REPLICATION; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_replication'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication'; -- default for bypassrls is false CREATE ROLE regress_test_def_bypassrls; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_def_bypassrls'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_bypassrls'; CREATE ROLE regress_test_bypassrls WITH BYPASSRLS; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_bypassrls'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls'; ALTER ROLE regress_test_bypassrls WITH NOBYPASSRLS; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_bypassrls'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls'; ALTER ROLE regress_test_bypassrls WITH BYPASSRLS; -SELECT * FROM pg_authid WHERE rolname = 'regress_test_bypassrls'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls'; -- clean up roles DROP ROLE regress_test_def_superuser; diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql index 5a9fdcad742..38e9b38bc40 100644 --- a/src/test/regress/sql/rowsecurity.sql +++ b/src/test/regress/sql/rowsecurity.sql @@ -232,11 +232,11 @@ SET SESSION AUTHORIZATION regress_rls_alice; SET row_security TO ON; -CREATE TABLE t1 (a int, junk1 text, b text) WITH OIDS; +CREATE TABLE t1 (id int not null primary key, a int, junk1 text, b text); ALTER TABLE t1 DROP COLUMN junk1; -- just a disturbing factor GRANT ALL ON t1 TO public; -COPY t1 FROM stdin WITH (oids); +COPY t1 FROM stdin WITH ; 101 1 aba 102 2 bbb 103 3 ccc @@ -246,18 +246,18 @@ COPY t1 FROM stdin WITH (oids); CREATE TABLE t2 (c float) INHERITS (t1); GRANT ALL ON t2 TO public; -COPY t2 FROM stdin WITH (oids); +COPY t2 FROM stdin; 201 1 abc 1.1 202 2 bcd 2.2 203 3 cde 3.3 204 4 def 4.4 \. -CREATE TABLE t3 (c text, b text, a int) WITH OIDS; +CREATE TABLE t3 (id int not null primary key, c text, b text, a int); ALTER TABLE t3 INHERIT t1; GRANT ALL ON t3 TO public; -COPY t3(a,b,c) FROM stdin WITH (oids); +COPY t3(id, a,b,c) FROM stdin; 301 1 xxx X 302 2 yyy Y 303 3 zzz Z @@ -278,7 +278,7 @@ SELECT * FROM t1 WHERE f_leak(b); EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b); -- reference to system column -SELECT oid, * FROM t1; +SELECT tableoid::regclass, * FROM t1; EXPLAIN (COSTS OFF) SELECT *, t1 FROM t1; -- reference to whole-row reference @@ -293,8 +293,8 @@ SELECT * FROM t1 WHERE f_leak(b) FOR SHARE; EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b) FOR SHARE; -- union all query -SELECT a, b, oid FROM t2 UNION ALL SELECT a, b, oid FROM t3; -EXPLAIN (COSTS OFF) SELECT a, b, oid FROM t2 UNION ALL SELECT a, b, oid FROM t3; +SELECT a, b, tableoid::regclass FROM t2 UNION ALL SELECT a, b, tableoid::regclass FROM t3; +EXPLAIN (COSTS OFF) SELECT a, b, tableoid::regclass FROM t2 UNION ALL SELECT a, b, tableoid::regclass FROM t3; -- superuser is allowed to bypass RLS checks RESET SESSION AUTHORIZATION; @@ -614,9 +614,9 @@ EXPLAIN (COSTS OFF) UPDATE only t1 SET b = b || '_updt' WHERE f_leak(b); UPDATE only t1 SET b = b || '_updt' WHERE f_leak(b); -- returning clause with system column -UPDATE only t1 SET b = b WHERE f_leak(b) RETURNING oid, *, t1; +UPDATE only t1 SET b = b WHERE f_leak(b) RETURNING tableoid::regclass, *, t1; UPDATE t1 SET b = b WHERE f_leak(b) RETURNING *; -UPDATE t1 SET b = b WHERE f_leak(b) RETURNING oid, *, t1; +UPDATE t1 SET b = b WHERE f_leak(b) RETURNING tableoid::regclass, *, t1; -- updates with from clause EXPLAIN (COSTS OFF) UPDATE t2 SET b=t2.b FROM t3 @@ -663,8 +663,8 @@ SET row_security TO ON; EXPLAIN (COSTS OFF) DELETE FROM only t1 WHERE f_leak(b); EXPLAIN (COSTS OFF) DELETE FROM t1 WHERE f_leak(b); -DELETE FROM only t1 WHERE f_leak(b) RETURNING oid, *, t1; -DELETE FROM t1 WHERE f_leak(b) RETURNING oid, *, t1; +DELETE FROM only t1 WHERE f_leak(b) RETURNING tableoid::regclass, *, t1; +DELETE FROM t1 WHERE f_leak(b) RETURNING tableoid::regclass, *, t1; -- -- S.b. view on top of Row-level security diff --git a/src/test/regress/sql/rowtypes.sql b/src/test/regress/sql/rowtypes.sql index faf2e108d6a..e6d389805cd 100644 --- a/src/test/regress/sql/rowtypes.sql +++ b/src/test/regress/sql/rowtypes.sql @@ -442,15 +442,15 @@ select r, r is null as isnull, r is not null as isnotnull from r; -- -- Tests for component access / FieldSelect -- -CREATE TABLE compositetable(a text, b text) WITH OIDS; +CREATE TABLE compositetable(a text, b text); INSERT INTO compositetable(a, b) VALUES('fa', 'fb'); -- composite type columns can't directly be accessed (error) SELECT d.a FROM (SELECT compositetable AS d FROM compositetable) s; -- but can be accessed with proper parens SELECT (d).a, (d).b FROM (SELECT compositetable AS d FROM compositetable) s; --- oids can't be accessed in composite types (error) -SELECT (d).oid FROM (SELECT compositetable AS d FROM compositetable) s; +-- system columns can't be accessed in composite types (error) +SELECT (d).ctid FROM (SELECT compositetable AS d FROM compositetable) s; -- accessing non-existing column in NULL datum errors out SELECT (NULL::compositetable).nonexistant; diff --git a/src/test/regress/sql/sanity_check.sql b/src/test/regress/sql/sanity_check.sql index 04aee457dda..a2feebc91bf 100644 --- a/src/test/regress/sql/sanity_check.sql +++ b/src/test/regress/sql/sanity_check.sql @@ -25,9 +25,9 @@ SELECT relname, relhasindex -- We exclude non-system tables from the check by looking at nspname. -- SELECT relname, nspname -FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = relnamespace -WHERE relhasoids - AND ((nspname ~ '^pg_') IS NOT FALSE) - AND NOT EXISTS (SELECT 1 FROM pg_index i WHERE indrelid = c.oid - AND indkey[0] = -2 AND indnatts = 1 - AND indisunique AND indimmediate); + FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = relnamespace JOIN pg_attribute a ON (attrelid = c.oid AND attname = 'oid') + WHERE relkind = 'r' and c.oid < 16384 + AND ((nspname ~ '^pg_') IS NOT FALSE) + AND NOT EXISTS (SELECT 1 FROM pg_index i WHERE indrelid = c.oid + AND indkey[0] = a.attnum AND indnatts = 1 + AND indisunique AND indimmediate); diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql index d7dfd753be2..5336185ed25 100644 --- a/src/test/regress/sql/triggers.sql +++ b/src/test/regress/sql/triggers.sql @@ -292,11 +292,11 @@ DROP TRIGGER insert_when ON main_table; DROP TRIGGER delete_when ON main_table; -- Test WHEN condition accessing system columns. -create table table_with_oids(a int) with oids; +create table table_with_oids(a int); insert into table_with_oids values (1); create trigger oid_unchanged_trig after update on table_with_oids for each row - when (new.oid = old.oid AND new.oid <> 0) + when (new.tableoid = old.tableoid AND new.tableoid <> 0) execute procedure trigger_func('after_upd_oid_unchanged'); update table_with_oids set a = a + 1; drop table table_with_oids; @@ -582,23 +582,12 @@ CREATE TABLE min_updates_test ( f2 int, f3 int); -CREATE TABLE min_updates_test_oids ( - f1 text, - f2 int, - f3 int) WITH OIDS; - INSERT INTO min_updates_test VALUES ('a',1,2),('b','2',null); -INSERT INTO min_updates_test_oids VALUES ('a',1,2),('b','2',null); - CREATE TRIGGER z_min_update BEFORE UPDATE ON min_updates_test FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger(); -CREATE TRIGGER z_min_update -BEFORE UPDATE ON min_updates_test_oids -FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger(); - \set QUIET false UPDATE min_updates_test SET f1 = f1; @@ -607,22 +596,12 @@ UPDATE min_updates_test SET f2 = f2 + 1; UPDATE min_updates_test SET f3 = 2 WHERE f3 is null; -UPDATE min_updates_test_oids SET f1 = f1; - -UPDATE min_updates_test_oids SET f2 = f2 + 1; - -UPDATE min_updates_test_oids SET f3 = 2 WHERE f3 is null; - \set QUIET true SELECT * FROM min_updates_test; -SELECT * FROM min_updates_test_oids; - DROP TABLE min_updates_test; -DROP TABLE min_updates_test_oids; - -- -- Test triggers on views -- diff --git a/src/test/regress/sql/without_oid.sql b/src/test/regress/sql/without_oid.sql deleted file mode 100644 index 9fbb454d4dc..00000000000 --- a/src/test/regress/sql/without_oid.sql +++ /dev/null @@ -1,92 +0,0 @@ --- --- WITHOUT OID --- - --- --- This test tries to verify that WITHOUT OIDS actually saves space. --- On machines where MAXALIGN is 8, WITHOUT OIDS may or may not save any --- space, depending on the size of the tuple header + null bitmap. --- As of 8.3 we need a null bitmap of 8 or less bits for the difference --- to appear. --- -CREATE TABLE wi (i INT, - n1 int, n2 int, n3 int, n4 int, - n5 int, n6 int, n7 int) WITH OIDS; -CREATE TABLE wo (i INT, - n1 int, n2 int, n3 int, n4 int, - n5 int, n6 int, n7 int) WITHOUT OIDS; - -INSERT INTO wi VALUES (1); -- 1 -INSERT INTO wo SELECT i FROM wi; -- 1 -INSERT INTO wo SELECT i+1 FROM wi; -- 1+1=2 -INSERT INTO wi SELECT i+1 FROM wo; -- 1+2=3 -INSERT INTO wi SELECT i+3 FROM wi; -- 3+3=6 -INSERT INTO wo SELECT i+2 FROM wi; -- 2+6=8 -INSERT INTO wo SELECT i+8 FROM wo; -- 8+8=16 -INSERT INTO wi SELECT i+6 FROM wo; -- 6+16=22 -INSERT INTO wi SELECT i+22 FROM wi; -- 22+22=44 -INSERT INTO wo SELECT i+16 FROM wi; -- 16+44=60 -INSERT INTO wo SELECT i+60 FROM wo; -- 60+60=120 -INSERT INTO wi SELECT i+44 FROM wo; -- 44+120=164 -INSERT INTO wi SELECT i+164 FROM wi; -- 164+164=328 -INSERT INTO wo SELECT i+120 FROM wi; -- 120+328=448 -INSERT INTO wo SELECT i+448 FROM wo; -- 448+448=896 -INSERT INTO wi SELECT i+328 FROM wo; -- 328+896=1224 -INSERT INTO wi SELECT i+1224 FROM wi; -- 1224+1224=2448 -INSERT INTO wo SELECT i+896 FROM wi; -- 896+2448=3344 -INSERT INTO wo SELECT i+3344 FROM wo; -- 3344+3344=6688 -INSERT INTO wi SELECT i+2448 FROM wo; -- 2448+6688=9136 -INSERT INTO wo SELECT i+6688 FROM wi WHERE i<=2448; -- 6688+2448=9136 - -SELECT count(oid) FROM wi; --- should fail -SELECT count(oid) FROM wo; - -VACUUM ANALYZE wi; -VACUUM ANALYZE wo; - -SELECT min(relpages) < max(relpages), min(reltuples) - max(reltuples) - FROM pg_class - WHERE relname IN ('wi', 'wo'); - -DROP TABLE wi; -DROP TABLE wo; - --- --- WITH / WITHOUT OIDS in CREATE TABLE AS --- -CREATE TABLE create_table_test ( - a int, - b int -); - -COPY create_table_test FROM stdin; -5 10 -10 15 -\. - -CREATE TABLE create_table_test2 WITH OIDS AS - SELECT a + b AS c1, a - b AS c2 FROM create_table_test; - -CREATE TABLE create_table_test3 WITHOUT OIDS AS - SELECT a + b AS c1, a - b AS c2 FROM create_table_test; - -SELECT count(oid) FROM create_table_test2; --- should fail -SELECT count(oid) FROM create_table_test3; - -PREPARE table_source(int) AS - SELECT a + b AS c1, a - b AS c2, $1 AS c3 FROM create_table_test; - -CREATE TABLE execute_with WITH OIDS AS EXECUTE table_source(1); -CREATE TABLE execute_without WITHOUT OIDS AS EXECUTE table_source(2); - -SELECT count(oid) FROM execute_with; --- should fail -SELECT count(oid) FROM execute_without; - -DROP TABLE create_table_test; -DROP TABLE create_table_test2; -DROP TABLE create_table_test3; -DROP TABLE execute_with; -DROP TABLE execute_without; |