Snipping most of this, I only have one suggestion/comment to make.
CREATE TABLE baz ( baz_key int4 NOT NULL, baz_number CHAR(15) NOT NULL, customer_id CHAR(39), foobar_id INTEGER, is_cancelled BOOL DEFAULT false NOT NULL, create_user VARCHAR(60) NOT NULL, create_datetime TIMESTAMP DEFAULT 'now()' NOT NULL, last_update_user VARCHAR(60) NOT NULL, last_update_datetime TIMESTAMP DEFAULT 'now()' NOT NULL, CONSTRAINT PK_baz PRIMARY KEY (baz_key) );
ALTER TABLE baz ADD FOREIGN KEY (customer_id) REFERENCES purchase (customer_id);
ALTER TABLE baz ADD FOREIGN KEY (foobar_id) REFERENCES foobar (foobar_id);
I needed to do this because I absolutely positively cannot over-allocate baz. I cannot allocate more than 100,000 period, and any number of users can attempt to purchase one or more baz simultaneously. I am attempting to avoid a race condition and avoid using database locks as I feared this table would turn into a bottleneck.
I have a similar situation in the database here, using the following example schema:
CREATE TABLE foo ( nID serial UNIQUE NOT NULL, bAvailable boolean NOT NULL DEFAULT true, nSomeField int4 NOT NULL, sSomeField text NOT NULL );
CREATE TABLE bar ( nfoo_id int4 UNIQUE NOT NULL );
Assume foo is the table with the 100k pre-populated records that you want to assign to visitors on your site. bar is a table whos only purpose is to eliminate race conditions, working off the following business rules:
1. -- someone attempts to get a 'foo' SELECT nID from foo WHERE bAvailable;
2. -- we first try to assign this 'foo' to ourselves -- the ? is bound to the foo.nID we selected in step 1. INSERT INTO bar (nfoo_ID) VALUES (?)
3. -- Only if step 2 is successful, do we continue, otherwise someone beat us to it.
UPDATE foo SET ... WHERE nID=?
The key here is step 2.
Since there is a UNIQUE constraint defined on the bar.nfoo_id (could even be an FK), only one INSERT will ever succeed. All others will fail. In step 3, you can set the bAvailable flag to false, along with whatever other values you need to set for your 'baz'.
This will get much easier once 7.4 is production-ready, as the WHERE IN .. or WHERE NOT IN.. subselects are (according to the HISTORY file) going to be as efficient as joins, instead of the O(n) operation they apparently are right now.
Until then however, I've found this simple trick works remarkably well.
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?