Re: [HACKERS] Problems starting Template1...
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, Oct 23, 2006 at 02:32:47PM -0400, luis garcia wrote: Hello, I'm from Venezuela, and I've been making some modifications to Postgre's Catalog, but it seems to be a problem creating the Template1 Database. When the creation of the database is starting this is what happens: [EMAIL PROTECTED]:~ /home/luis/pgsql/bin/initdb -D /home/luis/pgsql/data/ [...] incorrect number of columns in row (expected 32, got 29) ^ [...] I just added three more fields to pg_class structure (pg_class.h) just like this: Note that you are changing the core data structures of PostgreSQL. You should change all the programs that work with them too :-) More to the point: what are you trying to achieve? Regards - -- tomás -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (GNU/Linux) iD8DBQFFQGUiBcgs9XrR2kYRAgOuAJ424LQIicJORfaQATng9xQzk9Fq0wCeKRW4 bUtdKg/bsHA3Mc1XZg10k3U= =/EF7 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] New CRC algorithm: Slicing by 8
On Tue, 2006-10-24 at 15:42 -0400, Gregory Stark wrote: Tom Lane [EMAIL PROTECTED] writes: Simon is essentially arguing that if we are willing to assume no incomplete write() we may as well assume it for WAL too. This seems to me to be raising the risk significantly, but I admit that I can't put my finger on why exactly. Actually I think we can deal with torn pages in the WAL more easily than in database files anyways. In database files we need to get the entire page correctly one way or the other so we need full_page_writes in order to be deal properly. In the WAL we just need to be able to detect torn pages and stop reading WAL at that point. That's easier and doesn't really need a CRC. We could just adopt the Sybase strategy of storing a unique id number every 512 bytes throughout the WAL page. If those numbers don't match then we have a torn page; the system crashed at that point and we should stop reading WAL pages. Right, but I'm only suggesting using this if your safe from torn pages anyhow. I'm not suggesting anyone does wal_checksum = off when full_page_writes = on. I do think you're right: we're much less exposed by a torn page in WAL than we are for the stable database. I've looked into this in more depth following your suggestion: I think it seems straightforward to move the xl_prev field from being a header to a trailer. That way when we do the test on the back pointer we will be assured that there is no torn page effecting the remainder of the xlrec. That would make it safer with wal_checksum = off. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Problems starting Template1...
Hi, we all ready found the problem. I was creating the pg_class structure for 32 fields, but in the creation of Template1 I just inserted 29 initializationvalues, so the problem was that.Just like this: Wrong CODE:DATA(insert OID = 1259 ( pg_class PGNSP 83 PGUID 0 1259 0 0 0 0 0 f f r 32 0 0 0 0 0 t f f f f null 0 f _null_ ));Right CODE:DATA(insert OID = 1259 ( pg_class PGNSP 83 PGUID 0 1259 0 0 0 0 0 f f r 32 0 0 0 0 0 t f f f f null 0 f f null 0 _null_ )); If you see deep in there in the Wrong CODE there are just 29 values, butin the Right CODE are 32, just as it should be.Thanks anyway...2006/10/26, [EMAIL PROTECTED] [EMAIL PROTECTED]:-BEGIN PGP SIGNED MESSAGE- Hash: SHA1On Mon, Oct 23, 2006 at 02:32:47PM -0400, luis garcia wrote: Hello, I'm from Venezuela, and I've been making some modifications to Postgre's Catalog, but it seems to be a problem creating the Template1 Database. When the creation of the database is starting this is what happens: [EMAIL PROTECTED]:~ /home/luis/pgsql/bin/initdb -D /home/luis/pgsql/data/[...] incorrect number of columns in row (expected 32, got 29) ^[...] I just added three more fields to pg_class structure (pg_class.h) just like this:Note that you are changing the core data structures of PostgreSQL. You should change all the programs that work with them too :-)More to the point: what are you trying to achieve?Regards- -- tomás-BEGIN PGP SIGNATURE-Version: GnuPG v1.4.1 (GNU/Linux) iD8DBQFFQGUiBcgs9XrR2kYRAgOuAJ424LQIicJORfaQATng9xQzk9Fq0wCeKRW4bUtdKg/bsHA3Mc1XZg10k3U==/EF7-END PGP SIGNATURE--- Luis D. García M.Telf: 0414-3482018 - FACYT - UC -- Computación -
Re: [HACKERS] COPY does not work with regproc and aclitem
Tom Lane napsal(a): Zdenek Kotala [EMAIL PROTECTED] writes: I prepared patch which use oid output function instead regproc output. This change works only for COPY TO command. This is not a bug and we're not going to fix it, most especially not like that. OK, The behavior of regproc type is described in the documentation, but if we don't fix it, than Some error message like Regproc data type is not supported by COPY TO command could be useful. Because you find that something is wrong when you want to restore data back and it should be too late. Zdenek ---(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] COPY does not work with regproc and aclitem
Zdenek Kotala wrote: Tom Lane napsal(a): Zdenek Kotala [EMAIL PROTECTED] writes: I prepared patch which use oid output function instead regproc output. This change works only for COPY TO command. This is not a bug and we're not going to fix it, most especially not like that. OK, The behavior of regproc type is described in the documentation, but if we don't fix it, than Some error message like Regproc data type is not supported by COPY TO command could be useful. Because you find that something is wrong when you want to restore data back and it should be too late. But it works as expected. If the approach you suggest would be one we would take, then it should emit the same error on SELECT as well, shouldn't we? I think the problem is that regproc COPY is not useful to you for your particular use case. But there are workarounds, like the one I suggested and you promptly ignored. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] New CRC algorithm: Slicing by 8
Simon Riggs [EMAIL PROTECTED] writes: I've looked into this in more depth following your suggestion: I think it seems straightforward to move the xl_prev field from being a header to a trailer. That way when we do the test on the back pointer we will be assured that there is no torn page effecting the remainder of the xlrec. That would make it safer with wal_checksum = off. Hm. I think in practice this may actually help reduce the exposure to torn pages. However in theory there's no particular reason to think the blocks will be written out in physical order. The kernel may sync its buffers in some order dictated by its in-memory data structure and may end up coming across the second half of the 8kb page before the first half. It may even lie earlier on disk than the first half if the filesystem started a new extent at that point. If they were 4kb pages there would be fewer ways it could be written out of order, but even then the hard drive could find a bad block and remap it. I'm not sure what level of granularity drives remap at, it may be less than 4kb. To eliminate the need for the CRC in the WAL for everyone and still be safe from torn pages I think you have to have something like xl_prev repeated every 512b throughout the page. But if this is only an option for systems that don't expect to suffer from torn pages then sure, putting it in a footer seems like a good way to reduce the exposure somewhat. Putting it in both a header *and* a footer might be even better. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] COPY does not work with regproc and aclitem
Alvaro Herrera napsal(a): Zdenek Kotala wrote: Tom Lane napsal(a): Zdenek Kotala [EMAIL PROTECTED] writes: I prepared patch which use oid output function instead regproc output. This change works only for COPY TO command. This is not a bug and we're not going to fix it, most especially not like that. OK, The behavior of regproc type is described in the documentation, but if we don't fix it, than Some error message like Regproc data type is not supported by COPY TO command could be useful. Because you find that something is wrong when you want to restore data back and it should be too late. But it works as expected. If the approach you suggest would be one we would take, then it should emit the same error on SELECT as well, shouldn't we? It is right. I think the problem is that regproc COPY is not useful to you for your particular use case. But there are workarounds, like the one I suggested and you promptly ignored. Yes, I read your suggestion It is useful form me thanks for that. But I thought how to remove that regproc limitation or how to avoid some confusing. Current mention about regproc limitation/behavior in the documentation is really best solution. By the way, If I read carefully your suggestion, Tom's answer and documentation, correct solution (theoretical) is replace regproc by regprocedure datatype in the catalog, but there is problem in the boostrap phase? Thanks Zdenek ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_get_domaindef()
On Oct 26 03:33, FAST PostgreSQL wrote:
I couldn't find the CONSTRAINT name ('testconstraint' in this case) being
stored in the system catalog. Any idea where I can find it?
AFAIK, it is passed to the related procedure via a DomainIOData struct
that fcinfo-flinfo-fn_extra points to. (See domain_in() in
backend/utils/adt/domains.c)
Regards.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] Replication documentation addition
On Wed, Oct 25, 2006 at 05:46:33PM -0400, Bruce Momjian wrote: Josh Berkus wrote: So, like www.postgresql.org/docs/techdocs/replication? That would work. Yes. I like that idea, but I think that the URL needs to be decided upon, needs to be stable, and needs to be put into the docs. (I don't see it ATM, I guess because the URL isn't chosen yet?) We get so many questions about what replication system that I'm sure people are looking for outlines. A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_get_domaindef()
On Oct 26 05:27, Volkan YAZICI wrote:
On Oct 26 03:33, FAST PostgreSQL wrote:
I couldn't find the CONSTRAINT name ('testconstraint' in this case) being
stored in the system catalog. Any idea where I can find it?
AFAIK, it is passed to the related procedure via a DomainIOData struct
that fcinfo-flinfo-fn_extra points to. (See domain_in() in
backend/utils/adt/domains.c)
Ah, please excuse my wrong answer. See GetDomainConstraints() function
in the same file.
Regards.
---(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] Nasty btree deletion bug
Tom Lane wrote: I wrote: I've been analyzing Ed L's recent report of index corruption: http://archives.postgresql.org/pgsql-general/2006-10/msg01183.php Auch. That's nasty indeed. So I think the rule needs to be don't delete the rightmost child unless it's the only child, in which case you can delete the parent too --- but the same restriction must be observed at the next level up. The concept of a half-dead page would remain, but it'd be a transient state that would normally only persist for a moment between atomic page-delete actions. If we crash between two such actions, the half-dead page would remain present, but would be found and cleaned up by the next VACUUM. In the meantime it wouldn't cause any problem because the keyspace it gives up will belong to a sibling of the same parent at whatever level the delete is ultimately supposed to stop at, and so inserts and even splits in that keyspace won't create an inconsistency. I don't understand how this in the meantime thing works. I tried to work out a step-by-step example, could you take a look at it? See http://users.tkk.fi/~hlinnaka/pgsql/btree-deletion-bug/ ... Comments? Have I missed anything? It took me a lot of time with pen and paper to understand the issue. And I'm not sure I still understood it fully. The logic is very complex, which is bad for maintainability in itself :(. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Nasty btree deletion bug
Heikki Linnakangas [EMAIL PROTECTED] writes: I don't understand how this in the meantime thing works. I tried to work out a step-by-step example, could you take a look at it? See http://users.tkk.fi/~hlinnaka/pgsql/btree-deletion-bug/ [ looks at that for a bit... ] Yeah, you're right. Once the deletion is completed, the F lower-bound key will disappear from the grandparent, which would restore consistency --- but we could have already delivered wrong search answers, so that won't do. In theory, given a slow-enough-moving VACUUM process, this could happen even without a crash. So I think that means we have to go over to the other plan of locking everything all the way up to the top of the deletion before we start doing it --- and also, we'll need crash recovery logic to complete the incomplete deletion, same as for incomplete inserts. Yech --- more code than I was hoping to have to write, and more concurrency hit too. OTOH we might be able to get rid of the notion of half dead, which would allow some simplifications. Thanks for taking a close look! regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Replication documentation addition
With no new additions submitted today, I have moved my text into our SGML documentation: http://momjian.us/main/writings/pgsql/sgml/failover.html Please let me know what additional changes are needed. --- bruce wrote: Richard Troy wrote: Here is a new replication documentation section I want to add for 8.2: ftp://momjian.us/pub/postgresql/mypatches/replication ...Read the document, as promissed... First paragraph, (fail over) is inconsistent with title, failover, as are other spots throughout the document. The whole document should be consistent and I vote for failover and not fail over. OK. Fixed to failover Fourth paragraph, This sync problem is the fundamental difficulty for servers working together; Sync problem hasn't been defined. Actually, you're talking about the consistent attribute of the acid properties of all competent databases: Atomic, Consistency, Isolation, and Durability. At least define the term you are using - probably most easily done in the preceeding paragraph. OK, sync problem term removed, and spelled out fully. The fifth paragraph needs a lot more help, I think. Howabout this alternative: So called two phaised commit was developed as a strategy in which two or more databases are updated simultaneously and none of the data is committed until all are committed. This guarantees consistency between the databases with all propagation delay being absorbed by the writer at write time. There are times when this propagation delay is large, so sometimes alternatives are worked out which we'll call here asynchronous updates, however, in these cases, there is always a window of time in which some transaction can be lost should a failure occurr. For this reason, asynchronous updates are only used when the possibility of such losses is acceptible. I have modified the paragraph to use some of your terms. Paragraphs six through to shared disk failover seem very awkward to me. I don't like them at all. Shared disk failover has nothing to do with the sync problem as it's not a multiple-database solution. It's an uptime, 24 X 7 X 365 issue. Further, it also has nothing to do with disk arrays, though it is often used with RAID to help avoid disk based corruption problems. Yes, please see updated version. I removed the sync problem term from there. The point about Warm Standby needs to include a warning about WAL that it MUST be sensitive to the semantics of the database design or else it's fatally flawed. I'm talking about referential integrety. That is to say, it's inappropriate to capture updates on a table by table basis, as some such systems do, (I have no idea what's done by anyone in the PG world on this right now) because an update to one table (esp. inserts) very often go hand in glove with updates in other tables and to get one without the other can corrupt a database. We don't have that problem. We recover only full transactions. The description of Continuously running replication server should include the critical caveat - repeated if you think it's already said elsewhere - that it is ONLY suitable for applications in which a loss of (missing) update data doesn't matter. For example, an airline reservation system would be an inappropriate application for such a solution because what seats are available cannot be guaranteed to be correct. I have added note about data loss for the Slony item. Regarding data partitioning, I strongly disagree with the opening sentence in that it doesn't split a database into sets, it splits tables into sets. OK, changed. Data partitioning is often done within a single database on a single server and therefore, as a concept, has nothing whatsoever to do with different servers. Similarly, the second paragraph of this section is Uh, why would someone split things up like that on a single server? problematic. Please define your term first, then talk about some implementations - this is muddying the water. Further, there are both vertical and horizontal partitioning - you mention neither - and each has its own distinct uses. If partitioning is mentioned, it should be more complete. Uh, what exactly needs to be defined. Next, Query Broadcast Load Balancing... also needs a lot of work. First, it's foremost in my memory that sending read queries everywhere and returning the first result set back is a key way to improve application performance at the cost of additional load on other systems - I guess that's not at all what the document is after here, but it's a worthy part of a dialogue on broadcasting queries. In other words, this has more parts to it than just what the document now entertains. Secondly, the document Uh, do we want to go into that here? I guess I could. doesn't
Re: [DOCS] [HACKERS] Replication documentation addition
On Wed, Oct 25, 2006 at 08:42:07PM -0400, Bruce Momjian wrote: Jim C. Nasby wrote: Something else worth doing though is to have a paragraph explaining why there's no built-in replication. I don't have time to write something right now, but I can do it later tonight if no one beats me to it. I thought that was implied in the early paragraph about why there are many solutions. I think we should explicitely spell it out, especially considering how many times people ask about it. How about... This multitude of choices is why PostgreSQL does not ship with a replication solution by default; any bundled solution would only satisfy a subset of replication needs. (sorry for the non-standard patch, but anoncvs isn't sync'd up yet). -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) *** failover.sgml.org Thu Oct 26 10:32:45 2006 --- failover.sgml Thu Oct 26 10:55:03 2006 *** *** 29,35 working together. Because there is no single solution that eliminates the impact of the sync problem for all use cases, there are multiple solutions. Each solution addresses this problem in a different way, and ! minimizes its impact for a specific workload. /para para --- 29,37 working together. Because there is no single solution that eliminates the impact of the sync problem for all use cases, there are multiple solutions. Each solution addresses this problem in a different way, and ! minimizes its impact for a specific workload. This multitude of choices is ! why PostgreSQL does not ship with a replication solution by default; any ! bundled solution would only satisfy a subset of replication needs. /para para ---(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: [DOCS] [HACKERS] Replication documentation addition
Jim C. Nasby wrote: On Wed, Oct 25, 2006 at 08:42:07PM -0400, Bruce Momjian wrote: Jim C. Nasby wrote: Something else worth doing though is to have a paragraph explaining why there's no built-in replication. I don't have time to write something right now, but I can do it later tonight if no one beats me to it. I thought that was implied in the early paragraph about why there are many solutions. I think we should explicitely spell it out, especially considering how many times people ask about it. How about... This multitude of choices is why PostgreSQL does not ship with a replication solution by default; any bundled solution would only satisfy a subset of replication needs. The problem is that we do have some solutions in our code, like doing data partitioning in the application, warm standby, or using a shared disk for failover, so how do we spell that out? I say there are multiple solutions, but I don't see how I can say that all are external and not included. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [DOCS] [HACKERS] Replication documentation addition
Joshua D. Drake wrote: Bruce Momjian wrote: Jim C. Nasby wrote: On Wed, Oct 25, 2006 at 08:42:07PM -0400, Bruce Momjian wrote: Jim C. Nasby wrote: Something else worth doing though is to have a paragraph explaining why there's no built-in replication. I don't have time to write something right now, but I can do it later tonight if no one beats me to it. I thought that was implied in the early paragraph about why there are many solutions. I think we should explicitely spell it out, especially considering how many times people ask about it. How about... This multitude of choices is why PostgreSQL does not ship with a replication solution by default; any bundled solution would only satisfy a subset of replication needs. The problem is that we do have some solutions in our code, like doing data partitioning in the application, warm standby, or using a shared disk for failover, so how do we spell that out? I say there are multiple solutions, but I don't see how I can say that all are external and not included. None of those are replication solutions. So I would have to agree with Jim here. This isn't about what people do with their app, so that is not relevant. Warm standby is PITR which is a backup and recovery solution. It does not include a failover solution and is *not* replication. It technically does not provide an HA solution either as it will be almost always farther behind than a replication solution. Shared disk for failover could be used by anything it isn't special to a replication scenario it is standard for many HA. The section is no longer titled only replication, but is now Failover, Replication, Load Balancing, and Clustering Options, so it is more a catch-all, and hence saying nothing is included doesn't make sense. You could say no replication is included, but replication is only one part of the section, so where do you put that, and why is it worth it? -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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] New CRC algorithm: Slicing by 8
On Mon, Oct 23, 2006 at 05:23:27PM -0400, Tom Lane wrote: Mark Kirkwood [EMAIL PROTECTED] writes: Right - I think the regression is caused by libc and kernel being built with gcc 3.4.6 and the test program being built with gcc 4.1.2. Why do you think that? The performance of the CRC loop shouldn't depend at all on either libc or the kernel, because they're not invoked inside the loop. I can believe that not re-building GCC 4.1.x with the 4.1.x compiler could result in it not taking full advantage of new features and functions. Ken ---(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: [DOCS] [HACKERS] Replication documentation addition
Bruce Momjian wrote: Jim C. Nasby wrote: On Wed, Oct 25, 2006 at 08:42:07PM -0400, Bruce Momjian wrote: Jim C. Nasby wrote: Something else worth doing though is to have a paragraph explaining why there's no built-in replication. I don't have time to write something right now, but I can do it later tonight if no one beats me to it. I thought that was implied in the early paragraph about why there are many solutions. I think we should explicitely spell it out, especially considering how many times people ask about it. How about... This multitude of choices is why PostgreSQL does not ship with a replication solution by default; any bundled solution would only satisfy a subset of replication needs. The problem is that we do have some solutions in our code, like doing data partitioning in the application, warm standby, or using a shared disk for failover, so how do we spell that out? I say there are multiple solutions, but I don't see how I can say that all are external and not included. None of those are replication solutions. So I would have to agree with Jim here. This isn't about what people do with their app, so that is not relevant. Warm standby is PITR which is a backup and recovery solution. It does not include a failover solution and is *not* replication. It technically does not provide an HA solution either as it will be almost always farther behind than a replication solution. Shared disk for failover could be used by anything it isn't special to a replication scenario it is standard for many HA. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate ---(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] Compiling with GIST
I have compiled postgres 1.8.5 on Windows XP from source using MinGW, however the GIST index cannot be created since the following errors pop up:HINT: You must specify an operator class for the index or define a default operator class for the data type.ERROR: data type character varying has no default operator class for access method "gist"Do I have to create them one by one?Thanks
Re: [HACKERS] Replication documentation addition
Hi, A typo: (a write to any server has to be _propogated_) s/propogated/propagated Bruce Momjian wrote: Here is a new replication documentation section I want to add for 8.2: ftp://momjian.us/pub/postgresql/mypatches/replication Comments welcomed. -- Regards, Alexey Klyukin alexk(at)vollmond.org.ua Simferopol, Crimea, Ukraine. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Nasty btree deletion bug
Tom Lane wrote: In theory, given a slow-enough-moving VACUUM process, this could happen even without a crash. So I think that means we have to go over to the other plan of locking everything all the way up to the top of the deletion before we start doing it --- and also, we'll need crash recovery logic to complete the incomplete deletion, same as for incomplete inserts. Yech --- more code than I was hoping to have to write, and more concurrency hit too. ISTM we don't necessarily need the complete the incomplete deletion logic. Since we're holding all the pages up to the parent of the highest deleted page locked, we can atomically issue one big WAL record covering all the modifications. We can't do that with page splits, because we want to release the lock on the split pages before we move up to the parent to insert the child pointer. (whether or not it's worth it in page splits either is another issue) The concurrency hit probably isn't that bad. There shouldn't be much activity on empty pages. What does the original research paper by Lanin Shasha say about this? I don't have it at hand. I do have the Lehman Yao paper but that one doesn't discuss removal of non-leaf pages at all. OTOH we might be able to get rid of the notion of half dead, which would allow some simplifications. Yep. Thanks for taking a close look! No problem, I've been staring at the b-tree code lately anyway. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PgSQL users quota
Thanks for your response.Waiting on anyone to implement this feature ;))-Nick2006/10/23, Jonah H. Harris [EMAIL PROTECTED] :On 10/23/06, Jim C. Nasby [EMAIL PROTECTED] wrote: Since Jonah hasn't done anything with it he's presumably lost interest, so you'd need to find someone else looking for an itch to scratch. And it appears the original patch was against 7.4, so it'd probably need a decent amount of work to make it work with our current code.Well, it was more of the case of community wants vs. what I needed atthe time.I'm not quite sure if I have the patch lying around anywhere, but it was fairly trivial to implement. o Allow per-tablespace quotasYes, this is what came out of the discussion.A per-tablespace patchshould be fairly easy to implement once you get a consensus on what constitutes actual space usage... as VACUUMable space may or may notapply depending on your use-case.--Jonah H. Harris, Software Architect | phone: 732.331.1300EnterpriseDB Corporation| fax: 732.331.130133 Wood Ave S, 2nd Floor| [EMAIL PROTECTED]Iselin, New Jersey 08830| http://www.enterprisedb.com/
Re: [HACKERS] Nasty btree deletion bug
I wrote: [ looks at that for a bit... ] Yeah, you're right. Once the deletion is completed, the F lower-bound key will disappear from the grandparent, which would restore consistency --- but we could have already delivered wrong search answers, so that won't do. On further reflection, I think I understand why we've not realized the existence of this bug before: in fact, it *doesn't* lead to wrong search answers. I think the only visible consequence is exactly the failed to re-find parent key VACUUM error that Ed saw. The reason is that the key misordering in the grandparent level is nearly harmless. Using your example of - F D D ... * if we happen to come across the F key first during a binary search of the grandparent page, and we are looking for something = F, we will descend to its left, which is at worst a little bit inefficient: _bt_moveright will still ensure that we find what we seek. * if we happen to visit one of the D key(s) first, and we are looking for something D, we will descend to the right of that key. Well, that's not incorrect for the live data. In fact, the *only* key in the tree that we will fail to find this way is the F bounding key for the half-dead page itself (or one of its also-deletable parents). So that's exactly why VACUUM can fail while trying to clean up the half-dead page, and it's why we're not seeing reports of wrong query answers. So that reduces the priority of the bug quite a lot in my estimation; and makes me not want to incur a lot of additional code and locking to fix it. I'm wondering whether we can simply adopt a modified strategy for searching for a half-dead page's parent during _bt_pagedel. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Nasty btree deletion bug
Heikki Linnakangas [EMAIL PROTECTED] writes: What does the original research paper by Lanin Shasha say about this? Nothing very useful. The connection of our code to that paper is actually a bit tenuous: their approach to deletion is to make the target page's key space move left not right. That doesn't work real nicely in my opinion (for one thing, you have to replace the left sibling's high key, which is problematic for variable-size keys). regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Nasty btree deletion bug
Tom Lane wrote: On further reflection, I think I understand why we've not realized the existence of this bug before: in fact, it *doesn't* lead to wrong search answers. I think the only visible consequence is exactly the failed to re-find parent key VACUUM error that Ed saw. The reason is that the key misordering in the grandparent level is nearly harmless. Using your example of Yep. It's pretty harmless. But now that I look at the original post by Ed, I don't see how the failed to re-find parent key error could result from the issue we've been talking about. The error message is printed when _bt_getstackbuf is unable to re-find an item in the parent of a deleted page, but _bt_getstackbuf doesn't look at or compare the keys at all. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(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] Nasty btree deletion bug
Heikki Linnakangas [EMAIL PROTECTED] writes: But now that I look at the original post by Ed, I don't see how the failed to re-find parent key error could result from the issue we've been talking about. The error message is printed when _bt_getstackbuf is unable to re-find an item in the parent of a deleted page, but _bt_getstackbuf doesn't look at or compare the keys at all. Right, but _bt_getstackbuf is working from a search stack created by a standard search for the victim page's high key. If that search descended through a page to the right of the victim page's actual parent, _bt_getstackbuf isn't able to recover. 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: [DOCS] [HACKERS] Replication documentation addition
The documentation comes with the open source tarball. Yuck. I would welcome if the docs point to an unofficial wiki (maintained externally from authoritative PostgreSQL developers) or a website listing them and giving a brief of each solution. postgresql.org already does this for events (commercial training!) and news. Point to postgresql.org/download/commercial as there *already* are brief descriptions, pricing and website links. I wouldn't have looked in download for such a thing. Nor would I expect everyone with a Postgres related solution to want to post it on PosgreSql.org for download. However I agree that a simple web page listing such things is needed. It's easy to manage - way easier to manage than the development of a competent relational database engine! It's just a bunch of text, after all, and errors aren't that critical and will tend to self-correct through user attention. You list the ones that are stable in their existence (commercial or not). And how would you determine it? Years of existance? Contribution to PostgreSQL's source code? It is not easy and wouldn't be fair. There are ones that certainly will be listed, and other doubtful ones (which would perhaps complain, that's why I said 'all' - if they are not stable, either they stay out of the market or fix their problems). You have to just trust people. If it's clear that this isn't PostgreSql.org, stuff can be unstable, etc - it isn't the group's problem. No it doesn't. Because there is always the, It want's to be free! crowd. Yes, I agree there are. But also development in *that* cutting-edge is scarce. It feels that something had filled the gap if you list some commercial solution, mainly people in the trenches (DBAs). They would, obviously, firstly seek the commercial solutions as they are interested. So they click 'commercial products' in the main website. Not necessarily. Most times, I'll seek the better solution, which may or may not be commercial. Sometimes I'll avoid a commercial version because I don't like the company! ... But getting genuine donations of time - without direct $$ self-interest attached, is a whole nother kettle o fish. For example, there are a lot of students out there that are excellent and would love to have a mechanism to gain something for their resumes before entering the business world. ...There might be some residual interest at UCB, for example. Attracting this kind of support is a completely different dialogue, but on _this_ topic, surely seeking the it wants to be free! crowd can't (or shouldn't, in my view) be used as an excuse for not publishing pointers to commercial soltions that involve PostgreSql. Do it already! If people (who read the documentation) professionally work with PostgreSQL, they may already have been briefed by those commercial offerings in some way. Maybe, maybe not. The may is a wiggler; sounds like an excuse with a back door. The real answer is probably not! I'm in that world. I haven't been briefed. Ever. And I agree with your point, still. However, that would open a precedent for people to have to maintain lists of stable software in every documentation area. All that's needed is ONE list, with clear disclaimer. It'll be all text and links, and maybe the odd small .gif logo, if permitted, so it won't be a huge thing. Come on now, are there thousands of such products? Tens sounds more plausible. Regards, Richard -- Richard Troy, Chief Scientist Science Tools Corporation 510-924-1363 or 202-747-1263 [EMAIL PROTECTED], http://ScienceTools.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] plperl/plperlu interaction
Recently while doing a little research on how we could do perl module
preloading nicely, I constructed the following:
create function loadmods() returns void language plperlu as $$
use LWP::UserAgent;
$$;
select loadmods();
create function loadurl() returns text language plperl as $$
my $ua = LWP::UserAgent-new;
my $response = $ua-get('http://search.cpan.org/');
return $response-as_string;
$$;
select loadurl();
This works because plperl and plperlu share a common interpreter. I have
thought some about whether or not it is a security risk, and decided it
probably isn't, because only a superuser could construct the plperlu
function to load the external module - if an ordinary user tried it in
trusted plperl code there would be a perl error generated. It remains
true that a plperl function cannot on its own get access to an external
module, and to that extent we haven't broken the trust criteria. The
only way I know of in which we could actually prevent this effect would
be to run separate interpreters for plperl and plperlu. That wouldn't be
a great tragedy on its own, as perl interpreters aren't hugely heavy
objects, but we would probably break some legacy code, and it would take
a not insignificant coding effort. So we'd want to be very sure we
wanted to do that - personally I can live with this easily enough - the
superuser just has to be careful what they do. In cases of paranoia they
could use Symbol::delete_package() when they were done with the module,
although constantly loading and unloading a module won't perform very
nicely.
Anyway, it is probably not expected by many users that loading a module
in plperlu makes it available to plperl - I was slightly surprised
myself to see it work and I am probably more aware than most of perl and
plperl subtleties. I think therefore that at least this should be
documented.
thoughts?
cheers
andrew
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Compiling with GIST
HINT: You must specify an operator class for the index or define a default operator class for the data type. ERROR: data type character varying has no default operator class for access method gist use contrib/btree_gist module: compile it and add to your database by command 'psql DB btree_gist.sql'. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(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] plperl/plperlu interaction
Andrew Dunstan [EMAIL PROTECTED] writes: Anyway, it is probably not expected by many users that loading a module in plperlu makes it available to plperl - I was slightly surprised myself to see it work and I am probably more aware than most of perl and plperl subtleties. I think that is a bug and needs to be fixed. We have the precedent of pltcl, which uses separate interpreters for pltcl and pltclu for exactly this reason. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] GUC description cleanup
Neil Conway [EMAIL PROTECTED] writes: BTW, should pre_auth_delay be included in SHOW ALL? It's really just a debug aid, so I wouldn't complain if SHOW ALL didn't show it. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] plperl/plperlu interaction
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Anyway, it is probably not expected by many users that loading a module in plperlu makes it available to plperl - I was slightly surprised myself to see it work and I am probably more aware than most of perl and plperl subtleties. I think that is a bug and needs to be fixed. We have the precedent of pltcl, which uses separate interpreters for pltcl and pltclu for exactly this reason. Fair enough. I am not sure what our release timetable is - and presumably this should also be backpatched if we regard it as a bug. I won't be able to do much on this front for the next 2 weeks at least. 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] Replication documentation addition
On Thursday 26 October 2006 10:45, Andrew Sullivan wrote:
On Wed, Oct 25, 2006 at 05:46:33PM -0400, Bruce Momjian wrote:
Josh Berkus wrote:
So, like www.postgresql.org/docs/techdocs/replication? That would
work.
Yes.
I like that idea, but I think that the URL needs to be decided upon,
needs to be stable, and needs to be put into the docs. (I don't see
it ATM, I guess because the URL isn't chosen yet?) We get so many
questions about what replication system that I'm sure people are
looking for outlines.
A
Unfortunately the techdocs system won't support a url like the one above,
rather you'll end up with something more like the following
http://www.postgresql.org/docs/techdocs.54 which is the GUI Tools Guide
(which is linked in the FAQ fwiw). Once it is in place, it will be stable
though.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Nasty btree deletion bug
I wrote:
Right, but _bt_getstackbuf is working from a search stack created by
a standard search for the victim page's high key. If that search
descended through a page to the right of the victim page's actual
parent, _bt_getstackbuf isn't able to recover.
What I'm tempted to do, at least in the back branches, is simply adjust
_bt_pagedel to be able to recover from _bt_getstackbuf failure in this
scenario. It could use the same method that _bt_insert_parent does in
the concurrent-root-split case, ie (untested):
ItemPointerSet((stack-bts_btentry.t_tid), target, P_HIKEY);
pbuf = _bt_getstackbuf(rel, stack, BT_WRITE);
if (pbuf == InvalidBuffer)
+ {
+ /* Find the leftmost page at the next level up */
+ pbuf = _bt_get_endpoint(rel, opaque-btpo.level + 1, false);
+ stack-bts_blkno = BufferGetBlockNumber(pbuf);
+ stack-bts_offset = InvalidOffsetNumber;
+ _bt_relbuf(rel, pbuf);
+ /* and repeat search from there */
+ pbuf = _bt_getstackbuf(rel, stack, BT_WRITE);
+ if (pbuf == InvalidBuffer)
elog(ERROR, failed to re-find parent key in \%s\,
RelationGetRelationName(rel));
+ }
parent = stack-bts_blkno;
poffset = stack-bts_offset;
The question is whether we want a cleaner answer for future development,
and if so what that answer ought to look like. It seems like the
alternatives we've been discussing may not end up any simpler/shorter
than the current code, and it seems hard to justify giving up some
concurrency in the name of a simplification that doesn't simplify much.
Thoughts?
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] plperl/plperlu interaction
Andrew Dunstan wrote: Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Anyway, it is probably not expected by many users that loading a module in plperlu makes it available to plperl - I was slightly surprised myself to see it work and I am probably more aware than most of perl and plperl subtleties. I think that is a bug and needs to be fixed. We have the precedent of pltcl, which uses separate interpreters for pltcl and pltclu for exactly this reason. Fair enough. I am not sure what our release timetable is - and presumably this should also be backpatched if we regard it as a bug. I won't be able to do much on this front for the next 2 weeks at least. There is one other wrinkle, that has just come to my attention courtesy of [EMAIL PROTECTED] This is what the perlembed man page says: Now suppose we have more than one interpreter instance running at the same time. This is feasible, but only if you used the Configure option -Dusemultiplicity or the options -Dusethreads -Duseithreads when building perl. Now my local perl (FC5/ia64) has usemultiplicity defined. I am not sure how common this is. Perhaps people who use other platforms could look for these flags in the output of perl -e 'use Config qw(myconfig config_sh config_vars config_re); print config_sh();' cheers andrew ---(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] plperl/plperlu interaction
On Thu, Oct 26, 2006 at 03:15:00PM -0400, Andrew Dunstan wrote: Perhaps people who use other platforms could look for these flags in the output of perl -e 'use Config qw(myconfig config_sh config_vars config_re); print config_sh();' My Debian Sarge (i386) has: useithreads='define' usethreads='define' usemultiplicity='define' Have a nice day, -- Martijn van Oosterhout kleptog@svana.org 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] plperl/plperlu interaction
Andrew Dunstan [EMAIL PROTECTED] writes: Now suppose we have more than one interpreter instance running at the same time. This is feasible, but only if you used the Configure option -Dusemultiplicity or the options -Dusethreads -Duseithreads when building perl. Now my local perl (FC5/ia64) has usemultiplicity defined. I am not sure how common this is. Ouch. It's certainly not the default configuration :-( regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] plperl/plperlu interaction
On Oct 26, 2006, at 3:23 PM, Martijn van Oosterhout wrote: On Thu, Oct 26, 2006 at 03:15:00PM -0400, Andrew Dunstan wrote: Perhaps people who use other platforms could look for these flags in the output of perl -e 'use Config qw(myconfig config_sh config_vars config_re); print config_sh();' OSX 10.4.8: usemultiplicity='define' usethreads='define' useithreads='define' -- Jeff Trout [EMAIL PROTECTED] http://www.dellsmartexitin.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Eliminating phase 3 requirement for varlen increases via ALTER COLUMN
Jonah H. Harris [EMAIL PROTECTED] writes: The attached patch handles the simple case where a user wants to increase the user-defined storage size of a variable length object, such as VARCHAR or NUMERIC, without having to rebuild the table. This makes some really quite unacceptable assumptions about the meaning and encoding of typmod; in fact I don't believe it's even correct for NUMERIC, which uses a two-field encoding in typmod. NUMERIC(18,2) to NUMERIC(20,0) cannot be a work-free conversion. Given the plans to allow user-defined types to have their own interpretation of typmod, you can't just blithely assume you know the semantics of a typmod change. I'm also wondering what's the point of comparing attbyval etc when you've already checked it's the same type OID. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] plperl/plperlu interaction
Jeff Trout wrote: On Oct 26, 2006, at 3:23 PM, Martijn van Oosterhout wrote: On Thu, Oct 26, 2006 at 03:15:00PM -0400, Andrew Dunstan wrote: Perhaps people who use other platforms could look for these flags in the output of perl -e 'use Config qw(myconfig config_sh config_vars config_re); print config_sh();' OSX 10.4.8: usemultiplicity='define' usethreads='define' useithreads='define' Same here on Debian unstable (stock Perl packages). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] plperl/plperlu interaction
On Thu, Oct 26, 2006 at 03:35:11PM -0400, Jeff Trout wrote: On Oct 26, 2006, at 3:23 PM, Martijn van Oosterhout wrote: On Thu, Oct 26, 2006 at 03:15:00PM -0400, Andrew Dunstan wrote: Perhaps people who use other platforms could look for these flags in the output of perl -e 'use Config qw(myconfig config_sh config_vars config_re); print config_sh();' OSX 10.4.8: usemultiplicity='define' usethreads='define' useithreads='define' All 3 are undef on FreeBSD 6.1. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] plperl/plperlu interaction
On Thu, 26 Oct 2006, Alvaro Herrera wrote: Jeff Trout wrote: On Oct 26, 2006, at 3:23 PM, Martijn van Oosterhout wrote: On Thu, Oct 26, 2006 at 03:15:00PM -0400, Andrew Dunstan wrote: Perhaps people who use other platforms could look for these flags in the output of perl -e 'use Config qw(myconfig config_sh config_vars config_re); print config_sh();' OSX 10.4.8: usemultiplicity='define' usethreads='define' useithreads='define' Same here on Debian unstable (stock Perl packages). On my current Gentoo box: useithreads='undef' usemultiplicity='undef' usethreads='undef' My USE flags have ithreads disabled, since the description of the feature is Enable Perl threads, has some compatibility problems -- Whether you can hear it or not The Universe is laughing behind your back -- National Lampoon, Deteriorata ---(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] [PATCHES] Eliminating phase 3 requirement for varlen increases via ALTER COLUMN
On 10/26/06, Tom Lane [EMAIL PROTECTED] wrote: This makes some really quite unacceptable assumptions about the meaning and encoding of typmod ... True, so VARCHAR seems like the only one? That's the only one I've really encountered in the field on a fairly regular basis. I'm also wondering what's the point of comparing attbyval etc when you've already checked it's the same type OID. True. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [GENERAL] NOTICE: word is too long INSERT 0 3014
Any thoughts on the below? Joshua D. Drake wrote: Hello, I am running into this limitation ALOT with Tsearch2. What are my options to get around it. Do I have to compile PostgreSQL with a different block size? If yes, what are the downsides to doing so (outside of not being able to do straight upgrades)? Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] plperl/plperlu interaction
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Now suppose we have more than one interpreter instance running at the same time. This is feasible, but only if you used the Configure option -Dusemultiplicity or the options -Dusethreads -Duseithreads when building perl. Now my local perl (FC5/ia64) has usemultiplicity defined. I am not sure how common this is. Ouch. It's certainly not the default configuration :-( Well, so far many Linux platforms look OK, but FBSD does not. This could be ugly ;-( 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] [PATCHES] Eliminating phase 3 requirement for varlen increases via ALTER COLUMN
Jonah H. Harris [EMAIL PROTECTED] writes: On 10/26/06, Tom Lane [EMAIL PROTECTED] wrote: This makes some really quite unacceptable assumptions about the meaning and encoding of typmod ... True, so VARCHAR seems like the only one? That's the only one I've really encountered in the field on a fairly regular basis. Well, you could either hardwire some specific cases for specific types here, or think about inventing a general-purpose mechanism that would let types register a function to report whether a given typmod change requires actual work. I'm not sure it's worth the latter though. One point worth thinking about is that varchar(any) -- text could be a free coercion too, along with cases such as replacing a domain by its base type. I think we can detect this today by the expedient of noting whether the coercion ends up being just a RelabelType expression --- I'm actually a bit surprised that that knowledge doesn't seem to be in the code already. OTOH ... RelabelType means the bits are the same but it doesn't imply that the semantics of the bits are the same, eg, OID has a different sort order than int4. So ISTM that in general it'd still be necessary to recheck constraints and rebuild indexes. This might be a sufficient reason for limiting the optimization to a few known-safe cases like varchar/text, rather than trying to do it for any binary-compatible conversion. Another thought is that some cases would amount to checking constraints but not changing any bits on-disk, as in replacing a base type with a domain. Is it worth having these go through the non-rewriting code path? How would we be sure we didn't need to rebuild indexes? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] plperl/plperlu interaction
Andrew Dunstan wrote: Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Now suppose we have more than one interpreter instance running at the same time. This is feasible, but only if you used the Configure option -Dusemultiplicity or the options -Dusethreads -Duseithreads when building perl. Now my local perl (FC5/ia64) has usemultiplicity defined. I am not sure how common this is. Ouch. It's certainly not the default configuration :-( Well, so far many Linux platforms look OK, but FBSD does not. OpenBSD (which has perl in base) also has those 3 NOT defined ... This could be ugly ;-( yeah ... Stefan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Replication documentation addition
On Wed, 25 Oct 2006, Josh Berkus wrote: Bruce, It isn't designed for that. It is designed for people to understand what they want, and then they can look around for solutions. I think most agree we don't want a list of solutions in the documentation, though I have a few as examples. Do they? I've seen no discussion of the matter. I think we should have them. I completely agree; If you want to attract competent people from the business world, one thing you have to do is respect their time by helping them find information, especially about things they don't know exist. All that's needed are pointers, but the pointers need to be to solid documents/resources, not just the top of a heap - if you'll forgive the pun. Richard -- Richard Troy, Chief Scientist Science Tools Corporation 510-924-1363 or 202-747-1263 [EMAIL PROTECTED], http://ScienceTools.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [DOCS] [HACKERS] Replication documentation addition
On Thu, Oct 26, 2006 at 11:59:57AM -0400, Bruce Momjian wrote: Jim C. Nasby wrote: On Wed, Oct 25, 2006 at 08:42:07PM -0400, Bruce Momjian wrote: Jim C. Nasby wrote: Something else worth doing though is to have a paragraph explaining why there's no built-in replication. I don't have time to write something right now, but I can do it later tonight if no one beats me to it. I thought that was implied in the early paragraph about why there are many solutions. I think we should explicitely spell it out, especially considering how many times people ask about it. How about... This multitude of choices is why PostgreSQL does not ship with a replication solution by default; any bundled solution would only satisfy a subset of replication needs. The problem is that we do have some solutions in our code, like doing data partitioning in the application, warm standby, or using a shared disk for failover, so how do we spell that out? I say there are multiple solutions, but I don't see how I can say that all are external and not included. Good point... how about this? -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) Index: doc/src/sgml/failover.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/failover.sgml,v retrieving revision 1.2 diff -c -r1.2 failover.sgml *** doc/src/sgml/failover.sgml 26 Oct 2006 17:07:03 - 1.2 --- doc/src/sgml/failover.sgml 26 Oct 2006 18:26:21 - *** *** 29,35 working together. Because there is no single solution that eliminates the impact of the sync problem for all use cases, there are multiple solutions. Each solution addresses this problem in a different way, and ! minimizes its impact for a specific workload. /para para --- 29,40 working together. Because there is no single solution that eliminates the impact of the sync problem for all use cases, there are multiple solutions. Each solution addresses this problem in a different way, and ! minimizes its impact for a specific workload. A few of these solutions are ! provided with PostgreSQL itself, but it would be impractical for the core ! database to handle every scenario. That is why most solutions are implemented ! outside the database. PostgreSQL's unique extensibility is what allows this ! to happen, and 3rd-party solutions should not be thought of as ! qoutesecond-rate/ simply because they are not bundled with the database. /para para ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [DOCS] [HACKERS] Replication documentation addition
Jim C. Nasby wrote: On Thu, Oct 26, 2006 at 11:59:57AM -0400, Bruce Momjian wrote: Jim C. Nasby wrote: On Wed, Oct 25, 2006 at 08:42:07PM -0400, Bruce Momjian wrote: Jim C. Nasby wrote: Something else worth doing though is to have a paragraph explaining why there's no built-in replication. I don't have time to write something right now, but I can do it later tonight if no one beats me to it. I thought that was implied in the early paragraph about why there are many solutions. I think we should explicitely spell it out, especially considering how many times people ask about it. How about... This multitude of choices is why PostgreSQL does not ship with a replication solution by default; any bundled solution would only satisfy a subset of replication needs. The problem is that we do have some solutions in our code, like doing data partitioning in the application, warm standby, or using a shared disk for failover, so how do we spell that out? I say there are multiple solutions, but I don't see how I can say that all are external and not included. Good point... how about this? Sorry, that is too preachy, and I have the extensibility issue addressed in the commerical solutions section. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] plperl/plperlu interaction
Andrew, My Debian Sarge (i386) has: useithreads='define' usethreads='define' usemultiplicity='define' I get the same on Ubuntu and SuSE 9.3, so I think those are pervasive settings for Linux. Solaris 10update1: useithreads='undef' usethreads='undef' usemultiplicity='undef' -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(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] plperl/plperlu interaction
On 10/27/06, Jim C. Nasby [EMAIL PROTECTED] wrote: Undef in Slackware 10.2 Def in Ubuntu 6.06 Undef in Mandriva 2006 Undef in Solaris 10 06 Def in SLES 9.2 Perl 5.8 in SLES 8.1 throws a fit: Array found where operator expected at /usr/lib/perl5/5.8.0/warnings.pm line 294, at end of line (Missing operator before ?) Undefined subroutine main::config_sh called at -e line 2. Perl 5.004 in solaris 67 does't doesn't do config_re, neither does the perl 5.6 in Solaris 9 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Deadlock with pg_dump?
We're getting deadlock error messages in the production database logs
during times of inactivity, where the only other thing using the
database (we think) is the every-15-minutes pg_dump process. There
are still database connections up-and-running from unused Hibernate
Java processes, but they Shouldn't Be doing anything (and shouldn't
be holding locks, etc).
The deadlock error message looks like this:
ERROR: deadlock detected
DETAIL: Process 1120 waits for ShareLock on transaction 5847116;
blocked by process 1171.
Process 1171 waits for ExclusiveLock on tuple (6549,28) of relation
37637 of database 37574; blocked by process 1120.
Relation 37636 is the users table (schema attached).
Process 1120 was running an UPDATE query and changing a single row in
the users table. The users table does have foreign keys to 4 other
tables. Is it possible that those foreign key constraints acquire
locks in a different order than pg_dump (as it's SELECTing from the
tables), and it's hitting at *just* the right time to cause a deadlock?
I've tried to reproduce it on a test machine by running pgbench
(after adding foreign keys to the pgbench tables) and pg_dump in
tight loops in two concurrent shell scripts, but no deadlock.
Any ideas on how to track this down?
Under what conditions does a process acquire a ShareLock on another
transaction?
Thanks!
- Chris
Table public.users
Column | Type |
Modifiers
--++-
user_id | integer| not null default
nextval('users_user_id_seq'::regclass)
user_last_name | character varying(64) |
user_first_name | character varying(64) |
user_middle_name | character varying(64) |
univ_id | integer|
usrtyp_id| integer|
user_disabled| boolean| default false
customer_id | integer|
sysuser_id | integer|
user_dob | date |
Indexes:
users_pkey PRIMARY KEY, btree (user_id)
Foreign-key constraints:
fk_customer_id FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
ON UPDATE RESTRICT ON DELETE RESTRICT
fk_users_2 FOREIGN KEY (univ_id) REFERENCES universities(univ_id)
fk_users_3 FOREIGN KEY (usrtyp_id) REFERENCES user_type(usrtyp_id) ON
UPDATE RESTRICT
system_user_sysuser_id_fkey FOREIGN KEY (sysuser_id) REFERENCES
system_users(sysuser_id) ON UPDATE RESTRICT ON DELETE RESTRICT
---(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] [PATCHES] Eliminating phase 3 requirement for varlen increases via ALTER COLUMN
Jonah H. Harris [EMAIL PROTECTED] writes: On 10/26/06, Tom Lane [EMAIL PROTECTED] wrote: This makes some really quite unacceptable assumptions about the meaning and encoding of typmod ... True, so VARCHAR seems like the only one? That's the only one I've really encountered in the field on a fairly regular basis. I think what you want is to add a new method entry in pg_type to allow a type to declare a method to tell you whether a change is work-free or not. Then any type, even user-defined types, can allow some changes to be work-free and some not without exposing any implementation details outside the type. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] plperl/plperlu interaction
Andrej Ricnik-Bay wrote: On 10/27/06, Jim C. Nasby [EMAIL PROTECTED] wrote: Undef in Slackware 10.2 Def in Ubuntu 6.06 Undef in Mandriva 2006 Undef in Solaris 10 06 Def in SLES 9.2 Perl 5.8 in SLES 8.1 throws a fit: Array found where operator expected at /usr/lib/perl5/5.8.0/warnings.pm line 294, at end of line (Missing operator before ?) Undefined subroutine main::config_sh called at -e line 2. Perl 5.004 in solaris 67 does't doesn't do config_re, neither does the perl 5.6 in Solaris 9 You can also examine the output from perl -V cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] Eliminating phase 3 requirement for varlen increases via ALTER COLUMN
On 10/26/06, Gregory Stark [EMAIL PROTECTED] wrote: I think what you want is to add a new method entry in pg_type to allow a type to declare a method to tell you whether a change is work-free or not. Then any type, even user-defined types, can allow some changes to be work-free and some not without exposing any implementation details outside the type. Seems like too much work for a fairly simple use-case. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(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] Deadlock with pg_dump?
Chris Campbell [EMAIL PROTECTED] writes: ERROR: deadlock detected DETAIL: Process 1120 waits for ShareLock on transaction 5847116; blocked by process 1171. Process 1171 waits for ExclusiveLock on tuple (6549,28) of relation 37637 of database 37574; blocked by process 1120. Relation 37636 is the users table (schema attached). Process 1120 was running an UPDATE query and changing a single row in the users table. And what was 1171 doing? I really doubt that either of these could have been pg_dump. Given that you appear to be running 8.1 (tut-tut for not saying), it really shouldn't be a foreign key problem either. I'm betting these are just flat out conflicting updates of the same row(s). 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] plperl/plperlu interaction
Andrew Dunstan [EMAIL PROTECTED] writes: You can also examine the output from perl -V I think we've already established that we won't be able to ignore the case of not having support for multiple perl interpreters :-( So it seems we have these choices: 1. Do nothing (document it as a feature not a bug) 2. Support separate interpreters if possible, do nothing if not (still needs documentation) 3. Support separate interpreters if possible, refuse to run both plperl and plperlu functions in the same backend if not. Any other compromises possible? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] GUC description cleanup
Neil Conway [EMAIL PROTECTED] writes: Note that this patch breaks the translations of these strings, so I haven't applied it yet. Should I apply it now, or wait for 8.3 to branch? BTW, unless Peter says it's OK, my advice is to wait. It's already likely to be the case that translation updates are the critical path for releasing 8.2. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] plperl/plperlu interaction
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: You can also examine the output from perl -V I think we've already established that we won't be able to ignore the case of not having support for multiple perl interpreters :-( So it seems we have these choices: 1. Do nothing (document it as a feature not a bug) 2. Support separate interpreters if possible, do nothing if not (still needs documentation) 3. Support separate interpreters if possible, refuse to run both plperl and plperlu functions in the same backend if not. Any other compromises possible? How would we decide which wins in the third case? first in seems rather arbitrary. If we went that way I'd probably plump for just plperlu to be allowed. The the worst effect would be that the functions would have to be created by the superuser. It would be a great pity, of course - this threatens to do horrible things to portability ;-( I guess another possibility would be to allow 3 to be overridden by a switch to become 2. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] plperl/plperlu interaction
Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: 3. Support separate interpreters if possible, refuse to run both plperl and plperlu functions in the same backend if not. How would we decide which wins in the third case? first in seems rather arbitrary. If we went that way I'd probably plump for just plperlu to be allowed. First used in a given backend was exactly what I had in mind. Certainly it wouldn't be perfect, but your proposal seems to be disable plperl altogether if no separate-interpreter support, which seems overly harsh. Especially for someone who doesn't even want to install plperlu. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Deadlock with pg_dump?
On Oct 26, 2006, at 17:21, Tom Lane wrote: And what was 1171 doing? I really doubt that either of these could have been pg_dump. I know that process 1120 is a Java client (Hibernate) running an UPDATE query, but I have no idea what 1171 is. I doubt that 1171 was pg_dump, but when we turn off the pg_dump cron jobs (for 12-ish hours), the deadlocks go away. We usually see 5 or 6 deadlocks spread throughout the day. That's not definitive evidence, of course, but it's certainly curious. Given that you appear to be running 8.1 (tut-tut for not saying), it really shouldn't be a foreign key problem either. I'm betting these are just flat out conflicting updates of the same row(s). Yeah, 8.1.3. Sorry about the omission. Is there additional logging information I can turn on to get more details? I guess I need to see exactly what locks both processes hold, and what queries they were running when the deadlock occurred? Is that easily done, without turning on logging for *all* statements? Thanks! - Chris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Replication documentation addition
On Thu, Oct 26, 2006 at 03:06:13PM -0400, Robert Treat wrote: Unfortunately the techdocs system won't support a url like the one above, rather you'll end up with something more like the following http://www.postgresql.org/docs/techdocs.54 which is the GUI Tools Guide (which is linked in the FAQ fwiw). Once it is in place, it will be stable though. Surely this is what redirects were invented for, no? http://www.postgresql.org/replication redirects to [stable magic URL] Put the former in the docs. A -- Andrew Sullivan | [EMAIL PROTECTED] Users never remark, Wow, this software may be buggy and hard to use, but at least there is a lot of code underneath. --Damien Katz ---(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] Deadlock with pg_dump?
On Thu, Oct 26, 2006 at 06:11:59PM -0400, Chris Campbell wrote: On Oct 26, 2006, at 17:21, Tom Lane wrote: And what was 1171 doing? I really doubt that either of these could have been pg_dump. I know that process 1120 is a Java client (Hibernate) running an UPDATE query, but I have no idea what 1171 is. I doubt that 1171 was pg_dump, but when we turn off the pg_dump cron jobs (for 12-ish hours), the deadlocks go away. We usually see 5 or 6 deadlocks spread throughout the day. That's not definitive evidence, of course, but it's certainly curious. I seem to remember something funny about hibernate and locking, though I can't recall any details right now... but searching the archives might provide insight. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Deadlock with pg_dump?
Chris Campbell [EMAIL PROTECTED] writes: Is there additional logging information I can turn on to get more details? I guess I need to see exactly what locks both processes hold, and what queries they were running when the deadlock occurred? Is that easily done, without turning on logging for *all* statements? log_min_error_statement = error would at least get you the statements reporting the deadlocks, though not what they're conflicting against. Still, if you're seeing multiple occurrences per day, that would probably let you build up a good picture of all the involved operations over a couple of days. [ Memo to hackers: why is it that log_min_error_statement = error isn't the default? ] Unless your applications are issuing actual LOCK TABLE commands, it's really hard to see how pg_dump could be involved. It doesn't take anything stronger than AccessShareLock, and shouldn't be holding any tuple-level locks at all. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Deadlock with pg_dump?
Tom Lane wrote: [ Memo to hackers: why is it that log_min_error_statement = error isn't the default? ] I think it default to panic because it's the way to disable the feature, which was the easiest sell when the feature was introduced. I'm all for lowering it to error. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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] Deadlock with pg_dump?
On Oct 26, 2006, at 18:45, Tom Lane wrote: log_min_error_statement = error would at least get you the statements reporting the deadlocks, though not what they're conflicting against. Would it be possible (in 8.3, say) to log the conflicting backend's current statement (from pg_stat_activity, perhaps)? I guess the conflicting backend would currently be waiting for a lock, so its current query (before releasing the lock) is the one we want. Thanks! - Chris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] bug in on_error_rollback !?
I was thinking of recommending this to someone, but wanted to try it on my own first; good thing that I did. I think it is broken as of now.I assume that the error thrown for 'select 1', inside a transaction, with 'on_error_rollback on', is not supposed to raise it's head !!! Or am I missing something? postgres=# select version(); version PostgreSQL 8.2beta1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.2.3 (mingw special 20030504-1) (1 row)postgres=# begin; BEGINpostgres=# \set on_error_rollback on postgres=# select a; ERROR: column a does not existLINE 1: select a; ^postgres=# select 1; ERROR: current transaction is aborted, commands ignored until end of transaction blockpostgres=# end; ROLLBACKpostgres=# select a; ERROR: column a does not existLINE 1: select a; ^postgres=# select 1; ?column?-- 1(1 row) postgres=# -- gurjeet[.singh]@ EnterpriseDB.com[EMAIL PROTECTED] gmail | hotmail | yahoo }.com
