Re: 8.2.4 selects make applications wait indefinitely - Mailing list pgsql-general
From | Erik Jones |
---|---|
Subject | Re: 8.2.4 selects make applications wait indefinitely |
Date | |
Msg-id | A938D058-54CD-4AB2-9654-74FEBBDB270D@myemma.com Whole thread Raw |
In response to | 8.2.4 selects make applications wait indefinitely ("Carlos H. Reimer" <carlos.reimer@opendb.com.br>) |
Responses |
RES: 8.2.4 selects make applications wait indefinitely
("Carlos H. Reimer" <carlos.reimer@opendb.com.br>)
|
List | pgsql-general |
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@myemma.com 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
pgsql-general by date: