fordfrog/apgdiff

Another PostgreSQL Diff Tool

apgdiff's Issues

Don 't know how to parse: DISABLE TRIGGER

Converted from SourceForge issue 3033105, submitted by jmgouzit

Exception in thread "main" cz.startnet.utils.pgdiff.parsers.ParserException: Don
't know how to parse: DISABLE TRIGGER abzh_trigger_after_expfertiorg
at cz.startnet.utils.pgdiff.parsers.AlterTableParser.parseRows(AlterTabl
eParser.java:177)
at cz.startnet.utils.pgdiff.parsers.AlterTableParser.parse(AlterTablePar
ser.java:99)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDum
pLoader.java:176)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDum
pLoader.java:224)
at cz.startnet.utils.pgdiff.PgDiff.createDiff(PgDiff.java:32)
at cz.startnet.utils.pgdiff.Main.main(Main.java:39)

Parse error from ALTER COLUMN SET STORAGE ...

Converted from SourceForge issue 2158045, submitted by s_marshall

A fatal error is generated when apgdiff 1.3 encounters SQL of this form:

ALTER TABLE ... ALTER COLUMN ... SET STORAGE ...

e.g.

ALTER TABLE ONLY hdnowrad_conus_1km_tiles ALTER COLUMN raster SET STORAGE EXTERNAL;

A patch to AlterTableParser.java is attached which allows such SQL to be ignored. Of course, full support for this syntax would be more desirable.

Don't Drop Constraint which referes to a dropped column

Converted from SourceForge issue 2228636, submitted by guettli

Hi,

in old.sql

{{{
ADD CONSTRAINT myapp_buchungskreis_required_group_id_fkey FOREIGN KEY (required_group_id) REFERENCES auth_group(id) DEFERRABLE INITIALLY DEFERRED;
}}}

but required_group_id was dropped (does not exist) in new.sql.

apgdiff generates:
{{{
ALTER TABLE "myapp_buchungskreis"
DROP COLUMN "required_group_id",
ALTER COLUMN "default" SET NOT NULL;
...

ALTER TABLE "myapp_buchungskreis"
DROP CONSTRAINT "myapp_buchungskreis_required_group_id_fkey";
}}}

This fails:
ERROR: constraint "myapp_buchungskreis_required_group_id_fkey" does not exist

If you output all "ALTER TABLE ... DROP CONSTRAINT" statements before "ALTER TABLE ... DROP COLUMN" it works.

I moved the lines up and the update succeeded. Thank you for apgdiff.

Don 't know how to parse: DISABLE TRIGGER

Converted from SourceForge issue 3033093, submitted by jmgouzit

Exception in thread "main" cz.startnet.utils.pgdiff.parsers.ParserException: Don
't know how to parse: DISABLE TRIGGER abzh_trigger_after_expfertiorg
at cz.startnet.utils.pgdiff.parsers.AlterTableParser.parseRows(AlterTabl
eParser.java:177)
at cz.startnet.utils.pgdiff.parsers.AlterTableParser.parse(AlterTablePar
ser.java:99)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDum
pLoader.java:176)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDum
pLoader.java:224)
at cz.startnet.utils.pgdiff.PgDiff.createDiff(PgDiff.java:32)
at cz.startnet.utils.pgdiff.Main.main(Main.java:39)

Cannot parse complex VIEW definition

Converted from SourceForge issue 2124852, submitted by s_marshall

When differencing two schema, I encountered this fatal exception parsing a VIEW definition. The view is complicated, containing many parentheses, but it is valid and accepted by PostgreSQL. The example is from a PostgreSQL 8.3.3 database using apgdiff 1.2.

Exception in thread "main"
cz.startnet.utils.pgdiff.parsers.ParserException: Cannot parse command: CREATE VIEW make_new_partition_commands AS SELECT ((((((('SELECT public.find_or_create_time_partition('::text || quote_literal((drt.table_schema)::text)) || ', '::text) || quote_literal((drt.table_name)::text)) || ', '::text) || quote_literal(drt.time_column_name)) || ', '::text) || '''day'', now() + ''1 day''::interval);'::text) FROM (SELECT n.nspname AS table_schema, c.relname AS table_name, "substring"((t.tgargs)::text, '^[A-Za-z0-9_]+'::text) AS time_column_name, CASE WHEN (con.contype IS NULL) THEN false ELSE true END AS has_fk FROM ((((pg_trigger t JOIN pg_class c ON ((t.tgrelid = c.oid))) JOIN pg_namespace n ON ((c.relnamespace = n.oid))) JOIN pg_proc f ON ((t.tgfoid = f.oid))) LEFT JOIN pg_constraint con ON (((con.conrelid = c.oid) AND (con.contype = 'f'::"char")))) WHERE (f.proname ~ 'insert_into_time_partition'::text) ORDER BY CASE WHEN (con.contype IS NULL) THEN false ELSE true END) drt;
at cz.startnet.utils.pgdiff.parsers.CreateViewParser.parse(CreateViewParser.java:69)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDumpLoader.java:208)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDumpLoader.java:248)
at cz.startnet.utils.pgdiff.PgDiff.createDiff(PgDiff.java:37)
at cz.startnet.utils.pgdiff.Main.main(Main.java:43)

CLUSTER should be created after INDEXes

Converted from SourceForge issue 1732381, submitted by fordfrog

When new TABLE is being created and a CLUSTER is defined on it, CLUSTER is attempted to be created before INDEXes which doesn't work as CLUSTER depends on an INDEX.

Support for storage options in different places (patch)

Converted from SourceForge issue 2811047, submitted by aka50

In case, when tablespace specified, apg crashes.
This patch adds support for TABLESPACE (and of course a command line switch to disable this support).

NOT NULL should have DEFAULT value

Converted from SourceForge issue 1711091, submitted by primetime

Hello!

We used your tool to generate a diff between two dumps. The diff seems fine, but there is a problem when executing this sort of sentences:

ALTER TABLE "CARSERVICE"
ADD COLUMN "PRICE_CHARGE" double precision NOT NULL;

PostgreSQL returns the error:

ERROR: column "PRICE_CHARGE" contains null values

If you modify the sentence into this one:

ALTER TABLE "CARSERVICE"
ADD COLUMN "PRICE_CHARGE" double precision NOT NULL DEFAULT 0;

Then it works fine. Should "Another PostgreSQL Diff Tool" add those "DEFAULT" when using "ADD COLUMN"? We think that BOOLEAN should be set to FALSE by default, VARCHAR to an empty string and INTEGER and DOUBLE to 0.

We are not sure whether this is a problem with my PostgreSQL configuration, so we apologize in advance if this cannot be considered a bug (we are still new to PostgreSQL compared to other database systems).

Thanks in advance.

Null point exception at function name started with dot.

Converted from SourceForge issue 1849665, submitted by madejp

Due to my mistake, name of one function started with a dot (this was accepted by postgres). When i try make diff, i got null point exception:

Exception in thread "main" java.lang.NullPointerException
at cz.startnet.utils.pgdiff.parsers.CreateFunctionParser.parse(CreateFunctionParser.java:68)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDumpLoader.java:228)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDumpLoader.java:261)
at cz.startnet.utils.pgdiff.PgDiff.createDiff(PgDiff.java:37)
at cz.startnet.utils.pgdiff.Main.main(Main.java:34)

I found that dot in any position generate this error. I provide simple test file, which generate this error.

SET search_path not parsed correctly?

Converted from SourceForge issue 2122253, submitted by umsondo

Running the same DDL files (target file being a filesystem copy of the source) in apgdiff shows a diff of default search paths, ala 'SET search_path = foo, pg_catalog'; where the user probably expects the application to exit(0) or, less ideally, provide some other 'no valid diff' response. This is occuring in apgdiff 1.2 from schema dumps of Postgres 8.1.13.

crash with indexes on tables in schema

Converted from SourceForge issue 2832587, submitted by petere78

Example case:

CREATE TABLE foo.bar (
a integer
);

CREATE INDEX bar_index ON foo.bar (a);

apgdiff test.sql test.sql crashes with

xception in thread "main" java.lang.RuntimeException: Cannot get schema 'foo'. Need to issue 'CREATE SCHEMA foo;' before 'CREATE TABLE foo.bar...;'?
at cz.startnet.utils.pgdiff.parsers.CreateTableParser.parse(CreateTableParser.java:105)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDumpLoader.java:192)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDumpLoader.java:248)
at cz.startnet.utils.pgdiff.PgDiff.createDiff(PgDiff.java:37)
at cz.startnet.utils.pgdiff.Main.main(Main.java:43)

It appears to have to do with the table being in a non-default schema.

NoSuchMethodError: String.isEmpty

Converted from SourceForge issue 3067545, submitted by None

As I understand, you should either remove support for JRE 1.5 or replace all references to String.isEmpty() with, for example (s.length() == 0).

dropping column and dropping foreign key constraint

Converted from SourceForge issue 1782430, submitted by martin_cy

Hi, great tool!

running version: apgdiff-1.0.0_beta24.jar

found a small issue when running it on our db.

we renamed a table (tableA -> tableB) in the new schema and added some columns and dropped some others, instead of having the diff read it as full table drops and table creates we renamed the table on the "old" as well before running your diff app

app generates:

ALTER TABLE tableB
DROP COLUMN coll1,
ADD COLUMN coll3,
ALTER COLUMN coll4 DROP NOT NULL;

ALTER TABLE tableB
DROP CONSTRAINT coll1_fkey;

coll1 used to have a foreign key constraint called coll1_fkey

but when dropping the column coll1 the constraint is also dropped so there is no need to have it again as a specific transaction, and actually it will throw an error if the diff is run on the old db saying "coll1_fkey does not exist".

A small suggestion as well, output generated from the application maybe it should always start with a "BEGIN;" and finish with a "COMMIT;"

since if something goes wrong it will do a rollback and nothing is changed. That is always the first thing i do to the output generated.
Cheers
Martin

ALTER PROCEDURAL not supported in 2.0

Converted from SourceForge issue 3066796, submitted by thd84

The program borks on: 'ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;'

ALTER SEQUENCE OWNED BY

Converted from SourceForge issue 1900894, submitted by lmatsumura

ApgDiff don´t support

ALTER SEQUENCE OWNED BY

used by serial columns to create the dependency tree between the sequence and the table column.

CREATE OPERATOR not supported in 2.0

Converted from SourceForge issue 3066798, submitted by thd84

The program borks on: 'CREATE OPERATOR [...]'

indexs in new schemas are not picked up

Converted from SourceForge issue 2107326, submitted by martin_cy

old database got schema public only. A new schema is added called "schema1" in schema1 there is a table that got an index on it.

This index will not be included in the output!

looking at the code its quite obvious, it assume the schema must exist.. So i made a fix, please review it and add it. I've attached the modified pgDiffindexes.java (just adding an else to if (oldSchema != null) and submitting oldschema = null,

works correctly for all old cases and new cases.

I dont know how you would like to handle patches?

No DISABLE TRIGGER parser

Converted from SourceForge issue 2985853, submitted by dragomirivanov

Hello, I have some pg dumps, which contain ALTER TABLE ... DISABLE TRIGGER statements, which are not parsed.
Exception in thread "main" cz.startnet.utils.pgdiff.parsers.ParserException: Don't know how to parse: DISABLE TRIGGER articles_nom_relative_qty
at cz.startnet.utils.pgdiff.parsers.AlterTableParser.parseRows(AlterTableParser.java:177)
at cz.startnet.utils.pgdiff.parsers.AlterTableParser.parse(AlterTableParser.java:99)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDumpLoader.java:176)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDumpLoader.java:224)
at cz.startnet.utils.pgdiff.PgDiff.createDiff(PgDiff.java:32)
at cz.startnet.utils.pgdiff.Main.main(Main.java:39)
Is there any chance of this getting into next apgdiff release?

Thanks very much for this very useful tool :)
Best regards,
Dragomir

ParserException for CREATE VIEW

Converted from SourceForge issue 3007170, submitted by None

I got a parser exception while comparing two schema dumps. Please find the details as follows:

Exception in thread "main" cz.startnet.utils.pgdiff.parsers.ParserException: Can
not parse command: CREATE VIEW "tbl division" AS SELECT divisions.division_id AS
"Division Id", organisations.organisation_name AS "Division name", divisions.organisation_id AS "Org Id" FROM (divisions LEFT JOIN organisations ON ((organisations.organisation_id = divisions.organisation_id)));
at cz.startnet.utils.pgdiff.parsers.CreateViewParser.parse(CreateViewParser.java:63)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDumpLoader.java:185)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDumpLoader.java:224)
at cz.startnet.utils.pgdiff.PgDiff.createDiff(PgDiff.java:32)
at cz.startnet.utils.pgdiff.Main.main(Main.java:39)

Schemas with "CREATE DOMAIN" not supported in 2.0.1

Converted from SourceForge issue 3067908, submitted by None

I recently upgraded to 2.0.1, and after doing a diff of a schema dump with itself from a version 8.1 server, I get this error message:

Exception in thread "main" cz.startnet.utils.pgdiff.parsers.ParserException: Command not supported: CREATE DOMAIN lo AS oid;
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDumpLoader.java:260)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDumpLoader.java:285)
at cz.startnet.utils.pgdiff.PgDiff.createDiff(PgDiff.java:31)
at cz.startnet.utils.pgdiff.Main.main(Main.java:40)

parsing problems

Converted from SourceForge issue 1633622, submitted by marv_sf

apgdiff fails to parse certain valid syntaxes. For example, I'm getting:

Exception in thread "main" cz.startnet.utils.pgdiff.parsers.ParserException: Cannot parse command: CREATE TABLE reporter
at cz.startnet.utils.pgdiff.parsers.CreateTableParser.parse(CreateTableParser.java:75)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadSchema(PgDumpLoader.java:76)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadSchema(PgDumpLoader.java:113)
at cz.startnet.utils.pgdiff.PgDiff.createDiff(PgDiff.java:36)
at cz.startnet.utils.pgdiff.Main.main(Main.java:34)

trying to paser a certain file, where the table text looks like:

CREATE TABLE reporter
(
email character varying(50) NOT NULL,
frequency character varying(10) NOT NULL,
file character varying(2047) NOT NULL,
id serial NOT NULL,
stop_time date NOT NULL
)
WITHOUT OIDS;

I think someone copied the defination from pgadminIII instead of from pg_dump.

Changing the formatting is not really an option for what I'm trying to do. I'm trying to write a wrapper script around apgdiff (just to get the arguments right) so that I can use svn diff with apgdiff as its external diff command, in order to see what schema changes happened between certain versions of the code I have versioned.

Droping funcions and triggers

Converted from SourceForge issue 2991245, submitted by guest111

Triggers should be dropped before functions.

ERROR: cannot drop foo() because other objects depend on it

trigger trg_foo on tab_foo depends on function foo()

DROP FUNCTION problem

Converted from SourceForge issue 2176400, submitted by tmihail

Some problem. Generated DROP FUNCTION statements for functions whith OUT params are not right.

pg8.3.3

CREATE OR REPLACE FUNCTION users.activate_account(IN text, OUT error_code integer, OUT user_login text, OUT user_account_id bigint)
RETURNS record AS
$BODY$
..............
$BODY$
LANGUAGE 'plpgsql';

pgAdminIII 1.8.2

DROP FUNCTION users.activate_account(text);

apgdiff-1.3

DROP FUNCTION activate_account(text,integer,text,bigint); // function not found!!!

Please correct this.

Case sensitive patterns, quoted names

Converted from SourceForge issue 1648033, submitted by jmercun

Parsers assume uppercase CREATE/ALTER/etc. commands, which isn't always true (example dump pasted below).

Some dumps also have all names quoted - parser wrongly assumes quotes are a part of the name (example below).

Attachment: diff files for the parsers which address (some of) these issues (case insensitive patterns, quote handling).

Example of a dump my ISP sent me for a schema on their server:

-- TOC Entry ID 2 (OID 605638)

-- Name: admins_aid_seq Type: SEQUENCE Owner: xxxx

CREATE SEQUENCE "admins_aid_seq" start 1 increment 1 maxvalue 1000000000 minvalue 1 cache 1;

-- TOC Entry ID 108 (OID 605640)

-- Name: admins Type: TABLE Owner: enki

CREATE TABLE "admins" (
"aid" integer DEFAULT nextval('"admins_aid_seq"'::text) NOT NULL,
"companyid" integer DEFAULT 0 NOT NULL,
"groupid" integer DEFAULT 0 NOT NULL,
"username" character varying NOT NULL,
"password" character varying(40) NOT NULL,
"superuser" boolean DEFAULT 'f'::bool NOT NULL,
"name" character varying(40),
"surname" character varying(40),
"email" character varying(100) NOT NULL,
"tel" character varying(40),
"mobile" character varying(40),
"enabled" boolean DEFAULT 't'::bool NOT NULL,
"lastlogints" timestamp with time zone DEFAULT now() NOT NULL,
"expirienced" boolean DEFAULT 'f'::bool,
Constraint "admins_pkey" Primary Key ("aid")
);

Should add quotes to table and column names

Converted from SourceForge issue 1710134, submitted by primetime

Hello!

We have downloaded and tried your tool. Our needs are basically the same as you mention on your project home page, but there is a problem in the diff that your tool generates: it does not add the quotes around the table names and column names.

We use JPOX (http://www.jpox.org/) at work and it creates table names and column names all uppercase and quoted, so when executing any SQL sentence quotes must be used as well as PostgreSQL lowercases the names by default.

Example that works:

SELECT *
FROM "TEST";

Example that does not work:

SELECT *
FROM TEST;

You can find information regarding the quotes here ("Keywords and Identifiers" section):

http://www.commandprompt.com/ppbook/x1428

So, would it be possible for you to quote table and column names, or perhaps add an option to the command line tool that tells the tool to add the quotes (default could be left unquoted, as it is now)?

We are very happy that we could find such a useful and nice tool as yours, so thanks for your excellent work and keep up the good work!

Null Pointer Exception

Converted from SourceForge issue 1909539, submitted by travelfroggy

I can not run this on my file, I get a NULL pointer exception !

Find included two files to compare (they are only keys, i.e. the end of a normal DUMP file generated by pg_dump).

sequence needs to be created before it is referenced

Converted from SourceForge issue 1649112, submitted by markjugg

This is bug is against "beta17".

Overall, the tool worked very well. However, I ran into this bug.

A new column with added that included
DEFAULT nextval('foo_id_seq').

However, it wasn't until later the sequence was created by the diff:

CREATE SEQUENCE foo_id_seq

The sequence needs to be created before it is referenced.

Also, I noticed there is no final semi-colon at the end of the diff file, and it seems like there should be.

Thanks!

Mark

Alter column char->int

Converted from SourceForge issue 2114141, submitted by miksir

Diff of
CREATE TABLE t1 (
f1 character varying(30)
);
and
CREATE TABLE t1 (
f1 integer
);
will generate
ALTER TABLE t1
ALTER COLUMN f1 TYPE integer;
but is't not correct
ERROR: column "f1" cannot be cast to type "pg_catalog.int4"

Need exception for char->int converts (and may be others) for generate DROP/ADD pair.

Comments parsed as part of column type (sometimes)

Converted from SourceForge issue 2685058, submitted by radek_

Running the diff against the same file (so, obviously, no changes there):

$ java -jar dist/apgdiff-1.3.jar db-new.sql db-new.sql

ALTER TABLE foo
ALTER COLUMN -- TYPE col2 foo float /* TYPE change - table: foo original: col1 blok_pol numeric(8,2) new: col2 foo float */;

The db-new.sql file (one table, three columns) is attached.
If I remove the last column, this behaviour does not occur.
It changes even if I remove some spacing. Quite bizarre...

ALTER TABLE ... ENABLE|DISABLE TRIGGER ... not supported

Converted from SourceForge issue 1746647, submitted by sareche

Hi, great software... but there is a small bug, I contribute with this log

Exception in thread "main" cz.startnet.utils.pgdiff.parsers.ParserException: Don
't know how to parse: DISABLE TRIGGER ALL
at cz.startnet.utils.pgdiff.parsers.AlterTableParser.parseRows(AlterTabl
eParser.java:195)
at cz.startnet.utils.pgdiff.parsers.AlterTableParser.parse(AlterTablePar
ser.java:114)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadSchema(PgDumpLoader.
java:173)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadSchema(PgDumpLoader.
java:222)
at cz.startnet.utils.pgdiff.PgDiff.createDiff(PgDiff.java:36)
at cz.startnet.utils.pgdiff.Main.main(Main.java:34)

NullPointerException

Converted from SourceForge issue 1699472, submitted by cfischer69

Using beta 20. Feed apgdiff two dumps produced with pg_dump schema only uncompressed.

Here's the stack trace:

C:\Program Files\Java\jre1.6.0\lib>java -jar apgdiff-1.0.0_beta20.jar c:\qa.dmp c:\dev.dmp
Exception in thread "main" java.lang.NullPointerException
at java.util.regex.Matcher.getTextLength(Unknown Source)
at java.util.regex.Matcher.reset(Unknown Source)
at java.util.regex.Matcher.(Unknown Source)
at java.util.regex.Pattern.matcher(Unknown Source)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.getWholeFunction(PgDumpLoader.java:280)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadSchema(PgDumpLoader.java:188)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadSchema(PgDumpLoader.java:221)
at cz.startnet.utils.pgdiff.PgDiff.createDiff(PgDiff.java:36)
at cz.startnet.utils.pgdiff.Main.main(Main.java:34)

Object names not quoted properly

Converted from SourceForge issue 2513294, submitted by lorddef

The resulting SQL from this tool does not quote object names correctly. Take for example the following line in a schema dump:

"group" character varying(1) NOT NULL,

Comes out as

group character varying(1) NOT NULL,

And subsequently fails.

Another example, for a table name

CREATE TABLE "table-with-hyphens-in-name" (

comes out as

CREATE TABLE table-with-hyphens-in-name (

This is a real shame as otherwise this tool would be excellent

Partial indexes

Converted from SourceForge issue 2032983, submitted by randomdan

Partial indexes do not appear to be supported.

CREATE UNIQUE INDEX uni_products_undel_gcs ON products USING btree (id_group, code, suffix) WHERE (delete_date IS NULL);

Having looked briefly at the source for finding indexes, the regex does not match anything after the closing bracket of the column names... namely the WHERE clause.

Quoted table names aren't properly handled

Converted from SourceForge issue 3078630, submitted by None

Tried to use apgdiff to compare two databases which have table "user" (quoted). Got error:

Exception in thread "main" java.lang.NullPointerException
at cz.startnet.utils.pgdiff.parsers.CreateIndexParser.parse(CreateIndexParser.java:49)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDumpLoader.java:237)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDumpLoader.java:291)
at cz.startnet.utils.pgdiff.PgDiff.createDiff(PgDiff.java:31)
at cz.startnet.utils.pgdiff.Main.main(Main.java:40)

text field definition using ' to escape inner '

Converted from SourceForge issue 2094660, submitted by martin_cy

still a great tool!

tried it version 1.2 on a new database and ran into a problem..

table that cause problem look something like this:

CREATE TABLE user_preferences (
id integer DEFAULT nextval(('"user_preferences_id_seq"'::text)::regclass) NOT NULL,
exboxes text DEFAULT '''test1'',''test2'',''test3'',''test4'''::text
);

notice the many ' in for the exboxes text field. In sql it is valid to escape with '

Hopefully this is something you can fix with out to much effort.

the error thrown is:

java -jar ~/apgdiff-1.2.jar /tmp/t1.schema /tmp/t2.schema
Exception in thread "main" cz.startnet.utils.pgdiff.parsers.ParserException: Cannot parse command: CREATE TABLE user_preferences ( id integer DEFAULT nextval(('"user_preferences_id_seq"'::text)::regclass) NOT NULL, exboxes text DEFAULT '''test1'',''test2'',''test3'',''test4'''::text )
at cz.startnet.utils.pgdiff.parsers.CreateTableParser.parseRows(CreateTableParser.java:225)
at cz.startnet.utils.pgdiff.parsers.CreateTableParser.parse(CreateTableParser.java:112)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDumpLoader.java:192)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDumpLoader.java:248)
at cz.startnet.utils.pgdiff.PgDiff.createDiff(PgDiff.java:37)
at cz.startnet.utils.pgdiff.Main.main(Main.java:43)
Caused by: cz.startnet.utils.pgdiff.parsers.ParserException: Cannot parse command: ''test2''
at cz.startnet.utils.pgdiff.parsers.CreateTableParser.parseColumnDefs(CreateTableParser.java:151)
at cz.startnet.utils.pgdiff.parsers.CreateTableParser.parseRows(CreateTableParser.java:218)
... 5 more

Failing to parse

Converted from SourceForge issue 1674416, submitted by yoshicool

Asterisk12:~# java -jar apgdiff-1.0.0_beta18.jar schema.old schema.new
Exception in thread "main" cz.startnet.utils.pgdiff.parsers.ParserException: Cannot parse ALTER TABLE 'call_logs', line 'ALTER COLUMN id SET DEFAULT nextval('call_logs_id_seq'::regclass)'
at cz.startnet.utils.pgdiff.parsers.AlterTableParser.parseRows(AlterTableParser.java:154)
at cz.startnet.utils.pgdiff.parsers.AlterTableParser.parse(AlterTableParser.java:98)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadSchema(PgDumpLoader.java:84)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadSchema(PgDumpLoader.java:117)
at cz.startnet.utils.pgdiff.PgDiff.createDiff(PgDiff.java:36)
at cz.startnet.utils.pgdiff.Main.main(Main.java:34)
Caused by: cz.startnet.utils.pgdiff.parsers.ParserException: Don't know how to parse: ALTER COLUMN id SET DEFAULT nextval('call_logs_id_seq'::regclass)
at cz.startnet.utils.pgdiff.parsers.AlterTableParser.parseRows(AlterTableParser.java:149)
... 5 more

changed views are not picked up

Converted from SourceForge issue 1804162, submitted by martin_cy

Hi, thanks for the begin/commit in the last release, works good!

found another thing though! :)

we use views and we recently updated a few of them and to my surprise the change to the views were not picked up in your diff tool! Just to make sure i verified it in the schema dumps and the views are distinctly different (same name) but nothing about it in the output from the diff tool.

since its just one long string and views use the "CREATE OR REPLACE VIEW" so should be pretty easy to do.. i hope :)

cheers
and thanks.
Martin

Table constraints in CREATE TABLE are parsed as columns

Converted from SourceForge issue 1722521, submitted by mamuema

My new dump contains an unique constraint.

Beta22 then wants to add a column instead of a constraint:
ALTER TABLE MyTable
ADD COLUMN unique (a_id, u_id);

"COLUMN" should be removed to make it valid SQL.

Thanks,
Martin

can't run

Converted from SourceForge issue 1536662, submitted by nobody

I can't execute apgdiff beta8...

java -jar apgdiff.jar

Failed to load Main-Class manifest attribute from
apgdiff.jar

My java version is:

java -version

java version "1.5.0_07"
Java(TM) 2 Runtime Environment, Standard Edition (build
1.5.0_07-b03)
Java HotSpot(TM) Client VM (build 1.5.0_07-b03, mixed
mode, sharing)

My system are:
SuSE 10.0 x86 and SuSE 9.0 x86

SEQUENCE MAXVALUE/MINVALUE bug

Converted from SourceForge issue 2100013, submitted by miksir

touch test1
cat - >test2
CREATE SEQUENCE test_id_seq
INCREMENT BY 1
MAXVALUE 2147483647
NO MINVALUE
CACHE 1;
^D

java -jar apgdiff-1.2.jar test1 test2
CREATE SEQUENCE test_id_seq
INCREMENT BY 1
MAXVALUE 2147483647
MINVALUE 2147483647
CACHE 1;

Invalid FUNCTION Differences given

Converted from SourceForge issue 2957650, submitted by None

running apgdiff on the attached file against a copy of it's self causes it to report that the t_common_casttotext(timestamp...) functions need to be recreated
However if you remove the t_common_casttotext(time...) functions preceding them the files are counted as the same.

This appears in 1.3 and 1.4

Parsing of whole functions fails

Converted from SourceForge issue 1811401, submitted by ahermans

Attached is a testcase for a function that should be equal. But there seems to be something wrong with the way the functions are parsed because of the newlines. The whole body of the function with newlines is not included.

dropping columns too early

Converted from SourceForge issue 2804059, submitted by knollfrankweb

Hi all,

I have the following setup:
database1:
table1: primary key pk which is also a foreign key referencing table2.pk
table2: primary key pk

database2:
table1: primary key pk which is also a foreign key referencing table2.pk_new
table2: primary key pk_new

java -jar apgdiff-1.3.jar database1.schema database2.schema
produces the following statement:
ALTER TABLE table2
DROP COLUMN pk,
ADD COLUMN pk_new integer NOT NULL;

which applied to database1 results in "ERROR: cannot drop table table2 column pk because other objects depend on it". The problem is that the foreign key of table1 still references table2.pk which the above statement wants to drop.
So is the solution to first drop ALL foreign key constraints before altering column tables?

Cheers, Frank

changed views are not picked up

Converted from SourceForge issue 1804147, submitted by martin_cy

Hi, thanks for the begin/commit in the last release, works good!

found another thing though! :)

we use views and we recently updated a few of them and to my surprise the change to the views were not picked up in your diff tool! Just to make sure i verified it in the schema dumps and the views are distinctly different (same name) but nothing about it in the output from the diff tool.

since its just one long string and views use the "CREATE OR REPLACE VIEW" so should be pretty easy to do.. i hope :)

cheers
and thanks.
Martin

Spaces in field names

Converted from SourceForge issue 1814800, submitted by spdenne_work

Spaces in field names, with repeated first words, result in incorrect parsing.

diff'ing this table to itself produces alter table output:

CREATE TABLE quote_test (
"field name 1" int,
"field name 2" int,
"field name 3" int
);

java -jar apgdiff-1.0.0_beta26.jar test.sql test.sql

ALTER TABLE quote_test
ALTER COLUMN field TYPE name 2" int,
ALTER COLUMN field TYPE name 3" int;

2.0 doesn't support CREATE AGGREGATE

Converted from SourceForge issue 3065391, submitted by gryzman

this looks like an regression, as 1.4.x was supporting it fine. I am basically running same file I did successfully run with 1.4

and got:

Exception in thread "main" cz.startnet.utils.pgdiff.parsers.ParserException: Command not supported: CREATE AGGREGATE array_agg(anyelement) (
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDumpLoader.java:241)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDumpLoader.java:266)
at cz.startnet.utils.pgdiff.PgDiff.createDiff(PgDiff.java:31)
at cz.startnet.utils.pgdiff.Main.main(Main.java:40)

btw, maybe it is time to write a frontend that will connect to live postgresql database ?

Sequences dropped before dependent tables

Converted from SourceForge issue 2837629, submitted by xrkpwk

Apgdiff creates the diff for sequences before creating the one for tables. This is a problem when a table column's default value depends on a sequence. If you try to delete such a table from the schema, the resulting diff tries to delete the sequence first. This never works because the table that depends on it still exists.

Since tables can depend on sequences but sequences depend on nothing, moving the call to PgDiffTables.diffTables() before PgDiffSequences.diffSequences() in cz.startnet.utils.pgdiff.PgDiff.updateSchemas() seems to solve the problem.

Null pointer exception

Converted from SourceForge issue 1807868, submitted by deyah

I'm getting a null pointer exception when attempting a diff. Unfortunately I can't attached the .sql files as they are confidential.

java -jar apgdiff-1.0.0_beta26\apgdiff.jar oldmodel.sql newmodel.sql

Exception in thread "main" java.lang.NullPointerException
at cz.startnet.utils.pgdiff.parsers.CreateTableParser.parse(CreateTableParser.java:98)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDumpLoader.java:204)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDumpLoader.java:261)
at cz.startnet.utils.pgdiff.PgDiff.createDiff(PgDiff.java:37)
at cz.startnet.utils.pgdiff.Main.main(Main.java:34)

Different CREATE TABLE Syntax

Converted from SourceForge issue 2228687, submitted by guettli

Hi,

new.dump:
{{{
CREATE TABLE modwork_timeoutlog (
id integer NOT NULL,
timeout_id integer NOT NULL,
datetime timestamp with time zone NOT NULL,
ids text NOT NULL,
count integer NOT NULL,
CONSTRAINT modwork_timeoutlog_count_check CHECK ((count >= 0))
);
}}}

update.sql:
{{{
CREATE TABLE "modwork_timeoutlog" (
"id" integer DEFAULT nextval('modwork_timeoutlog_id_seq'::regclass) NOT NULL,
"timeout_id" integer NOT NULL,
"datetime" timestamp with time zone NOT NULL,
"ids" text NOT NULL,
"count" integer NOT NULL
);
}}}

I think it is a bug, that the column "id" gets this added:
DEFAULT nextval('modwork_timeoutlog_id_seq'::regclass)

CREATE SCHEMA not supported

Converted from SourceForge issue 1909536, submitted by travelfroggy

The instruction:
CREATE SCHEMA public AUTHORIZATION new_dbo; GRANT ALL ON SCHEMA public TO PUBLIC;

is not supported !
It generates an exception error "cannot parse the command" !

That's a shame !