PostgreSQL SQL - getting duplicate number is there a

Author

getting duplicate number is there a

Joel Fradkin

2005-05-16, 8:24 pm

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,lengt
h(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.

Ezequiel Tolnay

2005-05-17, 3:23 am

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,lengt
h(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

Richard Huxton

2005-05-17, 3:23 am

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,lengt
h(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_n
um)
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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Joel Fradkin

2005-05-17, 7:24 am

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_n
um)
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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Bruno Wolff III

2005-05-17, 9:23 am

On Mon, May 16, 2005 at 17:36:21 -0400,
Joel Fradkin <jfradkin@wazagua.com> 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,lengt
h(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.
>
>
>
>
>
>
>
>

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Sponsored Links