pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSELECT MAX(c) FROM (SELECT ... FOR UPDATE) AS foo
8+ messages / 4 participants
[nested] [flat]
* SELECT MAX(c) FROM (SELECT ... FOR UPDATE) AS foo
@ 2004-08-17 11:07 Markus Bertheau <twanger@bluetwanger.de>
0 siblings, 2 replies; 8+ messages in thread
From: Markus Bertheau @ 2004-08-17 11:07 UTC (permalink / raw)
To: pgsql-sql
Hi,
why is the following query not allowed:
SELECT MAX(position) FROM (SELECT position FROM classes WHERE name =
'foo' FOR UPDATE OF classes) AS foo
It's clear which rows should be locked here, I think.
Thanks
--
Markus Bertheau <twanger@bluetwanger.de>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SELECT MAX(c) FROM (SELECT ... FOR UPDATE) AS foo
@ 2004-08-17 14:12 Bruno Wolff III <bruno@wolff.to>
parent: Markus Bertheau <twanger@bluetwanger.de>
1 sibling, 1 reply; 8+ messages in thread
From: Bruno Wolff III @ 2004-08-17 14:12 UTC (permalink / raw)
To: Markus Bertheau <twanger@bluetwanger.de>; +Cc: pgsql-sql
On Tue, Aug 17, 2004 at 13:07:43 +0200,
Markus Bertheau <twanger@bluetwanger.de> wrote:
> Hi,
>
> why is the following query not allowed:
>
> SELECT MAX(position) FROM (SELECT position FROM classes WHERE name =
> 'foo' FOR UPDATE OF classes) AS foo
>
> It's clear which rows should be locked here, I think.
Even if it was allowed, it probably wouldn't be good enough because it won't
protect against newly inserted records. You really want to lock the table
against concurrent updates when doing this if concurrent updates can cause
a problem. You really want predicate locking, to lock any old or new rows
with name = 'foo', but postgres doesn't have that capability.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SELECT MAX(c) FROM (SELECT ... FOR UPDATE) AS foo
@ 2004-08-17 14:21 Markus Bertheau <twanger@bluetwanger.de>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 2 replies; 8+ messages in thread
From: Markus Bertheau @ 2004-08-17 14:21 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-sql
В Втр, 17.08.2004, в 16:12, Bruno Wolff III пишет:
> > SELECT MAX(position) FROM (SELECT position FROM classes WHERE name =
> > 'foo' FOR UPDATE OF classes) AS foo
> >
> > It's clear which rows should be locked here, I think.
>
> Even if it was allowed, it probably wouldn't be good enough because it won't
> protect against newly inserted records.
Can you detail an example where this wouldn't be good enough?
In a PL/pgSQL function I'm doing
PERFORM position FROM class_fields WHERE class = arg_class_name;
INSERT INTO class_fields (class, field, position) VALUES
(arg_class_name, arg_field_name, (SELECT MAX(position) FROM class_fields
WHERE class = arg_class_name));
Is this unsafe?
The question initially arose because I wanted to do something similar to
SELECT INTO var_new_position MAX(position) FROM class_fields WHERE class
= arg_class_name FOR UPDATE OF class_fields;
which didn't work.
Thanks
--
Markus Bertheau <twanger@bluetwanger.de>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SELECT MAX(c) FROM (SELECT ... FOR UPDATE) AS foo
@ 2004-08-17 14:26 Jean-Luc Lachance <jllachan@sympatico.ca>
parent: Markus Bertheau <twanger@bluetwanger.de>
1 sibling, 1 reply; 8+ messages in thread
From: Jean-Luc Lachance @ 2004-08-17 14:26 UTC (permalink / raw)
To: Markus Bertheau <twanger@bluetwanger.de>; +Cc: pgsql-sql
This query does not make sense to me.
Why would you create an updatable subquery just to get the highest value?
Maybe you are trying to achieve something other than what the query
suggest. You wou care to put in words what you want to do?
JLL
Markus Bertheau wrote:
> Hi,
>
> why is the following query not allowed:
>
> SELECT MAX(position) FROM (SELECT position FROM classes WHERE name =
> 'foo' FOR UPDATE OF classes) AS foo
>
> It's clear which rows should be locked here, I think.
>
> Thanks
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SELECT MAX(c) FROM (SELECT ... FOR UPDATE) AS foo
@ 2004-08-17 14:51 Markus Bertheau <twanger@bluetwanger.de>
parent: Jean-Luc Lachance <jllachan@sympatico.ca>
0 siblings, 1 reply; 8+ messages in thread
From: Markus Bertheau @ 2004-08-17 14:51 UTC (permalink / raw)
To: Jean-Luc Lachance <jllachan@sympatico.ca>; +Cc: pgsql-sql
В Втр, 17.08.2004, в 16:26, Jean-Luc Lachance пишет:
> This query does not make sense to me.
> Why would you create an updatable subquery just to get the highest value?
To make sure that the highest value will be the highest value (or then
second-highest) after I commit the transaction. See my other answer.
--
Markus Bertheau <twanger@bluetwanger.de>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SELECT MAX(c) FROM (SELECT ... FOR UPDATE) AS foo
@ 2004-08-17 15:09 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Markus Bertheau <twanger@bluetwanger.de>
1 sibling, 0 replies; 8+ messages in thread
From: Stephan Szabo @ 2004-08-17 15:09 UTC (permalink / raw)
To: Markus Bertheau <twanger@bluetwanger.de>; +Cc: Bruno Wolff III <bruno@wolff.to>; pgsql-sql
On Tue, 17 Aug 2004, Markus Bertheau wrote:
> Ð ÐÑÑ, 17.08.2004, в 16:12, Bruno Wolff III пиÑеÑ:
> > > SELECT MAX(position) FROM (SELECT position FROM classes WHERE name =
> > > 'foo' FOR UPDATE OF classes) AS foo
> > >
> > > It's clear which rows should be locked here, I think.
> >
> > Even if it was allowed, it probably wouldn't be good enough because it won't
> > protect against newly inserted records.
>
> Can you detail an example where this wouldn't be good enough?
Another transaction can come along and insert a row with name='foo' into
classes with a higher position value after you've done the above but
before you commit.
T1: begin;
T2: begin;
T1: select max(position) from (select position from classes where
name='foo' for update of classes) as foo;
-- say this gets 5
T2: insert into classes (name, position) values ('foo', 10);
-- This wouldn't be blocked by the for update lock.
T2: commit;
-- now if you were to do the T1 select above, you'd get a different
-- answer in read committed.
If we had predicate locking, I think you could probably manage these cases
in serializable mode, but for now I'm not sure anything less than a table
lock would do.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SELECT MAX(c) FROM (SELECT ... FOR UPDATE) AS foo
@ 2004-08-17 15:54 Jean-Luc Lachance <jllachan@sympatico.ca>
parent: Markus Bertheau <twanger@bluetwanger.de>
1 sibling, 0 replies; 8+ messages in thread
From: Jean-Luc Lachance @ 2004-08-17 15:54 UTC (permalink / raw)
To: pgsql-sql; +Cc: Markus Bertheau <twanger@bluetwanger.de>
If your intent is to insert a new record with position incremented by 1,
you should use a trigger. Look at the autoincrement thread from few days
ago.
Markus Bertheau wrote:
> В Втр, 17.08.2004, в 16:12, Bruno Wolff III пишет:
>
>>>SELECT MAX(position) FROM (SELECT position FROM classes WHERE name =
>>>'foo' FOR UPDATE OF classes) AS foo
>>>
>>>It's clear which rows should be locked here, I think.
>>
>>Even if it was allowed, it probably wouldn't be good enough because it won't
>>protect against newly inserted records.
>
>
> Can you detail an example where this wouldn't be good enough?
>
> In a PL/pgSQL function I'm doing
>
> PERFORM position FROM class_fields WHERE class = arg_class_name;
> INSERT INTO class_fields (class, field, position) VALUES
> (arg_class_name, arg_field_name, (SELECT MAX(position) FROM class_fields
> WHERE class = arg_class_name));
>
> Is this unsafe?
>
> The question initially arose because I wanted to do something similar to
>
> SELECT INTO var_new_position MAX(position) FROM class_fields WHERE class
> = arg_class_name FOR UPDATE OF class_fields;
>
> which didn't work.
>
> Thanks
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SELECT MAX(c) FROM (SELECT ... FOR UPDATE) AS foo
@ 2004-08-17 17:31 Bruno Wolff III <bruno@wolff.to>
parent: Markus Bertheau <twanger@bluetwanger.de>
0 siblings, 0 replies; 8+ messages in thread
From: Bruno Wolff III @ 2004-08-17 17:31 UTC (permalink / raw)
To: Markus Bertheau <twanger@bluetwanger.de>; +Cc: Jean-Luc Lachance <jllachan@sympatico.ca>; pgsql-sql
On Tue, Aug 17, 2004 at 16:51:21 +0200,
Markus Bertheau <twanger@bluetwanger.de> wrote:
> ?? ??????, 17.08.2004, ?? 16:26, Jean-Luc Lachance ??????????:
> > This query does not make sense to me.
> > Why would you create an updatable subquery just to get the highest value?
>
> To make sure that the highest value will be the highest value (or then
> second-highest) after I commit the transaction. See my other answer.
If you just need ordering that isn't too sensitive to overlapping transactions,
then using a sequence (using a serial type is a convenient way to do this)
is probably your best bet. You need to make sure the value won't roll over.
But at worst a bigserial should be needed to do this.
There will potentially be gaps in the numbers allocated accross the table.
There will be gaps in the numbers allocated for any value of name. However,
if all you need is ordering that shouldn't matter.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2004-08-17 17:31 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-17 11:07 SELECT MAX(c) FROM (SELECT ... FOR UPDATE) AS foo Markus Bertheau <twanger@bluetwanger.de>
2004-08-17 14:12 ` Bruno Wolff III <bruno@wolff.to>
2004-08-17 14:21 ` Markus Bertheau <twanger@bluetwanger.de>
2004-08-17 15:09 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-08-17 15:54 ` Jean-Luc Lachance <jllachan@sympatico.ca>
2004-08-17 14:26 ` Jean-Luc Lachance <jllachan@sympatico.ca>
2004-08-17 14:51 ` Markus Bertheau <twanger@bluetwanger.de>
2004-08-17 17:31 ` Bruno Wolff III <bruno@wolff.to>
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