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

On Thu, Nov 03, 2011 at 10:55:20AM -0400, Tom Lane wrote:
> hubert depesz lubaczewski <depesz(at)depesz(dot)com> writes:
> > 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:
>
> I was just going to ask you to check that. Weird as can be.
>
> Does plain old "SELECT COUNT(*)" show a difference between the two
> tables?
>
> Do you get similar misbehavior if you break the CREATE TABLE AS into a
> CREATE and an INSERT/SELECT? Also, please note the rowcount returned
> by INSERT/SELECT and see how it matches up with the tables afterwards.
>
> Does turning synchronize_seqscans off affect the behavior?

So, did some tests:

$ select count(*) from sssssss.xobjects;
count
----------
35179058
(1 row)

$ create table qqq as select * from sssssss.xobjects;
SELECT

$ select count(*) from qqq;
count
----------
35179631
(1 row)

$ select count(*) from qqq where xobject_id = -1;
count
-------
40
(1 row)

$ drop table qqq;
DROP TABLE

$ select count(*) from sssssss.xobjects;
count
----------
35182687
(1 row)

$ create table qqq ( like sssssss.xobjects );
CREATE TABLE

$ insert into qqq select * from sssssss.xobjects;
INSERT 0 35182962

$ select count(*) from qqq;
count
----------
35182962
(1 row)

$ select count(*) from qqq where xobject_id = -1;
count
-------
40
(1 row)

$ drop table qqq;
DROP TABLE

$ set synchronize_seqscans = off;
SET

$ create table qqq as select * from sssssss.xobjects;

SELECT
$ select count(*) from qqq;
count
----------
35185653
(1 row)

$ select count(*) from qqq where xobject_id = -1;
count
-------
40
(1 row)

$ drop table qqq;
DROP TABLE

$ create table qqq ( like sssssss.xobjects );
CREATE TABLE

$ insert into qqq select * from sssssss.xobjects;
INSERT 0 35188896

$ select count(*) from qqq;
count
----------
35188896
(1 row)

$ select count(*) from qqq where xobject_id = -1;
count
-------
40
(1 row)

$ drop table qqq;
DROP TABLE

as you can see counts of rows in created table are more or less
sensible, but whatever method I used - create table as, insert into,
using sychronized_scans (initially) or not (later) - copy of the table,
as long as it's in database, has 40 those "-1" rows.

one note - maybe it wasn't clear from my original mail - when I did
pg_dump of the xobjects table, it didn't have -1 rows.

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