Adding a New Column to a Postgres Partition Master Table

Question

I have a table:

dw_readings(date_key, time_key, account_key, reading_value, consumption, processed_date)

which has been partitioned on date_key by year. I now have need to add a reading_id column to the master table to support new functionality, however using the alter table statement doesn't seem to work correctly. After applying the alter to add the new reading_id column any insert into dw_readings results in reading_id being set to null despite the value being set in the insert statement; this can be reproduced through Java JDBC and through pgAdmin. However update statements setting the reading_id work correctly allowing me to set the column value.

The table is being altered using the following statement

ALTER dw_readings ADD COLUMN reading_id INTEGER;

What I need to know is how to properly add a new column to a partitioned table so that inserts work properly.

Show us the ALTER and INSERT statements you are using.

user330315
– user330315

2012-04-05 13:12:14 +00:00
Commented Apr 5, 2012 at 13:12 — user330315
– user330315, Commented Apr 5, 2012 at 13:12

xstrike · Accepted Answer · 2012-04-20 11:40:49Z

2

I managed to track down the issue in the code. The problem had to do with the rule sets created for the partitions.

    CREATE OR REPLACE RULE dw_readings_insert_y2009 AS
       ON INSERT TO dw_readings
       WHERE new.date_key >= 20090101 AND new.date_key < 20100101
       DO INSTEAD 
    INSERT INTO dw_readings_y2009 (date_key, time_key, account_key, reading_value, consumption, processed_date) 
      VALUES (new.date_key, new.time_key, new.account_key, new.reading_value, new.consumption, new.processed_date);

This rule did not include the new column and so would always insert null for the reading_id. The solution was to add reading_id to the INSERT statement of the DO INSTEAD.

answered Apr 20, 2012 at 11:40

xstrike

311 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Clodoaldo Neto · Accepted Answer · 2012-04-05 12:58:17Z

1

I can't reproduce it. Note that when you create a new column all the pre existing rows will have a null value in that column:

create table dw_readings(date_key date);
create table dw_readings_2012(
    check (extract(year from date_key) = 2012)
)   inherits(dw_readings);
;
insert into dw_readings_2012 (date_key) values ('2012-01-01'::date);
alter table dw_readings add column reading_id integer;
insert into dw_readings_2012 (date_key, reading_id) values ('2012-01-02'::date, 2);
select *
from dw_readings
;
  date_key  | reading_id 
------------+------------
 2012-01-01 |           
 2012-01-02 |          2
(2 rows)

answered Apr 5, 2012 at 12:58

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Comments

Krzysiek · Accepted Answer · 2016-08-31 20:09:42Z

0

As @xstrike mentioned, it should be enough to reload the rules. E.g if you have rune on insert you have to replace it on each partition:

CREATE OR REPLACE RULE table_partition_201608_rule
AS ON INSERT TO table_master WHERE date BETWEEN '2016-08-01 00:00:00.000' AND '2016-08-31 23:59:59.999'
DO INSTEAD (
  INSERT INTO table_partition_201608 VALUES (NEW.*)
);

answered Aug 31, 2016 at 20:09

Krzysiek

6808 silver badges20 bronze badges

1 Comment

Cyberience Over a year ago

Spot the Dungeons and Dragons reference :D

Collectives™ on Stack Overflow

Adding a New Column to a Postgres Partition Master Table

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related