Re: Postgresql Database Lock Problem
От | shohorab hossain |
---|---|
Тема | Re: Postgresql Database Lock Problem |
Дата | |
Msg-id | 569741.50741.qm@web51709.mail.re2.yahoo.com обсуждение исходный текст |
Ответ на | Re: Postgresql Database Lock Problem (Scott Marlowe <scott.marlowe@gmail.com>) |
Список | pgsql-general |
The statement that creates lock and waiting is in following. This is from pg_stats and pg_stat_activity view. Here AD_Sequence is a table that maintains sequence number for all database objects. It automatically generates primary keyvalue for all table insert. I think it also generates transaction related ID. For that reason it needs to update nextsequence value for transaction ID. Statment: ====================== SELECT CurrentNext, CurrentNextSys, IncrementNo, Prefix, Suffix, DecimalPattern, AD_Sequence_ID FROM AD_Sequence WHERE Name= $1 AND AD_Client_ID = $2 AND IsActive='Y' AND IsTableID='N' AND IsAutoSequence='Y' ORDER BY AD_Client_ID DESC FOR UPDATEOF AD_Sequence AD_Sequence Table Definition ===================== adempiere=# \d ad_sequence Table "adempiere.ad_sequence" Column | Type | Modifiers ----------------+-----------------------------+------------------------------ ad_sequence_id | numeric(10,0) | not null ad_client_id | numeric(10,0) | not null ad_org_id | numeric(10,0) | not null isactive | character(1) | default 'Y'::bpchar created | timestamp without time zone | not null default now() createdby | numeric(10,0) | not null updated | timestamp without time zone | not null default now() updatedby | numeric(10,0) | not null name | character varying(60) | not null description | character varying(255) | vformat | character varying(40) | isautosequence | character(1) | not null default 'Y'::bpchar incrementno | numeric(10,0) | not null startno | numeric(10,0) | not null currentnext | numeric(10,0) | not null currentnextsys | numeric(10,0) | not null isaudited | character(1) | default 'N'::bpchar istableid | character(1) | default 'N'::bpchar prefix | character varying(255) | suffix | character varying(255) | startnewyear | character(1) | default 'N'::bpchar datecolumn | character varying(60) | decimalpattern | character varying(40) | Indexes: "ad_sequence_pkey" PRIMARY KEY, btree (ad_sequence_id) "ad_sequence_name" UNIQUE, btree (ad_client_id, name) Check constraints: "ad_sequence_isactive_check" CHECK (isactive = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])) "ad_sequence_isaudited_check" CHECK (isaudited = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])) "ad_sequence_isautosequence_check" CHECK (isautosequence = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])) "ad_sequence_istableid_check" CHECK (istableid = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])) "ad_sequence_startnewyear_check" CHECK (startnewyear = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])) Foreign-key constraints: "sequenceclient" FOREIGN KEY (ad_client_id) REFERENCES ad_client(ad_client_id) DEFERRABLE INITIALLY DEFERRED "sequenceorg" FOREIGN KEY (ad_org_id) REFERENCES ad_org(ad_org_id) DEFERRABLE INITIALLY DEFERRED With Thanks & Regards: --------------------- Shohorab Hossain ----- Original Message ---- From: Scott Marlowe <scott.marlowe@gmail.com> To: shohorab hossain <shohorab23@yahoo.com> Cc: pgsql-admin@postgresql.org; pgsql-docs@postgresql.org; pgsql-general@postgresql.org Sent: Wednesday, November 18, 2009 2:55:25 Subject: Re: [GENERAL] Postgresql Database Lock Problem Next time this is happening join the pg_lock table to the pg_stat_activity table to see which query is holding the lock for a bazillion milliseconds, while it's happening. That query / statement may give you some clue what's wrong. Get your preferred Email name! Now you can @ymail.com and @rocketmail.com. http://mail.promotions.yahoo.com/newdomains/aa/
В списке pgsql-general по дате отправления: