Finding table for Oracle constraint violation

When Oracle reports a constraint violation, you might get something unhelpfully cryptic like:

ORA-00001: unique constraint (FOO.SYS_C003567231) violated

The constraint is system-generated, based on UNIQUE being specified in the DDL, so the name is unhelpful. With no other information, finding the affected table can be difficult. You can query for the table with the following SQL:

select constraint_name, table_name
from user_constraints
where constraint_name='SYS_C003567231

This is a handy query to know, so I'm noting it here for my future reference. It's also described in a number of other blogs like this one.

Reconsidering Java cloud hosting with PaaS

I have been looking into Java cloud hosting for my Groovy on Grails application, and I am quite pleased at what has been happening in this space. I last explored cloud hosting a couple of years ago. By contrast, today's PaaS (Platform as a Service) options look quite promising, particularly Cloud Foundry and Jelastic.

From NoSQL to NoServer

I attended an interesting presentation recently on CouchDB, a document store database that helped make the term NoSQL famous. One of the interesting ideas discussed is the idea of CouchApps: pure JavaScript/HTML applications working with and interacting exclusively with CouchDB. So not only do you not need a relational database, you can dump your application server too.

NoSQL: triumph of application over data

NoSQL databases are the rage now, aren’t they? Not too long ago, NEJUG hosted a presentation by Tim Berglund called "NoSQL Smackdown." Before giving his tour of several NoSQL databases, Tim tried to give his definition of "NoSQL." After showing the difficulties of defining NoSQL as a series of negatives (no SQL, no relations, no transactions), he merely settled on a vague definition. Basically, he says, NoSQL is merely a set of different approaches in data models, querying and scaling that trade off things like performance and the 3 guarantees of the CAP Theorem.

In other words, there is no such thing as a canonical NoSQL database. Most emphasize key/value or document-oriented storage, but then you have something totally different like Neo4j, a graph database. NoSQL fans like to emphasize scalability, but some candidates don’t really scale. You can’t even say that NoSQL means no SQL, because you have something like GemFire, which does allow you to use SQL. Nevertheless, allow me to give you my own definition of NoSQL. Ready? Here goes:

NoSQL databases are databases for people who don't care about data.

OOPs: mangling your database with objects

A developer once showed me a frightening sight: a page and a half of dense SQL, consisting mostly of join clauses. What was frightening was not so much the performance of this query -- it was a utility query, so the long query time was not important -- but the fact that he was actually proud of that query. How did a production DB schema get into such a state? The problem, in a word, was Hibernate. I'm a fan of Hibernate, so I'm not saying that there's something wrong about Hibernate. Rather, Hibernate lets you too easily do things that you should have second thoughts about. To borrow a metaphor, this tool comes with live chainsaws attached.

Don't forget to index your PostgreSQL foreign keys

One thing that surprised me with PostgreSQL after spending a lot of time with MySQL is that setting foreign key constraints do not automatically generate indexes on those columns. PostgreSQL will implicitly generate indexes for primary keys, but not foreign keys. Since those foreign key columns are precisely the ones you tend to use for joins (either explicitly or implicitly by ORMs like Hibernate), omitting indexes could result in a serious performance hit. Creating indexes is easy enough, whether using the usual SQL command or a Hibernate @Index annotation, but where do you start? Well, the whole point of this blog entry is to point you to another blog entry. It provides a handy query to look for foreign keys that could use indexes, ranked by the ones most likely to need an index. Take a look:

Postgresql: Indexes on Foreign Keys