Trigger not working if table contains column with non-equality type

[dzolo]

If the table contains a column which does not implement equality operator then the versioning trigger fails. The equality operator is not implemented for some PostGIS data types, JSON data types, etc.

Example error for table with PostGIS polygon type:

ERROR:  could not identify an equality operator for type polygon
CONTEXT:  PL/pgSQL function versioning() line 38 at IF

The reason is the NEW IS NOT DISTINCT FROM OLD statement in IF condition related to the ignore_unchanged_values feature. The failure occurs even if the ignore_unchanged_values is not enabled as all parts of the IF condition are executed. Thus the issue is quite severe as it breaks compatibility/functionality with the old fashion temporal_tables.

[simoneb]

@dzolo any thoughts about what would be a sensible solution?

[dzolo]

@simoneb simple solution for ignore_unchanged_values=false is to use nested if:

  IF ignore_unchanged_values AND TG_OP = 'UPDATE' THEN
    IF NEW IS NOT DISTINCT FROM OLD THEN
      RETURN OLD;
    END IF;
  END IF;

For the ignore_unchanged_values=true I down think that there will be an easy solution to this. Probably the ignore_unchanged_values feature will not be possible for such tables.

[dzolo]

I have created a PR at least for the ignore_unchanged_values=false

[simoneb]

Amazing thanks. Do you think it would be possible to include a test?

[dzolo]

OK, will do later.

[github-actions]

🎉 This issue has been resolved in version 0.5.0 🎉

The release is available on:

• GitHub release

Your optic bot 📦🚀