postgres-xc-general

[Postgres-xc-general] Problem with taking database dump with pgxc-1.2.1

[Juned K. <jkh...@gm...>]

Hi All,

recently i have just installed the pgxc-1.2.1 on one of my production server
but when i tried to take backup of all data it gave me below errors.

PGXC pg_dump mydatabase > mydatabase.sql
pg_dump: [archiver (db)] query failed: ERROR:  cannot execute nextval() in
a read-only transaction
pg_dump: [archiver (db)] query was: SELECT
pg_catalog.nextval('account_package_id_seq');

With some googling i found this
http://www.postgresql.org/message-id/4DC...@po...

How to solve this issue? even pg_dumpall is working

Please suggest.

-- 

Thanks,
Juned Khan
iNextrix Technologies Pvt Ltd.
www.inextrix.com

Re: [Postgres-xc-general] Problem with taking database dump with pgxc-1.2.1

[鈴木 幸市 <ko...@in...>]

Please try pg_dumpall if it works.

Regards;
---
Koichi Suzuki

2014/04/09 17:31、Juned Khan <jkh...@gm...<mailto:jkh...@gm...>> のメール：

Hi All,

recently i have just installed the pgxc-1.2.1 on one of my production server
but when i tried to take backup of all data it gave me below errors.

PGXC pg_dump mydatabase > mydatabase.sql
pg_dump: [archiver (db)] query failed: ERROR:  cannot execute nextval() in a read-only transaction
pg_dump: [archiver (db)] query was: SELECT pg_catalog.nextval('account_package_id_seq');

With some googling i found this
http://www.postgresql.org/message-id/4DC...@po...

How to solve this issue? even pg_dumpall is working

Please suggest.


--

Thanks,
Juned Khan
iNextrix Technologies Pvt Ltd.
www.inextrix.com<http://www.inextrix.com/>
------------------------------------------------------------------------------
Put Bad Developers to Shame
Dominate Development with Jenkins Continuous Integration
Continuously Automate Build, Test & Deployment
Start a new project now. Try Jenkins in the cloud.
http://p.sf.net/sfu/13600_Cloudbees_______________________________________________
Postgres-xc-general mailing list
Pos...@li...
https://lists.sourceforge.net/lists/listinfo/postgres-xc-general

Re: [Postgres-xc-general] Problem with taking database dump with pgxc-1.2.1

[Masataka S. <pg...@gm...>]

I can't believe pg_dumpall is working.

pg_dumpall uses pg_dump internally to dump each database. pg_dump uses
nextval() to obtain sequce values in a read only transaction. It's
invalid operation because nextval() changes a sequce value regardless
of being read-only transaction.

Regards.

On 9 April 2014 17:43, 鈴木 幸市 <ko...@in...> wrote:
> Please try pg_dumpall if it works.
>
> Regards;
> ---
> Koichi Suzuki
>
> 2014/04/09 17:31、Juned Khan <jkh...@gm...> のメール：
>
> Hi All,
>
> recently i have just installed the pgxc-1.2.1 on one of my production server
> but when i tried to take backup of all data it gave me below errors.
>
> PGXC pg_dump mydatabase > mydatabase.sql
> pg_dump: [archiver (db)] query failed: ERROR:  cannot execute nextval() in a
> read-only transaction
> pg_dump: [archiver (db)] query was: SELECT
> pg_catalog.nextval('account_package_id_seq');
>
> With some googling i found this
> http://www.postgresql.org/message-id/4DC...@po...
>
> How to solve this issue? even pg_dumpall is working
>
> Please suggest.
>
> --
>
> Thanks,
> Juned Khan
> iNextrix Technologies Pvt Ltd.
> www.inextrix.com
> ------------------------------------------------------------------------------
> Put Bad Developers to Shame
> Dominate Development with Jenkins Continuous Integration
> Continuously Automate Build, Test & Deployment
> Start a new project now. Try Jenkins in the cloud.
> http://p.sf.net/sfu/13600_Cloudbees_______________________________________________
> Postgres-xc-general mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
>
>
>
> ------------------------------------------------------------------------------
> Put Bad Developers to Shame
> Dominate Development with Jenkins Continuous Integration
> Continuously Automate Build, Test & Deployment
> Start a new project now. Try Jenkins in the cloud.
> http://p.sf.net/sfu/13600_Cloudbees
> _______________________________________________
> Postgres-xc-general mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
>

Re: [Postgres-xc-general] Problem with taking database dump with pgxc-1.2.1

[Juned K. <jkh...@gm...>]

Hi Masataka,

Yeah you are correct, even pg_dumpall is not working.
do you have any idea about how to solve this issue or any
alternative way to take dump ?


Regards,


On Thu, Apr 10, 2014 at 12:26 PM, Masataka Saito <pg...@gm...> wrote:

> I can't believe pg_dumpall is working.
>
> pg_dumpall uses pg_dump internally to dump each database. pg_dump uses
> nextval() to obtain sequce values in a read only transaction. It's
> invalid operation because nextval() changes a sequce value regardless
> of being read-only transaction.
>
> Regards.
>
> On 9 April 2014 17:43, 鈴木 幸市 <ko...@in...> wrote:
> > Please try pg_dumpall if it works.
> >
> > Regards;
> > ---
> > Koichi Suzuki
> >
> > 2014/04/09 17:31、Juned Khan <jkh...@gm...> のメール：
> >
> > Hi All,
> >
> > recently i have just installed the pgxc-1.2.1 on one of my production
> server
> > but when i tried to take backup of all data it gave me below errors.
> >
> > PGXC pg_dump mydatabase > mydatabase.sql
> > pg_dump: [archiver (db)] query failed: ERROR:  cannot execute nextval()
> in a
> > read-only transaction
> > pg_dump: [archiver (db)] query was: SELECT
> > pg_catalog.nextval('account_package_id_seq');
> >
> > With some googling i found this
> >
> http://www.postgresql.org/message-id/4DC...@po...
> >
> > How to solve this issue? even pg_dumpall is working
> >
> > Please suggest.
> >
> > --
> >
> > Thanks,
> > Juned Khan
> > iNextrix Technologies Pvt Ltd.
> > www.inextrix.com
> >
> ------------------------------------------------------------------------------
> > Put Bad Developers to Shame
> > Dominate Development with Jenkins Continuous Integration
> > Continuously Automate Build, Test & Deployment
> > Start a new project now. Try Jenkins in the cloud.
> >
> http://p.sf.net/sfu/13600_Cloudbees_______________________________________________
> > Postgres-xc-general mailing list
> > Pos...@li...
> > https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
> >
> >
> >
> >
> ------------------------------------------------------------------------------
> > Put Bad Developers to Shame
> > Dominate Development with Jenkins Continuous Integration
> > Continuously Automate Build, Test & Deployment
> > Start a new project now. Try Jenkins in the cloud.
> > http://p.sf.net/sfu/13600_Cloudbees
> > _______________________________________________
> > Postgres-xc-general mailing list
> > Pos...@li...
> > https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
> >
>



-- 
Thanks,
Juned Khan
iNextrix Technologies Pvt Ltd.
www.inextrix.com

Re: [Postgres-xc-general] Problem with taking database dump with pgxc-1.2.1

[Koichi S. <koi...@gm...>]

The fix will be okay for the short term basis.   At present, sequence
is handled by GTM and we need to invoke nextval() to get its value,
which advances current sequence value, which may not be comfortable
for one usecase.   This is the main reason why XC's pg_dump and
pg_dumpall do not work in read-only transaction.

We need this fix after this.

Regards;
---
Koichi Suzuki


2014-04-10 15:59 GMT+09:00 Juned Khan <jkh...@gm...>:
> Hi Masataka,
>
> Yeah you are correct, even pg_dumpall is not working.
> do you have any idea about how to solve this issue or any
> alternative way to take dump ?
>
>
> Regards,
>
>
> On Thu, Apr 10, 2014 at 12:26 PM, Masataka Saito <pg...@gm...> wrote:
>>
>> I can't believe pg_dumpall is working.
>>
>> pg_dumpall uses pg_dump internally to dump each database. pg_dump uses
>> nextval() to obtain sequce values in a read only transaction. It's
>> invalid operation because nextval() changes a sequce value regardless
>> of being read-only transaction.
>>
>> Regards.
>>
>> On 9 April 2014 17:43, 鈴木 幸市 <ko...@in...> wrote:
>> > Please try pg_dumpall if it works.
>> >
>> > Regards;
>> > ---
>> > Koichi Suzuki
>> >
>> > 2014/04/09 17:31、Juned Khan <jkh...@gm...> のメール：
>> >
>> > Hi All,
>> >
>> > recently i have just installed the pgxc-1.2.1 on one of my production
>> > server
>> > but when i tried to take backup of all data it gave me below errors.
>> >
>> > PGXC pg_dump mydatabase > mydatabase.sql
>> > pg_dump: [archiver (db)] query failed: ERROR:  cannot execute nextval()
>> > in a
>> > read-only transaction
>> > pg_dump: [archiver (db)] query was: SELECT
>> > pg_catalog.nextval('account_package_id_seq');
>> >
>> > With some googling i found this
>> >
>> > http://www.postgresql.org/message-id/4DC...@po...
>> >
>> > How to solve this issue? even pg_dumpall is working
>> >
>> > Please suggest.
>> >
>> > --
>> >
>> > Thanks,
>> > Juned Khan
>> > iNextrix Technologies Pvt Ltd.
>> > www.inextrix.com
>> >
>> > ------------------------------------------------------------------------------
>> > Put Bad Developers to Shame
>> > Dominate Development with Jenkins Continuous Integration
>> > Continuously Automate Build, Test & Deployment
>> > Start a new project now. Try Jenkins in the cloud.
>> >
>> > http://p.sf.net/sfu/13600_Cloudbees_______________________________________________
>> > Postgres-xc-general mailing list
>> > Pos...@li...
>> > https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
>> >
>> >
>> >
>> >
>> > ------------------------------------------------------------------------------
>> > Put Bad Developers to Shame
>> > Dominate Development with Jenkins Continuous Integration
>> > Continuously Automate Build, Test & Deployment
>> > Start a new project now. Try Jenkins in the cloud.
>> > http://p.sf.net/sfu/13600_Cloudbees
>> > _______________________________________________
>> > Postgres-xc-general mailing list
>> > Pos...@li...
>> > https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
>> >
>
>
>
>
> --
> Thanks,
> Juned Khan
> iNextrix Technologies Pvt Ltd.
> www.inextrix.com
>
> ------------------------------------------------------------------------------
> Put Bad Developers to Shame
> Dominate Development with Jenkins Continuous Integration
> Continuously Automate Build, Test & Deployment
> Start a new project now. Try Jenkins in the cloud.
> http://p.sf.net/sfu/13600_Cloudbees
> _______________________________________________
> Postgres-xc-general mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
>

Re: [Postgres-xc-general] Problem with taking database dump with pgxc-1.2.1

[Juned K. <jkh...@gm...>]

As of now this fix worked fine for me. is there any side effect of this
patch ?

And yeah please let me know when this issue gets fixed in main release
so i can apply patch accordingly.

Regards



On Fri, Apr 11, 2014 at 11:49 AM, Koichi Suzuki <koi...@gm...>wrote:

> The fix will be okay for the short term basis.   At present, sequence
> is handled by GTM and we need to invoke nextval() to get its value,
> which advances current sequence value, which may not be comfortable
> for one usecase.   This is the main reason why XC's pg_dump and
> pg_dumpall do not work in read-only transaction.
>
> We need this fix after this.
>
> Regards;
> ---
> Koichi Suzuki
>
>
> 2014-04-10 15:59 GMT+09:00 Juned Khan <jkh...@gm...>:
> > Hi Masataka,
> >
> > Yeah you are correct, even pg_dumpall is not working.
> > do you have any idea about how to solve this issue or any
> > alternative way to take dump ?
> >
> >
> > Regards,
> >
> >
> > On Thu, Apr 10, 2014 at 12:26 PM, Masataka Saito <pg...@gm...>
> wrote:
> >>
> >> I can't believe pg_dumpall is working.
> >>
> >> pg_dumpall uses pg_dump internally to dump each database. pg_dump uses
> >> nextval() to obtain sequce values in a read only transaction. It's
> >> invalid operation because nextval() changes a sequce value regardless
> >> of being read-only transaction.
> >>
> >> Regards.
> >>
> >> On 9 April 2014 17:43, 鈴木 幸市 <ko...@in...> wrote:
> >> > Please try pg_dumpall if it works.
> >> >
> >> > Regards;
> >> > ---
> >> > Koichi Suzuki
> >> >
> >> > 2014/04/09 17:31、Juned Khan <jkh...@gm...> のメール：
> >> >
> >> > Hi All,
> >> >
> >> > recently i have just installed the pgxc-1.2.1 on one of my production
> >> > server
> >> > but when i tried to take backup of all data it gave me below errors.
> >> >
> >> > PGXC pg_dump mydatabase > mydatabase.sql
> >> > pg_dump: [archiver (db)] query failed: ERROR:  cannot execute
> nextval()
> >> > in a
> >> > read-only transaction
> >> > pg_dump: [archiver (db)] query was: SELECT
> >> > pg_catalog.nextval('account_package_id_seq');
> >> >
> >> > With some googling i found this
> >> >
> >> >
> http://www.postgresql.org/message-id/4DC...@po...
> >> >
> >> > How to solve this issue? even pg_dumpall is working
> >> >
> >> > Please suggest.
> >> >
> >> > --
> >> >
> >> > Thanks,
> >> > Juned Khan
> >> > iNextrix Technologies Pvt Ltd.
> >> > www.inextrix.com
> >> >
> >> >
> ------------------------------------------------------------------------------
> >> > Put Bad Developers to Shame
> >> > Dominate Development with Jenkins Continuous Integration
> >> > Continuously Automate Build, Test & Deployment
> >> > Start a new project now. Try Jenkins in the cloud.
> >> >
> >> >
> http://p.sf.net/sfu/13600_Cloudbees_______________________________________________
> >> > Postgres-xc-general mailing list
> >> > Pos...@li...
> >> > https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
> >> >
> >> >
> >> >
> >> >
> >> >
> ------------------------------------------------------------------------------
> >> > Put Bad Developers to Shame
> >> > Dominate Development with Jenkins Continuous Integration
> >> > Continuously Automate Build, Test & Deployment
> >> > Start a new project now. Try Jenkins in the cloud.
> >> > http://p.sf.net/sfu/13600_Cloudbees
> >> > _______________________________________________
> >> > Postgres-xc-general mailing list
> >> > Pos...@li...
> >> > https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
> >> >
> >
> >
> >
> >
> > --
> > Thanks,
> > Juned Khan
> > iNextrix Technologies Pvt Ltd.
> > www.inextrix.com
> >
> >
> ------------------------------------------------------------------------------
> > Put Bad Developers to Shame
> > Dominate Development with Jenkins Continuous Integration
> > Continuously Automate Build, Test & Deployment
> > Start a new project now. Try Jenkins in the cloud.
> > http://p.sf.net/sfu/13600_Cloudbees
> > _______________________________________________
> > Postgres-xc-general mailing list
> > Pos...@li...
> > https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
> >
>



-- 
Thanks,
Juned Khan
iNextrix Technologies Pvt Ltd.
www.inextrix.com

Re: [Postgres-xc-general] Problem with taking database dump with pgxc-1.2.1

[Koichi S. <koi...@gm...>]

The patch restores the working environment back to those of 1.1 (PG
9.2) so I believe there must not be any side effect.   As I wrote,
because XC pg_dump uses nextval() to get sequence value, we have a
side effect that sequence values will be advanced.   This is the sam
as in 1.1.

Regards;
---
Koichi Suzuki


2014-04-11 15:37 GMT+09:00 Juned Khan <jkh...@gm...>:
> As of now this fix worked fine for me. is there any side effect of this
> patch ?
>
> And yeah please let me know when this issue gets fixed in main release
> so i can apply patch accordingly.
>
> Regards
>
>
>
> On Fri, Apr 11, 2014 at 11:49 AM, Koichi Suzuki <koi...@gm...>
> wrote:
>>
>> The fix will be okay for the short term basis.   At present, sequence
>> is handled by GTM and we need to invoke nextval() to get its value,
>> which advances current sequence value, which may not be comfortable
>> for one usecase.   This is the main reason why XC's pg_dump and
>> pg_dumpall do not work in read-only transaction.
>>
>> We need this fix after this.
>>
>> Regards;
>> ---
>> Koichi Suzuki
>>
>>
>> 2014-04-10 15:59 GMT+09:00 Juned Khan <jkh...@gm...>:
>> > Hi Masataka,
>> >
>> > Yeah you are correct, even pg_dumpall is not working.
>> > do you have any idea about how to solve this issue or any
>> > alternative way to take dump ?
>> >
>> >
>> > Regards,
>> >
>> >
>> > On Thu, Apr 10, 2014 at 12:26 PM, Masataka Saito <pg...@gm...>
>> > wrote:
>> >>
>> >> I can't believe pg_dumpall is working.
>> >>
>> >> pg_dumpall uses pg_dump internally to dump each database. pg_dump uses
>> >> nextval() to obtain sequce values in a read only transaction. It's
>> >> invalid operation because nextval() changes a sequce value regardless
>> >> of being read-only transaction.
>> >>
>> >> Regards.
>> >>
>> >> On 9 April 2014 17:43, 鈴木 幸市 <ko...@in...> wrote:
>> >> > Please try pg_dumpall if it works.
>> >> >
>> >> > Regards;
>> >> > ---
>> >> > Koichi Suzuki
>> >> >
>> >> > 2014/04/09 17:31、Juned Khan <jkh...@gm...> のメール：
>> >> >
>> >> > Hi All,
>> >> >
>> >> > recently i have just installed the pgxc-1.2.1 on one of my production
>> >> > server
>> >> > but when i tried to take backup of all data it gave me below errors.
>> >> >
>> >> > PGXC pg_dump mydatabase > mydatabase.sql
>> >> > pg_dump: [archiver (db)] query failed: ERROR:  cannot execute
>> >> > nextval()
>> >> > in a
>> >> > read-only transaction
>> >> > pg_dump: [archiver (db)] query was: SELECT
>> >> > pg_catalog.nextval('account_package_id_seq');
>> >> >
>> >> > With some googling i found this
>> >> >
>> >> >
>> >> > http://www.postgresql.org/message-id/4DC...@po...
>> >> >
>> >> > How to solve this issue? even pg_dumpall is working
>> >> >
>> >> > Please suggest.
>> >> >
>> >> > --
>> >> >
>> >> > Thanks,
>> >> > Juned Khan
>> >> > iNextrix Technologies Pvt Ltd.
>> >> > www.inextrix.com
>> >> >
>> >> >
>> >> > ------------------------------------------------------------------------------
>> >> > Put Bad Developers to Shame
>> >> > Dominate Development with Jenkins Continuous Integration
>> >> > Continuously Automate Build, Test & Deployment
>> >> > Start a new project now. Try Jenkins in the cloud.
>> >> >
>> >> >
>> >> > http://p.sf.net/sfu/13600_Cloudbees_______________________________________________
>> >> > Postgres-xc-general mailing list
>> >> > Pos...@li...
>> >> > https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> > ------------------------------------------------------------------------------
>> >> > Put Bad Developers to Shame
>> >> > Dominate Development with Jenkins Continuous Integration
>> >> > Continuously Automate Build, Test & Deployment
>> >> > Start a new project now. Try Jenkins in the cloud.
>> >> > http://p.sf.net/sfu/13600_Cloudbees
>> >> > _______________________________________________
>> >> > Postgres-xc-general mailing list
>> >> > Pos...@li...
>> >> > https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
>> >> >
>> >
>> >
>> >
>> >
>> > --
>> > Thanks,
>> > Juned Khan
>> > iNextrix Technologies Pvt Ltd.
>> > www.inextrix.com
>> >
>> >
>> > ------------------------------------------------------------------------------
>> > Put Bad Developers to Shame
>> > Dominate Development with Jenkins Continuous Integration
>> > Continuously Automate Build, Test & Deployment
>> > Start a new project now. Try Jenkins in the cloud.
>> > http://p.sf.net/sfu/13600_Cloudbees
>> > _______________________________________________
>> > Postgres-xc-general mailing list
>> > Pos...@li...
>> > https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
>> >
>
>
>
>
> --
> Thanks,
> Juned Khan
> iNextrix Technologies Pvt Ltd.
> www.inextrix.com

Re: [Postgres-xc-general] Problem with taking database dump with pgxc-1.2.1

[Pavan D. <pav...@gm...>]

On Fri, Apr 11, 2014 at 12:07 PM, Juned Khan <jkh...@gm...> wrote:

> As of now this fix worked fine for me. is there any side effect of this
> patch ?
>

IMV there won't be any significant side effect of the patch. pg_dump used
to run in RW mode till we fixed it in 9.3.  If it has worked for so many
years, it may work for some more time too :-) Marking pg_dump READ ONLY was
exactly to catch bugs like these. But other than that, I think its OK.

Thanks,
Pavan

-- 
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee

Re: [Postgres-xc-general] Problem with taking database dump with pgxc-1.2.1

[Juned K. <jkh...@gm...>]

Thanks to Pavan and Koichi for your inputs.

So i think as of now i do not need to worry about
this




On Fri, Apr 11, 2014 at 12:31 PM, Pavan Deolasee
<pav...@gm...>wrote:

> On Fri, Apr 11, 2014 at 12:07 PM, Juned Khan <jkh...@gm...> wrote:
>
>> As of now this fix worked fine for me. is there any side effect of this
>> patch ?
>>
>
> IMV there won't be any significant side effect of the patch. pg_dump used
> to run in RW mode till we fixed it in 9.3.  If it has worked for so many
> years, it may work for some more time too :-) Marking pg_dump READ ONLY was
> exactly to catch bugs like these. But other than that, I think its OK.
>
> Thanks,
> Pavan
>
> --
> Pavan Deolasee
> http://www.linkedin.com/in/pavandeolasee
>



-- 
Thanks,
Juned Khan
iNextrix Technologies Pvt Ltd.
www.inextrix.com

Re: [Postgres-xc-general] Problem with taking database dump with pgxc-1.2.1

[Aaron J. <aja...@re...>]

So, I tried the same thing and applied this patch.  I even verified that it was setting the transaction mode to "serializable, read only, deferrable" - yet the problem persists.  I believe my execution is similar to the OP.

pg_dump -d analysisdb --serializable-deferrable

pg_dump: [archiver (db)] query failed: ERROR:  cannot execute nextval() in a read-only transaction
pg_dump: [archiver (db)] query was: SELECT pg_catalog.nextval('customtype_customtypeid_seq');

Did I miss something else?

Aaron

________________________________
From: Juned Khan [jkh...@gm...]
Sent: Friday, April 11, 2014 2:05 AM
To: Pavan Deolasee
Cc: Postgres-XC mailing list
Subject: Re: [Postgres-xc-general] Problem with taking database dump with pgxc-1.2.1

Thanks to Pavan and Koichi for your inputs.

So i think as of now i do not need to worry about
this




On Fri, Apr 11, 2014 at 12:31 PM, Pavan Deolasee <pav...@gm...<mailto:pav...@gm...>> wrote:
On Fri, Apr 11, 2014 at 12:07 PM, Juned Khan <jkh...@gm...<mailto:jkh...@gm...>> wrote:
As of now this fix worked fine for me. is there any side effect of this
patch ?

IMV there won't be any significant side effect of the patch. pg_dump used to run in RW mode till we fixed it in 9.3.  If it has worked for so many years, it may work for some more time too :-) Marking pg_dump READ ONLY was exactly to catch bugs like these. But other than that, I think its OK.

Thanks,
Pavan

--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee



--
Thanks,
Juned Khan
iNextrix Technologies Pvt Ltd.
www.inextrix.com<http://www.inextrix.com/>

Re: [Postgres-xc-general] Problem with taking database dump with pgxc-1.2.1

[Masataka S. <pg...@gm...>]

On 17 June 2014 10:05, 鈴木 幸市 <ko...@in...> wrote:
> Year, it’s XC bug.   Because of the sequence handling difference from PG,
> transaction should not be read-only.   Please try the attached patch.   It
> will go to 1.2 and master tree if it works.

Your patch looks skipping to set transaction isolation level. Is it OK
to continue with READ COMMITTED transaction?

>
> Hope it helps.
>
> Regards;
> ---
> Koichi Suzuki
>
> 2014/06/17 4:25、Aaron Jackson <aja...@re...> のメール：
>
> So, I tried the same thing and applied this patch.  I even verified that it
> was setting the transaction mode to "serializable, read only, deferrable" -
> yet the problem persists.  I believe my execution is similar to the OP.
>
> pg_dump -d analysisdb --serializable-deferrable
>
> pg_dump: [archiver (db)] query failed: ERROR:  cannot execute nextval() in a
> read-only transaction
> pg_dump: [archiver (db)] query was: SELECT
> pg_catalog.nextval('customtype_customtypeid_seq');
>
> Did I miss something else?
>
> Aaron
>
> ________________________________
> From: Juned Khan [jkh...@gm...]
> Sent: Friday, April 11, 2014 2:05 AM
> To: Pavan Deolasee
> Cc: Postgres-XC mailing list
> Subject: Re: [Postgres-xc-general] Problem with taking database dump with
> pgxc-1.2.1
>
> Thanks to Pavan and Koichi for your inputs.
>
> So i think as of now i do not need to worry about
> this
>
>
>
>
> On Fri, Apr 11, 2014 at 12:31 PM, Pavan Deolasee <pav...@gm...>
> wrote:
>
> On Fri, Apr 11, 2014 at 12:07 PM, Juned Khan <jkh...@gm...> wrote:
>
> As of now this fix worked fine for me. is there any side effect of this
> patch ?
>
>
> IMV there won't be any significant side effect of the patch. pg_dump used to
> run in RW mode till we fixed it in 9.3.  If it has worked for so many years,
> it may work for some more time too :-) Marking pg_dump READ ONLY was exactly
> to catch bugs like these. But other than that, I think its OK.
>
> Thanks,
> Pavan
>
> --
> Pavan Deolasee
> http://www.linkedin.com/in/pavandeolasee
>
>
>
>
> --
> Thanks,
> Juned Khan
> iNextrix Technologies Pvt Ltd.
> www.inextrix.com
> ------------------------------------------------------------------------------
> HPCC Systems Open Source Big Data Platform from LexisNexis Risk Solutions
> Find What Matters Most in Your Big Data with HPCC Systems
> Open Source. Fast. Scalable. Simple. Ideal for Dirty Data.
> Leverages Graph Analysis for Fast Processing & Easy Data Exploration
> http://p.sf.net/sfu/hpccsystems_______________________________________________
>
> Postgres-xc-general mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
>
>
>
> ------------------------------------------------------------------------------
> HPCC Systems Open Source Big Data Platform from LexisNexis Risk Solutions
> Find What Matters Most in Your Big Data with HPCC Systems
> Open Source. Fast. Scalable. Simple. Ideal for Dirty Data.
> Leverages Graph Analysis for Fast Processing & Easy Data Exploration
> http://p.sf.net/sfu/hpccsystems
> _______________________________________________
> Postgres-xc-general mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
>

Re: [Postgres-xc-general] Problem with taking database dump with pgxc-1.2.1

[鈴木 幸市 <ko...@in...>]

This came in 1.2.   pg_dump runs as a single transaction so it looks okay.
---
Koichi Suzuki

2014/06/17 16:31、Masataka Saito <pg...@gm...> のメール：

> On 17 June 2014 10:05, 鈴木 幸市 <ko...@in...> wrote:
>> Year, it’s XC bug.   Because of the sequence handling difference from PG,
>> transaction should not be read-only.   Please try the attached patch.   It
>> will go to 1.2 and master tree if it works.
> 
> Your patch looks skipping to set transaction isolation level. Is it OK
> to continue with READ COMMITTED transaction?
> 
>> 
>> Hope it helps.
>> 
>> Regards;
>> ---
>> Koichi Suzuki
>> 
>> 2014/06/17 4:25、Aaron Jackson <aja...@re...> のメール：
>> 
>> So, I tried the same thing and applied this patch.  I even verified that it
>> was setting the transaction mode to "serializable, read only, deferrable" -
>> yet the problem persists.  I believe my execution is similar to the OP.
>> 
>> pg_dump -d analysisdb --serializable-deferrable
>> 
>> pg_dump: [archiver (db)] query failed: ERROR:  cannot execute nextval() in a
>> read-only transaction
>> pg_dump: [archiver (db)] query was: SELECT
>> pg_catalog.nextval('customtype_customtypeid_seq');
>> 
>> Did I miss something else?
>> 
>> Aaron
>> 
>> ________________________________
>> From: Juned Khan [jkh...@gm...]
>> Sent: Friday, April 11, 2014 2:05 AM
>> To: Pavan Deolasee
>> Cc: Postgres-XC mailing list
>> Subject: Re: [Postgres-xc-general] Problem with taking database dump with
>> pgxc-1.2.1
>> 
>> Thanks to Pavan and Koichi for your inputs.
>> 
>> So i think as of now i do not need to worry about
>> this
>> 
>> 
>> 
>> 
>> On Fri, Apr 11, 2014 at 12:31 PM, Pavan Deolasee <pav...@gm...>
>> wrote:
>> 
>> On Fri, Apr 11, 2014 at 12:07 PM, Juned Khan <jkh...@gm...> wrote:
>> 
>> As of now this fix worked fine for me. is there any side effect of this
>> patch ?
>> 
>> 
>> IMV there won't be any significant side effect of the patch. pg_dump used to
>> run in RW mode till we fixed it in 9.3.  If it has worked for so many years,
>> it may work for some more time too :-) Marking pg_dump READ ONLY was exactly
>> to catch bugs like these. But other than that, I think its OK.
>> 
>> Thanks,
>> Pavan
>> 
>> --
>> Pavan Deolasee
>> http://www.linkedin.com/in/pavandeolasee
>> 
>> 
>> 
>> 
>> --
>> Thanks,
>> Juned Khan
>> iNextrix Technologies Pvt Ltd.
>> www.inextrix.com
>> ------------------------------------------------------------------------------
>> HPCC Systems Open Source Big Data Platform from LexisNexis Risk Solutions
>> Find What Matters Most in Your Big Data with HPCC Systems
>> Open Source. Fast. Scalable. Simple. Ideal for Dirty Data.
>> Leverages Graph Analysis for Fast Processing & Easy Data Exploration
>> http://p.sf.net/sfu/hpccsystems_______________________________________________
>> 
>> Postgres-xc-general mailing list
>> Pos...@li...
>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
>> 
>> 
>> 
>> ------------------------------------------------------------------------------
>> HPCC Systems Open Source Big Data Platform from LexisNexis Risk Solutions
>> Find What Matters Most in Your Big Data with HPCC Systems
>> Open Source. Fast. Scalable. Simple. Ideal for Dirty Data.
>> Leverages Graph Analysis for Fast Processing & Easy Data Exploration
>> http://p.sf.net/sfu/hpccsystems
>> _______________________________________________
>> Postgres-xc-general mailing list
>> Pos...@li...
>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
>> 
> 

Re: [Postgres-xc-general] Problem with taking database dump with pgxc-1.2.1

[Masataka S. <pg...@gm...>]

On 17 June 2014 17:07, 鈴木 幸市 <ko...@in...> wrote:
> This came in 1.2.   pg_dump runs as a single transaction so it looks okay.

No, the isolation level is changed even before 1.1. The location of
statement block is moved from main to setup_connection and appended
READ ONLY attribute, but REPEATABLE READ or SERIALIZABLE level has
been used.
I thought it intend to protect from other concurrent transaction, and
removing it brings something wrong.

Are we ever seeing such error after Pavan's patch? He get rid of "READ
ONLY" from isolation level if the remote version is equal or higher
than PG9.1 (and XC 1.0 is based on PG9.1.)

> ---
> Koichi Suzuki
>
> 2014/06/17 16:31、Masataka Saito <pg...@gm...> のメール：
>
>> On 17 June 2014 10:05, 鈴木 幸市 <ko...@in...> wrote:
>>> Year, it’s XC bug.   Because of the sequence handling difference from PG,
>>> transaction should not be read-only.   Please try the attached patch.   It
>>> will go to 1.2 and master tree if it works.
>>
>> Your patch looks skipping to set transaction isolation level. Is it OK
>> to continue with READ COMMITTED transaction?
>>
>>>
>>> Hope it helps.
>>>
>>> Regards;
>>> ---
>>> Koichi Suzuki
>>>
>>> 2014/06/17 4:25〠Aaron Jackson <aja...@re...> 㠮メール：
>>>
>>> So, I tried the same thing and applied this patch.  I even verified that it
>>> was setting the transaction mode to "serializable, read only, deferrable" -
>>> yet the problem persists.  I believe my execution is similar to the OP.
>>>
>>> pg_dump -d analysisdb --serializable-deferrable
>>>
>>> pg_dump: [archiver (db)] query failed: ERROR:  cannot execute nextval() in a
>>> read-only transaction
>>> pg_dump: [archiver (db)] query was: SELECT
>>> pg_catalog.nextval('customtype_customtypeid_seq');
>>>
>>> Did I miss something else?
>>>
>>> Aaron
>>>
>>> ________________________________
>>> From: Juned Khan [jkh...@gm...]
>>> Sent: Friday, April 11, 2014 2:05 AM
>>> To: Pavan Deolasee
>>> Cc: Postgres-XC mailing list
>>> Subject: Re: [Postgres-xc-general] Problem with taking database dump with
>>> pgxc-1.2.1
>>>
>>> Thanks to Pavan and Koichi for your inputs.
>>>
>>> So i think as of now i do not need to worry about
>>> this
>>>
>>>
>>>
>>>
>>> On Fri, Apr 11, 2014 at 12:31 PM, Pavan Deolasee <pav...@gm...>
>>> wrote:
>>>
>>> On Fri, Apr 11, 2014 at 12:07 PM, Juned Khan <jkh...@gm...> wrote:
>>>
>>> As of now this fix worked fine for me. is there any side effect of this
>>> patch ?
>>>
>>>
>>> IMV there won't be any significant side effect of the patch. pg_dump used to
>>> run in RW mode till we fixed it in 9.3.  If it has worked for so many years,
>>> it may work for some more time too :-) Marking pg_dump READ ONLY was exactly
>>> to catch bugs like these. But other than that, I think its OK.
>>>
>>> Thanks,
>>> Pavan
>>>
>>> --
>>> Pavan Deolasee
>>> http://www.linkedin.com/in/pavandeolasee
>>>
>>>
>>>
>>>
>>> --
>>> Thanks,
>>> Juned Khan
>>> iNextrix Technologies Pvt Ltd.
>>> www.inextrix.com
>>> ------------------------------------------------------------------------------
>>> HPCC Systems Open Source Big Data Platform from LexisNexis Risk Solutions
>>> Find What Matters Most in Your Big Data with HPCC Systems
>>> Open Source. Fast. Scalable. Simple. Ideal for Dirty Data.
>>> Leverages Graph Analysis for Fast Processing & Easy Data Exploration
>>> http://p.sf.net/sfu/hpccsystems_______________________________________________
>>>
>>> Postgres-xc-general mailing list
>>> Pos...@li...
>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
>>>
>>>
>>>
>>> ------------------------------------------------------------------------------
>>> HPCC Systems Open Source Big Data Platform from LexisNexis Risk Solutions
>>> Find What Matters Most in Your Big Data with HPCC Systems
>>> Open Source. Fast. Scalable. Simple. Ideal for Dirty Data.
>>> Leverages Graph Analysis for Fast Processing & Easy Data Exploration
>>> http://p.sf.net/sfu/hpccsystems
>>> _______________________________________________
>>> Postgres-xc-general mailing list
>>> Pos...@li...
>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
>>>
>>
>

Re: [Postgres-xc-general] Problem with taking database dump with pgxc-1.2.1

[Aaron J. <aja...@re...>]

So, it would appear that after applying this patch, that pg_dump works the first time.  However, I prematurely terminated pg_dump (SIGINT on the terminal) and subsequently issued a count on one of my tables.  The query hung and eventually returned with “WARNING: Xid is invalid.” – the call to psql never actually finished.

I ended up restarting the gtm_proxy, data node and coordinator on host 1.  I think at this point, I’ll do a dump and rebuild the cluster since it seems unstable.

Aaron

From: 鈴木 幸市 [mailto:ko...@in...]
Sent: Monday, June 16, 2014 8:05 PM
To: Aaron Jackson
Cc: Postgres-XC mailing list
Subject: Re: [Postgres-xc-general] Problem with taking database dump with pgxc-1.2.1

Year, it’s XC bug.   Because of the sequence handling difference from PG, transaction should not be read-only.   Please try the attached patch.   It will go to 1.2 and master tree if it works.

Hope it helps.

Regards;
---
Koichi Suzuki

2014/06/17 4:25、Aaron Jackson <aja...@re...<mailto:aja...@re...>> のメール：


So, I tried the same thing and applied this patch.  I even verified that it was setting the transaction mode to "serializable, read only, deferrable" - yet the problem persists.  I believe my execution is similar to the OP.

pg_dump -d analysisdb --serializable-deferrable

pg_dump: [archiver (db)] query failed: ERROR:  cannot execute nextval() in a read-only transaction
pg_dump: [archiver (db)] query was: SELECT pg_catalog.nextval('customtype_customtypeid_seq');

Did I miss something else?

Aaron

________________________________
From: Juned Khan [jkh...@gm...<mailto:jkh...@gm...>]
Sent: Friday, April 11, 2014 2:05 AM
To: Pavan Deolasee
Cc: Postgres-XC mailing list
Subject: Re: [Postgres-xc-general] Problem with taking database dump with pgxc-1.2.1
Thanks to Pavan and Koichi for your inputs.
So i think as of now i do not need to worry about
this


On Fri, Apr 11, 2014 at 12:31 PM, Pavan Deolasee <pav...@gm...<mailto:pav...@gm...>> wrote:
On Fri, Apr 11, 2014 at 12:07 PM, Juned Khan <jkh...@gm...<mailto:jkh...@gm...>> wrote:
As of now this fix worked fine for me. is there any side effect of this
patch ?

IMV there won't be any significant side effect of the patch. pg_dump used to run in RW mode till we fixed it in 9.3.  If it has worked for so many years, it may work for some more time too :-) Marking pg_dump READ ONLY was exactly to catch bugs like these. But other than that, I think its OK.

Thanks,
Pavan

--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee



--
Thanks,
Juned Khan
iNextrix Technologies Pvt Ltd.
www.inextrix.com<http://www.inextrix.com/>
------------------------------------------------------------------------------
HPCC Systems Open Source Big Data Platform from LexisNexis Risk Solutions
Find What Matters Most in Your Big Data with HPCC Systems
Open Source. Fast. Scalable. Simple. Ideal for Dirty Data.
Leverages Graph Analysis for Fast Processing & Easy Data Exploration
http://p.sf.net/sfu/hpccsystems_______________________________________________
Postgres-xc-general mailing list
Pos...@li...<mailto:Pos...@li...>
https://lists.sourceforge.net/lists/listinfo/postgres-xc-general

Re: [Postgres-xc-general] Problem with taking database dump with pgxc-1.2.1

[鈴木 幸市 <ko...@in...>]

Hmm,  I’m afraid GTM have falled back local XID somehow.   Could you let me test this.   May need a bit.

Thank you.
---
Koichi Suzuki

2014/06/18 3:46、Aaron Jackson <aja...@re...<mailto:aja...@re...>> のメール：

So, it would appear that after applying this patch, that pg_dump works the first time.  However, I prematurely terminated pg_dump (SIGINT on the terminal) and subsequently issued a count on one of my tables.  The query hung and eventually returned with “WARNING: Xid is invalid.” – the call to psql never actually finished.

I ended up restarting the gtm_proxy, data node and coordinator on host 1.  I think at this point, I’ll do a dump and rebuild the cluster since it seems unstable.

Aaron

From: 鈴木 幸市 [mailto:ko...@in...]
Sent: Monday, June 16, 2014 8:05 PM
To: Aaron Jackson
Cc: Postgres-XC mailing list
Subject: Re: [Postgres-xc-general] Problem with taking database dump with pgxc-1.2.1

Year, it’s XC bug.   Because of the sequence handling difference from PG, transaction should not be read-only.   Please try the attached patch.   It will go to 1.2 and master tree if it works.

Hope it helps.

Regards;
---
Koichi Suzuki

2014/06/17 4:25、Aaron Jackson <aja...@re...<mailto:aja...@re...>> のメール：


So, I tried the same thing and applied this patch.  I even verified that it was setting the transaction mode to "serializable, read only, deferrable" - yet the problem persists.  I believe my execution is similar to the OP.

pg_dump -d analysisdb --serializable-deferrable

pg_dump: [archiver (db)] query failed: ERROR:  cannot execute nextval() in a read-only transaction
pg_dump: [archiver (db)] query was: SELECT pg_catalog.nextval('customtype_customtypeid_seq');

Did I miss something else?

Aaron

________________________________
From: Juned Khan [jkh...@gm...<mailto:jkh...@gm...>]
Sent: Friday, April 11, 2014 2:05 AM
To: Pavan Deolasee
Cc: Postgres-XC mailing list
Subject: Re: [Postgres-xc-general] Problem with taking database dump with pgxc-1.2.1
Thanks to Pavan and Koichi for your inputs.
So i think as of now i do not need to worry about
this


On Fri, Apr 11, 2014 at 12:31 PM, Pavan Deolasee <pav...@gm...<mailto:pav...@gm...>> wrote:
On Fri, Apr 11, 2014 at 12:07 PM, Juned Khan <jkh...@gm...<mailto:jkh...@gm...>> wrote:
As of now this fix worked fine for me. is there any side effect of this
patch ?

IMV there won't be any significant side effect of the patch. pg_dump used to run in RW mode till we fixed it in 9.3.  If it has worked for so many years, it may work for some more time too :-) Marking pg_dump READ ONLY was exactly to catch bugs like these. But other than that, I think its OK.

Thanks,
Pavan

--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee



--
Thanks,
Juned Khan
iNextrix Technologies Pvt Ltd.
www.inextrix.com<http://www.inextrix.com/>
------------------------------------------------------------------------------
HPCC Systems Open Source Big Data Platform from LexisNexis Risk Solutions
Find What Matters Most in Your Big Data with HPCC Systems
Open Source. Fast. Scalable. Simple. Ideal for Dirty Data.
Leverages Graph Analysis for Fast Processing & Easy Data Exploration
http://p.sf.net/sfu/hpccsystems_______________________________________________
Postgres-xc-general mailing list
Pos...@li...<mailto:Pos...@li...>
https://lists.sourceforge.net/lists/listinfo/postgres-xc-general

Re: [Postgres-xc-general] Problem with taking database dump with pgxc-1.2.1

[Aaron J. <aja...@re...>]

Not a problem, that's why we test these things (and take backups).

Aaron
________________________________
From: 鈴木 幸市 [ko...@in...]
Sent: Tuesday, June 17, 2014 7:45 PM
To: Aaron Jackson
Cc: Postgres-XC mailing list
Subject: Re: [Postgres-xc-general] Problem with taking database dump with pgxc-1.2.1

Hmm,  I’m afraid GTM have falled back local XID somehow.   Could you let me test this.   May need a bit.

Thank you.
---
Koichi Suzuki

2014/06/18 3:46、Aaron Jackson <aja...@re...<mailto:aja...@re...>> のメール：

So, it would appear that after applying this patch, that pg_dump works the first time.  However, I prematurely terminated pg_dump (SIGINT on the terminal) and subsequently issued a count on one of my tables.  The query hung and eventually returned with “WARNING: Xid is invalid.” – the call to psql never actually finished.

I ended up restarting the gtm_proxy, data node and coordinator on host 1.  I think at this point, I’ll do a dump and rebuild the cluster since it seems unstable.

Aaron

From: 鈴木 幸市 [mailto:ko...@in...]
Sent: Monday, June 16, 2014 8:05 PM
To: Aaron Jackson
Cc: Postgres-XC mailing list
Subject: Re: [Postgres-xc-general] Problem with taking database dump with pgxc-1.2.1

Year, it’s XC bug.   Because of the sequence handling difference from PG, transaction should not be read-only.   Please try the attached patch.   It will go to 1.2 and master tree if it works.

Hope it helps.

Regards;
---
Koichi Suzuki

2014/06/17 4:25、Aaron Jackson <aja...@re...<mailto:aja...@re...>> のメール：


So, I tried the same thing and applied this patch.  I even verified that it was setting the transaction mode to "serializable, read only, deferrable" - yet the problem persists.  I believe my execution is similar to the OP.

pg_dump -d analysisdb --serializable-deferrable

pg_dump: [archiver (db)] query failed: ERROR:  cannot execute nextval() in a read-only transaction
pg_dump: [archiver (db)] query was: SELECT pg_catalog.nextval('customtype_customtypeid_seq');

Did I miss something else?

Aaron

________________________________
From: Juned Khan [jkh...@gm...<mailto:jkh...@gm...>]
Sent: Friday, April 11, 2014 2:05 AM
To: Pavan Deolasee
Cc: Postgres-XC mailing list
Subject: Re: [Postgres-xc-general] Problem with taking database dump with pgxc-1.2.1
Thanks to Pavan and Koichi for your inputs.
So i think as of now i do not need to worry about
this


On Fri, Apr 11, 2014 at 12:31 PM, Pavan Deolasee <pav...@gm...<mailto:pav...@gm...>> wrote:
On Fri, Apr 11, 2014 at 12:07 PM, Juned Khan <jkh...@gm...<mailto:jkh...@gm...>> wrote:
As of now this fix worked fine for me. is there any side effect of this
patch ?

IMV there won't be any significant side effect of the patch. pg_dump used to run in RW mode till we fixed it in 9.3.  If it has worked for so many years, it may work for some more time too :-) Marking pg_dump READ ONLY was exactly to catch bugs like these. But other than that, I think its OK.

Thanks,
Pavan

--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee



--
Thanks,
Juned Khan
iNextrix Technologies Pvt Ltd.
www.inextrix.com<http://www.inextrix.com/>
------------------------------------------------------------------------------
HPCC Systems Open Source Big Data Platform from LexisNexis Risk Solutions
Find What Matters Most in Your Big Data with HPCC Systems
Open Source. Fast. Scalable. Simple. Ideal for Dirty Data.
Leverages Graph Analysis for Fast Processing & Easy Data Exploration
http://p.sf.net/sfu/hpccsystems_______________________________________________
Postgres-xc-general mailing list
Pos...@li...<mailto:Pos...@li...>
https://lists.sourceforge.net/lists/listinfo/postgres-xc-general