4 ms·

I would go further: give me a PL/ language (or an equivalent bytecode-abstract-machine abstraction) that lets me program — at least in a read-only capacity — di

by derefr 4y ago

I would go further: give me a PL/ language (or an equivalent bytecode-abstract-machine abstraction) that lets me program — at least in a read-only capacity — directly against the access-method handles, such that the DB's table heap-file pages, index B-tree nodes, locks, etc. are "objects" I can manipulate, pass to functions, and navigate graph-wise (i.e. ask a table for its partitions; ask a partition for its access-method; type-assert the access-method as a heap file; ask for the pages as a random-access array; probe some pages for row-tuples; iterate those, de-serializing and de-toasting them as a type-cast; and then implement some efficient search or sampling on those tuples. Basically the code you'd write in a PG extension to interact with the storage engine on that level, but limited to only "safe" actions against the storage, and so able to be directly user-exposed.) The closest analogy I know of to that, is how you work with ETS tables in Erlang. I want to send the RDBMS code that operates at that level! Actually, I presume that SQLite would necessarily have some low-level C interface that works like this — but few people seem to talk about it/be aware of it compared to its high-level SQL-level interface.

cdcarter 4y ago
SQLite compiles SQL to bytecode, and then executes that bytecode against the database. However, there's no public interface for creating/running bytecode directly, instead of as a result of a compiled statement. You almost certainly COULD do what you're trying to achieve, but the SQLite author's have specifically called it out as a bad idea - https://sqlite.org/forum/info/c695cbe47b955076 https://sqlite.org/forum/info/c695cbe47b955076 - since bytecode representation can change from release to release in a way that would only matter to the compiler (or to your weird hacked in interface). Meaning, non-portable.
- derefr 4y ago
  > since bytecode representation can change from release to release in a way that would only matter to the compiler (or to your weird hacked in interface). Meaning, non-portable. IIRC JVM static-analysis libraries get around this by essentially forcefully pulling in and reflecting upon the particular compiler release's internals that are being built against. The result is "non-portable", but only in the sense that it's getting tailored to the particular compiler release that's already concretely available in the build environment. Mind you, that's a bit different, because you don't usually ship the compiler parts of the JDK as part of your application JAR; while SQLite does ship this compiler as part of the library. Would be fine, though, as long as your executable's embedding SQLite statically (or in a Docker image, etc) — in other words, vendoring the particular version of SQLite that matches the version the application-layer codegen library was compiled against.
jiggawatts 4y ago
This is vaguely how the Microsoft "Jet" database engines work. Microsoft Exchange uses this low-level query authoring technique to achieve its scalability and performance goals. This generally makes its performance consistent and predictable. There were some attempts back in the early 2000s to move Exchange over to use the SQL Server RDBMS engine, and they added a bunch of features to enable this kind of low-level control. Not just join hints: you could force specific query plans by specifying the plan XML document for that query. See: https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver16#use-plan https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-tr... This wasn't good enough however, and Exchange still uses the Jet database. Something that might be interesting is an RDBMS "as a library", where instead of poking it with ASCII text queries, you get a full programming API surface where you can do exactly the type of thing you propose: perform arbitrary walks through data structures, develop custom indexes, or whatever.
felixge 4y ago
I would love this too, and looked into building an extension for it a few years ago. IIRC the main challenge was that many features such as row level security are built straight into the current query executor, making it difficult to build this as a production grade tool. I could try to dig up my notes if you’re interested.

by derefr 4y ago

cdcarter 4y ago

SQLite compiles SQL to bytecode, and then executes that bytecode against the database. However, there's no public interface for creating/running bytecode directly, instead of as a result of a compiled statement. You almost certainly COULD do what you're trying to achieve, but the SQLite author's have specifically called it out as a bad idea - https://sqlite.org/forum/info/c695cbe47b955076 https://sqlite.org/forum/info/c695cbe47b955076 - since bytecode representation can change from release to release in a way that would only matter to the compiler (or to your weird hacked in interface). Meaning, non-portable.

derefr 4y ago
> since bytecode representation can change from release to release in a way that would only matter to the compiler (or to your weird hacked in interface). Meaning, non-portable. IIRC JVM static-analysis libraries get around this by essentially forcefully pulling in and reflecting upon the particular compiler release's internals that are being built against. The result is "non-portable", but only in the sense that it's getting tailored to the particular compiler release that's already concretely available in the build environment. Mind you, that's a bit different, because you don't usually ship the compiler parts of the JDK as part of your application JAR; while SQLite does ship this compiler as part of the library. Would be fine, though, as long as your executable's embedding SQLite statically (or in a Docker image, etc) — in other words, vendoring the particular version of SQLite that matches the version the application-layer codegen library was compiled against.

jiggawatts 4y ago

This is vaguely how the Microsoft "Jet" database engines work. Microsoft Exchange uses this low-level query authoring technique to achieve its scalability and performance goals. This generally makes its performance consistent and predictable. There were some attempts back in the early 2000s to move Exchange over to use the SQL Server RDBMS engine, and they added a bunch of features to enable this kind of low-level control. Not just join hints: you could force specific query plans by specifying the plan XML document for that query. See: https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver16#use-plan https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-tr... This wasn't good enough however, and Exchange still uses the Jet database. Something that might be interesting is an RDBMS "as a library", where instead of poking it with ASCII text queries, you get a full programming API surface where you can do exactly the type of thing you propose: perform arbitrary walks through data structures, develop custom indexes, or whatever.

felixge 4y ago

I would love this too, and looked into building an extension for it a few years ago. IIRC the main challenge was that many features such as row level security are built straight into the current query executor, making it difficult to build this as a production grade tool. I could try to dig up my notes if you’re interested.