Обсуждение: Updating a specific number of rows in pl/pgsql
<p dir="LTR"><span lang="en-us"><font face="Calibri">I</font></span><span lang="en-us"><font face="Calibri">’</font></span><spanlang="en-us"><font face="Calibri">m working on a queuing application. As part of t</font><fontface="Calibri">his</font> <font face="Calibri">I</font></span><span lang="en-us"><font face="Calibri">’</font></span><spanlang="en-us"><font face="Calibri">m trying to write a</font></span><span lang="en-us"><font face="Calibri">pl/pgsql</font></span><span lang="en-us"> <font face="Calibri">function that updates aspec</font><font face="Calibri">ific number of rows in the most effic</font></span><span lang="en-us"><font face="Calibri">ientway possible. Multiple queues are contained within a single table.</font></span><p dir="LTR"><span lang="en-us"><fontface="Calibri">I</font> <font face="Calibri">can get the rows I want to update like this:</font></span><spanlang="en-us"></span><p dir="LTR"><span lang="en-us"></span><p dir="LTR"><span lang="en-us"><fontface="Courier New"> </font></span><span lang="en-us"> <font face="Courier New">SELECT *</font></span><pdir="LTR"><span lang="en-us"><font face="Courier New"></font></span><span lang="en-us"> <font face="CourierNew"></font></span><span lang="en-us"> <font face="Courier New"></font></span><span lang="en-us"> <font face="CourierNew">FROM queue</font></span><p dir="LTR"><span lang="en-us"><font face="Courier New"></font></span><span lang="en-us"> <fontface="Courier New"></font></span><span lang="en-us"> <font face="Courier New"></font></span><span lang="en-us"><font face="Courier New">WHERE</font> <font face="Courier New">id = p_queue_id</font></span><p dir="LTR"><spanlang="en-us"><font face="Courier New"></font></span><span lang="en-us"> <font face="Courier New"></font></span><spanlang="en-us"> <font face="Courier New"></font></span><span lang="en-us"> <font face="Courier New">ORDERBY</font> <font face="Courier New">rank</font></span><p dir="LTR"><span lang="en-us"><font face="Courier New"></font></span><spanlang="en-us"> <font face="Courier New"></font></span><span lang="en-us"> <font face="Courier New"></font></span><spanlang="en-us"> <font face="Courier New">LIMIT p</font><font face="Courier New">_number_of_items</font></span><spanlang="en-us"><font face="Courier New">;</font></span><span lang="en-us"></span><pdir="LTR"><span lang="en-us"></span><p dir="LTR"><span lang="en-us"><font face="Calibri">Of course,there may</font> <font face="Calibri">not be</font></span><span lang="en-us"> <font face="Courier New">p</font><fontface="Courier New">_number_of_items</font></span><span lang="en-us"><font face="Calibri"></font> <fontface="Calibri">a</font><font face="Calibri">vailable</font> <font face="Calibri">in the queue.</font></span><span lang="en-us"></span><pdir="LTR"><span lang="en-us"><font face="Calibri">I want to update all the rows in the cur</font></span><spanlang="en-us"><font face="Calibri">sor in the same way:</font></span><p dir="LTR"><span lang="en-us"><fontface="Courier New"> UPDATE q</font><font face="Courier New">ueue SET assigned</font><font face="CourierNew"> = TRUE;</font></span><span lang="en-us"></span><p dir="LTR"><span lang="en-us"></span><p dir="LTR"><spanlang="en-us"><font face="Calibri">The</font></span><span lang="en-us"> <font face="Calibri">“</font></span><spanlang="en-us"><font face="Calibri">obvious</font></span><span lang="en-us"><font face="Calibri">”</font></span><spanlang="en-us"><font face="Calibri"></font></span><span lang="en-us"> <font face="Calibri">solution</font></span><spanlang="en-us"><font face="Calibri"> is to</font></span><span lang="en-us"> <fontface="Calibri">get a</font></span><span lang="en-us"> <font face="Calibri">cursor</font></span><span lang="en-us"><fontface="Calibri"></font> <font face="Calibri">on the</font></span><span lang="en-us"> <font face="Calibri">query</font></span><spanlang="en-us"><font face="Calibri"> and attempt to MOVE through th</font></span><spanlang="en-us"><font face="Calibri">at cursor</font></span><span lang="en-us"><font face="Calibri"> ina loop</font></span><span lang="en-us"><font face="Calibri">,</font></span><span lang="en-us"> <font face="Calibri">usingthe row count from the SELECT to tell me when I am done</font></span><span lang="en-us"><font face="Calibri">.</font><font face="Calibri">I can then use</font></span><span lang="en-us"><font face="Calibri"> UPDATE</font></span><spanlang="en-us"> <font face="Calibri">…</font></span><span lang="en-us"><font face="Calibri"> WHERECURRENT OF</font></span><span lang="en-us"> <font face="Calibri">…</font></span><span lang="en-us"><font face="Calibri">to do the updates. This seems cumbersome</font></span><span lang="en-us"> <font face="Calibri">and inefficient</font></span><spanlang="en-us"> <font face="Calibri">to me</font></span><span lang="en-us"><font face="Calibri">.</font><font face="Calibri">Is there a better way?</font></span><span lang="en-us"></span><p dir="LTR"><spanlang="en-us"></span><p dir="LTR"><span lang="en-us"><font face="Calibri">Ideally, I</font></span><span lang="en-us"><fontface="Calibri">’</font></span><span lang="en-us"><font face="Calibri">d like to do something like:</font></span><pdir="LTR"><span lang="en-us"><font face="Courier New"> </font></span><span lang="en-us"> <font face="CourierNew">UPDATE</font> <font face="Courier New">(SELECT</font></span><span lang="en-us"> <font face="Courier New">…</font></span><spanlang="en-us"><font face="Courier New"> )</font></span><span lang="en-us"> <font face="Courier New">…</font></span><spanlang="en-us"></span><p dir="LTR"><span lang="en-us"></span><p dir="LTR"><span lang="en-us"></span><spanlang="en-us"><font face="Calibri">-- </font></span><p dir="LTR"><span lang="en-us"><font face="Calibri">PeterHeadland</font></span><p dir="LTR"><span lang="en-us"><font face="Calibri">Architect</font></span><pdir="LTR"><span lang="en-us"><font face="Calibri">Actuate Corporation</font></span><pdir="LTR"><span lang="en-us"></span>
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.
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 >
>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.
> 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 sequenceand 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 anticipatep_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 >
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 asequence 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 anticipatep_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 >> >
> 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 sequenceand 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 anticipatep_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 >
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