Re: Strange problem with create table as select * from table; - Mailing list pgsql-general

I wrote:
> Good detective work.  So now we at least have a believable theory about
> *what* is happening (something is stomping the first 8 data bytes of
> these particular rows), if not *why*.

Scratch that: something is stomping the first *six* bytes of data.
On a hunch I converted the original and bad xobject_id and magic_id
values to hex, and look at the pattern that pops out:

  badxi   |  badmi  | origxi  | origmi
----------+---------+---------+---------
 ffffffff | 1e2007f | 215a0f2 | 1e27862
 ffffffff | 1e2007f | 215da81 | 1e2b1f1
 ffffffff | 1e2007f | 215330e | 1e20a86
 ffffffff | 1e5007f | 2184b11 | 1e52281
 ffffffff | 1e5007f | 218597f | 1e530ef
 ffffffff | 1e5007f | 2184e4f | 1e525bf
 ffffffff | 1e6007f | 21939f6 | 1e61166
 ffffffff | 1e6007f | 21a1054 | 1e6e7c4
 ffffffff | 1e6007f | 219d7de | 1e6af4e
 ffffffff | 1e6007f | 219d9f6 | 1e6b166
 ffffffff | 1e8007f | 21b3861 | 1e80fd1
 ffffffff | 1e8007f | 21b361d | 1e80d8d
 ffffffff | 1f0007f | 223bde0 | 1f09528
 ffffffff | 1f0007f | 223a81a | 1f07f62

I'm assuming this is little-endian hardware, so the low-order half of
magic_id is adjacent to xobject_id.  We can see that in each case the
first six bytes are being overwritten with ff ff ff ff 7f 00, while the
high-order half of magic_id remains unchanged.

Not sure what it means yet, but this seems like confirmation of the
idea that something's stomping on the data while it passes through
CREATE TABLE AS.

BTW, did you try the separate INSERT/SELECT yet?  Does that show
corruption?

            regards, tom lane