Horton

Re: partitioned tables referenced by FKs

Robert Haas <robertmhaas@gmail.com>

View thread View on postgr.es
From: Robert Haas <robertmhaas@gmail.com>
To: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Amit Langote <amitlangote09@gmail.com>, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>, Pg Hackers <pgsql-hackers@postgresql.org>
Date: 2019-03-29T13:56:09Z
Lists: pgsql-hackers 
On Wed, Mar 20, 2019 at 11:58 AM Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:
> constraint is dropped.  I can only think of ugly data structure to
> support this, and adding enough hooks in dependency.c to support this is
> going to be badly received.

I don't know why dependency.c doesn't handle this internally.  If I
say that I want to delete a list of objects, some of which can't be
dropped without dropping certain other things, dependency.c ought to
be able to suspend judgement on what the problems are until it's
looked over that whole list.  It seems to me that we've had to fight
with this problem before, and I don't know why every individual bit of
code that calls into that file has to be responsible for working
around it individually.

> I think a better idea is to prevent DROP of a partition that's
> referenced by a foreign key, period.  We would only allow ALTER..DETACH
> (they can drop the partition once it's detached).  It seems to me that
> the check can be done more naturally during detach than during drop,
> because we're not constrained to do it within the dependency.c system.

IMHO, that sucks a lot.  Maybe we should live with it anyway, but it does suck.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Commits

  1. Support foreign keys that reference partitioned tables

  2. Fix partitioned index creation bug with dropped columns

  3. Improve psql's \d display of foreign key constraints

  4. Fix dependency recording bug for partitioned PKs

  5. Add index_get_partition convenience function

  6. Restore RI trigger sanity check

  7. Foreign keys on partitioned tables