Re: [HACKERS] Constraint Exclusion + Joins?
"Brandon Black" <[EMAIL PROTECTED]> writes: > Is this sort of dynamic constraint exclusion on the radar? What sort of fantasy have you got in mind? It doesn't seem likely to me that testing constraints explicitly against each row from the other table would be a win compared to letting the indexes do it. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Constraint Exclusion + Joins?
On 4/30/06, Heikki Linnakangas <[EMAIL PROTECTED]> wrote: On Fri, 28 Apr 2006, Brandon Black wrote: > I dug around in CVS to have a look for this, and I did eventually find > it (well, I found the corresponding docs patch that removed the note > about not working for joins). I see it's in MAIN but not in > 8_1_STABLE. Does that mean it's headed for 8.2.x when that comes > about? (Sorry, I'm not terribly familiar with how you guys handle all > of this). Yes. Perhaps I'm confused about the meaning of the removal of the JOINs-related caveat from the constraint exclusion docs in MAIN. What I was intending to ask about was constraint exclusion kicking in where the constrained column is being joined to a column of another table, with no constants involved. For a contrived example: -- CREATE TABLE basic ( basic_id INTEGER NOT NULL PRIMARY KEY, basic_data TEXT ); CREATE TABLE basic_sub1 ( PRIMARY KEY (basic_id), CHECK ( basic_id >= 0 AND basic_id < 100 ) ) INHERITS (basic); CREATE TABLE basic_sub2 ( PRIMARY KEY (basic_id), CHECK ( basic_id >= 100 AND basic_id < 200 ) ) INHERITS (basic); [...] CREATE TABLE jstuff ( jstuff_id INTEGER NOT NULL PRIMARY KEY, jstuff_data TEXT ); EXPLAIN ANALYZE SELECT basic.* FROM basic JOIN jstuff ON (basic.basic_id = jstuff.jstuff_id) WHERE jstuff_data = 'foo'; -- I tried things like the above with small test data sets against cvs just now on my home machine, and constraint exclusion doesn't seem to apply here (even if all of the joined jstuff rows have ids which only match the constraint for basic_sub3, all basic_subX's seem to get scanned, as is the case I'm seeing in my real code against 8.1.3). Is this sort of dynamic constraint exclusion on the radar? Without it, some inheritance-based partitioning constructs which could otherwise be written as a single query have to be done as loops with seperate nested statements (to extract constants and then manually plug them into the next statement down the chain), which seems to make it more difficult (or nearly impossible) to support varying where/grouping/ordering/count of the resultant inner query rows from client code efficiently without writing a seperate plpgsql function for every possible variation. -- Brandon ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Is a SERIAL column a "black box", or not?
Greg Stark <[EMAIL PROTECTED]> writes: > Tom Lane <[EMAIL PROTECTED]> writes: >> One argument against it is that it'd break trying to log who-did-what >> by the expedient of having a column default CURRENT_USER: >> blame_me text default current_user > No reason there couldn't be a separate function that returns the _actual_ user > rather than the effective user. ... except that the SQL spec says the above should work. In any case, if you're thinking of SESSION_USER, that's not really the right thing either. Imagine that the INSERT is being executed by a SECURITY DEFINER function --- the owner of the function, not the session user, is really the one that should be "blamed" with the insert. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Is a SERIAL column a "black box", or not?
Tom Lane <[EMAIL PROTECTED]> writes: > One argument against it is that it'd break trying to log who-did-what > by the expedient of having a column default CURRENT_USER: > blame_me text default current_user No reason there couldn't be a separate function that returns the _actual_ user rather than the effective user. Sort of like -- well actually the unix precedents here are more confusing than helpful. -- greg ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] InsertXLogFile in pg_resetxlog
Just to update everyone, I've refactored a good amount of the rebuild-control-values-from-WAL code and should have it ready for -patches tomorrow. -- Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation 732.331.1324 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Is a SERIAL column a "black box", or not?
On Mon, May 01, 2006 at 07:47:06PM -0400, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > I think a big point that's being missed here is that SERIAL *is* trying > > to be simple. If you need something more sophisticated or complex you > > shouldn't be using SERIAL at all, you should be doing the stuff > > yourself, by hand. > > I agree with this point in the abstract, but one important proviso is > that it has to be *possible* to do it by hand. One good thing about > the "SERIAL is just a macro" approach is that it keeps us honest about > making sure that SERIAL isn't exploiting any weird internal behaviors > that are hard to duplicate for handmade sequence defaults. We've > already broken that to some extent by having the hidden dependency, > and that in turn means that fairly-reasonable expectations like > "pg_get_serial_sequence should find the column's associated sequence" > don't work on handmade sequences. I don't want to go much further in > that direction. If there's a usability problem we're trying to solve > for SERIALs, we should make sure the problem gets solved for handmade > sequences too. > > regards, tom lane I agree with Tom's proviso and add one of my own, mentioned earlier. It should be easy to use a sequence w/alter sequence almost all of the time. The majority of the crowd should be able to use SERIAL in the majority of cases. One reason I am adamant about this is the v. useful dependencies that are (should be) set between the table and the sequence when it is declared as a SERIAL. --elein > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Is a SERIAL column a "black box", or not?
Sure, but there's no reason that would couldn't allow that with a true black-box SERIAL, either. In fact, you can do it today if you want, just by creating a wrapper around nextval(pg_get_serial_sequence()). Or just use lastval() Chris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Is a SERIAL column a "black box", or not?
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > On Mon, May 01, 2006 at 11:25:33AM -0400, Tom Lane wrote: >> [EMAIL PROTECTED] writes: >>> Ah. I was wondering about that. When I saw the first poster tag >>> 'SECURITY DEFINER' on the end of the expression I assumed it was >>> something that I didn't know you could do... :-) >> >> No, he was inventing syntax that doesn't exist. > Which begs the question, how hard would it be to add that syntax? Well, we could. The arguments against would come down to (a) nonstandard syntax, and (b) possibly needing to make SECURITY a more-reserved word. (We could avoid point (b) by using something that's already pretty reserved --- one idea that comes to mind is DEFAULT ... AS OWNER.) The discussion I was having with Bruno this morning essentially amounted to doing this automatically, rather than having syntax to enable it. I guess that backwards compatibility and spec compatibility might be good arguments for not doing it automatically, though. I could live with something like this if there's not major objections out there. BTW, has anyone looked into whether any of the other major DBs have something similar? You'd think anyone with sequence-like objects would have run into this issue. If there is precedent we might want to follow it. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Is a SERIAL column a "black box", or not?
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > I think a big point that's being missed here is that SERIAL *is* trying > to be simple. If you need something more sophisticated or complex you > shouldn't be using SERIAL at all, you should be doing the stuff > yourself, by hand. I agree with this point in the abstract, but one important proviso is that it has to be *possible* to do it by hand. One good thing about the "SERIAL is just a macro" approach is that it keeps us honest about making sure that SERIAL isn't exploiting any weird internal behaviors that are hard to duplicate for handmade sequence defaults. We've already broken that to some extent by having the hidden dependency, and that in turn means that fairly-reasonable expectations like "pg_get_serial_sequence should find the column's associated sequence" don't work on handmade sequences. I don't want to go much further in that direction. If there's a usability problem we're trying to solve for SERIALs, we should make sure the problem gets solved for handmade sequences too. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Is a SERIAL column a "black box", or not?
Jim C. Nasby wrote: On Mon, May 01, 2006 at 11:25:33AM -0400, Tom Lane wrote: [EMAIL PROTECTED] writes: Ah. I was wondering about that. When I saw the first poster tag 'SECURITY DEFINER' on the end of the expression I assumed it was something that I didn't know you could do... :-) No, he was inventing syntax that doesn't exist. Which begs the question, how hard would it be to add that syntax? I suspect it would be useful in cases besides sequences, and certainly seems to be a lot less of a hassle than having to wrap stuff in an extra function just to get that capability... In all the internal purity and technical concerns it helps PostGreSQL to have an easy migration path for MySQL refugees. Anyways I think its quite clear that there is more need for a black box than a macro. regards, Lukas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Is a SERIAL column a "black box", or not?
On Mon, May 01, 2006 at 11:25:33AM -0400, Tom Lane wrote: > [EMAIL PROTECTED] writes: > > Ah. I was wondering about that. When I saw the first poster tag > > 'SECURITY DEFINER' on the end of the expression I assumed it was > > something that I didn't know you could do... :-) > > No, he was inventing syntax that doesn't exist. Which begs the question, how hard would it be to add that syntax? I suspect it would be useful in cases besides sequences, and certainly seems to be a lot less of a hassle than having to wrap stuff in an extra function just to get that capability... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Is a SERIAL column a "black box", or not?
On Sun, Apr 30, 2006 at 11:45:14AM +0200, Svenne Krap wrote: > Tom Lane wrote: > >In short, I think there's a reasonably good case to be made for losing the > >hidden dependency and re-adopting the viewpoint that saying SERIAL is > >*exactly* the same as making a sequence and then making a default > >expression that uses the sequence. Nothing behind the curtain. > > > I speak more as a user than a hacker, but I do still lurk here ;) > > The way sequences are handled is imho one of the strongest features. The > possiblity to query nextval is bordering on divine. Sure, but there's no reason that would couldn't allow that with a true black-box SERIAL, either. In fact, you can do it today if you want, just by creating a wrapper around nextval(pg_get_serial_sequence()). -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Logging pg_autovacuum
Larry Rosenman wrote: > Jim C. Nasby wrote: >> On Mon, May 01, 2006 at 12:28:21PM -0500, Larry Rosenman wrote: >>> Since both vacuum and autovacuum will be cutting stats records, do >>> we want to just have the autovacuum >>> stats record have the fact that it was autovacuum that did the >>> vacuum? >>> >>> Or, is there a way when vacuum is run by autovacuum that I can get a >>> flag to set that says this (vacuum|analyze) was done by the >>> autovacuum daemon? >>> >>> I agree that the existing stats calls are good, but I'm still >>> reading code to see whether I can determine >>> at the time they are cut that this was autovacuum that did it. >> >> I think noting autovac vacuums/analyzes seperately is pg-dev/vacuum-time-patch-WIP.txt'nice-to-have' >> but not all that important. It'd probably be pretty easy to tell the >> difference just knowing what (if any) manual vacuums your system >> runs. >> >> While we're looking at logging, are you going to add stats stuff for >> the bgwriter as well, or should we add this to the TODO? > > I was going to do that after I got some comfort with what I'm doing > here. I've put a WIP patch up for comments: http://www.lerctr.org/~ler/pg-dev/vacuum-time-patch-WIP.txt this is *NOT* for application, as I still need to add access to the new fields to the views, etc. I'm looking to get comments on it. -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 512-248-2683 E-Mail: ler@lerctr.org US Mail: 430 Valona Loop, Round Rock, TX 78681-3893 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Is a SERIAL column a "black box", or not?
On Sun, Apr 30, 2006 at 01:42:37PM +0300, Hannu Krosing wrote: > > I do see the benefits with regard to simplified implementation, and > > flexibility. > > > > As a compromise, I could see either choice being correct. I don't > > see either direction as being both user friendly and simple. > > You can be user friendly and simple only if the user wants to do simple > things, or if you can exactly predict what a user wants, else you have > to grant some power to the user, and that involves complexity or at > least a learning curve. I think a big point that's being missed here is that SERIAL *is* trying to be simple. If you need something more sophisticated or complex you shouldn't be using SERIAL at all, you should be doing the stuff yourself, by hand. 99% of the time people just need a nice, simple autonumber field that behaves as expected. Namely, you don't have to assign seperate permissions for it, and when you drop the table or column, you don't end up with some other 'hidden' thing still hanging around. In other words, I think we should actually be moving towards #1, and only allow 'tweaking under the hood' that makes good sense. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Automatic free space map filling
On 5/1/06, Martijn van Oosterhoutwrote: On Mon, May 01, 2006 at 01:19:30PM -0500, Jim C. Nasby wrote:> ISTM that tying this directly to maintenance_work_mem is a bit> confusing, since the idea is to keep vacuum transaction duration down so> that it isn't causing dead tuples to build up itself. It seems like it > would be better to have vacuum start a fresh transaction after a certain> number of tuples have died. But since there's no way to actually measure> that without having row level stats turned on, maybe number of > transactions or length of time would be good surrogates.AIUI, vacuum starts a fresh cycle because it's accumulated a certainnumber of dead tuples to clean up. Isn't that what you're asking for?maintenance_work_mem is the limit on the amount of deleted tuple information that can be stored (amongst other things I'm sure)...Hmm, one idea, which may (or may not) be interesting for largetable vacuum is allowing a syntax similar to:VACUUM table WHERE some_col > now()-'1 hour'::interval; I.e. Let vacuum run "piggyback" on some index. This would allowfor a quick vacuum of a fraction of a large table. Especially whenthe table is large, and only some data (new data) are being modified. The vacuum for such a table would: 1. scan the index accoriding to the where criteria and create bitmap of blocks to look at. 2. go through these blocks and vacuum them.Hmm, another perhaps silly idea -- a special index kind for tracking tuple deaths. Ie -- something like whenever tuple is updated/deleted,insert an entry into such index, using last session the tuple is visiblefor as a key. Then, perhaps, vacuum could scan such an index and find tuples which are candidates for removal. I lack the knowledge ofPostgreSQL's internals, so forgive me if I am writing somethingcompletely insane. :) Regards, Dawid
Re: [HACKERS] InsertXLogFile in pg_resetxlog
On 5/1/06, Tom Lane <[EMAIL PROTECTED]> wrote: Definitely bad, very bad. Please put back the lock-checking code. That's what I was thinking. -- Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation 732.331.1324 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] InsertXLogFile in pg_resetxlog
"Jonah H. Harris" <[EMAIL PROTECTED]> writes: > While refactoring the patch, I've noticed that this patch allowed > pg_resetxlog to proceed while the server could potentially be up... is > this the desired behavior or should we require the lock file to be > removed first (as it was prior to this patch)? Definitely bad, very bad. Please put back the lock-checking code. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] InsertXLogFile in pg_resetxlog
On 5/1/06, Jonah H. Harris <[EMAIL PROTECTED]> wrote: I just scanned it, and it's pretty ugly overall. Did one of you guys want to clean it up? If not, I'll do it today. While refactoring the patch, I've noticed that this patch allowed pg_resetxlog to proceed while the server could potentially be up... is this the desired behavior or should we require the lock file to be removed first (as it was prior to this patch)? -- Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation 732.331.1324 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Automatic free space map filling
On Mon, May 01, 2006 at 01:19:30PM -0500, Jim C. Nasby wrote: > ISTM that tying this directly to maintenance_work_mem is a bit > confusing, since the idea is to keep vacuum transaction duration down so > that it isn't causing dead tuples to build up itself. It seems like it > would be better to have vacuum start a fresh transaction after a certain > number of tuples have died. But since there's no way to actually measure > that without having row level stats turned on, maybe number of > transactions or length of time would be good surrogates. AIUI, vacuum starts a fresh cycle because it's accumulated a certain number of dead tuples to clean up. Isn't that what you're asking for? maintenance_work_mem is the limit on the amount of deleted tuple information that can be stored (amongst other things I'm sure)... > Since it sounds like we'd want the transaction to start only at the > start of a clean cycle it could just check the limits at the start of > each cycle. That would prevent it from wrapping the vacuum of each small > table with a (rather pointless) new transaction. Every table has to be in its own transaction since thats the duration of the locks. Vacuum handling multiple tables in one transaction leaves you open to deadlocks. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [HACKERS] Automatic free space map filling
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: >>> Alvaro Herrera <[EMAIL PROTECTED]> writes: So for you it would certainly help a lot to be able to vacuum the first X pages of the big table, stop, release locks, create new transaction, continue with the next X pages, lather, rinse, repeat. >>> Bruce and I were discussing this the other day; it'd be pretty easy to >>> make plain VACUUM start a fresh transaction immediately after it >>> finishes a scan heap/clean indexes/clean heap cycle. > Except that wouldn't help when vacuuming a lot of small tables; each one > would get it's own transaction. What's your point? There's only a problem for big tables, and VACUUM already does use a new transaction for each table. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Automatic free space map filling
On Fri, Apr 28, 2006 at 03:58:16PM -0400, Bruce Momjian wrote: > Tom Lane wrote: > > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > > So for you it would certainly help a lot to be able to vacuum the first > > > X pages of the big table, stop, release locks, create new transaction, > > > continue with the next X pages, lather, rinse, repeat. > > > > > This is perfectly doable, it only needs enough motivation from a > > > knowledgeable person. > > > > Bruce and I were discussing this the other day; it'd be pretty easy to > > make plain VACUUM start a fresh transaction immediately after it > > finishes a scan heap/clean indexes/clean heap cycle. The infrastructure > > for this (in particular, session-level locks that won't be lost by > > closing the xact) is all there. You'd have to figure out how often to > > start a new xact ... every cycle is probably too often, at least for > > smaller maintenance_work_mem settings ... but it'd not be hard or > > involve any strange changes in system semantics. > > Should this be a TODO? One item of discussion was taht people should > just increase their workmem so the job can be done faster in larger > batches. Except that wouldn't help when vacuuming a lot of small tables; each one would get it's own transaction. ISTM that tying this directly to maintenance_work_mem is a bit confusing, since the idea is to keep vacuum transaction duration down so that it isn't causing dead tuples to build up itself. It seems like it would be better to have vacuum start a fresh transaction after a certain number of tuples have died. But since there's no way to actually measure that without having row level stats turned on, maybe number of transactions or length of time would be good surrogates. Since it sounds like we'd want the transaction to start only at the start of a clean cycle it could just check the limits at the start of each cycle. That would prevent it from wrapping the vacuum of each small table with a (rather pointless) new transaction. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Logging pg_autovacuum
Jim C. Nasby wrote: > On Mon, May 01, 2006 at 12:28:21PM -0500, Larry Rosenman wrote: >> Since both vacuum and autovacuum will be cutting stats records, do we >> want to just have the autovacuum >> stats record have the fact that it was autovacuum that did the >> vacuum? >> >> Or, is there a way when vacuum is run by autovacuum that I can get a >> flag to set that says this (vacuum|analyze) was done by the >> autovacuum daemon? >> >> I agree that the existing stats calls are good, but I'm still reading >> code to see whether I can determine >> at the time they are cut that this was autovacuum that did it. > > I think noting autovac vacuums/analyzes seperately is 'nice-to-have' > but not all that important. It'd probably be pretty easy to tell the > difference just knowing what (if any) manual vacuums your system runs. > > While we're looking at logging, are you going to add stats stuff for > the bgwriter as well, or should we add this to the TODO? I was going to do that after I got some comfort with what I'm doing here. -- Larry Rosenman Database Support Engineer PERVASIVE SOFTWARE. INC. 12365B RIATA TRACE PKWY 3015 AUSTIN TX 78727-6531 Tel: 512.231.6173 Fax: 512.231.6597 Email: [EMAIL PROTECTED] Web: www.pervasive.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] CVSup server seems to be down
On Mon, 1 May 2006, Alvaro Herrera wrote: Hi, Since yesterday (at least), the CVSup server has been down: $ cvsup -g cvsup.pgsql Cannot connect to cvsup.postgresql.org: Connection refused Will retry at 14:08:50 I hope it has not been deprecated? nope, just upgrade postgresql.org to FreeBSD 6.x/amd64 ... should be fixed now ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Logging pg_autovacuum
On Mon, May 01, 2006 at 12:28:21PM -0500, Larry Rosenman wrote: > Since both vacuum and autovacuum will be cutting stats records, do we > want to just have the autovacuum > stats record have the fact that it was autovacuum that did the vacuum? > > Or, is there a way when vacuum is run by autovacuum that I can get a > flag to set that says this > (vacuum|analyze) was done by the autovacuum daemon? > > I agree that the existing stats calls are good, but I'm still reading > code to see whether I can determine > at the time they are cut that this was autovacuum that did it. I think noting autovac vacuums/analyzes seperately is 'nice-to-have' but not all that important. It'd probably be pretty easy to tell the difference just knowing what (if any) manual vacuums your system runs. While we're looking at logging, are you going to add stats stuff for the bgwriter as well, or should we add this to the TODO? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Logging pg_autovacuum
On Sat, Apr 29, 2006 at 10:05:51AM -0700, Mark Dilger wrote: > Should we make the whole postgres logging system configurable, similar to > log4j > (or log4perl) rather than special-casing the autovacuum logs? Do we want to > see > options added piecemeal to the conf file such as "autovacuum_messages=silent"? This does sound like a better idea, especially since there's call for improving bgwriter and stats collector logging as well. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] CVSup server seems to be down
Hi, Since yesterday (at least), the CVSup server has been down: $ cvsup -g cvsup.pgsql Cannot connect to cvsup.postgresql.org: Connection refused Will retry at 14:08:50 I hope it has not been deprecated? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Logging pg_autovacuum
On Thu, Apr 27, 2006 at 08:12:23PM +, Chris Browne wrote: > Having some ability to collect statistics about "we recovered 42 pages > from table foo at 12:45" would seem useful both from an immediate > temporal perspective where it could suggest whether specific tables > were being vacuumed too (seldom|often), and from a more > global/analytic perspective of perhaps suggesting better kinds of > vacuuming policies. (In much the same way that I'd like to have some > way of moving towards an analytically better value for > default_statistics_target than 10...) > > If people are interested, I could provide a copy of the "analyze > VACUUM stats" script... Yeah, I'm interested. Though just like EXPLAIN output this seems like another case where having the ability to log this information into the database itself would be incredibly valuable for anyone wanting to do later analysis. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Logging pg_autovacuum
Martijn van Oosterhout wrote: > On Fri, Apr 28, 2006 at 04:08:41PM -0400, Robert Treat wrote: >> The first is to add a column(s) to pg_class to hold last >> vaccum/analyze time >> for each table. The upsides would be that this puts the information >> in a readily accessable place that can be viewed from third party >> tools and >> queried against for easier management along with accomplishing what >> the >> current logging is giving you. > > You know, rather than adding new columns to pg_class, why not extend > the stats collector to collect this information. This seems to fall > well within its mandate and it already has the necessary > infrastructure to store info like this. The people likely to want > this sort of info are likely to be running the stats collector > anyway. The GUC then becomes. > > stats_autovacuum= on > > No more fiddling with log levels or parsing log file, just: > > select from pg_stats_autovacuum; > > If this table is empty, it isn't running. How often do you want to > know if it was running 5 hours ago? > > Thoughts? [put just to the list, unless folks still want personal CC's.]. Since both vacuum and autovacuum will be cutting stats records, do we want to just have the autovacuum stats record have the fact that it was autovacuum that did the vacuum? Or, is there a way when vacuum is run by autovacuum that I can get a flag to set that says this (vacuum|analyze) was done by the autovacuum daemon? I agree that the existing stats calls are good, but I'm still reading code to see whether I can determine at the time they are cut that this was autovacuum that did it. Thanks for the nice introductory project :) LER -- Larry Rosenman Database Support Engineer PERVASIVE SOFTWARE. INC. 12365B RIATA TRACE PKWY 3015 AUSTIN TX 78727-6531 Tel: 512.231.6173 Fax: 512.231.6597 Email: [EMAIL PROTECTED] Web: www.pervasive.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Is a SERIAL column a "black box", or not?
Bruno Wolff III <[EMAIL PROTECTED]> writes: > The summary is that I was suggesting that default expressions, triggers and > constraints should all run as the table owner instead of the invoker as > there was little use for them to need the access of the invoker, while there > was benefit in having them run as the owner. I can see doing this for defaults and constraints, but there is a serious objection for triggers: you could not use a trigger with new.blame_me := current_user; as a more-bulletproof implementation of the tracking column I was on about before. Furthermore, there already is a way to express the desired behavior for triggers (when it is in fact the desired behavior): make the trigger function SECURITY DEFINER. > In addition there is a mild security issue in that default expressions > and constraints could be used as trojans so that inserting data into a > table could allow that table owner the ability to do things they > shouldn't be doing to the invoker's table. This risk would exist anyway unless you put strange constraints on RESET ROLE. It's in general no problem to get back to the outermost level's security settings. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Is a SERIAL column a "black box", or not?
I went back to see if I could find the discussion about this in the past. It was less than I thought. Most it was me posting with some feedback from Rod Taylor. The thread started with the subject "What user to defaults execute as?" on general, but I mutated the subject to "setuid for defaults, constraints and triggers (Was: What user to [sic] defaults execute as?)". The summary is that I was suggesting that default expressions, triggers and constraints should all run as the table owner instead of the invoker as there was little use for them to need the access of the invoker, while there was benefit in having them run as the owner. In addition there is a mild security issue in that default expressions and constraints could be used as trojans so that inserting data into a table could allow that table owner the ability to do things they shouldn't be doing to the invoker's table. Though in practice anyone granted to the ability to create functions (which you need to exploit this) is already very highly trusted. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] InsertXLogFile in pg_resetxlog
On 5/1/06, Tom Lane <[EMAIL PROTECTED]> wrote: This certainly looks like it was written by someone who'd just learned about lists yesterday :-(. I wonder how many other problems there are in that resetxlog patch? I didn't bother to look at it at all myself. Anyone have time to review it? I just scanned it, and it's pretty ugly overall. Did one of you guys want to clean it up? If not, I'll do it today. -- Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation 732.331.1324 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] InsertXLogFile in pg_resetxlog
Martijn van Oosterhout writes: > May I propose the entire part of that function after the comment /* the > list is empty. */ be replaced with something like the following (or > whatever idiom people prefer for singly-linked lists): This certainly looks like it was written by someone who'd just learned about lists yesterday :-(. I wonder how many other problems there are in that resetxlog patch? I didn't bother to look at it at all myself. Anyone have time to review it? http://archives.postgresql.org/pgsql-committers/2006-04/msg00299.php regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Is a SERIAL column a "black box", or not?
[EMAIL PROTECTED] writes: > Ah. I was wondering about that. When I saw the first poster tag > 'SECURITY DEFINER' on the end of the expression I assumed it was > something that I didn't know you could do... :-) No, he was inventing syntax that doesn't exist. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Is a SERIAL column a "black box", or not?
On Mon, May 01, 2006 at 11:18:13AM -0400, Tom Lane wrote: > [EMAIL PROTECTED] writes: > > If the user is specifying the default expression, they can specify > > SECURITY DEFINER themselves, yes? > Not unless they write a wrapper function to be a security definer > and call nextval(). Ah. I was wondering about that. When I saw the first poster tag 'SECURITY DEFINER' on the end of the expression I assumed it was something that I didn't know you could do... :-) mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Is a SERIAL column a "black box", or not?
[EMAIL PROTECTED] writes: > If the user is specifying the default expression, they can specify > SECURITY DEFINER themselves, yes? Not unless they write a wrapper function to be a security definer and call nextval(). regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Is a SERIAL column a "black box", or not?
On Mon, May 01, 2006 at 10:29:12AM -0400, Tom Lane wrote: > A cheesy compromise would be to switch userid for default-evaluation > only if the expression contains any volatile functions. I find this > idea pretty ugly, but it would allow us to still behave per-spec > for CURRENT_USER while getting the results we want for nextval(). > (current_user() is marked "stable".) If the user is specifying the default expression, they can specify SECURITY DEFINER themselves, yes? So it's really only the default definition of 'SERIAL' columns for new tables. SERIAL isn't per-spec, yes? So it could change in 8.2 without problem? Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] RELKIND_SPECIAL
Alvaro Herrera <[EMAIL PROTECTED]> writes: > How about we remove RELKIND_SPECIAL? It was there only to support > the XactLockTable hack, but we don't need that anymore. Go for it. Don't forget to remove the documentation mentions (catalog.sgml at least). Might be a good idea to leave the code for the case in psql/describe.c, though, just so psql doesn't choke if run on an old database? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Is a SERIAL column a "black box", or not?
Bruno Wolff III <[EMAIL PROTECTED]> writes: > I suggested a long time ago that default expressions should always be > executed as the owner of the table. This got shot down, but I don't remember > if it was because people thought the idea was bad in itself or if it was > the work involved (which I wasn't in a position to do). The more I think about it the better I like that idea. It seems like a natural and unsurprising semantics, whereas ideas involving implicit GRANTs seem to me to violate the principle of least surprise. It fixes the problem for both serial and handmade sequences --- indeed, it fixes related problems for functions other than nextval(). And it doesn't require introduction of any new syntax. One argument against it is that it'd break trying to log who-did-what by the expedient of having a column default CURRENT_USER: blame_me text default current_user You could still make use of session_user for this, but that's not really the right thing if the INSERT is being done from a security-definer function. I don't find this objection very compelling, because such a default is pretty fragile anyway: it could be broken just by assigning explicitly to the column. You'd be better off doing the logging by having a BEFORE trigger that sets the column value. However, I suspect that the SQL spec demands that such a default behave as it currently does, which means that changing this would violate spec. A cheesy compromise would be to switch userid for default-evaluation only if the expression contains any volatile functions. I find this idea pretty ugly, but it would allow us to still behave per-spec for CURRENT_USER while getting the results we want for nextval(). (current_user() is marked "stable".) regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] WITH/WITH RECURSIVE implementation discussion
On 5/1/06, Mark Cave-Ayland <[EMAIL PROTECTED]> wrote: The latest discussion I found related to this appears to be here: http://archives.postgresql.org/pgsql-hackers/2005-11/msg00564.php which indicates that Jonah is hoping to work on this for 8.2, but I don't see this item as being worked on in the TODO list - does that mean that development on this has halted? No, in between all my other work here at EnterpriseDB, I'm still working on it. I hope to get it submitted to -patches for review in the next two weeks. -- Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation 732.331.1324 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] WITH/WITH RECURSIVE implementation discussion
Hi folks, Looking ahead at some of the projects I'll be working on in the future, I'm seeing that having an implementation of WITH/WITH RECURSIVE for working with tree structures is going to be a very useful feature and so would like to re-start the discussions on this to see whether this could be achieved for PG 8.2. The latest discussion I found related to this appears to be here: http://archives.postgresql.org/pgsql-hackers/2005-11/msg00564.php which indicates that Jonah is hoping to work on this for 8.2, but I don't see this item as being worked on in the TODO list - does that mean that development on this has halted? Kind regards, Mark. WebBased Ltd 17 Research Way Plymouth PL6 8BT T: +44 (0)1752 797131 F: +44 (0)1752 791023 http://www.webbased.co.uk http://www.infomapper.com http://www.swtc.co.uk This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] inet increment with int
On Sat, Apr 29, 2006 at 10:24:48PM -0400, Bruce Momjian wrote: > > FYI, 8.2 will have this and more based on this applied patch: > > Add INET/CIDR operators: and, or, not, plus int8, minus int8, and inet > minus inet. I know, I'm already using it :-) Thanks, Patrick ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] InsertXLogFile in pg_resetxlog
There's been some new code added to pg_resetxlog which is confusing enough that Coverity is convinced there's a possible memory leak in InsertXLogFile. I think it may actually be a bug. At the least this bit needs rewriting to make it clearer what it does. What I think happens is this: 1. Assume the xlogfilelist has more than two entries already 2. In the loop CmpXLogFileOT returns true the first time, false the second At this point Prev = xlogfilelist and Curr = xlogfilelist->next and append2end = false. With these conditions all if tests fail and the file is never linked into the list. May I propose the entire part of that function after the comment /* the list is empty. */ be replaced with something like the following (or whatever idiom people prefer for singly-linked lists): --- cut --- /* currp points to memory location where the pointer needs to be updated */ XLogFileName **currp = &xlogfilelist; while( *currp && CmpXLogFileOT( NewSegFile, *currp ) ) currp = &( (*currp)->next ); NewSegFile->next = *currp; *currp = NewSegFile; --- cut --- Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature