Connecting to Aurora Postgres (Babelfish, 1433)

Question

I'm attempting to connect to a new Aurora PostgreSQL instance with Babelfish enabled.

NOTE: I am able to connect to the instance using the pg library through the normal port 5432 (the Postgres TDAS endpoint).

However, for this test, I am attempting to connect through the Babelfish TDS endpoint (1433) using the standard mssql package.

If I specify a database name (it is correct), I receive the error 'database "postgres" does not exist':

var config = {
    server: 'xxx.us-east-1.rds.amazonaws.com',
    database: 'postgres',
    user: 'xxx',
    password: 'xxx'
};

and the connection closes since the connection fails.

if I omit the database property in the config, like:

var config = {
    server: 'xxx.us-east-1.rds.amazonaws.com',
    user: 'xxx',
    password: 'xxx'
};

It will connect. Also, I can use that connection to query basic things like SELECT CURRENT_TIMESTAMP and it works!

However, I can't access any tables.

If I run:

SELECT COUNT(1) FROM PERSON

I receive an error 'relation "person" does not exist'.

If I dot-notate it:

SELECT COUNT(1) FROM postgres.dbo."PERSON"

I receive an error "Cross DB query is not supported".

So, I can't connect to the specific database directly and if I connect without specifying a database, I can't cross-query to the table.

Any one done this yet?

Or, if not, any ideas on helping me figure out what to try next? I'm out of ideas.

Answer 1

The answer was that I should be connecting to database "master".

Even though there is no database titled master in the instance, you still do connect to it.

Once connected, running the following:

select current_database();

This will indicate you are connected to database "babelfish_db".

I don't know how that works or why a database would have an undocumented alias.

Answer 2

Babelfish databases (that you connect to on port 1433) have nothing to do with PostgreSQL databases (port 5432). Essentially, all of Babelfish lives within a single PostgreSQL database (parameter babelfishpg_tsql.database_name).

You seem to have a single-db setup, because Cross DB query is not supported. With such a setup, you can only have a single database via port 1433 (apart from master and tempdb). You have to use CREATE DATABASE to create that single database (if it isn't already created; ask sys.databases).

I can't tell if it is supported to create a table in PostgreSQL (port 5432) and use it on port 1433 (the other way around is fine), but if so, you have to create it in a schema that you created with CREATE SCHEMA while connected on port 1433.

Answer 3

The bigger answer here is that cross-DB object references are not currently supported in Babelfish, outside your current SQL Server database. This is currently being worked on. Stay tuned.