Hi all,
We are facing some problems after the migration of our PostgreSQL 8.0 to the 8.2.4 version. The entire box runs under SUSE 10.3.
bd_sgp=# select version();
version
--------------------------------------------------------------------------------------------
PostgreSQL 8.2.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.2.1 (SUSE Linux)
The problem occurs when some SELECTs does not return any row and the application waits indefinitely. One of the SELECTs that locks is the "SELECT * FROM tb_produtos where codigo=5002;" although the query "SELECT codigo, descricao, embalagem, grupo, marca, unidade, grupo_cliente, codmarca, ativo, kg, codigo_deposito FROM tb_produtos where codigo=5002" runs fine. In summary, if you name all the table columns instead of using the * the query runs fine, otherwise it locks.
I've queried the pg_locks and no locks are there when the application was waiting.
pg_stat_activity reports that the SELECT was accepted by the database because the column "query_start" is updated although the pg_log (log_statement(all)) does not report it.
If the where clause is changed from "codigo=5002" to "codigo=3334" in the "SELECT *" statement, it runs fine.
The problem only occurs if we use remote clients, if the "SELECT * from tb_produtos where codigo=5002" is processed by a local(server) psql utility it runs fine too. When we try to run the query in a remote client using the windows psql it locks. The pg_stat_activity's current_query column reports "<idle>". We also tried ODBC clients and they lock too.
I've defined another table using the LIKE CREATE option and inserted all the 85 lines of tb_produtos into the new one and tried the "SELECT * FROM tb_produtostest where codigo=5002" against it. The query locks too.
Summary:
Local SELECT * FROM tb_produtos where codigo=5002 Runs
Remote SELECT * FROM tb_produtos where codigo=5002 locks
Remote SELECT * from tb_produtos where codigo=3334 runs
Remote SELECT list of all columns
FROM tb_produtos where codigo=5002 runs
I´ve noticed one strange local psql behaviour when we try to see the table definition of the tb_produtos table using the \d command. The column named "codigo_deposito" is returned as "ndices:deposito". Apparently is a psql issue because if we query the pg_attribute the column name appears correctly as "codigo_deposito".
I'm thinking to install the 8.2.5 to fix this issue. Am I thinking right?
Would appreciate any other suggestions.
Thank you very much in advance.
Reimer