Re: Hot Standby (v9d)

On Wed, 2009-01-28 at 22:19 +0200, Heikki Linnakangas wrote:
> Tom Lane wrote:
...
> > Well, those unexpectedly cancelled queries could have represented
> > critical functionality too. I think this argument calls the entire
> > approach into question. If there is no safe setting for the parameter
> > then we need to find a way to not have the parameter.
>
> We've gone through that already. Different ideas were hashed out around
> September. There's four basic feasible approaches to what to do when an
> incoming WAL record conflicts with a running read-only query:
>
> 1. Kill the query. (max_standby_delay=0)
> 2. Wait for the query to finish before continuing (max_standby_delay=-1)
> 3. Have a feedback loop from standby to master, feeding an OldestXmin to
> the master, preventing it from removing tuples that are still needed in
> the standby.
> 4. Allow the query to continue, knowing that it will return wrong results.
>
> I don't consider 4 to be an option. Option 3 has its own set of
> drawbacks, as a standby can then cause bloat in the master, and in any
> case we're not going to have it in this release. And then there's some
> middle ground, like wait a while and then kill the query
> (max_standby_delay > 0).
>
> I don't see any way around the fact that when a tuple is removed, it's
> gone and can't be accessed by queries. Either you don't remove it, or
> you kill the query.

Actually we came up with a solution to this - use filesystem level
snapshots (like LVM2+XFS or ZFS), and redirect backends with
long-running queries to use fs snapshot mounted to a different
mountpoint.

I don't think Simon has yet put full support for it in code, but it is
clearly _the_ solution for those who want to eat the cake and have it
too.

>
> I think the max_standby_delay setting is fairly easy to explain. It
> shouldn't be too hard for a DBA to set it correctly.
>
> --
> Heikki Linnakangas
> EnterpriseDB http://www.enterprisedb.com
>