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
---------------------------(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
