Update statement fails on table that has logging enabled

[ajkq]

Update statement fails on table that has logging enabled. The same values as insert statement succeeds.

2019-05-13 03:59:30.029 UTC [63903] ERROR:  operator does not exist: jsonb - jsonb at character 25
2019-05-13 03:59:30.029 UTC [63903] HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
2019-05-13 03:59:30.029 UTC [63903] QUERY:  SELECT (to_jsonb(NEW.*) - audit_row.row_data) - excluded_cols
2019-05-13 03:59:30.029 UTC [63903] CONTEXT:  PL/pgSQL function audit.if_modified_func() line 48 at assignment

Version: PostgreSQL 11.2 on x86_64-apple-darwin18.5.0, compiled by Apple LLVM version 10.0.1 (clang-1001.0.46.4), 64-bit

[m-martinez]

Thanks for the bug report! I’ll look into it ASAP.

[satsva]

I got hit with this issue as well. Please see details below of PG version and error message.

PostgreSQL 10.8 (Ubuntu 10.8-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.3.0-16ubuntu3) 7.3.0, 64-bit

ERROR: operator does not exist: jsonb - jsonb LINE 1: SELECT (to_jsonb(NEW.*) - audit_row.row_data) - excluded_col... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. QUERY: SELECT (to_jsonb(NEW.*) - audit_row.row_data) - excluded_cols CONTEXT: PL/pgSQL function audit.if_modified_func() line 48 at assignment SQL state: 42883

[m-martinez]

Oh no! OK I'll prioritize this.

Out of curiosity, if you run \df and \do do you see the following?

postgres=# \df
                              List of functions
 Schema |    Name     | Result data type |     Argument data types     | Type 
--------+-------------+------------------+-----------------------------+------
 public | jsonb_minus | jsonb            | "left" jsonb, "right" jsonb | func
 public | jsonb_minus | jsonb            | "left" jsonb, keys text[]   | func
(2 rows)

postgres=# \do
                                                         List of operators
 Schema | Name | Left arg type | Right arg type | Result type |                            Description                             
--------+------+---------------+----------------+-------------+--------------------------------------------------------------------
 public | -    | jsonb         | jsonb          | jsonb       | Delete matching pairs in the right argument from the left argument
 public | -    | jsonb         | text[]         | jsonb       | Delete specified keys
(2 rows)

Specifically, we're looking to see if the json_minus function was correctly setup

[satsva]

Yes...Thank you!

sandbox=# \df public.*
                               List of functions
 Schema |    Name     | Result data type |     Argument data types     |  Type  
--------+-------------+------------------+-----------------------------+--------
 public | jsonb_minus | jsonb            | "left" jsonb, keys text[]   | normal
 public | jsonb_minus | jsonb            | "left" jsonb, "right" jsonb | normal
(2 rows)

sandbox=# \do public.*
                                                         List of operators
 Schema | Name | Left arg type | Right arg type | Result type |                            Description                             
--------+------+---------------+----------------+-------------+--------------------------------------------------------------------
 public | -    | jsonb         | jsonb          | jsonb       | Delete matching pairs in the right argument from the left argument
 public | -    | jsonb         | text[]         | jsonb       | Delete specificed keys
(2 rows)

[m-martinez]

Interesting. OK what permissions do the functions have, you can get those with: SELECT proname, proacl FROM pg_proc WHERE proname='jsonb_minus';

Additionally, is there a way you can list for me the steps you took to produce the issue? So that I can try and reproduce on my environment? Thanks!