Thread: How to get the value in the lastvalue field
Hi, Can anyone in the list tell me how to get the value in the lastvalue field of a sequence. I tried select currval('field_seq');but got an error message ERROR: currval of sequence "field_seq" is not yet defined in this session. I think currval will work only after an insert. I don't want to use nextval as this increases the lastvalue value.
Thanks in advance Kind Regards, | |||
On 10/26/05 8:23 AM, "Venki" <Venki@insoft.com> wrote:
>
> Hi,
> Can anyone in the list tell me how to get the value in the lastvalue field of
> a sequence. I tried
> select currval('field_seq');
>
> but got an error message
>
> ERROR: currval of sequence "field_seq" is not yet defined in this session.
>
> I think currval will work only after an insert. I don't want to use nextval as
> this increases the lastvalue value.
That is correct. You can't call currval until you have called nextval
first. Why do you want to know? That might help answer the question.
Sean
You can select it from the sequence's associated relation as from any
table... try:
select * from sequence_name;
However, be aware that what you see there is the situation only in your
transaction, and it is very possible that other transactions will use
higher values concurrently. So it really depends on what you want to do
if it is actually safe to do it this way...
Cheers,
Csaba.
On Wed, 2005-10-26 at 14:23, Venki wrote:
> Hi,
> Can anyone in the list tell me how to get the value in the lastvalue
> field of a sequence. I tried
>
> select currval('field_seq');
>
> but got an error message
>
> ERROR: currval of sequence "field_seq" is not yet defined in this
> session.
>
> I think currval will work only after an insert. I don't want to use
> nextval as this increases the lastvalue value.
>
>
>
> Thanks in advance
>
> Kind Regards,
> Venki
>
>
>
>
>
>
Hi thanks for the replies. The situation is as follows. We get backups from the production server and update the local database in the local server but each time when we restore the database backup the sequence values are not getting updated properly. So what i thought was to write a function which will check the last value of the row in the table and compare it with the lastvalue of the sequnce and if it is not proper then update the lastvalue of the sequence to a proper value. I Hope that i have explained the situation properly. If you have any other suggestion it will be greatly helpful as we are new to postgres and might be doing something wrong when restoring the database. Regards venki -------Original Message------- From: Csaba Nagy Date: 10/26/05 18:24:48 To: Venki Subject: Re: [GENERAL] How to get the value in the lastvalue field You can select it from the sequence's associated relation as from any table... try: select * from sequence_name; However, be aware that what you see there is the situation only in your transaction, and it is very possible that other transactions will use higher values concurrently. So it really depends on what you want to do if it is actually safe to do it this way... Cheers, Csaba. On Wed, 2005-10-26 at 14:23, Venki wrote: > Hi, > Can anyone in the list tell me how to get the value in the lastvalue > field of a sequence. I tried > > select currval('field_seq'); > > but got an error message > > ERROR: currval of sequence "field_seq" is not yet defined in this > session. > > I think currval will work only after an insert. I don't want to use > nextval as this increases the lastvalue value. > > > > Thanks in advance > > Kind Regards, > Venki > > > > > > | |||
If I was you, I would assign separate non-overlapping sequence ranges
for all servers from start. The ranges should be sized considering what
traffic each server will have. When one of the servers is close to use
up it's sequence range, assign it another one...
Then you will always know that one or other record is coming from which
server, and don't need to care when moving between servers. All other
scenarios are error prone.
Cheers,
Csaba.
On Wed, 2005-10-26 at 14:42, Venki wrote:
> Hi
> thanks for the replies. The situation is as follows. We get backups
> from the production server and update the local database in the
> local server but each time when we restore the database backup the
> sequence values are not getting updated properly. So what i thought
> was to write a function which will check the last value of the row in
> the table and compare it with the lastvalue of the sequnce and if it
> is not proper then update the lastvalue of the sequence to a proper
> value.
>
> I Hope that i have explained the situation properly. If you have any
> other suggestion it will be greatly helpful as we are new to postgres
> and might be doing something wrong when restoring the database.
>
> Regards
> venki
>
> -------Original Message-------
>
> From: Csaba Nagy
> Date: 10/26/05 18:24:48
> To: Venki
> Cc: Postgres general mailing list
> Subject: Re: [GENERAL] How to get the value in the lastvalue field
>
> You can select it from the sequence's associated relation as from any
> table... try:
>
> select * from sequence_name;
>
> However, be aware that what you see there is the situation only in
> your
> transaction, and it is very possible that other transactions will use
> higher values concurrently. So it really depends on what you want to
> do
> if it is actually safe to do it this way...
>
> Cheers,
> Csaba.
>
>
> On Wed, 2005-10-26 at 14:23, Venki wrote:
> > Hi,
> > Can anyone in the list tell me how to get the value in the lastvalue
> > field of a sequence. I tried
> >
> > select currval('field_seq');
> >
> > but got an error message
> >
> > ERROR: currval of sequence "field_seq" is not yet defined in this
> > session.
> >
> > I think currval will work only after an insert. I don't want to use
> > nextval as this increases the lastvalue value.
> >
> >
> >
> > Thanks in advance
> >
> > Kind Regards,
> > Venki
> >
> >
> >
> >
> >
> >
>
>
>
>
>
On 10/26/05 8:42 AM, "Venki" <Venki@insoft.com> wrote: > > Hi > thanks for the replies. The situation is as follows. We get backups from the > production server and update the local database in the local server but each > time when we restore the database backup the sequence values are not getting > updated properly. So what i thought was to write a function which will check > the last value of the row in the table and compare it with the lastvalue of > the sequnce and if it is not proper then update the lastvalue of the sequence > to a proper value. > > I Hope that i have explained the situation properly. If you have any other > suggestion it will be greatly helpful as we are new to postgres and might be > doing something wrong when restoring the database. I may be wrong, but I thought that sequences were also dumped and restored with database dumps, unless you are dumping and restoring only data. Is that the case? Sean
On Wed, Oct 26, 2005 at 18:12:32 +0530, Venki <Venki@insoft.com> wrote: > Hi > thanks for the replies. The situation is as follows. We get backups from the > production server and update the local database in the local server but each > time when we restore the database backup the sequence values are not getting > updated properly. So what i thought was to write a function which will check > the last value of the row in the table and compare it with the lastvalue of > the sequnce and if it is not proper then update the lastvalue of the > sequence to a proper value. > > I Hope that i have explained the situation properly. If you have any other > suggestion it will be greatly helpful as we are new to postgres and might be > doing something wrong when restoring the database. If you are dumping and restoring by cluster or database, your sequences should be having their values set upon restore. If you are just restoring indvidual tables, than you need to adjust your process to also restore the sequence values.