pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsystem table storing sequence attributes
9+ messages / 4 participants
[nested] [flat]
* system table storing sequence attributes
@ 2007-05-22 07:03 Sabin Coanda <sabin.coanda@deuromedia.ro>
0 siblings, 1 reply; 9+ messages in thread
From: Sabin Coanda @ 2007-05-22 07:03 UTC (permalink / raw)
To: pgsql-sql
Hi there,
I'd like to read the global sequence attribute "currval", but not using
currval function, because it is session dependent and requires a nextval
function to be used before.
Do you know where is stored this value in the system tables ?
TIA,
Sabin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: system table storing sequence attributes
@ 2007-05-22 07:43 Marcin Stępnicki <mstepnicki@gmail.com>
parent: Sabin Coanda <sabin.coanda@deuromedia.ro>
0 siblings, 1 reply; 9+ messages in thread
From: Marcin Stępnicki @ 2007-05-22 07:43 UTC (permalink / raw)
To: pgsql-sql
Dnia Tue, 22 May 2007 10:03:28 +0300, Sabin Coanda napisał(a):
> Hi there,
>
> I'd like to read the global sequence attribute "currval", but not using
> currval function, because it is session dependent and requires a nextval
> function to be used before.
>
> Do you know where is stored this value in the system tables ?
select last_value from sequence_name
--
| And Do What You Will be the challenge | http://apcoln.linuxpl.org
| So be it in love that harms none | http://biznes.linux.pl
| For this is the only commandment. | http://www.juanperon.info
`---* JID: Aragorn_Vime@jabber.org *---' http://www.naszedzieci.org
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: system table storing sequence attributes
@ 2007-05-22 12:23 Sabin Coanda <sabin.coanda@deuromedia.ro>
parent: Marcin Stępnicki <mstepnicki@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Sabin Coanda @ 2007-05-22 12:23 UTC (permalink / raw)
To: pgsql-sql
...
> select last_value from sequence_name
Unfortunately there is the same problem. The documentation say: "It is an
error to call lastval if nextval has not yet been called in the current
session." .... and I don't want to call nextval before.
Sabin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: system table storing sequence attributes
@ 2007-05-22 12:38 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Sabin Coanda <sabin.coanda@deuromedia.ro>
1 sibling, 0 replies; 9+ messages in thread
From: A. Kretschmer @ 2007-05-22 12:38 UTC (permalink / raw)
To: pgsql-sql
am Tue, dem 22.05.2007, um 15:23:44 +0300 mailte Sabin Coanda folgendes:
> ...
> > select last_value from sequence_name
>
> Unfortunately there is the same problem. The documentation say: "It is an
> error to call lastval if nextval has not yet been called in the current
> session." .... and I don't want to call nextval before.
No, you can select last_value from a sequence.
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
test=# select last_value from t2_seq;
last_value
------------
3
(1 row)
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: system table storing sequence attributes
@ 2007-05-22 12:41 Marcin Stępnicki <mstepnicki@gmail.com>
parent: Sabin Coanda <sabin.coanda@deuromedia.ro>
1 sibling, 1 reply; 9+ messages in thread
From: Marcin Stępnicki @ 2007-05-22 12:41 UTC (permalink / raw)
To: pgsql-sql
Dnia Tue, 22 May 2007 15:23:44 +0300, Sabin Coanda napisał(a):
> ...
>> select last_value from sequence_name
>
> Unfortunately there is the same problem. The documentation say: "It is an
> error to call lastval if nextval has not yet been called in the current
> session." .... and I don't want to call nextval before.
I think that you either misunderstood this statement or try to break your
application in a nasty way ;). Please tell us more about your problem
and/or what do you want to achive, because sequences behave this way for a
reason (concurrency issues). Perhaps there is another solution.
--
| And Do What You Will be the challenge | http://apcoln.linuxpl.org
| So be it in love that harms none | http://biznes.linux.pl
| For this is the only commandment. | http://www.juanperon.info
`---* JID: Aragorn_Vime@jabber.org *---' http://www.naszedzieci.org
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: system table storing sequence attributes
@ 2007-05-22 13:12 Sabin Coanda <sabin.coanda@deuromedia.ro>
parent: Marcin Stępnicki <mstepnicki@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Sabin Coanda @ 2007-05-22 13:12 UTC (permalink / raw)
To: pgsql-sql
""Marcin Stêpnicki"" <mstepnicki@gmail.com> wrote in message
news:pan.2007.05.22.12.41.38.862364@gmail.com...
...
>
> I think that you either misunderstood this statement or try to break your
> application in a nasty way ;). Please tell us more about your problem
> and/or what do you want to achive, because sequences behave this way for a
> reason (concurrency issues). Perhaps there is another solution.
I have a table with a serial primary key aoto generated by a sequence. I
add/remove records. At a moment I'd like to know what is the current value
of the sequence. I don't wish to know this in the same session where I
add/remove records.
My Postgresql version is "PostgreSQL 8.2.3 on i686-pc-mingw32, compiled by
GCC gcc.exe (GCC) 3.4.2 (mingw-special)", on Windows XP OS, and I use
pgAdmin to open sessions on my database.
With this environment, try the following scenario:
Make a demo table:
CREATE TABLE "tbFoo"
(
"ID" integer NOT NULL DEFAULT nextval('"tbFoo_ID_seq"'::regclass)
)
At the beginning, no record are inserted in the table. I call:
SELECT currval( pg_get_serial_sequence('"tbFoo"','ID') );
This rise the following error: ERROR: currval of sequence "tbFoo_ID_seq" is
not yet defined in this session
SQL state: 55000
Then I add a record there:
INSERT INTO "tbFoo" DEFAULT VALUES;
I call SELECT currval( pg_get_serial_sequence('"tbFoo"','ID') ), and I get
1. That's ok because I just use indirectly a nextval to that sequence in the
insert process, on this session.
I close the session, and I open another one.
I call SELECT currval( pg_get_serial_sequence('"tbFoo"','ID') ), and I get
the error again:
ERROR: currval of sequence "tbFoo_ID_seq" is not yet defined in this session
SQL state: 55000
Sabin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: system table storing sequence attributes
@ 2007-05-22 13:22 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Sabin Coanda <sabin.coanda@deuromedia.ro>
1 sibling, 1 reply; 9+ messages in thread
From: A. Kretschmer @ 2007-05-22 13:22 UTC (permalink / raw)
To: pgsql-sql
am Tue, dem 22.05.2007, um 16:12:06 +0300 mailte Sabin Coanda folgendes:
> I close the session, and I open another one.
>
> I call SELECT currval( pg_get_serial_sequence('"tbFoo"','ID') ), and I get
> the error again:
> ERROR: currval of sequence "tbFoo_ID_seq" is not yet defined in this session
> SQL state: 55000
Try 'select last_value from "tbFoo_ID_seq";' instead.
A. Kretschmer
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: system table storing sequence attributes
@ 2007-05-22 13:47 Sabin Coanda <sabin.coanda@deuromedia.ro>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 0 replies; 9+ messages in thread
From: Sabin Coanda @ 2007-05-22 13:47 UTC (permalink / raw)
To: pgsql-sql
""A. Kretschmer"" <andreas.kretschmer@schollglas.com> wrote in message
news:20070522132243.GI32759@a-kretschmer.de...
...
>
> Try 'select last_value from "tbFoo_ID_seq";' instead.
>
It works. Thanks a lot !
Sabin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: system table storing sequence attributes
@ 2007-05-22 14:01 Milen A. Radev <milen@radev.net>
parent: Sabin Coanda <sabin.coanda@deuromedia.ro>
1 sibling, 0 replies; 9+ messages in thread
From: Milen A. Radev @ 2007-05-22 14:01 UTC (permalink / raw)
To: pgsql-sql; +Cc: pgsql-sql
Sabin Coanda написа:
> ""Marcin Stкpnicki"" <mstepnicki@gmail.com> wrote in message
> news:pan.2007.05.22.12.41.38.862364@gmail.com...
> ...
>> I think that you either misunderstood this statement or try to break your
>> application in a nasty way ;). Please tell us more about your problem
>> and/or what do you want to achive, because sequences behave this way for a
>> reason (concurrency issues). Perhaps there is another solution.
>
> I have a table with a serial primary key aoto generated by a sequence. I
> add/remove records. At a moment I'd like to know what is the current value
> of the sequence. I don't wish to know this in the same session where I
> add/remove records.
Why do you need to know that? I can't think of any reason.
[...]
--
Milen A. Radev
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2007-05-22 14:01 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-22 07:03 system table storing sequence attributes Sabin Coanda <sabin.coanda@deuromedia.ro>
2007-05-22 07:43 ` Marcin Stępnicki <mstepnicki@gmail.com>
2007-05-22 12:23 ` Sabin Coanda <sabin.coanda@deuromedia.ro>
2007-05-22 12:38 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-05-22 12:41 ` Marcin Stępnicki <mstepnicki@gmail.com>
2007-05-22 13:12 ` Sabin Coanda <sabin.coanda@deuromedia.ro>
2007-05-22 13:22 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-05-22 13:47 ` Sabin Coanda <sabin.coanda@deuromedia.ro>
2007-05-22 14:01 ` Milen A. Radev <milen@radev.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox