Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Thu, Nov 03, 2011 at 04:21:37PM +0100, Alban Hertroys wrote:
> On 3 November 2011 09:25, hubert depesz lubaczewski <depesz@xxxxxxxxxx> 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/

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux