Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 6921C1337B5B for ; Mon, 6 Dec 2010 08:47:15 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 46380-05 for ; Mon, 6 Dec 2010 12:47:09 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail.gmx.net (mailout-de.gmx.net [213.165.64.22]) by mail.postgresql.org (Postfix) with SMTP id D733E1337B4F for ; Mon, 6 Dec 2010 08:47:06 -0400 (AST) Received: (qmail invoked by alias); 06 Dec 2010 12:47:04 -0000 Received: from a88-115-218-165.elisa-laajakaista.fi (EHLO [10.0.0.101]) [88.115.218.165] by mail.gmx.net (mp048) with SMTP; 06 Dec 2010 13:47:04 +0100 X-Authenticated: #495269 X-Provags-ID: V01U2FsdGVkX1/8LJxCxxH82IO5scHDo1QAJSC5QmH/4gl7dMuXtt grPUfGLWQ8JkwM Subject: Re: [BUGS] BUG #5662: Incomplete view From: Peter Eisentraut To: pgsql-hackers@postgresql.org Cc: saera87@hotmail.com, Tom Lane In-Reply-To: <6630.1284920898@sss.pgh.pa.us> References: <201009190941.o8J9fT6H048689@wwwmaster.postgresql.org> <1284918458.20510.0.camel@vanquo.pezone.net> <6630.1284920898@sss.pgh.pa.us> Content-Type: multipart/mixed; boundary="=-Br9wh1QaDS1Cn/13aehE" Date: Mon, 06 Dec 2010 14:47:02 +0200 Message-ID: <1291639622.10677.12.camel@vanquo.pezone.net> Mime-Version: 1.0 X-Mailer: Evolution 2.30.3 X-Y-GMX-Trusted: 0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, RCVD_IN_DNSWL_NONE=-0.0001, T_RP_MATCHES_RCVD=-0.01, T_TO_NO_BRKTS_FREEMAIL=0.01 X-Spam-Level: X-Archive-Number: 201012/406 X-Sequence-Number: 175668 --=-Br9wh1QaDS1Cn/13aehE Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: 8bit On sön, 2010-09-19 at 14:28 -0400, Tom Lane wrote: > Or maybe we could implement that function, call it like this > > CAST((pg_sequence_parameters(c.oid)).max_value AS > cardinal_number) AS maximum_value, > > and plan on optimizing the view when we get LATERAL. Here is an implementation of that. I'm not exactly sure if the accesses to the sequence are correctly locked/unlocked, but it appears to work. I also revised the definition of the info schema view slightly, after juggling several more recent SQL standard drafts. --=-Br9wh1QaDS1Cn/13aehE Content-Disposition: attachment; filename="sequence-parameters.patch" Content-Type: text/x-patch; name="sequence-parameters.patch"; charset="UTF-8" Content-Transfer-Encoding: 7bit diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml index 9d30949..0c1cb5d 100644 --- a/doc/src/sgml/information_schema.sgml +++ b/doc/src/sgml/information_schema.sgml @@ -4013,31 +4013,42 @@ ORDER BY c.ordinal_position; - maximum_value - cardinal_number - Not yet implemented + start_value + character_data + The start value of the sequence minimum_value - cardinal_number - Not yet implemented + character_data + The minimum value of the sequence + + + + maximum_value + character_data + The maximum value of the sequence increment - cardinal_number - Not yet implemented + character_data + The increment of the sequence cycle_option yes_or_no - Not yet implemented + YES if the sequence cycles, else NO + + + Note that in accordance with the SQL standard, the start, minimum, + maximum, and increment values are returned as character strings. + diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index 8d9790d..1c2bd85 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -1430,16 +1430,17 @@ CREATE VIEW sequences AS CAST(64 AS cardinal_number) AS numeric_precision, CAST(2 AS cardinal_number) AS numeric_precision_radix, CAST(0 AS cardinal_number) AS numeric_scale, - CAST(null AS cardinal_number) AS maximum_value, -- FIXME - CAST(null AS cardinal_number) AS minimum_value, -- FIXME - CAST(null AS cardinal_number) AS increment, -- FIXME - CAST(null AS yes_or_no) AS cycle_option -- FIXME + CAST((pg_sequence_parameters(c.oid)).start_value AS character_data) AS start_value, + CAST((pg_sequence_parameters(c.oid)).minimum_value AS character_data) AS minimum_value, + CAST((pg_sequence_parameters(c.oid)).maximum_value AS character_data) AS maximum_value, + CAST((pg_sequence_parameters(c.oid)).increment AS character_data) AS increment, + CAST(CASE WHEN (pg_sequence_parameters(c.oid)).cycle_option THEN 'YES' ELSE 'NO' END AS yes_or_no) AS cycle_option FROM pg_namespace nc, pg_class c WHERE c.relnamespace = nc.oid AND c.relkind = 'S' AND (NOT pg_is_other_temp_schema(nc.oid)) AND (pg_has_role(c.relowner, 'USAGE') - OR has_table_privilege(c.oid, 'SELECT, UPDATE') ); + OR has_sequence_privilege(c.oid, 'SELECT, UPDATE, USAGE') ); GRANT SELECT ON sequences TO PUBLIC; diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c index bb8ebce..0070bb0 100644 --- a/src/backend/commands/sequence.c +++ b/src/backend/commands/sequence.c @@ -24,6 +24,7 @@ #include "commands/defrem.h" #include "commands/sequence.h" #include "commands/tablecmds.h" +#include "funcapi.h" #include "miscadmin.h" #include "nodes/makefuncs.h" #include "storage/bufmgr.h" @@ -1420,6 +1421,56 @@ process_owned_by(Relation seqrel, List *owned_by) } +/* + * Return sequence parameters, for use by information schema + */ +Datum +pg_sequence_parameters(PG_FUNCTION_ARGS) +{ + Oid relid = PG_GETARG_OID(0); + TupleDesc tupdesc; + Datum values[5]; + bool isnull[5]; + SeqTable elm; + Relation seqrel; + Buffer buf; + Form_pg_sequence seq; + + /* open and AccessShareLock sequence */ + init_sequence(relid, &elm, &seqrel); + + if (pg_class_aclcheck(relid, GetUserId(), ACL_SELECT | ACL_UPDATE | ACL_USAGE) != ACLCHECK_OK) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied for sequence %s", + RelationGetRelationName(seqrel)))); + + tupdesc = CreateTemplateTupleDesc(5, false); + TupleDescInitEntry(tupdesc, (AttrNumber) 1, "start_value", INT8OID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 2, "minimum_value", INT8OID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 3, "maximum_value", INT8OID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 4, "increment", INT8OID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 5, "cycle_option", BOOLOID, -1, 0); + + BlessTupleDesc(tupdesc); + + memset(isnull, 0, sizeof(isnull)); + + seq = read_info(elm, seqrel, &buf); + + values[0] = Int64GetDatum(seq->start_value); + values[1] = Int64GetDatum(seq->min_value); + values[2] = Int64GetDatum(seq->max_value); + values[3] = Int64GetDatum(seq->increment_by); + values[4] = BoolGetDatum(seq->is_cycled); + + UnlockReleaseBuffer(buf); + relation_close(seqrel, NoLock); + + return HeapTupleGetDatum(heap_form_tuple(tupdesc, values, isnull)); +} + + void seq_redo(XLogRecPtr lsn, XLogRecord *record) { diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 6c12f7c..0e0bbc6 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 201012031 +#define CATALOG_VERSION_NO 201012042 #endif diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index feae22e..df80b1f 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -2114,6 +2114,8 @@ DATA(insert OID = 1576 ( setval PGNSP PGUID 12 1 0 0 f f f t f v 2 0 20 "2205 DESCR("set sequence value"); DATA(insert OID = 1765 ( setval PGNSP PGUID 12 1 0 0 f f f t f v 3 0 20 "2205 20 16" _null_ _null_ _null_ _null_ setval3_oid _null_ _null_ _null_ )); DESCR("set sequence value and iscalled status"); +DATA(insert OID = 3078 ( pg_sequence_parameters PGNSP PGUID 12 1 0 0 f f f t f s 1 0 2249 "26" "{23,20,20,20,20,16}" "{i,o,o,o,o,o}" "{sequence_oid,start_value,minimum_value,maximum_value,increment,cycle_option}" _null_ pg_sequence_parameters _null_ _null_ _null_)); +DESCR("sequence parameters, for use by information schema"); DATA(insert OID = 1579 ( varbit_in PGNSP PGUID 12 1 0 0 f f f t f i 3 0 1562 "2275 26 23" _null_ _null_ _null_ _null_ varbit_in _null_ _null_ _null_ )); DESCR("I/O"); diff --git a/src/include/commands/sequence.h b/src/include/commands/sequence.h index b747125..0599239 100644 --- a/src/include/commands/sequence.h +++ b/src/include/commands/sequence.h @@ -69,6 +69,8 @@ extern Datum setval_oid(PG_FUNCTION_ARGS); extern Datum setval3_oid(PG_FUNCTION_ARGS); extern Datum lastval(PG_FUNCTION_ARGS); +extern Datum pg_sequence_parameters(PG_FUNCTION_ARGS); + extern void DefineSequence(CreateSeqStmt *stmt); extern void AlterSequence(AlterSeqStmt *stmt); extern void ResetSequence(Oid seq_relid); --=-Br9wh1QaDS1Cn/13aehE--