[SQL] Updating a specific number of rows in pl/pgsql

[Peter Headland]

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

[D'Arcy J.M. Cain]

On Mon, 10 Aug 2009 17:52:36 -0700
"Peter Headland" <phea...@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 <da...@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 (pgsq...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Pavel Stehule]

2009/8/11 D'Arcy J.M. Cain <da...@druid.net>:

> On Mon, 10 Aug 2009 17:52:36 -0700
> "Peter Headland" <phea...@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

[Peter Headland]

>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

[Peter Headland]

> 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

[Pavel Stehule]

2009/8/11 Peter Headland <phea...@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]

> 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: pgsq...@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....@gmail.com]
Sent: Tuesday, August 11, 2009 03:55
To: D'Arcy J.M. Cain
Cc: Peter Headland; pgsq...@postgresql.org

[Xharon]

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