Thread: Knowing when it is safe to call currval()

Hi All,

Is there any way to know ahead of time whether calling currval() on a 
sequence will cause an exception like:

ERROR:  some_id_seq.currval is not yet defined in this session

In my function I can't be sure whether nextval() will have previously 
been called in the session.

Thanks

Matthew.

On Sun, May 11, 2003 at 14:11:01 +1000, Matthew Horoschun <mhoroschun@canprint.com.au> wrote:
> Hi All,
> 
> Is there any way to know ahead of time whether calling currval() on a 
> sequence will cause an exception like:
> 
> ERROR:  some_id_seq.currval is not yet defined in this session
> 
> In my function I can't be sure whether nextval() will have previously 
> been called in the session.

Well you could just call nextval at the start of the session to make sure
it has a value.

Howvere it is odd that you would want to call currval when there may not
have already been a call to nextval. What are you doing with the returned
value?

On Sun, May 11, 2003 at 15:07:37 +1000, Matthew Horoschun <mhoroschun@canprint.com.au> wrote:
> Hi Bruno,
> 
> Thanks for the reply.

It is best to keep these discussions copied back to the list so that other
people can help and learn.

> 
> >Well you could just call nextval at the start of the session to make 
> >sure
> >it has a value.
> >
> >Howvere it is odd that you would want to call currval when there may 
> >not
> >have already been a call to nextval. What are you doing with the 
> >returned
> >value?
> 
> I have a bunch of SECURITY DEFINER functions that are called from 
> TRIGGERS which force the primary key of tables to the next value in a 
> sequence. Normal users can't call nextval() or setval() on any 
> sequences. The triggers basically do:

One way out of this using a different approach might be to make security
definer functions that will let normal users call nextval for the
sequences. You could then use these as the default for the appropiate
columns.

Along the way you are doing things, instead of checking to see if
the row is not equal to currval, perhaps check to see if it is null.
Then you could supply a value of null when you want it to be rewritten.

> 
> NEW.a_id := nextval( ''a_id_seq'' );
> 
> This normally works fine. However, I have two of these tables that have 
> foreign key constraints on each other.
> 
> CREATE TABLE a (
>     a_id            BIGINT NOT NULL,
>     default_b_id    BIGINT NOT NULL REFERENCES b ( b_id ) DEFERRABLE
> );
> 
> CREATE TABLE b (
>     b_id            BIGINT NOT NULL,
>     owning_a_id    BIGINT NOT NULL REFERENCES a ( a_id ) DEFERRABLE
> );
> 
> This situation is normally not a problem because I can defer RI checks 
> until the end of the transaction (this is in a SECURITY DEFINER 
> function):
> 
> BEGIN;
>     SET CONSTRAINTS ALL DEFERRED;
>     INSERT INTO a VALUES ( NULL, nextval( 'b_id_seq' ) );
>     INSERT INTO b VALUES ( currval( 'b_id_seq' ), currval( 'a_id_seq' ) 
>     );
> COMMIT;
> 
> But because of the TRIGGERS, the currval( 'b_id_seq' ) will be ignored. 
> Hence, I was planning on altering my TRIGGERS so that they do something 
> like:
> 
> IF NEW.a_id <> currval( ''a_id_seq'' ) THEN
>     NEW.a_id := nextval( ''a_id_seq'' );
> END IF;
> 
> Now, I admit this isn't all that elegant, but it is the only solution 
> I've been able to come up with so far.
> 
> Any ideas?
> 
> Thanks
> 
> Matthew.
> 

Hi Bruno,

Thanks for your assistance.

>> I have a bunch of SECURITY DEFINER functions that are called from
>> TRIGGERS which force the primary key of tables to the next value in a
>> sequence. Normal users can't call nextval() or setval() on any
>> sequences. The triggers basically do:
>
> One way out of this using a different approach might be to make 
> security
> definer functions that will let normal users call nextval for the
> sequences. You could then use these as the default for the appropiate
> columns.
>
> Along the way you are doing things, instead of checking to see if
> the row is not equal to currval, perhaps check to see if it is null.
> Then you could supply a value of null when you want it to be rewritten.
>

I've decided to do something similar to this. I'm now only doing the 
currval() check if the user-supplied value is not null.

IF NEW.a_id IS NOT NULL THEN    IF NEW.a_id <> currval( 'a_id_seq' ) THEN    RAISE EXCEPTION 'a_id must be NULL or the
currenta_id_seq value.';    END IF;
 
ELSE    NEW.a_id := nextval( 'a_id_seq'::text );
END IF;

It is a bit of a kludge, but I painted myself into the corner in the 
first place!

Cheers

Matthew.