Partition with check constraint with "like"
Hi,
I am trying to create partitions on the table based on first letter of the
column record value using inherit relation & check constraint.
Somehow able to create and load the data into the tables as per my requirement.
But the problem is when querying the data on that partitioned column, it's
referring to all the children's tables instead of the matching table.
create table t1(id int,name text);
CREATE TABLE partition_tab.t1_name_null( CONSTRAINT null_check CHECK (name IS
NULL)) INHERITS (t1); CREATE or replace FUNCTION
partition_tab.func_t1_insert_trigger() RETURNS trigger LANGUAGE 'plpgsql' COST
100 VOLATILE NOT LEAKPROOFAS $BODY$DECLARE chk_cond text; c_table TEXT;
c_table1 text; new_name text; m_table1 text; BEGIN if ( NEW.name is null)
THEN INSERT into partition_tab.t1_name_null VALUES (NEW.*); elseif ( NEW.name
is not null) THEN new_name:= substr(NEW.name,1,1); raise info 'new_name
%',new_name; c_table := TG_TABLE_NAME || '_' || new_name; c_table1 :=
'partition_tab.' || c_table; m_table1 := ''||TG_TABLE_NAME; IF NOT
EXISTS(SELECT relname FROM pg_class WHERE relname=lower(c_table)) THEN RAISE
NOTICE 'values out of range partition, creating partition table:
partition_tab.%',c_table;
chk_cond := new_name||'%'; raise info 'chk_cond %',chk_cond;
EXECUTE 'CREATE TABLE partition_tab.' || c_table || '(check ( name like '''||
chk_cond||''')) INHERITS (' ||TG_TABLE_NAME|| ');';
END IF; EXECUTE 'INSERT INTO ' || c_table1 || ' SELECT(' || m_table1 || ' '
|| quote_literal(NEW) || ').* RETURNING id;'; END IF; RETURN NULL; END;$BODY$;
CREATE TRIGGER t1_trigger BEFORE INSERT OR UPDATE ON t1 FOR EACH ROW EXECUTE
PROCEDURE partition_tab.func_t1_insert_trigger()
examples: Postgres 11 | db<>fiddle
|
|
| |
Postgres 11 | db<>fiddle
Free online SQL environment for experimenting and sharing.
|
|
|
Any suggestions.
Thanks,Rj
Re: Partition with check constraint with "like"
On Fri, 21 May 2021 at 12:32, Nagaraj Raj wrote:
> I am trying to create partitions on the table based on first letter of the
> column record value using inherit relation & check constraint.
You'll get much better performance out of native partitioning than you
will with the old inheritance method of doing it.
> EXECUTE 'CREATE TABLE partition_tab.' || c_table || '(check ( name like
> '''|| chk_cond||''')) INHERITS (' ||TG_TABLE_NAME|| ');';
This is a bad idea. There's a lock upgrade hazard here that could end
up causing deadlocks on INSERT. You should just create all the tables
you need beforehand.
I'd recommend you do this using RANGE partitioning. For example:
create table mytable (a text not null) partition by range (a);
create table mytable_a partition of mytable for values from ('a') to
('b'); -- note the upper bound of the range is non-inclusive.
create table mytable_b partition of mytable for values from ('b') to ('c');
insert into mytable values('alpha'),('bravo');
explain select * from mytable where a = 'alpha';
QUERY PLAN
---
Seq Scan on mytable_a mytable (cost=0.00..27.00 rows=7 width=32)
Filter: (a = 'alpha'::text)
(2 rows)
The mytable_b is not scanned.
David
Re: Partition with check constraint with "like"
Thank you. This is a great help.
But "a" have some records with alpha and numeric.
example :
insert into mytable values('alpha'),('bravo');
insert into mytable values('1lpha'),('2ravo');
On Thursday, May 20, 2021, 06:23:14 PM PDT, David Rowley
wrote:
On Fri, 21 May 2021 at 12:32, Nagaraj Raj wrote:
> I am trying to create partitions on the table based on first letter of the
> column record value using inherit relation & check constraint.
You'll get much better performance out of native partitioning than you
will with the old inheritance method of doing it.
> EXECUTE 'CREATE TABLE partition_tab.' || c_table || '(check ( name like
>'''|| chk_cond||''')) INHERITS (' ||TG_TABLE_NAME|| ');';
This is a bad idea. There's a lock upgrade hazard here that could end
up causing deadlocks on INSERT. You should just create all the tables
you need beforehand.
I'd recommend you do this using RANGE partitioning. For example:
create table mytable (a text not null) partition by range (a);
create table mytable_a partition of mytable for values from ('a') to
('b'); -- note the upper bound of the range is non-inclusive.
create table mytable_b partition of mytable for values from ('b') to ('c');
insert into mytable values('alpha'),('bravo');
explain select * from mytable where a = 'alpha';
QUERY PLAN
---
Seq Scan on mytable_a mytable (cost=0.00..27.00 rows=7 width=32)
Filter: (a = 'alpha'::text)
(2 rows)
The mytable_b is not scanned.
David
Re: Partition with check constraint with "like"
On Fri, May 21, 2021 at 02:36:14AM +, Nagaraj Raj wrote:
> Thank you. This is a great help.
> But "a" have some records with alpha and numeric.
So then you should make one or more partitions FROM ('1')TO('9').
> example :
> insert into mytable values('alpha'),('bravo');
> insert into mytable values('1lpha'),('2ravo');
>
>
> On Thursday, May 20, 2021, 06:23:14 PM PDT, David Rowley
> wrote:
>
> On Fri, 21 May 2021 at 12:32, Nagaraj Raj wrote:
> > I am trying to create partitions on the table based on first letter of the
> > column record value using inherit relation & check constraint.
>
> You'll get much better performance out of native partitioning than you
> will with the old inheritance method of doing it.
>
> > EXECUTE 'CREATE TABLE partition_tab.' || c_table || '(check ( name like
> >'''|| chk_cond||''')) INHERITS (' ||TG_TABLE_NAME|| ');';
>
> This is a bad idea. There's a lock upgrade hazard here that could end
> up causing deadlocks on INSERT. You should just create all the tables
> you need beforehand.
>
> I'd recommend you do this using RANGE partitioning. For example:
>
> create table mytable (a text not null) partition by range (a);
> create table mytable_a partition of mytable for values from ('a') to
> ('b'); -- note the upper bound of the range is non-inclusive.
> create table mytable_b partition of mytable for values from ('b') to ('c');
> insert into mytable values('alpha'),('bravo');
>
> explain select * from mytable where a = 'alpha';
> QUERY PLAN
> ---
> Seq Scan on mytable_a mytable (cost=0.00..27.00 rows=7 width=32)
> Filter: (a = 'alpha'::text)
> (2 rows)
>
> The mytable_b is not scanned.
Re: Partition with check constraint with "like"
On Thu, May 20, 2021, 8:38 PM Justin Pryzby wrote:
> On Fri, May 21, 2021 at 02:36:14AM +, Nagaraj Raj wrote:
> > Thank you. This is a great help.
> > But "a" have some records with alpha and numeric.
>
> So then you should make one or more partitions FROM ('1')TO('9').
>
What about 0? Sorry.
Seriously though, this seems like a dumb question but if I wanted a
partition for each numeric digit and each alpha character (upper and
lowercase?) And wanted to avoid using a default partition, how would I use
minvalue and maxvalue and determine which partition of
A to B
B to C
...
a to b
b to c
...
0 to 1
Etc... And how to figure out the gaps between 9 and A or z and A or what?
I hope the nature of my question makes sense. What is the ordering of the
characters as far as partitioning goes? Or rather, how would I figure that
out?
>
