Analytics tables not generated when sql views linked to analytics tables exist

Bug #1211010 reported by Dapo Adejumo on 2013-08-11
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
DHIS
High
Lars Helge Øverland

Bug Description

Analytics tables are not generated when sql view linked to the analytics table is present. Postgres cannot drop a table linked to a view without a cascade statement dropping the particular view. Since postgres cannot drop the analytics tables to populate a new one, the process is truncated .

Using version 2.12 build 11268.

This is not a bug with DHIS2. If you are creating views directly on the DB, you will need to encapsulate your view in a stored procedure if you are linking to any table which is dropped (same with the resource tables). In general, you cannot have a view directly on the DHIS2 database. If you do, it must be an SQL view in DHIS and not a view directly stored in the DB. So, I think when you mean SQL view, it is not an SQL view within DHIS2. I am not able to reproduce this by the following procedure.

1) Create an SQL view in DHIS2
SELECT * FROM analytics 2013 LIMIT 10;
2) Regenerate the analytics tables (no error).

However if you create a view directly on the database, you will get an error, but this is not a bug with DHIS2.

Changed in dhis2:
status: New → Incomplete

Well, you are right Dapo. I thought I was all clever, but did not look through the entire log. :/ The process does fail, because the SQL views are not being dropped prior to the creation of the analytics tables. Looks like this needs to occur after their creation and not after the creation of the resource tables.

Changed in dhis2:
status: Incomplete → Confirmed

No, you are absolutely right and I jumped the gun. The views are recreated after the resource table regeneration, and prior to the regeneration of the analytics tables. This step (regeneration of the SQL views) needs to occur after the analytics tables are populated.

Hi Dapo,

yes agree, we can make sure that the creation of SQL views happens at the
end.

Lars

On Sun, Aug 11, 2013 at 6:52 PM, Dapo Adejumo <email address hidden>wrote:

> Thanks Jason...glad you found it :) - the sequence needs to be changed.
>
>
> ..........................
> Regards
>
> Dapo Adejumo
> +2348033683677
> Skype : dapojorge
>
>
> -----Original Message-----
> From: Dhis2-devs
> [mailto:<email address hidden>] On
> Behalf Of jason.p.pickering
> Sent: Sunday, August 11, 2013 5:14 PM
> To: <email address hidden>
> Subject: [Dhis2-devs] [Bug 1211010] Re: Analytics tables not generated when
> sql views linked to analytics tables exist
>
> Well, you are right Dapo. I thought I was all clever, but did not look
> through the entire log. :/ The process does fail, because the SQL views
> are not being dropped prior to the creation of the analytics tables.
> Looks like this needs to occur after their creation and not after the
> creation of the resource tables.
>
>
> ** Changed in: dhis2
> Status: Incomplete => Confirmed
>
> --
> You received this bug notification because you are a member of DHIS 2
> developers, which is subscribed to DHIS.
> https://bugs.launchpad.net/bugs/1211010
>
> Title:
> Analytics tables not generated when sql views linked to analytics
> tables exist
>
> Status in DHIS 2 - District Health Information Software:
> Confirmed
>
> Bug description:
> Analytics tables are not generated when sql view linked to the
> analytics table is present. Postgres cannot drop a table linked to a
> view without a cascade statement dropping the particular view. Since
> postgres cannot drop the analytics tables to populate a new one, the
> process is truncated .
>
> Using version 2.12 build 11268.
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/dhis2/+bug/1211010/+subscriptions
>
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-devs
> Post to : <email address hidden>
> Unsubscribe : https://launchpad.net/~dhis2-devs
> More help : https://help.launchpad.net/ListHelp
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-devs
> Post to : <email address hidden>
> Unsubscribe : https://launchpad.net/~dhis2-devs
> More help : https://help.launchpad.net/ListHelp
>

Changed in dhis2:
assignee: nobody → Lars Helge Øverland (larshelge)
milestone: none → 2.13
Changed in dhis2:
importance: Undecided → High
Changed in dhis2:
milestone: 2.13 → 2.14
Dapo Adejumo (dapo-adejumo) wrote :

This bug has somehow found its way back ( tested on 2.14 build revision 13844). Needs to be fixed asap.

Changed in dhis2:
status: Confirmed → Fix Committed
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers