Обсуждение: Support retrieving value from any sequence

On Tue, Jul 14, 2015 at 10:52 AM, Thom Brown <thom@linux.com> wrote:
> When using currval() to find the current value of all sequences, it chokes
> on those that aren't initialised.  This is expected and documented as
> behaving in this manner.  However, I think it would be useful to also
> support retrieving the current value of a sequence, regardless of whether
> it's been used.  As this wouldn't be to get a sequence value for the current
> session, but all sessions, this would ideally get the real current value.
>
> The use-case I have in mind is for finding out how close to the 32-bit
> integer limit sequences have reached.  At the moment, this isn't possible
> without creating a custom function to go fetch the last_value from the
> specified sequence.
>
> So would it be desirable to have a function which accepts a sequence
> regclass as a parameter, and returns the last_value from the sequence?
>
> Effectively, the same result as what this provides:
>
> CREATE FUNCTION lastval(tablename regclass) RETURNS bigint AS $$
> DECLARE
>   last_value bigint;
> BEGIN
>   EXECUTE format('SELECT last_value FROM %I ', tablename) INTO last_value
> USING tablename;
>   RETURN last_value;
> END
> $$ LANGUAGE plpgsql;

Since it's trivial to define this function if you need it, I'm not
sure there's a reason to include it in core.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 14 July 2015 at 17:17, Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Tue, Jul 14, 2015 at 10:52 AM, Thom Brown <thom@linux.com> wrote:
> > When using currval() to find the current value of all sequences, it chokes
> > on those that aren't initialised.  This is expected and documented as
> > behaving in this manner.  However, I think it would be useful to also
> > support retrieving the current value of a sequence, regardless of whether
> > it's been used.  As this wouldn't be to get a sequence value for the current
> > session, but all sessions, this would ideally get the real current value.
> >
> > The use-case I have in mind is for finding out how close to the 32-bit
> > integer limit sequences have reached.  At the moment, this isn't possible
> > without creating a custom function to go fetch the last_value from the
> > specified sequence.
> >
> > So would it be desirable to have a function which accepts a sequence
> > regclass as a parameter, and returns the last_value from the sequence?
> >
> > Effectively, the same result as what this provides:
> >
> > CREATE FUNCTION lastval(tablename regclass) RETURNS bigint AS $$
> > DECLARE
> >   last_value bigint;
> > BEGIN
> >   EXECUTE format('SELECT last_value FROM %I ', tablename) INTO last_value
> > USING tablename;
> >   RETURN last_value;
> > END
> > $$ LANGUAGE plpgsql;
>
> Since it's trivial to define this function if you need it, I'm not
> sure there's a reason to include it in core.

It's not always possible to create functions on a system when access
is restricted.  It may even be the case that procedural languages are
prohibited, and plpgsql has been removed.

Thom

Thom Brown <thom@linux.com> writes:
> On 14 July 2015 at 17:17, Robert Haas <robertmhaas@gmail.com> wrote:
>> Since it's trivial to define this function if you need it, I'm not
>> sure there's a reason to include it in core.

> It's not always possible to create functions on a system when access
> is restricted.  It may even be the case that procedural languages are
> prohibited, and plpgsql has been removed.

By that argument, *any* random function has to be in the core.

I really don't see what's wrong with "SELECT last_value FROM sequence",
especially since that has worked in every Postgres version since 6.x.
Anyone slightly worried about backwards compatibility wouldn't use
an equivalent function even if we did add one.
        regards, tom lane

On 7/14/15 12:06 PM, Tom Lane wrote:
> Thom Brown <thom@linux.com> writes:
>> On 14 July 2015 at 17:17, Robert Haas <robertmhaas@gmail.com> wrote:
>>> Since it's trivial to define this function if you need it, I'm not
>>> sure there's a reason to include it in core.
>
>> It's not always possible to create functions on a system when access
>> is restricted.  It may even be the case that procedural languages are
>> prohibited, and plpgsql has been removed.
>
> By that argument, *any* random function has to be in the core.
>
> I really don't see what's wrong with "SELECT last_value FROM sequence",
> especially since that has worked in every Postgres version since 6.x.
> Anyone slightly worried about backwards compatibility wouldn't use
> an equivalent function even if we did add one.

Because you can't do that for all functions in a database.

FWIW, I think it'd be better to have a pg_sequences view that's the 
equivalent of SELECT * FROM <sequence> for every sequence in the 
database. That would let you get whatever info you needed.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Data in Trouble? Get it in Treble! http://BlueTreble.com