From pheadland@actuate.com Tue Aug 11 01:07:55 2009 Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id EC695633420 for ; Mon, 10 Aug 2009 22:07:55 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 65800-03 for ; Tue, 11 Aug 2009 01:07:36 +0000 (UTC) X-Greylist: delayed 00:15:15.489622 by SQLgrey-1.7.6 Received: from wyattearp.actuate.com (mx1.actuate.com [69.1.81.237]) by mail.postgresql.org (Postfix) with SMTP id B838B633219 for ; Mon, 10 Aug 2009 22:07:44 -0300 (ADT) Received: from exchange8.actuate.com (Not Verified[192.168.11.22]) by wyattearp.actuate.com with MailMarshal (v6, 1, 4, 441) id ; Mon, 10 Aug 2009 17:52:16 -0700 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01CA1A1D.F9262712" Subject: Updating a specific number of rows in pl/pgsql Date: Mon, 10 Aug 2009 17:52:36 -0700 Message-ID: <71F491F5DA99604A80DE49424BF3D02B0C7290CA@exchange8.actuate.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Updating a specific number of rows in pl/pgsql Thread-Index: AcoaHgTtODh7OfpsQMWwHycV5sYOEw== From: "Peter Headland" To: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.554 tagged_above=-10 required=5 tests=AWL=0.555, BAYES_05=-1.11, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200908/35 X-Sequence-Number: 32944 This is a multi-part message in MIME format. ------_=_NextPart_001_01CA1A1D.F9262712 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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 =3D 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 =3D 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 ... ) ... --=20 Peter Headland Architect Actuate Corporation ------_=_NextPart_001_01CA1A1D.F9262712 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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 =3D = 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 =3D 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

------_=_NextPart_001_01CA1A1D.F9262712-- From darcy@druid.net Tue Aug 11 10:25:20 2009 Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 0ED37632C16 for ; Tue, 11 Aug 2009 07:25:20 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 70145-07 for ; Tue, 11 Aug 2009 10:25:01 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.vex.net (smaug.vex.net [208.76.104.132]) by mail.postgresql.org (Postfix) with ESMTP id 5EAD16321A5 for ; Tue, 11 Aug 2009 07:25:07 -0300 (ADT) Received: from dilbert (dilbert.druid.net [207.35.13.14]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.vex.net (Postfix) with ESMTPSA id 6AEB61706D; Tue, 11 Aug 2009 06:25:03 -0400 (EDT) Date: Tue, 11 Aug 2009 06:25:03 -0400 From: "D'Arcy J.M. Cain" To: "Peter Headland" Cc: Subject: Re: Updating a specific number of rows in pl/pgsql Message-Id: <20090811062503.86993bf6.darcy@druid.net> In-Reply-To: <71F491F5DA99604A80DE49424BF3D02B0C7290CA@exchange8.actuate.com> References: <71F491F5DA99604A80DE49424BF3D02B0C7290CA@exchange8.actuate.com> X-Mailer: Sylpheed 2.6.0 (GTK+ 2.16.1; i386-portbld-freebsd7.2) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.299 tagged_above=-10 required=5 tests=AWL=1.300, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 200908/41 X-Sequence-Number: 32950 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. From pavel.stehule@gmail.com Tue Aug 11 10:55:18 2009 Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 3F2146337B8 for ; Tue, 11 Aug 2009 07:55:18 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 74965-09 for ; Tue, 11 Aug 2009 10:55:01 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-yw0-f183.google.com (mail-yw0-f183.google.com [209.85.211.183]) by mail.postgresql.org (Postfix) with ESMTP id 593F0633655 for ; Tue, 11 Aug 2009 07:55:07 -0300 (ADT) Received: by ywh13 with SMTP id 13so4567178ywh.15 for ; Tue, 11 Aug 2009 03:55:05 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=BgqpMs8j2D03Dqj/p+wc0QkVGY2ev6c8TU+MH4J5c00=; b=i9P9LbUnnIdLtscgQltNNfLh9uFHN4PbkChYq8kThhGFyAvhU9cvnw4CKlhiM98zFq L5aw2jeCLpWoFfIw0/6JEPIy8E6hTQ22ZJ25IPk2cpVX4L2UiiNFyZt01brAdC1lz97S q0DAnnxdIeWSi4ve9ehP2ujNVCXXOyR3XMVEM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=SIdH1VXHArds7LT3tiAITuv1mGoMS5UingD5wRKGOd/KfQfLIa/x2OG6NupJS+VnCF SZp17Bt5Yfuvbw8Eb0PmTyq1eoSny/Qo8jaP6Df3GSFjBnCnJ1sKcGfCvCBipAcT6b6+ 4a90d2mTR+m+aUCmAEiUFhGxYyC5FDohGscnA= MIME-Version: 1.0 Received: by 10.150.153.21 with SMTP id a21mr10345806ybe.109.1249988105845; Tue, 11 Aug 2009 03:55:05 -0700 (PDT) In-Reply-To: <20090811062503.86993bf6.darcy@druid.net> References: <71F491F5DA99604A80DE49424BF3D02B0C7290CA@exchange8.actuate.com> <20090811062503.86993bf6.darcy@druid.net> Date: Tue, 11 Aug 2009 12:55:05 +0200 Message-ID: <162867790908110355g3b62cc56u1c74092dd01bbd8e@mail.gmail.com> Subject: Re: Updating a specific number of rows in pl/pgsql From: Pavel Stehule To: "D'Arcy J.M. Cain" Cc: Peter Headland , pgsql-sql@postgresql.org Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.524 tagged_above=-10 required=5 tests=AWL=1.075, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 200908/42 X-Sequence-Number: 32951 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: >> >> =C2=A0 SELECT * >> =C2=A0 =C2=A0FROM queue >> =C2=A0 =C2=A0WHERE id =3D p_queue_id >> =C2=A0 =C2=A0ORDER BY rank >> =C2=A0 =C2=A0LIMIT 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: >> >> =C2=A0 UPDATE queue SET assigned =3D 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: > > =C2=A0UPDATE queue SET assigned =3D TRUE > =C2=A0WHERE queue_id IN (SELECT queue_id > =C2=A0 =C2=A0FROM queue > =C2=A0 =C2=A0WHERE id =3D p_queue_id > =C2=A0 =C2=A0ORDER BY rank > =C2=A0 =C2=A0LIMIT p_number_of_items); > there are one fast trick http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_first_n_rows_re= moving p.s. replace DELETE by UPDATE regards Pavel Stehule > -- > D'Arcy J.M. Cain =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0De= mocracy is three wolves > http://www.druid.net/darcy/ =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0| =C2=A0and a sheep voting on > +1 416 425 1212 =C2=A0 =C2=A0 (DoD#0082) =C2=A0 =C2=A0(eNTP) =C2=A0 | =C2= =A0what'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 > From pheadland@actuate.com Tue Aug 11 17:03:29 2009 Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 5123E633F27 for ; Tue, 11 Aug 2009 14:03:29 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 53501-03 for ; Tue, 11 Aug 2009 17:03:16 +0000 (UTC) X-Greylist: delayed 00:07:12.061534 by SQLgrey-1.7.6 Received: from mx3.actuate.com (unknown [212.140.245.163]) by mail.postgresql.org (Postfix) with ESMTP id C0832634020 for ; Tue, 11 Aug 2009 14:02:30 -0300 (ADT) Received: from exchange9.actuate.com ([192.168.213.9]) by mx3.actuate.com with Microsoft SMTPSVC(5.0.2195.6713); Tue, 11 Aug 2009 17:55:31 +0100 Received: from exchange8.actuate.com ([192.168.11.22]) by exchange9.actuate.com with Microsoft SMTPSVC(6.0.3790.3959); Tue, 11 Aug 2009 17:55:16 +0100 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Subject: Re: Updating a specific number of rows in pl/pgsql Date: Tue, 11 Aug 2009 09:55:36 -0700 Message-ID: <71F491F5DA99604A80DE49424BF3D02B0C7292D8@exchange8.actuate.com> In-Reply-To: <20090811062503.86993bf6.darcy@druid.net> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [SQL] Updating a specific number of rows in pl/pgsql Thread-Index: AcoabiUHL3IrTRnTS2iF7BjJ6NZBAAANgh7g References: <71F491F5DA99604A80DE49424BF3D02B0C7290CA@exchange8.actuate.com> <20090811062503.86993bf6.darcy@druid.net> From: "Peter Headland" To: "D'Arcy J.M. Cain" Cc: X-OriginalArrivalTime: 11 Aug 2009 16:55:16.0830 (UTC) FILETIME=[809E57E0:01CA1AA4] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.01 tagged_above=-10 required=5 tests=BAYES_05=-1.11, RDNS_NONE=0.1 X-Spam-Level: X-Archive-Number: 200908/45 X-Sequence-Number: 32954 >Assuming that there is a unique identifier on queue Alas, there is not. The PK is made up of 4 columns. --=20 Peter Headland Architect Actuate Corporation -----Original Message----- From: D'Arcy J.M. Cain [mailto:darcy@druid.net]=20 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: >=20 > SELECT * > FROM queue > WHERE id =3D p_queue_id > ORDER BY rank > LIMIT p_number_of_items; >=20 > Of course, there may not be p_number_of_items available in the queue. >=20 > I want to update all the rows in the cursor in the same way: >=20 > UPDATE queue SET assigned =3D 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 =3D TRUE WHERE queue_id IN (SELECT queue_id FROM queue WHERE id =3D p_queue_id ORDER BY rank LIMIT p_number_of_items); --=20 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. From pheadland@actuate.com Tue Aug 11 17:05:25 2009 Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id B9CAB633EFF for ; Tue, 11 Aug 2009 14:05:25 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 54329-01 for ; Tue, 11 Aug 2009 17:05:13 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mx3.actuate.com (unknown [212.140.245.163]) by mail.postgresql.org (Postfix) with ESMTP id C5124633189 for ; Tue, 11 Aug 2009 14:05:09 -0300 (ADT) Received: from exchange9.actuate.com ([192.168.213.9]) by mx3.actuate.com with Microsoft SMTPSVC(5.0.2195.6713); Tue, 11 Aug 2009 18:05:23 +0100 Received: from exchange8.actuate.com ([192.168.11.22]) by exchange9.actuate.com with Microsoft SMTPSVC(6.0.3790.3959); Tue, 11 Aug 2009 18:05:08 +0100 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: base64 Subject: Re: Updating a specific number of rows in pl/pgsql Date: Tue, 11 Aug 2009 10:05:28 -0700 Message-ID: <71F491F5DA99604A80DE49424BF3D02B0C7292EF@exchange8.actuate.com> In-Reply-To: <162867790908110355g3b62cc56u1c74092dd01bbd8e@mail.gmail.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [SQL] Updating a specific number of rows in pl/pgsql Thread-Index: AcoackW71t86cEE5QESjoqXQcTW6SgAMlqRQ References: <71F491F5DA99604A80DE49424BF3D02B0C7290CA@exchange8.actuate.com> <20090811062503.86993bf6.darcy@druid.net> <162867790908110355g3b62cc56u1c74092dd01bbd8e@mail.gmail.com> From: "Peter Headland" To: "Pavel Stehule" , "D'Arcy J.M. Cain" Cc: X-OriginalArrivalTime: 11 Aug 2009 17:05:08.0990 (UTC) FILETIME=[E192C9E0:01CA1AA5] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.755 tagged_above=-10 required=5 tests=AWL=0.745, BAYES_00=-2.599, RDNS_NONE=0.1 X-Spam-Level: X-Archive-Number: 200908/46 X-Sequence-Number: 32955 PiB0aGVyZSBhcmUgb25lIGZhc3QgdHJpY2sNCj4gaHR0cDovL3d3dy5wb3N0Z3Jlcy5jei9pbmRl eC5waHAvUG9zdGdyZVNRTF9TUUxfVHJpY2tzI0Zhc3RfZmlyc3Rfbl9yb3dzX3JlbW92aW5nDQoN ClRoYW5rcyAtIHRoYXQncyBhIHZlcnkgdXNlZnVsIHBhZ2UhDQoNClVuZm9ydHVuYXRlbHksIHRo ZXJlIGlzIG5vIHNpbmdsZSBjb2x1bW4gdGhhdCBwcm92aWRlcyBhIHVuaXF1ZSBpZCwgYW5kIEkg YW0gcmVsdWN0YW50IHRvIGFkZCBvbmUgKGZvciBleGFtcGxlLCB1c2luZyBhIHNlcXVlbmNlIGFu ZCBhIG5ldyBpbmRleCkgZm9yIHBlcmZvcm1hbmNlIHJlYXNvbnMuDQoNCkdpdmVuIHRoYXQgYWRk aXRpb25hbCBjb25zdHJhaW50LCBpcyBteSBvcmlnaW5hbCBwbGFuIHVzaW5nIGEgbG9vcCB0byBp dGVyYXRlIG92ZXIgYSBjdXJzb3IgcmVhc29uYWJsZT8gSSBkb24ndCBhbnRpY2lwYXRlIHBfbnVt YmVyX29mX2l0ZW1zIGJlaW5nIG1vcmUgdGhhbiAyMC4NCg0KLS0gDQpQZXRlciBIZWFkbGFuZA0K QXJjaGl0ZWN0DQpBY3R1YXRlIENvcnBvcmF0aW9uDQoNCg0KLS0tLS1PcmlnaW5hbCBNZXNzYWdl LS0tLS0NCkZyb206IFBhdmVsIFN0ZWh1bGUgW21haWx0bzpwYXZlbC5zdGVodWxlQGdtYWlsLmNv bV0gDQpTZW50OiBUdWVzZGF5LCBBdWd1c3QgMTEsIDIwMDkgMDM6NTUNClRvOiBEJ0FyY3kgSi5N LiBDYWluDQpDYzogUGV0ZXIgSGVhZGxhbmQ7IHBnc3FsLXNxbEBwb3N0Z3Jlc3FsLm9yZw0KU3Vi amVjdDogUmU6IFtTUUxdIFVwZGF0aW5nIGEgc3BlY2lmaWMgbnVtYmVyIG9mIHJvd3MgaW4gcGwv cGdzcWwNCg0KMjAwOS84LzExIEQnQXJjeSBKLk0uIENhaW4gPGRhcmN5QGRydWlkLm5ldD46DQo+ IE9uIE1vbiwgMTAgQXVnIDIwMDkgMTc6NTI6MzYgLTA3MDANCj4gIlBldGVyIEhlYWRsYW5kIiA8 cGhlYWRsYW5kQGFjdHVhdGUuY29tPiB3cm90ZToNCj4+IEkgY2FuIGdldCB0aGUgcm93cyBJIHdh bnQgdG8gdXBkYXRlIGxpa2UgdGhpczoNCj4+DQo+PiDCoCBTRUxFQ1QgKg0KPj4gwqAgwqBGUk9N IHF1ZXVlDQo+PiDCoCDCoFdIRVJFIGlkID0gcF9xdWV1ZV9pZA0KPj4gwqAgwqBPUkRFUiBCWSBy YW5rDQo+PiDCoCDCoExJTUlUIHBfbnVtYmVyX29mX2l0ZW1zOw0KPj4NCj4+IE9mIGNvdXJzZSwg dGhlcmUgbWF5IG5vdCBiZSBwX251bWJlcl9vZl9pdGVtcyBhdmFpbGFibGUgaW4gdGhlIHF1ZXVl Lg0KPj4NCj4+IEkgd2FudCB0byB1cGRhdGUgYWxsIHRoZSByb3dzIGluIHRoZSBjdXJzb3IgaW4g dGhlIHNhbWUgd2F5Og0KPj4NCj4+IMKgIFVQREFURSBxdWV1ZSBTRVQgYXNzaWduZWQgPSBUUlVF Ow0KPg0KPiBBc3N1bWluZyB0aGF0IHRoZXJlIGlzIGEgdW5pcXVlIGlkZW50aWZpZXIgb24gcXVl dWUsIGxldCdzIGNhbGwgaXQNCj4gcXVldWVfaWQsIHlvdSBzaG91bGQgYmUgYWJsZSB0byBkbyBz b21ldGhpbmcgbGlrZSB0aGlzOg0KPg0KPiDCoFVQREFURSBxdWV1ZSBTRVQgYXNzaWduZWQgPSBU UlVFDQo+IMKgV0hFUkUgcXVldWVfaWQgSU4gKFNFTEVDVCBxdWV1ZV9pZA0KPiDCoCDCoEZST00g cXVldWUNCj4gwqAgwqBXSEVSRSBpZCA9IHBfcXVldWVfaWQNCj4gwqAgwqBPUkRFUiBCWSByYW5r DQo+IMKgIMKgTElNSVQgcF9udW1iZXJfb2ZfaXRlbXMpOw0KPg0KDQp0aGVyZSBhcmUgb25lIGZh c3QgdHJpY2sNCmh0dHA6Ly93d3cucG9zdGdyZXMuY3ovaW5kZXgucGhwL1Bvc3RncmVTUUxfU1FM X1RyaWNrcyNGYXN0X2ZpcnN0X25fcm93c19yZW1vdmluZw0KDQpwLnMuIHJlcGxhY2UgREVMRVRF IGJ5IFVQREFURQ0KcmVnYXJkcw0KUGF2ZWwgU3RlaHVsZQ0KDQo+IC0tDQo+IEQnQXJjeSBKLk0u IENhaW4gPGRhcmN5QGRydWlkLm5ldD4gwqAgwqAgwqAgwqAgfCDCoERlbW9jcmFjeSBpcyB0aHJl ZSB3b2x2ZXMNCj4gaHR0cDovL3d3dy5kcnVpZC5uZXQvZGFyY3kvIMKgIMKgIMKgIMKgIMKgIMKg IMKgIMKgfCDCoGFuZCBhIHNoZWVwIHZvdGluZyBvbg0KPiArMSA0MTYgNDI1IDEyMTIgwqAgwqAg KERvRCMwMDgyKSDCoCDCoChlTlRQKSDCoCB8IMKgd2hhdCdzIGZvciBkaW5uZXIuDQo+DQo+IC0t DQo+IFNlbnQgdmlhIHBnc3FsLXNxbCBtYWlsaW5nIGxpc3QgKHBnc3FsLXNxbEBwb3N0Z3Jlc3Fs Lm9yZykNCj4gVG8gbWFrZSBjaGFuZ2VzIHRvIHlvdXIgc3Vic2NyaXB0aW9uOg0KPiBodHRwOi8v d3d3LnBvc3RncmVzcWwub3JnL21haWxwcmVmL3Bnc3FsLXNxbA0KPg0K From pavel.stehule@gmail.com Tue Aug 11 17:23:07 2009 Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id CE608633D46 for ; Tue, 11 Aug 2009 14:23:07 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 03188-01 for ; Tue, 11 Aug 2009 17:23:00 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-gx0-f217.google.com (mail-gx0-f217.google.com [209.85.217.217]) by mail.postgresql.org (Postfix) with ESMTP id 3133F63385A for ; Tue, 11 Aug 2009 14:22:56 -0300 (ADT) Received: by gxk17 with SMTP id 17so4639983gxk.19 for ; Tue, 11 Aug 2009 10:22:55 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=in6nnBVV9BLobtvAqO8zRLq0cRG1CDXb2QnrwaxrUbc=; b=nP+obt5YW9aTVgjj1EnDvFSWSk3ZzZNsp9UIwfPJmm2zTCRbxeskpkHCy2XAqgDhkL Q2mvHnZucQyyI6nT5Cc7pNfALXlKEMFWlQoK2WKIB7ARr2HN0AV9lDUVp66+ML1M8YQF Bx6/6hrxRmdLR5zIC4vmULoceKnvVQee2nreI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=GfZmmnsBxRkb7A8Lk4/S3O3GyK37HdootLR5NFGzUUGqTa4c5y8zgi0QC3G0QSQBeJ 8ZVVs/RpGs8esrMoEouL/dB8vyaMB/4esfW1rg67wHl9EWByoxlgdDaSLGCY3I5rX8yw YoYBwjQi8b38DR/SPz10nOr0/cte4BKxOYkkc= MIME-Version: 1.0 Received: by 10.150.215.10 with SMTP id n10mr11039574ybg.233.1250011375484; Tue, 11 Aug 2009 10:22:55 -0700 (PDT) In-Reply-To: <71F491F5DA99604A80DE49424BF3D02B0C7292EF@exchange8.actuate.com> References: <71F491F5DA99604A80DE49424BF3D02B0C7290CA@exchange8.actuate.com> <20090811062503.86993bf6.darcy@druid.net> <162867790908110355g3b62cc56u1c74092dd01bbd8e@mail.gmail.com> <71F491F5DA99604A80DE49424BF3D02B0C7292EF@exchange8.actuate.com> Date: Tue, 11 Aug 2009 19:22:55 +0200 Message-ID: <162867790908111022y48e65d31kcf5a037c1b41857f@mail.gmail.com> Subject: Re: Updating a specific number of rows in pl/pgsql From: Pavel Stehule To: Peter Headland Cc: "D'Arcy J.M. Cain" , pgsql-sql@postgresql.org Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 200908/47 X-Sequence-Number: 32956 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) fo= r performance reasons. ctid is unique system column in every table. postgres=3D# create table x(a int); CREATE TABLE Time: 655,062 ms postgres=3D# insert into x values(10); INSERT 0 1 Time: 49,237 ms postgres=3D# insert into x values(10); INSERT 0 1 Time: 1,740 ms postgres=3D# 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 ite= rate over a cursor reasonable? I don't anticipate p_number_of_items being m= ore 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 : >> On Mon, 10 Aug 2009 17:52:36 -0700 >> "Peter Headland" wrote: >>> I can get the rows I want to update like this: >>> >>> =C2=A0 SELECT * >>> =C2=A0 =C2=A0FROM queue >>> =C2=A0 =C2=A0WHERE id =3D p_queue_id >>> =C2=A0 =C2=A0ORDER BY rank >>> =C2=A0 =C2=A0LIMIT 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: >>> >>> =C2=A0 UPDATE queue SET assigned =3D 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: >> >> =C2=A0UPDATE queue SET assigned =3D TRUE >> =C2=A0WHERE queue_id IN (SELECT queue_id >> =C2=A0 =C2=A0FROM queue >> =C2=A0 =C2=A0WHERE id =3D p_queue_id >> =C2=A0 =C2=A0ORDER BY rank >> =C2=A0 =C2=A0LIMIT 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 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0D= emocracy is three wolves >> http://www.druid.net/darcy/ =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0| =C2=A0and a sheep voting on >> +1 416 425 1212 =C2=A0 =C2=A0 (DoD#0082) =C2=A0 =C2=A0(eNTP) =C2=A0 | = =C2=A0what'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 >> > From pheadland@actuate.com Tue Aug 11 17:26:47 2009 Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 2CC40633D46 for ; Tue, 11 Aug 2009 14:26:47 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 05444-02 for ; Tue, 11 Aug 2009 17:26:42 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from wyattearp.actuate.com (mx1.actuate.com [69.1.81.237]) by mail.postgresql.org (Postfix) with SMTP id 0656163385A for ; Tue, 11 Aug 2009 14:26:38 -0300 (ADT) Received: from exchange8.actuate.com (Not Verified[192.168.11.22]) by wyattearp.actuate.com with MailMarshal (v6, 1, 4, 441) id ; Tue, 11 Aug 2009 10:24:43 -0700 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: base64 Subject: Re: Updating a specific number of rows in pl/pgsql Date: Tue, 11 Aug 2009 10:24:54 -0700 Message-ID: <71F491F5DA99604A80DE49424BF3D02B0C729313@exchange8.actuate.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [SQL] Updating a specific number of rows in pl/pgsql Thread-Index: AcoackW71t86cEE5QESjoqXQcTW6SgAMlqRQAADu2dA= References: <71F491F5DA99604A80DE49424BF3D02B0C7290CA@exchange8.actuate.com> <20090811062503.86993bf6.darcy@druid.net> <162867790908110355g3b62cc56u1c74092dd01bbd8e@mail.gmail.com> From: "Peter Headland" To: "Peter Headland" , "Pavel Stehule" , "D'Arcy J.M. Cain" Cc: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 200908/48 X-Sequence-Number: 32957 PiBVbmZvcnR1bmF0ZWx5LCB0aGVyZSBpcyBubyBzaW5nbGUgY29sdW1uIHRoYXQgcHJvdmlkZXMg YSB1bmlxdWUgaWQuDQoNCkNvcnJlY3Rpb24gLSBJIGRpZCBub3QgdW5kZXJzdGFuZCB3aGF0IGN0 aWQgd2FzLCBidXQgbm93IEkgZG8sIHNvIEkgd2lsbCB0cnkgeW91ciB0aXAuDQoNCi0tIA0KUGV0 ZXIgSGVhZGxhbmQNCkFyY2hpdGVjdA0KQWN0dWF0ZSBDb3Jwb3JhdGlvbg0KDQoNCi0tLS0tT3Jp Z2luYWwgTWVzc2FnZS0tLS0tDQpGcm9tOiBQZXRlciBIZWFkbGFuZCANClNlbnQ6IFR1ZXNkYXks IEF1Z3VzdCAxMSwgMjAwOSAxMDowNQ0KVG86ICdQYXZlbCBTdGVodWxlJzsgRCdBcmN5IEouTS4g Q2Fpbg0KQ2M6IHBnc3FsLXNxbEBwb3N0Z3Jlc3FsLm9yZw0KU3ViamVjdDogUkU6IFtTUUxdIFVw ZGF0aW5nIGEgc3BlY2lmaWMgbnVtYmVyIG9mIHJvd3MgaW4gcGwvcGdzcWwNCg0KPiB0aGVyZSBh cmUgb25lIGZhc3QgdHJpY2sNCj4gaHR0cDovL3d3dy5wb3N0Z3Jlcy5jei9pbmRleC5waHAvUG9z dGdyZVNRTF9TUUxfVHJpY2tzI0Zhc3RfZmlyc3Rfbl9yb3dzX3JlbW92aW5nDQoNClRoYW5rcyAt IHRoYXQncyBhIHZlcnkgdXNlZnVsIHBhZ2UhDQoNClVuZm9ydHVuYXRlbHksIHRoZXJlIGlzIG5v IHNpbmdsZSBjb2x1bW4gdGhhdCBwcm92aWRlcyBhIHVuaXF1ZSBpZCwgYW5kIEkgYW0gcmVsdWN0 YW50IHRvIGFkZCBvbmUgKGZvciBleGFtcGxlLCB1c2luZyBhIHNlcXVlbmNlIGFuZCBhIG5ldyBp bmRleCkgZm9yIHBlcmZvcm1hbmNlIHJlYXNvbnMuDQoNCkdpdmVuIHRoYXQgYWRkaXRpb25hbCBj b25zdHJhaW50LCBpcyBteSBvcmlnaW5hbCBwbGFuIHVzaW5nIGEgbG9vcCB0byBpdGVyYXRlIG92 ZXIgYSBjdXJzb3IgcmVhc29uYWJsZT8gSSBkb24ndCBhbnRpY2lwYXRlIHBfbnVtYmVyX29mX2l0 ZW1zIGJlaW5nIG1vcmUgdGhhbiAyMC4NCg0KLS0gDQpQZXRlciBIZWFkbGFuZA0KQXJjaGl0ZWN0 DQpBY3R1YXRlIENvcnBvcmF0aW9uDQoNCg0KLS0tLS1PcmlnaW5hbCBNZXNzYWdlLS0tLS0NCkZy b206IFBhdmVsIFN0ZWh1bGUgW21haWx0bzpwYXZlbC5zdGVodWxlQGdtYWlsLmNvbV0gDQpTZW50 OiBUdWVzZGF5LCBBdWd1c3QgMTEsIDIwMDkgMDM6NTUNClRvOiBEJ0FyY3kgSi5NLiBDYWluDQpD YzogUGV0ZXIgSGVhZGxhbmQ7IHBnc3FsLXNxbEBwb3N0Z3Jlc3FsLm9yZw0KU3ViamVjdDogUmU6 IFtTUUxdIFVwZGF0aW5nIGEgc3BlY2lmaWMgbnVtYmVyIG9mIHJvd3MgaW4gcGwvcGdzcWwNCg0K MjAwOS84LzExIEQnQXJjeSBKLk0uIENhaW4gPGRhcmN5QGRydWlkLm5ldD46DQo+IE9uIE1vbiwg MTAgQXVnIDIwMDkgMTc6NTI6MzYgLTA3MDANCj4gIlBldGVyIEhlYWRsYW5kIiA8cGhlYWRsYW5k QGFjdHVhdGUuY29tPiB3cm90ZToNCj4+IEkgY2FuIGdldCB0aGUgcm93cyBJIHdhbnQgdG8gdXBk YXRlIGxpa2UgdGhpczoNCj4+DQo+PiDCoCBTRUxFQ1QgKg0KPj4gwqAgwqBGUk9NIHF1ZXVlDQo+ PiDCoCDCoFdIRVJFIGlkID0gcF9xdWV1ZV9pZA0KPj4gwqAgwqBPUkRFUiBCWSByYW5rDQo+PiDC oCDCoExJTUlUIHBfbnVtYmVyX29mX2l0ZW1zOw0KPj4NCj4+IE9mIGNvdXJzZSwgdGhlcmUgbWF5 IG5vdCBiZSBwX251bWJlcl9vZl9pdGVtcyBhdmFpbGFibGUgaW4gdGhlIHF1ZXVlLg0KPj4NCj4+ IEkgd2FudCB0byB1cGRhdGUgYWxsIHRoZSByb3dzIGluIHRoZSBjdXJzb3IgaW4gdGhlIHNhbWUg d2F5Og0KPj4NCj4+IMKgIFVQREFURSBxdWV1ZSBTRVQgYXNzaWduZWQgPSBUUlVFOw0KPg0KPiBB c3N1bWluZyB0aGF0IHRoZXJlIGlzIGEgdW5pcXVlIGlkZW50aWZpZXIgb24gcXVldWUsIGxldCdz IGNhbGwgaXQNCj4gcXVldWVfaWQsIHlvdSBzaG91bGQgYmUgYWJsZSB0byBkbyBzb21ldGhpbmcg bGlrZSB0aGlzOg0KPg0KPiDCoFVQREFURSBxdWV1ZSBTRVQgYXNzaWduZWQgPSBUUlVFDQo+IMKg V0hFUkUgcXVldWVfaWQgSU4gKFNFTEVDVCBxdWV1ZV9pZA0KPiDCoCDCoEZST00gcXVldWUNCj4g wqAgwqBXSEVSRSBpZCA9IHBfcXVldWVfaWQNCj4gwqAgwqBPUkRFUiBCWSByYW5rDQo+IMKgIMKg TElNSVQgcF9udW1iZXJfb2ZfaXRlbXMpOw0KPg0KDQp0aGVyZSBhcmUgb25lIGZhc3QgdHJpY2sN Cmh0dHA6Ly93d3cucG9zdGdyZXMuY3ovaW5kZXgucGhwL1Bvc3RncmVTUUxfU1FMX1RyaWNrcyNG YXN0X2ZpcnN0X25fcm93c19yZW1vdmluZw0KDQpwLnMuIHJlcGxhY2UgREVMRVRFIGJ5IFVQREFU RQ0KcmVnYXJkcw0KUGF2ZWwgU3RlaHVsZQ0KDQo+IC0tDQo+IEQnQXJjeSBKLk0uIENhaW4gPGRh cmN5QGRydWlkLm5ldD4gwqAgwqAgwqAgwqAgfCDCoERlbW9jcmFjeSBpcyB0aHJlZSB3b2x2ZXMN Cj4gaHR0cDovL3d3dy5kcnVpZC5uZXQvZGFyY3kvIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgfCDC oGFuZCBhIHNoZWVwIHZvdGluZyBvbg0KPiArMSA0MTYgNDI1IDEyMTIgwqAgwqAgKERvRCMwMDgy KSDCoCDCoChlTlRQKSDCoCB8IMKgd2hhdCdzIGZvciBkaW5uZXIuDQo+DQo+IC0tDQo+IFNlbnQg dmlhIHBnc3FsLXNxbCBtYWlsaW5nIGxpc3QgKHBnc3FsLXNxbEBwb3N0Z3Jlc3FsLm9yZykNCj4g VG8gbWFrZSBjaGFuZ2VzIHRvIHlvdXIgc3Vic2NyaXB0aW9uOg0KPiBodHRwOi8vd3d3LnBvc3Rn cmVzcWwub3JnL21haWxwcmVmL3Bnc3FsLXNxbA0KPg0K From sahapasci@gmail.com Wed Aug 12 07:50:53 2009 Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 38BBB632B7F for ; Wed, 12 Aug 2009 04:50:53 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 29182-08 for ; Wed, 12 Aug 2009 07:50:37 +0000 (UTC) Received: from news.hub.org (news.hub.org [200.46.204.72]) by mail.postgresql.org (Postfix) with ESMTP id 475FC63223F for ; Wed, 12 Aug 2009 04:50:41 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.14.3/8.14.3) with ESMTP id n7C7ocev082919 for ; Wed, 12 Aug 2009 04:50:38 -0300 (ADT) (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.14.3/8.14.3/Submit) id n7C7YXb5056850 for pgsql-sql@postgresql.org; Wed, 12 Aug 2009 04:34:33 -0300 (ADT) (envelope-from news) From: Xharon X-Newsgroups: pgsql.sql Subject: Re: Updating a specific number of rows in pl/pgsql Date: Wed, 12 Aug 2009 00:34:35 -0700 (PDT) Organization: http://groups.google.com Lines: 3 Message-ID: <9018c803-65e4-4e65-950e-c3f4d8fa7327@a13g2000yqc.googlegroups.com> References: <71F491F5DA99604A80DE49424BF3D02B0C7290CA@exchange8.actuate.com> <20090811062503.86993bf6.darcy@druid.net> <162867790908110355g3b62cc56u1c74092dd01bbd8e@mail.gmail.com> <71F491F5DA99604A80DE49424BF3D02B0C729313@exchange8.actuate.com> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Complaints-To: groups-abuse@google.com Complaints-To: groups-abuse@google.com Injection-Info: a13g2000yqc.googlegroups.com; posting-host=94.55.115.254; posting-account=Q9o68goAAACr7qwYJf8JRNVPdPZeabuS User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; Trident/4.0; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 1.1.4322; .NET CLR 3.5.21022; .NET CLR 3.5.30729; .NET CLR 3.0.30618; MS-RTC LM 8),gzip(gfe),gzip(gfe) To: pgsql-sql@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=-10 required=5 tests=BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 200908/63 X-Sequence-Number: 32972 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