Date: Mon, 14 May 2007 15:13:18 +0200
From: Johann Spies <jspies@sun.ac.za>
To: pgsql-admin@postgresql.org
Subject: ERROR:  invalid memory alloc request size 
Message-ID: <20070514131318.GA26537@sun.ac.za>
Mail-Followup-To: Johann Spies <jspies@sun.ac.za>, pgsql-admin@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.5.13 (2006-08-11)

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