lastval(seq) ?

[CSN]

How do you get the last value of a sequence without
having called nextval? phppgadmin displays last value
for sequences (I haven't found out how yet rooting
through the code).

CSN

__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Jeff]

On Mon, 20 Oct 2003 21:52:17 -0700 (PDT)
CSN <co*******************@yahoo.com> wrote:

How do you get the last value of a sequence without
having called nextval? phppgadmin displays last value
for sequences (I haven't found out how yet rooting
through the code).

CSN

select curval('my_sequence')
--
Jeff Trout <je**@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Richard Huxton]

On Tuesday 21 October 2003 13:10, Jeff wrote:

On Mon, 20 Oct 2003 21:52:17 -0700 (PDT)

CSN <co*******************@yahoo.com> wrote:

How do you get the last value of a sequence without
having called nextval? phppgadmin displays last value
for sequences (I haven't found out how yet rooting
through the code).

CSN

select curval('my_sequence')

Only works if you've called nextval() first.

I'm guessing phppgadmin uses SELECT * FROM my_seq_name

What were you planning to use this value for?

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[scott.marlowe]

On Mon, 20 Oct 2003, CSN wrote:

How do you get the last value of a sequence without
having called nextval? phppgadmin displays last value
for sequences (I haven't found out how yet rooting
through the code).

First, the mandatory, why would you want to do that?

Second: select last_value from table_id_seq;

It's not transaction safe, so don't expect it to work right on busy
multi-user systems.
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[CSN]

--- "scott.marlowe" <sc***********@ihs.com> wrote:

On Mon, 20 Oct 2003, CSN wrote:

How do you get the last value of a sequence without

having called nextval? phppgadmin displays last

value

for sequences (I haven't found out how yet rooting
through the code).

First, the mandatory, why would you want to do that?

For updating sequences after copy importing data (with
id's).

select setval('table_id_seq', (select max(id) from
table));

vs.

select setval('table_id_seq', (select last_value from
table_id_seq));

Is there a transaction-safe way?

CSN



Second: select last_value from table_id_seq;

It's not transaction safe, so don't expect it to
work right on busy
multi-user systems.

__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Alvaro Herrera Munoz]

On Tue, Oct 21, 2003 at 12:49:39PM -0700, CSN wrote:

For updating sequences after copy importing data (with
id's).

Is there a transaction-safe way?

Sure:

begin;
lock table table;
select setval('table_id_seq', (select max(id) from table));
commit;

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"El sabio habla porque tiene algo que decir;
el tonto, porque tiene que decir algo" (Platon).

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[scott.marlowe]

On Tue, 21 Oct 2003, CSN wrote:

--- "scott.marlowe" <sc***********@ihs.com> wrote:

On Mon, 20 Oct 2003, CSN wrote:

How do you get the last value of a sequence

without

having called nextval? phppgadmin displays last

value

for sequences (I haven't found out how yet rooting
through the code).

First, the mandatory, why would you want to do that?

For updating sequences after copy importing data (with
id's).

select setval('table_id_seq', (select max(id) from
table));

vs.

select setval('table_id_seq', (select last_value from
table_id_seq));

Is there a transaction-safe way?

Not sure. I don't think so.

That second one won't work, or at least shouldn't do much useful. I.e.
you're setting table_id_seq to be equal to itself.

The first one, the select max(id) one, is the standard way of doing this,
if you're afraid users might be diddling the data while you're trying to
import it, you can always set pg_hba.conf to only let you log in from
local or something and do it there. But mostly if the copy command and
the select setval are in a bacth file it should all happen fast enough to
escape notice by the users until it's already loaded and set.
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Christopher Browne]

co*******************@yahoo.com (CSN) writes:

For updating sequences after copy importing data (with
id's).

select setval('table_id_seq', (select max(id) from
table));

vs.

select setval('table_id_seq', (select last_value from
table_id_seq));

Is there a transaction-safe way?

There's not likely to be.

For any given potential value of currval('table_id_seq'), it is always
possible that a transaction could be held open that is using that
value.

The only really _safe_ way to reset sequences is to do so when there
are no transactions active on the system.

In practice, we have to live with that potential for lack of safety,
and I would be inclined to set the value to the maximum visible value
plus some reasonable constant, say 1000, on the assumption that unless
someone is trying to do something actively pathologically bad, that
should be "good enough."

But my preference would be to do so with applications that might be
doing potentially-evil things SHUT DOWN.
--
output = ("cbbrowne" "@" "libertyrms.info")
<http://dev6.int.libertyrms.com/>
Christopher Browne
[phone] x124 (land)