Re: Performance Concern - Mailing list pgsql-performance

At 05:56 10/25/2003, John Pagakis wrote:

Snipping most of this, I only have one suggestion/comment to make.

[snip]

>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);

[snip]

>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.

[snip]

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.

-Allen