Thread: Insert Statements Hanging

Insert Statements Hanging

From
Pallav Kalva
Date:
Hi,

    I am having problems with some of the Insert statements in the prod
database. Our client application is trying into insert some of the
records and it is not going through , they are just hanging. They are
running in a transaction and some how it is not telling us what is it
waiting on . Here is the output from pg_stat_activity

select current_query from pg_stat_activity where current_query <>
'<IDLE>' order by query_start;

current_query
------------------
 insert into provisioning.accountnote (fkserviceinstanceid, fkaccountid,
fknoteid, accountnoteid) values ($1, $2, $3, $4)

As soon as I kill the process id for this insert statement I see these
statements in the postgres log file

 FATAL:  terminating connection due to administrator command
 CONTEXT:  SQL statement "SELECT 1 FROM ONLY "provisioning"."account" x
WHERE "accountid" = $1 FOR UPDATE OF x"

I am hoping "SELECT 1 FROM ONLY "provisioning"."account" x WHERE
"accountid" = $1 FOR UPDATE OF x" is causing the problem. If that is the
case why doesnt it show in the pg_stat_activity view ? or am I missing
something here ? what would be the reason for insert statement to hang
like that ?

Postgres version:  8.0.12, vacuums and analyze are done regularly.

Here are table structures

\d provisioning.accountnote
                               Table "provisioning.accountnote"
       Column        |  Type   |                           Modifiers
---------------------+---------+---------------------------------------------------------------
 accountnoteid       | integer | not null default
nextval('provisioning.AccountNoteSeq'::text)
 fkaccountid         | integer | not null
 fknoteid            | integer | not null
 fkserviceinstanceid | integer |
Indexes:
    "pk_accountnote_accountnoteid" PRIMARY KEY, btree (accountnoteid)
    "idx_accountnote_fkaccountid" btree (fkaccountid)
    "idx_accountnote_fknoteid" btree (fknoteid)
    "idx_accountnote_fkserviceinstanceid" btree (fkserviceinstanceid)
Foreign-key constraints:
    "fk_accountnote_serviceinstance" FOREIGN KEY (fkserviceinstanceid)
REFERENCES provisioning.serviceinstance(serviceinstanceid)
    "fk_accountnote_note" FOREIGN KEY (fknoteid) REFERENCES
common.note(noteid)
    "fk_accountnote_account" FOREIGN KEY (fkaccountid) REFERENCES
provisioning.account(accountid)



\d provisioning.account
                                              Table "provisioning.account"
          Column          |            Type
|                           Modifiers

--------------------------+-----------------------------+----------------------------------------------------------------
 accountid                | integer                     | not null
default nextval('provisioning.AccountSeq'::text)
 createdate               | timestamp without time zone | not null
default ('now'::text)::timestamp(6) without time zone
 fkcontactid              | integer                     |
 login                    | text                        | not null
 password                 | text                        |
 fkserviceproviderid      | integer                     |
 serviceproviderreference | text                        |
Indexes:
    "pk_account_accountid" PRIMARY KEY, btree (accountid)
    "idx_account_fkcontactid" btree (fkcontactid)
    "idx_account_login" btree (login)
    "idx_account_serviceproviderreference" btree (serviceproviderreference)
Foreign-key constraints:
    "fk_account_serviceprovider" FOREIGN KEY (fkserviceproviderid)
REFERENCES provisioning.serviceprovider(serviceproviderid)
    "fk_account_contact" FOREIGN KEY (fkcontactid) REFERENCES
common.contact(contactid)

Thanks!
Pallav.


Re: Insert Statements Hanging

From
Alan Hodgson
Date:
On Wednesday 25 July 2007 13:27, Pallav Kalva <pkalva@livedatagroup.com>
wrote:
> I am hoping "SELECT 1 FROM ONLY "provisioning"."account" x WHERE
> "accountid" = $1 FOR UPDATE OF x" is causing the problem. If that is the
> case why doesnt it show in the pg_stat_activity view ? or am I missing
> something here ? what would be the reason for insert statement to hang
> like that ?

It's waiting for a lock, probably on one of the tables that it references
for foreign keys.

8.1 or later would have that happen a lot less; they altered the locking
requirements for foreign key lookups.

--
"It is a besetting vice of democracies to substitute public opinion for
law." - James Fenimore Cooper