pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCopy command freezes but INSERT works fine with trigger on insert.
3+ messages / 2 participants
[nested] [flat]
* Copy command freezes but INSERT works fine with trigger on insert.
@ 2004-08-27 20:16 Artimenko Igor <igorart7@yahoo.com>
0 siblings, 0 replies; 3+ messages in thread
From: Artimenko Igor @ 2004-08-27 20:16 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Copy command freezes but INSERT works fine with trigger oninsert.
@ 2004-08-27 20:29 Gregory S. Williamson <gsw@globexplorer.com>
0 siblings, 0 replies; 3+ messages in thread
From: Gregory S. Williamson @ 2004-08-27 20:29 UTC (permalink / raw)
To: Artimenko Igor <igorart7@yahoo.com>; pgsql-sql
The copy command will run as a single transaction -- all succeed or all fail, I think, so if you interrupt it no rows will be 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 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 performance might be an issue), but I am rather unacquainted with 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;
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Copy command freezes but INSERT works fine with trigger on insert.
@ 2004-08-27 20:53 Artimenko Igor <igorart7@yahoo.com>
0 siblings, 0 replies; 3+ messages in thread
From: Artimenko Igor @ 2004-08-27 20:53 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-08-27 20:53 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-27 20:16 Copy command freezes but INSERT works fine with trigger on insert. Artimenko Igor <igorart7@yahoo.com>
2004-08-27 20:29 Re: Copy command freezes but INSERT works fine with trigger oninsert. Gregory S. Williamson <gsw@globexplorer.com>
2004-08-27 20:53 Re: Copy command freezes but INSERT works fine with trigger on insert. Artimenko Igor <igorart7@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox