Re: [HACKERS] Unresolved Win32 bug reports
Bruce Momjian said: > Folks, my mailbox is filling with unresolved Win32 bug reports, > specifically: > > integer division > shared memory > statistics collector > rename > fsync > > I have put the emails at the bottom of the patches_hold queue: > > http://momjian.postgresql.org/cgi-bin/pgpatches_hold > There's also a pg_config buglet that David Fetter found that still needs to be fixed. I am currently travelling on family business, but when I return home in a couple of weeks will be working on getting my new machine built, and installing a permanent Windows VM (among others), which will make it easier for me to look at Windows issues within my realm of competence. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Google SoC--Idea Request
Christopher Kings-Lynne wrote: I think Martin Oosterhout's nearby email on coverity bug reports might make a good SoC project, but should it also be added to the TODO list? I may as well put up phpPgAdmin for it. We have plenty of projects available in phpPgAdmin... Same with pgAdmin3. Regards, Andreas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_dump -Ft failed on Windows XP
> >> Apparently it won't work at all if TMP isn't set?
>
> > I'm not *too* concerned about that, since TMP is normally set by the
OS
> > itself. There's one set in the "system environment" (to
c:\windows\temp
> > or whatrever) and then it's overridden by one set by the OS when it
> > loads a user profile.
>
> OK, then maybe not having it would be equivalent to /tmp-not-writable
> on Unix, ie, admin error.
>
> > Also to the point, what would you fall back to?
>
> Current directory maybe?
It tries \ (tested on Win 2000), if the dir argument is NULL and TMP is
not set.
But TMP is usually set.
Attached is a working version not yet adapted to port/.
- memoryleak fixed
- use _tmpname and _fdopen not the compatibility tmpname and fdopen
(imho only cosmetic)
- EACCES fixed (Win2000 needs _S_IREAD | _S_IWRITE or fails with EACCES,
even as Admin)
- I suggest adding a prefix pg_temp_ (for leftover temp files after
crash,
the name I get is then usually pg_temp_2)
Andreas
Index: bin/pg_dump/pg_backup_tar.c
===
RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_backup_tar.c,v
retrieving revision 1.50
diff -c -r1.50 pg_backup_tar.c
*** bin/pg_dump/pg_backup_tar.c 12 Feb 2006 06:11:50 - 1.50
--- bin/pg_dump/pg_backup_tar.c 21 Apr 2006 09:22:00 -
***
*** 362,368
--- 362,388
{
tm = calloc(1, sizeof(TAR_MEMBER));
+ #ifndef WIN32
tm->tmpFH = tmpfile();
+ #else
+ /* on win32, tmpfile() generates a filename in the root
directory, which requires
+* administrative permissions to write to. */
+ while (1)
+ {
+ char *tmpname;
+ int fd;
+
+ tmpname = _tempnam(NULL, "pg_temp_");
+ if (tmpname == NULL)
+ break;
+ fd = _open(tmpname, _O_RDWR | _O_CREAT | _O_EXCL |
_O_BINARY | _O_TEMPORARY, _S_IREAD | _S_IWRITE);
+ free(tmpname);
+ if (fd == -1 && errno == EEXIST)
+ continue; /* Try again with a new name if file
exists */
+ if (fd != -1)
+ tm->tmpFH = _fdopen(fd, "w+b");
+ break;
+ }
+ #endif
if (tm->tmpFH == NULL)
die_horribly(AH, modulename, "could not generate
temporary file name: %s\n", strerror(errno));
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[HACKERS] TODO item question [pg_hba.conf]
Hi, As advised, I spend a moment reading the code regarding the GRANT and REVOKE In order to add a new privilege to the ACL, I have created a mini patch. Could this be checked to see if I am on the right track? http://www.xs4all.nl/~gevik/patch/alpha.patch Thank you. ---(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] Checking assumptions
On Fri, Apr 21, 2006 at 09:12:51AM +0800, Christopher Kings-Lynne wrote: > >I havn't been able to find any more serious issues in the Coverity > >report, now that they've fixed the ereport() issue. A number of the > >issues it complains about are things we already Assert() for. For the > >rest, as long as the following assumptions are true we're done (well, > >except for ECPG). I think they are true but it's always good to check: > > Everytime someone does this, we fix everything except ECPG. Surely it's > time we fixed ECPG as well? I've got a patch (not by me) that should fix most of the issues. However, we have no way to test for regressions. So, that's why I suggested (elsewhere) someone get the ECPG regression stuff working so we can apply fixes and check they don't break anything... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [HACKERS] TODO item question [pg_hba.conf]
Gevik Babakhani wrote: > As advised, I spend a moment reading the code regarding the GRANT and REVOKE > In order to add a new privilege to the ACL, I have created a mini patch. > Could this be checked to see if I am on the right track? > > http://www.xs4all.nl/~gevik/patch/alpha.patch You are missing an ACL_*_CHR symbol and updating the ACL_ALL_RIGHTS_STR symbol. Also, you should know that changing this requires a change in CATALOG_VERSION_NO in catversion.h as well. -- 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] TODO item question [pg_hba.conf]
Thank you :)
> You are missing an ACL_*_CHR symbol and updating the ACL_ALL_RIGHTS_STR
> symbol.
That is why I could not see the new permission in pg_database.
I was actually looking for that for sometime :)
I have added the ACL_*_CHR 'D' Is this okay?
> Also, you should know that changing this requires a change in
> CATALOG_VERSION_NO in catversion.h as well.
Why is this needed? Is this a functional requirement?
I have changed it to
#define CATALOG_VERSION_NO200604211
Is this okay?
Regards,
Gevik.
gevik=# create role user1;
CREATE ROLE
gevik=# grant connection on database db2 to user1;
GRANT
gevik=# select datname,datacl from pg_catalog.pg_database;
datname | datacl
---+--
postgres |
db1 | {=T/gevik,gevik=CTD/gevik}
template1 | {gevik=CTD/gevik}
template0 | {gevik=CTD/gevik}
gevik | {=T/gevik,gevik=CTD/gevik}
db2 | {=T/gevik,gevik=CTD/gevik,user1=D/gevik}
(6 rows)
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] Checking assumptions
Martijn van Oosterhout wrote: -- Start of PGP signed section. > On Fri, Apr 21, 2006 at 09:12:51AM +0800, Christopher Kings-Lynne wrote: > > >I havn't been able to find any more serious issues in the Coverity > > >report, now that they've fixed the ereport() issue. A number of the > > >issues it complains about are things we already Assert() for. For the > > >rest, as long as the following assumptions are true we're done (well, > > >except for ECPG). I think they are true but it's always good to check: > > > > Everytime someone does this, we fix everything except ECPG. Surely it's > > time we fixed ECPG as well? > > I've got a patch (not by me) that should fix most of the issues. > However, we have no way to test for regressions. So, that's why I > suggested (elsewhere) someone get the ECPG regression stuff working so > we can apply fixes and check they don't break anything... Well, we should wait a reasonable time for Michael to review the changes, but if not, we should just move ahead and do our best to fix ecpg ourselves. -- 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 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] TODO item question [pg_hba.conf]
Gevik Babakhani wrote: > Thank you :) > > > You are missing an ACL_*_CHR symbol and updating the ACL_ALL_RIGHTS_STR > > symbol. > > That is why I could not see the new permission in pg_database. > I was actually looking for that for sometime :) > > I have added the ACL_*_CHR 'D' Is this okay? Hum, you literally added a symbol ACL_*_CHR? I was actually thinking in ACL_CONNECT_CHR or something like that ... While at it, why D? Isn't 'c' more natural? (And conveniently unused.) > > Also, you should know that changing this requires a change in > > CATALOG_VERSION_NO in catversion.h as well. > > Why is this needed? Is this a functional requirement? To force an initdb, because you are causing a system catalog change. Now that I think about it, maybe it's not needed, because the default state of the system should be the same as if no privilege has changed. OTOH you need to speficy the interpretation of the initial state of the ACL for a database. I think it should mean that PUBLIC has the CONNECT privilege. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Further reduction of bufmgr lock contention
I've been looking into Gavin Hamill's recent report of poor performance with PG 8.1 on an 8-way IBM PPC64 box. strace'ing backends shows a lot of semop() calls, indicating blocking at the LWLock or lmgr-lock levels, but not a lot of select() delays, suggesting we don't have too much of a problem at the hardware spinlock level. A typical breakdown of different kernel call types is 566 _llseek 10 brk 10 gettimeofday 4 mmap 4 munmap 562 read 4 recv 8 select 3014 semop 12 send 1 time 3 write (I'm hoping to get some oprofile results to confirm there's nothing strange going on at the hardware level, but no luck yet on getting oprofile to work on Debian/PPC64 ... anyone know anything about suitable kernels to use for that?) Instrumenting LWLockAcquire (with a patch I had developed last fall, but just now got around to cleaning up and committing to CVS) shows that the contention is practically all for the BufMappingLock: $ grep ^PID postmaster.log | sort +9nr | head -20 PID 23820 lwlock 0: shacq 2446470 exacq 6154 blk 12755 PID 23823 lwlock 0: shacq 2387597 exacq 4297 blk 9255 PID 23824 lwlock 0: shacq 1678694 exacq 4433 blk 8692 PID 23826 lwlock 0: shacq 1221221 exacq 3224 blk 5893 PID 23821 lwlock 0: shacq 1892453 exacq 1665 blk 5766 PID 23835 lwlock 0: shacq 2390685 exacq 1453 blk 5511 PID 23822 lwlock 0: shacq 1669419 exacq 1615 blk 4926 PID 23830 lwlock 0: shacq 1039468 exacq 1248 blk 2946 PID 23832 lwlock 0: shacq 698622 exacq 397 blk 1818 PID 23836 lwlock 0: shacq 544472 exacq 530 blk 1300 PID 23839 lwlock 0: shacq 497505 exacq 46 blk 885 PID 23842 lwlock 0: shacq 305281 exacq 1 blk 720 PID 23840 lwlock 0: shacq 317554 exacq 226 blk 355 PID 23840 lwlock 2: shacq 0 exacq 2872 blk 7 PID 23835 lwlock 2: shacq 0 exacq 3434 blk 6 PID 23835 lwlock 1: shacq 0 exacq 1452 blk 4 PID 23822 lwlock 1: shacq 0 exacq 1614 blk 3 PID 23820 lwlock 2: shacq 0 exacq 3582 blk 2 PID 23821 lwlock 1: shacq 0 exacq 1664 blk 2 PID 23830 lwlock 1: shacq 0 exacq 1247 blk 2 These numbers show that our rewrite of the bufmgr has done a great job of cutting down the amount of potential contention --- most of the traffic on this lock is shared rather than exclusive acquisitions --- but it seems that if you have enough CPUs it's still not good enough. (My best theory as to why Gavin is seeing better performance from a dual Opteron is simply that 2 processors will have 1/4th as much contention as 8 processors.) I have an idea about how to improve matters: I think we could break the buffer tag to buffer mapping hashtable into multiple partitions based on some hash value of the buffer tags, and protect each partition under a separate LWLock, similar to what we did with the lmgr lock table not long ago. Anyone have a comment on this strategy, or a better idea? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] TODO item question [pg_hba.conf]
"Gevik Babakhani" <[EMAIL PROTECTED]> writes: > I have added the ACL_*_CHR 'D' Is this okay? That seems an excessively random choice of character for CONNECT privilege. I see that 'C' is already taken, but we could use 'c'. >> Also, you should know that changing this requires a change in >> CATALOG_VERSION_NO in catversion.h as well. > Why is this needed? Is this a functional requirement? It's just something we do to avoid bogus bug reports from people who haven't initdb'd after something that requires a catalog change. In this case, since the system would stop working (or at least stop letting you connect) if you hadn't initdb'd, I think a catversion bump is indicated. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Further reduction of bufmgr lock contention
On 4/21/06, Tom Lane <[EMAIL PROTECTED]> wrote: > I've been looking into Gavin Hamill's recent report of poor performance > with PG 8.1 on an 8-way IBM PPC64 box. We have recently encountered some odd performance with 8.2dev on a 16-way Opteron. In the next few days we'll look into it and see if it may be related. Otherwise, it sounds good to me; a trial of the new strategy should definitely prove its value one way or the other. -- Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation 732.331.1324 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Google SoC--Idea Request
On Fri, Apr 21, 2006 at 10:27:48AM +0200, Andreas Pflug wrote: > Christopher Kings-Lynne wrote: > >>I think Martin Oosterhout's nearby email on coverity bug reports might > >>make a good SoC project, but should it also be added to the TODO list? > > > > > >I may as well put up phpPgAdmin for it. We have plenty of projects > >available in phpPgAdmin... > > Same with pgAdmin3. Is there a list of specific projects? I'm pretty sure we can't just say "work on (pgp)PgAdmin... -- 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] Google SoC--Idea Request
Robert and I are working on updating it ASAP. On 4/21/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > On Fri, Apr 21, 2006 at 10:27:48AM +0200, Andreas Pflug wrote: > > Christopher Kings-Lynne wrote: > > >>I think Martin Oosterhout's nearby email on coverity bug reports might > > >>make a good SoC project, but should it also be added to the TODO list? > > > > > > > > >I may as well put up phpPgAdmin for it. We have plenty of projects > > >available in phpPgAdmin... > > > > Same with pgAdmin3. > > Is there a list of specific projects? I'm pretty sure we can't just say > "work on (pgp)PgAdmin... > -- > 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 > -- Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation 732.331.1324 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] TODO item pg_hba.conf
Added to TODO: o %Allow per-database permissions to be set via GRANT Allow database connection checks based on GRANT rules in addition to the existing access checks in pg_hba.conf. and remove: o %Allow pg_hba.conf settings to be controlled via SQL This would add a function to load the SQL table from pg_hba.conf, and one to writes its contents to the flat file. The table should have a line number that is a float so rows can be inserted between existing rows, e.g. row 2.5 goes between row 2 and row 3. --- Gevik Babakhani wrote: > Hi, > > I read the discussion thread once again and unless I am absolutely > and totally on the wrong track this is what I understood from the > general plan to be. The current pg_hba.conf provides the famous > the host based mechanism to connect to a database. > In order to add the discussed functionality we want to hold > the CONNECT permission information inside a table in > the database (something like pg_connect). > > The parser has to be changed in order to understand the new grant > and revoke and of course the appropriate backend commands have to > be developed to store/check/remove the new privilege. > > The SQL command could be something like this: > > REVOKE CONNECT ON DATABASE foo FROM PUBLIC; > GRANT CONNECT ON DATABASE foo TO user1, user2, user3; > > There are some other important details but I will discuss them later. > > Would it be correct to state that: only the authentication > is checked (username and password) when connecting to the > server and not the any kind of privilege to access a database. > Please see postmaster.c:2753 Which brings us to the real > work to be done as suggested by Tom > in postinit.c:143 ReverifyMyDatabase(const char *name). > > Please advice. > Gevik. > > > > ---(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 > -- 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 5: don't forget to increase your free space map settings
Re: [HACKERS] Suggestion: Which Binary?
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- David Wheeler wrote: > On Apr 3, 2006, at 14:37, Tom Lane wrote: > > > I would suggest handling this strictly as an addition to our > > installation.sgml docs. > > Finally got 'round to this. Patch attached. There are quite a few > environment variables in the list that Peter sent to me that I know > nothing about. These I've listed, but the documentation for them is > full of "??"s. You can either fill them in, leave them out, or tell > me where to learn what they mean and I'll resubmit the patch. > > And by the way, Tom, I really appreciate the time you take to answer > my questions and point me to where I can create a patch to help the > project. It's people like you who create really successful open- > source projects, just by being so responsive and helpful. Now that's > leadership! > > Best, > > David > [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend -- 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] Further reduction of bufmgr lock contention
On Fri, 2006-04-21 at 13:01 -0400, Tom Lane wrote: > I've been looking into Gavin Hamill's recent report of poor performance > with PG 8.1 on an 8-way IBM PPC64 box. Ah good. > Instrumenting LWLockAcquire (with a patch I had developed last fall, > but just now got around to cleaning up and committing to CVS) shows > that the contention is practically all for the BufMappingLock: > $ grep ^PID postmaster.log | sort +9nr | head -20 > PID 23820 lwlock 0: shacq 2446470 exacq 6154 blk 12755 > PID 23823 lwlock 0: shacq 2387597 exacq 4297 blk 9255 > PID 23824 lwlock 0: shacq 1678694 exacq 4433 blk 8692 > PID 23826 lwlock 0: shacq 1221221 exacq 3224 blk 5893 BufMappingLock contention can be made worse by a poorly tuned bgwriter or if the cache hit rate is low. Perhaps in this case, increasing shared_buffers (again) might be enough to further reduce the contention? When we discussed this before http://archives.postgresql.org/pgsql-hackers/2005-02/msg00702.php ISTM then that a low shared_buffers cache hit rate combined with a high OS cache hit rate will cause high contention in an SMP environment. > These numbers show that our rewrite of the bufmgr has done a great job > of cutting down the amount of potential contention --- most of the > traffic on this lock is shared rather than exclusive acquisitions --- > but it seems that if you have enough CPUs it's still not good enough. > (My best theory as to why Gavin is seeing better performance from a > dual Opteron is simply that 2 processors will have 1/4th as much > contention as 8 processors.) Jonah mentions some 16-way CPU testing we have just begun. There are some interesting effects to decode, but most importantly all the CPUs do stay at 100% for much of the time (when other tuning has been done). So my feeling is that the BufMappingLock contention seen by Gavin is much worse than we see. (...and I had been thinking to investigate further with him on that point, though have just arrived back in UK). Another difference is the amount of read/write. My understanding is that Gavin's workload is mostly read-only which will greatly increase the buffer request rate since backends will spend proportionally more time consuming data and less time in xlog (etc). My understanding is that contention increases geometrically with number of potential lock holders (i.e. CPUs). > I have an idea about how to improve matters: I think we could break the > buffer tag to buffer mapping hashtable into multiple partitions based on > some hash value of the buffer tags, and protect each partition under a > separate LWLock, similar to what we did with the lmgr lock table not > long ago. Anyone have a comment on this strategy, or a better idea? I think this is the right way to go http://archives.postgresql.org/pgsql-hackers/2005-02/msg00240.php though the work for 8.1 was right to have been performed first. The earlier lmgr lock partitioning had a hard-coded number of partitions, which was sensible because of the reduced likelihood of effectiveness beyond a certain number of partitions. That doesn't follow here since the BufMappingLock contention will vary with the size of shared_buffers and with the number of CPUs in use (for a given workload). I'd like to see the partitioning calculated at server startup either directly from shared_buffers or via a parameter. We may not be restricted to only using a hash function as we were with lmgr, perhaps using a simple range partitioning. Test-wise: May be able to trial something next week, though system access not yet confirmed and I'm not sure we'll see an improvement on the workload we're testing on currently. I'll have a think about a pure test that we can run on both systems to measure the contention. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] plperl on AIX
I posted this in other lists with no response... Can anyone help? I'm having trouble getting plperl to work on AIX 5.3.2. Postgresql Version: 8.1.1 Perl Version: 5.8.7 I've rebuilt perl as a shared library version and built that into postgre using --with-perl. The postgre build/install works fine, without errors. The plperl regression tests all fail, though. Here are the test results of the first regression test: -- -- checkpoint so that if we have a crash in the tests, replay of the -- just-completed CREATE DATABASE won't discard the core dump file -- checkpoint; -- -- Test result value processing -- CREATE OR REPLACE FUNCTION perl_int(int) RETURNS INTEGER AS $$ return undef; $$ LANGUAGE plperl; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. connection to server was lost I've tried this same thing manually with the same result. Does anyone have any suggestions on how to get this to work? Thanks
Re: [HACKERS] Further reduction of bufmgr lock contention
Simon Riggs <[EMAIL PROTECTED]> writes: > On Fri, 2006-04-21 at 13:01 -0400, Tom Lane wrote: >> I've been looking into Gavin Hamill's recent report of poor performance >> with PG 8.1 on an 8-way IBM PPC64 box. > BufMappingLock contention can be made worse by a poorly tuned bgwriter > or if the cache hit rate is low. Perhaps in this case, increasing > shared_buffers (again) might be enough to further reduce the contention? Well, the cache hit rate is evidently pretty high, since so few of the BufMappingLock accesses are exclusive (any miss would result in an exclusive access). I did try increasing shared_buffers (from 4 to 8) but it didn't change performance noticeably. > Another difference is the amount of read/write. My understanding is that > Gavin's workload is mostly read-only which will greatly increase the > buffer request rate since backends will spend proportionally more time > consuming data and less time in xlog (etc). I believe the particular test case being looked at here is read-only (Gavin, is that correct?) > The earlier lmgr lock partitioning had a hard-coded number of > partitions, which was sensible because of the reduced likelihood of > effectiveness beyond a certain number of partitions. That doesn't follow > here since the BufMappingLock contention will vary with the size of > shared_buffers and with the number of CPUs in use (for a given > workload). I'd like to see the partitioning calculated at server startup > either directly from shared_buffers or via a parameter. We may not be > restricted to only using a hash function as we were with lmgr, perhaps > using a simple range partitioning. I don't think any of that follows; and a large number of partitions is risky because it increases the probability of exhausting shared memory (due to transient variations in the actual size of the hashtables for different partitions). > Test-wise: May be able to trial something next week, though system > access not yet confirmed and I'm not sure we'll see an improvement on > the workload we're testing on currently. I'll have a think about a pure > test that we can run on both systems to measure the contention. Keep in mind that Gavin's 8-way turns back into a pumpkin on Monday :-( 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] Google SoC--Idea Request
On Friday 21 April 2006 14:11, Jim C. Nasby wrote:
> On Fri, Apr 21, 2006 at 10:27:48AM +0200, Andreas Pflug wrote:
> > Christopher Kings-Lynne wrote:
> > >>I think Martin Oosterhout's nearby email on coverity bug reports might
> > >>make a good SoC project, but should it also be added to the TODO list?
> > >
> > >I may as well put up phpPgAdmin for it. We have plenty of projects
> > >available in phpPgAdmin...
> >
> > Same with pgAdmin3.
>
> Is there a list of specific projects? I'm pretty sure we can't just say
> "work on (pgp)PgAdmin...
http://www.postgresql.org/developer/summerofcode
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(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] plperl on AIX
John F Rizzo <[EMAIL PROTECTED]> writes: > I'm having trouble getting plperl to work on AIX 5.3.2. You need to gather more info. What shows up in the postmaster log when the backend crashes? Also, get a debugger stack trace from the core file the backend leaves behind. (If it doesn't leave a core file then the first thing to do is fix that --- on Linux "ulimit -c unlimited" before starting the postmaster helps, but I'm not too familiar with AIX.) 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] Suggestion: Which Binary?
On Apr 21, 2006, at 13:54, Bruce Momjian wrote: Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. Cool, thanks Bruce. Best, David ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Google SoC--Idea Request
On Fri, Apr 21, 2006 at 05:48:33PM -0400, Robert Treat wrote: > On Friday 21 April 2006 14:11, Jim C. Nasby wrote: > > On Fri, Apr 21, 2006 at 10:27:48AM +0200, Andreas Pflug wrote: > > > Christopher Kings-Lynne wrote: > > > >>I think Martin Oosterhout's nearby email on coverity bug reports might > > > >>make a good SoC project, but should it also be added to the TODO list? > > > > > > > >I may as well put up phpPgAdmin for it. We have plenty of projects > > > >available in phpPgAdmin... > > > > > > Same with pgAdmin3. > > > > Is there a list of specific projects? I'm pretty sure we can't just say > > "work on (pgp)PgAdmin... > > http://www.postgresql.org/developer/summerofcode Want to replace Many TODO ItemsA number of the items on our TODO list have been marked as good projects for beginners whos are new to the PostgreSQL code. Items on this list have the advantage of already having general community agreement that the feature is desireable. These items should also have some general discussion available in the mailing list archives to help get you started. You can find these items on the http://wwwmaster.postgresql.org/docs/faqs.TODO.html";>TODO list, they will be marked with apercent sign (%). with Many TODO Items: A number of the items on our TODO list have been marked as good projects for beginners who are new to the PostgreSQL code. Items on this list have the advantage of already having general community agreement that the feature is desireable. These items should also have some general discussion available in the mailing list archives to help get you started. You can find these items on the http://wwwmaster.postgresql.org/docs/faqs.TODO.html";>TODO list, they will be marked with apercent sign (%). ? -- 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] Simplifying Param lookups
Another thing I noticed while looking at Gavin Hamill's test case is that according to gprof, it's spending a remarkably large fraction of its time in lookupParam(): Each sample counts as 0.01 seconds. % cumulative self self total time seconds secondscalls s/call s/call name 13.87 20.2820.28 3219733 0.00 0.00 lookupParam 11.20 36.6516.37 6128411 0.00 0.00 LWLockAcquire 8.86 49.6012.95 6128574 0.00 0.00 LWLockRelease 5.73 57.97 8.37 12654786 0.00 0.00 _bt_compare 5.60 66.15 8.18 2746677 0.00 0.00 PinBuffer 5.53 74.24 8.09 669262 0.00 0.00 s_lock 5.17 81.80 7.56 1380848 0.00 0.00 slot_deform_tuple 3.72 87.24 5.44 2750944 0.00 0.00 UnpinBuffer 3.27 92.02 4.78 2772808 0.00 0.00 hash_search 2.23 95.28 3.26 16960980 0.00 0.00 FunctionCall2 I don't recall ever seeing this function high in a profile before, but in a complex function it's not so implausible as all that. lookupParam works by linear search, which means that accessing N different parameters will take O(N^2) time. AFAICS the only reason for a linear search is that the params.c code still has vestigial support for named rather than numbered Params. That's been dead code since the system left Berkeley, and I don't know of anything on the horizon that would make us want to revive it. (In places where we'd support named params, it'd make more sense to reduce the names to numbers before runtime anyway.) So I'm thinking about simplifying the ParamListInfo data structure down to a straight array indexed directly by parameter number. Anyone have a problem with that? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Simplifying Param lookups
Tom Lane wrote: > Another thing I noticed while looking at Gavin Hamill's test case is > that according to gprof, it's spending a remarkably large fraction of > its time in lookupParam(): > > Each sample counts as 0.01 seconds. > % cumulative self self total > time seconds secondscalls s/call s/call name > 13.87 20.2820.28 3219733 0.00 0.00 lookupParam > 11.20 36.6516.37 6128411 0.00 0.00 LWLockAcquire > 8.86 49.6012.95 6128574 0.00 0.00 LWLockRelease > 5.73 57.97 8.37 12654786 0.00 0.00 _bt_compare > 5.60 66.15 8.18 2746677 0.00 0.00 PinBuffer > 5.53 74.24 8.09 669262 0.00 0.00 s_lock > 5.17 81.80 7.56 1380848 0.00 0.00 slot_deform_tuple > 3.72 87.24 5.44 2750944 0.00 0.00 UnpinBuffer > 3.27 92.02 4.78 2772808 0.00 0.00 hash_search > 2.23 95.28 3.26 16960980 0.00 0.00 FunctionCall2 > > I don't recall ever seeing this function high in a profile before, but > in a complex function it's not so implausible as all that. lookupParam > works by linear search, which means that accessing N different > parameters will take O(N^2) time. > > AFAICS the only reason for a linear search is that the params.c code > still has vestigial support for named rather than numbered Params. > That's been dead code since the system left Berkeley, and I don't know > of anything on the horizon that would make us want to revive it. > (In places where we'd support named params, it'd make more sense to > reduce the names to numbers before runtime anyway.) > > So I'm thinking about simplifying the ParamListInfo data structure > down to a straight array indexed directly by parameter number. > Anyone have a problem with that? No problem, sounds smart. -- 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] Google SoC--Idea Request
Jim C. Nasby wrote: Same with pgAdmin3. Is there a list of specific projects? I'm pretty sure we can't just say "work on (pgp)PgAdmin... Our TODO list has some. Regards, Andreas ---(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] TODO item question [pg_hba.conf]
Hi, I have created a new patch. Please check to see if I am on the right track. 1) The GRANT and REVOKE statements look like: GRANT CONNECTION ON DATABASE db1 TO user1 (,user2,user3) REVOKE CONNECTION ON DATABASE db1 TO user1 (,user2,user3) 2) The file parsenodes.h is updated to support #define ACL_DATABASE_CONNECT 3) The file acl.h is updated to support #define ACL_DATABASE_CONNECT_CHR 'c' 4) Functions "string_to_privilege" and "privilege_to_string" in aclchk.c are updated to support ACL_DATABASE_CONNECT 5) Function "aclparse" in acl.c is updated to support ACL_DATABASE_CONNECT 6) Catalog version number is updated to CATALOG_VERSION_NO200604211 7) File postinit.c method "ReverifyMyDatabase" is updated by following: First we check to make sure we are not in bootstrap processing mode. If not, we check to see if the connected user has ACL_DATABASE_CONNECT. If not, ereport(FATAL,.) (Perhaps we should change the error message later) 8) File dbcommands.c method "createdb" is updated by following: When a new database is being created we add a default ACL by calling acldefault(ACL_OBJECT_DATABASE, and adding the default ACL by new_record[Anum_pg_database_datacl - 1] = PointerGetDatum(defaultAcl); This would mean, every time a new database gets created the owner of the database gets the ACL_OBJECT_DATABASE privilege and can login. Other users not having the privilege to that database get an error message. Because the catalog version is changed a pg_dump is necessarily, means all the new roles created from that point will get the ACL_OBJECT_DATABASE and everything should be "backward-compatible" At this moment the owner of the database CAN REVOKE himself form the ACL_OBJECT_DATABASE. If the implementation above is acceptable then I can work on this one :) http://www.xs4all.nl/~gevik/patch/patch-0.1.diff Did I forget something? Please advice. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Further reduction of bufmgr lock contention
On Fri, 2006-04-21 at 17:38 -0400, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > The earlier lmgr lock partitioning had a hard-coded number of > > partitions, which was sensible because of the reduced likelihood of > > effectiveness beyond a certain number of partitions. That doesn't follow > > here since the BufMappingLock contention will vary with the size of > > shared_buffers and with the number of CPUs in use (for a given > > workload). I'd like to see the partitioning calculated at server startup > > either directly from shared_buffers or via a parameter. We may not be > > restricted to only using a hash function as we were with lmgr, perhaps > > using a simple range partitioning. > > I don't think any of that follows; and a large number of partitions is > risky because it increases the probability of exhausting shared memory > (due to transient variations in the actual size of the hashtables for > different partitions). lmgr partitioning uses either 4 or 16, restricted by the hash function, for various reasons. I see no similar restriction on using a hash function here - we could equally well use range partitioning. That relieves the restriction on the number of partitions, allowing us either more or less partitions, according to need. We can place a limit on that if you see a problem - at what level do you see a problem? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Win32 sysconfig -> pg_service.conf
Andrew Dunstan wrote: > > > David Fetter wrote: > > >>>doesn't report anything by way of --sysconfdir, which in turn means > >>>that people have to do some fragile hackery in order even to see a > >>>pg_service.conf file. Can we put such a configuration directive > >>>into the binary builds? Is this known to work? > >>> > >>> > >>In any case, the default is $prefix/etc which is probably not what > >>you want anyway - why not set the PGSYSCONFDIR environment variable > >>to point to where you put the service file? > >> > >> > > > >Let's turn that question around. Why *shouldn't* there be a default > >built in? "No default" seems like a pretty poor fall-through. > > > > > > > > > > On further investigation, this appears to be an artifact of the > directory not existing, causing GetShortPathName to return an empty > string, as noted in this comment: > > * This can fail in 2 ways - if the path doesn't exist, or short names are > * disabled. In the first case, don't return any path. > > I think maybe we need a pg_config switch to allow us to fall back to > GetFullPathName, which does not fail if the target doesn't exist. After > all, it's cold comfort that libpq probably does the right thing if we > don't have any reasonable way of finding out what that is. > > In the case of Windows binary packages, the place that actually works is > apparently $bindir/../etc > > thoughts? In looking at cleanup_path(), why don't we just return the original string if GetShortPathName() doesn't return anything? -- 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 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] proposal - plpgsql: execute using into
Pavel Stehule wrote: > > > There are some problems about replacing string values in the SQL string. > > > >Doesn't the Oracle implementation already imply a solution to that? > > > > I don't know. I didn't find any detail documentation about it. I don't know > what Oracle exactly do. > > >I think we'd be best off to leave EXECUTE alone, at least until we've > >converged to the point where almost nobody is using non-standard-compliant > >strings. > > > > Maybe, but patch have to solve SQL string and non SQL strings too Pavel, I am still confused what the USING clause is for, and you need to research how Oracle handles it before we can add this. Would you provide an example of its usage? -- 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] Further reduction of bufmgr lock contention
Simon Riggs <[EMAIL PROTECTED]> writes: > lmgr partitioning uses either 4 or 16, restricted by the hash function, > for various reasons. I see no similar restriction on using a hash > function here - we could equally well use range partitioning. I don't really see any difference at all between the two cases as far as what hashing we use. The thing that's nagging at me at the moment is the realization that a partitioned hashtable will eat more shared memory than a single hashtable. It wasn't that long ago that we had to do some hacking to ensure that the buffer hashtable couldn't run out of memory after startup, and I'm afraid of re-introducing that failure mode. The lock manager can run out of memory without crashing the system, but the bufmgr can't (or at least could not in the recent past...) Now that we're considering using partitioning methods for both the buffer and lock hashtables, I wonder if there is any value in teaching dynahash.c itself about concurrent access --- that is, find a way to use a single shared hashtable instead of separate hashtables for the different partitions, by having the hashtable itself deal with concurrency to some extent. This is just handwaving at the moment... 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: [PATCHES] [HACKERS] Automatically setting work_mem
Where are we on this patch? --- Simon Riggs wrote: > On Tue, 2006-03-21 at 17:47 -0500, Tom Lane wrote: > > > I'm fairly unconvinced about Simon's underlying premise --- that we > > can't make good use of work_mem in sorting after the run building phase > > --- anyway. > > We can make good use of memory, but there does come a point in final > merging where too much is of no further benefit. That point seems to be > at about 256 blocks per tape; patch enclosed for testing. (256 blocks > per tape roughly doubles performance over 32 blocks at that stage). > > That is never the case during run building - more is always better. > > > If we cut back our memory usage > Simon inserts the words: "too far" > > then we'll be forcing a > > significantly more-random access pattern to the temp file(s) during > > merging, because we won't be able to pre-read as much at a time. > > Yes, thats right. > > If we have 512MB of memory that gives us enough for 2000 tapes, yet the > initial runs might only build a few runs. There's just no way that all > 512MB of memory is needed to optimise the performance of reading in a > few tapes at time of final merge. > > I'm suggesting we always keep 2MB per active tape, or the full > allocation, whichever is lower. In the above example that could release > over 500MB of memory, which more importantly can be reused by subsequent > sorts if/when they occur. > > > Enclose two patches: > 1. mergebuffers.patch allows measurement of the effects of different > merge buffer sizes, current default=32 > > 2. reassign2.patch which implements the two kinds of resource > deallocation/reassignment proposed. > > Best Regards, Simon Riggs > [ Attachment, skipping... ] [ Attachment, skipping... ] > > ---(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 -- 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 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
