From: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
To: Kenneth Hutchinson <kenneth.hutchinson@mpfyieldstar.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Issue with UPDATE statement on v8
Date: Fri, 23 Sep 2005 02:18:22 +0530
Message-ID: <a97c770305092213483a8351f0@mail.gmail.com> (raw)
In-Reply-To: <ED153B99D71F5B4ABE318CF841A5C3E602061528@RPIDALEXC004.corp.realpage.com>
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 = 7
> WHERE oid = 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 = w.relation AND
h."database" = w."database" OR h."transaction" = 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#MONITORING-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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mallah.rajesh@gmail.com, kenneth.hutchinson@mpfyieldstar.com
Subject: Re: Issue with UPDATE statement on v8
In-Reply-To: <a97c770305092213483a8351f0@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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