Ability to generate queries with distinct/group by

[daurnimator]

PostgREST should have a way to perform a SELECT DISTINCT query.

I suggest a new reserved query word 'distinct'.

• With no value it would just do a select distinct e.g. /mytable?distinct
• Otherwise it is a comma separated list of columns (or computed columns) which would be used inside of SELECT DISTINCT ON(....) e.g. /mytable?distinct=name would be SELECT DISTINCT ON(name) * FROM mytable

Note that the columns that you are distinct on will also need to have order applied.

[begriffs]

This has certainly been a frequently requested feature. My only hesitation is making "distinct" another reserved word, meaning it would block any filters on a column that happened to be called "distinct." However, like "select," this one is a sql reserved word too so it makes sense that we reserve it as a special url parameter.

I hesitate to try messing with the url parsing and query generation part of the code myself, that's more @steve-chavez's domain. Perhaps he can estimate the difficulty of this feature.

[steve-chavez]

Not sure if this should be implemented, by allowing distinct to be applied to any column unrestricted clients could potentially ddos a database.

I've bumped into a slow distinct query in postgresql a while ago and solved it by using a group by instead of distinct, remember distinct generating a more expensive seq scan, I don't have the details anymore but a quick googling suggest the problem could still persist:

https://dba.stackexchange.com/questions/93158/how-to-speed-up-select-distinct
https://dba.stackexchange.com/questions/63268/postgresql-being-slow-on-count-distinct-for-dates

As mentioned in one of the answers, you can mostly avoid doing distinct with better database-design or better queries.

So in general I think distinct is a expensive operation(similar to group by) and it should be better exposed behind a view or a stored procedure.

[ruslantalpa]

@steve-chavez this is a good point against this. PostgrREST should expose only safe/fast operations

[russelldavies]

For what it's worth, DISTINCT is just syntactic sugar for GROUP BY and I think in modern versions of postgres it's even implemented using the same code. (And DISTINCT ON is a postgres extension from way back that's a bit of a performance hack). So @steve-chavez's conclusion is correct.

[daurnimator]

As mentioned in one of the answers, you can mostly avoid doing distinct with better database-design or better queries.

I have many situations where I've wished for postgrest to have a distinct operator.
e.g. a table that contains events (timestamp, device, event_type, data), I want the latest event for each device subject to filters such as timestamp/event_type. So how would you allow a query to be written that accomplishes this? with my proposal it would be http://myapp.com/api/events?timestamp=lt.2017-08-08&event_type=eq.alarm&order=timestamp.desc&distinct=device

[steve-chavez]

The query would have to be behind a stored procedure/view, it's definitely necessary to use distinct for some cases, but as I've mentioned before exposing distinct unrestricted poses a threat for the database, a similar argument has been made for group by support in #158 (comment).

[daurnimator]

The query would have to be behind a stored procedure/view,

When it's behind a view/stored procedure then you can no longer support the arbitrary filters that I love about postgrest.

it's definitely necessary to use distinct for some cases,

In that case I think it's a requirement that we include the functionality.

but as I've mentioned before exposing distinct unrestricted poses a threat for the database

If you are afraid of performance issues, then perhaps we can only allow distinct when the jwt claim (or other setting?) permits it?
However I think there are already performance questions around postgrest: for a large table, the default route with no filters will be a huge response that may slow the database down to build.

[steve-chavez]

You can create a stored procedure that returns setof events and apply filters like in a table see the tests, the same for a view you can create it as in: create view distinct_devices as select distinct device, timestamp.. and do regular filters on that.

[daurnimator]

You can create a stored procedure that returns setof events and apply filters like in a table see the tests, the same for a view you can create it as in: create view distinct_devices as select distinct device, timestamp.. and do regular filters on that.

But the filtering needs to happen before the distinct operation

[ruslantalpa]

There will always be a fight between the two opposing needs expose more sql features vs protecting the database, striking the balance is hard. in this particular thread i am with @steve-chavez for now.

No if features like this http://www.databasesoup.com/2015/02/why-you-might-need-statementcostlimit.html make it into the core, we might relax a bit and expose more of the sql, in that situation i would go with implementing the groupby (while at the same time, having the default setting for cost limit to a low value).

should we close this for now? i don't see it going further in the immediate future.

[daurnimator]

This is probably my most wanted feature; and it certainly gets requested a lot in the chat room.

Perhaps we could have a toggle in the config file allow_distinct for the performance concerned?

[steve-chavez]

@ruslantalpa I also think that the idea of the cost limit is a good one, though is not that reliable a max limit could be worked out, that also is a must for other features like multiple requests in a transaction, I made a related comment on "Customize query timeouts" #249, maybe we should continue there with the discussion and see if we can implement that feature independently.

@daurnimator That toggle could be easily abused by the users, I don't think it should be added to the config, this phrase summarizes my reasoning:

A well-designed system makes it easy to do the right things and annoying (but not impossible) to do the wrong things.

Users should be thinking twice before exposing expensive operations as distinct, as it is now, PostgREST should not make it easy for users to make those kind of mistakes.

[russelldavies]

@daurnimator Have you had a look at pREST? It exposes more SQL directly, at the cost of potential performance issues, so may satisfy your DISTINCT needs via its GROUP BY support.