Message-ID: <46A7B216.2070608@livedatagroup.com>
Date: Wed, 25 Jul 2007 16:27:02 -0400
From: Pallav Kalva <pkalva@livedatagroup.com>
User-Agent: Thunderbird 1.5.0.10 (X11/20060911)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Insert Statements Hanging 
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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.