pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIssue with UPDATE statement on v8
5+ messages / 4 participants
[nested] [flat]
* Issue with UPDATE statement on v8
@ 2005-09-22 16:22 Kenneth Hutchinson <kenneth.hutchinson@mpfyieldstar.com>
2005-09-23 13:30 ` Re: Issue with UPDATE statement on v8 Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 5+ messages in thread
From: Kenneth Hutchinson @ 2005-09-22 16:22 UTC (permalink / raw)
To: pgsql-sql
Hello,
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. 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
This message is intended only for the use of the individual(s) or entity to which it is addressed and may contain information that is privileged, confidential, and/or proprietary to RealPage and its affiliated companies. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, forwarding or copying of this communication is prohibited without the express permission of the sender. If you have received this communication in error, please notify the sender immediately and delete the original message.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Issue with UPDATE statement on v8
2005-09-22 16:22 Issue with UPDATE statement on v8 Kenneth Hutchinson <kenneth.hutchinson@mpfyieldstar.com>
@ 2005-09-23 13:30 ` Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 5+ messages in thread
From: Michael Fuhr @ 2005-09-23 13:30 UTC (permalink / raw)
To: Kenneth Hutchinson <kenneth.hutchinson@mpfyieldstar.com>; +Cc: pgsql-sql
On Thu, Sep 22, 2005 at 11:22:22AM -0500, Kenneth Hutchinson wrote:
> UPDATE t_summary
> SET availability = 7
> WHERE oid = 28245084
>
> When this query is executed (within a function or without) the database
> will simply hang. If the UPDATE is turned into a SELECT, the query
> works just fine.
One possibility is that another transaction has updated this row
or done a SELECT FOR UPDATE and hasn't committed yet. Does just
this one record cause the update to hang or do all updates to the
table hang? Have you queried pg_locks in another session while the
update is hung?
--
Michael Fuhr
^ permalink raw reply [nested|flat] 5+ messages in thread
* Issue with UPDATE statement on v8
@ 2005-09-22 20:33 Kenneth Hutchinson <kenneth.hutchinson@mpfyieldstar.com>
2005-09-22 20:48 ` Re: Issue with UPDATE statement on v8 Tom Lane <tgl@sss.pgh.pa.us>
2005-09-22 20:48 ` Re: Issue with UPDATE statement on v8 Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Kenneth Hutchinson @ 2005-09-22 20:33 UTC (permalink / raw)
To: pgsql-sql
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. 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
This message is intended only for the use of the individual(s) or entity to which it is addressed and may contain information that is privileged, confidential, and/or proprietary to RealPage and its affiliated companies. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, forwarding or copying of this communication is prohibited without the express permission of the sender. If you have received this communication in error, please notify the sender immediately and delete the original message.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Issue with UPDATE statement on v8
2005-09-22 20:33 Issue with UPDATE statement on v8 Kenneth Hutchinson <kenneth.hutchinson@mpfyieldstar.com>
@ 2005-09-22 20:48 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2005-09-22 20:48 UTC (permalink / raw)
To: Kenneth Hutchinson <kenneth.hutchinson@mpfyieldstar.com>; +Cc: pgsql-sql
"Kenneth Hutchinson" <kenneth.hutchinson@mpfyieldstar.com> writes:
> UPDATE t_summary
> SET availability = 7
> WHERE oid = 28245084
> When this query is executed (within a function or without) the database
> will simply hang.
Is it really hung, or just taking an awfully long time? If the backend
is consuming no CPU or I/O then I'd agree it's the former; please look
into the pg_locks view to see if you can find out what it's waiting for.
If it's the latter, maybe you neglected to create an index on OID?
> Has anyone else experienced a similar issue?
We'd certainly have heard about it if so. But you haven't provided
enough info to let anyone reproduce the problem for investigation.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Issue with UPDATE statement on v8
2005-09-22 20:33 Issue with UPDATE statement on v8 Kenneth Hutchinson <kenneth.hutchinson@mpfyieldstar.com>
@ 2005-09-22 20:48 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Rajesh Kumar Mallah @ 2005-09-22 20:48 UTC (permalink / raw)
To: Kenneth Hutchinson <kenneth.hutchinson@mpfyieldstar.com>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-09-23 13:30 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-22 16:22 Issue with UPDATE statement on v8 Kenneth Hutchinson <kenneth.hutchinson@mpfyieldstar.com>
2005-09-23 13:30 ` Michael Fuhr <mike@fuhr.org>
2005-09-22 20:33 Issue with UPDATE statement on v8 Kenneth Hutchinson <kenneth.hutchinson@mpfyieldstar.com>
2005-09-22 20:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-09-22 20:48 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
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