Thread: long sql "in" clause crashes server process (8.0 Release)
On a table as CREATE TABLE bcachekunde ( id_p int8 NOT NULL, name text, konzern text, sort int2, pname text, strasse text, plz text, ort text, betreuera text, jdstyp text, letztespeicherung timestamp, CONSTRAINT bcachekunde_pkey PRIMARY KEY (id_p) ) I issue the following SQL: delete from bcachekunde where id_p in (1,2,3,4,6,10,12,13,14,16, [....10740 more ids...] 90041492, 90721705) with the result: Connection to the server gets lost. In the server log: 2005-01-24 11:35:04 LOG: server process (PID 3776) exited with unexpected status 128 2005-01-24 11:35:04 LOG: terminating any other active server processes 2005-01-24 11:35:05 WARNING: terminating connection because of crash of another server process 2005-01-24 11:35:05 DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2005-01-24 11:35:05 HINT: In a moment you should be able to reconnect to the database and repeat your command. 2005-01-24 11:35:05 LOG: all server processes terminated; reinitializing 2005-01-24 11:35:05 LOG: database system was interrupted at 2005-01-24 11:34:14 Westeuropäische Normalzeit 2005-01-24 11:35:05 LOG: checkpoint record is at 0/CD25428 2005-01-24 11:35:05 LOG: redo record is at 0/CD25428; undo record is at 0/0; shutdown TRUE 2005-01-24 11:35:05 LOG: next transaction ID: 885303; next OID: 110250 2005-01-24 11:35:05 LOG: database system was not properly shut down; automatic recovery in progress 2005-01-24 11:35:05 LOG: record with zero length at 0/CD25468 2005-01-24 11:35:05 LOG: redo is not required 2005-01-24 11:35:05 LOG: database system is ready 2005-01-24 11:36:51 LOG: server process (PID 3916) exited with unexpected status 128 2005-01-24 11:36:51 LOG: terminating any other active server processes 2005-01-24 11:36:51 WARNING: terminating connection because of crash of another server process 2005-01-24 11:36:51 DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2005-01-24 11:36:51 HINT: In a moment you should be able to reconnect to the database and repeat your command. 2005-01-24 11:36:51 LOG: all server processes terminated; reinitializing 2005-01-24 11:36:51 LOG: database system was interrupted at 2005-01-24 11:35:05 Westeuropäische Normalzeit 2005-01-24 11:36:51 LOG: checkpoint record is at 0/CD25468 2005-01-24 11:36:51 LOG: redo record is at 0/CD25468; undo record is at 0/0; shutdown TRUE 2005-01-24 11:36:51 LOG: next transaction ID: 885303; next OID: 110250 2005-01-24 11:36:51 LOG: database system was not properly shut down; automatic recovery in progress 2005-01-24 11:36:51 LOG: record with zero length at 0/CD254A8 2005-01-24 11:36:51 LOG: redo is not required 2005-01-24 11:36:51 LOG: database system is ready Yeah, I know, it is not a very orthodox SQL. Yes, I can split up that ids to make it work. BUT... it is not nice that the process crashes. And it is rather worse, that all the camarad processes are terminated. In German we call that "sippenhaft" :))) [terminating any other active server processes] (I know, Sun Tzu recommends killing all generals when some soldiers do not operate properly... but hey, that is THOUSENDS of years ago :) ) There should be some try ... except be around or other precautions. (especially since SQL often gets created automatically) Harald
"Harald Massa" <ghum@gmx.net> writes:
> I issue the following SQL:
> delete from bcachekunde where id_p in (1,2,3,4,6,10,12,13,14,16, [....10740
> more ids...] 90041492, 90721705)
> with the result:
> 2005-01-24 11:35:04 LOG: server process (PID 3776) exited with unexpected
> status 128
Anyone know what status 128 might mean on Windows?
On my non-Windows machine this works fine, but increasing the number of
ID values to 17000 or so causes
ERROR: stack depth limit exceeded
HINT: Increase the configuration parameter "max_stack_depth".
I'm suspicious that either the stack depth detection code doesn't work
at all on Windows, or the default setting of max_stack_depth is in fact
more than the default physical stack size on Windows. Can anyone check?
regards, tom lane
> I'm suspicious that either the stack depth detection code doesn't work
> at all on Windows, or the default setting of max_stack_depth is in
fact
> more than the default physical stack size on Windows. Can anyone
check?
>
> regards, tom lane
C:\msys\1.0\local\pgsql\bin>"c:\Program Files\Microsoft Visual
Studio\VC98\Bin"\dumpbin /headers pos
tmaster.exe | grep stack
200000 size of stack reserve
1000 size of stack commit
> > I'm suspicious that either the stack depth detection code doesn't work > > at all on Windows, or the default setting of max_stack_depth is in > fact > > more than the default physical stack size on Windows. Can anyone > check? > > > > regards, tom lane > > C:\msys\1.0\local\pgsql\bin>"c:\Program Files\Microsoft Visual > Studio\VC98\Bin"\dumpbin /headers pos > tmaster.exe | grep stack > 200000 size of stack reserve > 1000 size of stack commit clarification: those #s are in hex. So that's about 2mb of stack as I read it. Merlin
"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> C:\msys\1.0\local\pgsql\bin>"c:\Program Files\Microsoft Visual
> Studio\VC98\Bin"\dumpbin /headers pos
> tmaster.exe | grep stack
> 200000 size of stack reserve
> 1000 size of stack commit
Hmm, are those in bytes? The default value of max_stack_depth is 2048
(kilobytes), which really means that we are assuming a physical stack
limit of 3Mb or more, because the max_stack_depth check isn't exact.
Can we increase that setting during the Windows build? I'd like to see
it set to 4Mb if possible.
BTW, what's the difference between "stack reserve" and "stack commit"?
regards, tom lane
> Hmm, are those in bytes? The default value of max_stack_depth is 2048 > (kilobytes), which really means that we are assuming a physical stack > limit of 3Mb or more, because the max_stack_depth check isn't exact. Hex, so about 2 mb of stack by default. > Can we increase that setting during the Windows build? I'd like to see > it set to 4Mb if possible. Yes. Also, if the poster happens to have visual studio installed, there is a utility called editbin that allows manually changing a stack (be sure to change postgres.exe and postmaster.exe at the least). The linker switch to do this is: The Stack Allocations (/STACK:reserve[,commit]) option sets the size of the stack in bytes. > BTW, what's the difference between "stack reserve" and "stack commit"? Just guessing, but this may have something to do with controlling how much of the stack can be swapped out to virtual memory. I'd suggest leaving it alone. Merlin
> The linker switch to do this is: > > The Stack Allocations (/STACK:reserve[,commit]) option sets the size of > the stack in bytes. > arrgh, that's for the Microsoft compiler, not gcc. So I don't know. However the editbin trick should still work. Merlin
Ok: (sorry to spam about this) the gcc linker option to change the stack appears to be --stack... e.g. --stack=0x2000000 Checked, the default stack in mingw is 2mb. It was intentionally set there, have no idea why. For some info, check this link: http://64.233.161.104/search?q=cache:W3_gbgl0AlgJ:sourceforge.net/mailar chive/forum.php%3Fforum_id%3D5123%26max_rows%3D25%26style%3Dnested%26vie wmonth%3D200208+change+stack+size+mingw&hl=en Merlin
>> Can we increase that setting during the Windows build? I'd like to >see >> it set to 4Mb if possible. > >Yes. Also, if the poster happens to have visual studio >installed, there >is a utility called editbin that allows manually changing a stack (be >sure to change postgres.exe and postmaster.exe at the least). > >The linker switch to do this is: > >The Stack Allocations (/STACK:reserve[,commit]) option sets the size of >the stack in bytes. > On MingW, you add: -Wl,--stack=4194304 to get 4Mb stack. At least the value increases in the header. And I can confirm that doing this fixes the problem and turns it into the error message about stack size. (But won't things crash again if I increase that one? Or will it increase the stack manually then?) Patch attached. >> BTW, what's the difference between "stack reserve" and >"stack commit"? > >Just guessing, but this may have something to do with controlling how >much of the stack can be swapped out to virtual memory. I'd suggest >leaving it alone. IIRC: Stack Reserve = Amount of virtual memory used for stack Stack Commit = Initial/minimal amount of physical memory used for stack //Magnus
Attachment
"Magnus Hagander" <mha@sollentuna.net> writes:
> On MingW, you add:
> -Wl,--stack=4194304
> to get 4Mb stack. At least the value increases in the header.
> And I can confirm that doing this fixes the problem and turns it into
> the error message about stack size. (But won't things crash again if I
> increase that one?
Sure. The point is that Postgres doesn't have any way to determine the
allowed stack size, so you have to tell it. Our alternatives here are
(a) to change the default max_stack_depth for Windows, or (b) to make the
platform adhere to the default expectation. (b) looks easier.
> Patch attached.
Will apply.
regards, tom lane
>> On MingW, you add: >> -Wl,--stack=4194304 >> to get 4Mb stack. At least the value increases in the header. > >> And I can confirm that doing this fixes the problem and turns it into >> the error message about stack size. (But won't things crash >again if I >> increase that one? > >Sure. The point is that Postgres doesn't have any way to determine the >allowed stack size, so you have to tell it. Our alternatives here are >(a) to change the default max_stack_depth for Windows, or (b) >to make the >platform adhere to the default expectation. (b) looks easier. Agreed. Just wanted to be sure. If someone needs it, they can always tweak that number int he Makefile and rebuild from source. //Magnus
Does anyone have an idea whether we can/should attach the -Wl,--stack
option to the Cygwin link command as well?
regards, tom lane
>Does anyone have an idea whether we can/should attach the -Wl,--stack >option to the Cygwin link command as well? Not sure - don't have a cygwin env myself. I did some googling, and found a page that stated cygwin had a default stacksize of 32Mb. But then that page said MingW also had that, and if one has changed, there's a fair chance both have. //Magnus
"Magnus Hagander" <mha@sollentuna.net> writes:
> On MingW, you add:
> -Wl,--stack=4194304
> to get 4Mb stack. At least the value increases in the header.
> And I can confirm that doing this fixes the problem and turns it into
> the error message about stack size. (But won't things crash again if I
> increase that one? Or will it increase the stack manually then?)
> Patch attached.
Applied. Some googling confirmed that cygwin uses this switch too,
so I added it in that makefile branch as well. The references I found
suggested a slightly different syntax: -Wl,--stack,4194304. It's
likely that the "=" variant works fine on cygwin too, but just for
paranoia's sake I followed the references.
regards, tom lane