Обсуждение: POSTGRE CRASH AND CURRVAL PROBLEM HELP!
Please, i need help desperately.
Im running postgresql 8.1.9 on windows 2003 server standard edition service pack 2. intel pentium 4 3.2 - 1 gb ram
I have 5 databases in this server and they all have action all day ( inserts, selects etc )
i have 2 problems.
First, postgre crashes many times in the day and we have to restart the service to keep working. i created a log using the database conection class with php. when a query makes an error it logs in a txt file. i will show here the errors that im getting and i dont know what they mean.
2007-08-16 13:49:43||/labrsangioro/forms/recepcao_temp.php||ERROR: could not open relation 1663/1491040/1555634: Invalid argument
||update tb_atendimento set total_faturamento='33.6', tipo_desconto='%', total_desconto='30' where cod_atendimento=2683
2007-08-16 14:12:36||/labrsangioro/forms/recepcao_temp.php||PANIC: could not open file "pg_xlog/000000010000000000000046" (log file 0, segment 70): Invalid argument
||Update tb_atendimento set valor_subtotal='17', alteradopor='18', alteradoem='2007-08-16 14:12:35', ultimaacao='del_ex' where cod_atendimento=465
2007-08-16 16:26:29||/labrsangioro/forms/resultado_temp.php||ERROR: could not open relation 1663/1491040/2689: Invalid argument
||SELECT * FROM tb_campo_exame WHERE status_campo AND tb_exame_cod_exame=631 AND tipo <> 2 AND status_campo order by ordem
2007-08-17 8:05:40||/labrsangioro/forms/recepcao_temp.php||ERROR: could not open relation 1663/1491040/1555635: Invalid argument
||SELECT * FROM tb_atendimento WHERE status_atendimento AND tb_paciente_cod_paciente=2843 AND tb_local_cod_local=1 order by data_atendimento desc
2007-08-17 8:26:40||/labrsangioro/forms/recepcao_temp.php||ERROR: could not open relation 1663/1491040/2601: Invalid argument
||update tb_paciente set matricula='BP0010005809', endereco='', sexo='M', diabetico='Sim' where cod_paciente=2848
2007-08-17 8:27:00||/labrsangioro/forms/recepcao_temp.php||ERROR: could not open relation 1663/1491040/2601: Invalid argument
i tryed to reindex all the databases but the error keep ocurring in all the databases.
as you can see im getting the error: could not open relation xxxx/xxxxxx/xxxx: Invalid argument in queries that should execute normaly.
how i can solve this problem?
This maybe the cause that postgre crash or these errors ocur because postgre crash and cant complete the query?
what whould cause postgre to crash in my server?
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Now the second problem.
i always used nextval to insert autoincrement values, and currval to get the inserted value.
after i backup one of the databases and restore ( just it, backup and restore in the same server ) the curval function stoped to work and i get the following message:
2007-08-19 17:18:43||/labrsangioro/forms/cad_combo_temp.php||ERROR: currval of sequence "cod_combo_seq" is not yet defined in this session
||SELECT * from tb_dados_combo WHERE cod_combo=currval(' public.cod_combo_seq') AND desc_opcao='ss'
Notice that it always worked, and i aways call nextval before call currval. Here is a sample of my code:
$query="INSERT INTO tb_atendimento VALUES(nextval(' public.tb_atendimento_cod_atendimento_seq'),$convenio,$local,$cod_medico,$cod_paciente,'$obs_atendimento','0','$hoje_1','$ano_mes','$valor_total','$valor_subtotal','$urgencia','0','0','1','$horario_urg','$paciente','','0','$hoje_1','0','0','$total_desconto','$tipo_desconto',$cod_tabela,$Usuario,'$hoje_1',NULL,NULL,'$acao','$domicilio','f','1','$pago',NULL,NULL,'','','','',NULL,'','$data_entrega','$observacao')";
$db->exec_query($query);
$query="SELECT currval('public.tb_atendimento_cod_atendimento_seq') as cod_atend FROM tb_atendimento";
$db->exec_query($query);
$currval_atend = $db->Regs();
$currval_atend = $currval_atend[0]['cod_atend'];
so i have backedup and restored my 5 databases, in 2 of them curval stoped to work, and in the another 3, it work normal.
Im realy lost, someone help please.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Im running postgresql 8.1.9 on windows 2003 server standard edition service pack 2. intel pentium 4 3.2 - 1 gb ram
I have 5 databases in this server and they all have action all day ( inserts, selects etc )
i have 2 problems.
First, postgre crashes many times in the day and we have to restart the service to keep working. i created a log using the database conection class with php. when a query makes an error it logs in a txt file. i will show here the errors that im getting and i dont know what they mean.
2007-08-16 13:49:43||/labrsangioro/forms/recepcao_temp.php||ERROR: could not open relation 1663/1491040/1555634: Invalid argument
||update tb_atendimento set total_faturamento='33.6', tipo_desconto='%', total_desconto='30' where cod_atendimento=2683
2007-08-16 14:12:36||/labrsangioro/forms/recepcao_temp.php||PANIC: could not open file "pg_xlog/000000010000000000000046" (log file 0, segment 70): Invalid argument
||Update tb_atendimento set valor_subtotal='17', alteradopor='18', alteradoem='2007-08-16 14:12:35', ultimaacao='del_ex' where cod_atendimento=465
2007-08-16 16:26:29||/labrsangioro/forms/resultado_temp.php||ERROR: could not open relation 1663/1491040/2689: Invalid argument
||SELECT * FROM tb_campo_exame WHERE status_campo AND tb_exame_cod_exame=631 AND tipo <> 2 AND status_campo order by ordem
2007-08-17 8:05:40||/labrsangioro/forms/recepcao_temp.php||ERROR: could not open relation 1663/1491040/1555635: Invalid argument
||SELECT * FROM tb_atendimento WHERE status_atendimento AND tb_paciente_cod_paciente=2843 AND tb_local_cod_local=1 order by data_atendimento desc
2007-08-17 8:26:40||/labrsangioro/forms/recepcao_temp.php||ERROR: could not open relation 1663/1491040/2601: Invalid argument
||update tb_paciente set matricula='BP0010005809', endereco='', sexo='M', diabetico='Sim' where cod_paciente=2848
2007-08-17 8:27:00||/labrsangioro/forms/recepcao_temp.php||ERROR: could not open relation 1663/1491040/2601: Invalid argument
i tryed to reindex all the databases but the error keep ocurring in all the databases.
as you can see im getting the error: could not open relation xxxx/xxxxxx/xxxx: Invalid argument in queries that should execute normaly.
how i can solve this problem?
This maybe the cause that postgre crash or these errors ocur because postgre crash and cant complete the query?
what whould cause postgre to crash in my server?
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Now the second problem.
i always used nextval to insert autoincrement values, and currval to get the inserted value.
after i backup one of the databases and restore ( just it, backup and restore in the same server ) the curval function stoped to work and i get the following message:
2007-08-19 17:18:43||/labrsangioro/forms/cad_combo_temp.php||ERROR: currval of sequence "cod_combo_seq" is not yet defined in this session
||SELECT * from tb_dados_combo WHERE cod_combo=currval(' public.cod_combo_seq') AND desc_opcao='ss'
Notice that it always worked, and i aways call nextval before call currval. Here is a sample of my code:
$query="INSERT INTO tb_atendimento VALUES(nextval(' public.tb_atendimento_cod_atendimento_seq'),$convenio,$local,$cod_medico,$cod_paciente,'$obs_atendimento','0','$hoje_1','$ano_mes','$valor_total','$valor_subtotal','$urgencia','0','0','1','$horario_urg','$paciente','','0','$hoje_1','0','0','$total_desconto','$tipo_desconto',$cod_tabela,$Usuario,'$hoje_1',NULL,NULL,'$acao','$domicilio','f','1','$pago',NULL,NULL,'','','','',NULL,'','$data_entrega','$observacao')";
$db->exec_query($query);
$query="SELECT currval('public.tb_atendimento_cod_atendimento_seq') as cod_atend FROM tb_atendimento";
$db->exec_query($query);
$currval_atend = $db->Regs();
$currval_atend = $currval_atend[0]['cod_atend'];
so i have backedup and restored my 5 databases, in 2 of them curval stoped to work, and in the another 3, it work normal.
Im realy lost, someone help please.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
On 8/19/07, David Azevedo <davixz@gmail.com> wrote: > Please, i need help desperately. > > Im running postgresql 8.1.9 on windows 2003 server standard edition service > pack 2. intel pentium 4 3.2 - 1 gb ram > I have 5 databases in this server and they all have action all day ( > inserts, selects etc ) > > i have 2 problems. > > First, postgre crashes many times in the day and we have to restart the > service to keep working. i created a log using the database conection class > with php. when a query makes an error it logs in a txt file. i will show > here the errors that im getting and i dont know what they mean. On stable hardware with a stable OS, postgresql crashing once a year is too often. > > 2007-08-16 > 13:49:43||/labrsangioro/forms/recepcao_temp.php||ERROR: > could not open relation 1663/1491040/1555634: Invalid argument > ||update tb_atendimento set total_faturamento='33.6', tipo_desconto='%', > total_desconto='30' where cod_atendimento=2683 > 2007-08-16 This type of error is indicative of two possibilities. Either the hardware has issues, or the OS is doing something funny. On a unix machine I would generally suspect the hardware, but on Windows I tend to suspect some kind of anti-virus software holding a lock on your files when the database goes to access them. Turn off / remove / uninstall all anti-virus / firewall / email filter software on the box. However, it is quite likely that by now you have a corrupted data store and will need to restore from a backup for reliability. Oh, and buy more RAM for that box. 1 gig is REALLY small for a db server. :)
"David Azevedo" <davixz@gmail.com> writes: > 2007-08-16 13:49:43||/labrsangioro/forms/recepcao_temp.php||ERROR: could > not open relation 1663/1491040/1555634: Invalid argument > 2007-08-16 14:12:36||/labrsangioro/forms/recepcao_temp.php||PANIC: could > not open file "pg_xlog/000000010000000000000046" (log file 0, segment 70): > Invalid argument These look pretty bad. Can you confirm whether these files are really missing? Some people have reported strange errors not entirely unlike these being caused by virus firewalls which prevent Postgres from doing file operations it expects to be able to do. Can you disable any virus firewalls on this machine, at least temporarily or as far as local file operations are affected? > i always used nextval to insert autoincrement values, and currval to get the > inserted value. > > after i backup one of the databases and restore ( just it, backup and > restore in the same server ) the curval function stoped to work and i get > the following message: Is this the same server as is getting the errors above? If so then following an error it will try to reconnect to the server which will be a new connection and not have the sequence value set. Are there any other errors in your logs? If there were any other errors earlier it might have prevented your nextval() from getting called. How did you back it up and restore it? Are you referring to pg_dump/pg_restore or some system backup? Can you do \ds on the working and non-working database and compare the results? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
On 8/19/07, David Azevedo <davixz@gmail.com> wrote: > Please, i need help desperately. > > Im running postgresql 8.1.9 on windows 2003 server standard edition service > pack 2. intel pentium 4 3.2 - 1 gb ram > I have 5 databases in this server and they all have action all day ( > inserts, selects etc ) > > i have 2 problems. > > First, postgre crashes many times in the day and we have to restart the > service to keep working. i created a log using the database conection class > with php. when a query makes an error it logs in a txt file. i will show > here the errors that im getting and i dont know what they mean. > > 2007-08-16 > 13:49:43||/labrsangioro/forms/recepcao_temp.php||ERROR: > could not open relation 1663/1491040/1555634: Invalid argument > ||update tb_atendimento set total_faturamento='33.6', tipo_desconto='%', > total_desconto='30' where cod_atendimento=2683 This is coming from the operating system. What software have you installed besides the database? How much drive space do you have? Are you possibly running your WAL volume out of space? > Now the second problem. > > i always used nextval to insert autoincrement values, and currval to get the > inserted value. test this from the psql shell and report back, showing the log. merlin