POSTGRE CRASH AND CURRVAL PROBLEM HELP!

From: "David Azevedo" <davixz(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: POSTGRE CRASH AND CURRVAL PROBLEM HELP!
Date: 2007-08-19 20:24:45
Message-ID: 628fea630708191324r1c72a827va51743a812fa97e8@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2007-08-19 21:45:27 Re: POSTGRE CRASH AND CURRVAL PROBLEM HELP!
Previous Message Michael Glaesemann 2007-08-19 20:22:33 Re: entry log