Postgres INSERT INTO... SELECT violates foreign key constraint

Question

I'm having a really, really strange issue with postgres. I'm trying to generate GUIDs for business objects in my database, and I'm using a new schema for this. I've done this with several business objects already; the code I'm using here has been tested and has worked in other scenarios.

Here's the definition for the new table:

CREATE TABLE guid.public_obj
(
  guid uuid NOT NULL DEFAULT uuid_generate_v4(),
  id integer NOT NULL,
  CONSTRAINT obj_guid_pkey PRIMARY KEY (guid),
  CONSTRAINT obj_id_fkey FOREIGN KEY (id)
      REFERENCES obj (obj_id)
      ON UPDATE CASCADE ON DELETE CASCADE
)

However when I try to backfill this using the following code, I get a SQL state 23503 claiming that I'm violating the foreign key constraint.

INSERT INTO guid.public_obj (guid, id)
SELECT uuid_generate_v4(), o.obj_id
FROM obj o;

ERROR: insert or update on table "public_obj" violates foreign key constraint "obj_id_fkey"
SQL state: 23503
Detail: Key (id)=(-2) is not present in table "obj".

However, if I do a SELECT on the source table, the value is definitely present:

SELECT uuid_generate_v4(), o.obj_id
FROM obj o
WHERE obj_id = -2;

"0f218286-5b55-4836-8d70-54cfb117d836";-2

I'm baffled as to why postgres might think I'm violating the fkey constraint when I'm pulling the value directly out of the corresponding table. The only constraint on obj_id in the source table definition is that it's the primary key. It's defined as a serial; the select returns it as an integer. Please help!

are you keeping your schema's straight? I see your CREATE TABLE is creating in guid schema, but the obj table uses the default schema. ?? — pbuck
select relnamespace,relname from pg_class where relname = 'obj'; — Vao Tsun
I've got my schemas straight, yes. I'm using a new schema to hold all the guid relations because the business objects requiring guids are not all in the public schema. — Gideon

Gideon Gideon · Accepted Answer · 2017-06-20T18:13:41

Okay, apparently the reason this is failing is because unbeknownst to me the table (which, I stress, does not contain many elements) is partitioned. If I do a SELECT COUNT(*) FROM obj; it returns 348, but if I do a SELECT COUNT(*) FROM ONLY obj; it returns 44. Thus, there are two problems: first, some of the data in the table has not been partitioned correctly (there exists unpartitioned data in the parent table), and second, the data I'm interested in is split out across multiple child tables and the fkey constraint on the parent table fails because the data isn't actually in the parent table. (As a note, this is not my architecture; I'm having to work with something that's been around for quite some time.)

The partitioning is by implicit type (there are three partitions, each of which contains rows relating to a specific subtype of obj) and I think the eventual solution is going to be creating GUID tables for each of the subtypes. I'm going to have to handle the stuff that's actually in the obj table probably by selecting it into a temp table, dropping the rows from the obj table, then reinserting them so that they can be partitioned properly.

Postgres INSERT INTO... SELECT violates foreign key constraint

1 Answers