Обсуждение: BUG #5695: select into duplicates oid when using order by int

The following bug has been logged online:

Bug reference:      5695
Logged by:          Andreas Barth
Email address:      aba+postgresql@not.so.argh.org
PostgreSQL version: 8.3.11
Operating system:   Debian Linux (amd64)
Description:        select into duplicates oid when using order by int
Details:

The following testcase fails:

prod=# create table t (name text, typ integer) with oids;
CREATE TABLE
prod=# alter table t add constraint uoida unique(oid);
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "uoida" for
table "t"
ALTER TABLE
prod=# insert into t values ('1', 1);
INSERT 111953 1
prod=# insert into t (select * from t order by typ);
ERROR:  duplicate key value violates unique constraint "uoida"
prod=#


We discussed that on IRC a bit:
01:36 < johto> hmm. 8.4 does this:  if
(resultRelationDesc->rd_rel->relhasoids) HeapTupleSetOid(tuple, InvalidOid);
 and 8.3 doesn't. any idea why?
01:36 < johto> naively, that looks like the source of this bug
01:39 < RhodiumToad> johto: you'd have to track down the commit that changed
it
01:40  * johto stretches his git-fu
01:42 < RhodiumToad>
http://git.postgresql.org/gitweb?p=postgresql.git;a=commit;h=3d02cae310deec7
ca48ada68e553bfeedbd0f638
01:42 < RhodiumToad> johto: see that
01:43 < RhodiumToad> johto: note the "can't happen before 8.4" comment
01:43 < RhodiumToad> the question is, is that comment correct about the junk
filter?
01:45 < RhodiumToad> Articate: of course it isn't.
01:49 < RhodiumToad> johto: what it looks like, pre-8.4, is that there's a
junk filter on insert only if either: there are actual junk attrs, or the
plan can return physical tuples
01:49 < RhodiumToad> johto: so,  insert ... select * from table;   gets a
junk filter because it returns physical tuples
01:49 < RhodiumToad> johto: and  insert ... select * from table order by
somecol;   doesn't
01:50 < RhodiumToad> johto: (unless somecol is something other than a simple
column reference)
01:50 < RhodiumToad> johto: the question is, how are the oids making it
through the order by
01:53 < RhodiumToad> aha
01:54 < RhodiumToad> even minimaltuples might still have an oid field.
01:54 < RhodiumToad> so the oid can get through the sort.

"Andreas Barth" <aba+postgresql@not.so.argh.org> writes:
> PostgreSQL version: 8.3.11
> Description:        select into duplicates oid when using order by int

Hmm, interesting.  I doubt we'll try to change this, since the problem
doesn't occur in 8.4 or later, and anybody still using 8.3 or before
probably values stability over corner cases like this one.

            regards, tom lane