couch-to-postgres / pgcouch / couchpg / couchgres / postcouch

Node libary to stream CouchDB changes into PostgreSQL with a simple client example. Based on https://github.com/orchestrate-io/orchestrate-couchdb.

By adding a few some extra bits allows not only for SELECT queries on the data but also UPDATE/INSERT/DELETE on your couchdb docs within Postgres. It is also possible to use your couch views as tables.

Basically it allows postgres to use couchdb as its datastore - sort of like a Foreign Data Wrapper https://wiki.postgresql.org/wiki/Foreign_data_wrappers eg couchdb_fdw - but has a near realtime copy of records in postgres.

For example:

Add a doc to a couch

  curl -X PUT http://192.168.3.21:5984/example/1234567 -d '{"myvar":"foo"}'
  {"ok":true,"id":"1234567","rev":"1-d3747a58baa817834a21ceeaf3084c41"}      

See it in postgres:

 postgresdb=> SELECT id, doc FROM example WHERE id='1234567';

     id    |                                       doc                                        
  ---------+----------------------------------------------------------------------------------
   1234567 | {"_id": "1234567", "_rev": "1-d3747a58baa817834a21ceeaf3084c41", "myvar": "foo"}
  (1 row)

Update a doc using postgres:

  postgresdb=> UPDATE example 
  postgresdb-> SET doc=json_object_set_key(doc::json, 'myvar'::text, 'bar'::text)::jsonb, from_pg=true 
  postgresdb-> WHERE id='1234567';
  DEBUG:  pgsql-http: queried http://192.168.3.21:5984/example/1234567
  CONTEXT:  SQL statement "SELECT headers FROM http_post('http://192.168.3.21:5984/' || TG_TABLE_NAME || '/' || NEW.id::text, '', NEW.doc::text, 'application/json'::text)"
  PL/pgSQL function couchdb_put() line 9 at SQL statement
  UPDATE 0

The couchdb_put function needs some more work.

See it in couch:

  	curl -X GET http://192.168.3.21:5984/example/1234567 
    {"_id":"1234567","_rev":"2-b9f4c54fc36bdeb78c31590920c9751b","myvar":"bar"}

And in postgres:

  postgresdb=> SELECT id, doc FROM example WHERE id='1234567';
     id    |                                       doc                                        
  ---------+----------------------------------------------------------------------------------
   1234567 | {"_id": "1234567", "_rev": "2-b9f4c54fc36bdeb78c31590920c9751b", "myvar": "bar"}
  (1 row)

Add a doc using postgres

  postgresdb=> INSERT INTO example (id, doc, from_pg) VALUES ('7654321', json_object('{_id,myvar}','{7654321, 100}')::jsonb, true);
  DEBUG:  pgsql-http: queried http://192.168.3.21:5984/example/7654321
  CONTEXT:  SQL statement "SELECT headers FROM http_post('http://192.168.3.21:5984/' || TG_TABLE_NAME || '/' || NEW.id::text, '', NEW.doc::text, 'application/json'::text)"
  PL/pgSQL function couchdb_put() line 9 at SQL statement
  INSERT 0 0
  

See it in couch

  curl -X GET http://192.168.3.21:5984/example/7654321 
  {"_id":"7654321","_rev":"1-08343cb32bb0903348c0903e574cfbd0","myvar":"100"}

Update doc created postgres with couch

  curl -X PUT http://192.168.3.21:5984/example/7654321 -d '{"_id":"7654321","_rev":"1-08343cb32bb0903348c0903e574cfbd0","myvar":"50"}'
  {"ok":true,"id":"7654321","rev":"2-5057c4942c6b92f8a9e2c3e5a75fd0b9"

See it in postgres

  SELECT id, doc FROM example WHERE id='1234567';
     id    |                                       doc                                        
  ---------+----------------------------------------------------------------------------------
   1234567 | {"_id": "1234567", "_rev": "2-b9f4c54fc36bdeb78c31590920c9751b", "myvar": "bar"}
  (1 row)

Add some more docs

  INSERT INTO example (id, doc, from_pg) VALUES ('test1', json_object('{_id,myvar}','{test1, 100}')::jsonb, true);
  INSERT INTO example (id, doc, from_pg) VALUES ('test2', json_object('{_id,myvar}','{test2, 50}')::jsonb, true);

or

  curl -X PUT http://192.168.3.21:5984/example/test3 -d '{"_id":"test3", "myvar":"100"}'
  curl -X PUT http://192.168.3.21:5984/example/test4 -d '{"_id":"test4", "myvar":"50"}'
  curl -X PUT http://192.168.3.21:5984/example/test5 -d '{"_id":"test5", "myvar":"70"}'
  curl -X PUT http://192.168.3.21:5984/example/test6 -d '{"_id":"test6", "myvar":"20"}'
  curl -X PUT http://192.168.3.21:5984/example/test7 -d '{"_id":"test7", "myvar":"10"}'

Do a query on the docs

  SELECT id, doc->'myvar' AS myvar FROM example 
  WHERE id LIKE 'test%' AND CAST(doc->>'myvar' AS numeric) > 50
  ORDER BY myvar
  
    id   | myvar 
  -------+-------
   test3 | "100"
   test1 | "100"
   test5 | "70"
  (3 rows)

Update some of the docs

 UPDATE example 
 SET doc=json_object_set_key(
        doc::json, 'myvar'::text, (CAST(doc->>'myvar'::text AS numeric) + 50)::text
     )::jsonb,
     from_pg=true  
 WHERE id LIKE 'test%' AND CAST(doc->>'myvar' AS numeric) < 60

Peform same query

SELECT id, doc->'myvar' AS myvar FROM example 
WHERE id LIKE 'test%' AND CAST(doc->>'myvar' AS numeric) > 50
ORDER BY myvar

   id   | myvar 
 -------+-------
  test4 | "100"
  test2 | "100"
  test3 | "100"
  test1 | "100"
  test7 | "60"
  test5 | "70"
  test6 | "70"
 (7 rows)

Initially I didnt spot the above order being wrong so you need to be careful.

  SELECT id, CAST(doc->>'myvar' AS numeric) as myvar FROM example 
  WHERE id LIKE 'test%' AND CAST(doc->>'myvar' AS numeric) > 50
  ORDER BY myvar, doc->>'_id'

   id   | myvar 
 -------+-------
  test7 | "60"
  test5 | "70"
  test6 | "70"
  test1 | "100"
  test2 | "100"
  test3 | "100"
  test4 | "100"
 (7 rows)
 

Order is now correct.

And finally in couch

 curl -s -X POST '192.168.3.21:5984/example/_temp_view?include_docs=false' -H 'Content-Type: application/json' \
 -d '{"map":"function(doc) { emit(doc._id, doc.myvar) };"}'  
 {"total_rows":7,"offset":0,"rows":[
 {"id":"test1","key":"test1","value":"100"},
 {"id":"test2","key":"test2","value":"100"},
 {"id":"test3","key":"test3","value":"100"},
 {"id":"test4","key":"test4","value":"100"},
 {"id":"test5","key":"test5","value":"70"},
 {"id":"test6","key":"test6","value":"70"},
 {"id":"test7","key":"test7","value":"60"}
 ]}

It is also possible to use a couchdb view as a table:

The couch design doc:

{
  "_id": "_design/mw_views",
  "language": "javascript",
  "views": {
      "by_feedName": {
      "map": "function(doc) { emit(doc.feedName,null); }",
      "reduce": "_count"
    },
    "by_tags": {
      "map": "function(doc) { for(var i in doc.tags) { emit (doc.tags[i],null);  } }",
      "reduce": "_count"
    }
 }
}


WITH by_feedname_reduced AS (
  SELECT * FROM json_to_recordset(
    (
     SELECT  (content::json->>'rows')::json  
     FROM http_get('http://192.168.3.23:5984/articles/_design/mw_views/_view/by_feedName?group=true'))
    ) AS x (key text, value text)
)

SELECT * FROM by_feedname_reduced WHERE value::numeric > 6000 ORDER BY key 

This takes under a second to run but the initial build of the view takes about 20 mins for a fresh copy of the couchdb.

The equivilent query using the the data in postgres

WITH tbl AS (
    SELECT DISTINCT doc->>'feedName' as key, COUNT(doc->>'feedName') AS value 
    FROM articles
    GROUP BY doc->>'feedName'
)
SELECT key, value FROM tbl WHERE value > 6000 ORDER BY key;

This takes over 4 seconds.

Testing with my articles database from birdreader - https://github.com/glynnbird/birdreader

curl -X GET http://localhost:5984/articles
{"db_name":"articles","doc_count":63759,"doc_del_count":2,"update_seq":92467,"purge_seq":0,"compact_running":false,"disk_size":151752824,"data_size":121586165,"instance_start_time":"1418686121041424","disk_format_version":6,"committed_update_seq":92467}


SELECT DISTINCT jsonb_object_keys(doc) AS myfields
FROM articles ORDER BY myfields

This queries all of the documents and retrieves the couch documents fields.

On another couch database with a 'type' field for different doc types stored in the same database - about 70k docs.

SELECT DISTINCT doc->>'type' as doctype, count(doc->>'type')
FROM mytable GROUP BY doctype ORDER BY doctype 

Takes under a second.

SELECT DISTINCT doc->>'type' as doctype, jsonb_object_keys(doc) AS myfields
FROM mytable
ORDER BY doctype , myfields;

With no indexes the above query takes just over 10 secs. I have made no indexes or adjustments to the default FreeBSD postgresql94-server-9.4.r1 port.

Example setup and postgres configuration

git clone [email protected]:sysadminmike/couch-to-postgres.git

Get needed modules:

npm install 

Edit ./bin/index.js to suite your settings:

var settings =
      {
        couchdb: {
         url: 'http://192.168.3.21:5984',
         pgtable:  'example',
         database: 'example'
       }
      };

 pgclient = new pg.Client("postgres://mike@localhost/pgdatabase");

Before starting it up create the since_checkpoints table

CREATE TABLE since_checkpoints
(
  pgtable text NOT NULL,
  since numeric DEFAULT 0,
  enabled boolean DEFAULT false, --not used in the simple client example
  CONSTRAINT since_checkpoint_pkey PRIMARY KEY (pgtable)
)

This table is used to store the checkpoint for the database(s) being synced something akin to the couchdb _replicator database.

Create the table to store the couch docs:

CREATE TABLE example
(
  id text NOT NULL,
  doc jsonb,
  CONSTRAINT example_pkey PRIMARY KEY (id)
)

Start watching changes

./bin/index.js

It will add a record to the since_checkpoints table and begin syncing.

At this point you can now perform SELECT queries the docs within postgres as in the above example. This should be fine to use against a production couchdb as it makes no changes to and is performing the same tasks as the elastic search river plugin. With a bit of copy/pasting it is possible to use sql to create simple scripts or one liners to run in a shell with curl.

Also take a look at /bin/daemon.js and https://github.com/sysadminmike/couch-to-postgres/blob/master/daemon-README.md

To handle UPDATE/INSERT/DELETE more configuration is required. Note this is still experimental so I wouldnt point this at any production data.

First install the postgres extension pgsql-http at https://github.com/pramsey/pgsql-http

See note about pgsql-http module install if you not sure how to install a postgres extension - note this has just been updated to handle PUT and DELETE requests - i have not yet had a chane to test anything i have done on this page with the new version but will try to shortly but think any reference to http_post need updating as the new version specifies:

http_post(uri VARCHAR, content VARCHAR, content_type VARCHAR)

But on this page I am using the old one:

http_post(url VARCHAR, params VARCHAR, data VARCHAR, contenttype VARCHAR DEFAULT NULL)

So please bear this in mind if setting this up.

Then add it in the database you want to use:

CREATE EXTENSION http

If you havent already done it:

CREATE TABLE since_checkpoints   
(
  pgtable text NOT NULL,
  since numeric DEFAULT 0,
  enabled boolean DEFAULT false,
  CONSTRAINT since_checkpoint_pkey PRIMARY KEY (pgtable)
);

Add function to put data into couchdb:

CREATE OR REPLACE FUNCTION couchdb_put() RETURNS trigger AS $BODY$
DECLARE
    RES RECORD;
BEGIN
 IF (NEW.from_pg) IS NULL THEN
   RETURN NEW;
 ELSE 
   
   SELECT status FROM http_post('http://192.168.3.21:5984/' || TG_TABLE_NAME || '/' || NEW.id::text, '', NEW.doc::text, 'application/json'::text) INTO RES;    

   --Need to check RES for response code
   --RAISE EXCEPTION 'Result: %', RES;
   RETURN null;
 END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE  

Add function to modify fields inside the PostgreSQL JSON datatype - from: http://stackoverflow.com/questions/18209625/how-do-i-modify-fields-inside-the-new-postgresql-json-datatype

CREATE OR REPLACE FUNCTION json_object_set_key(json json, key_to_set text, value_to_set anyelement)
  RETURNS json AS
$BODY$
SELECT COALESCE(
  (SELECT ('{' || string_agg(to_json("key") || ':' || "value", ',') || '}')
     FROM (SELECT *
             FROM json_each("json")
            WHERE "key" <> "key_to_set"
            UNION ALL
           SELECT "key_to_set", to_json("value_to_set")) AS "fields"),
  '{}'
)::json
$BODY$
  LANGUAGE sql IMMUTABLE STRICT;
  
  

Create table to hold the docs

CREATE TABLE example
(
  id text NOT NULL,
  doc jsonb,
  from_pg boolean, -- for trigger nothing stored here
  CONSTRAINT example_pkey PRIMARY KEY (id)
);

Create trigger to stop data being inserted into the table from sql and send off to couch instead

CREATE TRIGGER add_doc_to_couch 
BEFORE INSERT OR UPDATE 
ON example FOR EACH ROW EXECUTE PROCEDURE couchdb_put();

Note: All queries in postgres must have "from_pg=true" for inserts and updates or the postgres will send the data to the table and not send it to couch.

I plan to reverse this logic and make the libary include this so it will be possible to issue inserts/updates and exclude this field.

You can now start the node client and give it a test.

A few variable to tune in ./lib/index.js need to move to config options

In checkpoint_changes function:

ckwait = 3 * 1000;  

This is how often the stream is checkpointed when the stream is active. I would adjust this depending on how busy you couchdb is. When the stream is idle this increases to 10 secs.

In startFollowing function there is: // The inactivity timer is for time between changes, or time between the // initial connection and the first change. Therefore it goes here. stream.inactivity_ms = 30000;

Maybe use NOTIFY and have node client LISTEN for a message when postgres calls couchdb_put() for the first time (can you do a timer in postgres?? or node will get notified about every update and only needs a wake up after idle time).

Performance wise compared to the php dumping script

On a test with a couchdb of about 150Mb with 65k docs the node libary complete working through _changes in about 17 minutes to add all the docs to an empty table and then keeps it in sync.

The couch-to-postgres-php-dumper script - https://github.com/sysadminmike/couch-to-postgres-php-dump takes about 28 minutes for the initial sync and 11 secs for a resync.

Replicating the same db from one jail running couch to another couch jail on the same machine as a baseline takes just over 8 minutes:

{"session_id":"661411f2137c64efc940f55b802dc35b","start_time":"Tue, 16 Dec 2014 17:00:05 GMT","end_time":"Tue, 16 Dec 2014 17:08:10 GMT","start_last_seq":0,"end_last_seq":92862,"recorded_seq":92862,"missing_checked":63840,"missing_found":63840,"docs_read":63840,"docs_written":63840,"doc_write_failures":0}

Looking at top I think postgres is waiting on the disk most of the time rather than the process being cpu bound - the single php process calling curl for each doc was hitting the cpu hard and couldnt be used as a solution for huge databases or have the ability to deal with more than one db at once.

On further testing with some dogey postgres conf settings:

fsync = off
synchronous_commit = off

As postgres is not the primary datastore its ok if the data dies considering a full rebuild now is under 2 mins:

mike:~/postgres-couch/couch-to-postgres-test % time ./bin/index.js
articlespg: {"db_name":"articlespg","doc_count":63838,"doc_del_count":2,"update_seq":63840,"purge_seq":0,"compact_running":false,"disk_size":242487416,"data_size":205414817,"instance_start_time":"1418749205916149","disk_format_version":6,"committed_update_seq":63840}
Connected to postgres
articlespg: initial since=0
articlespg: Starting checkpointer
articlespg: Checkpoint set to 7180 next check in 3 seconds
articlespg: Checkpoint set to 9344 next check in 3 seconds
articlespg: Checkpoint set to 11536 next check in 3 seconds
...
articlespg: Checkpoint set to 60920 next check in 3 seconds
articlespg: Checkpoint set to 63636 next check in 3 seconds
articlespg: Checkpoint set to 63840 next check in 3 seconds
articlespg: Checkpoint 63840 is current next check in: 10 seconds
^C45.919u 3.226s 1:42.10 48.1%  10864+321k 158+0io 0pf+0w
mike:~/postgres-couch/couch-to-postgres-test % 

So down to well under 2 minutes now todo the initial sync of the same test db - so 4 times faster than a native couch to couch sync. I think this is faster than Elastic search river doing a similar task.

Snippet from top while it was syncing:

  PID USERNAME    THR PRI NICE   SIZE    RES STATE   C   TIME    WCPU COMMAND
57635 mike          6  45    0   621M 66064K uwait   1   0:25  50.78% node
57636     70        1  36    0   186M 97816K sbwait  1   0:11  22.75% postgres
44831    919       11  24    0   181M 30048K uwait   0  67:28  20.51% beam.smp
23891    919       11  20    0   232M 69168K uwait   0  26:22   0.39% beam.smp
57624     70        1  20    0   180M 17840K select  0   0:00   0.29% postgres
57622     70        1  21    0   180M 65556K select  1   0:00   0.20% postgres

Possible ways to deploy - master-master postgres setup using couchdbs primary data store and setting up replication between all locations using Postgres and Couch as a pair.

 Location 1                              Location 2
 Postgres == CouchDB ---------- CouchDB == Postgres
                     \        /
                      \      /
                       \    /
                        \  /
                         \/    
                     Location 3
                      CouchDB
                        ||
                      Postgres  
                           
 Where === is the node client keeping the paired postgres up to date
 And ----- is couchdb performing replication 

IDEAS/TODOS - Comments most welcome.

How to do bulk updates:

WITH new_docs AS (
  SELECT json_object_set_key(doc::json, 'test'::text, 'Couch & Postgres are scool'::text)::jsonb AS docs
  FROM articlespg
),
agg_docs AS (
  SELECT json_agg(docs) AS aggjson FROM new_docs
)

SELECT headers FROM 
  http_post('http://192.168.3.21:5984/articlespg/_bulk_docs', '',
  '{"all_or_nothing":true, "docs":' || (SELECT * FROM agg_docs) || '}',
  'application/json'::text) ;    

I tried on the articles test db i am using and it was very fast for an update to < 100 rows I then tried to update all docs and crashed couch

DEBUG:  pgsql-http: queried http://192.168.3.21:5984/articlespg/_bulk_docs
ERROR:  Failed to connect to 192.168.3.21 port 5984: Connection refused
couchplay=> 

However if we split up the request in to smaller chunks:

WITH newdocs AS ( -- Make chage to json here 
  SELECT id, json_object_set_key(doc::json, 'test'::text, 'Couch & Postgres are scool'::text)::jsonb AS docs
  FROM articlespg 
),
chunked AS (  -- get in chunks 
SELECT docs, ((ROW_NUMBER() OVER (ORDER BY id) - 1)  / 50) +1 AS chunk_no  
FROM newdocs
),
chunked_newdocs AS (  -- Bulk up bulk_docs chunks to send 
    SELECT json_agg(docs) AS bulk_docs, chunk_no FROM chunked GROUP BY chunk_no  ORDER BY chunk_no
)

SELECT chunk_no, status FROM chunked_newdocs,
       http_post('http://192.168.3.21:5984/articlespg/_bulk_docs', '',
       '{"all_or_nothing":true, "docs":' || (bulk_docs) || '}',
       'application/json'::text); 

Chunk size - in this case 50 - i think safe to go to about 500 or 1000 depending on doc size - I tried 1000 to begin with but http_post timed out - and 500 seems to be fine.

Watching the node daemon while running chunked bulk updates i can see the changes streaming back to postgres almost as soon as the start so i think better using an UPDATE as postgres doesnt lock the table while this is happening ***Note need to retest this.

However I think better to change all PUTS to bulks POSTS - need a function like:

 post_docs(docs,chunk_size) - returning recordset of status codes? or just true/false?

how to deal with the case where there are 5 chunks and the first 2 sucseed but the 3rd fails? is it possible to rollback a transaction in postgres and give the function oldocs and newdocs then a post_docs chunk fails it can rollback the chunks which have succeeded?

to be used after like:

 SELECT post_docs(json_object_set_key(doc::json, 'test'::text, 'Couch & Postgres are scool'::text)::jsonb,100)
        AS results
 FROM articlespg

This also makes it very simple to make new databases - just add a new db in couch and change the url to point to it:

chunked AS (
  SELECT docs, ((ROW_NUMBER() OVER (ORDER BY id) - 1)  / 500) +1 AS chunk_no  
  FROM articlespg
),
chunked_newdocs AS (
   SELECT json_agg(docs) AS bulk_docs, chunk_no FROM chunked GROUP BY chunk_no  ORDER BY chunk_no
)
SELECT chunk_no, status FROM chunked_newdocs,
       http_post('http://192.168.3.21:5984/NEW_articlespg_COPY/_bulk_docs', '',
       '{"all_or_nothing":true, "docs":' || (bulk_docs) || '}', 'application/json'::text);  

I think maybe faster than a replication.

Note: On pgsql-http module install:

https://wiki.postgresql.org/wiki/Building_and_Installing_PostgreSQL_Extension_Modules

For FreeBSD you need to have curl and gettext-tools installed.

# gmake PG_CONFIG=/usr/local/bin/pg_config
cc -O2 -pipe  -fstack-protector -fno-strict-aliasing -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fPIC -DPIC -I. -I./ -I/usr/local/include/postgresql/server -I/usr/local/include/postgresql/internal -I/usr/local/include/libxml2 -I/usr/include -I/usr/local/include -I/usr/local/include  -c -o http.o http.c
http.c:89:1: warning: unused function 'header_value' [-Wunused-function]
header_value(const char* header_str, const char* header_name)
^
1 warning generated.
cc -O2 -pipe  -fstack-protector -fno-strict-aliasing -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fPIC -DPIC -I. -I./ -I/usr/local/include/postgresql/server -I/usr/local/include/postgresql/internal -I/usr/local/include/libxml2 -I/usr/include -I/usr/local/include -I/usr/local/include  -c -o stringbuffer.o stringbuffer.c
cc -O2 -pipe  -fstack-protector -fno-strict-aliasing -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fPIC -DPIC -shared -o http.so http.o stringbuffer.o -L/usr/local/lib -L/usr/local/lib -pthread -Wl,-rpath,/usr/lib:/usr/local/lib -fstack-protector -L/usr/local/lib -L/usr/lib  -L/usr/local/lib -Wl,--as-needed -Wl,-R'/usr/local/lib'  -L/usr/local/lib -lcurl



# gmake PG_CONFIG=/usr/local/bin/pg_config install
/bin/mkdir -p '/usr/local/lib/postgresql'
/bin/mkdir -p '/usr/local/share/postgresql/extension'
/bin/mkdir -p '/usr/local/share/postgresql/extension'
/usr/bin/install -c -o root -g wheel -m 755  http.so '/usr/local/lib/postgresql/http.so'
/usr/bin/install -c -o root -g wheel -m 644 http.control '/usr/local/share/postgresql/extension/'
/usr/bin/install -c -o root -g wheel -m 644 http--1.0.sql '/usr/local/share/postgresql/extension/'

Futher thoughts and ideas/questions or want to help? https://github.com/sysadminmike/couch-to-postgres/issues

More testing:

Update to all records in test articles db -

SELECT id , doc->>'read' FROM articlespg WHERE doc->>'read'='false'

couchplay=> SELECT id , doc->>'read' FROM articlespg WHERE doc->>'read'='false'
couchplay-> 
couchplay-> ;
	id        | ?column? 
------------------+----------
 _design/mw_views | false
(1 row)

Returns just the design doc.

On running:

  UPDATE articlespg 
  SET doc = json_object_set_key(doc::json, 'read'::text, true)::jsonb, from_pg=true ;

Something interesting happens with the feed and postgres - I think postgres locks the table while the update takes place as the feeder carries on querying couch but does not update postgres until the update is complete.

While the query is runing you can see the commit sequence in couch updating:

articlespg: {"db_name":"articlespg","doc_count":63838,"doc_del_count":2,"update_seq":233296,"purge_seq":0,"compact_running":false,"disk_size":2145373958,"data_size":214959726,"instance_start_time":"1418762851354294","disk_format_version":6,"committed_update_seq":233224}
articlespg: Checkpoint 192414 is current next check in: 10 seconds
PG_WATCHDOG: OK

articlespg: {"db_name":"articlespg","doc_count":63838,"doc_del_count":2,"update_seq":242301,"purge_seq":0,"compact_running":false,"disk_size":2234531964,"data_size":215440194,"instance_start_time":"1418762851354294","disk_format_version":6,"committed_update_seq":242301}
articlespg: Checkpoint 192414 is current next check in: 10 seconds
PG_WATCHDOG: OK

As soon as I get a return for the query the feed goes mad so think postgres has locked the table while the update runs. *** I need to restest this as i may have been doing this test after i introduced a bug stalling the feed on updates.

The UPDATE takes 475 seconds to return The river then takes about 3 minutes to catch up after the return So about 10 minutes to do an update on all 60k records.

I need to look at the bulk updates as i do now think it is possible to do all or nothing update and possible do in a transaction - i think if 2 updates to couch were issued and the second failed then the first would have still taken place as far as couch is concerned.
At the moment if a single PUT were to fail postgres assume no data has been updated but all of the docs up to then would have been updated - in a bulk this would not be a problem i think. Note so far not one insert or update has failed but i havent killed couch 1/2 way through.

This did give me an idea for another use for this. Populate a new couchdb from a subset of the couchdb tables in postgres by simply updating the put_function to temporarly submit updates to a different ip or db eg:

--SELECT headers FROM http_post('http://192.168.3.21:5984/' || TG_TABLE_NAME || '/' || NEW.id::text, '', NEW.doc::text, 'application/json'::text) INTO RES;    
  SELECT headers FROM http_post('http://192.168.3.21:5984/articlespg-subset' || '/' || NEW.id::text, '', NEW.doc::text, 'application/json'::text) INTO RES;    

Then re-run the update but with a WHERE

UPDATE articlespg 
SET doc = json_object_set_key(doc::json, 'read'::text, true)::jsonb, from_pg=true 
WHERE doc ->>'feedName' ='::Planet PostgreSQL::';

About 10 secs later a populated couchdb with just 761 docs matching the WHERE:

{"db_name":"articlespg-subset","doc_count":761,"doc_del_count":0,"update_seq":761,"purge_seq":0,"compact_running":false,"disk_size":6107249,"data_size":3380130,"instance_start_time":"1418770153501066","disk_format_version":6,"committed_update_seq":761}

A lot simpler that creating a design doc for a one of filtered replication. There is no reason why you couldnt do a union on two couch db tables in posgres and merge them into a new couchdb provided there are no id issues.

I have also done a quick test with excel and ms query & access and a passthrough sql query both via odbc to postgres - i can see the couch data in both - this makes ad hoc reports so simple.

Note on name - I think I like postcouch best - and i think most of the work will be done by http_post function POSTing (from postgres) to couchdb

(I think a good idea to also also give option to do PUTS)