Errors while adding new range partitions

[VerusK]

Now it's useless for new incoming data. For example I want to split my data by day(86400 sec), partition should be created every 86400 seconds but now it's just throws exception.

[funbringer]

@VerusK Could you show us the exception?

[VerusK]

I've tried to call select append_range_partition, it added one new partition and now I'm getting this:
NOTICE: Appending new partition...
CONTEXT: SQL statement "SELECT public.append_partition_internal($1, $2, $3, ARRAY[]::int4[])"
PL/pgSQL function append_range_partition(regclass) line 16 at EXECUTE
ERROR: lock main 33917 is not held
CONTEXT: SQL statement "SELECT public.release_partitions_lock()"
PL/pgSQL function append_range_partition(regclass) line 30 at PERFORM

********** Error **********

ERROR: lock main 33917 is not held
SQL state: XX000
Context: SQL statement "SELECT public.release_partitions_lock()"
PL/pgSQL function append_range_partition(regclass) line 30 at PERFORM

[VerusK]

When I'm disabling partitioning and then enabling it again, append works fine just first time

[VerusK]

Found one more bug:
I have last partition where:
CONSTRAINT public_clicks_240_check CHECK (ts_spawn >= 1466035200 AND ts_spawn < 1466121600)

when I'm trying to call SELECT add_range_partition('clicks', 1466121600, 1466121600 + 86400); I'm getting error: [P0001] ERROR: Specified range overlaps with existing partitions P0001

then I called ``SELECT add_range_partition('clicks', 1468972800, 1468972800 + 86400);` - far in the future - it worked, then I called my first add_range_partition and it worked as well.

[zilder]

Hi,
I've tried to reproduce your last error and couldn't do it -- append worked fine for me. I assume in your case it was due to the previous error (the one with release_partitions_lock). Without getting into to much details about implementation, I think pg_pathman's cache was in inconsistent state after release_partitions_lock error and it falsely believed that new partition was actually created but it wasn't. We'll take a closer look at what causes errors and will fix it. Thanks for your report!

[VerusK]

@zilder I've restarted PG and tried to call again SELECT add_range_partition('clicks', 1466121600, 1466121600 + 86400);
this time pg_pathman created multiple tables with the same constraint, but still no luck with: select append_range_partition('clicks');

[zilder]

@VerusK maybe you could upload a dump of your original table 'clicks' somewhere on dropbox or google drive so we could reproduce your case step by step? If it's not classified of course : )

[zilder]

@VerusK I've commited the patch. It won't solve the problem but at least will clarify the cause of the error. Could you please try to pull new version, rebuild it and repeat all your steps. To apply changes you need to recreate extension:

select drop_range_partitions('clicks'); -- will collect all the data back to parent table and drop partitions
drop extension pg_pathman;
create extension pg_pathman;

[VerusK]

@zilder will do, just a little bit later today.

[funbringer]

@VerusK are you still being affected by this issue?

[VerusK]

@funbringer just tried new code. Now I'm getting: [P0001] ERROR: syntax error at or near "AND" 42601 when calling select append_range_partition('clicks');

[funbringer]

@VerusK have you restarted the server?