Re: Re: PostgreSQL:ALTER TABLE command hangs forever when DB partitioning is enabled.

Hi Klaver,

Answer to your questions inline with tag <Sekhar>.

Thanks,
Sekhar

-----Original Message-----
From: Adrian Klaver [mailto:adrian(dot)klaver(at)aklaver(dot)com]
Sent: Thursday, April 21, 2016 7:47 PM
To: Venkatesan, Sekhar; pgsql-sql(at)postgresql(dot)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 | character varying(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 the base 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(dot)klaver(at)aklaver(dot)com