partition_table_concurrently

[megadawn]

Hi,
How do I use it exactly?
1.create table
2.SELECT public.create_range_partitions('partitioned_table', 'time_key', '2016-10-27'::date, '1 day'::interval, 1);
3.select public.partition_table_concurrently('partitioned_table', 10000);

that's it? I don't see anything in SELECT * FROM public.show_concurrent_part_tasks();

cheers

[funbringer]

hi again,

No, there's no need to call partition_table_concurrently() in this case. Whenever you call create_range_partitions(), the data is moved to partitions automatically, provided that partition_data BOOLEAN arg is set to true.

I don't see anything in SELECT * FROM public.show_concurrent_part_tasks()

Exactly, it shut down almost immediately after you spawned it. The data is already spread.

[megadawn]

in what case it should be called explicitly then?
We have a problem with a "classic" partitioning. while inserting data (not sorted by date) simultaneously from 170 connections to the one partitioned table we seem to have locking problem

[megadawn]

Just checked partitions created with the insert automatically, it made a few tables for each date inspite of 1 day interval:
alter table meter_read_delta_pm_28
add constraint pathman_meter_read_delta_pm_28_2_check
check (time_key >= '2016-11-02 00:00:00'::timestamp without time zone and time_key < '2016-11-03 00:00:00'::timestamp without time zone);
alter table meter_read_delta_pm_29
add constraint pathman_meter_read_delta_pm_29_2_check
check (time_key >= '2016-11-02 00:00:00'::timestamp without time zone and time_key < '2016-11-03 00:00:00'::timestamp without time zone);
.....
etc

then I checked tables.....

select count() --69354767 rows
from meter_read_delta_pm --parent table
where time_key >= DATE '2016-11-02'
and time_key < DATE '2016-11-03'
;
select count() --119474551 rows
from meter_read_delta_pm_28
where time_key >= DATE '2016-11-02'
and time_key < DATE '2016-11-03'
;
select count(*) --69354767 rows
from meter_read_delta_pm_29
where time_key >= DATE '2016-11-02'
and time_key < DATE '2016-11-03'
;

select count(*) --153 rows
from meter_read_delta_pm_26
where time_key >= DATE '2016-11-02'
and time_key < DATE '2016-11-03'
;
is that because we loading our data with many connections? Or I just don't get how to use it properly...

[megadawn]

SELECT * FROM public.show_partition_list();

"parent","partition","parttype","partattr","range_min","range_max"
"meter_read_delta_pm","meter_read_delta_pm_26",2,"time_key","2016-11-02 00:00:00","2016-11-03 00:00:00"
"meter_read_delta_pm","meter_read_delta_pm_27",2,"time_key","2016-11-02 00:00:00","2016-11-03 00:00:00"
"meter_read_delta_pm","meter_read_delta_pm_28",2,"time_key","2016-11-02 00:00:00","2016-11-03 00:00:00"
"meter_read_delta_pm","meter_read_delta_pm_29",2,"time_key","2016-11-02 00:00:00","2016-11-03 00:00:00"

explain analyze
select count(*) --69354767
from meter_read_delta_pm
where time_key >= DATE '2016-11-02'
and time_key < DATE '2016-11-03'
;
Seq Scan on meter_read_delta_pm_29 (cost=0.00..1753153.38 rows=48275238 width=0) (actual time=0.029..15396.532 rows=69354767 loops=1)');
so it takes the data from _29 only for some reason

[funbringer]

it made a few tables for each date inspite of 1 day interval:

Thanks for your report! This will be fixed ASAP. Please, wait for the release 1.2, it won't take long.

[funbringer]

in what case it should be called explicitly then?

This feature is useful if locking entire table is not an option. When you partition table concurrently, you can still modify it (e.g. INSERTs), because a special worker will lock small batches of rows and move them to partitions. See issue #38.

[megadawn]

well, it seems this is our case - we insert the data simultaneously from 170 connections into one partitioned table
If so, what should I do? partition_data in create_partitions should be false or..?

[funbringer]

well, it seems this is our case - we insert the data simultaneously from 170 connections into one partitioned table

What makes you think it's your case? You don't need to repartiton table every N minutes, right? Once the table has been partitioned, the new data will be automatically inserted into the corresponding partitions. If there's no suitable partition, pg_pathman is able to either spawn a new one (there is a bug related to concurrent access that you've run into, but it will be fixed in a new release) or abort the transaction. You can change the behavior any time.

But what about the data in parent? If you've partitioned the table with partition_data:=false, it goes nowhere. That's why you might want to call partition_table_concurrently().

I doubt that this is your case.

[megadawn]

I tried both partition_data:=false and true, don't see any difference, both copies into parent and children so I get 2 copies of all data

[funbringer]

I tried both partition_data:=false and true, don't see any difference, both copies into parent and children so I get 2 copies of all data

I strongly believe that you do a wrong thing, but I can't tell the exact reason of such a behavior. Perhaps you could tell us more about your queries and your actions. Otherwise it's hard to tell.

[megadawn]

all sorted