Re: What have I done!?!?!? :-)

Hi Perry,

Le 07/04/2022 à 00:25, Perry Smith a écrit :
> [...]
> 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/