Multiline RULE declarations are not parsed correctly

[ghost]

Original author: p.malo...@gmail.com (May 31, 2013 16:03:22)

Using PostgreSQL 9.2.
Given a migration that creates a multiline rule:
CREATE OR REPLACE RULE my_rule AS
ON DELETE TO some_table
DO ALSO
(
DELETE FROM some_other_table WHERE some_other_table.id=old.id;
DELETE FROM some_other_table2 WHERE some_other_table2.id=old.id;
);

This conforms to the syntax as stated here: http://www.postgresql.org/docs/9.2/static/sql-createrule.html

Flyway returns the following error:
ERROR: com.googlecode.flyway.core.api.FlywayException: Error executing statement at line 14: CREATE OR REPLACE RULE my_rule AS
ON DELETE TO some_table
DO INSTEAD
(
DELETE FROM some_other_table WHERE some_other_table.id=old.id
ERROR: Caused by org.postgresql.util.PSQLException: ERROR: syntax error at end of input
Position?: 136
ERROR: FlywayException: Migration of schema "data" to version 4.0.1.1 failed! Changes successfully rolled back.
ERROR: Occured in com.googlecode.flyway.core.command.DbMigrate.applyMigration() at line 275

From what I understand of Flyway's parser, as soon as it detects the ';' delimiter, it ends the statement and tries to run it, but in this case the statement is not completely parsed.
I believe this is a rather rare syntax where nested statements are not surrounded by dollar-quotes in PostgreSQL (like FUNCTIONs).

Thanks,
Patrick Malouin

Original issue: http://code.google.com/p/flyway/issues/detail?id=507

[emilecantin]

+1

[emilecantin]

This is a potential fix for this issue. The main limitation is that the opening and closing parentheses need to be on their own line, which is good enough for our purposes, but not ideal.

[ShiloHaendler]

Hi,

any progress on that matter?
I'm running flyway 3.2.1 with PostgreSQL 9.3, and that's still happening.

from our side, his seems to be a blocker from using flyway :(

Thanks,
Shilo

[axelfontaine]

@ShiloHaendler You are free to sponsor this fix if you want to have this sooner: http://flywaydb.org/support/

[kjkrol]

Workaround: Put a comment after each semicolon and remove semicolon from the last line.
eg.

CREATE OR REPLACE RULE my_rule AS
ON DELETE TO some_table
DO ALSO
(
DELETE FROM some_other_table WHERE some_other_table.id=old.id; -- comment
DELETE FROM some_other_table2 WHERE some_other_table2.id=old.id
);

[ShiloHaendler]

Thanks for the tip :)

On Wed, 9 Mar 2016 17:32 Karol Król, notifications@github.com wrote:

Workaround: Put a comment after each semicolon and remove semicolon from
the last line.
eg.

CREATE OR REPLACE RULE my_rule AS
ON DELETE TO some_table
DO ALSO
(
DELETE FROM some_other_table WHERE some_other_table.id=old.id; -- comment
DELETE FROM some_other_table2 WHERE some_other_table2.id=old.id
);

—
Reply to this email directly or view it on GitHub
#183 (comment).