hash partitions by character varying column

[baiyinqiqi]

hi!
I create hash partitions by column of character varying(40) ,then i query through the partition key,an error occurs like this:
ERROR: missing comparison function for types text & character varying
my statement is select * from tb_idm_mapping where auid = 'Zpm6sMkqF8ScmD9C6Kgn',
and auid is the partition key.
my pg version is postgresql-9.6.0.

[pensnarik]

Have the same issue. Maybe it's because only integer columns supperted by hash partitioning mode?

[zilder]

Hi @baiyinqiqi, @pensnarik,

pg_pathman uses information from operator family (pg_amproc) to find comparison function between two types used in restriction clause. And it turns out that there is no such function between text and character varying:

select * from pg_amproc where amproclefttype='text'::regtype and amprocrighttype='character varying'::regtype;
 amprocfamily | amproclefttype | amprocrighttype | amprocnum | amproc 
--------------+----------------+-----------------+-----------+--------
(0 rows)

But since we know that text and varchar (and some other types) are binary coercible we could use the same function that we would use to compare two text's. @funbringer is now working on this, I believe it will be fixed in 1.2.

Have the same issue. Maybe it's because only integer columns supperted by hash partitioning mode?

Actually you can use text type as a partitioning key.

[funbringer]

Hi @baiyinqiqi, @pensnarik

Thanks for the report. I've managed to fix this in rel_1_2_beta (you could give it a try).

Actually, this bug had nothing to do with HASH partitioning. There was a flaw in function handle_binary_opexpr(), which is essential for clause evaluation. The flaw lead to execution of a RANGE-related piece of code that happened to be broken as well.