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?
just to inform - the SQL views are dropped in reversed alphabetical order, and generated in alphabetical order based on name. This means there is no “intelligence” around detecting view inter-dependencies, but at least gives a way to deal with it (make sure that a view which depends on another view is alphabetically ordered after it).
regards,
Lars
···
On Mon, Sep 28, 2015 at 1:09 PM, Knut Staring knutst@gmail.com wrote:
I have a similar problem in 2.19 where backups are not getting generated because of sqlviews.
Thanks Lars. That should help. Though it is still a bit of mystery
why it worked on one system but not on the other. Is this alphabetic
ordering post 2.17?
···
On 28 September 2015 at 12:22, Lars Helge Øverland <larshelge@gmail.com> wrote:
Hi Bob,
just to inform - the SQL views are dropped in reversed alphabetical order,
and generated in alphabetical order based on name. This means there is no
"intelligence" around detecting view inter-dependencies, but at least gives
a way to deal with it (make sure that a view which depends on another view
is alphabetically ordered after it).
regards,
Lars
On Mon, Sep 28, 2015 at 1:09 PM, Knut Staring <knutst@gmail.com> wrote:
I have a similar problem in 2.19 where backups are not getting generated
because of sqlviews.
On Mon, Sep 28, 2015 at 11:03 AM, Bob Jolliffe <bobjolliffe@gmail.com> >> wrote:
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?