Inheritance table no longer does 'no inherit'

[rsquaretrade]

Hello.

We've moved most of our tables to native partitioning except for one table. Since upgrading to Postgresql 14.8, we noticed that once a table is moved to the archive schema, pg_partman does not do a "NO INHERIT" so the table is left in the parent table and we have to manually do an ALTER TABLE NO INHERIT the parent. Is this expected behavior? Thanks in advance.

[keithf4]

Are you using the built-in retention mechanism to move the old tables to a different schema?

[rsquaretrade]

Yes.

[keithf4]

Can you please post the entire entry in the pg_config config table for this partition set? SELECT * from pg_config WHERE parent_table = 'parent_table_name';

Can you also provide the complete schema by running \d+ parent_table_name?

[rsquaretrade]

Here is the schema info \d+

                                                                     Table "mmdm.wic_raw_log"
         Column             │           Type           │ Collation │ Nullable │             Default              │ Storage  │ Compression │ Stats target │ Description 

────────────────────────────────┼──────────────────────────┼───────────┼──────────┼──────────────────────────────────┼──────────┼─────────────┼──────────────┼───────────── wic_raw_log_id │ bigint │ │ not null │ generated by default as identity │ plain │ │ │ wic_file_info_id │ bigint │ │ │ │ plain │ │ │ order_id │ text │ │ │ │ extended │ │ │ merchant_name │ text │ │ │ │ extended │ │ │ transaction_timestamp │ timestamp with time zone │ │ │ │ plain │ │ │ buyer_id │ text │ │ │ │ extended │ │ │ buyer_email │ text │ │ │ │ extended │ │ │ buyer_first_name │ text │ │ │ │ extended │ │ │ buyer_last_name │ text │ │ │ │ extended │ │ │ buyer_phone_number │ text │ │ │ │ extended │ │ │ buyer_street_address_1 │ text │ │ │ │ extended │ │ │ buyer_street_address_2 │ text │ │ │ │ extended │ │ │ buyer_city │ text │ │ │ │ extended │ │ │ buyer_state │ text │ │ │ │ extended │ │ │ buyer_postal_code │ text │ │ │ │ extended │ │ │ buyer_country │ text │ │ │ │ extended │ │ │ item_line_item_id │ text │ │ │ │ extended │ │ │ item_merchant_sku │ text │ │ │ │ extended │ │ │ item_square_trade_sku │ text │ │ │ │ extended │ │ │ item_description │ text │ │ │ │ extended │ │ │ item_quantity │ integer │ │ │ │ plain │ │ │ unit_price │ numeric(15,2) │ │ │ │ main │ │ │ unit_price_currency │ text │ │ │ │ extended │ │ │ tax_amount │ numeric(15,2) │ │ │ │ main │ │ │ payment_id │ text │ │ │ │ extended │ │ │ payment_method │ text │ │ │ │ extended │ │ │ is_warranty │ boolean │ │ │ │ plain │ │ │ is_item │ boolean │ │ │ │ plain │ │ │ is_cancelled │ boolean │ │ │ │ plain │ │ │ is_valid │ boolean │ │ │ │ plain │ │ │ is_acknowledged │ boolean │ │ │ │ plain │ │ │ status │ text │ │ │ │ extended │ │ │ status_reason │ text │ │ │ │ extended │ │ │ retry_count │ integer │ │ │ │ plain │ │ │ created │ timestamp with time zone │ │ not null │ now() │ plain │ │ │ created_by │ text │ │ │ │ extended │ │ │ updated │ timestamp with time zone │ │ │ now() │ plain │ │ │ updated_by │ text │ │ │ │ extended │ │ │ order_checkout_status │ text │ │ │ │ extended │ │ │ external_transaction_id │ text │ │ │ │ extended │ │ │ external_transaction_timestamp │ timestamp with time zone │ │ │ │ plain │ │ │ external_transaction_amount │ numeric(15,2) │ │ │ │ main │ │ │ external_transaction_fee │ numeric(15,2) │ │ │ │ main │ │ │ is_shipped │ boolean │ │ │ │ plain │ │ │ payload │ jsonb │ │ │ │ extended │ │ │ item_id │ text │ │ │ │ extended │ │ │ serial_number │ text │ │ │ │ extended │ │ │ manufacturer_name │ text │ │ │ │ extended │ │ │ item_category │ text │ │ │ │ extended │ │ │ item_model │ text │ │ │ │ extended │ │ │ item_carrier │ text │ │ │ │ extended │ │ │ item_color │ text │ │ │ │ extended │ │ │ store_register_number │ text │ │ │ │ extended │ │ │ purchase_order_number │ text │ │ │ │ extended │ │ │ store_id │ text │ │ │ │ extended │ │ │ sales_representative_id │ text │ │ │ │ extended │ │ │ memory_capacity │ text │ │ │ │ extended │ │ │ part_id │ text │ │ │ │ extended │ │ │ item_phone_number │ text │ │ │ │ extended │ │ │ instance_id │ text │ │ │ │ extended │ │ │ item_condition │ text │ │ │ │ extended │ │ │ campaign_code │ text │ │ │ │ extended │ │ │ referrer_type │ text │ │ │ │ extended │ │ │ referrer_detail │ text │ │ │ │ extended │ │ │ event_type │ text │ │ │ │ extended │ │ │ event_id │ text │ │ │ │ extended │ │ │ mask_account_number │ text │ │ │ │ extended │ │ │ credit_card_type │ text │ │ │ │ extended │ │ │ expiration_month │ integer │ │ │ │ plain │ │ │ expiration_year │ integer │ │ │ │ plain │ │ │ is_discount │ boolean │ │ │ │ plain │ │ │ discount_type │ text │ │ │ │ extended │ │ │ discount_amount │ numeric(15,2) │ │ │ │ main │ │ │ discount_code │ text │ │ │ │ extended │ │ │ manufacturer_warranty_term │ integer │ │ │ │ plain │ │ │ manufacturer_warranty_unit │ text │ │ │ │ extended │ │ │ total_price │ numeric(15,2) │ │ │ │ main │ │ │ total_tax_amount │ numeric(15,2) │ │ │ │ main │ │ │ membership_number │ text │ │ │ │ extended │ │ │ membership_type │ text │ │ │ │ extended │ │ │ company_name │ text │ │ │ │ extended │ │ │ warranty_end_date │ timestamp with time zone │ │ │ │ plain │ │ │ is_cancellation_email_enabled │ boolean │ │ │ │ plain │ │ │ is_carrier_financed │ boolean │ │ │ │ plain │ │ │ reprocess_status │ text │ │ │ │ extended │ │ │ merchant_upc │ text │ │ │ │ extended │ │ │ cancellation_reason_code │ text │ │ │ │ extended │ │ │ cancellation_reason │ text │ │ │ │ extended │ │ │ is_manual_refund │ boolean │ │ │ │ plain │ │ │ merchant_id │ text │ │ │ │ extended │ │ │ Indexes: "wic_raw_log_pkey" PRIMARY KEY, btree (wic_raw_log_id) "wic_raw_log_item_line_item_id_is_valid_idx" btree (item_line_item_id, is_valid) "wic_raw_log_items_merchant_sku_idx" btree (item_merchant_sku) "wic_raw_log_merchant_id_idx" btree (merchant_id) "wic_raw_log_order_id_item_line_item_id_idx" btree (order_id, item_line_item_id) "wic_raw_log_status_idx" btree (status) "wic_raw_log_updated_idx" btree (updated) "wic_raw_log_warranty_creation_file_info_id_idx" btree (wic_file_info_id) Foreign-key constraints: "wic_raw_log_wic_file_info_id_fkey" FOREIGN KEY (wic_file_info_id) REFERENCES mmdm.wic_file_info(wic_file_info_id) Triggers: wic_raw_log_part_trig BEFORE INSERT ON mmdm.wic_raw_log FOR EACH ROW EXECUTE FUNCTION mmdm.wic_raw_log_part_trig_func() wic_raw_log_update_timestamp_bu_trg BEFORE UPDATE ON mmdm.wic_raw_log FOR EACH ROW EXECUTE FUNCTION update_timestamp() Child tables: mmdm_archive.wic_raw_log_p2023_06, mmdm.wic_raw_log_p2023_07, mmdm.wic_raw_log_p2023_08, mmdm.wic_raw_log_p2023_09, mmdm.wic_raw_log_p2023_10, mmdm.wic_raw_log_p2023_11, mmdm.wic_raw_log_p2023_12, mmdm.wic_raw_log_p2024_01, mmdm.wic_raw_log_p2024_02, mmdm.wic_raw_log_p2024_03 Access method: heap

[rsquaretrade]

Parent partman.part_config info:

postgresql://postgres@[local:/var/run/postgresql/14/svc_t1b_prod]:5432 13:36:59 mmdm=> select * from partman.part_config limit 10; ─[ RECORD 1 ]──────────────┬───────────────── parent_table │ mmdm.wic_raw_log control │ created partition_type │ partman partition_interval │ 1 mon constraint_cols │ premake │ 3 optimize_trigger │ 4 optimize_constraint │ 30 epoch │ none inherit_fk │ t retention │ 4 months retention_schema │ mmdm_archive retention_keep_table │ f retention_keep_index │ f infinite_time_partitions │ t datetime_string │ YYYY_MM automatic_maintenance │ on jobmon │ f sub_partition_set_full │ f undo_in_progress │ f trigger_exception_handling │ f upsert │ trigger_return_null │ t template_table │ publications │ inherit_privileges │ t constraint_valid │ t subscription_refresh │ drop_cascade_fk │ f ignore_default_data │ f

[rsquaretrade]

Replication partman.part_config info:

postgresql://postgres@[local:/var/run/postgresql/14/warehouse_prod]:5432 13:38:48 warehouse=> select * from partman.part_config where parent_table='mmdm.wic_raw_log'; ─[ RECORD 1 ]──────────────┬───────────────── parent_table │ mmdm.wic_raw_log partition_type │ partman partition_interval │ 1 mon control │ created constraint_cols │ premake │ 3 inherit_fk │ t retention │ 24 months retention_schema │ mmdm_archive retention_keep_table │ f retention_keep_index │ f datetime_string │ YYYY_MM automatic_maintenance │ on jobmon │ f undo_in_progress │ f sub_partition_set_full │ f epoch │ none optimize_trigger │ 4 optimize_constraint │ 30 infinite_time_partitions │ t trigger_exception_handling │ f upsert │ trigger_return_null │ t template_table │ publications │ inherit_privileges │ t constraint_valid │ t subscription_refresh │

[keithf4]

Apologies on the delay getting back to you on this.

I've tried replicating this so far on PG 15.4. So I'd imagine if upgrading the PG version from a version prior to 14.8 was part of the issue this would show it. I'll try and get 14.8 specifically running to test it there as well.

What was the old version of PG you upgraded from? What version of pg_partman are you running? This is with 4.7.4.

Things seem to be working as expected. Made a simpler table with just the control column.

github590=# select partman.create_parent('mmdm.wic_raw_log', 'created', 'partman', 'monthly', p_premake := 3, p_start_partition := '2023-01-01 12:00:00');
 create_parent 
---------------
 t
(1 row)

github590=# \d+ mmdm.wic_raw_log
                                                 Table "mmdm.wic_raw_log"
 Column  |           Type           | Collation | Nullable | Default | Storage | Compression | Stats target | Description 
---------+--------------------------+-----------+----------+---------+---------+-------------+--------------+-------------
 created | timestamp with time zone |           | not null |         | plain   |             |              | 
Triggers:
    wic_raw_log_part_trig BEFORE INSERT ON mmdm.wic_raw_log FOR EACH ROW EXECUTE FUNCTION mmdm.wic_raw_log_part_trig_func()
Child tables: mmdm.wic_raw_log_p2023_01,
              mmdm.wic_raw_log_p2023_02,
              mmdm.wic_raw_log_p2023_03,
              mmdm.wic_raw_log_p2023_04,
              mmdm.wic_raw_log_p2023_05,
              mmdm.wic_raw_log_p2023_06,
              mmdm.wic_raw_log_p2023_07,
              mmdm.wic_raw_log_p2023_08,
              mmdm.wic_raw_log_p2023_09,
              mmdm.wic_raw_log_p2023_10,
              mmdm.wic_raw_log_p2023_11,
              mmdm.wic_raw_log_p2023_12,
              mmdm.wic_raw_log_p2024_01,
              mmdm.wic_raw_log_p2024_02,
              mmdm.wic_raw_log_p2024_03
Access method: heap

github590=# update partman.part_config set retention = '4 months', retention_schema = 'mmdm_archive', retention_keep_table = 'false', retention_keep_index = 'false';
UPDATE 1
github590=# create schema mmdm_archive;
CREATE SCHEMA
github590=# begin;
BEGIN
github590=*# select partman.run_maintenance();
 run_maintenance 
-----------------

(1 row)

github590=*# \d+ mmdm.wic_raw_log
                                                 Table "mmdm.wic_raw_log"
 Column  |           Type           | Collation | Nullable | Default | Storage | Compression | Stats target | Description 
---------+--------------------------+-----------+----------+---------+---------+-------------+--------------+-------------
 created | timestamp with time zone |           | not null |         | plain   |             |              | 
Triggers:
    wic_raw_log_part_trig BEFORE INSERT ON mmdm.wic_raw_log FOR EACH ROW EXECUTE FUNCTION mmdm.wic_raw_log_part_trig_func()
Child tables: mmdm.wic_raw_log_p2023_08,
              mmdm.wic_raw_log_p2023_09,
              mmdm.wic_raw_log_p2023_10,
              mmdm.wic_raw_log_p2023_11,
              mmdm.wic_raw_log_p2023_12,
              mmdm.wic_raw_log_p2024_01,
              mmdm.wic_raw_log_p2024_02,
              mmdm.wic_raw_log_p2024_03
Access method: heap

github590=*# \dt mmdm_archive.*
                  List of relations
    Schema    |         Name         | Type  | Owner 
--------------+----------------------+-------+-------
 mmdm_archive | wic_raw_log_p2023_01 | table | keith
 mmdm_archive | wic_raw_log_p2023_02 | table | keith
 mmdm_archive | wic_raw_log_p2023_03 | table | keith
 mmdm_archive | wic_raw_log_p2023_04 | table | keith
 mmdm_archive | wic_raw_log_p2023_05 | table | keith
 mmdm_archive | wic_raw_log_p2023_06 | table | keith
 mmdm_archive | wic_raw_log_p2023_07 | table | keith
(7 rows)

Config:

github590=*# select * from partman.part_config;
-[ RECORD 1 ]--------------+-----------------
parent_table               | mmdm.wic_raw_log
control                    | created
partition_type             | partman
partition_interval         | 1 mon
constraint_cols            | 
premake                    | 3
optimize_trigger           | 4
optimize_constraint        | 30
epoch                      | none
inherit_fk                 | t
retention                  | 4 months
retention_schema           | mmdm_archive
retention_keep_table       | f
retention_keep_index       | f
infinite_time_partitions   | f
datetime_string            | YYYY_MM
automatic_maintenance      | on
jobmon                     | t
sub_partition_set_full     | f
undo_in_progress           | f
trigger_exception_handling | f
upsert                     | 
trigger_return_null        | t
template_table             | 
publications               | 
inherit_privileges         | t
constraint_valid           | t
subscription_refresh       | 
drop_cascade_fk            | f
ignore_default_data        | f

[keithf4]

If you haven't upgraded to the latest version 5 and still need to run the 4.x series, please make sure you update to the latest release (4.7.4). After reviewing release notes again, this was actually a bug that was fixed in version 4.7.1.

[rsquaretrade]

Thank you for that info, we will look into upgrading.