On Oct 10, 2007, at 10:09 PM, Carlos H. Reimer wrote:
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
Are all of these remote connections from the same machine? Did you
upgrade your client postgres libraries on your remote machine(s) as
well?
Erik Jones
Software Developer | Emma®
erik@xxxxxxxxxx
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
