pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedKnowing when it is safe to call currval()
4+ messages / 2 participants
[nested] [flat]
* Knowing when it is safe to call currval()
@ 2003-05-11 04:11 Matthew Horoschun <mhoroschun@canprint.com.au>
0 siblings, 1 reply; 4+ messages in thread
From: Matthew Horoschun @ 2003-05-11 04:11 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Knowing when it is safe to call currval()
@ 2003-05-11 04:41 Bruno Wolff III <bruno@wolff.to>
parent: Matthew Horoschun <mhoroschun@canprint.com.au>
0 siblings, 1 reply; 4+ messages in thread
From: Bruno Wolff III @ 2003-05-11 04:41 UTC (permalink / raw)
To: Matthew Horoschun <mhoroschun@canprint.com.au>; +Cc: pgsql-sql
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?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Knowing when it is safe to call currval()
@ 2003-05-11 13:35 Bruno Wolff III <bruno@wolff.to>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 4+ messages in thread
From: Bruno Wolff III @ 2003-05-11 13:35 UTC (permalink / raw)
To: Matthew Horoschun <mhoroschun@canprint.com.au>; +Cc: pgsql-sql
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.
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Knowing when it is safe to call currval()
@ 2003-05-11 14:28 Matthew Horoschun <mhoroschun@canprint.com.au>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 4+ messages in thread
From: Matthew Horoschun @ 2003-05-11 14:28 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-sql
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 current a_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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-05-11 14:28 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-11 04:11 Knowing when it is safe to call currval() Matthew Horoschun <mhoroschun@canprint.com.au>
2003-05-11 04:41 ` Bruno Wolff III <bruno@wolff.to>
2003-05-11 13:35 ` Bruno Wolff III <bruno@wolff.to>
2003-05-11 14:28 ` Matthew Horoschun <mhoroschun@canprint.com.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox