Migration - update schema -removing indexes

[suchym]

In 'EntityModule.RegisterMigrations' I create several GIST indexes (working with PostgreSQL) at the end of migration (AddPostUpgradeAction) by running an SQL script.
When I create database and apply a schema update, everything works as expected, but when migration is reapplied to the database, the GIST indexes are removed.
Upgrade options are set to 'DbUpgradeOptions.Default' only.
I use version 3.0.2 that VS package manager has shown me as latest stable.
So question is: How to avoid removing GIST indexes during a schema update?

Thank you in advance.

[rivantsov]

well, as of now, there's no easy way, unfortunately. Give me a couple of days to come up with something usable

[rivantsov]

I have some ideas to try, I will push smth shortly

[rivantsov]

pushed v 3.4.0, Look at test SchemaUpdate test, it creates extra index in migration; you just add the index name to ignore list - this is new thing, list of Db objects to ignore in schema upgrade; you can do it right in the RegisterMigr method where you add the script for the index.

[suchym]

I upgraded VITa to 3.4.0 version, created new database and tried to update schema.
I filled up the DbSettings.UpgradeSettings.IgnoreDbObjects list to test this feature when applying the update the second time.

I got an exception on the first run:
'42704: index "PK_UniqueStructure" for table "UniqueStructure" does not exist'
when VITA tried to execute command
ALTER TABLE common."UniqueStructure" CLUSTER ON "PK_UniqueStructure";

The order in which the commands are executed appears to be incorrect because
command:
ALTER TABLE common."UniqueStructure" ADD CONSTRAINT "PK_UniqueStructure" PRIMARY KEY ("Id")
appears after : 'ALTER TABLE common."UniqueStructure" CLUSTER ON "PK_UniqueStructure";' command in DbUpgradeEventArgs.UpdateInfo.AllScripts list.

I believe that DbUpgrading event displays an ordered list of commands in the DbUpgradeEventArgs.UpdateInfo.AllScripts list.

[suchym]

To the previous entry: When I applied the upgrade schema to an existing database I got exception but now related to the materialized view. The order in which the commands were executed was also incorrect.

Here is order:

1. {Drop Key: IXC_SpectraStatistics_PostprocessingPol(UniqueClusteredIndex)}
2. {ViewDrop:Drop View: spectrum."SpectraStatistics"}
3. {ColumnSetupComplete:Add View: spectrum."SpectraStatistics"}
4. {PrimaryKeyAdd:Add Key: IXC_SpectraStatistics_PostprocessingPol(UniqueClusteredIndex)}
   (ALTER TABLE spectrum."SpectraStatistics" CLUSTER ON "IXC_SpectraStatistics_PostprocessingPol";)
5. {IndexAdd:Add Key: IXC_SpectraStatistics_PostprocessingPol(UniqueClusteredIndex)}

The question is, why is the materialized view always re-created?
Creation command omits the WITH [NO] DATA clause
This can lead to incorrect application behavior .
PostgreSQL Documentation says:
WITH [ NO ] DATA
This clause specifies whether or not the materialized view should be populated at creation time. If not, the materialized view will be flagged as unscannable and cannot be queried until REFRESH MATERIALIZED VIEW is used.

[rivantsov]

looking into this

[rivantsov]

Looking at pg docs, trying to find this 'WITH NO DATA' clause, cannot find any:
https://www.postgresql.org/docs/13/rules-materializedviews.html
what docs you use?

[suchym]

Here's the link I used as the source of my note: https://www.postgresql.org/docs/current/sql-creatematerializedview.html ut 21. 9. 2021 o 7:54 Roman Ivantsov ***@***.***> napísal(a):

…

Looking at pg docs, trying to find this 'WITH NO DATA' clause, cannot find any: https://www.postgresql.org/docs/13/rules-materializedviews.html what docs you use? — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#189 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AFOYASCSAG5QRNXLMUNCBXLUDAMZNANCNFSM5CY2YDSA> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

[rivantsov]

ah, I see. But anyway, this With-Data clause is optional. I need no-data anyway because I need to wait for completion of all actions (just in case there are more keys and indexes, and tables must be finished etc) - before filling the data.
For wrong order error - found a cause, surprised it never showed up before, but I guess it's not so frequent combination. Will fix it.
As for Mat view rebuilt - I have no good answer. The problem with mat views is that
a) the PG people do not treat them as views and do not even return them in View list in info-schema queries so I had to hack my own workaround:

vita/src/2.Drivers/Vita.Data.Postgres/Schema/PgDbModelLoader.cs

Line 99 in 441d2bd

// Postgres does not return materialized views in information_schema.views - they say it's intentional (and stupid!)

b) I do not have the 'old' view definition in db, notice I put empty string in the query for View_Definition field, so I have no way to compare the new vs old, so I assume I need to rebuild the views always.

So the question is - do you really need that stuff? It seems gains/conveniences are marginal. If you do, the only way I see is create it and then add it to 'ignore' list

[rivantsov]

Pushed new version; problem with indexes (wrong order) should be fixed.
For mat views, trying a workaround. Saving view SQL hashes in DbVersion records (in DbInfoModule). Comparing hashes to figure out if views changed or not.
Let me know if it works for you

[suchym]

Thanks for the quick fix. The execution order is correct and the database update works. However, I still see the removal of clustered index in the materialized view. I added a materialized view to the ignore list and it seems that it remains intact but its cluster index is dropped and added even though I added it to the ignore list too. But that is enough for me for now. st 22. 9. 2021 o 7:47 Roman Ivantsov ***@***.***> napísal(a):

…

Pushed new version; problem with indexes (wrong order) should be fixed. For mat views, trying a workaround. Saving view SQL hashes in DbVersion records (in DbInfoModule). Comparing hashes to figure out if views changed or not. Let me know if it works for you — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#189 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AFOYASHRNVKVETRDLOUQ5WTUDFUWLANCNFSM5CY2YDSA> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

[suchym]

I forgot to say that the indexes added in the ignore list are really ignored, only the index in the materialized view is re-created. st 22. 9. 2021 o 7:47 Roman Ivantsov ***@***.***> napísal(a):

…

Pushed new version; problem with indexes (wrong order) should be fixed. For mat views, trying a workaround. Saving view SQL hashes in DbVersion records (in DbInfoModule). Comparing hashes to figure out if views changed or not. Let me know if it works for you — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#189 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AFOYASHRNVKVETRDLOUQ5WTUDFUWLANCNFSM5CY2YDSA> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

[rivantsov]

will look into this index issue