clarencen/lshift — Issue #81: Migration does not support Postgres boolean type
Author: clarencen
Created: 2011-11-14 22:41:38+00:00
State: open

The following error occurs when running a migration against a Postgres DB:

@@@
alter table config_options drop constraint FK80C74EA1C3C204DC
alter table endpoint drop constraint FK67C71D95C3C204DC
alter table endpoint_categories drop constraint FKEE1F9F06B6D4F2CB
alter table endpoint_categories drop constraint FKEE1F9F066D6BD5C8
alter table escalations drop constraint FK2B3C687E2E298B6C
alter table members drop constraint FK388EC9191902E93E
alter table members drop constraint FK388EC9195A11FA9E
alter table pair drop constraint FK3462DAF2DA557F
alter table pair drop constraint FK3462DAF68A3C7
alter table pair drop constraint FK3462DAC3C204DC
alter table prefix_category_descriptor drop constraint FK46474423466530AE
alter table range_category_descriptor drop constraint FKDC53C74E7A220B71
alter table repair_actions drop constraint FKF6BE324B2E298B6C
alter table set_category_descriptor drop constraint FKA51D45F39810CA56
alter table set_constraint_values drop constraint FK96C7B32744035BE4
drop table category_descriptor cascade
drop table config_options cascade
drop table diffs cascade
drop table domains cascade
drop table endpoint cascade
drop table endpoint_categories cascade
drop table escalations cascade
drop table members cascade
drop table pair cascade
drop table pending_diffs cascade
drop table prefix_category_descriptor cascade
drop table range_category_descriptor cascade
drop table repair_actions cascade
drop table set_category_descriptor cascade
drop table set_constraint_values cascade
drop table system_config_options cascade
drop table users cascade
drop sequence category_descriptor_sequence
drop sequence diffs_sequence
drop sequence pending_diffs_sequence
create table category_descriptor (category_id int4 not null, constraint_type varchar(255) not null, prefix_length int4, max_length int4, step int4, primary key (category_id))
create table config_options (opt_key varchar(255) not null, domain varchar(255) not null, opt_val varchar(255), primary key (opt_key, domain))
create table diffs (seq_id int4 not null, entity_id varchar(255), domain varchar(255), pair varchar(255), is_match bool, detected_at timestamp not null, last_seen timestamp not null, upstream_vsn varchar(255), downstream_vsn varchar(255), ignored bool, primary key (seq_id))
create table domains (name varchar(255) not null, primary key (name))
create table endpoint (name varchar(255) not null, domain varchar(255) not null, scan_url varchar(255), content_retrieval_url varchar(255), version_generation_url varchar(255), inbound_url varchar(255), content_type varchar(255) not null, inbound_content_type varchar(255), primary key (name, domain))
create table endpoint_categories (id varchar(255) not null, domain varchar(255) not null, category_descriptor_id int4 not null, name varchar(255) not null, primary key (id, domain, name))
create table escalations (name varchar(255) not null, pair_key varchar(255) not null, domain varchar(255) not null, action varchar(255) not null, action_type varchar(255) not null, event varchar(255) not null, origin varchar(255) not null, primary key (name, pair_key, domain))
create table members (domain_name varchar(255) not null, user_name varchar(255) not null, primary key (domain_name, user_name))
create table pair (pair_key varchar(255) not null, domain varchar(255) not null, upstream varchar(255), uep_domain varchar(255), downstream varchar(255), dep_domain varchar(255), version_policy_name varchar(255), matching_timeout int4, scan_cron_spec varchar(255), primary key (pair_key, domain))
create table pending_diffs (oid int4 not null, entity_id varchar(255), domain varchar(255), pair varchar(255), detected_at timestamp not null, last_seen timestamp not null, upstream_vsn varchar(255), downstream_vsn varchar(255), primary key (oid))
create table prefix_category_descriptor (id int4 not null, primary key (id))
create table range_category_descriptor (id int4 not null, data_type varchar(255), upper_bound varchar(255), lower_bound varchar(255), max_granularity varchar(255), primary key (id))
create table repair_actions (name varchar(255) not null, pair_key varchar(255) not null, domain varchar(255) not null, url varchar(255), scope varchar(255), primary key (name, pair_key, domain))
create table set_category_descriptor (id int4 not null, primary key (id))
create table set_constraint_values (value_id int4 not null, value_name varchar(255) not null, primary key (value_id, value_name))
create table system_config_options (opt_key varchar(255) not null, opt_val varchar(255), primary key (opt_key))
create table users (name varchar(255) not null, email varchar(255), password_enc varchar(255), superuser bool, primary key (name))
alter table config_options add constraint FK80C74EA1C3C204DC foreign key (domain) references domains
create index diff_last_seen on diffs (last_seen)
create index diff_detection on diffs (detected_at)
create index rdiff_ismatched on diffs (is_match)
create index rdiff_domain_idx on diffs (entity_id, domain, pair)
alter table endpoint add constraint FK67C71D95C3C204DC foreign key (domain) references domains
alter table endpoint_categories add constraint FKEE1F9F06B6D4F2CB foreign key (category_descriptor_id) references category_descriptor
alter table endpoint_categories add constraint FKEE1F9F066D6BD5C8 foreign key (id, domain) references endpoint
alter table escalations add constraint FK2B3C687E2E298B6C foreign key (pair_key, domain) references pair
alter table members add constraint FK388EC9191902E93E foreign key (domain_name) references domains
alter table members add constraint FK388EC9195A11FA9E foreign key (user_name) references users
alter table pair add constraint FK3462DAF2DA557F foreign key (downstream, dep_domain) references endpoint
alter table pair add constraint FK3462DAF68A3C7 foreign key (upstream, uep_domain) references endpoint
alter table pair add constraint FK3462DAC3C204DC foreign key (domain) references domains
create index pdiff_domain_idx on pending_diffs (entity_id, domain, pair)
alter table prefix_category_descriptor add constraint FK46474423466530AE foreign key (id) references category_descriptor
alter table range_category_descriptor add constraint FKDC53C74E7A220B71 foreign key (id) references category_descriptor
alter table repair_actions add constraint FKF6BE324B2E298B6C foreign key (pair_key, domain) references pair
alter table set_category_descriptor add constraint FKA51D45F39810CA56 foreign key (id) references category_descriptor
alter table set_constraint_values add constraint FK96C7B32744035BE4 foreign key (value_id) references category_descriptor
create sequence category_descriptor_sequence
create sequence diffs_sequence
create sequence pending_diffs_sequence
Failed to prepare the database - attempted to execute the following statements:
________________________________________________________________________________

insert into system_config_options(opt_val,opt_key) values(?,?)
insert into domains(name) values(?)
insert into users(email,password_enc,name,superuser) values(?,?,?,?)
________________________________________________________________________________

org.hibernate.exception.SQLGrammarException: error executing work
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52)
at org.hibernate.impl.SessionImpl.doWork(SessionImpl.java:2001)
at net.lshift.diffa.kernel.config.HibernateConfigStorePreparationStep$$anonfun$prepare$1.apply(HibernateConfigStorePreparationStep.scala:83)
at net.lshift.diffa.kernel.config.HibernateConfigStorePreparationStep$$anonfun$prepare$1.apply(HibernateConfigStorePreparationStep.scala:82)
at net.lshift.diffa.kernel.util.SessionHelper.withSession(SessionHelper.scala:32)
at net.lshift.diffa.kernel.config.HibernateConfigStorePreparationStep.prepare(HibernateConfigStorePreparationStep.scala:82)
at net.lshift.diffa.kernel.config.HibernateDomainConfigStoreTest$.sessionFactory(HibernateDomainConfigStoreTest.scala:555)
at net.lshift.diffa.kernel.config.HibernateDomainConfigStoreTest$.systemConfigStore(HibernateDomainConfigStoreTest.scala:563)
at net.lshift.diffa.kernel.config.HibernateDomainConfigStoreTest$.clearAllConfig(HibernateDomainConfigStoreTest.scala:567)
at net.lshift.diffa.kernel.config.HibernateDomainConfigStoreTest.setUp(HibernateDomainConfigStoreTest.scala:112)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:27)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:76)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)
at org.junit.runners.ParentRunner.run(ParentRunner.java:236)
at org.junit.runner.JUnitCore.run(JUnitCore.java:157)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:71)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:199)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:62)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:120)
Caused by: org.postgresql.util.PSQLException: ERROR: column "superuser" is of type boolean but expression is of type integer
Hint: You will need to rewrite or cast the expression.
Position: 70
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:381)
at net.lshift.hibernate.migrations.SingleStatementMigrationElement.apply(SingleStatementMigrationElement.java:29)
at net.lshift.hibernate.migrations.MigrationBuilder.apply(MigrationBuilder.java:85)
at net.lshift.diffa.kernel.config.HibernateConfigStorePreparationStep$$anonfun$prepare$1$$anon$1.execute(HibernateConfigStorePreparationStep.scala:92)
at org.hibernate.impl.SessionImpl.doWork(SessionImpl.java:1997)
... 33 more
@@@