Обсуждение: help with version checking
Hi all!,
I've got the following problem and I don't know how to solve it in
PostgreSQL.
I'd like to add a version checking to my db scripts. That is, I have
the db creation scripts and the changes/upgrade script, and there is a
table inside each db that holds the version of script executed. So
before apply the upgrade script I'd like to check if the installed
version matches the expected if not then show an error and terminate the
script execution.
In Oracle I used to do:
DEFINE PREVIOUS_VERSION = '2.3.5.10'
DECLARE v_version varchar2(100); v_ok number;
BEGIN select version into v_version from version where id = 1; if v_version <> '&PREVIOUS_VERSION' then
RAISE_application_error(-20000, 'This script needs SMC version [' || '&PREVIOUS_VERSION' ||
']detected version is [' || v_version || ']' ); end if;
END;
/
I tried to do the following in PostgreSQL:
DECLARE v_version VARCHAR;
BEGIN SELECT version INTO v_version FROM version WHERE id = 1;
IF v_version <> ''1.0.0.0'' THEN RAISE EXCEPTION ''This script needs Agenda version 1.0.0.0,
detected version %'', v_version; END IF;
END;
//The upgrade stuff
but when I execute it, gives a lot of errors:
psql -d dermagier -f upgrade_agenda.sql
psql:upgrade_agenda.sql:2: ERROR: syntax error at or near "VARCHAR" at
character 21
psql:upgrade_agenda.sql:5: ERROR: syntax error at or near "SELECT" at
character 9
psql:upgrade_agenda.sql:8: ERROR: syntax error at or near "IF" at
character 3
psql:upgrade_agenda.sql:9: ERROR: syntax error at or near "IF" at
character 7
psql:upgrade_agenda.sql:11: WARNING: there is no transaction in progress
COMMIT
Anybody knows how I can do this or which is the best way to do it?
Thank you very much
--
Arnau
Hi Daniel, > You should define a PL/PGSQL function such as: > > CREATE OR REPLACE FUNCTION check_version() > RETURNS void > AS $$ > DECLARE > v_version VARCHAR; > BEGIN > SELECT version INTO v_version FROM version WHERE id = 1; > > IF v_version <> '1.0.0.0' THEN > RAISE EXCEPTION 'This script needs Agenda version 1.0.0.0, detected > version %', v_version; > END IF; > END; > $$ LANGUAGE PLPGSQL; I don't want, if it's possible, to create a function. I just want to check a value stored in a table and if doesn't match the expected one then abort the script execution. -- Arnau
Arnau <arnaulist@andromeiberica.com> writes:
> I don't want, if it's possible, to create a function.
Unlike Oracle, PG makes a strong distinction between SQL and
programmable languages (including plpgsql). You can't write
plpgsql code without putting it into a function.
regards, tom lane
Tom Lane wrote: > Arnau <arnaulist@andromeiberica.com> writes: >> I don't want, if it's possible, to create a function. > > Unlike Oracle, PG makes a strong distinction between SQL and > programmable languages (including plpgsql). You can't write > plpgsql code without putting it into a function. > > regards, tom lane > I've tried Daniel's suggestion but the Raise doesn't terminate the script execution, so if doesn't do what I need. Notice the Update 1 arebassa@beowulf:~$ psql -d dermagier -f upgrade_agenda.sql CREATE FUNCTION psql:upgrade_agenda.sql:16: ERROR: This script needs Agenda version 1.0.0.0, detected version 1.0.0.1 UPDATE 1 -- Arnau
Arnau wrote: > Tom Lane wrote: >> Arnau <arnaulist@andromeiberica.com> writes: >>> I don't want, if it's possible, to create a function. >> >> Unlike Oracle, PG makes a strong distinction between SQL and >> programmable languages (including plpgsql). You can't write >> plpgsql code without putting it into a function. >> >> regards, tom lane >> > > I've tried Daniel's suggestion but the Raise doesn't terminate the > script execution, so if doesn't do what I need. Notice the Update 1 > > arebassa@beowulf:~$ psql -d dermagier -f upgrade_agenda.sql > CREATE FUNCTION > psql:upgrade_agenda.sql:16: ERROR: This script needs Agenda version > 1.0.0.0, detected version 1.0.0.1 > UPDATE 1 > > I paste the script I have created: CREATE OR REPLACE FUNCTION check_version() RETURNS void AS ' DECLARE v_version VARCHAR; BEGIN SELECT version INTO v_version FROM agenda_version WHERE id = 1; IF v_version <> ''1.0.0.0'' THEN RAISE EXCEPTION ''This script needs Agenda version 1.0.0.0, detected version %'', v_version; END IF; END; ' LANGUAGE 'plpgsql'; SELECT check_version(); UPDATE agenda_version set version = '1.0.0.1' where id = 1; -- Arnau
Can you do the whole thing inside a transaction context (both the version check and the updates)? The exception should cause the transaction to bail out, and the updates won't proceed. Thus: BEGIN; SELECT check_version(); UPDATE agenda_version set version = '1.0.0.1' where id = 1; COMMIT; I tried it with your script and it seemed to work for me. Hope this helps... -chris Arnau wrote: > Tom Lane wrote: >> Arnau <arnaulist@andromeiberica.com> writes: >>> I don't want, if it's possible, to create a function. >> >> Unlike Oracle, PG makes a strong distinction between SQL and >> programmable languages (including plpgsql). You can't write >> plpgsql code without putting it into a function. >> >> regards, tom lane >> > > I've tried Daniel's suggestion but the Raise doesn't terminate the > script execution, so if doesn't do what I need. Notice the Update 1 > > arebassa@beowulf:~$ psql -d dermagier -f upgrade_agenda.sql > CREATE FUNCTION > psql:upgrade_agenda.sql:16: ERROR: This script needs Agenda version > 1.0.0.0, detected version 1.0.0.1 > UPDATE 1 > >
> I tried to do the following in PostgreSQL: > > DECLARE > v_version VARCHAR; > > BEGIN > SELECT version INTO v_version FROM version WHERE id = 1; > > IF v_version <> ''1.0.0.0'' THEN > RAISE EXCEPTION ''This script needs Agenda version 1.0.0.0, > detected version %'', v_version; > END IF; > > END; > > //The upgrade stuff > > but when I execute it, gives a lot of errors: > > psql -d dermagier -f upgrade_agenda.sql > psql:upgrade_agenda.sql:2: ERROR: syntax error at or near "VARCHAR" at > character 21 > psql:upgrade_agenda.sql:5: ERROR: syntax error at or near "SELECT" at > character 9 > psql:upgrade_agenda.sql:8: ERROR: syntax error at or near "IF" at > character 3 > psql:upgrade_agenda.sql:9: ERROR: syntax error at or near "IF" at > character 7 > psql:upgrade_agenda.sql:11: WARNING: there is no transaction in progress > COMMIT > > > Anybody knows how I can do this or which is the best way to do it? > You should define a PL/PGSQL function such as: CREATE OR REPLACE FUNCTION check_version() RETURNS void AS $$ DECLARE v_version VARCHAR; BEGIN SELECT version INTO v_version FROM version WHERE id = 1; IF v_version <> '1.0.0.0' THEN RAISE EXCEPTION 'This scriptneeds Agenda version 1.0.0.0, detected version %', v_version; END IF; END; $$ LANGUAGE PLPGSQL; Regards, -- Daniel
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
Hi all,
Thanks for all replies, taking into account all your suggestions and
my google research I arrived to the next script. I'd like to know your
opinion. Hopefully this will be useful for somebody else.
--------------------------------
--used to stop the script execution on any error
\set ON_ERROR_STOP 1
--disable the autocommit
\set AUTOCOMMIT off
BEGIN;
/* Helper function used to check the current version. If it isn't the expected then raise an error an abort the
installation. */ CREATE OR REPLACE FUNCTION check_version() RETURNS void AS ' DECLARE current_version VARCHAR;
needed_version VARCHAR;
BEGIN --define the expected version needed_version := ''1.0.0.0'';
SELECT version INTO current_version FROM agenda_version WHERE id = 1;
IF current_version <> needed_version THEN RAISE EXCEPTION ''This script needs Agenda version %, detected
version %'', needed_version, current_version; RETURN; END IF;
RETURN;
END; ' LANGUAGE 'plpgsql';
/* Helper function used update the version to the current version. */ CREATE OR REPLACE FUNCTION
update_version()RETURNS void AS' DECLARE current_version VARCHAR;
BEGIN current_version := ''1.0.0.1'';
UPDATE agenda_version set version = current_version where id = 1;
RETURN; END; ' LANGUAGE 'plpgsql';
/* The first action ALWAYS MUST BE SELECT check_version() to ensure that the current version is the one needed
forthis changes script. */ SELECT check_version();
/* All the actions that must be performed by the changes script */
/* The last actions ALWAYS MUST BE: SELECT update_version(); DROP FUNCTION check_version(); DROP
FUNCTIONupdate_version();
to update the script version and remove the helper functions */ SELECT update_version(); DROP FUNCTION
check_version(); DROP FUNCTION update_version();
--close the transaction
END;
--
Arnau
I'd probably make a small change to make this a little cleaner.
Specifically, change check_version() to take an argument, which is the
needed version, and check this against the current value in
agenda_version, throwing the exception if they don't match. Once you've
written this, you'll never need to touch it again (no more DROP
FUNCTIONs required).
Then, at the end of your update script, you update the version in the
table via normal SQL (no need for a single-use function that does this).
With these tweaks, your update scripts could be simpler, like this:
BEGIN;
SELECT check_version('1.0.0.0');
-- Do all your updates etc. here --
UPDATE agenda_version SET version = '1.0.0.1' WHERE id =1;
COMMIT;
HTH. Good luck...
-chris
Arnau wrote:
> Hi all,
>
> Thanks for all replies, taking into account all your suggestions and
> my google research I arrived to the next script. I'd like to know your
> opinion. Hopefully this will be useful for somebody else.
>
>
>
> --------------------------------
>
> --used to stop the script execution on any error
> \set ON_ERROR_STOP 1
>
> --disable the autocommit
> \set AUTOCOMMIT off
>
> BEGIN;
>
> /*
> Helper function used to check the current version. If it isn't
> the expected then raise an error an abort the installation.
> */
> CREATE OR REPLACE FUNCTION check_version() RETURNS void AS '
> DECLARE
> current_version VARCHAR;
> needed_version VARCHAR;
>
> BEGIN
> --define the expected version
> needed_version := ''1.0.0.0'';
>
> SELECT version INTO current_version FROM agenda_version WHERE id
> = 1;
>
> IF current_version <> needed_version THEN
> RAISE EXCEPTION ''This script needs Agenda version %, detected
> version %'', needed_version, current_version;
> RETURN;
> END IF;
>
> RETURN;
>
> END;
> ' LANGUAGE 'plpgsql';
>
>
>
> /*
> Helper function used update the version to the current version.
> */
> CREATE OR REPLACE FUNCTION update_version() RETURNS void AS'
> DECLARE
> current_version VARCHAR;
>
> BEGIN
> current_version := ''1.0.0.1'';
>
> UPDATE agenda_version set version = current_version where id = 1;
>
> RETURN;
> END;
> ' LANGUAGE 'plpgsql';
>
>
>
>
> /*
> The first action ALWAYS MUST BE SELECT check_version() to ensure
> that the current version is the one needed for this changes script.
> */
> SELECT check_version();
>
>
>
> /*
> All the actions that must be performed by the changes script
> */
>
>
>
> /*
> The last actions ALWAYS MUST BE:
> SELECT update_version();
> DROP FUNCTION check_version();
> DROP FUNCTION update_version();
>
> to update the script version and remove the helper functions
> */
> SELECT update_version();
> DROP FUNCTION check_version();
> DROP FUNCTION update_version();
>
>
>
> --close the transaction
> END;
>
>