Thread: pg_reorg in core?
Hi all,<br /><br />During the last PGCon, I heard that some community members would be interested in having pg_reorg directlyin core.<br />Just to recall, pg_reorg is a functionality developped by NTT that allows to redistribute a table withouttaking locks on it.<br /> The technique it uses to reorganize the table is to create a temporary copy of the tableto be redistributed with a CREATE TABLE AS<br />whose definition changes if table is redistributed with a VACUUM FULLor CLUSTER.<br />Then it follows this mechanism:<br /> - triggers are created to redirect all the DMLs that occur onthe table to an intermediate log table.<br />- creation of indexes on the temporary table based on what the user wishes<br/>- Apply the logs registered during the index creation<br /> - Swap the names of freshly created table and oldtable<br />- Drop the useless objects<br /><br />The code is hosted by pg_foundry here: <a href="http://pgfoundry.org/projects/reorg/">http://pgfoundry.org/projects/reorg/</a>.<br/> I am also maintaining a fork ingithub in sync with pgfoundry here: <a href="https://github.com/michaelpq/pg_reorg">https://github.com/michaelpq/pg_reorg</a>.<br/><br />Just, do you guys thinkit is worth adding a functionality like pg_reorg in core or not?<br /><br />If yes, well I think the code of pg_reorgis going to need some modifications to make it more compatible with contrib modules using only EXTENSION.<br />Forthe time being pg_reorg is divided into 2 parts, binary and library.<br /> The library part is the SQL portion of pg_reorg,containing a set of C functions that are called by the binary part. This has been extended to support CREATE EXTENSIONrecently.<br />The binary part creates a command pg_reorg in charge of calling the set of functions created by thelib part, being just a wrapper of the library part to control the creation and deletion of the objects.<br /> It is alsoin charge of deleting the temporary objects by callback if an error occurs.<br /><br />By using the binary command, itis possible to reorganize a single table or a database, in this case reorganizing a database launches only a loop on eachtable of this database.<br /><br />My idea is to remove the binary part and to rely only on the library part to makepg_reorg a single extension with only system functions like other contrib modules.<br />In order to do that what is missingis a function that could be used as an entry point for table reorganization, a function of the type pg_reorg_table(tableoid)and pg_reorg_table(tableoid, text).<br /> All the functionalities of pg_reorg could be reproducible:<br/>- pg_reorg_table(tableoid) for a VACUUM FULL reorganization<br />- pg_reorg_table(tableoid, NULL) for aCLUSTER reorganization if table has a CLUSTER key<br />- pg_reorg_table(tableoid, columnname) for a CLUSTER reorganizationbased on a wanted column.<br /><br />Is it worth the shot?<br /><br />Regards,<br />-- <br />Michael Paquier<br/><a href="http://michael.otacoo.com" target="_blank">http://michael.otacoo.com</a><br />
On Thu, Sep 20, 2012 at 7:05 PM, Michael Paquier <michael.paquier@gmail.com> wrote: > Hi all, > > During the last PGCon, I heard that some community members would be > interested in having pg_reorg directly in core. I'm actually not crazy about this idea, at least not given the current state of pg_reorg. Right now, there are a quite a few fixes and features which remain to be merged in to cvs head, but at least we can develop pg_reorg on a schedule independent of Postgres itself, i.e. we can release new features more often than once a year. Perhaps when pg_reorg is more stable, and the known bugs and missing features have been ironed out, we could think about integrating into core. Granted, a nice thing about integrating with core is we'd probably have more of an early warning when reshuffling of PG breaks pg_reorg (e.g. the recent splitting of the htup headers), but such changes have been quick and easy to fix so far. > Just to recall, pg_reorg is a functionality developped by NTT that allows to > redistribute a table without taking locks on it. > The technique it uses to reorganize the table is to create a temporary copy > of the table to be redistributed with a CREATE TABLE AS > whose definition changes if table is redistributed with a VACUUM FULL or > CLUSTER. > Then it follows this mechanism: > - triggers are created to redirect all the DMLs that occur on the table to > an intermediate log table. N.B. CREATE TRIGGER takes an AccessExclusiveLock on the table, see below. > - creation of indexes on the temporary table based on what the user wishes > - Apply the logs registered during the index creation > - Swap the names of freshly created table and old table > - Drop the useless objects > > The code is hosted by pg_foundry here: http://pgfoundry.org/projects/reorg/. > I am also maintaining a fork in github in sync with pgfoundry here: > https://github.com/michaelpq/pg_reorg. > > Just, do you guys think it is worth adding a functionality like pg_reorg in > core or not? > > If yes, well I think the code of pg_reorg is going to need some > modifications to make it more compatible with contrib modules using only > EXTENSION. > For the time being pg_reorg is divided into 2 parts, binary and library. > The library part is the SQL portion of pg_reorg, containing a set of C > functions that are called by the binary part. This has been extended to > support CREATE EXTENSION recently. > The binary part creates a command pg_reorg in charge of calling the set of > functions created by the lib part, being just a wrapper of the library part > to control the creation and deletion of the objects. > It is also in charge of deleting the temporary objects by callback if an > error occurs. > > By using the binary command, it is possible to reorganize a single table or > a database, in this case reorganizing a database launches only a loop on > each table of this database. > > My idea is to remove the binary part and to rely only on the library part to > make pg_reorg a single extension with only system functions like other > contrib modules. > In order to do that what is missing is a function that could be used as an > entry point for table reorganization, a function of the type > pg_reorg_table(tableoid) and pg_reorg_table(tableoid, text). > All the functionalities of pg_reorg could be reproducible: > - pg_reorg_table(tableoid) for a VACUUM FULL reorganization > - pg_reorg_table(tableoid, NULL) for a CLUSTER reorganization if table has a > CLUSTER key > - pg_reorg_table(tableoid, columnname) for a CLUSTER reorganization based on > a wanted column. > > Is it worth the shot? I haven't seen this documented as such, but AFAICT the reason that pg_reorg is split into a binary and set of backend functions which are called by the binary is that pg_reorg needs to be able to control its steps in several transactions so as to avoid holding locks excessively. The reorg_one_table() function uses four or five transactions per table, in fact. If all the logic currently in the pg_reorg binary were moved into backend functions, calling pg_reorg_table() would have to be a single transaction, and there would be no advantage to using such a function vs. CLUSTER or VACUUM FULL. Also, having a separate binary we should be able to perform some neat tricks such as parallel index builds using multiple connections (I'm messing around with this idea now). AFAIK this would also not be possible if pg_reorg were contained solely in the library functions. Josh
On Fri, Sep 21, 2012 at 12:07 PM, Josh Kupershmidt <schmiddy@gmail.com> wrote:
-- On Thu, Sep 20, 2012 at 7:05 PM, Michael PaquierI'm actually not crazy about this idea, at least not given the current
<michael.paquier@gmail.com> wrote:
> Hi all,
>
> During the last PGCon, I heard that some community members would be
> interested in having pg_reorg directly in core.
state of pg_reorg. Right now, there are a quite a few fixes and
features which remain to be merged in to cvs head, but at least we can
develop pg_reorg on a schedule independent of Postgres itself, i.e. we
can release new features more often than once a year. Perhaps when
pg_reorg is more stable, and the known bugs and missing features have
been ironed out, we could think about integrating into core.
What could be also great is to move the project directly into github to facilitate its maintenance and development.
My own copy is based and synced on what is in pgfoundry as I don't own any admin access to on pgfoundry (honestly don't think I can get one either), even if I am from NTT. Hey, some people with admin rights here?
My own copy is based and synced on what is in pgfoundry as I don't own any admin access to on pgfoundry (honestly don't think I can get one either), even if I am from NTT. Hey, some people with admin rights here?
Granted, a nice thing about integrating with core is we'd probably
have more of an early warning when reshuffling of PG breaks pg_reorg
(e.g. the recent splitting of the htup headers), but such changes have
been quick and easy to fix so far.
Yes, that is also why I am proposing to integrate it into core. Its maintenance pace would be faster and easier than it is now in pgfoundry. However, if hackers do not think that it is worth adding it to core... Well separate development as done now would be fine but slower...
Also, just by watching the extension modules in contrib, I haven't seen one using both the library and binary at the same time like pg_reorg does.
Also, just by watching the extension modules in contrib, I haven't seen one using both the library and binary at the same time like pg_reorg does.
> - creation of indexes on the temporary table based on what the user wishesI haven't seen this documented as such, but AFAICT the reason that> - Apply the logs registered during the index creation
> - Swap the names of freshly created table and old table
> - Drop the useless objects
>
> The code is hosted by pg_foundry here: http://pgfoundry.org/projects/reorg/.
> I am also maintaining a fork in github in sync with pgfoundry here:
> https://github.com/michaelpq/pg_reorg.
>
> Just, do you guys think it is worth adding a functionality like pg_reorg in
> core or not?
>
> If yes, well I think the code of pg_reorg is going to need some
> modifications to make it more compatible with contrib modules using only
> EXTENSION.
> For the time being pg_reorg is divided into 2 parts, binary and library.
> The library part is the SQL portion of pg_reorg, containing a set of C
> functions that are called by the binary part. This has been extended to
> support CREATE EXTENSION recently.
> The binary part creates a command pg_reorg in charge of calling the set of
> functions created by the lib part, being just a wrapper of the library part
> to control the creation and deletion of the objects.
> It is also in charge of deleting the temporary objects by callback if an
> error occurs.
>
> By using the binary command, it is possible to reorganize a single table or
> a database, in this case reorganizing a database launches only a loop on
> each table of this database.
>
> My idea is to remove the binary part and to rely only on the library part to
> make pg_reorg a single extension with only system functions like other
> contrib modules.
> In order to do that what is missing is a function that could be used as an
> entry point for table reorganization, a function of the type
> pg_reorg_table(tableoid) and pg_reorg_table(tableoid, text).
> All the functionalities of pg_reorg could be reproducible:
> - pg_reorg_table(tableoid) for a VACUUM FULL reorganization
> - pg_reorg_table(tableoid, NULL) for a CLUSTER reorganization if table has a
> CLUSTER key
> - pg_reorg_table(tableoid, columnname) for a CLUSTER reorganization based on
> a wanted column.
>
> Is it worth the shot?
pg_reorg is split into a binary and set of backend functions which are
called by the binary is that pg_reorg needs to be able to control its
steps in several transactions so as to avoid holding locks
excessively. The reorg_one_table() function uses four or five
transactions per table, in fact. If all the logic currently in the
pg_reorg binary were moved into backend functions, calling
pg_reorg_table() would have to be a single transaction, and there
would be no advantage to using such a function vs. CLUSTER or VACUUM
FULL.
Of course, but functionalities like CREATE INDEX CONCURRENTLY use multiple transactions. Couldn't it be possible to use something similar to make the modifications visible to other backends?
Interesting idea, this could accelerate the whole process. I am just wondering about possible consistency issues like the logs being replayed before swap.
Also, having a separate binary we should be able to perform some neat
tricks such as parallel index builds using multiple connections (I'm
messing around with this idea now). AFAIK this would also not be
possible if pg_reorg were contained solely in the library functions.
Michael Paquier
http://michael.otacoo.com
On Thu, Sep 20, 2012 at 7:05 PM, Michael Paquier <michael.paquier@gmail.com> wrote: > Hi all, > > During the last PGCon, I heard that some community members would be > interested in having pg_reorg directly in core. > Just to recall, pg_reorg is a functionality developped by NTT that allows to > redistribute a table without taking locks on it. > The technique it uses to reorganize the table is to create a temporary copy > of the table to be redistributed with a CREATE TABLE AS > whose definition changes if table is redistributed with a VACUUM FULL or > CLUSTER. > Then it follows this mechanism: > - triggers are created to redirect all the DMLs that occur on the table to > an intermediate log table. > - creation of indexes on the temporary table based on what the user wishes > - Apply the logs registered during the index creation > - Swap the names of freshly created table and old table > - Drop the useless objects > I'm not familiar with pg_reorg, but I wonder why we need a separate program for this task. I know pg_reorg is ok as an external program per se, but if we could optimize CLUSTER (or VACUUM which I'm a little pessimistic about) in the same way, it's much nicer than having additional binary + extension. Isn't it possible to do the same thing above within the CLUSTER command? Maybe CLUSTER .. CONCURRENTLY? Thanks, -- Hitoshi Harada
On Thu, Sep 20, 2012 at 8:33 PM, Michael Paquier <michael.paquier@gmail.com> wrote: > On Fri, Sep 21, 2012 at 12:07 PM, Josh Kupershmidt <schmiddy@gmail.com> > wrote: >> >> On Thu, Sep 20, 2012 at 7:05 PM, Michael Paquier >> <michael.paquier@gmail.com> wrote: > What could be also great is to move the project directly into github to > facilitate its maintenance and development. No argument from me there, especially as I have my own fork in github, but that's up to the current maintainers. >> Granted, a nice thing about integrating with core is we'd probably >> have more of an early warning when reshuffling of PG breaks pg_reorg >> (e.g. the recent splitting of the htup headers), but such changes have >> been quick and easy to fix so far. > > Yes, that is also why I am proposing to integrate it into core. Its > maintenance pace would be faster and easier than it is now in pgfoundry. If the argument for moving pg_reorg into core is "faster and easier" development, well I don't really buy that. Yes, there would presumably be more eyeballs on the project, but you could make the same argument about any auxiliary Postgres project which wants more attention, and we can't have everything in core. And I fail to see how being in-core makes development "easier"; I think everyone here would agree that the bar to commit things to core is pretty darn high. If you're concerned about the [lack of] development on pg_reorg, there are plenty of things to fix without moving the project. I recently posted an "issues roundup" to the reorg list, if you are interested in pitching in. Josh
On Fri, Sep 21, 2012 at 5:17 AM, Josh Kupershmidt <schmiddy@gmail.com> wrote: > If the argument for moving pg_reorg into core is "faster and easier" > development, well I don't really buy that. I don't see any problem in having pg_reorg in PGXN instead. I've tried adding a META.json to the project and it seems working fine with the pgxn client. It is together with other patches in my own github fork. https://github.com/dvarrazzo/pg_reorg/ I haven't submitted it to PGXN as I prefer the original author to keep the ownership. -- Daniele
On Fri, Sep 21, 2012 at 9:33 PM, Daniele Varrazzo <daniele.varrazzo@gmail.com> wrote:
-- On Fri, Sep 21, 2012 at 5:17 AM, Josh Kupershmidt <schmiddy@gmail.com> wrote:I don't see any problem in having pg_reorg in PGXN instead.
> If the argument for moving pg_reorg into core is "faster and easier"
> development, well I don't really buy that.
I've tried adding a META.json to the project and it seems working fine
with the pgxn client. It is together with other patches in my own
github fork.
https://github.com/dvarrazzo/pg_reorg/
I haven't submitted it to PGXN as I prefer the original author to keep
the ownership.
Thanks, I merged your patches with the dev branch for the time being.
It would be great to have some input from the maintainers of pg_reorg in pgfoundry to see if they agree about putting it in pgxn.
It would be great to have some input from the maintainers of pg_reorg in pgfoundry to see if they agree about putting it in pgxn.
Michael Paquier
http://michael.otacoo.com
On Fri, Sep 21, 2012 at 1:00 PM, Hitoshi Harada <umi.tanuki@gmail.com> wrote:
-- I'm not familiar with pg_reorg, but I wonder why we need a separate
program for this task. I know pg_reorg is ok as an external program
per se, but if we could optimize CLUSTER (or VACUUM which I'm a little
pessimistic about) in the same way, it's much nicer than having
additional binary + extension. Isn't it possible to do the same thing
above within the CLUSTER command? Maybe CLUSTER .. CONCURRENTLY?
CLUSTER might be more adapted in this case as the purpose is to reorder the table.
The same technique used by pg_reorg (aka table coupled with triggers) could lower the lock access of the table.
Also, it could be possible to control each sub-operation in the same fashion way as CREATE INDEX CONCURRENTLY.
By the way, whatever the operation, VACUUM or CLUSTER used, I got a couple of doubts:
1) isn't it be too costly for a core operation as pg_reorg really needs many temporary objects? Could be possible to reduce the number of objects created if added to core though...
2) Do you think the current CLUSTER is enough and are there wishes to implement such an optimization directly in core?
The same technique used by pg_reorg (aka table coupled with triggers) could lower the lock access of the table.
Also, it could be possible to control each sub-operation in the same fashion way as CREATE INDEX CONCURRENTLY.
By the way, whatever the operation, VACUUM or CLUSTER used, I got a couple of doubts:
1) isn't it be too costly for a core operation as pg_reorg really needs many temporary objects? Could be possible to reduce the number of objects created if added to core though...
2) Do you think the current CLUSTER is enough and are there wishes to implement such an optimization directly in core?
Michael Paquier
http://michael.otacoo.com
(2012/09/21 22:32), Michael Paquier wrote: > On Fri, Sep 21, 2012 at 9:33 PM, Daniele Varrazzo > <daniele.varrazzo@gmail.com <mailto:daniele.varrazzo@gmail.com>> wrote: > > On Fri, Sep 21, 2012 at 5:17 AM, Josh Kupershmidt > <schmiddy@gmail.com <mailto:schmiddy@gmail.com>> wrote: > > I haven't submitted it to PGXN as I prefer the original author to keep > the ownership. > > Thanks, I merged your patches with the dev branch for the time being. > It would be great to have some input from the maintainers of pg_reorg > in pgfoundry to see if they agree about putting it in pgxn. > Hi, I'm Sakamoto, reorg mainainer. I'm very happy Josh, Michael and Daniele are interested in reorg. I'm working on the next version of reorg 1.1.8, which will be released in a couple of days. And I come to think that it is a point to reconsider the way to develop/maintain. To be honest, we have little available development resources, so no additional features are added recently. But features and fixes to be done (as Josh sums up. thanks). I think it is a good idea to develop on github. Michael's repo is the root? After the release of 1.1.8, I will freeze CVS repository and create a mirror on github. # Or Michael's repo will do :) I have received some patches from Josh, Daniele. It should be developed in the next major version 1.2. So some of them may not be included in 1.1.8 (caz it's minor versionup), but I feel so appreciated. I think we can discuss further at reorg list. Sakamoto
On Sat, Sep 22, 2012 at 9:08 AM, sakamoto <dsakamoto@lolloo.net> wrote:
-- (2012/09/21 22:32), Michael Paquier wrote:Hi, I'm Sakamoto, reorg mainainer.On Fri, Sep 21, 2012 at 9:33 PM, Daniele Varrazzo <daniele.varrazzo@gmail.com <mailto:daniele.varrazzo@gmail.com>> wrote:
On Fri, Sep 21, 2012 at 5:17 AM, Josh Kupershmidt<schmiddy@gmail.com <mailto:schmiddy@gmail.com>> wrote:
I haven't submitted it to PGXN as I prefer the original author to keep
the ownership.
Thanks, I merged your patches with the dev branch for the time being.
It would be great to have some input from the maintainers of pg_reorg in pgfoundry to see if they agree about putting it in pgxn.
I'm very happy Josh, Michael and Daniele are interested in reorg.
I'm working on the next version of reorg 1.1.8, which will be released in a couple of days.
And I come to think that it is a point to reconsider the way to develop/maintain.
To be honest, we have little available development resources, so no additional
features are added recently. But features and fixes to be done (as Josh sums up. thanks).
I think it is a good idea to develop on github. Michael's repo is the root?
After the release of 1.1.8, I will freeze CVS repository and create a mirror on github.
# Or Michael's repo will do :)
As you wish. You could create a root folder based on a new organization, or on your own account, or use my repo.
The result will be the same. I let it at your appreciation
The result will be the same. I let it at your appreciation
I have received some patches from Josh, Daniele. It should be developed in the next
major version 1.2. So some of them may not be included in 1.1.8 (caz it's minor versionup),
but I feel so appreciated.
Great!
Michael Paquier
http://michael.otacoo.com
<p dir="ltr">If the present project is having a tough time doing enhancements, I should think it mighty questionable to tryto draw it into core, that presses it towards a group of already very busy developers.<p dir="ltr">On the other hand,if the present development efforts can be made more public, by having them take place in a more public repository, thatat least has potential to let others in the community see and participate. There are no guarantees, but privacy is liableto hurt.<p dir="ltr">I wouldn't expect any sudden huge influx of developers, but a steady visible stream of developmenteffort would be mighty useful to a "merge into core" argument. <p dir="ltr">A *lot* of projects are a lot likethis. On the Slony project, we have tried hard to maintain this sort of visibility. Steve Singer, Jan Wieck and I doour individual efforts on git repos visible at GitHub to ensure ongoing efforts aren't invisible inside a corporate repo. It hasn't led to any massive of extra developers, but I am always grateful to see Peter Eisentraut's bug reports.
(2012/09/22 10:02), Christopher Browne wrote: > > If the present project is having a tough time doing enhancements, I > should think it mighty questionable to try to draw it into core, that > presses it towards a group of already very busy developers. > > On the other hand, if the present development efforts can be made more > public, by having them take place in a more public repository, that at > least has potential to let others in the community see and > participate. There are no guarantees, but privacy is liable to hurt. > > I wouldn't expect any sudden huge influx of developers, but a steady > visible stream of development effort would be mighty useful to a > "merge into core" argument. > > A *lot* of projects are a lot like this. On the Slony project, we > have tried hard to maintain this sort of visibility. Steve Singer, > Jan Wieck and I do our individual efforts on git repos visible at > GitHub to ensure ongoing efforts aren't invisible inside a corporate > repo. It hasn't led to any massive of extra developers, but I am > always grateful to see Peter Eisentraut's bug reports. > Agreed. What reorg project needs first is transparency, including issue traking, bugs, listup todo items, clearfied release schedules, quarity assurance and so force. Only after all that done, the discussion to put them to core can be started. Until now, reorg is developed and maintained behind corporate repository. But now that its activity goes slow, what I should do as a maintainer is to try development process more public and finds someone to corporate with:) Sakamoto
Hi, I'm sakamoto, maintainer of reorg. >> What could be also great is to move the project directly into github to >> facilitate its maintenance and development. >No argument from me there, especially as I have my own fork in github, >but that's up to the current maintainers. Yup, I am thinking development on CVS(onPgfoundry) is a bit awkward for me and github would be a suitable place. To be honest, we have little available development resources, so no additional features are added recently. But features and fixes to be done piled up, which Josh sums up. In the short term, within this month I'll release minor versionup of reorg to support PostgreSQL 9.2. And I think it's the time to reconsider the way we maintain pg_reorg. It's happy that Josh and Michael are interested in reorg, and I wish you to be a maintainer :) I think we can discuss at reorg list. M.Sakamoto NTT OSS Center
(2012/09/22 11:01), sakamoto wrote: > (2012/09/22 10:02), Christopher Browne wrote: >> >> If the present project is having a tough time doing enhancements, I >> should think it mighty questionable to try to draw it into core, that >> presses it towards a group of already very busy developers. >> >> On the other hand, if the present development efforts can be made more >> public, by having them take place in a more public repository, that at >> least has potential to let others in the community see and >> participate. There are no guarantees, but privacy is liable to hurt. >> >> I wouldn't expect any sudden huge influx of developers, but a steady >> visible stream of development effort would be mighty useful to a >> "merge into core" argument. >> >> A *lot* of projects are a lot like this. On the Slony project, we >> have tried hard to maintain this sort of visibility. Steve Singer, >> Jan Wieck and I do our individual efforts on git repos visible at >> GitHub to ensure ongoing efforts aren't invisible inside a corporate >> repo. It hasn't led to any massive of extra developers, but I am >> always grateful to see Peter Eisentraut's bug reports. >> > > Agreed. What reorg project needs first is transparency, including > issue traking, bugs, listup todo items, clearfied release schedules, > quarity assurance and so force. > Only after all that done, the discussion to put them to core can be > started. > > Until now, reorg is developed and maintained behind corporate repository. > But now that its activity goes slow, what I should do as a maintainer is to > try development process more public and finds someone to corporate with:) I think it's time to consider some *umbrella project* for maintaining several small projects outside the core. As you pointed out, the problem here is that it's difficult to keep enough eyeballs and development resource on tiny projects outside the core. For examples, NTT OSSC has created lots of tools, but they're facing some difficulties to keep them being maintained because of their development resources. There're diffrent code repositories, different web sites, diffirent issus tracking system and different dev mailing lists, for different small projects. My xlogdump as well. Actually, that's the reason why it's difficult to keep enough eyeballs on small third-party projects. And also the reason why some developers want to push their tools into the core, isn't it? :) To solve this problem, I would like to have some umbrella project. It would be called "pg dba utils", or something like this. This umbrella project may contain several third-party tools (pg_reorg, pg_rman, pg_filedump, xlogdump, etc, etc...) as its sub-modules. And also it may have single web site, code repository, issue tracking system and developer mailing list in order to share its development resource for testing, maintening and releasing. I think it would help third-party projects keep enough eyeballs even outside the core. Of course, if a third-party project has faster pace on its development and enough eyeballs to maintain, it's ok to be an independent project. However when a tool have already got matured with less eyeballs, it needs to be merged into this umbrella project. Any comments? > > Sakamoto > > -- Satoshi Nagayasu <snaga@uptime.jp> Uptime Technologies, LLC. http://www.uptime.jp
2012/9/22 Satoshi Nagayasu <snaga@uptime.jp>: > (2012/09/22 11:01), sakamoto wrote: >> (2012/09/22 10:02), Christopher Browne wrote: >>> >>> If the present project is having a tough time doing enhancements, I >>> should think it mighty questionable to try to draw it into core, that >>> presses it towards a group of already very busy developers. >>> >>> On the other hand, if the present development efforts can be made more >>> public, by having them take place in a more public repository, that at >>> least has potential to let others in the community see and >>> participate. There are no guarantees, but privacy is liable to hurt. >>> >>> I wouldn't expect any sudden huge influx of developers, but a steady >>> visible stream of development effort would be mighty useful to a >>> "merge into core" argument. >>> >>> A *lot* of projects are a lot like this. On the Slony project, we >>> have tried hard to maintain this sort of visibility. Steve Singer, >>> Jan Wieck and I do our individual efforts on git repos visible at >>> GitHub to ensure ongoing efforts aren't invisible inside a corporate >>> repo. It hasn't led to any massive of extra developers, but I am >>> always grateful to see Peter Eisentraut's bug reports. >>> >> >> Agreed. What reorg project needs first is transparency, including >> issue traking, bugs, listup todo items, clearfied release schedules, >> quarity assurance and so force. >> Only after all that done, the discussion to put them to core can be >> started. >> >> Until now, reorg is developed and maintained behind corporate repository. >> But now that its activity goes slow, what I should do as a maintainer is to >> try development process more public and finds someone to corporate with:) > > I think it's time to consider some *umbrella project* for maintaining > several small projects outside the core. > > As you pointed out, the problem here is that it's difficult to keep > enough eyeballs and development resource on tiny projects outside > the core. > > For examples, NTT OSSC has created lots of tools, but they're facing > some difficulties to keep them being maintained because of their > development resources. There're diffrent code repositories, different > web sites, diffirent issus tracking system and different dev mailing > lists, for different small projects. My xlogdump as well. > > Actually, that's the reason why it's difficult to keep enough eyeballs > on small third-party projects. And also the reason why some developers > want to push their tools into the core, isn't it? :) > > To solve this problem, I would like to have some umbrella project. > It would be called "pg dba utils", or something like this. > This umbrella project may contain several third-party tools (pg_reorg, > pg_rman, pg_filedump, xlogdump, etc, etc...) as its sub-modules. > > And also it may have single web site, code repository, issue tracking > system and developer mailing list in order to share its development > resource for testing, maintening and releasing. I think it would help > third-party projects keep enough eyeballs even outside the core. > > Of course, if a third-party project has faster pace on its development > and enough eyeballs to maintain, it's ok to be an independent project. > However when a tool have already got matured with less eyeballs, > it needs to be merged into this umbrella project. > > Any comments? > good idea Pavel >> >> Sakamoto >> >> > > > -- > Satoshi Nagayasu <snaga@uptime.jp> > Uptime Technologies, LLC. http://www.uptime.jp > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Sep 21, 2012 at 9:45 AM, M.Sakamoto <sakamoto_masahiko_b1@lab.ntt.co.jp> wrote: > Hi, > I'm sakamoto, maintainer of reorg. > >>> What could be also great is to move the project directly into github to >>> facilitate its maintenance and development. >>No argument from me there, especially as I have my own fork in github, >>but that's up to the current maintainers. > Yup, I am thinking development on CVS(onPgfoundry) is a bit awkward for > me and github would be a suitable place. Hello Sakamoto-san I have created a "reorg" organization on github: https://github.com/reorg/ You are welcome to become one of the owners of the organization. I have already added Itagaki Takahiro as owner because he has a github account. If you open a github account or give me the email of one you own I will invite you as organization owner. Michael is also member of the organization. I have re-converted the original CVS repository as Michael's conversion was missing the commit email info, but I have rebased his commits on the new master. My intention is to track CVS commits into the cvs branch of the repos and merge them into the master, until official development is moved to git. The repository is at <https://github.com/reorg/pg_reorg>. Because I'm not sure yet about a few details (from the development model to the committers emails) it may be rebased in the near future, until everything has been decided. Thank you very much. -- Daniele
On Sat, 2012-09-22 at 16:25 +0900, Satoshi Nagayasu wrote: > I think it's time to consider some *umbrella project* for maintaining > several small projects outside the core. Well, that was pgfoundry, and it didn't work out.
On Sat, Sep 22, 2012 at 7:45 PM, Peter Eisentraut <peter_e@gmx.net> wrote: > On Sat, 2012-09-22 at 16:25 +0900, Satoshi Nagayasu wrote: >> I think it's time to consider some *umbrella project* for maintaining >> several small projects outside the core. > > Well, that was pgfoundry, and it didn't work out. There seem to be some efforts to update it, but yeah, the software behind it didn't age gracefully, and it seems doubtful to me that people will be flocking back to pgfoundry. The other ongoing attempt at an "umbrella" is PGXN, and it's different enough in approach that, while it's not obvious that it'll succeed, if it fails, the failure wouldn't involve the same set of issues that made pgfoundry problematic. PGXN notably captures metadata about the project; resources (e.g. - SCM) don't have to be kept there. -- When confronted by a difficult problem, solve it by reducing it to the question, "How would the Lone Ranger handle this?"
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 >> I think it's time to consider some *umbrella project* for maintaining >> several small projects outside the core. > > Well, that was pgfoundry, and it didn't work out. I'm not sure that is quite analogous to what was being proposed. I read it as more of "let's package a bunch of these small utilities together into a single project", such that installing one installs them all (e.g. aptitude install pg_tools), and they all have a single bug tracker, etc. That tracker could be github, of course. I'm not convinced of the merit of that plan, but that's an alternative interpretation that doesn't involve our beloved pgfoundry. :) Oh, and -1 for putting it in core. Way too early, and not important enough. - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 201209222334 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAlBeg/AACgkQvJuQZxSWSsjL5ACgimT71B4lSb1ELhgMw5EBzAKs xHIAn08vxGzmM6eSmDfZfxlJDTousq7h =KgXW -----END PGP SIGNATURE-----
2012/09/23 12:37, Greg Sabino Mullane wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: RIPEMD160 > > >>> I think it's time to consider some *umbrella project* for maintaining >>> several small projects outside the core. >> >> Well, that was pgfoundry, and it didn't work out. > > I'm not sure that is quite analogous to what was being proposed. > I read it as more of "let's package a bunch of these small utilities > together into a single project", such that installing one installs them > all (e.g. aptitude install pg_tools), and they all have a single bug > tracker, etc. That tracker could be github, of course. Exactly --- I do not care the SCM system though. :) > I'm not convinced of the merit of that plan, but that's an alternative > interpretation that doesn't involve our beloved pgfoundry. :) For example, xlogdump had not been maintained for 5 years when I picked it up last year. And the latest pg_filedump that supports 9.2 has not been released yet. pg_reorg as well. If those tools are in a single project, it would be easier to keep attention on it. Then, developers can easily build *all of them* at once, fix them, and post any patch on the single mailing list. Actually, it would save developers from waisting their time. From my viewpoint, it's not just a SCM or distributing issue. It's about how to survive for such small projects around the core even if these could not come in the core. Regards, > > Oh, and -1 for putting it in core. Way too early, and not > important enough. > > - -- > Greg Sabino Mullane greg@turnstep.com > PGP Key: 0x14964AC8 201209222334 > http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 > -----BEGIN PGP SIGNATURE----- > > iEYEAREDAAYFAlBeg/AACgkQvJuQZxSWSsjL5ACgimT71B4lSb1ELhgMw5EBzAKs > xHIAn08vxGzmM6eSmDfZfxlJDTousq7h > =KgXW > -----END PGP SIGNATURE----- > > > > -- Satoshi Nagayasu <snaga@uptime.jp> Uptime Technologies, LLC. http://www.uptime.jp
On Mon, Sep 24, 2012 at 1:14 AM, Satoshi Nagayasu <snaga@uptime.jp> wrote:
Michael Paquier2012/09/23 12:37, Greg Sabino Mullane wrote:Exactly --- I do not care the SCM system though. :)
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: RIPEMD160
>
>
>>> I think it's time to consider some *umbrella project* for maintaining
>>> several small projects outside the core.
>>
>> Well, that was pgfoundry, and it didn't work out.
>
> I'm not sure that is quite analogous to what was being proposed.
> I read it as more of "let's package a bunch of these small utilities
> together into a single project", such that installing one installs them
> all (e.g. aptitude install pg_tools), and they all have a single bug
> tracker, etc. That tracker could be github, of course.
The bug tracker is going to be a mess if it has to manage 100 subprojects, knowing that each of them is strictly independant.
Maintainers are also different people for each tool.
Maintainers are also different people for each tool.
For example, xlogdump had not been maintained for 5 years when
> I'm not convinced of the merit of that plan, but that's an alternative
> interpretation that doesn't involve our beloved pgfoundry. :)
I picked it up last year. And the latest pg_filedump that supports 9.2
has not been released yet. pg_reorg as well.
If those tools are in a single project, it would be easier to keep
attention on it. Then, developers can easily build *all of them*
at once, fix them, and post any patch on the single mailing list.
Actually, it would save developers from waisting their time.
>From my viewpoint, it's not just a SCM or distributing issue.
It's about how to survive for such small projects around the core
even if these could not come in the core.
The package manager system could be easily pgxn. It is already designed for that.
For development what you are looking for here is something that github could perfectly manage.
As proposed by Masahiko, a single organization grouping all the tools (one repository per tool) would be enough. Please note that github can also host documentation. Bug tracker would be tool-dedicated in this case.
--
For development what you are looking for here is something that github could perfectly manage.
As proposed by Masahiko, a single organization grouping all the tools (one repository per tool) would be enough. Please note that github can also host documentation. Bug tracker would be tool-dedicated in this case.
--
http://michael.otacoo.com
On Mon, Sep 24, 2012 at 12:23 AM, Michael Paquier <michael.paquier@gmail.com> wrote: > As proposed by Masahiko, a single organization grouping all the tools (one > repository per tool) would be enough. Please note that github can also host > documentation. Bug tracker would be tool-dedicated in this case. From this PoV, pgFoundry allows your tool to be under http://yourtool.projects.postgresql.org instead of under a more generic namespace: I find it a nice and cozy place in the url space where to put your project. If pgFoundry will be dismissed I hope at least a hosting service for static pages will remain. -- Daniele
Excerpts from Daniele Varrazzo's message of dom sep 23 22:02:51 -0300 2012: > On Mon, Sep 24, 2012 at 12:23 AM, Michael Paquier > <michael.paquier@gmail.com> wrote: > > > As proposed by Masahiko, a single organization grouping all the tools (one > > repository per tool) would be enough. Please note that github can also host > > documentation. Bug tracker would be tool-dedicated in this case. > > From this PoV, pgFoundry allows your tool to be under > http://yourtool.projects.postgresql.org instead of under a more > generic namespace: I find it a nice and cozy place in the url space > where to put your project. If pgFoundry will be dismissed I hope at > least a hosting service for static pages will remain. I don't think that has been offered. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Mon, Sep 24, 2012 at 10:17 AM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote: > Excerpts from Daniele Varrazzo's message of dom sep 23 22:02:51 -0300 2012: >> On Mon, Sep 24, 2012 at 12:23 AM, Michael Paquier >> <michael.paquier@gmail.com> wrote: >> >> > As proposed by Masahiko, a single organization grouping all the tools (one >> > repository per tool) would be enough. Please note that github can also host >> > documentation. Bug tracker would be tool-dedicated in this case. >> >> From this PoV, pgFoundry allows your tool to be under >> http://yourtool.projects.postgresql.org instead of under a more >> generic namespace: I find it a nice and cozy place in the url space >> where to put your project. If pgFoundry will be dismissed I hope at >> least a hosting service for static pages will remain. > > I don't think that has been offered. But I don't think it's necessarily the case that pgFoundry is getting "dismissed", either. I got a note from Marc Fournier not too long ago (sent to some probably-not-small set of people with pgFoundry accounts) indicating that they were planning to upgrade gForge as far as they could, and then switch to FusionForge <http://fusionforge.org/>, which is evidently the successor. It shouldn't be assumed that the upgrade process will be easy or quick. -- When confronted by a difficult problem, solve it by reducing it to the question, "How would the Lone Ranger handle this?"
On 21 September 2012 08:42, Michael Paquier <michael.paquier@gmail.com> wrote: > > > On Fri, Sep 21, 2012 at 1:00 PM, Hitoshi Harada <umi.tanuki@gmail.com> > wrote: >> >> I'm not familiar with pg_reorg, but I wonder why we need a separate >> program for this task. I know pg_reorg is ok as an external program >> per se, but if we could optimize CLUSTER (or VACUUM which I'm a little >> pessimistic about) in the same way, it's much nicer than having >> additional binary + extension. Isn't it possible to do the same thing >> above within the CLUSTER command? Maybe CLUSTER .. CONCURRENTLY? > > CLUSTER might be more adapted in this case as the purpose is to reorder the > table. > The same technique used by pg_reorg (aka table coupled with triggers) could > lower the lock access of the table. > Also, it could be possible to control each sub-operation in the same fashion > way as CREATE INDEX CONCURRENTLY. > By the way, whatever the operation, VACUUM or CLUSTER used, I got a couple > of doubts: > 1) isn't it be too costly for a core operation as pg_reorg really needs many > temporary objects? Could be possible to reduce the number of objects created > if added to core though... > 2) Do you think the current CLUSTER is enough and are there wishes to > implement such an optimization directly in core? For me, the Postgres user interface should include * REINDEX CONCURRENTLY * CLUSTER CONCURRENTLY * ALTER TABLE CONCURRENTLY and also that autovacuum would be expanded to include REINDEX and CLUSTER, renaming it to automaint. The actual implementation mechanism for those probably looks something like pg_reorg, but I don't see it as preferable to include the utility directly into core, though potentially some of the underlying code might be. -- Simon Riggs http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On Sat, Sep 22, 2012 at 3:25 AM, Satoshi Nagayasu <snaga@uptime.jp> wrote: > > To solve this problem, I would like to have some umbrella project. > It would be called "pg dba utils", or something like this. > This umbrella project may contain several third-party tools (pg_reorg, > pg_rman, pg_filedump, xlogdump, etc, etc...) as its sub-modules. Great idea! +1 Roberto Mello
2012/09/25 0:15, Simon Riggs wrote: > On 21 September 2012 08:42, Michael Paquier <michael.paquier@gmail.com> wrote: >> >> >> On Fri, Sep 21, 2012 at 1:00 PM, Hitoshi Harada <umi.tanuki@gmail.com> >> wrote: >>> >>> I'm not familiar with pg_reorg, but I wonder why we need a separate >>> program for this task. I know pg_reorg is ok as an external program >>> per se, but if we could optimize CLUSTER (or VACUUM which I'm a little >>> pessimistic about) in the same way, it's much nicer than having >>> additional binary + extension. Isn't it possible to do the same thing >>> above within the CLUSTER command? Maybe CLUSTER .. CONCURRENTLY? >> >> CLUSTER might be more adapted in this case as the purpose is to reorder the >> table. >> The same technique used by pg_reorg (aka table coupled with triggers) could >> lower the lock access of the table. >> Also, it could be possible to control each sub-operation in the same fashion >> way as CREATE INDEX CONCURRENTLY. >> By the way, whatever the operation, VACUUM or CLUSTER used, I got a couple >> of doubts: >> 1) isn't it be too costly for a core operation as pg_reorg really needs many >> temporary objects? Could be possible to reduce the number of objects created >> if added to core though... >> 2) Do you think the current CLUSTER is enough and are there wishes to >> implement such an optimization directly in core? > > > For me, the Postgres user interface should include > * REINDEX CONCURRENTLY > * CLUSTER CONCURRENTLY > * ALTER TABLE CONCURRENTLY > and also that autovacuum would be expanded to include REINDEX and > CLUSTER, renaming it to automaint. > > The actual implementation mechanism for those probably looks something > like pg_reorg, but I don't see it as preferable to include the utility > directly into core, though potentially some of the underlying code > might be. I think it depends on what trade-off we can see. AFAIK, basically, rebuilding tables and/or indexes has a trade-off between "lock-free" and "disk-space". So, if we have enough disk space to build a "temporary" table/index when rebuilding a table/index, "concurrently" would be a great option, and I would love it to have in core. Regards, -- Satoshi Nagayasu <snaga@uptime.jp> Uptime Technologies, LLC. http://www.uptime.jp
>> For me, the Postgres user interface should include >> * REINDEX CONCURRENTLY I don't see why we don't have REINDEX CONCURRENTLY now. When I was writing out the instructions for today's update, I was thinking "we already have all the commands for this". -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
On 24 September 2012 17:36, Josh Berkus <josh@agliodbs.com> wrote: > >>> For me, the Postgres user interface should include >>> * REINDEX CONCURRENTLY > > I don't see why we don't have REINDEX CONCURRENTLY now. Same reason for everything on (anyone's) TODO list. Lack of vision is not holding us back, we just need the vision to realise it. -- Simon Riggs http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On 9/24/12 3:43 PM, Simon Riggs wrote: > On 24 September 2012 17:36, Josh Berkus <josh@agliodbs.com> wrote: >> >>>> For me, the Postgres user interface should include >>>> * REINDEX CONCURRENTLY >> >> I don't see why we don't have REINDEX CONCURRENTLY now. > > Same reason for everything on (anyone's) TODO list. Yes, I'm just pointing out that it would be a very small patch for someone, and that AFAIK it didn't make it on the TODO list yet. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
On Tuesday, September 25, 2012 12:55:35 AM Josh Berkus wrote: > On 9/24/12 3:43 PM, Simon Riggs wrote: > > On 24 September 2012 17:36, Josh Berkus <josh@agliodbs.com> wrote: > >>>> For me, the Postgres user interface should include > >>>> * REINDEX CONCURRENTLY > >> > >> I don't see why we don't have REINDEX CONCURRENTLY now. > > > > Same reason for everything on (anyone's) TODO list. > > Yes, I'm just pointing out that it would be a very small patch for > someone, and that AFAIK it didn't make it on the TODO list yet. Its not *that* small. 1. You need more than you can do with CREATE INDEX CONCURRENTLY and DROP INDEX CONCURRENTLY because the index can e.g. be referenced by a foreign key constraint. So you need to replace the existing index oid with a new one by swapping the relfilenodes of both after verifying several side conditions (indcheckxmin, indisvalid, indisready). It would probably have to look like: - build new index with indisready = false - newindex.indisready = true - wait - newindex.indisvalid = true - wait - swap(oldindex.relfilenode, newindex.relfilenode) - oldindex.indisvalid = false - wait - oldindex.indisready = false - wait - drop new index with old relfilenode Every wait indicates an externally visible state which you might encounter/need to cleanup... To make it viable to use that systemwide it might be necessary to batch the individual steps together for multiple indexes because all that waiting is going to suck if you do it for every single table in the database while you also have longrunning queries... 2. no support for concurrent on system tables (not easy for shared catalogs) 3. no support for the indexes of exlusion constraints (not hard I think) Greetings, Andres -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On Tue, Sep 25, 2012 at 8:13 AM, Andres Freund <andres@2ndquadrant.com> wrote:
I am afraid I do not get your point here.
-- Could you clarify what do you mean here by cleanup?On Tuesday, September 25, 2012 12:55:35 AM Josh Berkus wrote:Its not *that* small.
> On 9/24/12 3:43 PM, Simon Riggs wrote:
> > On 24 September 2012 17:36, Josh Berkus <josh@agliodbs.com> wrote:
> >>>> For me, the Postgres user interface should include
> >>>> * REINDEX CONCURRENTLY
> >>
> >> I don't see why we don't have REINDEX CONCURRENTLY now.
> >
> > Same reason for everything on (anyone's) TODO list.
>
> Yes, I'm just pointing out that it would be a very small patch for
> someone, and that AFAIK it didn't make it on the TODO list yet.
1. You need more than you can do with CREATE INDEX CONCURRENTLY and DROP INDEX
CONCURRENTLY because the index can e.g. be referenced by a foreign key
constraint. So you need to replace the existing index oid with a new one by
swapping the relfilenodes of both after verifying several side conditions
(indcheckxmin, indisvalid, indisready).
It would probably have to look like:
- build new index with indisready = false
- newindex.indisready = true
- wait
- newindex.indisvalid = true
- wait
- swap(oldindex.relfilenode, newindex.relfilenode)
- oldindex.indisvalid = false
- wait
- oldindex.indisready = false
- wait
- drop new index with old relfilenode
Every wait indicates an externally visible state which you might encounter/need
to cleanup...
I am afraid I do not get your point here.
2. no support for concurrent on system tables (not easy for shared catalogs)
Doesn't this exclude all the tables that are in the schema catalog?
3. no support for the indexes of exclusion constraints (not hard I think)
This just consists in a check of indisready in pg_index.
Michael Paquier
http://michael.otacoo.com
On Tuesday, September 25, 2012 04:37:05 AM Michael Paquier wrote: > On Tue, Sep 25, 2012 at 8:13 AM, Andres Freund <andres@2ndquadrant.com>wrote: > > On Tuesday, September 25, 2012 12:55:35 AM Josh Berkus wrote: > > > On 9/24/12 3:43 PM, Simon Riggs wrote: > > > > On 24 September 2012 17:36, Josh Berkus <josh@agliodbs.com> wrote: > > > >>>> For me, the Postgres user interface should include > > > >>>> * REINDEX CONCURRENTLY > > > >> > > > >> I don't see why we don't have REINDEX CONCURRENTLY now. > > > > > > > > Same reason for everything on (anyone's) TODO list. > > > > > > Yes, I'm just pointing out that it would be a very small patch for > > > someone, and that AFAIK it didn't make it on the TODO list yet. > > > > Its not *that* small. > > > > 1. You need more than you can do with CREATE INDEX CONCURRENTLY and DROP > > INDEX > > CONCURRENTLY because the index can e.g. be referenced by a foreign key > > constraint. So you need to replace the existing index oid with a new one > > by swapping the relfilenodes of both after verifying several side > > conditions (indcheckxmin, indisvalid, indisready). > > > > It would probably have to look like: > > > > - build new index with indisready = false > > - newindex.indisready = true > > - wait > > - newindex.indisvalid = true > > - wait > > - swap(oldindex.relfilenode, newindex.relfilenode) > > - oldindex.indisvalid = false > > - wait > > - oldindex.indisready = false > > - wait > > - drop new index with old relfilenode > > > > Every wait indicates an externally visible state which you might > > encounter/need > > to cleanup... > > Could you clarify what do you mean here by cleanup? > I am afraid I do not get your point here. Sorry, was a bit tired when writing the above. The point is that to work concurrent the CONCURRENT operations commit/start multiple transactions internally. It can be interrupted (user, shutdown, error, crash) and leave transient state behind every time it does so. What I wanted to say is that we need to take care that each of those can easily be cleaned up afterwards. > > 2. no support for concurrent on system tables (not easy for shared > > catalogs) > Doesn't this exclude all the tables that are in the schema catalog? No. Only SELECT array_to_string(array_agg(relname), ', ') FROM pg_class WHERE relisshared AND relkind = 'r'; their toast tables and their indexes are shared. The problem is that for those you cannot create a separate index and let it update concurrently because you cannot write into each databases pg_class/pg_index. > > 3. no support for the indexes of exclusion constraints (not hard I think) > This just consists in a check of indisready in pg_index. It will probably be several places, but yea, I don't think its hard. Andres -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On Tue, Sep 25, 2012 at 5:55 PM, Andres Freund <andres@2ndquadrant.com> wrote:
-- On Tuesday, September 25, 2012 04:37:05 AM Michael Paquier wrote:
> On Tue, Sep 25, 2012 at 8:13 AM, Andres Freund <andres@2ndquadrant.com>wrote:
> Could you clarify what do you mean here by cleanup?
> I am afraid I do not get your point here.
Sorry, was a bit tired when writing the above.
The point is that to work concurrent the CONCURRENT operations commit/start
multiple transactions internally. It can be interrupted (user, shutdown, error,
crash) and leave transient state behind every time it does so. What I wanted to
say is that we need to take care that each of those can easily be cleaned up
afterwards.
Sure, many errors may happen.
But, in the case of CREATE INDEX CONCURRENTLY, there is no clean up method
implemented as far as I know (might be missing something though). Isn't an index
only considered as invalid in case of failure for concurrent creation?
In the case of REINDEX it would be essential to create such a cleanup mechanism
as I cannot imagine a production database with an index that has been marked as
invalid due to a concurrent reindex failure, by assuming here, of course, that
REINDEX CONCURRENTLY would use the same level of process error as CREATE
INDEX CONCURRENTLY.
One of the possible cleanup mechanisms I got on top of my head is a callback at
transaction abort, each callback would need to be different for each subtransaction
used at during the concurrent operation.
In case the callback itself fails, well the old and/or new indexes become invalid.
implemented as far as I know (might be missing something though). Isn't an index
only considered as invalid in case of failure for concurrent creation?
In the case of REINDEX it would be essential to create such a cleanup mechanism
as I cannot imagine a production database with an index that has been marked as
invalid due to a concurrent reindex failure, by assuming here, of course, that
REINDEX CONCURRENTLY would use the same level of process error as CREATE
INDEX CONCURRENTLY.
One of the possible cleanup mechanisms I got on top of my head is a callback at
transaction abort, each callback would need to be different for each subtransaction
used at during the concurrent operation.
In case the callback itself fails, well the old and/or new indexes become invalid.
No. Only
> > 2. no support for concurrent on system tables (not easy for shared
> > catalogs)
> Doesn't this exclude all the tables that are in the schema catalog?
SELECT array_to_string(array_agg(relname), ', ') FROM pg_class WHERE
relisshared AND relkind = 'r';
their toast tables and their indexes are shared. The problem is that for those
you cannot create a separate index and let it update concurrently because you
cannot write into each databases pg_class/pg_index.
Yes indeed, I didn't think about things that are shared among databases.
Blocking that is pretty simple, only a matter of places checked.
Blocking that is pretty simple, only a matter of places checked.
Michael Paquier
http://michael.otacoo.com
Simon Riggs <simon@2ndQuadrant.com> writes: > For me, the Postgres user interface should include > * REINDEX CONCURRENTLY > * CLUSTER CONCURRENTLY > * ALTER TABLE CONCURRENTLY > and also that autovacuum would be expanded to include REINDEX and > CLUSTER, renaming it to automaint. FWIW, +1 to all those user requirements, and for not having pg_reorg simply moved as-is nearer to core. I would paint the shed "autoheal", maybe. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
On Wed, Sep 26, 2012 at 4:42 AM, Dimitri Fontaine <dimitri@2ndquadrant.fr> wrote:
Michael PaquierSimon Riggs <simon@2ndQuadrant.com> writes:FWIW, +1 to all those user requirements, and for not having pg_reorg
> For me, the Postgres user interface should include
> * REINDEX CONCURRENTLY
> * CLUSTER CONCURRENTLY
> * ALTER TABLE CONCURRENTLY
> and also that autovacuum would be expanded to include REINDEX and
> CLUSTER, renaming it to automaint.
simply moved as-is nearer to core. I would paint the shed "autoheal",
maybe.
Yes, completely agreed.
Based on what Simon is suggesting, REINDEX and CLUSTER extensions
are prerequisites for autovacuum extension. It would need to use a mechanism
that it slightly different than pg_reorg. ALTER TABLE could used something close
to pg_reorg by creating a new table then swaping the 2 tables. The cases of column
drop and addition particularly need some thoughts.
I would like to work on such features and provide patches for the 2 first. This will of
course strongly depend on the time I can spend on in the next couple of months.
Based on what Simon is suggesting, REINDEX and CLUSTER extensions
are prerequisites for autovacuum extension. It would need to use a mechanism
that it slightly different than pg_reorg. ALTER TABLE could used something close
to pg_reorg by creating a new table then swaping the 2 tables. The cases of column
drop and addition particularly need some thoughts.
I would like to work on such features and provide patches for the 2 first. This will of
course strongly depend on the time I can spend on in the next couple of months.
--
http://michael.otacoo.com
On Tuesday, September 25, 2012 01:48:34 PM Michael Paquier wrote: > On Tue, Sep 25, 2012 at 5:55 PM, Andres Freund <andres@2ndquadrant.com>wrote: > > On Tuesday, September 25, 2012 04:37:05 AM Michael Paquier wrote: > > > On Tue, Sep 25, 2012 at 8:13 AM, Andres Freund <andres@2ndquadrant.com > > > > > >wrote: > > > Could you clarify what do you mean here by cleanup? > > > I am afraid I do not get your point here. > > > > Sorry, was a bit tired when writing the above. > > > > The point is that to work concurrent the CONCURRENT operations > > commit/start multiple transactions internally. It can be interrupted > > (user, shutdown, error, > > crash) and leave transient state behind every time it does so. What I > > wanted to > > say is that we need to take care that each of those can easily be cleaned > > up > > afterwards. > > Sure, many errors may happen. > But, in the case of CREATE INDEX CONCURRENTLY, there is no clean up method > implemented as far as I know (might be missing something though). Isn't an > index only considered as invalid in case of failure for concurrent creation? Well, you can DROP or REINDEX the invalid index. There are several scenarios where you can get invalid indexes. Unique violations, postgres restarts, aborted index creation... > In the case of REINDEX it would be essential to create such a cleanup > mechanism as I cannot imagine a production database with an index that has > been marked as invalid due to a concurrent reindex failure, by assuming here, > of course, that REINDEX CONCURRENTLY would use the same level of process > error as CREATE INDEX CONCURRENTLY. Not sure what youre getting at? > One of the possible cleanup mechanisms I got on top of my head is a > callback at transaction abort, each callback would need to be different for > each subtransaction used at during the concurrent operation. > In case the callback itself fails, well the old and/or new indexes become > invalid. Thats not going to work. E.g. the session might have been aborted or such. Also, there is not much you can do from an callback at transaction end as you cannot do catalog modifications. I was thinking of REINDEX CONCURRENTLY CONTINUE or something vaguely similar. > > > > 2. no support for concurrent on system tables (not easy for shared > > > > catalogs) > > > > > > Doesn't this exclude all the tables that are in the schema catalog? > > > > No. Only SELECT array_to_string(array_agg(relname), ', ') FROM pg_class > > WHERE relisshared AND relkind = 'r'; > > their toast tables and their indexes are shared. The problem is that for > > those you cannot create a separate index and let it update concurrently > > because you cannot write into each databases pg_class/pg_index. > Yes indeed, I didn't think about things that are shared among databases. > Blocking that is pretty simple, only a matter of places checked. Its just a bit sad to make the thing not really appear lockless ;) Greetings, Andres -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On Wed, Sep 26, 2012 at 8:13 PM, Andres Freund <andres@2ndquadrant.com> wrote:
Michael PaquierOn Tuesday, September 25, 2012 01:48:34 PM Michael Paquier wrote:Well, you can DROP or REINDEX the invalid index.
> On Tue, Sep 25, 2012 at 5:55 PM, Andres Freund <andres@2ndquadrant.com>wrote:
> > On Tuesday, September 25, 2012 04:37:05 AM Michael Paquier wrote:
> > > On Tue, Sep 25, 2012 at 8:13 AM, Andres Freund <andres@2ndquadrant.com
> > >
> > >wrote:
> > > Could you clarify what do you mean here by cleanup?
> > > I am afraid I do not get your point here.
> >
> > Sorry, was a bit tired when writing the above.
> >
> > The point is that to work concurrent the CONCURRENT operations
> > commit/start multiple transactions internally. It can be interrupted
> > (user, shutdown, error,
> > crash) and leave transient state behind every time it does so. What I
> > wanted to
> > say is that we need to take care that each of those can easily be cleaned
> > up
> > afterwards.
>
> Sure, many errors may happen.
> But, in the case of CREATE INDEX CONCURRENTLY, there is no clean up method
> implemented as far as I know (might be missing something though). Isn't an
> index only considered as invalid in case of failure for concurrent creation?
There are several scenarios where you can get invalid indexes. Unique
violations, postgres restarts, aborted index creation...Not sure what youre getting at?
> In the case of REINDEX it would be essential to create such a cleanup
> mechanism as I cannot imagine a production database with an index that has
> been marked as invalid due to a concurrent reindex failure, by assuming here,
> of course, that REINDEX CONCURRENTLY would use the same level of process
> error as CREATE INDEX CONCURRENTLY.
I just meant that when CREATE INDEX CONCURRENTLY fails, the index created is
considered as invalid, so it cannot be used by planner.
Based on what you told before:
1) build new index with indisready = false
newindex.indisready = true
wait
2) newindex.indisvalid = true
wait
3) swap(oldindex.relfilenode, newindex.relfilenode)
oldindex.indisvalid = false
wait
4) oldindex.indisready = false
wait
drop new index with old relfilenode
If the reindex fails at step 1 or 2, the new index is not usable so the relation will finish
with an index which is not valid. If it fails at step 4, the old index is invalid. If it fails at step
3, both indexes are valid and both are usable for given relation.
Do you think it is acceptable to consider that the user has to do the cleanup of the old or new index
himself if there is a failure?
considered as invalid, so it cannot be used by planner.
Based on what you told before:
1) build new index with indisready = false
newindex.indisready = true
wait
2) newindex.indisvalid = true
wait
3) swap(oldindex.relfilenode, newindex.relfilenode)
oldindex.indisvalid = false
wait
4) oldindex.indisready = false
wait
drop new index with old relfilenode
If the reindex fails at step 1 or 2, the new index is not usable so the relation will finish
with an index which is not valid. If it fails at step 4, the old index is invalid. If it fails at step
3, both indexes are valid and both are usable for given relation.
Do you think it is acceptable to consider that the user has to do the cleanup of the old or new index
himself if there is a failure?
Thats not going to work. E.g. the session might have been aborted or such.
> One of the possible cleanup mechanisms I got on top of my head is a
> callback at transaction abort, each callback would need to be different for
> each subtransaction used at during the concurrent operation.
> In case the callback itself fails, well the old and/or new indexes become
> invalid.
Also, there is not much you can do from an callback at transaction end as you
cannot do catalog modifications.
I was thinking of REINDEX CONCURRENTLY CONTINUE or something vaguely similar.
You could also reissue the reindex command and avoid an additional command. When launching a
concurrent reindex, it could be possible to check if there is already an index that has been created to replace the
old one that failed previously. In order to control that, why not adding an additional field in pg_index?
When creating a new index concurrently, we register in its pg_index entry the oid of the index that it has to
replace. When reissuing the command after a failure, it is then possible to check if there is already an index that has
been issued by a previous REINDEX CONCURRENT command and based on the flag values of the old and new
indexes it is then possible to replay the command from the step where it previously failed.
--
concurrent reindex, it could be possible to check if there is already an index that has been created to replace the
old one that failed previously. In order to control that, why not adding an additional field in pg_index?
When creating a new index concurrently, we register in its pg_index entry the oid of the index that it has to
replace. When reissuing the command after a failure, it is then possible to check if there is already an index that has
been issued by a previous REINDEX CONCURRENT command and based on the flag values of the old and new
indexes it is then possible to replay the command from the step where it previously failed.
--
http://michael.otacoo.com
On Wednesday, September 26, 2012 02:39:36 PM Michael Paquier wrote: > Do you think it is acceptable to consider that the user has to do the > cleanup of the old or new index himself if there is a failure? The problem I see is that if you want the thing to be efficient you might end up doing step 1) for all/a bunch of indexes, then 2), then .... In that case you can have loads of invalid indexes around. > You could also reissue the reindex command and avoid an additional command. > When launching a concurrent reindex, it could be possible to check if there > is already an index that has been created to replace the old one that failed > previously. In order to control that, why not adding an additional field in > pg_index? > When creating a new index concurrently, we register in its pg_index entry > the oid of the index that it has to replace. When reissuing the command > after a failure, it is then possible to check if there is already an index > that has been issued by a previous REINDEX CONCURRENT command and based on > the flag values of the old and new indexes it is then possible to replay the > command from the step where it previously failed. I don't really like this idea but we might end up there anyway because we probably need to keep track whether an index is actually only a "replacement" index that shouldn't exist on its own. Otherwise its hard to know which indexes to drop if it failed halfway through. Greetings, Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Mon, Sep 24, 2012 at 03:55:35PM -0700, Josh Berkus wrote: > On 9/24/12 3:43 PM, Simon Riggs wrote: > > On 24 September 2012 17:36, Josh Berkus <josh@agliodbs.com> wrote: > >> > >>>> For me, the Postgres user interface should include > >>>> * REINDEX CONCURRENTLY > >> > >> I don't see why we don't have REINDEX CONCURRENTLY now. > > > > Same reason for everything on (anyone's) TODO list. > > Yes, I'm just pointing out that it would be a very small patch for > someone, and that AFAIK it didn't make it on the TODO list yet. I see it on the TODO list, and it has been there for years: https://wiki.postgresql.org/wiki/Todo#IndexesAdd REINDEX CONCURRENTLY, like CREATE INDEX CONCURRENTLY -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +