Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Problem with 0.3.7 and postgresql #34

Closed
tvaughan opened this issue May 2, 2014 · 13 comments
Closed

Problem with 0.3.7 and postgresql #34

tvaughan opened this issue May 2, 2014 · 13 comments

Comments

@tvaughan
Copy link

tvaughan commented May 2, 2014

This happens in 0.3.7 and not 0.3.6. I assume this is related to this commit: 7173605

Any clue? Thanks!

$ cat migrations/0001-create-extensions.up.sql 
CREATE EXTENSION IF NOT EXISTS hstore;
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
$ lein with-profile ragtime ragtime migrate -d "jdbc:postgresql:redacted"
Applying 0001-create-extensions
org.postgresql.util.PSQLException: Too many update results were returned.
    at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleCommandStatus(AbstractJdbc2Statement.java:2733)
    at org.postgresql.core.v3.QueryExecutorImpl$1.handleCommandStatus(QueryExecutorImpl.java:452)
    at org.postgresql.core.v3.QueryExecutorImpl.interpretCommandStatus(QueryExecutorImpl.java:2215)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1847)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:405)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2909)
    at clojure.java.jdbc.deprecated$execute_batch.invoke(deprecated.clj:423)
    at clojure.java.jdbc.deprecated$do_commands$fn__231.invoke(deprecated.clj:435)
    at clojure.java.jdbc.deprecated$transaction_STAR_.invoke(deprecated.clj:389)
    at clojure.java.jdbc.deprecated$do_commands.doInvoke(deprecated.clj:434)
    at clojure.lang.RestFn.invoke(RestFn.java:408)
    at ragtime.sql.files$run_sql_fn$fn__364$fn__365$fn__366.invoke(files.clj:80)
    at clojure.java.jdbc.deprecated$transaction_STAR_.invoke(deprecated.clj:377)
    at ragtime.sql.files$run_sql_fn$fn__364$fn__365.invoke(files.clj:77)
    at clojure.java.jdbc.deprecated$with_connection_STAR_.invoke(deprecated.clj:307)
    at ragtime.sql.files$run_sql_fn$fn__364.invoke(files.clj:76)
    at clojure.lang.AFn.applyToHelper(AFn.java:154)
    at clojure.lang.AFn.applyTo(AFn.java:144)
    at clojure.core$apply.invoke(core.clj:624)
    at ragtime.main$wrap_println$fn__298.doInvoke(main.clj:19)
    at clojure.lang.RestFn.invoke(RestFn.java:408)
    at ragtime.core$migrate.invoke(core.clj:38)
    at ragtime.core$migrate_all.invoke(core.clj:60)
    at ragtime.core$migrate_all.invoke(core.clj:53)
    at ragtime.main$migrate.invoke(main.clj:32)
    at clojure.lang.AFn.applyToHelper(AFn.java:154)
    at clojure.lang.AFn.applyTo(AFn.java:144)
    at clojure.core$apply.invoke(core.clj:626)
    at ragtime.main$_main.doInvoke(main.clj:67)
    at clojure.lang.RestFn.invoke(RestFn.java:703)
    at clojure.lang.Var.invoke(Var.java:442)
    at user$eval5$fn__7.invoke(form-init3619964908012902520.clj:1)
    at user$eval5.invoke(form-init3619964908012902520.clj:1)
    at clojure.lang.Compiler.eval(Compiler.java:6703)
    at clojure.lang.Compiler.eval(Compiler.java:6693)
    at clojure.lang.Compiler.load(Compiler.java:7130)
    at clojure.lang.Compiler.loadFile(Compiler.java:7086)
    at clojure.main$load_script.invoke(main.clj:274)
    at clojure.main$init_opt.invoke(main.clj:279)
    at clojure.main$initialize.invoke(main.clj:307)
    at clojure.main$null_opt.invoke(main.clj:342)
    at clojure.main$main.doInvoke(main.clj:420)
    at clojure.lang.RestFn.invoke(RestFn.java:421)
    at clojure.lang.Var.invoke(Var.java:383)
    at clojure.lang.AFn.applyToHelper(AFn.java:156)
    at clojure.lang.Var.applyTo(Var.java:700)
    at clojure.main.main(main.java:37)
Exception in thread "main" java.sql.BatchUpdateException: Batch entry 1 <unknown> was aborted.  Call getNextException to see the cause., compiling:(/tmp/form-init3619964908012902520.clj:1:90)
    at clojure.lang.Compiler.load(Compiler.java:7142)
    at clojure.lang.Compiler.loadFile(Compiler.java:7086)
    at clojure.main$load_script.invoke(main.clj:274)
    at clojure.main$init_opt.invoke(main.clj:279)
    at clojure.main$initialize.invoke(main.clj:307)
    at clojure.main$null_opt.invoke(main.clj:342)
    at clojure.main$main.doInvoke(main.clj:420)
    at clojure.lang.RestFn.invoke(RestFn.java:421)
    at clojure.lang.Var.invoke(Var.java:383)
    at clojure.lang.AFn.applyToHelper(AFn.java:156)
    at clojure.lang.Var.applyTo(Var.java:700)
    at clojure.main.main(main.java:37)
Caused by: java.sql.BatchUpdateException: Batch entry 1 <unknown> was aborted.  Call getNextException to see the cause.
    at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2762)
    at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleCommandStatus(AbstractJdbc2Statement.java:2733)
    at org.postgresql.core.v3.QueryExecutorImpl$1.handleCommandStatus(QueryExecutorImpl.java:452)
    at org.postgresql.core.v3.QueryExecutorImpl.interpretCommandStatus(QueryExecutorImpl.java:2215)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1847)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:405)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2909)
    at clojure.java.jdbc.deprecated$execute_batch.invoke(deprecated.clj:423)
    at clojure.java.jdbc.deprecated$do_commands$fn__231.invoke(deprecated.clj:435)
    at clojure.java.jdbc.deprecated$transaction_STAR_.invoke(deprecated.clj:389)
    at clojure.java.jdbc.deprecated$do_commands.doInvoke(deprecated.clj:434)
    at clojure.lang.RestFn.invoke(RestFn.java:408)
    at ragtime.sql.files$run_sql_fn$fn__364$fn__365$fn__366.invoke(files.clj:80)
    at clojure.java.jdbc.deprecated$transaction_STAR_.invoke(deprecated.clj:377)
    at ragtime.sql.files$run_sql_fn$fn__364$fn__365.invoke(files.clj:77)
    at clojure.java.jdbc.deprecated$with_connection_STAR_.invoke(deprecated.clj:307)
    at ragtime.sql.files$run_sql_fn$fn__364.invoke(files.clj:76)
    at clojure.lang.AFn.applyToHelper(AFn.java:154)
    at clojure.lang.AFn.applyTo(AFn.java:144)
    at clojure.core$apply.invoke(core.clj:624)
    at ragtime.main$wrap_println$fn__298.doInvoke(main.clj:19)
    at clojure.lang.RestFn.invoke(RestFn.java:408)
    at ragtime.core$migrate.invoke(core.clj:38)
    at ragtime.core$migrate_all.invoke(core.clj:60)
    at ragtime.core$migrate_all.invoke(core.clj:53)
    at ragtime.main$migrate.invoke(main.clj:32)
    at clojure.lang.AFn.applyToHelper(AFn.java:154)
    at clojure.lang.AFn.applyTo(AFn.java:144)
    at clojure.core$apply.invoke(core.clj:626)
    at ragtime.main$_main.doInvoke(main.clj:67)
    at clojure.lang.RestFn.invoke(RestFn.java:703)
    at clojure.lang.Var.invoke(Var.java:442)
    at user$eval5$fn__7.invoke(form-init3619964908012902520.clj:1)
    at user$eval5.invoke(form-init3619964908012902520.clj:1)
    at clojure.lang.Compiler.eval(Compiler.java:6703)
    at clojure.lang.Compiler.eval(Compiler.java:6693)
    at clojure.lang.Compiler.load(Compiler.java:7130)
    ... 11 more
Error encountered performing task 'ragtime' with profile(s): 'ragtime'
Suppressed exit
@weavejester
Copy link
Owner

Now that's interesting. On 0.3.7 I removed the functionality that splits up large SQL files if the database is PostgreSQL, because unlike MySQL, Postgres can handle multiple updates more robustly. But it looks like there was a limit I was unaware of.

I'll need to consider how to approach this in future. Either by taking an approach like clj-sql-up, where the migration files are written in Clojure, or by sticking with SQL and using an explicit delimiter command between SQL commands.

For now, I'd advise using 0.3.6. If you happen to have any syntax suggestions on this subject, I'd be interested to know.

@tvaughan
Copy link
Author

tvaughan commented May 2, 2014

We already use https://github.com/jkk/honeysql so it would be pretty natural for us to write our migrations in clojure.

@yogthos
Copy link
Contributor

yogthos commented May 7, 2014

Since the approach of splitting up large files works reliably for all databases, wouldn't it be better to simply continue using it with postgres as well. Is performance really that much of an issue here?

@weavejester
Copy link
Owner

The problem is that splitting up large files isn't reliable; it doesn't work with things like stored procedures.

@yogthos
Copy link
Contributor

yogthos commented May 7, 2014

Another option might be to use a custom statement separator such as a comment string the way yesql does.

@iwinux
Copy link

iwinux commented Jun 28, 2014

@weavejester @yogthos

Just ran into this problem. This answer from SO seems to solve / work around it.

@iwinux
Copy link

iwinux commented Jun 28, 2014

BTW: do-commands are deprecated in favor of db-do-commands

@martintrojer martintrojer mentioned this issue Sep 21, 2014
Closed
michaelklishin added a commit to michaelklishin/ragtime that referenced this issue Sep 29, 2014
@michaelklishin
Commit a missing fix for weavejester#34
c22cbd8
@michaelklishin michaelklishin mentioned this issue Oct 3, 2014
Closed
michaelklishin added a commit to michaelklishin/ragtime that referenced this issue Oct 3, 2014
@michaelklishin
Fixes weavejester#34
b769c85 
I've been running this fix as of c22cbd8 and it worked great for us.
@icambron
Copy link

I'm hitting this with just two SQL statements in my migration file, so I'm a bit confused about the "handles more robustly" part. Perhaps I'm missing something?

Assuming that solution from SO works, would you accept a pull request replacing do-commands with do-prepared for the Postgres case?

@michaelklishin
Copy link
Collaborator

@icambron #45 has been open for 40 days or so. Use https://github.com/michaelklishin/ragtime (released to Clojars as clojurewerkz/ragtime "0.4.0") which has a fix.

Joplin has recently switched to our fork because Ragtime isn't getting the attention it deserves.

@icambron
Copy link

@michaelklishin Thanks, I'll give a shot.

@weavejester
Copy link
Owner

Unfortunately Ragtime has been at the bottom of my priority list for a while, as I haven't done anything with SQL databases in over a year. It's definitely not getting the attention it deserves.

@michaelklishin would you be open to collaborating on Ragtime?

@michaelklishin
Copy link
Collaborator

@weavejester definitely. I'd be happy to take it over as I use Joplin quite a bit with both relational and non-relational data stores.

I can port the few fixes I have in our fork and cut a new release if you add me to the group on Clojars.

@martintrojer may be interested in joining, too.

@martintrojer
Copy link
Collaborator

Cool, let us now when ragtime is patched up and I'll move joplin back.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
7 participants
@michaelklishin @weavejester @tvaughan @iwinux @yogthos @icambron @martintrojer