Skip site navigation (1) Skip section navigation (2)

BUG #6535: LEFT JOIN on large table is altering data

From: aren(at)arencambre(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #6535: LEFT JOIN on large table is altering data
Date: 2012-03-16 15:03:00
Message-ID: E1S8YgG-0002hJ-SR@wrigleys.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged on the website:

Bug reference:      6535
Logged by:          Aren Cambre
Email address:      aren(at)arencambre(dot)com
PostgreSQL version: 9.1.3
Operating system:   Windows 7 x64
Description:        

(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.)

In a nutshell, I have a query LEFT JOINing table A with table B and
inserting the data into table A'. A' is a functional equivalent of table A.
Table B is actually built from a using a WITH at the top. I'm using it to
help add a rank() to the table.

Table A has just over 2 million rows.

There is a column named citation_id that has no null values. Not only does
citation_id have a NOT NULL constraint, I have verified with a query that it
has no null values.

The LEFT JOIN is pulling table A's citation_id. However, when the joined
table is inserted into table A', Postgres throws "null value in column
"citation_id" violates not-null constraint" errors.

Note that I am running Postgres 32 bit for PostGIS compatibility.


Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2012-03-16 17:17:46
Subject: Re: Regression from 8.4 to 9.1.2/9.1.3: Optimizing filters on constants in unions
Previous:From: Heikki LinnakangasDate: 2012-03-16 09:45:36
Subject: Re: Re: [PATCH] Use CC atomic builtins if available [was: Re: TAS patch for building on armel/armhf thumb]

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group