Getting information about sequences

On Mon, May 15, 2006 at 03:55:49PM +0300, Forums @ Existanze wrote:

Hello all,

I would like to ask if anyone can explain or point me to some sort of query
that would get the sequences for a database along with the current value
they contain.

I have been able to get the sequence names, but I can get their values all
in one query. From the documentation I can't use currval() because it needs
setval() to precede it in the same session.

Select sequence.last_value;

It's not guarenteed to give a value that unique across users, that's
what currval/nextval are for. For if you just want an idea of where
it's up to, this will do.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Martijn van Oosterhout <kleptog@svana.org> writes:

On Mon, May 15, 2006 at 03:55:49PM +0300, Forums @ Existanze wrote:

I have been able to get the sequence names, but I can get their values all
in one query.

Select sequence.last_value;

The hard part of the request is to do it "all in one query", though,
ie somehow show
select relname, last_value from pg_class where relkind = 'S';

I haven't been able to think of a way to do that, unless you want to
assume the existence of a plpgsql helper function. There's an open
request to list last_values in psql's "\ds", and it'd be real nice
to be able to do it all in one query for that.

regards, tom lane

On Mon, May 15, 2006 at 10:44:15AM -0400, Tom Lane wrote:

I haven't been able to think of a way to do that, unless you want to
assume the existence of a plpgsql helper function. There's an open
request to list last_values in psql's "\ds", and it'd be real nice
to be able to do it all in one query for that.

Long term I see a few ways of dealing with this:

- Pull that idea of storing all sequences in one table off the
shelf and implement it. The new heap up-date-in-place function may come
in handy there.

- Find a way of allowing functions to be declared inline, to avoid
creating system functions continuously. I don't know if there's
precedent for this.

- Create a function called: gettable(reloid) returns record, that takes
a relation OID and returns all the records in it. If all the tables
you're dealing with have a similar structure, you can use this to
iteratoe over, sequences just being a special case.

There's probably more, but that's all I can think of right now.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Martijn van Oosterhout <kleptog@svana.org> writes:

On Mon, May 15, 2006 at 10:44:15AM -0400, Tom Lane wrote:

I haven't been able to think of a way to do that, unless you want to
assume the existence of a plpgsql helper function. There's an open
request to list last_values in psql's "\ds", and it'd be real nice
to be able to do it all in one query for that.

Long term I see a few ways of dealing with this:
[ ideas snipped ]

Yeah (actually the point about the update-in-place code being relevant
had just occurred to me too). However, none of these are really
desirable solutions from psql's point of view, because they could only
work in 8.2 and later (or whenever we implemented them). It'd be nicer
if \ds still worked against back-rev servers, which means we need a
solution that works with the current server API. I'm thinking that psql
will need to pull the main \ds query result, and then manually issue a
select against each of the sequences (ick). On the other hand this may
be the best thing anyway, since it's entirely likely that some of those
selects would fail for permissions reasons, and we don't want the whole
\ds operation to go down in flames just because you don't have select
rights on one sequence.

regards, tom lane