ordering operator for bytea

From: Michael McCarthy <michael(at)tcsi(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: ordering operator for bytea
Date: 2000-01-18 04:14:36
Message-ID: Pine.GSO.3.96.1000117195237.5007F-100000@sanmarino.tcsi.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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 modify the 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(at)tcsi(dot)com 1080 Marina Village Parkway
(510) 749-8739 Alameda, CA 94501

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2000-01-18 05:06:04 Re: [SQL] ordering operator for bytea
Previous Message Tom Lane 2000-01-17 22:26:57 Re: [SQL] index usage ... strange !?