[GENERAL] SQLSTATE XX000 Internal Error 7

[Ben Madin]

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;

[Tom Lane]

Post by Ben Madin
SQLSTATE[XX000]: Internal error: 7 ERROR: could not open file "base/102979/430122_fsm": Invalid argument

[ scratches head ... ] AFAICS the only documented reason for open() to
fail with EINVAL on OS X is

[EINVAL] The value of oflag is not valid.

which is surely bogus since that code path calls it with a constant
value for oflag --- there's no way it could fail just some of the time.

So this is smelling like a kernel or filesystem bug. I wonder exactly
which OS X update you're running, and what sort of filesystem the
database is stored on.

regards, tom lane

[Ben Madin]

G'day Tom,

Post by Tom Lane

Post by Ben Madin
SQLSTATE[XX000]: Internal error: 7 ERROR: could not open file "base/102979/430122_fsm": Invalid argument

[ scratches head ... ] AFAICS the only documented reason for open() to
fail with EINVAL on OS X is
[EINVAL] The value of oflag is not valid.
which is surely bogus since that code path calls it with a constant
value for oflag --- there's no way it could fail just some of the time.
So this is smelling like a kernel or filesystem bug. I wonder exactly
which OS X update you're running, and what sort of filesystem the
database is stored on.

I think that sounds bad!

The OSX Update is 10.7.3 (11D50)

The System is a 2.66 GHz Intel Core i7 with 8GB RAM.

The database is stored on a partition that looks like :

Capacity: 447.69 GB (447 687 770 112 bytes)
Available: 74.96 GB (74 956 308 480 bytes)
Writable: Yes
File System: Journaled HFS+
BSD Name: disk0s2
Mount Point: /
Content: Apple_HFS


and the data is stored in the /usr/local/pgsql-9.1/data directory, but there is a symlink (as I've retained the previous versions when I upgrade.) and so the /usr/local directory looks like :

lrwxr-xr-x 1 root wheel 9 1 May 11:11 pgsql -> pgsql-9.1
drwxr-xr-x 11 root wheel 374 17 Feb 21:26 pgsql-8.4
drwxr-xr-x 8 root admin 272 17 Feb 21:26 pgsql-9.0
drwxr-xr-x 8 root admin 272 17 Feb 22:41 pgsql-9.1

and the data directory :

drwx------ 20 _postgres _postgres 680 1 May 11:11 data

is this the sort of exact information you were wondering?

Since I last posted, I have again received :

PL/pgSQL function "fill_ctybnda" line 18 at EXECUTE statement
ERROR: could not open file "base/102979/430320_fsm": Invalid argument

and I went looking and found in the base/102979/ directory:

-rw------- 1 _postgres _postgres 1253376 3 May 11:51 430320
-rw------- 1 _postgres _postgres 24576 3 May 11:51 430320_fsm

so it look to my uneducated eye as though it has been able to open the file(quite a few of the files ending in _fsm have 24576 bytes)

(PS How did you come to deciding that it was EINVAL - is that 'Error INVALid argument'?)

cheers

Ben

[Martijn van Oosterhout]

Post by Ben Madin
(PS How did you come to deciding that it was EINVAL - is that 'Error INVALid argument'?)

It's one of the standard error codes, see for example
http://www.jbox.dk/sanos/source/include/errno.h.html

Tom is right, it's not clear how this error can occur. Linux does it if
you ask for O_DIRECT on a filesystem that doesn't support it, but it
doesn't look like that's the problem here either.

Have a nice day,

[Tom Lane]

Post by Tom Lane
So this is smelling like a kernel or filesystem bug. I wonder exactly
which OS X update you're running, and what sort of filesystem the
database is stored on.

[ it's an up-to-date, pretty vanilla looking OS X Lion installation ]

Hm, so no smoking gun visible there. Can you put together a
self-contained test case that triggers this error for you? I recognize
that it'll be of the form "if you run this long enough, it'll fail"
rather than a deterministic failure, but that's OK. What we want to
know at this point is if anyone else can reproduce it. If we can do
that, and can show positively that the kernel is returning EINVAL for
valid-looking open() arguments, we'll have the needed ammunition to
file a bug with Apple.

(PS How did you come to deciding that it was EINVAL - is that 'Error INVALid argument'?)

The "Invalid argument" part of our error message comes from strerror(),
and that's the standard text for EINVAL.

regards, tom lane

[johnkeefe]

I know this post is a few months old now, but I have a strikingly similar
setup and am getting a similar, somewhat more reliable error:

ERROR: could not open file "base/29292/12186914_fsm": Invalid argument

It happens while doing an ST_Difference() function on two large geometries.

I'm running OSX 10.8 (Mountain Lion)
Postgresql 9.4.1
PostGIS 2.0.1

It's failing repeatedly for me right now, so at the moment it's a hard fail.
That said, just last week, with the same database, same command but
different table, I got this slightly different error:

ERROR: could not open file "base/29292/49722_fsm": Invalid argument

THAT error, however, cleared up mysteriously after I shut down and
restarted. (I haven't been able to get tonight's error to clear up in the
same way.)

What other information can I provide to help track squash this bug?

Thanks,
John




--
View this message in context: http://postgresql.1045698.n5.nabble.com/SQLSTATE-XX000-Internal-Error-7-tp5682117p5721837.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[Craig Ringer]

Post by johnkeefe
I know this post is a few months old now, but I have a strikingly similar
ERROR: could not open file "base/29292/12186914_fsm": Invalid argument
It happens while doing an ST_Difference() function on two large geometries.
I'm running OSX 10.8 (Mountain Lion)
Postgresql 9.4.1

Did you mean 8.4.1?

--
Craig Ringer

[johnkeefe]

Ack. I meant 9.1.4:

~ ∴ psql
psql (9.1.4)

Thanks.



--
View this message in context: http://postgresql.1045698.n5.nabble.com/SQLSTATE-XX000-Internal-Error-7-tp5682117p5721842.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[n***@gmail.com]

I just ran into this problem recently using Postgres.app 9.2 and it appears to somehow be related to my user's open files limit setting:

$ ulimit -n
256

I bumped it:

$ ulimit -n 2048
$ ulimit -n
2048

and started Postgres manually within the same shell:

$ /Applications/Postgres.app/Contents/MacOS/Postgres
2013-08-23 12:07:43.873 Postgres[67908:707] Existing PGVersion: 9.2
2013-08-23 12:07:43.874 Postgres[67908:707] Installed PGVersion: 9.2
2013-08-23 12:07:43.932 Postgres[67908:707] 67910 /Applications/Postgres.app/Contents/MacOS/bin/pg_ctl: Status 0

and executing the same _sequence_ of command that was consistently causing "could not open" now worked.

Interestingly and confusingly, i was reproducing the error on demand by dropping and recreating a relatively small schema containing a few thousand rows of data aggregated from other schemas. Once the error vanished, i can go back to the 256 open files limit and the same sequence of commands no longer reproduces the error. So the open files limit doesn't seem to be _the_ cause but it does seem related somehow.

2¢

Post by Ben Madin
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.
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
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
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
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 |
"ctybnda2009_recordid_key" UNIQUE CONSTRAINT, btree (recordid)
"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)
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;
--
http://www.postgresql.org/mailpref/pgsql-general