← Back to team overview

dhis2-devs team mailing list archive

Interesting problem with sql view dependencies

 

I am not sure if this is an issue which has been fixed in later
versions, but we recently chanced upon some odd behaviour in 2.17.

On the production system there are a number of views defined (through
the sql view interface).  A couple of those views depend on other
views.   When resource tables are regenerated the views are dropped
and recreated as expected.

But ...

When we restored an exact backup on to a different database on a new
system suddenly the resource table generation was failing because it
was failing to delete views which depended on other views.  It seems
the natural order was changed on the new sqlview table and so the list
of views to be deleted come out in a different order to the source
system.

So ... either

1.  the problem is fixed on a later version; or
2.  we need to have a "cleverer" way to drop the (potentially
interdependent) views; or
3.  we need to maintain some sort of ordering/cascading logic which is
restored with the backup; or
4.  we should issue a cautionary note that creating views which depend
on other views is potentially fragile.  It might work on one system
but cause odd problems on a restored system.

Maybe 4 is enough.  Anyway I thought I should raise the issue.  Any thoughts?

Bob


Follow ups