Re: Strange problem with create table as select * from table; - Mailing list pgsql-general

On Thu, Nov 03, 2011 at 07:00:30AM -0700, Adrian Klaver wrote:
> > I also verified that there are no concurrent updates that would set
> > xobject_id to -1, so it's not a problem of isolation.
> >
> > During the night I repeated the procedure and the rows that got duplicated
> > seem to be the same - at the very least - the same magic_id.
> >
> > Does above seem sensible for anyone? Any suggestions on what could be
> > broken?
>
> Do the xobject_id values have other negative numbers or is -1 just a special
> case? The only thing I can think of is a corrupted index on xobject_id.

minimal xobject_id in source table is 1000.

index on xobject_id might be corrupted, but it doesn't explain that I
don't see duplicates with group_by/having query on xobjects, which uses
seqscan:

$ explain select xobject_id, count(*) from sssssss.xobjects group by 1 having count(*) > 1;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 GroupAggregate  (cost=11718280.34..12682724.26 rows=35070688 width=4)
   Filter: (count(*) > 1)
   ->  Sort  (cost=11718280.34..11805957.06 rows=35070688 width=4)
         Sort Key: xobject_id
         ->  Seq Scan on xobjects  (cost=0.00..5884815.88 rows=35070688 width=4)
(5 rows)

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/