Reference to id vs. permid

[aazaff]

Here is an interesting case that I think has almost certainly already been worked out in database theory.

CREATE TABLE occurrences {
    id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    permid integer NOT NULL, 
    authorizer integer REFERENCES persons("id") NOT NULL, -- Whoever was the authorizer of the enterer
    enterer integer REFERENCES persons("id") NOT NULL, -- Whoever made the entry or edit
    taxon integer REFERENCES taxa("id") NOT NULL, -- the taxon in question
    reidentification integer REFERENCES occurrences("id"),
    collection integer REFERENCES collections("id") NOT NULL, -- collection taxon belongs to
    reference integer REFERENCES references("id") NOT NULL,
    preceded_by integer REFERENCES opinions("id"),
    succeeded_by integer REFERENCES opinions("id")
    };

At a conceptual level, the collection and reference field actually map the permid not the primary key id. If we map it to the primary key id, then we need to make sure the occurrences table is updated every time the collection or reference table is updated.

[aazaff]

This issue is dumb. I was being dubm.

[aazaff]

I wasn't really being dumb. It just wasn't obvious at the current stage.

The bottom line is we would use the system tables to track down all the referencing foreign keys and update them.

[NoisyFlowers]

Prompt:

Before we implement this immeditate change, I want to think about the overall versioning
strategy some more. So, if we do plan to update all fks when a new version of an entity is
added, what's the best way to go about that? Seems like it could be brittle and prone to error
if we just hope to remember what's pointing where for any given entity type. Could the process
be made generic by using system tables to identify fks?

Claude says:

● Great instinct. Hardcoding FK knowledge per table is a maintenance trap — every time the schema
evolves, someone has to remember to update the versioning logic. PostgreSQL's catalog tables
can solve this generically.

The idea

When a new version of an entity is inserted (say, a new row in characters with the same
permid), a generic function:

1. Queries pg_constraint to find all FKs pointing to that table
2. Excludes preceded_by_id / succeeded_by_id (those are the version chain itself)
3. Generates and executes UPDATE statements to swing all references from old id → new id

pg_constraint tells us:

    characters.parent_character_id ──────▶ characters.id                                         
    states.parent_character_id ──────────▶ characters.id
    characters.preceded_by_id ───────────▶ characters.id  ← EXCLUDE                              
    characters.succeeded_by_id ──────────▶ characters.id  ← EXCLUDE

Sketch of the function

CREATE OR REPLACE FUNCTION swing_fks_to_new_version(
   target_table text,                                                                         
   old_id integer,
   new_id integer                                                                             
) RETURNS void AS $$
DECLARE
   fk record;
BEGIN
   FOR fk IN
       SELECT
           src_ns.nspname  AS src_schema,                                                     
           src.relname     AS src_table,
           a_src.attname   AS src_column                                                      
       FROM pg_constraint con
       JOIN pg_class src    ON con.conrelid = src.oid                                         
       JOIN pg_namespace src_ns ON src.relnamespace = src_ns.oid
       JOIN pg_class tgt    ON con.confrelid = tgt.oid                                        
       JOIN pg_attribute a_src                                                                
         ON a_src.attrelid = con.conrelid
        AND a_src.attnum = ANY(con.conkey)                                                    
       WHERE con.contype = 'f'
         AND tgt.relname = target_table                                                       
         AND a_src.attname NOT IN (
               'preceded_by_id', 'succeeded_by_id'                                            
             ) 
   LOOP                                                                                       
       EXECUTE format(
           'UPDATE %I.%I SET %I = $1 WHERE %I = $2',                                          
           fk.src_schema, fk.src_table,
           fk.src_column, fk.src_column
       ) USING new_id, old_id;                                                                
   END LOOP;
END;                                                                                           
$$ LANGUAGE plpgsql;

Then the full "create new version" flow would be:

  BEGIN;                                                                                         
    -- 1. Insert new version
    INSERT INTO characters (..., preceded_by_id)                                                 
      VALUES (..., old_id) RETURNING id INTO new_id;
                                                                                                 
    -- 2. Close out old version
    UPDATE characters SET succeeded_by_id = new_id WHERE id = old_id;                            
                                                                                                 
    -- 3. Swing all structural FKs
    SELECT swing_fks_to_new_version('characters', old_id, new_id);                               
  COMMIT;                                                                                        

Things worth thinking about

Self-references are handled naturally. characters.parent_character_id → characters.id is just
another FK in pg_constraint. A child character pointing to the old parent gets swung to the new
one automatically.

Cross-table references too. states.parent_character_id → characters.id — states pointing to the
old character version get updated without anyone having to know about that relationship
explicitly.

What about additional_schema_refs? It has schema_id → schemas.id. If you version a schema, the
function picks that up and swings the join table rows to the new version. Same for
additional_collection_refs.

Should the exclusion list be broader? Right now we exclude preceded_by_id and succeeded_by_id
by name convention. If any future column breaks that naming convention, it would get
incorrectly updated. An alternative would be to tag version-chain columns with a COMMENT and
filter on that, but the naming convention is probably sufficient if you enforce it.

Performance consideration. For tables with lots of referencing rows, this is O(number of
referencing rows). For your use case that's probably fine, but if a schema has thousands of
characters each with dozens of states, versioning a schema means updating all those
parent_schema_id values. An index on each FK column (which Postgres doesn't auto-create for
FKs, unlike the referenced column) would help.

Could this be a trigger instead of an explicit call? You could put it on an AFTER INSERT
trigger that fires when preceded_by_id IS NOT NULL, so it happens automatically whenever a new
version is created. Tradeoff: less explicit, but impossible to forget.

[NoisyFlowers]

I like the idea of this being a trigger. This feels like something that should be part on the atomic db action for editing an entity.

What about the question of additional refs? I think if the only change to a schema, for instance, is that we are adding or removing an additional reference, we would likely have the API make that change to the additional_refs table without touching the schemas table. Of course, if the change is to the primary ref, then we would be adding a new schema record and the fk updates would be required.

What do you think of naming convention for exclusions vs COMMENT on excluded fields? Either one could be forgotten in the future, but the COMMENT at least makes it more explicit for someone trying to figure out what's happening.

[NoisyFlowers]

Hmm... not sure I agree with my remark about COMMENT ON. It seems those comments can only be seen via some rather cryptic psql or sql query syntax. So they probably would not be obvious to a future developer who wasn't aware of them.

[aazaff]

Yes, the psql comment function is very opaque. Get Outlook for iOS<https://aka.ms/o0ukef>

…

________________________________ From: NoisyFlowers ***@***.***> Sent: Friday, April 3, 2026 12:18:22 PM To: paleobot/pbdb2-dev ***@***.***> Cc: Zaffos, Andrew - (azaffos) ***@***.***>; State change ***@***.***> Subject: [EXT] Re: [paleobot/pbdb2-dev] Reference to id vs. permid (Issue #9) [https://avatars.githubusercontent.com/u/12547812?s=20&v=4] External Email

________________________________ NoisyFlowers left a comment (paleobot/pbdb2-dev#9)<#9 (comment)> Hmm... not sure I agree with my remark about COMMENT ON. It seems those comments can only be seen via some rather cryptic psql or sql query syntax. So they probably would not be obvious to a future developer who wasn't aware of them. — Reply to this email directly, view it on GitHub<#9?email_source=notifications&email_token=ACPQSQ3G3ITSGCS34YXQEFT4UAE75A5CNFSNUABFM5UWIORPF5TWS5BNNB2WEL2JONZXKZKDN5WW2ZLOOQXTIMJYGQ4DINBTG44KM4TFMFZW63VMON2GC5DFL5RWQYLOM5S2KZLWMVXHJLDGN5XXIZLSL5RWY2LDNM#issuecomment-4184844378>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/ACPQSQYJFVYFSUSAQYWKLEL4UAE75AVCNFSM6AAAAACT7TJUEKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHM2DCOBUHA2DIMZXHA>. You are receiving this because you modified the open/close state.Message ID: ***@***.***>