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 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:
>
> 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
-~----------~----~----~----~------~----~------~--~---
