Thread: Duplicate OIDs in pg_attribute
<br />I noticed that pg_attribute has rows with the same OID! You can <br />verify this by running. <p># select count(oid),oidfrom pg_attribute group by oid having <br />count(oid) > 1; <p>We see that these duplicate OIDs appear tohappen when <br />pg_attibute rows are inserted for indexes and primary keys. <p>select relname, attname <br />from pg_attribute, pg_class <br />where attrelid = pg_class.oid and <br />pg_attribute.oid in (select oid <br /> from pg_attribute <br /> group by oid <br /> having count(oid) > 1); <p>Isee: <br /><tt> relname | attname</tt><br /><tt> ---------------------------------+--------------</tt><br/><tt> pg_aggregate_name_type_index | aggname</tt><br/><tt> pg_aggregate_name_type_index | aggbasetype</tt><br /><tt> pg_amop_opid_index | amopclaid</tt><br /><tt> pg_amop_opid_index | amopopr</tt><br /><tt> pg_amop_opid_index | amopid</tt><br /><tt> pg_amop_strategy_index | amopid</tt><br/><tt> pg_amop_strategy_index | amopclaid</tt><br /><tt> pg_amop_strategy_index | amopstrategy</tt><br /><tt> pg_attribute_relid_attnam_index | attrelid</tt><br /><tt> pg_attribute_relid_attnam_index | attname</tt><br /><tt> pg_attribute_relid_attnum_index | attrelid</tt><br/><tt> pg_attribute_relid_attnum_index | attnum</tt><br /><tt> pg_inherits_relid_seqno_index | inhrelid</tt><br /><tt> pg_inherits_relid_seqno_index | inhseqno</tt><br /><tt> pg_largeobject_loid_pn_index | loid</tt><br /><tt> pg_largeobject_loid_pn_index | pageno</tt><br/><tt> pg_listener_pid_relname_index | listenerpid</tt><br /><tt> pg_listener_pid_relname_index | relname</tt><br /><tt> pg_operator_oprname_l_r_k_index | oprname</tt><br /><tt> pg_operator_oprname_l_r_k_index | oprleft</tt><br /><tt> pg_operator_oprname_l_r_k_index | oprright</tt><br/><tt> pg_operator_oprname_l_r_k_index | oprkind</tt><br /><tt> pg_proc_proname_narg_type_index| proname</tt><br /><tt> pg_proc_proname_narg_type_index | pronargs</tt><br /><tt> pg_proc_proname_narg_type_index | proargtypes</tt><br /><tt> pg_statistic_relid_att_index | starelid</tt><br/><tt> pg_statistic_relid_att_index | staattnum</tt><br /><tt> primarytest2_pkey | col1</tt><br /><tt> primarytest2_pkey | col2</tt><br /><tt> (29 rows)</tt><p>What do people think about this issue? It doesn't seem to have <br />major consequences now,but it should probably be fixed? When <br />system tables have referential integrity enforced, then it will be an <br/>issue. Can this be put on the TODO list? <p>Regards, <p>Joe Mitchell <br />Great Bridge LLC
Joe Mitchell <jmitchell@greatbridge.com> writes: > I noticed that pg_attribute has rows with the same OID! Joe previously asked me about this off-list, and I replied thus: This appears to be due to the incredibly grotty coding used in AppendAttributeTuples in src/backend/catalog/index.c --- rather than building tuples in any of several sane fashions, it's using an unholy combination of memmove and heap_modifytuple to update a single tuple object into successive states that correspond to the rows it needs to add to the table. Unfortunately the OID assigned by the first heap_insert gets carried along to the subsequent states, so the later calls to heap_insert don't think they should assign new OIDs. Good catch, Joe! This bug has probably been there since the beginning of time. It's evidently got no serious consequences (since in reality, OID uniqueness is not assumed for this table), but it ought to be fixed. A quick-hack solution would be to zero out the tuple's OID before each heap_insert, but really AppendAttributeTuples should be rewritten to construct each tuple independently in the first place. A quick 'glimpse' shows no other uses of heap_modifytuple except to update an existing tuple, so evidently no one was foolish enough to copy this technique. I recommend putting this on the TODO for 7.2. We can't fix it now unless we want to force an initdb. regards, tom lane
> Good catch, Joe! This bug has probably been there since the beginning > of time. It's evidently got no serious consequences (since in reality, > OID uniqueness is not assumed for this table), but it ought to be fixed. > A quick-hack solution would be to zero out the tuple's OID before each > heap_insert, but really AppendAttributeTuples should be rewritten to > construct each tuple independently in the first place. > > A quick 'glimpse' shows no other uses of heap_modifytuple except to > update an existing tuple, so evidently no one was foolish enough to > copy this technique. > > I recommend putting this on the TODO for 7.2. We can't fix it now > unless we want to force an initdb. Added to TODO: * Prevent pg_attribute from having duplicate oids for indexes -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026