Support retrieving value from any sequence
Hi all,
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;
Thom
On Tue, Jul 14, 2015 at 10:52 AM, Thom Brown <thom@linux.com> wrote:
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.
Why wouldn't you just query the catalog? I was under the impression last
said values were extra-transactional so that table should reflect the
global state.
What am I missing here?
David J.
On 14 July 2015 at 16:02, David G. Johnston <david.g.johnston@gmail.com>
wrote:
On Tue, Jul 14, 2015 at 10:52 AM, Thom Brown <thom@linux.com> wrote:
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.Why wouldn't you just query the catalog? I was under the impression
last said values were extra-transactional so that table should reflect the
global state.What am I missing here?
Where in the catalog do you mean?
Thom
On Tue, Jul 14, 2015 at 11:05 AM, Thom Brown <thom@linux.com> wrote:
On 14 July 2015 at 16:02, David G. Johnston <david.g.johnston@gmail.com>
wrote:On Tue, Jul 14, 2015 at 10:52 AM, Thom Brown <thom@linux.com> wrote:
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.Why wouldn't you just query the catalog? I was under the impression
last said values were extra-transactional so that table should reflect the
global state.What am I missing here?
Where in the catalog do you mean?
In attempting to answer your question I now better understand your
original proposal. Indeed the only way to get the sequence information is
to query it like a table.
This prompts the question: why a function and not (or in addition to) to a
view?
David J.
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers