Postgres Pro
Downloads
Home   >   mailing lists

Thread: lock problem

lock problem

From
marcelo Cortez
Date:
hi all

 I have a lock problem, one select query freeze my
application, the query in question is on complex
select  , with many join's, but select all of them.
 The select * from pg_catalog.pg_locks show many rows
while the query freeze my aplication.
the aplication use ODBC .
any ideas?.
any help wellcomed
 best
  MDC

pd: what query show pg_locks ? , have a view ?
sorry for my poor english and thanks for your time.


__________________________________________________
Correo Yahoo!
Espacio para todos tus mensajes, antivirus y antispam ¡gratis!
¡Abrí tu cuenta ya! - http://correo.yahoo.com.ar

Re: lock problem

From
Richard Huxton
Date:
marcelo Cortez wrote:
> hi all
>
>  I have a lock problem, one select query freeze my
> application, the query in question is on complex
> select

An ordinary select doesn't take any locks. What is the actual query
causing this?

--
   Richard Huxton
   Archonet Ltd

Re: lock problem

From
marcelo Cortez
Date:
Hi Richard , folks

the query is:

 SELECT c.actuacion_car AS c_actuacion,
 c.comentario1 || ' ' || c.comentario2 || ' ' ||
c.comentario3 AS c_comentario
FROM caratult AS c INNER JOIN extractt AS t1 ON
(c.id_extracto_car = t1.id_extracto)
INNER JOIN repartit AS r1 ON (c.id_reparticion_uc =
r1.id_reparticion)
INNER JOIN repartit AS r2 ON (t1.id_reparticion_ext =
r2.id_reparticion)
INNER JOIN repartit AS r3 ON (c.id_reparticion_car =
r3.id_reparticion)
LEFT JOIN carcallt AS l ON (c.actuacion_car =
l.actuacion_cal)
LEFT JOIN callest AS ll9 ON (l.id_calle_cal =
ll9.id_calle)
LEFT JOIN callest AS ll10 ON (l.id_calle1_cal =
ll10.id_calle)
LEFT JOIN callest AS ll11 ON (l.id_calle2_cal =
ll11.id_calle)
LEFT JOIN callest AS ll12 ON (l.id_esquina_cal =
ll12.id_calle)
LEFT JOIN pasest AS p ON (c.actuacion_car =
p.act_principal)
LEFT JOIN repartit AS r7 ON (p.id_repart_origen =
r7.id_reparticion)
LEFT JOIN repartit AS r8 ON (p.id_repart_destino =
r8.id_reparticion)
LEFT JOIN repartit AS r9 ON (p.id_reparticion_u =
r9.id_reparticion)
WHERE anio(c.actuacion_car)::integer = '2005'
::integer  AND
DATE_PART('year', fecha_inicio_real) ::integer = 2005
AND
upper(repart(c.actuacion_car)) = upper('AGJ') LIMIT
101

without table definition etc, you see are selects
only.
during this  query, select * from pg_catalog.pg_locks
show many rows with exclusive locks , and other
activities or user's be freezed,
 best regards.
  mdc

pd: other information

"PostgreSQL 7.4.3 on i386-pc-linux-gnu, compiled by
GCC 2.95.4"

kernel: Linux serverf 2.4.26-1-686-smp #1 SMP Fri Aug
20 19:39:10 CEST 2004 i686 unknown

Distro: Debian



 --- Richard Huxton <dev@archonet.com> escribió:

> marcelo Cortez wrote:
> > hi all
> >
> >  I have a lock problem, one select query freeze my
> > application, the query in question is on complex
> > select
>
> An ordinary select doesn't take any locks. What is
> the actual query
> causing this?
>
> --
>    Richard Huxton
>    Archonet Ltd
>








___________________________________________________________
1GB gratis, Antivirus y Antispam
Correo Yahoo!, el mejor correo web del mundo
http://correo.yahoo.com.ar

Re: lock problem

From
Richard Huxton
Date:
marcelo Cortez wrote:
> Hi Richard , folks
>
> the query is:
>
>  SELECT c.actuacion_car AS c_actuacion,
>  c.comentario1 || ' ' || c.comentario2 || ' ' ||
> c.comentario3 AS c_comentario
> FROM caratult AS c INNER JOIN extractt AS t1 ON
...etc


I'm not seeing anything here that should take locks, although it's late
where I am.

> without table definition etc, you see are selects
> only.
> during this  query, select * from pg_catalog.pg_locks
> show many rows with exclusive locks , and other
> activities or user's be freezed,

Are you *sure* that it's this query? Could you show some of the rows
from pg_locks along with corresponding rows from pg_stat_activity?

--
   Richard Huxton
   Archonet Ltd

Re: lock problem

From
marcelo Cortez
Date:
 --- Richard Huxton <dev@archonet.com> escribió:

> marcelo Cortez wrote:
> > Hi Richard , folks
> >
> > the query is:
> >
> >  SELECT c.actuacion_car AS c_actuacion,
> >  c.comentario1 || ' ' || c.comentario2 || ' ' ||
> > c.comentario3 AS c_comentario
> > FROM caratult AS c INNER JOIN extractt AS t1 ON
> ...etc
>
>
> I'm not seeing anything here that should take locks,
> although it's late
> where I am.
>
> > without table definition etc, you see are selects
> > only.
> > during this  query, select * from
> pg_catalog.pg_locks
> > show many rows with exclusive locks , and other
> > activities or user's be freezed,
>
> Are you *sure* that it's this query? Could you show
> some of the rows
> from pg_locks along with corresponding rows from
> pg_stat_activity?
>
> --
>    Richard Huxton
>    Archonet Ltd
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map
> settings
>


__________________________________________________
Correo Yahoo!
Espacio para todos tus mensajes, antivirus y antispam ¡gratis!
¡Abrí tu cuenta ya! - http://correo.yahoo.com.ar