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

Rather than explain how I got here, I’ll just explain the state I’m in.

From psql:

files_development=# \d files
                                          Table "public.files"
   Column   |              Type              | Collation | Nullable |              Default              
------------+--------------------------------+-----------+----------+-----------------------------------
 id         | bigint                         |           | not null | nextval('files_id_seq'::regclass)
 basename   | character varying              |           | not null | 
 parent_id  | bigint                         |           | not null | 
 dev        | bigint                         |           | not null | 
 ftype      | character varying              |           | not null | 
 uid        | bigint                         |           | not null | 
 gid        | bigint                         |           | not null | 
 ino        | bigint                         |           | not null | 
 mode       | bigint                         |           | not null | 
 mtime      | time without time zone         |           | not null | 
 nlink      | bigint                         |           | not null | 
 size       | bigint                         |           | not null | 
 created_at | timestamp(6) without time zone |           | not null | 
 updated_at | timestamp(6) without time zone |           | not null | 
Indexes:
    "files_pkey" PRIMARY KEY, btree (id)
    "index_files_on_parent_id" btree (parent_id)
Foreign-key constraints:
    "fk_rails_15605042e6" FOREIGN KEY (parent_id) REFERENCES files(id)
Referenced by:
    TABLE "files" CONSTRAINT "fk_rails_15605042e6" FOREIGN KEY (parent_id) REFERENCES files(id)

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 and I botched the code first time around and now I have this:

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?

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.

I’m wondering if the disabled constraints are still disabled somehow.  If so, how would I check for that and how would I turn them back on?  Or am I way off in the weeds?

Thank you for your time

Perry Smith