-
Notifications
You must be signed in to change notification settings - Fork 17
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Postgres disconnects after inserting into couchdb (http_put) #21
Comments
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
Put function:
Table:
|
sql schema ALTER DATABASE example OWNER TO tester; \connect example SET statement_timeout = 0; -- 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) -- 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) -- 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: testerCREATE FUNCTION couchdb_put() RETURNS trigger 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 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: testerCREATE FUNCTION json_object_set_key(json json, key_to_set text, value_to_set anyelement) RETURNS 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 ( ALTER TABLE example OWNER TO tester; -- TOC entry 2042 (class 0 OID 0) -- Name: COLUMN example.from_pg; Type: COMMENT; Schema: public; Owner: testerCOMMENT 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 ( 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 -- TOC entry 1920 (class 2606 OID 16395) -- Name: since_checkpoint_pkey; Type: CONSTRAINT; Schema: public; Owner: tester; Tablespace:ALTER TABLE ONLY since_checkpoints -- TOC entry 1923 (class 2620 OID 49157) -- Name: add_doc_to_couch; Type: TRIGGER; Schema: public; Owner: testerCREATE 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) -- Name: public; Type: ACL; Schema: -; Owner: postgresREVOKE ALL ON SCHEMA public FROM PUBLIC; -- Completed on 2015-10-04 12:17:36 CST -- PostgreSQL database dump complete |
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 |
with or without RAISE EXCEPTION the problem is the same- --Need to check RES for response code How can I install the old http? Where can I download it? |
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:
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 |
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 |
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? |
can you check the http_put function and paste it here so we can check all ok in that? |
New Log: 2015-10-04 13:02:04 CST [29152-2] LOG: server process (PID 29184) was terminated by signal 11: Segmentation fault Did you make any changes in your postgresql.conf for this purpose? this is the function Function: http_put: -- 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) OWNER TO tester; |
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:
and try to run the insert lets see if the http_put is doing something odd or the trigger stuff |
example=# INSERT INTO example (id, doc, from_pg) VALUES ('o', json_object('{_id,myvar}','{o, 100}')::jsonb, true); The postgres server log didn't show anything |
ok perfect thats what should happen |
the couch_put function is throwing the record away and not inserting it into the table |
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); |
example=# SELECT status FROM http_put('http://127.0.0.1:5984/example/12345'::text, '{myvar:1}'::text, 'Content-Type:application/json'::text); |
ahh ok there we have the issue - something up with the http module for some reason - does the new doc appear in couch ? |
try SELECT status FROM http_post('http://127.0.0.1:5984/example/12345'::text, '{myvar:1}'::text, 'Content-Type:application/json'::text); |
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 |
that should be
let couch make the id |
SELECT status FROM http_post('http://127.0.0.1:5984/example'::text, '{myvar:1}'::text, 'Content-Type:application/json'::text); status
(1 fila) example=# couchdb log: |
sorry the json was bad should be something like:
|
example=# SELECT status FROM http_post('http://127.0.0.1:5984/example'::text, '{"myvar":1}'::text, 'Content-Type:application/json'::text);
(1 fila) couchdb log: |
mmm something up with my example - from: http://docs.couchdb.org/en/stable/intro/curl.html
That should be the same as:
? |
Ahh i think thats the reason - please give this one a go:
Note i have removed the content/type from the content/type arg |
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; |
yes thatś works!!!! status
(1 fila) |
👍 |
does:
work? |
no, didn't work status
(1 row) |
sorry that was ment to be http_put |
no, with http_put,it didn't work, postgres disconnects, however in Couchdb data has been succesfully inserted |
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 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 |
sorry just so its clear
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 |
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 |
example=# INSERT INTO example (id, doc, from_pg) VALUES ('ooo', json_object('{_id,myvar}','{ooo, 100}')::jsonb, true); however couchdb inserted, postgres didn't this is the function -- Function: couchdb_put() -- DROP FUNCTION couchdb_put(); CREATE OR REPLACE FUNCTION couchdb_put() 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 |
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 |
yes the couch-to-postgres wasn't running, insert works!!!!!!! |
cool - i think we may have found a bug with the pgsql-http module and put requests |
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. |
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. |
I've installed couch-to-postgres and the http extension for postgres. When I insert from Postgres to Couchdb, postgres disconnects, however in Couchdb data has been succesfully inserted.
This is the change in the trigger function couchdb_put() I made, because with POST it never worked for me:
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;
This is the test:
INSERT INTO example (id, doc, from_pg) VALUES ('i', json_object('{_id,myvar}','{i, 100}')::jsonb, true);
This is the message I get from wireshark:
PUT /example/i HTTP/1.1\r\n
this is the message I get from the couchdb log:
Sun, 04 Oct 2015 16:29:33 GMT] [info] [<0.439.0>] 127.0.0.1 - - PUT /example/i 201
and this is the postgres log:
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
Versions:
postgresql-9.4
couchdb 1.6.1
pgsql-http 1.1
I would appreciate any help from you, thank you very much in advance!!
G.Carranza
The text was updated successfully, but these errors were encountered: