DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=EJi79PknPFtoUbRkTNsZP1F3Z+IrjtlJ86tpv7odvbTxtGZQuoWde9vDGzgwBxOeLZsf+SS1Tg5RqC93oTQfQNELxC9cQ+BQjJMhbiMj2OLyldxamIFWf2e1nRQS63lEb5//QtKwmHKDfHMeCjiy3FqM8gJ+zWQZKxjAXpQncdk=
Message-ID: <a97c770305092213483a8351f0@mail.gmail.com>
Date: Fri, 23 Sep 2005 02:18:22 +0530
From: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
Reply-To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
To: Kenneth Hutchinson <kenneth.hutchinson@mpfyieldstar.com>
Subject: Re: Issue with UPDATE statement on v8
Cc: pgsql-sql@postgresql.org
In-Reply-To: 
 <ED153B99D71F5B4ABE318CF841A5C3E602061528@RPIDALEXC004.corp.realpage.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: 
 <ED153B99D71F5B4ABE318CF841A5C3E602061528@RPIDALEXC004.corp.realpage.com>

On 9/23/05, Kenneth Hutchinson <kenneth.hutchinson@mpfyieldstar.com> wrote:
> Hello,
>
> I'm sorry if this has been sent to the community multiple times.  I am
> not able to determine whether my posts have gotten through.  If you have
> rec'd this multiple times, please let me know.
>
> We have recently migrated to Postgres 8 (not sure of exactly which
> build).  We have noticed that a few functions that were working
> previously are no longer behaving as expected.  One function in
> particular is giving me a strange result.
>
> The function giving us the problem is much more complicated, but for
> simplicity I've included one that is easier to read and results in the
> same behavior.
>
> UPDATE t_summary
> SET        availability =3D 7
> WHERE  oid =3D 28245084
>
> When this query is executed (within a function or without) the database
> will simply hang.

see if the update statement is being blocked by some other statement
by running following SQL stmt from another session while the first session =
is
hung

SELECT h.pid AS blocker, w.pid AS blockee
   FROM ONLY pg_locks h, ONLY pg_locks w
  WHERE h.granted AND NOT w.granted AND (h.relation =3D w.relation AND
h."database" =3D w."database" OR h."transaction" =3D w."transaction");

(sql above was posted by mr. Tom Lane in a particular reply)

But You have to enable command string in statictics part of postgresql.conf
file to know which pid corresponds to which sql.

http://www.postgresql.org/docs/8.0/interactive/monitoring-stats.html#MONITO=
RING-STATS-SETUP

Hope it helps

regds
mallah.


If the UPDATE is turned into a SELECT, the query
> works just fine.  For some reason, the UPDATE is just not working.  This
> same function/query works fines in Postgres 7.2.
>
> The schema for the targeted table is shown below.
>
> CREATE TABLE t_summary (
>     id                              varchar(20) NULL,
>     availability                 int4 NULL DEFAULT 0,
> )
>
> Has anyone else experienced a similar issue?  If more information is
> needed to determine the problem, please let me know.  I've trimmed down
> the function's query and table's schema for this posting.
>
> Thanks in advance!
>
> kh
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>