[odb-users] ODB 2.1 on PostgreSQL

[Simon Riggs]

Hi,

Very happy to see new release of ODB on PostgreSQL.

I wanted to pass on a few comments on the PostgreSQL limitations
section, listed here
http://www.codesynthesis.com/products/odb/doc/manual.xhtml#15.5
to allow some further understanding on those areas.

15.5.1 Query Result Caching

The PostgreSQL ODB runtime implementation will always return a cached
query result (Section 4.4, "Query Result") even when explicitly
requested not to. This is a limitation of the PostgreSQL client
library (libpq) which does not support uncached (streaming) query
results.

PostgreSQL 9.2 now supports streaming...
http://www.postgresql.org/docs/9.2/static/libpq-single-row-mode.html


15.5.3 Unique Constraint Violations

Due to the granularity of the PostgreSQL error codes, it is impossible
to distinguish between the duplicate primary key and other unique
constraint violations. As a result, when making an object persistent,
the PostgreSQL ODB runtime will translate all unique constraint
violation errors to the object_already_persistent exception (Section
3.14, "ODB Exceptions").

> create table test2 (id integer primary key, id2 integer unique);
CREATE TABLE
> insert into test2 values (1, 1);
INSERT 0 1
ERROR:  duplicate key value violates unique constraint "test2_pkey"
DETAIL:  Key (id)=(1) already exists.
> insert into test2 values (2, 1);
ERROR:  duplicate key value violates unique constraint "test2_id2_key"
DETAIL:  Key (id2)=(1) already exists.

The error message itself doesn't differentiate between primary keys
and unique constraints, but the object naming convention shows that
PKs have the "_pkey" suffix by default. Changing that isn't
recommended, so you could probably rely on it.

Hope that helps,

-- 
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services