pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Problems ensuring uniqueness?
5+ messages / 2 participants
[nested] [flat]

* Problems ensuring uniqueness?
@ 2001-06-18 21:10 Christopher Smith <x@xman.org>
  2001-06-18 21:42 ` Re: [SQL] Problems ensuring uniqueness? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Christopher Smith @ 2001-06-18 21:10 UTC (permalink / raw)
  To: pgsql-sql; pgsql-jdbc@postgresql.org

Hi there. I'm currently using Postgresql 7.0.3, accessing using the JDBC
drivers. I'm enforcing the most severe transaction isolation available
(serializable). I'm currently doing something like the following (this
is pseudo code, with the if being performed in Java):

begin
lock table foo in access exclusive mode
select count(*) from foo where key1 = bar, key2 = baz

if count > 1
    rollback
else
    insert into foo (key1, key2, other) values (bar, baz, stuff)
    commit

Now, table foo has a primary key made up of key1 & key2. Without
concurrent access, the code works great. However, provided I use enough
threads, I inevitably get back errors from the database indicating that
I've tried to insert a record which violates the uniquness of the
primary key.

Is there away to avoid this error by doing my SQL & locking properly, or
should I just catch the exception and deal with it?

--Chris




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] Problems ensuring uniqueness?
  2001-06-18 21:10 Problems ensuring uniqueness? Christopher Smith <x@xman.org>
@ 2001-06-18 21:42 ` Tom Lane <tgl@sss.pgh.pa.us>
  2001-06-18 21:57   ` Re: [SQL] Problems ensuring uniqueness? Christopher Smith <x@xman.org>
  0 siblings, 1 reply; 5+ messages in thread

From: Tom Lane @ 2001-06-18 21:42 UTC (permalink / raw)
  To: Christopher Smith <x@xman.org>; +Cc: pgsql-sql; pgsql-jdbc@postgresql.org

Christopher Smith <x@xman.org> writes:
> begin
> lock table foo in access exclusive mode
> select count(*) from foo where key1 = bar, key2 = baz

> if count > 1
>     rollback
> else
>     insert into foo (key1, key2, other) values (bar, baz, stuff)
>     commit

> Now, table foo has a primary key made up of key1 & key2. Without
> concurrent access, the code works great. However, provided I use enough
> threads, I inevitably get back errors from the database indicating that
> I've tried to insert a record which violates the uniquness of the
> primary key.

Um ... surely that should be "if count > 0" ?  Or was that just a
transcription error?

This approach certainly ought to work as desired given the exclusive
lock, so a silly typo seems like a plausible explanation...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] Problems ensuring uniqueness?
  2001-06-18 21:10 Problems ensuring uniqueness? Christopher Smith <x@xman.org>
  2001-06-18 21:42 ` Re: [SQL] Problems ensuring uniqueness? Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-06-18 21:57   ` Christopher Smith <x@xman.org>
  2001-06-18 22:04     ` Re: [SQL] Problems ensuring uniqueness? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Christopher Smith @ 2001-06-18 21:57 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql; pgsql-jdbc@postgresql.org

On 18 Jun 2001 17:42:15 -0400, Tom Lane wrote:
> Christopher Smith <x@xman.org> writes:
> Um ... surely that should be "if count > 0" ?  Or was that just a
> transcription error?
> 
> This approach certainly ought to work as desired given the exclusive
> lock, so a silly typo seems like a plausible explanation...

Sorry, it is indeed a transcription error (sadly). The actual line in
question looks like this in Java:

boolean answer = resultSetCount.getInt(1) > 0;

I accidently transcribed the field offset instead of the comparison
constant.... probably should split that up into two lines of code to
avoid confusion like this...

--Chris




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] Problems ensuring uniqueness?
  2001-06-18 21:10 Problems ensuring uniqueness? Christopher Smith <x@xman.org>
  2001-06-18 21:42 ` Re: [SQL] Problems ensuring uniqueness? Tom Lane <tgl@sss.pgh.pa.us>
  2001-06-18 21:57   ` Re: [SQL] Problems ensuring uniqueness? Christopher Smith <x@xman.org>
@ 2001-06-18 22:04     ` Tom Lane <tgl@sss.pgh.pa.us>
  2001-06-18 22:23       ` Re: [SQL] Problems ensuring uniqueness? Christopher Smith <x@xman.org>
  0 siblings, 1 reply; 5+ messages in thread

From: Tom Lane @ 2001-06-18 22:04 UTC (permalink / raw)
  To: Christopher Smith <x@xman.org>; +Cc: pgsql-sql; pgsql-jdbc@postgresql.org

Christopher Smith <x@xman.org> writes:
>> Um ... surely that should be "if count > 0" ?  Or was that just a
>> transcription error?
>> 
>> This approach certainly ought to work as desired given the exclusive
>> lock, so a silly typo seems like a plausible explanation...

> Sorry, it is indeed a transcription error (sadly).

Oh well.  The next thought, given that you mention threads, is that
you've got multiple threads issuing commands to the same backend
connection; in which case the interlocking you think you have doesn't
exist at all...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] Problems ensuring uniqueness?
  2001-06-18 21:10 Problems ensuring uniqueness? Christopher Smith <x@xman.org>
  2001-06-18 21:42 ` Re: [SQL] Problems ensuring uniqueness? Tom Lane <tgl@sss.pgh.pa.us>
  2001-06-18 21:57   ` Re: [SQL] Problems ensuring uniqueness? Christopher Smith <x@xman.org>
  2001-06-18 22:04     ` Re: [SQL] Problems ensuring uniqueness? Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-06-18 22:23       ` Christopher Smith <x@xman.org>
  0 siblings, 0 replies; 5+ messages in thread

From: Christopher Smith @ 2001-06-18 22:23 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql; pgsql-jdbc@postgresql.org

On 18 Jun 2001 18:04:14 -0400, Tom Lane wrote:
> Christopher Smith <x@xman.org> writes:
> >> Um ... surely that should be "if count > 0" ?  Or was that just a
> >> transcription error?
> >> 
> >> This approach certainly ought to work as desired given the exclusive
> >> lock, so a silly typo seems like a plausible explanation...
> 
> > Sorry, it is indeed a transcription error (sadly).
> 
> Oh well.  The next thought, given that you mention threads, is that
> you've got multiple threads issuing commands to the same backend
> connection; in which case the interlocking you think you have doesn't
> exist at all...

You got it bang on... I thought I had isolated access to the connections
properly, but shortly after posting that last e-mail, had a eureka moment.
I presume this will make the bug go away, so I'll encourage everyone to
ignore this thread (other than as a warning ;-) until I've confirmed I
still have the problem after making the correct adjustments.

--Chris




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2001-06-18 22:23 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-18 21:10 Problems ensuring uniqueness? Christopher Smith <x@xman.org>
2001-06-18 21:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-06-18 21:57   ` Christopher Smith <x@xman.org>
2001-06-18 22:04     ` Tom Lane <tgl@sss.pgh.pa.us>
2001-06-18 22:23       ` Christopher Smith <x@xman.org>

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