Обсуждение: postgres database crashed
We have a following table xyz( id int , fname varchar(50), img image)<br />where image is a data type we have created similarto lo.<br /><br />so when we executed an insert query on the following table :-<br /><br />insert into xyz VALUES(2541, '/home/ravi/jdbc/2_down/76.jpg', '76.jpg');<br /><br />It works well for nearly 2500 images but from there onwardswe obtained the following error and the database crashes.<br /><br />this is the bt we obtained on using gdb withthe postgres process<br /><br />#0 0x00313bbc in memcpy () from /lib/libc.so.6<br />#1 0x0819496d in datumCopy ()<br/>#2 0x0812d202 in copyObject ()<br />#3 0x08151c49 in eval_const_expressions_mutator ()<br />#4 0x08150c2f in expression_tree_mutator()<br />#5 0x08151c49 in eval_const_expressions_mutator ()<br />#6 0x08150ece in expression_tree_mutator()<br />#7 0x08151c49 in eval_const_expressions_mutator ()<br />#8 0x08152818 in eval_const_expressions()<br />#9 0x081487d8 in preprocess_expression ()<br />#10 0x08149a04 in subquery_planner ()<br />#110x08149e56 in planner ()<br />#12 0x0817c25c in pg_plan_query ()<br />#13 0x0817c8d2 in pg_plan_queries ()<br />#140x0817e135 in PostgresMain ()<br />#15 0x0815b6e5 in ServerLoop ()<br />#16 0x0815c459 in PostmasterMain ()<br />#170x08128f48 in main ()<br /><br /><br />this is the LOG obtained<br /><br />LOG: server process (PID 2499) was terminatedby signal 11<br />(gdb) LOG: terminating any other active server processes<br />LOG: all server processes terminated;reinitializing<br />LOG: database system was interrupted at 2006-10-10 23:40:05 IST<br />LOG: checkpoint recordis at 0/14B37B98<br />LOG: redo record is at 0/14B37B98; undo record is at 0/0; shutdown FALSE<br />LOG: next transactionID: 210546; next OID: 851968; next MultiXactId: 1<br />LOG: database system was not properly shut down; automaticrecovery in progress<br />FATAL: the database system is starting up<br />LOG: record with zero length at 0/14B37BD8<br/>LOG: redo is not required<br />LOG: database system is ready<br />LOG: transaction ID wrap limit is 1073952152,limited by database "benchmark"<br /><br /><br />Can somebody suggest us what might be the cause of error andwhat can we do to resolve it ?<br /><p> __________________________________________________<br />Do You Yahoo!?<br />Tiredof spam? Yahoo! Mail has the best spam protection around <br />http://mail.yahoo.com
Ashish Goel <postgres_help@yahoo.com> writes:
> We have a following table xyz( id int , fname varchar(50), img image)
> where image is a data type we have created similar to lo.
> ...
> Can somebody suggest us what might be the cause of error and what can we do to resolve it ?
Incorrect code in your custom datatype, almost certainly. Check
computations of memory size allocations, for example. Test it in
a backend compiled with --enable-cassert.
regards, tom lane
But the same code worked when I inserted around 2500 images in the database. After that it started crashing. So , I don'tthink it's because of error in the code. Can u suggest some other possible reasons and also why is it crashing at callto memcpy(). I have also checked the memory allocations , and i don't find problem there.<br /><br /><br /><br /><b><i>TomLane <tgl@sss.pgh.pa.us></i></b> wrote:<blockquote class="replbq" style="border-left: 2px solid rgb(16,16, 255); margin-left: 5px; padding-left: 5px;"> Ashish Goel writes:<br />> We have a following table xyz( idint , fname varchar(50), img image)<br />> where image is a data type we have created similar to lo.<br />> ...<br/>> Can somebody suggest us what might be the cause of error and what can we do to resolve it ?<br /><br />Incorrectcode in your custom datatype, almost certainly. Check<br />computations of memory size allocations, for example.Test it in<br />a backend compiled with --enable-cassert.<br /><br /> regards, tom lane<br /></blockquote><br /><p><hrsize="1" />How low will we go? Check out Yahoo! Messengers low <a href="http://us.rd.yahoo.com/mail_us/taglines/postman8/*http://us.rd.yahoo.com/evt=39663/*http://voice.yahoo.com"> PC-to-Phonecall rates.</a>
Did you follow Tom's suggestion of trying with a postgres configured with --enable-cassert ? cheers andrew Ashish Goel wrote: > But the same code worked when I inserted around 2500 images in the > database. After that it started crashing. So , I don't think it's because > of error in the code. Can u suggest some other possible reasons and also > why is it crashing at call to memcpy(). I have also checked the memory > allocations , and i don't find problem there. > > > > Tom Lane <tgl@sss.pgh.pa.us> wrote: Ashish Goel > writes: >> We have a following table xyz( id int , fname varchar(50), img image) >> where image is a data type we have created similar to lo. >> ... >> Can somebody suggest us what might be the cause of error and what can we >> do to resolve it ? > > Incorrect code in your custom datatype, almost certainly. Check > computations of memory size allocations, for example. Test it in > a backend compiled with --enable-cassert. >
But the same code worked when I inserted around 2500 images in the database. After that it started crashing. So , I don't think it's because of error in the code. Can u suggest some other possible reasons and also why is it crashing at call to memcpy().
Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail.
Tom Lane <tgl@sss.pgh.pa.us> wrote:Ashish Goel writes:
> We have a following table xyz( id int , fname varchar(50), img image)
> where image is a data type we have created similar to lo.
> ...
> Can somebody suggest us what might be the cause of error and what can we do to resolve it ?
Incorrect code in your custom datatype, almost certainly. Check
computations of memory size allocations, for example. Test it in
a backend compiled with --enable-cassert.
regards, tom lane
All-new Yahoo! Mail - Fire up a more powerful email and get things done faster.
Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail.
Hi, Ashish, Ashish Goel wrote: > But the same code worked when I inserted around 2500 images in the > database. After that it started crashing. Testing can never prove that there are no bugs. It's like the proof that all odd numbers above 1 are prime: 3 is prime, 5 is prime, 7 is prime, so I conclude that all odd numbers above 1 are prime. So , I don't think it's > because of error in the code. Can u suggest some other possible reasons > and also why is it crashing at call to memcpy(). - broken hardware - compiler bugs - bugs in PostgreSQL But without having seen your code, I tend to assume that it's something like a wrong length flag in some corner case in your code. ... Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org
Hi all,<br /><br />I am sorry but I forgot to mention that in the database schema we are maintaining referrences to the maintable xyz(int id, img image, fname varhcar(50))<br /><br />There are around 14 tables referrencing this table . The referrencesare being made to the column id.<br /><br />The code works well if we don't maintain the referrences but whenwe include the referrences then the database crashes somewhere between 2500-3000 transactions.<br /><br />So could thisproblem be due to the multiple referrences being made to the same table ?<br /><br /><b><i>Markus Schaber <schabi@logix-tt.com></i></b>wrote:<blockquote class="replbq" style="border-left: 2px solid rgb(16, 16, 255); margin-left:5px; padding-left: 5px;"> Hi, Ashish,<br /><br />Ashish Goel wrote:<br />> But the same code worked when Iinserted around 2500 images in the<br />> database. After that it started crashing.<br /><br />Testing can never provethat there are no bugs.<br /><br />It's like the proof that all odd numbers above 1 are prime:<br /><br />3 is prime,5 is prime, 7 is prime, so I conclude that all odd numbers<br />above 1 are prime.<br /><br /><br /> So , I don't thinkit's<br />> because of error in the code. Can u suggest some other possible reasons<br />> and also why is itcrashing at call to memcpy().<br /><br />- broken hardware<br />- compiler bugs<br />- bugs in PostgreSQL<br /><br />Butwithout having seen your code, I tend to assume that it's something<br />like a wrong length flag in some corner casein your code.<br />...<br /><br />Markus<br />-- <br />Markus Schaber | Logical Tracking&Tracing International AG<br/>Dipl. Inf. | Software Development GIS<br /><br />Fight against software patents in Europe! www.ffii.org<br />www.nosoftwarepatents.org<br/><br /></blockquote><br /><p><hr size="1" />Get your own <a href=" http://us.rd.yahoo.com/evt=43290/*http://smallbusiness.yahoo.com/domains">webaddress for just $1.99/1st yr</a>. We'll help.<a href="http://us.rd.yahoo.com/evt=41244/*http://smallbusiness.yahoo.com/">Yahoo! Small Business</a>.
Hi, Ashish, Ashish Goel wrote: > I am sorry but I forgot to mention that in the database schema we are > maintaining referrences to the main table xyz(int id, img image, fname > varhcar(50)) > > There are around 14 tables referrencing this table . The referrences are > being made to the column id. > > The code works well if we don't maintain the referrences but when we > include the referrences then the database crashes somewhere between > 2500-3000 transactions. > > So could this problem be due to the multiple referrences being made to > the same table ? I doubt so. Foreign key references are among the basics of SQL, they're pretty well tested. Could you try to replace your image type e. G. with bytea for your test purposes, and see, whether it crashes, too? HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org
Markus Schaber <schabi@logix-tt.com> writes:
> Ashish Goel wrote:
>> The code works well if we don't maintain the referrences but when we
>> include the referrences then the database crashes somewhere between
>> 2500-3000 transactions.
>>
>> So could this problem be due to the multiple referrences being made to
>> the same table ?
> I doubt so.
> Foreign key references are among the basics of SQL, they're pretty well
> tested.
I'm betting that this is a memory-clobber problem in that custom datatype.
The reason the symptoms come and go when varying unrelated stuff is that
it might be chancing to clobber momentarily-unused memory rather than
live data structures. (In the above example, the queue of pending FK
trigger events is likely what got clobbered.)
regards, tom lane