Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

IMMV with duplicate data in the pkey can't be created due to auto index creation #99

Open
aeg opened this issue Sep 7, 2024 · 0 comments

Comments

@aeg
Copy link

aeg commented Sep 7, 2024

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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant