[Bug]: Backup with pg_dump fails on sys.timestamp column

[staticlibs]

What happened?

With rowversion hatch enabled:

exec sp_babelfish_configure 'babelfishpg_tsql.escape_hatch_rowversion', 'ignore'

If we create the following DB:

create database db1
use db1
create table tab1(col1 timestamp)

Then pg_dump fails on this DB:

PGPASSWORD=12345678 ./bin/pg_dump -h 127.0.0.1 -p 5432 -U jdbc_user --bbf-database-name db1 -f db1.sql jdbc_testdb
pg_dump: error: query failed: ERROR:  column "col1" is a ROWVERSION/TIMESTAMP column
DETAIL:  ROWVERSION/TIMESTAMP columns cannot be used in COPY.
pg_dump: detail: Query was: COPY db1_dbo.tab1 (col1) TO stdout;

At the same time, pg_dump works fine if we replace timestamp type with its synonym rowversion:

create database db2
use db2
create table tab2(col2 rowversion)

It appeared that this check in pg_dump passes for rowversion, but fails for timestamp. This happens because the type name for timestamp is fetched by pg_dump in a partially quoted form as an output of pg_catalog.format_type function (simplified query):

select pg_catalog.format_type(typ.oid, null)
from pg_attribute att
join pg_type typ on typ.oid = att.atttypid
where att.attrelid = 'db1_dbo.tab1'::regclass::oid
and att.attname = 'col1'

sys."timestamp"

Quotes are added around the timestamp type name in format_type implementation at this point because the timestamp (unlike the rowversion) is a Postgres keyword.

It is not immediately clear how to fix this issue - whether the timestamp/rowversion check can be extended in pg_dump, or whether the implementation of format_type needs a special case for sys.timestamp.

Version

BABEL_4_X_DEV (Default)

Extension

None

Which flavor of Linux are you using when you see the bug?

Fedora

Relevant log output

No response

Code of Conduct

• I agree to follow this project's Code of Conduct.

[rishabhtanwar29]

Hi @staticlibs, we actually recommend using --quote-all-identifiers option with pg_dump command, see here. I verified that pg_dump works well when --quote-all-identifiers option is used.

[staticlibs]

@rishabhtanwar29 Thanks for the info! I will consider using --quote-all-identifiers option with pg_dump in scripts/tools.