Thread: Rules, views, sequences and returned values
Hello. Only a question.
I have a table and want to make a view updateable with rules. Table has a
sequence that creates default value to primary key when inserting.
And now the question: Is it posible to return value of primary key from
rule of inserting with NEW? Is that value what i would get from extern
calling to insert, instead of OID?.
Thanks and good day :)
David
> I have a table and want to make a view updateable with rules. Table has a
> sequence that creates default value to primary key when inserting.
>
> And now the question: Is it posible to return value of primary key from
> rule of inserting with NEW? Is that value what i would get from extern
> calling to insert, instead of OID?.
SELECT currval('seq-table-name_seq-field-name_seq');
where seq-table-name is the name of your table (not the view), and
seq-field-name is the name of the primary key.
Greg
On Fri, Mar 23, 2001 at 09:53:49AM -0500, Gregory Wood wrote:
> > I have a table and want to make a view updateable with rules. Table has a
> > sequence that creates default value to primary key when inserting.
> >
> > And now the question: Is it posible to return value of primary key from
> > rule of inserting with NEW? Is that value what i would get from extern
> > calling to insert, instead of OID?.
>
> SELECT currval('seq-table-name_seq-field-name_seq');
>
> where seq-table-name is the name of your table (not the view), and
> seq-field-name is the name of the primary key.
i'm sure there's a reason for this--
psql=> select currval('mytable_afield_seq') ;
ERROR: mytable_afield_seq.currval is not yet defined in this session
but i'll be darned if i can figure out what it is. (after one
call to "nextval()" currval() works fine, of course.) someone hit
me with a clue stick!
--
It is always hazardous to ask "Why?" in science, but it is often
interesting to do so just the same.
-- Isaac Asimov, 'The Genetic Code'
will@serensoft.com
http://newbieDoc.sourceforge.net/ -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
> > > I have a table and want to make a view updateable with rules. Table
has a
> > > sequence that creates default value to primary key when inserting.
> > >
> > > And now the question: Is it posible to return value of primary key
from
> > > rule of inserting with NEW? Is that value what i would get from
extern
> > > calling to insert, instead of OID?.
> >
> > SELECT currval('seq-table-name_seq-field-name_seq');
> >
> > where seq-table-name is the name of your table (not the view), and
> > seq-field-name is the name of the primary key.
>
> i'm sure there's a reason for this--
>
> psql=> select currval('mytable_afield_seq') ;
> ERROR: mytable_afield_seq.currval is not yet defined in this session
>
> but i'll be darned if i can figure out what it is. (after one
> call to "nextval()" currval() works fine, of course.) someone hit
> me with a clue stick!
currval() is the value last used by the backend, not by the database.
Meaning that you have to actually use the sequence before the backend has a
value to retrieve. That means either doing a nextval() or doing an INSERT
(which implicitly performs the nextval() ).
Greg
On Fri, Mar 23, 2001 at 12:23:35PM -0500, Gregory Wood wrote:
> > i'm sure there's a reason for this--
> >
> > psql=> select currval('mytable_afield_seq') ;
> > ERROR: mytable_afield_seq.currval is not yet defined in this session
> >
> > but i'll be darned if i can figure out what it is. (after one
> > call to "nextval()" currval() works fine, of course.) someone hit
> > me with a clue stick!
>
> currval() is the value last used by the backend, not by the database.
> Meaning that you have to actually use the sequence before the backend has a
> value to retrieve. That means either doing a nextval() or doing an INSERT
> (which implicitly performs the nextval() ).
<dense mode=on>
seems like the backend (server?) would know the current value of
a sequence, since it has to get it, then add one to it, to get
the next one in line. if george inserts a record, i'd think that
ringo would be able to see the current counter afterwards,
independent of session...
so i can't retrieve and use the existing current value for a
sequence unless i bump it one? me no grok.
</dense>
--
It is always hazardous to ask "Why?" in science, but it is often
interesting to do so just the same.
-- Isaac Asimov, 'The Genetic Code'
will@serensoft.com
http://newbieDoc.sourceforge.net/ -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
will trillich <will@serensoft.com> writes:
> On Fri, Mar 23, 2001 at 12:23:35PM -0500, Gregory Wood wrote:
> > > i'm sure there's a reason for this--
> > >
> > > psql=> select currval('mytable_afield_seq') ;
> > > ERROR: mytable_afield_seq.currval is not yet defined in this session
> > >
> > > but i'll be darned if i can figure out what it is. (after one
> > > call to "nextval()" currval() works fine, of course.) someone hit
> > > me with a clue stick!
> >
> > currval() is the value last used by the backend, not by the database.
> > Meaning that you have to actually use the sequence before the backend has a
> > value to retrieve. That means either doing a nextval() or doing an INSERT
> > (which implicitly performs the nextval() ).
>
> <dense mode=on>
> seems like the backend (server?) would know the current value of
> a sequence, since it has to get it, then add one to it, to get
> the next one in line. if george inserts a record, i'd think that
> ringo would be able to see the current counter afterwards,
> independent of session...
Remember there are (potentially) multiple backends, one per client
connection. Because of transaction isolation, there may be multiple
outstanding values of 'currval'. There's no way to know the "real"
current value in your transaction unless you do 'nextval' (which
atomically gets the next free value of the sequence).
Does this make sense? I probably didn't explain it very well.
-Doug
> > currval() is the value last used by the backend, not by the database. > > Meaning that you have to actually use the sequence before the backend has a > > value to retrieve. That means either doing a nextval() or doing an INSERT > > (which implicitly performs the nextval() ). > > <dense mode=on> > seems like the backend (server?) would know the current value of > a sequence, since it has to get it, then add one to it, to get > the next one in line. if george inserts a record, i'd think that > ringo would be able to see the current counter afterwards, > independent of session... Well, the problem is that currval() is the value last inserted by *that* backend into the database. The reasoning for this is simple: If Paul just inserted a record and wants to find out what sequence value was inserted, he would use currval(). If currval() returned the last sequence value regardless of who last inserted, and Yoko inserted between Paul's INSERT and currval(), then Paul would get Yoko's currval(), not the one he just inserted. This means that if he uses that value to insert values into another table that are foreign keyed into the original table, he'll be connecting them to the wrong table. > so i can't retrieve and use the existing current value for a > sequence unless i bump it one? me no grok. > </dense> The idea is to retrieve the value after it has been used. If you grab the currval() and someone else grabs a currval() before you use it... bad things happen. Better to do the insert and see what value you ended up with. If you really want to grab a sequence value, just do nextval() and then you can use that value explicitly for an insert or whatnot. And don't feel bad, took me a while to figure all this out.
On Fri, Mar 23, 2001 at 04:11:56PM -0600, will trillich wrote:
> On Fri, Mar 23, 2001 at 12:23:35PM -0500, Gregory Wood wrote:
> > > i'm sure there's a reason for this--
> > >
> > > psql=> select currval('mytable_afield_seq') ;
> > > ERROR: mytable_afield_seq.currval is not yet defined in this session
> > >
> > > but i'll be darned if i can figure out what it is. (after one
> > > call to "nextval()" currval() works fine, of course.) someone hit
> > > me with a clue stick!
> >
> > currval() is the value last used by the backend, not by the database.
> > Meaning that you have to actually use the sequence before the backend has a
> > value to retrieve. That means either doing a nextval() or doing an INSERT
> > (which implicitly performs the nextval() ).
>
> <dense mode=on>
> seems like the backend (server?) would know the current value of
> a sequence, since it has to get it, then add one to it, to get
> the next one in line. if george inserts a record, i'd think that
> ringo would be able to see the current counter afterwards,
> independent of session...
>
> so i can't retrieve and use the existing current value for a
> sequence unless i bump it one? me no grok.
> </dense>
Hmm, I wonder if the currval() function could just do a
SELECT last_value FROM <sequence>;
if the nextval() has yet set the currval() context. I suppose there
could be problems with that approach.
Why would you want to get the last value of the sequence if you haven't
used it yet? Seems there'd be no context from a previous use of
nextval(), so what would you use the value for?
--
Eric G. Miller <egm2@jps.net>
On Fri, 23 Mar 2001, will trillich wrote:
> On Fri, Mar 23, 2001 at 12:23:35PM -0500, Gregory Wood wrote:
> > > i'm sure there's a reason for this--
> > >
> > > psql=> select currval('mytable_afield_seq') ;
> > > ERROR: mytable_afield_seq.currval is not yet defined in this session
> > >
> > > but i'll be darned if i can figure out what it is. (after one
> > > call to "nextval()" currval() works fine, of course.) someone hit
> > > me with a clue stick!
> >
> > currval() is the value last used by the backend, not by the database.
> > Meaning that you have to actually use the sequence before the backend has a
> > value to retrieve. That means either doing a nextval() or doing an INSERT
> > (which implicitly performs the nextval() ).
>
> <dense mode=on>
> seems like the backend (server?) would know the current value of
> a sequence, since it has to get it, then add one to it, to get
> the next one in line. if george inserts a record, i'd think that
> ringo would be able to see the current counter afterwards,
> independent of session...
But, that value can't be guaranteed to be meaningful for other
than the instant that you got it. One instant later you don't
know it's meaningful (ie, still the current value of the sequence).
What would ringo do with that information anyway? Conversely,
what about if I've inserted something and then someone else did
as well? I want to be able to get the value the sequence gave
me. The point of the sequence is to be able get a unique value for
inserting and to be able to retrieve that value, not that the
sequences always succeed on that (rules and triggers can screw
that up), but it's close.
"Gregory Wood" <gregw@com-stock.com> writes:
>> so i can't retrieve and use the existing current value for a
>> sequence unless i bump it one? me no grok.
>> </dense>
> The idea is to retrieve the value after it has been used. If you grab the
> currval() and someone else grabs a currval() before you use it... bad things
> happen. Better to do the insert and see what value you ended up with. If you
> really want to grab a sequence value, just do nextval() and then you can use
> that value explicitly for an insert or whatnot.
If you do want to know the last value assigned by *anyone*, you can do
select last_value from SEQUENCE;
Note however that this value may very well be obsolete milliseconds
after you read it. It should certainly not be presumed to have anything
to do with the last nextval() value your own process assigned, nor with
the next nextval() you will assign in the future. Offhand I can't see
that it has any real usefulness: if you think you need it, you are
likely doing things wrong.
regards, tom lane
On Fri, Mar 23, 2001 at 05:28:24PM -0500, Gregory Wood wrote:
> Well, the problem is that currval() is the value last inserted by *that*
> backend into the database. The reasoning for this is simple:
okay ... :)
> If Paul just inserted a record and wants to find out what sequence value was
> inserted, he would use currval(). If currval() returned the last sequence
> value regardless of who last inserted, and Yoko inserted between Paul's
> INSERT and currval(), then Paul would get Yoko's currval(), not the one he
> just inserted. This means that if he uses that value to insert values into
> another table that are foreign keyed into the original table, he'll be
> connecting them to the wrong table.
<dawn arrive=now> aha. grok. </dawn>
> > so i can't retrieve and use the existing current value for a
> > sequence unless i bump it one? me no grok.
> > </dense>
>
> The idea is to retrieve the value after it has been used. If you grab the
> currval() and someone else grabs a currval() before you use it... bad things
> happen. Better to do the insert and see what value you ended up with. If you
> really want to grab a sequence value, just do nextval() and then you can use
> that value explicitly for an insert or whatnot.
>
> And don't feel bad, took me a while to figure all this out.
thanks for the steerage! i'll catch up eventually (but by then
everyone will have moved on...)
--
It is always hazardous to ask "Why?" in science, but it is often
interesting to do so just the same.
-- Isaac Asimov, 'The Genetic Code'
will@serensoft.com
http://newbieDoc.sourceforge.net/ -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
Greetings everyone, Does anyone know if a reputable source has done market share studies with the various SQL servers out there in enterprise Web settings? I'm trying to convince a company to have support for more than Microsoft and Oracle servers so I am looking for some numbers to back me up :-) If anyone can help me with this, I would appreciate it. I don't care too much if it includes all of the open-source SQL servers out there, just something to open their eyes. Thanks Dan Harrington