pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Allen Landsidel <all@biosys.net>
To: thebfh@toolsmythe.com
To: john@pagakis.com
To: pgsql-performance@postgresql.org
Subject: Re: Performance Concern
Date: Sat, 25 Oct 2003 07:20:03 -0400
Message-ID: <6.0.0.22.0.20031025070905.02458060@pop.hotpop.com> (raw)
In-Reply-To: <KKEBKDPPLALEFHBEAOCCIEDFDEAA.thebfh@toolsmythe.com>
References: <KKEBKDPPLALEFHBEAOCCGEBLDEAA.john@pagakis.com>
	<KKEBKDPPLALEFHBEAOCCIEDFDEAA.thebfh@toolsmythe.com>

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 




view thread (16+ messages)  latest in thread

Message-ID: <6.0.0.22.0.20031025070905.02458060@pop.hotpop.com>
Permalink:  ../6.0.0.22.0.20031025070905.02458060@pop.hotpop.com/
Also on:    postgresql.org/message-id/6.0.0.22.0.20031025070905.02458060@pop.hotpop.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: all@biosys.net, thebfh@toolsmythe.com, john@pagakis.com
  Subject: Re: Performance Concern
  In-Reply-To: <6.0.0.22.0.20031025070905.02458060@pop.hotpop.com>
  References: <KKEBKDPPLALEFHBEAOCCGEBLDEAA.john@pagakis.com> <KKEBKDPPLALEFHBEAOCCIEDFDEAA.thebfh@toolsmythe.com> <6.0.0.22.0.20031025070905.02458060@pop.hotpop.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox