Re: Idea: Avoid JOINs by using path expressions to follow FKs - Mailing list pgsql-hackers

From Joel Jacobson
Subject Re: Idea: Avoid JOINs by using path expressions to follow FKs
Date
Msg-id 9db6d619-522f-457b-8f28-3d7730c6a26d@www.fastmail.com
Whole thread Raw
In response to Re: Idea: Avoid JOINs by using path expressions to follow FKs  (Pavel Stehule <pavel.stehule@gmail.com>)
Responses Re: Idea: Avoid JOINs by using path expressions to follow FKs  (Pavel Stehule <pavel.stehule@gmail.com>)
List pgsql-hackers
On Tue, Mar 30, 2021, at 10:24, Pavel Stehule wrote:


I think this is a good example of where this improves the situation the most,
when you have multiple joins of the same table, forcing you to come up with multiple aliases
for the same table, keeping them all in memory while writing and reading such queries.

...
I remember multiple self joins only when developers used an EAV model. This is an antipattern, and today we have better tools, and we don't need it.  It is scary, because it is completely against the relational model.

No, you are mistaken. There are no self-joins in any of the examples I presented.
I merely joined in the same table multiple times, but not with itself, so it's not a self join.

Here is the query again, it doesn't contain any self-joins:

    SELECT
      format
      (
        'ALTER TABLE %I.%I RENAME CONSTRAINT %I TO %I;',
        conrel_nsp.nspname,
        conrel.relname,
        pg_constraint.conname,
        confrel.relname
      ) AS sql_cmd,
      COUNT(*) OVER (PARTITION BY pg_constraint.conrelid, pg_constraint.confrelid)
      AS count_foreign_keys_to_same_table
    FROM pg_constraint
    JOIN pg_class AS conrel
      ON conrel.oid = pg_constraint.conrelid
    JOIN pg_class AS confrel
      ON confrel.oid = pg_constraint.confrelid
    JOIN pg_namespace AS conrel_nsp
      ON conrel_nsp.oid = conrel.relnamespace
    WHERE pg_constraint.contype = 'f'

Where would the antipattern be here?

/Joel

pgsql-hackers by date:

Previous
From: Hanefi Onaldi
Date:
Subject: RE: [EXTERNAL] Any objection to documenting pg_sequence_last_value()?
Next
From: Peter Smith
Date:
Subject: Use consistent terminology for tablesync slots.