From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Eisentraut <peter_e@gmx.net>
Cc: saera87@hotmail.com
Cc: pgsql-bugs@postgresql.org
Subject: Re: BUG #5662: Incomplete view
Date: Sun, 19 Sep 2010 14:28:18 -0400
Message-ID: <6630.1284920898@sss.pgh.pa.us> (raw)
In-Reply-To: <1284918458.20510.0.camel@vanquo.pezone.net>
References: <201009190941.o8J9fT6H048689@wwwmaster.postgresql.org>
<1284918458.20510.0.camel@vanquo.pezone.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tgl@sss.pgh.pa.us, peter_e@gmx.net, saera87@hotmail.com, pgsql-bugs@postgresql.org
Subject: Re: BUG #5662: Incomplete view
In-Reply-To: <6630.1284920898@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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