I've got a constriant between two fields in one table.

\n            Column            |  Type   |                           Modifiers                            \n------------------------------+---------+----------------------------------------------------------------\n cv_entry_id                  | bigint  | not null default nextval('cv_entry_cv_entry_id_seq'::regclass)\noriginal_book_id             | bigint  | \n

with constraint:

"translation_references_book" FOREIGN KEY (original_book_id) REFERENCES cv_entry(cv_entry_id) DEFERRABLE

I'm inside a transaction, I insert a record for the translation before inserting the record for the book and I get a constraint violation. I thought deferred constraints were not to be checked until commit?

I have executed 'set constraints all deferred'.

For now I've dropped that particular constraint and checked it with
select original_book_id from cv_entry where original_book_id is not null and original_book_id not in (select cv_entry_id from cv_entry);

it comes out clean - so this looks like a (VERY ANNOYING) but in PG's constraints checking - I have version 8.1.

Or am I missing something?

I have googled away and cannot find anywhere a coherent explanation of the differences or semantic benefits between choosing initially deferred and deferrable. So I've simply declared them all deferrable.

I'll changed them to initially deferred and see.

This is really getting to be nice development environment - I've added schema comparison/migration features to glorp in squeak and now I just change the object model and it syncs it on demand - with warnings for non-empty tables when dropping and options to merge dropped non-empty columns to other columns. Doing the inverse sync from tables to objects should take all of a couple hours giving round tripping in two directions.

Rails, shmails.