Re: [Gnumed-devel] <bug> bootstrap database: not existing table referenced in v7->v8

[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