Thread: [GENERAL] Aquameta 0.1 - Request for reviews, contributors

On Fri, Sep 08, 2017 at 02:41:09PM -0700, Eric Hanson wrote:
> 2. We need contributors.  Aquameta has eight modules (
> http://aquameta.org/code), each of which could probably be a PostgreSQL
> extension that might be useful outside our stack, and a great place to
> start would be to start to convert them as possible to PostgreSQL
> extensions.  If you're interested in contributing, please join our IRC
> channel at #aquameta on irc.freenode.net.

The meta module immediately got my attention.  I've been wanting that
for years, and wanting to implement something like it.  Recently I've
been making use of DDL event triggers to do a variety of things (e.g.,
an audit module that produces audit history that is amenable to
relational queries), and was thinking of using a combination of event
and regular triggers to build something like this "meta" module.  I'm
glad now I won't have to!

For pub/sub I've got an alternative view materialization mechanism that
produces history, and a tail-f-over-HTTP daemon for publishing
append-only logs.  But also pg_notify() is very useful as well (and I
have a utility for helping on the client side, "pqasyncnotifier").

For a REST interface I swear by PostgREST -- it's written in Haskell, so
it's got that to recommend it, and it's very easy to setup and use.

I also have a schema2json module that produces nice JSON representations
of PG SQL schemas, including parsing COMMENTary as JSON texts (if valid)
and hoisting them safely into the surrounding objects (this part is done
with a shell script that uses jq[0]).  This seems similar to your
semantics module, though I'm using COMMENTs containing JSON while you're
building a proper schema.

You can see some of these things here:

https://github.com/twosigma/postgresql-contrib

Nico

[0] https://stedolan.github.io/jq


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Here's a review comment.  Just one for now.

Looking at the meta module, I see things like this:
       execute 'select (count(*) = 1) from ' || quote_ident((row_id::meta.schema_id).name) || '.' ||
quote_ident((row_id::meta.relation_id).name)||               ' where ' || quote_ident((row_id.pk_column_id).name) || '
=' || quote_literal(row_id.pk_value)    into answer;
 

I recently learned what I find to be a better idiom:
   execute format(       $q$           select exists (select *                          from %1$I.%2$I
       where %3$I = %4$L);       $q$,       -- interpolated arguments here       (row_id::meta.schema_id).name,
(row_id::meta.relation_id).name,      (row_id.pk_column_id).name, row_id.pk_value     into answer;
 

That is, PostgreSQL has extended string literal syntax where you can use
$stuff$ instead of single-quotes, and that makes it much easier to write
dynamic (generated for EXECUTE) SQL.  In particular, because your
$EDITOR [generally] won't recognize this, syntax highlighting for the
$quoted$ code will work as expected!

This is better not only because it's more concise, easier to line-wrap,
and easier on the eyes, but also because you get to use format().  I
suspect using format() makes it harder to forget to quote something
appropriately -- harder to accidentally create a SQL injection
vulnerability.  I usually use argument numbering (%<n>$I) instead of
referring to the positionally (%I, %L, %s) because it helps a lot
whenever I need to refer to one of them multiple times.

Of course, this is just a matter of style, but I strongly feel that this
is the superior style (at least I find or stumble into a better style),
especially when you have several layers of trigger functions creating
more trigger functions, as you can easily nest $foo$-quoted string
literals by having different quote forms for each level.

Also, I used exists() instead of count(*) = 1 -- that's just my personal
preference, and a less defensible style matter (it is more verbose...).

Nico
-- 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general