Re: [BUGS] BUG #6535: LEFT JOIN on large table is altering data

2012-03-19 Thread Aren Cambre
> > > Thanks. I am running this on 1 year old hardware on a wholly-encrypted C > > drive, encrypted with TrueCrypt. I suspect that I would have been alerted > > already with that or SMART if I had hardware issues? > > That sounds excessively optimistic -- not necessarily a good > characteristic on

Re: [BUGS] BUG #6535: LEFT JOIN on large table is altering data

2012-03-19 Thread Alvaro Herrera
Excerpts from Aren Cambre's message of dom mar 18 23:39:16 -0300 2012: > Thanks. I am running this on 1 year old hardware on a wholly-encrypted C > drive, encrypted with TrueCrypt. I suspect that I would have been alerted > already with that or SMART if I had hardware issues? That sounds excessiv

Re: [BUGS] BUG #6535: LEFT JOIN on large table is altering data

2012-03-18 Thread Aren Cambre
Thanks. I am running this on 1 year old hardware on a wholly-encrypted C drive, encrypted with TrueCrypt. I suspect that I would have been alerted already with that or SMART if I had hardware issues? I am on 9.1.3, although I think the data was inserted back when I was running 9.0.x. I upgraded to

Re: [BUGS] BUG #6535: LEFT JOIN on large table is altering data

2012-03-18 Thread Tom Lane
Aren Cambre writes: > Just want to again emphasize that my database state has changed, so I am > not sure this is remains a good case for finding a bug. Your description of an apparently-all-nulls row sounds a great deal like data corruption. I suspect there may be more wrong than just the one r

Re: [BUGS] BUG #6535: LEFT JOIN on large table is altering data

2012-03-18 Thread Aren Cambre
Kevin, Thanks. That was a direct copy/paste. It happened that the *UNION*ed queries spat out those results in the same order that the queries appeared. Just want to again emphasize that my database state has changed, so I am not sure this is remains a good case for finding a bug. Aren On Sun, M

Re: [BUGS] BUG #6535: LEFT JOIN on large table is altering data

2012-03-18 Thread Kevin Grittner
"Kevin Grittner" wrote: > Aren Cambre wrote: > >> SELECT COUNT(*) >> FROM consistent.master >> WHERE citation_id IS NOT NULL >> UNION >> SELECT COUNT(*) >> FROM consistent.master >> UNION >> SELECT COUNT(*) >> FROM consistent.master >> WHERE citation_id IS NULL >> >> I got this result: >> >> 2

Re: [BUGS] BUG #6535: LEFT JOIN on large table is altering data

2012-03-18 Thread Aren Cambre
Kevin, You're absolutely correct; there's no telling what order the planner will use. I did in fact run the queries separately and got the same result that I intimated in the UNIONed queries. I can no longer reproduce, however, because I have since altered the table. So if there is a bug, I may n

Re: [BUGS] BUG #6535: LEFT JOIN on large table is altering data

2012-03-18 Thread Kevin Grittner
Aren Cambre wrote: > SELECT COUNT(*) > FROM consistent.master > WHERE citation_id IS NOT NULL > UNION > SELECT COUNT(*) > FROM consistent.master > UNION > SELECT COUNT(*) > FROM consistent.master > WHERE citation_id IS NULL > > I got this result: > > 2085344 > 2085343 > 0 > > Not clear how ad

Re: [BUGS] BUG #6535: LEFT JOIN on large table is altering data

2012-03-18 Thread Aren Cambre
Thanks. While creating that very file, I discovered that 1 row had blanks in every field despite a column having a *NOT NULL* constraint and another column being a* *serial. Removing that column appears to fixed the problem. Something about that column made Postgres unhappy, though. If I ran these

Re: [BUGS] BUG #6535: LEFT JOIN on large table is altering data

2012-03-16 Thread Tom Lane
a...@arencambre.com writes: > (Note that this issue is described fully at > http://stackoverflow.com/questions/9713537/postgres-left-join-is-creating-new-data. > A few knowledgeable people have weighed in, and there is no solution > identified.) This might be a bug, but you've not provided suffici