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.
