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.

Laurenz Albe · Answer 1 · 2021-12-02T15:42:27.687

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.

edited Dec 02 '21 at 15:42

answered Dec 02 '21 at 14:42

Laurenz Albe

209,280
17
206
263

Thanks, Laurenz! I must've misunderstood what I was supposed to do to connect through Babelfish. So, I am expected to create my tables under the babelfish_db database? – Nick Dec 02 '21 at 14:48
Yes, I think that is their name for it. – Laurenz Albe Dec 02 '21 at 14:55
If I specify database name in the connection, I still receive 'database "babelfish_db" does not exist'. If I don't, I receive 'could not find default database for user "postgres"'. – Nick Dec 02 '21 at 15:01
No, `babelfish_db` is the PostgreSQL database. You will have to connect to `master` in Babelfish, then create your database. It is quite confusing. – Laurenz Albe Dec 02 '21 at 15:04
Where would you create tables? babelfish_db > public > tables > (here)? – Nick Dec 02 '21 at 15:07
Which "database" do you connect to from an application? babelfish_db? or master_dbo? or postgres? – Nick Dec 02 '21 at 15:09
A PostgreSQL application or an SQL Server application? – Laurenz Albe Dec 02 '21 at 15:13
Both a NodeJS application and an ASP application. – Nick Dec 02 '21 at 15:14
It is different, depending on which port and protocol you use to connect. – Laurenz Albe Dec 02 '21 at 15:23
I'm using Port 1433, and TDS for connection – Nick Dec 02 '21 at 15:26
For clarity, cross-DB access has nothing to do with single-db mode or multi-db mode. Also in single-db mode you can have cross-DB object references, for example to master or tempdb. – RobV Feb 09 '22 at 18:03

score 0 · Accepted Answer · answered Dec 03 '21 at 16:11

0

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.

answered Dec 03 '21 at 16:11

Nick

466
1
6
12

Note that 'select current_database()' is not T_SQL - it's actually a PG function that happens to be callable from TSQL. This only tells you that you are connected to this PG database; it has nothing to do with the T-SQL view of the world with simulated 'current SQL Server database' (as per db_name()) from a T-SQL perspective – RobV Feb 09 '22 at 18:02

score 0 · Answer 3 · answered Feb 09 '22 at 18:04

0

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.

answered Feb 09 '22 at 18:04

RobV

2,263
1
11
7

Connecting to Aurora Postgres (Babelfish, 1433)

3 Answers3