-
Notifications
You must be signed in to change notification settings - Fork 69
Description
Hey, I got a issue when I used pg_pathman, I'm not sure if it's a bug. Hope the Following steps are helpful, thanks.
1. Env
RHEL 6.3
PostgreSQL 10.2
pg_pathman10-1.4.9
2. Steps
First, I created a table, partitioned it, and disabled the parent table.
app_db=# show pg_pathman.enable_auto_partition;
pg_pathman.enable_auto_partition
----------------------------------
off
(1 row)
app_db=# drop table public.tb1 cascade;
NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to sequence tb1_seq
drop cascades to table p_201807
DROP TABLE
app_db=# create table public.tb1
app_db-# (
app_db(# day date not null,
app_db(# c2 int,
app_db(# c3 int
app_db(# );
CREATE TABLE
app_db=# select create_range_partitions('public.tb1', 'day', '2018-6-19'::date, interval '1 day', 0, false);
create_range_partitions
-------------------------
0
(1 row)
app_db=# select set_enable_parent('public.tb1',false);
set_enable_parent
-------------------
(1 row)
app_db=# select * from pathman_config_params;
partrel | enable_parent | auto | init_callback | spawn_using_bgw
---------+---------------+------+---------------+-----------------
tb1 | f | t | | f
(1 row)
Then, I added a patition with a name which exceeded 49 bytes.
app_db=# select add_range_partition('public.tb1', '2018-8-1'::date, '2018-8-31'::date, 'p_01234567890123456789012345678901234567890123456789');
add_range_partition
------------------------------------------------------
p_01234567890123456789012345678901234567890123456789
(1 row)
app_db=# show pg_pathman.enable;
pg_pathman.enable
-------------------
on
(1 row)
When I selected data from this table, I got the error below. According to this error message, pg_pathman needed a check named pathman_p_01234567890123456789012345678901234567890123456789_check. Then I queried from pg_constraint and got a constraint named pathman_p_01234567890123456789012345678901234567890123456789_ch, it was similar with what pg_pathman needed.
There is a description about the length of a databse object identifier in PostgreSQL Doc, if an object's name exceeds 63 bytes, it will be truncated. So, I suppose pg_pathman's check is truncated by pg, that's why it cann't find it.
The system uses no more than NAMEDATALEN-1 bytes of an identifier; longer names can be written
in commands, but they will be truncated. By default, NAMEDATALEN is 64 so the maximum identifier
length is 63 bytes. If this limit is problematic, it can be raised by changing the NAMEDATALEN constant
in src/include/pg_config_manual.h
--PostgreSQL 10.5 Documentation(Page 31).
app_db=# select * from public.tb1;
ERROR: constraint "pathman_p_01234567890123456789012345678901234567890123456789_check" of partition "p_01234567890123456789012345678901234567890123456789" does not exist
HINT: pg_pathman will be disabled to allow you to resolve this issue
app_db=# show pg_pathman.enable;
pg_pathman.enable
-------------------
off
(1 row)
app_db=#
app_db=# select * from pathman_partition_list;
ERROR: constraint "pathman_p_01234567890123456789012345678901234567890123456789_check" of partition "p_01234567890123456789012345678901234567890123456789" does not exist
HINT: pg_pathman will be disabled to allow you to resolve this issue
app_db=#
app_db=#
app_db=# select conname from pg_constraint;
conname
-----------------------------------------------------------------
cardinal_number_domain_check
yes_or_no_check
pathman_config_parttype_check
pathman_config_interval_check
pathman_config_pkey
pathman_config_params_init_callback_check
pathman_config_params_pkey
pathman_p_01234567890123456789012345678901234567890123456789_ch
(8 rows)
app_db=# select drop_range_partition('p_01234567890123456789012345678901234567890123456789');
drop_range_partition
------------------------------------------------------
p_01234567890123456789012345678901234567890123456789
(1 row)
When pg_pathman went wrong, it also disabled "pg_pathman.enable" in the session in which the select executed.
app_db=# show pg_pathman.enable;
pg_pathman.enable
-------------------
off
(1 row)
app_db=# set pg_pathman.enable = on;
NOTICE: RuntimeAppend, RuntimeMergeAppend and PartitionFilter nodes and some other options have been enabled
SET
app_db=# select * from pathman_partition_list;
parent | partition | parttype | expr | range_min | range_max
--------+-----------+----------+------+-----------+-----------
(0 rows)
app_db=#
3. pg_pathman-1.5.2
I did the same test in pg_pathman-1.5.2, its behavior was little bit different that "pg_pathman.enable" wasn't be disabled and "HINT: pg_pathman will be disabled to allow you to resolve this issue" was not present. Maybe you noticed this issue and made some change I guess.
app_db=# select * from public.tb1;
ERROR: constraint "pathman_p_01234567890123456789012345678901234567890123456789_check" of partition "p_01234567890123456789012345678901234567890123456789" does not exist
app_db=# show pg_pathman.enable;
pg_pathman.enable
-------------------
on
(1 row)
But is it more reasonable that pg_pathman checkes the length of partition name when a patition is added? If it exceeds the limit value, just raise a error and exit. Or is it doable? Thanks.