[Dbix-class] Temporarily turn off table constraints in Postgresql?

[Jason Kohles]

On Jun 29, 2007, at 8:40 PM, Dennis Daupert wrote:

> I have tables that mutually reference one another, which prevents  
> initial addition of data to the tables. EG:
>
> "page_content_version_fkey" FOREIGN KEY (content_version, id)  
> REFERENCES content(version, page) ON UPDATE CASCADE ON DELETE CASCADE
>
> "content_page_fkey" FOREIGN KEY (page) REFERENCES page(id) ON  
> UPDATE CASCADE ON DELETE CASCADE
>
> Is there a way to temporarily disengage or silence or otherwise  
> render harmless those constraints to allow the data load?  
> Postgresql 81
>
> I tried running the data load inside a transaction, but no joy.
>

You can use 'SET CONSTRAINTS ALL DEFERRED' to tell postgres to defer  
constraint checking to the end of the transaction, rather than after  
each statement.

test=# SET CONSTRAINTS ALL DEFERRED;
SET CONSTRAINTS
test=# BEGIN;
BEGIN
( whatever statements you want... )
test# COMMIT;
COMMIT

If you have a DBIC script to do the data loading, you can do this  
with 'on_connect_do'  in your script...

my $schema = My::Schema->connect(
	$dsn, $user, $password,
	{ AutoCommit => 1 },
	{ on_connect_do => [ 'SET CONSTRAINTS ALL DEFERRED' ] },
);
$schema->txn_do( \&data_loader );

-- 
Jason Kohles
email at jasonkohles.com
http://www.jasonkohles.com/
"A witty saying proves nothing."  -- Voltaire