Re: Idea: Avoid JOINs by using path expressions to follow FKs

On Tue, Mar 30, 2021, at 22:01, Isaac Morland wrote:
> On Tue, 30 Mar 2021 at 15:33, Joel Jacobson <joel(at)compiler(dot)org> wrote:
>>> Also, should the join be a left join, which would therefore return a NULL when there is no matching record? Or could we have a variation such as ->? to give a left join (NULL when no matching record) with -> using an inner join (record is not included in result when no matching record).
>>
>> Interesting idea, but I think we can keep it simple, and still support the case you mention:
>>
>> If we only have -> and you want to exclude records where the column is NULL (i.e. INNER JOIN),
>> I think we should just use the WHERE clause and filter on such condition.
>>
>
> Just to be clear, it will always be a left join? Agreed that getting the inner join behaviour can be done in the WHERE clause. I think this is a case where simple is good. As long as the left join case is supported I'm happy.

Hmm, I guess, since technically, if all foreign key column(s) are declared as NOT NULL, we would know for sure such values exist, so a LEFT JOIN and INNER JOIN would always produce the same result.
I'm not sure if the query planner could produce different plans though, and if an INNER JOIN could be more efficient. If it matters, then I think we should generate an INNER JOIN for the "all column(s) NOT NULL" case.

>
>> Thanks for the encouraging words. I have exactly the same experience myself and share your view.
>>
>> I look forward to continued discussion on this matter.
>
> I had another idea: maybe the default name of a foreign key constraint to a primary key should simply be the name of the target table? That is, if I say:
>
> FOREIGN KEY (...) REFERENCES t
>
> ... then unless the table name t is already in use as a constraint name, it will be used as the constraint name. It would be nice not to have to keep repeating, like this:
>
> CONSTRAINT t FOREIGN KEY (...) REFERENCES t
>

I suggested earlier in the thread to allow making the default name format user-definable,
since some users according to the comment in pg_constraint.c might depend on apps that rely on the name
being unique within the namespace and not just the table.

Here is the commit that implemented this:

commit 45616f5bbbb87745e0e82b00e77562d6502aa042
Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Date: Thu Jun 10 17:56:03 2004 +0000

Clean up generation of default names for constraints, indexes, and serial
sequences, as per recent discussion. All these names are now of the
form table_column_type, with digits added if needed to make them unique.
Default constraint names are chosen to be unique across their whole schema,
not just within the parent object, so as to be more SQL-spec-compatible
and make the information schema views more useful.

So if nothing has changed since then, I don't think we should change the default name for all users.
But like I said earlier, I think it would be good if users who know what they are doing could override the default name format.

/Joel