Postgres disconnects after inserting into couchdb (http_put)

[sysadminmike]

Postgres disconnects after inserting into couchdb (http_put)

[sysadminmike]

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();

[gcarranza]

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

[sysadminmike]

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;

[gcarranza]

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?

[sysadminmike]

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:

1. Postgres submits doc to couch and is saved by couch
2. Couch-to-postgres inserts the doc into postgres table
3. 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

[gcarranza]

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

[sysadminmike]

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?

[sysadminmike]

can you check the http_put function and paste it here so we can check all ok in that?

[gcarranza]

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
$BODY$ SELECT http(('PUT', $1, NULL, $3, $2)::http_request) $BODY$
LANGUAGE sql VOLATILE
COST 100;
ALTER FUNCTION http_put(character varying, character varying, character varying)

OWNER TO tester;

[sysadminmike]

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

[gcarranza]

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

[sysadminmike]

ok perfect thats what should happen

[sysadminmike]

the couch_put function is throwing the record away and not inserting it into the table

[sysadminmike]

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);

[gcarranza]

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.

[sysadminmike]

ahh ok there we have the issue - something up with the http module for some reason - does the new doc appear in couch ?

[sysadminmike]

try

SELECT status FROM http_post('http://127.0.0.1:5984/example/12345'::text, '{myvar:1}'::text, 'Content-Type:application/json'::text);

[sysadminmike]

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

[sysadminmike]

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

[gcarranza]

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

[sysadminmike]

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); 

[gcarranza]

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

[sysadminmike]

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');

?

[sysadminmike]

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

[sysadminmike]

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;

[gcarranza]

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)

[sysadminmike]

👍

[sysadminmike]

does:

SELECT status FROM http_put('http://127.0.0.1:5984/example/somenewid', '{"company": "Example, Inc."}', 'application/json');

work?

[gcarranza]

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)

[sysadminmike]

sorry that was ment to be http_put

[gcarranza]

no, with http_put,it didn't work, postgres disconnects, however in Couchdb data has been succesfully inserted

[sysadminmike]

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;

[sysadminmike]

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

[sysadminmike]

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

[gcarranza]

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
$BODY$
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;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION couchdb_put()
OWNER TO tester;

[sysadminmike]

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

[gcarranza]

yes the couch-to-postgres wasn't running, insert works!!!!!!!

[sysadminmike]

cool - i think we may have found a bug with the pgsql-http module and put requests

[gcarranza]

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.

[sysadminmike]

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.