psql error for restoring pgsl backup on cmd

问题:

I'm having a hard time understanding what this error means. The command I used was:

psql -U postgres -d app -1 -f postgres.sql

and this is the error:

 psql:postgres.sql:1879: ERROR:  current transaction is aborted, commands ignored
     until end of transaction block

ROLLBACK
psql:postgres.sql:0: WARNING:  there is no transaction in progress

Not really sure how to make a transaction in progress. This is the sql file that I was trying to import to postgresl: http://pastebin.com/2xMGhstd

回答1:

As joop explained, your SQL file is inconsistent.

There is a foreign key constraint from raffle.user_id to "user".id, which means that for every value in raffle.user_id there must be a row in "user" where id has the same value.

Now there is no row inserted in "user" with an id equal to 1, but the script attempts to insert a row in raffle with user_id equal to 1.

That violates the foreign key constraint an causes an error. Once there has been an error in a PostgreSQL transaction, all you can do is ROLLBACK. Until you do that, all statements in the transaction will fail with the error you observe.

The only solutions you have are either to fix the data so that they are consistent or to give up consistency by removing the foreign key constraint.

Remark: it is a bad idea to choose a reserved SQL keyword like USER as name.

回答2:

As @joop commented: your .dmp file is crippled because user_id=1 does not exist in the users table, but is referred in the raffle table. Example: run the next snippet, then uncomment the line with user#1 on it and run it again.

---

-- Drop the schema *after* usage
-- DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;

CREATE TABLE users (
        id INTEGER NOT NULL,
        email VARCHAR(120),
        PRIMARY KEY (id),
        UNIQUE (email)
);
    -- UNCOMMENT the next line to also add user#1
-- INSERT INTO users VALUES(1,'[e-mail]');
INSERT INTO users VALUES(2,'[e-mail]');
INSERT INTO users VALUES(3,'[e-mail]');
INSERT INTO users VALUES(4,'[e-mail]');
INSERT INTO users VALUES(5,'[e-mail]');
INSERT INTO users VALUES(6,'[e-mail]');
INSERT INTO users VALUES(7,'[e-mail]');
INSERT INTO users VALUES(8,'[e-mail]');
INSERT INTO users VALUES(9,'[e-mail]');
INSERT INTO users VALUES(10,'[e-mail]');
CREATE TABLE raffle (
        id INTEGER NOT NULL,
        user_id INTEGER,
        colour VARCHAR(120),
        up1 VARCHAR(4),
        up2 VARCHAR(4),
        PRIMARY KEY (id),
        CONSTRAINT _color_up1_up2_uc UNIQUE (colour, up1, up2),
        FOREIGN KEY(user_id) REFERENCES users (id)
);
INSERT INTO raffle VALUES(1,1,'Blue','7c4c','5c7e');
INSERT INTO raffle VALUES(2,1,'Pink','635d','853f');
INSERT INTO raffle VALUES(3,1,'Plum','5e80','7611');
INSERT INTO raffle VALUES(4,1,'Aqua','937c','1b75');
INSERT INTO raffle VALUES(5,2,'Navy','1d9a','8914');
INSERT INTO raffle VALUES(6,1,'Grey','d869','fc97');
INSERT INTO raffle VALUES(7,4,'Rose','5fee','b31f');
INSERT INTO raffle VALUES(8,1,'Ruby','d5b4','e749');
INSERT INTO raffle VALUES(9,2,'Teal','cf0b','3bf5');
INSERT INTO raffle VALUES(10,1,'Gold','98a7','3079');
INSERT INTO raffle VALUES(11,1,'Jade','5c69','66f8');
INSERT INTO raffle VALUES(12,1,'Lime','156f','6b34');
INSERT INTO raffle VALUES(13,1,'Blue','7da3','d95b');
INSERT INTO raffle VALUES(14,1,'Pink','a63e','b9b6');
INSERT INTO raffle VALUES(15,1,'Plum','d989','71a5');
INSERT INTO raffle VALUES(16,1,'Aqua','7372','0682');
/****
INSERT INTO raffle VALUES(17,9,'Navy','01b3','e444');
INSERT INTO raffle VALUES(18,1,'Grey','d679','0123');
INSERT INTO raffle VALUES(19,1,'Rose','5963','692d');
...
***/
COMMIT;