Eliminating implicit or explicit cross joins

Answer

Cross joins are a costly query operation that can slow system performance. In NPS 4.5 and later, the enable_crossjoin_control variable can be added to the /nz/data/postgresql.conf file to prevent users from running queries that contain an explicit or implicit cross join.

Note: Because this setting involves a system-wide change, you should notify all affected users before making this change. Additionally, all components of the affected application must be completely tested.

After enabling this feature, implicit cross-joins will error out.

You can allow any user or group to use the cross join explicitly.The following SQL adds or creates a user or group to use cross join explicitly:

Create / Alter user <name> allow cross join { NULL | TRUE | FALSE }
Create / Alter group <name> allow cross join { NULL | TRUE | FALSE }

If a user is defined with a ‘cross join’ property of NULL, the system will look up the value for the groups of which the user is a member. The system will take the lowest non-null value (FALSE is lower than TRUE).