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:
Yes, this would be a bug of automatic index creation when the view has set-returning function in FROM clause. I'll fix this.
I want the ability to suppress auto index creation when needed to create IMMV.
Even though putting the bug-fix aside, your proposal also may make sense. This can be workaround for issues like this case and also it might be possible that users can find another efficient index.
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: