pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedInsert Statements Hanging
2+ messages / 2 participants
[nested] [flat]
* Insert Statements Hanging
@ 2007-07-25 20:27 Pallav Kalva <pkalva@livedatagroup.com>
2007-07-25 20:55 ` Re: Insert Statements Hanging Alan Hodgson <ahodgson@simkin.ca>
0 siblings, 1 reply; 2+ messages in thread
From: Pallav Kalva @ 2007-07-25 20:27 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Insert Statements Hanging
2007-07-25 20:27 Insert Statements Hanging Pallav Kalva <pkalva@livedatagroup.com>
@ 2007-07-25 20:55 ` Alan Hodgson <ahodgson@simkin.ca>
0 siblings, 0 replies; 2+ messages in thread
From: Alan Hodgson @ 2007-07-25 20:55 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-07-25 20:55 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-25 20:27 Insert Statements Hanging Pallav Kalva <pkalva@livedatagroup.com>
2007-07-25 20:55 ` Alan Hodgson <ahodgson@simkin.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox