I was interested to what SchemaUpdate did, and ran it against my Postgres database + mappings. It only generates the database creation script, not an update.

I know fom the documentation that SchemaUpdate relies on JDBC metadata to do its work, and I gather from looking at the Postgres JDBC mailing lists that the driver has some weakenesses in that area.

Is anyone using SchemaUpdate with Postgres successfully? If so, what configuration (versions etc) works?


Thanks,


Sherman

Hey,

I run the Ant SchemaUpdate target, and it looks like it generates the SQL fine, but it doesn't actually run it aganinst the database. I tried adding a field to one of my tables and SchmaUpdate prints to the console that it wishes to do "alter table users add column testString VARCHAR(255)", but nothing actually happens in the database?

When i try to run the generated SQL-line through my SQL-editor it works fine. Anyone have any ideas?

Postgres 7.2.1
pg73jdbc3.jar
Hibernate 2.1.2

Thanks,
Anders,

i have exactly the same with mySql, script is ok but i have to execute it manually (using phpMyAdmin for example)

I also have the same problem with Postgres 7.3.4, pg73jdbc3.jar, Hibernate 2.1.2. I get the following output, with a table that
already exists and only one field has been added.


[schemaupdate] 24-Mar-2004 12:52:45 net.sf.hibernate.cfg.Environment <clinit>
[schemaupdate] INFO: Hibernate 2.1.2
[schemaupdate] 24-Mar-2004 12:52:45 net.sf.hibernate.cfg.Environment <clinit>
[schemaupdate] INFO: hibernate.properties not found
[schemaupdate] 24-Mar-2004 12:52:45 net.sf.hibernate.cfg.Environment <clinit>
[schemaupdate] INFO: using CGLIB reflection optimizer
[schemaupdate] 24-Mar-2004 12:52:45 net.sf.hibernate.cfg.Configuration addFile
[schemaupdate] INFO: Mapping file: /home/retnuh/newbay/mixxe/mappings/User.hbm.xml
[schemaupdate] 24-Mar-2004 12:52:45 net.sf.hibernate.cfg.Binder bindRootClass
[schemaupdate] INFO: Mapping class: com.newbay.mixxe.domain.User -> users
[schemaupdate] 24-Mar-2004 12:52:46 net.sf.hibernate.dialect.Dialect <init>
[schemaupdate] INFO: Using dialect: net.sf.hibernate.dialect.PostgreSQLDialect
[schemaupdate] 24-Mar-2004 12:52:46 net.sf.hibernate.connection.DriverManagerConnectionProvider configure
[schemaupdate] INFO: Using Hibernate built-in connection pool (not for production use!)
[schemaupdate] 24-Mar-2004 12:52:46 net.sf.hibernate.connection.DriverManagerConnectionProvider configure
[schemaupdate] INFO: Hibernate connection pool size: 20
[schemaupdate] 24-Mar-2004 12:52:46 net.sf.hibernate.connection.DriverManagerConnectionProvider configure
[schemaupdate] INFO: using driver: org.postgresql.Driver at URL: jdbc:postgresql://localhost/mixxe
[schemaupdate] 24-Mar-2004 12:52:46 net.sf.hibernate.connection.DriverManagerConnectionProvider configure
[schemaupdate] INFO: connection properties: {user=mixxe, password=mixxe}
[schemaupdate] 24-Mar-2004 12:52:46 net.sf.hibernate.tool.hbm2ddl.SchemaUpdate execute
[schemaupdate] INFO: Running hbm2ddl schema update
[schemaupdate] 24-Mar-2004 12:52:46 net.sf.hibernate.tool.hbm2ddl.SchemaUpdate execute
[schemaupdate] INFO: fetching database metadata
[schemaupdate] 24-Mar-2004 12:52:46 net.sf.hibernate.tool.hbm2ddl.SchemaUpdate execute
[schemaupdate] INFO: updating schema
[schemaupdate] 24-Mar-2004 12:52:46 net.sf.hibernate.cfg.Configuration secondPassCompile
[schemaupdate] INFO: processing one-to-many association mappings
[schemaupdate] 24-Mar-2004 12:52:46 net.sf.hibernate.cfg.Configuration secondPassCompile
[schemaupdate] INFO: processing one-to-one association property references
[schemaupdate] 24-Mar-2004 12:52:46 net.sf.hibernate.cfg.Configuration secondPassCompile
[schemaupdate] INFO: processing foreign key constraints
[schemaupdate] create table users (id INT4 not null, version INT4 not null, userName VARCHAR(255) not null unique, password VARCHAR(255) not null, phoneNumber VARCHAR(255) not null unique, greenBeans INT4, primary key (id))
[schemaupdate] create sequence hibernate_sequence
[schemaupdate] 24-Mar-2004 12:52:46 net.sf.hibernate.tool.hbm2ddl.SchemaUpdate execute
[schemaupdate] INFO: schema update complete
[schemaupdate] 24-Mar-2004 12:52:46 net.sf.hibernate.connection.DriverManagerConnectionProvider close
[schemaupdate] INFO: cleaning up connection pool: jdbc:postgresql://localhost/mixxe

I have this identical problem but I am using Oracle 9i as my database.

Pretty much everything else (including the SchemaGenerateTask) works perfectly.

I am also having a similar issue:
Hibernate 2.1.6
PostgreSQL 7.4.3
pg74.215.jdbc3.jar (I had problems with the 7.3 driver on a 7.4 db.. )
proxool .8.3

Here's what I did:
1) Create Cat and Item mapping files.
2) Run SchemaExport, run resulting sql file in PgAdmin. No errors.
3) Create Person mapping file.
4) Run schema update.

Here's what I think is wrong:
1) There should be no SQL statements relating to Cat or Item, as they already exist in the DB and there was no modification to the mapping files.
2) The correct create table statement is issued for the Person table, but does not get executed by Postgres (no table is actually created.) The SQL is syntatically correct though.

I'm thinking this is most likely a JDBC problem with Postgres, but am not sure- anyone have any suggestions? For now I'm planning to use SchemaExport but not schema update.... SchemaUpdate would be extremely useful to me if I could get it to work. As a side note- it would also be nice if you could set a parameter to have SchemaUpdate export SQL just like SchemaExport does. (sometimes you don't want to auto-magically make updates to your DB ;)

Any help would be greatly appreciated.

Here's the output from my schema update ant task:
[SchemaUpdate] 10:15:01,445 INFO Environment:469 - Hibernate 2.1.6
[SchemaUpdate] 10:15:01,445 INFO Environment:498 - hibernate.properties not found
[SchemaUpdate] 10:15:01,461 INFO Environment:529 - using CGLIB reflection optimizer
[SchemaUpdate] 10:15:01,461 INFO Configuration:929 - configuring from file: hibernate.cfg.xml
[SchemaUpdate] 10:15:01,570 INFO Configuration:331 - Mapping resource: Cat.hbm
[SchemaUpdate] 10:15:01,679 INFO Binder:229 - Mapping class: test.Cat -> cat
[SchemaUpdate] 10:15:01,758 INFO Configuration:331 - Mapping resource: Item.hbm
[SchemaUpdate] 10:15:01,804 INFO Binder:229 - Mapping class: test.Item -> item
[SchemaUpdate] 10:15:01,804 INFO Configuration:331 - Mapping resource: Person.hbm
[SchemaUpdate] 10:15:01,836 INFO Binder:229 - Mapping class: test.Person -> person
[SchemaUpdate] 10:15:01,836 INFO Configuration:1053 - Configured SessionFactory: null
[SchemaUpdate] 10:15:01,867 INFO Dialect:82 - Using dialect: net.sf.hibernate.dialect.PostgreSQLDialect
[SchemaUpdate] 10:15:01,867 INFO ProxoolConnectionProvider:112 - Configuring Proxool Provider using JAXPConfigurator: proxool.cfg.xml
[SchemaUpdate] 10:15:01,898 DEBUG JAXPConfigurator:105 - Configuring from reader: java.io.InputStreamReader@1a06e38
[SchemaUpdate] 10:15:01,898 DEBUG JAXPConfigurator:72 - SAXParserFactory class: org.apache.xerces.jaxp.SAXParserFactoryImpl
[SchemaUpdate] 10:15:01,898 DEBUG JAXPConfigurator:77 - sax parser classorg.apache.xerces.jaxp.SAXParserImpl
[SchemaUpdate] 10:15:01,898 DEBUG JAXPConfigurator:81 - XML reader class: org.apache.xerces.parsers.SAXParser
[SchemaUpdate] 10:15:01,914 DEBUG JAXPConfigurator:114 - Setting sax feature: 'http://xml.org/sax/features/namespaces'. State: true.
[SchemaUpdate] 10:15:01,914 DEBUG JAXPConfigurator:114 - Setting sax feature: 'http://xml.org/sax/features/namespace-prefixes'. State: false.
[SchemaUpdate] 10:15:01,914 DEBUG XMLConfigurator:241 - Adding driver property: user=bjordan
[SchemaUpdate] 10:15:01,914 DEBUG XMLConfigurator:239 - Adding driver property: password=*******
[SchemaUpdate] 10:15:01,914 DEBUG XMLConfigurator:224 - Setting property 'proxool.maximum-connection-count' to value '10'.
[SchemaUpdate] 10:15:01,914 DEBUG XMLConfigurator:224 - Setting property 'proxool.house-keeping-test-sql' to value 'select CURRENT_DATE'.
[SchemaUpdate] 10:15:01,914 DEBUG XMLConfigurator:191 - Created url: proxool.hibertest:org.postgresql.Driver:jdbc:postgresql://ecom:5432/hibernate
[SchemaUpdate] 10:15:01,929 INFO ProxoolFacade:77 - Proxool 0.8.3 (14-Dec-2003 16:06)
[SchemaUpdate] 10:15:01,961 DEBUG hibertest:249 - Recognised proxool property: proxool.driver=org.postgresql.Driver
[SchemaUpdate] 10:15:01,976 DEBUG hibertest:249 - Recognised proxool property: proxool.url=jdbc:postgresql://ecom:5432/hibernate
[SchemaUpdate] 10:15:01,976 DEBUG hibertest:249 - Delegating property to driver: user=bjordan
[SchemaUpdate] 10:15:01,976 DEBUG hibertest:249 - Delegating property to driver: password=********
[SchemaUpdate] 10:15:01,976 DEBUG hibertest:249 - Recognised proxool property: proxool.maximum-connection-count=10
[SchemaUpdate] 10:15:01,976 DEBUG hibertest:249 - Recognised proxool property: proxool.house-keeping-test-sql=select CURRENT_DATE
[SchemaUpdate] 10:15:02,008 DEBUG ShutdownHook:79 - Registered shutdownHook
[SchemaUpdate] 10:15:02,023 DEBUG HouseKeeperController:81 - Registering 'hibertest' house keeper
[SchemaUpdate] 10:15:02,023 DEBUG HouseKeeperController:89 - Starting a house keeper thread
[SchemaUpdate] 10:15:02,023 INFO ProxoolConnectionProvider:132 - Configuring Proxool Provider to use pool alias: proxool.hibertest
[SchemaUpdate] 10:15:02,023 INFO SchemaUpdate:102 - Running hbm2ddl schema update
[SchemaUpdate] 10:15:02,023 INFO SchemaUpdate:110 - fetching database metadata
[SchemaUpdate] 10:15:02,133 DEBUG hibertest:162 - Remembering default value: isReadOnly() = false
[SchemaUpdate] 10:15:02,164 DEBUG hibertest:166 - org.postgresql.Driver does not support getHoldability. Proxool doesn't mind.
[SchemaUpdate] 10:15:02,164 DEBUG hibertest:177 - org.postgresql.Driver does not support setHoldability. Proxool doesn't mind.
[SchemaUpdate] 10:15:02,164 DEBUG hibertest:162 - Remembering default value: getTransactionIsolation() = 2
[SchemaUpdate] 10:15:02,164 DEBUG hibertest:162 - Remembering default value: getTypeMap() = null
[SchemaUpdate] 10:15:02,164 DEBUG hibertest:162 - Remembering default value: getCatalog() = hibernate
[SchemaUpdate] 10:15:02,336 INFO hibertest:613 - Proxool statistics legend: "s - r (a/t/o)" > s=served, r=refused (only shown if non-zero), a=active, t=total, o=offline (being tested)
[SchemaUpdate] 10:15:02,336 DEBUG hibertest:181 - 000000 (01/01/00) - Connection #1 created on demand = ACTIVE
[SchemaUpdate] 10:15:02,523 INFO SchemaUpdate:124 - updating schema
[SchemaUpdate] 10:15:02,523 INFO Configuration:627 - processing one-to-many association mappings
[SchemaUpdate] 10:15:02,523 INFO Configuration:636 - processing one-to-one association property references
[SchemaUpdate] 10:15:02,539 INFO Configuration:661 - processing foreign key constraints
[SchemaUpdate] create table item (id int8 not null, description varchar(255), name varchar(255), primary key (id))
[SchemaUpdate] create table cat (id int8 not null, sex varchar(1), name varchar(16), weight float4, age int4 not null, primary key (id))
[SchemaUpdate] create table person (id int8 not null, username varchar(255) not null, fname varchar(255) not null, lname varchar(255) not null, email varchar(255), primary key (id))
[SchemaUpdate] create sequence cat_id_seq
[SchemaUpdate] 10:15:02,601 INFO SchemaUpdate:143 - schema update complete
[SchemaUpdate] 10:15:02,601 INFO hibertest:474 - Shutting down 'hibertest' pool immediately [main]
[SchemaUpdate] 10:15:02,601 DEBUG hibertest:431 - 000001 (00/00/00) - #0001 removed because of shutdown.
[SchemaUpdate] 10:15:02,617 DEBUG hibertest:543 - Connection #1 closed
[SchemaUpdate] 10:15:02,617 INFO hibertest:560 - 'hibertest' pool has been closed down by main in 16 milliseconds.
[SchemaUpdate] 10:15:02,617 DEBUG ShutdownHook:42 - Removed shutdownHook
BUILD SUCCESSFUL
Total time: 2 seconds

Thanks,

Bucky