Re: ID column naming convention

On 10/22/15 11:37 AM, Karsten Hilbert wrote:
> On Mon, Oct 19, 2015 at 04:25:39AM -0500, Jim Nasby wrote:
>
>> BTW, if there's any interest, I have code that sets up a non-inheritance
>> table specifically for doing foreign keys to the inheritance parent. It
>> doesn't support updates right now, but should be fully safe from a FK
>> standpoint.
>
> Can you tell me more ?

I create a '_fk' table that looks like this:

> CREATE TABLE _lead.lead_fk(
> lead_id int NOT NULL PRIMARY KEY
> , organic_lead_id int CONSTRAINT organic_lead_id_sanity CHECK( organic_lead_id IS NULL OR organic_lead_id = lead_id )
> , some_lead_provider_lead_id int CONSTRAINT some_lead_provider_lead_id_sanity CHECK( some_lead_provider_lead_id IS NULL OR some_lead_provider_lead_id = lead_id )
> -- TODO , CHECK( only one blah_lead_id field is NOT NULL using count_nulls() from PGXN )
> );

Then each table that inherits from lead.lead (as well as lead.lead
itself) has:

> , FOREIGN KEY (lead_id) REFERENCES _lead.lead_fk DEFERRABLE INITIALLY DEFERRED

That FK is in place to ensure that when a lead record is inserted, a
corresponding row is inserted into _lead.lead_fk as well. That insert is
handled by this trigger function:

CREATE OR REPLACE FUNCTION _lead.tg_lead_fk(
) RETURNS trigger LANGUAGE plpgsql

-- !!!!!!!!!
SECURITY DEFINER SET search_path = pg_catalog
-- !!!!!!!!!
AS $body$
BEGIN
EXECUTE format(
$$INSERT INTO _lead.lead_fk( lead_id, %I ) VALUES( $1, $1 )$$
, TG_TABLE_NAME || '_id'
)
USING NEW.lead_id
;
RETURN NEW;
END
$body$;

Finally, a table that needs to have a FK to a lead has

> , lead_id int NOT NULL REFERENCES _lead.lead_fk

I also have the following in a pgTap test function to verify that the FK
exists on all children of the lead.lead table.

FOR r IN
SELECT * FROM cat_tools.pg_class_v WHERE reloid = 'lead.lead'::regclass
UNION ALL
SELECT c.*
FROM pg_inherits i
JOIN cat_tools.pg_class_v c ON reloid = inhrelid
WHERE inhparent = 'lead.lead'::regclass
LOOP
RETURN NEXT col_is_pk(
r.relschema
, r.relname
, array[ 'lead_id' ]
, 'lead_id is PK'
);

RETURN NEXT fk_ok(
r.relschema
, r.relname
, 'lead_id'
, '_lead'
, 'lead_fk'
, 'lead_id'
);
END LOOP;

At some point I'll turn this into metacode so that setting all of this
up is just a function call. I just haven't gotten to it yet. (Though, if
someone wanted to pay me to do that... ;P )
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com