Re: [BUGS] Postgres 9.0 crash on win7
Just an update on this issue: I've been able to reproduce the crash (thanks for the test case!) and obtain the crash information here on my 32-bit windows 7 install, so there's no need for you to do anything else so far. I still can't get a usable backtrace. The autovacuum workers/launcher split makes it *really* hard to catch an autovacuum worker in action. The post-mortem debugger won't trigger for service processes, so I can't trap it that way, and I can't pre-attach a debugger to it. OTOH, it's now pretty clearly autovacuum that's dying, as Tom Lane suggested it probably would be. debug5 logging shows: 2010-10-04 18:18:54 WST 3692 DEBUG: InitPostgres 2010-10-04 18:18:54 WST 3692 DEBUG: my backend id is 3 2010-10-04 18:18:54 WST 3692 DEBUG: StartTransaction 2010-10-04 18:18:54 WST 3692 DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 2010-10-04 18:18:54 WST 3692 DEBUG: mapped win32 error code 2 to 2 2010-10-04 18:18:54 WST 3692 DEBUG: CommitTransaction 2010-10-04 18:18:54 WST 3692 DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 2010-10-04 18:18:54 WST 3692 DEBUG: autovacuum: processing database "test" 2010-10-04 18:18:54 WST 3692 DEBUG: StartTransaction 2010-10-04 18:18:54 WST 3692 DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 2010-10-04 18:18:54 WST 3692 DEBUG: pg_statistic: vac: 0 (threshold 118), anl: 0 (threshold 84) ... followed by lots more startup messages, a series of transactions, then: 2010-10-04 18:18:55 WST 3692 DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 159661/1/0 (used), nestlvl: 1, children: 2010-10-04 18:18:55 WST 3692 DEBUG: StartTransaction 2010-10-04 18:18:55 WST 3692 DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 2010-10-04 18:18:55 WST 3692 DEBUG: poslist: vac: 0 (threshold 50), anl: 1804 (threshold 50) 2010-10-04 18:18:55 WST 3692 DEBUG: autovac_balance_cost(pid=3692 db=98315, rel=98390, cost_limit=200, cost_delay=20) 2010-10-04 18:18:55 WST 3692 DEBUG: CommitTransaction 2010-10-04 18:18:55 WST 3692 DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 2010-10-04 18:18:55 WST 3692 DEBUG: StartTransaction 2010-10-04 18:18:55 WST 3692 DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 2010-10-04 18:18:55 WST 3692 DEBUG: analyzing "public.poslist" 2010-10-04 18:18:55 WST 2408 DEBUG: server process (PID 3692) was terminated by exception 0xC005 2010-10-04 18:18:55 WST 2408 LOG: server process (PID 3692) was terminated by exception 0xC005 Autovacuum usually dies after: analyzing "public.suolo" (three times) but I've also seen it die after: analyzing "public.poslist" as shown above. I'm really struggling to get a debugger attached to the *...@#$@#...@$* thing though. Ideas? *punting to PostGIS folks for a look* -- Craig Ringer Tech-related writing at http://soapyfrogs.blogspot.com/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5691: Documentation typo ?
The following bug has been logged online: Bug reference: 5691 Logged by: Tatsuhito Kasahara Email address: kasahara.tatsuh...@oss.ntt.co.jp PostgreSQL version: 9.0 Operating system: Any Description:Documentation typo ? Details: The following is a part of doc/src/sgml/user-manag.sgml == For example, if for some reason you want to disable index scans (hint: not a good idea) anytime you connect, you can use: ALTER ROLE myname SET statement_timeout = '5min'; == It seems to be a wrong example (or text). Best regards, -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Postgres 9.0 crash on win7
Hi, I have do some other test. I set autovacuum = off. With this setting the server don't crash. But certainly something go bad. Infact if (after run the query) I connect with posql as user 'postgres' and try a single command VACUUM; nothing happened. But if I try a single command ANALYZE; the server crash istantly. After this I restart the server and re-try reconnecting to PG9 but without re-run the query script. again if a run ANALYZE the server crash. I think the script has insert write something wrong in a table and after this insert. Always time someone (autovacuum or user) try an analyze this cause the crash. however seem to be the analyze to do the crash, not the vacuum. Perhaps to problem is in the INSERT INTO. that put sometime that the old PG8.4.4 can understand and use, meanwhile PG) don't understand or is not capable to use. for example, I use often some field of big size: VARCHAR(20) the next try I do is to remove the postgis components to see if again this happened. Andrea. Il 04/10/2010 04:56, Tom Lane ha scritto: Craig Ringer writes: While it's consistently crashing my Pg 9 on win7 32-bit, too, I haven't been able to get a backtrace yet. I thought it'd be trivial given the ease of reproducing the crash - but the process that's crashing isn't the backend running the query. It looks like it's one of the helpers like the stats collector, autovac, bgwriter, etc. I'm unsure which yet. I'd bet on autovacuum. You might be able to reproduce the crash in the foreground process by issuing a manual VACUUM or ANALYZE. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Planner producing 100% duplicate subplans when unneeded
On Mon, Sep 27, 2010 at 5:09 PM, Daniel Grace wrote: > Is there any chance this might be looked at in a future release? This is another interesting example of a case where an inlining-type optimization (which is effectively what's happening here, I think) turns out to be a negative. We had one a while back that involved actual function inlining, which is not quite what's happening here, but it's close. It doesn't seem too hard to figure out whether or not inlining is a win (non-trivial subexpressions should probably never get duplicated), but nobody's gotten around to writing the logic to make it work yet. One useful technique is to stick "OFFSET 0" into the subquery; that prevents it from being inlined and gives you something more like the plan you were hoping for. Whether or not this will get looked at in a future release is a tough question to answer. It's possible that someone (most likely, Tom) will get motivated to fix this out of sheer annoyance with the current behavior, or will notice a way to improve it incidentally while making some other change. But of course the only way to make sure it gets fixed is to do it yourself (or pay someone to do it). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5692: fatal error
The following bug has been logged online: Bug reference: 5692 Logged by: V.J. Email address: molo...@gmx.de PostgreSQL version: 8.4 Operating system: Ubuntu Lucid 10.04 Description:fatal error Details: * Starting PostgreSQL 8.4 database server * The PostgreSQL server failed to start. Please check the log output: 2010-10-04 19:58:40 CEST FATAL: konnte auf private Schl?sseldatei >>server.key<< nicht zugreifen: No such file or directory [fail] -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5692: fatal error
On Mon, Oct 4, 2010 at 11:59, V.J. wrote: > * Starting PostgreSQL 8.4 database server > * The PostgreSQL server failed to start. Please check > the log output: > 2010-10-04 19:58:40 CEST FATAL: konnte auf private Schl?sseldatei >>>server.key<< nicht zugreifen: No such file or directory Looks like you have ssl = on but are missing some of the required ssl files ("server.key"). See http://www.postgresql.org/docs/8.4/static/ssl-tcp.html for more. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Planner producing 100% duplicate subplans when unneeded
As a theoretical question (I'm not savvy on Postgres's code but might be intrigued enough to beat on it anyways), is it feasible to do an additional pass on the query plan that essentially goes: - Are these two subplans identical? - Are they at the same part of the tree? and if both of these conditions are true, discard one subplan and rewrite all references to point to the other one? Assuming it IS possible, are there any particular situations where it wouldn't work? On Mon, Oct 4, 2010 at 11:47 AM, Robert Haas wrote: > On Mon, Sep 27, 2010 at 5:09 PM, Daniel Grace wrote: >> Is there any chance this might be looked at in a future release? > > This is another interesting example of a case where an inlining-type > optimization (which is effectively what's happening here, I think) > turns out to be a negative. We had one a while back that involved > actual function inlining, which is not quite what's happening here, > but it's close. It doesn't seem too hard to figure out whether or not > inlining is a win (non-trivial subexpressions should probably never > get duplicated), but nobody's gotten around to writing the logic to > make it work yet. One useful technique is to stick "OFFSET 0" into > the subquery; that prevents it from being inlined and gives you > something more like the plan you were hoping for. > > Whether or not this will get looked at in a future release is a tough > question to answer. It's possible that someone (most likely, Tom) > will get motivated to fix this out of sheer annoyance with the current > behavior, or will notice a way to improve it incidentally while making > some other change. But of course the only way to make sure it gets > fixed is to do it yourself (or pay someone to do it). > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise Postgres Company > -- Daniel Grace AGE, LLC System Administrator and Software Developer dgr...@wingsnw.com // (425)327-0079 // www.wingsnw.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Planner producing 100% duplicate subplans when unneeded
On Mon, Oct 4, 2010 at 4:15 PM, Daniel Grace wrote: > As a theoretical question (I'm not savvy on Postgres's code but might > be intrigued enough to beat on it anyways), is it feasible to do an > additional pass on the query plan that essentially goes: > > - Are these two subplans identical? > - Are they at the same part of the tree? > > and if both of these conditions are true, discard one subplan and > rewrite all references to point to the other one? > > Assuming it IS possible, are there any particular situations where it > wouldn't work? Well, volatile functions would be a problem, but I don't think that's really the way to go anyway. I think that deciding whether or not to collapse subqueries into the main query (which is what's happening here) seems like it could be done by counting the number of times any given subexpression is referenced, which seems like it would be a lot cheaper than checking things against each other pairwise to see if they match up. Slowing down the planner to detect cases like this wouldn't be very appealing: SELECT (SELECT SUM(a) FROM bob, SELECT SUM(b) FROM bob, SELECT SUM(c) FROM bob); ...because very few people are going to write that query that way anyway, and unless it's almost free to notice the duplication, it doesn't make sense to spend planning time on it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5684: pg_restore does not restore schemas
On Wed, Sep 29, 2010 at 2:20 PM, Nathan Gorter wrote: > I am attempting to perform a dump from one database, create a new database, > and restore the data to that new database. In my old database I have schemas > besides the public schema that I have created myself. The pg_dump works fine > and the pg_restore does not generate any errors, but the pg_restore fails to > create the extra schemas, and instead restores the tables in those other > schemas into the public schema. I have included my pg_dump and pg_restore > commands below. I have tried the pg_restore command with and without the -c > switch, and I have tried calling pg_restore twice, the first time using the > -s switch and the second time using the -a switch. None of these have > worked. On a note, when I manually create the extra schemas myself before > performing the restore, it all works correctly. When you use -t table, it only dumps those particular tables, and not any schemas, functions, operators, or other database objects that might be present in the target database. This is by design, so it's not a bug. If you remove all the -t options, you'll get a dump of all objects of all types in that database. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5690: pg_upgrade fails
On Sun, Oct 3, 2010 at 12:16 PM, Tony marston wrote: > > The following bug has been logged online: > > Bug reference: 5690 > Logged by: Tony marston > Email address: t...@marston-home.demon.co.uk > PostgreSQL version: 9.0 > Operating system: Windows XP > Description: pg_upgrade fails > Details: > > I am trying to upgrade from version 8.4 to 9.0, but when I run pg_upgrade it > fails with the following error: > > "Creating catalog dump Access is denied" > > There were problems executing "f:/postgresql/9.0/bin/pg_dumpall --port 5432 > --username "postgres" --schema-only --binary-upgrade > > "C:\WINDOWS\system32\pg_upgrade_dump_all.sql" > > How can this be? If it's running as superuser then surely it has automatic > access to everything!!! Hmm. Does anything should up in the server log? What do you get if you run that command by hand? What if you run it by hand without --binary-upgrade? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Planner producing 100% duplicate subplans when unneeded
Robert Haas writes: > On Mon, Sep 27, 2010 at 5:09 PM, Daniel Grace wrote: >> Is there any chance this might be looked at in a future release? > This is another interesting example of a case where an inlining-type > optimization (which is effectively what's happening here, I think) > turns out to be a negative. We had one a while back that involved > actual function inlining, which is not quite what's happening here, > but it's close. It doesn't seem too hard to figure out whether or not > inlining is a win (non-trivial subexpressions should probably never > get duplicated), but nobody's gotten around to writing the logic to > make it work yet. Actually this case has some history behind it. The reason that the sub-sub-query gets duplicated is that we flatten the sub-query, resulting in duplicating its output expressions wherever they're referenced. Now before you say that that's stupid, please notice that this case got disabled in 7.3 as a bug workaround, and that almost immediately produced howls of pain: http://archives.postgresql.org/pgsql-general/2002-12/msg00410.php http://archives.postgresql.org/pgsql-performance/2002-12/msg00214.php so I undid it as soon as I could: http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=de97072e3c88e104a55b0d5c67477f1b0097c003 While just shutting off the pull-up unconditionally would merely require reintroducing a contains_subplans() restriction in is_simple_subquery(), I'm unwilling to do that because of the earlier complaints. And "figuring out whether it's a win" is a lot harder than you opine above: at the point where this decision has to be taken, we have no cost information whatsoever, and not even any clear idea whether the subquery outputs are referenced at all let alone multiply referenced. My thought about the current shortest path to a solution would be to wrap subquery-containing output expressions in PlaceHolderVars. Per this comment elsewhere in is_simple_subquery(), /* * Don't pull up a subquery that has any volatile functions in its * targetlist. Otherwise we might introduce multiple evaluations of these * functions, if they get copied to multiple places in the upper query, * leading to surprising results. (Note: the PlaceHolderVar mechanism * doesn't quite guarantee single evaluation; else we could pull up anyway * and just wrap such items in PlaceHolderVars ...) */ that doesn't entirely work at the moment, but it might work if we were to rejigger the PHV mechanism a bit. This would be attractive because the added overhead of a PHV would be nearly nil, so we could afford to do this without making any predictions about relative costs. As a workaround until somebody gets around to looking at that, I'd suggest that Daniel plaster his sub-query with the good old all-purpose optimization fence, "OFFSET 0". That will prevent flattening and thus prevent the sub-sub-query from getting duplicated. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Postgres 9.0 crash on win7
On 4/10/2010 10:56 AM, Tom Lane wrote: Craig Ringer writes: While it's consistently crashing my Pg 9 on win7 32-bit, too, I haven't been able to get a backtrace yet. I thought it'd be trivial given the ease of reproducing the crash - but the process that's crashing isn't the backend running the query. It looks like it's one of the helpers like the stats collector, autovac, bgwriter, etc. I'm unsure which yet. I'd bet on autovacuum. You might be able to reproduce the crash in the foreground process by issuing a manual VACUUM or ANALYZE. Thanks. I wasn't able to get the backend running the script to crash initially, even with an explicit VACUUM, ANALYZE, or VACUUM ANALYZE. After turning autovacuum off completely, though, it does crash when ANALYZE is run. postgres.exe!pfree(void * pointer=0x68f08610) Line 591 + 0x3 bytes C postgres.exe!examine_attribute(RelationData * onerel=0x, int attnum=5, Node * index_expr=0x) Line 877 C postgres.exe!do_analyze_rel(RelationData * onerel=0x01747b48, VacuumStmt * vacstmt=0x01690580, char update_reltuples='', char inh=0) Line 357 + 0xa bytes C postgres.exe!analyze_rel(unsigned int relid=131097, VacuumStmt * vacstmt=0x01690580, BufferAccessStrategyData * bstrategy=0x018fc0f0, char update_reltuples='') Line 232 C postgres.exe!vacuum(VacuumStmt * vacstmt=0x01690580, unsigned int relid=0, char do_toast='', BufferAccessStrategyData * bstrategy=0x018fc0f0, char for_wraparound=0, char isTopLevel='') Line 248C postgres.exe!standard_ProcessUtility(Node * parsetree=0x01690580, const char * queryString=0x0168fc78, ParamListInfoData * params=0x, char isTopLevel='', _DestReceiver * dest=0x01690730, char * completionTag=0x004ff998) Line 1012 + 0x13 bytesC postgres.exe!PortalRunUtility(PortalData * portal=0x, Node * utilityStmt=0x, char isTopLevel='', _DestReceiver * dest=0x01690730, char * completionTag=0x004ff998) Line 1199 C postgres.exe!PortalRunMulti(PortalData * portal=0x, char isTopLevel='', _DestReceiver * dest=0x01690730, _DestReceiver * altdest=0x01690730, char * completionTag=0x004ff998) Line 1298 + 0x11 bytes C postgres.exe!PortalRun(PortalData * portal=0x016dd028, long count=2147483647, char isTopLevel='', _DestReceiver * dest=0x01690730, _DestReceiver * altdest=0x01690730, char * completionTag=0x004ff998) Line 823 + 0x17 bytes C postgres.exe!exec_simple_query(const char * query_string=0x) Line 1059 C postgres.exe!PostgresMain(int argc=2, char * * argv=0x01635220, const char * username=0x009780b0) Line 3871C postgres.exe!BackendRun(Port * port=0x0002) Line 3550 + 0x17 bytes C postgres.exe!SubPostmasterMain(int argc=3, char * * argv=0x00972878) Line 4042 + 0x8 bytes C postgres.exe!main(int argc=3, char * * argv=0x00972878) Line 165 + 0x7 bytes C postgres.exe!__tmainCRTStartup() Line 586 + 0x17 bytes C kernel32.d...@basethreadinitthunk@12() + 0x12 bytes ntdll.dll!___rtluserthreadst...@8() + 0x27 bytes ntdll.dll!__rtluserthreadst...@8() + 0x1b bytes It's crashing in pfree, as called by examine_attribute here: if (!ok || stats->compute_stats == NULL || stats->minrows <= 0) { pfree(stats->attrtype); pfree(stats->attr); <-- crash pfree(stats); return NULL; } ... which is palloc'd earlier in examine_attribute. VC++ is having trouble examining the locals in examine_attribute(); I'm unsure if this is an optimization issue, lack of full debug info, or something wrong with the state of the stack. It's definitely crashing while analyzing the relation "suolo" - not only do the logs show analysis beginning, but onerel->rd_rel->relname is "suolo". At the time of the crash it seems to have already added the column with attr->attname = "codpoligono" to vacattrstats and is examining the column with attnum=5 when it crashes. A quick look at pg_class and pg_attribute shows that this is (surprise!) the "geom" column of type "geometry". PostGIS on Windows is a bit outside my depth, especially as it's not a neat crash in the analyze function its self. Hopefully this'll give the PostGIS folks something to go on, though. Andrea: please pass it on. -- Craig Ringer Tech-related writing at http://soapyfrogs.blogspot.com/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Postgres 9.0 crash on win7
Craig Ringer writes: > After turning autovacuum off completely, though, it does crash when > ANALYZE is run. >> postgres.exe!pfree(void * pointer=0x68f08610) Line 591 + 0x3 bytes C >> postgres.exe!examine_attribute(RelationData * onerel=0x, int >> attnum=5, Node * index_expr=0x) Line 877 C >> postgres.exe!do_analyze_rel(RelationData * onerel=0x01747b48, VacuumStmt * >> vacstmt=0x01690580, char update_reltuples='', char inh=0) Line 357 + 0xa >> bytes C Hmm. That is suspiciously close to the location of some last-minute changes in Postgres 9.0. I wonder whether Andrea is using a version of PostGIS that was compiled against pre-9.0RC1 Postgres sources. If they weren't accounting for this patch: http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=67becf8d41a082eaaf6db6e0860d49409b79e32b then we could easily have a crash right about here --- in fact it looks like this is exactly what you'd get, because the extension would think that the compute_stats field is where attrtype now is, so the "pfree(stats->attrtype)" would be trying to pfree a function address. In short, what we've got here is a version skew problem. That doubtless explains why Craig couldn't duplicate it on his Linux machine. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Postgres 9.0 crash on win7
On 5/10/2010 10:02 AM, Tom Lane wrote: Craig Ringer writes: After turning autovacuum off completely, though, it does crash when ANALYZE is run. postgres.exe!pfree(void * pointer=0x68f08610) Line 591 + 0x3 bytes C postgres.exe!examine_attribute(RelationData * onerel=0x, int attnum=5, Node * index_expr=0x) Line 877 C postgres.exe!do_analyze_rel(RelationData * onerel=0x01747b48, VacuumStmt * vacstmt=0x01690580, char update_reltuples='', char inh=0) Line 357 + 0xa bytes C Hmm. That is suspiciously close to the location of some last-minute changes in Postgres 9.0. I wonder whether Andrea is using a version of PostGIS that was compiled against pre-9.0RC1 Postgres sources. If so, I am to and it's the latest PostGIS binary from their website. In short, what we've got here is a version skew problem. That doubtless explains why Craig couldn't duplicate it on his Linux machine. Yep, 'cos I built PostGIS directly against the installed Pg on my linux box. I'm about to head for work. When I get back I'll build PostGIS locally and see if the problem conveniently goes away. -- Craig Ringer Tech-related writing at http://soapyfrogs.blogspot.com/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs