[GENERAL] table lock when where clause uses unique constraing instead of primary key.

2013-11-04 Thread Jeff Amiel
PostgreSQL 9.2.4 on x86_64-pc-solaris2.10, compiled by gcc (GCC) 3.4.3 (csl-sol210-3_4-branch+sol_rpath), 64-bit Have got an annoying scenario that has been creating issues for us for years…. Time to try to figure it out. Essentially, we have a user table where we maintain username, id number,

Re: [GENERAL] table lock when where clause uses unique constraing instead of primary key.

2013-11-04 Thread Rob Sargent
On 11/04/2013 10:06 AM, Jeff Amiel wrote: PostgreSQL 9.2.4 on x86_64-pc-solaris2.10, compiled by gcc (GCC) 3.4.3 (csl-sol210-3_4-branch+sol_rpath), 64-bit Have got an annoying scenario that has been creating issues for us for years…. Time to try to figure it out. Essentially, we have a user

Re: [GENERAL] table lock when where clause uses unique constraing instead of primary key.

2013-11-04 Thread Jeff Amiel
to: Rob Sargent The login references have nothing to do with postgres - is simply table/column names being used. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] table lock when where clause uses unique constraing instead of primary key.

2013-11-04 Thread Rob Sargent
On 11/04/2013 11:38 AM, Jeff Amiel wrote: to: Rob Sargent The login references have nothing to do with postgres - is simply table/column names being used. I'm sorry, I thought user_profile.login_attempts was being set to zero during login (or perhaps after successfulloginin a two-step

Re: [GENERAL] table lock when where clause uses unique constraing instead of primary key.

2013-11-04 Thread Adrian Klaver
On 11/04/2013 09:06 AM, Jeff Amiel wrote: PostgreSQL 9.2.4 on x86_64-pc-solaris2.10, compiled by gcc (GCC) 3.4.3 (csl-sol210-3_4-branch+sol_rpath), 64-bit Have got an annoying scenario that has been creating issues for us for years…. Time to try to figure it out. Essentially, we have a user

Re: [GENERAL] table lock when where clause uses unique constraing instead of primary key.

2013-11-04 Thread Jeff Amiel
On Monday, November 4, 2013 1:48 PM, Adrian Klaver adrian.kla...@gmail.com wrote: Any triggers on user_profile? Any FK relationship in either direction? I grepped the schema (just to be sure) - no foreign keys on columns or table at all. I do have an audit trigger on the table for

Re: [GENERAL] table lock when where clause uses unique constraing instead of primary key.

2013-11-04 Thread Adrian Klaver
On 11/04/2013 12:15 PM, Jeff Amiel wrote: On Monday, November 4, 2013 1:48 PM, Adrian Klaver adrian.kla...@gmail.com wrote: Any triggers on user_profile? Any FK relationship in either direction? I grepped the schema (just to be sure) - no foreign keys on columns or table at all. I

Re: [GENERAL] table lock when where clause uses unique constraing instead of primary key.

2013-11-04 Thread Jeff Amiel
On Monday, November 4, 2013 2:25 PM, Adrian Klaver adrian.kla...@gmail.com wrote: I grepped the schema (just to be sure) - no foreign keys on columns or table at all. I do have an audit trigger on the table for updates - inserts into an audit table when changes are made and it DOES

Re: [GENERAL] table lock when where clause uses unique constraing instead of primary key.

2013-11-04 Thread Adrian Klaver
On 11/04/2013 12:44 PM, Jeff Amiel wrote: On Monday, November 4, 2013 2:25 PM, Adrian Klaver adrian.kla...@gmail.com wrote: I grepped the schema (just to be sure) - no foreign keys on columns or table at all. I do have an audit trigger on the table for updates - inserts into an audit

Re: [GENERAL] table lock when where clause uses unique constraing instead of primary key.

2013-11-04 Thread Rob Sargent
On 11/04/2013 01:56 PM, Adrian Klaver wrote: On 11/04/2013 12:44 PM, Jeff Amiel wrote: On Monday, November 4, 2013 2:25 PM, Adrian Klaver adrian.kla...@gmail.com wrote: I grepped the schema (just to be sure) - no foreign keys on columns or table at all. I do have an audit trigger on

Re: [GENERAL] table lock when where clause uses unique constraing instead of primary key.

2013-11-04 Thread Adrian Klaver
On 11/04/2013 01:16 PM, Jeff Amiel wrote: On Monday, November 4, 2013 2:56 PM, Adrian Klaver adrian.kla...@gmail.com wrote: In the screenshot you posted what are the columns indicating, in particular the third one? Assuming the third column is pointing to the pid of the offending

Re: [GENERAL] table lock when where clause uses unique constraing instead of primary key.

2013-11-04 Thread Jeff Amiel
On Monday, November 4, 2013 3:23 PM, Adrian Klaver adrian.kla...@gmail.com wrote: Probably poor choice of words:). So then, what we are looking at is other clients trying to update user_profile but not succeeding because pid 4899 is blocking. At this point all I can see is that the

Re: [GENERAL] table lock when where clause uses unique constraing instead of primary key.

2013-11-04 Thread Adrian Klaver
On 11/04/2013 01:56 PM, Jeff Amiel wrote: On Monday, November 4, 2013 3:23 PM, Adrian Klaver adrian.kla...@gmail.com wrote: Probably poor choice of words:). So then, what we are looking at is other clients trying to update user_profile but not succeeding because pid 4899 is blocking. At