Re: Support for Limit in Update, Insert... - Mailing list pgsql-general

From Jim C. Nasby
Subject Re: Support for Limit in Update, Insert...
Date
Msg-id 20050910000149.GS7630@pervasive.com
Whole thread Raw
In response to Re: Support for Limit in Update, Insert...  ("Jim C. Nasby" <jnasby@pervasive.com>)
List pgsql-general
On Fri, Sep 09, 2005 at 06:42:10PM -0500, Jim C. Nasby wrote:
> On Thu, Sep 08, 2005 at 10:49:25PM -0400, Tom Lane wrote:
> > Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> > > This has been discussed before, and rejected.  Please see the archives.
> >
> > For SELECT, both LIMIT and OFFSET are only well-defined in the presence
> > of an ORDER BY clause.  (One could argue that we should reject them when
> > no ORDER BY, but given that the database isn't getting changed as a side
> > effect, that's probably too anal-retentive.  When the database *is*
> > going to be changed, however, I for one like well-defined results.)
> >
> > If this proposal included adding an ORDER BY to UPDATE/DELETE, then it
> > would at least be logically consistent.  I have not seen the use-case
> > for it though.  In any case you can usually get the equivalent result
> > with something like
> >
> >     UPDATE foo SET ...
> >     WHERE pkey IN (SELECT pkey FROM foo ORDER BY ... LIMIT ...);
>
> BTW, this is a case where using ctid would make sense, though you can't:
>
> decibel=# update rrs set parent=parent+1 where ctid in (select ctid from
> rrs order by rrs_id limit 1);
> ERROR:  could not identify an ordering operator for type tid
> HINT:  Use an explicit ordering operator or modify the query.
> ERROR:  could not identify an ordering operator for type tid
> HINT:  Use an explicit ordering operator or modify the query.
> decibel=#

Actually, after trying this, curiosity took hold:
(Note that it's not actually safe to use ctid like this)

decibel=# explain analyze select * from rrs where ctid='(0,3)';
                                          QUERY PLAN
----------------------------------------------------------------------------------------------
 Tid Scan on rrs  (cost=0.00..4.01 rows=1 width=66) (actual time=0.072..0.076 rows=1 loops=1)
   Filter: (ctid = '(0,3)'::tid)
 Total runtime: 0.265 ms
(3 rows)

decibel=#

Shouldn't there be an access method that goes directly to the specified
ctid instead of doing a seqscan? Even on a small table it seems this
would be faster than a seqscan.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

pgsql-general by date:

Previous
From: "pobox@verysmall.org"
Date:
Subject: Re: PostgreSQL and XML support
Next
From: Matthew Terenzio
Date:
Subject: Re: Postgresql Hosting