Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC
The point here is that fsync-off is only realistic for development or playpen installations. You don't turn it off in a production machine, and I can't see that you'd turn off the full-page-write option either. So we have not solved anyone's performance problem. Yes, this is basically another fsync-like option that isn't for production usage in most cases. Sad but true. Just to make my position perfectly clear: I don't want to see this option shipped in 8.1. Why not ? If your filesystem buffer size matches your pg page size, and you have a persistent write cache, the option makes perfect sense. Andreas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC
On Thu, 2005-07-07 at 11:59 -0400, Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: The point here is that fsync-off is only realistic for development or playpen installations. You don't turn it off in a production machine, and I can't see that you'd turn off the full-page-write option either. So we have not solved anyone's performance problem. Yes, this is basically another fsync-like option that isn't for production usage in most cases. Sad but true. Just to make my position perfectly clear: I don't want to see this option shipped in 8.1. It's reasonable to have it in there for now as an aid to our performance investigations, but I don't see that it has any value for production. Well, this is the first I am hearing that, and of course your position is just one vote. One idea would be to just tie its behavior directly to fsync and remove the option completely (that was the original TODO), or we can adjust it so it doesn't have the same risks as fsync, or the same lack of failure reporting as fsync. I second Tom's objection, until we agree either: - a conclusive physical test that shows that specific hardware *never* causes torn pages - a national/international standard name/number for everybody to ask their manufacturer whether or not they comply with that (I doubt that exists...) - a conclusive check for torn pages that can be added to the recovery code to show whether or not they have occurred. Is there also a potential showstopper in the redo machinery? We work on the assumption that the post-checkpoint block is available in WAL as a before image. Redo for all actions merely replay the write action again onto the block. If we must reapply the write action onto the block, the redo machinery must check to see whether the write action has already been successfully applied before it decides to redo. I'm not sure that the current code does that. Having raised that objection, ISTM that checking for torn pages can be accomplished reasonably well using a few rules... These are simple because we do not update in place for MVCC. Since inserts and vacuums alter the pd_upper and pd_lower, we should be able to do a self-consistency check that shows that all items are correctly placed. If there is non-zero data higher than the pd_higher pointer, then we know that the first sector is torn. If a pointer doesn't match with a row version, then the page is torn. It is possible that the first sector of a page could be undetectably torn if it was nearly full and the item pointer pointed to the first sector. However, for every page touched, the last WAL record to touch that page should have an LSN that matches the database page. In most cases they would match, proving the page was not torn. If they did not match we would have no proof either way, so we would be advised to act as if the page were torn for that situation. Possibly, we could reinstate the idea of putting the LSN at the beginning and end of every page, since that would help prove the first sector (only) was not torn. It is possible that a page could be torn and yet still be consistent, but this could only occur for a delete. Reapplying the delete, whether or not it is visible on the page would overcome that without problem. It is possible that there are one or more sectors of empty space in the middle of a block could be torn, but their contents would still be identical so is irrelevant and can be ignored. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC
On 7/7/05, Bruce Momjian pgman@candle.pha.pa.us wrote: One idea would be to just tie its behavior directly to fsync and remove the option completely (that was the original TODO), or we can adjust it so it doesn't have the same risks as fsync, or the same lack of failure reporting as fsync. I wonder about one thing -- how much impact has the underlying filesystem? I mean, the problem with partial writes to pages is how to handle a situation when the machine looses power and we are not sure if the write was completed or not. But then again, imagine the data is on a filesystem with data journaling (like ext3 with data=journal). There, to my understanding, the data is first written into journal prior to be written to disk drive. Assuming the drive looses power during the process, I guess there would be two possible situations: 1) the modification was committed to journal completely, so we can replay the journal and we are sure the 8kb block is fine. (*) 2) the modification in the journal is not complete. It has not been fully committed to the filesystem journal. And we are safe to assume that drive has an old data. (*) I am not sure if it is true for 8kb-blocks, and of course, I haven't got good knowledge about ext3's journalling and its atomicity... Assuming above are true, it would be interesting to see how ext3 with data=journal and partial writes competes with ext3 data=someother without it. I don't have extensive knowledge with journalling internals, but I thought I would mention it, so people with wider knowledge could put their input here. Regards, Dawid ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Must be owner to truncate?
* Christopher Kings-Lynne ([EMAIL PROTECTED]) wrote: I'm strongly in favour of this patch. I am currently in this situation: 1. Web db user runs as non-superuser, non-owner. 2. I have a table of a tens of thousands of rows that I must delete entirely and rebuild every day at least (pg_trgm word list) 3. It just gets slow over time, even with autovac. 4. I can't vacuum it as i'm not the owner, and I cannot truncate it either. 5. Table has no triggers or FK's whatsoever. So, stephen frost's suggestion would be fantastic. This is a very similar situation to what I'm in, which is why I was asking for the change. :) Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Must be owner to truncate?
* Christopher Kings-Lynne ([EMAIL PROTECTED]) wrote: There are other reasons for restricting it: * truncate takes a much stronger lock than a plain delete does. * truncate is not MVCC-safe. I don't really agree with the viewpoint that truncate is just a quick DELETE, and so I do not agree that DELETE permissions should be enough to let you do a TRUNCATE. Ah. I didn't realise that 2nd point. I don't care so much about the stronger lock in my application. Does truncate not being MVCC-safe cause problems in your situation? It certainly doesn't in mine and I expect the same is true for alot of others in the same situation. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Must be owner to truncate?
* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: * Andrew - Supernews ([EMAIL PROTECTED]) wrote: It's not MVCC-safe even with the AccessExclusive lock; This seems like something which should probably be fixed, You've missed the point entirely: this *cannot* be fixed, at least not without giving up the performance advantages that make TRUNCATE interesting. Alright, can we give that rather significant performance advantage to non-owners in some way then? Perhaps as an extra grant right? This is along the lines of what I was thinking, though I do see that it gets more complicated when dealing with transactions which started before the one committing the truncate (Not a problem in my case, but would have to be dealt with to be MVCC-safe): TRUNCATE is fast because it knows that it's delete'ing everything and so it just creates a new (empty) file and deletes the old file. DELETE goes through the entire file marking each record for deletion and then the system has to wait around for the vacuum'er to come through and clean up the file. New transactions using that file have to scan past all of the deleted tuples until they get vacuumed though. My thinking is along these lines: delete from x;/truncate x; -- Creates a new, empty, file and makes it the 'current' file -- Marks the old file for deletion, but it is kept around for any transactions which were started before the truncate; -- New transactions use the empty file -- Once all transactions using the old file have completed, the old file can be deleted. -- Old transactions which insert rows would need to use the new file or scan the old file for rows which they added, I suppose. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Hmmm 8.1 pg_dumpall cannot dump older db's?
On Thu, Jul 07, 2005 at 23:44:44 -0400, Tom Lane [EMAIL PROTECTED] wrote: The thing that makes this slightly painful is that we can't tell what version we are dumping *from* until we've connected, and so we cannot automagically do the right thing here. I don't really see any other way to do it than the try-and-fallback approach. But after falling back to template1, a version check could be made and if running 8.1 or higher an error message could be displayed. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Hmmm 8.1 pg_dumpall cannot dump older db's?
Bruno Wolff III [EMAIL PROTECTED] writes: But after falling back to template1, a version check could be made and if running 8.1 or higher an error message could be displayed. Once we're connected to template1, we might as well just use it ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC
Simon Riggs [EMAIL PROTECTED] writes: Is there also a potential showstopper in the redo machinery? We work on the assumption that the post-checkpoint block is available in WAL as a before image. Redo for all actions merely replay the write action again onto the block. If we must reapply the write action onto the block, the redo machinery must check to see whether the write action has already been successfully applied before it decides to redo. I'm not sure that the current code does that. The redo machinery relies on the page LSN to tell whether the update has occurred. In the presence of torn pages, that's of course unreliable. Having raised that objection, ISTM that checking for torn pages can be accomplished reasonably well using a few rules... I have zero confidence in this; the fact that you can think of (incomplete, inaccurate) heuristics for heap-page operations doesn't mean you can make it work for indexes. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC
On Thu, 7 Jul 2005, Tom Lane wrote: We still don't know enough about the situation to know what a solution might look like. Is the slowdown Josh is seeing due to the extra CPU cost of the CRCs, or the extra I/O cost, or excessive locking of the WAL-related data structures while we do this stuff, or ???. Need more data. I wonder if a different BLCKSZ would make a difference either way. Say, 1024 bytes instead of the default 8192. - Heikki ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] SQL99 - Nested Tables
On Wed, 6 Jul 2005, Darren Alcorn wrote: I was interested as to if there were plans to develop SQL99 nested tables. Could you give an example of SQL99 nested tables? It might help us who don't know what the term stand for understand the issue. I've browsed through (bur not fully read) sql99 more then once in my life and I don't recall any nested tables. -- /Dennis Björklund ---(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] Hmmm 8.1 pg_dumpall cannot dump older db's?
Tom Lane wrote: Bruno Wolff III [EMAIL PROTECTED] writes: But after falling back to template1, a version check could be made and if running 8.1 or higher an error message could be displayed. Once we're connected to template1, we might as well just use it ... Agreed. In any case, I thought that dropping the postgres database was supposed to be OK if you wanted to work that way. (I also thought fallback was the way all this was supposed to work anyway). cheers andrew ---(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] SQL99 - Nested Tables
Here is a link that has a description. There is also a lot of examples (of syntax as well) on Oracle's website. http://www-db.stanford.edu/~ullman/fcdb/oracle/or-objects.html#nested Darren On Jul 8, 2005, at 9:58 AM, Dennis Bjorklund wrote: On Wed, 6 Jul 2005, Darren Alcorn wrote: I was interested as to if there were plans to develop SQL99 nested tables. Could you give an example of SQL99 nested tables? It might help us who don't know what the term stand for understand the issue. I've browsed through (bur not fully read) sql99 more then once in my life and I don't recall any nested tables. -- /Dennis Björklund ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] SQL99 - Nested Tables
The way I understand Nested Tables and Object Relational Databases, they basically are a layer on top of any old RDBMS that adds ease for the user. I personally believe in normalization theory I just don't like implementing it to avoid JOIN syntax. How difficult would it be to implement (for those more familiar with the code) to write such a layer. It could always be a patch until I can persuade more people that it's a good idea. Darren On Jul 7, 2005, at 7:09 PM, David Fetter wrote: On Thu, Jul 07, 2005 at 12:53:14PM -0700, Josh Berkus wrote: Darren, I was mainly interested because of the simplicity it seems to add for implementing an application using the database. While those accustomed to writing SQL queries using joins and keys might prefer it for many understandable reasons, there is something to be said for multidimensional data structures. It would be like if you _had_ to have multiple arrays to store information in C instead of using a multidimensional array. I'm open to debate on the subject as I'd love to be convinced that Oracle is wrong. Ooops. Our discussion somehow got shifted off list. Suffice it to say that not everyone agrees with me. Where not everyone includes one C. J. Date ;) I think the XML features are important and I'd be more suited writing something more straight forward versus re-inventing the wheel. I brought it up for debate, because I thought it was something that should be thought about. Yes, I just don't see how nested tables relate to XML. To me, they don't relate directly, as tables (nested or otherwise) have no intrinsic row ordering, where XML does. Nested tables is a Good Thing(TM) though :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(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] SQL99 - Nested Tables
On Fri, Jul 08, 2005 at 10:03:57 -0400, Darren Alcorn [EMAIL PROTECTED] wrote: Here is a link that has a description. There is also a lot of examples (of syntax as well) on Oracle's website. http://www-db.stanford.edu/~ullman/fcdb/oracle/or-objects.html#nested So they are permitting sets as a data type and then proceed to use a misleading example (polygons are ordered sets of points). Postgres already provides mechanisms to do the same thing using custom datatypes. What it looks like they provide in addition is efficient(?) relational (i.e. set) operators and foreign key constraints. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Mailing list
This list and all the other PostgreSQL lists suddenly started showing up in my main mailbox instead of being sorted into my PG mailing list folder. It turns out that the X-Mailing-List header that used to appear in all messages has disappeared. Is this permanent or just a misconfiguration that can be fixed? -- D'Arcy J.M. Cain darcy@druid.net | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Must be owner to truncate?
On Thu, 7 Jul 2005, Stephen Frost wrote: * Andrew - Supernews ([EMAIL PROTECTED]) wrote: On 2005-07-07, Stephen Frost [EMAIL PROTECTED] wrote: * truncate is not MVCC-safe. Erm, that's why it gets a stronger lock, so I don't really see what this has to do with it. It's not MVCC-safe even with the AccessExclusive lock; it damages snapshots that were taken before the truncate operation but which don't have a lock on the table yet. The only reason it doesn't break pg_dump is that the first thing that pg_dump does is to take AccessShare locks on every table that it's going to dump. This seems like something which should probably be fixed, but which is probably too late to fix for 8.1. Of course, if we could fix this then it seems like it would be possible for us to just change 'delete from x' to behave as truncate does now given appropriate conditions. I'm not as Doesn't the lock difference between delete and truncate mean that suddenly deletes on x may or may not block concurrent selects to x (depending on whether it's a full table delete and whether x has delete triggers)? Or are you thinking that after making it MVCC safe the lock could be lessened? With the current truncate lock, it seems bad to me for users who want to do: begin; delete from x; -- do inserts and other stuff to the now empty x while still allowing access to x. Especially if whether or not you have access depends on whether there are delete triggers on x. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Mailing list
I just enabled teh RFC2369 stuff, which adds 'List-*' headers to the message ... apparently, that overrides the X-Mailing-List setting ... What you want to check for is: List-ID: pgsql-hackers.postgresql.org instead ... I'm going to look at getting X-Mailing-List added back in though ... On Fri, 8 Jul 2005, D'Arcy J.M. Cain wrote: This list and all the other PostgreSQL lists suddenly started showing up in my main mailbox instead of being sorted into my PG mailing list folder. It turns out that the X-Mailing-List header that used to appear in all messages has disappeared. Is this permanent or just a misconfiguration that can be fixed? -- D'Arcy J.M. Cain darcy@druid.net | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Mailing list
There, that should do it ... On Fri, 8 Jul 2005, Marc G. Fournier wrote: I just enabled teh RFC2369 stuff, which adds 'List-*' headers to the message ... apparently, that overrides the X-Mailing-List setting ... What you want to check for is: List-ID: pgsql-hackers.postgresql.org instead ... I'm going to look at getting X-Mailing-List added back in though ... On Fri, 8 Jul 2005, D'Arcy J.M. Cain wrote: This list and all the other PostgreSQL lists suddenly started showing up in my main mailbox instead of being sorted into my PG mailing list folder. It turns out that the X-Mailing-List header that used to appear in all messages has disappeared. Is this permanent or just a misconfiguration that can be fixed? -- D'Arcy J.M. Cain darcy@druid.net | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: explain analyze is your friend Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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
[HACKERS] Documentation on roles
Hackers, Who is working on providing documentation for roles? I was just going to alter the docs on users to remove the SYSID part, but I noticed there is nothing at all for roles ... _Is_ anybody working on it at all? -- Alvaro Herrera (alvherre[a]alvh.no-ip.org) We are who we choose to be, sang the goldfinch when the sun is high (Sandman) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Documentation on roles
* Alvaro Herrera ([EMAIL PROTECTED]) wrote: Who is working on providing documentation for roles? I was just going to alter the docs on users to remove the SYSID part, but I noticed there is nothing at all for roles ... _Is_ anybody working on it at all? Just to put it out there, I'm not currently working on it. I havn't played w/ SGML much but I can start working on it if no one else is. Sorry for not having had it done already. :/ I had been hoping that someone else was working on it, but sounds like probably not.. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Documentation on roles
Alvaro Herrera [EMAIL PROTECTED] writes: Who is working on providing documentation for roles? Nothing's been done as yet, but Stephen and I are definitely on the hook to provide some. I was just going to alter the docs on users to remove the SYSID part, but I noticed there is nothing at all for roles ... Don't worry about it, will handle that as part of the roles docs update. regards, tom lane PS: Is the above your new primary email address? I need to update my address book if so ... ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Documentation on roles
On Fri, Jul 08, 2005 at 11:50:46AM -0400, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: I was just going to alter the docs on users to remove the SYSID part, but I noticed there is nothing at all for roles ... Don't worry about it, will handle that as part of the roles docs update. Ok, cool. PS: Is the above your new primary email address? I need to update my address book if so ... Yes, thanks. -- Alvaro Herrera (alvherre[a]alvh.no-ip.org) Thou shalt study thy libraries and strive not to reinvent them without cause, that thy code may be short and readable and thy days pleasant and productive. (7th Commandment for C Programmers) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC
Tom, Great. BTW, don't bother testing snapshots between 2005/07/05 2300 EDT and just now --- Bruce's full_page_writes patch introduced a large random negative component into the timing ... Ach. Starting over, then. --Josh -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC
On Fri, 2005-07-08 at 09:47 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Having raised that objection, ISTM that checking for torn pages can be accomplished reasonably well using a few rules... I have zero confidence in this; the fact that you can think of (incomplete, inaccurate) heuristics for heap-page operations doesn't mean you can make it work for indexes. If we can find heuristics that cover some common cases, then I would be happy. Anything that allows us to prove that we don't need to recover is good. If we reduce the unknown state to an acceptable risk, then we are more likely to make use of the performance gain in the real world. Of course, they need to be accurate. Let's not get hung up on my error rate. I don't think we should care too much about indexes. We can rebuild them...but losing heap sectors means *data loss*. Best Regards, Simon Riggs ---(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] Checkpoint cost, looks like it is WAL/CRC
Simon Riggs [EMAIL PROTECTED] writes: I don't think we should care too much about indexes. We can rebuild them...but losing heap sectors means *data loss*. If you're so concerned about *data loss* then none of this will be acceptable to you at all. We are talking about going from a system that can actually survive torn-page cases to one that can only tell you whether you've lost data to such a case. Arguing about the probability with which we can detect the loss seems beside the point. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Fixing domain input
We've seen a couple of bug reports now about how domain constraints aren't checked during input of a parameter that's been deduced to be of a domain type, eg http://archives.postgresql.org/pgsql-interfaces/2005-07/msg9.php http://archives.postgresql.org/pgsql-bugs/2005-07/msg00084.php There's also the long-standing bugaboo that plpgsql doesn't enforce domain constraints. In the first of these threads, I suggested hacking the parameter type resolution rules so that parameters wouldn't be assigned inferred types that are domains, but only their base types. However, that only fixes things when the parameter type is inferred --- if it's specified as a domain by the client, we'd still see the problem. And it does nothing for plpgsql. It occurs to me that a cleaner solution would be to stop giving domain types the same typinput routines as their base types. Instead, give them all a specialized routine domain_in (comparable to array_in) that first invokes the base type's input function and then applies any relevant constraint checks. Likewise for typreceive (but we'd not need to touch the output functions). This has a number of attractions: * Solves both cases of the domain-parameter problem. * Since plpgsql does all type coercions by calling output and input functions, I believe this would automatically fix the bugs in plpgsql. * Allows us to eliminate special cases for domains in parse_coerce.c, copy.c, possibly other places. The main disadvantage of it is that for domains that have CHECK constraints, it's necessary to set up an ExprContext in which the check expressions can be evaluated; and in turn that requires an ExecutorState, plus ExecInitExpr, etc. So there's a pretty fair amount of setup overhead involved, and doing that repeatedly in a series of calls is not attractive from a performance standpoint. (This may be why we didn't do it that way originally, though I don't recall any more whether it was even considered.) We could eliminate this overhead in the case of COPY by adding an API kluge that lets domain_in() detect whether it's being called inside COPY IN, and let it piggyback on COPY's EState, so that the setup overhead is still only paid once per COPY command. In other scenarios such as plpgsql I'm not sure we can afford to try to amortize the setup across multiple calls --- plpgsql is pretty cavalier about the context it calls things in, and I think we'd see huge memory leaks if we didn't free the EState before returning from domain_in(). Still, a slow feature is better than silently failing to apply the constraint, which is where we are now. Thoughts? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
RES: [HACKERS] Pg_autovacuum on FreeBSD
Hi, Thanks a Lot, it works. So when it was changed (use of postgresql=YES), because I1m still use the old way, download sources from postgres mirros, compile and install. Best Regards Rodrigo -Mensagem original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Em nome de Christopher Kings-Lynne Enviada em: quinta-feira, 7 de julho de 2005 23:26 Para: Rodrigo Moreno Cc: pgsql-hackers@postgresql.org Assunto: Re: [HACKERS] Pg_autovacuum on FreeBSD The pg_autovacuum on FreeBSD and pg 803 is not working. Just do nothing, no log, nothing in screen, no daemonize. It was ok on pg746. Could some one help me ? They both work fine for me on my test box... Are you aware that they change the port? You need to put postgresql=YES in your /etc/rc.conf. Chris ---(end of broadcast)--- TIP 3: 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Very vague pg_dump question ...
I'm currently looking into a problem that a client is reporting that pg_dump from 8.0.3 is 'skipping' one of their sequences ... I'm waiting for more info, but am curious if anyone knows (or can think of?) any reason why this might happen? The only thing I can think of is that the sequence is owned by someone other then who the database is being dump'd as, and has no permissions to 'read' it ... but anything I'm not thinking of? thanks ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Very vague pg_dump question ...
Marc G. Fournier [EMAIL PROTECTED] writes: I'm currently looking into a problem that a client is reporting that pg_dump from 8.0.3 is 'skipping' one of their sequences ... I'm waiting for more info, but am curious if anyone knows (or can think of?) any reason why this might happen? The only thing I can think of is that the sequence is owned by someone other then who the database is being dump'd as, and has no permissions to 'read' it ... but anything I'm not thinking of? AFAIK, a permissions problem would result in a pretty obvious error message from pg_dump ... though it's certainly possible for someone to ignore that, especially if they are running pg_dump noninteractively. Skipping in what sense --- no DDL, no setval, both? Is this a separately created sequence or a SERIAL sequence? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Very vague pg_dump question ...
Marc, I'm currently looking into a problem that a client is reporting that pg_dump from 8.0.3 is 'skipping' one of their sequences ... I'm waiting for more info, but am curious if anyone knows (or can think of?) any reason why this might happen? The only thing I can think of is that the sequence is owned by someone other then who the database is being dump'd as, and has no permissions to 'read' it ... but anything I'm not thinking of? I've encountered databases where a continuous upgrade from 7.2 can cause bad dependencies which make certain objects get dropped. Usually running pg_depends fixes this. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Very vague pg_dump question ...
On Fri, 8 Jul 2005, Tom Lane wrote: Marc G. Fournier [EMAIL PROTECTED] writes: I'm currently looking into a problem that a client is reporting that pg_dump from 8.0.3 is 'skipping' one of their sequences ... I'm waiting for more info, but am curious if anyone knows (or can think of?) any reason why this might happen? The only thing I can think of is that the sequence is owned by someone other then who the database is being dump'd as, and has no permissions to 'read' it ... but anything I'm not thinking of? AFAIK, a permissions problem would result in a pretty obvious error message from pg_dump ... though it's certainly possible for someone to ignore that, especially if they are running pg_dump noninteractively. Skipping in what sense --- no DDL, no setval, both? Is this a separately created sequence or a SERIAL sequence? This is what I'm still looking to find out ... all I got was the sequence isn't being recreated in the dump, and when I message the client back, I get a vacation message, so obviously it wasn't a critical bug for them *roll eyes* Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
