Re: Design question regarding arrays

A Gilmore wrote:
> Thanks a lot of the detailed reply, its a huge help. Does bring me to
> one other question Id like to clarify. Using this method Ill be doing
> inserts into books_categories based on the ID of the latest INSERT into
> the books table. Since I cannot really have a INSERT return a value
> such as the serial used without getting into PL/pgSQL, how can I get the
> ID of the book just inserted?
>
> According to board threads I have found doing something like :
>
> select book_id from books where book_id = currval('book_id_seq');
>
> Could lead to problems if multiple inserts are occuring at the same time
> since currval() may have changed since the insert, and transactions do
> not prevent that. Is this correct, that transactions will not help, and
> if so what is the most common way of dealing with this issue (since I
> assume its common) ?

Well, I assume that all your book titles are unique, so after
INSERTing a title, you can immediately SELECT the row which
contains that title, so you will get the right ID, no matter
whether there are concurrent updates/inserts:

INSERT books (title) VALUES ('PostgreSQL for Dummies')

SELECT id FROM books WHERE title = 'PostgreSQL for Dummies'

If your titles are not unique, it will get a bit more difficult.
In that case, you can (in fact: must) set the "serializable"
isolation level (not the default which is "read committed"),
then you INSERT the new row in the books table and immediately
(within the same transaction) SELECT currval() from the serial
column. The serializable isolation level will guarantee that
you will get the new value caused by your own most recent
INSERT within the same transaction. However, be prepared that
the transaction might fail if a concurrent update occurs. The
docs have much more information on the topic of isolation
levels:

http://www.postgresql.org/docs/current/static/transaction-iso.html

Best regards
Oliver

--
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.

"I made up the term 'object-oriented', and I can tell you
I didn't have C++ in mind."
-- Alan Kay, OOPSLA '97