GENERATED ... AS IDENTITY (options) wrapped in an extra pair of parentheses, fails to load in PostgreSQL

[Lunch418]

ora2pg version: 25.0

When converting an Oracle IDENTITY column with explicit sequence options, the generated PostgreSQL DDL wraps the options clause in an extra, redundant pair of parentheses. The result fails to load.

Oracle source:

CREATE TABLE customers (
    customer_id NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
    name        VARCHAR2(100)
);

[darold]

When I try your code the export by ora2pg last development code is:

CREATE TABLE customers (
        customer_id bigint GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 MAXVALUE 9223372036854775807 MINVALUE 1 NO CYCLE CACHE 20 ),
        name varchar(100)
) ;

and insert into PG give no error:

gilles=# CREATE TABLE customers (
        customer_id bigint GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 MAXVALUE 9223372036854775807 MINVALUE 1 NO CYCLE CACHE 20 ),
        name varchar(100)
) ;
CREATE TABLE

[darold]

please show the ora2pg output and the error.

[Lunch418]

Hi darold,

I think you ran it against a live Oracle instance, the MAXVALUE/MINVALUE/CACHE 20 in your output come from the catalog, they're not in my DDL. My case is file input (-i), no Oracle connection at all. That path still breaks on current master (0e694f7):

$ cat customers.sql
CREATE TABLE customers (
    customer_id NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
    name        VARCHAR2(100)
);
$ ora2pg -c /dev/null -i customers.sql -t TABLE -o out.sql
$ grep IDENTITY out.sql
	customer_id bigint GENERATED ALWAYS AS IDENTITY ((START WITH 1 INCREMENT BY 1)),
$ psql -f out.sql
psql:out.sql:16: ERROR:  syntax error at or near "("
LINE 2:  customer_id bigint GENERATED ALWAYS AS IDENTITY ((START WIT...
                                                          ^

In read_schema_from_file() the options captured at Ora2Pg.pm:3270 still have the parens from the DDL, and Ora2Pg.pm:8817 wraps them again. From the catalog they come without parens, so the live path is fine.

This fixes it here:

 							$self->{identity_info}{$tb_name}{$c_name}{options} =~ s/\s+/ /igs;
+							$self->{identity_info}{$tb_name}{$c_name}{options} =~ s/^\s*\((.*)\)\s*$/$1/s;

Tested on PG 16 with no options, BY DEFAULT ON NULL ... NOT NULL and a huge MAXVALUE, all load fine. I can send a PR if you prefer.

[darold]

commit f1e6978 fixes this issue.

[Lunch418]

Thanks for the quick fix! One more case though: if anything follows the options in the column definition, there's still an extra paren, because (.*) in the IDENTITY regex eats the rest of the column:

$ cat t.sql
CREATE TABLE t3 (
    id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 100 INCREMENT BY 5) NOT NULL,
    name VARCHAR2(100)
);
CREATE TABLE t5 (
    id NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) PRIMARY KEY,
    name VARCHAR2(100)
);
$ ora2pg -c /dev/null -i t.sql -t TABLE -o out.sql
$ grep IDENTITY out.sql
	id bigint GENERATED BY DEFAULT AS IDENTITY (START WITH 100 INCREMENT BY 5)),
	id bigint GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) PRIMARY KEY),

Both fail to load, and for t5 the primary key is gone too.

Capturing only the parenthesized group when there is one leaves the rest to the normal column parsing (Ora2Pg.pm:3266 on 16a0202):

-if ($c =~ s/\bGENERATED\s+(ALWAYS|BY\s+DEFAULT)\s+(ON\s+NULL\s+)?AS\s+IDENTITY\s*(.*)//is)
+if ($c =~ s/\bGENERATED\s+(ALWAYS|BY\s+DEFAULT)\s+(ON\s+NULL\s+)?AS\s+IDENTITY\s*(\([^\(\)]*\)|.*)//is)

With your change on top:

	id bigint GENERATED BY DEFAULT AS IDENTITY (START WITH 100 INCREMENT BY 5),
	id bigint GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
ALTER TABLE t5 ADD PRIMARY KEY (id);

Checked on PG 16 together with the earlier cases and the DBMS_METADATA style without parens (... AS IDENTITY MINVALUE 1 ... NOSCALE NOT NULL ENABLE), everything loads and identity/PK are in place.

[darold]

Thanks! Fix applied in commit d53a3df