Re: Re: PostgreSQL:ALTER TABLE command hangs forever when DB partitioning is enabled. - Mailing list pgsql-sql
| From | Venkatesan, Sekhar |
|---|---|
| Subject | Re: Re: PostgreSQL:ALTER TABLE command hangs forever when DB partitioning is enabled. |
| Date | |
| Msg-id | F84DE43FDACD4C45AA84E2DA016FAE2F1C6A810F@MX105CL01.corp.emc.com Whole thread Raw |
| In response to | Re: Re: PostgreSQL:ALTER TABLE command hangs forever when DB partitioning is enabled. (Adrian Klaver <adrian.klaver@aklaver.com>) |
| List | pgsql-sql |
Hi Klaver,
Answer to your questions inline with tag <Sekhar>.
Thanks,
Sekhar
-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Thursday, April 21, 2016 7:47 PM
To: Venkatesan, Sekhar; pgsql-sql@postgresql.org
Cc: Choudhuri, Saurav; Rao, Raghavendra
Subject: Re: [SQL] Re: PostgreSQL:ALTER TABLE command hangs forever when DB partitioning is enabled.
On 04/21/2016 03:23 AM, Venkatesan, Sekhar wrote:
> For DROP_INDEX case, adding the lock_timeout setting to few seconds
> helps in clearing the DB locks and the drop index operation to complete.
>
> For ALTER table hang issue, none of the DB timeout settings are helping.
>
> Need your assistance to identify if there is any known issue when data
> partitioning is enabled in PostgreSQL DB.
You have shown the error, but it would be helpful to know what the setup is:
What are the tables involved in the partitioning scheme?
<Sekhar>: The table on which "alter table" statement is fired is inherited from dm_sysobject_s table.
dm_sysobject_s table definition after partition looks like this:
dm_frepo9_docbase=> \d+ dm_sysobject_s Table "frepo9.dm_sysobject_s" Column
| Type | Modifiers | Storage |
Stats target | Description
-------------------------+-----------------------------+-----------+----------+-
-------------+-------------r_object_id | character(16) | | extended |
|object_name | character varying(255) | | extended | |r_object_type |
charactervarying(32) | | extended | |title | character varying(400) |
| extended | |subject | character varying(192) | | extended |
|a_application_type | character varying(32) | | extended | |a_status
|character varying(16) | | extended | |r_creation_date | timestamp without time zone
| | plain | |r_modify_date | timestamp without time zone | | plain |
|r_modifier | character varying(255) | | extended | |r_access_date
| timestamp without time zone | | plain | |a_is_hidden | smallint
| | plain | |i_is_deleted | smallint | | plain |
|a_retention_date | timestamp without time zone | | plain | |a_archive
| smallint | | plain | |a_compound_architecture | character varying(16)
| | extended | |a_link_resolved | smallint | | plain |
|i_reference_cnt | integer | | plain | |i_has_folder
| smallint | | plain | |r_link_cnt | integer
| | plain | |r_link_high_cnt | integer | | plain |
|r_assembled_from_id | character(16) | | extended | |r_frzn_assembly_cnt
| integer | | plain | |r_has_frzn_assembly | smallint
| | plain | |resolution_label | character varying(32) | | extended |
|r_is_virtual_doc | integer | | plain | |i_contents_id
| character(16) | | extended | |a_content_type | character varying(32)
| | extended | |r_page_cnt | integer | | plain |
|r_content_size | integer | | plain | |a_full_text
| smallint | | plain | |a_storage_type | character varying(64)
| | extended | |i_cabinet_id | character(16) | | extended
| |owner_name | character varying(255) | | extended | |owner_permit
| integer | | plain | |group_name | character
varying(255) | | extended | |group_permit | integer |
|plain | |world_permit | integer | | plain |
|i_antecedent_id | character(16) | | extended | |i_chronicle_id |
character(16) | | extended | |i_latest_flag | smallint |
| plain | |r_lock_owner | character varying(255) | | extended |
|r_lock_date | timestamp without time zone | | plain | |r_lock_machine
|character varying(80) | | extended | |log_entry | character varying(120)
| | extended | |i_branch_cnt | integer | | plain |
|i_direct_dsc | smallint | | plain | |r_immutable_flag
| smallint | | plain | |r_frozen_flag | smallint
| | plain | |r_has_events | smallint | | plain |
|acl_domain | character varying(255) | | extended | |acl_name
| character varying(32) | | extended | |a_special_app | character varying(32)
| | extended | |i_is_reference | smallint | | plain |
|r_creator_name | character varying(255) | | extended | |r_is_public
| smallint | | plain | |r_policy_id | character(16)
| | extended | |r_resume_state | integer | | plain |
|r_current_state | integer | | plain | |r_alias_set_id
| character(16) | | extended | |a_category | character varying(64)
| | extended | |language_code | character varying(5) | | extended |
|a_is_template | smallint | | plain | |a_controlling_app
| character varying(32) | | extended | |r_full_content_size | double precision
| | plain | |a_is_signed | smallint | | plain |
|a_last_review_date | timestamp without time zone | | plain | |i_retain_until
| timestamp without time zone | | plain | |i_partition | integer
| | plain | |i_is_replica | smallint | | plain
| |i_vstamp | integer | | plain | |i_property_bag
| character varying(1024) | | extended | |
Indexes: "d_1f000ffc8000010b" UNIQUE, btree (r_object_id, i_partition) "d_1f000ffc8000000e" btree (i_chronicle_id)
"d_1f000ffc8000000f" btree (object_name) "d_1f000ffc8000002a" btree (r_lock_owner) "d_1f000ffc8000002f" btree
(acl_domain,acl_name) "d_1f000ffc80000032" btree (r_modifier) "d_1f000ffc8000003c" btree (r_policy_id)
"d_1f000ffc8000005d"btree (owner_name) "d_1f000ffc8000023f" btree (r_modify_date)
Triggers: tr_fn_trigg_dm_sysobject_s BEFORE INSERT ON dm_sysobject_s FOR EACH ROW EXEC
UTE PROCEDURE trigg_dm_sysobject_s()
Child tables: my_ptype_122243_s, my_ptype_523356_s, my_ptype_557657_s,
my_ptype_585555_s, my_ptype_657416_s, my_ptype_980715_s, p1_dm_sysobject_s,
p2_dm_sysobject_s, p3_dm_sysobject_s, p4_dm_sysobject_s
How is the partitioning been done?
<Sekhar>: We have DB trigger functions implemented to move the data to the right partitions when rows are inserted in
thebase tables based on check constraints defined on each partitioned tables.
dm_sysobject_s table is partitioned into 4 tables.
p1_dm_sysobject_s: Check constraints: "chk_2" CHECK (i_partition < 2)
Inherits: dm_sysobject_s
p2_dm_sysobject_s: Check constraints: "chk_5" CHECK (i_partition >= 2 AND i_partition < 5)
Inherits: dm_sysobject_s
p3_dm_sysobject_s: Check constraints: "chk_8" CHECK (i_partition >= 5 AND i_partition < 8)
Inherits: dm_sysobject_s
p4_dm_sysobject_s:
Check constraints: "chk_10" CHECK (i_partition >= 8 AND i_partition < 10)
Inherits: dm_sysobject_s
Child tables: my_ptype_54001_s
How are the statements being sent to server?
<Sekhar>: I don't understand what you mean by "how statements are being sent to server?" What specific information you
need?
In particular I am seeing a BEGIN on the DDL statements, is that coming from you or the client software?
<Sekhar>: AS I am aware, It's not coming from my application. Does that have to do with postgres client ?
>
> Thanks,
> Sekhar
--
Adrian Klaver
adrian.klaver@aklaver.com