Re: [openstack-dev] [Keystone]ON DELETE RESTRICT VS ON DELETE CASCADE

[Morgan Fainberg]

In general I'd say that cascade is the right approach. There are some very
limited cases where restrict should be used. Overall, I'd like to see less
reliance on FK constraints anywhere. The reason for using Cascade is that
we should be very specific in our code to prevent deletion independent of
the backend (move these checks to the controller level) if we want to
prevent deletion cascades. In short, we should not rely on an
implementation specific detail to know if we can / cannot delete something.

--Morgan

On Sat, Mar 7, 2015 at 7:37 PM, Chen, Wei D  wrote:

> Hi,
>
> I did some homework to follow up the inline comment about on delete
> cascade subclauses of the foreign key clause[1], when ' ON
> DELETE CASCADE ' is given, delete a recode from parent table will DELETE
> all the corresponding rows from the CHILD table
> automatically *without any warning*. 'ON DELETE RESTRICT' looks different,
> it will fail complaining about the existing child rows,
> this is the default foreign key relationship behavior, this seems give end
> user a chance to double check the data.
>
> I did a quick test against the table 'endpoint_group', the output error
> message like below,
> mysql> delete from endpoint_group;
> ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key
> constraint fails (`keystone`.`project_endpoint_group`,
> CONSTRAINT `project_endpoint_group_ibfk_1` FOREIGN KEY
> (`endpoint_group_id`) REFERENCES `endpoint_group` (`id`))
>
> I am a little confused about two different subclauses as both of them can
> be found in the table definition of SQL backends, it hard
> to say which one is better, is it worthwhile to move all of them to "ON
> DELETE CASCADE" or "ON DELETE RESTRICT"?
>
>
> [1]
> https://review.openstack.org/#/c/151931/5/keystone/contrib/endpoint_filter/migrate_repo/versions/002_add_endpoint_groups.py
>
> Best Regards,
> Dave Chen
>
>
> __
> OpenStack Development Mailing List (not for usage questions)
> Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
>
>
__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [Keystone]ON DELETE RESTRICT VS ON DELETE CASCADE

[Mike Bayer]

Morgan Fainberg  wrote:

> In general I'd say that cascade is the right approach. There are some very 
> limited cases where restrict should be used. Overall, I'd like to see less 
> reliance on FK constraints anywhere.


can you elaborate on your reasoning that FK constraints should be used less
overall?  or do you just mean that the client side should be mirroring the same
rules that would be enforced by the FKs?




> The reason for using Cascade is that we should be very specific in our code 
> to prevent deletion independent of the backend (move these checks to the 
> controller level) if we want to prevent deletion cascades. In short, we 
> should not rely on an implementation specific detail to know if we can / 
> cannot delete something.
> 
> --Morgan
> 
> On Sat, Mar 7, 2015 at 7:37 PM, Chen, Wei D  wrote:
> Hi,
> 
> I did some homework to follow up the inline comment about on delete cascade 
> subclauses of the foreign key clause[1], when ' ON
> DELETE CASCADE ' is given, delete a recode from parent table will DELETE all 
> the corresponding rows from the CHILD table
> automatically *without any warning*. 'ON DELETE RESTRICT' looks different, it 
> will fail complaining about the existing child rows,
> this is the default foreign key relationship behavior, this seems give end 
> user a chance to double check the data.
> 
> I did a quick test against the table 'endpoint_group', the output error 
> message like below,
> mysql> delete from endpoint_group;
> ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key 
> constraint fails (`keystone`.`project_endpoint_group`,
> CONSTRAINT `project_endpoint_group_ibfk_1` FOREIGN KEY (`endpoint_group_id`) 
> REFERENCES `endpoint_group` (`id`))
> 
> I am a little confused about two different subclauses as both of them can be 
> found in the table definition of SQL backends, it hard
> to say which one is better, is it worthwhile to move all of them to "ON 
> DELETE CASCADE" or "ON DELETE RESTRICT"?
> 
> 
> [1] 
> https://review.openstack.org/#/c/151931/5/keystone/contrib/endpoint_filter/migrate_repo/versions/002_add_endpoint_groups.py
> 
> Best Regards,
> Dave Chen
> 
> 
> __
> OpenStack Development Mailing List (not for usage questions)
> Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
> 
> 
> __
> OpenStack Development Mailing List (not for usage questions)
> Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [Keystone]ON DELETE RESTRICT VS ON DELETE CASCADE

[David Stanek]

On Sun, Mar 8, 2015 at 1:37 PM, Mike Bayer  wrote:

> can you elaborate on your reasoning that FK constraints should be used less
> overall?  or do you just mean that the client side should be mirroring the
> same
> rules that would be enforced by the FKs?
>

I don't think he means that we will use them less.  Our SQL backends are
full of them.  What Keystone can't do is rely on them because not all
implementations of our backends support FKs.


-- 
David
blog: http://www.traceback.org
twitter: http://twitter.com/dstanek
www: http://dstanek.com
__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [Keystone]ON DELETE RESTRICT VS ON DELETE CASCADE

[Morgan Fainberg]

On March 8, 2015 at 11:24:37 AM, David Stanek (dsta...@dstanek.com) wrote:

On Sun, Mar 8, 2015 at 1:37 PM, Mike Bayer  wrote:
can you elaborate on your reasoning that FK constraints should be used less
overall?  or do you just mean that the client side should be mirroring the same
rules that would be enforced by the FKs?

I don't think he means that we will use them less.  Our SQL backends are full 
of them.  What Keystone can't do is rely on them because not all 
implementations of our backends support FKs.
100% spot on David. We support implementations that have no real concept of FK 
and we cannot assume that a cascade (or restrict) will occur on these 
implementations.



—Morga



--
David
blog: http://www.traceback.org
twitter: http://twitter.com/dstanek
www: http://dstanek.com
__ 
OpenStack Development Mailing List (not for usage questions) 
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe 
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev 
__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [Keystone]ON DELETE RESTRICT VS ON DELETE CASCADE

[Chen, Wei D]

+1,

 

I am fan of checking the constraints in the controller level instead of relying 
on FK constraints itself, thanks.

 

 

Best Regards,

Dave Chen

 

From: Morgan Fainberg [mailto:morgan.fainb...@gmail.com] 
Sent: Monday, March 09, 2015 2:29 AM
To: David Stanek; OpenStack Development Mailing List (not for usage questions)
Subject: Re: [openstack-dev] [Keystone]ON DELETE RESTRICT VS ON DELETE CASCADE

 

On March 8, 2015 at 11:24:37 AM, David Stanek (dsta...@dstanek.com) wrote:


On Sun, Mar 8, 2015 at 1:37 PM, Mike Bayer  wrote:

can you elaborate on your reasoning that FK constraints should be used less
overall?  or do you just mean that the client side should be mirroring the same
rules that would be enforced by the FKs?


I don't think he means that we will use them less.  Our SQL backends are full 
of them.  What Keystone can't do is rely on them because not all 
implementations of our backends support FKs.

100% spot on David. We support implementations that have no real concept of FK 
and we cannot assume that a cascade (or restrict) will occur on these 
implementations.

 

—Morga

 

--

David
blog: http://www.traceback.org
twitter: http://twitter.com/dstanek

www: http://dstanek.com

__ 
OpenStack Development Mailing List (not for usage questions) 
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe 
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev 



smime.p7s
Description: S/MIME cryptographic signature
__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [Keystone]ON DELETE RESTRICT VS ON DELETE CASCADE

[Adam Young]

On 03/08/2015 02:28 PM, Morgan Fainberg wrote:
On March 8, 2015 at 11:24:37 AM, David Stanek (dsta...@dstanek.com 
) wrote:


On Sun, Mar 8, 2015 at 1:37 PM, Mike Bayer>wrote:


can you elaborate on your reasoning that FK constraints should be
used less
overall?  or do you just mean that the client side should be
mirroring the same
rules that would be enforced by the FKs?


I don't think he means that we will use them less. Our SQL backends 
are full of them.  What Keystone can't do is rely on them because not 
all implementations of our backends support FKs.


100% spot on David. We support implementations that have no real 
concept of FK and we cannot assume that a cascade (or restrict) will 
occur on these implementations.




And even if the back ends do, we split behavior across identity, 
assignments, and resources ,and FKs cannot  cross those; Thety can and 
will vary independently.




—Morga




--
David
blog:http://www.traceback.org
twitter:http://twitter.com/dstanek
www:http://dstanek.com
__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: 
openstack-dev-requ...@lists.openstack.org?subject:unsubscribe

http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev



__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev


__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [Keystone]ON DELETE RESTRICT VS ON DELETE CASCADE

[Clint Byrum]

Excerpts from David Stanek's message of 2015-03-08 11:18:05 -0700:
> On Sun, Mar 8, 2015 at 1:37 PM, Mike Bayer  wrote:
> 
> > can you elaborate on your reasoning that FK constraints should be used less
> > overall?  or do you just mean that the client side should be mirroring the
> > same
> > rules that would be enforced by the FKs?
> >
> 
> I don't think he means that we will use them less.  Our SQL backends are
> full of them.  What Keystone can't do is rely on them because not all
> implementations of our backends support FKs.
> 

Note that they're also a huge waste of SQL performance. It's _far_ cheaper
to scale out application servers and garbage-collect using background jobs
like pt-archiver than it will ever be to scale out a consistent data-store
and do every single little bit of house keeping in real time.  So even
on SQL backends, I'd recommend just disabling and dropping FK constraints
if you expect any more than the bare minimum usage of Keystone.

__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [Keystone]ON DELETE RESTRICT VS ON DELETE CASCADE

[Mike Bayer]

Wei D  wrote:

> +1,
> 
>  
> 
> I am fan of checking the constraints in the controller level instead of 
> relying on FK constraints itself, thanks.

Why shouldn’t the storage backends, be they relational or not, be tasked
with verifying integrity of data manipulations? If data integrity rules are
pushed out to the frontend, the frontend starts implementing parts of the
backend. Other front-ends to the same persistence backend might not have the
same rule checks, and you are now wide open for invalid data to be
persisted.

Front-ends should of course be encouraged to report on a potential issue in
integrity before proceeding with an operation, but IMO the backend should
definitely not allow the operation to proceed if the frontend fails to check
for a constraint. Persistence operations in which related objects must also
be modified in response to a primary object (e.g. a CASCADE situation),
else integrity will fail, should also be part of the backend, not the front end.





> Best Regards,
> 
> Dave Chen
> 
>  
> 
> From: Morgan Fainberg [mailto:morgan.fainb...@gmail.com] 
> Sent: Monday, March 09, 2015 2:29 AM
> To: David Stanek; OpenStack Development Mailing List (not for usage questions)
> Subject: Re: [openstack-dev] [Keystone]ON DELETE RESTRICT VS ON DELETE CASCADE
> 
>  
> 
> On March 8, 2015 at 11:24:37 AM, David Stanek (dsta...@dstanek.com) wrote:
> 
> 
> On Sun, Mar 8, 2015 at 1:37 PM, Mike Bayer  wrote:
> 
> can you elaborate on your reasoning that FK constraints should be used less
> overall?  or do you just mean that the client side should be mirroring the 
> same
> rules that would be enforced by the FKs?
> 
> 
> I don't think he means that we will use them less.  Our SQL backends are full 
> of them.  What Keystone can't do is rely on them because not all 
> implementations of our backends support FKs.
> 
> 100% spot on David. We support implementations that have no real concept of 
> FK and we cannot assume that a cascade (or restrict) will occur on these 
> implementations.
> 
>  
> 
> —Morga
> 
>  
> 
> --
> 
> David
> blog: http://www.traceback.org
> twitter: http://twitter.com/dstanek
> 
> www: http://dstanek.com
> 
> __ 
> OpenStack Development Mailing List (not for usage questions) 
> Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe 
> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev 
> 
> __
> OpenStack Development Mailing List (not for usage questions)
> Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [Keystone]ON DELETE RESTRICT VS ON DELETE CASCADE

[David Stanek]

On Sun, Mar 8, 2015 at 10:28 PM, Chen, Wei D  wrote:

> +1,
>
>
>
> I am fan of checking the constraints in the controller level instead of
> relying on FK constraints itself, thanks.
>

The Keystone controllers shouldn't do any business logic. This should be in
the managers. The controllers should do nothing more that take web stuff
and convert it for use by the managers.


-- 
David
blog: http://www.traceback.org
twitter: http://twitter.com/dstanek
www: http://dstanek.com
__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [Keystone]ON DELETE RESTRICT VS ON DELETE CASCADE

[Mike Bayer]

Clint Byrum  wrote:

> Excerpts from David Stanek's message of 2015-03-08 11:18:05 -0700:
>> On Sun, Mar 8, 2015 at 1:37 PM, Mike Bayer  wrote:
>> 
>>> can you elaborate on your reasoning that FK constraints should be used less
>>> overall?  or do you just mean that the client side should be mirroring the
>>> same
>>> rules that would be enforced by the FKs?
>> 
>> I don't think he means that we will use them less.  Our SQL backends are
>> full of them.  What Keystone can't do is rely on them because not all
>> implementations of our backends support FKs.
> 
> Note that they're also a huge waste of SQL performance. It's _far_ cheaper
> to scale out application servers and garbage-collect using background jobs
> like pt-archiver than it will ever be to scale out a consistent data-store
> and do every single little bit of house keeping in real time.  So even
> on SQL backends, I'd recommend just disabling and dropping FK constraints
> if you expect any more than the bare minimum usage of Keystone.

Im about -1000 on disabling foreign key constraints. Any decision based on
“performance” IMHO has to be proven with benchmarks. Foreign keys on modern
databases like MySQL and Postgresql do not add overhead to any significant
degree compared to just the workings of the Python code itself (which means,
a benchmark here should be illustrating a tangible impact on the python
application itself). OTOH, the prospect of a database with failed
referential integrity is a recipe for disaster.   


__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [Keystone]ON DELETE RESTRICT VS ON DELETE CASCADE

[Morgan Fainberg]

On Monday, March 9, 2015, Mike Bayer  wrote:

>
>
> Wei D > wrote:
>
> > +1,
> >
> >
> >
> > I am fan of checking the constraints in the controller level instead of
> relying on FK constraints itself, thanks.
>
> Why shouldn’t the storage backends, be they relational or not, be tasked
> with verifying integrity of data manipulations? If data integrity rules are
> pushed out to the frontend, the frontend starts implementing parts of the
> backend. Other front-ends to the same persistence backend might not have
> the
> same rule checks, and you are now wide open for invalid data to be
> persisted.
>
> Front-ends should of course be encouraged to report on a potential issue in
> integrity before proceeding with an operation, but IMO the backend should
> definitely not allow the operation to proceed if the frontend fails to
> check
> for a constraint. Persistence operations in which related objects must also
> be modified in response to a primary object (e.g. a CASCADE situation),
> else integrity will fail, should also be part of the backend, not the
> front end.
>
>
>
>
You are assuming data is stored in an all SQL environment. In keystone it
is highly unlikely that you can make this assumption. When you discuss
users, groups, projects, domains, roles, assignments, etc... All of these
could be crossing SQL, LDAP, MongoDB, etc. in short, do not assume you are
even talking the same language.  This is why FKs are of minimal benefit to
us. The manager layer contains the business logic (and should) to handle
the cross-referencing of objects. The only FKs we have are for
uuid/PK identitifiers at the moment (afaik), these are/should-be immutable.

So tl;dr, we have an architecture that is not conducive to foreign keys,
and therefore should not use them beyond bare-minimums, instead rely on the
manager to do business logic. This is not the case for all OpenStack
projects.


>
>
> > Best Regards,
> >
> > Dave Chen
> >
> >
> >
> > From: Morgan Fainberg [mailto:morgan.fainb...@gmail.com ]
> > Sent: Monday, March 09, 2015 2:29 AM
> > To: David Stanek; OpenStack Development Mailing List (not for usage
> questions)
> > Subject: Re: [openstack-dev] [Keystone]ON DELETE RESTRICT VS ON DELETE
> CASCADE
> >
> >
> >
> > On March 8, 2015 at 11:24:37 AM, David Stanek (dsta...@dstanek.com
> ) wrote:
> >
> >
> > On Sun, Mar 8, 2015 at 1:37 PM, Mike Bayer  > wrote:
> >
> > can you elaborate on your reasoning that FK constraints should be used
> less
> > overall?  or do you just mean that the client side should be mirroring
> the same
> > rules that would be enforced by the FKs?
> >
> >
> > I don't think he means that we will use them less.  Our SQL backends are
> full of them.  What Keystone can't do is rely on them because not all
> implementations of our backends support FKs.
> >
> > 100% spot on David. We support implementations that have no real concept
> of FK and we cannot assume that a cascade (or restrict) will occur on these
> implementations.
> >
> >
> >
> > —Morga
> >
> >
> >
> > --
> >
> > David
> > blog: http://www.traceback.org
> > twitter: http://twitter.com/dstanek
> >
> > www: http://dstanek.com
> >
> >
> __
> > OpenStack Development Mailing List (not for usage questions)
> > Unsubscribe:
> openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
> > http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
> >
> >
> __
> > OpenStack Development Mailing List (not for usage questions)
> > Unsubscribe:
> openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
> > http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
>
> __
> OpenStack Development Mailing List (not for usage questions)
> Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
>
__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [Keystone]ON DELETE RESTRICT VS ON DELETE CASCADE

[Mike Bayer]

Morgan Fainberg  wrote:

> 
> 
> On Monday, March 9, 2015, Mike Bayer  wrote:
> 
> 
> Wei D  wrote:
> 
> > +1,
> >
> >
> >
> > I am fan of checking the constraints in the controller level instead of 
> > relying on FK constraints itself, thanks.
> 
> Why shouldn’t the storage backends, be they relational or not, be tasked
> with verifying integrity of data manipulations? If data integrity rules are
> pushed out to the frontend, the frontend starts implementing parts of the
> backend. Other front-ends to the same persistence backend might not have the
> same rule checks, and you are now wide open for invalid data to be
> persisted.
> 
> Front-ends should of course be encouraged to report on a potential issue in
> integrity before proceeding with an operation, but IMO the backend should
> definitely not allow the operation to proceed if the frontend fails to check
> for a constraint. Persistence operations in which related objects must also
> be modified in response to a primary object (e.g. a CASCADE situation),
> else integrity will fail, should also be part of the backend, not the front 
> end.
> 
> 
> 
> 
> You are assuming data is stored in an all SQL environment. In keystone it is 
> highly unlikely that you can make this assumption. When you discuss users, 
> groups, projects, domains, roles, assignments, etc... All of these could be 
> crossing SQL, LDAP, MongoDB, etc. in short, do not assume you are even 
> talking the same language.  This is why FKs are of minimal benefit to us.

You should read my paragraph above again; I referred to “the storage
backends, **be they relational or not**, be tasked with verifying
integrity”. Which means, for example in an LDAP system where deleting a
parent key means all the child keys are automatically deleted, that is what
I mean by “the backend has verified integrity”. The controller didn’t need
to dip into the LDAP backend’s system and make sure that the child keys of
the parent were removed first.   The LDAP system naturally performs this
task.

In a relational backend, it should not be possible to perform an operation
where a row is in place which refers to a primary key that no longer exists.
This should be independent of the system which refers to this schema, even
if that system might be unaware that the backend is in fact relational. I’m
a little surprised this is suddenly controversial.

> 
> So tl;dr, we have an architecture that is not conducive to foreign keys, and 
> therefore should not use them beyond bare-minimums, instead rely on the 
> manager to do business logic. This is not the case for all OpenStack projects.

If your relational backend contains more than one table, and any of these
tables happen to store primary key identifiers from some of the other
tables, then foreign keys are relevant and necessary.


> > Best Regards,
> >
> > Dave Chen
> >
> >
> >
> > From: Morgan Fainberg [mailto:morgan.fainb...@gmail.com]
> > Sent: Monday, March 09, 2015 2:29 AM
> > To: David Stanek; OpenStack Development Mailing List (not for usage 
> > questions)
> > Subject: Re: [openstack-dev] [Keystone]ON DELETE RESTRICT VS ON DELETE 
> > CASCADE
> >
> >
> >
> > On March 8, 2015 at 11:24:37 AM, David Stanek (dsta...@dstanek.com) wrote:
> >
> >
> > On Sun, Mar 8, 2015 at 1:37 PM, Mike Bayer  wrote:
> >
> > can you elaborate on your reasoning that FK constraints should be used less
> > overall?  or do you just mean that the client side should be mirroring the 
> > same
> > rules that would be enforced by the FKs?
> >
> >
> > I don't think he means that we will use them less.  Our SQL backends are 
> > full of them.  What Keystone can't do is rely on them because not all 
> > implementations of our backends support FKs.
> >
> > 100% spot on David. We support implementations that have no real concept of 
> > FK and we cannot assume that a cascade (or restrict) will occur on these 
> > implementations.
> >
> >
> >
> > —Morga
> >
> >
> >
> > --
> >
> > David
> > blog: http://www.traceback.org
> > twitter: http://twitter.com/dstanek
> >
> > www: http://dstanek.com
> >
> > __
> > OpenStack Development Mailing List (not for usage questions)
> > Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
> > http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
> >
> > __
> > OpenStack Develo

Re: [openstack-dev] [Keystone]ON DELETE RESTRICT VS ON DELETE CASCADE

[Clint Byrum]

Excerpts from Mike Bayer's message of 2015-03-09 10:26:37 -0700:
> 
> Clint Byrum  wrote:
> 
> > Excerpts from David Stanek's message of 2015-03-08 11:18:05 -0700:
> >> On Sun, Mar 8, 2015 at 1:37 PM, Mike Bayer  wrote:
> >> 
> >>> can you elaborate on your reasoning that FK constraints should be used 
> >>> less
> >>> overall?  or do you just mean that the client side should be mirroring the
> >>> same
> >>> rules that would be enforced by the FKs?
> >> 
> >> I don't think he means that we will use them less.  Our SQL backends are
> >> full of them.  What Keystone can't do is rely on them because not all
> >> implementations of our backends support FKs.
> > 
> > Note that they're also a huge waste of SQL performance. It's _far_ cheaper
> > to scale out application servers and garbage-collect using background jobs
> > like pt-archiver than it will ever be to scale out a consistent data-store
> > and do every single little bit of house keeping in real time.  So even
> > on SQL backends, I'd recommend just disabling and dropping FK constraints
> > if you expect any more than the bare minimum usage of Keystone.
> 
> Im about -1000 on disabling foreign key constraints. Any decision based on
> “performance” IMHO has to be proven with benchmarks. Foreign keys on modern
> databases like MySQL and Postgresql do not add overhead to any significant
> degree compared to just the workings of the Python code itself (which means,
> a benchmark here should be illustrating a tangible impact on the python
> application itself). OTOH, the prospect of a database with failed
> referential integrity is a recipe for disaster.   
> 

So I think I didn't speak clearly enough here. The benchmarks are of
course needed, but there's a tipping point when write activity gets to
a certain level where it's cheaper to let it get a little skewed and
correct asynchronously. This is not unique to SQL, this is all large
scale distributed systems. There's probably a super cool formula for it
too, but roughly it is

(num_trans_per_s * cost_of_fk_check_per_trans)

versus

(error_cost * error_rate)+(cost_find_all_errors/seconds_to_find_all_errors)

So it's not really something I think one can blindly accept as "better",
but rather something that one needs to calculate for themselves. You say
cost_of_fk_check_per_trans is negligible, but that has been measured as
not true in the past:

http://www.percona.com/blog/2010/09/20/instrumentation-and-the-cost-of-foreign-keys/

That article demonstrates that the FK adds lock contention in
InnoDB. There's more. With NDB (MySQL cluster) it's an 18% performance
hit on raw throughput:

http://johanandersson.blogspot.com/2013/06/benchmarking-performance-impact-of.html

Though that could be artificially inflated due to being a raw benchmark.

Now, where that point is with Keystone I don't know. The point is, if you
write the code relying on the existence, Keystone becomes a vertically
scaling app that cannot ever scale out beyond whatever that limit is.

__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [Keystone]ON DELETE RESTRICT VS ON DELETE CASCADE

[Mike Bayer]

Clint Byrum  wrote:

> 
> So I think I didn't speak clearly enough here. The benchmarks are of
> course needed, but there's a tipping point when write activity gets to
> a certain level where it's cheaper to let it get a little skewed and
> correct asynchronously. This is not unique to SQL, this is all large
> scale distributed systems. There's probably a super cool formula for it
> too, but roughly it is
> 
> (num_trans_per_s * cost_of_fk_check_per_trans)
> 
> versus
> 
> (error_cost * error_rate)+(cost_find_all_errors/seconds_to_find_all_errors)

Well the error cost here would be a database that would be “corrupted”,
meaning it has rows which no longer refer to things that exist and the
database is now in a case where it may very well be unusable by the
application, without being rolled back to some known state. 

If Keystone truly doesn’t care about ACID it might want to consider MyISAM
tables, which are faster for read-heavy workloads, though these aren’t
compatible with Galera.

> So it's not really something I think one can blindly accept as "better",
> but rather something that one needs to calculate for themselves. You say
> cost_of_fk_check_per_trans is negligible, but that has been measured as
> not true in the past:
> 
> http://www.percona.com/blog/2010/09/20/instrumentation-and-the-cost-of-foreign-keys/

That’s not a surprising case because the “parent” row being modified is
being referred to by the “child” row that’s still in transaction. This is an
implementation detail of the ACID guarantees which one gets when they use a
relational database. If Keystone’s relational backend in fact has a
performance bottleneck due to an operation like this, it should be visited
individually. But I think it’s extremely unlikely this is actually the case.

> That article demonstrates that the FK adds lock contention in
> InnoDB. There's more. With NDB (MySQL cluster) it's an 18% performance
> hit on raw throughput:
> 
> http://johanandersson.blogspot.com/2013/06/benchmarking-performance-impact-of.html

For NDB cluster, foreign key support was only added to that system two years
ago, in version 5.6.10 in 2013. This is clearly not a system designed to
support foreign keys in the first place, the feature is entirely bleeding
edge for that specific system, and performance like that is entirely
atypical outside for database systems outside of NDB cluster. Specifically
with Openstack, the clustering solution usually used is Galera which has no
such performance issue.

So sure, if you’re using NDB cluster, FOREIGN KEY support is
bleeding edge and you may very well want to disable constraints as you’re
using a system that wasn’t designed with this use case in mind. But because
using a relational database is somewhat pointless if you don’t need ACID,
I’d probably use Galera instead.

> 
> Now, where that point is with Keystone I don't know. The point is, if you
> write the code relying on the existence, Keystone becomes a vertically
> scaling app that cannot ever scale out beyond whatever that limit is.

There seems to be some misunderstanding that using foreign keys to enforce
referential integrity seems to imply that the application is now dependent
on these constraints being in place. I notice that the conversation was
originally talking a bit about allowing rows to be deleted using CASCADE,
and my original question referred to the notion of foreign key use
*overall*, not specifically as a means to offer automatic deletion of
related rows with CASCADE.   The use of foreign key constraints
in openstack applications does not imply an unbreakable reliance
upon them at all, for two reasons.

For the first reason, foreign keys first and foremost offer nothing more
than an integrity guarantee that prevents a particular row from being
deleted or having its primary key modified such that other rows which refer
to that primary key would now be left with an invalid reference. At this
level, you can have an application that is working perfectly, you can then
shut off the foreign key constraints entirely, and the application will
continue to work perfectly with no change; the difference is only that if
the application at some point failed, due to bugs either present or newly
introduced, or if an end user tried to tinker with the database directly,
operations which would leave referencing rows hanging will not be blocked.
So the use of foreign keys at this level has nothing to do with the
application relying upon their existence; they are nothing more than
integrity guarantees which can be silently removed.

For the second, within the realm of ON DELETE CASCADE specifically, Keystone
like all other Openstack applications uses SQLAlchemy for relational
persistence. SQLAlchemy supports foreign key constraints that support or
don’t support ON DELETE CASCADE transparently; meaning, whether or not a
foreign key can be relied upon to delete dependent rows is nothing more than
a configuration option in the mappings; SQLAlchemy will 

Re: [openstack-dev] [Keystone]ON DELETE RESTRICT VS ON DELETE CASCADE

[Clint Byrum]

Excerpts from Mike Bayer's message of 2015-03-09 17:26:36 -0700:
> 
> Clint Byrum  wrote:
> 
> > 
> > So I think I didn't speak clearly enough here. The benchmarks are of
> > course needed, but there's a tipping point when write activity gets to
> > a certain level where it's cheaper to let it get a little skewed and
> > correct asynchronously. This is not unique to SQL, this is all large
> > scale distributed systems. There's probably a super cool formula for it
> > too, but roughly it is
> > 
> > (num_trans_per_s * cost_of_fk_check_per_trans)
> > 
> > versus
> > 
> > (error_cost * error_rate)+(cost_find_all_errors/seconds_to_find_all_errors)
> 
> Well the error cost here would be a database that would be “corrupted”,
> meaning it has rows which no longer refer to things that exist and the
> database is now in a case where it may very well be unusable by the
> application, without being rolled back to some known state. 
> 

That's not a cost, that's a situation. What's the actual cost to the
user? "may very well be unusable" implies uncertainty, which is certainly
a risk, but the cost is unknown. Typically one must estimate the cost
with each error found.

> If Keystone truly doesn’t care about ACID it might want to consider MyISAM
> tables, which are faster for read-heavy workloads, though these aren’t
> compatible with Galera.
> 

Please try to refrain from using false equivalence. ACID stands for
Atomicity, Consistency, Isolation, Durability. Nowhere in there does it
stand for "referential integrity". If Keystone uses transactions
properly, ACID is preserved. Also I don't think it is productive to
bring up MyISAM in any serious conversation about databases.

> > So it's not really something I think one can blindly accept as "better",
> > but rather something that one needs to calculate for themselves. You say
> > cost_of_fk_check_per_trans is negligible, but that has been measured as
> > not true in the past:
> > 
> > http://www.percona.com/blog/2010/09/20/instrumentation-and-the-cost-of-foreign-keys/
> 
> That’s not a surprising case because the “parent” row being modified is
> being referred to by the “child” row that’s still in transaction. This is an
> implementation detail of the ACID guarantees which one gets when they use a
> relational database. If Keystone’s relational backend in fact has a
> performance bottleneck due to an operation like this, it should be visited
> individually. But I think it’s extremely unlikely this is actually the case.
> 

Lock contention is a real thing that will inevitably slow down transaction
speed if not carefully avoided. One less query (which is what FK checks
end up being) means one less read lock taken and one less place to have
to think through.

In practical matters, the fact that identity and assignment are not
allowed to FK does practically shutdown most of the real possibilities
of this type of contention.

> > That article demonstrates that the FK adds lock contention in
> > InnoDB. There's more. With NDB (MySQL cluster) it's an 18% performance
> > hit on raw throughput:
> > 
> > http://johanandersson.blogspot.com/2013/06/benchmarking-performance-impact-of.html
> 
> For NDB cluster, foreign key support was only added to that system two years
> ago, in version 5.6.10 in 2013. This is clearly not a system designed to
> support foreign keys in the first place, the feature is entirely bleeding
> edge for that specific system, and performance like that is entirely
> atypical outside for database systems outside of NDB cluster. Specifically
> with Openstack, the clustering solution usually used is Galera which has no
> such performance issue.
> 
> So sure, if you’re using NDB cluster, FOREIGN KEY support is
> bleeding edge and you may very well want to disable constraints as you’re
> using a system that wasn’t designed with this use case in mind. But because
> using a relational database is somewhat pointless if you don’t need ACID,
> I’d probably use Galera instead.
> 

NDB is probably overkill for Keystone until we get up into the millions
of users scale. One day maybe :). The point is that this is a high performance
DB with high performance demands and it is 18% slower for some types of
operations when FK's are added.

> > 
> > Now, where that point is with Keystone I don't know. The point is, if you
> > write the code relying on the existence, Keystone becomes a vertically
> > scaling app that cannot ever scale out beyond whatever that limit is.
> 
> There seems to be some misunderstanding that using foreign keys to enforce
> referential integrity seems to imply that the application is now dependent
> on these constraints being in place. I notice that the conversation was
> originally talking a bit about allowing rows to be deleted using CASCADE,
> and my original question referred to the notion of foreign key use
> *overall*, not specifically as a means to offer automatic deletion of
> related rows with CASCADE.   The use of foreign key constraints
> in o

Re: [openstack-dev] [Keystone]ON DELETE RESTRICT VS ON DELETE CASCADE

[Adam Young]

On 03/09/2015 01:26 PM, Mike Bayer wrote:

Im about -1000 on disabling foreign key constraints.

So was I.  We didn't do it out of performance.

Since I am responsible for tipping over this particular cow, let me explain.

No, is too much. Let me sum up.

In the murky past, Keystone was primarily the identity back end.  I 
start with users, then tenant, and then it grew to have roles.


If this has stayed all in a SQL back end, you bet your sweet bippy I'd 
have left the integrity constraints in place.  THere is a big reason it 
didn't.


My first hack in Keystone was putting LDAP support back in, after the 
Keysteon Light rewrite pulled it out.  Back then, I waws warned that 
LDAP was different, and I kind of knew that it was, but I tried to do 
everything in LDAP we were doing in SQl, and, while the solution was 
bogus, it kindof worked if you squinted and were able to accept putting 
service users in your active directory.


Oh, and didn't want to write to it.  I mean, sure, there was writable 
LDAP. BUt people don't use L:DAP that way.  LDAP is maintained by 
corporate IT...which really means HR.  Bottom line is that the OpenStack 
lab people are not going to be writing projects into their LDAP servers.


At the same time, the abstractions were growing.  We added groups, 
domains, and role assignments.  Federation was in the distance, and 
mapping had to go somewhere.


At the Protland summit, a few conversation made it clear that we needed 
to split the Identity backend into a read only LDAP portion and a SQL 
writable portion.  Oh, sure, you could still keep users in SQL, and many 
people wanted to, but  LDAP was the larger concern, and, again, we knew 
federation was coming with similar constraints. So, a FK from the 
role-assignments table into the proejct table would be OK,  but now to 
either users or groups:  if thiose were in LDAP, there would be nothing 
there, and the constraint could not be met.



We've gone even further this release.  The assignments backend itself is 
being split up.  TBGH, I don't know if this is an essential split, but 
some of the main Keystone developers have worked really hard to make it 
work, and to show how Keystone specific data (role assignments)  can be 
kept separate from the projects and domains.


So, no, we are not talking performance.  We are talking architecture and 
functionality.  Keystone, with few exceptions, does not own the user 
database.  Keystone consumes it.  As time goes on, Keystone will do a 
better job of consume pre-existing data, and minimizing the amount of 
custom data it manages.


Does that make more sense?



__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [Keystone]ON DELETE RESTRICT VS ON DELETE CASCADE

[Mike Bayer]

Clint Byrum  wrote:

> 
> Please try to refrain from using false equivalence. ACID stands for
> Atomicity, Consistency, Isolation, Durability. Nowhere in there does it
> stand for "referential integrity”. 

This point is admittedly controversial as I’ve had this debate before, but
it is common that the database concept of integrity constraints is
considered under the umbrella of “consistency” as one of the facets of this
guarantee. Just check the second sentence of Wikipedia’s page (which I have
been told is itself incorrect, which if so, I would greatly appreciate
someone editing this page as well as their ACID page to remove all
references to “constraints, cascades, and triggers” and perhaps clarify that
these concepts have nothing to do with ACID):
http://en.wikipedia.org/wiki/Consistency_%28database_systems%29

> 
> I'm not entirely sure what you've said above actually prevents coders
> from relying on the constraints. Being careful about deleting all of the
> child rows before a parent is good practice. I have seen code like this
> in the past though:
> 
> try:
>  parent.delete()
> except ForeignKeyFailure:
>  parent.children.delete()
>  parent.delete()
> 
> This means if you don't have the FK's, you may never delete the
> children. Is this a bug? YES. Is it super obvious that it is the wrong
> thing to do? No.

So the point you’re making here is that, if foreign key constraints are
removed, poorly written code might silently fail. I’m glad we agree this is
an issue!  It’s the only point I’m making.



__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [Keystone]ON DELETE RESTRICT VS ON DELETE CASCADE

[Mike Bayer]

Adam Young  wrote:

> On 03/09/2015 01:26 PM, Mike Bayer wrote:
>> Im about -1000 on disabling foreign key constraints.
> So was I.  We didn't do it out of performance.
> 
> Since I am responsible for tipping over this particular cow, let me explain.
> 
> No, is too much. Let me sum up.
> 
> In the murky past, Keystone was primarily the identity back end.  I start 
> with users, then tenant, and then it grew to have roles.
> 
> If this has stayed all in a SQL back end, you bet your sweet bippy I'd have 
> left the integrity constraints in place.  THere is a big reason it didn't.
> 
> My first hack in Keystone was putting LDAP support back in, after the 
> Keysteon Light rewrite pulled it out.  Back then, I waws warned that LDAP was 
> different, and I kind of knew that it was, but I tried to do everything in 
> LDAP we were doing in SQl, and, while the solution was bogus, it kindof 
> worked if you squinted and were able to accept putting service users in your 
> active directory.
> 
> Oh, and didn't want to write to it.  I mean, sure, there was writable LDAP. 
> BUt people don't use L:DAP that way.  LDAP is maintained by corporate 
> IT...which really means HR.  Bottom line is that the OpenStack lab people are 
> not going to be writing projects into their LDAP servers.
> 
> At the same time, the abstractions were growing.  We added groups, domains, 
> and role assignments.  Federation was in the distance, and mapping had to go 
> somewhere.
> 
> At the Protland summit, a few conversation made it clear that we needed to 
> split the Identity backend into a read only LDAP portion and a SQL writable 
> portion.  Oh, sure, you could still keep users in SQL, and many people wanted 
> to, but  LDAP was the larger concern, and, again, we knew federation was 
> coming with similar constraints. So, a FK from the role-assignments table 
> into the proejct table would be OK,  but now to either users or groups:  if 
> thiose were in LDAP, there would be nothing there, and the constraint could 
> not be met.
> 
> 
> We've gone even further this release.  The assignments backend itself is 
> being split up.  TBGH, I don't know if this is an essential split, but some 
> of the main Keystone developers have worked really hard to make it work, and 
> to show how Keystone specific data (role assignments)  can be kept separate 
> from the projects and domains.
> 
> So, no, we are not talking performance.  We are talking architecture and 
> functionality.  Keystone, with few exceptions, does not own the user 
> database.  Keystone consumes it.  As time goes on, Keystone will do a better 
> job of consume pre-existing data, and minimizing the amount of custom data it 
> manages.
> 
> Does that make more sense?

Somewhat vaguely. If by "So, a FK from the role-assignments table into the
proejct table would be OK, but now to either users or groups: if thiose were
in LDAP, there would be nothing there, and the constraint could not be
met.”, we mean that we start with this:

create table project (
   id integer primary key
)

create table users (
   id integer primary key
)

create table groups (
   id integer primary key
)

create table role_assignments (
id integer primary key
project_id integer references project(id)
)


and then we change it, such that we are really doing this:

create table role_assignments (
id integer primary key
project_or_group_or_user_id integer
)

if *that’s* what you mean, that’s known as a “polymorphic foreign key”, and
it is not actually a foreign key at all, it is a terrible antipattern started by
the PHP/Rails community and carried forth by projects like Django. For
details on how to correct for this pattern, I wrote about it many years ago
here:
http://techspot.zzzeek.org/2007/05/29/polymorphic-associations-with-sqlalchemy/.
SQLAlchemy has an example suite that illustrates several approaches to
mitigating this anti pattern which you can see here:
http://docs.sqlalchemy.org/en/rel_0_9/orm/examples.html#module-examples.generic_associations.

So if that’s what we mean, then that is exactly what I’m trying to target as
a “don’t do that” situation; it’s unnecessary and incorrect. LDAP and SQL
databases are obviously very different, so in order to achieve parity
between them, a lot of work has to be done on the SQL side in particular as
it is much more structured than LDAP. If we are diluting our SQL databases
to turn into amorphous, unstructured blobs, then I think that’s a very bad
idea and I’m not sure why relational databases have any place, when
unstructured solutions like MongoDB are readily available. I’d note that
LDAP servers themselves will often use relational storage as their actual
backend, and you can be assured these systems can make correct use of
normalization internally.

> __
> OpenStack Development Mailing List (not for usage questions)
> Unsubscribe: openstack-dev-requ...@lists.openstack.org?s

Re: [openstack-dev] [Keystone]ON DELETE RESTRICT VS ON DELETE CASCADE

[Mike Bayer]

Mike Bayer  wrote:

> 
>> I'm not entirely sure what you've said above actually prevents coders
>> from relying on the constraints. Being careful about deleting all of the
>> child rows before a parent is good practice. I have seen code like this
>> in the past though:
>> 
>> try:
>> parent.delete()
>> except ForeignKeyFailure:
>> parent.children.delete()
>> parent.delete()
>> 
>> This means if you don't have the FK's, you may never delete the
>> children. Is this a bug? YES. Is it super obvious that it is the wrong
>> thing to do? No.
> 
> So the point you’re making here is that, if foreign key constraints are
> removed, poorly written code might silently fail. I’m glad we agree this is
> an issue!  It’s the only point I’m making.

I apologize for my snark here. The above code is wrong, and I think it is
obviously wrong. People working on this code should be familiar with
SQLAlchemy basics (at least having read the ORM tutorial), and that includes
the very easy to use features of relationship management.

Even if we are dealing with a version of the above that does not use
SQLAlchemy, it should be apparent that a DELETE should be emitted for the
child rows whether or not they’ve been tested as existing, if we are
deleting on the criteria of “parent_id”. Code like the above should ideally
never get through review, and if code like that exists right now, it should
be fixed.

What foreign key guarantees get us for the above would be for the much
more common case that someone emits a DELETE for the parent row
without being at all aware that there are dependent rows present.  That
silent failure leaves those child rows as orphans which will
lead to application failures when accessed, assuming the application 
also attempts to access the referenced parent.


__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [Keystone]ON DELETE RESTRICT VS ON DELETE CASCADE

[Clint Byrum]

Excerpts from Mike Bayer's message of 2015-03-10 08:35:23 -0700:
> 
> Mike Bayer  wrote:
> 
> > 
> >> I'm not entirely sure what you've said above actually prevents coders
> >> from relying on the constraints. Being careful about deleting all of the
> >> child rows before a parent is good practice. I have seen code like this
> >> in the past though:
> >> 
> >> try:
> >> parent.delete()
> >> except ForeignKeyFailure:
> >> parent.children.delete()
> >> parent.delete()
> >> 
> >> This means if you don't have the FK's, you may never delete the
> >> children. Is this a bug? YES. Is it super obvious that it is the wrong
> >> thing to do? No.
> > 
> > So the point you’re making here is that, if foreign key constraints are
> > removed, poorly written code might silently fail. I’m glad we agree this is
> > an issue!  It’s the only point I’m making.
> 
> I apologize for my snark here. The above code is wrong, and I think it is
> obviously wrong. People working on this code should be familiar with
> SQLAlchemy basics (at least having read the ORM tutorial), and that includes
> the very easy to use features of relationship management.
> 

No need to apologize, and I appreciate very much what point you're
making. They have a benefit, and I didn't mean to imply they don't when
I first suggested they be disabled and dropped. The point I'm making is,
their benefits can often be outweighed by their costs. Keystone is already
feeling a bit of the cost of rigidity with a separation of things into
disparate backends.

Anyway, I hope we can end this with a better understanding for everyone.
They're not always a good idea, and they're not always a bad idea.

__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [Keystone]ON DELETE RESTRICT VS ON DELETE CASCADE

[Adam Young]

On 03/10/2015 10:23 AM, Mike Bayer wrote:

if*that’s*  what you mean, that’s known as a “polymorphic foreign key”, and
it is not actually a foreign key at all, it is a terrible antipattern started by
the PHP/Rails community and carried forth by projects like Django.
A) Heh. it is much, much older than that.  SQL Database have been around 
for long enough for these antipatterns to be discovered and rediscovered 
by multiple generations.  I'm aware of the mean by which we cn mitigate 
them.


But that is not what we are doing here.  These are no "parity" issues 
even.  It is distributed data.


User sand Groups are in, not just one LDAP server,  but many.  With 
Federation, the users  will not even be in a system we can enumerate.  
Which is good, we should never have been allowing "list users" in the 
first place.


What the Assignments table is doing is pulling together the User and 
groups from remote systems together with role defintions and project 
definitions in the local database.  The data is not in one database.  It 
is in Many.
__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [Keystone]ON DELETE RESTRICT VS ON DELETE CASCADE

[Mike Bayer]

Adam Young  wrote:

> On 03/10/2015 10:23 AM, Mike Bayer wrote:
>> if *that’s*
>>  what you mean, that’s known as a “polymorphic foreign key”, and
>> it is not actually a foreign key at all, it is a terrible antipattern 
>> started by
>> the PHP/Rails community and carried forth by projects like Django. 
> A) Heh. it is much, much older than that.  SQL Database have been around for 
> long enough for these antipatterns to be discovered and rediscovered by 
> multiple generations.  I'm aware of the mean by which we cn mitigate them. 
> 
> But that is not what we are doing here.  These are no "parity" issues even.  
> It is distributed data.
> 
> User sand Groups are in, not just one LDAP server,  but many.  With 
> Federation, the users  will not even be in a system we can enumerate.  Which 
> is good, we should never have been allowing "list users" in the first place.
> 
> What the Assignments table is doing is pulling together the User and groups 
> from remote systems together with role defintions and project definitions in 
> the local database.  The data is not in one database.  It is in Many.

Of course, if you are referring to data that is related to rows in a
*remote* database, either another SQL database or something else like an
LDAP, you need not have any kind of “foreign key” setup. This is normally
technically infeasible in any case unless you want to do remote schema
access (which you do not). Although I do recommend that referring to remote
databases be done using some kind of portable identifier, usually a GUID,
and not the auto incrementing integers that are local to the schema.

If I can summarize the proposed reasons to remove foreign keys, since my
original question was, "can you elaborate on your reasoning that FK
constraints should be used less overall?”, and I just want to make sure 
I’ve got my answer:

1. You have modified your schema such that you are no longer pointing to a
remote record, or that remote record is not in the local database. This
reason is perfectly fine.

2. You’ve benchmarked that the presence of foreign keys is making your
application measurably slower. This reason is perfectly fine, but I don’t
believe Keystone would ever see this (but since we’d be measuring, it
doesn’t matter what I think).

3. You still have tables/rows that refer to each other, but you either have
changed it to not refer to the primary key (note that at least Postgresql
supports foreign keys to UNIQUE columns), or it still refers to the primary
key but the FK is just removed for arbitrary, non-performance based reasons,
or you’re doing some non-normalized, relationally incorrect thing like a 
“polymorphic foreign key”.  I’m totally against these reason, but it does not 
seem 
like this is being proposed.  

So I think Morgan’s original idea was that, for those inter-table
relationships where we refer to something that could just as well come from
a remote datasource, we should not assume any foreign key is present. That
is great. My only point is, if for the time being there *are* two tables
there with a column that refers to the PK of the other, unless you’ve proven
a performance issue, a plain, non-CASCADING foreign key should be left in
place, even though the application should in no way assume this FK is 
present.   Obviously any kind of application logic that makes use of 
an integrity error to learn something about the database should be removed.


__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev