Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am using PostgreSQL 9.1.5 and the psql to run a scipt containing a series of INSERT statements in various tables:

psql -U usernamefoo databasenamefoo -f dml_script.sql

The table constraints have been created with the DEFERRABLE option and the script itself (file dml_script.sql above) issues the following before an offending INSERT:

SET CONSTRAINTS ALL DEFERRED;                  
INSERT INTO KM_TRANSACTION_GROUPS ..

I've checked that the specific constraint on the table in question has been created with the DEFRRABLE option:

ALTER TABLE ONLY KM_TRANSACTION_GROUPS ADD CONSTRAINT TRGR_TRGR_FK FOREIGN KEY (TRGR_PARENT_ID) REFERENCES KM_TRANSACTION_GROUPS(TRGR_ID) DEFERRABLE;

Yet, running the DML script fails:

psql:./schema-dml-test.sql:8: ERROR:  insert or update on table "km_transaction_groups" violates foreign key constraint "trgr_trgr_fk"
DETAIL:  Key (trgr_parent_id)=(2) is not present in table "km_transaction_groups".

My guess is that psql treats each INSERT line in the dml_script.sql as a separate transaction. If that's the reason, is there a way to group INSERT statements in the dml_script.sql file into one longer transaction (or perhaps treat the whole file as one single transaction) so that at its end (when the scope of the SET CONSTRAINTS ALL DEFERRED; terminates), no constraints are violated?

Otherwise some other method (other then changing the order of the INSERT statements which I have reasons I don't want to) to allow me to load the table with a series of INSERT statements so that constraints can be temporarily deactivated ?

share|improve this question

migrated from serverfault.com Oct 8 '12 at 18:49

This question came from our site for system and network administrators.

1 Answer 1

up vote 5 down vote accepted

The -1 option to psql causes it to wrap a file specified by -f in a BEGIN..COMMIT block, making it a transaction.

Otherwise, add the BEGIN and COMMIT commands to your script so that it becomes a single transaction.

share|improve this answer
    
Yup - by default interactive psql sessions are "every line is a transaction": You need explicit BEGIN/ROLLBACK/COMMIT statements if you want a multi-statement transaction... –  voretaq7 Oct 5 '12 at 14:08
    
See the documentation: Transactions –  ypercube Oct 8 '12 at 18:53
    
You can also turn off that dreaded autocommit mode using: \set AUTOCOMMIT off (I have that in my psqlrc file) –  a_horse_with_no_name Oct 8 '12 at 21:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.