Thread: 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.

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

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