On Wed, Apr 12, 2017 at 3:14 PM, Gavin Wahl wrote:
> I think this paragraph explains why it happens:
> https://www.postgresql.org/docs/9.6/static/transaction-
> iso.html#XACT-READ-COMMITTED.
>
> > If the first updater commits, the second updater will ignore the row if
> the
> > first updater dele
I have this table:
CREATE TABLE test (id INT PRIMARY KEY);
INSERT INTO test VALUES (1);
Then I run these two transactions simultaneously:
one| two
---+---
BEGIN; |
This is correct behavior with MVCC. Do a 'select * from x' in thread 2 and to
understand why. The select for update in thread 2 sees the data in table x as
it was prior to thread 1 committing, thus it won't see the row with a=2.
For further suggestions you'll have to explain what you are logic
Hi,
I hit a UPDATE/LOCK issue in my application and the result has surprised me
somewhat.
And for the repro, it boils down into this:
---
CREATE TABLE x (a int, b bool);
INSERT INTO x VALUES (1, TRUE);
COMMIT;
_THREAD 1_:
BEGIN;
UPDATE x SET b=FALSE;
INSERT INTO x VALUES (2, TRUE);
On Tue, Feb 1, 2011 at 11:18 AM, Tom Lane wrote:
> "Yngve Nysaeter Pettersen" writes:
>> To avoid having the processes trample each other's queries (the first
>> attempt was to select the first matching entries of the table, which
>> caused one to block all other transactions), one of the steps I
On 3/14/2011 10:13 AM, Yngve N. Pettersen (Developer Opera Software ASA)
wrote:
Hello all,
Just a quick update of how it went.
I ended up using code similar to a combination of Andy Colson's and David
Johnston's suggestions below, and performance is back at what is was
before. Thanks for the su
Hello all,
Just a quick update of how it went.
I ended up using code similar to a combination of Andy Colson's and David
Johnston's suggestions below, and performance is back at what is was
before. Thanks for the suggestions
BTW: AFAICT I never got a response from Tom Lane about whether it was
Hello David,
On Wed, 02 Feb 2011 01:36:15 +0100, David Johnston
wrote:
If random sampling is desirable would the following construct limit
locking
only to the sampled rows?
SELECT id
FROM tasktable
WHERE id IN (SELECT random_id_sample())
FOR UPDATE
The "random_id_sample" would supply a
If random sampling is desirable would the following construct limit locking
only to the sampled rows?
SELECT id
FROM tasktable
WHERE id IN (SELECT random_id_sample())
FOR UPDATE
The "random_id_sample" would supply a configurable group of IDs off of
tasktable which the FOR UPDATE would then lock
On Tue, 01 Feb 2011 20:04:31 +0100, Andy Colson
wrote:
On 2/1/2011 12:51 PM, Yngve Nysaeter Pettersen wrote:
So, AFAICT I am afraid it would not work in the general case for my
project :( .
However, it might be useful in somebody else's project :) .
No, I didn't think it would work for y
Hmm...
May I ask how this look in details. If e.g. I do select * from myeshop offset
100 limit 20, I have 1000 rows which rows will be locked?
a) 0 to 120, or
b) all rows will be locked.?
Kind regards,
Radek
Tom Lane Tuesday 01 February 2011 18:18:17
> In 9.0, LIMIT/OFFSET processing is done
On 2/1/2011 12:51 PM, Yngve Nysaeter Pettersen wrote:
Thanks Andy,
On Tue, 01 Feb 2011 19:29:08 +0100, Andy Colson
wrote:
On 2/1/2011 12:10 PM, Yngve Nysaeter Pettersen wrote:
On Tue, 01 Feb 2011 18:11:23 +0100, Andy Colson
I think the random limit thing is a race condition itself. Whenev
Thanks Andy,
On Tue, 01 Feb 2011 19:29:08 +0100, Andy Colson
wrote:
On 2/1/2011 12:10 PM, Yngve Nysaeter Pettersen wrote:
On Tue, 01 Feb 2011 18:11:23 +0100, Andy Colson
I think the random limit thing is a race condition itself. Whenever
you have multiple processes hitting the same rows yo
On 2/1/2011 12:10 PM, Yngve Nysaeter Pettersen wrote:
On Tue, 01 Feb 2011 18:11:23 +0100, Andy Colson
I think the random limit thing is a race condition itself. Whenever
you have multiple processes hitting the same rows you're going to run
into problems. Have you thought of using a sequence ins
On Tue, 01 Feb 2011 18:11:23 +0100, Andy Colson
wrote:
On 2/1/2011 10:59 AM, Yngve Nysaeter Pettersen wrote:
Hi,
Thanks for the quick answer, Andy.
On Tue, 01 Feb 2011 16:19:17 +0100, Andy Colson
wrote:
So, if I understand correctly, you:
q = SELECT record_id FROM queue
WHERE project_
On Tue, 01 Feb 2011 18:18:17 +0100, Tom Lane wrote:
"Yngve Nysaeter Pettersen" writes:
To avoid having the processes trample each other's queries (the first
attempt was to select the first matching entries of the table, which
caused one to block all other transactions), one of the steps I too
"Yngve Nysaeter Pettersen" writes:
> To avoid having the processes trample each other's queries (the first
> attempt was to select the first matching entries of the table, which
> caused one to block all other transactions), one of the steps I took was
> to select a set of idle rows at a ran
On 2/1/2011 10:59 AM, Yngve Nysaeter Pettersen wrote:
Hi,
Thanks for the quick answer, Andy.
On Tue, 01 Feb 2011 16:19:17 +0100, Andy Colson
wrote:
So, if I understand correctly, you:
q = SELECT record_id FROM queue
WHERE project_id = my_project AND state = idle
LIMIT n OFFSET i FOR UPDATE
Hi,
Thanks for the quick answer, Andy.
On Tue, 01 Feb 2011 16:19:17 +0100, Andy Colson
wrote:
So, if I understand correctly, you:
q = SELECT record_id FROM queue
WHERE project_id = my_project AND state = idle
LIMIT n OFFSET i FOR UPDATE
while not q.eof
update queu
On 2/1/2011 6:32 AM, Yngve Nysaeter Pettersen wrote:
Hello all,
I am in the process of migrating a system from Postgresql 8.3 to 9.0,
and have run into a problem with the task queue systems I am using.
The task queue controls the allocation of tasks between about 1000
processes working in paral
Hello all,
I am in the process of migrating a system from Postgresql 8.3 to 9.0, and
have run into a problem with the task queue systems I am using.
The task queue controls the allocation of tasks between about 1000
processes working in parallel, and is essentially a table of
record_id
Well, I did, but I clicked the link to Chapter 13 before scrolling down
further to see the Caution section. Heheh.
Thanks for the heads up.
On Sun, 2009-01-11 at 12:12 -0500, Tom Lane wrote:
> Darren Govoni writes:
> > This seems like a bug to me. Is it fixed in the current dev build? Or is
> >
Darren Govoni writes:
> This seems like a bug to me. Is it fixed in the current dev build? Or is
> there something else I'm missing?
I think you have not read the documentation about FOR UPDATE and LIMIT
on the SELECT reference page:
http://www.postgresql.org/docs/8.3/static/sql-select.html#SQL-F
Hi,
I have a simple scenario that is producing incorrect results with
8.3,ubuntu.
I have queries attempting the same "select-for update limit 1". at the
same time. There are 2 rows in my test database.
The first will enter the transaction and only update ONE of the rows it
selected, because it u
"D. Dante Lorenso" <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> "D. Dante Lorenso" <[EMAIL PROTECTED]> writes:
>>> I am calling a stored proc from PHP. Since I do not begin a
>>> transaction, I assume that my call is automatically committed
>>> immediately after invocation.
>>
>> Have you a
Tom Lane wrote:
"D. Dante Lorenso" <[EMAIL PROTECTED]> writes:
Douglas McNaught wrote:
How quickly after you update the row status are you comitting (and
releasing locks)?
I am calling a stored proc from PHP. Since I do not begin a
transaction, I assume that my call is automatically committ
"D. Dante Lorenso" <[EMAIL PROTECTED]> writes:
> Douglas McNaught wrote:
>> How quickly after you update the row status are you comitting (and
>> releasing locks)?
> I am calling a stored proc from PHP. Since I do not begin a
> transaction, I assume that my call is automatically committed
> imm
Douglas McNaught wrote:
"D. Dante Lorenso" <[EMAIL PROTECTED]> writes:
How quickly after you update the row status are you comitting (and
releasing locks)?
I am calling a stored proc from PHP. Since I do not begin a
transaction, I assume that my call is automatically committed
immediately af
On Aug 15, 2007, at 9:21 PM, D. Dante Lorenso wrote:
Erik Jones wrote:
On Aug 15, 2007, at 2:39 PM, [EMAIL PROTECTED] wrote:
Erik Jones wrote:
On Aug 15, 2007, at 1:09 PM, D. Dante Lorenso wrote:
...to ensure that only one server is processing the queue item,
so inside PGSQL, use SELECT .
"D. Dante Lorenso" <[EMAIL PROTECTED]> writes:
> I need logic like "atomic test and set" or pop 1 item off the queue
> atomically and tell me what that item was.
>
> In my situation, there are a dozen or so machines polling this queue
> periodically looking for work to do. As more polling is occu
"D. Dante Lorenso" <[EMAIL PROTECTED]> writes:
> ... When my server is under severe load, however, this function begins to
> take a long time to execute and I begin to suspect that the FOR UPDATE
> lock might be locking the whole table and not just the row.
FOR UPDATE only locks the selected row
Erik Jones wrote:
On Aug 15, 2007, at 2:39 PM, [EMAIL PROTECTED] wrote:
Erik Jones wrote:
On Aug 15, 2007, at 1:09 PM, D. Dante Lorenso wrote:
...to ensure that only one server is processing the queue item, so
inside PGSQL, use SELECT ... FOR UPDATE to lock the row...
When my server is unde
On Aug 15, 2007, at 2:39 PM, [EMAIL PROTECTED] wrote:
Erik Jones wrote:
On Aug 15, 2007, at 1:09 PM, D. Dante Lorenso wrote:
...to ensure that only one server is processing the queue item,
so inside PGSQL, use SELECT ... FOR UPDATE to lock the row...
When my server is under severe load, how
Erik Jones wrote:
On Aug 15, 2007, at 1:09 PM, D. Dante Lorenso wrote:
...to ensure that only one server is processing the queue item, so
inside PGSQL, use SELECT ... FOR UPDATE to lock the row...
When my server is under severe load, however, this function begins to
take a long time to execu
On Aug 15, 2007, at 1:09 PM, D. Dante Lorenso wrote:
All,
I have a stored procedure that I use to manage a queue. I want to
pop an item off the queue to ensure that only one server is
processing the queue item, so inside PGSQL, use SELECT ... FOR
UPDATE to lock the row. Here's how I pop
All,
I have a stored procedure that I use to manage a queue. I want to pop
an item off the queue to ensure that only one server is processing the
queue item, so inside PGSQL, use SELECT ... FOR UPDATE to lock the row.
Here's how I pop the queue item:
---
>-Original Message-
>From: [EMAIL PROTECTED]
>[mailto:[EMAIL PROTECTED] On Behalf Of Panther
>Sent: dinsdag 30 januari 2007 7:07
>To: pgsql-general@postgresql.org
>Subject: [GENERAL] SELECT FOR UPDATE with ORDER BY to avoid
>row-level deadlock?
>
>Hi,
>
&
Hi,
My problem is that if I try to update more than one row in a table like
> UPDATE mytable SET something = 84 WHERE not_unique_col = 41;
in two concurrent transactions, it can result in a deadlock if the two
UPDATEs visit the rows in a different order.
The same applies, if I try to
> SELECT * F
Tom Lane <[EMAIL PROTECTED]> wrote:
> Daniel Denes <[EMAIL PROTECTED]> writes:
>
> > But what if I try like
> >> SELECT * FROM mytable
> >> WHERE not_unique_col = 41 ORDER BY pri_key ASC FOR UPDATE;
> > and do the UPDATE after this? It should never lead to a deadlock,
> > assuming the rows sel
=?ISO-8859-2?Q?D=E1niel_D=E9nes?= <[EMAIL PROTECTED]> writes:
> But what if I try like
>> SELECT * FROM mytable
>> WHERE not_unique_col = 41 ORDER BY pri_key ASC FOR UPDATE;
> and do the UPDATE after this? It should never lead to a deadlock,
> assuming the rows selected FOR UPDATE are locked in th
Hi,
My problem is that if I try to update more than one row in a table like
> UPDATE mytable SET something = 84 WHERE not_unique_col = 41;
in two concurrent transactions, it can result in a deadlock if the two
UPDATEs visit the rows in a different order.
The same applies, if I try to
> SELECT * F
Vlad <[EMAIL PROTECTED]> writes:
> Though question is - doesn't it seem logical to be able to use full
> table names in FOR UPDATE part like I can use them in WHERE part (if I
> don't need/want to use an alias)? Is it something postgresql speciffic
> or it's SQL standard (pardon my ignorance)?
The
Tom,
yes, this part is not well documented - specially double checked
before sendin email to the list.
Though question is - doesn't it seem logical to be able to use full
table names in FOR UPDATE part like I can use them in WHERE part (if I
don't need/want to use an alias)? Is it something postg
Vlad <[EMAIL PROTECTED]> writes:
> yes, we actually use table alias as a workaround, I thought that it's
> actually looks like error in postgresql parser (or deeper) that needs
> to be reported.
No, it's supposed to be that way: FOR UPDATE items are table aliases.
Perhaps this isn't adequately doc
On Tue, 2005-09-06 at 13:45 -0400, Vlad wrote:
> SELECT * FROM one.aa, two.bb WHERE one.aa.a = two.bb.b FOR UPDATE OF
> one.aa;
>
> ERROR: syntax error at or near "." at character 73 (points to the
> last instance of "one.aa" in SQL query
Try using a table alias, and reference that alias in the
yes, we actually use table alias as a workaround, I thought that it's
actually looks like error in postgresql parser (or deeper) that needs
to be reported.
thanks.
On 9/6/05, Matt Miller <[EMAIL PROTECTED]> wrote:
> On Tue, 2005-09-06 at 13:45 -0400, Vlad wrote:
> > SELECT * FROM one.aa, two.bb W
CREATE SCHEMA one;
CREATE TABLE one.aa ( a INT );
CREATE SCHEMA two;
CREATE TABLE two.bb ( b INT );
SELECT * FROM one.aa, two.bb WHERE one.aa.a = two.bb.b FOR UPDATE OF one.aa;
ERROR: syntax error at or near "." at character 73 (points to the
last instance of "one.aa" in SQL query)
p.s. in our a
gt;
To: "Havasvölgyi Ottó" <[EMAIL PROTECTED]>
Cc:
Sent: Friday, July 29, 2005 3:12 AM
Subject: Re: [GENERAL] Select for update
On Fri, Jul 29, 2005 at 12:05:46AM +0200, Havasvölgyi Ottó wrote:
create function pidtest_del(_pid integer) returns void as $$
declare
row pidtest;
beg
svölgyi Ottó" <[EMAIL PROTECTED]>
Cc:
Sent: Friday, July 29, 2005 8:58 AM
Subject: Re: [GENERAL] Select for update
On Fri, Jul 29, 2005 at 00:05:46 +0200,
Havasvölgyi Ottó <[EMAIL PROTECTED]> wrote:
This function deletes a row, and updates the pid field where pid is
geater
th
On Fri, Jul 29, 2005 at 00:05:46 +0200,
Havasvölgyi Ottó <[EMAIL PROTECTED]> wrote:
>
> This function deletes a row, and updates the pid field where pid is geater
> than the deleted pid value, so that the gap caused by the deletion is not
> present any more.
This isn't directly related to you
On Fri, Jul 29, 2005 at 12:05:46AM +0200, Havasvölgyi Ottó wrote:
> create function pidtest_del(_pid integer) returns void as $$
> declare
> row pidtest;
> begin
> perform pid from pidtest where pid>=_pid for update;
> delete from pidtest where pid=_pid;
> for row in select * from pidtest where pid
t; <[EMAIL PROTECTED]>
Cc:
Sent: Thursday, July 28, 2005 2:02 PM
Subject: Re: [GENERAL] Select for update
On Thu, Jul 28, 2005 at 01:22:31PM +0200, Havasvölgyi Ottó wrote:
Is it normal that when I select for update a record, but I don't select
all
the fields, that the contents of
On Thu, Jul 28, 2005 at 01:22:31PM +0200, Havasvölgyi Ottó wrote:
> Is it normal that when I select for update a record, but I don't select all
> the fields, that the contents of fields not selected will be deleted:
>
> create table pidtest(pid integer, szoveg text) without oids;
>
> select pid
Havasvölgyi Ottó wrote:
Hi,
Is it normal that when I select for update a record, but I don't select
all the fields, that the contents of fields not selected will be deleted:
create table pidtest(pid integer, szoveg text) without oids;
select pid from pistest where pid>5 for update;
After co
Hi,
Is it normal that when I select for update a record, but I don't select all
the fields, that the contents of fields not selected will be deleted:
create table pidtest(pid integer, szoveg text) without oids;
select pid from pistest where pid>5 for update;
After committing (autocommit), th
on 3/21/04 5:10 PM, [EMAIL PROTECTED] purportedly said:
> I'm trying to build a type of record "check out" program and I'm just
> a wee bit confused.
> Several users will be using the same select
> statement at the same time and just want to make sure they're getting
> different records. example..
on 2/16/04 10:51 AM, [EMAIL PROTECTED] purportedly said:
> I am trying to gather stats about how many times a resource in our web
> app is viewed, i.e. just a COUNT. There are potentially millions of
> resources within the system.
>
> I thought of two methods:
>
> 1.An extra column in the re
I need som ehelp concerning queries using SLEECT
... FOR UPDATE
I have two tables (see description
below)
One is the table of reference, the other one is a
table where i have daily infoirmation
I want to check the differences on the two tables
based on the fields omc_index and bsc for
> That may be because there is no guarantee of the ordering of your returned
> records.
That makes sense - I just didn't think of it. Doh!
> Maybe you need to re-initialise your variables before executing the
> select?
I'm not sure what variables I could re-initialise here as the select
statemen
On 10/12/2003 09:10 John Sidney-Woollett wrote:
[snip]
Actually from my tests, the simple code below *sometimes* blocks on the
locked row, and sometimes skips the locked row, and returns the next
unlocked row.
That may be because there is no guarantee of the ordering of your returned
records.
[s
> Unlikely as PostgreSQL doesn't support read uncommitted...
You're right - Postgres only offers two levels "PostgreSQL offers the Read
Committed and Serializable isolation levels."
> I think you need to play with a couple of psql sessions to sort this out.
> I think you might have a race conditi
On 09/12/2003 11:47 John Sidney-Woollett wrote:
Hi
I have a function called from a java app (via jdbc) which identifies
images awaiting processing. This is determined by checking the
WPImageStateID field on the WPImageHeader record (1=awaiting, 2=being
processed, 3=complete).
The (jdbc) connection
Hi
I have a function called from a java app (via jdbc) which identifies
images awaiting processing. This is determined by checking the
WPImageStateID field on the WPImageHeader record (1=awaiting, 2=being
processed, 3=complete).
The (jdbc) connection to the database is a standard one so I suspect
I just finished reading Bruce M's book, so this thread confuses me,
esp. Jan's posts. I take full heed of the need for application level
user/thread management, but I was interested in using a parallel
set-up in PG (however redundant that might be). Now that Jan has
discounted "SELECT...FOR UPDA
> It seems to me that SELECT ... FOR UPDATE is not the way to go if
> it is possible that the selected record may be held for any length
> of time.
But transactions are supposed to occur very quickly.
> For instance, say you are storing web pages in the database, and you
> want a number of devel
Jan Wieck wrote:
>Oliver Elphick wrote:
>
>>Jan Wieck wrote:
>> >But the question itself tells that you're about to implement
>> >a major design error in your application. Holding database
>> >locks during user interaction IS A BAD THING. Never, never
>> >ever do it
A 'document' may consist of several records from several tables, a
relationship the DBMS doesn't understand. It also introduces alot of
complexity. You may need a way to expire locks after a certain amount of
time, or allow some users to override other users' locks.
In a system I'm working on,
Oliver Elphick wrote:
> Jan Wieck wrote:
> >But the question itself tells that you're about to implement
> >a major design error in your application. Holding database
> >locks during user interaction IS A BAD THING. Never, never
> >ever do it that way. And any
Jan Wieck wrote:
>But the question itself tells that you're about to implement
>a major design error in your application. Holding database
>locks during user interaction IS A BAD THING. Never, never
>ever do it that way. And anybody telling you something
>
Hi all,
I'm trying SELECT FOR UPDATE
but I have a problem with locks.
example:
I have two users accessing to the same records, say...
user1:
DELECT * FROM table WHERE field=value FOR UPDATE
---
user2:
DELECT * FROM table W
Joseph Shraibman <[EMAIL PROTECTED]> writes:
> But the locking isn't working properly. I do something that should
> cause 3 different threads to try and do that append, and the first one
> goes through properly, but the second two append to the result of the
> first on only, meaning that the thir
And I forgot to mention my version is:
PostgreSQL 7.0.1 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66
Joseph Shraibman wrote:
>
> I have a program that does this:
> BEGIN;
> SELECT mystring FROM mytable WHERE x = 3 AND y = 4 FOR UPDATE;
>
> UPDATE mytable SET mystring = '' WHERE x = 3 AN
I have a program that does this:
BEGIN;
SELECT mystring FROM mytable WHERE x = 3 AND y = 4 FOR UPDATE;
UPDATE mytable SET mystring = '' WHERE x = 3 AND y
= 4;
END;
But the locking isn't working properly. I do something that should
cause 3 different threads to try and do that append, and the fir
73 matches
Mail list logo
