Re: Add primary keys to system catalogs

Dear Joel,

I would love to start working on this again, I tried to revive the patch
again in 2019, however, I faced the same problems as last time (detailed in
the thread) and I didn't get the support I needed to continue with this
patch.

Are you willing to help rebase and finally publish this patch?

Best Regards,
Mark Rofail

On Tue, 19 Jan 2021 at 2:22 PM Joel Jacobson <joel(at)compiler(dot)org> wrote:

> On Mon, Jan 18, 2021, at 18:23, Tom Lane wrote:
> > I realized that there's a stronger roadblock for
> > treating catalog interrelationships as SQL foreign keys. Namely,
> > that we always represent no-reference situations with a zero OID,
> > whereas it'd have to be NULL to look like a valid foreign-key case.
>
> Another roadblock is perhaps the lack of foreign keys on arrays,
> which would be needed by the following references:
>
> SELECT * FROM oidjoins WHERE column_type ~ '(vector|\[\])$' ORDER BY 1,2,3;
> table_name | column_name | column_type | ref_table_name |
> ref_column_name
>
> ----------------------+----------------+-------------+----------------+-----------------
> pg_constraint | conexclop | oid[] | pg_operator | oid
> pg_constraint | conffeqop | oid[] | pg_operator | oid
> pg_constraint | confkey | int2[] | pg_attribute |
> attnum
> pg_constraint | conkey | int2[] | pg_attribute |
> attnum
> pg_constraint | conpfeqop | oid[] | pg_operator | oid
> pg_constraint | conppeqop | oid[] | pg_operator | oid
> pg_extension | extconfig | oid[] | pg_class | oid
> pg_index | indclass | oidvector | pg_opclass | oid
> pg_index | indcollation | oidvector | pg_collation | oid
> pg_index | indkey | int2vector | pg_attribute |
> attnum
> pg_partitioned_table | partattrs | int2vector | pg_attribute |
> attnum
> pg_partitioned_table | partclass | oidvector | pg_opclass | oid
> pg_partitioned_table | partcollation | oidvector | pg_collation | oid
> pg_policy | polroles | oid[] | pg_authid | oid
> pg_proc | proallargtypes | oid[] | pg_type | oid
> pg_proc | proargtypes | oidvector | pg_type | oid
> pg_statistic_ext | stxkeys | int2vector | pg_attribute |
> attnum
> pg_trigger | tgattr | int2vector | pg_attribute |
> attnum
> (18 rows)
>
> I see there is an old thread with work in this area, "Re: GSoC 2017:
> Foreign Key Arrays":
>
>
> https://www.postgresql.org/message-id/flat/76a8d3d8-a22e-3299-7c4e-6e115dbf3762%40proxel.se#a3ddc34863465ef83adbd26022cdbcc0
>
> The last message in the thread is from 2018-10-02:
> >On Tue, 2 Oct, 2018 at 05:13:26AM +0200, Michael Paquier wrote:
> >>On Sat, Aug 11, 2018 at 05:20:57AM +0200, Mark Rofail wrote:
> >> I am still having problems rebasing this patch. I can not figure it out
> on
> >> my own.
> >Okay, it's been a couple of months since this last email, and nothing
> >has happened, so I am marking it as returned with feedback.
> >--
> >Michael
>
> Personally, I would absolutely *love* FKs on array columns.
> I always feel shameful when adding array columns to tables,
> when the elements are PKs in some other table.
> It's convenient and often the best design,
> but it feels dirty knowing there are no FKs to help detect application
> bugs.
>
> Let's hope the current FKs-on-catalog-discussion can ignite the old
> Foreign Key Arrays thread.
>
>
> /Joel
>