0

I'm using PostgreSQL 9.3. I have two separated databases which have a tables I need to join. As known I can use dblink query to access to the remote database.

select * 
from 
    customer  
    INNER JOIN  
    dblink('host=192.168.3.9 dbname=db2 user=postgres password=postgres', '
        SELECT
            id
        FROM campaign_customer
        ') AS table2 (
            int id
            date_added char(50)
        ) ON customer.cust_id = table2.id

It looks a bit strange. Does such a solution cause a potential (I mean join via dblink) risk? If yes, can you explain what exact is that risk? I would like to look at any other possible solution.

I'm especially interested in the performance of such query and how it can affect to a transaction to a different database?

  • Please add a tag for the specific RDBMS you're using. – Barmar Sep 17 '14 at 07:27
  • @Barmar I'm sorry. Fixed. –  Sep 17 '14 at 07:29
  • What kinds of risk are you worried about? Security? Slow Queries? Unreliable Queries? Excess resource usage? – harmic Sep 17 '14 at 07:32
  • http://stackoverflow.com/questions/4678862/joining-results-from-two-separate-databases expands on KIM's answer and explains why a fdw will typically have better performance than dblink. – John Powell Sep 17 '14 at 08:24

1 Answers1

0

I'd ratter say FOREIGN DATA WRAPPER is better way which can avoid connection string exposure.

KIM
  • 1,204
  • 2
  • 17
  • 33