[SQL] 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(-2, '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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] help with version checking
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] help with version checking
Tom Lane wrote: Arnau <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED]:~$ 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] help with version checking
Arnau wrote: Tom Lane wrote: Arnau <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED]:~$ 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] help with version checking
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; -- Arnau ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Very strange postgresql behaviour
Hi all, I have postgresql 7.4.2 running on debian and I have the oddest postgresql behaviour I've ever seen. I do the following queries: espsm_asme=# select customer_app_config_id, customer_app_config_name from customer_app_config where customer_app_config_id = 5929 or customer_app_config_id = 11527 order by customer_app_config_id; customer_app_config_id | customer_app_config_name +-- 5929 | INFO (1 row) I do the same query but changing the order of the or conditions: espsm_asme=# select customer_app_config_id, customer_app_config_name from customer_app_config where customer_app_config_id = 11527 or customer_app_config_id = 5929 order by customer_app_config_id; customer_app_config_id | customer_app_config_name +-- 11527 | MOVIDOSERENA TONI 5523 (1 row) As you can see, the configuration 5929 and 11527 both exists, but when I do the queries they don't appear. Here below you have the execution plans. Those queries use an index, I have done reindex table customer_app_config but nothing has changed. espsm_asme=# explain analyze select customer_app_config_id, customer_app_config_name from customer_app_config where customer_app_config_id = 11527 or customer_app_config_id = 5929 order by customer_app_config_id; QUERY PLAN Sort (cost=10.28..10.29 rows=2 width=28) (actual time=0.252..0.253 rows=1 loops=1) Sort Key: customer_app_config_id -> Index Scan using pk_cag_customer_application_id, pk_cag_customer_application_id on customer_app_config (cost=0.00..10.27 rows=2 width=28) (actual time=0.168..0.232 rows=1 loops=1) Index Cond: ((customer_app_config_id = 11527::numeric) OR (customer_app_config_id = 5929::numeric)) Total runtime: 0.305 ms (5 rows) espsm_asme=# explain analyze select customer_app_config_id, customer_app_config_name from customer_app_config where customer_app_config_id = 5929 or customer_app_config_id = 11527 order by customer_app_config_id; QUERY PLAN Sort (cost=10.28..10.29 rows=2 width=28) (actual time=0.063..0.064 rows=1 loops=1) Sort Key: customer_app_config_id -> Index Scan using pk_cag_customer_application_id, pk_cag_customer_application_id on customer_app_config (cost=0.00..10.27 rows=2 width=28) (actual time=0.034..0.053 rows=1 loops=1) Index Cond: ((customer_app_config_id = 5929::numeric) OR (customer_app_config_id = 11527::numeric)) Total runtime: 0.114 ms (5 rows) The table definition is the following: espsm_asme=# \d customer_app_config Table "public.customer_app_config" Column | Type | Modifiers --+---+ customer_app_config_id | numeric(10,0) | not null customer_app_config_name | character varying(32) | not null keyword | character varying(43) | application_id | numeric(10,0) | not null customer_id | numeric(10,0) | not null customer_app_contents_id | numeric(10,0) | number_access_id | numeric(10,0) | prefix | character varying(10) | separator| numeric(1,0) | default 0 on_hold | numeric(1,0) | not null default 0 with_toss| numeric(1,0) | not null default 0 number_id| numeric(10,0) | param_separator_id | numeric(4,0) | default 1 memory_timeout | integer | with_memory | numeric(1,0) | default 0 session_enabled | numeric(1,0) | default 0 session_timeout | integer | number | character varying(15) | Indexes: "pk_cag_customer_application_id" primary key, btree (customer_app_config_id) "un_cag_kwordnumber" unique, btree (keyword, number_id) "idx_cappconfig_ccontentsid" btree (customer_app_contents_id) "idx_cappconfig_cusidappid" btree (customer_id, application_id) "idx_cappconfig_customerid" btree (customer_id) "idx_cappconfig_onhold" btree (on_hold) "idx_cappconfig_onholdkeyw" btree (on_hold, keyword) Rules: A lot of rules that I don't paste as matter of length. Do you have any idea about how I can fix this? -- Arnau ---(end of broadcast)-
