ERROR: canceling statement due to conflict with recovery – When creating a view
17 March, 2011
I am trying to create a view in YF and getting the following error message. Note: I know 100% that the SQL statement is valid, as I have used it in the same view for another client org.
SQL statement is invalid.
The error message returned from the database was:
ERROR: canceling statement due to conflict with recovery
Detail: User query might have needed to see row versions that must be removed.
After looking through the logs you emailed across.
We have noticed a few of these errors in the logs occurring at different times.
The error message is not something we're familiar with, but it seems to have something to do with PostgreSQL hot standby servers and replication:
http://www.postgresql.org/docs/9.0/static/hot-standby.html#HOT-STANDBY-CONFLICT
Are your queries running against a standby server?
Yes, they are running against a server which receives streaming updates from our main production database, so we can report certain data changes as they occur.
We are looking at the Postgres documentation to review our options.
The current situation since we deployed into production prevents us from performing maintenance on any highly used views except during very quiet periods. These potential maintenance windows will become shorter as we deploy to clients across a wider range of timezones.
Do you have any clients with similar requirements?
Are there any configuration settings within YF that can be used to reduce the impact of this issue, or is it purely a database issue for us to resolve?
Any guidance from your end would be appreciated.
I'm not aware of any clients using this sort of server replication on Postgres.
One thing you could try is changing the transaction isolation setting in Yellowfin - I'm not 100% sure this will have an affect but it's worth trying.
At the moment there is a single setting that applies to all data sources, although this will soon become a per-data source setting. To change this setting see this forum post:
http://www.yellowfinbi.com/YFForum-SQL-Error-Record-Is-Locked-?thread=95562#p1
Try setting the value to READ_UNCOMMITTED or NONE. You will need to restart Yellowfin after making this change.
One thing you could try is changing the transaction isolation setting in Yellowfin - I'm not 100% sure this will have an affect but it's worth trying.
At the moment there is a single setting that applies to all data sources, although this will soon become a per-data source setting. To change this setting see this forum post:
http://www.yellowfinbi.com/YFForum-SQL-Error-Record-Is-Locked-?thread=95562#p1
Try setting the value to READ_UNCOMMITTED or NONE. You will need to restart Yellowfin after making this change.