pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #5662: Incomplete view
7+ messages / 3 participants
[nested] [flat]
* BUG #5662: Incomplete view
@ 2010-09-19 09:41 saera87@hotmail.com
0 siblings, 1 reply; 7+ messages in thread
From: saera87@hotmail.com @ 2010-09-19 09:41 UTC (permalink / raw)
To: pgsql-bugs@postgresql.org
The following bug has been logged online:
Bug reference: 5662
Logged by:
Email address: saera87@hotmail.com
PostgreSQL version: 8.4
Operating system: Windows Vista
Description: Incomplete view
Details:
The Sequence view in the information schema is incomplete. It does not
return a Sequence's maximum_value, minimum_value or increment. Please
complete the view.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #5662: Incomplete view
@ 2010-09-19 17:47 Peter Eisentraut <peter_e@gmx.net>
parent: saera87@hotmail.com
0 siblings, 1 reply; 7+ messages in thread
From: Peter Eisentraut @ 2010-09-19 17:47 UTC (permalink / raw)
To: saera87@hotmail.com; +Cc: pgsql-bugs@postgresql.org
On sön, 2010-09-19 at 09:41 +0000, saera87@hotmail.com wrote:
> The Sequence view in the information schema is incomplete. It does not
> return a Sequence's maximum_value, minimum_value or increment. Please
> complete the view.
This is known and documented:
http://www.postgresql.org/docs/8.4/static/infoschema-sequences.html
Should still be fixed eventually, of course.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #5662: Incomplete view
@ 2010-09-19 18:28 Tom Lane <tgl@sss.pgh.pa.us>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 2 replies; 7+ messages in thread
From: Tom Lane @ 2010-09-19 18:28 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: saera87@hotmail.com; pgsql-bugs@postgresql.org
Peter Eisentraut <peter_e@gmx.net> writes:
> On sön, 2010-09-19 at 09:41 +0000, saera87@hotmail.com wrote:
>> The Sequence view in the information schema is incomplete. It does not
>> return a Sequence's maximum_value, minimum_value or increment. Please
>> complete the view.
> This is known and documented:
> http://www.postgresql.org/docs/8.4/static/infoschema-sequences.html
> Should still be fixed eventually, of course.
I think the difficulty is in the fact that you can't join to a sequence
whose name isn't predetermined. In the past we've speculated about
creating a single catalog or view containing all sequences' parameters,
so that information_schema.sequences could be implemented with a join
to that. However, there's never been any movement on that, and it seems
less than trivial to do.
What about inventing a function to extract a sequence's parameters?
Perhaps something like
pg_sequence_parameter(seq regclass, colname text) returns bigint
which would do an appropriate permissions check and then fetch the named
column. (This could actually be implemented in a line or two in
plpgsql, but I think we want it in C because information_schema
shouldn't depend on plpgsql.) This would work OK for all the bigint
columns, and we could cheat a bit for the boolean columns by returning
0 or 1. You couldn't fetch the sequence_name column this way, but
that's okay with me --- we don't maintain that anyway.
Given that, the sequence view would include outputs like
CAST(pg_sequence_parameter(c.oid, 'max_value') AS cardinal_number) AS maximum_value,
The main objection I can see to this is that fetching multiple column
values would involve multiple accesses to the sequence. But it's not
clear that a solution based on a single view would be any better
performance-wise.
Another possibility, if we had LATERAL, would be a function that
takes just the sequence OID and returns all its parameters as a row.
But again, if we want to do it that way then fixing the view will
involve waiting for a complex feature that might or might not
show up anytime soon.
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.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #5662: Incomplete view
@ 2010-09-22 13:09 Peter Eisentraut <peter_e@gmx.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 7+ messages in thread
From: Peter Eisentraut @ 2010-09-22 13:09 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: saera87@hotmail.com; pgsql-bugs@postgresql.org
On sön, 2010-09-19 at 14:28 -0400, Tom Lane wrote:
> What about inventing a function to extract a sequence's parameters?
> Perhaps something like
>
> pg_sequence_parameter(seq regclass, colname text) returns
> bigint
>
> which would do an appropriate permissions check and then fetch the
> named column. (This could actually be implemented in a line or two in
> plpgsql, but I think we want it in C because information_schema
> shouldn't depend on plpgsql.)
Why shouldn't it?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #5662: Incomplete view
@ 2010-09-22 13:59 Tom Lane <tgl@sss.pgh.pa.us>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2010-09-22 13:59 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: saera87@hotmail.com; pgsql-bugs@postgresql.org
Peter Eisentraut <peter_e@gmx.net> writes:
> On sön, 2010-09-19 at 14:28 -0400, Tom Lane wrote:
>> which would do an appropriate permissions check and then fetch the
>> named column. (This could actually be implemented in a line or two in
>> plpgsql, but I think we want it in C because information_schema
>> shouldn't depend on plpgsql.)
> Why shouldn't it?
Because plpgsql is removable (and I don't think that property is
negotiable).
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [BUGS] BUG #5662: Incomplete view
@ 2010-12-06 12:47 Peter Eisentraut <peter_e@gmx.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 7+ messages in thread
From: Peter Eisentraut @ 2010-12-06 12:47 UTC (permalink / raw)
To: pgsql-hackers; +Cc: saera87@hotmail.com, Tom Lane <tgl@sss.pgh.pa.us>
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.
Attachments:
sequence-parameters.patch (text/x-patch, 7.3K) download
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;
</row>
<row>
- <entry><literal>maximum_value</literal></entry>
- <entry><type>cardinal_number</type></entry>
- <entry>Not yet implemented</entry>
+ <entry><literal>start_value</literal></entry>
+ <entry><type>character_data</type></entry>
+ <entry>The start value of the sequence</entry>
</row>
<row>
<entry><literal>minimum_value</literal></entry>
- <entry><type>cardinal_number</type></entry>
- <entry>Not yet implemented</entry>
+ <entry><type>character_data</type></entry>
+ <entry>The minimum value of the sequence</entry>
+ </row>
+
+ <row>
+ <entry><literal>maximum_value</literal></entry>
+ <entry><type>character_data</type></entry>
+ <entry>The maximum value of the sequence</entry>
</row>
<row>
<entry><literal>increment</literal></entry>
- <entry><type>cardinal_number</type></entry>
- <entry>Not yet implemented</entry>
+ <entry><type>character_data</type></entry>
+ <entry>The increment of the sequence</entry>
</row>
<row>
<entry><literal>cycle_option</literal></entry>
<entry><type>yes_or_no</type></entry>
- <entry>Not yet implemented</entry>
+ <entry><literal>YES</literal> if the sequence cycles, else <literal>NO</literal></entry>
</row>
</tbody>
</tgroup>
</table>
+
+ <para>
+ Note that in accordance with the SQL standard, the start, minimum,
+ maximum, and increment values are returned as character strings.
+ </para>
</sect1>
<sect1 id="infoschema-sql-features">
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);
* Re: [BUGS] BUG #5662: Incomplete view
@ 2011-01-02 13:17 Peter Eisentraut <peter_e@gmx.net>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 0 replies; 7+ messages in thread
From: Peter Eisentraut @ 2011-01-02 13:17 UTC (permalink / raw)
To: pgsql-hackers; +Cc: saera87@hotmail.com, Tom Lane <tgl@sss.pgh.pa.us>
On mån, 2010-12-06 at 14:47 +0200, Peter Eisentraut wrote:
> 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.
Committed.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2011-01-02 13:17 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-09-19 09:41 BUG #5662: Incomplete view saera87@hotmail.com
2010-09-19 17:47 ` Peter Eisentraut <peter_e@gmx.net>
2010-09-19 18:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-09-22 13:09 ` Peter Eisentraut <peter_e@gmx.net>
2010-09-22 13:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-12-06 12:47 ` Peter Eisentraut <peter_e@gmx.net>
2011-01-02 13:17 ` Peter Eisentraut <peter_e@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox