Unable to migrate data from different schema

[Darkbladecr]

Following the offline migration steps from the how-to, unfortunately the procedure partition_data_proc() fails if both the p_parent_table and p_source_table are not from the same schema.

Example script and docker setup:

CREATE SCHEMA partman;
CREATE EXTENSION pg_partman SCHEMA partman;

CREATE SCHEMA org;

CREATE TABLE org.original_table (
    id bigint not null
    , col2 text not null
    , col3 timestamptz DEFAULT now()
    , col4 TEXT
);

CREATE INDEX ON org.original_table (id);

INSERT INTO org.original_table (id, col2, col3, col4) VALUES (generate_series(1,100000), 'stuff'||generate_series(1,100000), now(), 'stuff');

ALTER TABLE org.original_table RENAME to old_nonpartitioned_table;

CREATE SCHEMA test;

CREATE TABLE test.original_table (
    id bigint not null
    , col2 text not null
    , col3 timestamptz DEFAULT now()
    , col4 text)
PARTITION BY RANGE (id);

CREATE INDEX ON test.original_table (id);

SELECT partman.create_parent(
    p_parent_table := 'test.original_table'
    , p_control := 'id'
    , p_interval := '10000'
);

CALL partman.partition_data_proc(
    p_parent_table := 'test.original_table'
    , p_loop_count := 200
    , p_interval := '1000'
    , p_source_table := 'org.old_nonpartitioned_table'
);

Error:

ERROR:  relation "org.original_table_p0" does not exist
LINE 3:         INSERT INTO org.original_table_p0 (id,col2,col3,col4...
                            ^
QUERY:  WITH partition_data AS (
            DELETE FROM ONLY org.old_nonpartitioned_table WHERE id >= 0 AND id < 1001 RETURNING *)
        INSERT INTO org.original_table_p0 (id,col2,col3,col4) SELECT id,col2,col3,col4 FROM partition_data
CONTEXT:  PL/pgSQL function partman.partition_data_id(text,integer,bigint,numeric,text,boolean,text,text[]) line 215 at EXECUTE
SQL statement "SELECT partman.partition_data_id (p_parent_table := 'test.original_table', p_lock_wait := '0', p_order := 'ASC', p_analyze := false, p_batch_interval := '1000', p_source_table := 'org.old_nonpartitioned_table')"
PL/pgSQL function partman.partition_data_proc(text,integer,text,integer,integer,integer,text,text,text[],boolean) line 86 at EXECUTE

Works fine if they are both from the same schema though.

[keithf4]

Yup, I think I see the problem. Let me see if I can get a fix in for the next release.

[keithf4]

This was hopefully a very easy fix. Surprised that bug had been around for so long with no one saying anything, so thank you!

If you have a chance, I have a beta release up for testing that has a fix for this in it. Please see the PR for instructions on how to install it. Please do not install on to your production systems!

#615

[Darkbladecr]

great thank you, will try it out!

[Darkbladecr]

Can confirm bug is fixed in the 5.1.0-beta and tested with the initial SQL script posted using the docker container from my repo:
https://github.com/Darkbladecr/pg_partman/tree/pg_partman5.1.0

[keithf4]

Thank you!

[keithf4]

Version 5.1 has been released with this fix