Thread: Copy command freezes but INSERT works fine with trigger on insert.

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 

Copy from .. works fine for 1000 records to load. For data set of 60000 records I could never get
it finish. And I'm planing to reload 1000,000 records.

So there is a limit somewhere between 1,000 & 60,000 since it starts working slower. The only
question for me left is. What config parameter(s) I should increase to speed up copy command and
to which values rougthly?  

Also do you know config settings to see if copy operation progresses or waits for something?

    
__________________________________
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail