Thread: ordering operator for bytea
Given the following schema and query (to PQexec) we get an error from postgres (postmaster debug log shown): Table = asn1octtable15545 +------------------------------+------------------------------+-------+ | Field | Type | Length| +------------------------------+------------------------------+-------+ | parent_oid_class | int4 | 4 | | parent_oid_inst_ms | int4 | 4 | | parent_oid_inst_ls | int4 | 4 | | parent_oid_tomid | int4 | 4 | | attr_code | int4 | 4 | | item_index | int4 | 4 | | octet_string | bytea | var | +------------------------------+------------------------------+-------+ Index: asn1octtable15545_x StartTransactionCommand query: BEGIN ProcessUtility: BEGIN CommitTransactionCommand StartTransactionCommand query: DECLARE osp_cursor CURSOR FOR select item_index, octet_string, parent_oid_inst_ms, parent_oid_inst_ls from asn1octtable15545 where (parent_oid_class = 5000 and attr_code = 5023 and parent_oid_tomid = 15545 and parent_oid_inst_ms = 1018757128 and parent_oid_inst_ls = 948163998) or (parent_oid_class = 5000 and attr_code = 5023 and parent_oid_tomid = 15545 and parent_oid_inst_ms = 1018757127 and parent_oid_inst_ls = 948163998) order by parent_oid_inst_ms ASC, parent_oid_inst_ls ASC, item_index ASC ERROR: Unable to identify an ordering operator '<' for type 'bytea' Use an explicit ordering operator or modifythe query AbortCurrentTransaction StartTransactionCommand query: COMMIT ProcessUtility: COMMIT CommitTransactionCommand Strangely, we tried the same query in psql, and it works fine; we can get this error message in psql only if we include the octet_string (bytea) column in the order by clause, which is not our intent. any_ordering_op (where this error message originates) is called two places in transformSortClause; are there any known bugs in this area? We have only seen this problem for tables that contain a bytea column, but we need to be able to store unprintable characters, and to order rows from such a table. Your insight, please! Thanks, //\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\ Michael McCarthy TCSI Corporation michael@tcsi.com 1080 Marina Village Parkway (510) 749-8739 Alameda, CA 94501
Michael McCarthy <michael@tcsi.com> writes:
> Given the following schema and query (to PQexec) we get an error from
> postgres (postmaster debug log shown):
> query: DECLARE osp_cursor CURSOR FOR select item_index, octet_string,
> parent_oid_inst_ms, parent_oid_inst_ls from asn1octtable15545 where
> (parent_oid_class = 5000 and attr_code = 5023 and parent_oid_tomid =
> 15545 and parent_oid_inst_ms = 1018757128 and parent_oid_inst_ls =
> 948163998) or (parent_oid_class = 5000 and attr_code = 5023 and
> parent_oid_tomid = 15545 and parent_oid_inst_ms = 1018757127 and
> parent_oid_inst_ls = 948163998) order by parent_oid_inst_ms ASC,
> parent_oid_inst_ls ASC, item_index ASC
> ERROR: Unable to identify an ordering operator '<' for type 'bytea'
> Use an explicit ordering operator or modify the query
> Strangely, we tried the same query in psql, and it works fine;
Are you by chance running with KSQO enabled in your application?
Your query looks to me like the kind that KSQO would trigger on;
and if it triggers, it transforms the query into a UNION. UNION
requires a DISTINCT pass, which requires sorting, which requires
an ordering operator --- and bytea hasn't got one.
There isn't any real good reason for bytea not to have comparison
operators, AFAIK ... it's just that no one has gotten 'round to
writing them. If you have a strong need to have KSQO turned on,
I'd suggest writing up some comparators for bytea using memcmp.
(Please contribute them if you do ;-).)
> only seen this problem for tables that contain a bytea column, but we need
> to be able to store unprintable characters, and to order rows from such a
> table.
text shouldn't have any problem with "unprintable" characters other than
null (\0); if you can live without storing nulls, switching to text
might be the path of least resistance.
regards, tom lane