Review Board 1.7.22


HIVE-3995 PostgreSQL upgrade scripts are not valid

Review Request #9349 - Created Feb. 7, 2013 and submitted

Jarek Cecho
HIVE-3995
Reviewers
hive
hive
I found issues in three files:

* 010-HIVE-3072.postgres.sql

Just escaping double quotes were missing.

* 011-HIVE-3649.postgres.sql

This alter statement tries to add new "not null" column without any default value. The statement will fail if the table already contains some data. I've checked similar script in mysql/ folder and that script is allowing null values. Thus I've allowed null values also here.

* 012-HIVE-1362.postgres

Just syntax fixes.
I've tested the upgrade scripts by loading file hive-schema-0.9.0.postgres.sql with some data and executing upgrade-0.9.0-to-0.10.0.postgres.sql on top of that.
/trunk/metastore/scripts/upgrade/postgres/010-HIVE-3072.postgres.sql
Revision 1443292 New Change
[20] 10 lines
[+20]
11
    "STRING_LIST_ID" bigint NOT NULL,
11
    "STRING_LIST_ID" bigint NOT NULL,
12
    "STRING_LIST_VALUE" character varying(256) DEFAULT NULL::character varying,
12
    "STRING_LIST_VALUE" character varying(256) DEFAULT NULL::character varying,
13
    "INTEGER_IDX" bigint NOT NULL
13
    "INTEGER_IDX" bigint NOT NULL
14
);
14
);
15

    
   
15

   
16
ALTER TABLE ONLY SKEWED_STRING_LIST_VALUES"
16
ALTER TABLE ONLY "SKEWED_STRING_LIST_VALUES"
17
    ADD CONSTRAINT "SKEWED_STRING_LIST_VALUES_pkey" PRIMARY KEY ("STRING_LIST_ID", "INTEGER_IDX");
17
    ADD CONSTRAINT "SKEWED_STRING_LIST_VALUES_pkey" PRIMARY KEY ("STRING_LIST_ID", "INTEGER_IDX");
18

    
   
18

   
19
ALTER TABLE ONLY "SKEWED_STRING_LIST_VALUES"
19
ALTER TABLE ONLY "SKEWED_STRING_LIST_VALUES"
20
    ADD CONSTRAINT "SKEWED_STRING_LIST_VALUES_fkey" FOREIGN KEY ("STRING_LIST_ID") REFERENCES "SKEWED_STRING_LIST"("STRING_LIST_ID") DEFERRABLE;
20
    ADD CONSTRAINT "SKEWED_STRING_LIST_VALUES_fkey" FOREIGN KEY ("STRING_LIST_ID") REFERENCES "SKEWED_STRING_LIST"("STRING_LIST_ID") DEFERRABLE;
21

    
   
21

   
[+20] [20] 41 lines
/trunk/metastore/scripts/upgrade/postgres/011-HIVE-3649.postgres.sql
Revision 1443292 New Change
 
/trunk/metastore/scripts/upgrade/postgres/012-HIVE-1362.postgres.sql
Revision 1443292 New Change
 
  1. /trunk/metastore/scripts/upgrade/postgres/010-HIVE-3072.postgres.sql: Loading...
  2. /trunk/metastore/scripts/upgrade/postgres/011-HIVE-3649.postgres.sql: Loading...
  3. /trunk/metastore/scripts/upgrade/postgres/012-HIVE-1362.postgres.sql: Loading...