CODEWITHSUNDEEP
postgresqlforeign-data-wrappermulticorn
Zubair Nabi
Zubair Nabi

Reputation: 1046

Async foreign tables, foreign data wrapper

i am working on foreign data wrappers in postgres, using multicorn and using triggers to insert data in foreign tables, however i do not want the postgres to wait for response after trigger, just trigger inserts it and then forgets. how can that be possible.

Actually i am using it for a foreign table

CREATE FOREIGN TABLE media_es (
    id BIGINT,
    title TEXT,
    description TEXT,
    tags TEXT,
    query TEXT,
    score NUMERIC
  )
  SERVER multicorn_es
  OPTIONS (
      host 'elasticsearch',  
      port '9200',
      index 'test',
      type 'media',
      rowid_column 'id',
      query_column 'query',
      score_column 'score'
  );


CREATE TRIGGER es_insert_media
      AFTER INSERT
          ON media
      FOR EACH ROW
          EXECUTE PROCEDURE index_media();

CREATE OR REPLACE FUNCTION index_media()
      RETURNS trigger
      AS $def$
          BEGIN
          INSERT INTO media_es
                (
                  id,
                  title,
                  description,
                  tags
              )
          VALUES
              (
                  NEW.id,
                  NEW.title,
                  NEW.description,
                  NEW.tags
              )
          ;
          RETURN NEW;  
      END;
  $def$ LANGUAGE plpgsql;

Upvotes: 0

Views: 435

Answers (1)

Ameba Brain
Ameba Brain

Reputation: 343

postgres dblink extension allows async calls to remote server via dblink-send-query command.

not sure how it will work within trigger in terms of establishing multiple connections. caution should be taken here for resource leakage

Upvotes: 0

Related Questions