Thread: Auto-partitioning in PostgreSQL 10

Auto-partitioning in PostgreSQL 10

From
zafiirah jumeen
Date:

Hello,

 

I was trying to do auto partitioning in PostgreSQL 10.

First of all, I created 2 tables t_dossier_bac_history_insert_table and t_dossier_bac_history_sensor_collections.

And then, I created a trigger which would execute a function (which would create my partitions) before inputting data in t_dossier_bac_history_insert_table.

But, I am having an error which is as follows when I try to insert data from an existing table to t_dossier_bac_history_insert_table:

 

ERROR: query string argument of EXECUTE is null CONTEXT: PL/pgSQL function sensor_partition() line 27 at EXECUTE SQL state: 22004

 

Could you please help me in resolving this issue.

Please see below for part of my codes.

Thank you in advance.

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE t_dossier_bac_history_insert_table

(

    id_dossier_bac_history character(32) COLLATE pg_catalog."default" NOT NULL,

    boo_supprime boolean,

    dat_create timestamp without time zone DEFAULT timezone('utc'::text, now()),

    dat_supprime timestamp without time zone,

    dat_update timestamp without time zone,

    num_version bigint NOT NULL,

    dat_date_entree timestamp without time zone,

    dat_date_sortie timestamp without time zone,

    id_bac character(32) COLLATE pg_catalog."default" NOT NULL,

    id_dossier character(32) COLLATE pg_catalog."default" NOT NULL,

    boo_en_migration boolean DEFAULT false

 

)

WITH (

    OIDS = FALSE

)

TABLESPACE pg_default;

 

 

CREATE TABLE t_dossier_bac_history_sensor_collections

(

    id_dossier_bac_history character(32) COLLATE pg_catalog."default" NOT NULL,

    boo_supprime boolean,

    dat_create timestamp without time zone DEFAULT timezone('utc'::text, now()),

    dat_supprime timestamp without time zone,

    dat_update timestamp without time zone,

    num_version bigint NOT NULL,

    dat_date_entree timestamp without time zone,

    dat_date_sortie timestamp without time zone,

    id_bac character(32) COLLATE pg_catalog."default" NOT NULL,

    id_dossier character(32) COLLATE pg_catalog."default" NOT NULL,

    boo_en_migration boolean DEFAULT false

 

)

PARTITION BY LIST (id_bac)

WITH (

    OIDS = FALSE

)

TABLESPACE pg_default;

 

 

CREATE TRIGGER insert_to_t_dossier_bac_history_sensor_collections

BEFORE INSERT

ON t_dossier_bac_history_insert_table

FOR EACH ROW

EXECUTE PROCEDURE sensor_partition();

 

 

 

CREATE OR REPLACE FUNCTION sensor_partition()

RETURNS TRIGGER AS $$

DECLARE

sensor_table TEXT;

new_table TEXT;

new_insert TEXT;

BEGIN

sensor_table='id_bac_'||NEW.id_bac;

 

               IF NOT EXISTS (SELECT relname FROM pg_class                      --CHECK IF TABLE 'sensor_table' exists. If not, create the table.

                                                   WHERE relname=sensor_table) THEN

                                                   RAISE NOTICE 'Creating Partition:%', sensor_table;

                                                  

                                                   new_table := 'CREATE TABLE '|| sensor_table                                  --Table does not exists, create table/partition

                                                                 || ' PARTITION OF t_dossier_bac_history_sensor_collections' || ' (id_dossier_bac_history, dat_create, dat_supprime, dat_update, num_version, dat_date_entree, dat_date_sortie, id_bac, id_dossier, boo_en_migration)'

                                                                                                                || ' FOR VALUES IN ( '''|| NEW.id_bac ||''' ) ;';

                                                                                                               

                                                                                                               

                                                               

                                                                                                                 

                                                   EXECUTE new_table;

                                                   ELSE

               new_table:= "The table exist already";                                         --Table already exists, do not create table

               END IF;

                                                  

                                                  

                                                   new_insert := 'INSERT INTO t_dossier_bac_history_sensor_collections VALUES('''

                                                                                                                || NEW.id_dossier_bac_history ||''', '

                                                                                                                || NEW.boo_supprime ||', '''

                                                                                                                || NEW.dat_create ||''','' '

                                                                                                                || NEW.dat_supprime ||''','' '

                                                                                                                || NEW.dat_update ||''', '

                                                                                                                || NEW.num_version ||','' '

                                                                                                                || NEW.dat_date_entree ||''','' '

                                                                                                   || NEW.dat_date_sortie ||''','' '

                                                                                                                || NEW.id_bac ||''','' '

                                                                                                                || NEW.id_dossier ||''', '

                                                                                                                || NEW.boo_en_migration ||');';

                                                                               

                                                                                                                --RAISE NOTICE 'Creating Partition:%', NEW.id_dossier_bac_history;

                                                  EXECUTE new_insert;

                                                  -- RETURN NULL;

END;

$$ LANGUAGE plpgsql VOLATILE;

 

 

INSERT INTO t_dossier_bac_history_insert_table

SELECT * FROM t_dossier_bac_history;

Re: Auto-partitioning in PostgreSQL 10

From
Mark Dilger
Date:
> On Jun 25, 2018, at 3:00 AM, zafiirah jumeen <zafiirahjumeen@gmail.com> wrote:
>
> Hello,
>
> I was trying to do auto partitioning in PostgreSQL 10.
> First of all, I created 2 tables t_dossier_bac_history_insert_table and t_dossier_bac_history_sensor_collections.
> And then, I created a trigger which would execute a function (which would create my partitions) before inputting data
int_dossier_bac_history_insert_table. 
> But, I am having an error which is as follows when I try to insert data from an existing table to
t_dossier_bac_history_insert_table:
>
> ERROR: query string argument of EXECUTE is null CONTEXT: PL/pgSQL function sensor_partition() line 27 at EXECUTE SQL
state:22004 
>
> Could you please help me in resolving this issue.


The variable 'new_insert' will be null when any of the new columns
are null, because concatenation of null with anything else renders
a null result.  You could look at the documentation for the function
named COALESCE() and see how to use that to prevent the 'new_insert'
variable from becoming null.

There may be broader problems in the general design of your solution,
too.

I think you should post questions of this sort to pgsql-general
rather than here.

> Please see below for part of my codes.
> Thank you in advance.
>
>
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> CREATE TABLE t_dossier_bac_history_insert_table
> (
>     id_dossier_bac_history character(32) COLLATE pg_catalog."default" NOT NULL,
>     boo_supprime boolean,
>     dat_create timestamp without time zone DEFAULT timezone('utc'::text, now()),
>     dat_supprime timestamp without time zone,
>     dat_update timestamp without time zone,
>     num_version bigint NOT NULL,
>     dat_date_entree timestamp without time zone,
>     dat_date_sortie timestamp without time zone,
>     id_bac character(32) COLLATE pg_catalog."default" NOT NULL,
>     id_dossier character(32) COLLATE pg_catalog."default" NOT NULL,
>     boo_en_migration boolean DEFAULT false
>
> )
> WITH (
>     OIDS = FALSE
> )
> TABLESPACE pg_default;
>
>
> CREATE TABLE t_dossier_bac_history_sensor_collections
> (
>     id_dossier_bac_history character(32) COLLATE pg_catalog."default" NOT NULL,
>     boo_supprime boolean,
>     dat_create timestamp without time zone DEFAULT timezone('utc'::text, now()),
>     dat_supprime timestamp without time zone,
>     dat_update timestamp without time zone,
>     num_version bigint NOT NULL,
>     dat_date_entree timestamp without time zone,
>     dat_date_sortie timestamp without time zone,
>     id_bac character(32) COLLATE pg_catalog."default" NOT NULL,
>     id_dossier character(32) COLLATE pg_catalog."default" NOT NULL,
>     boo_en_migration boolean DEFAULT false
>
> )
> PARTITION BY LIST (id_bac)
> WITH (
>     OIDS = FALSE
> )
> TABLESPACE pg_default;
>
>
> CREATE TRIGGER insert_to_t_dossier_bac_history_sensor_collections
> BEFORE INSERT
> ON t_dossier_bac_history_insert_table
> FOR EACH ROW
> EXECUTE PROCEDURE sensor_partition();
>
>
>
> CREATE OR REPLACE FUNCTION sensor_partition()
> RETURNS TRIGGER AS $$
> DECLARE
> sensor_table TEXT;
> new_table TEXT;
> new_insert TEXT;
> BEGIN
> sensor_table='id_bac_'||NEW.id_bac;
>
>                IF NOT EXISTS (SELECT relname FROM pg_class                      --CHECK IF TABLE 'sensor_table'
exists.If not, create the table. 
>                                                    WHERE relname=sensor_table) THEN
>                                                    RAISE NOTICE 'Creating Partition:%', sensor_table;
>
>                                                    new_table := 'CREATE TABLE '|| sensor_table
         --Table does not exists, create table/partition 
>                                                                  || ' PARTITION OF
t_dossier_bac_history_sensor_collections'|| ' (id_dossier_bac_history, dat_create, dat_supprime, dat_update,
num_version,dat_date_entree, dat_date_sortie, id_bac, id_dossier, boo_en_migration)' 
>                                                                                                                 || '
FORVALUES IN ( '''|| NEW.id_bac ||''' ) ;'; 
>
>
>
>
>                                                    EXECUTE new_table;
>                                                    ELSE
>                new_table:= "The table exist already";                                         --Table already exists,
donot create table 
>                END IF;
>
>
>                                                    new_insert := 'INSERT INTO
t_dossier_bac_history_sensor_collectionsVALUES(''' 
>                                                                                                                 ||
NEW.id_dossier_bac_history||''', ' 
>                                                                                                                 ||
NEW.boo_supprime||', ''' 
>                                                                                                                 ||
NEW.dat_create||''','' ' 
>                                                                                                                 ||
NEW.dat_supprime||''','' ' 
>                                                                                                                 ||
NEW.dat_update||''', ' 
>                                                                                                                 ||
NEW.num_version||','' ' 
>                                                                                                                 ||
NEW.dat_date_entree||''','' ' 
>                                                                                                    ||
NEW.dat_date_sortie||''','' ' 
>                                                                                                                 ||
NEW.id_bac||''','' ' 
>                                                                                                                 ||
NEW.id_dossier||''', ' 
>                                                                                                                 ||
NEW.boo_en_migration||');'; 
>
>
--RAISENOTICE 'Creating Partition:%', NEW.id_dossier_bac_history; 
>                                                   EXECUTE new_insert;
>                                                   -- RETURN NULL;
> END;
> $$ LANGUAGE plpgsql VOLATILE;
>
>
> INSERT INTO t_dossier_bac_history_insert_table
> SELECT * FROM t_dossier_bac_history;

mark