4 ms·
Very neat indeed. I thought Postgres had a max identifier length of 63 characters so I was surprised to see "cityofchicago/covid19-daily-cases-deaths-and-hospit
by jarym 6y ago
Very neat indeed. I thought Postgres had a max identifier length of 63 characters so I was surprised to see "cityofchicago/covid19-daily-cases-deaths-and-hospitalizations-naz8-j4nc".covid19_daily_cases_deaths_and_hospitalizations in the FROM part of the statement. Does the max identifier length not apply for some reason here or have Splitgraph done something to increase it?
On a related note, I've long wanted longer identifier lengths in Postgres so we can have more meaningful column names but the powers-that-be have always refused... hopefully one day it'll increase in the default distribution.
- aargh_aargh 6y agohttps://www.postgresql.org/docs/12/limits.html https://www.postgresql.org/docs/12/limits.html > identifier length 63 bytes can be increased by recompiling PostgreSQL Maybe they compiled their postgres with a different limit? Just a guess I, haven't tried it.
- xyst 6y agoLooks like you have to modify the source code and rebuild from source to get longer identifiers. “ ... The system uses no more than NAMEDATALEN-1 bytes of an identifier; longer names can be written in commands, but they will be truncated. By default, NAMEDATALEN is 64 so the maximum identifier length is 63 bytes. If this limit is problematic, it can be raised by changing the NAMEDATALEN constant in src/include/pg_config_manual.h. ...” https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS https://www.postgresql.org/docs/current/sql-syntax-lexical.h...
- mildbyte 6y agoCo-founder here. The 63-char limit still applies (we didn't recompile Postgres!) but we have some code in front, embedded in a layer of PgBouncers, that intercepts the query, parses it and rewrites it into a shorter dataset ID hash that we then "mount" on the database on the fly using Postgres FDWs before forwarding it. We also use this to drop unwanted queries and rewrite clients' introspection queries (e.g. information_schema.tables) to give them a list of featured datasets instead of normal Postgres schema names.
- marton78 6y agoNice! And how do you parse SQL? Do you use Postgres's parser or did you have to implement one yourself?
- mildbyte 6y agoWe use pglast [0]: it's basically a Python wrapper around Postgres's query parsing code. [0] https://github.com/lelit/pglast https://github.com/lelit/pglast
- jarym 6y agoFascinating and impressive - nice work and nice solution until the day comes where the identifier length is increased by default