Re: Locking of auto generated sequence

Hi All,

Is there a way to stop a auto generated sequence from incrementing for some time and then activate it to do the increments.

Regards

---------------------------------
Do you Yahoo!?
Yahoo! Search presents - Jib Jab's 'Second Term'

sid tow wrote:
> Hi All,
>
> Is there a way to stop a auto generated sequence from incrementing
> for some time and then activate it to do the increments.

You might be able to do something by revoking permissions to the
sequence, and wrapping nextval/currval with security=owner functions. If
you can describe what you're trying to achieve and why that might help.

--
Richard Huxton
Archonet Ltd

I am using a 7.2 version of postgreSQL and here if I have to copy data in a particular table then I cannot specify the columns I want to update, but rather i will have to copy data into all the columns present. Now the problem is that I have a auto generated sequence starting from 1 in one of the columns. If I try to copy the data into this column (numerically incrementing numbers ie same as the auto seq) also then i cannot get the last value of the sequence number which I can get by
select last_value from seq;.
But if I insert data in the columns other than this one then the sequence auto increments and I get the value in my next run. Can you tell me why is it happening this way and is there any solution so that i use only copy commands to update the data.

Richard Huxton <dev(at)archonet(dot)com> wrote:
sid tow wrote:
> Hi All,
>
> Is there a way to stop a auto generated sequence from incrementing
> for some time and then activate it to do the increments.

You might be able to do something by revoking permissions to the
sequence, and wrapping nextval/currval with security=owner functions. If
you can describe what you're trying to achieve and why that might help.

--
Richard Huxton
Archonet Ltd

---------------------------------
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.

sid tow wrote:
> I am using a 7.2 version of postgreSQL and here if I have to copy
> data in a particular table then I cannot specify the columns I want
> to update, but rather i will have to copy data into all the columns
> present. Now the problem is that I have a auto generated sequence
> starting from 1 in one of the columns. If I try to copy the data into
> this column (numerically incrementing numbers ie same as the auto
> seq) also then i cannot get the last value of the sequence number
> which I can get by select last_value from seq;.

That's because the sequence value hasn't been updated. If you're
supplying your own numbers you should do something like:

BEGIN;
LOCK my_table IN EXCLUSIVE MODE;
SELECT setval('my_sequence_name', max(my_id_column)) FROM my_table;
COMMIT;

> But if I insert data
> in the columns other than this one then the sequence auto increments
> and I get the value in my next run. Can you tell me why is it
> happening this way and is there any solution so that i use only copy
> commands to update the data.

The sequence gets incremented when the column has no value and its
DEFAULT gets used. The default is nextval() which increments the
sequence and returns its value.

HTH
--
Richard Huxton
Archonet Ltd