Grails

Postgresql requires flush on save or selects performed after an insert/update won't return correct results

Details

  • Type: Bug Bug
  • Status: Closed Closed
  • Priority: Minor Minor
  • Resolution: Fixed
  • Affects Version/s: 1.0.1
  • Fix Version/s: 1.2-M4
  • Component/s: Persistence
  • Labels:
    None
  • Environment:
    Ubuntu 7.04, Grails 1.0.1, Groovy 1.5.4, Java 1.6.0.03, Postgres 8.3

Description

class BootStrap {

def init = { servletContext -> new Book(title:"The Shining").save() println Book.findAll().size() }
def destroy = {
}
}

As far as I'm aware the above should output 1 as hibernate is supposed to persist inserts/updates before executing selects. This is not happening with Postgesql. The insert/update statements are being cached and executed later. I've tested mysql, hsqldb and oracle and none had the issue.

The only way to get it to work is to put a flush in every save. i.e. save(flush:true)

This problem occurs in the BootStrap as well as in the controllers causing the show directly after a create to fail because the row doesn't exist to be selected yet. After an edit, the show will display the old values because the new ones haven't been persisted yet.

Attachments

Issue Links

This issue depends on:
GRAILS-5287 Allow global configuration of GORM settings (cache, identity, nullability etc.) Blocker Closed

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
David Buschman added a comment -

I am seeing the same behavior in 1.0.4,

I have been developing on SqlServer with no problems, but production is PostGreSQL

In my case I only have to do this for the first call to the database, then it "wakes" up and works correctly from then on.

I think this is a GORM initialization issue with the PostgreSQL driver

Show
David Buschman added a comment - I am seeing the same behavior in 1.0.4, I have been developing on SqlServer with no problems, but production is PostGreSQL In my case I only have to do this for the first call to the database, then it "wakes" up and works correctly from then on. I think this is a GORM initialization issue with the PostgreSQL driver
Hide
Permalink
David Buschman added a comment -

more on this problem

when the save() method was called no errors were reported on the object and the NEVER was committed into the database.

PGAdmin III never showed the record so it appears that no errors are returned on the object and the transaction is rolled back (or never exists)

Once the save(flush:true) was invoked on the first record to be inserted, then all was well.

Show
David Buschman added a comment - more on this problem when the save() method was called no errors were reported on the object and the NEVER was committed into the database. PGAdmin III never showed the record so it appears that no errors are returned on the object and the transaction is rolled back (or never exists) Once the save(flush:true) was invoked on the first record to be inserted, then all was well.
Hide
Permalink
Martin Fuchs-Lautensack added a comment -

This is still there with Grails 1.1.1 !

I suggest raising the level because it causes lots of trouble during bootstrap and effectively prevents the usage of the acegi security plugin and other if you need to create default users.

Show
Martin Fuchs-Lautensack added a comment - This is still there with Grails 1.1.1 ! I suggest raising the level because it causes lots of trouble during bootstrap and effectively prevents the usage of the acegi security plugin and other if you need to create default users.
Hide
Permalink
Graeme Rocher added a comment -

It is not really a Grails bug but a Hibernate one so there isn't much we can do about it until the issue is resolved in Hibernate. Making noise on the Hibernate JIRA would probably be more effective.

Show
Graeme Rocher added a comment - It is not really a Grails bug but a Hibernate one so there isn't much we can do about it until the issue is resolved in Hibernate. Making noise on the Hibernate JIRA would probably be more effective.
Hide
Permalink
Martin Fuchs-Lautensack added a comment -

More than willing to report it to Hibernate JIRA.

Just to make sure I fully understand: Is this problem just present during bootstrap? I just switched over to postgresql from mysql. Or will I have to change every single save in my app to flush:true?

Show
Martin Fuchs-Lautensack added a comment - More than willing to report it to Hibernate JIRA. Just to make sure I fully understand: Is this problem just present during bootstrap? I just switched over to postgresql from mysql. Or will I have to change every single save in my app to flush:true?
Hide
Permalink
Graeme Rocher added a comment -

I don't know whether it only happens in bootstrap, if it does only happen in bootstrap then this may be a Grails issue afterall

Show
Graeme Rocher added a comment - I don't know whether it only happens in bootstrap, if it does only happen in bootstrap then this may be a Grails issue afterall
Hide
Permalink
Martin Fuchs-Lautensack added a comment -

From my limited point of view I see the problem only during bootstrap.

My app started again by putting save(flush:true) during bootstrap and userbootstrap.

At runtime all other DB operations seem to work as expected without requiring a change, including adding of associations and criteria queries.

To be more specific, the problem in my case is
Authority.findByAuthority('ROLE_ADMIN').addToPeople(admin)
gave an NPE to admin although a person admin was saved before as well as a Authority ROLE_ADMIN.

The funny thing is, sometimes when the stuff is already in the database it passes, not reproducibly. But when I start with a blank database it fails every time.

Show
Martin Fuchs-Lautensack added a comment - From my limited point of view I see the problem only during bootstrap. My app started again by putting save(flush:true) during bootstrap and userbootstrap. At runtime all other DB operations seem to work as expected without requiring a change, including adding of associations and criteria queries. To be more specific, the problem in my case is Authority.findByAuthority('ROLE_ADMIN').addToPeople(admin) gave an NPE to admin although a person admin was saved before as well as a Authority ROLE_ADMIN. The funny thing is, sometimes when the stuff is already in the database it passes, not reproducibly. But when I start with a blank database it fails every time.
Hide
Permalink
Darren Marsh added a comment -

I see it in controllers and services as well, so it's not just a bootstrap thing.

You need a fast machine and/or a fast db operation to see the problem. That may be why Martin is only seeing it with an empty database. I don't get the issue when I use a database on a slower system or across the network.

Show
Darren Marsh added a comment - I see it in controllers and services as well, so it's not just a bootstrap thing. You need a fast machine and/or a fast db operation to see the problem. That may be why Martin is only seeing it with an empty database. I don't get the issue when I use a database on a slower system or across the network.
Hide
Permalink
Graeme Rocher added a comment -

Hmm and does running your save() methods within the context of a transaction help, not sure what can be done about this at this point

Show
Graeme Rocher added a comment - Hmm and does running your save() methods within the context of a transaction help, not sure what can be done about this at this point
Hide
Permalink
Martin Fuchs-Lautensack added a comment -

In the meantime I also saw problems during runtime.

Just to throw something into the discussion:

I've configured my postgresql to use password authenticated login because it seemed to me the only simple way. Basically my pg_hba.conf is like in this example
http://hartsock.blogspot.com/2008/07/postgresql-and-grails.html

However, in the postgresql manual it says that such config may cause unexpected behaviour with multi threaded applications. Is this something to worry about or maybe a possible cause of problems we should eliminate? And if so, what would be a better way to configure it?

Thanks, Martin

Show
Martin Fuchs-Lautensack added a comment - In the meantime I also saw problems during runtime. Just to throw something into the discussion: I've configured my postgresql to use password authenticated login because it seemed to me the only simple way. Basically my pg_hba.conf is like in this example http://hartsock.blogspot.com/2008/07/postgresql-and-grails.html However, in the postgresql manual it says that such config may cause unexpected behaviour with multi threaded applications. Is this something to worry about or maybe a possible cause of problems we should eliminate? And if so, what would be a better way to configure it? Thanks, Martin
Hide
Permalink
Marc Palmer added a comment -

I'm also seeing this still with grails 1.2-m2 and WeceemApp running against postgres. WTF?

Show
Marc Palmer added a comment - I'm also seeing this still with grails 1.2-m2 and WeceemApp running against postgres. WTF?
Hide
Permalink
Ken T added a comment - - edited

Does someone have a link to the Hibernate JIRA that addresses this issue? Is there a way to tell GORM to always do a flush on save until this issue is resolved? This doesn't seem like a minor issue to me. I may have to stop using PostgreSQL databases for the back end because of this issue. And since there is an obvious way to work around this problem in GORM (make it flush by default based on a configuration setting) I really think the priority of this bug should be moved up.

Show
Ken T added a comment - - edited Does someone have a link to the Hibernate JIRA that addresses this issue? Is there a way to tell GORM to always do a flush on save until this issue is resolved? This doesn't seem like a minor issue to me. I may have to stop using PostgreSQL databases for the back end because of this issue. And since there is an obvious way to work around this problem in GORM (make it flush by default based on a configuration setting) I really think the priority of this bug should be moved up.
Hide
Permalink
Juri Kuehn added a comment -

I searched the hibernate jira for similar issues but couldnt find any
You can try to set flush.mode='always' in hibernate section to activate flushing before every query
see:
http://jira.codehaus.org/browse/GRAILS-3515
https://www.hibernate.org/hib_docs/v3/api/org/hibernate/FlushMode.html

Show
Juri Kuehn added a comment - I searched the hibernate jira for similar issues but couldnt find any You can try to set flush.mode='always' in hibernate section to activate flushing before every query see: http://jira.codehaus.org/browse/GRAILS-3515 https://www.hibernate.org/hib_docs/v3/api/org/hibernate/FlushMode.html
Hide
Permalink
Juri Kuehn added a comment -

I set up a hibernate test app, to see if this is a hibernate or a grails problem and could narrow down to the identity generator.

http://docs.jboss.org/hibernate/core/3.3/reference/en/html/objectstate.html#objectstate-flushing states that the session is automatically flushed before query executions. I could not reproduce this behavior. In my tests i had to flush explicitly before every query to get current data, both for postgresql and mysql (but i'm really not a hibernate expert, maybe i did something wrong).

what i did find out: As stated in the hibernate docs about flushing: "An exception is that objects using native ID generation are inserted when they are saved." which is the default for mysql, so objects are persisted instantly and the bug described here does not apply.
For postgres on the other hand, hibernate uses the "increment" generator by default, which needs explicit flush call to persist, so it seems.

Setting the generator of your domains to 'identity' fixes the problem
–
static mapping = { id generator:'identity' }
–

Please comment (i guess its a hibernate issue). I would love to see this issue fixed. Testapps to play around are attached.

Show
Juri Kuehn added a comment - I set up a hibernate test app, to see if this is a hibernate or a grails problem and could narrow down to the identity generator. http://docs.jboss.org/hibernate/core/3.3/reference/en/html/objectstate.html#objectstate-flushing states that the session is automatically flushed before query executions. I could not reproduce this behavior. In my tests i had to flush explicitly before every query to get current data, both for postgresql and mysql (but i'm really not a hibernate expert, maybe i did something wrong). what i did find out: As stated in the hibernate docs about flushing: "An exception is that objects using native ID generation are inserted when they are saved." which is the default for mysql, so objects are persisted instantly and the bug described here does not apply. For postgres on the other hand, hibernate uses the "increment" generator by default, which needs explicit flush call to persist, so it seems. Setting the generator of your domains to 'identity' fixes the problem – static mapping = { id generator:'identity' } – Please comment (i guess its a hibernate issue). I would love to see this issue fixed. Testapps to play around are attached.
Hide
Permalink
Juri Kuehn added a comment -

Testapps to reproduce this issue.

Show
Juri Kuehn added a comment - Testapps to reproduce this issue.
Hide
Permalink
Juri Kuehn added a comment -

I dont know if this is helpful, but here is some more information:
1) Within a transaction the session is flushed in postgresql as expected.

// without transaction
new Book(title: "THE ASSOCIATE").save()
new Book(title: "CROSS COUNTRY").save()
new Book(title: "COVET").save()
new Book(title: "HEAT LIGHTNING").save()

println Book.count() // prints 0, session not flushed automatically
// with transaction
Book.withTransaction {
  new Book(title: "THE ASSOCIATE").save()
  new Book(title: "CROSS COUNTRY").save()
  new Book(title: "COVET").save()
  new Book(title: "HEAT LIGHTNING").save()

  println Book.count() // prints 4, session is flushed correctly within transaction before making queries
}

2) When entering controller actions, the session is not active.

println sessionFactory.currentSession.transaction.active // prints false

With HSQL and MySQL session is flushed without the need for the transaction to be active, so the first code snippet would print 4. With Postgresql an excplicit flush is needed. If i got this right, this is not default behavior of hibernate: https://forum.hibernate.org/viewtopic.php?f=1&t=1000395 Hibernate does no autoflush if no transaction is active.

So maybe this is some transaction issue in Grails or Spring in conjunction with Postgresql?
Don't have any more ideas, maybe someone more experienced can look into this.

Show
Juri Kuehn added a comment - I dont know if this is helpful, but here is some more information: 1) Within a transaction the session is flushed in postgresql as expected. 
// without transaction
new Book(title: "THE ASSOCIATE").save()
new Book(title: "CROSS COUNTRY").save()
new Book(title: "COVET").save()
new Book(title: "HEAT LIGHTNING").save()

println Book.count() // prints 0, session not flushed automatically
// with transaction
Book.withTransaction {
  new Book(title: "THE ASSOCIATE").save()
  new Book(title: "CROSS COUNTRY").save()
  new Book(title: "COVET").save()
  new Book(title: "HEAT LIGHTNING").save()

  println Book.count() // prints 4, session is flushed correctly within transaction before making queries
}
2) When entering controller actions, the session is not active. 
println sessionFactory.currentSession.transaction.active // prints false
With HSQL and MySQL session is flushed without the need for the transaction to be active, so the first code snippet would print 4. With Postgresql an excplicit flush is needed. If i got this right, this is not default behavior of hibernate: https://forum.hibernate.org/viewtopic.php?f=1&t=1000395 Hibernate does no autoflush if no transaction is active. So maybe this is some transaction issue in Grails or Spring in conjunction with Postgresql? Don't have any more ideas, maybe someone more experienced can look into this.
Hide
Permalink
Graeme Rocher added a comment -

So running the code within a transaction sounds like the best way forward if you are using postgres, which if you are saving data to the database is the best way forward anyway.

@Ken T As Juri said you can tell Grails to flush every call with a configuration setting and you could even do it with meta-programming:

Book.metaClass.save = {-> save(flush:true} }
Show
Graeme Rocher added a comment - So running the code within a transaction sounds like the best way forward if you are using postgres, which if you are saving data to the database is the best way forward anyway. @Ken T As Juri said you can tell Grails to flush every call with a configuration setting and you could even do it with meta-programming: 
Book.metaClass.save = {-> save(flush:true} }
Hide
Permalink
Graeme Rocher added a comment -

@Juri

This is not a Grails or Spring thing, its just that MySQL and HSQL both use 'identity' generation strategies as you mentioned. So if there is no declared transaction then the underlying connection is set to 'auto-commit'. Since the way 'identity' generation works requires an row to be inserted into the table the autocommit inserts an actual record into the database table.

With Postgres and the 'increment' generation strategy (and also Oracle which uses 'sequence' by default) no actual insert is required to assigned an identifier so what happens is hibernate doesn't do any inserts hence no rows are inserted. This results in the query returning inconsistent data. With a declared transaction on the other hand you have the database ensuring consistency across writes and reads.

Show
Graeme Rocher added a comment - @Juri This is not a Grails or Spring thing, its just that MySQL and HSQL both use 'identity' generation strategies as you mentioned. So if there is no declared transaction then the underlying connection is set to 'auto-commit'. Since the way 'identity' generation works requires an row to be inserted into the table the autocommit inserts an actual record into the database table. With Postgres and the 'increment' generation strategy (and also Oracle which uses 'sequence' by default) no actual insert is required to assigned an identifier so what happens is hibernate doesn't do any inserts hence no rows are inserted. This results in the query returning inconsistent data. With a declared transaction on the other hand you have the database ensuring consistency across writes and reads.
Hide
Permalink
Martin Fuchs-Lautensack added a comment - - edited

Graeme,
I vaguely recall having seen a jira request to make identity strategy the default for all DBs including postgresql. If this could be achieved, wouldn't it provide a way to solve both issues at the same time?
Out of the box I'd prefer to have the ID generation the same independently of the selected database. I don't know of a global way to configure the id generator and would need to put the declaration in every class. Is there a better way?
Thanks, Martin

p.s.
of course its not a fix but sounds to me like it would be easier to achieve than putting transactions everywhere. how about plugins? how about scaffolded code? etc.

pps.
here is the request I meant:
http://jira.codehaus.org/browse/GRAILS-2615

also there was a jira about not being able to influence the id of an object instance/not being editable unless the id generator is explicitly defined

Show
Martin Fuchs-Lautensack added a comment - - edited Graeme, I vaguely recall having seen a jira request to make identity strategy the default for all DBs including postgresql. If this could be achieved, wouldn't it provide a way to solve both issues at the same time? Out of the box I'd prefer to have the ID generation the same independently of the selected database. I don't know of a global way to configure the id generator and would need to put the declaration in every class. Is there a better way? Thanks, Martin p.s. of course its not a fix but sounds to me like it would be easier to achieve than putting transactions everywhere. how about plugins? how about scaffolded code? etc. pps. here is the request I meant: http://jira.codehaus.org/browse/GRAILS-2615 also there was a jira about not being able to influence the id of an object instance/not being editable unless the id generator is explicitly defined
Hide
Permalink
Graeme Rocher added a comment -

I plan to add an option to globally configure many GORM settings for 1.2 RC1 so yes I can make this globally configurable

Show
Graeme Rocher added a comment - I plan to add an option to globally configure many GORM settings for 1.2 RC1 so yes I can make this globally configurable
Hide
Permalink
Martin Fuchs-Lautensack added a comment -

sounds cool, thank you!

here is the other one with the id being ignored on save when id set to assigned. sounds like it could also be fixed by such a global settings policy

http://jira.codehaus.org/browse/GRAILS-1984

Show
Martin Fuchs-Lautensack added a comment - sounds cool, thank you! here is the other one with the id being ignored on save when id set to assigned. sounds like it could also be fixed by such a global settings policy http://jira.codehaus.org/browse/GRAILS-1984
Hide
Permalink
Juri Kuehn added a comment -

great! thanks for clarification!

this issue can be closed then? (i guess, as there is nothing to fix)

To summarize: Hibernate flushes automatically before executing queries, if a transaction for current session is active. For some databases like MySQL and HSQL is 'identity' the default id generator and causes all .save() calls to persist directly to get the id for the new entity. For other databases, like PostgreSQL (and Oracle?), sequences are used for id generation, so hibernate can obtain the id and batch up db writes until session is flushed.

Options to handle this issue:

  • use transactions (Domain.withTransaction {...)
  • flush manually where needed with .save(flush:true)
  • enable instant flushing on some domain objects (e.g. in Bootstrap.groovy): Domain.metaClass.save = {-> save(flush:true} }
  • set your id generator to identity, to get same behaviour as with mysql and hsql: static mapping = { id generator:'identity' }. upcoming grails release will provide an application wide setting for this

Greetings,
Juri

Show
Juri Kuehn added a comment - great! thanks for clarification! this issue can be closed then? (i guess, as there is nothing to fix) To summarize: Hibernate flushes automatically before executing queries, if a transaction for current session is active. For some databases like MySQL and HSQL is 'identity' the default id generator and causes all .save() calls to persist directly to get the id for the new entity. For other databases, like PostgreSQL (and Oracle?), sequences are used for id generation, so hibernate can obtain the id and batch up db writes until session is flushed. Options to handle this issue:
  • use transactions (Domain.withTransaction {...)
  • flush manually where needed with .save(flush:true)
  • enable instant flushing on some domain objects (e.g. in Bootstrap.groovy): Domain.metaClass.save = {-> save(flush:true} }
  • set your id generator to identity, to get same behaviour as with mysql and hsql: static mapping = { id generator:'identity' }. upcoming grails release will provide an application wide setting for this
Greetings, Juri
Hide
Permalink
Graeme Rocher added a comment -

identity generation strategy now globally configurable. Fixed by GRAILS-5287

Show
Graeme Rocher added a comment - identity generation strategy now globally configurable. Fixed by GRAILS-5287

People

Vote (6)
Watch (5)

Dates

  • Created:
    Updated:
    Resolved:
Atlassian JIRA (v4.4.1#660-r161644) | Report a problem
Powered by a free Atlassian JIRA open source license for Grails project. Try JIRA - bug tracking software for your team.