BUG #5695: select into duplicates oid when using order by int - Mailing list pgsql-bugs

From Andreas Barth
Subject BUG #5695: select into duplicates oid when using order by int
Date
Msg-id 201010070001.o9701GjE023985@wwwmaster.postgresql.org
Whole thread Raw
Responses Re: BUG #5695: select into duplicates oid when using order by int
List pgsql-bugs
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.

pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: PGDUMP Bug
Next
From: Tom Lane
Date:
Subject: Re: BUG #5695: select into duplicates oid when using order by int