Re: lastval(seq) ?

From: Christopher Browne <cbbrowne(at)libertyrms(dot)info>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: lastval(seq) ?
Date: 2003-10-21 21:14:18
Message-ID: 60smlmjnth.fsf@dev6.int.libertyrms.info
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

cool_screen_name90001(at)yahoo(dot)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)

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Jeff Eckermann 2003-10-21 22:03:56 Re: plpgsql: return multiple result sets or temp table
Previous Message scott.marlowe 2003-10-21 20:43:15 Re: lastval(seq) ?