From d7c734841b3e6cb44de363a8a3d83c35b75b30d9 Mon Sep 17 00:00:00 2001 From: Robert Haas Date: Wed, 4 Jul 2012 20:34:24 -0400 Subject: [PATCH] Reduce messages about implicit indexes and sequences to DEBUG1. Per recent discussion on pgsql-hackers, these messages are too chatty for most users. --- contrib/btree_gist/expected/not_equal.out | 1 - contrib/citext/expected/citext.out | 1 - contrib/citext/expected/citext_1.out | 1 - contrib/dblink/expected/dblink.out | 4 -- contrib/pgstattuple/expected/pgstattuple.out | 1 - contrib/sepgsql/expected/ddl.out | 3 -- contrib/sepgsql/expected/dml.out | 1 - contrib/tablefunc/expected/tablefunc.out | 1 - doc/src/sgml/tcn.sgml | 1 - src/backend/commands/indexcmds.c | 2 +- src/backend/parser/parse_utilcmd.c | 2 +- src/pl/plpython/expected/plpython_error.out | 1 - src/pl/plpython/expected/plpython_error_0.out | 1 - src/pl/plpython/expected/plpython_schema.out | 10 ---- .../expected/plpython_subtransaction.out | 1 - .../expected/plpython_subtransaction_0.out | 1 - .../expected/plpython_subtransaction_5.out | 1 - src/test/regress/expected/alter_table.out | 28 ----------- src/test/regress/expected/arrays.out | 1 - src/test/regress/expected/cluster.out | 9 ---- src/test/regress/expected/collate.out | 1 - src/test/regress/expected/copy2.out | 1 - src/test/regress/expected/copyselect.out | 2 - src/test/regress/expected/create_table.out | 5 -- .../regress/expected/create_table_like.out | 7 --- src/test/regress/expected/delete.out | 2 - src/test/regress/expected/dependency.out | 7 --- src/test/regress/expected/domain.out | 1 - src/test/regress/expected/enum.out | 1 - src/test/regress/expected/foreign_data.out | 1 - src/test/regress/expected/foreign_key.out | 46 ------------------- src/test/regress/expected/functional_deps.out | 9 ---- src/test/regress/expected/inherit.out | 6 --- src/test/regress/expected/join.out | 14 ------ src/test/regress/expected/namespace.out | 2 - src/test/regress/expected/plpgsql.out | 3 -- src/test/regress/expected/rangefuncs.out | 4 -- src/test/regress/expected/rangetypes.out | 2 - src/test/regress/expected/returning.out | 1 - src/test/regress/expected/rowtypes.out | 2 - src/test/regress/expected/rules.out | 9 ---- src/test/regress/expected/select_views.out | 1 - src/test/regress/expected/select_views_1.out | 1 - src/test/regress/expected/sequence.out | 7 --- src/test/regress/expected/sequence_1.out | 7 --- src/test/regress/expected/subselect.out | 3 -- src/test/regress/expected/temp.out | 2 - src/test/regress/expected/transactions.out | 2 - src/test/regress/expected/triggers.out | 10 ---- src/test/regress/expected/truncate.out | 5 -- src/test/regress/expected/typed_table.out | 3 -- src/test/regress/expected/union.out | 1 - src/test/regress/expected/vacuum.out | 1 - src/test/regress/expected/with.out | 2 - src/test/regress/output/constraints.source | 10 ---- 55 files changed, 2 insertions(+), 250 deletions(-) diff --git a/contrib/btree_gist/expected/not_equal.out b/contrib/btree_gist/expected/not_equal.out index d9b91e2d56..1d5b55db5a 100644 --- a/contrib/btree_gist/expected/not_equal.out +++ b/contrib/btree_gist/expected/not_equal.out @@ -31,7 +31,6 @@ CREATE TABLE zoo ( animal TEXT, EXCLUDE USING gist (cage WITH =, animal WITH <>) ); -NOTICE: CREATE TABLE / EXCLUDE will create implicit index "zoo_cage_animal_excl" for table "zoo" INSERT INTO zoo VALUES(123, 'zebra'); INSERT INTO zoo VALUES(123, 'zebra'); INSERT INTO zoo VALUES(123, 'lion'); diff --git a/contrib/citext/expected/citext.out b/contrib/citext/expected/citext.out index 5392a7d1f3..000c925642 100644 --- a/contrib/citext/expected/citext.out +++ b/contrib/citext/expected/citext.out @@ -218,7 +218,6 @@ SELECT citext_cmp('B'::citext, 'a'::citext) > 0 AS true; CREATE TEMP TABLE try ( name citext PRIMARY KEY ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "try_pkey" for table "try" INSERT INTO try (name) VALUES ('a'), ('ab'), ('â'), ('aba'), ('b'), ('ba'), ('bab'), ('AZ'); SELECT name, 'a' = name AS eq_a FROM try WHERE name <> 'â'; diff --git a/contrib/citext/expected/citext_1.out b/contrib/citext/expected/citext_1.out index 5316ad0cda..8373dc8846 100644 --- a/contrib/citext/expected/citext_1.out +++ b/contrib/citext/expected/citext_1.out @@ -218,7 +218,6 @@ SELECT citext_cmp('B'::citext, 'a'::citext) > 0 AS true; CREATE TEMP TABLE try ( name citext PRIMARY KEY ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "try_pkey" for table "try" INSERT INTO try (name) VALUES ('a'), ('ab'), ('â'), ('aba'), ('b'), ('ba'), ('bab'), ('AZ'); SELECT name, 'a' = name AS eq_a FROM try WHERE name <> 'â'; diff --git a/contrib/dblink/expected/dblink.out b/contrib/dblink/expected/dblink.out index 1db153aeb1..a1899ed3c2 100644 --- a/contrib/dblink/expected/dblink.out +++ b/contrib/dblink/expected/dblink.out @@ -1,6 +1,5 @@ CREATE EXTENSION dblink; CREATE TABLE foo(f1 int, f2 text, f3 text[], primary key (f1,f2)); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo" INSERT INTO foo VALUES (0,'a','{"a0","b0","c0"}'); INSERT INTO foo VALUES (1,'b','{"a1","b1","c1"}'); INSERT INTO foo VALUES (2,'c','{"a2","b2","c2"}'); @@ -56,7 +55,6 @@ ERROR: invalid attribute number 4 -- retest using a quoted and schema qualified table CREATE SCHEMA "MySchema"; CREATE TABLE "MySchema"."Foo"(f1 int, f2 text, f3 text[], primary key (f1,f2)); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "Foo_pkey" for table "Foo" INSERT INTO "MySchema"."Foo" VALUES (0,'a','{"a0","b0","c0"}'); -- list the primary key fields SELECT * @@ -885,8 +883,6 @@ CREATE TEMP TABLE test_dropped col2 INT NOT NULL DEFAULT 112, col2b INT NOT NULL DEFAULT 113 ); -NOTICE: CREATE TABLE will create implicit sequence "test_dropped_id_seq" for serial column "test_dropped.id" -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_dropped_pkey" for table "test_dropped" INSERT INTO test_dropped VALUES(default); ALTER TABLE test_dropped DROP COLUMN col1, diff --git a/contrib/pgstattuple/expected/pgstattuple.out b/contrib/pgstattuple/expected/pgstattuple.out index 7f28177890..2d8dd5e19c 100644 --- a/contrib/pgstattuple/expected/pgstattuple.out +++ b/contrib/pgstattuple/expected/pgstattuple.out @@ -5,7 +5,6 @@ CREATE EXTENSION pgstattuple; -- indexes should be that. -- create table test (a int primary key); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test" select * from pgstattuple('test'::text); table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent -----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+-------------- diff --git a/contrib/sepgsql/expected/ddl.out b/contrib/sepgsql/expected/ddl.out index 1c7bcc5ca9..e7a8d9c301 100644 --- a/contrib/sepgsql/expected/ddl.out +++ b/contrib/sepgsql/expected/ddl.out @@ -22,7 +22,6 @@ LOG: SELinux: allowed { create } scontext=unconfined_u:unconfined_r:unconfined_ GRANT ALL ON SCHEMA regtest_schema TO regtest_sepgsql_test_user; SET search_path = regtest_schema, public; CREATE TABLE regtest_table (x serial primary key, y text); -NOTICE: CREATE TABLE will create implicit sequence "regtest_table_x_seq" for serial column "regtest_table.x" LOG: SELinux: allowed { add_name } scontext=unconfined_u:unconfined_r:unconfined_t:s0 tcontext=unconfined_u:object_r:sepgsql_schema_t:s0 tclass=db_schema name="schema regtest_schema" LOG: SELinux: allowed { create } scontext=unconfined_u:unconfined_r:unconfined_t:s0 tcontext=unconfined_u:object_r:sepgsql_seq_t:s0 tclass=db_sequence name="sequence regtest_table_x_seq" LOG: SELinux: allowed { add_name } scontext=unconfined_u:unconfined_r:unconfined_t:s0 tcontext=unconfined_u:object_r:sepgsql_schema_t:s0 tclass=db_schema name="schema regtest_schema" @@ -35,7 +34,6 @@ LOG: SELinux: allowed { create } scontext=unconfined_u:unconfined_r:unconfined_ LOG: SELinux: allowed { create } scontext=unconfined_u:unconfined_r:unconfined_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_column name="table regtest_table column ctid" LOG: SELinux: allowed { create } scontext=unconfined_u:unconfined_r:unconfined_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_column name="table regtest_table column x" LOG: SELinux: allowed { create } scontext=unconfined_u:unconfined_r:unconfined_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_column name="table regtest_table column y" -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "regtest_table_pkey" for table "regtest_table" ALTER TABLE regtest_table ADD COLUMN z int; LOG: SELinux: allowed { create } scontext=unconfined_u:unconfined_r:unconfined_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_column name="table regtest_table column z" CREATE TABLE regtest_table_2 (a int) WITH OIDS; @@ -74,7 +72,6 @@ LOG: SELinux: allowed { create } scontext=unconfined_u:unconfined_r:unconfined_ SET SESSION AUTHORIZATION regtest_sepgsql_test_user; SET search_path = regtest_schema, public; CREATE TABLE regtest_table_3 (x int, y serial); -NOTICE: CREATE TABLE will create implicit sequence "regtest_table_3_y_seq" for serial column "regtest_table_3.y" LOG: SELinux: allowed { add_name } scontext=unconfined_u:unconfined_r:unconfined_t:s0 tcontext=unconfined_u:object_r:sepgsql_schema_t:s0 tclass=db_schema name="schema regtest_schema" LOG: SELinux: allowed { create } scontext=unconfined_u:unconfined_r:unconfined_t:s0 tcontext=unconfined_u:object_r:sepgsql_seq_t:s0 tclass=db_sequence name="sequence regtest_table_3_y_seq" LOG: SELinux: allowed { add_name } scontext=unconfined_u:unconfined_r:unconfined_t:s0 tcontext=unconfined_u:object_r:sepgsql_schema_t:s0 tclass=db_schema name="schema regtest_schema" diff --git a/contrib/sepgsql/expected/dml.out b/contrib/sepgsql/expected/dml.out index 949789f2f1..1a29a63971 100644 --- a/contrib/sepgsql/expected/dml.out +++ b/contrib/sepgsql/expected/dml.out @@ -22,7 +22,6 @@ SECURITY LABEL ON COLUMN t5.e IS 'system_u:object_r:sepgsql_table_t:s0'; SECURITY LABEL ON COLUMN t5.f IS 'system_u:object_r:sepgsql_ro_table_t:s0'; SECURITY LABEL ON COLUMN t5.g IS 'system_u:object_r:sepgsql_secret_table_t:s0'; CREATE TABLE customer (cid int primary key, cname text, ccredit text); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "customer_pkey" for table "customer" SECURITY LABEL ON COLUMN customer.ccredit IS 'system_u:object_r:sepgsql_secret_table_t:s0'; INSERT INTO customer VALUES (1, 'Taro', '1111-2222-3333-4444'), (2, 'Hanako', '5555-6666-7777-8888'); diff --git a/contrib/tablefunc/expected/tablefunc.out b/contrib/tablefunc/expected/tablefunc.out index 7ad4336ada..0437ecf90a 100644 --- a/contrib/tablefunc/expected/tablefunc.out +++ b/contrib/tablefunc/expected/tablefunc.out @@ -146,7 +146,6 @@ SELECT * FROM crosstab_out('SELECT rowid, attribute, val FROM ct where rowclass -- hash based crosstab -- create table cth(id serial, rowid text, rowdt timestamp, attribute text, val text); -NOTICE: CREATE TABLE will create implicit sequence "cth_id_seq" for serial column "cth.id" insert into cth values(DEFAULT,'test1','01 March 2003','temperature','42'); insert into cth values(DEFAULT,'test1','01 March 2003','test_result','PASS'); -- the next line is intentionally left commented and is therefore a "missing" attribute diff --git a/doc/src/sgml/tcn.sgml b/doc/src/sgml/tcn.sgml index f662d09c45..623094183d 100644 --- a/doc/src/sgml/tcn.sgml +++ b/doc/src/sgml/tcn.sgml @@ -44,7 +44,6 @@ test(# b date not null, test(# c text, test(# primary key (a, b) test(# ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tcndata_pkey" for table "tcndata" CREATE TABLE test=# create trigger tcndata_tcn_trigger test-# after insert or update or delete on tcndata diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 1f9bf61890..e5d1d8b699 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -593,7 +593,7 @@ DefineIndex(RangeVar *heapRelation, constraint_type = NULL; /* keep compiler quiet */ } - ereport(NOTICE, + ereport(DEBUG1, (errmsg("%s %s will create implicit index \"%s\" for table \"%s\"", is_alter_table ? "ALTER TABLE / ADD" : "CREATE TABLE /", constraint_type, diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index 55a74eeaf4..e01e103774 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -379,7 +379,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) "seq", snamespaceid); - ereport(NOTICE, + ereport(DEBUG1, (errmsg("%s will create implicit sequence \"%s\" for serial column \"%s.%s\"", cxt->stmtType, sname, cxt->relation->relname, column->colname))); diff --git a/src/pl/plpython/expected/plpython_error.out b/src/pl/plpython/expected/plpython_error.out index 8a4f571253..e1ec9c2c13 100644 --- a/src/pl/plpython/expected/plpython_error.out +++ b/src/pl/plpython/expected/plpython_error.out @@ -318,7 +318,6 @@ PL/Python function "sql_from_python_error" CREATE TABLE specific ( i integer PRIMARY KEY ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "specific_pkey" for table "specific" CREATE FUNCTION specific_exception(i integer) RETURNS void AS $$ from plpy import spiexceptions diff --git a/src/pl/plpython/expected/plpython_error_0.out b/src/pl/plpython/expected/plpython_error_0.out index b8d10dd735..6f74a5038f 100644 --- a/src/pl/plpython/expected/plpython_error_0.out +++ b/src/pl/plpython/expected/plpython_error_0.out @@ -318,7 +318,6 @@ PL/Python function "sql_from_python_error" CREATE TABLE specific ( i integer PRIMARY KEY ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "specific_pkey" for table "specific" CREATE FUNCTION specific_exception(i integer) RETURNS void AS $$ from plpy import spiexceptions diff --git a/src/pl/plpython/expected/plpython_schema.out b/src/pl/plpython/expected/plpython_schema.out index 3ec331c0f0..23d94f62bf 100644 --- a/src/pl/plpython/expected/plpython_schema.out +++ b/src/pl/plpython/expected/plpython_schema.out @@ -5,8 +5,6 @@ CREATE TABLE users ( userid serial, PRIMARY KEY(lname, fname) ) ; -NOTICE: CREATE TABLE will create implicit sequence "users_userid_seq" for serial column "users.userid" -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users" CREATE INDEX users_username_idx ON users(username); CREATE INDEX users_fname_idx ON users(fname); CREATE INDEX users_lname_idx ON users(lname); @@ -15,17 +13,11 @@ CREATE TABLE taxonomy ( id serial primary key, name text unique ) ; -NOTICE: CREATE TABLE will create implicit sequence "taxonomy_id_seq" for serial column "taxonomy.id" -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "taxonomy_pkey" for table "taxonomy" -NOTICE: CREATE TABLE / UNIQUE will create implicit index "taxonomy_name_key" for table "taxonomy" CREATE TABLE entry ( accession text not null primary key, eid serial unique, txid int2 not null references taxonomy(id) ) ; -NOTICE: CREATE TABLE will create implicit sequence "entry_eid_seq" for serial column "entry.eid" -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "entry_pkey" for table "entry" -NOTICE: CREATE TABLE / UNIQUE will create implicit index "entry_eid_key" for table "entry" CREATE TABLE sequences ( eid int4 not null references entry(eid), pid serial primary key, @@ -33,8 +25,6 @@ CREATE TABLE sequences ( sequence text not null, multipart bool default 'false' ) ; -NOTICE: CREATE TABLE will create implicit sequence "sequences_pid_seq" for serial column "sequences.pid" -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "sequences_pkey" for table "sequences" CREATE INDEX sequences_product_idx ON sequences(product) ; CREATE TABLE xsequences ( pid int4 not null references sequences(pid), diff --git a/src/pl/plpython/expected/plpython_subtransaction.out b/src/pl/plpython/expected/plpython_subtransaction.out index c2c22f0ae4..ced4682440 100644 --- a/src/pl/plpython/expected/plpython_subtransaction.out +++ b/src/pl/plpython/expected/plpython_subtransaction.out @@ -384,7 +384,6 @@ SELECT * FROM subtransaction_tbl; TRUNCATE subtransaction_tbl; ALTER TABLE subtransaction_tbl ADD PRIMARY KEY (i); -NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "subtransaction_tbl_pkey" for table "subtransaction_tbl" CREATE FUNCTION pk_violation_inside_subtransaction() RETURNS void AS $$ with plpy.subtransaction(): diff --git a/src/pl/plpython/expected/plpython_subtransaction_0.out b/src/pl/plpython/expected/plpython_subtransaction_0.out index ece0134a94..6f4be556d5 100644 --- a/src/pl/plpython/expected/plpython_subtransaction_0.out +++ b/src/pl/plpython/expected/plpython_subtransaction_0.out @@ -359,7 +359,6 @@ SELECT * FROM subtransaction_tbl; TRUNCATE subtransaction_tbl; ALTER TABLE subtransaction_tbl ADD PRIMARY KEY (i); -NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "subtransaction_tbl_pkey" for table "subtransaction_tbl" CREATE FUNCTION pk_violation_inside_subtransaction() RETURNS void AS $$ with plpy.subtransaction(): diff --git a/src/pl/plpython/expected/plpython_subtransaction_5.out b/src/pl/plpython/expected/plpython_subtransaction_5.out index 66de239499..4ca18357dc 100644 --- a/src/pl/plpython/expected/plpython_subtransaction_5.out +++ b/src/pl/plpython/expected/plpython_subtransaction_5.out @@ -359,7 +359,6 @@ SELECT * FROM subtransaction_tbl; TRUNCATE subtransaction_tbl; ALTER TABLE subtransaction_tbl ADD PRIMARY KEY (i); -NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "subtransaction_tbl_pkey" for table "subtransaction_tbl" CREATE FUNCTION pk_violation_inside_subtransaction() RETURNS void AS $$ with plpy.subtransaction(): diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index a0ae5eb9a7..c7dce415a6 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -162,7 +162,6 @@ alter table stud_emp rename to pg_toast_stud_emp; alter table pg_toast_stud_emp rename to stud_emp; -- renaming index should rename constraint as well ALTER TABLE onek ADD CONSTRAINT onek_unique1_constraint UNIQUE (unique1); -NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "onek_unique1_constraint" for table "onek" ALTER INDEX onek_unique1_constraint RENAME TO onek_unique1_constraint_foo; ALTER TABLE onek DROP CONSTRAINT onek_unique1_constraint_foo; -- renaming constraint @@ -171,7 +170,6 @@ ALTER TABLE onek RENAME CONSTRAINT onek_check_constraint TO onek_check_constrain ALTER TABLE onek DROP CONSTRAINT onek_check_constraint_foo; -- renaming constraint should rename index as well ALTER TABLE onek ADD CONSTRAINT onek_unique1_constraint UNIQUE (unique1); -NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "onek_unique1_constraint" for table "onek" DROP INDEX onek_unique1_constraint; -- to see whether it's there ERROR: cannot drop index onek_unique1_constraint because constraint onek_unique1_constraint on table onek requires it HINT: You can drop constraint onek_unique1_constraint on table onek instead. @@ -263,7 +261,6 @@ Check constraints: Inherits: constraint_rename_test ALTER TABLE constraint_rename_test ADD CONSTRAINT con3 PRIMARY KEY (a); -NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "con3" for table "constraint_rename_test" ALTER TABLE constraint_rename_test RENAME CONSTRAINT con3 TO con3foo; -- ok \d constraint_rename_test Table "public.constraint_rename_test" @@ -297,10 +294,8 @@ ALTER TABLE IF EXISTS constraint_rename_test ADD CONSTRAINT con4 UNIQUE (a); NOTICE: relation "constraint_rename_test" does not exist, skipping -- FOREIGN KEY CONSTRAINT adding TEST CREATE TABLE tmp2 (a int primary key); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tmp2_pkey" for table "tmp2" CREATE TABLE tmp3 (a int, b int); CREATE TABLE tmp4 (a int, b int, unique(a,b)); -NOTICE: CREATE TABLE / UNIQUE will create implicit index "tmp4_a_b_key" for table "tmp4" CREATE TABLE tmp5 (a int, b int); -- Insert rows into tmp2 (pktable) INSERT INTO tmp2 values (1); @@ -450,7 +445,6 @@ explain (costs off) select * from nv_parent where d between '2009-08-01'::date a -- Note: these tables are TEMP to avoid name conflicts when this test -- is run in parallel with foreign_key.sql. CREATE TEMP TABLE PKTABLE (ptest1 int PRIMARY KEY); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" INSERT INTO PKTABLE VALUES(42); CREATE TEMP TABLE FKTABLE (ftest1 inet); -- This next should fail, because int=inet does not exist @@ -485,7 +479,6 @@ DROP TABLE PKTABLE; -- On the other hand, this should work because int implicitly promotes to -- numeric, and we allow promotion on the FK side CREATE TEMP TABLE PKTABLE (ptest1 numeric PRIMARY KEY); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" INSERT INTO PKTABLE VALUES(42); CREATE TEMP TABLE FKTABLE (ftest1 int); ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable; @@ -498,7 +491,6 @@ DROP TABLE FKTABLE; DROP TABLE PKTABLE; CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet, PRIMARY KEY(ptest1, ptest2)); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" -- This should fail, because we just chose really odd types CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp); ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable; @@ -660,7 +652,6 @@ drop table atacc1; create table atacc1 ( test int ) with oids; -- add a unique constraint alter table atacc1 add constraint atacc_test1 unique (test); -NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_test1" for table "atacc1" -- insert first value insert into atacc1 (test) values (2); -- should fail @@ -671,7 +662,6 @@ DETAIL: Key (test)=(2) already exists. insert into atacc1 (test) values (4); -- try adding a unique oid constraint alter table atacc1 add constraint atacc_oid1 unique(oid); -NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_oid1" for table "atacc1" -- 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" @@ -684,7 +674,6 @@ insert into atacc1 (test) values (2); insert into atacc1 (test) values (2); -- add a unique constraint (fails) alter table atacc1 add constraint atacc_test1 unique (test); -NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_test1" for table "atacc1" ERROR: could not create unique index "atacc_test1" DETAIL: Key (test)=(2) is duplicated. insert into atacc1 (test) values (3); @@ -700,7 +689,6 @@ drop table atacc1; create table atacc1 ( test int, test2 int); -- add a unique constraint alter table atacc1 add constraint atacc_test1 unique (test, test2); -NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_test1" for table "atacc1" -- insert initial value insert into atacc1 (test,test2) values (4,4); -- should fail @@ -714,9 +702,7 @@ insert into atacc1 (test,test2) values (5,5); drop table atacc1; -- lets do some naming tests create table atacc1 (test int, test2 int, unique(test)); -NOTICE: CREATE TABLE / UNIQUE will create implicit index "atacc1_test_key" for table "atacc1" alter table atacc1 add unique (test2); -NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc1_test2_key" for table "atacc1" -- should fail for @@ second one @@ insert into atacc1 (test2, test) values (3, 3); insert into atacc1 (test2, test) values (2, 3); @@ -727,7 +713,6 @@ drop table atacc1; create table atacc1 ( test int ) with oids; -- add a primary key constraint alter table atacc1 add constraint atacc_test1 primary key (test); -NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1" -- insert first value insert into atacc1 (test) values (2); -- should fail @@ -747,7 +732,6 @@ ERROR: multiple primary keys for table "atacc1" are not allowed 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); -NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_oid1" for table "atacc1" drop table atacc1; -- let's do one where the primary key constraint fails when added create table atacc1 ( test int ); @@ -756,7 +740,6 @@ insert into atacc1 (test) values (2); insert into atacc1 (test) values (2); -- add a primary key (fails) alter table atacc1 add constraint atacc_test1 primary key (test); -NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1" ERROR: could not create unique index "atacc_test1" DETAIL: Key (test)=(2) is duplicated. insert into atacc1 (test) values (3); @@ -783,17 +766,14 @@ create table atacc1 ( test int ); insert into atacc1 (test) values (0); -- add a primary key column without a default (fails). alter table atacc1 add column test2 int primary key; -NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1" ERROR: column "test2" contains null values -- now add a primary key column with a default (succeeds). alter table atacc1 add column test2 int default 0 primary key; -NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1" drop table atacc1; -- something a little more complicated create table atacc1 ( test int, test2 int); -- add a primary key constraint alter table atacc1 add constraint atacc_test1 primary key (test, test2); -NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1" -- try adding a second primary key - should fail alter table atacc1 add constraint atacc_test2 primary key (test); ERROR: multiple primary keys for table "atacc1" are not allowed @@ -819,7 +799,6 @@ insert into atacc1 (test,test2) values (5,5); drop table atacc1; -- lets do some naming tests create table atacc1 (test int, test2 int, primary key(test)); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1" -- only first should succeed insert into atacc1 (test2, test) values (3, 3); insert into atacc1 (test2, test) values (2, 3); @@ -844,7 +823,6 @@ ERROR: relation "non_existent" does not exist -- test checking for null values and primary key create table atacc1 (test int not null) with oids; alter table atacc1 add constraint "atacc1_pkey" primary key (test); -NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1" alter table atacc1 alter column test drop not null; ERROR: column "test" is in a primary key alter table atacc1 drop constraint "atacc1_pkey"; @@ -1183,7 +1161,6 @@ ERROR: column "a" does not exist alter table atacc1 add check ("........pg.dropped.1........" > 3); ERROR: column "........pg.dropped.1........" does not exist create table atacc2 (id int4 unique); -NOTICE: CREATE TABLE / UNIQUE will create implicit index "atacc2_id_key" for table "atacc2" alter table atacc1 add foreign key (a) references atacc2(id); ERROR: column "a" referenced in foreign key constraint does not exist alter table atacc1 add foreign key ("........pg.dropped.1........") references atacc2(id); @@ -1678,7 +1655,6 @@ HINT: Specify a USING expression to perform the conversion. alter table foo alter f1 TYPE varchar(10); create table anothertab (atcol1 serial8, atcol2 boolean, constraint anothertab_chk check (atcol1 <= 3)); -NOTICE: CREATE TABLE will create implicit sequence "anothertab_atcol1_seq" for serial column "anothertab.atcol1" insert into anothertab (atcol1, atcol2) values (default, true); insert into anothertab (atcol1, atcol2) values (default, false); select * from anothertab; @@ -1833,7 +1809,6 @@ and relnamespace != (select oid from pg_namespace where nspname = 'pg_catalog') and c.relname != 'my_locks' group by c.relname; create table alterlock (f1 int primary key, f2 text); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "alterlock_pkey" for table "alterlock" begin; alter table alterlock alter column f2 set statistics 150; select * from my_locks order by 1; relname | max_lockmode @@ -1964,8 +1939,6 @@ select non_strict(NULL); create schema alter1; create schema alter2; create table alter1.t1(f1 serial primary key, f2 int check (f2 > 0)); -NOTICE: CREATE TABLE will create implicit sequence "t1_f1_seq" for serial column "t1.f1" -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1" create view alter1.v1 as select * from alter1.t1; create function alter1.plus1(int) returns int as 'select $1+1' language sql; create domain alter1.posint integer check (value > 0); @@ -2297,7 +2270,6 @@ CREATE TABLE tt8(a int); CREATE SCHEMA alter2; ALTER TABLE IF EXISTS tt8 ADD COLUMN f int; ALTER TABLE IF EXISTS tt8 ADD CONSTRAINT xxx PRIMARY KEY(f); -NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "xxx" for table "tt8" ALTER TABLE IF EXISTS tt8 ADD CHECK (f BETWEEN 0 AND 10); ALTER TABLE IF EXISTS tt8 ALTER COLUMN f SET DEFAULT 0; ALTER TABLE IF EXISTS tt8 RENAME COLUMN f TO f1; diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 9865b69b8b..04d186bb14 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -980,7 +980,6 @@ select 33 = all ('{33,null,33}'); -- test indexes on arrays create temp table arr_tbl (f1 int[] unique); -NOTICE: CREATE TABLE / UNIQUE will create implicit index "arr_tbl_f1_key" for table "arr_tbl" insert into arr_tbl values ('{1,2,3}'); insert into arr_tbl values ('{1,2}'); -- failure expected: diff --git a/src/test/regress/expected/cluster.out b/src/test/regress/expected/cluster.out index 979057d26f..bc26846d6a 100644 --- a/src/test/regress/expected/cluster.out +++ b/src/test/regress/expected/cluster.out @@ -3,15 +3,11 @@ -- CREATE TABLE clstr_tst_s (rf_a SERIAL PRIMARY KEY, b INT); -NOTICE: CREATE TABLE will create implicit sequence "clstr_tst_s_rf_a_seq" for serial column "clstr_tst_s.rf_a" -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "clstr_tst_s_pkey" for table "clstr_tst_s" CREATE TABLE clstr_tst (a SERIAL PRIMARY KEY, b INT, c TEXT, d TEXT, CONSTRAINT clstr_tst_con FOREIGN KEY (b) REFERENCES clstr_tst_s); -NOTICE: CREATE TABLE will create implicit sequence "clstr_tst_a_seq" for serial column "clstr_tst.a" -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "clstr_tst_pkey" for table "clstr_tst" CREATE INDEX clstr_tst_b ON clstr_tst (b); CREATE INDEX clstr_tst_c ON clstr_tst (c); CREATE INDEX clstr_tst_c_b ON clstr_tst (c,b); @@ -312,11 +308,8 @@ WHERE pg_class.oid=indexrelid -- Verify that clustering all tables does in fact cluster the right ones CREATE USER clstr_user; CREATE TABLE clstr_1 (a INT PRIMARY KEY); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "clstr_1_pkey" for table "clstr_1" CREATE TABLE clstr_2 (a INT PRIMARY KEY); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "clstr_2_pkey" for table "clstr_2" CREATE TABLE clstr_3 (a INT PRIMARY KEY); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "clstr_3_pkey" for table "clstr_3" ALTER TABLE clstr_1 OWNER TO clstr_user; ALTER TABLE clstr_3 OWNER TO clstr_user; GRANT SELECT ON clstr_2 TO clstr_user; @@ -386,7 +379,6 @@ SELECT * FROM clstr_1; -- Test MVCC-safety of cluster. There isn't much we can do to verify the -- results with a single backend... CREATE TABLE clustertest (key int PRIMARY KEY); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "clustertest_pkey" for table "clustertest" INSERT INTO clustertest VALUES (10); INSERT INTO clustertest VALUES (20); INSERT INTO clustertest VALUES (30); @@ -436,7 +428,6 @@ SELECT * FROM clustertest; -- check that temp tables can be clustered create temp table clstr_temp (col1 int primary key, col2 text); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "clstr_temp_pkey" for table "clstr_temp" insert into clstr_temp values (2, 'two'), (1, 'one'); cluster clstr_temp using clstr_temp_pkey; select * from clstr_temp; diff --git a/src/test/regress/expected/collate.out b/src/test/regress/expected/collate.out index 81ac6de7b3..63ed976330 100644 --- a/src/test/regress/expected/collate.out +++ b/src/test/regress/expected/collate.out @@ -557,7 +557,6 @@ SET enable_seqscan TO 0; SET enable_hashjoin TO 0; SET enable_nestloop TO 0; CREATE TABLE collate_test20 (f1 text COLLATE "C" PRIMARY KEY); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "collate_test20_pkey" for table "collate_test20" INSERT INTO collate_test20 VALUES ('foo'), ('bar'); CREATE TABLE collate_test21 (f2 text COLLATE "POSIX" REFERENCES collate_test20); INSERT INTO collate_test21 VALUES ('foo'), ('bar'); diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out index 2449eefb16..b1d07b3e1f 100644 --- a/src/test/regress/expected/copy2.out +++ b/src/test/regress/expected/copy2.out @@ -5,7 +5,6 @@ CREATE TEMP TABLE x ( d text, e text ) WITH OIDS; -NOTICE: CREATE TABLE will create implicit sequence "x_a_seq" for serial column "x.a" CREATE FUNCTION fn_x_before () RETURNS TRIGGER AS ' BEGIN NEW.e := ''before trigger fired''::text; diff --git a/src/test/regress/expected/copyselect.out b/src/test/regress/expected/copyselect.out index 7c16a61733..72865fe1eb 100644 --- a/src/test/regress/expected/copyselect.out +++ b/src/test/regress/expected/copyselect.out @@ -2,14 +2,12 @@ -- Test cases for COPY (select) TO -- create table test1 (id serial, t text); -NOTICE: CREATE TABLE will create implicit sequence "test1_id_seq" for serial column "test1.id" insert into test1 (t) values ('a'); insert into test1 (t) values ('b'); insert into test1 (t) values ('c'); insert into test1 (t) values ('d'); insert into test1 (t) values ('e'); create table test2 (id serial, t text); -NOTICE: CREATE TABLE will create implicit sequence "test2_id_seq" for serial column "test2.id" insert into test2 (t) values ('A'); insert into test2 (t) values ('B'); insert into test2 (t) values ('C'); diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out index d20790f909..ade60c0258 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -205,18 +205,13 @@ CREATE TABLE IF NOT EXISTS test_tsvector( ); NOTICE: relation "test_tsvector" already exists, skipping CREATE UNLOGGED TABLE unlogged1 (a int primary key); -- OK -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "unlogged1_pkey" for table "unlogged1" INSERT INTO unlogged1 VALUES (42); CREATE UNLOGGED TABLE public.unlogged2 (a int primary key); -- also OK -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "unlogged2_pkey" for table "unlogged2" CREATE UNLOGGED TABLE pg_temp.unlogged3 (a int primary key); -- not OK ERROR: only temporary relations may be created in temporary schemas CREATE TABLE pg_temp.implicitly_temp (a int primary key); -- OK -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "implicitly_temp_pkey" for table "implicitly_temp" CREATE TEMP TABLE explicitly_temp (a int primary key); -- also OK -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "explicitly_temp_pkey" for table "explicitly_temp" CREATE TEMP TABLE pg_temp.doubly_temp (a int primary key); -- also OK -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "doubly_temp_pkey" for table "doubly_temp" CREATE TEMP TABLE public.temp_to_perm (a int primary key); -- not OK ERROR: cannot create temporary relation in non-temporary schema DROP TABLE unlogged1, public.unlogged2; diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out index 8bec55c3ca..3c6b585e60 100644 --- a/src/test/regress/expected/create_table_like.out +++ b/src/test/regress/expected/create_table_like.out @@ -49,7 +49,6 @@ SELECT * FROM inhf; /* Single entry with value 'text' */ ALTER TABLE inhx add constraint foo CHECK (xx = 'text'); ALTER TABLE inhx ADD PRIMARY KEY (xx); -NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "inhx_pkey" for table "inhx" CREATE TABLE inhg (LIKE inhx); /* Doesn't copy constraint */ INSERT INTO inhg VALUES ('foo'); DROP TABLE inhg; @@ -68,7 +67,6 @@ SELECT * FROM inhg; /* Two records with three columns in order x=x, xx=text, y=y DROP TABLE inhg; CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* copies indexes */ -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "inhg_pkey" for table "inhg" INSERT INTO inhg VALUES (5, 10); INSERT INTO inhg VALUES (20, 10); -- should fail ERROR: duplicate key value violates unique constraint "inhg_pkey" @@ -78,12 +76,9 @@ DROP TABLE inhg; CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, PRIMARY KEY(x)); /* fails */ ERROR: multiple primary keys for table "inhg" are not allowed CREATE TABLE inhz (xx text DEFAULT 'text', yy int UNIQUE); -NOTICE: CREATE TABLE / UNIQUE will create implicit index "inhz_yy_key" for table "inhz" CREATE UNIQUE INDEX inhz_xx_idx on inhz (xx) WHERE xx <> 'test'; /* Ok to create multiple unique indexes */ CREATE TABLE inhg (x text UNIQUE, LIKE inhz INCLUDING INDEXES); -NOTICE: CREATE TABLE / UNIQUE will create implicit index "inhg_x_key" for table "inhg" -NOTICE: CREATE TABLE / UNIQUE will create implicit index "inhg_yy_key" for table "inhg" INSERT INTO inhg (xx, yy, x) VALUES ('test', 5, 10); INSERT INTO inhg (xx, yy, x) VALUES ('test', 10, 15); INSERT INTO inhg (xx, yy, x) VALUES ('foo', 10, 15); -- should fail @@ -93,7 +88,6 @@ DROP TABLE inhg; DROP TABLE inhz; -- including storage and comments CREATE TABLE ctlt1 (a text CHECK (length(a) > 2) PRIMARY KEY, b text); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "ctlt1_pkey" for table "ctlt1" CREATE INDEX ctlt1_b_key ON ctlt1 (b); CREATE INDEX ctlt1_fnidx ON ctlt1 ((a || b)); COMMENT ON COLUMN ctlt1.a IS 'A'; @@ -192,7 +186,6 @@ SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_con (1 row) CREATE TABLE ctlt_all (LIKE ctlt1 INCLUDING ALL); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "ctlt_all_pkey" for table "ctlt_all" \d+ ctlt_all Table "public.ctlt_all" Column | Type | Modifiers | Storage | Stats target | Description diff --git a/src/test/regress/expected/delete.out b/src/test/regress/expected/delete.out index d3d1a357c0..e7eb3285f8 100644 --- a/src/test/regress/expected/delete.out +++ b/src/test/regress/expected/delete.out @@ -3,8 +3,6 @@ CREATE TABLE delete_test ( a INT, b text ); -NOTICE: CREATE TABLE will create implicit sequence "delete_test_id_seq" for serial column "delete_test.id" -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "delete_test_pkey" for table "delete_test" INSERT INTO delete_test (a) VALUES (10); INSERT INTO delete_test (a, b) VALUES (50, repeat('x', 10000)); INSERT INTO delete_test (a) VALUES (100); diff --git a/src/test/regress/expected/dependency.out b/src/test/regress/expected/dependency.out index 700def75b1..bcff8ddc17 100644 --- a/src/test/regress/expected/dependency.out +++ b/src/test/regress/expected/dependency.out @@ -6,8 +6,6 @@ CREATE USER regression_user2; CREATE USER regression_user3; CREATE GROUP regression_group; CREATE TABLE deptest (f1 serial primary key, f2 text); -NOTICE: CREATE TABLE will create implicit sequence "deptest_f1_seq" for serial column "deptest.f1" -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "deptest_pkey" for table "deptest" GRANT SELECT ON TABLE deptest TO GROUP regression_group; GRANT ALL ON TABLE deptest TO regression_user, regression_user2; -- can't drop neither because they have privileges somewhere @@ -59,12 +57,9 @@ ERROR: permission denied to reassign objects -- this one is allowed DROP OWNED BY regression_user0; CREATE TABLE deptest1 (f1 int unique); -NOTICE: CREATE TABLE / UNIQUE will create implicit index "deptest1_f1_key" for table "deptest1" GRANT ALL ON deptest1 TO regression_user1 WITH GRANT OPTION; SET SESSION AUTHORIZATION regression_user1; CREATE TABLE deptest (a serial primary key, b text); -NOTICE: CREATE TABLE will create implicit sequence "deptest_a_seq" for serial column "deptest.a" -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "deptest_pkey" for table "deptest" GRANT ALL ON deptest1 TO regression_user2; RESET SESSION AUTHORIZATION; \z deptest1 @@ -91,8 +86,6 @@ DROP OWNED BY regression_user1; GRANT ALL ON deptest1 TO regression_user1; SET SESSION AUTHORIZATION regression_user1; CREATE TABLE deptest (a serial primary key, b text); -NOTICE: CREATE TABLE will create implicit sequence "deptest_a_seq" for serial column "deptest.a" -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "deptest_pkey" for table "deptest" CREATE TABLE deptest2 (f1 int); -- make a serial column the hard way CREATE SEQUENCE ss1; diff --git a/src/test/regress/expected/domain.out b/src/test/regress/expected/domain.out index 03204ff79f..78e7704956 100644 --- a/src/test/regress/expected/domain.out +++ b/src/test/regress/expected/domain.out @@ -263,7 +263,6 @@ create table defaulttest , col7 ddef4 DEFAULT 8000 , col8 ddef5 ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "defaulttest_pkey" for table "defaulttest" insert into defaulttest(col4) values(0); -- fails, col5 defaults to null ERROR: null value in column "col5" violates not-null constraint DETAIL: Failing row contains (3, 12, 5, 0, null, 88, 8000, 12.12). diff --git a/src/test/regress/expected/enum.out b/src/test/regress/expected/enum.out index 06c445ac67..bf94af5ef6 100644 --- a/src/test/regress/expected/enum.out +++ b/src/test/regress/expected/enum.out @@ -516,7 +516,6 @@ DROP FUNCTION echo_me(rainbow); -- RI triggers on enum types -- CREATE TABLE enumtest_parent (id rainbow PRIMARY KEY); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "enumtest_parent_pkey" for table "enumtest_parent" CREATE TABLE enumtest_child (parent rainbow REFERENCES enumtest_parent); INSERT INTO enumtest_parent VALUES ('red'); INSERT INTO enumtest_child VALUES ('red'); diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out index af72c91d74..1bfdca134b 100644 --- a/src/test/regress/expected/foreign_data.out +++ b/src/test/regress/expected/foreign_data.out @@ -665,7 +665,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 (c1 serial) SERVER sc; -- ERROR -NOTICE: CREATE FOREIGN TABLE will create implicit sequence "ft1_c1_seq" for serial column "ft1.c1" ERROR: default values on foreign tables are not supported CREATE FOREIGN TABLE ft1 () SERVER s0 WITH OIDS; -- ERROR ERROR: syntax error at or near "WITH OIDS" diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out index 502307bac2..04668a8886 100644 --- a/src/test/regress/expected/foreign_key.out +++ b/src/test/regress/expected/foreign_key.out @@ -6,7 +6,6 @@ -- First test, check and cascade -- CREATE TABLE PKTABLE ( ptest1 int PRIMARY KEY, ptest2 text ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" CREATE TABLE FKTABLE ( ftest1 int REFERENCES PKTABLE MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE, ftest2 int ); -- Insert test data into PKTABLE INSERT INTO PKTABLE VALUES (1, 'Test1'); @@ -61,7 +60,6 @@ DROP TABLE PKTABLE; -- check set NULL and table constraint on multiple columns -- CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 text, PRIMARY KEY(ptest1, ptest2) ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, CONSTRAINT constrname FOREIGN KEY(ftest1, ftest2) REFERENCES PKTABLE MATCH FULL ON DELETE SET NULL ON UPDATE SET NULL); -- Test comments @@ -174,7 +172,6 @@ DROP TABLE FKTABLE; -- check set default and table constraint on multiple columns -- CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 text, PRIMARY KEY(ptest1, ptest2) ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" CREATE TABLE FKTABLE ( ftest1 int DEFAULT -1, ftest2 int DEFAULT -2, ftest3 int, CONSTRAINT constrname2 FOREIGN KEY(ftest1, ftest2) REFERENCES PKTABLE MATCH FULL ON DELETE SET DEFAULT ON UPDATE SET DEFAULT); -- Insert a value in PKTABLE for default @@ -267,7 +264,6 @@ DROP TABLE FKTABLE; -- First test, check with no on delete or on update -- CREATE TABLE PKTABLE ( ptest1 int PRIMARY KEY, ptest2 text ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" CREATE TABLE FKTABLE ( ftest1 int REFERENCES PKTABLE MATCH FULL, ftest2 int ); -- Insert test data into PKTABLE INSERT INTO PKTABLE VALUES (1, 'Test1'); @@ -342,7 +338,6 @@ DROP TABLE PKTABLE; -- MATCH SIMPLE -- Base test restricting update/delete CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, ftest4 int, CONSTRAINT constrname3 FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE); -- Insert Primary Key values @@ -406,7 +401,6 @@ DROP TABLE FKTABLE; DROP TABLE PKTABLE; -- cascade update/delete CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, ftest4 int, CONSTRAINT constrname3 FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE ON DELETE CASCADE ON UPDATE CASCADE); @@ -503,7 +497,6 @@ DROP TABLE FKTABLE; DROP TABLE PKTABLE; -- set null update / set default delete CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" CREATE TABLE FKTABLE ( ftest1 int DEFAULT 0, ftest2 int, ftest3 int, ftest4 int, CONSTRAINT constrname3 FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE ON DELETE SET DEFAULT ON UPDATE SET NULL); @@ -607,7 +600,6 @@ DROP TABLE FKTABLE; DROP TABLE PKTABLE; -- set default update / set null delete CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" CREATE TABLE FKTABLE ( ftest1 int DEFAULT 0, ftest2 int DEFAULT -1, ftest3 int DEFAULT -2, ftest4 int, CONSTRAINT constrname3 FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE ON DELETE SET NULL ON UPDATE SET DEFAULT); @@ -724,7 +716,6 @@ SELECT * from FKTABLE; DROP TABLE FKTABLE; DROP TABLE PKTABLE; CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" CREATE TABLE FKTABLE_FAIL1 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest2) REFERENCES PKTABLE); ERROR: column "ftest2" referenced in foreign key constraint does not exist CREATE TABLE FKTABLE_FAIL2 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest1) REFERENCES PKTABLE(ptest2)); @@ -736,7 +727,6 @@ ERROR: table "fktable_fail2" does not exist DROP TABLE PKTABLE; -- Test for referencing column number smaller than referenced constraint CREATE TABLE PKTABLE (ptest1 int, ptest2 int, UNIQUE(ptest1, ptest2)); -NOTICE: CREATE TABLE / UNIQUE will create implicit index "pktable_ptest1_ptest2_key" for table "pktable" CREATE TABLE FKTABLE_FAIL1 (ftest1 int REFERENCES pktable(ptest1)); ERROR: there is no unique constraint matching given keys for referenced table "pktable" DROP TABLE FKTABLE_FAIL1; @@ -747,7 +737,6 @@ DROP TABLE PKTABLE; -- -- Basic one column, two table setup CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" INSERT INTO PKTABLE VALUES(42); -- This next should fail, because int=inet does not exist CREATE TABLE FKTABLE (ftest1 inet REFERENCES pktable); @@ -781,7 +770,6 @@ DROP TABLE PKTABLE; -- On the other hand, this should work because int implicitly promotes to -- numeric, and we allow promotion on the FK side CREATE TABLE PKTABLE (ptest1 numeric PRIMARY KEY); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" INSERT INTO PKTABLE VALUES(42); CREATE TABLE FKTABLE (ftest1 int REFERENCES pktable); -- Check it actually works @@ -797,7 +785,6 @@ DROP TABLE FKTABLE; DROP TABLE PKTABLE; -- Two columns, two tables CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, PRIMARY KEY(ptest1, ptest2)); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" -- This should fail, because we just chose really odd types CREATE TABLE FKTABLE (ftest1 cidr, ftest2 timestamp, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable); ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented @@ -829,29 +816,24 @@ DROP TABLE PKTABLE; -- Make sure this still works... CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest3, ptest4) REFERENCES pktable(ptest1, ptest2)); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" DROP TABLE PKTABLE; -- And this, CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest3, ptest4) REFERENCES pktable); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" DROP TABLE PKTABLE; -- This shouldn't (mixed up columns) CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest3, ptest4) REFERENCES pktable(ptest2, ptest1)); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" ERROR: foreign key constraint "pktable_ptest3_fkey" cannot be implemented DETAIL: Key columns "ptest3" and "ptest2" are of incompatible types: integer and inet. -- Nor should this... (same reason, we have 4,3 referencing 1,2 which mismatches types CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest4, ptest3) REFERENCES pktable(ptest1, ptest2)); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" ERROR: foreign key constraint "pktable_ptest4_fkey" cannot be implemented DETAIL: Key columns "ptest4" and "ptest1" are of incompatible types: inet and integer. -- Not this one either... Same as the last one except we didn't defined the columns being referenced. CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest4, ptest3) REFERENCES pktable); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" ERROR: foreign key constraint "pktable_ptest4_fkey" cannot be implemented DETAIL: Key columns "ptest4" and "ptest1" are of incompatible types: inet and integer. -- @@ -859,8 +841,6 @@ DETAIL: Key columns "ptest4" and "ptest1" are of incompatible types: inet and i -- Basic 2 table case: 1 column of matching types. create table pktable_base (base1 int not null); create table pktable (ptest1 int, primary key(base1), unique(base1, ptest1)) inherits (pktable_base); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" -NOTICE: CREATE TABLE / UNIQUE will create implicit index "pktable_base1_ptest1_key" for table "pktable" create table fktable (ftest1 int references pktable(base1)); -- now some ins, upd, del insert into pktable(base1) values (1); @@ -921,7 +901,6 @@ drop table pktable_base; create table pktable_base(base1 int not null, base2 int); create table pktable(ptest1 int, ptest2 int, primary key(base1, ptest1), foreign key(base2, ptest2) references pktable(base1, ptest1)) inherits (pktable_base); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" insert into pktable (base1, ptest1, base2, ptest2) values (1, 1, 1, 1); insert into pktable (base1, ptest1, base2, ptest2) values (2, 1, 1, 1); insert into pktable (base1, ptest1, base2, ptest2) values (2, 2, 2, 1); @@ -946,7 +925,6 @@ drop table pktable_base; -- 2 columns (2 tables), mismatched types create table pktable_base(base1 int not null); create table pktable(ptest1 inet, primary key(base1, ptest1)) inherits (pktable_base); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" -- just generally bad types (with and without column references on the referenced table) create table fktable(ftest1 cidr, ftest2 int[], foreign key (ftest1, ftest2) references pktable); ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented @@ -970,22 +948,18 @@ drop table pktable_base; create table pktable_base(base1 int not null, base2 int); create table pktable(ptest1 inet, ptest2 inet[], primary key(base1, ptest1), foreign key(base2, ptest2) references pktable(base1, ptest1)) inherits (pktable_base); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" ERROR: foreign key constraint "pktable_base2_fkey" cannot be implemented DETAIL: Key columns "ptest2" and "ptest1" are of incompatible types: inet[] and inet. create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), foreign key(base2, ptest2) references pktable(ptest1, base1)) inherits (pktable_base); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" ERROR: foreign key constraint "pktable_base2_fkey" cannot be implemented DETAIL: Key columns "base2" and "ptest1" are of incompatible types: integer and inet. create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), foreign key(ptest2, base2) references pktable(base1, ptest1)) inherits (pktable_base); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" ERROR: foreign key constraint "pktable_ptest2_fkey" cannot be implemented DETAIL: Key columns "ptest2" and "base1" are of incompatible types: inet and integer. create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), foreign key(ptest2, base2) references pktable(base1, ptest1)) inherits (pktable_base); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" ERROR: foreign key constraint "pktable_ptest2_fkey" cannot be implemented DETAIL: Key columns "ptest2" and "base1" are of incompatible types: inet and integer. drop table pktable; @@ -1000,12 +974,10 @@ CREATE TABLE pktable ( id INT4 PRIMARY KEY, other INT4 ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" CREATE TABLE fktable ( id INT4 PRIMARY KEY, fk INT4 REFERENCES pktable DEFERRABLE ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "fktable_pkey" for table "fktable" -- default to immediate: should fail INSERT INTO fktable VALUES (5, 10); ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey" @@ -1022,12 +994,10 @@ CREATE TABLE pktable ( id INT4 PRIMARY KEY, other INT4 ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" CREATE TABLE fktable ( id INT4 PRIMARY KEY, fk INT4 REFERENCES pktable DEFERRABLE INITIALLY DEFERRED ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "fktable_pkey" for table "fktable" -- default to deferred, should succeed BEGIN; INSERT INTO fktable VALUES (100, 200); @@ -1050,12 +1020,10 @@ CREATE TABLE pktable ( id INT4 PRIMARY KEY, other INT4 ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" CREATE TABLE fktable ( id INT4 PRIMARY KEY, fk INT4 REFERENCES pktable DEFERRABLE ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "fktable_pkey" for table "fktable" BEGIN; SET CONSTRAINTS ALL DEFERRED; -- should succeed, for now @@ -1073,12 +1041,10 @@ CREATE TABLE pktable ( id INT4 PRIMARY KEY, other INT4 ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" CREATE TABLE fktable ( id INT4 PRIMARY KEY, fk INT4 REFERENCES pktable DEFERRABLE INITIALLY DEFERRED ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "fktable_pkey" for table "fktable" BEGIN; -- no error here INSERT INTO fktable VALUES (100, 200); @@ -1095,10 +1061,6 @@ CREATE TEMP TABLE pktable ( id3 REAL UNIQUE, UNIQUE(id1, id2, id3) ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" -NOTICE: CREATE TABLE / UNIQUE will create implicit index "pktable_id2_key" for table "pktable" -NOTICE: CREATE TABLE / UNIQUE will create implicit index "pktable_id3_key" for table "pktable" -NOTICE: CREATE TABLE / UNIQUE will create implicit index "pktable_id1_id2_id3_key" for table "pktable" CREATE TEMP TABLE fktable ( x1 INT4 REFERENCES pktable(id1), x2 VARCHAR(4) REFERENCES pktable(id2), @@ -1166,12 +1128,10 @@ CREATE TEMP TABLE pktable ( id int primary key, other int ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" CREATE TEMP TABLE fktable ( id int primary key, fk int references pktable deferrable initially deferred ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "fktable_pkey" for table "fktable" INSERT INTO pktable VALUES (5, 10); BEGIN; -- doesn't match PK, but no error yet @@ -1226,7 +1186,6 @@ CREATE TEMP TABLE users ( id INT PRIMARY KEY, name VARCHAR NOT NULL ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users" INSERT INTO users VALUES (1, 'Jozko'); INSERT INTO users VALUES (2, 'Ferko'); INSERT INTO users VALUES (3, 'Samko'); @@ -1236,7 +1195,6 @@ CREATE TEMP TABLE tasks ( worker INT REFERENCES users ON UPDATE CASCADE ON DELETE SET NULL, checked_by INT REFERENCES users ON UPDATE CASCADE ON DELETE SET NULL ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tasks_pkey" for table "tasks" INSERT INTO tasks VALUES (1,1,NULL,NULL); INSERT INTO tasks VALUES (2,2,2,NULL); INSERT INTO tasks VALUES (3,3,3,3); @@ -1296,7 +1254,6 @@ create temp table selfref ( foreign key (b) references selfref (a) on update cascade on delete cascade ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "selfref_pkey" for table "selfref" insert into selfref (a, b) values (0, 0), @@ -1323,7 +1280,6 @@ commit; -- Test that SET DEFAULT actions recognize updates to default values -- create temp table defp (f1 int primary key); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "defp_pkey" for table "defp" create temp table defc (f1 int default 0 references defp on delete set default); insert into defp values (0), (1), (2); @@ -1359,7 +1315,6 @@ DETAIL: Key (f1)=(1) is still referenced from table "defc". -- Test the difference between NO ACTION and RESTRICT -- create temp table pp (f1 int primary key); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pp_pkey" for table "pp" create temp table cc (f1 int references pp on update no action); insert into pp values(12); insert into pp values(11); @@ -1371,7 +1326,6 @@ ERROR: update or delete on table "pp" violates foreign key constraint "cc_f1_fk DETAIL: Key (f1)=(13) is still referenced from table "cc". drop table pp, cc; create temp table pp (f1 int primary key); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pp_pkey" for table "pp" create temp table cc (f1 int references pp on update restrict); insert into pp values(12); insert into pp values(11); diff --git a/src/test/regress/expected/functional_deps.out b/src/test/regress/expected/functional_deps.out index 21e3652533..32381b8ae7 100644 --- a/src/test/regress/expected/functional_deps.out +++ b/src/test/regress/expected/functional_deps.out @@ -6,16 +6,12 @@ CREATE TEMP TABLE articles ( body text UNIQUE, created date ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "articles_pkey" for table "articles" -NOTICE: CREATE TABLE / UNIQUE will create implicit index "articles_title_key" for table "articles" -NOTICE: CREATE TABLE / UNIQUE will create implicit index "articles_body_key" for table "articles" CREATE TEMP TABLE articles_in_category ( article_id int, category_id int, changed date, PRIMARY KEY (article_id, category_id) ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "articles_in_category_pkey" for table "articles_in_category" -- test functional dependencies based on primary keys/unique constraints -- base tables -- group by primary key (OK) @@ -119,7 +115,6 @@ ERROR: column "p.name" must appear in the GROUP BY clause or be used in an aggr LINE 1: SELECT product_id, p.name, (sum(s.units) * p.price) AS sales ^ ALTER TABLE products ADD PRIMARY KEY (product_id); -NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "products_pkey" for table "products" -- OK now SELECT product_id, p.name, (sum(s.units) * p.price) AS sales FROM products p LEFT JOIN sales s USING (product_id) @@ -140,8 +135,6 @@ CREATE TEMP TABLE node ( -- snip PRIMARY KEY (nid, vid) ); -NOTICE: CREATE TABLE will create implicit sequence "node_nid_seq" for serial column "node.nid" -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "node_pkey" for table "node" CREATE TEMP TABLE users ( uid integer NOT NULL default '0', name varchar(60) NOT NULL default '', @@ -150,8 +143,6 @@ CREATE TEMP TABLE users ( PRIMARY KEY (uid), UNIQUE (name) ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users" -NOTICE: CREATE TABLE / UNIQUE will create implicit index "users_name_key" for table "users" -- OK SELECT u.uid, u.name FROM node n INNER JOIN users u ON u.uid = n.uid diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index 6613fea84a..3128154195 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -536,7 +536,6 @@ SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid; -- Confirm PRIMARY KEY adds NOT NULL constraint to child table CREATE TEMP TABLE z (b TEXT, PRIMARY KEY(aa, b)) inherits (a); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "z_pkey" for table "z" INSERT INTO z VALUES (NULL, 'text'); -- should fail ERROR: null value in column "aa" violates not-null constraint DETAIL: Failing row contains (null, text). @@ -1044,17 +1043,12 @@ drop cascades to table patest2 -- Test merge-append plans for inheritance trees -- create table matest0 (id serial primary key, name text); -NOTICE: CREATE TABLE will create implicit sequence "matest0_id_seq" for serial column "matest0.id" -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "matest0_pkey" for table "matest0" create table matest1 (id integer primary key) inherits (matest0); NOTICE: merging column "id" with inherited definition -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "matest1_pkey" for table "matest1" create table matest2 (id integer primary key) inherits (matest0); NOTICE: merging column "id" with inherited definition -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "matest2_pkey" for table "matest2" create table matest3 (id integer primary key) inherits (matest0); NOTICE: merging column "id" with inherited definition -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "matest3_pkey" for table "matest3" create index matest0i on matest0 ((1-id)); create index matest1i on matest1 ((1-id)); -- create index matest2i on matest2 ((1-id)); -- intentionally missing diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index b8b069b455..c46d35df89 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -2323,11 +2323,8 @@ from yy -- (as seen in early 8.2.x releases) -- create temp table zt1 (f1 int primary key); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "zt1_pkey" for table "zt1" create temp table zt2 (f2 int primary key); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "zt2_pkey" for table "zt2" create temp table zt3 (f3 int primary key); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "zt3_pkey" for table "zt3" insert into zt1 values(53); insert into zt2 values(53); select * from @@ -2452,19 +2449,16 @@ create temp table a ( code char not null, constraint a_pk primary key (code) ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "a_pk" for table "a" create temp table b ( a char not null, num integer not null, constraint b_pk primary key (a, num) ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "b_pk" for table "b" create temp table c ( name char not null, a char, constraint c_pk primary key (name) ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "c_pk" for table "c" insert into a (code) values ('p'); insert into a (code) values ('q'); insert into b (a, num) values ('p', 1); @@ -2831,11 +2825,8 @@ select b.unique1 from -- begin; CREATE TEMP TABLE a (id int PRIMARY KEY, b_id int); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for table "a" CREATE TEMP TABLE b (id int PRIMARY KEY, c_id int); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "b_pkey" for table "b" CREATE TEMP TABLE c (id int PRIMARY KEY); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "c_pkey" for table "c" INSERT INTO a VALUES (0, 0), (1, NULL); INSERT INTO b VALUES (0, 0), (1, NULL); INSERT INTO c VALUES (0), (1); @@ -2876,9 +2867,7 @@ select id from a where id in ( rollback; create temp table parent (k int primary key, pd int); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "parent_pkey" for table "parent" create temp table child (k int unique, cd int); -NOTICE: CREATE TABLE / UNIQUE will create implicit index "child_k_key" for table "child" insert into parent values (1, 10), (2, 20), (3, 30); insert into child values (1, 100), (4, 400); -- this case is optimizable @@ -2961,9 +2950,7 @@ select p.* from -- bug 5255: this is not optimizable by join removal begin; CREATE TEMP TABLE a (id int PRIMARY KEY); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for table "a" CREATE TEMP TABLE b (id int PRIMARY KEY, a_id int); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "b_pkey" for table "b" INSERT INTO a VALUES (0), (1); INSERT INTO b VALUES (0, 0), (1, NULL); SELECT * FROM b LEFT JOIN a ON (b.a_id = a.id) WHERE (a.id IS NULL OR a.id > 0); @@ -2982,7 +2969,6 @@ rollback; -- another join removal bug: this is not optimizable, either begin; create temp table innertab (id int8 primary key, dat1 int8); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "innertab_pkey" for table "innertab" insert into innertab values(123, 42); SELECT * FROM (SELECT 1 AS x) ss1 diff --git a/src/test/regress/expected/namespace.out b/src/test/regress/expected/namespace.out index 58a0c4dc81..7c26da5636 100644 --- a/src/test/regress/expected/namespace.out +++ b/src/test/regress/expected/namespace.out @@ -9,8 +9,6 @@ CREATE SCHEMA test_schema_1 a serial, b int UNIQUE ); -NOTICE: CREATE TABLE will create implicit sequence "abc_a_seq" for serial column "abc.a" -NOTICE: CREATE TABLE / UNIQUE will create implicit index "abc_b_key" for table "abc" -- verify that the objects were created SELECT COUNT(*) FROM pg_class WHERE relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'test_schema_1'); diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out index fb47fa7d45..30053363f3 100644 --- a/src/test/regress/expected/plpgsql.out +++ b/src/test/regress/expected/plpgsql.out @@ -2083,7 +2083,6 @@ PL/pgSQL function test_variable_storage() line 8 at PERFORM -- test foreign key error trapping -- create temp table master(f1 int primary key); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "master_pkey" for table "master" create temp table slave(f1 int references master deferrable); insert into master values(1); insert into slave values(1); @@ -2154,7 +2153,6 @@ drop function trap_foreign_key_2(); -- Test proper snapshot handling in simple expressions -- create temp table users(login text, id serial); -NOTICE: CREATE TABLE will create implicit sequence "users_id_seq" for serial column "users.id" create function sp_id_user(a_login text) returns int as $$ declare x int; begin @@ -2669,7 +2667,6 @@ NOTICE: {10,20,30}; 20; xyz; xyzabc; (10,aaa,,30); drop function raise_exprs(); -- continue statement create table conttesttbl(idx serial, v integer); -NOTICE: CREATE TABLE will create implicit sequence "conttesttbl_idx_seq" for serial column "conttesttbl.idx" insert into conttesttbl(v) values(10); insert into conttesttbl(v) values(20); insert into conttesttbl(v) values(30); diff --git a/src/test/regress/expected/rangefuncs.out b/src/test/regress/expected/rangefuncs.out index 9fbdd83f67..6710b9c15e 100644 --- a/src/test/regress/expected/rangefuncs.out +++ b/src/test/regress/expected/rangefuncs.out @@ -58,7 +58,6 @@ select foot.fooid, foot.f2 from foot(sin(pi()/2)::int) ORDER BY 1,2; (2 rows) CREATE TABLE foo (fooid int, foosubid int, fooname text, primary key(fooid,foosubid)); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo" INSERT INTO foo VALUES(1,1,'Joe'); INSERT INTO foo VALUES(1,2,'Ed'); INSERT INTO foo VALUES(2,1,'Mary'); @@ -230,7 +229,6 @@ DROP TABLE foo2; DROP TABLE foo; -- Rescan tests -- CREATE TABLE foorescan (fooid int, foosubid int, fooname text, primary key(fooid,foosubid)); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foorescan_pkey" for table "foorescan" INSERT INTO foorescan values(5000,1,'abc.5000.1'); INSERT INTO foorescan values(5001,1,'abc.5001.1'); INSERT INTO foorescan values(5002,1,'abc.5002.1'); @@ -316,7 +314,6 @@ SELECT * FROM foorescan f WHERE f.fooid IN (SELECT fooid FROM vw_foorescan) ORDE (10 rows) CREATE TABLE barrescan (fooid int primary key); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "barrescan_pkey" for table "barrescan" INSERT INTO barrescan values(5003); INSERT INTO barrescan values(5004); INSERT INTO barrescan values(5005); @@ -579,7 +576,6 @@ DROP FUNCTION foo(int); -- some tests on SQL functions with RETURNING -- create temp table tt(f1 serial, data text); -NOTICE: CREATE TABLE will create implicit sequence "tt_f1_seq" for serial column "tt.f1" create function insert_tt(text) returns int as $$ insert into tt(data) values($1) returning f1 $$ language sql; diff --git a/src/test/regress/expected/rangetypes.out b/src/test/regress/expected/rangetypes.out index 094b89493e..e37fab3972 100644 --- a/src/test/regress/expected/rangetypes.out +++ b/src/test/regress/expected/rangetypes.out @@ -836,8 +836,6 @@ create table test_range_excl( exclude using gist (room with =, during with &&), exclude using gist (speaker with =, during with &&) ); -NOTICE: CREATE TABLE / EXCLUDE will create implicit index "test_range_excl_room_during_excl" for table "test_range_excl" -NOTICE: CREATE TABLE / EXCLUDE will create implicit index "test_range_excl_speaker_during_excl" for table "test_range_excl" insert into test_range_excl values(int4range(123, 123, '[]'), int4range(1, 1, '[]'), '[2010-01-02 10:00, 2010-01-02 11:00)'); insert into test_range_excl diff --git a/src/test/regress/expected/returning.out b/src/test/regress/expected/returning.out index b04f6f1a0e..69bdacc103 100644 --- a/src/test/regress/expected/returning.out +++ b/src/test/regress/expected/returning.out @@ -3,7 +3,6 @@ -- -- Simple cases CREATE TEMP TABLE foo (f1 serial, f2 text, f3 int default 42); -NOTICE: CREATE TABLE will create implicit sequence "foo_f1_seq" for serial column "foo.f1" INSERT INTO foo (f2,f3) VALUES ('test', DEFAULT), ('More', 11), (upper('more'), 7+9) RETURNING *, f1+f3 AS sum; diff --git a/src/test/regress/expected/rowtypes.out b/src/test/regress/expected/rowtypes.out index a5ddbdc140..d93150aea6 100644 --- a/src/test/regress/expected/rowtypes.out +++ b/src/test/regress/expected/rowtypes.out @@ -305,8 +305,6 @@ CREATE TABLE price ( active BOOLEAN NOT NULL, price NUMERIC ); -NOTICE: CREATE TABLE will create implicit sequence "price_id_seq" for serial column "price.id" -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "price_pkey" for table "price" CREATE TYPE price_input AS ( id INTEGER, price NUMERIC diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 23c84ade53..73bdd7cd7b 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1400,13 +1400,11 @@ create table rule_and_refint_t1 ( id1b integer, primary key (id1a, id1b) ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "rule_and_refint_t1_pkey" for table "rule_and_refint_t1" create table rule_and_refint_t2 ( id2a integer, id2c integer, primary key (id2a, id2c) ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "rule_and_refint_t2_pkey" for table "rule_and_refint_t2" create table rule_and_refint_t3 ( id3a integer, id3b integer, @@ -1416,7 +1414,6 @@ create table rule_and_refint_t3 ( foreign key (id3a, id3b) references rule_and_refint_t1 (id1a, id1b), foreign key (id3a, id3c) references rule_and_refint_t2 (id2a, id2c) ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "rule_and_refint_t3_pkey" for table "rule_and_refint_t3" insert into rule_and_refint_t1 values (1, 11); insert into rule_and_refint_t1 values (1, 12); insert into rule_and_refint_t1 values (2, 21); @@ -1462,18 +1459,13 @@ drop view fooview; -- check for planner problems with complex inherited UPDATES -- create table id (id serial primary key, name text); -NOTICE: CREATE TABLE will create implicit sequence "id_id_seq" for serial column "id.id" -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "id_pkey" for table "id" -- currently, must respecify PKEY for each inherited subtable create table test_1 (id integer primary key) inherits (id); NOTICE: merging column "id" with inherited definition -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_1_pkey" for table "test_1" create table test_2 (id integer primary key) inherits (id); NOTICE: merging column "id" with inherited definition -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_2_pkey" for table "test_2" create table test_3 (id integer primary key) inherits (id); NOTICE: merging column "id" with inherited definition -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_3_pkey" for table "test_3" insert into test_1 (name) values ('Test 1'); insert into test_1 (name) values ('Test 2'); insert into test_2 (name) values ('Test 3'); @@ -1516,7 +1508,6 @@ reset client_min_messages; -- constraint exclusion -- create temp table t1 (a integer primary key); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1" create temp table t1_1 (check (a >= 0 and a < 10)) inherits (t1); create temp table t1_2 (check (a >= 10 and a < 20)) inherits (t1); create rule t1_ins_1 as on insert to t1 diff --git a/src/test/regress/expected/select_views.out b/src/test/regress/expected/select_views.out index 8278ede8bf..e4eba1ae36 100644 --- a/src/test/regress/expected/select_views.out +++ b/src/test/regress/expected/select_views.out @@ -1260,7 +1260,6 @@ CREATE TABLE customer ( tel text, passwd text ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "customer_pkey" for table "customer" CREATE TABLE credit_card ( cid int references customer(cid), cnum text, diff --git a/src/test/regress/expected/select_views_1.out b/src/test/regress/expected/select_views_1.out index 986b4b2d4a..94b439825c 100644 --- a/src/test/regress/expected/select_views_1.out +++ b/src/test/regress/expected/select_views_1.out @@ -1260,7 +1260,6 @@ CREATE TABLE customer ( tel text, passwd text ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "customer_pkey" for table "customer" CREATE TABLE credit_card ( cid int references customer(cid), cnum text, diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out index 1def070903..578778c312 100644 --- a/src/test/regress/expected/sequence.out +++ b/src/test/regress/expected/sequence.out @@ -2,7 +2,6 @@ --- test creation of SERIAL column --- CREATE TABLE serialTest (f1 text, f2 serial); -NOTICE: CREATE TABLE will create implicit sequence "serialtest_f2_seq" for serial column "serialtest.f2" INSERT INTO serialTest VALUES ('foo'); INSERT INTO serialTest VALUES ('bar'); INSERT INTO serialTest VALUES ('force', 100); @@ -20,11 +19,6 @@ SELECT * FROM serialTest; -- test smallserial / bigserial CREATE TABLE serialTest2 (f1 text, f2 serial, f3 smallserial, f4 serial2, f5 bigserial, f6 serial8); -NOTICE: CREATE TABLE will create implicit sequence "serialtest2_f2_seq" for serial column "serialtest2.f2" -NOTICE: CREATE TABLE will create implicit sequence "serialtest2_f3_seq" for serial column "serialtest2.f3" -NOTICE: CREATE TABLE will create implicit sequence "serialtest2_f4_seq" for serial column "serialtest2.f4" -NOTICE: CREATE TABLE will create implicit sequence "serialtest2_f5_seq" for serial column "serialtest2.f5" -NOTICE: CREATE TABLE will create implicit sequence "serialtest2_f6_seq" for serial column "serialtest2.f6" INSERT INTO serialTest2 (f1) VALUES ('test_defaults'); INSERT INTO serialTest2 (f1, f2, f3, f4, f5, f6) @@ -220,7 +214,6 @@ CREATE TEMP TABLE t1 ( f2 int DEFAULT nextval('myseq2'), f3 int DEFAULT nextval('myseq3'::text) ); -NOTICE: CREATE TABLE will create implicit sequence "t1_f1_seq" for serial column "t1.f1" -- Both drops should fail, but with different error messages: DROP SEQUENCE t1_f1_seq; ERROR: cannot drop sequence t1_f1_seq because other objects depend on it diff --git a/src/test/regress/expected/sequence_1.out b/src/test/regress/expected/sequence_1.out index 918c88d1cc..22399ab68d 100644 --- a/src/test/regress/expected/sequence_1.out +++ b/src/test/regress/expected/sequence_1.out @@ -2,7 +2,6 @@ --- test creation of SERIAL column --- CREATE TABLE serialTest (f1 text, f2 serial); -NOTICE: CREATE TABLE will create implicit sequence "serialtest_f2_seq" for serial column "serialtest.f2" INSERT INTO serialTest VALUES ('foo'); INSERT INTO serialTest VALUES ('bar'); INSERT INTO serialTest VALUES ('force', 100); @@ -20,11 +19,6 @@ SELECT * FROM serialTest; -- test smallserial / bigserial CREATE TABLE serialTest2 (f1 text, f2 serial, f3 smallserial, f4 serial2, f5 bigserial, f6 serial8); -NOTICE: CREATE TABLE will create implicit sequence "serialtest2_f2_seq" for serial column "serialtest2.f2" -NOTICE: CREATE TABLE will create implicit sequence "serialtest2_f3_seq" for serial column "serialtest2.f3" -NOTICE: CREATE TABLE will create implicit sequence "serialtest2_f4_seq" for serial column "serialtest2.f4" -NOTICE: CREATE TABLE will create implicit sequence "serialtest2_f5_seq" for serial column "serialtest2.f5" -NOTICE: CREATE TABLE will create implicit sequence "serialtest2_f6_seq" for serial column "serialtest2.f6" INSERT INTO serialTest2 (f1) VALUES ('test_defaults'); INSERT INTO serialTest2 (f1, f2, f3, f4, f5, f6) @@ -220,7 +214,6 @@ CREATE TEMP TABLE t1 ( f2 int DEFAULT nextval('myseq2'), f3 int DEFAULT nextval('myseq3'::text) ); -NOTICE: CREATE TABLE will create implicit sequence "t1_f1_seq" for serial column "t1.f1" -- Both drops should fail, but with different error messages: DROP SEQUENCE t1_f1_seq; ERROR: cannot drop sequence t1_f1_seq because other objects depend on it diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out index 4ea8211c69..ad870f665f 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -442,17 +442,14 @@ select * from numeric_table -- Test case for bug #4290: bogus calculation of subplan param sets -- create temp table ta (id int primary key, val int); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "ta_pkey" for table "ta" insert into ta values(1,1); insert into ta values(2,2); create temp table tb (id int primary key, aval int); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tb_pkey" for table "tb" insert into tb values(1,1); insert into tb values(2,1); insert into tb values(3,2); insert into tb values(4,2); create temp table tc (id int primary key, aid int); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tc_pkey" for table "tc" insert into tc values(1,1); insert into tc values(2,2); select diff --git a/src/test/regress/expected/temp.out b/src/test/regress/expected/temp.out index c39a88ad51..addf1ec444 100644 --- a/src/test/regress/expected/temp.out +++ b/src/test/regress/expected/temp.out @@ -119,7 +119,6 @@ ERROR: ON COMMIT can only be used on temporary tables -- Test foreign keys BEGIN; CREATE TEMP TABLE temptest1(col int PRIMARY KEY); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "temptest1_pkey" for table "temptest1" CREATE TEMP TABLE temptest2(col int REFERENCES temptest1) ON COMMIT DELETE ROWS; INSERT INTO temptest1 VALUES (1); @@ -138,7 +137,6 @@ SELECT * FROM temptest2; BEGIN; CREATE TEMP TABLE temptest3(col int PRIMARY KEY) ON COMMIT DELETE ROWS; -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "temptest3_pkey" for table "temptest3" CREATE TEMP TABLE temptest4(col int REFERENCES temptest3); COMMIT; ERROR: unsupported ON COMMIT and foreign key combination diff --git a/src/test/regress/expected/transactions.out b/src/test/regress/expected/transactions.out index 6981692da9..5c84ec50ca 100644 --- a/src/test/regress/expected/transactions.out +++ b/src/test/regress/expected/transactions.out @@ -538,14 +538,12 @@ rollback; BEGIN; savepoint x; CREATE TABLE koju (a INT UNIQUE); -NOTICE: CREATE TABLE / UNIQUE will create implicit index "koju_a_key" for table "koju" INSERT INTO koju VALUES (1); INSERT INTO koju VALUES (1); ERROR: duplicate key value violates unique constraint "koju_a_key" DETAIL: Key (a)=(1) already exists. rollback to x; CREATE TABLE koju (a INT UNIQUE); -NOTICE: CREATE TABLE / UNIQUE will create implicit index "koju_a_key" for table "koju" INSERT INTO koju VALUES (1); INSERT INTO koju VALUES (1); ERROR: duplicate key value violates unique constraint "koju_a_key" diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out index 0e7177edc1..b5af066558 100644 --- a/src/test/regress/expected/triggers.out +++ b/src/test/regress/expected/triggers.out @@ -529,8 +529,6 @@ ALTER TABLE main_table DROP COLUMN b; rollback; -- Test enable/disable triggers create table trigtest (i serial primary key); -NOTICE: CREATE TABLE will create implicit sequence "trigtest_i_seq" for serial column "trigtest.i" -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "trigtest_pkey" for table "trigtest" -- test that disabling RI triggers works create table trigtest2 (i int references trigtest(i) on delete cascade); create function trigtest() returns trigger as $$ @@ -1155,9 +1153,6 @@ CREATE TABLE country_table ( country_name text unique not null, continent text not null ); -NOTICE: CREATE TABLE will create implicit sequence "country_table_country_id_seq" for serial column "country_table.country_id" -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "country_table_pkey" for table "country_table" -NOTICE: CREATE TABLE / UNIQUE will create implicit index "country_table_country_name_key" for table "country_table" INSERT INTO country_table (country_name, continent) VALUES ('Japan', 'Asia'), ('UK', 'Europe'), @@ -1176,8 +1171,6 @@ CREATE TABLE city_table ( population bigint, country_id int references country_table ); -NOTICE: CREATE TABLE will create implicit sequence "city_table_city_id_seq" for serial column "city_table.city_id" -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "city_table_pkey" for table "city_table" CREATE VIEW city_view AS SELECT city_id, city_name, population, country_name, continent FROM city_table ci @@ -1445,11 +1438,8 @@ drop cascades to view european_city_view DROP TABLE country_table; -- Test pg_trigger_depth() create table depth_a (id int not null primary key); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "depth_a_pkey" for table "depth_a" create table depth_b (id int not null primary key); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "depth_b_pkey" for table "depth_b" create table depth_c (id int not null primary key); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "depth_c_pkey" for table "depth_c" create function depth_a_tf() returns trigger language plpgsql as $$ begin diff --git a/src/test/regress/expected/truncate.out b/src/test/regress/expected/truncate.out index 1817bac8e3..5c5277e0f1 100644 --- a/src/test/regress/expected/truncate.out +++ b/src/test/regress/expected/truncate.out @@ -1,6 +1,5 @@ -- Test basic TRUNCATE functionality. CREATE TABLE truncate_a (col1 integer primary key); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "truncate_a_pkey" for table "truncate_a" INSERT INTO truncate_a VALUES (1); INSERT INTO truncate_a VALUES (2); SELECT * FROM truncate_a; @@ -33,8 +32,6 @@ SELECT * FROM truncate_a; -- Test foreign-key checks CREATE TABLE trunc_b (a int REFERENCES truncate_a); CREATE TABLE trunc_c (a serial PRIMARY KEY); -NOTICE: CREATE TABLE will create implicit sequence "trunc_c_a_seq" for serial column "trunc_c.a" -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "trunc_c_pkey" for table "trunc_c" CREATE TABLE trunc_d (a int REFERENCES trunc_c); CREATE TABLE trunc_e (a int REFERENCES truncate_a, b int REFERENCES trunc_c); TRUNCATE TABLE truncate_a; -- fail @@ -143,7 +140,6 @@ SELECT * FROM trunc_e; DROP TABLE truncate_a,trunc_c,trunc_b,trunc_d,trunc_e CASCADE; -- Test TRUNCATE with inheritance CREATE TABLE trunc_f (col1 integer primary key); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "trunc_f_pkey" for table "trunc_f" INSERT INTO trunc_f VALUES (1); INSERT INTO trunc_f VALUES (2); CREATE TABLE trunc_fa (col2a text) INHERITS (trunc_f); @@ -367,7 +363,6 @@ DROP FUNCTION trunctrigger(); CREATE SEQUENCE truncate_a_id1 START WITH 33; CREATE TABLE truncate_a (id serial, id1 integer default nextval('truncate_a_id1')); -NOTICE: CREATE TABLE will create implicit sequence "truncate_a_id_seq" for serial column "truncate_a.id" ALTER SEQUENCE truncate_a_id1 OWNED BY truncate_a.id1; INSERT INTO truncate_a DEFAULT VALUES; INSERT INTO truncate_a DEFAULT VALUES; diff --git a/src/test/regress/expected/typed_table.out b/src/test/regress/expected/typed_table.out index 1fe426d6e3..dce609803c 100644 --- a/src/test/regress/expected/typed_table.out +++ b/src/test/regress/expected/typed_table.out @@ -45,8 +45,6 @@ CREATE TABLE persons2 OF person_type ( id WITH OPTIONS PRIMARY KEY, UNIQUE (name) ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "persons2_pkey" for table "persons2" -NOTICE: CREATE TABLE / UNIQUE will create implicit index "persons2_name_key" for table "persons2" \d persons2 Table "public.persons2" Column | Type | Modifiers @@ -62,7 +60,6 @@ CREATE TABLE persons3 OF person_type ( PRIMARY KEY (id), name WITH OPTIONS DEFAULT '' ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "persons3_pkey" for table "persons3" \d persons3 Table "public.persons3" Column | Type | Modifiers diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out index 405c5847cd..14028314e8 100644 --- a/src/test/regress/expected/union.out +++ b/src/test/regress/expected/union.out @@ -462,7 +462,6 @@ LINE 1: SELECT '3.4'::numeric UNION SELECT 'foo'; CREATE TEMP TABLE t1 (a text, b text); CREATE INDEX t1_ab_idx on t1 ((a || b)); CREATE TEMP TABLE t2 (ab text primary key); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey" for table "t2" INSERT INTO t1 VALUES ('a', 'b'), ('x', 'y'); INSERT INTO t2 VALUES ('ab'), ('xy'); set enable_seqscan = off; diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out index f1b8dd0cac..190043c255 100644 --- a/src/test/regress/expected/vacuum.out +++ b/src/test/regress/expected/vacuum.out @@ -59,7 +59,6 @@ SELECT * FROM vactst; VACUUM (FULL, FREEZE) vactst; VACUUM (ANALYZE, FULL) vactst; CREATE TABLE vaccluster (i INT PRIMARY KEY); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "vaccluster_pkey" for table "vaccluster" ALTER TABLE vaccluster CLUSTER ON vaccluster_pkey; INSERT INTO vaccluster SELECT * FROM vactst; CLUSTER vaccluster; diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out index 57e178afa9..2a41736cc8 100644 --- a/src/test/regress/expected/with.out +++ b/src/test/regress/expected/with.out @@ -139,7 +139,6 @@ CREATE TEMP TABLE department ( parent_department INTEGER REFERENCES department, -- upper department ID name TEXT -- department name ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "department_pkey" for table "department" INSERT INTO department VALUES (0, NULL, 'ROOT'); INSERT INTO department VALUES (1, 0, 'A'); INSERT INTO department VALUES (2, 1, 'B'); @@ -398,7 +397,6 @@ CREATE TEMPORARY TABLE tree( id INTEGER PRIMARY KEY, parent_id INTEGER REFERENCES tree(id) ); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tree_pkey" for table "tree" INSERT INTO tree VALUES (1, NULL), (2, 1), (3,1), (4,2), (5,2), (6,2), (7,3), (8,3), (9,4), (10,4), (11,7), (12,7), (13,7), (14, 9), (15,11), (16,11); diff --git a/src/test/regress/output/constraints.source b/src/test/regress/output/constraints.source index f37c9b1369..e8237f4a69 100644 --- a/src/test/regress/output/constraints.source +++ b/src/test/regress/output/constraints.source @@ -343,7 +343,6 @@ SELECT * FROM COPY_TBL; -- Primary keys -- CREATE TABLE PRIMARY_TBL (i int PRIMARY KEY, t text); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "primary_tbl_pkey" for table "primary_tbl" INSERT INTO PRIMARY_TBL VALUES (1, 'one'); INSERT INTO PRIMARY_TBL VALUES (2, 'two'); INSERT INTO PRIMARY_TBL VALUES (1, 'three'); @@ -366,7 +365,6 @@ SELECT '' AS four, * FROM PRIMARY_TBL; DROP TABLE PRIMARY_TBL; CREATE TABLE PRIMARY_TBL (i int, t text, PRIMARY KEY(i,t)); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "primary_tbl_pkey" for table "primary_tbl" INSERT INTO PRIMARY_TBL VALUES (1, 'one'); INSERT INTO PRIMARY_TBL VALUES (2, 'two'); INSERT INTO PRIMARY_TBL VALUES (1, 'three'); @@ -390,7 +388,6 @@ DROP TABLE PRIMARY_TBL; -- Unique keys -- CREATE TABLE UNIQUE_TBL (i int UNIQUE, t text); -NOTICE: CREATE TABLE / UNIQUE will create implicit index "unique_tbl_i_key" for table "unique_tbl" INSERT INTO UNIQUE_TBL VALUES (1, 'one'); INSERT INTO UNIQUE_TBL VALUES (2, 'two'); INSERT INTO UNIQUE_TBL VALUES (1, 'three'); @@ -414,7 +411,6 @@ SELECT '' AS five, * FROM UNIQUE_TBL; DROP TABLE UNIQUE_TBL; CREATE TABLE UNIQUE_TBL (i int, t text, UNIQUE(i,t)); -NOTICE: CREATE TABLE / UNIQUE will create implicit index "unique_tbl_i_t_key" for table "unique_tbl" INSERT INTO UNIQUE_TBL VALUES (1, 'one'); INSERT INTO UNIQUE_TBL VALUES (2, 'two'); INSERT INTO UNIQUE_TBL VALUES (1, 'three'); @@ -438,7 +434,6 @@ DROP TABLE UNIQUE_TBL; -- Deferrable unique constraints -- CREATE TABLE unique_tbl (i int UNIQUE DEFERRABLE, t text); -NOTICE: CREATE TABLE / UNIQUE will create implicit index "unique_tbl_i_key" for table "unique_tbl" INSERT INTO unique_tbl VALUES (0, 'one'); INSERT INTO unique_tbl VALUES (1, 'two'); INSERT INTO unique_tbl VALUES (2, 'tree'); @@ -482,7 +477,6 @@ SELECT * FROM unique_tbl; ALTER TABLE unique_tbl DROP CONSTRAINT unique_tbl_i_key; ALTER TABLE unique_tbl ADD CONSTRAINT unique_tbl_i_key UNIQUE (i) DEFERRABLE INITIALLY DEFERRED; -NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "unique_tbl_i_key" for table "unique_tbl" BEGIN; INSERT INTO unique_tbl VALUES (1, 'five'); INSERT INTO unique_tbl VALUES (5, 'one'); @@ -578,7 +572,6 @@ CREATE TABLE circles ( (c1 WITH &&, (c2::circle) WITH &&) WHERE (circle_center(c1) <> '(0,0)') ); -NOTICE: CREATE TABLE / EXCLUDE will create implicit index "circles_c1_c2_excl" for table "circles" -- these should succeed because they don't match the index predicate INSERT INTO circles VALUES('<(0,0), 5>', '<(0,0), 5>'); INSERT INTO circles VALUES('<(0,0), 5>', '<(0,0), 4>'); @@ -595,7 +588,6 @@ INSERT INTO circles VALUES('<(20,20), 10>', '<(10,10), 5>'); -- should fail on existing data without the WHERE clause ALTER TABLE circles ADD EXCLUDE USING gist (c1 WITH &&, (c2::circle) WITH &&); -NOTICE: ALTER TABLE / ADD EXCLUDE will create implicit index "circles_c1_c2_excl1" for table "circles" ERROR: could not create exclusion constraint "circles_c1_c2_excl1" DETAIL: Key (c1, (c2::circle))=(<(0,0),5>, <(0,0),5>) conflicts with key (c1, (c2::circle))=(<(0,0),5>, <(0,0),4>). -- try reindexing an existing constraint @@ -606,7 +598,6 @@ CREATE TABLE deferred_excl ( f1 int, CONSTRAINT deferred_excl_con EXCLUDE (f1 WITH =) INITIALLY DEFERRED ); -NOTICE: CREATE TABLE / EXCLUDE will create implicit index "deferred_excl_con" for table "deferred_excl" INSERT INTO deferred_excl VALUES(1); INSERT INTO deferred_excl VALUES(2); INSERT INTO deferred_excl VALUES(1); -- fail @@ -627,7 +618,6 @@ ALTER TABLE deferred_excl DROP CONSTRAINT deferred_excl_con; -- This should fail, but worth testing because of HOT updates UPDATE deferred_excl SET f1 = 3; ALTER TABLE deferred_excl ADD EXCLUDE (f1 WITH =); -NOTICE: ALTER TABLE / ADD EXCLUDE will create implicit index "deferred_excl_f1_excl" for table "deferred_excl" ERROR: could not create exclusion constraint "deferred_excl_f1_excl" DETAIL: Key (f1)=(3) conflicts with key (f1)=(3). DROP TABLE deferred_excl; -- 2.40.0