pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedundefined relations in pg_locks
5+ messages / 3 participants
[nested] [flat]
* undefined relations in pg_locks
@ 2008-02-06 13:36 Sabin Coanda <sabin.coanda@deuromedia.ro>
2008-02-06 14:01 ` Re: undefined relations in pg_locks Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 1 reply; 5+ messages in thread
From: Sabin Coanda @ 2008-02-06 13:36 UTC (permalink / raw)
To: pgsql-sql
Hi there,
I have a procedure where a dead-lock occurs, and I'm trying to find the
tables involved in the lock.
Unfortunatelly, I don't find the related objects of the oids of "relation"
field. Also all the fields "classid" and "objid" are null.
May I suppose there were references to temporary objects ?
However, how cand I get the related objects involved in this lock ?
TIA,
Sabin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: undefined relations in pg_locks
2008-02-06 13:36 undefined relations in pg_locks Sabin Coanda <sabin.coanda@deuromedia.ro>
@ 2008-02-06 14:01 ` Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 0 replies; 5+ messages in thread
From: Guillaume Lelarge @ 2008-02-06 14:01 UTC (permalink / raw)
To: Sabin Coanda <sabin.coanda@deuromedia.ro>; +Cc: pgsql-sql
Sabin Coanda wrote:
> Hi there,
>
> I have a procedure where a dead-lock occurs, and I'm trying to find the
> tables involved in the lock.
>
> Unfortunatelly, I don't find the related objects of the oids of "relation"
> field. Also all the fields "classid" and "objid" are null.
>
> May I suppose there were references to temporary objects ?
> However, how cand I get the related objects involved in this lock ?
>
Maybe they're not objects of the database you're connected to. You need
to check the "database" column of pg_locks.
Regards.
--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* undefined relations in pg_locks
@ 2008-04-07 08:56 Sabin Coanda <sabin.coanda@deuromedia.ro>
2008-04-07 14:03 ` Re: undefined relations in pg_locks Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Sabin Coanda @ 2008-04-07 08:56 UTC (permalink / raw)
To: pgsql-sql
Hi there,
Sorry I repeat a problem which concerned me since two months, but I got no
answer. It's not clear for me whether it is trivial or without a solution. I
promisse not to repeat it in the future if I will give no answer this time
too.
So, I'm working with "PostgreSQL 8.2.3 on i686-redhat-linux-gnu, compiled by
GCC gcc (GCC) 4.0.2 20051125 (Red Hat 4.0.2-8)";
I have a procedure where a dead-lock occurs, and I'm trying to find the
tables involved in the lock.
A little description of how my procedure works maybe helps. So I have a loop
where I call a second procedure with exception trapping. At its turn, it
calls a third procedure. I guess an exception may occur in the third
procedure, where I use a temporary table. When an exception occurs, I guess
my temporary table table is not dropped, and this may lock the process when
another call try to create that temporary table again. Could be this
scenario what there happens to me ?
Unfortunatelly, I don't find the related objects of the oids of "relation"
field. Also all the fields "classid" and "objid" are null.
May I suppose there were references to temporary tables?
However, how cand I get the related objects involved in this lock, by other
way than analyse deeply in the code ?
TIA,
Sabin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: undefined relations in pg_locks
2008-04-07 08:56 undefined relations in pg_locks Sabin Coanda <sabin.coanda@deuromedia.ro>
@ 2008-04-07 14:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-04-09 07:06 ` Re: undefined relations in pg_locks Sabin Coanda <sabin.coanda@deuromedia.ro>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2008-04-07 14:03 UTC (permalink / raw)
To: Sabin Coanda <sabin.coanda@deuromedia.ro>; +Cc: pgsql-sql
"Sabin Coanda" <sabin.coanda@deuromedia.ro> writes:
> Unfortunatelly, I don't find the related objects of the oids of "relation"
> field. Also all the fields "classid" and "objid" are null.
> May I suppose there were references to temporary tables?
Are you sure they are in the same database you are working in? If the
locktype is "relation" and the database column matches your database's
OID, the relation column definitely ought to match some OID in pg_class.
It is possible that you can't see the pg_class row because the relation
was created in a transaction that hasn't committed yet --- but such an
entry could never be a reason for a deadlock, because no other
transaction could be trying to lock it.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: undefined relations in pg_locks
2008-04-07 08:56 undefined relations in pg_locks Sabin Coanda <sabin.coanda@deuromedia.ro>
2008-04-07 14:03 ` Re: undefined relations in pg_locks Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-04-09 07:06 ` Sabin Coanda <sabin.coanda@deuromedia.ro>
0 siblings, 0 replies; 5+ messages in thread
From: Sabin Coanda @ 2008-04-09 07:06 UTC (permalink / raw)
To: pgsql-sql
Oops! You are right. It was not a dead-lock.
I let the process to continue and after about one hour I got in the log the
following message 'cannot have more than 2^32-1 commands in a transaction'.
After some investigations in the code I found an infinite loop.
Thanks and sorry for wasting time,
Sabin
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2008-04-09 07:06 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-06 13:36 undefined relations in pg_locks Sabin Coanda <sabin.coanda@deuromedia.ro>
2008-02-06 14:01 ` Guillaume Lelarge <guillaume@lelarge.info>
2008-04-07 08:56 undefined relations in pg_locks Sabin Coanda <sabin.coanda@deuromedia.ro>
2008-04-07 14:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-04-09 07:06 ` Sabin Coanda <sabin.coanda@deuromedia.ro>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox