Thread: lastval(seq) ?
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
On Mon, 20 Oct 2003 21:52:17 -0700 (PDT)
CSN <cool_screen_name90001@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 <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/
On Tuesday 21 October 2003 13:10, Jeff wrote:
> On Mon, 20 Oct 2003 21:52:17 -0700 (PDT)
>
> CSN <cool_screen_name90001@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
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.
--- "scott.marlowe" <scott.marlowe@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
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).
On Tue, 21 Oct 2003, CSN wrote:
>
> --- "scott.marlowe" <scott.marlowe@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.
cool_screen_name90001@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
(416) 646 3304 x124 (land)