fordfrog / apgdiff

Another PostgreSQL Diff Tool
http://www.apgdiff.com
MIT License
355 stars 138 forks source link

Cannot parse string: ALTER TABLE public.xxxx ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY #277

Open jometho opened 4 years ago

jometho commented 4 years ago

Met this exception while trying to compare two PostgreSQL dumps exported using pg_dump from IntelliJ

Exception in thread "main" cz.startnet.utils.pgdiff.parsers.ParserException: Cannot parse string: ALTER TABLE public.agents ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY ( SEQUENCE NAME public.xxxx_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); Unsupported command at position 43 'ADD GENERATED BY DEF' at cz.startnet.utils.pgdiff.parsers.Parser.throwUnsupportedCommand(Parser.java:403) at cz.startnet.utils.pgdiff.parsers.AlterRelationParser.parseAlterColumn(AlterRelationParser.java:343) at cz.startnet.utils.pgdiff.parsers.AlterRelationParser.parse(AlterRelationParser.java:86) at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDumpLoader.java:239) at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDumpLoader.java:311) at cz.startnet.utils.pgdiff.PgDiff.createDiff(PgDiff.java:35) at cz.startnet.utils.pgdiff.Main.main(Main.java:39)

jalissonmello commented 3 years ago

Can you help testing ? There is 2.6.1 jar on https://github.com/jalissonmello/apgdiff/tree/develop/releases

AVee commented 2 years ago

I just tested this using 2.7.0, the basic case seems to work. However it still fails when dealing with permissions on the sequence, because it doesn't know the sequence exists.

This can be reproduced with this database:

CREATE TABLE test (id bigint generated always as identity, value text);
CREATE ROLE idtest;
GRANT select, update on sequence test_id_seq to idtest;

This generated the following dump:

--
-- Name: test; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.test (
    id bigint NOT NULL,
    value text
);

ALTER TABLE public.test OWNER TO postgres;

--
-- Name: test_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

ALTER TABLE public.test ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME public.test_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

--
-- Name: SEQUENCE test_id_seq; Type: ACL; Schema: public; Owner: postgres
--

GRANT SELECT,UPDATE ON SEQUENCE public.test_id_seq TO idtest;

And when compared to itself generates this error:

$ apgdiff idtest.sql idtest.sql 
Exception in thread "main" java.lang.RuntimeException: Cannot find sequence 'public.test_id_seq' for statement 'GRANT SELECT,UPDATE ON SEQUENCE public.test_id_seq TO idtest;'. Missing CREATE SEQUENCE?
        at cz.startnet.utils.pgdiff.parsers.GrantRevokeParser.parse(GrantRevokeParser.java:275)
        at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDumpLoader.java:278)
        at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDumpLoader.java:322)
        at cz.startnet.utils.pgdiff.PgDiff.createDiff(PgDiff.java:35)
        at cz.startnet.utils.pgdiff.Main.main(Main.java:39)