Re: What have I done!?!?!? :-) - Mailing list pgsql-general

[...]

Notice that parent_id is suppose to refer to an id in the same table — at least, that is what I’m trying to do.  I’m trying to create a “root” entry whose parent points to themselves

Note that you don't usually define a root as having a parent_id being the same as its id (hard to manage especially when you use a sequence nextval() to auto-fill the "id" primary keys).
The usual way is to have parent_id being nullable and roots are then rows with no parent_id. This matches the intuitive idea of a root which makes code more maintainable.

and I botched the code first time around and now I have this:

files_development=# select * from files;
 id | basename | parent_id |    dev    |   ftype   | uid  | gid  | ino | mode  |     mtime      | nlink | size |         created_at         |         updated_at         
----+----------+-----------+-----------+-----------+------+------+-----+-------+----------------+-------+------+----------------------------+----------------------------
 11 | pedz     |      1234 | 687931150 | directory | 1000 | 1002 |   2 | 16877 | 18:43:29.65271 |    31 |   34 | 2022-04-06 21:58:43.570539 | 2022-04-06 21:58:43.570539
 12 | pedz     |        12 | 687931150 | directory | 1000 | 1002 |   2 | 16877 | 18:43:29.65271 |    31 |   34 | 2022-04-06 22:00:29.087417 | 2022-04-06 22:00:29.115021
(2 rows)

The record with id 11 has a parent id of 1234 which doesn’t exist.

My question isn’t how do I fix it, my question is why didn’t Postgres back out the botched record?  Why isn’t it complaining?

Disabling referential integrity in Active Record explicitly disables triggers that would have made PostgreSQL return an error.

I’m using Active Record with the psql adapter.  It has a disable_referential_integrity which takes a block of code.  When the block of code exists, the constraints are put back.  At least, that is what I thought.

If you look at ActiveRecord's code (https://www.rubydoc.info/docs/rails/ActiveRecord/ConnectionAdapters/PostgreSQL/ReferentialIntegrity#disable_referential_integrity-instance_method) :
before the block of code the triggers are disabled, then the block is executed and finally the triggers are enabled again (but only after they would have had a chance to be used).

I don't think this code is meant for general use (I believe I only used it in data migrations on rare occasions). I would bet that this isn't safe to use in many cases : unless I missed something you could kill your process before the triggers are enabled again leaving your application with 0 constraints until disable_referential_integrity is used again. What happens when several processes are using it simultaneously is probably not what you want either (triggers being enabled again by another process in the middle of the execution of your code).

I’m wondering if the disabled constraints are still disabled somehow.

Constraints are implemented using triggers so they aren't meant to ensure a consistent global state, they only check that the modifications are OK at the moment they are done.
If you disable constraints temporarily nothing prevents your data from being inconsistent with your constraints.

 If so, how would I check for that and how would I turn them back on?  Or am I way off in the weeds?

I'd say the later : in your case I would use a NULL parent_id for root(s). Your way leads you to bend PostgreSQL until its back brakes.

Best regards,

-- 
Lionel Bouton
gérant de JTEK SARL
https://www.linkedin.com/in/lionelbouton/