Re: [HACKERS] code cleanup for SearchSysCache
"Qingqing Zhou" <[EMAIL PROTECTED]> writes: > "Tom Lane" <[EMAIL PROTECTED]> wrote >> You'd need two essentially equivalent versions of SearchSysCache, and >> you'd lose the ability to make the error message identify what was being >> searched for, so I vote no. > Both arguments are not necessarily true. This change is quite like what we > made to hash_search(). There is only one SearchSysCache() which will take an > extra argument "isComplain" (vs. HASH_ENTER_NULL). The error message can be > easily identified from the first parameter "cacheId" -- we will add another > field in struct cachedesc which describs the cache name. I think you misunderstood my second point: you might want a custom error message for a particular usage. The bottom line though is I don't see this as a useful improvement, and given the amount of code it will break (both inside and outside our CVS), marginal niceness isn't a good enough reason to change. If we had another reason forcing a change in SearchSysCache's API, then maybe we'd do this at the same time, but I can't see doing it by itself. 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] code cleanup for SearchSysCache
"Tom Lane" <[EMAIL PROTECTED]> wrote > > You'd need two essentially equivalent versions of SearchSysCache, and > you'd lose the ability to make the error message identify what was being > searched for, so I vote no. > Both arguments are not necessarily true. This change is quite like what we made to hash_search(). There is only one SearchSysCache() which will take an extra argument "isComplain" (vs. HASH_ENTER_NULL). The error message can be easily identified from the first parameter "cacheId" -- we will add another field in struct cachedesc which describs the cache name. Regards, Qingqing ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] ADD/DROP INHERITS
I forget whether the developer managed to get it working without doing any table rewriting. In theory the table just needs to know that records that are "missing" that column in the null bitmap should behave as if they have the default value. But I seem to recall some headaches with that approach. The problem is if you then change the default. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] TODO: Rename some /contrib modules from pg* to pg_*
Tom Lane wrote: > "Andrew Dunstan" <[EMAIL PROTECTED]> writes: > > Renaming directories is nasty in CVS no matter which way you look at it - it > > is one of the known bad limitations. You might preserve the history but you > > could also break every existing repo copy. See > > http://ximbiot.com/cvs/manual/cvs-1.11.15/cvs_7.html#SEC74 > > > Unless there is a *very* good reason this should be avoided. > > I think the we-shouldn't-rename-the-shlibs argument is sufficient to > kill the idea, quite aside from CVS limitations. > TODO removed. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] That EXPLAIN ANALYZE patch still needs work
It's worth noting that on Darwin (on Apple hardware) gettimeofday is never a syscall whereas on Linux (AFAIK), it always is. On Jun 8, 2006, at 7:58 PM, Mark Kirkwood wrote: Tom Lane wrote: Alvaro Herrera <[EMAIL PROTECTED]> writes: Wow, that is slow. Maybe a problem in the kernel? Perhaps something similar to this: http://www.ussg.iu.edu/hypermail/linux/kernel/0603.2/index.html#1282 Yeah, that's a pretty interesting thread. I came across something similar on a Red Hat internal list. It seems there are three or four different popular standards for clock hardware in the Intel world, and some good implementations and some pretty bad implementations of each. So the answer may well boil down to "if you're using cheap junk PC hardware then gettimeofday will be slow". OS seems to matter as well - I've got two identical Supermicro P3TDER dual intel boxes. 1 running FreeBSD 6.1, one running Gentoo Linux 2.6.16. Doing the 'select count(*) vs explain analyze select count(*) on 10 row table gives: Freebsd : select 108 ms explain analyze 688 ms Linux : select 100 ms explain analyze 196 ms Both systems have ACPI enabled in BIOS (which means there is a better timecounter than 'i8254' available (FreeBSD says its using 'ACPI-safe' - not sure how to check on Linux). ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM [EMAIL PROTECTED] ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] TODO: Determine optimal fdatasync/fsync, O_SYNC/O_DSYNC options
Josh Berkus writes: > "Performs best" is pretty elusive. On Solaris 10, for example, fdatasync > does best on pgbench on x86, but opendatasync does best on DBT2 on Sparc. > I'm not certain we can really determine this with any accuracy. Yeah, if it were easy we've have done it already :-(. One thing that would be useful though is to try to gather datapoints like the above for a range of different operating systems. Even if we could tell people "here are the two or three settings worth trying for your OS", we'd be ahead of the game. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] That EXPLAIN ANALYZE patch still needs work
Tom Lane wrote: Alvaro Herrera <[EMAIL PROTECTED]> writes: Wow, that is slow. Maybe a problem in the kernel? Perhaps something similar to this: http://www.ussg.iu.edu/hypermail/linux/kernel/0603.2/index.html#1282 Yeah, that's a pretty interesting thread. I came across something similar on a Red Hat internal list. It seems there are three or four different popular standards for clock hardware in the Intel world, and some good implementations and some pretty bad implementations of each. So the answer may well boil down to "if you're using cheap junk PC hardware then gettimeofday will be slow". OS seems to matter as well - I've got two identical Supermicro P3TDER dual intel boxes. 1 running FreeBSD 6.1, one running Gentoo Linux 2.6.16. Doing the 'select count(*) vs explain analyze select count(*) on 10 row table gives: Freebsd : select 108 ms explain analyze 688 ms Linux : select 100 ms explain analyze 196 ms Both systems have ACPI enabled in BIOS (which means there is a better timecounter than 'i8254' available (FreeBSD says its using 'ACPI-safe' - not sure how to check on Linux). Cheers Mark ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] self-deadlock at FATAL exit of boostrap process on read error
"Qingqing Zhou" <[EMAIL PROTECTED]> writes: > "Tom Lane" <[EMAIL PROTECTED]> wrote >> Do you have a test case to reproduce this problem? > According to the error message, the problem happens during reading > pg_database. I just tried to plug in this line in mdread(): OK, patch applied for this. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] TODO: Determine optimal fdatasync/fsync, O_SYNC/O_DSYNC options
Tom, JD, > No, that only tells you if the various alternatives *work*. Not which > one performs best. "Performs best" is pretty elusive. On Solaris 10, for example, fdatasync does best on pgbench on x86, but opendatasync does best on DBT2 on Sparc. I'm not certain we can really determine this with any accuracy. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] TODO: Rename some /contrib modules from pg* to pg_*
"Andrew Dunstan" <[EMAIL PROTECTED]> writes: > Renaming directories is nasty in CVS no matter which way you look at it - it > is one of the known bad limitations. You might preserve the history but you > could also break every existing repo copy. See > http://ximbiot.com/cvs/manual/cvs-1.11.15/cvs_7.html#SEC74 > Unless there is a *very* good reason this should be avoided. I think the we-shouldn't-rename-the-shlibs argument is sufficient to kill the idea, quite aside from CVS limitations. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] TODO: Rename some /contrib modules from pg* to pg_*
Marc G. Fournier said: > On Thu, 8 Jun 2006, Tom Lane wrote: > >> "Joshua D. Drake" <[EMAIL PROTECTED]> writes: >>> 1. Fix makefiles so that contrib modules such as pgcrypto are not >>> pg_crypto 2. Move directories to reflect above >>> 3. Fix source and makefiles within sub project directories to create >>> binaries and libs with correct output.. thus libpgcrypto.so.0.0 would >>> become libpg_crypto.so.0.0 >> >> That will lose the CVS history of the modules > > Umm, if co-ordinated with me, I can make the name changes at the file > system level, so that history isn't lost .. > Renaming directories is nasty in CVS no matter which way you look at it - it is one of the known bad limitations. You might preserve the history but you could also break every existing repo copy. See http://ximbiot.com/cvs/manual/cvs-1.11.15/cvs_7.html#SEC74 Unless there is a *very* good reason this should be avoided. cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] TODO: Rename some /contrib modules from pg* to pg_*
On Thu, 8 Jun 2006, Tom Lane wrote: "Joshua D. Drake" <[EMAIL PROTECTED]> writes: 1. Fix makefiles so that contrib modules such as pgcrypto are not pg_crypto 2. Move directories to reflect above 3. Fix source and makefiles within sub project directories to create binaries and libs with correct output.. thus libpgcrypto.so.0.0 would become libpg_crypto.so.0.0 That will lose the CVS history of the modules Umm, if co-ordinated with me, I can make the name changes at the file system level, so that history isn't lost .. Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED] Yahoo . yscrappy Skype: hub.orgICQ . 7615664 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] TODO: Determine optimal fdatasync/fsync, O_SYNC/O_DSYNC options
"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > Doesn't this exist in: > src/tools/fsync? No, that only tells you if the various alternatives *work*. Not which one performs best. 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
[HACKERS] TODO: Determine optimal fdatasync/fsync, O_SYNC/O_DSYNC options
Doesn't this exist in: src/tools/fsync? Do we just need to make it more user friendly? 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/ ---(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] TODO: Rename some /contrib modules from pg* to pg_*
Tom Lane wrote: "Joshua D. Drake" <[EMAIL PROTECTED]> writes: 1. Fix makefiles so that contrib modules such as pgcrypto are not pg_crypto 2. Move directories to reflect above 3. Fix source and makefiles within sub project directories to create binaries and libs with correct output.. thus libpgcrypto.so.0.0 would become libpg_crypto.so.0.0 That will lose the CVS history of the modules, which is not worth the small benefit gained from more consistent-looking names. Renaming existing shared libraries is also a very bad idea, because it will break existing dump scripts. I don't know when that TODO item got put in, but it's a stupid idea. O.k. just trying to help :) Joshua D. Drake regards, tom lane -- === 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/ ---(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] TODO: Rename some /contrib modules from pg* to pg_*
"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > 1. Fix makefiles so that contrib modules such as pgcrypto are not pg_crypto > 2. Move directories to reflect above > 3. Fix source and makefiles within sub project directories to create > binaries and libs with correct output.. thus libpgcrypto.so.0.0 would > become libpg_crypto.so.0.0 That will lose the CVS history of the modules, which is not worth the small benefit gained from more consistent-looking names. Renaming existing shared libraries is also a very bad idea, because it will break existing dump scripts. I don't know when that TODO item got put in, but it's a stupid idea. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] TODO: Rename some /contrib modules from pg* to pg_*
Joshua D. Drake wrote: Hello, I read this as: 1. Fix makefiles so that contrib modules such as pgcrypto are not pg_crypto err "are now" 2. Move directories to reflect above 3. Fix source and makefiles within sub project directories to create binaries and libs with correct output.. thus libpgcrypto.so.0.0 would become libpg_crypto.so.0.0 Is this correct? If so I personally would like to claim this TODO. 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/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] That EXPLAIN ANALYZE patch still needs work
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Wow, that is slow. Maybe a problem in the kernel? Perhaps something > similar to this: > http://www.ussg.iu.edu/hypermail/linux/kernel/0603.2/index.html#1282 Yeah, that's a pretty interesting thread. I came across something similar on a Red Hat internal list. It seems there are three or four different popular standards for clock hardware in the Intel world, and some good implementations and some pretty bad implementations of each. So the answer may well boil down to "if you're using cheap junk PC hardware then gettimeofday will be slow". In fact, I just got around to trying this on my old x86 PC, and behold: regression=# select count(*) from tenk1; count --- 1 (1 row) Time: 9.670 ms regression=# explain analyze select count(*) from tenk1; QUERY PLAN Aggregate (cost=470.00..470.01 rows=1 width=0) (actual time=93.549..93.553 rows=1 loops=1) -> Seq Scan on tenk1 (cost=0.00..445.00 rows=1 width=0) (actual time=0.014..49.261 rows=1 loops=1) Total runtime: 93.663 ms (3 rows) Time: 94.431 ms regression=# So that says that this machine takes about 4 usec to do gettimeofday(), as compared to 3 usec on my nominally 4x slower HPPA machine. The new dual Xeon sitting next to it probably has a much less junky motherboard, with a better clock ... and I find it unsurprising that the HP and Apple machines I was trying aren't subject to such problems. I didn't get the impression from that linux-kernel thread that the proposed patch had actually gone in yet anyplace; anyone know how to track that? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] That EXPLAIN ANALYZE patch still needs work
On Thu, Jun 08, 2006 at 05:32:36PM -0400, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > The powerbook tests were not very repeatable at 100,000 rows, so I > > bumped up to 1M. The results still aren't very repeatable... > > Hmm, I suspect you pushed it into the realm where it's doing actual > I/O ... which we don't want for this particular test. (Although it's > worth noting that if your query *is* doing actual I/O, the EXPLAIN > overhead certainly ought to be down in the noise.) Hmm... I guess it depends on how smart the OS cache is; the table is 36MB; 4406 pages. But shared buffers is 3000... Bumping shared_buffers up to 5k, there is a bigger difference, but these numbers are still all over the place, so I don't know that they're very trust-worthy. decibel=# explain analyze select count(*) from i; QUERY PLAN --- Aggregate (cost=16905.05..16905.06 rows=1 width=0) (actual time=5398.625..5398.626 rows=1 loops=1) -> Seq Scan on i (cost=0.00..14405.24 rows=24 width=0) (actual time=0.034..3967.927 rows=100 loops=1) Total runtime: 5398.871 ms (3 rows) Time: 5400.900 ms decibel=# explain analyze select count(*) from i; QUERY PLAN --- Aggregate (cost=16905.05..16905.06 rows=1 width=0) (actual time=5626.671..5626.671 rows=1 loops=1) -> Seq Scan on i (cost=0.00..14405.24 rows=24 width=0) (actual time=0.035..3875.641 rows=100 loops=1) Total runtime: 5626.904 ms (3 rows) Time: 5628.896 ms decibel=# explain analyze select count(*) from i; QUERY PLAN --- Aggregate (cost=16905.05..16905.06 rows=1 width=0) (actual time=5137.549..5137.549 rows=1 loops=1) -> Seq Scan on i (cost=0.00..14405.24 rows=24 width=0) (actual time=0.020..3440.034 rows=100 loops=1) Total runtime: 5137.707 ms (3 rows) Time: 5139.178 ms decibel=# select count(*) from i; count - 100 (1 row) Time: 4357.443 ms decibel=# select count(*) from i; count - 100 (1 row) Time: 4251.208 ms decibel=# select count(*) from i; count - 100 (1 row) Time: 4712.912 ms decibel=# select count(*) from i; count - 100 (1 row) Time: 4479.278 ms decibel=# -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ADD/DROP constraints
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > That could also break some partitioning schemes; I don't think it's a > given that parents and children have matching constraints, and afaik a > parent can have constraints that a child doesn't. Not unless you drop the inherited constraint; the fact that you can is a bug we are going to fix someday soon. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] ADD/DROP constraints
Greg Stark <[EMAIL PROTECTED]> writes: > Or maybe I should insist that a matching constraint name be present *and* that > the source text match? That's more of a pain to code though. Yeah, that's what I'd go with. I believe that there are bits of the system (probably in pg_dump) that look *only* at the constraint name when deciding what's inherited. (This is of course bogus, but until someone does something about coninhcount it's going to be hard to have a non-bogus solution.) Allowing a name mismatch would be bad. One other point is that you should NOT rely on consrc. See the note at the bottom of http://developer.postgresql.org/docs/postgres/catalog-pg-constraint.html (Someday we should get rid of consrc altogether.) Unfortunately it won't do to compare conbin either, because that will contain column numbers that won't necessarily match. I fear you'll have to actually reverse-compile the conbin strings and see if you get a match. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] That EXPLAIN ANALYZE patch still needs work
Just tested 2 windows boxes, both running 8.1.3 and XP SP2. P4 2.8G desktop 47ms297ms Pentium M 2G laptop 40ms240ms -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] TODO: Rename some /contrib modules from pg* to pg_*
Hello, I read this as: 1. Fix makefiles so that contrib modules such as pgcrypto are not pg_crypto 2. Move directories to reflect above 3. Fix source and makefiles within sub project directories to create binaries and libs with correct output.. thus libpgcrypto.so.0.0 would become libpg_crypto.so.0.0 Is this correct? If so I personally would like to claim this TODO. 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/ ---(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] That EXPLAIN ANALYZE patch still needs work
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > The powerbook tests were not very repeatable at 100,000 rows, so I > bumped up to 1M. The results still aren't very repeatable... Hmm, I suspect you pushed it into the realm where it's doing actual I/O ... which we don't want for this particular test. (Although it's worth noting that if your query *is* doing actual I/O, the EXPLAIN overhead certainly ought to be down in the noise.) regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] That EXPLAIN ANALYZE patch still needs work
Tom Lane wrote: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > Tried on two machines. The first (Turion64 laptop) gives 44-45 ms for > > the SELECT, and 50-51 ms for the EXPLAIN ANALYZE. > > > The second machine, desktop Celeron 533, gives 197-200 ms for the SELECT > > and 788-790 for the EXPLAIN ANALYZE. I guess this is the reproduction > > you were looking for. > > Do you have oprofile installed on these? Comparing oprofile results > might give some more insight where the time is going. No, I don't. I'll have a look. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] That EXPLAIN ANALYZE patch still needs work
On Thu, Jun 08, 2006 at 04:58:07PM -0400, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > So the timing is clearly responsible for the additional time I'm > > personally experiencing and very likely to be that for others also. > > Well, that's certainly unsurprising, but the question is why it's such > a large overhead for you when it's not on other apparently-similar kit. > > If anyone else is still following this discussion, could you try the > test case Simon gave a bit earlier (select count(*) from 10-row > table, check \timing with and without explain analyze)? If we could get > a clearer picture of which platforms show the problem and which don't, > maybe we could make some progress. > > (BTW, I just tried it on my Apple Powerbook G4, and got results in line > with what I get elsewhere. So I've now tried it on three very different > CPU types and OSes, and not duplicated Simon's problem on any of them.) CPU: AMD Athlon(tm) 64 Processor 3500+ (2210.20-MHz K8-class CPU) 8.1.3 on amd64-portbld-freebsd6.0 25ms353ms CPU: AMD Opteron(tm) Processor 244 (1792.50-MHz K8-class CPU) (dual CPU) 8.1.4 on amd64-portbld-freebsd6.0 31ms295ms Powerbook G4 1.33GHz 8.1.4 on powerpc-apple-darwin8.6.0 5.1s5.8s* The powerbook tests were not very repeatable at 100,000 rows, so I bumped up to 1M. The results still aren't very repeatable... decibel=# select count(*) from i; count - 100 (1 row) Time: 4914.604 ms decibel=# select count(*) from i; count - 100 (1 row) Time: 5186.516 ms decibel=# select count(*) from i; count - 100 (1 row) Time: 5174.418 ms decibel=# explain analyze select count(*) from i; QUERY PLAN --- Aggregate (cost=16905.05..16905.06 rows=1 width=0) (actual time=5729.623..5729.624 rows=1 loops=1) -> Seq Scan on i (cost=0.00..14405.24 rows=24 width=0) (actual time=0.155..4039.317 rows=100 loops=1) Total runtime: 5729.907 ms (3 rows) Time: 5732.076 ms decibel=# explain analyze select count(*) from i; QUERY PLAN --- Aggregate (cost=16905.05..16905.06 rows=1 width=0) (actual time=5916.025..5916.026 rows=1 loops=1) -> Seq Scan on i (cost=0.00..14405.24 rows=24 width=0) (actual time=0.157..4246.123 rows=100 loops=1) Total runtime: 5916.261 ms (3 rows) Time: 5922.542 ms decibel=# explain analyze select count(*) from i; QUERY PLAN --- Aggregate (cost=16905.05..16905.06 rows=1 width=0) (actual time=5800.788..5800.789 rows=1 loops=1) -> Seq Scan on i (cost=0.00..14405.24 rows=24 width=0) (actual time=0.152..4126.781 rows=100 loops=1) Total runtime: 5801.026 ms (3 rows) Time: 5803.070 ms decibel=# explain analyze select count(*) from i; QUERY PLAN --- Aggregate (cost=16905.05..16905.06 rows=1 width=0) (actual time=5994.285..5994.286 rows=1 loops=1) -> Seq Scan on i (cost=0.00..14405.24 rows=24 width=0) (actual time=0.156..4341.463 rows=100 loops=1) Total runtime: 5994.520 ms (3 rows) Time: 5996.577 ms -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] ADD/DROP INHERITS
Simon Riggs <[EMAIL PROTECTED]> writes: > On Thu, 2006-06-08 at 16:47 -0400, Tom Lane wrote: > > Greg Stark <[EMAIL PROTECTED]> writes: > > > Tom Lane <[EMAIL PROTECTED]> writes: > > >> So? They'll get re-merged with the parent column during CREATE TABLE > > >> anyway. > > > > > But merged columns that are defined locally still appear in the position > > > they > > > were defined locally. Not with the other inherited columns. > > Based on the test case Tom shows, I think we need to enforce that ADD > INHERITS will barf if the columns are not in exactly the order they > would have been in if we add done a CREATE ... INHERITS followed by a > DROP INHERITS. Well firstly I think that rule is much too hard to explain to users. You would have to simplify it into something that makes more sense from a user's point of view. But there's a bigger problem, it won't actually help. To maintain that invariant you would never be allowed to DROP a parent unless you had no locally defined columns at all. And if you had multiple parents you would have further restrictions no multiply defined columns and you can only drop parents in the reverse order they were listed on the inherits line. So basically that rule translates into "you can only add a parent with precisely the same definition as your child table and you can only drop a parent if it's the last parent in the list and none of the columns are shared with other parents". Is that what you want? -- greg ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] ADD/DROP INHERITS
Simon Riggs wrote: Based on the test case Tom shows, I think we need to enforce that ADD INHERITS will barf if the columns are not in exactly the order they would have been in if we add done a CREATE ... INHERITS followed by a DROP INHERITS. That wouldn't be a problem if we just say to people, if you want to create a new partition do: CREATE TABLE new_child ... LIKE child; then later ALTER TABLE new_partition ADD INHERITS parent; This seems like a very reasonable restriction. I imagine in the most common case at least they will be exact clones. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] That EXPLAIN ANALYZE patch still needs work
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Tried on two machines. The first (Turion64 laptop) gives 44-45 ms for > the SELECT, and 50-51 ms for the EXPLAIN ANALYZE. > The second machine, desktop Celeron 533, gives 197-200 ms for the SELECT > and 788-790 for the EXPLAIN ANALYZE. I guess this is the reproduction > you were looking for. Do you have oprofile installed on these? Comparing oprofile results might give some more insight where the time is going. 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] That EXPLAIN ANALYZE patch still needs work
Tom Lane wrote: > If anyone else is still following this discussion, could you try the > test case Simon gave a bit earlier (select count(*) from 10-row > table, check \timing with and without explain analyze)? If we could get > a clearer picture of which platforms show the problem and which don't, > maybe we could make some progress. Tried on two machines. The first (Turion64 laptop) gives 44-45 ms for the SELECT, and 50-51 ms for the EXPLAIN ANALYZE. The second machine, desktop Celeron 533, gives 197-200 ms for the SELECT and 788-790 for the EXPLAIN ANALYZE. I guess this is the reproduction you were looking for. All results are repeated multiple times, I'm reporting times other than the first query (which is slower). More specs on the Celeron machine follow: $ cat /proc/cpuinfo processor : 0 vendor_id : GenuineIntel cpu family : 6 model : 6 model name : Celeron (Mendocino) stepping: 5 cpu MHz : 534.616 cache size : 128 KB fdiv_bug: no hlt_bug : no f00f_bug: no coma_bug: no fpu : yes fpu_exception : yes cpuid level : 2 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 sep mtrr pge mca cmov pat pse36 mmx fxsr bogomips: 1070.25 $ uname -a Linux drizzt 2.6.16-1-686 #2 Sat Apr 15 20:45:20 UTC 2006 i686 GNU/Linux -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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] ADD/DROP INHERITS
Simon Riggs <[EMAIL PROTECTED]> writes: > Based on the test case Tom shows, I think we need to enforce that ADD > INHERITS will barf if the columns are not in exactly the order they > would have been in if we add done a CREATE ... INHERITS followed by a > DROP INHERITS. This seems overly strong; if we enforced that policy consistently, then it would for example be illegal to ADD COLUMN to a parent. Consider create table p(f1 int); create table c(f2 int) inherits (p); alter table p add column f3 int; The column order in c will now be f1,f2,f3. However, after a dump and reload it'll be f1,f3,f2, because f3 will already be an inherited column when c is created. This is pretty much unavoidable and we've taken care of the various loose ends needed to make it work safely. What I'm saying is just that we should avoid *unnecessary* changes of column order, and in particular that means taking at least a little care to try to select a reasonable inhseqno during ADD INHERITS. If you think the "first unused" policy wouldn't take care of enough cases, one idea is to try to look at the columns that will be inherited from the new parent, and to see if we can deduce a suitable inhseqno based on those columns' positions. I suspect this will be a pretty ugly heuristic though ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] That EXPLAIN ANALYZE patch still needs work
Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: >> So the timing is clearly responsible for the additional time I'm >> personally experiencing and very likely to be that for others also. > > Well, that's certainly unsurprising, but the question is why it's such > a large overhead for you when it's not on other apparently-similar > kit. > > If anyone else is still following this discussion, could you try the > test case Simon gave a bit earlier (select count(*) from 10-row > table, check \timing with and without explain analyze)? If we could > get a clearer picture of which platforms show the problem and which > don't, maybe we could make some progress. > > (BTW, I just tried it on my Apple Powerbook G4, and got results in > line with what I get elsewhere. So I've now tried it on three very > different CPU types and OSes, and not duplicated Simon's problem on > any of them.) > $ psql Welcome to psql 8.1.4, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit ler=# create table test1(id int); CREATE TABLE ler=# insert into test1 select * from generate_series(1,10); INSERT 0 10 ler=# \timing Timing is on. ler=# select count(*) from test1; count 10 (1 row) Time: 39.191 ms ler=# explain analyze select count(*) from test1; QUERY PLAN Aggregate (cost=1988.18..1988.19 rows=1 width=0) (actual time=282.899..282.901 rows=1 loops=1) -> Seq Scan on test1 (cost=0.00..1698.74 rows=115774 width=0) (actual time=0.007..147.845 rows=10 loops=1) Total runtime: 282.987 ms (3 rows) Time: 283.764 ms ler=# \q $ uname -a FreeBSD thebighonker.lerctr.org 6.1-STABLE FreeBSD 6.1-STABLE #59: Thu Jun 1 09:40:47 CDT 2006 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/THEBIGHONKER amd64 $ sysctl hw hw.machine: amd64 hw.model: Intel(R) Xeon(TM) CPU 3.00GHz hw.ncpu: 4 hw.byteorder: 1234 hw.physmem: 4286132224 hw.usermem: 4003151872 hw.pagesize: 4096 hw.floatingpoint: 1 hw.machine_arch: amd64 hw.realmem: 5368709120 [snip] the database is 8.1.4 -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 512-248-2683 E-Mail: ler@lerctr.org US Mail: 430 Valona Loop, Round Rock, TX 78681-3893 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] That EXPLAIN ANALYZE patch still needs work
Simon Riggs wrote: > postgres=# explain analyze select count(*) from accounts; >QUERY PLAN > > Aggregate (cost=2890.00..2890.01 rows=1 width=0) (actual > time=0.000..690.780 rows=1 loops=1) >-> Seq Scan on accounts (cost=0.00..2640.00 rows=10 width=0) > (actual time=0.000..2672.562 rows=10 loops=1) > Total runtime: 734.474 ms > (3 rows) > > Time: 891.822 ms Wow, that is slow. Maybe a problem in the kernel? Perhaps something similar to this: http://www.ussg.iu.edu/hypermail/linux/kernel/0603.2/index.html#1282 -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] ADD/DROP constraints
On Thu, Jun 08, 2006 at 04:30:22PM -0400, Greg Stark wrote: > Or maybe I should insist that a matching constraint name be present *and* that > the source text match? That's more of a pain to code though. That could also break some partitioning schemes; I don't think it's a given that parents and children have matching constraints, and afaik a parent can have constraints that a child doesn't. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] ADD/DROP INHERITS
On Thu, 2006-06-08 at 16:47 -0400, Tom Lane wrote: > Greg Stark <[EMAIL PROTECTED]> writes: > > Tom Lane <[EMAIL PROTECTED]> writes: > >> So? They'll get re-merged with the parent column during CREATE TABLE > >> anyway. > > > But merged columns that are defined locally still appear in the position > > they > > were defined locally. Not with the other inherited columns. Based on the test case Tom shows, I think we need to enforce that ADD INHERITS will barf if the columns are not in exactly the order they would have been in if we add done a CREATE ... INHERITS followed by a DROP INHERITS. That wouldn't be a problem if we just say to people, if you want to create a new partition do: CREATE TABLE new_child ... LIKE child; then later ALTER TABLE new_partition ADD INHERITS parent; > > Basically I think if you're doing multiple inheritance and start using > > add/drop inherits your column order is going to turn into chop suey quickly. The column ordering is too important for other purposes. Things like COPY, INSERT etc all depend upon specific column orderings. If ADD INHERITS lets a wierd ordering go past that cannot ever be re-created then everything will start to break. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] ADD/DROP INHERITS
On Thu, Jun 08, 2006 at 04:44:10PM -0400, Greg Stark wrote: > Hannu Krosing <[EMAIL PROTECTED]> writes: > > > for example - to be consistent, one should also make "ALTER TABLE ALTER > > COLUMN col SET DEFAULT x" change each "default" value, no ? > > er, I think that is in fact a no. Yeah... once a default value is stored, there's no way to tell it was stored because of the default clause; nor should there be. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] That EXPLAIN ANALYZE patch still needs work
Simon Riggs <[EMAIL PROTECTED]> writes: > So the timing is clearly responsible for the additional time I'm > personally experiencing and very likely to be that for others also. Well, that's certainly unsurprising, but the question is why it's such a large overhead for you when it's not on other apparently-similar kit. If anyone else is still following this discussion, could you try the test case Simon gave a bit earlier (select count(*) from 10-row table, check \timing with and without explain analyze)? If we could get a clearer picture of which platforms show the problem and which don't, maybe we could make some progress. (BTW, I just tried it on my Apple Powerbook G4, and got results in line with what I get elsewhere. So I've now tried it on three very different CPU types and OSes, and not duplicated Simon's problem on any of them.) regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] That EXPLAIN ANALYZE patch still needs work
On Thu, 2006-06-08 at 12:56 -0400, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > So far we have myself, Kevin, Martijn and Luke all saying there is a
> > distortion or a massive overhead caused by EXPLAIN ANALYZE.
> > http://archives.postgresql.org/pgsql-hackers/2006-03/msg00954.php
> > http://archives.postgresql.org/pgsql-patches/2006-05/msg00168.php
>
> Given that we're seeing diametrically opposed results on the same OS
> (FC5) and similar (at least all Intel) hardware, I think the prudent
> thing is to find out what's really going on before leaping in with
> proposed solutions. As the person who's *not* seeing the problem,
> I'm not in a position to do that investigation...
That seems reasonable.
I've cut a patch to remove timing from the EA results.
Output shown here:
postgres=# set explain_analyze_timing = on;
SET
Time: 0.673 ms
postgres=# explain analyze select count(*) from accounts;
QUERY PLAN
Aggregate (cost=2890.00..2890.01 rows=1 width=0) (actual
time=0.000..690.780 rows=1 loops=1)
-> Seq Scan on accounts (cost=0.00..2640.00 rows=10 width=0)
(actual time=0.000..2672.562 rows=10 loops=1)
Total runtime: 734.474 ms
(3 rows)
Time: 891.822 ms
postgres=# set explain_analyze_timing = off;
SET
Time: 0.480 ms
postgres=# explain analyze select count(*) from accounts;
QUERY PLAN
Aggregate (cost=2890.00..2890.01 rows=1 width=0) (rows=1 loops=1)
-> Seq Scan on accounts (cost=0.00..2640.00 rows=10 width=0)
(rows=10 loops=1)
Total runtime: 133.674 ms
(3 rows)
Time: 134.565 ms
postgres=# select count(*) from accounts;
count
10
(1 row)
Time: 130.528 ms
So the timing is clearly responsible for the additional time I'm
personally experiencing and very likely to be that for others also.
As to why that should be the case, I'm not sure. The timing overhead
seems fairly constant on particular hardware/OS, just different for
each.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
Index: src/backend/commands/explain.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/explain.c,v
retrieving revision 1.147
diff -c -r1.147 explain.c
*** src/backend/commands/explain.c 8 Apr 2006 18:49:52 - 1.147
--- src/backend/commands/explain.c 8 Jun 2006 20:27:13 -
***
*** 689,699
{
double nloops = planstate->instrument->nloops;
! appendStringInfo(str, " (actual time=%.3f..%.3f rows=%.0f loops=%.0f)",
1000.0 * planstate->instrument->startup / nloops,
1000.0 * planstate->instrument->total / nloops,
planstate->instrument->ntuples / nloops,
planstate->instrument->nloops);
}
else if (es->printAnalyze)
appendStringInfo(str, " (never executed)");
--- 689,704
{
double nloops = planstate->instrument->nloops;
! if (planstate->instrument->actualtime)
! appendStringInfo(str, " (actual time=%.3f..%.3f rows=%.0f loops=%.0f)",
1000.0 * planstate->instrument->startup / nloops,
1000.0 * planstate->instrument->total / nloops,
planstate->instrument->ntuples / nloops,
planstate->instrument->nloops);
+ else
+ appendStringInfo(str, " (rows=%.0f loops=%.0f)",
+ planstate->instrument->ntuples / nloops,
+ planstate->instrument->nloops);
}
else if (es->printAnalyze)
appendStringInfo(str, " (never executed)");
Index: src/backend/executor/instrument.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/executor/instrument.c,v
retrieving revision 1.17
diff -c -r1.17 instrument.c
*** src/backend/executor/instrument.c 7 Jun 2006 18:49:03 - 1.17
--- src/backend/executor/instrument.c 8 Jun 2006 20:27:13 -
***
*** 17,23
#include
#include "executor/instrument.h"
!
/*
* As of PostgreSQL 8.2, we try to reduce the overhead of EXPLAIN ANALYZE
--- 17,23
#include
#include "executor/instrument.h"
! #include "utils/guc.h"
/*
* As of PostgreSQL 8.2, we try to reduce the overhead of EXPLAIN ANALYZE
***
*** 115,120
--- 115,121
instr = palloc0(n * sizeof(Instrumentation));
/* we don't need to do any initialization except zero 'em */
+ instr->actualtime = explain_analyze_timing;
return instr;
}
***
*** 123,128
--- 124,132
void
InstrStartNode(Instrumentation *instr)
{
+ if (!instr->actualtime)
+ return;
+
if (INSTR_TIME_IS_ZERO(instr->starttime))
{
/*
***
*** 150,155
--- 154,166
{
/* coun
Re: [HACKERS] ADD/DROP constraints
Ühel kenal päeval, N, 2006-06-08 kell 16:30, kirjutas Greg Stark: > On a separate note. The one major remaining piece here is in constraints. I'm > thinking what I have to check is that every constraint present on the parent > table is present on the child tables. And I'm thinking I should do that by > looking at the constraint's textual definition (consrc). > > This doesn't allow you to get by with a single stronger constraint -- you > would still need the redundant looser constraint to satisfy the inheritance. You could find some infrastructure for "stronger constraint" recognition in constraint exclusion code, if you want to go that way. > But it does let you get by with constraint names that don't match the > parent's. > > I'm not sure that's such a good thing, since pg_dump would then generate a > redundant constraint when it generates the table. Maybe that would go if > constraints got conislocal and coninh. Currently pg_dump generates all constraints with ONLY clause anyway. But I agree that we should get rid of ONLY for ADD CONSTRAINT once we disallow dropping inherited constraints. -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.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] ADD/DROP INHERITS
Greg Stark <[EMAIL PROTECTED]> writes: > Tom Lane <[EMAIL PROTECTED]> writes: >> So? They'll get re-merged with the parent column during CREATE TABLE >> anyway. > But merged columns that are defined locally still appear in the position they > were defined locally. Not with the other inherited columns. Really? regression=# create table p (p1 int, p2 int, p3 int); CREATE TABLE regression=# create table c (c1 int, c2 int) inherits (p); CREATE TABLE regression=# create table gc (gc1 int, p2 int, c1 int, gc2 int) inherits (c); NOTICE: merging column "p2" with inherited definition NOTICE: merging column "c1" with inherited definition CREATE TABLE regression=# \d gc Table "public.gc" Column | Type | Modifiers +-+--- p1 | integer | p2 | integer | p3 | integer | c1 | integer | c2 | integer | gc1| integer | gc2| integer | Inherits: c regression=# > Basically I think if you're doing multiple inheritance and start using > add/drop inherits your column order is going to turn into chop suey quickly. Very possibly, but that doesn't mean that we shouldn't take any concern for avoiding unnecessary changes. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] ADD/DROP INHERITS
Hannu Krosing <[EMAIL PROTECTED]> writes: > for example - to be consistent, one should also make "ALTER TABLE ALTER > COLUMN col SET DEFAULT x" change each "default" value, no ? er, I think that is in fact a no. -- greg ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] ADD/DROP INHERITS
Tom Lane <[EMAIL PROTECTED]> writes: > Greg Stark <[EMAIL PROTECTED]> writes: > > Tom Lane <[EMAIL PROTECTED]> writes: > >> It'll affect the order in which pg_dump lists the parents, which will > >> affect the order in which the columns are created on dump and reload. > > > Hm, if column order is important for table with multiple parents then you > > have > > other problems already. The attislocal->1 mutation will cause any > > singly-inherited columns to go to the head of the list. > > So? They'll get re-merged with the parent column during CREATE TABLE > anyway. But merged columns that are defined locally still appear in the position they were defined locally. Not with the other inherited columns. It's not going to matter to partitioned table users who are dropping the only parent since that will just make *all* the columns into local columns. And it's not going to matter to someone who drops all parents and then replaces them in the same order. But it will matter to the same people to whom the reordered inhseqno matters. If you drop a parent and then readd it then that parent will both go to the end of the list of parents which make any of multiple-inherited columns from that parent go to the end of the list as well as mark any singly-inherited columns from that parent as local which push them to the start of the list. Note that if you don't re-add the parents you'll be left with a column order that intermixes inherited and locally defined columns which *can't* be created in postgres no matter what sequence of commands pg_dump dumps. Basically I think if you're doing multiple inheritance and start using add/drop inherits your column order is going to turn into chop suey quickly. I think the only way to fix that would be to basically erase the whole local/inherited distinction and let pg_dump specify the precise order of all the columns. -- greg ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: Ending EXPLAIN ANALYZE early (was Re: [HACKERS] That EXPLAIN
On Thu, 2006-06-08 at 13:51 -0400, Tom Lane wrote: > I wrote: > > Simon Riggs <[EMAIL PROTECTED]> writes: > >> A full EXPLAIN ANALYZE is always desirable - we agree on that. The > >> question is what we do when one is not available. > > > The least bad alternative I've heard is to let EXPLAIN ANALYZE print > > out stats-so-far if the query is canceled by control-C or statement > > timeout. The objection to this is you may mistake startup transients > > for full query behavior ... but at least the numbers will be good as > > far as they go. > > I thought some more about this Thanks. > So it seems we need a way to stop the query If we were able to stream results back to the client, then EA would be simply a meta-query, emitting a stream of explain text tuples, rather than result tuples. No further need for stopping the query to return intermediate results. If we did this say every minute, rather than on interrupt, you'd be getting close to being able to build that "progress bar" that people have been mentioning - I'd not really thought that was possible until now. Would allow us to build a pg_top that would show the current EA results for a query as it executes. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PG 8.2
On Thu, Jun 08, 2006 at 01:07:44PM -0700, Josh Berkus wrote: > Andrew, > > > It does not yet exist. When it is released you will be able to download > > it from www.postgresql.org. That is some months away. > > I would have just said "We don't know. If you can figure it out, let us > know what's in 8.2, it will save us a lot of arguing." Ha! Anyway, you can get what will eventually become 8.2 via anonymous CVS, but it's still under active development, so caveat emptor. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] ADD/DROP constraints
On a separate note. The one major remaining piece here is in constraints. I'm thinking what I have to check is that every constraint present on the parent table is present on the child tables. And I'm thinking I should do that by looking at the constraint's textual definition (consrc). This doesn't allow you to get by with a single stronger constraint -- you would still need the redundant looser constraint to satisfy the inheritance. But it does let you get by with constraint names that don't match the parent's. I'm not sure that's such a good thing, since pg_dump would then generate a redundant constraint when it generates the table. Maybe that would go if constraints got conislocal and coninh. Or maybe I should insist that a matching constraint name be present *and* that the source text match? That's more of a pain to code though. Is there a convenient hash module in the source for small simple hashes that don't require disk spilling? Just a string->string thing I could look up constraint definitions by name from? -- greg ---(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] ADD/DROP INHERITS
Hannu Krosing <[EMAIL PROTECTED]> writes: > I remember that discussion, but I'm surprised that something got > implemented and accepted into core with so many unsolvable > problems/logical inconsistencies/new pitfalls. The current behavior of ALTER ADD COLUMN & SET DEFAULT is per SQL spec. If you feel it's inconsistent, take it up with the standards committee. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] ADD/DROP INHERITS
Ühel kenal päeval, N, 2006-06-08 kell 11:42, kirjutas Greg Stark: > Hannu Krosing <[EMAIL PROTECTED]> writes: > > > Do you mean that in newer versions ALTER TABLE ADD COLUMN will change > > existing data without asking me ? > > > > That would be evil! > > > > Even worse if ALTER TABLE ALTER COLUMN SET DEFAULT would do the same. > > postgres=# alter table test add b integer default 1; > ALTER TABLE > postgres=# select * from test; > a | b > ---+--- > 0 | 1 > (1 row) > > > > It was awfully annoying for users when that feature was missing. > > > Any non-linearities in the user interface like this > > > end up being surprises and annoyances for users. > > > > I would be *really*, *really*, *really* annoyed if an op that I expected > > to take less than 1 sec takes 5 hours and then forces me to spend > > another 10 hours on VACUUM FULL+REINDEX or CLUSTER to get performance > > back. > > I forget whether the developer managed to get it working without doing any > table rewriting. In theory the table just needs to know that records that are > "missing" that column in the null bitmap should behave as if they have the > default value. But I seem to recall some headaches with that approach. I remember that discussion, but I'm surprised that something got implemented and accepted into core with so many unsolvable problems/logical inconsistencies/new pitfalls. for example - to be consistent, one should also make "ALTER TABLE ALTER COLUMN col SET DEFAULT x" change each "default" value, no ? but how should one know it for records which are updated, possibly in columns newer than the one with changed DEFAULT. Or was a new default bitmap introduced in addition to null bitmap ? -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.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] PG 8.2
Andrew, > It does not yet exist. When it is released you will be able to download > it from www.postgresql.org. That is some months away. I would have just said "We don't know. If you can figure it out, let us know what's in 8.2, it will save us a lot of arguing." -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ADD/DROP INHERITS
Greg Stark wrote: Tom Lane <[EMAIL PROTECTED]> writes: Greg Stark <[EMAIL PROTECTED]> writes: It's not a precise noop in database internal data structures, but I don't see any user-visible effects switching around seqnos would have. But maybe there's something I don't know about? It'll affect the order in which pg_dump lists the parents, which will affect the order in which the columns are created on dump and reload. (Or at least it ought to ... right offhand I don't see anything in the pg_dump source code that ensures the original order is preserved. This may be a pg_dump bug.) Hm, if column order is important for table with multiple parents then you have other problems already. The attislocal->1 mutation will cause any singly-inherited columns to go to the head of the list. If you dropped any table but the first parent then it isn't going to matter if it's in the right place in the inheritance list or not. If you really want to preserve column order then it might be necessary to invent some syntax that indicates a column should be created with attislocal=f. Then pg_dump can dump a complete list of columns including inherited columns and CREATE TABLE can use that order merging in inherited definitions without changing the order. But it would be a nonstandard extension :( hmm, I take it we will just select by name in some canonical order (presumably the parent's order)? ISTR discussion at one time of implementing logical vs. physical ordering ... would that have any relevance here? cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] How to avoid transaction ID wrap
Ühel kenal päeval, N, 2006-06-08 kell 12:09, kirjutas Tom Lane: > Hannu Krosing <[EMAIL PROTECTED]> writes: > > If the aim is to *only* avoid transaction wraparound, then maybe we > > could introduce VACUUM FREEZE ONLY; which never removes any old tuples, > > but instead just marks them by setting xmin=xmax for them, in addition > > to its freezing of live-and-visible-to-all tuples. > > > This would avoid touching indexes at all and may well be what is desired > > for tables with only very little updates/deletes. > > Seems like useless complexity. If there are so few dead tuples that you > can afford to not reclaim them, then there are so few that reclaiming > them isn't really going to cost much either ... It will cost 1 full scan per index, which can be quite a lot of disk read traffic, if indexes are not used, say when most access is local to some hotspot. > regards, tom lane > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.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] ADD/DROP INHERITS
Greg Stark <[EMAIL PROTECTED]> writes: > Tom Lane <[EMAIL PROTECTED]> writes: >> It'll affect the order in which pg_dump lists the parents, which will >> affect the order in which the columns are created on dump and reload. > Hm, if column order is important for table with multiple parents then you have > other problems already. The attislocal->1 mutation will cause any > singly-inherited columns to go to the head of the list. So? They'll get re-merged with the parent column during CREATE TABLE anyway. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] PG 8.2
It does not yet exist. When it is released you will be able to download it from www.postgresql.org. That is some months away. cheers andrew Milen Kulev wrote: Hi guy, Where I con download Postgres 8.2 from ? Regards Milen ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] ADD/DROP INHERITS
Tom Lane <[EMAIL PROTECTED]> writes: > Greg Stark <[EMAIL PROTECTED]> writes: > > It's not a precise noop in database internal data structures, but I don't > > see > > any user-visible effects switching around seqnos would have. But maybe > > there's > > something I don't know about? > > It'll affect the order in which pg_dump lists the parents, which will > affect the order in which the columns are created on dump and reload. > (Or at least it ought to ... right offhand I don't see anything in the > pg_dump source code that ensures the original order is preserved. This > may be a pg_dump bug.) Hm, if column order is important for table with multiple parents then you have other problems already. The attislocal->1 mutation will cause any singly-inherited columns to go to the head of the list. If you dropped any table but the first parent then it isn't going to matter if it's in the right place in the inheritance list or not. If you really want to preserve column order then it might be necessary to invent some syntax that indicates a column should be created with attislocal=f. Then pg_dump can dump a complete list of columns including inherited columns and CREATE TABLE can use that order merging in inherited definitions without changing the order. But it would be a nonstandard extension :( -- greg ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] PG 8.2
Hi guy, Where I con download Postgres 8.2 from ? Regards Milen ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: Ending EXPLAIN ANALYZE early (was Re: [HACKERS] That EXPLAIN ANALYZE patch still needs work)
Tom Lane wrote: > A possible objection to this is that running a query inside a > subtransaction might have different/worse performance than running it > at top level. I don't recall any severe bottlenecks of that kind but > that doesn't mean there aren't any (Alvaro, any comments?) Nope, nothing that I recall. Maybe make sure to generate the Xid of the subtransaction before starting the EXPLAIN itself (so that there is no pg_subtrans I/O pending, etc) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: Ending EXPLAIN ANALYZE early (was Re: [HACKERS] That EXPLAIN ANALYZE patch still needs work)
Tom Lane <[EMAIL PROTECTED]> writes: > Gregory Stark <[EMAIL PROTECTED]> writes: > > Tom Lane <[EMAIL PROTECTED]> writes: > > Would it be possible to make a whole new protocol message for EXPLAIN > > results? > > I'm really unwilling to get into that. For one thing, that would > absolutely positively break *all* use of EXPLAIN from un-fixed clients, > whether you were trying to stop early or not. Well I was picturing still having the manual EXPLAIN ANALYZE which works as it does now replacing the query results in addition to a lower level protocol request which generates a protocol response with the data. > > The reason I'm suggesting that is because it might make it easier to > > implement > > the SIGINFO handler that sends incremental EXPLAIN results on demand that I > > was describing earlier. > > Doesn't matter, because that's not happening ;-) SIGINFO isn't > portable, and even if it were, a signal handler couldn't possibly > generate EXPLAIN output (remember those catalog accesses). Well the signal gets handled by psql. It would have to be a wire protocol message like Query Cancel that the backend watches for and handles the first time it can. -- greg ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] drop if exists remainder
Tom Lane wrote: > Josh Berkus writes: > > This has been a problem in the past. I'd generally ask that, if a patch > > which was discussed on -hackers gets rejected on -patches, that discussion > > be brought back to -hackers. Often the people who supported the original > > feature are not on -patches and then are unpleasantly surprised when the > > feature they though was accepted doesn't show up in the next version. > > Um, if they're not reading -patches, why would they think the feature > had been accepted, or even submitted? In any case, when we reject a > patch, it's not usually a conclusion that will get reversed just because > more people are involved in the discussion. The people who might > actually be able to *fix* the patch are probably reading -patches. But there may be people in -hackers who can *convince* those on -patches that the patch should get fixed and not dropped (e.g. the case at hand). -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] drop if exists remainder
Josh Berkus writes: > This has been a problem in the past. I'd generally ask that, if a patch > which was discussed on -hackers gets rejected on -patches, that discussion > be brought back to -hackers. Often the people who supported the original > feature are not on -patches and then are unpleasantly surprised when the > feature they though was accepted doesn't show up in the next version. Um, if they're not reading -patches, why would they think the feature had been accepted, or even submitted? In any case, when we reject a patch, it's not usually a conclusion that will get reversed just because more people are involved in the discussion. The people who might actually be able to *fix* the patch are probably reading -patches. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Running a query twice to ensure cached results.
Ron Mayer <[EMAIL PROTECTED]> writes: > Does running a query only twice really insure that a result is cached? > It seems not to be the case for seq-scans on Linux. Should work for tables small enough to fit into the shared_buffers arena. I wouldn't necessarily assume it for large tables. Note that what I was really doing was taking the timing measurement again on data cached by the *first* run, so that I would have something that could fairly be compared to the following EXPLAIN ANALYZE --- which of course would likewise be operating on cached data. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] ADD/DROP INHERITS
Greg Stark <[EMAIL PROTECTED]> writes: > It's not a precise noop in database internal data structures, but I don't see > any user-visible effects switching around seqnos would have. But maybe there's > something I don't know about? It'll affect the order in which pg_dump lists the parents, which will affect the order in which the columns are created on dump and reload. (Or at least it ought to ... right offhand I don't see anything in the pg_dump source code that ensures the original order is preserved. This may be a pg_dump bug.) > I did wonder whether it was kosher to leave holes. Not sure. I don't offhand see anything that requires the numbers to be consecutive. If you don't compact out the holes during DROP, then ADD could use the rule of "first unused number" instead of max+1. This would ensure DROP/ADD is a no-op for simple cases in which you only unlink from one parent. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: Ending EXPLAIN ANALYZE early (was Re: [HACKERS] That EXPLAIN
I think what he meant was "a separate EXPLAIN-CANCEL message" on a cancel-type connection, which would be completely backwards compatible. Old clients simply wouldn't be able to use the special EXPLAIN cancel, just like it is now. On Thu, June 8, 2006 3:01 pm, Tom Lane wrote: > Gregory Stark <[EMAIL PROTECTED]> writes: > >> Tom Lane <[EMAIL PROTECTED]> writes: >> Would it be possible to make a whole new protocol message for EXPLAIN >> results? > > I'm really unwilling to get into that. For one thing, that would > absolutely positively break *all* use of EXPLAIN from un-fixed clients, > whether you were trying to stop early or not. > >> The reason I'm suggesting that is because it might make it easier to >> implement the SIGINFO handler that sends incremental EXPLAIN results on >> demand that I was describing earlier. > > Doesn't matter, because that's not happening ;-) SIGINFO isn't > portable, and even if it were, a signal handler couldn't possibly generate > EXPLAIN output (remember those catalog accesses). > > > regards, tom lane > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > > > http://www.postgresql.org/docs/faq > > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] drop if exists remainder
Andrew Dunstan wrote: > Josh Berkus wrote: > >Andrew, > > > >>What happened was that Tom objected to (or at least queried the need > >>for) the patch on the grounds that it was bloat that nobody had asked > >>for. And when I asked I wasn't exactly deluged with requests to commit, > >>so I concluded that it was not generally wanted. > > > >Did you poll on -hackers or on -patches? A *lot* less people read > >-patches. > > Yeah. true. Although, I must say that I discovered very early on in my > pg-hacking experience that unless you read -patches too you don't really > know what's going on ;-) Actually reading -committers is also a must, because you then know that something is really going on and it's not just chatter. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: Ending EXPLAIN ANALYZE early (was Re: [HACKERS] That EXPLAIN ANALYZE patch still needs work)
Gregory Stark <[EMAIL PROTECTED]> writes: > Tom Lane <[EMAIL PROTECTED]> writes: > Would it be possible to make a whole new protocol message for EXPLAIN results? I'm really unwilling to get into that. For one thing, that would absolutely positively break *all* use of EXPLAIN from un-fixed clients, whether you were trying to stop early or not. > The reason I'm suggesting that is because it might make it easier to implement > the SIGINFO handler that sends incremental EXPLAIN results on demand that I > was describing earlier. Doesn't matter, because that's not happening ;-) SIGINFO isn't portable, and even if it were, a signal handler couldn't possibly generate EXPLAIN output (remember those catalog accesses). regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] ADD/DROP INHERITS
Tom Lane <[EMAIL PROTECTED]> writes: > BTW ... are you intending to renumber inhseqno entries of remaining > pg_inherits items after DROP INHERITS? Which seqno will be assigned > by ADD INHERITS? This seems like another area in which DROP/ADD will > not be a complete no-op. I assigned inhseqno to be max(inhseqno)+1. I was already scanning the parents to check for duplicate parents so I just accumulated a maximum seqno at the same time. It's not a precise noop in database internal data structures, but I don't see any user-visible effects switching around seqnos would have. But maybe there's something I don't know about? The actual order only seems to be significant in that it affects the ordering of inherited columns. But that's already thrown to the wind as soon as you allow adding new parents anyways. I'm just matching by name regardless of position. And in any case that is only going to match the original ordering of the original sequno ordering. I did wonder whether it was kosher to leave holes. -- greg ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] drop if exists remainder
Josh Berkus wrote: Andrew, What happened was that Tom objected to (or at least queried the need for) the patch on the grounds that it was bloat that nobody had asked for. And when I asked I wasn't exactly deluged with requests to commit, so I concluded that it was not generally wanted. Did you poll on -hackers or on -patches? A *lot* less people read -patches. Yeah. true. Although, I must say that I discovered very early on in my pg-hacking experience that unless you read -patches too you don't really know what's going on ;-) This has been a problem in the past. I'd generally ask that, if a patch which was discussed on -hackers gets rejected on -patches, that discussion be brought back to -hackers. Often the people who supported the original feature are not on -patches and then are unpleasantly surprised when the feature they though was accepted doesn't show up in the next version. Fair point. Maybe I only posted on -patches. 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: Ending EXPLAIN ANALYZE early (was Re: [HACKERS] That EXPLAIN ANALYZE patch still needs work)
Tom Lane <[EMAIL PROTECTED]> writes: > After we've printed the results, we have a bit of a problem: if > ExplainCancelPending is set, we now need to abort the transaction. It > would not do at all to allow an incompletely executed UPDATE to commit. > I experimented with throwing an elog() at the bottom of ExplainQuery() > after end_tup_output(), but this does not work: psql only prints the > error and not the data, because libpq throws away the query result > upon seeing the error. We could probably hack psql to print the results > before noting the error, but I'm concerned about making a change that > would change the behavior for other error-at-end-of-statement cases. > Also, it's not clear what might have to happen to get non-psql clients > to play along. Would it be possible to make a whole new protocol message for EXPLAIN results? The reason I'm suggesting that is because it might make it easier to implement the SIGINFO handler that sends incremental EXPLAIN results on demand that I was describing earlier. Then libpq would have a separate api call to check for EXPLAIN results. If a non-psql client doesn't check it then it doesn't get the EXPLAIN results but the behaviour is correct. If it does then it can get the EXPLAIN results. This might also let you run with EXPLAIN ANALYZE instrumentation but still get the regular query results. Since the explain analyze results would still be available out of band. -- Gregory Stark T + 514 938 2456 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Running a query twice to ensure cached results.
Tom Lane wrote: -- do it again to ensure fully cached bench=# select count(*) from accounts; Short summary: Does running a query only twice really insure that a result is cached? It seems not to be the case for seq-scans on Linux. I think this may matters to the discussions about a readahead thread/process that come up on this list that come up here occasionally. Experimental results here suggest that for larger tables Linux seems to detect a seq-scan and not bother caching. It's very reproducible for me here to do a reboot and not see the full speedup on a seq_scan until the third time I run a query.su An example shown below [1] shows that the third run of a query is faster than the second run. The output of a 'vmstat 5' [2] while these queries was happening agrees that significant I/O was still happening on the second run, but no I/O happened the third time. The table comfortably fits in memory (700MB table on a 2GB ram machine) and the machine was otherwise idle so noone else wiped out the cache between the first and second runs. Why do I think this is worth mentioning here? * I think it impacts the occasional thread about wanting to include logic in postgresql for readahead [3] or for the threads suggesting hinting to the the OS though madvise or similar to avoid caching seq-scans. It seems that the Linux is detecting and at least somewhat reacting to seq scans even with no hinting. Anything added to postgresql might end up being a duplicated effort. I think Bruce suggested that Solaris does this free-behind automatically [4], but this is the first I've noticed that Linux seems to do similar. * I think it matters to people who post explain analyze twice without running it so often they get stable results. (I note that this was not a problem for Tom since the timing of his first and second runs were the same so I assume he was just saying that he observed that the query was cached rather than that the first run forced the second run to be cached.) Ron = == [note 1] the repeated queries showing the speedup after 3 runs. == Running the same select count(*) 4 times after a clean reboot. == Seems the OS's caching logic decided that the first seq_scan == wasn't 'interesting' enough = fli=# select count(*) from facets_s; count -- 15976558 (1 row) Time: 29788.047 ms fli=# select count(*) from facets_s; count -- 15976558 (1 row) Time: 19344.573 ms fli=# select count(*) from facets_s; count -- 15976558 (1 row) Time: 13411.272 ms fli=# select count(*) from facets_s; count -- 15976558 (1 row) Time: 13107.856 ms # [note 2] vmstat 5 while the above queries were being run procs ---memory-- ---swap-- -io --system-- cpu r b swpd free buff cache si sobibo incs us sy id wa 1 1140 62140 71256 713360004731 9284 7 1 92 0 *** the first time 1 0140 50860 31912 80830402 2521529 1147 2612 49 15 0 36 1 0360 54420 2 85524000 23934 7 1139 2553 47 14 0 39 0 1360 54008 11100 87870800 2370425 1149 2467 46 12 0 41 0 1360 52512 11140 89659200 24062 6 1135 2460 47 11 0 41 *** the second time 0 0360 52688 11172 90691600 1335719 1085 1989 31 7 38 24 1 0360 53976 11076 9125400 44 1427357 1113 2102 32 7 29 32 2 0360 54788 10908 92378800 2450954 1171 2474 46 12 0 42 1 0360 54944 3096 93994800 1118039 1093 1976 65 13 0 22 *** the third time 3 0360 54280 3872 94050800 26414 1041 1560 85 15 0 0 1 0360 53852 3904 940940008829 1022 1505 53 9 36 2 2 0360 51616 4052 94306800 44354 1037 1552 82 15 0 4 1 0360 51488 4060 9431800022 2 1013 1522 84 16 0 0 # [3] http://archives.postgresql.org/pgsql-hackers/2005-11/msg01449.php [4] http://archives.postgresql.org/pgsql-performance/2003-10/msg00188.php ---(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] [PATCHES] drop if exists remainder
Andrew, > What happened was that Tom objected to (or at least queried the need > for) the patch on the grounds that it was bloat that nobody had asked > for. And when I asked I wasn't exactly deluged with requests to commit, > so I concluded that it was not generally wanted. Did you poll on -hackers or on -patches? A *lot* less people read -patches. This has been a problem in the past. I'd generally ask that, if a patch which was discussed on -hackers gets rejected on -patches, that discussion be brought back to -hackers. Often the people who supported the original feature are not on -patches and then are unpleasantly surprised when the feature they though was accepted doesn't show up in the next version. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] ADD/DROP INHERITS
Greg Stark <[EMAIL PROTECTED]> writes: > I'm a bit confused about what pg_depends entries would be necessary then. If > there's something like this there: > Child Table <--(AUTO)-- pg_inherit entry --(NORMAL)-> Parent Table I think that would work, but it seems pretty baroque. pg_inherit entries are not separately accessible SQL objects; not in the sense that, say, a table's rowtype is. I think it'd be just about as easy to leave the catalog definitions as-is and just manually drop the child-to-parent pg_depend entry. This would certainly be less code than all the infrastructure needed to add pg_inherit entries as a separate kind of dependency object. I also note that to go in this direction, pg_inherits would need to add an OID column, and an index on it. BTW ... are you intending to renumber inhseqno entries of remaining pg_inherits items after DROP INHERITS? Which seqno will be assigned by ADD INHERITS? This seems like another area in which DROP/ADD will not be a complete no-op. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] drop if exists remainder
Jim C. Nasby wrote: Important as you are, "one swallow does not make a summer". On the other hand, unless we want the lists filling up with a bunch of +1 posts, it's probably better to assume that unless someone objects a patch would be accepted. What happened was that Tom objected to (or at least queried the need for) the patch on the grounds that it was bloat that nobody had asked for. And when I asked I wasn't exactly deluged with requests to commit, so I concluded that it was not generally wanted. Since then I have had probably 10 requests for it, so I am now going to work to update it and will post a revised patch. 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] drop if exists remainder
On Thu, Jun 08, 2006 at 12:34:54PM -0400, Andrew Dunstan wrote: > David Fetter wrote: > >On Thu, Jun 08, 2006 at 09:43:19AM -0400, Andrew Dunstan wrote: > > > >>OK there does seem to be some demand for this, so I will rework the > >>patch, and hope to get it done by feature freeze - it has bitrotted > >>with 7 merge conflicts, including the grammar file, so I need to > >>look carefully at that. Pity people didn't speak up when this was > >>first raised. :-) > >> > > > >I did :) > > > > > > > Important as you are, "one swallow does not make a summer". On the other hand, unless we want the lists filling up with a bunch of +1 posts, it's probably better to assume that unless someone objects a patch would be accepted. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Ending EXPLAIN ANALYZE early (was Re: [HACKERS] That EXPLAIN ANALYZE patch still needs work)
I wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
>> A full EXPLAIN ANALYZE is always desirable - we agree on that. The
>> question is what we do when one is not available.
> The least bad alternative I've heard is to let EXPLAIN ANALYZE print
> out stats-so-far if the query is canceled by control-C or statement
> timeout. The objection to this is you may mistake startup transients
> for full query behavior ... but at least the numbers will be good as
> far as they go.
I thought some more about this, and it seems doable but tricky (ie,
there are many wrong ways to do it). Here are my conclusions:
We can't just use the normal QueryCancel logic that throws an
elog(ERROR) from someplace down inside the query. This would leave
the backend in an unclean state from which we could only certainly
recover by doing AbortTransaction. And once we've aborted the
transaction we can't do catalog accesses, which gets in the way of
producing the EXPLAIN printout.
Running the test query inside a subtransaction would fix that,
but aborting the subtransaction would throw away the executor state,
including the Instrumentation nodes we need.
So it seems we need a way to stop the query without raising an error
per se. What I'm thinking is that while EXPLAIN ANALYZE is running,
timeout or SIGINT should not set QueryCancelPending, but instead
set a separate flag "ExplainCancelPending", which we then test in
ExecProcNode(), say
if (node->instrument)
{
+ if (ExplainCancelPending)
+ return NULL;
InstrStartNode(node->instrument);
}
There might be one or two other places to check it, but basically
we'd only notice the flag in very limited circumstances where
it's definitely safe to force early termination of ExecutorRun.
When control comes back to explain.c, we just print the results as
normal (but probably adding a line explicitly noting that the query
was abandoned before completion). Note that we won't have any "running"
Instrumentation nodes to contend with, since the change doesn't cause
nodes to drop out after they've started timing. So the data is good,
just incomplete.
After we've printed the results, we have a bit of a problem: if
ExplainCancelPending is set, we now need to abort the transaction. It
would not do at all to allow an incompletely executed UPDATE to commit.
I experimented with throwing an elog() at the bottom of ExplainQuery()
after end_tup_output(), but this does not work: psql only prints the
error and not the data, because libpq throws away the query result
upon seeing the error. We could probably hack psql to print the results
before noting the error, but I'm concerned about making a change that
would change the behavior for other error-at-end-of-statement cases.
Also, it's not clear what might have to happen to get non-psql clients
to play along.
It seems like the best solution is to establish a subtransaction around
the entire EXPLAIN command (not just the test query), which we can abort
after we've printed the results.
A possible objection to this is that running a query inside a
subtransaction might have different/worse performance than running it
at top level. I don't recall any severe bottlenecks of that kind but
that doesn't mean there aren't any (Alvaro, any comments?)
Note that this would mean that ending an EXPLAIN ANALYZE early, via
either control-C or statement timeout, would be treated as a non-error
situation from the point of view of the outer transaction. This bothers
me a bit, because in fact the effects if any of the tested query would
have been rolled back. Not sure we have any choice though. If we
expose the error then we'll have problems with clients not showing the
EXPLAIN results.
Thoughts?
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [HACKERS] ADD/DROP INHERITS
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Greg Stark wrote: > > > Well I'm not actually deleting anything. The dependency is between the two > > tables and I don't want to delete either of the tables. > > > > Perhaps what should really be happening here is that there should be > > dependencies from the pg_inherit entry to the two tables rather than from > > one > > table to the other. > > > > Then a simple performDeletion on the pg_inherit entry would take care of the > > dependencies. > > Sounds like a reasonable thing to do ... If you drop the parent table, > does that cascade to the child table as well? Maybe what should happen > is that the child table is "disinherited". I think what should happen is: . If you drop a child the pg_inherit line (and dependencies) silently disappears but the parent stays. . If you drop a parent you get an error unless you use cascade in which case the pg_inherits line and the child all go away. . If you disown the child the pg_inherit line (and dependencies) is deleted At least that's what partitioned table users would want. In that case the partitions are creatures of the main table with no identity of their own. But perhaps that's not the case for other users of inherited tables? I'm a bit confused about what pg_depends entries would be necessary then. If there's something like this there: Child Table <--(AUTO)-- pg_inherit entry --(NORMAL)-> Parent Table Then deleting the child table will correctly delete the pg_inherits line, but deleting the parent with CASCADE will stop at the pg_inherits line without deleting the child. Whereas something like this: Child Table <---(AUTO)--- pg_inherit entry --(NORMAL)-> Parent Table --(NORMAL)--> Would make the cascade go through but mean that I can't drop the pg_inherit line with performDeletion() without having the child table disappear. -- greg ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] ADD/DROP INHERITS
Greg Stark <[EMAIL PROTECTED]> writes: > Perhaps what should really be happening here is that there should be > dependencies from the pg_inherit entry to the two tables rather than from one > table to the other. This seems unlikely to still have the correct semantics (DROP on child is OK, DROP on parent is not unless CASCADE, in which case child is dropped too, etc etc). regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Type of bare text strings
Alvaro Herrera <[EMAIL PROTECTED]> writes: > "unknown". This seems to be a cstring (i.e. length 5 considering the > trailing \0) Yeah. "unknown" used to have the same representation as "text", ie varlena, but I changed it recently because I realized that the normal thing we do with an "unknown" literal is feed it to some datatype's input converter. Making it the same as cstring saves a conversion step. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] ADD/DROP INHERITS
Greg Stark <[EMAIL PROTECTED]> writes: > Tom Lane <[EMAIL PROTECTED]> writes: >> The implementation I had in mind was to add columns similar to attinhcount >> and attislocal to pg_constraint. > Hm that would be simpler. That still leaves NOT NULL as a bit of a headache. Yeah, I think we would want to start storing NOT NULL constraints explicitly in pg_constraint so that we could track them. This would allow fixing some other things too, like the fact that we fail to remember names for NOT NULL constraints. attnotnull might still be useful as an optimization, or maybe it should just go away. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] That EXPLAIN ANALYZE patch still needs work
Simon Riggs <[EMAIL PROTECTED]> writes: > So far we have myself, Kevin, Martijn and Luke all saying there is a > distortion or a massive overhead caused by EXPLAIN ANALYZE. > http://archives.postgresql.org/pgsql-hackers/2006-03/msg00954.php > http://archives.postgresql.org/pgsql-patches/2006-05/msg00168.php The first of these shows the exact opposite of what you are claiming, and the second is without details of any sort that might help determine what the actual problem is. Given that we're seeing diametrically opposed results on the same OS (FC5) and similar (at least all Intel) hardware, I think the prudent thing is to find out what's really going on before leaping in with proposed solutions. As the person who's *not* seeing the problem, I'm not in a position to do that investigation... 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] More on inheritance and foreign keys
Andreas Pflug <[EMAIL PROTECTED]> writes: > I managed uniqueness using normal indexes and ins/upd triggers on all > child tables: Do I need to point out the race-condition problems in this? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] drop if exists remainder
David Fetter wrote: On Thu, Jun 08, 2006 at 09:43:19AM -0400, Andrew Dunstan wrote: OK there does seem to be some demand for this, so I will rework the patch, and hope to get it done by feature freeze - it has bitrotted with 7 merge conflicts, including the grammar file, so I need to look carefully at that. Pity people didn't speak up when this was first raised. :-) I did :) Important as you are, "one swallow does not make a summer". 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] ADD/DROP INHERITS
Tom Lane <[EMAIL PROTECTED]> writes: > Greg Stark <[EMAIL PROTECTED]> writes: > > Come to think of it it's pretty strange that you can drop an inherited > > constraint from a child. And doing an experiment it seems you can also DROP > > NOT NULL on a child which is also pretty strange. > > Yeah. I think we had agreed that this is a bug. Note the TODO entries: Ok, so it's definitely correct for me to require that new children have NOT NULL if their parent has NOT NULL. > > I don't see how to block these operations though unless we either search > > parent classes for constraints to check at run-time or add additional > > dependency records to block dropping these things. > > The implementation I had in mind was to add columns similar to attinhcount > and attislocal to pg_constraint. Hm that would be simpler. That still leaves NOT NULL as a bit of a headache. -- greg ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] ADD/DROP INHERITS
Greg Stark wrote: > Well I'm not actually deleting anything. The dependency is between the two > tables and I don't want to delete either of the tables. > > Perhaps what should really be happening here is that there should be > dependencies from the pg_inherit entry to the two tables rather than from one > table to the other. > > Then a simple performDeletion on the pg_inherit entry would take care of the > dependencies. Sounds like a reasonable thing to do ... If you drop the parent table, does that cascade to the child table as well? Maybe what should happen is that the child table is "disinherited". I note that our documentation http://www.postgresql.org/docs/8.1/static/sql-droptable.html does not specify what happens. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] How to avoid transaction ID wrap
On Thu, Jun 08, 2006 at 09:13:33AM -0700, Joshua D. Drake wrote: > Tom Lane wrote: > >"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > >>I was recently involved in a project where we had to decrease the > >>checkpoint_timeout . The problem was, that the database was performing > >>so many transactions that if we waiting for 5 minutes, checkpoint would > >>take entirely too long. > > > >Seems like the correct fix for that is to make the bgwriter more > >aggressive. Narrowing the checkpoint spacing is a pretty horrid answer > >because of the resulting increase in full-page-image WAL traffic. > > Well we did that as well. Here are the basic symptons: > > During normal processing which contained about 250 connections > everything was fine. A checkpoint would start and connections would > start piling up, sometimes breaking 1000. > > We narrowed that down to users having to wait longer for query execution > so instead of just reusing connections new connections had to be > initiated because the existing connections were busy. > > We tried many different parameters, and bgwriter did significantly help > but the only "solution" was to make checkpoints happen at a much more > aggressive time frame. > > Modify bgwriters settings and the checkpoint actually increased our > velocity by about 70% by the time we were done. Bgwriter was definitely > the largest chunk of that although other parameters combined outweighed > it (effective_cache, shared_buffers etc...). Did you try increasing the checkpoint interval, in the hopes that it would allow the bgwritter enough extra time to get everything pushed out? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] ADD/DROP INHERITS
On Thu, Jun 08, 2006 at 12:19:49PM -0400, Greg Stark wrote: > > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > > On Thu, Jun 08, 2006 at 11:42:49AM -0400, Greg Stark wrote: > > > But I seem to recall some headaches with that approach. > > > > What happens if you > > > > ALTER TABLE ... ADD new_column int DEFAULT 1; > > ALTER TABLE ... ALTER new_column SET DEFAULT 2; > > Ah yes. Keeping track of multiple old defaults and when they were in effect > would indeed be quite a headache. Probably. One possibility would be to track the table definition on an XID basis and compare that info to the XMIN of a given row; that would allow you to know exactly what the state of the table columns was. But there's still a lot of pitfalls with that, such as VACUUM FREEZE. Since ALTER TABLE on a very large table can be such a nightmare maybe some day this will happen, but I'm not holding my breath. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Snowball and ispell in tsearch2
Maybe putting it on pgFoundry? Hmm, it's a variant. We can create project 'tsearch2_dict' and there I'll place contrib module which will make all Snowball stemmers. Right now I'm working on supporting OpenOffice's dictionaries in tsearch2, so it will be simple to add it to packaging system. I suggest that in the same cvs somebody will manage packages/package's builder for different packaging system (sorry, I havn't any experience with that systems) BTW, it will be good, if packaging will work with "maked" postgres, something like: % cd PGSQL/contrib/tsearch2 % make LANG=norwegian -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ADD/DROP INHERITS
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > On Thu, Jun 08, 2006 at 11:42:49AM -0400, Greg Stark wrote: > > But I seem to recall some headaches with that approach. > > What happens if you > > ALTER TABLE ... ADD new_column int DEFAULT 1; > ALTER TABLE ... ALTER new_column SET DEFAULT 2; Ah yes. Keeping track of multiple old defaults and when they were in effect would indeed be quite a headache. -- greg ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Type of bare text strings
Alvaro Herrera wrote:
> Jim C. Nasby wrote:
> > What type are bare strings considered if they haven't been cast? I'm
> > curious as
> > to how the first case is of size 5, and how the last case is 301...
> >
> > decibel=# select pg_column_size('test');
> > 5
>
> "unknown". This seems to be a cstring (i.e. length 5 considering the
> trailing \0)
Yup. This is handled by this code:
else if (typlen == -2)
{
/* cstring */
result = strlen(DatumGetCString(value)) + 1;
}
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] How to avoid transaction ID wrap
Tom Lane wrote: "Joshua D. Drake" <[EMAIL PROTECTED]> writes: I was recently involved in a project where we had to decrease the checkpoint_timeout . The problem was, that the database was performing so many transactions that if we waiting for 5 minutes, checkpoint would take entirely too long. Seems like the correct fix for that is to make the bgwriter more aggressive. Narrowing the checkpoint spacing is a pretty horrid answer because of the resulting increase in full-page-image WAL traffic. Well we did that as well. Here are the basic symptons: During normal processing which contained about 250 connections everything was fine. A checkpoint would start and connections would start piling up, sometimes breaking 1000. We narrowed that down to users having to wait longer for query execution so instead of just reusing connections new connections had to be initiated because the existing connections were busy. We tried many different parameters, and bgwriter did significantly help but the only "solution" was to make checkpoints happen at a much more aggressive time frame. Modify bgwriters settings and the checkpoint actually increased our velocity by about 70% by the time we were done. Bgwriter was definitely the largest chunk of that although other parameters combined outweighed it (effective_cache, shared_buffers etc...). Sincerely, Joshua D. Drake regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- === 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/ ---(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] Type of bare text strings
Jim C. Nasby wrote:
> What type are bare strings considered if they haven't been cast? I'm curious
> as
> to how the first case is of size 5, and how the last case is 301...
>
> decibel=# select pg_column_size('test');
> 5
"unknown". This seems to be a cstring (i.e. length 5 considering the
trailing \0)
> decibel=# select pg_column_size('test'::varchar);
> 8
> decibel=# select pg_column_size('test'::text);
> 8
> decibel=# select pg_column_size('test'::char(4));
> 8
4 fixed varlena + 4 string length
> decibel=# select pg_column_size('test'::name);
> 64
name is fixed 64 bytes (not varlena)
> decibel=# select
> pg_column_size('123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
> 301
Same as the first case. (There are actual 300 chars here according to
my count, is that right?)
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] How to avoid transaction ID wrap
Hannu Krosing <[EMAIL PROTECTED]> writes: > If the aim is to *only* avoid transaction wraparound, then maybe we > could introduce VACUUM FREEZE ONLY; which never removes any old tuples, > but instead just marks them by setting xmin=xmax for them, in addition > to its freezing of live-and-visible-to-all tuples. > This would avoid touching indexes at all and may well be what is desired > for tables with only very little updates/deletes. Seems like useless complexity. If there are so few dead tuples that you can afford to not reclaim them, then there are so few that reclaiming them isn't really going to cost much either ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] That EXPLAIN ANALYZE patch still needs work
On Thu, 2006-06-08 at 10:27 -0400, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > On Wed, 2006-06-07 at 17:28 -0400, Tom Lane wrote: > >> The overhead seems to be on the order of a couple tens of percent usually. > >> I don't see how that makes the difference between an EXPLAIN ANALYZE you > >> can run and one you can't. > > > Well, thats not my experience and doesn't match others posted on > > -hackers. > > > A simple test with pgbench shows the timing overhead of EXPLAIN ANALYZE > > to be consistently above 500% (or more than +400%, depending upon how > > you style those numbers). > > I think we ought to find out why your machine is so broken. > I'm too lazy to pull up any of my other machines right now, but this is > generally consistent with my experience ever since EXPLAIN ANALYZE was > written. Great. Well it isn't consistent with mine, or others who've posted to this list. > So: what's your platform exactly? FC5, Intel laptop running cvstip, new in January. But thats irrelevant. I'm not a user, I solve others problems, as you know. Hence my interest in a usable tool to do that. So far we have myself, Kevin, Martijn and Luke all saying there is a distortion or a massive overhead caused by EXPLAIN ANALYZE. http://archives.postgresql.org/pgsql-hackers/2006-03/msg00954.php http://archives.postgresql.org/pgsql-patches/2006-05/msg00168.php It's real. I won't press the point further. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] ADD/DROP INHERITS
On Thu, Jun 08, 2006 at 11:42:49AM -0400, Greg Stark wrote: > > > It was awfully annoying for users when that feature was missing. > > > Any non-linearities in the user interface like this > > > end up being surprises and annoyances for users. > > > > I would be *really*, *really*, *really* annoyed if an op that I expected > > to take less than 1 sec takes 5 hours and then forces me to spend > > another 10 hours on VACUUM FULL+REINDEX or CLUSTER to get performance > > back. > > I forget whether the developer managed to get it working without doing any > table rewriting. In theory the table just needs to know that records that are > "missing" that column in the null bitmap should behave as if they have the > default value. But I seem to recall some headaches with that approach. What happens if you ALTER TABLE ... ADD new_column int DEFAULT 1; ALTER TABLE ... ALTER new_column SET DEFAULT 2; -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 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] More on inheritance and foreign keys
Tom Lane wrote: "Zeugswetter Andreas DCP SD" <[EMAIL PROTECTED]> writes: The solution to the foreign key problem seems easy if I modify PostgreSQL implementation and take off the ONLY word from the SELECT query, but it's not an option for me, as I'm I think that the ONLY was wrong from day one :-( Well, sure, but until we have an implementation that actually *works* across multiple tables, it has to be there so that we can at least consistently support the current single-table semantics. Until we have some form of cross-table unique constraint (index or whatever) I managed uniqueness using normal indexes and ins/upd triggers on all child tables: CREATE OR REPLACE FUNCTION checkchildsunique RETURNS trigger AS $BODY$BEGIN IF EXISTS ( SELECT 1 FROM foo Master WHERE Master.primaryKeyCol = NEW.primaryKeyCol) THEN RAISE EXCEPTION 'Primary Key violation in table % on %', TG_RELNAME, TG_OP; END IF; RETURN NEW; END;$BODY$ LANGUAGE 'plpgsql' Shouldn't be too complicated to implement it as internal function. Regards, Andreas ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Type of bare text strings
What type are bare strings considered if they haven't been cast? I'm curious as
to how the first case is of size 5, and how the last case is 301...
decibel=# select pg_column_size('test');
5
decibel=# select pg_column_size('test'::varchar);
8
decibel=# select pg_column_size('test'::text);
8
decibel=# select pg_column_size('test'::name);
64
decibel=# select pg_column_size('test'::char(4));
8
decibel=# select
pg_column_size('123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
301
decibel=#
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] ADD/DROP INHERITS
Greg Stark <[EMAIL PROTECTED]> writes: > Come to think of it it's pretty strange that you can drop an inherited > constraint from a child. And doing an experiment it seems you can also DROP > NOT NULL on a child which is also pretty strange. Yeah. I think we had agreed that this is a bug. Note the TODO entries: o Prevent parent tables from altering or dropping constraints like CHECK that are inherited by child tables unless CASCADE is used o %Prevent child tables from altering or dropping constraints like CHECK that were inherited from the parent table > I don't see how to block these operations though unless we either search > parent classes for constraints to check at run-time or add additional > dependency records to block dropping these things. The implementation I had in mind was to add columns similar to attinhcount and attislocal to pg_constraint. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] drop if exists remainder
On Thu, Jun 08, 2006 at 09:43:19AM -0400, Andrew Dunstan wrote: > OK there does seem to be some demand for this, so I will rework the > patch, and hope to get it done by feature freeze - it has bitrotted > with 7 merge conflicts, including the grammar file, so I need to > look carefully at that. Pity people didn't speak up when this was > first raised. :-) I did :) Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter 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] How to avoid transaction ID wrap
"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > I was recently involved in a project where we had to decrease the > checkpoint_timeout . The problem was, that the database was performing > so many transactions that if we waiting for 5 minutes, checkpoint would > take entirely too long. Seems like the correct fix for that is to make the bgwriter more aggressive. Narrowing the checkpoint spacing is a pretty horrid answer because of the resulting increase in full-page-image WAL traffic. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] ADD/DROP INHERITS
Hannu Krosing <[EMAIL PROTECTED]> writes: > Do you mean that in newer versions ALTER TABLE ADD COLUMN will change > existing data without asking me ? > > That would be evil! > > Even worse if ALTER TABLE ALTER COLUMN SET DEFAULT would do the same. postgres=# alter table test add b integer default 1; ALTER TABLE postgres=# select * from test; a | b ---+--- 0 | 1 (1 row) > > It was awfully annoying for users when that feature was missing. > > Any non-linearities in the user interface like this > > end up being surprises and annoyances for users. > > I would be *really*, *really*, *really* annoyed if an op that I expected > to take less than 1 sec takes 5 hours and then forces me to spend > another 10 hours on VACUUM FULL+REINDEX or CLUSTER to get performance > back. I forget whether the developer managed to get it working without doing any table rewriting. In theory the table just needs to know that records that are "missing" that column in the null bitmap should behave as if they have the default value. But I seem to recall some headaches with that approach. > > In any case there's a separate problem with defaults. We want to guarantee > > that you can DROP a partition and then re-ADD it and the result should be a > > noop at least from the user's perspective. > > If DROP partition keeps defaults, and ADD does not change them then DROP > +ADD is a NOOP. > > > We can't do that unless I compromise on my idea that adding a child after > > the fact should be equivalent to creating it with the parent in the > > definition. It does make DROP+ADD a noop which is why I'm suggesting it. I'm just noting that it makes a second reason why: CREATE TABLE foo (a integer) INHERITS (bar); and: CREATE TABLE foo (a integer); ALTER TABLE foo INHERIT bar; are not equivalent. Since in the first case a will acquire any defaults on a from bar whereas in the second case it will remain with a default of NULL. > constraints should match, that is a child table should already have all > the constraints of parent, but may have more. Well even that is a problem. You can drop an inherited constraint from a child. So this would mean you wouldn't be able to re-add that partition back. Come to think of it it's pretty strange that you can drop an inherited constraint from a child. And doing an experiment it seems you can also DROP NOT NULL on a child which is also pretty strange. I don't see how to block these operations though unless we either search parent classes for constraints to check at run-time or add additional dependency records to block dropping these things. > > We could do a pass-3 check for the NOT NULL constraint but if we're not > > doing > > other schema changes then it makes more sense to just refuse to add such a > > table. > > nono. the ADD/DROP INHERITS should not do any data checking, just > comparison of metainfo. the partitions could be huge and having to check > data inside them would negate most of the usefullness for ADD/DROP > INHERITS. I agree that it's important to be possible to add/drop partitions in constant time. That's the whole advantage of partitioned tables. However it might be reasonable to support *additional* operations that aren't necessary for partitioned tables but make sense for other applications even if these operations are more expensive. But it seems the priority right now is clearly on partitioned tables and these other operations are for another day. -- greg ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] More on inheritance and foreign keys
"Zeugswetter Andreas DCP SD" <[EMAIL PROTECTED]> writes: >> The solution to the foreign key problem seems easy if I >> modify PostgreSQL implementation and take off the ONLY word >> from the SELECT query, but it's not an option for me, as I'm > I think that the ONLY was wrong from day one :-( Well, sure, but until we have an implementation that actually *works* across multiple tables, it has to be there so that we can at least consistently support the current single-table semantics. Until we have some form of cross-table unique constraint (index or whatever) we can't support multi-table foreign keys --- taking off the ONLY is not a fix. > Of course then we would need > REFERENCES tenk ONLY (unique1) > to allow current behavior. When we do have the support I'd be inclined to just change the semantics. I don't think we need to be backward compatible with what everyone agrees is a bug. (Also, your proposal would cover having a non-inheritable referenced table, but what of inheritance on the referencing side?) regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ADD/DROP INHERITS
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Greg Stark wrote: > > > > I can't find any standard api to remove a single specific dependency. It > > seems > > normally dependencies are only removed when dropping objects via > > performDeletion. > > Huh, and can't you just drop an inheritance entry with performDeletion? > Maybe what you should do is add support for that to doDeletion (and all > dependency stuff it seems ...) Well I'm not actually deleting anything. The dependency is between the two tables and I don't want to delete either of the tables. Perhaps what should really be happening here is that there should be dependencies from the pg_inherit entry to the two tables rather than from one table to the other. Then a simple performDeletion on the pg_inherit entry would take care of the dependencies. I'm not sure how many other changes that would entail though. -- greg ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] code cleanup for SearchSysCache
"Qingqing Zhou" <[EMAIL PROTECTED]> writes: > There are roughly 420 calls of SearchSysCache() and 217 of which are just > report "cache lookup failed". Shall we put the elog in the SearchSysCache > itself? You'd need two essentially equivalent versions of SearchSysCache, and you'd lose the ability to make the error message identify what was being searched for, so I vote no. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
