pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michael McCarthy <michael@tcsi.com>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] ordering operator for bytea
Date: Tue, 18 Jan 2000 00:06:04 -0500
Message-ID: <29119.948171964@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.GSO.3.96.1000117195237.5007F-100000@sanmarino.tcsi.com>
References: <Pine.GSO.3.96.1000117195237.5007F-100000@sanmarino.tcsi.com>

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



view thread (2+ messages)

Message-ID: <29119.948171964@sss.pgh.pa.us>
Permalink:  ../29119.948171964@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/29119.948171964@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, michael@tcsi.com
  Subject: Re: [SQL] ordering operator for bytea
  In-Reply-To: <29119.948171964@sss.pgh.pa.us>
  References: <Pine.GSO.3.96.1000117195237.5007F-100000@sanmarino.tcsi.com> <29119.948171964@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox