Re: [HACKERS] Avoid MVCC using exclusive lock possible?
On Tuesday 24 February 2004 22:13, Stephen wrote: > Hi, > > Recently, I ran a huge update on an Integer column affecting 100 million > rows in my database. What happened was my disk space increased in size and > my IO load was very high. It appears that MVCC wants to rewrite each row > (each row was about 5kB due to a bytea column). In addition, VACUUM needs > to run to recover space eating up even more IO bandwidth. I am sure people have answered the approach you have suggested so let me suggest a workaround for your problem. You could run following in a transaction. - begin - Create another table with exact same structure - write a procedure that reads from input table and updates the value in between - drop the original table - rename new table to old one - commit - analyze new table Except for increased disk space, this approach has all the good things postgresql offers. Especially using transactable DDLs it is huge benefit. You certainly do save on vacuum. If the entire table is updated then you can almost certainly get things done faster this way. HTH Shridhar ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Collaboration Tool Proposal -- Summary to date
Josh Berkus <[EMAIL PROTECTED]> writes: > C. BZ does not have any PG support in its default branch, and the RH port is > currently unmaintained. I was quite surprised to read this, and I'm sure Dave Lawrence (RH's BZ maintainer) would be too. As would be the thousands of people who regularly use bugzilla.redhat.com. If you want to reject BZ because you don't like it, fine, but please don't allege that it's unmaintained or that we'd have to put our own resources into maintaining it. There *will* be BZ-on-PG running at Red Hat for the foreseeable future. Obviously Dave would like to get the port folded back upstream, and it looks like that will happen eventually, but we need not fear being alone in running BZ-on-PG meanwhile. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Check Constraints and pg_dump
Tom, I have another instance of a possible function being used as a check constraint: a function that makes sure there is one row, and only one row in a table. At table creation, and the creation of the constraint, there are no rows in the table. So, even if the constraint is a valid one to have, it will fail. We use check constraint functions in a few places, and they work just fine for us (minus the case of trying to restore the database from a pg_dump archive). Is it impossible to treat check constraints similar to other constraints and make them deferrable, specifically in the restoration from a pg_dump archive? Is there a specific reason check constraints are handled differently from other constraints? Jonathan Scott On Thu, 26 Feb 2004 08:54:59 -0500 Tom Lane <[EMAIL PROTECTED]> wrote: > Jonathan Scott <[EMAIL PROTECTED]> writes: > > The functions and tables create just fine, but when it gets to the > > COPY part of the sql script, it tries to load tables in what really is > > the wrong order. The check constraint is making sure there is a "plan" > > before there is a "contract", yet pg_dump is trying to load the > > contract table before there is anything in the plan table. > > Shouldn't you be using a foreign key for that? > > I don't see any reasonable way that pg_dump can be expected to > understand that a check constraint expresses a relationship between two > tables. The semantics of check constraints aren't right for it anyway. > > All else being equal, I think the tables will be loaded in OID order, > so a possible workaround is to create the plan table first. But an > FK seems like a better answer. > > regards, tom lane > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > -- Jonathan Scott, Programmer, Vanten K.K. [EMAIL PROTECTED] Tel: 03-5919-0266 http://www.vanten.com Fax: 03-5919-0267 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Server Side PL support
Yes. I am looking at a few of these things (preloading, intra-perl calling, array and tuple return), and I understand that CommandPrompt is doing some plperl work too. They already have a plperl which does triggers. My question was not "what functionality do we need from PLs?" but rather "what do PLs need from the core for good support?" I particularly had catalog support in mind, but there could be other areas too. I'm glad to see you support the efforts to make plperl something more useful. One idea I had for a GForge/GBorg project when that gets going is a place for collaborating on experimental plperl work, before it gets merged back to the core. cheers andrew elein wrote: This is a very interesting topic. Joe Conway has a very good idea of pl requirements since he just implemented pl/R. Some requirements for pl languages are these: * support query execution * support trigger functions * allocating storage for per statement function calls This is like the SD[] dictionary in plpythonu. * support for all built-in datatypes, e.g. easy array support for pl languages which have natural arrays, sets or dictionaries. * enable easy fastpath functionality or similar pl to pl function calls Note that array support, trigger and query support for plperl does not yet exist. IMHO extended support for plperl should have a relatively high priority. We are actively reaching out to the perl community and full support of the interface is important. Collaboration on the implementation is also possible--it has been discussed with some perl folks. elein [EMAIL PROTECTED] On Sun, Feb 29, 2004 at 02:20:19PM -0500, Andrew Dunstan wrote: I have been taking a brief look at pltcl, and particularly its ability to preload modules. By comparison with most of the core product this seems to be somewhat out of date and unpolished (e.g. hardcoded path to libpgtcl.so, no use of schemas for the supporting tables, lack of comments). Since my understanding of tcl is extremely rusty, I didn't dig further than that. However, I am interested in getting a similar facility working for plperl, and thus wanted to start a discussion on what general facilities could/should be made available to server side PLs. Or should we just assume that each PL will create it's own support tables? cheers andrew ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Collaboration Tool Proposal -- Summary to date
On Sun, 29 Feb 2004, Neil Conway wrote: > Josh Berkus wrote: > > D. One possible reservation may be integrating RT with GForge. > > I'm confused. Are we considering moving core backend development over > to GForge as well, or just GBorg? (Personally the former doesn't > strike me as a good idea, at least initially.) There are no plans, at this time, to move the core development stuff from its current "format" ... this is all for gborg hosted projects at this time ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [pgsql-www] [HACKERS] Collaboration Tool Proposal -- Summary to date
Folks, Re: moving the main project to GForge/whatever: we're not considering that at this time. The way the discussion got entangled is that a few people mentioned wanting a better bug tracker than then one offered with GForge, and that we are considering using a Bug Tracker for the main project. If we do want an upgraded BT for the main project, it would make sense to use the same BT for GForge/GBorg projects. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Collaboration Tool Proposal -- Summary to date
Neil Conway wrote: Josh Berkus wrote: D. One possible reservation may be integrating RT with GForge. I'm confused. Are we considering moving core backend development over to GForge as well, or just GBorg? (Personally the former doesn't strike me as a good idea, at least initially.) You are correct that this has (quite annoyingly) been overlooked in much of the discussion. Indeed, the needs of a GBorg project might well differ both from the core project and from other GBorg projects. ISTM the sensible thing right now would be to work on migrating GBorg and leave the core project exactly as it is. OTOH, there was considerable discussion a few months ago about bug tracking for the core project, and we have unfortunately largely repeated that discussion with similar results (for cheese in my_favourite_bugtrackers print "I like $cheese\n"; ). I think that a careful choice made for GBorg might allow us to progress the matter for the core project at a later stage, and the choice should be made with that possible suitability in mind. I think that the PostgreSQL project would be very much sending the wrong message to use an effectively non-Postgres tool. Frankly, I think the PostgreSQL project would be sending "the wrong message" if we chose our tools on any basis other than functionality. We ought to use what works, whether it supports PG or not. Whether the bug tracker tool uses PostgreSQL, flat files or MS Access to store data is entirely secondary to whether it serves the needs of the development group. The big issue is not going to be the bug tracker iteself, but how easy it is to glue it to GForge (and if it requires too much customised glue we really won't be making an advance at all). On those grounds alone a FOSS bug tracker surely is preferable, regardless of political considerations. Apart from the fact that its DB Schema lacks all referential integrity constraints - a legacy of its origin in you-know-what - RT doesn't look half bad. If we wanted to step outside the FOSS world, I don't think bug tracking would be the area where there might be most need, but maybe that's just me ;-) cheers andrew ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Collaboration Tool Proposal -- Summary to date
Neil, > Frankly, I think the PostgreSQL project would be sending "the wrong > message" if we chose our tools on any basis other than functionality. > We ought to use what works, whether it supports PG or not. Whether the > bug tracker tool uses PostgreSQL, flat files or MS Access to store > data is entirely secondary to whether it serves the needs of the > development group. OK, then, more substantial: I personally lack confidence in any tool that uses an in-memory object database to store persistent data. I also feel pessimistic about our ability to extend and integrate a tool which uses radically different storage mechanism than the other tools we're using. Finally, for any of these things I forsee asking the communites involved with those projects for help, and it seems foolish to beg for help (as would probably be required of a project that does nor support PG) when there are people offering to help us. THIS JUST IN: as if we didn't have enough options, Talli of the OpenACS community has offered their help with using OpenACS modules for any of the web tasks we've discussed. More later. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Collaboration Tool Proposal -- Summary to date
Josh Berkus wrote: D. One possible reservation may be integrating RT with GForge. I'm confused. Are we considering moving core backend development over to GForge as well, or just GBorg? (Personally the former doesn't strike me as a good idea, at least initially.) I think that the PostgreSQL project would be very much sending the wrong message to use an effectively non-Postgres tool. Frankly, I think the PostgreSQL project would be sending "the wrong message" if we chose our tools on any basis other than functionality. We ought to use what works, whether it supports PG or not. Whether the bug tracker tool uses PostgreSQL, flat files or MS Access to store data is entirely secondary to whether it serves the needs of the development group. -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [pgsql-www] Collaboration Tool Proposal -- Summary to date
On Sun, 29 Feb 2004, Josh Berkus wrote: > A. GF-Tr does not support e-mail interaction at all. Just curious, but: 1. how much work would be involved in adding that? 2. would the gforge developers be willing to integrate it in? The reason I ask is that we have several PHP developers around, some of which might be willing to work on integrating such into the bug tracker ... ? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Collaboration Tool Proposal -- Summary to date
Folks, I thought that I would give everyone a summary of the current discussion of collaboration tools and bug-trackers for our project as I read them. I think that we are quite close to a consensus. Please comment if I've missed something. GBorg-->GForge migration: so far, nobody has objected to this idea, except for justifiable caution about the resources required.If the conversion can be accomplished relatively seamlessly, and/or through outside help, I don't think we have any reason NOT to proceed with a *gradual* migration. BugTrackers: here, opinion is more divided. Many people seem to feel that they would like bug trackers more sophisticated than those offered by the built-in GForge tool.The criteria that seem to have general consensus are: A. The bug tracker should have some kind of e-mail interface which allows responding to bugs a well as tracking them, so that people who don't like web interfaces don't need to use them. B. The bug tracker must be OSS; proprietary software is too risky when there are alternatives. C. The bug tracker must use PostgreSQL, and it would be preferable if PostgreSQL support was available in the default branch of the project. And I will add one that I see as unavoidable, even though it's been sort of glossed over in the discussions: D. The bug tracker should not require extensive customization or other work by our team, becuase we simply don't have the people. Based on this, I will evaluate the various bug trackers which have been mentioned to date: GForge's Tracker: This choice has the tremendous benefit of already being built-in to GForge and thus integrated with the rest of the project infrastructure. On the rest of the criteria: A. GF-Tr does not support e-mail interaction at all. B. pass C. pass D. pass Otherwise, GF-Tr's other detraction is that it is relatively unsophisticated, not supporting, for example, tying bugs to version numbers. This simplicity can also be an asset as far as start-up time is concerned, though, but there exists the danger that newbies would use the tracker while developers continute to use e-mail. making the system ineffective. BugZilla: This has been a popular suggestion because lots of people are familiar with it. However, BZ fails our criteria on three counts: A. BZ does not support issue alterations by e-mail; in fact, you can't even log in by e-mail link. B. Pass C. BZ does not have any PG support in its default branch, and the RH port is currently unmaintained. While a member of the BZ team is attempting to complete a port, there is no expected completion date. D. Given C., we could reasonably expect that using BZ would require significant support from the PG community in order to maintain a PG port. Given that one of the goals of the migration is to *reduce* the resources required by our community to maintain our infrastructure, this seems unwise. There is also the factor that several people on this list hate BZ's interface with a passion not expressed for other possible tools. I am one of them, I'm afraid, and since I am the primary volunteer for admining the system, I think my opinion carries some weight. I find the BZ interface baffling, cumbersome, inefficient, and difficult to learn. Jira: While I have not actually tested it, this is known as a very sophisticated, professional enterprise-grade bug tracker. The commercial developers are PostgreSQL supporters and have offered us this option as their support for our project, for which we are greatful. A. Pass B. Jira is unfortunately not OSS, meaning that we would be 100% dependant on the management policy of Alessian corp. for our use of it. I am not comfortable with this idea, nor is Core, nor several other people. C. Pass D. Pass There is the further issue that based on technical requirements Jira might have to the eternally hosted to postgresql.org, making it difficult to integrate it into the rest of our operations. Request Tracker: perl.org's issue tracker has grown quite sophisticated and added PostgreSQL support. A. Pass -- RT supports commenting on, and modifying, bugs by e-mail, as well as running e-mail "scripts" on creation or alteration of bugs. B. Pass C. Pass -- PostgreSQL and MySQL are fully supported in version 3. D. One possible reservation may be integrating RT with GForge. Andrew D. and some of the GForge people will be checking on how troublesome this will be, and whether or not this might become a standard GForge option in the future. Overall, I personally am liking the new RT and seeing it as our best option for a bug-tracker which would genuinely improve the operations of our community. One thing I'm really attracted to is the ability to create "personal list" so that I can put my personal core-member todo list online. Roundup: This was suggested by a couple of people, including Elein who is quite fond of it. Per my perusing, however, there are
[HACKERS] Server Side PL support
I have been taking a brief look at pltcl, and particularly its ability to preload modules. By comparison with most of the core product this seems to be somewhat out of date and unpolished (e.g. hardcoded path to libpgtcl.so, no use of schemas for the supporting tables, lack of comments). Since my understanding of tcl is extremely rusty, I didn't dig further than that. However, I am interested in getting a similar facility working for plperl, and thus wanted to start a discussion on what general facilities could/should be made available to server side PLs. Or should we just assume that each PL will create it's own support tables? cheers andrew ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] pgAdmin
Quick one: Anyone know how to use Putty to open a connection up under SSH which will allow pgAdmin III to connect to a postgresql database ie. Only access to server postgresql is on is via ssh. Thanks Hammer ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] anoncvs problem
odd, nothing has changed there in months now ... the readers file contains anoncvs and *it* hasn't changed since Aug of '01 ... what are you trying to use for a passwd? I just tried two random ones, and they both connected fine ... On Sun, 29 Feb 2004, Oliver Elphick wrote: > Following instructions on > http://developer.postgresql.org/docs/postgres/cvs.html does not > currently work: > > $ cvs -d :pserver:[EMAIL PROTECTED]:/projects/cvsroot login > Logging in to > :pserver:[EMAIL PROTECTED]:2401/projects/cvsroot > CVS password: > cvs login: authorization failed: server anoncvs.postgresql.org rejected > access to /projects/cvsroot for user anoncvs > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] anoncvs problem
Following instructions on http://developer.postgresql.org/docs/postgres/cvs.html does not currently work: $ cvs -d :pserver:[EMAIL PROTECTED]:/projects/cvsroot login Logging in to :pserver:[EMAIL PROTECTED]:2401/projects/cvsroot CVS password: cvs login: authorization failed: server anoncvs.postgresql.org rejected access to /projects/cvsroot for user anoncvs ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [ADMIN] Schema comparisons
On Saturday 28 February 2004 21:23, Alex J. Avriette wrote:
> On Sat, Feb 28, 2004 at 10:39:40AM -0500, Tom Lane wrote:
> > >> Have you tried diffing pg_dump output? It's not the greatest tool but
> > >> it's helpful.
> > >
> > > Yes, I did. It was quite cumbersome. Especially since the OIDs and
> > > TOC entry numbers didn't matchup; and, since those didn't always match,
> > > the order of objects wasn't quite the same either. So, diff was
> > > throwing a lot of false positives at me.
> >
> > Yeah. CVS-tip pg_dump doesn't show OIDs by default, to make it easier
> > to use for purposes like this. The ordering issue is the bigger problem
> > though. I presume that the object creation history is different in the
> > two databases and so pg_dump's habit of sorting by OID isn't helpful.
>
> I recently had to figure out what was different between the "live" schema
> and the schema in cvs at work. This was a really painful process, and it
> occurred to me that it wouldn't be terribly hard to write a perl program
> to do it (I wound up using vim and diff). Is there interest in such a tool?
> I could probably have one written within a day or two.
>
I've gone the vim-diff route in the past myself, but a nice command line tool
to do it written in perl could certianly be nice. If nothing else you could
toss it up on gborg. Incidentally I think there is already a tool that does
this on sourceforge, but it uses tcl and requires a running webserver, so
it's a little overbearing for most peoples needs imho.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [HACKERS] 7.3.6 for Monday ... still a go?
On Sat, 28 Feb 2004, Tom Lane wrote: > AFAIK we are good to go in terms of the code --- there are no open > issues that I'd want to back-patch to 7.3. We just need release notes. > But I don't have time this weekend to do the release notes either... 'k, once you or Bruce get a sec to do the release notes, I'll tag/bundle it up ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Avoid MVCC using exclusive lock possible?
Neil Conway <[EMAIL PROTECTED]> writes: > Jon Jensen wrote: >> How would you do a rollback if an error occurred mid-query? How would you >> keep your table from being a big pile of junk if a power failure happened >> during the query? > As most non-MVCC database do: by writing WAL records. > In theory, it seems to me that we could implement an overwriting > storage manager for PostgreSQL (i.e. we'd have both MVCC and non-MVCC, > and allow users to choose at runtime). It would require a *lot* of > work, but I can't see any fundamental reason why it wouldn't be possible. It would be possible, but what's the point? There's no evidence that this approach is superior to what we do, and a fair number of reasons to think it's inferior. In particular, with this approach you have to maintain the entire history of a transaction in WAL, so that you have the info available to roll back if it aborts. So the OP's concern about needing 2X the disk space to update his table still applies, it's just in a different place. It's true that it's easier to get the space back when no longer needed --- VACUUM FULL is expensive if it's moving lots of records. But in general I think our VACUUM-based approach is superior to the Oracle-style UNDO approach, because it pushes the maintenance overhead out of foreground transaction processing and into a schedulable background process. Certainly any Oracle DBA will tell you that huge transactions are a headache to handle in Oracle. I think what the OP actually wanted us to offer was non-transaction-safe overwrite-in-place updating. That might be technically feasible (not sure how we'd deal with changes in indexed columns though). But it's so contrary to the basic design goals of the project that I don't think anyone will take the suggestion seriously. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Avoid MVCC using exclusive lock possible?
Jon Jensen wrote: How would you do a rollback if an error occurred mid-query? How would you keep your table from being a big pile of junk if a power failure happened during the query? As most non-MVCC database do: by writing WAL records. In theory, it seems to me that we could implement an overwriting storage manager for PostgreSQL (i.e. we'd have both MVCC and non-MVCC, and allow users to choose at runtime). It would require a *lot* of work, but I can't see any fundamental reason why it wouldn't be possible. -Neil ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [ADMIN] Schema comparisons
On Sunday 29 February 2004 02:01, Tom Lane wrote: > Richard Huxton <[EMAIL PROTECTED]> writes: > > I've been looking at storing $REVISION$ in comments for each object, so > > my install scripts can halt if there is a problem. Not wanting to use my > > only comment slot for this I was thinking about an extension to the > > COMMENT ON statement: > > COMMENT ON TABLE foo IS 'This is where I stroe my foos.'; > > COMMENT ON TABLE foo SECTION 'default' IS 'I meant store my foos.'; > > COMMENT ON TABLE foo SECTION 'revision' IS '1.19'; > > COMMENT ON TABLE foo SECTION 'bar' IS 'baz'; > > This seems a little, um, specialized. Why don't you just keep the info > in a user-defined table? For the same reasons you don't store existing comments in a user-defined table: 1. It's convenient to have a standard (across providers) place for them. 2. It's meta-data, not data. 3. It gets dumped along with my table. If it's just a case of "looks like a waste of time" then I might well waste my time and do it. On the other hand, if it's a case of "unnecessary complication - don't want it in the code" then I'll not bother. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
