Thread: Re: Copy command freezes but INSERT works fine with trigger oninsert.
Re: Copy command freezes but INSERT works fine with trigger oninsert.
From
"Gregory S. Williamson"
Date:
The copy command will run as a single transaction -- all succeed or all fail, I think, so if you interrupt it no rows willbe loaded. The inserts -- unless wrapped into a transaction with a "begin; ... commit;" block will each be a single transaction. Check you postgres log file for the time when they copy was runnint --- you should see it writing something like this asit does its logging: 2004-08-18 09:56:26 LOG: removing transaction log file "0000002200000089" 2004-08-18 09:56:26 LOG: removing transaction log file "0000002200000087" ... In and of itself I don't see why the trigger would stop copy (although performance might be an issue), but I am rather unacquaintedwith triggers in postgres, so perhaps someone more knowledgable could comment. HTH, Greg Williamson DBA GlobeXplorer LLC -----Original Message----- From: Artimenko Igor [mailto:igorart7@yahoo.com] Sent: Fri 8/27/2004 1:16 PM To: pgsql-sql@postgresql.org Cc: Subject: [SQL] Copy command freezes but INSERT works fine with trigger oninsert. I tried to add up records 2 different ways: 1. using command [copy messageinfo (user_id, receivedtime, filename, sendersstring, subject, hasattachments, groupmsg, msgsize, version ) FROM '/meridtemp/messageinfo.dat';] from psql 2. using simple INSERT INTO messageinfo ( user_id ) VALUES( 1728::int8 ); In 2nd case it worked but not in 1st one. Why? Later I did an experiment & repeated it few times. After copy command is running for a while from within psql and trying to insert 60,000 records I interrupted it by pressing CTRL+C few times. Each time I had different line within addmsgcountSync where it stopped. It tells me that “copy” command does not freeze on one particular statement but it did not insert a single record. For this table messageinfo I have trigger: CREATE TRIGGER msgInjector AFTER INSERT ON messageinfo FOR EACH ROW EXECUTE PROCEDURE addmsgcountSync(); CREATE OR REPLACE FUNCTION addmsgcountSync() RETURNS TRIGGER AS ' DECLARE currentTime injector.lastreceivedtime%TYPE;vlastreceivedtime injector.lastreceivedtime%TYPE;userIdRec RECORD;vIDmessageinfo.user_id%TYPE;injectorCursor CURSOR ( tt int8 ) FOR SELECT lastreceivedtime FROM injector WHERE injector.id= tt::int8 ; BEGINvID = NEW.user_id;IF ( vID IS NOT NULL ) THEN -- Find out lastrecievedtime we need cursor OPEN injectorCursor(vID ); FETCH injectorCursor INTO userIdRec; vlastreceivedtime = userIdRec.lastreceivedtime; CLOSEinjectorCursor; currentTime = CURRENT_TIMESTAMP; IF vlastreceivedtime < currentTime THEN vlastreceivedtime= currentTime; END IF; -- To make sure time of last message is not newer than lastreceivedtime time IF vlastreceivedtime < NEW.receivedtime THEN vlastreceivedtime = NEW.receivedtime; END IF; -- Stopescopy command but not insert one ? UPDATE injector SET addmsgcount = addmsgcount + 1, lastreceivedtime = vlastreceivedtimeWHERE injector.id = vID::int8;END IF;RETURN NULL; END; ' LANGUAGE 'plpgsql'; ===== Thanks a lot Igor Artimenko I specialize in Java, J2EE, Unix, Linux, HP, AIX, Solaris, Progress, Oracle, DB2, Postgres, Data Modeling __________________________________ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match