JDBC river

Elastic Stack Elasticsearch
1

How to run function in postgres having insert statement in jdbc river? When I run it I get error:

org.postgresql.util.PSQLException: ERROR: cannot execute INSERT in a read-only transaction

I enabled write and got error:

org.postgresql.util.PSQLException: A result was returned when none was expected.

My jdbc river parameters:

  PUT /_river/postgres_pm_file_river/_meta
    {
        "type" : "jdbc",
        "jdbc" : {
            "url" : "jdbc:postgresql://localhost:5432/test",
            "user" : "test",
            "password" : "test#",
            "sql" : [
                 {
                    "statement" : "select  t_id _id,file_name from file_base"              
                }
            ],
            "index" : "pm_file",
            "type" : "pm_file_stat_2g",
            "schedule": "0 0/1 * * * ?"
        },
        "type_mapping" : {
                  "postgres_pm_file_river" : {
                      "properties" : {
                           "modified_date" : { 
                                "type" : "date",
                                "format" : "yyyy-MM-dd HH:mm:ssz"
                           }                       
                      }
                  }
            }
    }
2

Hello,

you can't execute the write requests, however you can execute procedures in which you can execute your write request.

I'm wrote an article about this, in French and on MySQL, but I suppose you can do the same thing with PostGreSQL.

http://blog.dev-art.fr/nosql/elastic-search-river-jdbc-incremental/

3

I don't think postgres have stored procedure. I am doing insert using function.

4

Can you show the setup with the insert statement?

Insert statement should work.

5

Here is my function in postgres:

CREATE OR REPLACE FUNCTION file_stat()
  RETURNS TABLE(name text,uid_pk integer) AS
$BODY$
DECLARE
    cnt integer;
BEGIN
select count(*) into cnt from test a where a.name > (select max(max_ins_time) from test_time);
IF cnt > 0 THEN
	insert into test
	select max(g.begin_timestamp) max_ins_time,'2G' file_type from test g where g.begin_timestamp > (select max(max_ins_time) from test_time)	
	RETURN QUERY
	select name,uid_pk from test b where b.begin_timestamp > (select max(max_ins_time) from test_time);
end if;
END;
6

Yes, but I asked for the setup with the insert statement.

It seems you forgot to declare the statement as callable.

7

@jprante making callable works. Thank you !

For anyone seeking reference:

"sql" : [
             {
                "statement" : "select  t_id _id,file_name from file_base",
                 "callable" : true
            }