You can subscribe to this list here.
2010 |
Jan
|
Feb
|
Mar
|
Apr
(10) |
May
(17) |
Jun
(3) |
Jul
|
Aug
|
Sep
(8) |
Oct
(18) |
Nov
(51) |
Dec
(74) |
---|---|---|---|---|---|---|---|---|---|---|---|---|
2011 |
Jan
(47) |
Feb
(44) |
Mar
(44) |
Apr
(102) |
May
(35) |
Jun
(25) |
Jul
(56) |
Aug
(69) |
Sep
(32) |
Oct
(37) |
Nov
(31) |
Dec
(16) |
2012 |
Jan
(34) |
Feb
(127) |
Mar
(218) |
Apr
(252) |
May
(80) |
Jun
(137) |
Jul
(205) |
Aug
(159) |
Sep
(35) |
Oct
(50) |
Nov
(82) |
Dec
(52) |
2013 |
Jan
(107) |
Feb
(159) |
Mar
(118) |
Apr
(163) |
May
(151) |
Jun
(89) |
Jul
(106) |
Aug
(177) |
Sep
(49) |
Oct
(63) |
Nov
(46) |
Dec
(7) |
2014 |
Jan
(65) |
Feb
(128) |
Mar
(40) |
Apr
(11) |
May
(4) |
Jun
(8) |
Jul
(16) |
Aug
(11) |
Sep
(4) |
Oct
(1) |
Nov
(5) |
Dec
(16) |
2015 |
Jan
(5) |
Feb
|
Mar
(2) |
Apr
(5) |
May
(4) |
Jun
(12) |
Jul
|
Aug
|
Sep
|
Oct
|
Nov
|
Dec
(4) |
2019 |
Jan
|
Feb
|
Mar
|
Apr
|
May
|
Jun
|
Jul
(2) |
Aug
|
Sep
|
Oct
|
Nov
|
Dec
|
From: Nikhil S. <ni...@st...> - 2013-10-24 15:00:43
|
Seems like a good thing to add in the GTM daemons (covers GTM Standby as well?). So +1 from me. Regards, Nikhils On Thu, Oct 24, 2013 at 6:43 AM, Koichi Suzuki <koi...@gm...>wrote: > Hi, > > Libpq for GTM and GTM_Proxy has a feature to store its own error messages > to gtm_conn structure. Because Libpq is to be linked to any other > applications, GTM libpq itself does not call elog(). However, modules > calling gtm libpq can do this. > > I found we can add this feature to gtm_proxy's proxy_main.c and backend's > gtm.c and hope this helps to analyze what's going on in gtm communications. > > If I have reasonable approval, I'd like to submit a patch for this. > > Regards; > --- > Koichi Suzuki > > > ------------------------------------------------------------------------------ > October Webinars: Code for Performance > Free Intel webinars can help you accelerate application performance. > Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most > from > the latest Intel processors and coprocessors. See abstracts and register > > http://pubads.g.doubleclick.net/gampad/clk?id=60135991&iu=/4140/ostg.clktrk > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > -- StormDB - http://www.stormdb.com The Database Cloud |
From: Koichi S. <koi...@gm...> - 2013-10-24 01:13:41
|
Hi, Libpq for GTM and GTM_Proxy has a feature to store its own error messages to gtm_conn structure. Because Libpq is to be linked to any other applications, GTM libpq itself does not call elog(). However, modules calling gtm libpq can do this. I found we can add this feature to gtm_proxy's proxy_main.c and backend's gtm.c and hope this helps to analyze what's going on in gtm communications. If I have reasonable approval, I'd like to submit a patch for this. Regards; --- Koichi Suzuki |
From: Nikhil S. <ni...@st...> - 2013-10-23 07:19:34
|
+1 I had diagnosed this same issue quite a while ago. We should wait for the buffer to drain out before adding more into it. The buffer size was going beyond 1GB in size pretty quickly! Regards, Nikhils On Wed, Oct 23, 2013 at 10:35 AM, Koichi Suzuki <koi...@gm...>wrote: > I've found that in copy command, we have a risk to overflow the buffer in > coordinator if datanode is very slow (for various reasons). > > When we issue copy command against tens of gigabytes of data, coordinator > send all of then without synch. When some of the data fails to send, it > adds next data and tries to send all of them. When a datanode is very > slow (for any reason), the data will continue to be cashed at the > coordinator and finally overflows. > > The patch avoids this issue. When amount of unsent data exceeds the > criteria, coordinator will keep retry until this amount is below the second > criteria. > > This can be applied to all the releases, as well as the master. > > Regards; > --- > Koichi Suzuki > > > ------------------------------------------------------------------------------ > October Webinars: Code for Performance > Free Intel webinars can help you accelerate application performance. > Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most > from > the latest Intel processors and coprocessors. See abstracts and register > > http://pubads.g.doubleclick.net/gampad/clk?id=60135991&iu=/4140/ostg.clktrk > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > -- StormDB - http://www.stormdb.com The Database Cloud |
From: Koichi S. <koi...@gm...> - 2013-10-22 02:12:32
|
(Please take a look at WIP branch master_pg93_merge). I reviewed this patch and found that this patch handles CLEAN CONNECTION as simply NOP. To disable event trigger for some DDLs, we need to move the code from ProcessUtilitySlow() to Standard_ProcessUtility(). My previous patch was not sufficient. We need to move the code for XC-specific DDLs in this manner. I don't think this change affects regression test. DDL handling was done as follows: (outside) --> ProcessUtility() /* ProcessUtility_hook is handled here */ --> standard_ProcessUtility() /* DDLs without event trigger support are handled here */ --> ProcessUtilitySlow() /* DDLs with event trigger support are handled here */ 3_1_clean_con.patch just returns from standard_ProcessUtility() without doing anything. Unfortunately, CLEAN CONNECTION HANDLER is now in ProcessUtilitySlow() and this should be moved back to standard_ProcessUtility(). Any thoughts? --- Koichi Suzuki 2013/9/30 Abbas Butt <abb...@en...> > Here is the patch, forgot to attach it with the previous mail. > Regards > > > > On Mon, Sep 30, 2013 at 7:23 AM, Abbas Butt <abb...@en...> > wrote: > > > > Hi, > > The current implementation of event triggers assumes that any statment > > that is not handeled by the main switch-case in standard_ProcessUtility > > supports event triggers. > > CLEAN CONNECTION is a new statement added by XC and does not support > > event triggers as of now. > > > > This patch is intended for the master_pg93_merge branch and applies > cleanly > > on top of the following two patches. > > 20130924_01.patch and fix_for_pooler_socket_20130926.patch > > > > Regards > > -- > > Abbas > > Architect > > > > Ph: 92.334.5100153 > > Skype ID: gabbasb > > www.enterprisedb.com > > > > Follow us on Twitter > > @EnterpriseDB > > > > Visit EnterpriseDB for tutorials, webinars, whitepapers and more > > > > > -- > -- > Abbas > Architect > > Ph: 92.334.5100153 > Skype ID: gabbasb > www.enterprisedb.com > > Follow us on Twitter > @EnterpriseDB > > Visit EnterpriseDB for tutorials, webinars, whitepapers and more > > > On Mon, Sep 30, 2013 at 7:23 AM, Abbas Butt <abb...@en...> > wrote: > > > > Hi, > > The current implementation of event triggers assumes that any statment > > that is not handeled by the main switch-case in standard_ProcessUtility > > supports event triggers. > > CLEAN CONNECTION is a new statement added by XC and does not support > > event triggers as of now. > > > > This patch is intended for the master_pg93_merge branch and applies > cleanly > > on top of the following two patches. > > 20130924_01.patch and fix_for_pooler_socket_20130926.patch > > > > Regards > > -- > > Abbas > > Architect > > > > Ph: 92.334.5100153 > > Skype ID: gabbasb > > www.enterprisedb.com > > > > Follow us on Twitter > > @EnterpriseDB > > > > Visit EnterpriseDB for tutorials, webinars, whitepapers and more > > > > > -- > -- > Abbas > Architect > > Ph: 92.334.5100153 > Skype ID: gabbasb > www.enterprisedb.com > > Follow us on Twitter > @EnterpriseDB > > Visit EnterpriseDB for tutorials, webinars, whitepapers and more > > > ------------------------------------------------------------------------------ > October Webinars: Code for Performance > Free Intel webinars can help you accelerate application performance. > Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most > from > the latest Intel processors and coprocessors. See abstracts and register > > http://pubads.g.doubleclick.net/gampad/clk?id=60133471&iu=/4140/ostg.clktrk > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > |
From: Masataka S. <pg...@gm...> - 2013-10-10 08:11:11
|
Hi, all. I observed wrong sorted results on a SELECT query with COLLATE clause. test=# SELECT * FROM FOOBAR ORDER BY all_alpha COLLATE "en_US"; all_alpha ----------- A b B z Z a (6 rows) Related ticket is here. https://sourceforge.net/p/postgres-xc/bugs/458/ Please refer the ticket for more details including instructions to reproduce. Regards. |
From: Matt W. <MW...@XI...> - 2013-10-09 23:29:59
|
Below is a summary of the changes made against git pull from this morning. I don't claim to be the best resource for this, so please do take the time to verify what I've sent. I also welcome comments and suggestions. Please note that the entire config directory was replaced with the one from Postgres 9.3.0. Likewise, I used configure.in from Postgres 9.3.0 as the starting point for the updates. autoconf successfully creates the configure file, which is also attached. I've tried to capture all the XC pieces that had been added manually to the previous configure file, but the new configure file should be checked and verified. In particular, I moved the '-DPGXC' from configure to pgxc_config.h and updated postgres.h to include this new header. The changes all compile successfully under Solaris 10 using the Solaris Studio compiler. Being that the Solaris compiler seems to be more demanding than gcc about syntax and "correctness", I believe this is a good sign. I also ran this with test data and saw no issues. FYI, Matt % git status # On branch master # Changes to be committed: # (use "git reset HEAD <file>..." to unstage) # # new file: src/include/pgxc_config.h # new file: src/include/pgxc_config.h.in # new file: src/include/stamp-ext-h # # Changes not staged for commit: # (use "git add <file>..." to update what will be committed) # (use "git checkout -- <file>..." to discard changes in working directory) # # modified: config/c-compiler.m4 # modified: config/config.guess # modified: config/config.sub # modified: config/install-sh # modified: config/perl.m4 # modified: config/programs.m4 # modified: config/python.m4 # modified: configure # modified: configure.in # modified: src/backend/pgxc/pool/pgxcnode.c # modified: src/include/postgres.h # -----Original Message----- From: Michael Paquier [mailto:mic...@gm...] Sent: Tuesday, October 08, 2013 5:43 PM To: Matt Warner Cc: 鈴木 幸市; Postgres-XC Developers; Koichi Suzuki Subject: Re: [Postgres-xc-developers] Minor Fixes On Wed, Oct 9, 2013 at 9:13 AM, Matt Warner <MW...@xi...> wrote: > So I owe you a different patch for proxy_main.c as well. Btw, I can see why it is needed for pgxcnode.h as we use FIONREAD there, but why is it necessary to have an include to filio.h in proxy_main.c? I couldn't figure out why so I simply removed that from the patch committed. -- Michael |
From: Matt W. <MW...@XI...> - 2013-10-09 17:13:27
|
I've checked out latest with git and as you point out, there's no need for filio.h in proxy_main.c. There is a need for it in backend/pgxc/pgxcnode.c, so I've put in an "#ifdef HAVE_SYS_FILIO_H" to conditionally include filio.h. As soon as I complete a successful compilation of the latest pull from git and do some testing, I'll send the changes. Matt -----Original Message----- From: Michael Paquier [mailto:mic...@gm...] Sent: Tuesday, October 08, 2013 5:43 PM To: Matt Warner Cc: 鈴木 幸市; Postgres-XC Developers; Koichi Suzuki Subject: Re: [Postgres-xc-developers] Minor Fixes On Wed, Oct 9, 2013 at 9:13 AM, Matt Warner <MW...@xi...> wrote: > So I owe you a different patch for proxy_main.c as well. Btw, I can see why it is needed for pgxcnode.h as we use FIONREAD there, but why is it necessary to have an include to filio.h in proxy_main.c? I couldn't figure out why so I simply removed that from the patch committed. -- Michael |
From: Masataka S. <pg...@gm...> - 2013-10-09 09:28:21
|
I made a ticket for this issue. https://sourceforge.net/p/postgres-xc/bugs/457/ The test case is also failing to XC 1.0.0. XC 1.0.0 returns same error code as XC on the master branch. The test is introduced only to master branch after branch REL9_2_STABLE, which I use to test XC 1.1 was created. It is why REL1_1_STABLE branch passes the JDBC regression test. Regards. On Mon, Oct 7, 2013 at 11:42 PM, Ahsan Hadi <ahs...@en...> wrote: > Thanks Masataka. > > Can you create a source-forge ticket for this please? Attach a reproducible > test case with the ticket. > > > > On Thu, Oct 3, 2013 at 2:45 PM, Masataka Saito <pg...@gm...> wrote: >> >> Hello, all. >> >> I found Postgres-XC returns different error code from PostgreSQL's one >> for repeated rollback, and it makes a pgjdbc's regression test case >> fail. >> >> PostgreSQL returns error code 42704 which is defined as >> ERRCODE_UNDEFINED_OBJECT. It is maybe returned in >> src/backend/access/transam/twophase.c::LockGXact. >> Postgres-XC returns error code XX000 which is defined as >> ERRCODE_INTERNAL_ERROR It is maybe returned in >> src/backend/pgxc/pool/execRemote.c::FinishRemotePreparedTransaction. >> >> Error messages are quite similar. I will show it at the next line. >> > prepared transaction with identifier >> > "0_8fHx8fF5/OCyZd9xZmJ2MSPCE1olci037CNQYoXoVCKbi63JcZIoUeJxU7PKl/t4qI4xV1agFPE+9gRrzheGHA==_BAUGAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA==" >> > does not exist. >> >> Does anyone know ... >> * Why they have different error codes, and is it reasonable? >> * To fix the code affects other components? >> >> You can reproduce using pgjdbc's regression test. The source code of >> the test is >> org.postgresql.test.xa.XADataSourceTest.testRepeatedRolledBack. >> > >> > http://sourceforge.net/p/postgres-xc/pgjdbc-xc/ci/master/tree/org/postgresql/test/xa/XADataSourceTest.java#l331 >> >> Regards. >> >> >> ------------------------------------------------------------------------------ >> October Webinars: Code for Performance >> Free Intel webinars can help you accelerate application performance. >> Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most >> from >> the latest Intel processors and coprocessors. See abstracts and register > >> >> http://pubads.g.doubleclick.net/gampad/clk?id=60134791&iu=/4140/ostg.clktrk >> _______________________________________________ >> Postgres-xc-developers mailing list >> Pos...@li... >> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > > > > -- > Ahsan Hadi > Snr Director Product Development > EnterpriseDB Corporation > The Enterprise Postgres Company > > Phone: +92-51-8358874 > Mobile: +92-333-5162114 > > Website: www.enterprisedb.com > EnterpriseDB Blog: http://blogs.enterprisedb.com/ > Follow us on Twitter: http://www.twitter.com/enterprisedb > > This e-mail message (and any attachment) is intended for the use of the > individual or entity to whom it is addressed. This message contains > information from EnterpriseDB Corporation that may be privileged, > confidential, or exempt from disclosure under applicable law. If you are not > the intended recipient or authorized to receive this for the intended > recipient, any use, dissemination, distribution, retention, archiving, or > copying of this communication is strictly prohibited. If you have received > this e-mail in error, please notify the sender immediately by reply e-mail > and delete this message. |
From: Nikhil S. <ni...@st...> - 2013-10-09 08:13:40
|
+1 for an approach using the Primary Key. As Pavan mentioned, maybe Update already works using PK as an identifier so we might have a precedent already. Regards, Nikhils On Wed, Oct 9, 2013 at 12:12 PM, Pavan Deolasee <pav...@gm...>wrote: > On Wed, Oct 9, 2013 at 12:09 PM, Andrei Martsinchyk < > and...@gm...> wrote: > >> I believe even plain vacuum may move tuples within a page and purge empty >> pages, therefore CTIDs could be changed. >> > > Plain vacuum does not move tuples around.. Note that all indexes must be > updated if a tuple is moved, even within a page. But besides that all your > points below still hold true. > > >> Autovacuum on Datamodes are not in sync and concurrent writes may occur >> in different order on Datanodes. >> CTID can not be used to identify tuple replicas. Primary key could be >> used, or special system column. Otherwise not shippable updates against >> replicated tables should not be allowed. >> >> >> 2013/10/9 Pavan Deolasee <pav...@gm...> >> >>> >>> >>> >>> On Wed, Oct 9, 2013 at 11:49 AM, Koichi Suzuki <koi...@gm...>wrote: >>> >>>> Hmm.. Yes, it could be serious. I suppose that CTID changes only when >>>> vacuum full occurs. >>>> >>> >>> CTID will also change every time a row is updated. So the bug should be >>> very easy to reproduce. One easiest way to compile different datanodes with >>> diferent block size. Another way could be to update/delete from a >>> replicated table and vacuum the table on one datanode but not other and >>> then again update several rows in the table. Most likely the new versions >>> on two different nodes will not have the same CTID anymore. >>> >>> >>>> Vacuum freeze will updates xmim to the smallest value and usual vacuum >>>> recycles "dead" row area. We may have a chance to assign different CTIDs >>>> to the same rows in replicated table if vacuum full runs locally in >>>> datanodes. (My assumption may be wrong. Does vacuum freeze move tuples >>>> and change their CTID?) >>>> >>>> >>> No, it does not. >>> >>> Thanks, >>> Pavan >>> >>> -- >>> Pavan Deolasee >>> http://www.linkedin.com/in/pavandeolasee >>> >>> >>> ------------------------------------------------------------------------------ >>> October Webinars: Code for Performance >>> Free Intel webinars can help you accelerate application performance. >>> Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most >>> from >>> the latest Intel processors and coprocessors. See abstracts and register >>> > >>> >>> http://pubads.g.doubleclick.net/gampad/clk?id=60134071&iu=/4140/ostg.clktrk >>> >>> _______________________________________________ >>> Postgres-xc-developers mailing list >>> Pos...@li... >>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers >>> >>> >> >> >> -- >> Andrei Martsinchyk >> >> >> StormDB - http://www.stormdb.com >> The Database Cloud >> >> > > > -- > Pavan Deolasee > http://www.linkedin.com/in/pavandeolasee > > > ------------------------------------------------------------------------------ > October Webinars: Code for Performance > Free Intel webinars can help you accelerate application performance. > Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most > from > the latest Intel processors and coprocessors. See abstracts and register > > http://pubads.g.doubleclick.net/gampad/clk?id=60134071&iu=/4140/ostg.clktrk > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > -- StormDB - http://www.stormdb.com The Database Cloud |
From: Pavan D. <pav...@gm...> - 2013-10-09 06:42:35
|
On Wed, Oct 9, 2013 at 12:09 PM, Andrei Martsinchyk < and...@gm...> wrote: > I believe even plain vacuum may move tuples within a page and purge empty > pages, therefore CTIDs could be changed. > Plain vacuum does not move tuples around.. Note that all indexes must be updated if a tuple is moved, even within a page. But besides that all your points below still hold true. > Autovacuum on Datamodes are not in sync and concurrent writes may occur in > different order on Datanodes. > CTID can not be used to identify tuple replicas. Primary key could be > used, or special system column. Otherwise not shippable updates against > replicated tables should not be allowed. > > > 2013/10/9 Pavan Deolasee <pav...@gm...> > >> >> >> >> On Wed, Oct 9, 2013 at 11:49 AM, Koichi Suzuki <koi...@gm...>wrote: >> >>> Hmm.. Yes, it could be serious. I suppose that CTID changes only when >>> vacuum full occurs. >>> >> >> CTID will also change every time a row is updated. So the bug should be >> very easy to reproduce. One easiest way to compile different datanodes with >> diferent block size. Another way could be to update/delete from a >> replicated table and vacuum the table on one datanode but not other and >> then again update several rows in the table. Most likely the new versions >> on two different nodes will not have the same CTID anymore. >> >> >>> Vacuum freeze will updates xmim to the smallest value and usual vacuum >>> recycles "dead" row area. We may have a chance to assign different CTIDs >>> to the same rows in replicated table if vacuum full runs locally in >>> datanodes. (My assumption may be wrong. Does vacuum freeze move tuples >>> and change their CTID?) >>> >>> >> No, it does not. >> >> Thanks, >> Pavan >> >> -- >> Pavan Deolasee >> http://www.linkedin.com/in/pavandeolasee >> >> >> ------------------------------------------------------------------------------ >> October Webinars: Code for Performance >> Free Intel webinars can help you accelerate application performance. >> Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most >> from >> the latest Intel processors and coprocessors. See abstracts and register > >> >> http://pubads.g.doubleclick.net/gampad/clk?id=60134071&iu=/4140/ostg.clktrk >> >> _______________________________________________ >> Postgres-xc-developers mailing list >> Pos...@li... >> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers >> >> > > > -- > Andrei Martsinchyk > > > StormDB - http://www.stormdb.com > The Database Cloud > > -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee |
From: Andrei M. <and...@gm...> - 2013-10-09 06:39:46
|
I believe even plain vacuum may move tuples within a page and purge empty pages, therefore CTIDs could be changed. Autovacuum on Datamodes are not in sync and concurrent writes may occur in different order on Datanodes. CTID can not be used to identify tuple replicas. Primary key could be used, or special system column. Otherwise not shippable updates against replicated tables should not be allowed. 2013/10/9 Pavan Deolasee <pav...@gm...> > > > > On Wed, Oct 9, 2013 at 11:49 AM, Koichi Suzuki <koi...@gm...>wrote: > >> Hmm.. Yes, it could be serious. I suppose that CTID changes only when >> vacuum full occurs. >> > > CTID will also change every time a row is updated. So the bug should be > very easy to reproduce. One easiest way to compile different datanodes with > diferent block size. Another way could be to update/delete from a > replicated table and vacuum the table on one datanode but not other and > then again update several rows in the table. Most likely the new versions > on two different nodes will not have the same CTID anymore. > > >> Vacuum freeze will updates xmim to the smallest value and usual vacuum >> recycles "dead" row area. We may have a chance to assign different CTIDs >> to the same rows in replicated table if vacuum full runs locally in >> datanodes. (My assumption may be wrong. Does vacuum freeze move tuples >> and change their CTID?) >> >> > No, it does not. > > Thanks, > Pavan > > -- > Pavan Deolasee > http://www.linkedin.com/in/pavandeolasee > > > ------------------------------------------------------------------------------ > October Webinars: Code for Performance > Free Intel webinars can help you accelerate application performance. > Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most > from > the latest Intel processors and coprocessors. See abstracts and register > > http://pubads.g.doubleclick.net/gampad/clk?id=60134071&iu=/4140/ostg.clktrk > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > -- Andrei Martsinchyk StormDB - http://www.stormdb.com The Database Cloud |
From: Pavan D. <pav...@gm...> - 2013-10-09 06:39:33
|
On Wed, Oct 9, 2013 at 12:03 PM, 鈴木 幸市 <ko...@in...> wrote: > Thanks Pavan for the comment. > > So possible approach to resolve this problem could be separate system > column to maintain column identification, or asking primary key for > replicated tables when trigger (and other CTID-dependent feature) is used. > > Right. But I wonder why is it limited to triggers only ? How does UPDATE on a replicated table work anyways ? Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee |
From: 鈴木 幸市 <ko...@in...> - 2013-10-09 06:33:18
|
Thanks Pavan for the comment. So possible approach to resolve this problem could be separate system column to maintain column identification, or asking primary key for replicated tables when trigger (and other CTID-dependent feature) is used. Any other ideas/thoughts? --- Koichi Suzuki On 2013/10/09, at 15:26, Pavan Deolasee <pav...@gm...<mailto:pav...@gm...>> wrote: On Wed, Oct 9, 2013 at 11:49 AM, Koichi Suzuki <koi...@gm...<mailto:koi...@gm...>> wrote: Hmm.. Yes, it could be serious. I suppose that CTID changes only when vacuum full occurs. CTID will also change every time a row is updated. So the bug should be very easy to reproduce. One easiest way to compile different datanodes with diferent block size. Another way could be to update/delete from a replicated table and vacuum the table on one datanode but not other and then again update several rows in the table. Most likely the new versions on two different nodes will not have the same CTID anymore. Vacuum freeze will updates xmim to the smallest value and usual vacuum recycles "dead" row area. We may have a chance to assign different CTIDs to the same rows in replicated table if vacuum full runs locally in datanodes. (My assumption may be wrong. Does vacuum freeze move tuples and change their CTID?) No, it does not. Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee ------------------------------------------------------------------------------ October Webinars: Code for Performance Free Intel webinars can help you accelerate application performance. Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most from the latest Intel processors and coprocessors. See abstracts and register > http://pubads.g.doubleclick.net/gampad/clk?id=60134071&iu=/4140/ostg.clktrk_______________________________________________ Postgres-xc-developers mailing list Pos...@li... https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers |
From: Pavan D. <pav...@gm...> - 2013-10-09 06:27:06
|
On Wed, Oct 9, 2013 at 11:49 AM, Koichi Suzuki <koi...@gm...>wrote: > Hmm.. Yes, it could be serious. I suppose that CTID changes only when > vacuum full occurs. > CTID will also change every time a row is updated. So the bug should be very easy to reproduce. One easiest way to compile different datanodes with diferent block size. Another way could be to update/delete from a replicated table and vacuum the table on one datanode but not other and then again update several rows in the table. Most likely the new versions on two different nodes will not have the same CTID anymore. > Vacuum freeze will updates xmim to the smallest value and usual vacuum > recycles "dead" row area. We may have a chance to assign different CTIDs > to the same rows in replicated table if vacuum full runs locally in > datanodes. (My assumption may be wrong. Does vacuum freeze move tuples > and change their CTID?) > > No, it does not. Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee |
From: Koichi S. <koi...@gm...> - 2013-10-09 06:19:34
|
Hmm.. Yes, it could be serious. I suppose that CTID changes only when vacuum full occurs. Vacuum freeze will updates xmim to the smallest value and usual vacuum recycles "dead" row area. We may have a chance to assign different CTIDs to the same rows in replicated table if vacuum full runs locally in datanodes. (My assumption may be wrong. Does vacuum freeze move tuples and change their CTID?) I think we have two different fix for this. 1. Do not assume "the same CTID to the same row". In this case, to provide "row identification" to replicated tables, we may need different system column, 2. Ask primary key for replicated tables when trigger is used, or 3. Run vacuum full (and freeze, if vacuum freeze moves tuples among blocks) in more strict way, that is, lock all the replica in the datanode in advance so that CTID can be maintained the same. I understand the approach 1 and 2 is safer. Maybe we can begin with 2 and add 1. If vacuum freeze moves tuples, approach 3 may not be acceptable. Anyway, it can take a bit and need more discussion on the spec. Regards; --- Koichi Suzuki 2013/9/28 Mason Sharp <ma...@st...> > > > > On Thu, Sep 26, 2013 at 9:51 PM, Amit Khandekar < > ami...@en...> wrote: > >> http://sourceforge.net/p/postgres-xc/bugs/402/ >> >> But this issue has nothing do with triggers. I think there should be some >> way to reproduce without triggers. Although, if any user triggers or unique >> constraint triggers makes our job of reproducing the issue easy, that would >> be good. >> > > Under what other circumstances can one reproduce? When the statement > cannot be pushed down directly, like an UPDATE with volatile functions? > > It was not hard to reproduce... insert a few hundred rows in a table. > Execute an UPDATE on one that has a trigger defined. I guess it could > depend on when each node's particular auto-vacuum was run, perhaps with > concurrent activity. > > Anyway, I think this is a major issue. > > > >> >> >> On 27 September 2013 09:33, Ashutosh Bapat < >> ash...@en...> wrote: >> >>> Hi Hackers, >>> While working on triggers me and Amit had thought of this problem and >>> there should be a mail from Amit in that regards. What stopped us from >>> working on this more, was a reproduction scenario where same row landed on >>> two different nodes with different CTID. We couldn't get that scenario even >>> with complex ingredients like vacuum analyze etc. If you are hitting this >>> problem, can one provide us a reproduction. >>> >>> >>> On Thu, Sep 26, 2013 at 11:30 PM, mason_s <ma...@us...> wrote: >>> >>>> ------------------------------ >>>> >>>> * [bugs:#454] <http://sourceforge.net/p/postgres-xc/bugs/454/> Update >>>> triggers on replicated tables may corrupt data* >>>> >>>> *Status:* open >>>> *Created:* Thu Sep 26, 2013 06:00 PM UTC by mason_s >>>> *Last Updated:* Thu Sep 26, 2013 06:00 PM UTC >>>> *Owner:* nobody >>>> >>>> We noticed that when updating a single row on a replicated table that >>>> we were getting duplicate key violations, even though the primary key >>>> columns were not involved. >>>> >>>> We dug deeper and noticed that the mechanism uses ctid to identify what >>>> to update for update triggers that are non-"shippable" and executed on a >>>> coordinator. In the case of a replicated table, the ctid value may be >>>> different on different nodes for each tuple. It appears that Postgres-XC >>>> just uses one ctid value from one of the nodes and then sends down the same >>>> UPDATE statement to all of the individual nodes. >>>> >>>> It should either get each ctid for each node and update, or determine >>>> the corresponding unique key values and use that in the generated WHERE >>>> clause. >>>> ------------------------------ >>>> >>>> Sent from sourceforge.net because you indicated interest in >>>> https://sourceforge.net/p/postgres-xc/bugs/454/ >>>> >>>> To unsubscribe from further messages, please visit >>>> https://sourceforge.net/auth/subscriptions/ >>>> >>> >>> >>> >>> -- >>> Best Wishes, >>> Ashutosh Bapat >>> EnterpriseDB Corporation >>> The Postgres Database Company >>> >>> >>> ------------------------------------------------------------------------------ >>> October Webinars: Code for Performance >>> Free Intel webinars can help you accelerate application performance. >>> Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most >>> from >>> the latest Intel processors and coprocessors. See abstracts and register >>> > >>> >>> http://pubads.g.doubleclick.net/gampad/clk?id=60133471&iu=/4140/ostg.clktrk >>> _______________________________________________ >>> Postgres-xc-developers mailing list >>> Pos...@li... >>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers >>> >>> >> >> >> ------------------------------------------------------------------------------ >> October Webinars: Code for Performance >> Free Intel webinars can help you accelerate application performance. >> Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most >> from >> the latest Intel processors and coprocessors. See abstracts and register > >> >> http://pubads.g.doubleclick.net/gampad/clk?id=60133471&iu=/4140/ostg.clktrk >> _______________________________________________ >> Postgres-xc-developers mailing list >> Pos...@li... >> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers >> >> > > > -- > Mason Sharp > > StormDB - http://www.stormdb.com > The Database Cloud > Postgres-XC Support and Services > > > ------------------------------------------------------------------------------ > October Webinars: Code for Performance > Free Intel webinars can help you accelerate application performance. > Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most > from > the latest Intel processors and coprocessors. See abstracts and register > > http://pubads.g.doubleclick.net/gampad/clk?id=60133471&iu=/4140/ostg.clktrk > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > |
From: Michael P. <mic...@gm...> - 2013-10-09 00:43:19
|
On Wed, Oct 9, 2013 at 9:13 AM, Matt Warner <MW...@xi...> wrote: > So I owe you a different patch for proxy_main.c as well. Btw, I can see why it is needed for pgxcnode.h as we use FIONREAD there, but why is it necessary to have an include to filio.h in proxy_main.c? I couldn't figure out why so I simply removed that from the patch committed. -- Michael |
From: Matt W. <MW...@XI...> - 2013-10-09 00:13:42
|
I've taken configure.in and the M4 files from Postgres 9.3.0 and added in the XC-specific pieces. I hope to have not missed anything. I am testing to ensure I can perform a complete build with no issues before I send the updated files. With respect to how to handle the include of filio.h and defining PGXC, I added in a pgxc_config.h.in file to handle both. This seems to be in line with how Postgres is handling these types of configs and only requires a simple ifdef for including filio.h. So I owe you a different patch for proxy_main.c as well. FYI, Matt -----Original Message----- From: Matt Warner Sent: Tuesday, October 08, 2013 1:45 PM To: 'Michael Paquier' Cc: 鈴木 幸市; Postgres-XC Developers; Koichi Suzuki Subject: RE: [Postgres-xc-developers] Minor Fixes Yes, I have autoconf in place and have been putting in time here and there to update the configure.in. I've also been looking at using autoconf to check for the presence of filio.h and go that route instead of specifying "__SOLARIS__". That would seem to be more portable. Matt -----Original Message----- From: Michael Paquier [mailto:mic...@gm...] Sent: Tuesday, October 08, 2013 5:33 AM To: Matt Warner Cc: 鈴木 幸市; Postgres-XC Developers; Koichi Suzuki Subject: Re: [Postgres-xc-developers] Minor Fixes On Tue, Oct 8, 2013 at 9:03 PM, Michael Paquier <mic...@gm...> wrote: > 3 - Patch for makesgml I pushed 3 as it was a trivial change as well, remains 1 and 2. Matt, are you able to get an environment with autoconf 2.63? I cannot recall on the top of my head an environment that has it... Ubuntu or CentOS maintain autoconf archives, no? -- Michael |
From: Matt W. <MW...@XI...> - 2013-10-08 20:45:33
|
Yes, I have autoconf in place and have been putting in time here and there to update the configure.in. I've also been looking at using autoconf to check for the presence of filio.h and go that route instead of specifying "__SOLARIS__". That would seem to be more portable. Matt -----Original Message----- From: Michael Paquier [mailto:mic...@gm...] Sent: Tuesday, October 08, 2013 5:33 AM To: Matt Warner Cc: 鈴木 幸市; Postgres-XC Developers; Koichi Suzuki Subject: Re: [Postgres-xc-developers] Minor Fixes On Tue, Oct 8, 2013 at 9:03 PM, Michael Paquier <mic...@gm...> wrote: > 3 - Patch for makesgml I pushed 3 as it was a trivial change as well, remains 1 and 2. Matt, are you able to get an environment with autoconf 2.63? I cannot recall on the top of my head an environment that has it... Ubuntu or CentOS maintain autoconf archives, no? -- Michael |
From: Michael P. <mic...@gm...> - 2013-10-08 12:33:06
|
On Tue, Oct 8, 2013 at 9:03 PM, Michael Paquier <mic...@gm...> wrote: > 3 - Patch for makesgml I pushed 3 as it was a trivial change as well, remains 1 and 2. Matt, are you able to get an environment with autoconf 2.63? I cannot recall on the top of my head an environment that has it... Ubuntu or CentOS maintain autoconf archives, no? -- Michael |
From: Michael P. <mic...@gm...> - 2013-10-08 12:03:33
|
I just finished working on the patch for GTM-Proxy and directly pushed it as it was trivial. I also decided to backpatch it as there were some incorrectness in the code. The following things are still missing: 1 - Change configure.in and regenerate configure with autoconf (addition of a flag for Solaris, don't like much this method but I couldn't come up with smth better) 2 - Patch for pgxcnode.c 3 - Patch for makesgml I'll work on the patch for makesgml next and post it here for review. Regards, -- Michael |
From: Ashutosh B. <ash...@en...> - 2013-10-08 08:55:48
|
It looks like having multiple connections to the datanodes from different coordinators could be a problem if there are writes involved. I am checking if 1.B.ii below is feasible. I remember, Michael had implementing some way by which rows can be sent to and fro between coordinator and the datanode for implementing alter table distribute by. It might be possible to use the same protocol. Michael, can you please point out relevant code in as much details as possible? B. Population of materialized view (REFRESH MV command) > There are two approaches here. > i. REFRESH MV command would be sent to each of the coordinators, and > each of > the coordinator would populate its MV table separately. This means > that each > of the coordinators would fire the same query and would get the same > result, > which is waste of resources. In a sane MV implementation we won't > expect MVs > to be refreshed frequently, and thus having this wastage once in a > while > would not be an overhead. Given that this approach needs very small > efforts, > it might be acceptable in version 1. The planner code is sprinkled > with > !IsConnFromCoord()? where we do not create RemoteQueryPaths for > remote > relations. For population of MV, we need to lift this restriction. I > am not sure how > to distinguish normal scans from scans for refreshing MV. > > ii. REFRESH MV on the coordinator where the REFRESH command is > issued and > send the data using COPY or some bulk protocols to the other > coordinators. > This approach needs some extra efforts for constructing the COPY > command > and the corresponding data file to be sent over to the other > coordinators. > Also, it needs some changes to REFRESH MATERIALIZED handling so that > such > data can be sent along with it. We may try this approach in version > 2. > > -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company |
From: Ahsan H. <ahs...@en...> - 2013-10-07 14:42:37
|
Thanks Masataka. Can you create a source-forge ticket for this please? Attach a reproducible test case with the ticket. On Thu, Oct 3, 2013 at 2:45 PM, Masataka Saito <pg...@gm...> wrote: > Hello, all. > > I found Postgres-XC returns different error code from PostgreSQL's one > for repeated rollback, and it makes a pgjdbc's regression test case > fail. > > PostgreSQL returns error code 42704 which is defined as > ERRCODE_UNDEFINED_OBJECT. It is maybe returned in > src/backend/access/transam/twophase.c::LockGXact. > Postgres-XC returns error code XX000 which is defined as > ERRCODE_INTERNAL_ERROR It is maybe returned in > src/backend/pgxc/pool/execRemote.c::FinishRemotePreparedTransaction. > > Error messages are quite similar. I will show it at the next line. > > prepared transaction with identifier > "0_8fHx8fF5/OCyZd9xZmJ2MSPCE1olci037CNQYoXoVCKbi63JcZIoUeJxU7PKl/t4qI4xV1agFPE+9gRrzheGHA==_BAUGAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA==" > does not exist. > > Does anyone know ... > * Why they have different error codes, and is it reasonable? > * To fix the code affects other components? > > You can reproduce using pgjdbc's regression test. The source code of > the test is org.postgresql.test.xa.XADataSourceTest.testRepeatedRolledBack. > > > http://sourceforge.net/p/postgres-xc/pgjdbc-xc/ci/master/tree/org/postgresql/test/xa/XADataSourceTest.java#l331 > > Regards. > > > ------------------------------------------------------------------------------ > October Webinars: Code for Performance > Free Intel webinars can help you accelerate application performance. > Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most > from > the latest Intel processors and coprocessors. See abstracts and register > > http://pubads.g.doubleclick.net/gampad/clk?id=60134791&iu=/4140/ostg.clktrk > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > -- Ahsan Hadi Snr Director Product Development EnterpriseDB Corporation The Enterprise Postgres Company Phone: +92-51-8358874 Mobile: +92-333-5162114 Website: www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message. |
From: Koichi S. <koi...@gm...> - 2013-10-07 09:38:49
|
Now PostgreSQL comes with event triggers, I think we should consider to support it at our next major release. Here's my idea for the first version implementation. Any suggestions/ideas are welcome. 1. Event trigger definitions in pg_event_trigger catalog itself will be propagated to all the nodes. We can use existing infrastructure for this (maybe we can exclude datanodes from such propagation though...) 2. Event trigger will be fired only at the originating coordinator. Trigger function can get XC cluster configuration and determine what they should do with other nodes if necessary. It makes event trigger function design simpler. Regards; --- Koichi Suzuki |
From: Koichi S. <koi...@gm...> - 2013-10-04 02:28:07
|
Thanks Ashutosh for the quick and nice work. Please find my comment inline. Regards; --- Koichi Suzuki 2013/10/3 Ashutosh Bapat <ash...@en...> > Hi All, > I am working on supporting MVs in XC. Here are some notes I have written. > Please provide your comments. I am leaning towards creating MVs at the > coordinators. > > For any materialized views (called MV henceforth) implementation, we need > to > implement following functionality > 1. Creation of MV (bare creation without population) > 2. Population of MV > 3. Querying MV > 4. Dropping MV > 5. Alter MV > 6. Creating Indexes on MV > > Following write-up discussed various approaches for implementing MVs in XC > 1. Create materialised view only on the coordinators > While creating the materialised view, the CREATE MATERIALIZED VIEW > statement is > turned into a CTAS query. The new table is created as a materialised > view table. > In XC, CTAS is implemented by rewriting the statement as CREATE TABLE > ...; INSERT INTO > .... Hence we will need to stop the MV's CTAS being rewritten. > > A. Creation of MV > At the time of creation of the MV table, we will need to create the > table > locally at the coordinator. The subsequent heap_insert is expected to > automatically populate this local table. By propagating Create MV > statement > to each of the coordinator (and not the datanode/s) we will get the > same > effect. More details below. > We will need to set the distribution info of MV to NULL to indicate > that > it's a local table. > Given that the catalog tables are created locally on each of the > coordinators, we should be able to CREATE MV locally on the > coordinators. > > B. Population of materialized view (REFRESH MV command) > There are two approaches here. > i. REFRESH MV command would be sent to each of the coordinators, and > each of > the coordinator would populate its MV table separately. This means > that each > of the coordinators would fire the same query and would get the same > result, > which is waste of resources. In a sane MV implementation we won't > expect MVs > to be refreshed frequently, and thus having this wastage once in a > while > would not be an overhead. Given that this approach needs very small > efforts, > it might be acceptable in version 1. The planner code is sprinkled > with > !IsConnFromCoord()? where we do not create RemoteQueryPaths for > remote > relations. For population of MV, we need to lift this restriction. I > am not sure how > to distinguish normal scans from scans for refreshing MV. > Especially in DDL handling, this IsConnFromCoord() is widely used from place to place for specific needs. So in REFRESH MV command handling, you can control what to do in such a manner. utility.c contains such things. Because master_pg93_merge is under the work and utility.c may need some more correction, it may be better to look at REL1_1_STABLE or master. > > ii. REFRESH MV on the coordinator where the REFRESH command is > issued and > send the data using COPY or some bulk protocols to the other > coordinators. > This approach needs some extra efforts for constructing the COPY > command > and the corresponding data file to be sent over to the other > coordinators. > Also, it needs some changes to REFRESH MATERIALIZED handling so that > such > data can be sent along with it. We may try this approach in version > 2. > I agree on this. > > C. Querying MV > Nothing more needs to be done here. In most of the places, we have > treated > tables with no distribution info as local tables. Hence this won't be > difficult. There might be corner cases, testing will be required for > such > corner cases. > OK. I think this is very important step toward coordinator/datanode integration. > > D. ALTER MV > I don't think there is any change required here. We will need to > test it > to make sure that the things are working as expected. We should > propagate > the ALTER MV command to all the coordinators and not to the > datanodes. > I'm afraid native code for ALTER MV does not allow to run inside 2PC or transaction block, as ALTER TYPE ... ADD. If so, we should check this only at initiated node as done in utility.c and typecmds.c, such as #ifdef PGXC /* * We disallow this in transaction blocks, because we can't cope * with enum OID values getting into indexes and then having their * defining pg_enum entries go away. */ /* Allow this to be run inside transaction block on remote nodes */ if (IS_PGXC_COORDINATOR && !IsConnFromCoord()) #endif PreventTransactionChain(isTopLevel, "ALTER TYPE ... ADD"); > > E. Creating indexes on MV > Given that we can create indexes on the catalog tables, it should be > possible to create indexes on the MV tables as well. Testing will > need to > be done to make sure that this works. CREATE INDEX command will need > to be > propagated to all the coordinators and not to the datanodes. > Yes, we need this change. > > Materialized views are a query caching technique. Usually a query would > be > served by a single materialized view or multiple of them. So, having > them > created on the coordinator/s reduces one trip to the datanode. This > will also > allow us to take a step towards coordinator - datanode merge. > At present, this means we cannot ship joins with MVIEW. I hope it is acceptable as the first implementation. > > 2. Create MV definition on both the coordinator and datanode but keep the > data on the datanode/s > While creating the materialised view, the CREATE MATERIALIZED VIEW > statement is > turned into a CTAS query. The new table is created as a materialised > view table. > In XC, CTAS is implemented by rewriting the statement as CREATE TABLE > ...; INSERT INTO > .... We need to filter MVs out of this rewriting. > > A. Creating MV > Pass MV definition to the datanode and all the coordinators. This > would be > done in two phases. First we create just the definition on all the > nodes. > In the second phase, the MV table created on the datanode will be > populated using method below. > > B. Populating an MV > We need to device a special INSERT INTO protocol which would run the > associated query on the initiating coordinator and insert the > results into > the MV table at the datanodes. REFRESH MV would be turned into > INSERT INTO > at the initiating coordinator and each other coordinator would get > REFRESH > MV command so that the MV would be set as scanable without causing > the > query to be fired. > > C. Querying MV > Like normal tables, we will have to create corresponding > REMOTE_QUERY_PATH for MV relations. > > D. ALTER MV > Passing down ALTER MV to the datanodes should work. But it needs some > testing to make sure that ALTER MV is working fine. > Please see the comment above. > > E. Creating indexes on MV > This looks straight forward, since we can create indexes on the > datanodes. > Needs some testing. > OK. > > If we create MV on the datanodes, and do not allow distribution to be > specified, > those MVs need to be replicated (since they are stable by definition) by > default > as against the default distribution by hash. Given that materialized views > are > query caching technique, we expect that a query would be entirely served > by a > materialized view or multiple of those. In such case, having materialized > views > on datanode/s would create unnecessary hops from coordinator to the > datanode. > > Distributing MV > --------------- > If we need to create materialized view only on selected nodes > (coordinators/datanodes) we will need to > a. extend the CREATE/ALTER MV syntax to specify nodes where to create > the MV. > b. disallow any REFRESH on the nodes where the MV does not exist. > We may take up this extension in version 2. > I think it's okay as the first implementation. There's two options for internal. i) Maintain MVIEW in pgxc_class or other catalog, with full distribution information, ii) Not having any pgxc-specific catalogs and hard code version-1 specific assumption. This could be determined by required resource. Anyway, we need option i) to control the distribution at the next improvement. > > What happens to an MV when a node is added or dropped? > Ouch! Yes, it is very important point to consider. Assuming the option ii) above, because all the catalogues are copied when a node is added, we can just REFRESH it. When nodes are dropped, maybe we don't have to do anything. If we take the option i) above, we need to provide ALTER MVIEW ... ADD NODE and ALTER MVIEW DELETE NODE to maintain the distribution and we should redistribute MVIEW to the new set of nodes. This is similar to ALTER TABLE approach. ------------------------------------------------------ > We need to test. Having MV at the coordinator helps here, since addition or > removal of datanode doesn't affect the MV. While adding a coordinator, the > catalog replication itself would create the MV. Dropping a node wouldn't > be a > problem. We need to test the feature. > > > -- > Best Wishes, > Ashutosh Bapat > EnterpriseDB Corporation > The Postgres Database Company > > > ------------------------------------------------------------------------------ > October Webinars: Code for Performance > Free Intel webinars can help you accelerate application performance. > Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most > from > the latest Intel processors and coprocessors. See abstracts and register > > http://pubads.g.doubleclick.net/gampad/clk?id=60134791&iu=/4140/ostg.clktrk > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > |
From: Ashutosh B. <ash...@en...> - 2013-10-03 12:33:53
|
Hi All, I am working on supporting MVs in XC. Here are some notes I have written. Please provide your comments. I am leaning towards creating MVs at the coordinators. For any materialized views (called MV henceforth) implementation, we need to implement following functionality 1. Creation of MV (bare creation without population) 2. Population of MV 3. Querying MV 4. Dropping MV 5. Alter MV 6. Creating Indexes on MV Following write-up discussed various approaches for implementing MVs in XC 1. Create materialised view only on the coordinators While creating the materialised view, the CREATE MATERIALIZED VIEW statement is turned into a CTAS query. The new table is created as a materialised view table. In XC, CTAS is implemented by rewriting the statement as CREATE TABLE ...; INSERT INTO .... Hence we will need to stop the MV's CTAS being rewritten. A. Creation of MV At the time of creation of the MV table, we will need to create the table locally at the coordinator. The subsequent heap_insert is expected to automatically populate this local table. By propagating Create MV statement to each of the coordinator (and not the datanode/s) we will get the same effect. More details below. We will need to set the distribution info of MV to NULL to indicate that it's a local table. Given that the catalog tables are created locally on each of the coordinators, we should be able to CREATE MV locally on the coordinators. B. Population of materialized view (REFRESH MV command) There are two approaches here. i. REFRESH MV command would be sent to each of the coordinators, and each of the coordinator would populate its MV table separately. This means that each of the coordinators would fire the same query and would get the same result, which is waste of resources. In a sane MV implementation we won't expect MVs to be refreshed frequently, and thus having this wastage once in a while would not be an overhead. Given that this approach needs very small efforts, it might be acceptable in version 1. The planner code is sprinkled with !IsConnFromCoord()? where we do not create RemoteQueryPaths for remote relations. For population of MV, we need to lift this restriction. I am not sure how to distinguish normal scans from scans for refreshing MV. ii. REFRESH MV on the coordinator where the REFRESH command is issued and send the data using COPY or some bulk protocols to the other coordinators. This approach needs some extra efforts for constructing the COPY command and the corresponding data file to be sent over to the other coordinators. Also, it needs some changes to REFRESH MATERIALIZED handling so that such data can be sent along with it. We may try this approach in version 2. C. Querying MV Nothing more needs to be done here. In most of the places, we have treated tables with no distribution info as local tables. Hence this won't be difficult. There might be corner cases, testing will be required for such corner cases. D. ALTER MV I don't think there is any change required here. We will need to test it to make sure that the things are working as expected. We should propagate the ALTER MV command to all the coordinators and not to the datanodes. E. Creating indexes on MV Given that we can create indexes on the catalog tables, it should be possible to create indexes on the MV tables as well. Testing will need to be done to make sure that this works. CREATE INDEX command will need to be propagated to all the coordinators and not to the datanodes. Materialized views are a query caching technique. Usually a query would be served by a single materialized view or multiple of them. So, having them created on the coordinator/s reduces one trip to the datanode. This will also allow us to take a step towards coordinator - datanode merge. 2. Create MV definition on both the coordinator and datanode but keep the data on the datanode/s While creating the materialised view, the CREATE MATERIALIZED VIEW statement is turned into a CTAS query. The new table is created as a materialised view table. In XC, CTAS is implemented by rewriting the statement as CREATE TABLE ...; INSERT INTO .... We need to filter MVs out of this rewriting. A. Creating MV Pass MV definition to the datanode and all the coordinators. This would be done in two phases. First we create just the definition on all the nodes. In the second phase, the MV table created on the datanode will be populated using method below. B. Populating an MV We need to device a special INSERT INTO protocol which would run the associated query on the initiating coordinator and insert the results into the MV table at the datanodes. REFRESH MV would be turned into INSERT INTO at the initiating coordinator and each other coordinator would get REFRESH MV command so that the MV would be set as scanable without causing the query to be fired. C. Querying MV Like normal tables, we will have to create corresponding REMOTE_QUERY_PATH for MV relations. D. ALTER MV Passing down ALTER MV to the datanodes should work. But it needs some testing to make sure that ALTER MV is working fine. E. Creating indexes on MV This looks straight forward, since we can create indexes on the datanodes. Needs some testing. If we create MV on the datanodes, and do not allow distribution to be specified, those MVs need to be replicated (since they are stable by definition) by default as against the default distribution by hash. Given that materialized views are query caching technique, we expect that a query would be entirely served by a materialized view or multiple of those. In such case, having materialized views on datanode/s would create unnecessary hops from coordinator to the datanode. Distributing MV --------------- If we need to create materialized view only on selected nodes (coordinators/datanodes) we will need to a. extend the CREATE/ALTER MV syntax to specify nodes where to create the MV. b. disallow any REFRESH on the nodes where the MV does not exist. We may take up this extension in version 2. What happens to an MV when a node is added or dropped? ------------------------------------------------------ We need to test. Having MV at the coordinator helps here, since addition or removal of datanode doesn't affect the MV. While adding a coordinator, the catalog replication itself would create the MV. Dropping a node wouldn't be a problem. We need to test the feature. -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company |