Re: [BUGS] BUG #5662: Incomplete view

The following bug has been logged online:

Bug reference: 5662
Logged by:
Email address: saera87(at)hotmail(dot)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.

On sön, 2010-09-19 at 09:41 +0000, saera87(at)hotmail(dot)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.

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> On sn, 2010-09-19 at 09:41 +0000, saera87(at)hotmail(dot)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

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?

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> On sn, 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

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.

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.