Re: Getting rid of duplicate tables. - Mailing list pgsql-general

Tom Lane wrote:

>Jared Carr <jared@89glass.com> writes:
>
>
>>>For starters, what PG version is this?  And how many entries are there
>>>in pg_class at the moment?  (The output from "VACUUM VERBOSE pg_class"
>>>would be useful to look at.)
>>>
>>>
>
>
>
>>This is version 7.4.1 (upgraded from 7.4 about 3 weeks ago).
>>
>>
>
>Okay, that shoots down my faint hope that this was an old bug.  I am
>growing increasingly suspicious that there is some corner-case problem
>with duplicate rows in 7.4, because we've seen two or three reports now
>of problems with unexpected duplicates.  The reports all have different
>symptoms but I sense a common theme ...
>
>

Well, hopefully I can provide enough information to find out what that
theme is.

>>And the contents of pg_class relating to this table.
>>
>>live=# select * from pg_class where relname='order_to_do';
>>
>>
>
>I was just about to ask for that, but I need to see the system columns
>too:
>
>select oid,ctid,cmax,xmax,cmin,xmin,* from pg_class
>  where relname='order_to_do';
>
>

live=# select oid,ctid,cmax,xmax,cmin,xmin,* from pg_class where
relname='order_to_do';
   oid    |  ctid   | cmax |   xmax   |   cmin   |   xmin   |
relname   | relnamespace | reltype  | relowner | relam | relfilenode |
relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex |
relisshared | relkind | relnatts | relchecks | reltriggers | relukeys |
relfkeys | relrefs | relhasoids | relhaspkey | relhasrules |
relhassubclass | relacl

----------+---------+------+----------+----------+----------+-------------+--------------+----------+----------+-------+-------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+--------
 11462032 |  (27,2) |    0 |        2 |        2 | 46034931 |
order_to_do |         2200 | 11462033 |      101 |     0 |    11462032
|      506 |     59401 |      11462039 |             0 | t           |
f           | r       |        5 |         0 |           1 |        0
|        0 |       0 | t          | t          | f           |
f              |
 11462032 | (27,43) |    2 | 46034931 | 46034931 |  8051642 |
order_to_do |         2200 | 11462033 |      101 |     0 |    11462032
|      453 |     53407 |      11462039 |             0 | t           |
f           | r       |        5 |         0 |           1 |        0
|        0 |       0 | t          | t          | f           |
f              |
(2 rows)


>Also, would you check whether you get the same results from this query
>with enable_indexscan switched off?
>
>
I am pretty sure that they are the same with index scan turned off, but
here is the result for verification.

live=# set enable_indexscan=off;
SET
live=# select oid,ctid,cmax,xmax,cmin,xmin,* from pg_class where
relname='order_to_do';
   oid    |  ctid   | cmax |   xmax   |   cmin   |   xmin   |
relname   | relnamespace | reltype  | relowner | relam | relfilenode |
relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex |
relisshared | relkind | relnatts | relchecks | reltriggers | relukeys |
relfkeys | relrefs | relhasoids | relhaspkey | relhasrules |
relhassubclass | relacl

----------+---------+------+----------+----------+----------+-------------+--------------+----------+----------+-------+-------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+--------
 11462032 |  (27,2) |    0 |        2 |        2 | 46034931 |
order_to_do |         2200 | 11462033 |      101 |     0 |    11462032
|      506 |     59401 |      11462039 |             0 | t           |
f           | r       |        5 |         0 |           1 |        0
|        0 |       0 | t          | t          | f           |
f              |
 11462032 | (27,43) |    2 | 46034931 | 46034931 |  8051642 |
order_to_do |         2200 | 11462033 |      101 |     0 |    11462032
|      453 |     53407 |      11462039 |             0 | t           |
f           | r       |        5 |         0 |           1 |        0
|        0 |       0 | t          | t          | f           |
f              |
(2 rows)

thanks
Jared Carr