Clarify some error messages about duplicate things.

[postgresql] / src / test / regress / output / tablespace.source

diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index 43044a2bcf86f66e43d3d732b7dbbd6bd003a709..607232f5353bf8b17a2cf3fe51e87388135c672e 100644 (file)
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -1,17 +1,9 @@
 -- create a tablespace we can use
 CREATE TABLESPACE testspace LOCATION '@testtablespace@';
--- create a schema in the tablespace
-CREATE SCHEMA testschema TABLESPACE testspace;
--- sanity check
-SELECT nspname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_namespace n
-       where n.nsptablespace = t.oid and n.nspname = 'testschema';
-  nspname   |  spcname  
-------------+-----------
- testschema | testspace
-(1 row)
-
+-- create a schema we can use
+CREATE SCHEMA testschema;
 -- try a table
-CREATE TABLE testschema.foo (i int);
+CREATE TABLE testschema.foo (i int) TABLESPACE testspace;
 SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
     where c.reltablespace = t.oid AND c.relname = 'foo';
  relname |  spcname  
@@ -21,8 +13,27 @@ SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
 
 INSERT INTO testschema.foo VALUES(1);
 INSERT INTO testschema.foo VALUES(2);
+-- tables from dynamic sources
+CREATE TABLE testschema.asselect TABLESPACE testspace AS SELECT 1;
+SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
+    where c.reltablespace = t.oid AND c.relname = 'asselect';
+ relname  |  spcname  
+----------+-----------
+ asselect | testspace
+(1 row)
+
+PREPARE selectsource(int) AS SELECT $1;
+CREATE TABLE testschema.asexecute TABLESPACE testspace
+    AS EXECUTE selectsource(2);
+SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
+    where c.reltablespace = t.oid AND c.relname = 'asexecute';
+  relname  |  spcname  
+-----------+-----------
+ asexecute | testspace
+(1 row)
+
 -- index
-CREATE INDEX foo_idx on testschema.foo(i);
+CREATE INDEX foo_idx on testschema.foo(i) TABLESPACE testspace;
 SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
     where c.reltablespace = t.oid AND c.relname = 'foo_idx';
  relname |  spcname  
@@ -30,6 +41,20 @@ SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
  foo_idx | testspace
 (1 row)
 
+-- let's try moving a table from one place to another
+CREATE TABLE testschema.atable AS VALUES (1), (2);
+CREATE UNIQUE INDEX anindex ON testschema.atable(column1);
+ALTER TABLE testschema.atable SET TABLESPACE testspace;
+ALTER INDEX testschema.anindex SET TABLESPACE testspace;
+INSERT INTO testschema.atable VALUES(3);       -- ok
+INSERT INTO testschema.atable VALUES(1);       -- fail (checks index)
+ERROR:  duplicate key value violates unique constraint "anindex"
+SELECT COUNT(*) FROM testschema.atable;                -- checks heap
+ count 
+-------
+     3
+(1 row)
+
 -- Will fail with bad path
 CREATE TABLESPACE badspace LOCATION '/no/such/location';
 ERROR:  could not set permissions on directory "/no/such/location": No such file or directory
@@ -40,6 +65,9 @@ ERROR:  tablespace "nosuchspace" does not exist
 DROP TABLESPACE testspace;
 ERROR:  tablespace "testspace" is not empty
 DROP SCHEMA testschema CASCADE;
+NOTICE:  drop cascades to table testschema.atable
+NOTICE:  drop cascades to table testschema.asexecute
+NOTICE:  drop cascades to table testschema.asselect
 NOTICE:  drop cascades to table testschema.foo
 -- Should succeed
 DROP TABLESPACE testspace;