Connection to AWS DB with RPostgres on M1 Mac

[samterfa]

Connection to AWS DB with RPostgres on M1 Mac

[krlmlr]

Thanks for the detailed report. A Redshift connection works with my M1 Mac, but I never tried sslmode .

Would you be comfortable cloning the repository and running a tweaked version for debugging?

[samterfa]

Sure, I'm happy to dig in. Thanks for the quick response.

[krlmlr]

Thanks. Could you please try to find out which line of the dbConnect() call fails?

[samterfa]

It fails here with length(opts) > 0.
if (length(opts) == 0) { ptr <- connection_create(character(), character(), check_interrupts) } else { ptr <- connection_create(names(opts), as.vector(opts), check_interrupts) }

[samterfa]

Printing opts shows the correct dbname, user, host, and port. client_encoding is also set to "utf8". I tried setting it manually to utf16 and tried using check_interrupts as TRUE and FALSE but same error each time.

[samterfa]

I've further identified the issue happens in the DbConnection::DbConnection() function in the file DbConnection.cpp at pConn_ = PQconnectdbParams(&c_keys[0], &c_values[0], false);. This returns CONNECTION_BAD.

[krlmlr]

Thanks. I wonder what RPostgreSQL is doing differently. Perhaps we can slightly change what we do here, to improve matters.

[samterfa]

I'm happy to try anything. I don't think anything's likely wrong with the package. I imagine I have some symlink issue or something preventing this from working correctly.

[krlmlr]

If you could clone the RPostgreSQL code and take a look what the connection call is looking like there, and with what arguments?

[samterfa]

The connection call in RPostgreSQL is my_connection = PQsetdbLogin(host, port, options, tty, dbname, user, password); . I then changed the connection code for RPostgres to use PQsetdbLogin instead of PQconnectdbParams and received the same error as before for Redshift. Also, as before, I was able to successfully connect to an AWS Aurora database. I can't tell any difference between RPostgreSQL and RPostgres when both are using PQsetdbLogin to create the connection. The only question I have now is if RPostgres isn't using the same version of libpq on my system as RPostgreSQL. Any ideas on a good way to test that?

[krlmlr]

Thanks. When I run R CMD INSTALL . for RPostgres, I'm seeing:

Using PKG_CFLAGS=-I/Users/kirill/git/R/r-dbi/RPostgres/.deps/include
Using PKG_LIBS=-L/Users/kirill/git/R/r-dbi/RPostgres/.deps/lib -lbrewpq -lbrewpgport -lbrewpgcommon -lbrewssl -lbrewcrypto -lldap -dynamic -Wl,-search_paths_first -lkrb5
Using PKG_PLOGR=-DPLOGR_ENABLE

Perhaps RPostgreSQL installation shows similar information?

[samterfa]

I installed RPostgreSQL from source and noticed this in the install logs.
system -lpq don't appear to work; use internal configure: Using internal package libpq-fe.h checking for "src/libpq/libpq-fe.h"... yes configure: creating ./config.status config.status: creating src/Makevars

Above this, there were other checks that seem like they should have been successful, but weren't. For example, I can run pg_config on in terminal and get information, so checking for pg_config... no seems odd to me.

checking for gcc... gcc
checking whether the C compiler works... yes
checking for C compiler default output file name... a.out
checking for suffix of executables...
checking whether we are cross compiling... no
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ISO C89... none needed
checking build system type... aarch64-apple-darwin21.5.0
checking host system type... aarch64-apple-darwin21.5.0
checking target system type... aarch64-apple-darwin21.5.0
checking for pg_config... no
configure: checking for PostgreSQL header files
configure: Checking include /usr/include.
configure: Checking include /usr/include/postgresql.
configure: Checking include /usr/local/include.
configure: Checking include /usr/local/include/postgresql.
configure: Checking include /usr/local/pgsql/include.
configure: Checking include /usr/local/postgresql/include.
configure: Checking lib /usr/lib.
configure: Checking lib /usr/lib/postgresql.
configure: Checking lib /usr/local/lib.
configure: Checking lib /usr/local/lib/postgresql.
configure: Checking lib /usr/local/postgresql/lib.
gcc -g -O2 -I -L conftest.c -lpq -o pqconftest

[samterfa]

Also when I run DBI::dbGetInfo(RPostgres::Postgres()), I should get the version of libpq it's using, right? When I run it I get $client.version [1] ‘13.2’. However, I brew installed version 14.5 which is the one returned by which psql. Do I have a rogue version of libpq on my machine?

[krlmlr]

Good catch: 13.2 might come from our autobrew script. Can you try setting the DISABLE_AUTOBREW environment variable to 1, and rerun?

[samterfa]

Success! I had to set DISABLE_AUTOBREW to 1, and add /opt/homebrew/opt/libpq/bin to my PATH in RStudio, then install from source. RPostgres::dbGetInfo(RPostgres::Postgres()) now shows 14.5 as expected, and I can connect to the Redshift database!

Thanks so much for your patience with this!

[krlmlr]

Thank you for pushing through!

@jeroen: the autobrew version of libpq seems to be too old to support Redshift. What's the best way to proceed?

[jeroen]

I'll look into this. I upgraded the autobrew versions for MacOS 11.0+ (including arm) to libpq 14.5. So you can test those from:

install.packages("RPostgres", repos = 'https://r-dbi.r-universe.dev')

However CRAN still tarets macos high-sierra (10.13) and I'm having difficulty getting the new libpq to work on there.

[jeroen]

I've now also updated the other autobrew versions. So when you submit the next version to CRAN, new MacOS binaries will automatically be built with the new version.