SEGFAULT on SELECT * FROM view
Hi all,
When SELECTing from one particular view, I get reproducible SEGFAULTs in
my pg 9.1.2 production database. To test for hardware errors, I ran
memtest, which succeeded. I then asked for help in #postgres and got
advice to create stacktraces, but I couldn't find the problem yet.
Scenario: VIEW 'vwa' on TABLE 'tba' and some subselects and aggregates.
I've no idea what's wrong with this view, but a
SELECT * FROM vwa WHERE myid = 1000010 LIMIT 100 OFFSET 0;
and even EXPLAINing this statement SEGFAULTs the server.
gdb trace: http://pgsql.privatepaste.com/eacd1b6c5d
gdb "ec" output: http://pgsql.privatepaste.com/a61db5b564
smaps: http://pgsql.privatepaste.com/3c4f494015
Then I used the definition of vwa, and created vwb, and the very same
SELECT (just with vwb) works perfectly fine. I've a pastebin link
comparing the two view definitions, which I'm willing to share privately
if that helps. The views actually differ, although the look identical
with \d+ in the psql console, in that the newer view names more columns
that were added to the referenced tables lately.
So, you tell me, what's wrong with the old view?
Thanks a lot,
Chris
"chris r." <chricki@gmx.net> writes:
So, you tell me, what's wrong with the old view?
Nothing; that looks like a planner bug to me. Please submit a
self-contained test case.
regards, tom lane
So, you tell me, what's wrong with the old view?
Nothing; that looks like a planner bug to me. Please submit a
self-contained test case.
I ported the entire schema to my test DB server and could not reproduce
the error there. Note that probably recreating the view solves this
issue. Given this, how should I proceed to create a test case? Any
tutorial on this? (I'm not too familiar with all this yet.)
Chris
"chris r." <chricki@gmx.net> writes:
Nothing; that looks like a planner bug to me. Please submit a
self-contained test case.
I ported the entire schema to my test DB server and could not reproduce
the error there. Note that probably recreating the view solves this
issue. Given this, how should I proceed to create a test case? Any
tutorial on this? (I'm not too familiar with all this yet.)
It's possibly statistics-dependent; make sure you have the same stats
targets on both DBs, and try re-analyzing a few times. Check other
planner parameters are the same, too.
regards, tom lane
I ported the entire schema to my test DB server and could not reproduce
the error there. Note that probably recreating the view solves this
issue. Given this, how should I proceed to create a test case? Any
tutorial on this? (I'm not too familiar with all this yet.)It's possibly statistics-dependent; make sure you have the same stats
targets on both DBs, and try re-analyzing a few times. Check other
planner parameters are the same, too.
In addition to the schema, I now also copied parts of the data in our
production system to the testing DB. (I cannot copy all data, as it's
too large for the testing DB.) After a couple of VACUUM FULL ANALYZEs,
the bug still doesn't reproduce.
In our production environment, I'm working with the re-created view that
doesn't show the SEGFAULT behavior. I can live with this workaround, as
only one particular view seems to be affected, but if you want me to
debug more on the error please let me know.
Thanks for your help until now!
Chris
On Mon, Jan 2, 2012 at 1:42 AM, chris r. <chricki@gmx.net> wrote:
I ported the entire schema to my test DB server and could not reproduce
the error there. Note that probably recreating the view solves this
issue. Given this, how should I proceed to create a test case? Any
tutorial on this? (I'm not too familiar with all this yet.)It's possibly statistics-dependent; make sure you have the same stats
targets on both DBs, and try re-analyzing a few times. Check other
planner parameters are the same, too.In addition to the schema, I now also copied parts of the data in our
production system to the testing DB. (I cannot copy all data, as it's
too large for the testing DB.) After a couple of VACUUM FULL ANALYZEs,
the bug still doesn't reproduce.In our production environment, I'm working with the re-created view that
doesn't show the SEGFAULT behavior. I can live with this workaround, as
only one particular view seems to be affected, but if you want me to
debug more on the error please let me know.
It would be awfully nice to figure out what was going on. Another
thing that would help, if you can still reproduce the error, would be
to get a stack trace. The easiest way would probably be to enable
core dumps (pg_ctl -c start, or ulimit -c unlimited followed by pg_ctl
start). Then, when you get a core, which might end up inside $PGDATA
or perhaps elsewhere depending on your system configuration, do:
gdb /path/to/postgres /path/to/corefile
and then from within gdb:
bt
...to get a backtrace.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company