Wei Yen

Interesting things I've learned: Oct 2024

This is a random collection of interesting things I've learned while working on my projects, in October 2024.

PostgresQL JDBC driver in Android

If for some reason you need to connect to a Postgres database directly from an Android app, there's good news and bad news:

The good news: JDBC actually works in Android.

The bad news: the latest PostgresQL JDBC driver does not compile on Android.

PostgresQL version 42.2.10 and above are broken on Android, as they have a dependency on java.lang.management.ManagementFactory, which doesn't exist in Android's ART runtime.

The solution: use PostgresQL 42.2.9, which does work on Android.

Postgres text vs binary formats

When establishing a connection to Postgres, you can specify the format of the data sent from the server to the client using the Format code.

Broadly speaking, there are two formats:

The text representation of values is whatever strings are produced and accepted by the input/output conversion functions for the particular data type. In the transmitted representation, there is no trailing null character; the frontend must add one to received values if it wants to process them as C strings. (The text format does not allow embedded nulls, by the way.)

Binary representations for integers use network byte order (most significant byte first). For other data types consult the documentation or source code to learn about the binary representation. Keep in mind that binary representations for complex data types might change across server versions; the text format is usually the more portable choice.

Source

I learned this while attempting to port Selectable to iOS. The binary format is more efficient if you know, ahead of time, what kind of data you're going to be dealing with. However, that is not the case for a general-purpose database-management app like Selectable.

iOS libraries for database connections are scarce, fortunately Vapor's PostgresNIO works well in iOS. Unfortunately, PostgresNIO defaults to using the binary format. I had to fork the library and replace the default format with text.

Postgres schema introspection

To find out what tables are available, you can query either

  • the information_schema views, or
  • the pg_catalog views.

information_schema is more portable across database systems. pg_catalog is more efficient, but it's Postgres-specific.

psql

psql -E will print SQL statements used to generate results for introspection queries (e.g. \d, \d+, \dt, etc.).

This is very useful for when trying to understand how the pg_catalog views work.

Cursed SQL statements

SELECT from some_table; — without selecting any columns — is a valid SQL statement. It returns one empty row per row in some_table.

SELECT; is also a valid SQL statement. This returns one empty row.

LLMs for automation

Claude Sonnet 3.5 is great at writing shell scripts and Github actions, which is great because I am not good at either.

I've managed to get it to write a Github action that will let me a) bump the version of my Android app and b) commit and push the changes to the repo.

LLMs for generating icons

LLM icon generation are extremely hit-or-miss. I found that I got better results by getting the LLM to generate an SVG of the icon, which tends to result in a more structured and therefore more polished-looking icon.