Skip site navigation (1) Skip section navigation (2)

Insert Statements Hanging

From: Pallav Kalva <pkalva(at)livedatagroup(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Insert Statements Hanging
Date: 2007-07-25 20:27:02
Message-ID: 46A7B216.2070608@livedatagroup.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.


Responses

pgsql-performance by date

Next:From: Bill MoranDate: 2007-07-25 20:28:34
Subject: Re: Simple select hangs while CPU close to 100%
Previous:From: Pavel StehuleDate: 2007-07-25 19:06:02
Subject: Re: Simple select hangs while CPU close to 100%

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group