Wednesday, July 15, 2015

Dropping columns on partitioned tables.

Say you have a partitioned table and you want to add a column. There is no problem - you just add the column to the parent table, and it is added to all the children. But what if you want to drop a column? Then things are no so straightforward. If the child's column was created before it was inherited then it won't be dropped just by dropping it on the parent. So it very much depends on how the child is set up. If you do:
create table child() inherits (parent);
then dropping a column in the parent drops it in the child too. But if you do:
create table child (like parent);
alter table child inherit parent;
then dropping a column in the parent won't drop it in the child. The pg_partman package follows this second pattern when setting up child partitions, as I discovered yesterday when a client ran into this problem. In this case you have to delete the column from the children yourself. I devised the following snippet of code to accomplish this after you have deleted the column from the parent:
do $$
      declare 
        child oid;
      begin
        for child in
            select inhrelid
            from pg_inherits
            where inhparent = 'parent'::regclass
        loop
            execute 'alter table ' || child::regclass ||
                    ' drop column if exists some_column';
        end loop;
      end;
   $$; 

3 comments:

  1. "create table child() like (parent)" -> "create table child() inherit (parent)" I guess. Thanks for the article.

    ReplyDelete
    Replies
    1. Well, pg_partman has good reasons for doing it that way. "like" lets you import various constraints, indexes etc. There's nothing wrong in what they are doing, it's just that there is this little wrinkle.

      Delete
  2. Came across this when planning to drop columns on a partitioned table in postgres 12. When running your script, it came out that the columns already were dropped in the child tables even though they were defined before they were set up as partitions.

    Anyhow, thanks :-)

    ReplyDelete