Re: [SQL] Updating a specific number of rows in pl/pgsql
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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Updating a specific number of rows in pl/pgsql
> 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.steh...@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 : > On Mon, 10 Aug 2009 17:52:36 -0700 > "Peter Headland" 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 | 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 > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Updating a specific number of rows in pl/pgsql
2009/8/11 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. 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.steh...@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 : >> On Mon, 10 Aug 2009 17:52:36 -0700 >> "Peter Headland" 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 | 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 >> > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
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.steh...@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 : > On Mon, 10 Aug 2009 17:52:36 -0700 > "Peter Headland" 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 | 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 > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Updating a specific number of rows in pl/pgsql
>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:da...@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" 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 | 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
Re: [SQL] Updating a specific number of rows in pl/pgsql
2009/8/11 D'Arcy J.M. Cain : > On Mon, 10 Aug 2009 17:52:36 -0700 > "Peter Headland" 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 | 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 > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Updating a specific number of rows in pl/pgsql
On Mon, 10 Aug 2009 17:52:36 -0700 "Peter Headland" 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 | 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
[SQL] Updating a specific number of rows in pl/pgsql
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
