[YSQL] SQL script produced by ysql_dumpall contains a syntax error and cannot be imported back correctly.

[User-26]

Jira Link: DB-9651

Description

Hello! I'm trying to copy data to another DB via SQL script created by ysql_dumpall, but getting the following error during importing:

ysqlsh:all-dbs-export.sql:10220159: invalid command \chain_gln;
ysqlsh:all-dbs-export.sql:10220166: ERROR:  syntax error at or near "CREATE"
LINE 6: CREATE INDEX b65db84a26d64db16ff1913d514a1332 ON gln.from_gb...
        ^

10220158    ALTER TABLE ONLY gln.master_gln_ranking
10220159        ADD CONSTRAINT "gln\chain_gln" UNIQUE USING INDEX gln\chain_gln;
10220160    
10220161    
10220162    --
10220163    -- Name: b65db84a26d64db16ff1913d514a1332; Type: INDEX; Schema: gln; Owner: dev-1
10220164    --
10220165    
10220166    CREATE INDEX b65db84a26d64db16ff1913d514a1332 ON gln.from_gbq_master_stores USING lsm (_airbyte_emitted_at HASH);

It looks like there is an issue with "gln\chain_gln" having a backward slash in the name.

Issue Type

kind/bug

Warning: Please confirm that this issue does not contain any sensitive information

• [X] I confirm this issue does not contain any sensitive information.

[ddorian]

Hi @User-26

What version are you using?

Can you provide a small schema so we can replicate?

I tried with a short example but couldn't replicate.

[User-26]

Hello, @ddorian!

Version 2.18.3.0 Here is the code for reproducing the issue:

CREATE DATABASE demo_github_issue_20656_db;
\connect demo_github_issue_20656_db

CREATE SCHEMA demo_schema;

CREATE TABLE demo_schema.demo_table (
    aaa bigint NOT NULL,
    bbb bigint NOT NULL,
    ccc numeric(10,5),
    ddd timestamp without time zone DEFAULT now() NOT NULL
);

CREATE UNIQUE INDEX NONCONCURRENTLY "index_name_with\backslash" ON demo_schema.demo_table USING lsm (aaa HASH, bbb ASC);

ALTER TABLE ONLY demo_schema.demo_table ADD CONSTRAINT "index_name_with\backslash" UNIQUE USING INDEX "index_name_with\backslash";

Steps to reproduce the issue:

1. Execute the above script.
2. Export data using ysql_dump.
3. Import data from step 2 using ysqlsh.
4. Check for error "invalid command \backslash" in logs.

The SQL script created by ysql_dump doesn't have escape quotes around index's name "index_name_with\backslash" and therefore cannot be imported back. The psql from PostgreSQL doesn't have this issue.

[ddorian]

Thank you, can confirm also on ysql_dump in 2.20.1.1

[ddorian]

Also doesn't work with ysql_dump --quote-all-identifiers.