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

On 4/8/22 09:27, Magnus Hagander wrote:
> 
> 
> On Fri, Apr 8, 2022 at 3:23 PM Perry Smith <pedz@easesoftware.com 
> <mailto:pedz@easesoftware.com>> wrote:
>     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
> 
> 
> This is perfectly normal code and nothing wrong with it. DEFERRED 
> constraints are how you are *supposed* to handle such things. It defers 
> the check of the foreign key to the end of the transaction, but it will 
> still fail to commit if the foreign key is broken *at that point*. But 
> it lets you do things like modify multiple tables that refer to each 
> other, and have the changes only checked when they're all done.

Indeed, especially because this code does not require any elevated 
permissions, guarantees referential integrity at commit time and 
guarantees that no inconsistent, intermediate state will ever be visible 
to another, concurrent session.

It only affects constraints that have been declared DEFERRABLE. Those 
that are not are silently ignored (as per SQL standard).

A lot of frameworks didn't support foreign keys because one of the most 
popular databases at that time didn't support them. Well, the SQL parser 
of that particular database would accept the syntax, but the engine 
would not enforce anything. Even the manual of that database stated that 
"foreign keys are mostly for documentation purposes and are not needed 
as long as the application does all operations in the correct order." 
They changed that part of the documentation when support for InnoDB was 
added. Therefore I would not put all blame on the Rails developers.


Best Regards, Jan