Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id E15DA9F93A2 for ; Fri, 29 Dec 2006 07:59:56 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 20902-02 for ; Fri, 29 Dec 2006 07:59:32 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from mail.gmx.net (mail.gmx.net [213.165.64.20]) by postgresql.org (Postfix) with SMTP id D93329FA363 for ; Fri, 29 Dec 2006 07:59:48 -0400 (AST) Received: (qmail invoked by alias); 29 Dec 2006 11:59:46 -0000 Received: from Cfcf9.c.strato-dslnet.de (EHLO merkur.hilbert.loc) [62.104.252.249] by mail.gmx.net (mp029) with SMTP; 29 Dec 2006 12:59:46 +0100 X-Authenticated: #1433807 Received: from ncq by merkur.hilbert.loc with local (Exim 4.63) (envelope-from ) id 1H0GOn-0001Oy-6e for pgsql-sql@postgresql.org; Fri, 29 Dec 2006 12:59:45 +0100 Date: Fri, 29 Dec 2006 12:59:45 +0100 From: Karsten Hilbert To: pgsql-sql@postgresql.org Subject: Re: help with version checking Message-ID: <20061229115945.GD4668@merkur.hilbert.loc> References: <000001c72aa7$e705f1e0$0b00a8c0@tedy> <45940403.4010909@andromeiberica.com> <21284.1167328587@sss.pgh.pa.us> <459409A2.9020103@andromeiberica.com> <45940B07.4010708@andromeiberica.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <45940B07.4010708@andromeiberica.com> User-Agent: Mutt/1.5.13 (2006-08-11) X-Y-GMX-Trusted: 0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/185 X-Sequence-Number: 27141 In GNUmed we have created a function gm_concat_table_structure() in http://cvs.savannah.gnu.org/viewcvs/gnumed/gnumed/server/sql/gmSchemaRevisionViews.sql?rev=1.6&root=gnumed&view=log which returns a reproducable, human-readable TEXT concatenation of all the relevant parts of the schema. We then do select md5(gm_concat_table_structure()); and compare the output to known hashes for certain schema versions. That way we don't simply "believe" what is in a table "current_version" but rather actually *detect* (within reasonable limits) the version. It works well so far, no noticably delay even during client startup (which does the check and complains on mismatches). It may not scale particularly well to very large schemata, possibly. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346