You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
In pg_ivm 1.9, IMMV creation fails due to issues with index creation, which worked in 1.5.
expect
I want the ability to suppress auto index creation when needed to create IMMV.
how to reproduce
CREATE TABLE table_name ( id SERIAL PRIMARY KEY, json jsonb );
INSERT INTO table_name (json) VALUES ('{"a": [{"name":"b"},{"name": "c"},{"name": "d"}]}'::jsonb);
INSERT INTO table_name (json) VALUES ('{"a": [{"name":"e"},{"name": "f"},{"name": "g"}]}'::jsonb);
postgres=# SELECT
id,
(element->>'name') AS name
FROM
table_name,
jsonb_array_elements(json->'a') AS element;
id | name
----+------
2 | b
2 | c
2 | d
3 | e
3 | f
3 | g
(6 rows)
then create immv(failed)
postgres=# select create_immv('json_iv', $$SELECT
id,
(element->>'name') AS name
FROM
table_name,
jsonb_array_elements(json->'a') AS element$$);
ERROR: could not create unique index "json_iv_index"
DETAIL: Key (id)=(2) is duplicated.
Added DISTINCT in the SELECT then I could create the IMMV, but the execution plan is different, so it's inefficient and not what I need.
postgres=# select create_immv('json_iv', $$SELECT
distinct id,
(element->>'name') AS name
FROM
table_name,
jsonb_array_elements(json->'a') AS element$$);
NOTICE: created index "json_iv_index" on immv "json_iv"
create_immv
-------------
6
(1 row)
postgres=# \d table_name
Table "public.table_name"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+----------------------------------------
id | integer | | not null | nextval('table_name_id_seq'::regclass)
json | jsonb | | |
Indexes:
"table_name_pkey" PRIMARY KEY, btree (id)
The text was updated successfully, but these errors were encountered:
In pg_ivm 1.9, IMMV creation fails due to issues with index creation, which worked in 1.5.
expect
I want the ability to suppress auto index creation when needed to create IMMV.
how to reproduce
then create immv(failed)
Added
DISTINCT
in theSELECT
then I could create the IMMV, but the execution plan is different, so it's inefficient and not what I need.The text was updated successfully, but these errors were encountered: