4

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 ?

Improve this question

1 Answer 1

Reset to default
5

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.

Improve this answer
3
  • 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, 2012 at 14:08
  • See the documentation: Transactions
    – ypercubeᵀᴹ
    Oct 8, 2012 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, 2012 at 21:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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