Обсуждение: GETTING INFO FROM SEQUENCE OBJECTS
How do I use the functions to get data from sequence objects?
For example, I need to know what is the value of a serial field after inserting a new record; the one assigned to the record just inserted.
I have tried the psql interfase with the following:
select currval('restaurant.ordenes_clave_seq');
but I get a message telling me that "currval of sequence "ordenes_clave_seq" is not yet defined in this session.
Please advice.
Respectfully,
Jorge Maldonado
2009/11/2 JORGE MALDONADO <jorgemal1960@gmail.com>:
> How do I use the functions to get data from sequence objects?
> For example, I need to know what is the value of a serial field after
> inserting a new record; the one assigned to the record just inserted.
>
> I have tried the psql interfase with the following:
> select currval('restaurant.ordenes_clave_seq');
> but I get a message telling me that "currval of sequence "ordenes_clave_seq"
> is not yet defined in this session.
Thats a "per session" function request...
however, if you describe the sequence, you'll see something like this:
\d dt_messages_sequence
Sequence "public.dt_messages_sequence"
Column | Type
---------------+---------
sequence_name | name
last_value | bigint
increment_by | bigint
max_value | bigint
min_value | bigint
cache_value | bigint
log_cnt | bigint
is_cycled | boolean
is_called | boolean
and then you can get the current value:
trackerData=> select last_value from dt_messages_sequence;
last_value
------------
1208
(1 row)
... however, if the sequence is being used in the session, then you
must use currval rather than the select I described above... because
it is thread-safe. This mechanism I described above may give you
something less than the current value if the sequence is in use.
>
> Please advice.
>
> Respectfully,
> Jorge Maldonado
--
Brian Modra Land line: +27 23 5411 462
Mobile: +27 79 69 77 082
5 Jan Louw Str, Prince Albert, 6930
Postal: P.O. Box 2, Prince Albert 6930
South Africa
http://www.zwartberg.com/
In response to JORGE MALDONADO :
> How do I use the functions to get data from sequence objects?
> For example, I need to know what is the value of a serial field after inserting
> a new record; the one assigned to the record just inserted.
>
> I have tried the psql interfase with the following:
> select currval('restaurant.ordenes_clave_seq');
> but I get a message telling me that "currval of sequence "ordenes_clave_seq" is
> not yet defined in this session.
You have to use nextval() first, without nextval() the current value of
the sequence are undefined in this session.
In other words: first insert your new row into the table, after that
call currval().
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
A client of mine hade a HD crash, he had a backup of his pgsql/data catalog, but when trying to start the server (on a newly installed postgres, same version he had) I get the following message: postgres$ FATAL: database files are incompatible with server DETAIL: The database cluster was initialized with PG_CONTROL_VERSION 906166272, but the server was compiled with PG_CONTROL_VERSION 822. HINT: It looks like you need to initdb. Is it beyond repair ? Any last resorts ? Regards, Lars.
How did you get the data onto the new server? On Tue, Nov 3, 2009 at 10:00 AM, Lars Gustafsson <gumse@mac.com> wrote: > A client of mine hade a HD crash, he had a backup of his pgsql/data catalog, > but when trying to start the server (on a newly installed postgres, same > version he had) I get the following message: > > postgres$ FATAL: database files are incompatible with server > DETAIL: The database cluster was initialized with PG_CONTROL_VERSION > 906166272, but the server was compiled with PG_CONTROL_VERSION 822. > HINT: It looks like you need to initdb. > > > Is it beyond repair ? Any last resorts ? > -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ L. Friedman netllama@gmail.com LlamaLand https://netllama.linux-sxs.org
He had the /data directory on a USB memory Lars. 3 nov 2009 kl. 19.24 skrev Lonni J Friedman: > How did you get the data onto the new server? > > On Tue, Nov 3, 2009 at 10:00 AM, Lars Gustafsson <gumse@mac.com> > wrote: >> A client of mine hade a HD crash, he had a backup of his pgsql/data >> catalog, >> but when trying to start the server (on a newly installed postgres, >> same >> version he had) I get the following message: >> >> postgres$ FATAL: database files are incompatible with server >> DETAIL: The database cluster was initialized with PG_CONTROL_VERSION >> 906166272, but the server was compiled with PG_CONTROL_VERSION 822. >> HINT: It looks like you need to initdb. >> >> >> Is it beyond repair ? Any last resorts ? >> > > -- > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > L. Friedman netllama@gmail.com > LlamaLand https://netllama.linux-sxs.org
That's never a recommended way to move data. You need to take the dump and import it into the new instance. On Tue, Nov 3, 2009 at 10:30 AM, Lars Gustafsson <gumse@mac.com> wrote: > He had the /data directory on a USB memory > > Lars. > > > 3 nov 2009 kl. 19.24 skrev Lonni J Friedman: > >> How did you get the data onto the new server? >> >> On Tue, Nov 3, 2009 at 10:00 AM, Lars Gustafsson <gumse@mac.com> wrote: >>> >>> A client of mine hade a HD crash, he had a backup of his pgsql/data >>> catalog, >>> but when trying to start the server (on a newly installed postgres, same >>> version he had) I get the following message: >>> >>> postgres$ FATAL: database files are incompatible with server >>> DETAIL: The database cluster was initialized with PG_CONTROL_VERSION >>> 906166272, but the server was compiled with PG_CONTROL_VERSION 822. >>> HINT: It looks like you need to initdb. >>> >>> >>> Is it beyond repair ? Any last resorts ? -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ L. Friedman netllama@gmail.com LlamaLand https://netllama.linux-sxs.org
2009/11/3 Lars Gustafsson <gumse@mac.com>: > A client of mine hade a HD crash, he had a backup of his pgsql/data catalog, > but when trying to start the server (on a newly installed postgres, same > version he had) I get the following message: > > postgres$ FATAL: database files are incompatible with server > DETAIL: The database cluster was initialized with PG_CONTROL_VERSION > 906166272, but the server was compiled with PG_CONTROL_VERSION 822. > HINT: It looks like you need to initdb. > > > Is it beyond repair ? Any last resorts ? This might not be it, but make sure you're using a 32 bit version of Postgres if the old server was 32 bits, and a 64 bit version if the old server was 64 bit. -- Michael Wood <esiotrot@gmail.com>
On Tue, Nov 3, 2009 at 6:38 PM, Michael Wood <esiotrot@gmail.com> wrote: > 2009/11/3 Lars Gustafsson <gumse@mac.com>: >> A client of mine hade a HD crash, he had a backup of his pgsql/data catalog, >> but when trying to start the server (on a newly installed postgres, same >> version he had) I get the following message: >> >> postgres$ FATAL: database files are incompatible with server >> DETAIL: The database cluster was initialized with PG_CONTROL_VERSION >> 906166272, but the server was compiled with PG_CONTROL_VERSION 822. >> HINT: It looks like you need to initdb. >> >> >> Is it beyond repair ? Any last resorts ? > > This might not be it, but make sure you're using a 32 bit version of > Postgres if the old server was 32 bits, and a 64 bit version if the > old server was 64 bit. Close. In this case he's switching from a little-endian machine to big-endian or vice versa. 906166272 is 0x36030000 and 822 is 0x00000336. Notice that the bytes are reversed. I think later versions check for this and print a more specific error message. The database is probably fine but the on-disk files are architecture-specific. You must put it on a machine with a very similar architecture. -- greg