Postgres: could not identify an ordering operator for type ... (for SQLA-created table)
Marcin Krol
Now that I have my PG db filled, I'm encountering this exception while
trying to use it:
ProgrammingError: (ProgrammingError) could not identify an ordering
operator for type virtualization
HINT: Use an explicit ordering operator or modify the query.
'SELECT hosts.id AS hosts_id, hosts."IP" AS "hosts_IP",
hosts."HostName" AS "hosts_HostName", hosts."Location" AS
"hosts_Location", hosts."Architecture_id" AS "hosts_Architecture_id",
hosts."OS_Kind_id" AS "hosts_OS_Kind_id", hosts."OS_version_id" AS
"hosts_OS_version_id", hosts."Additional_info" AS
"hosts_Additional_info", hosts."Column_12" AS "hosts_Column_12",
hosts."Column_13" AS "hosts_Column_13", hosts."Email_id" AS
"hosts_Email_id", hosts."Username" AS "hosts_Username", hosts."Password"
AS "hosts_Password", hosts."Alias" AS "hosts_Alias",
hosts."Virtualization_id" AS "hosts_Virtualization_id",
hosts."Shareable" AS "hosts_Shareable",
hosts."Shareable_between_projects" AS
"hosts_Shareable_between_projects", hosts."Notes" AS "hosts_Notes",
hosts."CPU" AS "hosts_CPU", hosts."RAM" AS "hosts_RAM",
hosts."Column_24" AS "hosts_Column_24", hosts."Batch" AS "hosts_Batch",
hosts."ASSET" AS "hosts_ASSET", hosts."Owner" AS "hosts_Owner",
hosts."SSH_KEY_PRESENT" AS "hosts_SSH_KEY_PRESENT",
hosts."Machine_Type_Model" AS "hosts_Machine_Type_Model",
hosts."MAC_ADDRESS_ETH_0" AS "hosts_MAC_ADDRESS_ETH_0",
hosts."Physical_Box" AS "hosts_Physical_Box", hosts."Up_n_running" AS
"hosts_Up_n_running", hosts."Available" AS "hosts_Available",
hosts."Earliest_reservation_id" AS "hosts_Earliest_reservation_id",
hosts."Project_id" AS "hosts_Project_id", architecture.id AS
architecture_id, architecture."Architecture" AS
"architecture_Architecture", os_kind.id AS os_kind_id, os_kind."OS_Kind"
AS "os_kind_OS_Kind", os_version.id AS os_version_id,
os_version."OS_version" AS "os_version_OS_version", virtualization.id AS
virtualization_id, virtualization."Virtualization" AS
"virtualization_Virtualization", virtualization.color AS
virtualization_color, project.id AS project_id, project."Project" AS
"project_Project" \nFROM hosts, architecture, os_kind, os_version,
virtualization, project, email \nWHERE hosts."Architecture_id" =
architecture.id AND hosts."OS_Kind_id" = os_kind.id AND
hosts."OS_version_id" = os_version.id AND hosts."Email_id" = email.id
AND hosts."Virtualization_id" = virtualization.id AND hosts."Project_id"
= project.id AND hosts."Up_n_running" = %(Up_n_running_1)s AND
hosts."Shareable" = %(Shareable_1)s ORDER BY Virtualization DESC, IP
ASC' {'Shareable_1': True, 'Up_n_running_1': True}
Note the 'ORDER BY Virtualization' clause. The Postgres docs say:
33.13.5. System Dependencies on Operator Classes
"PostgreSQL uses operator classes to infer the properties of operators
in more ways than just whether they can be used with indexes. Therefore,
you might want to create operator classes even if you have no intention
of indexing any columns of your data type.
In particular, there are SQL features such as ORDER BY and DISTINCT that
require comparison and sorting of values. To implement these features on
a user-defined data type, PostgreSQL looks for the default B-tree
operator class for the data type. The "equals" member of this operator
class defines the system's notion of equality of values for GROUP BY and
DISTINCT, and the sort ordering imposed by the operator class defines
the default ORDER BY ordering.
Comparison of arrays of user-defined types also relies on the semantics
defined by the default B-tree operator class.
If there is no default B-tree operator class for a data type, the system
will look for a default hash operator class. But since that kind of
operator class only provides equality, in practice it is only enough to
support array equality.
When there is no default operator class for a data type, you will get
errors like "could not identify an ordering operator" if you try to use
these SQL features with the data type. "
( http://www.postgresql.org/docs/7.4/static/xindex.html )
But I am not using any user-defined data class. It's just a table with
VARCHAR for virtualization.Virtualization column, as created by SQLA:
reservations=# \d virtualization
Table "public.virtualization"
Column | Type | Modifiers
----------------+-------------------+-------------------------------------------------------------
id | integer | not null default
nextval('virtualization_id_seq'::regclass)
Virtualization | character varying |
color | character varying |
Indexes:
"virtualization_pkey" PRIMARY KEY, btree (id)
The SQLA query in question:
selectexpr = session.query(Host, Architecture, OS_Kind, OS_version,
Virtualization, Project)
...
if direction == 'descending':
selectexpr = selectexpr.filter_by(Up_n_running =
True).filter_by(Shareable = True).order_by(desc(field))
else:
selectexpr = selectexpr.filter_by(Up_n_running =
True).filter_by(Shareable = True).order_by(asc(field))
..where field happens to be 'Virtualization' by default.
The above query works flawlessly with Sqlite, it's just Postgres (8.1)
backend that has this problem.
Mike! Help! :-)
Regards,
mk
Michael Bayer
table and column name. im guessing the "field" you send to
order_by() is a string otherwise it would render it as
tablename.columnname and would be quoted for the upper cased
Virtualization.
Michael Bayer
table and column name. im guessing the "field" you send to
order_by() is a string otherwise it would render it as
tablename.columnname and would be quoted for the upper cased
Virtualization.
On Mar 31, 2009, at 5:44 AM, Marcin Krol wrote:
>
Marcin Krol
> my guess is that its confusing the names "virtualization" between the
> table and column name. im guessing the "field" you send to
> order_by() is a string
tedious programming, because column for "order by" comes from web app
input, and so it's obviously most straightforward to use a string...
> otherwise it would render it as
> tablename.columnname and would be quoted for the upper cased
> Virtualization.
It turns out to be Postgres-specific problem, I asked on PG-general
users group and the reply is that it is standard quirk of Postgres:
"Quoting an identifier also makes it case-sensitive, whereas unquoted
names are always folded to lower case"
http://www.postgresql.org/docs/current/interactive/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
This is works-as-designed problem:
reservations=# SELECT * FROM virtualization ORDER BY Virtualization;
ERROR: could not identify an ordering operator for type virtualization
virtualization.Virtualization;
ERROR: column virtualization.virtualization does not exist
But:
reservations=# SELECT * FROM virtualization ORDER BY "Virtualization";
id | Virtualization | color
----+-----------------+---------
1 | BOX | #FAFAFA
17 | BOX | #FAFAFA
9 | BOX ESX HOST | #FAFAFA
7 | BOX MSVS HOST | #FAFAFA
18 | BOX MSVS HOST | #FAFAFA
So there's a humble request/proposal from me for you: would it be
possible for SQLA to always quote column names in ORDER BY clauses for
PG backend, even when it's a string?
In the meantime I will probably just rename the columns to lowercase...
Regards,
mk
Michael Bayer
On Mar 31, 2009, at 11:13 AM, Marcin Krol wrote:
>
> So there's a humble request/proposal from me for you: would it be
> possible for SQLA to always quote column names in ORDER BY clauses for
> PG backend, even when it's a string?
>
> In the meantime I will probably just rename the columns to
> lowercase...
we quote column names that are sent as mixed case, since we assume the
same case sensitivity rules as PG. but if you're sending a string to
ORDER BY, we have no idea what that is. It could be
"call_some_function(virtual.Virtualization, 12)" or something like
that, and we aren't parsing that. You should be sending SQL
expressions and not strings to order_by() if you'd like SQLA to know
something about it.