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

On Sun, May 11, 2003 at 15:07:37 +1000,
Matthew Horoschun <mhoroschun(at)canprint(dot)com(dot)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.
>