pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUpdating a specific number of rows in pl/pgsql
8+ messages / 4 participants
[nested] [flat]
* Updating a specific number of rows in pl/pgsql
@ 2009-08-11 00:52 Peter Headland <pheadland@actuate.com>
2009-08-11 10:25 ` Re: Updating a specific number of rows in pl/pgsql D'Arcy J.M. Cain <darcy@druid.net>
0 siblings, 1 reply; 8+ messages in thread
From: Peter Headland @ 2009-08-11 00:52 UTC (permalink / raw)
To: pgsql-sql
I'm working on a queuing application. As part of this I'm trying to
write a pl/pgsql function that updates a specific number of rows in the
most efficient way possible. Multiple queues are contained within a
single table.
I can get the rows I want to update like this:
SELECT *
FROM queue
WHERE id = p_queue_id
ORDER BY rank
LIMIT p_number_of_items;
Of course, there may not be p_number_of_items available in the queue.
I want to update all the rows in the cursor in the same way:
UPDATE queue SET assigned = TRUE;
The "obvious" solution is to get a cursor on the query and attempt to
MOVE through that cursor in a loop, using the row count from the SELECT
to tell me when I am done. I can then use UPDATE ... WHERE CURRENT OF
... to do the updates. This seems cumbersome and inefficient to me. Is
there a better way?
Ideally, I'd like to do something like:
UPDATE (SELECT ... ) ...
--
Peter Headland
Architect
Actuate Corporation
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Updating a specific number of rows in pl/pgsql
2009-08-11 00:52 Updating a specific number of rows in pl/pgsql Peter Headland <pheadland@actuate.com>
@ 2009-08-11 10:25 ` D'Arcy J.M. Cain <darcy@druid.net>
2009-08-11 10:55 ` Re: Updating a specific number of rows in pl/pgsql Pavel Stehule <pavel.stehule@gmail.com>
2009-08-11 16:55 ` Re: Updating a specific number of rows in pl/pgsql Peter Headland <pheadland@actuate.com>
0 siblings, 2 replies; 8+ messages in thread
From: D'Arcy J.M. Cain @ 2009-08-11 10:25 UTC (permalink / raw)
To: Peter Headland <pheadland@actuate.com>; +Cc: pgsql-sql
On Mon, 10 Aug 2009 17:52:36 -0700
"Peter Headland" <pheadland@actuate.com> wrote:
> I can get the rows I want to update like this:
>
> SELECT *
> FROM queue
> WHERE id = p_queue_id
> ORDER BY rank
> LIMIT p_number_of_items;
>
> Of course, there may not be p_number_of_items available in the queue.
>
> I want to update all the rows in the cursor in the same way:
>
> UPDATE queue SET assigned = TRUE;
Assuming that there is a unique identifier on queue, let's call it
queue_id, you should be able to do something like this:
UPDATE queue SET assigned = TRUE
WHERE queue_id IN (SELECT queue_id
FROM queue
WHERE id = p_queue_id
ORDER BY rank
LIMIT p_number_of_items);
--
D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Updating a specific number of rows in pl/pgsql
2009-08-11 00:52 Updating a specific number of rows in pl/pgsql Peter Headland <pheadland@actuate.com>
2009-08-11 10:25 ` Re: Updating a specific number of rows in pl/pgsql D'Arcy J.M. Cain <darcy@druid.net>
@ 2009-08-11 10:55 ` Pavel Stehule <pavel.stehule@gmail.com>
2009-08-11 17:05 ` Re: Updating a specific number of rows in pl/pgsql Peter Headland <pheadland@actuate.com>
2009-08-11 17:24 ` Re: Updating a specific number of rows in pl/pgsql Peter Headland <pheadland@actuate.com>
1 sibling, 2 replies; 8+ messages in thread
From: Pavel Stehule @ 2009-08-11 10:55 UTC (permalink / raw)
To: D'Arcy J.M. Cain <darcy@druid.net>; +Cc: Peter Headland <pheadland@actuate.com>; pgsql-sql
2009/8/11 D'Arcy J.M. Cain <darcy@druid.net>:
> On Mon, 10 Aug 2009 17:52:36 -0700
> "Peter Headland" <pheadland@actuate.com> wrote:
>> I can get the rows I want to update like this:
>>
>> SELECT *
>> FROM queue
>> WHERE id = p_queue_id
>> ORDER BY rank
>> LIMIT p_number_of_items;
>>
>> Of course, there may not be p_number_of_items available in the queue.
>>
>> I want to update all the rows in the cursor in the same way:
>>
>> UPDATE queue SET assigned = TRUE;
>
> Assuming that there is a unique identifier on queue, let's call it
> queue_id, you should be able to do something like this:
>
> UPDATE queue SET assigned = TRUE
> WHERE queue_id IN (SELECT queue_id
> FROM queue
> WHERE id = p_queue_id
> ORDER BY rank
> LIMIT p_number_of_items);
>
there are one fast trick
http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_first_n_rows_removing
p.s. replace DELETE by UPDATE
regards
Pavel Stehule
> --
> D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves
> http://www.druid.net/darcy/ | and a sheep voting on
> +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Updating a specific number of rows in pl/pgsql
2009-08-11 00:52 Updating a specific number of rows in pl/pgsql Peter Headland <pheadland@actuate.com>
2009-08-11 10:25 ` Re: Updating a specific number of rows in pl/pgsql D'Arcy J.M. Cain <darcy@druid.net>
2009-08-11 10:55 ` Re: Updating a specific number of rows in pl/pgsql Pavel Stehule <pavel.stehule@gmail.com>
@ 2009-08-11 17:05 ` Peter Headland <pheadland@actuate.com>
2009-08-11 17:22 ` Re: Updating a specific number of rows in pl/pgsql Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Peter Headland @ 2009-08-11 17:05 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; D'Arcy J.M. Cain <darcy@druid.net>; +Cc: pgsql-sql
> there are one fast trick
> http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_first_n_rows_removing
Thanks - that's a very useful page!
Unfortunately, there is no single column that provides a unique id, and I am reluctant to add one (for example, using a sequence and a new index) for performance reasons.
Given that additional constraint, is my original plan using a loop to iterate over a cursor reasonable? I don't anticipate p_number_of_items being more than 20.
--
Peter Headland
Architect
Actuate Corporation
-----Original Message-----
From: Pavel Stehule [mailto:pavel.stehule@gmail.com]
Sent: Tuesday, August 11, 2009 03:55
To: D'Arcy J.M. Cain
Cc: Peter Headland; pgsql-sql@postgresql.org
Subject: Re: [SQL] Updating a specific number of rows in pl/pgsql
2009/8/11 D'Arcy J.M. Cain <darcy@druid.net>:
> On Mon, 10 Aug 2009 17:52:36 -0700
> "Peter Headland" <pheadland@actuate.com> wrote:
>> I can get the rows I want to update like this:
>>
>> SELECT *
>> FROM queue
>> WHERE id = p_queue_id
>> ORDER BY rank
>> LIMIT p_number_of_items;
>>
>> Of course, there may not be p_number_of_items available in the queue.
>>
>> I want to update all the rows in the cursor in the same way:
>>
>> UPDATE queue SET assigned = TRUE;
>
> Assuming that there is a unique identifier on queue, let's call it
> queue_id, you should be able to do something like this:
>
> UPDATE queue SET assigned = TRUE
> WHERE queue_id IN (SELECT queue_id
> FROM queue
> WHERE id = p_queue_id
> ORDER BY rank
> LIMIT p_number_of_items);
>
there are one fast trick
http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_first_n_rows_removing
p.s. replace DELETE by UPDATE
regards
Pavel Stehule
> --
> D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves
> http://www.druid.net/darcy/ | and a sheep voting on
> +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Updating a specific number of rows in pl/pgsql
2009-08-11 00:52 Updating a specific number of rows in pl/pgsql Peter Headland <pheadland@actuate.com>
2009-08-11 10:25 ` Re: Updating a specific number of rows in pl/pgsql D'Arcy J.M. Cain <darcy@druid.net>
2009-08-11 10:55 ` Re: Updating a specific number of rows in pl/pgsql Pavel Stehule <pavel.stehule@gmail.com>
2009-08-11 17:05 ` Re: Updating a specific number of rows in pl/pgsql Peter Headland <pheadland@actuate.com>
@ 2009-08-11 17:22 ` Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Pavel Stehule @ 2009-08-11 17:22 UTC (permalink / raw)
To: Peter Headland <pheadland@actuate.com>; +Cc: D'Arcy J.M. Cain <darcy@druid.net>; pgsql-sql
2009/8/11 Peter Headland <pheadland@actuate.com>:
>> there are one fast trick
>> http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_first_n_rows_removing
>
> Thanks - that's a very useful page!
>
> Unfortunately, there is no single column that provides a unique id, and I am reluctant to add one (for example, using a sequence and a new index) for performance reasons.
ctid is unique system column in every table.
postgres=# create table x(a int);
CREATE TABLE
Time: 655,062 ms
postgres=# insert into x values(10);
INSERT 0 1
Time: 49,237 ms
postgres=# insert into x values(10);
INSERT 0 1
Time: 1,740 ms
postgres=# select ctid, a from x;
ctid | a
-------+----
(0,1) | 10
(0,2) | 10
(2 rows)
>
> Given that additional constraint, is my original plan using a loop to iterate over a cursor reasonable? I don't anticipate p_number_of_items being more than 20.
why not? for small number of iteration is loop over cursor good solution.
Pavel Stehule
>
> --
> Peter Headland
> Architect
> Actuate Corporation
>
>
> -----Original Message-----
> From: Pavel Stehule [mailto:pavel.stehule@gmail.com]
> Sent: Tuesday, August 11, 2009 03:55
> To: D'Arcy J.M. Cain
> Cc: Peter Headland; pgsql-sql@postgresql.org
> Subject: Re: [SQL] Updating a specific number of rows in pl/pgsql
>
> 2009/8/11 D'Arcy J.M. Cain <darcy@druid.net>:
>> On Mon, 10 Aug 2009 17:52:36 -0700
>> "Peter Headland" <pheadland@actuate.com> wrote:
>>> I can get the rows I want to update like this:
>>>
>>> SELECT *
>>> FROM queue
>>> WHERE id = p_queue_id
>>> ORDER BY rank
>>> LIMIT p_number_of_items;
>>>
>>> Of course, there may not be p_number_of_items available in the queue.
>>>
>>> I want to update all the rows in the cursor in the same way:
>>>
>>> UPDATE queue SET assigned = TRUE;
>>
>> Assuming that there is a unique identifier on queue, let's call it
>> queue_id, you should be able to do something like this:
>>
>> UPDATE queue SET assigned = TRUE
>> WHERE queue_id IN (SELECT queue_id
>> FROM queue
>> WHERE id = p_queue_id
>> ORDER BY rank
>> LIMIT p_number_of_items);
>>
>
> there are one fast trick
> http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_first_n_rows_removing
>
> p.s. replace DELETE by UPDATE
> regards
> Pavel Stehule
>
>> --
>> D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves
>> http://www.druid.net/darcy/ | and a sheep voting on
>> +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Updating a specific number of rows in pl/pgsql
2009-08-11 00:52 Updating a specific number of rows in pl/pgsql Peter Headland <pheadland@actuate.com>
2009-08-11 10:25 ` Re: Updating a specific number of rows in pl/pgsql D'Arcy J.M. Cain <darcy@druid.net>
2009-08-11 10:55 ` Re: Updating a specific number of rows in pl/pgsql Pavel Stehule <pavel.stehule@gmail.com>
@ 2009-08-11 17:24 ` Peter Headland <pheadland@actuate.com>
2009-08-12 07:34 ` Re: Updating a specific number of rows in pl/pgsql Xharon <sahapasci@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Peter Headland @ 2009-08-11 17:24 UTC (permalink / raw)
To: Peter Headland <pheadland@actuate.com>; Pavel Stehule <pavel.stehule@gmail.com>; D'Arcy J.M. Cain <darcy@druid.net>; +Cc: pgsql-sql
> Unfortunately, there is no single column that provides a unique id.
Correction - I did not understand what ctid was, but now I do, so I will try your tip.
--
Peter Headland
Architect
Actuate Corporation
-----Original Message-----
From: Peter Headland
Sent: Tuesday, August 11, 2009 10:05
To: 'Pavel Stehule'; D'Arcy J.M. Cain
Cc: pgsql-sql@postgresql.org
Subject: RE: [SQL] Updating a specific number of rows in pl/pgsql
> there are one fast trick
> http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_first_n_rows_removing
Thanks - that's a very useful page!
Unfortunately, there is no single column that provides a unique id, and I am reluctant to add one (for example, using a sequence and a new index) for performance reasons.
Given that additional constraint, is my original plan using a loop to iterate over a cursor reasonable? I don't anticipate p_number_of_items being more than 20.
--
Peter Headland
Architect
Actuate Corporation
-----Original Message-----
From: Pavel Stehule [mailto:pavel.stehule@gmail.com]
Sent: Tuesday, August 11, 2009 03:55
To: D'Arcy J.M. Cain
Cc: Peter Headland; pgsql-sql@postgresql.org
Subject: Re: [SQL] Updating a specific number of rows in pl/pgsql
2009/8/11 D'Arcy J.M. Cain <darcy@druid.net>:
> On Mon, 10 Aug 2009 17:52:36 -0700
> "Peter Headland" <pheadland@actuate.com> wrote:
>> I can get the rows I want to update like this:
>>
>> SELECT *
>> FROM queue
>> WHERE id = p_queue_id
>> ORDER BY rank
>> LIMIT p_number_of_items;
>>
>> Of course, there may not be p_number_of_items available in the queue.
>>
>> I want to update all the rows in the cursor in the same way:
>>
>> UPDATE queue SET assigned = TRUE;
>
> Assuming that there is a unique identifier on queue, let's call it
> queue_id, you should be able to do something like this:
>
> UPDATE queue SET assigned = TRUE
> WHERE queue_id IN (SELECT queue_id
> FROM queue
> WHERE id = p_queue_id
> ORDER BY rank
> LIMIT p_number_of_items);
>
there are one fast trick
http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_first_n_rows_removing
p.s. replace DELETE by UPDATE
regards
Pavel Stehule
> --
> D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves
> http://www.druid.net/darcy/ | and a sheep voting on
> +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Updating a specific number of rows in pl/pgsql
2009-08-11 00:52 Updating a specific number of rows in pl/pgsql Peter Headland <pheadland@actuate.com>
2009-08-11 10:25 ` Re: Updating a specific number of rows in pl/pgsql D'Arcy J.M. Cain <darcy@druid.net>
2009-08-11 10:55 ` Re: Updating a specific number of rows in pl/pgsql Pavel Stehule <pavel.stehule@gmail.com>
2009-08-11 17:24 ` Re: Updating a specific number of rows in pl/pgsql Peter Headland <pheadland@actuate.com>
@ 2009-08-12 07:34 ` Xharon <sahapasci@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Xharon @ 2009-08-12 07:34 UTC (permalink / raw)
To: pgsql-sql
ctid is the physical location of the row version, so it's not
static..
check http://www.postgresql.org/docs/8.2/static/ddl-system-columns.html
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Updating a specific number of rows in pl/pgsql
2009-08-11 00:52 Updating a specific number of rows in pl/pgsql Peter Headland <pheadland@actuate.com>
2009-08-11 10:25 ` Re: Updating a specific number of rows in pl/pgsql D'Arcy J.M. Cain <darcy@druid.net>
@ 2009-08-11 16:55 ` Peter Headland <pheadland@actuate.com>
1 sibling, 0 replies; 8+ messages in thread
From: Peter Headland @ 2009-08-11 16:55 UTC (permalink / raw)
To: D'Arcy J.M. Cain <darcy@druid.net>; +Cc: pgsql-sql
>Assuming that there is a unique identifier on queue
Alas, there is not. The PK is made up of 4 columns.
--
Peter Headland
Architect
Actuate Corporation
-----Original Message-----
From: D'Arcy J.M. Cain [mailto:darcy@druid.net]
Sent: Tuesday, August 11, 2009 03:25
To: Peter Headland
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Updating a specific number of rows in pl/pgsql
On Mon, 10 Aug 2009 17:52:36 -0700
"Peter Headland" <pheadland@actuate.com> wrote:
> I can get the rows I want to update like this:
>
> SELECT *
> FROM queue
> WHERE id = p_queue_id
> ORDER BY rank
> LIMIT p_number_of_items;
>
> Of course, there may not be p_number_of_items available in the queue.
>
> I want to update all the rows in the cursor in the same way:
>
> UPDATE queue SET assigned = TRUE;
Assuming that there is a unique identifier on queue, let's call it
queue_id, you should be able to do something like this:
UPDATE queue SET assigned = TRUE
WHERE queue_id IN (SELECT queue_id
FROM queue
WHERE id = p_queue_id
ORDER BY rank
LIMIT p_number_of_items);
--
D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2009-08-12 07:34 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-08-11 00:52 Updating a specific number of rows in pl/pgsql Peter Headland <pheadland@actuate.com>
2009-08-11 10:25 ` D'Arcy J.M. Cain <darcy@druid.net>
2009-08-11 10:55 ` Pavel Stehule <pavel.stehule@gmail.com>
2009-08-11 17:05 ` Peter Headland <pheadland@actuate.com>
2009-08-11 17:22 ` Pavel Stehule <pavel.stehule@gmail.com>
2009-08-11 17:24 ` Peter Headland <pheadland@actuate.com>
2009-08-12 07:34 ` Xharon <sahapasci@gmail.com>
2009-08-11 16:55 ` Peter Headland <pheadland@actuate.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