dplyr - Error: x and y don't share the same src. Set copy = TRUE to copy y into x's source (this may be time consuming)
<p>Normally we do not find any trouble in using the below connection method and run queries from redshift</p>

<pre><code>require("RPostgreSQL")
drv &lt;- dbDriver("PostgreSQL")
conn &lt;- dbConnect(drv, dbname = "redshiftdb",
 host = "XX.XX.XX.XX", port = "1234",
 user = "userid", password = "pwd")

my_data &lt;- dbGetQuery(conn, "select a.*, b.* from redshiftdb.schema1.table1 inner join redshiftdb.schema2.table2 on a.key = b.key")
</code></pre>

<p>But the problem with this method is people can use long complex SQL queries which becomes hard to debug and illustrate while re engineering. Unless I am hard core SQL coder.</p>

<p>I have been learning R language since September and I thought it would it would be interesting to use dplyr joins and pipes to do the same work.
I connected using</p>

<pre><code>conn &lt;- src_postgres(dbname = "redshiftdb",
 host = "XX.XX.XX.XX", port = 1234,
 user = "userid",
 password = "pwd")
my_tbl1 &lt;- tbl(conn, dplyr::sql('select * from schema1.table1'))
my_tbl2 &lt;- tbl(conn, dplyr::sql('select * from schema1.table2'))
my_tbl3 &lt;- tbl(conn, dplyr::sql('select * from schema1.table3'))
my_tbl4 &lt;- tbl(conn, dplyr::sql('select * from schema1.table4'))
my_tbl5 &lt;- tbl(conn, dplyr::sql('select * from schema2.table1'))
my_tbl6 &lt;- tbl(conn, dplyr::sql('select distinct var1, var2 from schema2.table2'))
my_tbl7 &lt;- tbl(conn, dplyr::sql('select * from schema2.table3'))
</code></pre>

<p>I get the above error using left_join and %>% to join across tables in schema1 as well as cross schema (i.e. schema1 &amp; schema2)
When I use copy = TRUE it takes time and gives WARNING : only 1,00,000 records copied and it is really time consuming.</p>

<p>I have checked
<a href="https://github.com/hadley/dplyr/issues/244" rel="noreferrer">https://github.com/hadley/dplyr/issues/244</a>
but the pool method does not seem to work.
Any help would be much appreciated or else learning dplyr would be of no use to serve my immediate purpose.</p>