Hello everyone,
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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
