I noticed that pg_attribute has rows with the same OID!  You can
verify this by running.

# select count(oid),oid from pg_attribute group by oid having
count(oid) > 1;

We see that these duplicate OIDs appear to happen when
pg_attibute rows are inserted for indexes and primary keys.

select relname, attname
from pg_attribute , pg_class
where attrelid = pg_class.oid and
pg_attribute.oid in (select oid
                from pg_attribute
                group by oid
                having count(oid) > 1);

I see:
                     relname             |   attname
        ---------------------------------+--------------
         pg_aggregate_name_type_index    | aggname
         pg_aggregate_name_type_index    | aggbasetype
         pg_amop_opid_index              | amopclaid
         pg_amop_opid_index              | amopopr
         pg_amop_opid_index              | amopid
         pg_amop_strategy_index          | amopid
         pg_amop_strategy_index          | amopclaid
         pg_amop_strategy_index          | amopstrategy
         pg_attribute_relid_attnam_index | attrelid
         pg_attribute_relid_attnam_index | attname
         pg_attribute_relid_attnum_index | attrelid
         pg_attribute_relid_attnum_index | attnum
         pg_inherits_relid_seqno_index   | inhrelid
         pg_inherits_relid_seqno_index   | inhseqno
         pg_largeobject_loid_pn_index    | loid
         pg_largeobject_loid_pn_index    | pageno
         pg_listener_pid_relname_index   | listenerpid
         pg_listener_pid_relname_index   | relname
         pg_operator_oprname_l_r_k_index | oprname
         pg_operator_oprname_l_r_k_index | oprleft
         pg_operator_oprname_l_r_k_index | oprright
         pg_operator_oprname_l_r_k_index | oprkind
         pg_proc_proname_narg_type_index | proname
         pg_proc_proname_narg_type_index | pronargs
         pg_proc_proname_narg_type_index | proargtypes
         pg_statistic_relid_att_index    | starelid
         pg_statistic_relid_att_index    | staattnum
         primarytest2_pkey               | col1
         primarytest2_pkey               | col2
        (29 rows)

What do people think about this issue?  It doesn't seem to have
major consequences now, but it should probably be fixed?  When
system tables have referential integrity enforced, then it will be an
issue.  Can this be put on the TODO list?

Regards,

Joe Mitchell
Great Bridge LLC


 
I noticed that pg_attribute has rows with the same OID!  You can
verify this by running.
# select count(oid),oid from pg_attribute group by oid having
count(oid) > 1;
We see that these duplicate OIDs appear to happen when
pg_attibute rows are inserted for indexes and primary keys.
select relname, attname
from pg_attribute , pg_class
where attrelid = pg_class.oid and
pg_attribute.oid in (select oid
               
from pg_attribute
               
group by oid
               
having count(oid) > 1);
I see:
                    
relname            
|   attname
        ---------------------------------+--------------
         pg_aggregate_name_type_index   
| aggname
         pg_aggregate_name_type_index   
| aggbasetype
         pg_amop_opid_index             
| amopclaid
         pg_amop_opid_index             
| amopopr
         pg_amop_opid_index             
| amopid
         pg_amop_strategy_index         
| amopid
         pg_amop_strategy_index         
| amopclaid
         pg_amop_strategy_index         
| amopstrategy
         pg_attribute_relid_attnam_index
| attrelid
         pg_attribute_relid_attnam_index
| attname
         pg_attribute_relid_attnum_index
| attrelid
         pg_attribute_relid_attnum_index
| attnum
         pg_inherits_relid_seqno_index  
| inhrelid
         pg_inherits_relid_seqno_index  
| inhseqno
         pg_largeobject_loid_pn_index   
| loid
         pg_largeobject_loid_pn_index   
| pageno
         pg_listener_pid_relname_index  
| listenerpid
         pg_listener_pid_relname_index  
| relname
         pg_operator_oprname_l_r_k_index
| oprname
         pg_operator_oprname_l_r_k_index
| oprleft
         pg_operator_oprname_l_r_k_index
| oprright
         pg_operator_oprname_l_r_k_index
| oprkind
         pg_proc_proname_narg_type_index
| proname
         pg_proc_proname_narg_type_index
| pronargs
         pg_proc_proname_narg_type_index
| proargtypes
         pg_statistic_relid_att_index   
| starelid
         pg_statistic_relid_att_index   
| staattnum
         primarytest2_pkey              
| col1
         primarytest2_pkey              
| col2
        (29 rows)
What do people think about this issue?  It doesn't seem to have
major consequences now, but it should probably be fixed?  When
system tables have referential integrity enforced, then it will be
an
issue.  Can this be put on the TODO list?
Regards,
Joe Mitchell
Great Bridge LLC
Tue, 06 Feb 2001 14:52:47 -0500
Joe Mitchell <jmitchell@greatbridge.com>
Duplicate OIDs in pg_attribute
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

Tue, 06 Feb 2001 19:46:44 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Duplicate OIDs in pg_attribute 
> 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, Pennsylvania 19026

Tue, 6 Feb 2001 19:52:27 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Duplicate OIDs in pg_attribute