G'day all,
I'm going to go slowly on this, but I am intermittently (as in sometimes the query works fine, sometimes it stops after 5 minutes and I get the message) receiving the error message below on a long running query that is populating a newly created table with a PostGIS Geometry column.
The Error Message is :
SQLSTATE[XX000]: Internal error: 7 ERROR: could not open file "base/102979/430122_fsm": Invalid argument
I don't seem to be able to leverage any search engine to explain what this message means - To many quotes, colons and brackets for google. I'm not even sure why a file is being opened, but I assume that the file is part of the data storage - which maybe I need to know about, but I haven't so far!
I have also fiddled a bit with the postgresql.conf settings to increase work men etc. The details of the table and query are below.
So my question is really - what does this error message mean, and where do I start looking for what could be causing it. Should I try a debug trace, or just looking in the logs (I've attached the log entries at the bottom, but maybe I should up the logging level)?
I haven't yet posted this to the postgis list, as it looked as though this message is a postgresql message, not a postgis one.
cheers
Ben
I'm running on :
PostgreSQL 9.1.3 on x86_64-apple-darwin11.3.0, compiled by i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. build 5658) (LLVM build 2336.9.00), 64-bit
and PostGIS :
POSTGIS="1.5.3" GEOS="3.3.2-CAPI-1.7.2" PROJ="Rel. 4.7.1, 23 September 2009" LIBXML="2.7.3" USE_STATS
The table definition at the time that the error is occurring is:
Table "system.ctybnda2009"
Column | Type | Modifiers
-----------+-----------------------------+----------------------------------------------------------------
recordid | integer | not null default nextval('ctybnda2009_recordid_seq'::regclass)
ccode | character varying(3) |
year | integer | not null default 2005
fips | character varying(2) |
l_1_name | character varying |
l_2_name | character varying |
l_3_name | character varying |
area | numeric |
modfiedon | timestamp without time zone | not null default now()
the_geom | geometry |
Indexes:
"ctybnda2009_recordid_key" UNIQUE CONSTRAINT, btree (recordid)
Check constraints:
"enforce_dims_the_geom" CHECK (st_ndims(the_geom) = 2)
"enforce_geotype_the_geom" CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL)
"enforce_srid_the_geom" CHECK (st_srid(the_geom) = 4326)
The query that causes the problem is :
INSERT INTO system.ctybnda2009 (ccode, the_geom)
SELECT m.country, st_multi(st_union(m.geom))
FROM maptable m
LEFT OUTER JOIN countries c
ON m.country = c.ccode
WHERE geom IS NOT NULL
AND m.valstart <= 2009
AND (m.valend IS NULL OR m.valend >= 2009)
GROUP BY 1
ORDER BY 1;
postgresql.log
2012-05-03 05:18:23 WSTERROR: could not open file "base/102979/430122_fsm": Invalid argument
2012-05-03 05:18:23 WSTSTATEMENT: INSERT INTO system.ctybnda2011 (ccode, the_geom) SELECT m.country, st_multi(st_union(m.geom)) FROM maptable m LEFT OUTER JOIN countries c ON m.country = c.ccode WHERE geom IS NOT NULL AND m.valstart <= $1 AND (m.valend IS NULL OR m.valend >= $2) GROUP BY 1 ORDER BY 1;
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
