Thread: List last value of all sequences
I'm interested in producing a list of all sequence names and the
corresponding last value. Starting with a list of sequence names
generated by
SELECT c.relname FROM pg_class c WHERE (c.relkind = 'S');
my initial thought was to extend that with
SELECT c.relname, currval(c.relname)
FROM pg_class c WHERE (c.relkind = 'S');
but of course that doesn't work since "currval" is not defined until
"nextval" has been called at least once, and I don't want to increment
the sequences...just query the value.
I know that for a given sequence, E.G., "city_city_seq" I can get its
value using
SELECT last_value FROM city_city_seq;
So my next try used a function defined as
CREATE FUNCTION public.get_sequence_last_value(name) RETURNS int4 AS '
DECLARE
ls_sequence ALIAS FOR $1;
lr_record RECORD;
li_return INT4;
BEGIN
FOR lr_record IN EXECUTE ''SELECT last_value FROM '' || ls_sequence LOOP
li_return := lr_record.last_value;
END LOOP;
RETURN li_return;
END;' LANGUAGE 'plpgsql' VOLATILE;
Followed by
SELECT c.relname, get_sequence_last_value(c.relname)
FROM pg_class c WHERE (c.relkind = 'S');
Which works and produces the result I want, but that function seems
really messy. Is there a cleaner way to do this?
~Berend Tober
On Thursday 24 July 2003 13:46, btober@seaworthysys.com wrote: > I'm interested in producing a list of all sequence names and the > corresponding last value. Starting with a list of sequence names > generated by > > SELECT c.relname FROM pg_class c WHERE (c.relkind = 'S'); [snip] > So my next try used a function defined as > > CREATE FUNCTION public.get_sequence_last_value(name) RETURNS int4 AS ' > DECLARE > ls_sequence ALIAS FOR $1; > lr_record RECORD; > li_return INT4; > BEGIN > FOR lr_record IN EXECUTE ''SELECT last_value FROM '' || ls_sequence LOOP > li_return := lr_record.last_value; > END LOOP; > RETURN li_return; > END;' LANGUAGE 'plpgsql' VOLATILE; > > Followed by > > SELECT c.relname, get_sequence_last_value(c.relname) > FROM pg_class c WHERE (c.relkind = 'S'); > > Which works and produces the result I want, but that function seems > really messy. Is there a cleaner way to do this? Why not just have two (nested) loops in the function? FOR class_rec IN SELECT c.relname FROM pg_class WHERE c.relkind=''S'' LOOP FOR lr_record IN EXECTUTE ... || class_rec.relname... LOOP ... END LOOP END LOOP Or am I missing something? -- Richard Huxton Archonet Ltd
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 This will work in most cases: SELECT c.relname, setval(c.relname, CASE WHEN nextval(c.relname) > 1 THEN currval(c.relname)-1 ELSE 1 END,'true') FROM pg_class c WHERE c.relkind='S'; It works for simple sequences in which the number is incremented by 1 each time it is called. We need the CASE to account for newly created sequences in which the last_value is equal to min_value (1) and the "is_called" flag is set to false. It will still fail on other cases, such as sequences that start with something other than 1, increment other than +1, or are at their max_value. The function you provided should work fine as well, although it should return BIGINT, not int4. If you are doing this check often, you might want to also have a function that returns all the sequences for you when called. - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200307241009 -----BEGIN PGP SIGNATURE----- Comment: http://www.turnstep.com/pgp.html iD8DBQE/H+mHvJuQZxSWSsgRAicMAJ4zqV/UmDlUKdQtI8e3qAorEJeKPACfVs97 vx8Oc9kFaGd8tpd1+yhR7jY= =zJKz -----END PGP SIGNATURE-----
> This will work in most cases:
>
> SELECT c.relname,
> setval(c.relname, CASE WHEN nextval(c.relname) > 1 THEN
> currval(c.relname)-1 ELSE 1 END,'true')
> FROM pg_class c WHERE c.relkind='S';
>
The main problem with this approach is that, while you get the "current
value", the sequence is incremented by the call. I just want to
(strictly) look at the value.
I think I forgot to cc the list in a reply to another respondent in which
I explained the further, previously unstated objective of creating an
updateble view so that I can conveniently see AND CHANGE the sequence
values:
CREATE FUNCTION public.set_sequence(name, int4) RETURNS int4 AS '
DECLARE
l_sequence_name ALIAS FOR $1;
l_last_value ALIAS FOR $2;
BEGIN
IF l_last_value = 0 THEN
PERFORM setval(l_sequence_name,1, False);
ELSE
PERFORM setval(l_sequence_name,l_last_value);
END IF;
RETURN 1;
END;' LANGUAGE 'plpgsql' VOLATILE;
CREATE VIEW public.sequence_values AS
SELECT pg_get_userbyid(c.relowner) AS sequenceowner,
c.relname AS sequencename,
get_sequence_last_value(c.relname) AS last_value
FROM pg_class c
WHERE (c.relkind = 'S')
ORDER BY pg_get_userbyid(c.relowner), c.relname;
CREATE RULE sequence_values_rd AS ON DELETE TO sequence_values DO INSTEAD
NOTHING;
CREATE RULE sequence_values_ri AS ON INSERT TO sequence_values DO INSTEAD
NOTHING;
CREATE RULE sequence_values_ru AS ON UPDATE TO sequence_values DO INSTEAD
SELECT set_sequence(new.sequencename, new.last_value) AS set_sequence;
~Berend Tober
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > The main problem with this approach is that, while you get the "current > value", the sequence is incremented by the call. I just want to > (strictly) look at the value. The sequence values do not change: test it for yourself. > IF l_last_value = 0 THEN > PERFORM setval(l_sequence_name,1, False); > ELSE > PERFORM setval(l_sequence_name,l_last_value); > END IF; Pretty good idea; just beware of sequences that do not start at 1 :) - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200307241520 -----BEGIN PGP SIGNATURE----- Comment: http://www.turnstep.com/pgp.html iD8DBQE/IDPUvJuQZxSWSsgRAv1MAJ9Ax8EpRTf2ElbdeN/hjMIkWSpPqwCgqAVV Xgese545H2Qa4Znwt1Y7AlE= =IkFq -----END PGP SIGNATURE-----
On Thu, 24 Jul 2003 greg@turnstep.com wrote: > > The main problem with this approach is that, while you get the "current > > value", the sequence is incremented by the call. I just want to > > (strictly) look at the value. > > The sequence values do not change: test it for yourself. I'm not sure, but if another transaction wanted to do a nextval at "the same time" couldn't the sequence of events end up going something like: T1: nextval T2: nextval T1: setval(currval-1) which would do the wrong thing since the next nextval would give the same value that T2 already got?
>> The main problem with this approach is that, while you get the >> "current value", the sequence is incremented by the call. I just want >> to (strictly) look at the value. > > The sequence values do not change: test it for yourself. I guess nothing beats empirical evidence! I was basing my comments on my (mis?)understanding of what the documentation says. >> IF l_last_value = 0 THEN >> PERFORM setval(l_sequence_name,1, False); >> ELSE >> PERFORM setval(l_sequence_name,l_last_value); >> END IF; > > Pretty good idea; just beware of sequences that do not start at 1 :) > Good point. I think I can deal with that. Thank you. ~Berend Tober
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Stephan Szabo wrote: > I'm not sure, but if another transaction wanted to do a nextval at "the > same time" couldn't the sequence of events end up going something like: > > T1: nextval > T2: nextval > T1: setval(currval-1) *shrug* It's possible, though perhaps unlikely, as transaction 2 has a /very/ small window in which to wreak its havok. I like the function solution better anyway; consider the pure SQL one an academic exercise. :) Berend Tober said: > I guess nothing beats empirical evidence! I was basing my comments on my > (mis?)understanding of what the documentation says. My query should match the docs. I'm getting the next value, which increments the sequence by one, then I set the sequence to one less than the value I just grabbed, so it should be back in its initial state. So, we are both correct: I am changing the value, and it stays the same. Aren't sequences fun? :) - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200307241716 -----BEGIN PGP SIGNATURE----- Comment: http://www.turnstep.com/pgp.html iD4DBQE/IE1XvJuQZxSWSsgRApJsAJdha8ZoVJi0QYxRK/2hgnZKLObyAJ4gkBJI 349WNU+i+5j6WttaGcgKtw== =C0O+ -----END PGP SIGNATURE-----