Thread: pgsql: Add DISTINCT to information schema usage views

Add DISTINCT to information schema usage views

Since pg_depend can contain duplicate entries, we need to eliminate
those in information schema views that build on pg_depend, using
DISTINCT.  Some of the older views already did that correctly, but
some of the more recently added ones didn't.  (In some of these views,
it might not be possible to reproduce the issue because of how the
implementation happens to deduplicate dependencies while recording
them, but it seems better to keep this consistent in all cases.)

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/d84ffffe582b8e036a14c6bc2378df29167f3a00

Modified Files
--------------
src/backend/catalog/information_schema.sql | 18 ++++++++++++------
src/include/catalog/catversion.h           |  2 +-
2 files changed, 13 insertions(+), 7 deletions(-)

On Wed, 21 Apr 2021 at 22:32, Peter Eisentraut <peter@eisentraut.org> wrote:
> Add DISTINCT to information schema usage views

What do you think of the idea of just getting rid of all these
DISTINCTs and instead do a semi-join so that we don't get duplicate
rows?

For me, I don't really like the DISTINCTs as the behaviour is prone to
change as the target list changes in the view.  Maybe these views are
standard enough that that's not going to happen very often, but it
could.

The 2nd reason I'm not a fan of using DISTINCT is that it's really
chopping the problem off at the tail instead of the head.

I've attached a patch that gets rid of all the DISTINCTs.

David