Re: ERROR: "ft1" is of the wrong type. - Mailing list pgsql-hackers

At Thu, 18 Feb 2021 17:17:37 +0900 (JST), Kyotaro Horiguchi <horikyota.ntt@gmail.com> wrote in 
> I can add some regression tests to cover all the live cases. That
> could reveal no-longer-used combinations.

The attached is that.

ATT_VIEW is used for "CREATE OR REPLACE view" and checked against
earlier in DefineVirtualRelation. But we can add a test to make sure
that is checked anywhere.

All other values can be exercised.

ATT_TABLE | ATT_MATVIEW
ATT_TABLE | ATT_MATVIEW | ATT_INDEX | ATT_PARTITIONED_INDEX
ATT_TABLE | ATT_MATVIEW | ATT_INDEX | ATT_PARTITIONED_INDEX |
     ATT_FOREIGN_TABLE
ATT_TABLE | ATT_MATVIEW | ATT_FOREIGN_TABLE
ATT_TABLE | ATT_MATVIEW | ATT_INDEX | ATT_FOREIGN_TABLE
ATT_TABLE | ATT_PARTITIONED_INDEX
ATT_TABLE | ATT_VIEW | ATT_MATVIEW | ATT_INDEX
ATT_TABLE | ATT_VIEW | ATT_FOREIGN_TABLE:
ATT_FOREIGN_TABLE

These are provoked by the following commands respectively:

  ALTER TABLE <view> CLUSTER ON
  ALTER TABLE <view> SET TABLESPACE
  ALTER TABLE <view> ALTER COLUMN <col> SET STATISTICS
  ALTER TABLE <view> ALTER COLUMN <col> SET STORGE
  ALTER TABLE <view> ALTER COLUMN <col> SET()
  ALTER TABLE <view> ATTACH PARTITION
  ALTER TABLE/INDEX <partidx> SET/RESET
  ALTER TABLE <matview> ALTER <col> SET DEFAULT
  ALTER TABLE/INDEX <pidx> ALTER COLLATION ..REFRESH VERSION
  ALTER TABLE <view> OPTIONS ()

The following three errors are already excised.

ATT_TABLE
ATT_TABLE | ATT_FOREIGN_TABLE
ATT_TABLE | ATT_COMPOSITE_TYPE | ATT_FOREIGN_TABLE:


By the way, I find this as somewhat mystifying. I'm not sure it worth
fixing though..

ALTER MATERIALIZED VIEW mv1 ALTER COLUMN a SET DEFAULT 1;
ERROR:  "mv1" is not a table, view, or foreign table

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 0ce6ee4622..4a367c9609 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -6,6 +6,7 @@ SET client_min_messages TO 'warning';
 DROP ROLE IF EXISTS regress_alter_table_user1;
 RESET client_min_messages;
 CREATE USER regress_alter_table_user1;
+CREATE VIEW at_v1 AS SELECT 1 as a;
 --
 -- add attribute
 --
@@ -120,6 +121,9 @@ ALTER INDEX attmp_idx ALTER COLUMN 4 SET STATISTICS 1000;
 ERROR:  column number 4 of relation "attmp_idx" does not exist
 ALTER INDEX attmp_idx ALTER COLUMN 2 SET STATISTICS -1;
 DROP TABLE attmp;
+-- test that the command correctly complains for the object of a wrong type
+ALTER TABLE at_v1 ALTER COLUMN a SET STATISTICS 0; -- ERROR
+ERROR:  "at_v1" is not a table, materialized view, index, partitioned index, or foreign table
 --
 -- rename - check on both non-temp and temp tables
 --
@@ -1186,6 +1190,11 @@ select * from def_test;
 alter table def_test alter column c1 set default 'wrong_datatype';
 ERROR:  invalid input syntax for type integer: "wrong_datatype"
 alter table def_test alter column c2 set default 20;
+-- set defaults to an incorrect object: this should fail
+create materialized view def_tmp_mv as select 1 as a;
+alter table def_tmp_mv alter a set default 0;
+ERROR:  "def_tmp_mv" is not a table, view, or foreign table
+drop materialized view def_tmp_mv;
 -- set defaults on a non-existent column: this should fail
 alter table def_test alter column c3 set default 30;
 ERROR:  column "c3" of relation "def_test" does not exist
@@ -2076,6 +2085,9 @@ Indexes:
     "at_part_2_a_idx" btree (a)
     "at_part_2_b_idx" btree (b)
 
+-- check if the command correctly complains for the object of a wrong type 
+alter table at_partitioned_a_idx set (dummy = 1); -- ERROR
+ERROR:  "at_partitioned_a_idx" is not a table, view, materialized view, or index
 drop table at_partitioned;
 -- Alter column type when no table rewrite is required
 -- Also check that comments are preserved
@@ -2212,6 +2224,9 @@ Indexes:
  a      | text    | yes  | a          | external | 
 btree, for table "public.test_storage"
 
+-- test that SET STORAGE correctly complains for the object of a wrong type
+alter table at_v1 alter column a set storage plain; -- ERROR
+ERROR:  "at_v1" is not a table, materialized view, or foreign table
 -- ALTER COLUMN TYPE with a check constraint and a child table (bug #13779)
 CREATE TABLE test_inh_check (a float check (a > 10.2), b float);
 CREATE TABLE test_inh_check_child() INHERITS(test_inh_check);
@@ -2684,6 +2699,9 @@ select * from my_locks order by 1;
 (2 rows)
 
 commit;
+-- test that the command corectly complains for the object of a wrong type
+alter table at_v1 alter column a set (dummy = 1);
+ERROR:  "at_v1" is not a table, materialized view, index, or foreign table
 begin; alter table alterlock alter column f2 set storage extended;
 select * from my_locks order by 1;
   relname  |    max_lockmode     
@@ -4110,6 +4128,9 @@ ERROR:  remainder for hash partition must be less than modulus
 ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 3, REMAINDER 2);
 ERROR:  every hash partition modulus must be a factor of the next larger modulus
 DROP TABLE fail_part;
+-- check that attach partition correctly complains for the object of a wrong type
+ALTER TABLE at_v1 ATTACH PARTITION dummy default; -- ERROR
+ERROR:  "at_v1" is not a table or partitioned index
 --
 -- DETACH PARTITION
 --
@@ -4350,6 +4371,11 @@ alter table at_test_sql_partop attach partition at_test_sql_partop_1 for values
 drop table at_test_sql_partop;
 drop operator class at_test_sql_partop using btree;
 drop function at_test_sql_partop;
+-- check that the command correctly complains for the object of a wrong type
+create table attmp();
+alter table attmp options (dummy '1');
+ERROR:  "attmp" is not a foreign table
+drop table attmp;
 /* Test case for bug #16242 */
 -- We create a parent and child where the child has missing
 -- non-null attribute values, and arrange to pass them through
diff --git a/src/test/regress/expected/cluster.out b/src/test/regress/expected/cluster.out
index bdae8fe00c..b4d4163836 100644
--- a/src/test/regress/expected/cluster.out
+++ b/src/test/regress/expected/cluster.out
@@ -572,7 +572,12 @@ SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
 (4 rows)
 
 COMMIT;
+-- check that the command correctly complains for the object of a wrong type
+CREATE VIEW  clstr_tst_view AS SELECT 1;
+ALTER TABLE clstr_tst_view CLUSTER ON x; -- ERROR
+ERROR:  "clstr_tst_view" is not a table or materialized view
 -- clean up
+DROP VIEW  clstr_tst_view;
 DROP TABLE clustertest;
 DROP TABLE clstr_1;
 DROP TABLE clstr_2;
diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out
index bc3752e923..981e626f53 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -2148,6 +2148,13 @@ AND objid::regclass::text = 'icuidx17_part';
  icuidx17_part | f
 (1 row)
 
+-- Test that ALTER COLLATION REFRESH VERSION correctly complains for
+-- wrong object.  We use ALTER TABLE, not ALTER INDEX since we are
+-- exercising ATWrongRelkindError here.
+CREATE VIEW failview AS SELECT 1 AS a;
+ALTER TABLE failview ALTER COLLATION a REFRESH VERSION; -- ERROR
+ERROR:  "failview" is not an index
+DROP VIEW failview;
 -- cleanup
 RESET search_path;
 SET client_min_messages TO warning;
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index bd5fe60450..a507ad37be 100644
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -72,6 +72,9 @@ ERROR:  cannot change data type of view column "b" from integer to numeric
 -- should work
 CREATE OR REPLACE VIEW viewtest AS
     SELECT a, b, 0 AS c FROM viewtest_tbl;
+-- check that the command correctly complains for the object of a wrong type
+CREATE OR REPLACE VIEW view_base_table AS SELECT 1 AS a;
+ERROR:  "view_base_table" is not a view
 DROP VIEW viewtest;
 DROP TABLE viewtest_tbl;
 -- tests for temporary views
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index b9e25820bc..ffa9287967 100644
--- a/src/test/regress/expected/foreign_data.out
+++ b/src/test/regress/expected/foreign_data.out
@@ -877,8 +877,10 @@ ERROR:  column "no_column" of relation "ft1" does not exist
 ALTER FOREIGN TABLE ft1 DROP COLUMN IF EXISTS no_column;
 NOTICE:  column "no_column" of relation "ft1" does not exist, skipping
 ALTER FOREIGN TABLE ft1 DROP COLUMN c9;
+ALTER FOREIGN TABLE ft1 SET TABLESPACE ts;                      -- ERROR (wrong object type)
+ERROR:  "ft1" is not a table, materialized view, index, or partitioned index
 ALTER FOREIGN TABLE ft1 SET SCHEMA foreign_schema;
-ALTER FOREIGN TABLE ft1 SET TABLESPACE ts;                      -- ERROR
+ALTER FOREIGN TABLE ft1 SET TABLESPACE ts;                      -- ERROR (not found)
 ERROR:  relation "ft1" does not exist
 ALTER FOREIGN TABLE foreign_schema.ft1 RENAME c1 TO foreign_column_1;
 ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1;
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 4cc55d8525..1b70458790 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -9,6 +9,8 @@ RESET client_min_messages;
 
 CREATE USER regress_alter_table_user1;
 
+CREATE VIEW at_v1 AS SELECT 1 as a;
+
 --
 -- add attribute
 --
@@ -158,6 +160,8 @@ ALTER INDEX attmp_idx ALTER COLUMN 2 SET STATISTICS -1;
 
 DROP TABLE attmp;
 
+-- test that the command correctly complains for the object of a wrong type
+ALTER TABLE at_v1 ALTER COLUMN a SET STATISTICS 0; -- ERROR
 
 --
 -- rename - check on both non-temp and temp tables
@@ -916,6 +920,11 @@ select * from def_test;
 alter table def_test alter column c1 set default 'wrong_datatype';
 alter table def_test alter column c2 set default 20;
 
+-- set defaults to an incorrect object: this should fail
+create materialized view def_tmp_mv as select 1 as a;
+alter table def_tmp_mv alter a set default 0;
+drop materialized view def_tmp_mv;
+
 -- set defaults on a non-existent column: this should fail
 alter table def_test alter column c3 set default 30;
 
@@ -1409,6 +1418,10 @@ alter table at_partitioned attach partition at_part_2 for values from (1000) to
 alter table at_partitioned alter column b type numeric using b::numeric;
 \d at_part_1
 \d at_part_2
+
+-- check if the command correctly complains for the object of a wrong type 
+alter table at_partitioned_a_idx set (dummy = 1); -- ERROR
+
 drop table at_partitioned;
 
 -- Alter column type when no table rewrite is required
@@ -1500,6 +1513,9 @@ alter table test_storage alter column a set storage external;
 \d+ test_storage
 \d+ test_storage_idx
 
+-- test that SET STORAGE correctly complains for the object of a wrong type
+alter table at_v1 alter column a set storage plain; -- ERROR
+
 -- ALTER COLUMN TYPE with a check constraint and a child table (bug #13779)
 CREATE TABLE test_inh_check (a float check (a > 10.2), b float);
 CREATE TABLE test_inh_check_child() INHERITS(test_inh_check);
@@ -1721,6 +1737,9 @@ begin; alter table alterlock set (autovacuum_enabled = off, fillfactor = 80);
 select * from my_locks order by 1;
 commit;
 
+-- test that the command corectly complains for the object of a wrong type
+alter table at_v1 alter column a set (dummy = 1);
+
 begin; alter table alterlock alter column f2 set storage extended;
 select * from my_locks order by 1;
 rollback;
@@ -2640,6 +2659,9 @@ ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 8, R
 ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 3, REMAINDER 2);
 DROP TABLE fail_part;
 
+-- check that attach partition correctly complains for the object of a wrong type
+ALTER TABLE at_v1 ATTACH PARTITION dummy default; -- ERROR
+
 --
 -- DETACH PARTITION
 --
@@ -2852,6 +2874,11 @@ drop operator class at_test_sql_partop using btree;
 drop function at_test_sql_partop;
 
 
+-- check that the command correctly complains for the object of a wrong type
+create table attmp();
+alter table attmp options (dummy '1');
+drop table attmp;
+
 /* Test case for bug #16242 */
 
 -- We create a parent and child where the child has missing
diff --git a/src/test/regress/sql/cluster.sql b/src/test/regress/sql/cluster.sql
index 188183647c..827a15b305 100644
--- a/src/test/regress/sql/cluster.sql
+++ b/src/test/regress/sql/cluster.sql
@@ -257,7 +257,12 @@ EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
 SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
 COMMIT;
 
+-- check that the command correctly complains for the object of a wrong type
+CREATE VIEW  clstr_tst_view AS SELECT 1;
+ALTER TABLE clstr_tst_view CLUSTER ON x; -- ERROR
+
 -- clean up
+DROP VIEW  clstr_tst_view;
 DROP TABLE clustertest;
 DROP TABLE clstr_1;
 DROP TABLE clstr_2;
diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql
index 0de2ed8d85..583c671c34 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -875,6 +875,13 @@ SELECT objid::regclass, refobjversion = 'not a version' AS ver FROM pg_depend
 WHERE refclassid = 'pg_collation'::regclass
 AND objid::regclass::text = 'icuidx17_part';
 
+-- Test that ALTER COLLATION REFRESH VERSION correctly complains for
+-- wrong object.  We use ALTER TABLE, not ALTER INDEX since we are
+-- exercising ATWrongRelkindError here.
+CREATE VIEW failview AS SELECT 1 AS a;
+ALTER TABLE failview ALTER COLLATION a REFRESH VERSION; -- ERROR
+DROP VIEW failview;
+
 -- cleanup
 RESET search_path;
 SET client_min_messages TO warning;
diff --git a/src/test/regress/sql/create_view.sql b/src/test/regress/sql/create_view.sql
index fbd1313b9c..8fbbe05c56 100644
--- a/src/test/regress/sql/create_view.sql
+++ b/src/test/regress/sql/create_view.sql
@@ -77,6 +77,9 @@ CREATE OR REPLACE VIEW viewtest AS
 CREATE OR REPLACE VIEW viewtest AS
     SELECT a, b, 0 AS c FROM viewtest_tbl;
 
+-- check that the command correctly complains for the object of a wrong type
+CREATE OR REPLACE VIEW view_base_table AS SELECT 1 AS a;
+
 DROP VIEW viewtest;
 DROP TABLE viewtest_tbl;
 
diff --git a/src/test/regress/sql/foreign_data.sql b/src/test/regress/sql/foreign_data.sql
index 73f9f621d8..e96aef5396 100644
--- a/src/test/regress/sql/foreign_data.sql
+++ b/src/test/regress/sql/foreign_data.sql
@@ -406,8 +406,9 @@ ALTER FOREIGN TABLE ft1 OPTIONS (DROP delimiter, SET quote '~', ADD escape '@');
 ALTER FOREIGN TABLE ft1 DROP COLUMN no_column;                  -- ERROR
 ALTER FOREIGN TABLE ft1 DROP COLUMN IF EXISTS no_column;
 ALTER FOREIGN TABLE ft1 DROP COLUMN c9;
+ALTER FOREIGN TABLE ft1 SET TABLESPACE ts;                      -- ERROR (wrong object type)
 ALTER FOREIGN TABLE ft1 SET SCHEMA foreign_schema;
-ALTER FOREIGN TABLE ft1 SET TABLESPACE ts;                      -- ERROR
+ALTER FOREIGN TABLE ft1 SET TABLESPACE ts;                      -- ERROR (not found)
 ALTER FOREIGN TABLE foreign_schema.ft1 RENAME c1 TO foreign_column_1;
 ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1;
 \d foreign_schema.foreign_table_1