pg_blkchain

PostgreSQL Blockchain Extension

WARNING: This is work-in-progress, use at your own risk!

This is a C language Postgres extension that provides Bitcoin blockchain functionality.

What You Can Do

CREATE EXTENSION pg_blkchain;

SELECT op_sym, encode(data, 'escape')
  FROM parse_script(E'\\x04ffff001d0104455468652054696d65732030332f4a616e2f32'::bytea ||
                    E'\\x303039204368616e63656c6c6f72206f6e206272696e6b206f66'::bytea ||
                    E'\\x207365636f6e64206261696c6f757420666f722062616e6b73'::bytea);
   op_sym    |                                encode
-------------+-----------------------------------------------------------------------
 OP_PUSHDATA | \377\377\000\x1D
 OP_PUSHDATA | \x04
 OP_PUSHDATA | The Times 03/Jan/2009 Chancellor on brink of second bailout for banks
(3 rows)

Assuming you have a table with a BYTEA column named tx, which contains transactions, you can do stuff like:

  -- Note: this requires the pgcrypto extension for digest().

  SELECT n_in, verify_sig(tx, ptx, n_in)
   FROM (
    SELECT (vin).n n_in, p.tx ptx, x.tx tx
      FROM (
        SELECT get_vin(tx) vin, tx
          FROM rtxs
        WHERE id = 37898
      ) x
    JOIN rtxs p
      ON (vin).prevout_hash = digest(digest(p.tx, 'sha256'), 'sha256')
   ) x;
 n_in | verify_sig
------+------------
    0 | t
    1 | t

SELECT parse_script((get_vout(tx)).scriptpubkey) FROM rtxs WHERE id = 37898;
                          parse_script
----------------------------------------------------------------
 (OP_DUP,118,)
 (OP_HASH160,169,)
 (OP_PUSHDATA,20,"\\x32b0f5cad60641be97317b3f013ce53f60893448")
 (OP_EQUALVERIFY,136,)
 (OP_CHECKSIG,172,)
(5 rows)

-- Note: this will take a while to run!

SELECT (parse_script((get_vout(tx)).scriptpubkey)).op_sym, count(1)
FROM rtxs
GROUP BY op_sym
ORDER BY count(1) DESC LIMIT 10;
         op_sym         |   count
------------------------+-----------
 OP_PUSHDATA            | 678204416
 OP_HASH160             | 672704434
 OP_CHECKSIG            | 598508189
 OP_EQUALVERIFY         | 597189173
 OP_DUP                 | 597189166
 OP_EQUAL               |  75515405
 OP_RETURN              |   3017195
 OP_CHECKMULTISIG       |    574881
 OP_TRUE                |    572552
 OP_9                   |      2635

More details to follow. This blog post has some more info.

If you find this interesting, comment here in an issue or on twitter @humblehack, whatever. Also if you'd like to help.

Building

This extension requires github.com/libbitc/libbitc, which is a fork of picocoin with SegWit support and other improvements.

Building and installing libbitc is up to the reader, the following worked on my Debian system perfectly:

  # build and install .deb packages
  git clone https://github.com/grisha/libbitc.git
  cd libbitc
  git submodule update --init --recursive
  ./autogen.sh
  ./configure
  make pgk-deb
  sudo dpkg -i ./libbitc*deb

Once you have libbitc installed, you should be able to just

make
sudo make install

This was developed and tested only on PG 9.6.