Re: What have I done!?!?!? :-) - Mailing list pgsql-general
| From | Perry Smith |
|---|---|
| Subject | Re: What have I done!?!?!? :-) |
| Date | |
| Msg-id | E62EA153-1EFD-411D-9553-5F890FC5139D@easesoftware.com Whole thread Raw |
| In response to | Re: What have I done!?!?!? :-) (Jan Wieck <jan@wi3ck.info>) |
| Responses |
Re: What have I done!?!?!? :-)
|
| List | pgsql-general |
On Apr 8, 2022, at 07:47, Jan Wieck <jan@wi3ck.info> wrote:On 4/8/22 01:57, Nikolay Samokhvalov wrote:On Thu, Apr 7, 2022 at 8:10 AM Jan Wieck <jan@wi3ck.info <mailto:jan@wi3ck.info>> wrote:
So **IF** Active Record is using that feature, then it can dump any
amount of garbage into your PostgreSQL database and PostgreSQL will
happily accept it with zero integrity checking.
It's DISABLE TRIGGER ALL https://github.com/rails/rails/blob/831031a8cec5bfe59ef653ae2857d4fe64c5698d/activerecord/lib/active_record/connection_adapters/postgresql/referential_integrity.rb#L12 <https://github.com/rails/rails/blob/831031a8cec5bfe59ef653ae2857d4fe64c5698d/activerecord/lib/active_record/connection_adapters/postgresql/referential_integrity.rb#L12>
Similar poison, same side effect.
Looking further at that code it also directly updates the PostgreSQL system catalog. This is a big, red flag.
Why do the Rails developers think they need a sledgehammer like that? It seems to be doing that for over 7 years, so it is hard to tell from the commit log why they need to disable RI at all.
It has been a long time since I’ve done Rails stuff. What follows is the best I can recall but please take it with a grain of salt.
The first problem is that generally Rails does not put constraints in the database. There were others like me who thought that was insane and would put constraints in the database — this includes foreign key constraints, check constraints, etc. About the only constraint that could be added into the DB using native Rails was the “not null” constraint.
When foreign and other constraints were added, it broke something they call “Fixtures” which are present db states that are plopped into the DB during testing. To “fix” that, I (and others) would add this into our code base: (I’m adding this to see what you guys think of it — is it safer / better or just as insane?)
def disable_referential_integrity(&block)
transaction {
begin
execute "SET CONSTRAINTS ALL DEFERRED"
yield
ensure
execute "SET CONSTRAINTS ALL IMMEDIATE"
end
}
end
transaction {
begin
execute "SET CONSTRAINTS ALL DEFERRED"
yield
ensure
execute "SET CONSTRAINTS ALL IMMEDIATE"
end
}
end
The above would only be used during testing.
For this project, I wondered if it was in the AR code base and found a method with the same name. Note that my method was all done under one transaction. The code they have uses multiple transactions. I’m curious to know if either method is better / safer.
For the longest time, the Rails developers refused to put constraints into the DB insisting that the app could do it. Eventually (SURPRISE!!!!) they were proven wrong. Users can now add foreign key constraints using native AR constructs as well as general check constraints and indexes. Before it had to be done using SQL. Rails did have a general purpose “execute” statement (shown above).
Attachment
pgsql-general by date: