The following query gives an error when run on solaris 2.7, but it works
fine with an equivalent configuration on rh 6.2.
select tr.transition_key,
tr.transition_name
from wf_transition_info tr
left outer join
wf_context_assignments ca
on (tr.context_key = ca.context_key and
tr.transition_key = ca.transition_key)
where tr.workflow_key = 'expenses_wf'
and tr.context_key = 'default'
and tr.trigger_type = 'user'
and tr.assignment_callback is null;
.
.
.
DROP
CREATE
psql:tst.sql:210: ERROR: RIGHT JOIN is only supported with mergejoinable join conditions
acspg=# select version();
version
-----------------------------------------------------------------
PostgreSQL 7.1 on sparc-sun-solaris2.7, compiled by GCC 2.7.2.2
(1 row)
acspg=#
The following code can be used to reproduce the error:
drop table wf_workflows;
create table wf_workflows (
workflow_key varchar(100)
constraint wf_workflows_pk
primary key,
description text
);
drop table wf_contexts;
create table wf_contexts (
context_key varchar(100)
constraint wf_context_pk
primary key,
context_name varchar(100)
constraint wf_contexts_context_name_nn
not null
constraint wf_contexts_context_name_un
unique
);
drop table wf_transitions;
create table wf_transitions (
transition_key varchar(100),
transition_name varchar(100)
constraint wf_transition_name_nn
not null,
workflow_key varchar(100)
constraint wf_transition_workflow_fk
references wf_workflows(workflow_key)
on delete cascade,
-- purely for UI purposes
sort_order integer
constraint wf_transition_order_ck
check (sort_order > 0),
trigger_type varchar(40)
constraint wf_transition_trigger_type_ck
check (trigger_type in
('','automatic','user','message','time')),
constraint wf_transition_pk
primary key (workflow_key, transition_key),
constraint wf_trans_wf_key_trans_name_un
unique (workflow_key, transition_name)
);
drop table wf_context_transition_info;
create table wf_context_transition_info (
context_key varchar(100)
constraint wf_context_trans_context_fk
references wf_contexts,
workflow_key varchar(100)
constraint wf_context_trans_workflow_fk
references wf_workflows,
transition_key varchar(100),
/* information for the transition in the context */
/* The integer of minutes this task is estimated to take */
estimated_minutes integer,
/*
* Will be called when the transition is enabled/fired.
* signature: (case_id in integer, transition_key in varchar, custom_arg in varchar2)
*/
enable_callback varchar(100),
enable_custom_arg text,
fire_callback varchar(100),
fire_custom_arg text,
/*
* Must insert rows into the wf_task_assignments table.
* Will be called when the transition becomes enabled
* signature: (task_id in integer, custom_arg in varchar)
*/
assignment_callback varchar(100),
assignment_custom_arg text,
/*
* Must return the date that the timed transition should fire
* Will be called when the transition is enabled
* signature: (case_id in integer, transition_key in varchar, custom_arg in varchar2) return date
*/
time_callback varchar(100),
time_custom_arg text,
/*
* Returns the deadline for this task.
* Will be called when the transition becomes enabled
* Signature: (case_id in integer, transition_key in varchar, custom_arg in varchar2) return date
*/
deadline_callback varchar(100),
deadline_custom_arg text,
/* The name of an attribute that holds the deadline */
deadline_attribute_name varchar(100),
/*
* Must return the date that the user's hold on the task times out.
* called when the user starts the task.
* signature: (case_id in integer, transition_key in varchar, custom_arg in varchar2) return date
*/
hold_timeout_callback varchar(100),
hold_timeout_custom_arg text,
/*
* Notification callback
* Will be called when a notification is sent i.e., when a transition is enabled,
* or assignment changes.
* signature: (task_id in integer,
* custom_arg in varchar,
* party_to in integer,
* party_from in out integer,
* subject in out varchar,
* body in out varchar)
*/
notification_callback varchar(100),
notification_custom_arg text,
/*
* Unassigned callback
* Will be called whenever a task becomes unassigned
* Signature: (case_id in integer, transition_key in varchar, custom_arg in varchar2)
*/
unassigned_callback varchar(100),
unassigned_custom_arg text,
/* name of the privilege we should check before allowing access
* to task information.
*/
access_privilege text,
/* table constraints */
constraint wf_context_trans_trans_fk
foreign key (workflow_key, transition_key) references wf_transitions(workflow_key, transition_key)
on delete cascade,
constraint wf_context_transition_pk
primary key (context_key, workflow_key, transition_key)
);
drop table wf_context_assignments;
create table wf_context_assignments (
context_key varchar(100)
constraint wf_context_assign_context_fk
references wf_contexts(context_key)
on delete cascade,
workflow_key varchar(100)
constraint wf_context_assign_workflow_fk
references wf_workflows(workflow_key)
on delete cascade,
transition_key varchar(100),
party_id integer,
/* table constraints */
constraint wf_context_assign_trans_fk
foreign key (workflow_key, transition_key) references wf_transitions(workflow_key, transition_key)
on delete cascade,
constraint wf_context_assign_pk
primary key (context_key, workflow_key, transition_key, party_id)
);
drop view wf_transition_contexts;
create view wf_transition_contexts as
select t.transition_key,
t.transition_name,
t.workflow_key,
t.sort_order,
t.trigger_type,
c.context_key,
c.context_name
from wf_transitions t, wf_contexts c;
drop view wf_transition_info;
create view wf_transition_info as
select t.transition_key,
t.transition_name,
t.workflow_key,
t.sort_order,
t.trigger_type,
t.context_key,
ct.estimated_minutes,
ct.enable_callback,
ct.enable_custom_arg,
ct.fire_callback,
ct.fire_custom_arg,
ct.assignment_callback,
ct.assignment_custom_arg,
ct.time_callback,
ct.time_custom_arg,
ct.deadline_callback,
ct.deadline_custom_arg,
ct.deadline_attribute_name,
ct.hold_timeout_callback,
ct.hold_timeout_custom_arg,
ct.notification_callback,
ct.notification_custom_arg,
ct.unassigned_callback,
ct.unassigned_custom_arg,
ct.access_privilege
from wf_transition_contexts t LEFT OUTER JOIN wf_context_transition_info ct
on (ct.workflow_key = t.workflow_key and
ct.transition_key = t.transition_key and
ct.context_key = t.context_key);
Regards,
Dan Wickstrom