Re: Strange problem with create table as select * from table;

On 3 November 2011 09:25, hubert depesz lubaczewski <depesz(at)depesz(dot)com> wrote:
> All looks good. pg_dump of the table also doesn't show any strange problems, and is duplicate free. But:
>
> $ create table zzz as select * from sssssss.xobjects;
> SELECT
>
> $ select xobject_id, count(*) from zzz group by 1 having count(*) > 1 order by 2 desc;
>  xobject_id | count
> ------------+-------
>         -1 |    40
> (1 row)

Can you verify that these queries both do actually use a sequential
scan, and not, for some reason, an index scan? Just to rule out the
index corruption scenario.
You (or someone near you) might have disabled seqscans, for example.

Another thought: Is it possible that xobject_id just happens to be
used internally as a hidden field by Postgres or by an extension? That
would be another explanation for seeing -1 or duplicates in that
column.

If that's the case, I would have expected an error on creation of that table.

And lastly, is this behaviour after copying a table into a new one
reproducible or did it happen just once?

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.