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 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 se

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 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 offending >query is updat

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 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 query it is interesting that

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 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 -

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 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 m

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 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 do a separate select

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 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

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 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 updates - inserts into an a

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 tabl

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 proc

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 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 tabl

[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, en