ERROR: wrong constraint format for HASH partition

[erhuabushuo]

Problem description

Explain your problem here (it's always better to provide reproduction steps) ...

I created hash partition, when I migrate the data to partition table, I got:

ERROR: wrong constraint format for HASH partition
HINT: pg_pathman will be disabled to allow you to resolve this issue

Environment

[funbringer]

I'm sorry, but we need more details to be able to help you. You should have used our issue template.

Which version of pg_pathman you're using?
Which version of PostgreSQL you're using?
How did you create your partitioned table (sql queries)?
How did you migrate your data (sql queries)?

[funbringer]

Feel free to add more information here, and I'll reopen this issue.

[ohmycto]

Hi! I have just the same issue:

ERROR: wrong constraint format for HASH partition <part_name>
HINT: pg_pathman will be disabled to allow you to resolve this issue

When I do \d <part_name> I can see the following check:

"pathman_<part_name>_0_check" CHECK (get_hash_part_idx(hashint4(<part_key>), 100) = 0)

I did everything right in the same way on another (test) database with similar scheme and env, and it works there! I get this error in production only, although checks on partitions are identical.

Here's my environment:

SELECT * FROM pg_extension;
    extname    | extowner | extnamespace | extrelocatable | extversion |      extconfig      | extcondition
---------------+----------+--------------+----------------+------------+---------------------+--------------
 plpgsql       |       10 |           11 | f              | 1.0        |                     |
 btree_gin     |       10 |         2200 | t              | 1.0        |                     |
 dblink        |       10 |        17225 | t              | 1.1        |                     |
 fuzzystrmatch |       10 |        17225 | t              | 1.0        |                     |
 intarray      |       10 |         2200 | t              | 1.0        |                     |
 pgstattuple   |       10 |         2200 | t              | 1.1        |                     |
 pg_trgm       |       10 |         2200 | t              | 1.3        |                     |
 btree_gist    |    16384 |        17225 | t              | 1.2        |                     |
 hstore        |       10 |         2200 | t              | 1.4        |                     |
 pg_pathman    |    16384 |        17225 | f              | 1.4        | {46492080,46492091} | {"",""}
 pg_repack     |       10 |         2200 | f              | 1.4.2      |                     |
(11 строк)

SELECT version();
                                                 version
----------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit
(1 строка)

SELECT get_pathman_lib_version();
 get_pathman_lib_version
-------------------------
 10409
(1 строка)

Thanks.

[ohmycto]

Here's exactly what I did:

SELECT create_hash_partitions('visitors', 'account_id', 100, FALSE);
\d+ visitors -- to make sure that there are 100 child partitions
SELECT partition_table_concurrently('visitors', 10000, 1.0);

ERROR: wrong constraint format for HASH partition visitors_0
HINT: pg_pathman will be disabled to allow you to resolve this issue

[funbringer]

I guess the issue is confirmed.

[funbringer]

@secoint, I couldn't reproduce your example. A heisenbug, perhaps? Could you try one more time, close session after ERROR message is raised, reconnect and perform a select from this table?

[ohmycto]

@funbringer I've tried several times already, within different days. The problem is that I can reproduce the issue only in production, and I can't do it again and again on production db.

UPDATE: the other table in the same database has been hash-partitioned successfully! So the problem seems to be not in the database/extension/env, but in table itself, right? The table is huge (86 GB). Any thoughts how to handle this?

[funbringer]

The problem is that I can reproduce the issue only in production, and I can't do it again and again on production db.

That's ok, we shouldn't mess with huge tables, what I meant is that you could do a few experiments with a new tiny table (~10 partitions, maybe a few rows) :)

So the problem seems to be not in the database/extension/env, but in table itself, right?

That's a big question. Maybe there's something wrong with validate_hash_constraint(). It's hard to tell, since we fail to reproduce this issue consistently.

[funbringer]

Any thoughts how to handle this?

I think that simple reconnect could solve this problem. I'll investigate and post the results here.

[ohmycto]

Thanks, @funbringer, I'll try to find a way to reproduce the issue consistently.

I will also try to repeat the procedure and reset the session as you suggested, but I can do it during the low load period (saturday night, maybe).

[ohmycto]

Hi @funbringer,

Could you try one more time, close session after ERROR message is raised, reconnect and perform a select from this table?

I tried to reconnect after receiving an error, but this didn't change anything. After reconnecting and trying to start SELECT partition_table_concurrently I got the same error.

[funbringer]

I tried to reconnect after receiving an error, but this didn't change anything.

Could you show the conbin field of the broken constraint's row from pg_constraint?

SELECT conbin FROM pg_constraint WHERE conname = 'NAME_OF_CONSTRAINT';

[ohmycto]

I can't unfortunaterly because this was production table and I rolled everything back ASAP. Still can't reproduce it on any other (test) table.

P.S. May be I'll make another attempt in upcoming days and copy conbin information.

[ohmycto]

Hi @funbringer ,

Now I got the conbin output as you requested:

SELECT conbin FROM pg_constraint WHERE conname = 'pathman_visitors_0_check';
                                                                                                                                                                                                                                                                                                                                                                                                                        conbin
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {OPEXPR :opno 96 :opfuncid 65 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 46492176 :funcresulttype 23 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 450 :funcresulttype 23 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 7 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 7 :location 8}) :location -1} {CONST :consttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location -1 :constvalue 4 [ 100 0 0 0 0 0 0 0 ]}) :location -1} {CONST :consttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location -1 :constvalue 4 [ 0 0 0 0 0 0 0 0 ]}) :location -1}
(1 строка)

[funbringer]

Hi @secoint, @erhuabushuo

I've pushed a fix to master. I'd be glad to publish a new release if it helps.

[ohmycto]

Wow, thanks! Looking forward to a new release with this fix!

[funbringer]

1.4.11 is now available.