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

On Thu, Nov 03, 2011 at 04:21:37PM +0100, Alban Hertroys wrote:
> 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.

yes, i tested it with explain.

> 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.

no. it's not a special field. just plain old "something_id", with
underscore, so it is not special in any way.

> 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?

fully reproductible, as i mentioned at the end of my original mail.

Best regards,

depesz

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