Обсуждение: lock problem
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
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
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
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
--- 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