Re: [HACKERS] core dump on 8.1 and no dump on REL8_1_STABLE
initdb -E KOI8-R --locale ru_RU.KOI8-R -D $DIR In HEAD I get HEAD and REL8_1STABLE works fine, 8.1 release not (I don't test REL8_1_0, just take a source package) -- 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] MS SQL Server compatibility functions
Fredrik Olsson wrote: Devrim GUNDUZ wrote: Hi, On Wed, 23 Nov 2005, Fredrik Olsson wrote: snip I guess I am not the only one moving from MS SQL Server, so is there interest for others to use my work, as a contrib perhaps. And how should I continue from here in that case? I'd start a new project at pgfoundry and then would begin talking about a contrib module. Good idea. I have registered mssqlsupport as project name there, and will add all there is as soon as/if the project gets accepted. Said and done, project is up at http://pgfoundry.org/projects/mssqlsupport/ regards -- //Fredrik Olsson Treyst AB +46-19-362182 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] MS SQL Server compatibility functions
Am Mittwoch, 23. November 2005 16:32 schrieb Pollard, Mike: If this gets added as a contrib, here's a version of uniqueidentifier and newid() I wrote that maintains the same format as the SQL Server version: CREATE SCHEMA sqlserver AUTHORIZATION postgres; GRANT ALL ON SCHEMA sqlserver TO public; CREATE SEQUENCE sqlserver.uniqueidentifier0 INCREMENT 1 MINVALUE 0 MAXVALUE 9223372036854775807 START 0 CACHE 1; GRANT ALL ON TABLE sqlserver.uniqueidentifier0 TO public; CREATE SEQUENCE sqlserver.uniqueidentifier1 INCREMENT 1 MINVALUE 0 MAXVALUE 9223372036854775807 START 0 CACHE 1; GRANT ALL ON TABLE sqlserver.uniqueidentifier1 TO public; Why do you use GRANT ALL and not GRANT SELECT, UPDATE? All means everybody can do bad things with those sequences. Regards, Mario Weilguni ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [BUGS] BUG #2052: Federal Agency Tech Hub Refuses to Accept
On Fri, 2005-11-18 at 09:32 -0500, Tom Lane wrote: All known CVE problems are resolved in 8.0.4. I was unaware of this. I've looked at the release notes and searched the archives, but this doesn't seem to be mentioned by CVE number. (The vulnerabilities and their resolutions are described, just without direct cross reference to their CVE number.) Do we have an on-project description of this? If we-as-a-project know this, it seems straightforward to write it down. It seems like we need a much clearer resource for security admins to check our compliance levels. This could be a source of similar refusal-to-implement PostgreSQL at other installations, so could almost be regarded as an advocacy issue. Other software projects have been criticized badly for their security response and info dissemination - I don't believe that applies here, but it does indicate the general requirement and its priority. i.e. don't just fix the bugs, tell everyone you've fixed the bugs. Or, at very least, put stronger security warnings onto the releases. (My own advice is always to watch for announcements and stay current). Thoughts? Best Regards, Simon Riggs Stephen's detailed reply to CVE worries copied below for context: On Fri, 2005-11-18 at 10:08 -0500, Stephen Frost wrote: * Ferindo Middleton ([EMAIL PROTECTED]) wrote: CVE-2005-0245 Buffer overflow in gram.y for PostgreSQL 8.0.0 and earlier may allow attackers to execute arbitrary code via a large number of arguments to a refcursor function (gram.y), which leads to a heap-based buffer overflow, a different vulnerability than CVE-2005-0247. I think this was fixed in 8.0.2... CVE-2005-0244 PostgreSQL 8.0.0 and earlier allows local users to bypass the EXECUTE permission check for functions by using the CREATE AGGREGATE command. This appears to have been fixed in 8.0.1. CVE-2005-0227 PostgreSQL (pgsql) 7.4.x, 7.2.x, and other versions allows local users to load arbitrary shared libraries and execute code via the LOAD extension. The CVE says it only affected pre-8.0 releases and I'm inclined to believe it. CVE-2005-0246 The intagg contrib module for PostgreSQL 8.0.0 and earlier allows attackers to cause a denial of service (crash) via crafted arrays. Contrib modules are only an issue if you install them. If you don't need them, don't install them. Don't know if this was fixed but honestly I expect it was, the Postgres folks don't just sit around on their hands when CVE's come out. CVE-2005-0247 Multiple buffer overflows in gram.y for PostgreSQL 8.0.1 and earlier may allow attackers to execute arbitrary code via (1) a large number of variables in a SQL statement being handled by the read_sql_construct function, (2) a large number of INTO variables in a SELECT statement being handled by the make_select_stmt function, (3) alarge number of arbitrary variables in a SELECT statement being handled by the make_select_stmt function, and (4) a large number of INTO variables in a FETCH statement being handled by the make_fetch_stmt function, a different set of vulnerabilities than CVE-2005-0245. Looks like this was fixed in 8.0.2.. CVE-2005-1409 PostgreSQL 7.3.x through 8.0.x gives public EXECUTE access to certain character conversion functions, which allows unprivileged users to call those functions with malicious values, with unknown impact, aka the Character conversion vulnerability This appears to have been fixed in 8.0.3. CVE-2005-1410 - The tsearch2 module in PostgreSQL 7.4 through 8.0.x declares the (1) dex_init, (2) snb_en_init, (3) snb_ru_init, (4)spell_init, and (5) syn_init functions as internal even when they do not take an internal argument, which allows attackers to cause a denial of service (application crash) and possibly have other impacts via SQL commands that call other functions that accept internal arguments. This appears to have been fixed in 8.0.3. It looks like these were all fixed rather quickly after they were discovered and brought to the attention of the PostgreSQL team. http://www.gsa.gov/networx - Networx Hosting Center - NHC User Instructions, Executive Summary. No software is without bugs. It would be foolish to assume that you can deploy a system once and never have to update it for newly discovered security vulnerabilities. If you'd like a comparison to a product they may be allowing elsewhere you might consider looking at Oracle's track record for fixing security issues. It's rather... poor. There have been a number of articles to this affect on bugtraq recently, you shouldn't have too much trouble finding good examples. Enjoy, Stephen ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] MS SQL Server compatibility functions
Why do you use GRANT ALL and not GRANT SELECT, UPDATE? All means everybody can do bad things with those sequences. GRANT ALL on a sequence IS GRANT SELECT UPDATE. Chris ---(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] [BUGS] BUG #2052: Federal Agency Tech Hub Refuses to Accept
All known CVE problems are resolved in 8.0.4. I was unaware of this. I've looked at the release notes and searched the archives, but this doesn't seem to be mentioned by CVE number. (The vulnerabilities and their resolutions are described, just without direct cross reference to their CVE number.) Do we have an on-project description of this? If we-as-a-project know this, it seems straightforward to write it down. It seems like we need a much clearer resource for security admins to check our compliance levels. This could be a source of similar refusal-to-implement PostgreSQL at other installations, so could almost be regarded as an advocacy issue. Other software projects have been criticized badly for their security response and info dissemination - I don't believe that applies here, but it does indicate the general requirement and its priority. i.e. don't just fix the bugs, tell everyone you've fixed the bugs. Or, at very least, put stronger security warnings onto the releases. (My own advice is always to watch for announcements and stay current). Thoughts? How about a simlpe webpage that has more or less a table with: CVE-number | present in releases | fixed in releases CVE-number | present in releases | fixed in releases CVE-number | present in releases | fixed in releases etc? Perhaps also a link to an advisory of our own? Yeah, looking around a bit, it looks like unless you're on -hackers, it's kinda hard to know. Any reason we don't publish security pulletins to bugtraq for example? //Magnus ---(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] POWER vs. POW ???
How come these give slightly different results? test=# SELECT POW(2,-2); pow -- 0.25 (1 row) test=# SELECT POWER(2,-2); power --- 0.25 (1 row) (Note width of result field.) Chris ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] POWER vs. POW ???
On Nov 24, 2005, at 21:00 , Christopher Kings-Lynne wrote:
How come these give slightly different results?
test=# SELECT POW(2,-2);
pow
--
0.25
(1 row)
test=# SELECT POWER(2,-2);
power
---
0.25
(1 row)
(Note width of result field.)
It appears that the line is extended one underscore beyond the width
of the wider of the attribute name and value. Am I missing something?
test=# create table foo (this_is_a_long_attribute text not null,
short_attr text not null);
CREATE TABLE
test=# insert into foo (this_is_a_long_attribute, short_attr) values
('narrow_value', 'this_is_a_very_wide_value');
INSERT 0 1
test=# select * from foo;
this_is_a_long_attribute |short_attr
--+---
narrow_value | this_is_a_very_wide_value
(1 row)
Michael Glaesemann
grzm myrealbox com
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[HACKERS] TRUNC vs. TRUNCATE
Hi, I notice we added CEILING() as an alias to CEIL() for compatibility. We also have POWER() for POW(). I notice that MySQL uses TRUNCATE() and we only have TRUNC(). Is TRUNCATE actually spec compliant? Should we add TRUNCATE anyway for consistency and compatibility? Chris ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] POWER vs. POW ???
On Thu, 24 Nov 2005 20:00:21 +0800 Christopher Kings-Lynne [EMAIL PROTECTED] wrote: How come these give slightly different results? test=# SELECT POW(2,-2); pow -- 0.25 (1 row) test=# SELECT POWER(2,-2); power --- 0.25 (1 row) (Note width of result field.) The width of the label? Note that the first is controlled by the result. The width of the result is four. Add a padding space on each end and you get six. With the second the label (power vs. pow) is larger than the result so it controls the width. It is five so add a padding space on each end and you get seven, one more than the first. Check these out. test=# SELECT POW(2,-2) AS REALLY_BIG_LABEL; really_big_label -- 0.25 (1 row) test=# SELECT POWER(2,-2) AS X; x -- 0.25 (1 row) -- D'Arcy J.M. Cain darcy@druid.net | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 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] POWER vs. POW ???
Also, POW() is not documented here: http://www.postgresql.org/docs/8.1/interactive/functions-math.html Chris Christopher Kings-Lynne wrote: How come these give slightly different results? test=# SELECT POW(2,-2); pow -- 0.25 (1 row) test=# SELECT POWER(2,-2); power --- 0.25 (1 row) (Note width of result field.) Chris ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] POWER vs. POW ???
It appears that the line is extended one underscore beyond the width of the wider of the attribute name and value. Am I missing something? Ah yes, I'm stupid :P Chris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] NVL vs COALESCE
When we're having an alias discussion, I'd really like to see NVL in postgres. Not because of porting from oracle as much as just spelling that without the reference manual is completely impossible. Best regards, Marcus ---(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] NVL vs COALESCE
On Nov 24, 2005, at 21:21 , Marcus Engene wrote: When we're having an alias discussion, I'd really like to see NVL in postgres. Not because of porting from oracle as much as just spelling that without the reference manual is completely impossible. NVL: what a very unfortunate spelling. (NULL VaLue? NULL Valued Logic? Named Very Loosely? Someone help me here :) ) AFAICT, COALESCE is SQL standard, while NVL isn't. I think an index entry might be a good idea. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] NVL vs COALESCE
If we're going to do that we should add IFNULL() from MySQL as well... Chris Michael Glaesemann wrote: On Nov 24, 2005, at 21:21 , Marcus Engene wrote: When we're having an alias discussion, I'd really like to see NVL in postgres. Not because of porting from oracle as much as just spelling that without the reference manual is completely impossible. NVL: what a very unfortunate spelling. (NULL VaLue? NULL Valued Logic? Named Very Loosely? Someone help me here :) ) AFAICT, COALESCE is SQL standard, while NVL isn't. I think an index entry might be a good idea. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] PL/php in pg_pltemplate
Alvaro Herrera wrote: Is anybody opposed to having PL/php in pg_pltemplate in the 8.1 branch? If not, I will add it on monday. (I plan to add it to 8.2 at the same time.) pg_pltemplate should only be used for languages that are included in the PostgreSQL source tree. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [BUGS] BUG #2052: Federal Agency Tech Hub Refuses to Accept
Simon Riggs wrote: I was unaware of this. I've looked at the release notes and searched the archives, but this doesn't seem to be mentioned by CVE number. (The vulnerabilities and their resolutions are described, just without direct cross reference to their CVE number.) We really should write the CVE numbers into the commit messages and the release notes. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] POWER vs. POW ???
On Thu, Nov 24, 2005 at 08:00:21PM +0800, Christopher Kings-Lynne wrote: How come these give slightly different results? test=# SELECT POW(2,-2); pow -- 0.25 (1 row) test=# SELECT POWER(2,-2); power --- 0.25 (1 row) (Note width of result field.) The result field is the length of min(the string, printable result) + 2: # SELECT POW(2,-2); pow -- 0.25 (1 row) # SELECT POW(2,-2) as power; power --- 0.25 (1 row) # SELECT POW(2,-2) as p; p -- 0.25 (1 row) -- __ Nothing is as subjective as reality Reinoud van Leeuwen[EMAIL PROTECTED] http://www.xs4all.nl/~reinoud __ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [GENERAL] Private email requests
Bruce, list, I translated that part to german, I know the source is strong in Germany - maybe it can be a helpfull addition - even if most speak English, they are even more happy to read sth. in German. Harald Due to time constraints, I do not directly answer general PostgreSQLquestions.For assistance, please join the appropriate mailing list and post your question:http://www.postgresql.org/communityYou can also try the #postgresql IRC channel on irc.freenode.net .Seethe PostgreSQL FAQ for more information. Leider ist es mir wegen Zeitknappheit nicht möglich, persönlich auf PostgreSQL-Fragen zu antworten. Unterstützung findet man durch Mitgliedschaft in der passenden Mailing-Liste; dort sind Fragen willkommen: http://www.postgresql.org/community Eine hilfreiche Ressource ist auch der #postgresql IRC-Channel auf dem Server irc.freenode.net. In der PostgreSQL FAQ gibt es mehr Informationen. Bitte beachten: Das Gros der Kommunikation erfolgt in Englisch. -- GHUM Harald Massapersuasion python postgresqlHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607
Re: [HACKERS] NVL vs COALESCE
When we're having an alias discussion, I'd really like to see NVL in postgres. Not because of porting from oracle as much as just spelling that without the reference manual is completely impossible. Best regards, Marcus You can found NVL in orafunc on pgfoundry. Regards Pavel Stehule _ Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] MS SQL Server compatibility functions
If you're referring to my procedure for newid(), then it was just because of pure laziness; it was an internal proof of concept project, and I was still concentrating on getting it working. Mike Pollard SUPRA Server SQL Engineering and Support Cincom Systems, Inc. Better to remain silent and be thought a fool than to speak out and remove all doubt. Abraham Lincoln -Original Message- From: Christopher Kings-Lynne [mailto:[EMAIL PROTECTED] Sent: Thursday, November 24, 2005 5:57 AM To: Mario Weilguni Cc: pgsql-hackers@postgresql.org; Pollard, Mike; Bill Bartlett; Fredrik Olsson Subject: Re: [HACKERS] MS SQL Server compatibility functions Why do you use GRANT ALL and not GRANT SELECT, UPDATE? All means everybody can do bad things with those sequences. GRANT ALL on a sequence IS GRANT SELECT UPDATE. Chris ---(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] PL/php in pg_pltemplate
Peter Eisentraut [EMAIL PROTECTED] writes: Alvaro Herrera wrote: Is anybody opposed to having PL/php in pg_pltemplate in the 8.1 branch? If not, I will add it on monday. (I plan to add it to 8.2 at the same time.) pg_pltemplate should only be used for languages that are included in the PostgreSQL source tree. I don't see any strong reason for enforcing that as policy, if the language maintainer wants an entry. (But is Alvaro the maintainer of pl/php?) My recollection is that we identified some pros and cons of having listings for non-core languages, and decided it should be up to the language maintainers to decide what they want. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [BUGS] BUG #2052: Federal Agency Tech Hub Refuses to Accept
On Thu, 2005-11-24 at 15:09 +0100, Peter Eisentraut wrote: We really should write the CVE numbers into the commit messages and the release notes. I think that would be good. On Thu, 2005-11-24 at 12:35 +0100, Magnus Hagander wrote: All known CVE problems are resolved in 8.0.4. I was unaware of this. I've looked at the release notes and searched the archives, but this doesn't seem to be mentioned by CVE number. (The vulnerabilities and their resolutions are described, just without direct cross reference to their CVE number.) Do we have an on-project description of this? If we-as-a-project know this, it seems straightforward to write it down. It seems like we need a much clearer resource for security admins to check our compliance levels. This could be a source of similar refusal-to-implement PostgreSQL at other installations, so could almost be regarded as an advocacy issue. How about a simple webpage that has more or less a table with: CVE-number | present in releases | fixed in releases CVE-number | present in releases | fixed in releases CVE-number | present in releases | fixed in releases ..and I think we should do this too. Have to say I'm a bit worried about overloading Tom and Bruce, who write most of the security patches and relevant release notes. Anybody else volunteer to maintain the web page? Best Regards, Simon Riggs ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] someone working to add merge?
On 11/24/2005 1:30 AM, Martijn van Oosterhout wrote: On Wed, Nov 23, 2005 at 04:55:25PM -0500, Jan Wieck wrote: The largest problem I see with MERGE is the question of BEFORE triggers. Consider a BEFORE INSERT trigger that modifies a third table, after which the constraint or whatever post-heap_insert-attempt we might use detects a conflict. How do we undo the actions of the BEFORE trigger? The only way to do that is to plan the query as a nestloop, with the USING part as the outer loop. If the (updating) scan of the INTO relation did not hit any tuple, then do the INSERT. We can only undo the side effects of any BEFORE trigger by wrapping each and evey nested INTO relation insert attempt into its own subtransaction. Umm, if there are any errors you abort the transaction, just like any other case. ACID requires that either the whole statement is done, or none. If a trigger causes the INSERT or UPDATE to fail you have no choice but to abort the transaction. I guess you misunderstood. What I am talking about is a problem in the order of execution. since we don't have predicate locking, there is a possibility that our implementation of MERGE decides to do an INSERT while another transaction does the same. What has to happen is that the BEFORE INSERT trigger is called, then the heap tuple inserted, then the index tuples created. At this time, the duplicate key error occurs, telling us that we had a conflict and that we have to try an UPDATE instead. That means, in the end this particular row's INSERT has never happened and we have to undo the BEFORE INSERT triggers actions too. Besides, someone posted an example on Oracle, they don't require an index so I don't think we realistically can say that people need one. If two concurrent MERGEs, which can't see eachothers output, both end up INSERTing, that not an error unless the user has a UNIQUE constraint, so the problem vanishes. Not following the semantics is an error. MERGE is not supposed to do multiple inserts for the same match, concurrency or not. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [BUGS] BUG #2052: Federal Agency Tech Hub Refuses to Accept
Simon Riggs said: On Thu, 2005-11-24 at 15:09 +0100, Peter Eisentraut wrote: We really should write the CVE numbers into the commit messages and the release notes. I think that would be good. A security page on the web site that summarised the info would be good too. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [BUGS] BUG #2052: Federal Agency Tech Hub Refuses to Accept
Andrew Dunstan [EMAIL PROTECTED] writes: On Thu, 2005-11-24 at 15:09 +0100, Peter Eisentraut wrote: We really should write the CVE numbers into the commit messages and the release notes. A security page on the web site that summarised the info would be good too. Not to mention a lot easier to create after-the-fact ... 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] PL/php in pg_pltemplate
Tom Lane wrote: I don't see any strong reason for enforcing that as policy, if the language maintainer wants an entry. (But is Alvaro the maintainer of pl/php?) My recollection is that we identified some pros and cons of having listings for non-core languages, and decided it should be up to the language maintainers to decide what they want. Perhaps Alvaro can identify the reasons why he wants this done and then we can determine whether it's a good idea. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [BUGS] BUG #2052: Federal Agency Tech Hub Refuses to Accept
On Thursday 24 November 2005 06:09, Peter Eisentraut wrote: Simon Riggs wrote: I was unaware of this. I've looked at the release notes and searched the archives, but this doesn't seem to be mentioned by CVE number. (The vulnerabilities and their resolutions are described, just without direct cross reference to their CVE number.) We really should write the CVE numbers into the commit messages and the release notes. I also belive that we should have these referenced visably on the website much the same way apache does: http://httpd.apache.org/security_report.html -- Darcy Buskermolen Wavefire Technologies Corp. http://www.wavefire.com ph: 250.717.0200 fx: 250.763.1759 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] gprof SELECT COUNT(*) results
I did some gprof on a simple SELECT COUNT(*) FROM test query on cvs tip. Linux josh.db 2.4.29-1 #2 Tue Jan 25 17:03:33 EST 2005 i686 unknown gcc: 2.96 gprof: 2.13.90.0.2 ./configure --without-readline There are 260k or so records in table test(i int), about 1500 pages. I give a shared_buffers to 3000, which is enough to hold all data pages. Other GUCs are by default. After some warmups (to make sure these pages are in the file system buffers), I do SELECT COUNT(*) for 10 times of each round, and I tested 3 rounds. The results are: - Round 1 - % cumulative self self total time seconds secondscalls s/call s/call name 16.67 0.27 0.27 2648542 0.00 0.00 LWLockAcquire 13.58 0.49 0.22 2648543 0.00 0.00 LWLockRelease 8.02 0.62 0.13 5266128 0.00 0.00 LockBuffer 8.02 0.75 0.13 2621456 0.00 0.00 heapgettup - Round 2 - % cumulative self self total time seconds secondscalls s/call s/call name 19.14 0.31 0.31 2648542 0.00 0.00 LWLockAcquire 13.58 0.53 0.22 2648543 0.00 0.00 LWLockRelease 11.11 0.71 0.18 2621456 0.00 0.00 heapgettup 6.79 0.82 0.11 5266128 0.00 0.00 LockBuffer - Round 3 - % cumulative self self total time seconds secondscalls s/call s/call name 17.12 0.25 0.25 2648542 0.00 0.00 LWLockAcquire 8.22 0.37 0.12 2648543 0.00 0.00 LWLockRelease 7.53 0.48 0.11 2621456 0.00 0.00 heapgettup 6.85 0.58 0.10 2621440 0.00 0.00 ExecEvalConst There are some variance in the results, so my question is: (1) Are these results faithful? (2) If so, does it indicate that LWLock needs some improvements? Regards, Qingqing ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] gprof SELECT COUNT(*) results
On Thu, 2005-11-24 at 13:25 -0500, Qingqing Zhou wrote: I did some gprof on a simple SELECT COUNT(*) FROM test query on cvs tip. Linux josh.db 2.4.29-1 #2 Tue Jan 25 17:03:33 EST 2005 i686 unknown gcc: 2.96 gprof: 2.13.90.0.2 ./configure --without-readline There are 260k or so records in table test(i int), about 1500 pages. I give a shared_buffers to 3000, which is enough to hold all data pages. Other GUCs are by default. After some warmups (to make sure these pages are in the file system buffers), I do SELECT COUNT(*) for 10 times of each round, and I tested 3 rounds. The results are: - Round 1 - % cumulative self self total time seconds secondscalls s/call s/call name 16.67 0.27 0.27 2648542 0.00 0.00 LWLockAcquire 13.58 0.49 0.22 2648543 0.00 0.00 LWLockRelease 8.02 0.62 0.13 5266128 0.00 0.00 LockBuffer 8.02 0.75 0.13 2621456 0.00 0.00 heapgettup - Round 2 - % cumulative self self total time seconds secondscalls s/call s/call name 19.14 0.31 0.31 2648542 0.00 0.00 LWLockAcquire 13.58 0.53 0.22 2648543 0.00 0.00 LWLockRelease 11.11 0.71 0.18 2621456 0.00 0.00 heapgettup 6.79 0.82 0.11 5266128 0.00 0.00 LockBuffer - Round 3 - % cumulative self self total time seconds secondscalls s/call s/call name 17.12 0.25 0.25 2648542 0.00 0.00 LWLockAcquire 8.22 0.37 0.12 2648543 0.00 0.00 LWLockRelease 7.53 0.48 0.11 2621456 0.00 0.00 heapgettup 6.85 0.58 0.10 2621440 0.00 0.00 ExecEvalConst There are some variance in the results, so my question is: (1) Are these results faithful? (2) If so, does it indicate that LWLock needs some improvements? Maybe, maybe not. The whole system is designed around high levels of concurrent access. If you know for certain you don't ever need that then other systems are probably the right choice. Concurrency has a cost and a benefit. If you measure the cost, but not the benefit, it will seem expensive. Your results show you have 2.6m rows, not 260k rows. Yes? Best Regards, Simon Riggs ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] gprof SELECT COUNT(*) results
On Thu, 24 Nov 2005, Simon Riggs wrote: Maybe, maybe not. The whole system is designed around high levels of concurrent access. If you know for certain you don't ever need that then other systems are probably the right choice. Concurrency has a cost and a benefit. If you measure the cost, but not the benefit, it will seem expensive. Yeah, understood. What I can't understand that in this case why it costs so much -- without concurrency, the LWLock code path just invloves spinlock_lock/unlock and serveral simple instructions? What's more, we can see that for each row, a LWLock pair is invoked. So on a more aggressive thought, can we change it to page level? I know it is terriblly difficult since our query processor infrastructure is based on a single-tuple interface ... Your results show you have 2.6m rows, not 260k rows. Yes? It is 260k since I test each round by 10 SELECT COUNT(*). Regards, Qingqing ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] NULL safe equality operator
Hi guys, Does anyone know how I'd go about implementing the following MySQL operator in PostgreSQL? --- NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand isNULL. mysql SELECT 1 = 1, NULL = NULL, 1 = NULL; - 1, 1, 0 mysql SELECT 1 = 1, NULL = NULL, 1 = NULL; - 1, NULL, NULL --- Chris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] NULL safe equality operator
On Nov 25, 2005, at 11:17 , Christopher Kings-Lynne wrote: Hi guys, Does anyone know how I'd go about implementing the following MySQL operator in PostgreSQL? I'm sure you know how to implement this with a stored procedure. AFAICT, if you wanted to actually implement this as an operator, you'd need to write C procedures for each datatype to make it an operator. Is that something you're looking at doing? Michael Glaesemann grzm myrealbox com create or replace function null_safe_cmp (integer, integer) returns int immutable language sql as $$ select case when $1 is null and $2 is null then 1 when ($1 is null and $2 is not null) or ($1 is not null and $2 is null) then 0 else case when $1 = $2 then 1 else 0 end end; $$; test=# select null_safe_cmp(1,1); null_safe_cmp --- 1 (1 row) test=# select null_safe_cmp(0,1); null_safe_cmp --- 0 (1 row) test=# select null_safe_cmp(1,0); null_safe_cmp --- 0 (1 row) test=# select null_safe_cmp(NULL,1); null_safe_cmp --- 0 (1 row) test=# select null_safe_cmp(1,NULL); null_safe_cmp --- 0 (1 row) test=# select null_safe_cmp(NULL,NULL); null_safe_cmp --- 1 (1 row) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] NULL safe equality operator
Christopher Kings-Lynne [EMAIL PROTECTED] writes: Does anyone know how I'd go about implementing the following MySQL operator in PostgreSQL? NOT (x IS DISTINCT FROM y) would be the standard-compliant way of spelling that. 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] gprof SELECT COUNT(*) results
Qingqing Zhou [EMAIL PROTECTED] writes: Yeah, understood. What I can't understand that in this case why it costs so much -- without concurrency, the LWLock code path just invloves spinlock_lock/unlock and serveral simple instructions? You executed LWLock 2.6 million times in just under 300ms. If my math is right that's about 115 nanoseconds per lock or about 300 cycles on a 2.6Ghz processor. That sounds like a lot but it's about the right order of magnitude. Was this on a multiprocessor machine? In which case a big part of that time is probably spent synchronizing between the processors. -- 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: [HACKERS] gprof SELECT COUNT(*) results
On Thu, 24 Nov 2005, Greg Stark wrote: You executed LWLock 2.6 million times in just under 300ms. If my math is right that's about 115 nanoseconds per lock or about 300 cycles on a 2.6Ghz processor. That sounds like a lot but it's about the right order of magnitude. Was this on a multiprocessor machine? In which case a big part of that time is probably spent synchronizing between the processors. Your math is right iff my math is right :-) It is a 2.4G desktop computer. I may need to write some separate tests to see if this is what we should pay for bus lock instruction. Regards, Qingqing ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] gprof SELECT COUNT(*) results
Qingqing Zhou [EMAIL PROTECTED] writes: Yeah, understood. What I can't understand that in this case why it costs so much -- without concurrency, the LWLock code path just invloves spinlock_lock/unlock and serveral simple instructions? I don't see those costing nearly as much as your results show ... perhaps there's something platform-specific at work? What I see, down to the 1% level, is Each sample counts as 0.01 seconds. % cumulative self self total time seconds secondscalls ms/call ms/call name 37.98 13.9113.91 _mcount 6.53 16.30 2.39 5242900 0.00 0.00 heapgettup 3.33 17.52 1.22 10542596 0.00 0.00 LockBuffer 3.30 18.73 1.21 5242880 0.00 0.00 advance_transition_function 2.68 19.71 0.98 5242880 0.00 0.00 IncrBufferRefCount 2.46 20.61 0.90 5385174 0.00 0.00 LWLockRelease 2.38 21.48 0.87 5271273 0.00 0.00 ReleaseAndReadBuffer 2.35 22.34 0.86 5385174 0.00 0.00 LWLockAcquire 2.18 23.14 0.80 5242938 0.00 0.00 ReleaseBuffer 2.10 23.91 0.77 5242900 0.00 0.00 ExecStoreTuple 1.97 24.63 0.72 noshlibs 1.91 25.33 0.70 5242900 0.00 0.00 SeqNext 1.72 25.96 0.63 5271294 0.00 0.00 ResourceOwnerRememberBuffer 1.72 26.59 0.63 5242900 0.00 0.00 heap_getnext 1.72 27.22 0.63 5242880 0.00 0.00 advance_aggregates 1.69 27.84 0.62 5242940 0.00 0.00 ExecProcNode 1.64 28.44 0.60 $$dyncall 1.61 29.03 0.59 5242900 0.00 0.00 MemoryContextReset 1.53 29.59 0.56 5242880 0.00 0.00 HeapTupleSatisfiesSnapshot 1.45 30.12 0.53 5242880 0.00 0.00 int8inc 1.37 30.62 0.50 5243140 0.00 0.00 ExecClearTuple 1.17 31.05 0.43 5242880 0.00 0.00 ExecEvalExprSwitchContext 1.15 31.47 0.42 5271294 0.00 0.00 ResourceOwnerForgetBuffer 1.12 31.88 0.41 SeqNext 1.09 32.28 0.40 5271294 0.00 0.00 ResourceOwnerEnlargeBuffers 1.09 32.68 0.40 5242900 0.00 0.00 ExecScan 1.04 33.06 0.38 5242900 0.00 0.00 ExecSeqScan (This is for 20, not 10, iterations of your example, but otherwise it's the same test case.) I've since gotten rid of the IncrBufferRefCount, ReleaseBuffer, and ResourceOwnerXXX entries by eliminating some inefficiency in ExecStoreTuple, so that puts the buffer lock stuff further up, but it's still not all that critical by my numbers. What's more, we can see that for each row, a LWLock pair is invoked. So on a more aggressive thought, can we change it to page level? Yeah, I was wondering the same. It'd be possible to rewrite the seqscan stuff so that we do the visibility tests for all the tuples on a given page at once, taking the buffer content lock just once, and saving aside the valid tuple IDs to return later. This should definitely be faster when all the tuples actually get fetched. It might be a bit slower for a LIMIT query, but I'm not sure if we care that much. The only other objection I can think of is that if there are any broken tuples on a page, this approach would likely make it impossible to fetch any of the non-broken ones :-( regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] gprof SELECT COUNT(*) results
On Thu, 24 Nov 2005, Qingqing Zhou wrote:
I may need to write some separate tests to see if this is what we should
pay for bus lock instruction.
Here I come up with a test program to see how spinlock costs:
$/pgsql/src/backend/storage/lmgr#./a.out
Spinlock pair(2648542) duration: 143.134 ms
$/pgsql/src/backend/storage/lmgr#./a.out
Spinlock pair(2648542) duration: 143.107 ms
$/pgsql/src/backend/storage/lmgr#./a.out
Spinlock pair(2648542) duration: 143.104 ms
So seems lock instruction really costs ...
Regards,
Qingqing
---
/*
* spintest.c -
* Test spinlock acquire/release without concurrency.
*
* To compile (the -pg is to match the gprof make I used):
* backend/storage/lmgr#gcc -O2 -pg -Wall -I ../../../include/
spintest.c
*/
#include postgres.h
#include storage/lwlock.h
#include storage/spin.h
#include sys/time.h
#define TIMES 2648542
int NumLocks = 0;
void
s_lock(volatile slock_t *lock, const char *file, int line)
{
fprintf(stderr, should never be here\n);
abort();
}
int
main(void)
{
int i;
slock_t lock = 0;
struct timeval start_t, stop_t;
long usecs;
gettimeofday(start_t, NULL);
for (i = 0; i TIMES; i ++)
{
SpinLockAcquire_NoHoldoff(lock);
/* pretend to do something */
NumLocks ++;
SpinLockRelease_NoHoldoff(lock);
}
gettimeofday(stop_t, NULL);
if (stop_t.tv_usec start_t.tv_usec)
{
stop_t.tv_sec--;
stop_t.tv_usec += 100;
}
usecs = (long) (stop_t.tv_sec - start_t.tv_sec) * 100
+ (long) (stop_t.tv_usec - start_t.tv_usec);
fprintf (stdout, Spinlock pair(%u) duration: %ld.%03ld ms\n,
TIMES,
(long) ((stop_t.tv_sec - start_t.tv_sec) * 1000 +
(stop_t.tv_usec - start_t.tv_usec) / 1000),
(long) (stop_t.tv_usec - start_t.tv_usec) % 1000);
return 0;
}
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] PL/php in pg_pltemplate
Le Jeudi 24 Novembre 2005 18:07, Peter Eisentraut a écrit : Tom Lane wrote: I don't see any strong reason for enforcing that as policy, if the language maintainer wants an entry. (But is Alvaro the maintainer of pl/php?) My recollection is that we identified some pros and cons of having listings for non-core languages, and decided it should be up to the language maintainers to decide what they want. Perhaps Alvaro can identify the reasons why he wants this done and then we can determine whether it's a good idea. Hi, I am not a postgresql expert but there is one thing I think important about adding language into core postgresql: each time you'll add a new features needing external libraries into core package, this mean the guy building postgresql should have devellopment files for of the library installed at postgresql compil time. For exemple, on pgfoundry you can found pgrpm (rpm binding for postgres), this kind of thing have nothing to do into postgresql core package IMHO, because to be built it need rpm to be installed, most of system doesn't have rpm. It is same things for language, is it a good to have perl, tcl, python, php, ruby, lua, basic, lisp (add other crazy idea here :) provided by postgresql itself ? I don't know what language I will use tomorrow, but I know I am already using postgres. Wouldn't be better, like for pgrpm, to provide this kind of things as external project/plugins that can be build and installed later ? But you surelly have a better point of view of this than me. BTW, In mandriva, since postgresql 8.0, there is one rpm per language to avoid dependencies flow, so you need to install language lib only if you install PL/language .so files. My 2 cents, hope this help. pgpcbwVxXDzJs.pgp Description: PGP signature
Re: [HACKERS] NULL safe equality operator
On Thu, 24 Nov 2005, Tom Lane wrote: NOT (x IS DISTINCT FROM y) would be the standard-compliant way of spelling that. That's the sql99 way. In sql2003 (but not in pg) one can also do X IS NOT DISTINCT FROM y -- /Dennis Björklund ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] gprof SELECT COUNT(*) results
On Thu, 24 Nov 2005, Tom Lane wrote: I don't see those costing nearly as much as your results show ... perhaps there's something platform-specific at work? What I see, down to the 1% level, is I can see your computer is really slow, so my theory is that since it is easy to hold a running-slowly horse than a fast one, so my spinlock on a 2.4G modern machine should takes relatively longer time to get effective. Just kidding. I am not sure what's happened, but in previous email there is a program I wrote to test the spinlock performance. In my machine, the profiling results matches the single spinlock test. The only other objection I can think of is that if there are any broken tuples on a page, this approach would likely make it impossible to fetch any of the non-broken ones :-( What do you mean by broken tuple? An data corrupted tuple? So you mean if scan operator find a broken tuple on a page, then it will abort the operation without returning any other good tuples? I think this is acceptable. Regards, Qingqing ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] NULL safe equality operator
when ($1 is null and $2 is not null) or ($1 is not null and $2 is null) then 0 That's the same as: when $1 is null != $2 is null then 0 Chris ---(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] NULL safe equality operator
On Nov 25, 2005, at 16:24 , Christopher Kings-Lynne wrote: when ($1 is null and $2 is not null) or ($1 is not null and $2 is null) then 0 That's the same as: when $1 is null != $2 is null then 0 Yeah, I saw your commit. Nice shortcut. Also didn't know you could define operators using SQL functions. Tom's suggestion of NOT (a DISTINCT FROM b) is really cool. Much cleaner in my opinion. I learn a lot from these lists :) Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] NULL safe equality operator
Yeah, I saw your commit. Nice shortcut. Also didn't know you could define operators using SQL functions. Tom's suggestion of NOT (a DISTINCT FROM b) is really cool. Much cleaner in my opinion. I learn a lot from these lists :) Needs to return 0 or 1 though. Chris ---(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] NULL safe equality operator
On Nov 25, 2005, at 16:37 , Michael Glaesemann wrote: Tom's suggestion of NOT (a DISTINCT FROM b) is really cool. Even cooler if I spell it correctly: NOT (a IS DISTINCT FROM b) Michael Glaesemann grzm myrealbox 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] NULL safe equality operator
On Nov 25, 2005, at 16:43 , Christopher Kings-Lynne wrote: Yeah, I saw your commit. Nice shortcut. Also didn't know you could define operators using SQL functions. Tom's suggestion of NOT (a DISTINCT FROM b) is really cool. Much cleaner in my opinion. I learn a lot from these lists :) Needs to return 0 or 1 though. CREATE OR REPLACE FUNCTION null_safe_cmp (ANYELEMENT, ANYELEMENT) RETURNS INTEGER IMMUTABLE LANGUAGE SQL AS $$ SELECT CASE WHEN NOT ($1 IS DISTINCT FROM $2) THEN 1 ELSE 0 END; $$; select null_safe_cmp (1,1) as (1,1) , null_safe_cmp (1,0) as (1,0) , null_safe_cmp (1,NULL) as (1,NULL) , null_safe_cmp (NULL,1) as (NULL,1) , null_safe_cmp (NULL::integer,NULL::integer) as (NULL,NULL); (1,1) | (1,0) | (1,NULL) | (NULL,1) | (NULL,NULL) ---+---+--+--+- 1 | 0 |0 |0 | 1 (1 row) test=# select null_safe_cmp (NULL,NULL); ERROR: could not determine anyarray/anyelement type because input has type unknown test=# select null_safe_cmp (NULL::integer,NULL::integer); null_safe_cmp --- 1 (1 row) Same casting problem due to anyelement, of course. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
