Re: \describe* - Mailing list pgsql-hackers
| From | Corey Huinker |
|---|---|
| Subject | Re: \describe* |
| Date | |
| Msg-id | CADkLM=f_K+mi-MJwnv-3MyTvLbz8zQfajtfFE2ecEsuQ7AUHvg@mail.gmail.com Whole thread Raw |
| In response to | Re: \describe* (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: \describe*
(Thomas Munro <thomas.munro@gmail.com>)
|
| List | pgsql-hackers |
> So what is the uptake on implementing this at the server side, ie.
> DESCRIBE?
I'm pretty skeptical of this idea, unless you are willing to throw
away at least one and possibly both of the following goals:
1. Compatibility with psql's existing \d behavior.
I don't think compatibility with the behavior should be a goal in itself.
Coverage of the majority of the use-cases is.
2. Usability of DESCRIBE for any purpose whatsoever other than emitting
something that looks just like what psql prints.
We've migrated many of the \d displays so far away from "a single query
result" that I don't believe there's a way for a server command to
duplicate them, at least not without some seriously unholy in-bed-ness
between the server command and some postprocessing logic in describe.c.
(At which point you've lost whatever system architectural value there
might be in the whole project, since having a more-arm's-length
relationship there kinda seems like the point to me.)
I think there's a genuine use for regular printed output, and there's also a use for a query-able output. Maybe that queryable output is just a JSONB output that the outer query can pick apart as it sees fit, and that would handle the fact that the data often doesn't fit into a single query's output.
Incidentally, I had need of this very functionality in Snowflake the other day. The data dictionary there isn't capable of telling you which columns are in a primary key, but that information is printed when you run "DESCRIBE my_table". The workaround is to run "DESCRIBE my_table" and then make another query using a table function to recall the output of the last query made in the session, and then filter that. Yeah, as a pattern it's weird and sad, but it shows that there's are uses for something DESCRIBE-ish on the server side.
So if we're going servier-side on DESCRIBE, it should be it's own entity, not beholden to design decisions made in psql.
There are a bunch of other little behavioral differences that you just
can't replicate server-side, like the fact that localization of the
results depends on psql's LC_MESSAGES not the server's. Maybe people
would be okay with changing that, but it's not a transparent
reimplementation.
I think people would be OK with that. We're asking the server what it knows about an object, not how psql feels about that same information.
I think if we want to have server-side describe capability, we're better
off just to implement a DESCRIBE command that's not intended to be exactly
like \d anything, and not try to make it be the implementation for \d
anything. (This was, in fact, where David started IIUC. Other people's
sniping at that idea hasn't yielded any better idea.)
I'm very much in support of server-side DESCRIBE that's not beholden to \d in any way. For instance, I'm totally fine with DESCRIBE not being able to handle wildcard patterns.
My initial suggestion for client-side \describe was mostly borne of it being easy to implement a large subset of the \d commands to help users. Not all users have psql access, so having a server side command helps more people.
It could be that we decide that DESCRIBE is set-returning, and we have to break up \d functionality to suit. By this I mean that we might find it simpler to require DESCRIBE TABLE foo to only show columns with minimal information about PKs and follow up commands like "DESCRIBE TABLE foo INDEXES" or "DESCRIBE TABLE foo CONSTRAINTS" to keep output in tabular format.
In particular, I'm really strongly against having "\describe-foo-bar"
invoke DESCRIBE, because (a) that will break compatibility with the
existing \des command, and (b) it's not actually saving any typing,
and (c) I think it'd confuse users no end.
+1. Having psql figure out which servers can give proper servier-side-describes would boggle the mind.
Of course, this line of thought does lead to the conclusion that we'd be
maintaining psql/describe.c and server-side DESCRIBE in parallel forever,
Not fun, but what's our motivation for adding new new \d functionality once a viable DESCRIBE is in place? Wouldn't the \d commands essentially be feature-frozen at that point?
which doesn't sound like fun. But we should be making DESCRIBE with an
eye to more use-cases than psql. If it allows jdbc to not also maintain
a pile of equivalent code, that'd be a win. If it allows pg_dump to toss
a bunch of logic overboard (or at least stop incrementally adding new
variants), that'd be a big win.
I don't know enough about JDBC internals to know what sort of non-set results it can handle, but that seems key to showing us how to proceed.
As for pg_dump, that same goal was a motivation for a similar server-side command "SHOW CREATE <object>" (essentially, pg_dump of <object>) which would have basically the same design issues as DESCRIBE would, though the result set would be a much simpler SETOF text.
pgsql-hackers by date: