problematic view definition

I should have mentioned this is on

PostgreSQL 8.4.5 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real (Debian 4.4.5-4) 4.4.5, 32-bit

Karsten

On Wed, Feb 09, 2011 at 11:12:01PM +0100, Karsten Hilbert wrote:

Date: Wed, 9 Feb 2011 23:12:01 +0100
From: Karsten Hilbert <Karsten.Hilbert@gmx.net>
To: pgsql-general <pgsql-general@postgresql.org>
Subject: [GENERAL] problematic view definition
User-Agent: Mutt/1.5.20 (2009-06-14)

Hi all !

Attached find some table and view definitions from the
GNUmed (www.gnumed.de) database.

Unfortunately I do not understand why PostgreSQL says

psql:xx.sql:14: ERROR: could not implement UNION
DETAIL: Some of the datatypes only support hashing, while others only support sorting.

when I say

select * from dem.v_message_inbox;

I mean, I (hope I) do understand what PostgreSQL tries to
tell me but I don't know how to find out which columns are
affected ...

Thanks !

Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

Output format is wrapped.
Expanded display is on.
Table "dem.message_inbox"
Column | Type | Modifiers
--------------------+--------------------------+-----------------------------------------------------------------------
pk_audit | integer | not null default nextval('audit.audit_fields_pk_audit_seq'::regclass)
row_version | integer | not null default 0
modified_when | timestamp with time zone | not null default now()
modified_by | name | not null default "current_user"()
pk | integer | not null default nextval('dem.provider_inbox_pk_seq'::regclass)
fk_staff | integer |
fk_inbox_item_type | integer | not null
comment | text |
data | text |
importance | smallint | default 0
fk_patient | integer |
ufk_context | integer[] |
Indexes:
"provider_inbox_pkey" PRIMARY KEY, btree (pk)
Check constraints:
"message_must_have_recipient" CHECK ((fk_staff IS NULL AND fk_patient IS NULL) IS FALSE)
"provider_inbox_comment_check" CHECK (btrim(COALESCE(comment, 'xxxDEFAULTxxx'::text)) <> ''::text)
"provider_inbox_importance_check" CHECK (importance = (-1) OR importance = 0 OR importance = 1)
Foreign-key constraints:
"message_inbox_fk_patient_fkey" FOREIGN KEY (fk_patient) REFERENCES dem.identity(pk) ON UPDATE CASCADE ON DELETE RESTRICT
"provider_inbox_fk_inbox_item_type_fkey" FOREIGN KEY (fk_inbox_item_type) REFERENCES dem.inbox_item_type(pk)
"provider_inbox_fk_staff_fkey" FOREIGN KEY (fk_staff) REFERENCES dem.staff(pk)
Triggers:
tr_message_inbox_generic_mod AFTER INSERT OR DELETE OR UPDATE ON dem.message_inbox DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE dem.trf_announce_message_inbox_generic_mod_no_pk()
tr_message_inbox_mod AFTER INSERT OR DELETE OR UPDATE ON dem.message_inbox DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE dem.trf_announce_message_inbox_mod()
zt_del_message_inbox BEFORE DELETE ON dem.message_inbox FOR EACH ROW EXECUTE PROCEDURE audit.ft_del_message_inbox()
zt_ins_message_inbox BEFORE INSERT ON dem.message_inbox FOR EACH ROW EXECUTE PROCEDURE audit.ft_ins_message_inbox()
zt_upd_message_inbox BEFORE UPDATE ON dem.message_inbox FOR EACH ROW EXECUTE PROCEDURE audit.ft_upd_message_inbox()
Inherits: audit.audit_fields

View "dem.v_message_inbox"
Column | Type | Modifiers
--------------------+--------------------------+-----------
received_when | timestamp with time zone |
provider | text |
importance | integer |
category | text |
l10n_category | text |
type | text |
l10n_type | text |
comment | text |
pk_context | integer[] |
data | text |
pk_inbox_message | integer |
pk_staff | integer |
pk_category | integer |
pk_type | integer |
pk_patient | integer |
is_virtual | boolean |
xmin_message_inbox | xid |
View definition:
( ( SELECT mi.modified_when AS received_when, ( SELECT staff.short_alias
FROM dem.staff
WHERE staff.pk = mi.fk_staff) AS provider, mi.importance, vit.category, vit.l10n_category, vit.type, vit.l10n_type, mi.comment, mi.ufk_context AS pk_context, mi.data, mi.pk AS pk_inbox_message, mi.fk_staff AS pk_staff, vit.pk_category, mi.fk_inbox_item_type AS pk_type, mi.fk_patient AS pk_patient, false AS is_virtual, mi.xmin AS xmin_message_inbox
FROM dem.message_inbox mi, dem.v_inbox_item_type vit
WHERE mi.fk_inbox_item_type = vit.pk_type
UNION
SELECT now() AS received_when, ( SELECT staff.short_alias
FROM dem.staff
WHERE staff.pk = vo4dnd.pk_intended_reviewer) AS provider, 0 AS importance, 'clinical' AS category, _('clinical'::text) AS l10n_category, 'review docs' AS type, _('review docs'::text) AS l10n_type, ( SELECT ((((_('unreviewed documents for patient'::text) || ' ['::text) || dn.lastnames) || ', '::text) || dn.firstnames) || ']'::text
FROM dem.names dn
WHERE dn.id_identity = vo4dnd.pk_patient AND dn.active IS TRUE) AS comment, NULL::integer[] AS pk_context, NULL::text AS data, NULL::integer AS pk_inbox_message, vo4dnd.pk_intended_reviewer AS pk_staff, ( SELECT v_inbox_item_type.pk_category
FROM dem.v_inbox_item_type
WHERE v_inbox_item_type.type = 'review docs'::text) AS pk_category, ( SELECT v_inbox_item_type.pk_type
FROM dem.v_inbox_item_type
WHERE v_inbox_item_type.type = 'review docs'::text) AS pk_type, vo4dnd.pk_patient, true AS is_virtual, NULL::xid AS xmin_message_inbox
FROM blobs.v_obj4doc_no_data vo4dnd
WHERE vo4dnd.reviewed IS FALSE)
UNION
SELECT now() AS received_when, ( SELECT staff.short_alias
FROM dem.staff
WHERE staff.pk = vtr.pk_intended_reviewer) AS provider, 0 AS importance, 'clinical' AS category, _('clinical'::text) AS l10n_category, 'review results' AS type, _('review results'::text) AS l10n_type, ( SELECT ((((_('unreviewed (normal) results for patient'::text) || ' ['::text) || dn.lastnames) || ', '::text) || dn.firstnames) || ']'::text
FROM dem.names dn
WHERE dn.id_identity = vtr.pk_patient AND dn.active IS TRUE) AS comment, NULL::integer[] AS pk_context, NULL::text AS data, NULL::integer AS pk_inbox_message, vtr.pk_intended_reviewer AS pk_staff, ( SELECT v_inbox_item_type.pk_category
FROM dem.v_inbox_item_type
WHERE v_inbox_item_type.type = 'review results'::text) AS pk_category, ( SELECT v_inbox_item_type.pk_type
FROM dem.v_inbox_item_type
WHERE v_inbox_item_type.type = 'review results'::text) AS pk_type, vtr.pk_patient, true AS is_virtual, NULL::xid AS xmin_message_inbox
FROM clin.v_test_results vtr
WHERE vtr.reviewed IS FALSE AND (vtr.is_technically_abnormal IS FALSE OR vtr.is_technically_abnormal IS NULL AND vtr.abnormality_indicator IS NULL))
UNION
SELECT now() AS received_when, ( SELECT staff.short_alias
FROM dem.staff
WHERE staff.pk = vtr.pk_intended_reviewer) AS provider, 1 AS importance, 'clinical' AS category, _('clinical'::text) AS l10n_category, 'review results' AS type, _('review results'::text) AS l10n_type, ( SELECT ((((_('unreviewed (abnormal) results for patient'::text) || ' ['::text) || dn.lastnames) || ', '::text) || dn.firstnames) || ']'::text
FROM dem.names dn
WHERE dn.id_identity = vtr.pk_patient AND dn.active IS TRUE) AS comment, NULL::integer[] AS pk_context, NULL::text AS data, NULL::integer AS pk_inbox_message, vtr.pk_intended_reviewer AS pk_staff, ( SELECT v_inbox_item_type.pk_category
FROM dem.v_inbox_item_type
WHERE v_inbox_item_type.type = 'review results'::text) AS pk_category, ( SELECT v_inbox_item_type.pk_type
FROM dem.v_inbox_item_type
WHERE v_inbox_item_type.type = 'review results'::text) AS pk_type, vtr.pk_patient, true AS is_virtual, NULL::xid AS xmin_message_inbox
FROM clin.v_test_results vtr
WHERE vtr.reviewed IS FALSE AND (vtr.is_technically_abnormal IS TRUE OR vtr.is_technically_abnormal IS NULL AND vtr.abnormality_indicator IS NOT NULL);

View "clin.v_test_results"
Column | Type | Modifiers
--------------------------------+--------------------------+-----------
pk_patient | integer |
pk_test_result | integer |
clin_when | timestamp with time zone |
unified_abbrev | text |
unified_name | text |
unified_loinc | text |
unified_val | text |
unified_target_min | numeric |
unified_target_max | numeric |
unified_target_range | text |
soap_cat | text |
comment | text |
val_num | numeric |
val_alpha | text |
val_unit | text |
conversion_unit | text |
val_normal_min | numeric |
val_normal_max | numeric |
val_normal_range | text |
val_target_min | numeric |
val_target_max | numeric |
val_target_range | text |
abnormality_indicator | text |
norm_ref_group | text |
note_test_org | text |
material | text |
material_detail | text |
abbrev_tt | text |
name_tt | text |
loinc_tt | text |
code_tt | text |
coding_system_tt | text |
comment_tt | text |
name_test_org | text |
contact_test_org | text |
abbrev_meta | text |
name_meta | text |
loinc_meta | text |
comment_meta | text |
episode | text |
health_issue | text |
reviewed | boolean |
is_technically_abnormal | boolean |
is_clinically_relevant | boolean |
review_comment | text |
last_reviewer | text |
last_reviewed | timestamp with time zone |
review_by_you | boolean |
review_by_responsible_reviewer | boolean |
responsible_reviewer | text |
you_are_responsible | boolean |
modified_by | text |
modified_when | timestamp with time zone |
row_version | integer |
pk_item | integer |
pk_encounter | integer |
pk_episode | integer |
pk_test_type | integer |
pk_intended_reviewer | integer |
pk_request | integer |
xmin_test_result | xid |
pk_test_org | integer |
pk_meta_test_type | integer |
pk_health_issue | integer |
pk_last_reviewer | integer |
View definition:
SELECT cenc.fk_patient AS pk_patient, tr.pk AS pk_test_result, tr.clin_when, vutt.unified_abbrev, vutt.unified_name, vutt.unified_loinc,
CASE
WHEN COALESCE(btrim(tr.val_alpha), ''::text) = ''::text THEN tr.val_num::text
ELSE
CASE
WHEN tr.val_num IS NULL THEN tr.val_alpha
ELSE ((tr.val_num::text || ' ('::text) || tr.val_alpha) || ')'::text
END
END AS unified_val, COALESCE(tr.val_target_min, tr.val_normal_min) AS unified_target_min, COALESCE(tr.val_target_max, tr.val_normal_max) AS unified_target_max, COALESCE(tr.val_target_range, tr.val_normal_range) AS unified_target_range, tr.soap_cat, tr.narrative AS comment, tr.val_num, tr.val_alpha, tr.val_unit, vutt.conversion_unit, tr.val_normal_min, tr.val_normal_max, tr.val_normal_range, tr.val_target_min, tr.val_target_max, tr.val_target_range, tr.abnormality_indicator, tr.norm_ref_group, tr.note_test_org, tr.material, tr.material_detail, vutt.abbrev_tt, vutt.name_tt, vutt.loinc_tt, vutt.code_tt, vutt.coding_system_tt, vutt.comment_tt, cto.internal_name AS name_test_org, cto.contact AS contact_test_org, vutt.abbrev_meta, vutt.name_meta, vutt.loinc_meta, vutt.comment_meta, epi.description AS episode, chi.description AS health_issue, COALESCE(rtr.fk_reviewed_row, 0)::boolean AS reviewed, rtr.is_technically_abnormal, rtr.clinically_relevant AS is_clinically_relevant, rtr.comment AS review_comment, (
SELECT staff.short_alias
FROM dem.staff
WHERE staff.pk = rtr.fk_reviewer) AS last_reviewer, rtr.modified_when AS last_reviewed, COALESCE(rtr.fk_reviewer = (( SELECT staff.pk
FROM dem.staff
WHERE staff.db_user = "current_user"())), false) AS review_by_you, COALESCE(tr.fk_intended_reviewer = rtr.fk_reviewer, false) AS review_by_responsible_reviewer, ( SELECT staff.short_alias
FROM dem.staff
WHERE staff.pk = tr.fk_intended_reviewer) AS responsible_reviewer, COALESCE(tr.fk_intended_reviewer = (( SELECT staff.pk
FROM dem.staff
WHERE staff.db_user = "current_user"())), false) AS you_are_responsible,
CASE
WHEN (( SELECT 1
FROM dem.staff
WHERE staff.db_user = tr.modified_by)) IS NULL THEN ('<'::text || tr.modified_by::text) || '>'::text
ELSE ( SELECT staff.short_alias
FROM dem.staff
WHERE staff.db_user = tr.modified_by)
END AS modified_by, tr.modified_when, tr.row_version, tr.pk_item, tr.fk_encounter AS pk_encounter, tr.fk_episode AS pk_episode, tr.fk_type AS pk_test_type, tr.fk_intended_reviewer AS pk_intended_reviewer, tr.fk_request AS pk_request, tr.xmin AS xmin_test_result, vutt.pk_test_org, vutt.pk_meta_test_type, epi.fk_health_issue AS pk_health_issue, rtr.fk_reviewer AS pk_last_reviewer
FROM clin.test_result tr
LEFT JOIN clin.encounter cenc ON tr.fk_encounter = cenc.pk
LEFT JOIN clin.episode epi ON tr.fk_episode = epi.pk
LEFT JOIN clin.reviewed_test_results rtr ON tr.pk = rtr.fk_reviewed_row
LEFT JOIN clin.health_issue chi ON epi.fk_health_issue = chi.pk, clin.v_unified_test_types vutt
LEFT JOIN clin.test_org cto ON vutt.pk_test_org = cto.pk
WHERE tr.fk_type = vutt.pk_test_type;

View "blobs.v_obj4doc_no_data"
Column | Type | Modifiers
-------------------------------+--------------------------+-----------
pk_patient | integer |
pk_obj | integer |
seq_idx | integer |
size | integer |
date_generated | timestamp with time zone |
type | text |
l10n_type | text |
ext_ref | text |
episode | text |
doc_comment | text |
obj_comment | text |
filename | text |
pk_intended_reviewer | integer |
reviewed | boolean |
reviewed_by_you | boolean |
reviewed_by_intended_reviewer | boolean |
pk_doc | integer |
pk_type | integer |
pk_encounter | integer |
pk_episode | integer |
pk_health_issue | integer |
xmin_doc_obj | xid |
View definition:
SELECT vdm.pk_patient, dobj.pk AS pk_obj, dobj.seq_idx, octet_length(COALESCE(dobj.data, ''::bytea)) AS size, vdm.clin_when AS date_generated, vdm.type, vdm.l10n_type, vdm.ext_ref, vdm.episode, vdm.comment AS doc_comment, dobj.comment AS obj_comment, dobj.filename, dobj.fk_intended_reviewer AS pk_intended_reviewer, (EXISTS ( SELECT 1
FROM blobs.reviewed_doc_objs
WHERE reviewed_doc_objs.fk_reviewed_row = dobj.pk)) AS reviewed, (EXISTS ( SELECT 1
FROM blobs.reviewed_doc_objs
WHERE reviewed_doc_objs.fk_reviewed_row = dobj.pk AND reviewed_doc_objs.fk_reviewer = (( SELECT staff.pk
FROM dem.staff
WHERE staff.db_user = "current_user"())))) AS reviewed_by_you, (EXISTS ( SELECT 1
FROM blobs.reviewed_doc_objs
WHERE reviewed_doc_objs.fk_reviewed_row = dobj.pk AND reviewed_doc_objs.fk_reviewer = dobj.fk_intended_reviewer)) AS reviewed_by_intended_reviewer, vdm.pk_doc, vdm.pk_type, vdm.pk_encounter, vdm.pk_episode, vdm.pk_health_issue, dobj.xmin AS xmin_doc_obj
FROM blobs.v_doc_med vdm, blobs.doc_obj dobj
WHERE vdm.pk_doc = dobj.fk_doc;

View "dem.v_inbox_item_type"
Column | Type | Modifiers
------------------+---------+-----------
type | text |
l10n_type | text |
category | text |
l10n_category | text |
is_user_type | boolean |
is_user_category | boolean |
pk_type | integer |
pk_category | integer |
View definition:
SELECT it.description AS type, _(it.description) AS l10n_type, ic.description AS category, _(ic.description) AS l10n_category, it.is_user AS is_user_type, ic.is_user AS is_user_category, it.pk AS pk_type, it.fk_inbox_item_category AS pk_category
FROM dem.inbox_item_type it, dem.inbox_item_category ic
WHERE it.fk_inbox_item_category = ic.pk;

select * from dem.v_message_inbox;

psql:xx.sql:14: ERROR: could not implement UNION
DETAIL: Some of the datatypes only support hashing, while others only support sorting.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

For the record:

On Wed, Feb 09, 2011 at 11:12:01PM +0100, Karsten Hilbert wrote:

Attached find some table and view definitions from the
GNUmed (www.gnumed.de) database.

Unfortunately I do not understand why PostgreSQL says

psql:xx.sql:14: ERROR: could not implement UNION
DETAIL: Some of the datatypes only support hashing, while others only support sorting.

The solution lies in these bits:

View "dem.v_message_inbox"
Column | Type | Modifiers
--------------------+--------------------------+-----------
received_when | timestamp with time zone |
provider | text |
importance | integer |
category | text |
l10n_category | text |
type | text |
l10n_type | text |
comment | text |
pk_context | integer[] |

This data type can only be hashed.

data | text |
pk_inbox_message | integer |
pk_staff | integer |
pk_category | integer |
pk_type | integer |
pk_patient | integer |
is_virtual | boolean |
xmin_message_inbox | xid |

This data type can only be sorted.

By defining an explicit caster:

create or replace function gm.xid2int(xid)
returns integer
language 'sql'
as 'select $1::text::integer;';

and applying that to the XMIN column inside the view
definition nicely solves the "could not implement UNION".

Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:

On Wed, Feb 09, 2011 at 11:12:01PM +0100, Karsten Hilbert wrote:

Unfortunately I do not understand why PostgreSQL says

psql:xx.sql:14: ERROR: could not implement UNION
DETAIL: Some of the datatypes only support hashing, while others only support sorting.

The solution lies in these bits:

pk_context | integer[] |

This data type can only be hashed.

xmin_message_inbox | xid |

This data type can only be sorted.

ITYM the other way round, right?

As of 9.1 there will be support for hashing arrays, so this particular
problem should go away without hacks.

regards, tom lane

On Sun, Feb 20, 2011 at 02:31:46PM -0500, Tom Lane wrote:

Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:

On Wed, Feb 09, 2011 at 11:12:01PM +0100, Karsten Hilbert wrote:

Unfortunately I do not understand why PostgreSQL says

psql:xx.sql:14: ERROR: could not implement UNION
DETAIL: Some of the datatypes only support hashing, while others only support sorting.

The solution lies in these bits:

pk_context | integer[] |

This data type can only be hashed.

xmin_message_inbox | xid |

This data type can only be sorted.

ITYM the other way round, right?

Indeed :-)

As of 9.1 there will be support for hashing arrays, so this particular
problem should go away without hacks.

Great. PG is getting better by the day :-)

Anyway, I consider the "explicit cast" not really that bad
of a hack.

Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346