From aa731ed8433914641e42f32fec0fcf27f01aab7e Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sun, 2 Oct 2005 23:50:16 +0000 Subject: [PATCH] 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). --- contrib/dbmirror/MirrorSetup.sql | 14 +- contrib/dbmirror/README.dbmirror | 10 +- contrib/dbmirror/pending.c | 243 +++++++-------------- doc/src/sgml/datatype.sgml | 14 +- doc/src/sgml/func.sgml | 78 +++++-- doc/src/sgml/release.sgml | 21 +- src/backend/catalog/dependency.c | 55 ++++- src/backend/catalog/information_schema.sql | 5 +- src/backend/commands/sequence.c | 102 +++++---- src/backend/commands/tablecmds.c | 63 +----- src/backend/parser/analyze.c | 7 +- src/backend/utils/adt/regproc.c | 19 +- src/bin/psql/describe.c | 5 +- src/include/catalog/catversion.h | 4 +- src/include/catalog/pg_cast.h | 7 +- src/include/catalog/pg_proc.h | 14 +- src/include/commands/sequence.h | 9 +- src/include/utils/builtins.h | 3 +- src/test/regress/expected/domain.out | 4 +- src/test/regress/expected/rules.out | 8 +- src/test/regress/expected/sequence.out | 111 +++++++++- src/test/regress/input/constraints.source | 3 +- src/test/regress/output/constraints.source | 3 +- src/test/regress/sql/domain.sql | 4 +- src/test/regress/sql/sequence.sql | 45 +++- 25 files changed, 512 insertions(+), 339 deletions(-) diff --git a/contrib/dbmirror/MirrorSetup.sql b/contrib/dbmirror/MirrorSetup.sql index 19ac1b280f..18c5b0693e 100644 --- a/contrib/dbmirror/MirrorSetup.sql +++ b/contrib/dbmirror/MirrorSetup.sql @@ -2,7 +2,7 @@ BEGIN; CREATE FUNCTION "recordchange" () RETURNS trigger AS -'$libdir/pending.so', 'recordchange' LANGUAGE 'C'; +'$libdir/pending', 'recordchange' LANGUAGE 'C'; @@ -48,15 +48,15 @@ CASCADE ON DELETE CASCADE UPDATE pg_proc SET proname='nextval_pg' WHERE proname='nextval'; -CREATE FUNCTION pg_catalog.nextval(text) RETURNS int8 AS -'$libdir/pending.so', 'nextval' LANGUAGE 'C' STRICT; +CREATE FUNCTION pg_catalog.nextval(regclass) RETURNS int8 AS + '$libdir/pending', 'nextval_mirror' LANGUAGE 'C' STRICT; UPDATE pg_proc set proname='setval_pg' WHERE proname='setval'; -CREATE FUNCTION pg_catalog.setval("unknown",integer,boolean) RETURNS int8 AS -'$libdir/pending.so', 'setval' LANGUAGE 'C' STRICT; -CREATE FUNCTION pg_catalog.setval("unknown",integer) RETURNS int8 AS -'$libdir/pending.so', 'setval' LANGUAGE 'C' STRICT; +CREATE FUNCTION pg_catalog.setval(regclass, int8, boolean) RETURNS int8 AS + '$libdir/pending', 'setval3_mirror' LANGUAGE 'C' STRICT; +CREATE FUNCTION pg_catalog.setval(regclass, int8) RETURNS int8 AS + '$libdir/pending', 'setval_mirror' LANGUAGE 'C' STRICT; COMMIT; diff --git a/contrib/dbmirror/README.dbmirror b/contrib/dbmirror/README.dbmirror index 8dfdf7a84b..1c4c9a0914 100644 --- a/contrib/dbmirror/README.dbmirror +++ b/contrib/dbmirror/README.dbmirror @@ -56,7 +56,7 @@ Pending tables. Requirements: --------------------------------- --PostgreSQL-7.4 (Older versions are no longer supported) +-PostgreSQL-8.1 (Older versions are no longer supported) -Perl 5.6 or 5.8 (Other versions might work) -PgPerl (http://gborg.postgresql.org/project/pgperl/projdisplay.php) @@ -177,15 +177,15 @@ If you are starting with an empty master database then the slave should be empty as well. Otherwise use pg_dump to ensure that the slave database tables are initially identical to the master. -6) Add entries in the MirrorHost table. +6) Add entries in the dbmirror_MirrorHost table. -Each slave database must have an entry in the MirrorHost table. +Each slave database must have an entry in the dbmirror_MirrorHost table. -The name of the host in the MirrorHost table must exactly match the +The name of the host in the dbmirror_MirrorHost table must exactly match the slaveHost variable for that slave in the configuration file. For example -INSERT INTO "MirrorHost" ("SlaveName") VALUES ('backup_system'); +INSERT INTO dbmirror_MirrorHost (SlaveName) VALUES ('backup_system'); 6) Start DBMirror.pl diff --git a/contrib/dbmirror/pending.c b/contrib/dbmirror/pending.c index 3ed9d2128c..36f5837bbd 100644 --- a/contrib/dbmirror/pending.c +++ b/contrib/dbmirror/pending.c @@ -1,7 +1,7 @@ /**************************************************************************** * pending.c - * $Id: pending.c,v 1.21 2005/03/29 00:16:48 tgl Exp $ - * $PostgreSQL: pgsql/contrib/dbmirror/pending.c,v 1.21 2005/03/29 00:16:48 tgl Exp $ + * $Id: pending.c,v 1.22 2005/10/02 23:50:05 tgl Exp $ + * $PostgreSQL: pgsql/contrib/dbmirror/pending.c,v 1.22 2005/10/02 23:50:05 tgl Exp $ * * This file contains a trigger for Postgresql-7.x to record changes to tables * to a pending table for mirroring. @@ -30,12 +30,13 @@ * * ***************************************************************************/ -#include +#include "postgres.h" -#include -#include -#include -#include +#include "executor/spi.h" +#include "commands/sequence.h" +#include "commands/trigger.h" +#include "utils/lsyscache.h" +#include "utils/array.h" enum FieldUsage { @@ -81,11 +82,11 @@ PG_FUNCTION_INFO_V1(recordchange); -extern Datum nextval(PG_FUNCTION_ARGS); -extern Datum setval(PG_FUNCTION_ARGS); +extern Datum setval_mirror(PG_FUNCTION_ARGS); +extern Datum setval3_mirror(PG_FUNCTION_ARGS); +extern Datum nextval_mirror(PG_FUNCTION_ARGS); -int saveSequenceUpdate(const text *sequenceName, - int nextSequenceValue); +static void saveSequenceUpdate(Oid relid, int64 nextValue, bool iscalled); /***************************************************************************** @@ -310,11 +311,9 @@ storeKeyInfo(char *cpTableName, HeapTuple tTupleData, SPI_pfree(cpKeyData); if (iRetCode != SPI_OK_INSERT) - { - ereport(ERROR, (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION) - ,errmsg("error inserting row in pendingDelete"))); - return -1; - } + ereport(ERROR, + (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION), + errmsg("error inserting row in pendingDelete"))); debug_msg("insert successful"); @@ -583,161 +582,75 @@ packageData(HeapTuple tTupleData, TupleDesc tTupleDesc, Oid tableOid, } -PG_FUNCTION_INFO_V1(setval); +/* + * Support for mirroring sequence objects. + */ + +PG_FUNCTION_INFO_V1(setval_mirror); Datum -setval(PG_FUNCTION_ARGS) +setval_mirror(PG_FUNCTION_ARGS) { + Oid relid = PG_GETARG_OID(0); + int64 next = PG_GETARG_INT64(1); + int64 result; + result = DatumGetInt64(DirectFunctionCall2(setval_oid, + ObjectIdGetDatum(relid), + Int64GetDatum(next))); - text *sequenceName; - - Oid setvalArgTypes[3] = {TEXTOID, INT4OID,BOOLOID}; - int nextValue; - void *setvalPlan = NULL; - Datum setvalData[3]; - const char *setvalQuery = "SELECT setval_pg($1,$2,$3)"; - int ret; - char is_called; - - sequenceName = PG_GETARG_TEXT_P(0); - nextValue = PG_GETARG_INT32(1); - is_called = PG_GETARG_BOOL(2); - - setvalData[0] = PointerGetDatum(sequenceName); - setvalData[1] = Int32GetDatum(nextValue); - if(PG_NARGS() > 2) - { - setvalData[2] = BoolGetDatum(is_called); - } - else - { - setvalData[2]=1; - } - - if (SPI_connect() < 0) - { - ereport(ERROR, (errcode(ERRCODE_EXTERNAL_ROUTINE_EXCEPTION), - errmsg("dbmirror:setval could not connect to SPI"))); - return -1; - } - - setvalPlan = SPI_prepare(setvalQuery, 3, setvalArgTypes); - if (setvalPlan == NULL) - { - ereport(ERROR, (errcode(ERRCODE_EXTERNAL_ROUTINE_EXCEPTION), - errmsg("dbmirror:setval could not prepare plan"))); - return -1; - } - - ret = SPI_execp(setvalPlan, setvalData, NULL, 1); - - if (ret != SPI_OK_SELECT || SPI_processed != 1) - return -1; - - debug_msg2("dbmirror:setval: setval_pg returned ok:%d", nextValue); - - ret = saveSequenceUpdate(sequenceName, nextValue); - - SPI_pfree(setvalPlan); - - SPI_finish(); - debug_msg("dbmirror:setval about to return"); - return Int64GetDatum(nextValue); + saveSequenceUpdate(relid, result, true); + PG_RETURN_INT64(result); } - - -PG_FUNCTION_INFO_V1(nextval); +PG_FUNCTION_INFO_V1(setval3_mirror); Datum -nextval(PG_FUNCTION_ARGS) +setval3_mirror(PG_FUNCTION_ARGS) { - text *sequenceName; - - const char *nextvalQuery = "SELECT nextval_pg($1)"; - Oid nextvalArgTypes[1] = {TEXTOID}; - void *nextvalPlan = NULL; - Datum nextvalData[1]; - - - int ret; - HeapTuple resTuple; - char isNull; - int nextSequenceValue; - - - - debug_msg("dbmirror:nextval Starting pending.so:nextval"); - - - sequenceName = PG_GETARG_TEXT_P(0); - - if (SPI_connect() < 0) - { - ereport(ERROR, (errcode(ERRCODE_EXTERNAL_ROUTINE_EXCEPTION), - errmsg("dbmirror:nextval could not connect to SPI"))); - return -1; - } - - nextvalPlan = SPI_prepare(nextvalQuery, 1, nextvalArgTypes); - - - debug_msg("prepared plan to call nextval_pg"); - - - if (nextvalPlan == NULL) - { - ereport(ERROR, (errcode(ERRCODE_EXTERNAL_ROUTINE_EXCEPTION), - errmsg("dbmirror:nextval error creating plan"))); - return -1; - } - nextvalData[0] = PointerGetDatum(sequenceName); - - ret = SPI_execp(nextvalPlan, nextvalData, NULL, 1); - - debug_msg("dbmirror:Executed call to nextval_pg"); - - - if (ret != SPI_OK_SELECT || SPI_processed != 1) - return -1; - - resTuple = SPI_tuptable->vals[0]; + Oid relid = PG_GETARG_OID(0); + int64 next = PG_GETARG_INT64(1); + bool iscalled = PG_GETARG_BOOL(2); + int64 result; - debug_msg("dbmirror:nextval Set resTuple"); - - nextSequenceValue = *(unsigned int *) (DatumGetPointer(SPI_getbinval(resTuple, - SPI_tuptable->tupdesc, - 1, &isNull))); + result = DatumGetInt64(DirectFunctionCall3(setval3_oid, + ObjectIdGetDatum(relid), + Int64GetDatum(next), + BoolGetDatum(iscalled))); + saveSequenceUpdate(relid, result, iscalled); + PG_RETURN_INT64(result); +} - debug_msg2("dbmirror:nextval Set SPI_getbinval:%d", nextSequenceValue); +PG_FUNCTION_INFO_V1(nextval_mirror); +Datum +nextval_mirror(PG_FUNCTION_ARGS) +{ + Oid relid = PG_GETARG_OID(0); + int64 result; - saveSequenceUpdate(sequenceName, nextSequenceValue); - SPI_pfree(resTuple); - SPI_pfree(nextvalPlan); + result = DatumGetInt64(DirectFunctionCall1(nextval_oid, + ObjectIdGetDatum(relid))); - SPI_finish(); + saveSequenceUpdate(relid, result, true); - return Int64GetDatum(nextSequenceValue); + PG_RETURN_INT64(result); } -int -saveSequenceUpdate(const text *sequenceName, - int nextSequenceVal) +static void +saveSequenceUpdate(Oid relid, int64 nextValue, bool iscalled) { - - Oid insertArgTypes[2] = {TEXTOID, INT4OID}; + Oid insertArgTypes[2] = {NAMEOID, INT4OID}; Oid insertDataArgTypes[1] = {NAMEOID}; - void *insertPlan = NULL; - void *insertDataPlan = NULL; + void *insertPlan; + void *insertDataPlan; Datum insertDatum[2]; Datum insertDataDatum[1]; - char nextSequenceText[32]; + char nextSequenceText[64]; const char *insertQuery = "INSERT INTO dbmirror_Pending (TableName,Op,XID) VALUES" \ @@ -746,36 +659,50 @@ saveSequenceUpdate(const text *sequenceName, "INSERT INTO dbmirror_PendingData(SeqId,IsKey,Data) VALUES " \ "(currval('dbmirror_pending_seqid_seq'),'t',$1)"; - int ret; - + if (SPI_connect() < 0) + ereport(ERROR, + (errcode(ERRCODE_EXTERNAL_ROUTINE_EXCEPTION), + errmsg("dbmirror:savesequenceupdate could not connect to SPI"))); insertPlan = SPI_prepare(insertQuery, 2, insertArgTypes); insertDataPlan = SPI_prepare(insertDataQuery, 1, insertDataArgTypes); - debug_msg("Prepared insert query"); - - if (insertPlan == NULL || insertDataPlan == NULL) - ereport(ERROR, (errcode(ERRCODE_EXTERNAL_ROUTINE_EXCEPTION), errmsg("dbmirror:nextval error creating plan"))); + ereport(ERROR, + (errcode(ERRCODE_EXTERNAL_ROUTINE_EXCEPTION), + errmsg("dbmirror:savesequenceupdate error creating plan"))); + insertDatum[0] = PointerGetDatum(get_rel_name(relid)); insertDatum[1] = Int32GetDatum(GetCurrentTransactionId()); - insertDatum[0] = PointerGetDatum(sequenceName); - sprintf(nextSequenceText, "%d", nextSequenceVal); + snprintf(nextSequenceText, sizeof(nextSequenceText), + INT64_FORMAT ",'%c'", + nextValue, iscalled ? 't' : 'f'); + + /* + * note type cheat here: we prepare a C string and then claim it is a + * NAME, which the system will coerce to varchar for us. + */ insertDataDatum[0] = PointerGetDatum(nextSequenceText); - debug_msg2("dbmirror:savesequenceupdate: Setting value %s", + + debug_msg2("dbmirror:savesequenceupdate: Setting value as %s", nextSequenceText); debug_msg("dbmirror:About to execute insert query"); - ret = SPI_execp(insertPlan, insertDatum, NULL, 1); + if (SPI_execp(insertPlan, insertDatum, NULL, 1) != SPI_OK_INSERT) + ereport(ERROR, + (errcode(ERRCODE_EXTERNAL_ROUTINE_EXCEPTION), + errmsg("error inserting row in dbmirror_Pending"))); - ret = SPI_execp(insertDataPlan, insertDataDatum, NULL, 1); + if (SPI_execp(insertDataPlan, insertDataDatum, NULL, 1) != SPI_OK_INSERT) + ereport(ERROR, + (errcode(ERRCODE_EXTERNAL_ROUTINE_EXCEPTION), + errmsg("error inserting row in dbmirror_PendingData"))); debug_msg("dbmirror:Insert query finished"); SPI_pfree(insertPlan); SPI_pfree(insertDataPlan); - return ret; - + SPI_finish(); } diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index 099f5e8ab6..48caaa2994 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -1,5 +1,5 @@ @@ -3113,6 +3113,18 @@ SELECT * FROM pg_attribute operand. + + An additional property of the OID alias types is that if a + constant of one of these types appears in a stored expression + (such as a column default expression or view), it creates a dependency + on the referenced object. For example, if a column has a default + expression nextval('my_seq'::regclass), + PostgreSQL + understands that the default expression depends on the sequence + my_seq; the system will not let the sequence be dropped + without first removing the default expression. + + Another identifier type used by the system is xid, or transaction (abbreviated xact) identifier. This is the data type of the system columns diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index c6c9d87e8a..a641db7ee7 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,5 +1,5 @@ @@ -6875,12 +6875,12 @@ SELECT TIMESTAMP 'now'; -- incorrect for use with DEFAULT - nextval(text) + nextval(regclass) bigint Advance sequence and return new value - currval(text) + currval(regclass) bigint Return value most recently obtained with nextval for specified sequence @@ -6891,12 +6891,12 @@ SELECT TIMESTAMP 'now'; -- incorrect for use with DEFAULT Return value most recently obtained with nextval - setval(text, bigint) + setval(regclass, bigint) bigint Set sequence's current value - setval(text, bigint, boolean) + setval(regclass, bigint, boolean) bigint Set sequence's current value and is_called flag @@ -6905,11 +6905,15 @@ SELECT TIMESTAMP 'now'; -- incorrect for use with DEFAULT - For largely historical reasons, the sequence to be operated on by a - sequence-function call is specified by a text-string argument. To + The sequence to be operated on by a sequence-function call is specified by + a regclass argument, which is just the OID of the sequence in the + pg_class system catalog. You do not have to look up the + OID by hand, however, since the regclass datatype's input + converter will do the work for you. Just write the sequence name enclosed + in single quotes, so that it looks like a literal constant. To achieve some compatibility with the handling of ordinary - SQL names, the sequence functions convert their - argument to lowercase unless the string is double-quoted. Thus + SQL names, the string will be converted to lowercase + unless it contains double quotes around the sequence name. Thus nextval('foo') operates on sequence foo nextval('FOO') operates on sequence foo @@ -6921,10 +6925,46 @@ nextval('myschema.foo') operates on myschema.foosame as above nextval('foo') searches search path for foo - Of course, the text argument can be the result of an expression, - not only a simple literal, which is occasionally useful. + See for more information about + regclass. + + + Before PostgreSQL 8.1, the arguments of the + sequence functions were of type text, not regclass, and + the above-described conversion from a text string to an OID value would + happen at runtime during each call. For backwards compatibility, this + facility still exists, but internally it is now handled as an implicit + coercion from text to regclass before the function is + invoked. + + + + When you write the argument of a sequence function as an unadorned + literal string, it becomes a constant of type regclass. + Since this is really just an OID, it will track the originally + identified sequence despite later renaming, schema reassignment, + etc. This early binding behavior is usually desirable for + sequence references in column defaults and views. But sometimes you will + want late binding where the sequence reference is resolved + at runtime. To get late-binding behavior, force the constant to be + stored as a text constant instead of regclass: + +nextval('foo'::text) foo is looked up at runtime + + Note that late binding was the only behavior supported in + PostgreSQL releases before 8.1, so you + may need to do this to preserve the semantics of old applications. + + + + Of course, the argument of a sequence function can be an expression + as well as a constant. If it is a text expression then the implicit + coercion will result in a run-time lookup. + + + The available sequence functions are: @@ -7001,6 +7041,14 @@ SELECT setval('foo', 42, false); Next nextval wi + + If a sequence object has been created with default parameters, + nextval calls on it will return successive values + beginning with 1. Other behaviors can be obtained by using + special parameters in the command; + see its command reference page for more information. + + To avoid blocking of concurrent transactions that obtain numbers from the @@ -7013,14 +7061,6 @@ SELECT setval('foo', 42, false); Next nextval wi - - If a sequence object has been created with default parameters, - nextval calls on it will return successive values - beginning with 1. Other behaviors can be obtained by using - special parameters in the command; - see its command reference page for more information. - - diff --git a/doc/src/sgml/release.sgml b/doc/src/sgml/release.sgml index 092179f01b..37d67a9479 100644 --- a/doc/src/sgml/release.sgml +++ b/doc/src/sgml/release.sgml @@ -1,5 +1,5 @@