Message-ID: <20040827201614.78627.qmail@web11905.mail.yahoo.com>
Date: Fri, 27 Aug 2004 13:16:14 -0700 (PDT)
From: Artimenko Igor <igorart7@yahoo.com>
Subject: Copy command freezes but INSERT works fine with trigger on insert. 
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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;
 vID messageinfo.user_id%TYPE;
 injectorCursor CURSOR ( tt int8 ) FOR SELECT lastreceivedtime FROM injector WHERE injector.id =
tt::int8 ;

BEGIN
	vID = 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;
		CLOSE injectorCursor;
		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;
		-- Stopes copy command but not insert one ?
		UPDATE injector SET addmsgcount = addmsgcount + 1, lastreceivedtime = vlastreceivedtime WHERE
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