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

On Thursday, November 03, 2011 8:30:34 am hubert depesz lubaczewski wrote:
> On Thu, Nov 03, 2011 at 08:23:01AM -0700, Adrian Klaver wrote:
> > On Thursday, November 03, 2011 8:05:38 am hubert depesz lubaczewski wrote:
> > > On Thu, Nov 03, 2011 at 08:04:19AM -0700, Adrian Klaver wrote:
> > > > So just to be clear there is and never has been a -1 value for
> > > > xobject_id in the source table?
> > >
> > > yes. min value of xobject_id is 1000, and we had trigger in place on
> > > the table which logged all inserts/updates/deletes and the value -1
> > > never showed up (At least in the last couple of days, during which i
> > > was making the copies).
> > >
> > > > So a select count(*) from sssssss.xobjects where xobject_id = -1 on
> > > > the source table yields 0?
> > >
> > > yes, that's correct. both using index, and usingf seq scan.
> >
> > Hmmm. Now we await the results of the tests Tom suggested. Just a
> > thought, any other strange behavior, hiccups in the database over the
> > past couple of days?
>
> no. it's doing it's job without problems.
>
> other tests are running, but simple question - how to get number of rows
> affected from psql?

See Toms answer

>
> create table xxx as select * from xobjects;
> returns just:
> SELECT

The thing that has me puzzled is shown below from you original post:

$ 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)

Looks like multiple tuples of the same row where magic_id(s) of 32505983 and
32047231 where touched three times and 32440447 twice. For some reason the
original table is seeing only the most recent version while the CREATE AS is
pulling it and past versions. Some sort of visibility problem, exactly what is
beyond me at this point.

>
> Best regards,
>
> depesz

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com