Comments (40)
Mmm not sure whats happening exactly - currently i am not using the insert/update stuff in postgres to update the docs in couch and rather our application is connecting directly to couchdb.
Can you post your sql schema - this is what i had running before but have not updated this to the pgsql-http 1.1 so the http_post function is incorrect but here is the working schema
-- Function: http_post(character varying, character varying, character varying, character varying)
-- DROP FUNCTION http_post(character varying, character varying, character varying, character varying);
CREATE OR REPLACE FUNCTION http_post(url character varying, params character varying, data character varying, contenttype character varying DEFAULT NULL::character varying)
RETURNS http_response AS
'$libdir/http', 'http_post'
LANGUAGE c VOLATILE
COST 1;
ALTER FUNCTION http_post(character varying, character varying, character varying, character varying)
OWNER TO pgsql;
Put function:
-- Function: couchdb_put()
-- DROP FUNCTION couchdb_put();
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 headers 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
COST 100;
Table:
-- Table: articlespg
-- DROP TABLE articles;
CREATE TABLE articles
(
id text NOT NULL,
doc jsonb,
from_pg boolean,
CONSTRAINT articles_pkey PRIMARY KEY (id)
);
-- Trigger: add_doc_to_couch on articles
-- DROP TRIGGER add_doc_to_couch ON articles;
CREATE TRIGGER add_doc_to_couch
BEFORE INSERT OR UPDATE
ON articles
FOR EACH ROW
EXECUTE PROCEDURE couchdb_put();
from couch-to-postgres.
sql schema
CREATE DATABASE example WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'es_SV.UTF-8' LC_CTYPE = 'es_SV.UTF-8';
ALTER DATABASE example OWNER TO tester;
\connect example
SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
-- TOC entry 177 (class 3079 OID 11863)
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
-- TOC entry 2040 (class 0 OID 0)
-- Dependencies: 177
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
-- TOC entry 178 (class 3079 OID 24645)
-- Name: http; Type: EXTENSION; Schema: -; Owner:
CREATE EXTENSION IF NOT EXISTS http WITH SCHEMA public;
-- TOC entry 2041 (class 0 OID 0)
-- Dependencies: 178
-- Name: EXTENSION http; Type: COMMENT; Schema: -; Owner:
COMMENT ON EXTENSION http IS 'HTTP client for PostgreSQL, allows web page retrieval inside the database.';
SET search_path = public, pg_catalog;
-- TOC entry 199 (class 1255 OID 40965)
-- Name: couchdb_put(); Type: FUNCTION; Schema: public; Owner: tester
CREATE FUNCTION couchdb_put() RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
RES RECORD;
BEGIN
IF (NEW.from_pg) IS NULL THEN
RETURN NEW;
ELSE
SELECT status FROM http_put('http://127.0.0.1:5984/' || TG_TABLE_NAME || '/' || NEW.id::text, NEW.doc::text, 'Content-Type:application/json'::text) INTO RES;
--Need to check RES for response code
RAISE EXCEPTION 'Result: %', RES;
RETURN null;
END IF;
END;
$$;
ALTER FUNCTION public.couchdb_put() OWNER TO tester;
-- TOC entry 198 (class 1255 OID 24667)
-- Name: json_object_set_key(json, text, anyelement); Type: FUNCTION; Schema: public; Owner: tester
CREATE FUNCTION json_object_set_key(json json, key_to_set text, value_to_set anyelement) RETURNS json
LANGUAGE sql IMMUTABLE STRICT
AS $$
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
$$;
ALTER FUNCTION public.json_object_set_key(json json, key_to_set text, value_to_set anyelement) OWNER TO tester;
SET default_tablespace = '';
SET default_with_oids = false;
-- TOC entry 173 (class 1259 OID 16396)
-- Name: example; Type: TABLE; Schema: public; Owner: tester; Tablespace:
CREATE TABLE example (
id text NOT NULL,
doc jsonb,
from_pg boolean
);
ALTER TABLE example OWNER TO tester;
-- TOC entry 2042 (class 0 OID 0)
-- Dependencies: 173
-- Name: COLUMN example.from_pg; Type: COMMENT; Schema: public; Owner: tester
COMMENT ON COLUMN example.from_pg IS ' -- for trigger nothing stored here';
-- TOC entry 172 (class 1259 OID 16386)
-- Name: since_checkpoints; Type: TABLE; Schema: public; Owner: tester; Tablespace:
CREATE TABLE since_checkpoints (
pgtable text NOT NULL,
since numeric DEFAULT 0,
enabled boolean DEFAULT false
);
ALTER TABLE since_checkpoints OWNER TO tester;
-- TOC entry 1922 (class 2606 OID 16403)
-- Name: example_pkey; Type: CONSTRAINT; Schema: public; Owner: tester; Tablespace:
ALTER TABLE ONLY example
ADD CONSTRAINT example_pkey PRIMARY KEY (id);
-- TOC entry 1920 (class 2606 OID 16395)
-- Name: since_checkpoint_pkey; Type: CONSTRAINT; Schema: public; Owner: tester; Tablespace:
ALTER TABLE ONLY since_checkpoints
ADD CONSTRAINT since_checkpoint_pkey PRIMARY KEY (pgtable);
-- TOC entry 1923 (class 2620 OID 49157)
-- Name: add_doc_to_couch; Type: TRIGGER; Schema: public; Owner: tester
CREATE TRIGGER add_doc_to_couch BEFORE INSERT OR UPDATE ON example FOR EACH ROW EXECUTE PROCEDURE couchdb_put();
-- TOC entry 2039 (class 0 OID 0)
-- Dependencies: 5
-- Name: public; Type: ACL; Schema: -; Owner: postgres
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;
-- Completed on 2015-10-04 12:17:36 CST
-- PostgreSQL database dump complete
from couch-to-postgres.
In your couch_put you have uncommented the RAISE EXCEPTION - i think this may be causing the issue - give it a go with that commented out?
--Need to check RES for response code
RAISE EXCEPTION 'Result: %', RES;
RETURN null;
from couch-to-postgres.
with or without RAISE EXCEPTION the problem is the same-
--Need to check RES for response code
--RAISE EXCEPTION 'Result: %', RES;
RETURN null;
How can I install the old http? Where can I download it?
from couch-to-postgres.
i dont think its that there maybe something causing the issue as it looks like the http stuff is fine if the document is inserted into couch - can you see the new doc in couch via futon or curl?
If you can see the doc then the http function is ok and there is something else happening:
- Postgres submits doc to couch and is saved by couch
- Couch-to-postgres inserts the doc into postgres table
- Postrgres tries to insert the doc but fails as it already exists.
Can you stop couch-to-postgres and try to run the insert. The doc should appear in couch but NOT in postgres - if it appears in postgres then there is something up with the trigger
from couch-to-postgres.
I stopped couch-to-postgres and did the insert in posgres, this was also inserted in Couchdb. However, nothing was inserted back in Postgres. Postgres still disconnects
from couch-to-postgres.
ok then i think the http function is ok and there is something else causing postgres to get upset - do you still get the rollback error in the log?
from couch-to-postgres.
can you check the http_put function and paste it here so we can check all ok in that?
from couch-to-postgres.
New Log:
2015-10-04 13:02:04 CST [29152-2] LOG: server process (PID 29184) was terminated by signal 11: Segmentation fault
2015-10-04 13:02:04 CST [29152-3] DETALLE: The process that failed was executing: INSERT INTO example (id, doc, from_pg) VALUES ('v', json_object('{_id,myvar}','{v, 100}')::jsonb, true);
2015-10-04 13:02:04 CST [29152-4] LOG: all server processes terminated; reinitializing
2015-10-04 13:02:04 CST [29186-1] tester@example FATAL: the database system is starting up
2015-10-04 13:02:04 CST [29157-2] WARNING: terminating connection because of crash of another server
--------------- the next is the same than previous-------------------
2015-10-04 10:33:35 CST [1727-3] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally an
d possibly corrupted shared memory.
2015-10-04 10:33:35 CST [1727-4] HINT: In a moment you should be able to reconnect to the database and repeat your command.
2015-10-04 10:33:35 CST [709-20] LOG: all server processes terminated; reinitializing
2015-10-04 10:33:35 CST [1770-1] LOG: database system was interrupted; last known up at 2015-10-04 10:29:34 CST
2015-10-04 10:33:35 CST [1770-2] LOG: database system was not properly shut down; automatic recovery in progress
2015-10-04 10:33:35 CST [1770-3] LOG: record with zero length at 0/18146D0
2015-10-04 10:33:35 CST [1770-4] LOG: redo is not required
2015-10-04 10:33:35 CST [1770-5] LOG: MultiXact member wraparound protections are now enabled.
Did you make any changes in your postgresql.conf for this purpose?
this is the function Function: http_put:
-- Function: http_put(character varying, character varying, character varying)
-- DROP FUNCTION http_put(character varying, character varying, character varying);
CREATE OR REPLACE FUNCTION http_put(uri character varying, content character varying, content_type character varying)
RETURNS http_response AS
LANGUAGE sql VOLATILE
COST 100;
ALTER FUNCTION http_put(character varying, character varying, character varying)
OWNER TO tester;
from couch-to-postgres.
Nope no changes for this - the only change was to play around with speeding things up
What happens if you comment out the http stuff:
So reduce the couchdb_put() to:
IF (NEW.from_pg) IS NULL THEN
RETURN NEW;
ELSE
--SELECT status FROM http_put('http://127.0.0.1:5984/' || TG_TABLE_NAME || '/' || NEW.id::text, NEW.doc::text, 'Content-Type:application/json'::text) INTO RES;
RETURN null;
END IF;
END;
and try to run the insert
lets see if the http_put is doing something odd or the trigger stuff
from couch-to-postgres.
example=# INSERT INTO example (id, doc, from_pg) VALUES ('o', json_object('{_id,myvar}','{o, 100}')::jsonb, true);
INSERT 0 0
example=# select * from example;
id | doc | from_pg
----+-----+---------
(0 rows)
The postgres server log didn't show anything
from couch-to-postgres.
ok perfect thats what should happen
from couch-to-postgres.
the couch_put function is throwing the record away and not inserting it into the table
from couch-to-postgres.
lets try to run the http_put so somethinglike
SELECT status FROM http_put('http://127.0.0.1:5984/example/12345'::text, '{myvar:1}'::text, 'Content-Type:application/json'::text);
from couch-to-postgres.
example=# SELECT status FROM http_put('http://127.0.0.1:5984/example/12345'::text, '{myvar:1}'::text, 'Content-Type:application/json'::text);
When I write this it shows the same error, postgres disconnects.
from couch-to-postgres.
ahh ok there we have the issue - something up with the http module for some reason - does the new doc appear in couch ?
from couch-to-postgres.
try
SELECT status FROM http_post('http://127.0.0.1:5984/example/12345'::text, '{myvar:1}'::text, 'Content-Type:application/json'::text);
from couch-to-postgres.
you should have both http_put and http_post available in the list of http functions - you should be able to insert with a POST as well
from couch-to-postgres.
that should be
SELECT status FROM http_post('http://127.0.0.1:5984/example'::text, '{myvar:1}'::text, 'Content-Type:application/json'::text);
let couch make the id
from couch-to-postgres.
SELECT status FROM http_post('http://127.0.0.1:5984/example'::text, '{myvar:1}'::text, 'Content-Type:application/json'::text);
status
415
(1 fila)
example=#
example=# let ocuchmake the id
example-# @gcarranza
example-# Markdown supported
example-# Write Preview
example-#
example-# Attach files by dragging & dropping or selecting them.
couchdb log:
[Sun, 04 Oct 2015 19:55:55 GMT] [info] [<0.13404.0>] 127.0.0.1 - - POST /example 415
from couch-to-postgres.
sorry the json was bad should be something like:
SELECT status FROM http_post('http://127.0.0.1:5984/example'::text, '{"myvar":1}'::text, 'Content-Type:application/json'::text);
from couch-to-postgres.
example=# SELECT status FROM http_post('http://127.0.0.1:5984/example'::text, '{"myvar":1}'::text, 'Content-Type:application/json'::text);
status
415
(1 fila)
couchdb log:
[Sun, 04 Oct 2015 20:31:04 GMT] [info] [<0.13724.0>] 127.0.0.1 - - POST /example 415
from couch-to-postgres.
mmm something up with my example - from: http://docs.couchdb.org/en/stable/intro/curl.html
shell> curl -H 'Content-Type: application/json' \
-X POST http://127.0.0.1:5984/demo \
-d '{"company": "Example, Inc."}'
{"ok":true,"id":"8843faaf0b831d364278331bc3001bd8", "rev":"1-33b9fbce46930280dab37d672bbc8bb9"}
That should be the same as:
SELECT status FROM http_post('http://127.0.0.1:5984/example', '{"company": "Example, Inc."}', 'application/json');
?
from couch-to-postgres.
Ahh i think thats the reason - please give this one a go:
SELECT status FROM http_post('http://127.0.0.1:5984/example', '{"company": "Example, Inc."}', 'application/json');
Note i have removed the content/type from the content/type arg
from couch-to-postgres.
i think that this could be the issue in the couch_put function as well
SELECT status FROM http_put('http://127.0.0.1:5984/' || TG_TABLE_NAME || '/' || NEW.id::text, NEW.doc::text, 'Content-Type:application/json'::text) INTO RES;
should be
SELECT status FROM http_put('http://127.0.0.1:5984/' || TG_TABLE_NAME || '/' || NEW.id::text, NEW.doc::text, 'application/json'::text) INTO RES;
from couch-to-postgres.
yes thatΕ works!!!!
example=# SELECT status FROM http_post('http://127.0.0.1:5984/example', '{"company": "Example, Inc."}', 'application/json');
status
201
(1 fila)
from couch-to-postgres.
π
from couch-to-postgres.
does:
SELECT status FROM http_put('http://127.0.0.1:5984/example/somenewid', '{"company": "Example, Inc."}', 'application/json');
work?
from couch-to-postgres.
no, didn't work
example=# SELECT status FROM http_post('http://127.0.0.1:5984/example/somenewid', '{"company": "Example, Inc."}', 'application/json');
status
400
(1 row)
from couch-to-postgres.
sorry that was ment to be http_put
from couch-to-postgres.
no, with http_put,it didn't work, postgres disconnects, however in Couchdb data has been succesfully inserted
from couch-to-postgres.
ok we can use post instead you just need to include the _id with in the doc instead of in the url - or let couch set it
so the couch_put needs to be like
BEGIN
IF (NEW.from_pg) IS NULL THEN
RETURN NEW;
ELSE
SELECT status FROM http_post('http://127.0.0.1:5984/' || TG_TABLE_NAME::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;
from couch-to-postgres.
sorry just so its clear
SELECT status FROM http_post('http://127.0.0.1:5984/' || TG_TABLE_NAME::text, NEW.doc::text, 'application/json'::text) INTO RES;
I think is the correct line to then run if put requests are causing an issue - note you need to include the doc._id or couch will set it
from couch-to-postgres.
if you are running UPDATES on the data (for more than a few rows) I suggest you take a look at the bits in the readme on _bulk_docs rather than run UPDATE on each row - in fact i think this is probably the best way to go for big inserts as well
from couch-to-postgres.
example=# INSERT INTO example (id, doc, from_pg) VALUES ('ooo', json_object('{_id,myvar}','{ooo, 100}')::jsonb, true);
ERROR: Result: (201)
example=# select * from example;
id | doc | from_pg
----+-----+---------
(0 rows)
however couchdb inserted, postgres didn't
this is the function
-- Function: couchdb_put()
-- DROP FUNCTION couchdb_put();
CREATE OR REPLACE FUNCTION couchdb_put()
RETURNS trigger AS
DECLARE
RES RECORD;
BEGIN
IF (NEW.from_pg) IS NULL THEN
RETURN NEW;
ELSE
SELECT status FROM http_post('http://127.0.0.1:5984/' || TG_TABLE_NAME::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;
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION couchdb_put()
OWNER TO tester;
from couch-to-postgres.
im guessing couch-to-postgres isnt running
start it up and things should now work
remember there may be a small delay after inserting the record and it being in postgres
from couch-to-postgres.
yes the couch-to-postgres wasn't running, insert works!!!!!!!
from couch-to-postgres.
cool - i think we may have found a bug with the pgsql-http module and put requests
from couch-to-postgres.
great, thanks a lot for your quick and right suggestions. Now I can try updates and multiple inserts with the on _bulk_docs module and might contact you later if necessary on this new issue.
from couch-to-postgres.
no probs - will close this issue - tbh a lot of the readme on actually updating/inserting docs is more musing by me rather than anything solid i am using in production but in various tests it has been quite reliable.
from couch-to-postgres.
Related Issues (20)
- Hovercraft / pl/Erlang / pl/sh - a place for any ideas on this HOT 1
- update readme to reflect change to http_post function in postgres http extension
- add _add + friends feed to api HOT 1
- daemon - add some kind of stats collection
- daemon - __sidekiq esque
- daemon wakeup idea HOT 1
- multi master replication HOT 5
- sort out couch_put - replace with couch_post
- look at upsert on postgres 9.5 for update function
- possible issue with couch restarting
- we want to find some tools to publish postgresql table to redis or couchdb as a restful service HOT 2
- works on workstation, not working on server HOT 27
- example.sql
- example use sql HOT 2
- Change logic for from_pg to from_couch HOT 2
- make libary and daemon into proper npm HOT 2
- tests - need to setup some automated tests
- attachments - issue to hold info on them HOT 3
- look at forever - for node daemon HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
π Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. πππ
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google β€οΈ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from couch-to-postgres.