Thread: ERROR: invalid memory alloc request size
I am using postgresql 8.1 on Debian Stable.
I am trying to build a database on a logserver to enable me to query
firewall en squid-logs more efficiently. I am working with large
amounts of data.
I have so far successfully populated 4 tables with data from one month's
firewall logs. One of them is fw_accepted:
firewall=> select count(*) from fw_accepted;
count
-----------
422790881
(1 row)
firewall=> \d fw_accepted;
Table "public.fw_accepted"
Column | Type | Modifiers
------------+------------------------+-----------
num | bigint | not null
date | date | not null
time | time without time zone | not null
start_date | date |
start_time | time without time zone |
elapsed | interval |
bytes | bigint |
src | inet |
dst | inet |
proto | character varying |
service | bigint |
rule | smallint |
fw_user | character varying |
Indexes:
"bestemming" btree (dst, date, "time")
"bron" btree (src, date, "time")
"fw_tyd" btree (date)
"gebruiker" btree (fw_user, date, "time")
"nommer" btree (num, date, "time")
I did that by using the dropping the indexes, copying the data from a
csv-file and recreating the indexes again.
While trying to do the same on the squidlogs-table the following
happens:
$ psql -d firewall -f laai_squid_logs
SET
DROP INDEX
DROP INDEX
psql:laai_squid_logs:4: ERROR: invalid memory alloc request size 1350958157
CONTEXT: COPY squidlogs, line 238780099
CREATE INDEX
CREATE INDEX
$ cat laai_squid_logs
SET client_encoding = 'LATIN1';
DROP INDEX squidbron;
DROP INDEX squidtyd ;
\copy squidlogs from '/home/log/proxy/nasql/accesspg.csv' using delimiters ' ' csv quote as '"'
CREATE INDEX squidbron
ON squidlogs
USING btree
(bron, datum, tyd);
CREATE INDEX squidtyd
ON squidlogs
USING btree
(datum, tyd);
The particular line in the csv-file looks like this (but I doubt whether
it's contents is the cause of the problem):
"2007-04-21" "14:48:48" "192.168.0.100" "134"
"http://sharefsn.com/quicksilver\"><img%20src=\"http://sharefsn.com/images/bnr/ban_freestore_sm.gif\"%20border=0></a>"
The table definition:
Table "public.squidlogs"
Column | Type | Modifiers
--------+------------------------+-----------
datum | date | not null
tyd | time without time zone | not null
bron | inet | not null
bytes | bigint |
url | character varying |
Indexes:
"squidbron" btree (bron, datum, tyd)
"squidtyd" btree (datum, tyd)
The server is an old HP LP 2000 with 1G of RAM. Maybe that is the problem,
but then, how could I copy the data into the other 4 tables without
similar problems?
Any idea on how to solve this?
Regards
Johann
--
Johann Spies Telefoon: 021-808 4036
Informasietegnologie, Universiteit van Stellenbosch
"Yea, though I walk through the valley of the shadow of
death, I will fear no evil, for thou art with me;
thy rod and thy staff they comfort me." Psalms 23:4
Johann Spies <jspies@sun.ac.za> writes:
> I am using postgresql 8.1 on Debian Stable.
8.1.what?
> psql:laai_squid_logs:4: ERROR: invalid memory alloc request size 1350958157
> CONTEXT: COPY squidlogs, line 238780099
Is this repeatable? Is it always the same line and same bogus request size?
If Postgres was built with debug symbols it would be useful to get a
stack trace to try to figure out more about what's happening. If you
want to try that, attach to the backend with gdb while the COPY is
running, then do
$ gdb /path/to/postgres PID-of-backend
gdb> b errfinish
gdb> cont
... wait for breakpoint to be hit ...
gdb> bt
gdb> q
answer 'y' to ok-to-detach prompt
regards, tom lane
On Mon, May 14, 2007 at 10:34:18AM -0400, Tom Lane wrote:
> Johann Spies <jspies@sun.ac.za> writes:
> > I am using postgresql 8.1 on Debian Stable.
>
> 8.1.what?
Sorry. The version is 8.1.8-1
>
> > psql:laai_squid_logs:4: ERROR: invalid memory alloc request size 1350958157
> > CONTEXT: COPY squidlogs, line 238780099
>
> Is this repeatable?
Yes.
> Is it always the same line and same bogus request size?
>
Yes - the same line. I am not 100% certain that it is the same bogus
request size. I will test it overnight again. It takes a long time.
> If Postgres was built with debug symbols it would be useful to get a
> stack trace to try to figure out more about what's happening. If you
> want to try that, attach to the backend with gdb while the COPY is
> running, then do
>
> $ gdb /path/to/postgres PID-of-backend
> gdb> b errfinish
> gdb> cont
> ... wait for breakpoint to be hit ...
> gdb> bt
> gdb> q
> answer 'y' to ok-to-detach prompt
>
Unfortunately gdb reports "no debugging symbols found". I will try and
recompile postgresql with debugging symbols and try again.
Thanks.
Johann
--
Johann Spies Telefoon: 021-808 4036
Informasietegnologie, Universiteit van Stellenbosch
"Yea, though I walk through the valley of the shadow of
death, I will fear no evil, for thou art with me;
thy rod and thy staff they comfort me." Psalms 23:4
On Mon, May 14, 2007 at 05:13:25PM +0200, Johann Spies wrote:
>
> On Mon, May 14, 2007 at 10:34:18AM -0400, Tom Lane wrote:
> > Johann Spies <jspies@sun.ac.za> writes:
> > > I am using postgresql 8.1 on Debian Stable.
> >
> > 8.1.what?
>
> Sorry. The version is 8.1.8-1
> >
> > > psql:laai_squid_logs:4: ERROR: invalid memory alloc request size 1350958157
> > > CONTEXT: COPY squidlogs, line 238780099
> >
> > Is this repeatable?
>
> Yes.
>
> > Is it always the same line and same bogus request size?
> >
>
> Yes - the same line. I am not 100% certain that it is the same bogus
> request size.
>
I can confirm that it the same.
Regards
Johann
--
Johann Spies Telefoon: 021-808 4036
Informasietegnologie, Universiteit van Stellenbosch
"Let not your heart be troubled: ye believe in God,
believe also in me." John 14:1
* Tom Lane <tgl@sss.pgh.pa.us> [070514 16:36]:
>
> If Postgres was built with debug symbols it would be useful to get a
> stack trace to try to figure out more about what's happening. If you
> want to try that, attach to the backend with gdb while the COPY is
> running, then do
>
> $ gdb /path/to/postgres PID-of-backend
Which pg-process? The Postmaster or psql? I have done this for the
Postmaster now but
> gdb> b errfinish
> gdb> cont
> ... wait for breakpoint to be hit ...
This did not happen although the error occurred (after a few hours).
Regards
Johann
--
Johann Spies Telefoon: 021-808 4036
Informasietegnologie, Universiteit van Stellenbosch
"Let not your heart be troubled: ye believe in God,
believe also in me." John 14:1
* Tom Lane <tgl@sss.pgh.pa.us> [070514 16:36]:
> Johann Spies <jspies@sun.ac.za> writes:
> > I am using postgresql 8.1 on Debian Stable.
>
> 8.1.what?
>
I have tried it now on 8.2.4-1 and the error happened exactly at the
same place and with the same bogus request size again.
Regards
Johann
--
Johann Spies Telefoon: 021-808 4036
Informasietegnologie, Universiteit van Stellenbosch
"In my Father's house are many mansions: if it were not
so, I would have told you. I go to prepare a place for
you. And if I go and prepare a place for you, I will
come again, and receive you unto myself; that where I
am, there ye may be also." John 14:2,3
Johann Spies <jspies@sun.ac.za> writes:
> * Tom Lane <tgl@sss.pgh.pa.us> [070514 16:36]:
>> $ gdb /path/to/postgres PID-of-backend
> Which pg-process? The Postmaster or psql? I have done this for the
> Postmaster now but
>> gdb> b errfinish
>> gdb> cont
>> ... wait for breakpoint to be hit ...
> This did not happen although the error occurred (after a few hours).
You got the wrong process then --- maybe the postmaster itself rather
than the backend assigned to your psql session? You can identify the
backend PID via pg_backend_pid() within the psql session of interest,
or look in pg_stat_activity.
regards, tom lane
On Wed, May 16, 2007 at 01:50:04PM -0400, Tom Lane wrote:
> You got the wrong process then --- maybe the postmaster itself rather
> than the backend assigned to your psql session? You can identify the
> backend PID via pg_backend_pid() within the psql session of interest,
> or look in pg_stat_activity.
Thanks. I have now activated gdb on three sub-process busy with the
query as far as I can determine. The first one was the the writer
process which reached a breakpoint before the query ended. Here is the
output:
Program received signal SIGINT, Interrupt.
0xffffe410 in __kernel_vsyscall ()
(gdb) bt
#0 0xffffe410 in __kernel_vsyscall ()
#1 0xb7c5b03d in select () from /lib/tls/i686/cmov/libc.so.6
#2 0x082a8dff in pg_usleep ()
#3 0x081c1862 in BackgroundWriterMain ()
#4 0x080cbf71 in BootstrapMain ()
#5 0x081c6c87 in ClosePostmasterPorts ()
#6 0x081cab77 in PostmasterMain ()
#7 <signal handler called>
#8 0xffffe410 in __kernel_vsyscall ()
#9 0xb7c5b03d in select () from /lib/tls/i686/cmov/libc.so.6
#10 0x081c8037 in ClosePostmasterPorts ()
#11 0x081c9924 in PostmasterMain ()
#12 0x081823b5 in main ()
(gdb) q
Another one that happened before an error message from psql. I typed
'cont' after this output and the error message from psql followed
thereafter.
Breakpoint 1, 0x0827eef6 in errfinish ()
(gdb) bt
#0 0x0827eef6 in errfinish ()
#1 0x0828039c in elog_finish ()
#2 0x082993b3 in MemoryContextAlloc ()
#3 0x0828d437 in pg_mb2wchar ()
#4 0x0811c288 in CreateCopyDestReceiver ()
#5 0x0811ea43 in DoCopy ()
#6 0x081f9d00 in ProcessUtility ()
#7 0x081f6ed9 in PostgresMain ()
#8 0x081f7cac in FreeQueryDesc ()
#9 0x081f84d9 in PortalRun ()
#10 0x081f3b51 in pg_parse_query ()
#11 0x081f5296 in PostgresMain ()
#12 0x081c8a1f in ClosePostmasterPorts ()
#13 0x081c9924 in PostmasterMain ()
#14 0x081823b5 in main ()
Regards
Johann
--
Johann Spies Telefoon: 021-808 4036
Informasietegnologie, Universiteit van Stellenbosch
"Jesus said unto her, I am the resurrection, and the
life. He that believeth in me, though he were dead,
yet shall he live." John 11:25
Johann Spies <jspies@sun.ac.za> writes:
> Breakpoint 1, 0x0827eef6 in errfinish ()
> (gdb) bt
> #0 0x0827eef6 in errfinish ()
> #1 0x0828039c in elog_finish ()
> #2 0x082993b3 in MemoryContextAlloc ()
> #3 0x0828d437 in pg_mb2wchar ()
> #4 0x0811c288 in CreateCopyDestReceiver ()
> #5 0x0811ea43 in DoCopy ()
> #6 0x081f9d00 in ProcessUtility ()
> #7 0x081f6ed9 in PostgresMain ()
> #8 0x081f7cac in FreeQueryDesc ()
> #9 0x081f84d9 in PortalRun ()
> #10 0x081f3b51 in pg_parse_query ()
> #11 0x081f5296 in PostgresMain ()
> #12 0x081c8a1f in ClosePostmasterPorts ()
> #13 0x081c9924 in PostmasterMain ()
> #14 0x081823b5 in main ()
Hmmm ... this is interesting, but unfortunately backtraces from
non-debug builds are often not very trustworthy, and that seems to have
happened here. CreateCopyDestReceiver doesn't call pg_mb2wchar, and
pg_mb2wchar doesn't call MemoryContextAlloc, so we're being lied to
right at the spot where we might've hoped to learn something.
Could you please rebuild the executables with --enable-debug and try
again?
regards, tom lane
Hallo Tom,
> > Breakpoint 1, 0x0827eef6 in errfinish ()
> > (gdb) bt
> > #0 0x0827eef6 in errfinish ()
> > #1 0x0828039c in elog_finish ()
> > #2 0x082993b3 in MemoryContextAlloc ()
> > #3 0x0828d437 in pg_mb2wchar ()
> > #4 0x0811c288 in CreateCopyDestReceiver ()
> > #5 0x0811ea43 in DoCopy ()
> > #6 0x081f9d00 in ProcessUtility ()
> > #7 0x081f6ed9 in PostgresMain ()
> > #8 0x081f7cac in FreeQueryDesc ()
> > #9 0x081f84d9 in PortalRun ()
> > #10 0x081f3b51 in pg_parse_query ()
> > #11 0x081f5296 in PostgresMain ()
> > #12 0x081c8a1f in ClosePostmasterPorts ()
> > #13 0x081c9924 in PostmasterMain ()
> > #14 0x081823b5 in main ()
>
> Hmmm ... this is interesting, but unfortunately backtraces from
> non-debug builds are often not very trustworthy, and that seems to have
> happened here. CreateCopyDestReceiver doesn't call pg_mb2wchar, and
> pg_mb2wchar doesn't call MemoryContextAlloc, so we're being lied to
> right at the spot where we might've hoped to learn something.
>
> Could you please rebuild the executables with --enable-debug and try
> again?
I did that. The following breakpoint occurred again before an error
message from the process on the terminal:
Breakpoint 1, 0x0827eef6 in errfinish ()
(gdb) bt
#0 0x0827eef6 in errfinish ()
#1 0x0828039c in elog_finish ()
#2 0x082993b3 in MemoryContextAlloc ()
#3 0x0828d437 in pg_mb2wchar ()
#4 0x0811c288 in CreateCopyDestReceiver ()
#5 0x0811ea43 in DoCopy ()
#6 0x081f9d00 in ProcessUtility ()
#7 0x081f6ed9 in PostgresMain ()
#8 0x081f7cac in FreeQueryDesc ()
#9 0x081f84d9 in PortalRun ()
#10 0x081f3b51 in pg_parse_query ()
#11 0x081f5296 in PostgresMain ()
#12 0x081c8a1f in ClosePostmasterPorts ()
#13 0x081c9924 in PostmasterMain ()
#14 0x081823b5 in main ()
It looks the same as the previous one to me.
Regards
Johann
--
Johann Spies Telefoon: 021-808 4036
Informasietegnologie, Universiteit van Stellenbosch
"For to me to live is Christ, and to die is gain."
Philippians 1:21
Johann Spies <jspies@sun.ac.za> writes:
>> Could you please rebuild the executables with --enable-debug and try
>> again?
> I did that. The following breakpoint occurred again before an error
> message from the process on the terminal:
> Breakpoint 1, 0x0827eef6 in errfinish ()
> (gdb) bt
> #0 0x0827eef6 in errfinish ()
> #1 0x0828039c in elog_finish ()
> #2 0x082993b3 in MemoryContextAlloc ()
> #3 0x0828d437 in pg_mb2wchar ()
> #4 0x0811c288 in CreateCopyDestReceiver ()
> #5 0x0811ea43 in DoCopy ()
> #6 0x081f9d00 in ProcessUtility ()
> #7 0x081f6ed9 in PostgresMain ()
> #8 0x081f7cac in FreeQueryDesc ()
> #9 0x081f84d9 in PortalRun ()
> #10 0x081f3b51 in pg_parse_query ()
> #11 0x081f5296 in PostgresMain ()
> #12 0x081c8a1f in ClosePostmasterPorts ()
> #13 0x081c9924 in PostmasterMain ()
> #14 0x081823b5 in main ()
> It looks the same as the previous one to me.
That's because it is the same :-(. You forgot to install the debuggable
executable, or something like that.
regards, tom lane
On Fri, May 18, 2007 at 11:10:21AM -0400, Tom Lane wrote:
> That's because it is the same :-(. You forgot to install the debuggable
> executable, or something like that.
>
Well, I did install it and I did restart postgresql. Then I don't know
any way forward. I have tried the same process on a new, fast server and
got exactly the same error.
Maybe I should try another database.
Regards
Johann
--
Johann Spies Telefoon: 021-808 4036
Informasietegnologie, Universiteit van Stellenbosch
"O come, let us worship and bow down; let us kneel
before the LORD our maker." Psalms 95:6
Johann Spies <jspies@sun.ac.za> writes:
> On Fri, May 18, 2007 at 11:10:21AM -0400, Tom Lane wrote:
>> That's because it is the same :-(. You forgot to install the debuggable
>> executable, or something like that.
>>
> Well, I did install it and I did restart postgresql. Then I don't know
> any way forward. I have tried the same process on a new, fast server and
> got exactly the same error.
Well, if you aren't having any luck debugging it yourself, maybe you
could put together a self-contained test case for someone else to
reproduce the problem with?
regards, tom lane
On Mon, May 21, 2007 at 10:40:40AM -0400, Tom Lane wrote:
> Well, if you aren't having any luck debugging it yourself, maybe you
> could put together a self-contained test case for someone else to
> reproduce the problem with?
Hallo Tom,
Sorry for the long delay.
Maybe the following was what you were looking for:
gdb) bt
#0 errfinish (dummy=0) at elog.c:313
#1 0x00000000005f40c1 in elog_finish (elevel=<value optimized out>, fmt=<value optimized out>) at elog.c:939
#2 0x0000000000609585 in MemoryContextAlloc (context=0x941210, size=1350958157) at mcxt.c:504
#3 0x00000000005ff1c8 in perform_default_encoding_conversion (
src=0x2b881e87f040 "\"2007-04-20\" \"18:07:06\" \"192.168.0.100\" \"73\"
\"http://www.kerkbode.co.za/kerkbode/images/blank.gif\\\"\"\n\"2007-04-20\"\"18:07:06\" \"192.168.0.100\" \"69\"
\"http://develop.christians.co.za/phpAdsNew/adx.js\"\n\"20"...,len=337739539, is_client_to_server=5 '\005') at
mbutils.c:461
#4 0x00000000004c5393 in CopyReadLine (cstate=0x930d50) at copy.c:2210
#5 0x00000000004c7203 in DoCopy (stmt=0x90c590) at copy.c:1894
#6 0x000000000058110d in ProcessUtility (parsetree=0x90c590, params=0x0, dest=0x90c630, completionTag=0x7fffbf08d660
"")
at utility.c:635
#7 0x000000000057e76c in PortalRunUtility (portal=0x93a570, query=0x90c6c0, dest=0x90c630,
completionTag=0x7fffbf08d660 "") at pquery.c:1067
#8 0x000000000057f58d in PortalRunMulti (portal=0x93a570, dest=0x5, altdest=0x90c630, completionTag=0x7fffbf08d660 "")
at pquery.c:1135
#9 0x000000000057fd34 in PortalRun (portal=0x93a570, count=9223372036854775807, dest=0x90c630, altdest=0x90c630,
completionTag=0x7fffbf08d660 "") at pquery.c:700
#10 0x000000000057bde5 in exec_simple_query (
query_string=0x90c180 "COPY squidlogs FROM STDIN DELIMITER ' ' CSV QUOTE AS '\"'") at postgres.c:939
#11 0x000000000057d210 in PostgresMain (argc=4, argv=<value optimized out>, username=0x87c720 "js") at postgres.c:3424
#12 0x0000000000556b6a in ServerLoop () at postmaster.c:2931
#13 0x0000000000557707 in PostmasterMain (argc=3, argv=0x877370) at postmaster.c:963
#14 0x0000000000519403 in main (argc=3, argv=<value optimized out>) at main.c:188
Regards
Johann
--
Johann Spies Telefoon: 021-808 4036
Informasietegnologie, Universiteit van Stellenbosch
"Wait on the LORD; be of good courage, and he shall
strengthen thine heart; wait, I say, on the LORD."
Psalms 27:14
Johann Spies wrote: > On Mon, May 21, 2007 at 10:40:40AM -0400, Tom Lane wrote: > > Well, if you aren't having any luck debugging it yourself, maybe you > > could put together a self-contained test case for someone else to > > reproduce the problem with? > > Hallo Tom, > > Sorry for the long delay. > > Maybe the following was what you were looking for: > > > gdb) bt > #0 errfinish (dummy=0) at elog.c:313 > #1 0x00000000005f40c1 in elog_finish (elevel=<value optimized out>, fmt=<value optimized out>) at elog.c:939 > #2 0x0000000000609585 in MemoryContextAlloc (context=0x941210, size=1350958157) at mcxt.c:504 > #3 0x00000000005ff1c8 in perform_default_encoding_conversion ( > src=0x2b881e87f040 "\"2007-04-20\" \"18:07:06\" \"192.168.0.100\" \"73\" \"http://www.kerkbode.co.za/kerkbode/images/blank.gif\\\"\"\n\"2007-04-20\"\"18:07:06\" \"192.168.0.100\" \"69\" \"http://develop.christians.co.za/phpAdsNew/adx.js\"\n\"20"...,len=337739539, is_client_to_server=5 '\005') at mbutils.c:461 Interesting. You are passing a single 300 MB string here, not 90-something char lines. The problem is probably in newlines. Why are they not getting used as line separators escapes me. -- Alvaro Herrera http://www.advogato.org/person/alvherre "There is evil in the world. There are dark, awful things. Occasionally, we get a glimpse of them. But there are dark corners; horrors almost impossible to imagine... even in our worst nightmares." (Van Helsing, Dracula A.D. 1972)
Alvaro Herrera <alvherre@commandprompt.com> writes: > Johann Spies wrote: >> #3 0x00000000005ff1c8 in perform_default_encoding_conversion ( >> src=0x2b881e87f040 "\"2007-04-20\" \"18:07:06\" \"192.168.0.100\" \"73\" \"http://www.kerkbode.co.za/kerkbode/images/blank.gif\\\"\"\n\"2007-04-20\"\"18:07:06\" \"192.168.0.100\" \"69\" \"http://develop.christians.co.za/phpAdsNew/adx.js\"\n\"20"...,len=337739539, is_client_to_server=5 '\005') at mbutils.c:461 > Interesting. You are passing a single 300 MB string here, not > 90-something char lines. The problem is probably in newlines. Why are > they not getting used as line separators escapes me. Stripped of gdb's own backslashing, the data seems to be ...images/blank.gif\"" "2007... which leads me to think that the dump was generated on the assumption that backslash is the escape character, but is being reloaded under a different assumption. Once you got out-of-sync on that one data value, you'd have a mess --- all the intended line-ending newlines would be inside quotes, up till you hit another URL with an embedded quote (or newline?). CSV is not exactly a robust representation :-( So the short answer seems to be that we were all misled by the large alloc-request number into thinking there was some data-corruption behavior involved, when we should have been quizzing Johann on exactly what COPY parameters he was giving. regards, tom lane
On Mon, May 28, 2007 at 11:18:37AM -0400, Tom Lane wrote: L> Alvaro Herrera <alvherre@commandprompt.com> writes: > > Johann Spies wrote: > >> #3 0x00000000005ff1c8 in perform_default_encoding_conversion ( > >> src=0x2b881e87f040 "\"2007-04-20\" \"18:07:06\" \"192.168.0.100\" \"73\" \"http://www.kerkbode.co.za/kerkbode/images/blank.gif\\\"\"\n\"2007-04-20\"\"18:07:06\" \"192.168.0.100\" \"69\" \"http://develop.christians.co.za/phpAdsNew/adx.js\"\n\"20"...,len=337739539, is_client_to_server=5 '\005') at mbutils.c:461 > > > > Interesting. You are passing a single 300 MB string here, not > > 90-something char lines. The problem is probably in newlines. Why are > > they not getting used as line separators escapes me. > > Stripped of gdb's own backslashing, the data seems to be > > ...images/blank.gif\"" > "2007... > Thanks to you both. I have grepped -C 5 for the particuler line(s) and the result was: "2007-04-20" "18:07:06" "146.232.182.90" "0" "http://static.ak.facebook.com/images/x_to_hide_hover.gif" "2007-04-20" "18:07:06" "192.168.0.100" "73" "http://www.kerkbode.co.za/kerkbode/images/blank.gif\"" "2007-04-20" "18:07:06" "192.168.0.100" "69" "http://develop.christians.co.za/phpAdsNew/adx.js" "2007-04-20" "18:07:06" "146.232.186.248" "873" "http://ads.cnn.com/html.ng/site=cnn&cnn_position=306x60_lft&cnn_rollup=homepage¶ms.styles=fs&tile=1177085201252&page.allowcompete=yes&domId=283029" "2007-04-20" "18:07:06" "146.232.182.216" "13958" "http://us.js2.yimg.com/us.js.yimg.com/lib/s2/yschx_intl_20070330.css" "2007-04-20" "18:07:06" "146.232.141.240" "5246" "http://photos-819.ak.facebook.com/photos-ak-sf2p/v75/18/87/46601522/a46601522_30445819_7495.jpg" "2007-04-20" "18:07:06" "146.232.53.112" "30663" "http://spe.atdmt.com/ds/YCMEAOAKLO07/oakley_sportsperformance/mlt_RI_300x250.jpg?" > which leads me to think that the dump was generated on the assumption > that backslash is the escape character, but is being reloaded under a > different assumption. Once you got out-of-sync on that one data value, > you'd have a mess --- all the intended line-ending newlines would be > inside quotes, up till you hit another URL with an embedded quote > (or newline?). CSV is not exactly a robust representation :-( What alternative is there to import large amounts of data? > So the short answer seems to be that we were all misled by the large > alloc-request number into thinking there was some data-corruption > behavior involved, when we should have been quizzing Johann on exactly > what COPY parameters he was giving. I think I gave it in my first email. Here it is: \copy squidlogs from '/home/js/accesspg.csv' using delimiters ' ' csv quote as '"' Regards Johann -- Johann Spies Telefoon: 021-808 4036 Informasietegnologie, Universiteit van Stellenbosch "Let no man despise thy youth; but be thou an example of the believers, in word, in conversation, in charity, in spirit, in faith, in purity." I Timothy 4:12
On Mon, May 28, 2007 at 11:18:37AM -0400, Tom Lane wrote: > Alvaro Herrera <alvherre@commandprompt.com> writes: > > Johann Spies wrote: > >> #3 0x00000000005ff1c8 in perform_default_encoding_conversion ( > >> src=0x2b881e87f040 "\"2007-04-20\" \"18:07:06\" \"192.168.0.100\" \"73\" \"http://www.kerkbode.co.za/kerkbode/images/blank.gif\\\"\"\n\"2007-04-20\"\"18:07:06\" \"192.168.0.100\" \"69\" \"http://develop.christians.co.za/phpAdsNew/adx.js\"\n\"20"...,len=337739539, is_client_to_server=5 '\005') at mbutils.c:461 > > > > Interesting. You are passing a single 300 MB string here, not > > 90-something char lines. The problem is probably in newlines. Why are > > they not getting used as line separators escapes me. > > Stripped of gdb's own backslashing, the data seems to be > > ...images/blank.gif\"" ^^ And that is where the problem is. > "2007... > > which leads me to think that the dump was generated on the assumption > that backslash is the escape character, but is being reloaded under a > different assumption. Backslash is indeed used as escape character. I see now that I should have specified it in my copy command! I have figured out how to do it now and successfully imported that part of the logs into a table. Thanks for your help. Regards Johann -- Johann Spies Telefoon: 021-808 4036 Informasietegnologie, Universiteit van Stellenbosch "Let no man despise thy youth; but be thou an example of the believers, in word, in conversation, in charity, in spirit, in faith, in purity." I Timothy 4:12