Strange problem with create table as select * from table; - Mailing list pgsql-general
| From | hubert depesz lubaczewski |
|---|---|
| Subject | Strange problem with create table as select * from table; |
| Date | |
| Msg-id | 20111103082558.GA31748@depesz.com Whole thread Raw |
| Responses |
Re: Strange problem with create table as select * from table;
(Adrian Klaver <adrian.klaver@gmail.com>)
Re: Strange problem with create table as select * from table; (Alban Hertroys <haramrae@gmail.com>) |
| List | pgsql-general |
Hi
We have pretty weird situation, which seems to be impossible, but perhaps you'll notice something that will let me fix
theproblem.
System: SunOS 5.11 snv_130
Pg: PostgreSQL 8.4.7 on i386-pc-solaris2.11, compiled by cc: Sun C 5.10 SunOS_i386 2009/06/03, 64-bit
In there I have a table:
$ \d sssssss.xobjects
Table "sssssss.xobjects"
Column | Type | Modifiers
---------------------------+--------------------------+-----------------------------------------------------------------------
xobject_id | integer | not null default
nextval('sssssss.xobjects_xobject_id_seq'::regclass)
magic_id | integer |
xxxxxxxxxxxxxx | integer |
xxxxxxxxxxxxx | integer |
creation_tsz | timestamp with time zone |
xxxx | character varying(255) |
xxxxxxxxxx | character varying(255) |
xxxxxxxxxxx | character varying(255) |
xxxx | character varying(255) |
xxxxx | character varying(255) |
xxx | character varying(255) |
xxxxxxxxxx | integer |
xxxxxxxxxxxxxx | character varying(128) |
xxxxxxxxxxxxx | character varying(255) |
xxxxxxxxxxxxxx | character varying(24) |
xxxxxxxxxxxxxxxxxxx | text |
xxxxxxxxxxxxxxxxxx | text |
xxxxxxxx | boolean | default false
xxxxxxxxxxx | numeric(24,2) |
xxxxxxxxxxxxxxxxxxx | numeric(24,2) |
xxxxxxxxxxxxx | character varying(6) | default 'USD'::character varying
xxxxxxxxxxxxxxxxxxxx | text |
xxxxxxxxxxx | boolean | default false
xxxxxxxxxxxxxxxxx | numeric(24,2) |
xxxxxxxxxxxxxxxxxxx | character varying(6) |
xxxxxxxxxxxxxxxxxxxxxxxxx | numeric(24,2) |
xxxxxxxxxxxxxx | numeric(24,2) |
xxxxxxxxx | boolean |
xxxxxxxxxxxxxxxx | integer |
xxxxxxxxxxxxxxx | numeric(24,2) |
xxxxxxxxxxxxxxxxxxxxxxx | numeric(24,2) |
xxxxxxxxxxxxxxxxxx | numeric(24,2) |
xxxxxxxxxxxxxxxxxxxx | numeric(24,2) |
xxxxxxxxx | bigint |
xxxxxxxxxxxxxxx | numeric(24,2) |
xxxxxxxxxxxxxxxxxxx | numeric(24,2) |
xxxxxxxxxxxxxxxxxxxxx | numeric(24,2) |
xxxxxxxxxxxxxxxxx | numeric(24,2) |
xxxxxxxxxxxxxxxxxxxxx | numeric(24,2) |
xxxxxxxxxxxxxxxxxxxxxxx | numeric(24,2) |
xxxxxxxxxxxxxx | numeric(24,2) |
xxxxxxxxxxxxxxxxxx | numeric(24,2) |
xxxxxxxxxxxxxxxxxxxx | numeric(24,2) |
xxxxxxxxxxxxxxxxx | integer |
xxxxxxxxxxxxxxxxxxxxxx | text |
xxxxxxxxx | character varying(255) |
xxxxxxx | integer |
xxxxxxxxxxxxxxxx | boolean |
xxxxxxxxxxxxxxxx | numeric(24,2) |
Indexes:
"xobjects_pkey" PRIMARY KEY, btree (xobject_id)
"xobjects_creation_tsz" btree (creation_tsz)
$ select * from pg_class where oid = 'sssssss.xobjects'::regclass;
-[ RECORD 1 ]--+-------------------------------------------------------------------------------------------
relname | xobjects
relnamespace | 9868449
reltype | 7441360
relowner | 10
relam | 0
relfilenode | 334123501
reltablespace | 0
relpages | 5534109
reltuples | 3.49685e+07
reltoastrelid | 334123504
reltoastidxid | 0
relhasindex | t
relisshared | f
relistemp | f
relkind | r
relnatts | 49
relchecks | 0
relhasoids | f
relhaspkey | t
relhasrules | f
relhastriggers | t
relhassubclass | f
relfrozenxid | 75260515
relacl | {postgres=arwdDxt/postgres,developer=r/postgres,sitemaps=r/postgres,uuuuuuu_ro=r/postgres}
reloptions | [null]
$ select * from pg_stat_user_tables where relid = 'sssssss.xobjects'::regclass;
-[ RECORD 1 ]----+------------------------------
relid | 7441358
schemaname | sssssss
relname | xobjects
seq_scan | 302
seq_tup_read | 8367856283
idx_scan | 34898129
idx_tup_fetch | 2836717789
n_tup_ins | 7772954
n_tup_upd | 1
n_tup_del | 5539090
n_tup_hot_upd | 1
n_live_tup | 35068206
n_dead_tup | 137275
last_vacuum | [null]
last_autovacuum | 2011-10-30 12:29:38.853241+00
last_analyze | [null]
last_autoanalyze | 2011-10-30 06:30:28.334954+00
This table looks perfectly OK. What's important - it doesn't have any duplicates in xobject_id column:
$ select xobject_id, count(*) from sssssss.xobjects group by 1 having count(*) > 1;
xobject_id | count
------------+-------
(0 rows)
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)
$ select magic_id from zzz where xobject_id = -1 order by 1;
magic_id
----------
30343295
30343295
30408831
30408831
30408831
30539903
30605439
30605439
30670975
30670975
30670975
30802047
30867583
30933119
31195263
31195263
31260799
31326335
31588479
31588479
31588479
31654015
31719551
31785087
31785087
31785087
31850623
31850623
31850623
31850623
31981695
31981695
32047231
32047231
32112767
32309375
32374911
32440447
32505983
32505983
(40 rows)
What's interesting is that when I did it previously, couple of days ago, and sampled some randon magic_ids that I foudn
withxobject_id = -1, I got:
$ select xobject_id, magic_id from zzz where magic_id in ( 32440447, 32047231, 32505983);
xobject_id | magic_id
------------+----------
35858705 | 32505983
35793169 | 32440447
-1 | 32440447
-1 | 32047231
-1 | 32505983
-1 | 32505983
35399951 | 32047231
-1 | 32047231
(8 rows)
please note that the same ids are duplicated now too.
and if magic_id was twice with xobject_id = -1, it is the same case now!.
In base sssssss.xobjects, all those rows are just once:
$ select xobject_id, magic_id from sssssss.xobjects where magic_id in (32440447, 32047231, 32505983);
xobject_id | magic_id
------------+----------
35858705 | 32505983
35793169 | 32440447
35399951 | 32047231
(3 rows)
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 -
thesame magic_id.
Does above seem sensible for anyone? Any suggestions on what could be broken?
Best regards,
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
pgsql-general by date: