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.