Error occurs when updating user-defined type columns.

[nuko-yokohama]

Error occurs when updating user-defined type columns.

Create an INCREMENTAL MATERIALIZED VIEW by specifying a query that includes user-defined type columns. After the view is created, an error occurs when inserting into the view source table (including the user-defined type column).

ERROR:  operator does not exist

An execution example is shown below.

[ec2-user@ip-10-0-1-10 ivm]$ psql testdb -a -f extension-insert.sql
--
-- pg_fraction: https://github.com/nuko-yokohama/pg_fraction
--
DROP EXTENSION IF EXISTS pg_fraction CASCADE;
psql:extension-insert.sql:4: NOTICE:  drop cascades to column data of table foo
DROP EXTENSION
DROP TABLE IF EXISTS foo CASCADE;
DROP TABLE
CREATE EXTENSION IF NOT EXISTS pg_fraction;
CREATE EXTENSION
\dx
                   List of installed extensions
    Name     | Version |   Schema   |         Description
-------------+---------+------------+------------------------------
 pg_fraction | 1.0     | public     | fraction data type
 plpgsql     | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

\dT+ fraction
                                        List of data types
 Schema |   Name   | Internal name | Size | Elements |  Owner   | Access privileges | Description
--------+----------+---------------+------+----------+----------+-------------------+-------------
 public | fraction | fraction      | 16   |          | postgres |                   |
(1 row)

CREATE TABLE foo (id int, data fraction);
CREATE TABLE
INSERT INTO foo (id, data) VALUES (1,'2/3'),(2,'1/3'),(3,'1/2');
INSERT 0 3
SELECT id, data FROM foo WHERE data >= '1/2';
 id | data
----+------
  1 | 2/3
  3 | 1/2
(2 rows)

CREATE INCREMENTAL MATERIALIZED VIEW foo_imv AS SELECT id, data FROM foo WHERE data >= '1/2';
SELECT 2
TABLE foo_imv;
 id | data
----+------
  1 | 2/3
  3 | 1/2
(2 rows)

INSERT INTO foo (id, data) VALUES (4,'2/3'),(5,'2/5'),(6,'3/6'); -- error
psql:extension-insert.sql:17: ERROR:  operator does not exist: fraction pg_catalog.= fraction
LINE 1: ...(mv.id IS NULL AND diff.id IS NULL)) AND (mv.data OPERATOR(p...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
QUERY:  WITH updt AS (UPDATE public.foo_imv AS mv SET __ivm_count__ = mv.__ivm_count__ OPERATOR(pg_catalog.+) diff.__ivm_count__  FROM pg_temp_3.pg_temp_73900 AS diff WHERE (mv.id OPERATOR(pg_catalog.=) diff.id OR (mv.id IS NULL AND diff.id IS NULL)) AND (mv.data OPERATOR(pg_catalog.=) diff.data OR (mv.data IS NULL AND diff.data IS NULL)) RETURNING mv.id, mv.data) INSERT INTO public.foo_imv SELECT * FROM pg_temp_3.pg_temp_73900 AS diff WHERE NOT EXISTS (SELECT 1 FROM updt AS mv WHERE (mv.id OPERATOR(pg_catalog.=) diff.id OR (mv.id IS NULL AND diff.id IS NULL)) AND (mv.data OPERATOR(pg_catalog.=) diff.data OR (mv.data IS NULL AND diff.data IS NULL)));
TABLE foo;
 id | data
----+------
  1 | 2/3
  2 | 1/3
  3 | 1/2
(3 rows)

TABLE foo_imv;
 id | data
----+------
  1 | 2/3
  3 | 1/2
(2 rows)

DROP MATERIALIZED VIEW foo_imv;
DROP MATERIALIZED VIEW
INSERT INTO foo (id, data) VALUES (4,'2/3'),(5,'2/5'),(6,'3/6');
INSERT 0 3
TABLE foo;
 id | data
----+------
  1 | 2/3
  2 | 1/3
  3 | 1/2
  4 | 2/3
  5 | 2/5
  6 | 1/2
(6 rows)

Best regards.

[yugo-n]

@nuko-yokohama I fixed it and added a regression test. Also, the latest patch including this fix has been submitted. Could you please confirm this and close this issue?

Thanks,

[nuko-yokohama]

Thank you for the fix.

commit id = e28d1d4d79fad97f55d0bb927525cbb3dd2738c0 confirmed that this problem was resolved. The issue closes.