Change nextval and other sequence functions to specify their sequence

argument as a 'regclass' value instead of a text string.  The frontend
conversion of text string to pg_class OID is now encapsulated as an
implicitly-invocable coercion from text to regclass.  This provides
backwards compatibility to the old behavior when the sequence argument
is explicitly typed as 'text'.  When the argument is just an unadorned
literal string, it will be taken as 'regclass', which means that the
stored representation will be an OID.  This solves longstanding problems
with renaming sequences that are referenced in default expressions, as
well as new-in-8.1 problems with renaming such sequences' schemas or
moving them to another schema.  All per recent discussion.
Along the way, fix some rather serious problems in dbmirror's support
for mirroring sequence operations (int4 vs int8 confusion for instance).

7 files changed, 158 insertions, 20 deletions

diff --git a/src/test/regress/expected/domain.out b/src/test/regress/expected/domain.out
index e959b4a9b7..d2766ee2a4 100644
--- a/src/test/regress/expected/domain.out
+++ b/src/test/regress/expected/domain.out
@@ -162,7 +162,7 @@ create domain ddef2 oid DEFAULT '12';
 -- Type mixing, function returns int8
 create domain ddef3 text DEFAULT 5;
 create sequence ddef4_seq;
-create domain ddef4 int4 DEFAULT nextval(cast('ddef4_seq' as text));
+create domain ddef4 int4 DEFAULT nextval('ddef4_seq');
 create domain ddef5 numeric(8,2) NOT NULL DEFAULT '12.12';
 create table defaulttest
             ( col1 ddef1
@@ -189,7 +189,6 @@ select * from defaulttest;
     3 |   12 | 5    |    4 |   42 |   88 | 8000 | 12.12
 (4 rows)
 
-drop sequence ddef4_seq;
 drop table defaulttest cascade;
 -- Test ALTER DOMAIN .. NOT NULL
 create domain dnotnulltest integer;
@@ -300,6 +299,7 @@ drop domain ddef2 restrict;
 drop domain ddef3 restrict;
 drop domain ddef4 restrict;
 drop domain ddef5 restrict;
+drop sequence ddef4_seq;
 -- Make sure that constraints of newly-added domain columns are
 -- enforced correctly, even if there's no default value for the new
 -- column. Per bug #1433
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 756a716eca..978a6dcd63 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1335,10 +1335,10 @@ SELECT tablename, rulename, definition FROM pg_rules
  rtest_nothn1  | rtest_nothn_r2  | CREATE RULE rtest_nothn_r2 AS ON INSERT TO rtest_nothn1 WHERE ((new.a >= 30) AND (new.a < 40)) DO INSTEAD NOTHING;
  rtest_nothn2  | rtest_nothn_r3  | CREATE RULE rtest_nothn_r3 AS ON INSERT TO rtest_nothn2 WHERE (new.a >= 100) DO INSTEAD INSERT INTO rtest_nothn3 (a, b) VALUES (new.a, new.b);
  rtest_nothn2  | rtest_nothn_r4  | CREATE RULE rtest_nothn_r4 AS ON INSERT TO rtest_nothn2 DO INSTEAD NOTHING;
- rtest_order1  | rtest_order_r1  | CREATE RULE rtest_order_r1 AS ON INSERT TO rtest_order1 DO INSTEAD INSERT INTO rtest_order2 (a, b, c) VALUES (new.a, nextval('rtest_seq'::text), 'rule 1 - this should run 1st'::text);
- rtest_order1  | rtest_order_r2  | CREATE RULE rtest_order_r2 AS ON INSERT TO rtest_order1 DO INSERT INTO rtest_order2 (a, b, c) VALUES (new.a, nextval('rtest_seq'::text), 'rule 2 - this should run 2nd'::text);
- rtest_order1  | rtest_order_r3  | CREATE RULE rtest_order_r3 AS ON INSERT TO rtest_order1 DO INSTEAD INSERT INTO rtest_order2 (a, b, c) VALUES (new.a, nextval('rtest_seq'::text), 'rule 3 - this should run 3rd'::text);
- rtest_order1  | rtest_order_r4  | CREATE RULE rtest_order_r4 AS ON INSERT TO rtest_order1 WHERE (new.a < 100) DO INSTEAD INSERT INTO rtest_order2 (a, b, c) VALUES (new.a, nextval('rtest_seq'::text), 'rule 4 - this should run 4th'::text);
+ rtest_order1  | rtest_order_r1  | CREATE RULE rtest_order_r1 AS ON INSERT TO rtest_order1 DO INSTEAD INSERT INTO rtest_order2 (a, b, c) VALUES (new.a, nextval('rtest_seq'::regclass), 'rule 1 - this should run 1st'::text);
+ rtest_order1  | rtest_order_r2  | CREATE RULE rtest_order_r2 AS ON INSERT TO rtest_order1 DO INSERT INTO rtest_order2 (a, b, c) VALUES (new.a, nextval('rtest_seq'::regclass), 'rule 2 - this should run 2nd'::text);
+ rtest_order1  | rtest_order_r3  | CREATE RULE rtest_order_r3 AS ON INSERT TO rtest_order1 DO INSTEAD INSERT INTO rtest_order2 (a, b, c) VALUES (new.a, nextval('rtest_seq'::regclass), 'rule 3 - this should run 3rd'::text);
+ rtest_order1  | rtest_order_r4  | CREATE RULE rtest_order_r4 AS ON INSERT TO rtest_order1 WHERE (new.a < 100) DO INSTEAD INSERT INTO rtest_order2 (a, b, c) VALUES (new.a, nextval('rtest_seq'::regclass), 'rule 4 - this should run 4th'::text);
  rtest_person  | rtest_pers_del  | CREATE RULE rtest_pers_del AS ON DELETE TO rtest_person DO DELETE FROM rtest_admin WHERE (rtest_admin.pname = old.pname);
  rtest_person  | rtest_pers_upd  | CREATE RULE rtest_pers_upd AS ON UPDATE TO rtest_person DO UPDATE rtest_admin SET pname = new.pname WHERE (rtest_admin.pname = old.pname);
  rtest_system  | rtest_sys_del   | CREATE RULE rtest_sys_del AS ON DELETE TO rtest_system DO (DELETE FROM rtest_interface WHERE (rtest_interface.sysname = old.sysname); DELETE FROM rtest_admin WHERE (rtest_admin.sysname = old.sysname); );
diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index 6e919d1f1d..ca9ece284b 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -19,18 +19,82 @@ SELECT * FROM serialTest;
  force | 100
 (3 rows)
 
- 
+-- basic sequence operations using both text and oid references
 CREATE SEQUENCE sequence_test;
  
-BEGIN;
-SELECT nextval('sequence_test');
+SELECT nextval('sequence_test'::text);
  nextval 
 ---------
        1
 (1 row)
 
+SELECT nextval('sequence_test'::regclass);
+ nextval 
+---------
+       2
+(1 row)
+
+SELECT currval('sequence_test'::text);
+ currval 
+---------
+       2
+(1 row)
+
+SELECT currval('sequence_test'::regclass);
+ currval 
+---------
+       2
+(1 row)
+
+SELECT setval('sequence_test'::text, 32);
+ setval 
+--------
+     32
+(1 row)
+
+SELECT nextval('sequence_test'::regclass);
+ nextval 
+---------
+      33
+(1 row)
+
+SELECT setval('sequence_test'::text, 99, false);
+ setval 
+--------
+     99
+(1 row)
+
+SELECT nextval('sequence_test'::regclass);
+ nextval 
+---------
+      99
+(1 row)
+
+SELECT setval('sequence_test'::regclass, 32);
+ setval 
+--------
+     32
+(1 row)
+
+SELECT nextval('sequence_test'::text);
+ nextval 
+---------
+      33
+(1 row)
+
+SELECT setval('sequence_test'::regclass, 99, false);
+ setval 
+--------
+     99
+(1 row)
+
+SELECT nextval('sequence_test'::text);
+ nextval 
+---------
+      99
+(1 row)
+
 DROP SEQUENCE sequence_test;
-END;
 -- renaming sequences
 CREATE SEQUENCE foo_seq;
 ALTER TABLE foo_seq RENAME TO foo_seq_new;
@@ -41,6 +105,45 @@ SELECT * FROM foo_seq_new;
 (1 row)
 
 DROP SEQUENCE foo_seq_new;
+-- renaming serial sequences
+ALTER TABLE serialtest_f2_seq RENAME TO serialtest_f2_foo;
+INSERT INTO serialTest VALUES ('more');
+SELECT * FROM serialTest;
+  f1   | f2  
+-------+-----
+ foo   |   1
+ bar   |   2
+ force | 100
+ more  |   3
+(4 rows)
+
+--
+-- Check dependencies of serial and ordinary sequences
+--
+CREATE TEMP SEQUENCE myseq2;
+CREATE TEMP SEQUENCE myseq3;
+CREATE TEMP TABLE t1 (
+  f1 serial,
+  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 table t1 column f1 requires it
+HINT:  You may drop table t1 column f1 instead.
+DROP SEQUENCE myseq2;
+NOTICE:  default for table t1 column f2 depends on sequence myseq2
+ERROR:  cannot drop sequence myseq2 because other objects depend on it
+HINT:  Use DROP ... CASCADE to drop the dependent objects too.
+-- This however will work:
+DROP SEQUENCE myseq3;
+DROP TABLE t1;
+-- Fails because no longer existent:
+DROP SEQUENCE t1_f1_seq;
+ERROR:  sequence "t1_f1_seq" does not exist
+-- Now OK:
+DROP SEQUENCE myseq2;
 --
 -- Alter sequence
 --
diff --git a/src/test/regress/input/constraints.source b/src/test/regress/input/constraints.source
index f92f55af2b..350f29152a 100644
--- a/src/test/regress/input/constraints.source
+++ b/src/test/regress/input/constraints.source
@@ -147,9 +147,8 @@ DROP TABLE INSERT_CHILD;
 --
 
 DELETE FROM INSERT_TBL;
-DROP SEQUENCE INSERT_SEQ;
 
-CREATE SEQUENCE INSERT_SEQ START 4;
+ALTER SEQUENCE INSERT_SEQ RESTART WITH 4;
 
 CREATE TABLE tmp (xd INT, yd TEXT, zd INT);
 
diff --git a/src/test/regress/output/constraints.source b/src/test/regress/output/constraints.source
index 70ab60ac40..5ba46c062f 100644
--- a/src/test/regress/output/constraints.source
+++ b/src/test/regress/output/constraints.source
@@ -213,8 +213,7 @@ DROP TABLE INSERT_CHILD;
 -- Check constraints on INSERT INTO
 --
 DELETE FROM INSERT_TBL;
-DROP SEQUENCE INSERT_SEQ;
-CREATE SEQUENCE INSERT_SEQ START 4;
+ALTER SEQUENCE INSERT_SEQ RESTART WITH 4;
 CREATE TABLE tmp (xd INT, yd TEXT, zd INT);
 INSERT INTO tmp VALUES (null, 'Y', null);
 INSERT INTO tmp VALUES (5, '!check failed', null);
diff --git a/src/test/regress/sql/domain.sql b/src/test/regress/sql/domain.sql
index 386adede16..c80b812626 100644
--- a/src/test/regress/sql/domain.sql
+++ b/src/test/regress/sql/domain.sql
@@ -131,7 +131,7 @@ create domain ddef2 oid DEFAULT '12';
 -- Type mixing, function returns int8
 create domain ddef3 text DEFAULT 5;
 create sequence ddef4_seq;
-create domain ddef4 int4 DEFAULT nextval(cast('ddef4_seq' as text));
+create domain ddef4 int4 DEFAULT nextval('ddef4_seq');
 create domain ddef5 numeric(8,2) NOT NULL DEFAULT '12.12';
 
 create table defaulttest
@@ -155,7 +155,6 @@ COPY defaulttest(col5) FROM stdin;
 
 select * from defaulttest;
 
-drop sequence ddef4_seq;
 drop table defaulttest cascade;
 
 -- Test ALTER DOMAIN .. NOT NULL
@@ -244,6 +243,7 @@ drop domain ddef2 restrict;
 drop domain ddef3 restrict;
 drop domain ddef4 restrict;
 drop domain ddef5 restrict;
+drop sequence ddef4_seq;
 
 -- Make sure that constraints of newly-added domain columns are
 -- enforced correctly, even if there's no default value for the new
diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index a8b73c02bf..806a718192 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -10,13 +10,24 @@ INSERT INTO serialTest VALUES ('force', 100);
 INSERT INTO serialTest VALUES ('wrong', NULL);
  
 SELECT * FROM serialTest;
- 
+
+-- basic sequence operations using both text and oid references
 CREATE SEQUENCE sequence_test;
  
-BEGIN;
-SELECT nextval('sequence_test');
+SELECT nextval('sequence_test'::text);
+SELECT nextval('sequence_test'::regclass);
+SELECT currval('sequence_test'::text);
+SELECT currval('sequence_test'::regclass);
+SELECT setval('sequence_test'::text, 32);
+SELECT nextval('sequence_test'::regclass);
+SELECT setval('sequence_test'::text, 99, false);
+SELECT nextval('sequence_test'::regclass);
+SELECT setval('sequence_test'::regclass, 32);
+SELECT nextval('sequence_test'::text);
+SELECT setval('sequence_test'::regclass, 99, false);
+SELECT nextval('sequence_test'::text);
+
 DROP SEQUENCE sequence_test;
-END;
 
 -- renaming sequences
 CREATE SEQUENCE foo_seq;
@@ -24,6 +35,32 @@ ALTER TABLE foo_seq RENAME TO foo_seq_new;
 SELECT * FROM foo_seq_new;
 DROP SEQUENCE foo_seq_new;
 
+-- renaming serial sequences
+ALTER TABLE serialtest_f2_seq RENAME TO serialtest_f2_foo;
+INSERT INTO serialTest VALUES ('more');
+SELECT * FROM serialTest;
+
+--
+-- Check dependencies of serial and ordinary sequences
+--
+CREATE TEMP SEQUENCE myseq2;
+CREATE TEMP SEQUENCE myseq3;
+CREATE TEMP TABLE t1 (
+  f1 serial,
+  f2 int DEFAULT nextval('myseq2'),
+  f3 int DEFAULT nextval('myseq3'::text)
+);
+-- Both drops should fail, but with different error messages:
+DROP SEQUENCE t1_f1_seq;
+DROP SEQUENCE myseq2;
+-- This however will work:
+DROP SEQUENCE myseq3;
+DROP TABLE t1;
+-- Fails because no longer existent:
+DROP SEQUENCE t1_f1_seq;
+-- Now OK:
+DROP SEQUENCE myseq2;
+
 --
 -- Alter sequence
 --