Re: Copy command freezes but INSERT works fine with trigger oninsert.

The copy command will run as a single transaction -- all succeed or all fai=
l, I think, so if you interrupt it no rows will be loaded. The inserts -- u=
nless wrapped into a transaction with a "begin; ... commit;" block will eac=
h 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 as it 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 perf=
ormance might be an issue), but I am rather unacquainted with triggers in p=
ostgres, 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:=09
Subject: [SQL] Copy command freezes but INSERT works fine with trigger onin=
sert.=20
I tried to add up records 2 different ways:=20
1. using command [copy messageinfo (user_id, receivedtime, filename, send=
ersstring, subject,
hasattachments, groupmsg, msgsize, version ) FROM '/meridtemp/messageinf=
o.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?=20
Later I did an experiment & repeated it few times. After copy command is ru=
nning for a while from
within psql and trying to insert 60,000 records I interrupted it by pressin=
g CTRL+C few times.
Each time I had different line within addmsgcountSync where it stopped. It =
tells me that =93copy=94
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();
=20
CREATE OR REPLACE FUNCTION addmsgcountSync() RETURNS TRIGGER AS=20
'
DECLARE=20
currentTime injector.lastreceivedtime%TYPE;
vlastreceivedtime injector.lastreceivedtime%TYPE;
userIdRec RECORD;
vID messageinfo.user_id%TYPE;
injectorCursor CURSOR ( tt int8 ) FOR SELECT lastreceivedtime FROM injecto=
r WHERE injector.id =3D
tt::int8 ;

BEGIN
vID =3D NEW.user_id;
IF ( vID IS NOT NULL ) THEN
-- Find out lastrecievedtime we need cursor
OPEN injectorCursor( vID );
FETCH injectorCursor INTO userIdRec;
vlastreceivedtime =3D userIdRec.lastreceivedtime;
CLOSE injectorCursor;
currentTime =3D CURRENT_TIMESTAMP;
IF vlastreceivedtime < currentTime THEN
vlastreceivedtime =3D currentTime;
END IF;
-- To make sure time of last message is not newer than lastreceivedtime t=
ime
IF vlastreceivedtime < NEW.receivedtime THEN
vlastreceivedtime =3D NEW.receivedtime;
END IF;
-- Stopes copy command but not insert one ?
UPDATE injector SET addmsgcount =3D addmsgcount + 1, lastreceivedtime =3D=
vlastreceivedtime WHERE
injector.id =3D vID::int8;
END IF;
RETURN NULL;
END;
'
LANGUAGE 'plpgsql';



=====3D
Thanks a lot
Igor Artimenko
I specialize in=20
Java, J2EE, Unix, Linux, HP, AIX, Solaris, Progress, Oracle, DB2, Postgres,=
Data Modeling


=09

__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail=20

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




---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org