FW: Getting information about sequences

Hello all again,

Thank you for all of your replies,

We are creating a jdbc backup solution that does NOT require the pgdump
program.
We have managed to get it going pretty fast, and the output size at the
moment is rellativelly smaller, but anyway. Most of the key information you
can get from the driver, like primary, foreign keys, but we have a problem
when we restore this information back.

We totally destroy the database being restored and create a new one from
scratch with the information from the backup we took. We need to get the
sequences to the proper value. So at the moment the way we are doing it is
during the backup we get all the sequences from the database, then we get
the primary keys for all the tables, map them using
'table_name'_'id_name'_seq and issue the following command for each sequence
SELECT setval('sequence', (SELECT MAX('id_name') from 'table_name')); during
the restore.

At the moment it works, but it is too sloppy, and apparently not that easy.
As to the temporary table it is not a solution. Because it will not always
be available. The user can backup the info and move to another country(which
is what is happening) and restore this same information on another location.
Part of some requirement.

Once again Thank you all for your replies,

Best Regards,
Fotis

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Tom Lane
> Sent: 15 May 2006 18:11
> To: Martijn van Oosterhout
> Cc: Forums @ Existanze; pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Getting information about sequences
>
> Martijn van Oosterhout <kleptog(at)svana(dot)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
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>