Re: [PATCHES] Changes for AIX buildfarm
Attached is a patch for HEAD to implement Peter's suggestion to set the SHLIB_LINK in the contrib modules that need it. Also, this changes Makefile.aix to use SHLIB_LINK instead of LIBS so that the changes to the contrib Makefiles are picked up correctly. Further this makes it match more closely the link rules from Makefile.shlib. -rocco -Original Message- From: Peter Eisentraut [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 20, 2005 2:12 PM To: Rocco Altier Cc: pgsql-patches@postgresql.org; Tom Lane; Andrew Dunstan; Zeugswetter Andreas DAZ SD Subject: Re: [PATCHES] Changes for AIX buildfarm Tom Lane wrote: Fair enough. But I'm concerned about the proposed patch because it seems to revert a deliberate change made some time ago: http://developer.postgresql.org/cvsweb.cgi/pgs ql/src/Makefile.shlib#r ev1.65 http://archives.postgresql.org/pgsql-committers/2002-10/msg00054.php so I think we need more eyeballs on the problem before deciding this is a good fix. A potentially more correct solution would be to set SHLIB_LINK in the makefiles of the affected modules only. Certainly we don't want to link with all libraries all the time. -- Peter Eisentraut http://developer.postgresql.org/~petere/ aix.contrib.patch Description: aix.contrib.patch ---(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: [PATCHES] Roles - SET ROLE Updated
Stephen Frost [EMAIL PROTECTED] writes: Here's a much better version of the SET ROLE work. I'm reasonably happy with it. The only parts I don't like are that I had to do some ugly things in gram.y to avoid making NONE reserved, and I can't seem to see how to avoid having ROLE be reserved (I understand it was reserved in SQL99 but not in SQL2003...). Updated yet again, fixing a bug in the prior one that caused it to not work properly, and some additional things: I don't think this patch works; it certainly doesn't do what I'd expect to happen with SECURITY DEFINER functions. At the very least you'd need to make fmgr_security_definer save/restore the current role setting. But I doubt that this is even the direction we want to head in. After rereading SQL99 4.31, I don't think there is any need to distinguish CURRENT_USER from CURRENT_ROLE, mainly because our implementation does not distinguish users from roles at all. (Which I think is good.) So ISTM we should not change GetUserId() as you propose, but leave it alone and implement SetRole approximately like SetSessionUserId is implemented, ie, setting a background value that may sometimes get copied into CurrentUserId. The stack aspect only matters to the extent that SetRoleId has precedence over SetSessionUserId for determining the outside-a-transaction value of CurrentUserId. 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] Roles - SET ROLE Updated
Stephen Frost [EMAIL PROTECTED] writes: * Tom Lane ([EMAIL PROTECTED]) wrote: After rereading SQL99 4.31, I don't think there is any need to distinguish CURRENT_USER from CURRENT_ROLE, mainly because our implementation does not distinguish users from roles at all. CURRENT_USER and CURRENT_ROLE can have different values, as I understand SQL2003, and there are places where one is used instead of the other It's possible for CURRENT_ROLE to be null according to the spec; if you like we could implement that as returning what the current outer-level SET ROLE value is (which would then make it semantically more like SESSION_USER than CURRENT_USER). I don't think CURRENT_USER should ever be allowed to be null, or to be different from the active authorization identifier, first because it's silly and second because it will break existing applications that depend on CURRENT_USER for authorization checking. Given that we don't really distinguish users and roles, I would be inclined to make the same argument for CURRENT_ROLE too, leaving SHOW ROLE (and its function equivalent) as the only way to see what you SET ROLE to. But it's less likely to break existing apps if we don't. (such as with the 'grantor' in grants, according to SQL2003 the 'grantor' should be the CURRENT_USER, regardless of if CURRENT_ROLE is set or not). Exactly. CURRENT_USER has to be the active authorization identifier. Do you want me to rework the patch along these lines or are you already working on it? I'm working on it ... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Roles - SET ROLE Updated
BTW, I realized we do not support granting roles to PUBLIC: regression=# create role r; CREATE ROLE regression=# grant r to public; ERROR: role public does not exist but as far as I can tell SQL99 expects this to work. 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] Roles - SET ROLE Updated
* Tom Lane ([EMAIL PROTECTED]) wrote: BTW, I realized we do not support granting roles to PUBLIC: regression=# create role r; CREATE ROLE regression=# grant r to public; ERROR: role public does not exist but as far as I can tell SQL99 expects this to work. Indeed, I believe you're correct, sorry about missing that. Stephen signature.asc Description: Digital signature
Re: [PATCHES] Roles - SET ROLE Updated
Stephen Frost [EMAIL PROTECTED] writes: * Tom Lane ([EMAIL PROTECTED]) wrote: BTW, I realized we do not support granting roles to PUBLIC: regression=# create role r; CREATE ROLE regression=# grant r to public; ERROR: role public does not exist but as far as I can tell SQL99 expects this to work. Indeed, I believe you're correct, sorry about missing that. However, on second thought I'm not sure that this is sensible anyway. Consider that every role is implicitly a member of PUBLIC --- so isn't the above a creation of a circular membership loop, which is (for good reason) forbidden by the spec? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Roles - SET ROLE Updated
Another issue: I like the has_role() function and in fact think it needs to come in multiple variants just like has_table_privilege and friends: has_role(name, name) has_role(name, oid) has_role(oid, name) has_role(oid, oid) has_role(name) -- implicitly has_role(current_user, ...) has_role(oid) However I'm a bit dubious about whether has_role isn't an invasion of application namespace. pg_has_role would be better, but we have the (mis) precedent of has_table_privilege. What do you think about calling it has_role_privilege? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] Roles - SET ROLE Updated
* Tom Lane ([EMAIL PROTECTED]) wrote: Another issue: I like the has_role() function and in fact think it needs to come in multiple variants just like has_table_privilege and friends: has_role(name, name) has_role(name, oid) has_role(oid, name) has_role(oid, oid) has_role(name) -- implicitly has_role(current_user, ...) has_role(oid) However I'm a bit dubious about whether has_role isn't an invasion of application namespace. pg_has_role would be better, but we have the (mis) precedent of has_table_privilege. What do you think about calling it has_role_privilege? I thought about that originally. It seemed a bit long to me and I felt that having the 'privilege' of a role wasn't quite the same as having a 'role', but honestly I'm not terribly picky and on reflection a role *is* like other objects in the catalog (I originally hadn't considered it such), so, that's fine with me... has_role() was another reason I was thinking about having a seperate function for 'is_member_of_role' which didn't pollute the cache, just a side-note. Thanks, Stephen signature.asc Description: Digital signature
Re: [PATCHES] COPY FROM performance improvements
I just ran through a few tests with the v14 patch against 100GB of data from dbt3 and found a 30% improvement; 3.6 hours vs 5.3 hours. Just to give a few details, I only loaded data and started a COPY in parallel for each the data files: http://www.testing.osdl.org/projects/dbt3testing/results/fast_copy/ Here's a visual of my disk layout, for those familiar with the database schema: http://www.testing.osdl.org/projects/dbt3testing/results/fast_copy/layout-dev4-010-dbt3.html I have 6 arrays of fourteen 15k rpm drives in a split-bus configuration attached to a 4-way itanium2 via 6 compaq smartarray pci-x controllers. Let me know if you have any questions. Mark ---(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] COPY FROM performance improvements
Cool! At what rate does your disk setup write sequential data, e.g.: time dd if=/dev/zero of=bigfile bs=8k count=50 (sized for 2x RAM on a system with 2GB) BTW - the Compaq smartarray controllers are pretty broken on Linux from a performance standpoint in our experience. We've had disastrously bad results from the SmartArray 5i and 6 controllers on kernels from 2.4 - 2.6.10, on the order of 20MB/s. For comparison, the results on our dual opteron with a single LSI SCSI controller with software RAID0 on a 2.6.10 kernel: [EMAIL PROTECTED] dbfast]$ time dd if=/dev/zero of=bigfile bs=8k count=50 50+0 records in 50+0 records out real0m24.702s user0m0.077s sys 0m8.794s Which calculates out to about 161MB/s. - Luke On 7/21/05 2:55 PM, Mark Wong [EMAIL PROTECTED] wrote: I just ran through a few tests with the v14 patch against 100GB of data from dbt3 and found a 30% improvement; 3.6 hours vs 5.3 hours. Just to give a few details, I only loaded data and started a COPY in parallel for each the data files: http://www.testing.osdl.org/projects/dbt3testing/results/fast_copy/ Here's a visual of my disk layout, for those familiar with the database schema: http://www.testing.osdl.org/projects/dbt3testing/results/fast_copy/layout-dev4 -010-dbt3.html I have 6 arrays of fourteen 15k rpm drives in a split-bus configuration attached to a 4-way itanium2 via 6 compaq smartarray pci-x controllers. Let me know if you have any questions. Mark ---(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] COPY FROM performance improvements
Luke Lonergan wrote: Cool! At what rate does your disk setup write sequential data, e.g.: time dd if=/dev/zero of=bigfile bs=8k count=50 (sized for 2x RAM on a system with 2GB) BTW - the Compaq smartarray controllers are pretty broken on Linux from a performance standpoint in our experience. We've had disastrously bad results from the SmartArray 5i and 6 controllers on kernels from 2.4 - 2.6.10, on the order of 20MB/s. O.k. this strikes me as interesting, now we know that Compaq and Dell are borked for Linux. Is there a name brand server (read Enterprise) that actually does provide reasonable performance? For comparison, the results on our dual opteron with a single LSI SCSI controller with software RAID0 on a 2.6.10 kernel: [EMAIL PROTECTED] dbfast]$ time dd if=/dev/zero of=bigfile bs=8k count=50 50+0 records in 50+0 records out real0m24.702s user0m0.077s sys 0m8.794s Which calculates out to about 161MB/s. - Luke On 7/21/05 2:55 PM, Mark Wong [EMAIL PROTECTED] wrote: I just ran through a few tests with the v14 patch against 100GB of data from dbt3 and found a 30% improvement; 3.6 hours vs 5.3 hours. Just to give a few details, I only loaded data and started a COPY in parallel for each the data files: http://www.testing.osdl.org/projects/dbt3testing/results/fast_copy/ Here's a visual of my disk layout, for those familiar with the database schema: http://www.testing.osdl.org/projects/dbt3testing/results/fast_copy/layout-dev4 -010-dbt3.html I have 6 arrays of fourteen 15k rpm drives in a split-bus configuration attached to a 4-way itanium2 via 6 compaq smartarray pci-x controllers. Let me know if you have any questions. Mark ---(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 -- Your PostgreSQL solutions provider, Command Prompt, Inc. 24x7 support - 1.800.492.2240, programming, and consulting Home of PostgreSQL Replicator, plPHP, plPerlNG and pgPHPToolkit http://www.commandprompt.com / http://www.postgresql.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] COPY FROM performance improvements
Joshua, On 7/21/05 5:08 PM, Joshua D. Drake [EMAIL PROTECTED] wrote: O.k. this strikes me as interesting, now we know that Compaq and Dell are borked for Linux. Is there a name brand server (read Enterprise) that actually does provide reasonable performance? I think late model Dell (post the bad chipset problem, circa 2001-2?) and IBM and Sun servers are fine because they all use simple SCSI adapters from LSI or Adaptec. The HP Smartarray is an aberration, they don't have good driver support for Linux and as a consequence have some pretty bad problems with both performance and stability. On Windows they perform quite well. Also - there are very big issues with some SATA controllers and Linux we've seen, particularly the Silicon Image, Highpoint other non-Intel controllers. Not sure about Nvidia, but the only ones I trust now are 3Ware and the others mentioned in earlier posts. - Luke ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] COPY FROM performance improvements
Joshua, On 7/21/05 7:53 PM, Joshua D. Drake [EMAIL PROTECTED] wrote: Well I know that isn't true at least not with ANY of the Dells my customers have purchased in the last 18 months. They are still really, really slow. That's too bad, can you cite some model numbers? SCSI? I have great success with Silicon Image as long as I am running them with Linux software RAID. The LSI controllers are also really nice. That's good to hear, I gave up on Silicon Image controllers on Linux about 1 year ago, which kernel are you using with success? Silicon Image controllers are the most popular, so it's important to see them supported well, though I'd rather see more SATA headers than 2 off of the built-in chipsets. - Luke ---(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
