X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 18C485E47DD for ; Mon, 6 Sep 2004 15:34:59 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 25721-09 for ; Mon, 6 Sep 2004 14:34:56 +0000 (GMT) Received: from simmts5-srv.bellnexxia.net (simmts5.bellnexxia.net [206.47.199.163]) by svr1.postgresql.org (Postfix) with ESMTP id 46DFE5E47C7 for ; Mon, 6 Sep 2004 15:34:49 +0100 (BST) Received: from localhost ([67.71.24.121]) by simmts5-srv.bellnexxia.net (InterMail vM.5.01.06.10 201-253-122-130-110-20040306) with ESMTP id <20040906143308.ITJX1635.simmts5-srv.bellnexxia.net@localhost>; Mon, 6 Sep 2004 10:33:08 -0400 Received: from [192.168.250.6] ([192.168.250.6]) by (8.11.6/8.11.6) with ESMTP id i86EYug16980; Mon, 6 Sep 2004 10:34:56 -0400 Message-ID: <413C75E5.7090406@sympatico.ca> Date: Mon, 06 Sep 2004 10:36:21 -0400 From: Jean-Luc Lachance User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7.2) Gecko/20040804 Netscape/7.2 (ax) X-Accept-Language: en, en-us, en-gb, fr, fr-ca MIME-Version: 1.0 To: Michael Paesold Cc: Tsirkin Evgeny , pgsql-admin@postgresql.org Subject: Re: duplicates References: <008601c493e5$e0965940$0b00a8c0@forge> <00c701c493eb$def5b9e0$0b00a8c0@forge> <009501c493f6$52932770$ad01a8c0@zaphod> <00a701c493fd$dd44d060$ad01a8c0@zaphod> <00ea01c4940d$59f54b30$ad01a8c0@zaphod> In-Reply-To: <00ea01c4940d$59f54b30$ad01a8c0@zaphod> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/55 X-Sequence-Number: 14758 If the reccord you are inserting do not depend on data in the record you want to delete, why not simply use a trigger? Before insert delete the record with the same key! Michael Paesold wrote: > Tsirkin Evgeny wrote: > > > >>one moer question ,how did you tested it? >> >> >>>I have tested this here. I don't really know if this is just the case > > with > >>>Best Regards, >>>Michael Paesold > > > First I created the your schedule table. Then I opened two psql sessions... > > Session A Session B > > BEGIN; > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; > DELETE FROM schedule WHERE studentid = 1; > INSERT INTO schedule VALUES (1, 1, 0, 0); > > BEGIN; > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; > DELETE FROM schedule WHERE studentid = 1; > -- if there existed records for this delete, > -- session B will now wait for session A to > -- commit/rollback and error out on commit; > -- otherwise no error > > > COMMIT; > INSERT INTO schedule VALUES (1, 1, 0, 0); > COMMIT; > > > You can also try and rollback the first transaction etc. > > Again, what really helps is: > ALTER TABLE schedule ADD CONSTRAINT uniq_schedule UNIQUE (studentid, > groupid); > > Best Regards, > Michael Paesold > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >