Re: Support for Limit in Update, Insert...

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(at)alvh(dot)no-ip(dot)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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461