Parallel query fails on standby server
Hi All,
While testing a parallel scan feature on standby server, it is found that
the parallel query fails with an error "*ERROR: failed to initialize
transaction_read_only to 0*".
Following are the steps used to reproduce the issue:
*Master :-*
edb=# create table ert(n int);
edb=# insert into ert values (generate_series(1,5000000));
edb=# analyze ert;
edb=# vacuum ert;
*Slave :-*
edb=# set max_parallel_degree =5;
SET
edb=# explain analyze verbose select * from ert where n<=1000;
ERROR: failed to initialize transaction_read_only to 0
CONTEXT: parallel worker, PID 26042
*Root cause Analysis:* After debugging the worker, it is observed that in
*RestoreGUCState()*, if a guc var can't be skipped it is Initialiazed with
a default value and
in this process when a guc variable "*transaction_read_only*" is being
Initialzed it calls a check_hook *check_transaction_read_only()* which
eventually fails due to
below check which says the guc var "*transaction_read_only*" can't be set
while recovery is in progress:
*if
(RecoveryInProgress()){GUC_check_errcode(ERRCODE_FEATURE_NOT_SUPPORTED);GUC_check_errmsg("cannot
set transaction read-write mode during recovery");return false;}*
*Solution:* Make use of a global variable "*InitializingParallelWorker"* to
protect the check for *RecoveryInProgress()* when Parallel Worker is being
Initialsed.
PFA patch to fix the issue.
With Regards,
Ashutosh Sharma
EnterpriseDB: *http://www.enterprisedb.com <http://www.enterprisedb.com>*
Attachments:
fix-for-parallel-query-on-standby.patchtext/x-patch; charset=US-ASCII; name=fix-for-parallel-query-on-standby.patchDownload
diff --git a/src/backend/commands/variable.c b/src/backend/commands/variable.c
index 903b3a6..c7173c9 100644
--- a/src/backend/commands/variable.c
+++ b/src/backend/commands/variable.c
@@ -482,11 +482,13 @@ show_log_timezone(void)
* nothing since XactReadOnly will be reset by the next StartTransaction().
* The IsTransactionState() test protects us against trying to check
* RecoveryInProgress() in contexts where shared memory is not accessible.
+ * We can also skip the check for RecoveryInProgress while initializing the
+ * Parallel Workers by making use of the global variable InitializingParallelWorker.
*/
bool
check_transaction_read_only(bool *newval, void **extra, GucSource source)
{
- if (*newval == false && XactReadOnly && IsTransactionState())
+ if (*newval == false && XactReadOnly && IsTransactionState() && !InitializingParallelWorker)
{
/* Can't go to r/w mode inside a r/o transaction */
if (IsSubTransaction())
On 8 March 2016 at 20:30, Ashutosh Sharma <ashu.coek88@gmail.com> wrote:
While testing a parallel scan feature on standby server, it is found that
the parallel query fails with an error "*ERROR: failed to initialize
transaction_read_only to 0*".
Looks like it might be a good idea to add some tests to src/test/recovery
for parallel query on standby servers...
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
On Tue, Mar 8, 2016 at 9:51 PM, Craig Ringer <craig@2ndquadrant.com> wrote:
On 8 March 2016 at 20:30, Ashutosh Sharma <ashu.coek88@gmail.com> wrote:
While testing a parallel scan feature on standby server, it is found that
the parallel query fails with an error "ERROR: failed to initialize
transaction_read_only to 0".Looks like it might be a good idea to add some tests to src/test/recovery
for parallel query on standby servers...
An even better thing would be a set of read-only tests based on the
database "regression" generated by make check, itself run with
pg_regress.
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Mar 8, 2016 at 8:23 AM, Michael Paquier
<michael.paquier@gmail.com> wrote:
On Tue, Mar 8, 2016 at 9:51 PM, Craig Ringer <craig@2ndquadrant.com> wrote:
On 8 March 2016 at 20:30, Ashutosh Sharma <ashu.coek88@gmail.com> wrote:
While testing a parallel scan feature on standby server, it is found that
the parallel query fails with an error "ERROR: failed to initialize
transaction_read_only to 0".Looks like it might be a good idea to add some tests to src/test/recovery
for parallel query on standby servers...An even better thing would be a set of read-only tests based on the
database "regression" generated by make check, itself run with
pg_regress.
I'm not sure anything in the main regression suite actually goes
parallel right now, which is probably the first thing to fix.
Unless, of course, you use force_parallel_mode=regress, max_parallel_degree>0.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Mar 9, 2016 at 12:34 AM, Robert Haas <robertmhaas@gmail.com> wrote:
On Tue, Mar 8, 2016 at 8:23 AM, Michael Paquier
<michael.paquier@gmail.com> wrote:On Tue, Mar 8, 2016 at 9:51 PM, Craig Ringer <craig@2ndquadrant.com> wrote:
On 8 March 2016 at 20:30, Ashutosh Sharma <ashu.coek88@gmail.com> wrote:
While testing a parallel scan feature on standby server, it is found that
the parallel query fails with an error "ERROR: failed to initialize
transaction_read_only to 0".Looks like it might be a good idea to add some tests to src/test/recovery
for parallel query on standby servers...An even better thing would be a set of read-only tests based on the
database "regression" generated by make check, itself run with
pg_regress.I'm not sure anything in the main regression suite actually goes
parallel right now, which is probably the first thing to fix.Unless, of course, you use force_parallel_mode=regress, max_parallel_degree>0.
I was thinking about a test in src/test/recovery, that runs a standby
and a master. pg_regress with the main recovery test suite is run on
the master, then a second pg_regress run happens with a set of
read-only queries, set with sql/expected located in src/test/recovery
directly for example. Do we actually have a buildfarm animal using
those parameters in extra_config?
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers