johnf wrote:
> On Thursday 23 August 2007 14:47, Uwe Grauer wrote:
>> If this is a isolation level problem, you might be right with your guess.
>> Someone with a installed postgresql db should do a test to find out.
>> These kind of things might work quite differently between the various
>> database implementations.
>
> Uwe, I use Postgres daily. What was Ed's guess? All the forms we have were
> working correctly until Ed made this major change. Now none of forms that
> have children work. The isolation level has not changed. In fact nothing
> has changed as far as the DB is concerned.
>
> Uwe how does firebird work with respect to children? Do you have forms that
> use children? Assuming you have forms that have children - how are you
> dealing with FK's for the children?
AFAICS the current method for retrieving the last insert id for
postgresql is somehow strange.
There are two better ones from the postgresql faq:
4.11.2) How do I get the value of a SERIAL insert?
One approach is to retrieve the next SERIAL value from the sequence
object with the nextval() function before inserting and then insert it
explicitly. Using the example table in 4.11.1, an example in a
pseudo-language would look like this:
new_id = execute("SELECT nextval('person_id_seq')");
execute("INSERT INTO person (id, name) VALUES (new_id, 'Blaise
Pascal')");
You would then also have the new value stored in new_id for use in other
queries (e.g., as a foreign key to the person table). Note that the name
of the automatically created SEQUENCE object will be named <table>_<
serialcolumn>_seq, where table and serialcolumn are the names of your
table and your SERIAL column, respectively.
Alternatively, you could retrieve the assigned SERIAL value with the
currval() function after it was inserted by default, e.g.,
execute("INSERT INTO person (name) VALUES ('Blaise Pascal')");
new_id = execute("SELECT currval('person_id_seq')");
4.11.3) Doesn't currval() lead to a race condition with other users?
No. currval() returns the current value assigned by your session, not by
all sessions.
4.11.4) Why aren't my sequence numbers reused on transaction abort? Why
are there gaps in the numbering of my sequence/SERIAL column?
To improve concurrency, sequence values are given out to running
transactions as needed and are not locked until the transaction
completes. This causes gaps in numbering from aborted transactions.
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-dev
Searchable Archives: http://leafe.com/archives/search/dabo-dev
This message: http://leafe.com/archives/byMID/dabo-dev/[EMAIL PROTECTED]
