On Sat, Jul 22, 2017 at 5:50 PM, Mark Rofail <markm.rof...@gmail.com> wrote: > so personally I don't think we should leave creating a GIN index up to the > user, it should be automatically generated instead.
I can certainly understand why you feel that way, but trying to do that in your patch is just going to get your patch rejected. We don't want array foreign keys to have different behavior than regular foreign keys, and regular foreign keys don't do this automatically. We could change that, but I suspect it would cause us some pretty serious problems with upgrades from older versions with the existing behavior to newer versions with the revised behavior. There are other problems, too. Suppose the user creates the foreign key and then drops the associated index; then, they run pg_dump. Will restoring the dump recreate the index? If so, then you've broken dump/restore, because now it doesn't actually recreate the original state of the database. You might think of fixing this by not letting the index be dropped, but that's problematic too, because a fairly-standard way of removing index bloat is to create a new index with the "concurrently" flag and then drop the old one. Another problem entirely is that the auto-generated index will need to have an auto-generated name, and that name might happen to conflict with the name of some other object that already exists in the database, which doesn't initially seem like a problem because you can just generate a different name instead; indeed, we already do such things. But the thorny point is that you have to preserve whatever name you choose -- and the linkage to the array foreign key that caused it to be created -- across a dump/restore cycle; otherwise you'll have cases where conflicting names cause failures. I doubt this is a comprehensive list of things that might go wrong; it's intended as an illustrative list, not an exhaustive one. This is a jumbo king-sized can of worms, and even a very experienced contributor would likely find it extremely difficult to sort all of the problems that would result from a change in this area. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
