How to amend Aurora Postgres' parameter `babelfishpg_tsql.escape_hatch_rowversion` value from `strict` to `ignore`

0

Hi.

TL;DR: I'm trying to understand how to change the parameter babelfishpg_tsql.escape_hatch_rowversion values to ignore for all SQL sessions ran against the TDS port.


I'm using Aurora Postgres and would like to create a table with a column of type timestamp. By default, the parameter babelfishpg_tsql.escape_hatch_rowversion defined in the Babelfish Postgres extension babelfishpg_tsql is set to strict, thus running a script to create a table with a column of type timestamp against the TDS port returns an error like the following: error : To use the TIMESTAMP datatype, set 'babelfishpg_tsql.escape_hatch_rowversion' to 'ignore'.

Example of a Create Table script:

CREATE TABLE schema1.tablename1 (
  column1 timestamp NOT NULL,
)

I can change the parameter value against the Postgres port using the command SET babelfishpg_tsql.escape_hatch_rowversion TO 'ignore';, and against the TDS port using the command sp_babelfish_configure 'babelfishpg_tsql.escape_hatch_rowversion', 'ignore'.

When running the command sp_babelfish_configure 'babelfishpg_tsql.escape_hatch_rowversion', 'ignore' against the TDS port in SQL Server Management Studio, I can run the above-mentioned create table command on the same SQL session successfully.

The parameter value change, though, is limited to a single session. I wanted to change the parameter value in a custom Aurora cluster parameter group created based on the default parameter group named aurora-postgresql15, but the parameter did not exist in the parameter group.

Liroy
asked 7 months ago277 views
1 Answer
0

To apply changes to the current session and on the cluster level, include the server keyword.

To set one or more escape hatches to the value specified, run sp_babelfish_configure ['hatch_name' [, 'strict'|'ignore' [, 'server']]. To make the settings permanent on a cluster-wide level, include the server keyword, such as shown in the following:

EXECUTE sp_babelfish_configure 'escape_hatch_unique_constraint', 'ignore', 'server'

To set them for the current session only, don't use server.

[+] https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/babelfish-strict.html#babelfish-escape_hatches

AWS
SUPPORT ENGINEER
answered 7 months ago
profile pictureAWS
EXPERT
reviewed 7 months ago
  • Thank you!

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions