Subject: Insert race hazard condition.
From: Roy Souther <roy@SiliconTao.com>
Reply-To: roy@SiliconTao.com
To: pgsql-sql@postgresql.org
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-F9qYZ/WHNeSJ6L/s0R89"
Organization: Silicon Tao Technology Systems Inc.
Message-Id: <1048622477.2643.44.camel@UtopiaPlanetia.Ept>
Mime-Version: 1.0
Date: 25 Mar 2003 13:01:17 -0700

--=-F9qYZ/WHNeSJ6L/s0R89
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

First of all I am a programmer not an DBA expert.=20

For a long time now when I create a table I have an index field that is
of type int4 to be the primary key. All of the queries and activity on
each record uses this field to do the posting, manipulating and reading
of data to and from tables.

When I add a record to a table I would do it in two steps.
INSERT INTO mytable SELECT MAX(index)+1 FROM mytable
SELECT MAX(index) FROM mytable

The first part would create a new record with the next index value. The
second command would return to me the index value so that I could use it
to update data in that record using the index value.

Up till now this has worked fine in C C++ and Perl, the languages I use
mostly. These two commands happen very quickly so on a database that has
a low amount of activity it works fine. Now I am getting into a
situation where many connections are adding a lot of records very
quickly. Last week an error happened where two records where created
that had the same index value.

There seems to be many ways to solve this. My first idea would be if I
could make the INSERT some how return the index+1 that it creates but I
can not find any way to make that work.

My current idea is to have a short function that does both steps then
returns the index value. This works and I think it should not cause any
race hazard because the SQL execution should be completed before it
returns to the program.

Is there a better way to do this? How do DBAs with a lot of connections
safely create new records?

Roy Souther
www.SiliconTao.com

Changing the way people do business.

--=-F9qYZ/WHNeSJ6L/s0R89
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.7 (GNU/Linux)

iD8DBQA+gLWNCbnxcmEBt40RAg3OAKCGUoHV9FBFAUtC1uRGJSNUGj5mdgCgsF1g
6Kc9bRTqD0/SviEmI0YXGes=
=VJQp
-----END PGP SIGNATURE-----

--=-F9qYZ/WHNeSJ6L/s0R89--