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

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

  1. we need to maintain some sort of ordering/cascading logic which is

restored with the backup; or

  1. 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


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Knut Staring

Dept. of Informatics, University of Oslo

Norway: +4791880522

Skype: knutstar

http://dhis2.org

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.


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

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

  1. we need to maintain some sort of ordering/cascading logic which is

restored with the backup; or

  1. 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


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Knut Staring

Dept. of Informatics, University of Oslo

Norway: +4791880522

Skype: knutstar

http://dhis2.org

Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

http://www.dhis2.org

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?

Bob

_______________________________________________
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp

--
Knut Staring
Dept. of Informatics, University of Oslo
Norway: +4791880522
Skype: knutstar
http://dhis2.org

_______________________________________________
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp

--
Lars Helge Øverland
Lead developer, DHIS 2
University of Oslo
Skype: larshelgeoverland
http://www.dhis2.org

It might - I cannot remember…

Lars

···

On Mon, Sep 28, 2015 at 1:29 PM, Bob Jolliffe bobjolliffe@gmail.com wrote:

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
  1. we need to have a “cleverer” way to drop the (potentially

interdependent) views; or

  1. we need to maintain some sort of ordering/cascading logic which is

restored with the backup; or

  1. 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


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Knut Staring

Dept. of Informatics, University of Oslo

Norway: +4791880522

Skype: knutstar

http://dhis2.org


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

http://www.dhis2.org

Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

http://www.dhis2.org