<bug> bootstrap database: not existing table referenced in v7->v8

[Jerzy Luszawski]

Hello,
I've just tried to bootstrap v12 database from CVS, and I was surprised by
error during v7->v8 upgrade.

I have found that in module gmNotificationSchemaGenerator.py a
table 'message_inbox' is referenced, while it is known under this name only
since v12 (previously: 'provider_inbox').

Reverting this name in the gmNotificationSchemaGenerator.py solved the problem
for me, but I think it requires more attention.
Also dem.trf_announce_provider_inbox_generic_mod_no_pk() remains in v12
(shouldn't it be dropped?)

Regards,
Jerzy Luszawski


_______________________________________________
Gnumed-devel mailing list
Gnumed-devel@...
http://lists.gnu.org/mailman/listinfo/gnumed-devel

[Karsten Hilbert]

> I've just tried to bootstrap v12 database from CVS, and I was surprised by
> error during v7->v8 upgrade.
>
> I have found that in module gmNotificationSchemaGenerator.py a
> table 'message_inbox' is referenced, while it is known under this name only
> since v12 (previously: 'provider_inbox').

True enough, it was renamed because it can now be used for
messages generically, not limited to provider bound messages.

> Reverting this name in the gmNotificationSchemaGenerator.py solved the problem
> for me, but I think it requires more attention.

Yes. The proper fix is to disable generating audit and
notification schema objects below v12 (in CVS HEAD, that
is) which I did.

> Also dem.trf_announce_provider_inbox_generic_mod_no_pk() remains in v12
> (shouldn't it be dropped?)

It should and in fact there's explicit code to do so in
v12-dem-message_inbox-dynamic.sql under sql/dynamic/ which
is also duly included in the .conf file. So you may want to
double-check with the above fix.

Checked in.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


_______________________________________________
Gnumed-devel mailing list
Gnumed-devel@...
http://lists.gnu.org/mailman/listinfo/gnumed-devel

[Jerzy Luszawski]

Tuesday 27 October 2009 11:51:34 Karsten Hilbert napisał(a):

> > I have found that in module gmNotificationSchemaGenerator.py a
> > table 'message_inbox' is referenced, while it is known under this name
> > only since v12 (previously: 'provider_inbox').
>
> True enough, it was renamed because it can now be used for
> messages generically, not limited to provider bound messages.
>
> > Reverting this name in the gmNotificationSchemaGenerator.py solved the
> > problem for me, but I think it requires more attention.
>
> Yes. The proper fix is to disable generating audit and
> notification schema objects below v12 (in CVS HEAD, that
> is) which I did.
>
> > Also dem.trf_announce_provider_inbox_generic_mod_no_pk() remains in v12
> > (shouldn't it be dropped?)
>
> It should and in fact there's explicit code to do so in
> v12-dem-message_inbox-dynamic.sql under sql/dynamic/ which
> is also duly included in the .conf file. So you may want to
> double-check with the above fix.
>
> Checked in.

I tried to bootstrap it, but was stuck at different place:
--- log file:
2009-10-27 22:18:55  ERROR     gm.bootstrapper (/home/jlk/gnumed-CVS/gnumed/gnumed/Gnumed/pycommon/gmPsql.py::run() #231): ../sql/v8-v9/dynamic/v9-clin-health_issue-dynamic.sql:111:
record "old" has no field "id_patient"CONTEXT:  PL/pgSQL function "ft_upd_health_issue" line 5 at SQL statementSQL statement "update clin.health_issue set fk_encounter =  $1  where pk =  
$2 "PL/pgSQL function "tmp_add_encounters_to_issues" line 43 at SQL statement
2009-10-27 22:18:55  ERROR     gm.bootstrapper (./bootstrap_gm_db_system.py::_import_schema() #1248): failed to import [../sql/v8-v9/dynamic/v9-clin-health_issue-dynamic.sql]
2009-10-27 22:18:55  ERROR     gm.bootstrapper (./bootstrap_gm_db_system.py::bootstrap() #1112): Cannot import schema definition for bundle [v8-v9-dynamic] into database [gnumed_v9].
--- when trying it under psql:
gnumed_v9=> \i ../sql/v8-v9/dynamic/v9-clin-health_issue-dynamic.sql
COMMENT
psql:../sql/v8-v9/dynamic/v9-clin-health_issue-dynamic.sql:20: NOTICE:  drop cascades to trigger tr_ensure_issue_encounter_patient_consistency on table clin.health_issue
DROP FUNCTION
CREATE FUNCTION
CREATE TRIGGER
CREATE FUNCTION
psql:../sql/v8-v9/dynamic/v9-clin-health_issue-dynamic.sql:112: NOTICE:  issue: 1
psql:../sql/v8-v9/dynamic/v9-clin-health_issue-dynamic.sql:112: NOTICE:  creating new encounter
psql:../sql/v8-v9/dynamic/v9-clin-health_issue-dynamic.sql:112: NOTICE:  linking issue (1) <-> encounter (7)
psql:../sql/v8-v9/dynamic/v9-clin-health_issue-dynamic.sql:112: ERROR:  record "old" has no field "id_patient"
CONTEXT:  PL/pgSQL function "ft_upd_health_issue" line 5 at SQL statement
SQL statement "update clin.health_issue set fk_encounter =  $1  where pk =  $2 "
PL/pgSQL function "tmp_add_encounters_to_issues" line 52 at SQL statement
----------------------------------
This did not happen previously.
After the investigation i changed back "audit disable" to  0 in update_db-v7_v8,conf and update_db-v7_v8.conf, and the bootstrapping went on, until v11->v12:
---------- log:
2009-10-27 23:22:41  DEBUG     gm.bootstrapper (/home/jlk/gnumed-CVS/gnumed/gnumed/Gnumed/pycommon/gmPsql.py::run() #226):
alter table audit.log_substance_brand
        add column external_code text
2009-10-27 23:22:41  ERROR     gm.bootstrapper (/home/jlk/gnumed-CVS/gnumed/gnumed/Gnumed/pycommon/gmPsql.py::run() #231): ../sql/v11-v12/static/v12-clin-substance_brand-static.sql:19:
relation "audit.log_substance_brand" does not exist
2009-10-27 23:22:41  ERROR     gm.bootstrapper (./bootstrap_gm_db_system.py::_import_schema() #1248): failed to import [../sql/v11-v12/static/v12-clin-substance_brand-static.sql]
--------------------------------
I did the same for update_db-v10_v11.conf ( "audit disable = 0") and finally got v12 ready.

I have checked-in these small changes to CVS.
Wouldn't it be better to disable all auditing before upgrading database?

BTW: Since which database version the notification schema is used? v12?

Jerzy Luszawski


_______________________________________________
Gnumed-devel mailing list
Gnumed-devel@...
http://lists.gnu.org/mailman/listinfo/gnumed-devel

[Jim Busser]

>> It should and in fact there's explicit code to do so in
>> v12-dem-message_inbox-dynamic.sql under sql/dynamic/ which
>> is also duly included in the .conf file. So you may want to
>> double-check with the above fix.
>>
>> Checked in.

v12 is development and as such not yet released and also remains  
advised against (?) for production systems?


_______________________________________________
Gnumed-devel mailing list
Gnumed-devel@...
http://lists.gnu.org/mailman/listinfo/gnumed-devel

[Karsten Hilbert]

On Tue, Oct 27, 2009 at 05:44:33PM -0700, Jim Busser wrote:

> >>It should and in fact there's explicit code to do so in
> >>v12-dem-message_inbox-dynamic.sql under sql/dynamic/ which
> >>is also duly included in the .conf file. So you may want to
> >>double-check with the above fix.
> >>
> >>Checked in.
>
> v12 is development and as such not yet released and also remains
> advised against (?) for production systems?

Oh, we are fixing stuff back in v7/v8.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


_______________________________________________
Gnumed-devel mailing list
Gnumed-devel@...
http://lists.gnu.org/mailman/listinfo/gnumed-devel

[Karsten Hilbert]

On Wed, Oct 28, 2009 at 12:25:52AM +0100, Jerzy Luszawski wrote:

> I tried to bootstrap it, but was stuck at different place:
> --- log file:
> 2009-10-27 22:18:55  ERROR     gm.bootstrapper (/home/jlk/gnumed-CVS/gnumed/gnumed/Gnumed/pycommon/gmPsql.py::run() #231): ../sql/v8-v9/dynamic/v9-clin-health_issue-dynamic.sql:111:

Here we are adding a column fk_encounter to
clin.health_issue. The reasoning is that we want to be able
to track during which encounter a particular health issue
was created.

> record "old" has no field "id_patient"CONTEXT:  PL/pgSQL function "ft_upd_health_issue" line 5 at SQL statementSQL statement "update clin.health_issue set fk_encounter =  $1  where pk =  
> $2 "PL/pgSQL function "tmp_add_encounters_to_issues" line 43 at SQL statement

Here we are creating new encounters to link the issues to.
We are using a dedicated temporary stored procedure for
that. Updating clin.health_issue activates the audit
trigger. Which fails because it assumes a column id_patient
rather than fk_patient (that renaming was done v2 -> v3 in
both the health_issue table and the audit table). Usually
the audit trigger is updated whenever the audit schema is
regenerated. During bootstrapping this is only done when
"audit disable = 0" ;-)    Ah, now I recall, that was the
reason why it was set to 0 in v7 -> v8 :-))

> CONTEXT:  PL/pgSQL function "ft_upd_health_issue" line 5 at SQL statement
> SQL statement "update clin.health_issue set fk_encounter =  $1  where pk =  $2 "
> PL/pgSQL function "tmp_add_encounters_to_issues" line 52 at SQL statement
> psql:../sql/v8-v9/dynamic/v9-clin-health_issue-dynamic.sql:112: NOTICE:  issue: 1
> psql:../sql/v8-v9/dynamic/v9-clin-health_issue-dynamic.sql:112: NOTICE:  creating new encounter
> psql:../sql/v8-v9/dynamic/v9-clin-health_issue-dynamic.sql:112: NOTICE:  linking issue (1) <-> encounter (7)
> psql:../sql/v8-v9/dynamic/v9-clin-health_issue-dynamic.sql:112: ERROR:  record "old" has no field "id_patient"

Yep, there it is ...

> This did not happen previously.
> After the investigation i changed back "audit disable" to  0 in update_db-v7_v8.conf

That's correct.

, and the bootstrapping went on, until v11->v12:
> ---------- log:
> 2009-10-27 23:22:41  DEBUG     gm.bootstrapper (/home/jlk/gnumed-CVS/gnumed/gnumed/Gnumed/pycommon/gmPsql.py::run() #226):
> alter table audit.log_substance_brand
> add column external_code text
> 2009-10-27 23:22:41  ERROR     gm.bootstrapper (/home/jlk/gnumed-CVS/gnumed/gnumed/Gnumed/pycommon/gmPsql.py::run() #231): ../sql/v11-v12/static/v12-clin-substance_brand-static.sql:19:
> relation "audit.log_substance_brand" does not exist

Ah, OK, I see. The table substance_brand was created in v11.
Only if the audit generator is run will the audit table be
created. And in that file we try to adjust the log table
cloning a change in the original table.

> I did the same for update_db-v10_v11.conf ( "audit disable = 0") and finally got v12 ready.

Works for me, too, now.

> Wouldn't it be better to disable all auditing before upgrading database?

During upgrading we sometimes apply updates to the data
itself (such as in the above case where we newly link issues
to encounters). If we disabled auditing during bootstrapping
we would create gaps in the audit trail :-)

> BTW: Since which database version the notification schema is used? v12?

Nope, v2.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


_______________________________________________
Gnumed-devel mailing list
Gnumed-devel@...
http://lists.gnu.org/mailman/listinfo/gnumed-devel