Special considerations targeting PostgreSQL?

General Questions/Discussions
1

Hi there,

I’m having very weird problems when working with PostgreSQL. It’s hard to identify exactly what’s wrong as things seems to change every time I try to do something new. I’m working with ORM classes, synchronizing to ER diagrams and generating both SQL and Java ORM code. All settings are default except enabling quoted SQL and disabling comments.

Once I get everything to build properly in dbva I’m presented with random problems when trying to use the generated code. One strange error is hibernate blowing up when interpretting table columns as integers when they’re defined as strings<->varchars. Both mappings, java code and DDLs are ok, no integers to be seen anywhere.

Another, much more frequent, error is objects failing to persist properly. Creating an object using a factory yields an unpersisted object that I can configure normally. When happy I call save() on that object and expect the object to be persisted. Which it is. But in many cases the object is not updated with a corresponding ID which of course causes persistence exceptions if any other object is dependent on that object. Still, when looking in the database tables everything is as expected. This particular error seems to be reproducable by just adding a one-to-many association.

What makes me a bit frustrated is that the very same model works perfectly when targeting mysql or sql server. Without a flaw.

This must be the most useless error report I’ve ever written but there’s so much going wrong here that I’m forced to think that there must be something fundamentally wrong with my setup. I’ve been using VP for several years working with mysql, sql server and oracle and I Never had this kind of problems. On the other hand I never used postgres together with VP before so my question is really: Is there a postgres-specific configuration required that I’ve missed alltogether? As compared to setting up a project targeting mysql or sql server?

I’m using DBVA 5.1 (20091009), postgresql 8.4 and JDBC4 driver 8.4-701. Everything running on Mac OS X 10.6.1 (also tried on Mac OS X 10.5.x)

/Magnus Drougge

2

Hello Drougge,

Thank you for your message. Would you mind send me your project file also some sample program to repeat the problem? You can send it to support@visual-paradigm.com. Thanks in advance!

Best regards,
Rain

3

I think I solved the problem. Somewhere in the back of my head a small bell was ringing and all of a sudden I realised that postgres uses sequences for id generation. By just setting the default id generator for the project to sequence instead of native and regenerating the ER diagram, database and code I got everything to work exactly as expected.

One could argue that dbva would automatically handle this when using ‘native’ and to some extent it seems to be doing just that. When looking in the database all expected sequences are there. But as I said in my previous post, weird problems arise when you actually use the generated code.

Anyway, I’ll create a simple model that reproduces the problem and send it in as requested.

Best Regards,

/Magnus Drougge