Re: update database v20-v21 failed

Hi Marc,

thanks for your report.

> tryed to update to Server v21 - plausability check failed. Log appended

You are on PG 9.4 which is good.

The schema has been converted successfully:

> 2016-03-25 18:12:25  DEBUG     gm.cfg (/var/lib/gnumed/Gnumed/pycommon/gmCfg2.py::get() #393): option [database gnumed_v21::target version] found in source [file]
> 2016-03-25 18:12:26  INFO      gm.db (/var/lib/gnumed/Gnumed/pycommon/gmPG2.py::database_schema_compatible() #594): detected schema version [21], hash [e6a51a89dd22b75b61ead8f7083f251f]
> 2016-03-25 18:12:26  INFO      gm.bootstrapper (./bootstrap_gm_db_system.py::verify_result_hash() #1064): database identity hash properly verified

But at least one plausibility check fails:

> 2016-03-25 18:12:28  ERROR     gm.bootstrapper (./bootstrap_gm_db_system.py::check_data_plausibility() #920): plausibility check [v_staff] failed, expected [4], found [7]

This means that either the check is faulty or the definition
of dem.v_staff is wrong.

The good news is that we do know that the data in the base
table dem.staff is OK:

> 2016-03-25 18:12:27  INFO      gm.bootstrapper (./bootstrap_gm_db_system.py::check_data_plausibility() #924): plausibility check [staff] succeeded

This:

	v_staff::::select count(1) from dem.v_staff
		select count(1) from dem.v_staff

is what the check actually checks for. And this

	expected [4], found [7]

is what is found, meaning that v21 dem.v_staff shows 3
entries more than what would have been expected by examining
v20 dem.v_staff. Now, given that the definition of
dem.v_staff has been modified from 

	create view dem.v_staff as
	select
		...
	from
		dem.staff s
			join dem.v_basic_person vbp on s.fk_identity = vbp.pk_identity
	;

to

	create view dem.v_staff as
	select
		...
	from
		dem.staff d_s
			join dem.v_all_persons d_vp on d_s.fk_identity = d_vp.pk_identity
	;

we notice that previously, dem.v_staff denormalized dem.staff
against a table of *active* persons while now it denormalizes
against *all* persons, even "deleted" (= inactive) ones. That
leads to dem.staff rows showing up the dem.identity row of
which had been deactivated earlier but had not been removed
from dem.staff. Quite possibly, such removal may not have
been possible for the reason that data stored in the database
is still linked to that (previous) member of staff.

In itself such unmasking of "invisible" staff accounts is a
good thing as it shows potential security concerns (it is not
a risk as yet but it might one day become one under
unforeseen circumstances).

You can investigate yourself by running

	select * from dem.v_staff

in both databases and looking at .person_is_deleted in
gnumed_v21.

There's several options for you to proceed:

- ignore the skipped checks and just use the database
  (technically, it was converted just fine, it is only
  that most checks haven't been run)

- remove the offending check from .../server/bootstrap/update_db-v20_v21.conf
  and rerun the upgrade (remember that your
  v20 database is always left untouched)

- edit .../server/bootstrap/update_db-v20_v21.conf to make

	v_staff::::select count(1) from dem.v_staff
		select count(1) from dem.v_staff

  read

	v_staff::::select count(1) from dem.v_staff
		select count(1) from dem.v_staff where not person_is_deleted

- wait for 1.6.2 to be released with the aforementioned fix


Please report !

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346