Re: \describe*

>
> > 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.