I always thought that most \-commands in psql were just frontends for queries? For example:
% psql db_name
(1)=# \set ECHO_HIDDEN
(1)=# \d
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' TH
EN 'sequence' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partit
ioned index' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('r','p','v','m','S','f','')
AND n.nspname <> 'pg_catalog'
AND n.nspname !~ '^pg_toast'
AND n.nspname <> 'information_schema'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
List of relations
Schema β Name β Type β Owner
βββββββββΌβββββββββββββββββββββββββββββββββββββββββΌβββββββββββΌββββββββ
...
I re-implemented quite a bit of that in a PostgreSQL management tool I built, based just on the ECHO_HIDDEN queries. Never even had to look at any C code.
I'm not sure if I entirely follow what translating C to JS gives you, other than a bit of (fairly simple) parsing of \cmd and flags? I didn't look too carefully at the code, so maybe I'm missing something.
arp242
12 days ago
If you just need one or two of the commands, just using those queries directly is easy.
If you want to support all of them, including handling all the different Postgres versions, it becomes quite a lot to implement.
matharmin
12 days ago
Some commands issue multiple queries and include logic for stitching the results into a nice table. Thereβs not a one-to-one mapping from commands to queries by any means.
gmac
12 days ago
Yeah, that's right; but the "glue code" I've had to write for this has been really minimal. Certainly less effort than translating C to JavaScript with regexps (that on its own is something of an accomplishment). I didn't port everything so maybe I missed some more complex bits.
I mean, if this works well for you then that's grand; just wanted to point out to others that \-commands are typically very "non-magic" and you don't need to go the route of translating C code.
arp242
12 days ago
Certainly thereβs a decently long tail of rarely-used commands, obscure features of commands, and multiple code paths supporting multiple server versions.
gmac
12 days ago
Yeah, I don't understand why they'd translate the C code instead of just writing catalog/info schema queries, which seems easier AND more educational.
meepmorp
12 days ago
[flagged]
zX41ZdbW
12 days ago
Almost every single post you make mentions clickhouse. You've replied to a few of my posts now with this vaguely on-topic forceful injection of clickhouse.
Of course I'm not saying you can never bring up clickhouse, but at this point it's just becoming spam. High-effort spam, maybe. But still spam.
arp242
12 days ago
Wow, you are right. Nice catch.
millerm
12 days ago
I'm the author of ClickHouse, and this topic is interesting to me.
zX41ZdbW
12 days ago
I am aware. But this thread is not about Clickhouse. And your comment contributed nothing to the general conversation either. The general pattern is really not appropriate.
arp242
11 days ago
Thank you! Let's take it as a comment about comparing styles in different database CLIs.
Some have \-commands, and some have .-commands, which I found hacky - because it is a micro language notation in addition to the actual language.
zX41ZdbW
11 days ago
When you keep "comparing" tons of stuff to the project you wrote then you're just spamming. Like I said, this is hardly the first time I've seen it, and that's just from random posts I've seen.
arp242
11 days ago
Sorry for distracting you. My apologies.
I will try to remember your login and not answer your comments in the future.
At the same time, it is possible that I will occasionally answer your comments by mistake.
Cheers, Alexey.
zX41ZdbW
10 days ago
The fact that this is not a manual reimplementation, but a "transpilation" of the psql source from C to JavaScript, makes this super interesting to me.
From the Postgres master branch (17devel), we take exec_command_d, exec_command_list and exec_command_sf_sv from command.c, and all of describe.c and sql_help.c, from src/bin/psql.
We use plenty of RegExp search-and-replace to turn this C code into valid JS syntax.
We implement some C library functions, such as strlen and strchr, and some Postgres support functions, such as printTable and printQuery, in JavaScript.
When I started my programming journey I thought I could just "translate" Pascal to C++ just with search/replace and minor adjustments. Turns out sometimes it's the best approach :)
This seems really cool, but also a nightmare to maintain. What are the plans for keeping this in sync with the upstream codebase?
darby_eight
12 days ago
OP (and library author) here. This is a fair point, and one I freely admit to in the accompanying blog post. Itβs probably not crucial to sync more than once per major Postgres version, but weβre still figuring out how weβll deal with this.
gmac
12 days ago
uses emscripten to compile the C to WASM, so itβs automated.
sroussey
12 days ago
Thw convertet is nice, I could imagine it being it's own library, with the generator, and a release including the generated code every postures release.
stuaxo
11 days ago
I'm not sure I understand how to use that.
codesnik
12 days ago
Itβs used in Neonβs web-based console to support \d and friends alongside regular SQL queries.
You might well never need to use it, but if youβre building an online query editor it could be very handy!
gmac
12 days ago
Yeah, I don't really get where / how I would use this. The documentation is really lacking on outlining the usage / use-case.
I always thought that most \-commands in psql were just frontends for queries? For example:
I re-implemented quite a bit of that in a PostgreSQL management tool I built, based just on the ECHO_HIDDEN queries. Never even had to look at any C code.I'm not sure if I entirely follow what translating C to JS gives you, other than a bit of (fairly simple) parsing of \cmd and flags? I didn't look too carefully at the code, so maybe I'm missing something.
arp242
12 days ago
If you just need one or two of the commands, just using those queries directly is easy.
If you want to support all of them, including handling all the different Postgres versions, it becomes quite a lot to implement.
matharmin
12 days ago
Some commands issue multiple queries and include logic for stitching the results into a nice table. Thereβs not a one-to-one mapping from commands to queries by any means.
gmac
12 days ago
Yeah, that's right; but the "glue code" I've had to write for this has been really minimal. Certainly less effort than translating C to JavaScript with regexps (that on its own is something of an accomplishment). I didn't port everything so maybe I missed some more complex bits.
I mean, if this works well for you then that's grand; just wanted to point out to others that \-commands are typically very "non-magic" and you don't need to go the route of translating C code.
arp242
12 days ago
Certainly thereβs a decently long tail of rarely-used commands, obscure features of commands, and multiple code paths supporting multiple server versions.
gmac
12 days ago
Yeah, I don't understand why they'd translate the C code instead of just writing catalog/info schema queries, which seems easier AND more educational.
meepmorp
12 days ago
[flagged]
zX41ZdbW
12 days ago
Almost every single post you make mentions clickhouse. You've replied to a few of my posts now with this vaguely on-topic forceful injection of clickhouse.
Of course I'm not saying you can never bring up clickhouse, but at this point it's just becoming spam. High-effort spam, maybe. But still spam.
arp242
12 days ago
Wow, you are right. Nice catch.
millerm
12 days ago
I'm the author of ClickHouse, and this topic is interesting to me.
zX41ZdbW
12 days ago
I am aware. But this thread is not about Clickhouse. And your comment contributed nothing to the general conversation either. The general pattern is really not appropriate.
arp242
11 days ago
Thank you! Let's take it as a comment about comparing styles in different database CLIs.
Some have \-commands, and some have .-commands, which I found hacky - because it is a micro language notation in addition to the actual language.
zX41ZdbW
11 days ago
When you keep "comparing" tons of stuff to the project you wrote then you're just spamming. Like I said, this is hardly the first time I've seen it, and that's just from random posts I've seen.
arp242
11 days ago
Sorry for distracting you. My apologies.
I will try to remember your login and not answer your comments in the future. At the same time, it is possible that I will occasionally answer your comments by mistake.
Cheers, Alexey.
zX41ZdbW
10 days ago
The fact that this is not a manual reimplementation, but a "transpilation" of the psql source from C to JavaScript, makes this super interesting to me.
Read more here: https://neon.tech/blog/bringing-psqls-d-to-your-web-browserwilliamjackson
12 days ago
When I started my programming journey I thought I could just "translate" Pascal to C++ just with search/replace and minor adjustments. Turns out sometimes it's the best approach :)
ivanjermakov
12 days ago
OP and library author here. Youβll find the background to this in this blog post: https://neon.tech/blog/bringing-psqls-d-to-your-web-browser
gmac
12 days ago
Ok, we've changed the URL to that from https://github.com/neondatabase/psql-describe above. Thanks!
dang
11 days ago
This seems really cool, but also a nightmare to maintain. What are the plans for keeping this in sync with the upstream codebase?
darby_eight
12 days ago
OP (and library author) here. This is a fair point, and one I freely admit to in the accompanying blog post. Itβs probably not crucial to sync more than once per major Postgres version, but weβre still figuring out how weβll deal with this.
gmac
12 days ago
uses emscripten to compile the C to WASM, so itβs automated.
sroussey
12 days ago
Thw convertet is nice, I could imagine it being it's own library, with the generator, and a release including the generated code every postures release.
stuaxo
11 days ago
I'm not sure I understand how to use that.
codesnik
12 days ago
Itβs used in Neonβs web-based console to support \d and friends alongside regular SQL queries.
You might well never need to use it, but if youβre building an online query editor it could be very handy!
gmac
12 days ago
Yeah, I don't really get where / how I would use this. The documentation is really lacking on outlining the usage / use-case.
theogravity
12 days ago
Anyone using Neon in prod? How is it?
redwood
11 days ago
I wish \d allowed sorting alphabetically.
antifa
11 days ago