Search Postgresql Archives

Re: 8.2.4 selects make applications wait indefinitely

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux