From jfradkin@wazagua.com Mon May 16 21:35:58 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 82D9252810 for ; Mon, 16 May 2005 18:35:58 -0300 (ADT) 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 01705-07 for ; Mon, 16 May 2005 21:35:55 +0000 (GMT) Received: from mta10.nationwireless.net (mta10.nationwireless.net [63.163.128.10]) by svr1.postgresql.org (Postfix) with ESMTP id 80DB952805 for ; Mon, 16 May 2005 18:35:50 -0300 (ADT) Received: from jfradkin [63.162.100.127] by mta10.nationwireless.net with ESMTP (SMTPD-8.20) id A23803A0; Mon, 16 May 2005 17:35:52 -0400 From: "Joel Fradkin" To: Subject: getting duplicate number is there a Date: Mon, 16 May 2005 17:36:21 -0400 Message-ID: <000e01c55a5f$4d912d00$797ba8c0@jfradkin> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_000F_01C55A3D.C67F8D00" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.934 tagged_above=0 required=5 tests=AWL, HTML_MESSAGE, MSGID_DOLLARS X-Spam-Level: * X-Archive-Number: 200505/118 X-Sequence-Number: 21431 This is a multi-part message in MIME format. ------=_NextPart_000_000F_01C55A3D.C67F8D00 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit I was using SET TRANSACTION ISOLATION LEVEL SERIALIZABLE in MSSQL. Is there something similar in postgres to ensure its not in the middle of being updated? sql = "SELECT COUNT(*) FROM tblcase WHERE LocationID = " & intLocationID & _ " and substr(casenum,length(casenum)-1,2) = '" & right(year(date),2) & "' AND clientnum = '" & _ chrClientNum & "'" I will add a select just before doing the insert to see if this helps, its not happening a bunch, but 5 6 times a day is still an issue for me. I use the count as a segment of my case number so each time a new case is entered the count goes up for that location for that year. Joel Fradkin Wazagua, Inc. 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305 jfradkin@wazagua.com www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology & advanced tools. C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc This email message is for the use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and delete and destroy all copies of the original message, including attachments. ------=_NextPart_000_000F_01C55A3D.C67F8D00 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

I was using SET = TRANSACTION ISOLATION LEVEL SERIALIZABLE in MSSQL.

Is there something = similar in postgres to ensure its not in the middle of being = updated?

= sql =3D "SELECT COUNT(*) FROM tblcase WHERE LocationID =3D " & = intLocationID & _

&= nbsp; " and substr(casenum,length(casenum)-1,2) =3D '" & = right(year(date),2) & "' AND clientnum =3D '" & _

&= nbsp; chrClientNum & "'"

I will add a select just before doing the insert to = see if this helps, its not happening a bunch, but 5 6 times a day is still an = issue for me.

I use the count as a segment of my case number so = each time a new case is entered the count goes up for that location for that = year.

Joel Fradkin

Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida = 34243
Tel. 941-753-7111 ext 305

jfradkin@wazagua.com
www.wazagua.com
Powered by Wazagua
Providing you with the latest Web-based technology & advanced = tools.
© 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
This email message is for the use of the intended recipient(s) and = may contain confidential and privileged information. Any unauthorized = review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and delete = and destroy all copies of the original message, including = attachments.

------=_NextPart_000_000F_01C55A3D.C67F8D00-- From mail@etolnay.com.ar Tue May 17 05:12:19 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A520B5280E for ; Tue, 17 May 2005 02:12:19 -0300 (ADT) 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 61288-05 for ; Tue, 17 May 2005 05:12:08 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id D703452801 for ; Tue, 17 May 2005 02:12:07 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.13.1/8.13.1) with ESMTP id j4H5C7pU074340 for ; Tue, 17 May 2005 05:12:07 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.13.1/8.13.1/Submit) id j4H51Hm6070693 for pgsql-sql@postgresql.org; Tue, 17 May 2005 05:01:17 GMT (envelope-from news) From: Ezequiel Tolnay X-Newsgroups: pgsql.sql Subject: Re: getting duplicate number is there a Date: Tue, 17 May 2005 15:02:17 +1000 Organization: Hub.Org Networking Services Lines: 31 Message-ID: References: <000e01c55a5f$4d912d00$797ba8c0@jfradkin> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.6) Gecko/20050319 X-Accept-Language: en-au, en-gb, en-us, en, es-ar, es-es In-Reply-To: <000e01c55a5f$4d912d00$797ba8c0@jfradkin> To: pgsql-sql@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200505/125 X-Sequence-Number: 21438 You can select "for update", so you ensure that the rows are locked for your current transaction's use exclusively. If the rows in question had been modified by another ongoing transaction, then the select will get blocked until the other transaction is finished. Cheers, Ezequiel Tolnay etolnay@gbtech.com.au Joel Fradkin wrote: > I was using SET TRANSACTION ISOLATION LEVEL SERIALIZABLE in MSSQL. > > Is there something similar in postgres to ensure its not in the middle > of being updated? > > sql = "SELECT COUNT(*) FROM tblcase WHERE LocationID = " & > intLocationID & _ > > " and substr(casenum,length(casenum)-1,2) = '" & > right(year(date),2) & "' AND clientnum = '" & _ > > chrClientNum & "'" > > I will add a select just before doing the insert to see if this helps, > its not happening a bunch, but 5 6 times a day is still an issue for me. > > I use the count as a segment of my case number so each time a new case > is entered the count goes up for that location for that year. > > Joel Fradkin From dev@archonet.com Tue May 17 07:17:47 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D559752804 for ; Tue, 17 May 2005 04:17:47 -0300 (ADT) 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 56516-01 for ; Tue, 17 May 2005 07:17:46 +0000 (GMT) Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75]) by svr1.postgresql.org (Postfix) with ESMTP id 67DD2528F2 for ; Tue, 17 May 2005 04:17:45 -0300 (ADT) Received: from mainbox.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id D500A402A22; Tue, 17 May 2005 08:15:22 +0100 (BST) Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id D04F715EA6; Tue, 17 May 2005 08:11:35 +0100 (BST) Received: from mainbox.archonet.com ([127.0.0.1]) by localhost (mainbox [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 15267-06; Tue, 17 May 2005 08:11:35 +0100 (BST) Received: from [192.168.1.17] (client17.office.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 91C9415EA5; Tue, 17 May 2005 08:11:35 +0100 (BST) Message-ID: <42899927.9070801@archonet.com> Date: Tue, 17 May 2005 08:11:35 +0100 From: Richard Huxton User-Agent: Mozilla Thunderbird 1.0 (X11/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Joel Fradkin Cc: pgsql-sql@postgresql.org Subject: Re: getting duplicate number is there a References: <000e01c55a5f$4d912d00$797ba8c0@jfradkin> In-Reply-To: <000e01c55a5f$4d912d00$797ba8c0@jfradkin> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.053 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200505/119 X-Sequence-Number: 21432 Joel Fradkin wrote: > I was using SET TRANSACTION ISOLATION LEVEL SERIALIZABLE in MSSQL. > > Is there something similar in postgres to ensure its not in the middle of > being updated? Yep - see the SQL COMMANDS reference section under SET TRANSACTION ... You could use LOCK TABLE too. See Chapter 12 - Concurrency Control for discussion. > sql = "SELECT COUNT(*) FROM tblcase WHERE LocationID = " & > intLocationID & _ > " and substr(casenum,length(casenum)-1,2) = '" & > right(year(date),2) & "' AND clientnum = '" & _ > > chrClientNum & "'" > > I will add a select just before doing the insert to see if this helps, its > not happening a bunch, but 5 6 times a day is still an issue for me. > > I use the count as a segment of my case number so each time a new case is > entered the count goes up for that location for that year. I'd be tempted to have a case_numbers table with (year,location,max_num) and lock/read/insert to that. Makes everything explicit, and means you don't have to mess around with counts/substrings. -- Richard Huxton Archonet Ltd From jfradkin@wazagua.com Tue May 17 12:31:16 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 92C445282F for ; Tue, 17 May 2005 09:31:16 -0300 (ADT) 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 78449-01 for ; Tue, 17 May 2005 12:31:15 +0000 (GMT) Received: from mta10.nationwireless.net (mta10.nationwireless.net [63.163.128.10]) by svr1.postgresql.org (Postfix) with ESMTP id A90F952813 for ; Tue, 17 May 2005 09:31:13 -0300 (ADT) Received: from jfradkin [63.162.100.127] by mta10.nationwireless.net with ESMTP (SMTPD-8.20) id A414062C; Tue, 17 May 2005 08:31:16 -0400 From: "Joel Fradkin" To: "'Richard Huxton'" Cc: Subject: Re: getting duplicate number is there a Date: Tue, 17 May 2005 08:31:46 -0400 Message-ID: <000501c55adc$640855e0$797ba8c0@jfradkin> MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit In-Reply-To: <42899927.9070801@archonet.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.953 tagged_above=0 required=5 tests=AWL, MSGID_DOLLARS X-Spam-Level: * X-Archive-Number: 200505/120 X-Sequence-Number: 21433 I actually had the same thought (a counter table, I might be able to add fields to the location table, but we have several applications case is just an example). I agree that is probably the safest way and it also fixes another issue I have been having when a user wants to transfer a case to another location. I appreciate the ideas, I could probably safely lock the numbering table as I would be afraid of locking the case table. Joel Fradkin I'd be tempted to have a case_numbers table with (year,location,max_num) and lock/read/insert to that. Makes everything explicit, and means you don't have to mess around with counts/substrings. -- Richard Huxton Archonet Ltd From bruno@wolff.to Tue May 17 13:20:26 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5CD3D52856 for ; Tue, 17 May 2005 10:20:26 -0300 (ADT) 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 96251-10 for ; Tue, 17 May 2005 13:20:16 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.197.194]) by svr1.postgresql.org (Postfix) with SMTP id E768B5281B for ; Tue, 17 May 2005 10:20:15 -0300 (ADT) Received: (qmail 11076 invoked by uid 500); 17 May 2005 13:18:47 -0000 Date: Tue, 17 May 2005 08:18:47 -0500 From: Bruno Wolff III To: Joel Fradkin Cc: pgsql-sql@postgresql.org Subject: Re: getting duplicate number is there a Message-ID: <20050517131847.GA10153@wolff.to> Mail-Followup-To: Bruno Wolff III , Joel Fradkin , pgsql-sql@postgresql.org References: <000e01c55a5f$4d912d00$797ba8c0@jfradkin> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <000e01c55a5f$4d912d00$797ba8c0@jfradkin> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.009 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200505/121 X-Sequence-Number: 21434 On Mon, May 16, 2005 at 17:36:21 -0400, Joel Fradkin wrote: > I was using SET TRANSACTION ISOLATION LEVEL SERIALIZABLE in MSSQL. > > > > Is there something similar in postgres to ensure its not in the middle of > being updated? Postgres also has SET TRANSACTION ISOLATION LEVEL SERIALIZABLE. This will prevent the current transaction from seeing the results of any transactions that were not committed before the current transaction started. In this mode updates can fail because of actions of concurrent transactions, so you need to be able to retry. Also Postgres does not do predicate locking. For some operations serializable isn't good enough. Instead you need to lock a table to prevent inserts. The common case is two simultaneous transactions that insert a record into the same table and store the count of the number of records in the table, while expecting things to look like one transaction happened before the other. (E.g. that they return distinct values for the counts.) > > > > sql = "SELECT COUNT(*) FROM tblcase WHERE LocationID = " & > intLocationID & _ > > " and substr(casenum,length(casenum)-1,2) = '" & > right(year(date),2) & "' AND clientnum = '" & _ > > chrClientNum & "'" > > > > I will add a select just before doing the insert to see if this helps, its > not happening a bunch, but 5 6 times a day is still an issue for me. > > I use the count as a segment of my case number so each time a new case is > entered the count goes up for that location for that year. > > > > Joel Fradkin > > > > Wazagua, Inc. > 2520 Trailmate Dr > Sarasota, Florida 34243 > Tel. 941-753-7111 ext 305 > > > > jfradkin@wazagua.com > www.wazagua.com > Powered by Wazagua > Providing you with the latest Web-based technology & advanced tools. > C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc > This email message is for the use of the intended recipient(s) and may > contain confidential and privileged information. Any unauthorized review, > use, disclosure or distribution is prohibited. If you are not the intended > recipient, please contact the sender by reply email and delete and destroy > all copies of the original message, including attachments. > > > > > > > >