Обсуждение: 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.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;
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
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:
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 14 July 2015 at 16:02, David G. Johnston <david.g.johnston@gmail.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
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