Re: 8.2.4 selects make applications wait indefinitely

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(at)myemma(dot)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