pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed(no subject)
4+ messages / 4 participants
[nested] [flat]
* (no subject)
@ 1999-09-02 06:04 Martin Neumann <lists@mne.de>
0 siblings, 3 replies; 4+ messages in thread
From: Martin Neumann @ 1999-09-02 06:04 UTC (permalink / raw)
To: pgsql-sql
Every day I get a plain-vanilla ASCII-file containg space-separated
values. I parse the file with a script and make each row in the file an
INSERT-statement. Sometimes one of these many statements contains
rubbish becaused the line in the file I was processing contained
rubbish.
If I did a separate transaction for every INSERT-statement this doesn't
hurt because only one statement (the broken one) doesn't get processed.
But I have to do about 100.000 INSERTs everyday and this is _slow_ if I
let PostgreSQL process each statement individually.
So my idea was to use a chained transaction. This works perfectly as
long as all INSERT-statement are okay. But if one is broken, PostgreSQL
doesn't process the other statements, too.
My goal is to use chained transaction and to don't have to bother
about broken INSERT-statements. Is that possible or to do I have to
check the statements manually (by the script) before executing?
--
`Unser Kopf ist rund, damit das Denken die Richtung wechseln kann.'
~ Francis Picabia
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: your mail
@ 1999-09-02 06:44 Zalman Stern <zalman@netcom.com>
parent: Martin Neumann <lists@mne.de>
2 siblings, 0 replies; 4+ messages in thread
From: Zalman Stern @ 1999-09-02 06:44 UTC (permalink / raw)
To: lists@mne.de; +Cc: pgsql-sql
I would either validate the data in the script, or batch the records into
bunches of 100 or 1000 or whatever inserts. If one of the batches fails,
then reprocess only that batch using individual inserts.
I believe using transactions of moderate size will be faster than using
really really huge ones. There are of course also transactional semantics
issues if you have concurrent access going on.
-Z-
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: none
@ 1999-09-02 07:59 Anatoly K. Lasareff <tolik@aaanet.ru>
parent: Martin Neumann <lists@mne.de>
2 siblings, 0 replies; 4+ messages in thread
From: tolik@aaanet.ru @ 1999-09-02 07:59 UTC (permalink / raw)
To: Martin Neumann <lists@mne.de>; +Cc: pgsql-sql
>>>>> "MN" == Martin Neumann <lists@mne.de> writes:
MN> Every day I get a plain-vanilla ASCII-file containg space-separated
MN> values. I parse the file with a script and make each row in the file an
MN> INSERT-statement. Sometimes one of these many statements contains
MN> rubbish becaused the line in the file I was processing contained
MN> rubbish.
MN> If I did a separate transaction for every INSERT-statement this doesn't
MN> hurt because only one statement (the broken one) doesn't get processed.
MN> But I have to do about 100.000 INSERTs everyday and this is _slow_ if I
MN> let PostgreSQL process each statement individually.
MN> So my idea was to use a chained transaction. This works perfectly as
MN> long as all INSERT-statement are okay. But if one is broken, PostgreSQL
MN> doesn't process the other statements, too.
MN> My goal is to use chained transaction and to don't have to bother
MN> about broken INSERT-statements. Is that possible or to do I have to
MN> check the statements manually (by the script) before executing?
I think your second way is better - check statement in script
before executing.
--
Anatoly K. Lasareff Email: tolik@icomm.ru
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re:
@ 1999-09-02 09:59 Clayton Cottingham <drfrog@smartt.com>
parent: Martin Neumann <lists@mne.de>
2 siblings, 0 replies; 4+ messages in thread
From: Clayton Cottingham @ 1999-09-02 09:59 UTC (permalink / raw)
To: Martin Neumann <lists@mne.de>; pgsql-sql
martin:
couple o things:
use something like sed to pre process the file taking out the erronous
data, if you know what the bad data is
one thing i use sed for is html escape single quotes and the like,
it should work well for you
the regex for substituting like that is:
s/old/new/g
the sed command operates regexes with an -e switch :
sed -e s/error/correction/g errorfile>correctedfile
takes errorfile in and changes any occurence of 'error' to 'correction'
and
prints all data to correctedfile
also if its a flat file you could just use the copy command in psql
instead of inserts:
\copy tablein from filename
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~1999-09-02 09:59 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-09-02 06:04 Martin Neumann <lists@mne.de>
1999-09-02 06:44 ` Re: your mail Zalman Stern <zalman@netcom.com>
1999-09-02 07:59 ` Re: none Anatoly K. Lasareff <tolik@aaanet.ru>
1999-09-02 09:59 ` Re: Clayton Cottingham <drfrog@smartt.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