scenic icon indicating copy to clipboard operation
scenic copied to clipboard

Materialization is dumped to structure.rb incorrectly

Open mustmodify opened this issue 2 years ago • 0 comments

  1. Thank you for this incredibly useful gem.
  2. I tested this issue with both scenic 1.5.4, 1.5.5, and 1.6.0.
  3. The bug I'm reporting is that "WITH NO DATA" appears in structure.rb after turning a normal view into a materialized view. The migration does NOT instruct scenic to use WITH NO DATA and it appears that the view is correctly created but incorrectly dumped.
   create_view :answers,
      version: 8,
      materialized: true

    add_index :answers, :unique_answer_key, unique: true

When I ran migrations, I noticed that structure.sql included WITH NO DATA. While not a total disaster, it wasn't convenient. As I dig into your gem, I:

  • tried using the materialized: {no_data: false} code path.
  • verified that no_data was false in /lib/scenic/adapters/postgres.rb
  • verified that the SQL it created DID NOT include WITH NO DATA
  • ran this right after a down-and-up migration (the nth time), but I do think it recreated the view):
dbname=# select count(unique_answer_key) FROM answers;
 count
-------
     7
(1 row)
  • got this definition from postgres:
dbname=# \d+ answers;
                                           Materialized view "public.answers"
        Column         |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description
-----------------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 unique_answer_key     | text                        |           |          |         | extended |              |
 task_id               | bigint                      |           |          |         | plain    |              |
 date_answer           | date                        |           |          |         | plain    |              |
...
 discarded_at          | timestamp without time zone |           |          |         | plain    |              |

Indexes:
    "index_task_answers_on_unique_answer_key" UNIQUE, btree (unique_answer_key)

View definition:
 WITH raw_answers_a AS (
...        ), raw_answers_b AS (
...        ), raw_answers AS (
         SELECT *
           FROM raw_answers_a
        UNION
         *
           FROM raw_answers_b
        )
 SELECT (raw_answers.form_submission_id::text || '_'::text) || raw_answers.question_id AS unique_answer_key,
    raw_answers.task_id,
    ....
      LEFT JOIN reportable_fields ON reportable_fields.id = questions."reportableFieldId";
Access method: heap

I haven't tried but presumably the WITH NO DATA would be present there. So every piece of data I have suggests it was created with data. And yet I find this in db/structure.sql:

     ... the definition ...
     LEFT JOIN public.reportable_fields ON ((reportable_fields.id = questions."reportableFieldId")))
  WITH NO DATA;

mustmodify avatar Jul 13 '22 17:07 mustmodify