PostgreSQL: Cannot establish a savepoint in auto-commit mode.

[stephanos]

I was about to move from MySQL to PostgreSQL when this happened:

Caused by: org.postgresql.util.PSQLException: Cannot establish a
savepoint in auto-commit mode.
at
org.postgresql.jdbc3.AbstractJdbc3Connection.setSavepoint(AbstractJdbc3Connection.java:
101)
at org.squeryl.internals.DatabaseAdapter
$class.execFailSafeExecute(DatabaseAdapter.scala:346)
at
org.squeryl.adapters.PostgreSqlAdapter.execFailSafeExecute(PostgreSqlAdapter.scala:
24)
at org.squeryl.internals.DatabaseAdapter
$class.dropForeignKeyStatement(DatabaseAdapter.scala:698)
at
org.squeryl.adapters.PostgreSqlAdapter.dropForeignKeyStatement(PostgreSqlAdapter.scala:
24)
at org.squeryl.Schema$$anonfun$_dropForeignKeyConstraints
$1.apply(Schema.scala:226)
at org.squeryl.Schema$$anonfun$_dropForeignKeyConstraints
$1.apply(Schema.scala:224)
at scala.collection.mutable.ResizableArray
$class.foreach(ResizableArray.scala:60)
at scala.collection.mutable.ArrayBuffer.foreach(ArrayBuffer.scala:44)
at org.squeryl.Schema$class._dropForeignKeyConstraints(Schema.scala:
224)
at org.squeryl.Schema$class.drop(Schema.scala:167)

Any ideas how to fix this?
(squeryl v0.9.4, PostgreSQL v9.1 + JDBC v9.0-801.jdbc4)

Cheers!

[Maxime Lévesque]

I'm guessing from the error message that your connection is in auto commit mode,
when you create your jdbc connection, try settin c.setAutoCommit(false)

ML

2011/10/21 stephanos <stephan...@googlemail.com>

[David Whittaker]

Max,

Shouldn't that be happening anyway as long as it's within a transaction/inTransaction block?

2011/10/21 Maxime Lévesque <maxime....@gmail.com>

[Maxime Lévesque]

If my memory is good, auto commit is false by default, but since
the message complains about an action not being possible in auto commit
mode, it's my first guess.

ML

2011/10/21 David Whittaker <da...@iradix.com>

[David Whittaker]

Hmmm.... http://download.oracle.com/javase/7/docs/api/java/sql/Connection.html

"By default a Connection object is in auto-commit mode, which means that it automatically commits changes after executing each statement."

I know that Squeryl rollbacks work properly, so this must be getting turned off somewhere.

2011/10/21 Maxime Lévesque <maxime....@gmail.com>

[stephanos]

If it helps:
- I'm using Java 7
- there are no transactions in my code
- at no point am I manually setting/changing the auto-commit value

Cheers


On Oct 21, 11:16 pm, David Whittaker <d...@iradix.com> wrote:
> Hmmm....http://download.oracle.com/javase/7/docs/api/java/sql/Connection.html

>
> "By default a Connection object is in auto-commit mode, which means that it
> automatically commits changes after executing each statement."
>
> I know that Squeryl rollbacks work properly, so this must be getting turned
> off somewhere.
>

> 2011/10/21 Maxime Lévesque <maxime.leves...@gmail.com>

>
>
>
>
>
>
>
>
>
> > If my memory is good, auto commit is false by default, but since
> > the message complains about an action not being possible in auto commit
> > mode, it's my first guess.
>
> > ML
>

> > 2011/10/21 David Whittaker <d...@iradix.com>

>
> >> Max,
>
> >> Shouldn't that be happening anyway as long as it's within a
> >> transaction/inTransaction block?
>

> >> 2011/10/21 Maxime Lévesque <maxime.leves...@gmail.com>

>
> >>> I'm guessing from the error message that your connection is in auto
> >>> commit mode,
> >>> when you create your jdbc connection, try settin c.setAutoCommit(false)
>
> >>> ML
>

> >>> 2011/10/21 stephanos <stephan.beh...@googlemail.com>

[Maxime Lévesque]

Ok then try this : set autocommit manually to false

2011/10/22 stephanos <stephan...@googlemail.com>

[stephanos]

When I set auto-commit to false all the inserts run through without an
error - but as soon as I query something it tells me that table XY
does not exist. Looking into the schema I see that actually nothing
was created (I used drop/create).

I went through almost all layers with the debugger, but cannot find
any errors - the create statements are all passed through, but never
actually get to the database (I also tried using a brand new user with
no rights, same thing).

So it seems without auto-commit nothing is actually ever committed.

On Oct 22, 2:39 pm, Maxime Lévesque <maxime.leves...@gmail.com> wrote:
> Ok then try this : set autocommit manually to false
>

> 2011/10/22 stephanos <stephan.beh...@googlemail.com>

[Maxime Lévesque]

Can you show us the code that creates the schema ?

It should look like this :

transaction {
   yourSchema.create
}


It seems like somhow a rollback is happening,

it could be because of this :

  http://www.assembla.com/spaces/squeryl/tickets/66-nonlocalreturnexception-can-cause-a-silent-rollback

any exception getting thrown inside the transaction will cause it to rollback.

ML

2011/10/22 stephanos <stephan...@googlemail.com>

[stephanos]

UPDATE:

Well, as I said I don't use any transactions, but I followed your
suggestion and wrapped the create() statement in one: same result.

I don't think there is any rollback: I placed breakpoints at all
places inside Squeryl where the word 'rollback' occurred, and none was
activated (only when the "drop" statements were executed
silenceException() was called - but not for the "create" statements!)

I added a bit of initialization code after create(), and surprisingly
it works! -Adding a user and retrieving it worked.
BUT as soon as a query is issued from a web request, it fails and
says: Relation 'user' does not exist.

So I think somewhere a 'commit()' is missing. What do you think?

On Oct 23, 5:31 am, Maxime Lévesque <maxime.leves...@gmail.com> wrote:
> Can you show us the code that creates the schema ?
>
> It should look like this :
>
> transaction {
>    yourSchema.create
>
> }
>
> It seems like somhow a rollback is happening,
>
> it could be because of this :
>

> http://www.assembla.com/spaces/squeryl/tickets/66-nonlocalreturnexcep...

>
> any exception getting thrown inside the transaction will cause it to
> rollback.
>
> ML
>

> 2011/10/22 stephanos <stephan.beh...@googlemail.com>

[Maxime Lévesque]

This is very puzzling, I've never heard of this before, if you can post
the code somewhere  I'll look at it.

The postgres schema creation code creates savepoints  before
droping schema objects, so that to avoid a rollback ofthe whole transaction
when droping something that doesn't exist. What version are you using ?
perhaps there has been a change concerning savepoints,
i.e. their default behavior.

ML

2011/10/23 stephanos <stephan...@googlemail.com>

[David Whittaker]

Almost more puzzling to me is... how was this even working without a transaction / inTransaction block?  I would expect Squeryl to fail without obtaining a connection since there would be no Session bound to the thread.

2011/10/23 Maxime Lévesque <maxime....@gmail.com>

[stephanos]

Hm, maybe there is something wrong in my code.
I'll try to extract the relevant parts and create a sample project on
GitHub.

On Oct 23, 3:55 pm, David Whittaker <d...@iradix.com> wrote:
> Almost more puzzling to me is... how was this even working without a
> transaction / inTransaction block?  I would expect Squeryl to fail without
> obtaining a connection since there would be no Session bound to the thread.
>

> 2011/10/23 Maxime Lévesque <maxime.leves...@gmail.com>

>
>
>
>
>
>
>
>
>
> > This is very puzzling, I've never heard of this before, if you can post
> > the code somewhere  I'll look at it.
>
> > The postgres schema creation code creates savepoints  before
> > droping schema objects, so that to avoid a rollback ofthe whole transaction
>
> > when droping something that doesn't exist. What version are you using ?
> > perhaps there has been a change concerning savepoints,
> > i.e. their default behavior.
>
> > ML
>

> > 2011/10/23 stephanos <stephan.beh...@googlemail.com>

[stephanos]

I created a sample project: https://github.com/stephanos/squeryl_postgres
(Java 7, Scala 2.9.1, SQueryl v0.9.4, PostgreSQL v9.1 + JDBC
v9.0-801.jdbc4)

Please note that I kept the original structure of my SQL-related code,
but trimmed down the contents to the relevant bits. Also keep in mind
that this code worked for months when using MySQL, only when targeting
PostgreSQL it apparently fails (database empty).

I hope it helps.

[David Whittaker]

Hmm.... It's definitely not a typical Squeryl flow.  You would generally have a single SessionFactory that is initialized when your application starts, and there are very few situations where you need to access Session.currentSession or Session.currentSessionOption yourself.  All Squeryl code is generally wrapped in a transaction or inTransaction block, and aside from starting a transaction those blocks will make sure that a Session is vended from the SessionFactory and bound to the current thread.  Calls to Squeryl methods (create, drop, insert, queries, etc) will all use the Session bound to the current thread.  It may help you to look the documentation over, in particular http://squeryl.org/sessions-and-tx.html should help.

-Dave

[stephanos]

Okay, I will follow your recommendations and adapt my code, but do you
think that this is the reason for the problems?
I mean, like I said, it all worked fine with MySql...

Did you try to run the code?

On Oct 23, 11:11 pm, David Whittaker <d...@iradix.com> wrote:
> Hmm.... It's definitely not a typical Squeryl flow.  You would generally
> have a single SessionFactory that is initialized when your application
> starts, and there are very few situations where you need to access
> Session.currentSession or Session.currentSessionOption yourself.  All
> Squeryl code is generally wrapped in a transaction or inTransaction block,
> and aside from starting a transaction those blocks will make sure that a
> Session is vended from the SessionFactory and bound to the current thread.
>  Calls to Squeryl methods (create, drop, insert, queries, etc) will all use
> the Session bound to the current thread.  It may help you to look the
> documentation over, in particularhttp://squeryl.org/sessions-and-tx.htmlshouldhelp.
>
> -Dave
>
> On Sun, Oct 23, 2011 at 12:07 PM, stephanos

> <stephan.beh...@googlemail.com>wrote:

[stephanos]

I applied your suggestions and it works! :)
Very cool - thanks for your time!

[David Whittaker]

No problem.