database version requirement switch: 9.1, was: Problem in git bootstrapping v18 to v19 db

On Tue, Jul 16, 2013 at 12:11:10AM +0000, Jim Busser wrote:

> > I have pushed this whole shebang of coding frenzy into the
> > public repository. It should be stress-tested for bugs.
> 
> Does the v18 --> v19 db upgrade SQL have a bug?
> 
> I can't run it successfully to completion.
> 
> It ends:
> 
> 2013-07-15 14:28:55  DEBUG     gm.bootstrapper (/Users/djb/git/gnumed/gnumed/gnumed/Gnumed/pycommon/gmPsql.py::run() #233): 
> select gm.log_script_insertion('v19-gm-schema_revision_handling.sql', '19.0')
> 2013-07-15 14:28:55  ERROR     gm.bootstrapper (/Users/djb/git/gnumed/gnumed/gnumed/Gnumed/pycommon/gmPsql.py::run() #238): ../sql/v18-v19/dynamic/v19-gm-schema_revision_handling.sql:181: column tbl.convalidated does not existLINE 1: ...ntdef(tbl.oid, true), ' ', '_') || '::active=' || tbl.conval...                                                             ^QUERY:   select * from (select tbl.contype, 'CONSTRAINT:type=' || tbl.contype || ':' || replace(pg_catalog.pg_get_constraintdef(tbl.oid, true), ' ', '_') || '::active=' || tbl.convalidated as condef from pg_catalog.pg_constraint tbl where tbl.conrelid = ( $1  || '.' ||  $2 )::regclass ) as FKs order by FKs.contype, decode(md5(FKs.condef), 'hex')CONTEXT:  PL/pgSQL function "concat_table_structure_v19_and_up" line 69 at FOR over SELEC
 T rowsSQL statement "select gm.concat_table_structure_v19_and_up()"PL/pgSQL function "concat_table_structure" line 4 at SQL statementSQL statement "select md5(gm.concat_table_structure())"PL/pgSQL function "log_script_insertion" line 14 at SQL statement

OK, so it seems older PG versions did not have
pg_constraint.convalidated. This column tells us whether a
given constraint is fully active on all rows of a given
table. This protects patient data against someone dropping a
constraint, inserting a bunch of invalidly linked data, and
recreating the constraint as NOT VALID (and thusly, inactive
on the existing, invalid rows).

Further examination shows that this has been introduced with
PG 9.1 which has been released in September 2011. Debian
Stable contains 9.1, too.

I should think this is the time to switch and require PG 9.1.

This will finally enable all sorts of nifty things to happen
such as a much improved database notification listener
(which should help with speed and some concurrency issues
resulting in hangs or crashes when accessing the EMR of a
patient).

So, I guess 1.4 will require PG 9.1.

Karsten
-- 
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346