[HACKERS] Please help update the "How to beta Test" page
Folks, Please help update the wiki page around how to beta test. Particularly, please update it with particular things we'd like to see users test for, like data corruption related to freezing (with some notes on how to test for this). https://wiki.postgresql.org/wiki/HowToBetaTest Thanks! -- -- Josh Berkus Red Hat OSAS (any opinions are my own) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Please help!
When using a temp table in plpgsql functions that has columns comprised from many tables populated by joins, how do you specify a temp table return type when its generated by select into and dropped dynamically? I get an error when I specify returns setof temp_table. Also when I specify a permanent table as the return type, I get extra blank columns in the result set which are the additional columns of the permanent table I don't need and the data is displayed in the wrong columns. I tried defining composite types, but get a runtime error that it isn't available unless I first define it outside of the function. Since requests are dynamic, its impossible for me to determine all possible composite types required ahead of time. In plpgsql, how do you return back a result set that is determined and generated at runtime based on a report request? Also why does plpgsql require you to define what is returned? I'm relatively new to PostgreSQL and really impressed by its capabilities. Do you have any examples showing how to define and return a dynamic result set at runtime within a function? I tried posting these questions to the general mailing list, but didn't receive any feedback. Any help would be greatly appreciated. Thanks, Brandon ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Please help!
Brandon E Hofmann [EMAIL PROTECTED] writes: In plpgsql, how do you return back a result set that is determined and generated at runtime based on a report request? If I understand what you are asking for, you don't. Also why does plpgsql require you to define what is returned? plpgsql is not imposing this, the system as a whole does. Else, the parser would have no idea what to expand * to in select * from myfunc(42); There are facilities that let you use the same textual function myfunc for different result column sets, but this doesn't get you off the hook of having to tell the calling query what the column set is going to be. 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
PostgreSQL not stsrt!:( -Re: [HACKERS] Please help, pgAdmin3 on Debian!
Which is good. But I've got big trouble to login to this initial db by using this auto-created username postgres through pgAdmin:((( The first try failed due to Ident authentication failed, so I follow the suggestion on the pop-up window of pgAdmin3, and changed the ident method in the pg_hba.conf file all to md5 to try again, but the database now ask me for the password!! which I couldn't figure out the passwd so I tried to created rules in the pg_ident.conf file to map both the ordinary user and root user od Debian system to postgres, and tried again. But still failed,:((( ident authentication failedagain!!!:((( I've tried many times for all I could think and failed everytime failed:((( By the way each time before I try, I did pg_ctl reload, and I could see the failure reason changed after I do reload. You could try to change the ident method to trust (in pg_hba.conf). This should allow you to login. Then, set the password of the postgres user (alter user postgres with password 'blabla1212' ; ). Then you could change the ident method back to md5 . Adrian Maier Hi Adrian and All, I've got trouble again!:(( I changed the possword of postgres yesterday following the inst you gave, everything worked fine after the change, and I was able to login to the initial db through pgAdmin3 as the user postgres. I loged out of the Debian system until after 1am this very morning, and everything was fine at that time. But when I tried to login again to day today right now, I got problem. The Debian system started booting, everything goes fine until at the moment to start the postgresql server. The booting msg shows Starting postgreSQL 8.1 database server: main, and then hung forever and not move anymore. I have to turn off the computer power to stop it! I tried to loged in via the Recovery mode and was able to login as root, but I don't know what to do about the PostgreSQL and whatever related system booting process at the command line mode??:(( So is there anything has been wrong with what I did yesterday? Does the password change of user postgres has some impact on the Debian system booting? Please help me out of this. The PostgreSQl 8.1 just installed, not in use at all, all has been done on it was the change of the password of user postgres, in order to login to the initial db through pgAdmin3. Any help would be greatly appreciated!!! Thank you in advance!!! leo ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: PostgreSQL not stsrt!:( -Re: [HACKERS] Please help, pgAdmin3
-hackers is not the place for these questions. Please ask your questions in the correct forum, possibly pgsql-general. -hackers is for discussion of backend development. cheers andrew lmyho wrote: Which is good. But I've got big trouble to login to this initial db by using this auto-created username postgres through pgAdmin:((( The first try failed due to Ident authentication failed, so I follow the suggestion on the pop-up window of pgAdmin3, and changed the ident method in the pg_hba.conf file all to md5 to try again, but the database now ask me for the password!! which I couldn't figure out the passwd so I tried to created rules in the pg_ident.conf file to map both the ordinary user and root user od Debian system to postgres, and tried again. But still failed,:((( ident authentication failedagain!!!:((( I've tried many times for all I could think and failed everytime failed:((( By the way each time before I try, I did pg_ctl reload, and I could see the failure reason changed after I do reload. You could try to change the ident method to trust (in pg_hba.conf). This should allow you to login. Then, set the password of the postgres user (alter user postgres with password 'blabla1212' ; ). Then you could change the ident method back to md5 . Adrian Maier Hi Adrian and All, I've got trouble again!:(( I changed the possword of postgres yesterday following the inst you gave, everything worked fine after the change, and I was able to login to the initial db through pgAdmin3 as the user postgres. I loged out of the Debian system until after 1am this very morning, and everything was fine at that time. But when I tried to login again to day today right now, I got problem. The Debian system started booting, everything goes fine until at the moment to start the postgresql server. The booting msg shows Starting postgreSQL 8.1 database server: main, and then hung forever and not move anymore. I have to turn off the computer power to stop it! I tried to loged in via the Recovery mode and was able to login as root, but I don't know what to do about the PostgreSQL and whatever related system booting process at the command line mode??:(( So is there anything has been wrong with what I did yesterday? Does the password change of user postgres has some impact on the Debian system booting? Please help me out of this. The PostgreSQl 8.1 just installed, not in use at all, all has been done on it was the change of the password of user postgres, in order to login to the initial db through pgAdmin3. Any help would be greatly appreciated!!! Thank you in advance!!! leo ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Please help, pgAdmin3 on Debian!
Dear All,I am totally new to the PostgreSQL, and pgAdmin. I really need your help. I just installed the PostgreSQL8.1 and pgAdmin3 on a Debian system, using the apt-get install command. Apparently, the initial database and the user "postgres" have also been automatically created during the installation.Which is good. But I've got big trouble to login to this initial db by using this auto-created username "postgres" through pgAdmin:((( The first try failed due to "Ident authentication failed", so I follow the suggestion on the pop-up window of pgAdmin3, and changed the ident method in the pg_hba.conf file all to md5 to try again, but the database now ask me for the password!! which I couldn't figure out the passwd so I tried to created rules in the pg_ident.conf file to map both the ordinary user and root user od Debian system to postgres, and tried again. But still failed,:((( "ident authentication failed"again!!!:((( I've tried many times for all I could think and failed everytime failed:((( By the way each time before I try, I did "pg_ctl reload", and I could see the failure reason changed after I do reload. I've sent mail to other list but no anwser back. I believe people in this group must know what's the reason and solution. So would you please help me? So if there is auto-created password for this auto-created postgres user, please anyone tell me what it is?? Also otherwise, how can I login using the "ident auth" method through pgAdmin3 (just locally) on the Debian system??Any help would be greatly appreciated!!! Thank you so much for help!!!leo New Yahoo! Messenger with Voice. Call regular phones from your PC for low, low rates.
Re: [HACKERS] Please help, pgAdmin3 on Debian!
On 3/28/06, lmyho [EMAIL PROTECTED] wrote: Dear All, Which is good. But I've got big trouble to login to this initial db by using this auto-created username postgres through pgAdmin:((( The first try failed due to Ident authentication failed, so I follow the suggestion on the pop-up window of pgAdmin3, and changed the ident method in the pg_hba.conf file all to md5 to try again, but the database now ask me for the password!! which I couldn't figure out the passwd so I tried to created rules in the pg_ident.conf file to map both the ordinary user and root user od Debian system to postgres, and tried again. But still failed,:((( ident authentication failedagain!!!:((( I've tried many times for all I could think and failed everytime failed:((( By the way each time before I try, I did pg_ctl reload, and I could see the failure reason changed after I do reload. I've sent mail to other list but no anwser back. I believe people in this group must know what's the reason and solution. So would you please help me? So if there is auto-created password for this auto-created postgres user, please anyone tell me what it is?? You could try to change the ident method to trust (in pg_hba.conf). This should allow you to login. Then, set the password of the postgres user (alter user postgres with password 'blabla1212' ; ). Then you could change the ident method back to md5 . Adrian Maier ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Please help, pgAdmin3 on Debian!
Hi lmyho, lmyho [2006-03-28 0:17 -0800]: I am totally new to the PostgreSQL, and pgAdmin. I really need your help. I just installed the PostgreSQL8.1 and pgAdmin3 on a Debian system, using the apt-get install command. Apparently, the initial database and the user postgres have also been automatically created during the installation. Please feel free to mail me directly ([EMAIL PROTECTED]) for questions related to the Debian packages. It might be regarded as noise on the upstream lists. Which is good. But I've got big trouble to login to this initial db by using this auto-created username postgres through pgAdmin:((( The first try failed due to Ident authentication failed The 'postgres' user in Debian is a system user with a locked password, since it is not recommended to use it for normal work with the database. As /usr/share/postgresql-common/README.Debian describes, you should first create your own database user and work with that. Then the default 'ident' authentication scheme will work, and you are free to set a password for your db user as well (so that connecting from remote computer over TCP works as well). If you really need to connect as user postgres to do administrative tasks, then the easiest solution is to set a password for the user postgres, as already mentioned in the previous reply. HTH, Martin -- Martin Pitthttp://www.piware.de Ubuntu Developer http://www.ubuntu.com Debian Developer http://www.debian.org In a world without walls and fences, who needs Windows and Gates? signature.asc Description: Digital signature
Re: [HACKERS] Please help, pgAdmin3 on Debian!
You could try to change the ident method to trust (in pg_hba.conf). This should allow you to login. Then, set the password of the postgres user (alter user postgres with password 'blabla1212' ; ). Then you could change the ident method back to md5 . Hi Adrian, Thank you for help!! I've made the change and I am able to login using pgAdmin3 now.:) Trying to learn more about PostgreSQL! Thanks!!! leo __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Please Help: PostgreSQL Query Optimizer
Through googling, i found that Normal Disk has external data transfer rate of around 40MBps, ^^ Does this includes, seek and rotational latency ? where as Main Memory has Data transfer rate ranging from 1.6GBps to 2.8GBps. As we can see, the ratio between Disk and Main Memory data transfer rates is around 50. Then, if we multiply all cpu_* paramters by 50, the resulting values will be: random_page_cost = 1; cpu_tuple_cost = 0.5; cpu_index_tuple_cost = 0.05; cpu_operator_cost = 0.0125; Would it be a suitable approach ? We request all of u to give comments/suggestions on this calcualations. Thanking You. On Sun, 11 Dec 2005, Tom Lane wrote: [ trimming cc list to something sane ] Anjan Kumar. A. [EMAIL PROTECTED] writes: In Main Memory DataBase(MMDB) entire database on the disk is loaded on to the main memory during initial startup of the system. There after all the references are made to database on the main memory. When the system is going to shutdown, we will write back the database on the main memory to disk. Here, for the sake of recovery we are writing log records on to the disk during the transaction execution. Don't you get 99.9% of this for free with Postgres' normal behavior? Just increase shared_buffers. Can any one tell me the modifications needs to be incorporated to PostgreSQL, so that it considers only Processing Costs during optimization of the Query. Assuming that a page fetch costs zero is wrong even in an all-in-memory environment. So I don't see any reason you can't maintain the convention that a page fetch costs 1.0 unit, and just adjust the other cost parameters in the light of a different idea about what that actually means. Will it be sufficient, if we change the default values of above paramters in src/include/optimizer/cost.h and src/backend/utils/misc/postgresql.conf.sample as follows: random_page_cost = 4; cpu_tuple_cost = 2; cpu_index_tuple_cost = 0.2; cpu_operator_cost = 0.05; You'd want random_page_cost = 1 since there is presumably no penalty for random access in this context. Also, I think you'd want cpu_operator_cost a lot higher than that (maybe you dropped a decimal place? You scaled the others up by 200 but this one only by 20). It's entirely possible that the ratios of the cpu_xxx_cost values aren't very good and will need work. In the past we've never had occasion to study them very carefully, since they were only marginal contributions anyway. regards, tom lane -- Regards. Anjan Kumar A. MTech2, Comp Sci., www.cse.iitb.ac.in/~anjankumar __ Bradley's Bromide: If computers get too powerful, we can organize them into a committee -- that will do them in. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Please Help: PostgreSQL Query Optimizer
Defaulat values of various parameters in PostgreSQL: #random_page_cost = 4 # units are one sequential page fetch cost #cpu_tuple_cost = 0.01 # (same) #cpu_index_tuple_cost = 0.001 # (same) #cpu_operator_cost = 0.0025 # (same) #effective_cache_size = 1000# typically 8KB each Since sequential access is not significantly faster than random access in a MMDB, random_page_cost will be approximately same as sequential page fetch cost. If we make both sequential_page_fetch_cost and random_page_cost to 1, then we need to increase the various cpu_* paramters by multiplying the default values with appropriate Scaling Factor. Now, we need to determine this Scaling Factor. Through googling, i found that Normal Disk has external data transfer rate of around 40MBps, where as Main Memory has Data transfer rate ranging from 1.6GBps to 2.8GBps. As we can see, the ratio between Disk and Main Memory data transfer rates is around 50. Then, if we multiply all cpu_* paramters by 50, the resulting values will be: random_page_cost = 1; cpu_tuple_cost = 0.5; cpu_index_tuple_cost = 0.05; cpu_operator_cost = 0.0125; Would it be a suitable approach ? We request all of u to give comments/suggestions on this calcualations. Thanking You. On Sun, 11 Dec 2005, Tom Lane wrote: [ trimming cc list to something sane ] Anjan Kumar. A. [EMAIL PROTECTED] writes: In Main Memory DataBase(MMDB) entire database on the disk is loaded on to the main memory during initial startup of the system. There after all the references are made to database on the main memory. When the system is going to shutdown, we will write back the database on the main memory to disk. Here, for the sake of recovery we are writing log records on to the disk during the transaction execution. Don't you get 99.9% of this for free with Postgres' normal behavior? Just increase shared_buffers. Can any one tell me the modifications needs to be incorporated to PostgreSQL, so that it considers only Processing Costs during optimization of the Query. Assuming that a page fetch costs zero is wrong even in an all-in-memory environment. So I don't see any reason you can't maintain the convention that a page fetch costs 1.0 unit, and just adjust the other cost parameters in the light of a different idea about what that actually means. Will it be sufficient, if we change the default values of above paramters in src/include/optimizer/cost.h and src/backend/utils/misc/postgresql.conf.sample as follows: random_page_cost = 4; cpu_tuple_cost = 2; cpu_index_tuple_cost = 0.2; cpu_operator_cost = 0.05; You'd want random_page_cost = 1 since there is presumably no penalty for random access in this context. Also, I think you'd want cpu_operator_cost a lot higher than that (maybe you dropped a decimal place? You scaled the others up by 200 but this one only by 20). It's entirely possible that the ratios of the cpu_xxx_cost values aren't very good and will need work. In the past we've never had occasion to study them very carefully, since they were only marginal contributions anyway. regards, tom lane -- Regards. Anjan Kumar A. MTech2, Comp Sci., www.cse.iitb.ac.in/~anjankumar __ A woman physician has made the statement that smoking is neither physically defective nor morally degrading, and that nicotine, even when indulged to in excess, is less harmful than excessive petting. -- Purdue Exponent, Jan 16, 1925 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Please Help: PostgreSQL Query Optimizer
On Mon, Dec 12, 2005 at 06:39:42PM +0530, Anjan Kumar. A. wrote: Through googling, i found that Normal Disk has external data transfer rate of around 40MBps, where as Main Memory has Data transfer rate ranging from 1.6GBps to 2.8GBps. I think 40MB/s is a burst speed. You should do some testing to verify. In any case, PostgreSQL doesn't come close to the theoretical maximum disk bandwidth even on a sequential scan. There's been discussion about this on various lists in the past. For a single drive, expect something more in the range of 4-6MB/s (depending on the drive). More important that throughput though, is latency. Because the latency on memory is much closer to 0 (it's not truely 0 due to L1/L2 caching), you can serve concurrent requests a lot faster. -- 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] Please Help: PostgreSQL Query Optimizer
Anjan, But, in PostgreSQL all costs are scaled relative to a page fetch. If we make both sequential_page_fetch_cost and random_page_cost to 1, then we need to increase the various cpu_* paramters by multiplying the default values with appropriate Scaling Factor. Now, we need to determine this Scaling Factor. I see, so you're saying that because the real cost of a page fetch has decreased, the CPU_* costs should increase proportionally because relative to the real costs of a page fetch they should be higher? That makes a sort of sense. The problem that you're going to run into is that currently we have no particularly reason to believe that the various cpu_* costs are more than very approximately correct as rules of thumb. So I think you'd be a lot better off trying to come up with some means of computing the real cpu costs of each operation, rather than trying to calculate a multiple of numbers which may be wrong in the first place. I know that someone on this list was working on a tool to digest EXPLAIN ANALYZE results and run statistics on them. Can't remember who, though. Also, I'm still curious on how you're handling shared_mem, work_mem and maintenance_mem. You didn't answer last time. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Please Help: PostgreSQL Query Optimizer
I'm working on a project, whose implementation deals with PostgreSQL. A brief description of the project is given below. Project Description: In Main Memory DataBase(MMDB) entire database on the disk is loaded on to the main memory during initial startup of the system. There after all the references are made to database on the main memory. When the system is going to shutdown, we will write back the database on the main memory to disk. Here, for the sake of recovery we are writing log records on to the disk during the transaction execution. We want to implement MMDB by modifying PostgreSQL. We implemented our own Main Memory File System to store the primary copy of the database in main memory, and Modified the PostgreSQL to access the data in the Main Memory File System. Now, in our implementation Disk access is completely avoided during normal transaction execution. So, we need to modify the Query Optimizer of PostgreSQL so that it wont consider disk related costs during calculation of Query Costs. Query Optimizer should try to minimize the Processing Cost. The criteria for cost can be taken as the number of tuples that have to read/write from main memory, number of comparisons, etc. Can any one tell me the modifications needs to be incorporated to PostgreSQL, so that it considers only Processing Costs during optimization of the Query. In PostgreSQL, Path costs are measured in units of disk accesses. One sequential page fetch has cost 1. I think, in PostgreSQL following paramters are used in calculating the cost of the Query Path : #random_page_cost = 4 # units are one sequential page fetch cost #cpu_tuple_cost = 0.01 # (same) #cpu_index_tuple_cost = 0.001 # (same) #cpu_operator_cost = 0.0025 # (same) #effective_cache_size = 1000# typically 8KB each In our case we are reading pages from Main Memory File System, but not from Disk. Will it be sufficient, if we change the default values of above paramters in src/include/optimizer/cost.h and src/backend/utils/misc/postgresql.conf.sample as follows: random_page_cost = 4; cpu_tuple_cost = 2; cpu_index_tuple_cost = 0.2; cpu_operator_cost = 0.05; Please help us in this regard. I request all of you to give comments/suggestions on this. Waiting for your kind help. -- Thanks. Anjan Kumar A. MTech2, Comp Sci., www.cse.iitb.ac.in/~anjankumar __ May's Law: The quality of correlation is inversly proportional to the density of control. (The fewer the data points, the smoother the curves.) ---(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] Please Help: PostgreSQL Query Optimizer
Anjan, In our case we are reading pages from Main Memory File System, but not from Disk. Will it be sufficient, if we change the default values of above paramters in src/include/optimizer/cost.h and src/backend/utils/misc/postgresql.conf.sample as follows: random_page_cost = 4; This should be dramatically lowered. It's supposed to represent the ratio of seek-fetches to seq scans on disk. Since there's no disk, it should be a flat 1.0. However, we are aware that there are flaws in our calculations involving random_page_cost, such that the actual number for a system where there is no disk cost would be lower than 1.0. Your research will hopefully help us find these flaws. cpu_tuple_cost = 2; cpu_index_tuple_cost = 0.2; cpu_operator_cost = 0.05; I don't see why you're increasing the various cpu_* costs. CPU costs would be unaffected by the database being in memory. In general, I lower these by a divisor based on the cpu speed; for example, on a dual-opteron system I lower the defaults by /6. However, that's completely unrelated to using an MMDB. So, other than random_page_cost, I don't know of other existing GUCs that would be directly related to using a disk/not using a disk. How are you handling shared memory and work memory? I look forward to hearing more about your test! -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [DOCS] [HACKERS] Please Help: PostgreSQL Query Optimizer
Josh Berkus josh@agliodbs.com writes: I don't see why you're increasing the various cpu_* costs. You missed the point Josh --- these numbers are relative to the cost of a page fetch, so if page fetch is measured in microseconds instead of milliseconds, then you *do* want to bump the CPU costs up. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Please Help: PostgreSQL Query Optimizer
Since sequential access is not significantly faster than random access in a MMDB, random_page_cost will be approximately same as sequential page fetch cost. As every thing is present in Main Memory, we need to give approximately same cost to read/write to Main Memory and CPU Related operations. But, in PostgreSQL all costs are scaled relative to a page fetch. If we make both sequential_page_fetch_cost and random_page_cost to 1, then we need to increase the various cpu_* paramters by multiplying the default values with appropriate Scaling Factor. Now, we need to determine this Scaling Factor. Still, i want to confirm whether this approach is the correct one. On Sun, 11 Dec 2005, Josh Berkus wrote: Anjan, In our case we are reading pages from Main Memory File System, but not from Disk. Will it be sufficient, if we change the default values of above paramters in src/include/optimizer/cost.h and src/backend/utils/misc/postgresql.conf.sample as follows: random_page_cost = 4; This should be dramatically lowered. It's supposed to represent the ratio of seek-fetches to seq scans on disk. Since there's no disk, it should be a flat 1.0. However, we are aware that there are flaws in our calculations involving random_page_cost, such that the actual number for a system where there is no disk cost would be lower than 1.0. Your research will hopefully help us find these flaws. cpu_tuple_cost = 2; cpu_index_tuple_cost = 0.2; cpu_operator_cost = 0.05; I don't see why you're increasing the various cpu_* costs. CPU costs would be unaffected by the database being in memory. In general, I lower these by a divisor based on the cpu speed; for example, on a dual-opteron system I lower the defaults by /6. However, that's completely unrelated to using an MMDB. So, other than random_page_cost, I don't know of other existing GUCs that would be directly related to using a disk/not using a disk. How are you handling shared memory and work memory? I look forward to hearing more about your test! -- Regards. Anjan Kumar A. MTech2, Comp Sci., www.cse.iitb.ac.in/~anjankumar __ Do not handicap your children by making their lives easy. -- Robert Heinlein ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Please Help: PostgreSQL Query Optimizer
Hi,
I'm very new to this list -- I've been using and advocating PostgreSQL for
no less than 4 or 5 years now, and have participated in some of the other
mailing lists, but never on this one.
My question is (short version): how would one go about adding a new
(built-in) function to PostgreSQL?
Long-ish version:
I know the answer in theory -- one goes through the source code, find
out how it all works, and modify/add the code to add or fix whatever
feature we want.
I guess my point in here would be rather a feature request -- except
that I'd find it pretty exciting to implement it myself, and then propose
the new feature by volunteering the implementation that I already wrote
(seems like the spirit of open-source communities, right?) -- then of
course, it would be subject to consensus, whether or not the feature
makes sense and the implementation is good enough.
I'm interested in adding additional hash functions -- PG supports, as part
of the built-in SQL functions, MD5 hashing. So, for instance, I can simply
type, at a psql console, the following:
select md5('abc');
My feature request (which again, I'd like to implement it myself) would
be the ability to do:
select sha1('xyz'), sha256('etc');
(At least these two -- maybe for completeness it would be good to have
sha224, 384, and 512, but I guess SHA1 and SHA-256 would be a very good
and sound starting point)
So, can you offer some advice or pointers on how to go about that?
I started by doing a search for the string md5 through all the source
code -- the problem is, md5 shows up in many many many places (it is
part of the authentication protocol, among other things), so I got a
little bit lost searching through it all.
I wonder if you have some documents specifically aimed at providing
advice and documentation for prospective developers (or for people
that want to tweak the source code to fix/tuneup or add functionality),
I guess that would be great for me in this case.
Thanks!
Carlos
--
---(end of broadcast)---
TIP 6: explain analyze is your friend
Adding funtions to postgresql (Not - )e: [HACKERS] Please Help: PostgreSQL Query Optimizer
Ühel kenal päeval, P, 2005-12-11 kell 17:55, kirjutas Carlos Moreno:
Hi,
I'm very new to this list -- I've been using and advocating PostgreSQL for
no less than 4 or 5 years now, and have participated in some of the other
mailing lists, but never on this one.
My question is (short version): how would one go about adding a new
(built-in) function to PostgreSQL?
Ask your question as a separate post, not as an answer t another
thread :)
I'm interested in adding additional hash functions -- PG supports, as part
of the built-in SQL functions, MD5 hashing. So, for instance, I can simply
type, at a psql console, the following:
select md5('abc');
My feature request (which again, I'd like to implement it myself) would
be the ability to do:
select sha1('xyz'), sha256('etc');
(At least these two -- maybe for completeness it would be good to have
sha224, 384, and 512, but I guess SHA1 and SHA-256 would be a very good
and sound starting point)
Take a look at the pgcrypto contrib module (in src/contrib/pgcrypto )
Not only does it show how to add functions, but it also provides many of
the ones you need.
Hannu
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] Please Help: PostgreSQL Query Optimizer
Actually, there is probably comparatively little to gain from making it
a builtin. And SHA1 is already there in the pgcrypto contrib module.
Presumably if we wanted a builtin we would start from that code base.
cheers
andrew
Carlos Moreno wrote:
Hi,
I'm very new to this list -- I've been using and advocating PostgreSQL
for
no less than 4 or 5 years now, and have participated in some of the other
mailing lists, but never on this one.
My question is (short version): how would one go about adding a new
(built-in) function to PostgreSQL?
Long-ish version:
I know the answer in theory -- one goes through the source code, find
out how it all works, and modify/add the code to add or fix whatever
feature we want.
I guess my point in here would be rather a feature request -- except
that I'd find it pretty exciting to implement it myself, and then propose
the new feature by volunteering the implementation that I already wrote
(seems like the spirit of open-source communities, right?) -- then of
course, it would be subject to consensus, whether or not the feature
makes sense and the implementation is good enough.
I'm interested in adding additional hash functions -- PG supports, as
part
of the built-in SQL functions, MD5 hashing. So, for instance, I can
simply
type, at a psql console, the following:
select md5('abc');
My feature request (which again, I'd like to implement it myself) would
be the ability to do:
select sha1('xyz'), sha256('etc');
(At least these two -- maybe for completeness it would be good to have
sha224, 384, and 512, but I guess SHA1 and SHA-256 would be a very good
and sound starting point)
So, can you offer some advice or pointers on how to go about that?
I started by doing a search for the string md5 through all the source
code -- the problem is, md5 shows up in many many many places (it is
part of the authentication protocol, among other things), so I got a
little bit lost searching through it all.
I wonder if you have some documents specifically aimed at providing
advice and documentation for prospective developers (or for people
that want to tweak the source code to fix/tuneup or add functionality),
I guess that would be great for me in this case.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: Adding funtions to postgresql (Not - )e: [HACKERS] Please Help:
Hannu Krosing wrote: Ask your question as a separate post, not as an answer t another thread :) Also, if you post to a mailing list, you should have the courtesy to arrange it so your spam filter does not reject replies. cheers andrew ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: Adding funtions to postgresql (Not - )e: [HACKERS] Please Help:
Hannu Krosing wrote:
Ühel kenal päeval, P, 2005-12-11 kell 17:55, kirjutas Carlos Moreno:
Hi,
I'm very new to this list -- I've been using and advocating PostgreSQL for
no less than 4 or 5 years now, and have participated in some of the other
mailing lists, but never on this one.
My question is (short version): how would one go about adding a new
(built-in) function to PostgreSQL?
Ask your question as a separate post, not as an answer t another
thread :)
I know... Sorry -- My brain must have temporarily shut down when I was
posting. Wrong subject, wrong link to the rest of the messages... :-(
My feature request (which again, I'd like to implement it myself) would
be the ability to do:
select sha1('xyz'), sha256('etc');
(At least these two -- maybe for completeness it would be good to have
sha224, 384, and 512, but I guess SHA1 and SHA-256 would be a very good
and sound starting point)
Take a look at the pgcrypto contrib module (in src/contrib/pgcrypto )
Not only does it show how to add functions, but it also provides many of
the ones you need.
Ok. Will do.
Thanks for the pointer!
Carlos
--
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Please Help: PostgreSQL Query Optimizer
Am Sonntag, den 11.12.2005, 17:55 -0500 schrieb Carlos Moreno:
...
I'm interested in adding additional hash functions -- PG supports, as part
of the built-in SQL functions, MD5 hashing. So, for instance, I can simply
type, at a psql console, the following:
select md5('abc');
My feature request (which again, I'd like to implement it myself) would
be the ability to do:
select sha1('xyz'), sha256('etc');
(At least these two -- maybe for completeness it would be good to have
sha224, 384, and 512, but I guess SHA1 and SHA-256 would be a very good
and sound starting point)
So, can you offer some advice or pointers on how to go about that?
You might want to check out contrib/pgcrypto
more often then not, if you want something, its
already done ;)
Not sure if this will ever be included in the core,
since not many people need these advanced hash functions.
HTH
Tino Wildenhain
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[HACKERS] Please help re function packaging...
Anyone please help... I'm a newbie on creating functions in postgresql.
Here is an oracle package that I'm trying to port to postgresql:
CREATE OR REPLACE PACKAGE BODY NewsPkg.NewsTools
AS
PROCEDURE del_news (i_id IN VARCHAR2)
IS
BEGIN
DELETE FROM tbl_news_type
WHERE uniqueid = i_id;
DELETE FROM tbl_news
WHERE uniqueid = i_id;
END del_news;
PROCEDURE upd_newstype
IS
CURSOR cur_news
IS
SELECT uniqueid
FROM tbl_news_type
WHERE mother_id IS NULL
GROUP BY uniqueid;
v_id tbl_news_type.uniqueid%TYPE;
BEGIN
OPEN cur_news;
LOOP
FETCH cur_news INTO v_id;
upd_mothernews (v_id);
COMMIT;
END LOOP;
CLOSE cur_news;
END upd_newstype;
END NewsTools;
Can anyone help me on how this oracle package would look like in
postgresql... I would really appreciate it if someone could show me
even the synopsis of this code in postgresql. I've been trying all
sorts of ways to come up with the code in postgresql but when I this
command:
select NewsTools.del_news('20040111DN001869');
I get this error:
ERROR: Namespace "NewsTools" does not exist
Tnx,
Darius
[HACKERS] Please help
I've have a MAJOR crash an hour ago and postgresql doesn't start anymoe (version 7.3.4). Here's the error log: Oct 30 17:16:20 server postgres[4135]: [1-2]This probably means that some data blocks are corrupted Oct 30 17:16:20 server postgres[4135]: [1-3]and you will have to use the last backup for recovery. Oct 30 17:16:20 server postgres[4135]: [2] LOG: checkpoint record is at 36/C27C14C0 Oct 30 17:16:20 server postgres[4135]: [3] LOG: redo record is at 36/C2782998; undo record is at 0/0; shutdown FALSE Oct 30 17:16:20 server postgres[4135]: [4] LOG: next transaction id: 203794305; next oid: 32417798 Oct 30 17:16:20 server postgres[4135]: [5] LOG: database system was not properly shut down; automatic recovery in progress Oct 30 17:16:20 server postgres[4135]: [6] LOG: redo starts at 36/C2782998 Oct 30 17:16:21 server postgres[4135]: [7] PANIC: Invalid page header in block 6157 of 29135442 Oct 30 17:16:21 server postgres[4132]: [1] LOG: startup process (pid 4135) was terminated by signal 6 Oct 30 17:16:21 server postgres[4132]: [2] LOG: aborting startup due to startup process failure Is there anything I can do not to reload all backups? Regards -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Please help
[EMAIL PROTECTED] writes: I've have a MAJOR crash an hour ago and postgresql doesn't start anymoe (version 7.3.4). Oct 30 17:16:21 server postgres[4135]: [7] PANIC: Invalid page header in block 6157 of 29135442 Oct 30 17:16:21 server postgres[4132]: [1] LOG: startup process (pid 4135) was terminated by signal 6 Oct 30 17:16:21 server postgres[4132]: [2] LOG: aborting startup due to startup process failure Is there anything I can do not to reload all backups? You could try turning on zero_damaged_pages in postgresql.conf. If you are lucky, the page in question is going to be rewritten from WAL anyway. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Please help
Thanks Tom, The answer came too late and I could'nt wait. pg_resetlog did nearly the trick, Only one database was really hurt. So I reloaded all but this one from pg_dumpall then the last one from backup... I'm cursed On Thu, 30 Oct 2003, Tom Lane wrote: Date: Thu, 30 Oct 2003 17:25:02 -0500 From: Tom Lane [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: pgsql-hackers list [EMAIL PROTECTED] Subject: Re: [HACKERS] Please help [EMAIL PROTECTED] writes: I've have a MAJOR crash an hour ago and postgresql doesn't start anymoe (version 7.3.4). Oct 30 17:16:21 server postgres[4135]: [7] PANIC: Invalid page header in block 6157 of 29135442 Oct 30 17:16:21 server postgres[4132]: [1] LOG: startup process (pid 4135) was terminated by signal 6 Oct 30 17:16:21 server postgres[4132]: [2] LOG: aborting startup due to startup process failure Is there anything I can do not to reload all backups? You could try turning on zero_damaged_pages in postgresql.conf. If you are lucky, the page in question is going to be rewritten from WAL anyway. regards, tom lane -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Please help
Olivier PRENANT [EMAIL PROTECTED] writes: It seems that connection from php pg_connect not supplying a password lives the process for a certain ammount of time running, then postmaster just hangs. That's hard to believe. In 7.2 or later, the backend should give up and close the connection and exit if the client doesn't finish the authentication handshake within 60 seconds. Can anyone else reproduce a problem with lack of a password on a PHP connection? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Please help
On Mon, 21 Oct 2002, Tom Lane wrote: Date: Mon, 21 Oct 2002 12:52:10 -0400 From: Tom Lane [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: pgsql-hackers list [EMAIL PROTECTED] Subject: Re: [HACKERS] Please help Olivier PRENANT [EMAIL PROTECTED] writes: pyrenet=# VACUUM ANALYZE ; FATAL 2: could not open transaction-commit log directory (/usr/local/pgsql/data/pg_clog): Too many open files Hmm. Do you have any idea what sysconf(_SC_OPEN_MAX) returns on your platform? You could try reducing the max_files_per_process parameter. The point is, it occurs today for the very first time! Question: does (with 7.2) augmenting max_connection suffice, or do I have to recompile? That's the only thing that comes to my mind! I changed max_coneections (and related parameters) in postgresql.conf only... I say that, because I tried to change socket_directory in postgresql.conf and clients didn't work anymore regards, tom lane -- Olivier PRENANT Tel:+33-5-61-50-97-00 (Work) Quartier d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Please help
On Mon, 2002-10-21 at 12:57, Larry Rosenman wrote: On Mon, 2002-10-21 at 12:39, Olivier PRENANT wrote: Hi larry, Glad to see you around... On 21 Oct 2002, Larry Rosenman wrote: Date: 21 Oct 2002 12:34:48 -0500 From: Larry Rosenman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Tom Lane [EMAIL PROTECTED], pgsql-hackers list [EMAIL PROTECTED] Subject: Re: [HACKERS] Please help The point is, it occurs today for the very first time! Question: does (with 7.2) augmenting max_connection suffice, or do I have to recompile? You might need to up the Shared Memory parameters and the Semaphore Parameters in your OS (UnixWare IIRC). I did! Ok. That's the only thing that comes to my mind! I changed max_coneections (and related parameters) in postgresql.conf only... I say that, because I tried to change socket_directory in postgresql.conf and clients didn't work anymore Sorry, I mis-explain! I mean changing socket_directory in postgresql.conf and restart server did create .s.PGSQL.5432 in the new dir, however clients (like psql) still want it in /tmp!! That **WOULD** take a recompile. Or (IIRC), changing the connect string passed from PHP to PostgreSQL. LER See above. regards, tom lane -- Olivier PRENANT Tel:+33-5-61-50-97-00 (Work) Quartier d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Olivier PRENANT Tel:+33-5-61-50-97-00 (Work) Quartier d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(end of broadcast)--- TIP 3: 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 -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(end of broadcast)--- TIP 3: 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] Please help
Olivier PRENANT [EMAIL PROTECTED] writes: pyrenet=# VACUUM ANALYZE ; FATAL 2: could not open transaction-commit log directory (/usr/local/pgsql/data/pg_clog): Too many open files Hmm. Do you have any idea what sysconf(_SC_OPEN_MAX) returns on your platform? You could try reducing the max_files_per_process parameter. regards, tom lane ---(end of broadcast)--- TIP 3: 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] Please help
Hi Tom, I can assure you that there are no problem anymore now that I have put the password. However the modification pg_hba.conf has been done a few days ago and never noticed the error until today where I had a *LOT* of visits to my site. So I still think it's a matter of bad connection delay. Connexion where comming very fast and postmaster did'nt release them fast enough. I now have this problem when I run vacuum analyze: Script started on Mon Oct 21 18:20:35 2002 ~ 18:20:35: psql pyrenet Password: Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit pyrenet=# VACUUM ANALYZE ; FATAL 2: could not open transaction-commit log directory (/usr/local/pgsql/data/pg_clog): Too many open files server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: NOTICE: Message from PostgreSQL backend: The Postmaster has informed me that some other backend died abnormally and possibly corrupted shared memory. I have rolled back the current transaction and am going to terminate your database system connection and exit. Please reconnect to the database system and repeat your query. Failed. !# \q ~ 18:21:21: psql pyrenet Password: Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit pyrenet=# VACUUM ANALYZE ; FATAL 2: could not open transaction-commit log directory (/usr/local/pgsql/data/pg_clog): Too many open files server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. !# \q script done on Mon Oct 21 18:21:51 2002 What causes (FILES=64). Again, this is the very first time I have such problems on postgresql!! It works so well, it's the central point of my system!! On Mon, 21 Oct 2002, Tom Lane wrote: Date: Mon, 21 Oct 2002 11:35:33 -0400 From: Tom Lane [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: pgsql-hackers list [EMAIL PROTECTED] Subject: Re: [HACKERS] Please help Olivier PRENANT [EMAIL PROTECTED] writes: It seems that connection from php pg_connect not supplying a password lives the process for a certain ammount of time running, then postmaster just hangs. That's hard to believe. In 7.2 or later, the backend should give up and close the connection and exit if the client doesn't finish the authentication handshake within 60 seconds. Can anyone else reproduce a problem with lack of a password on a PHP connection? regards, tom lane -- Olivier PRENANT Tel:+33-5-61-50-97-00 (Work) Quartier d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Please help
Thanks for your reply. Actually, I just found xhat happened 2 mn ago! Last week I changed my pg_hba.conf to require an md5 password for one specific database and updated all my script but ONE. It seems that connection from php pg_connect not supplying a password lives the process for a certain ammount of time running, then postmaster just hangs. Don't really know what happens here, but supplying a password on this script made the problem go away... (at least for now) Regards On Mon, 21 Oct 2002, Tom Lane wrote: Date: Mon, 21 Oct 2002 11:02:43 -0400 From: Tom Lane [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: pgsql-hackers list [EMAIL PROTECTED] Subject: Re: [HACKERS] Please help Olivier PRENANT [EMAIL PROTECTED] writes: Without modifying anything, postgresql (since today) has a strange behavior: All connections are rejected with No space left on device. Could you be out of swap space? I'd like to see the *exact* context in which you see this error message, though. regards, tom lane -- Olivier PRENANT Tel:+33-5-61-50-97-00 (Work) Quartier d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Please help
On Mon, 2002-10-21 at 12:39, Olivier PRENANT wrote: Hi larry, Glad to see you around... On 21 Oct 2002, Larry Rosenman wrote: Date: 21 Oct 2002 12:34:48 -0500 From: Larry Rosenman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Tom Lane [EMAIL PROTECTED], pgsql-hackers list [EMAIL PROTECTED] Subject: Re: [HACKERS] Please help The point is, it occurs today for the very first time! Question: does (with 7.2) augmenting max_connection suffice, or do I have to recompile? You might need to up the Shared Memory parameters and the Semaphore Parameters in your OS (UnixWare IIRC). I did! Ok. That's the only thing that comes to my mind! I changed max_coneections (and related parameters) in postgresql.conf only... I say that, because I tried to change socket_directory in postgresql.conf and clients didn't work anymore Sorry, I mis-explain! I mean changing socket_directory in postgresql.conf and restart server did create .s.PGSQL.5432 in the new dir, however clients (like psql) still want it in /tmp!! That **WOULD** take a recompile. LER See above. regards, tom lane -- Olivier PRENANT Tel:+33-5-61-50-97-00 (Work) Quartier d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Olivier PRENANT Tel:+33-5-61-50-97-00 (Work) Quartier d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(end of broadcast)--- TIP 3: 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] Please help
On Monday 21 October 2002 15:42, Olivier PRENANT wrote: Hi all, Without modifying anything, postgresql (since today) has a strange behavior: All connections are rejected with No space left on device. There's plenty of space in shm, disk... I have no idea whether it's relevant, but maybe you have a problem with semaphores? See: http://www.ca.postgresql.org/docs/faq-english.html#3.4 (A lack of available semaphores can also produce the message No space left on device.) Sorry I can't help any further. Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] please help on query
I can't improve performance on this
query:
SELECTsupplier.name,supplier.addressFROMsupplier,nationWHEREsupplier.suppkey
IN(SELECTpartsupp.suppkeyFROMpartsuppWHEREpartsupp.partkey
IN(SELECTpart.partkeyFROMpartWHEREpart.name
like 'forest%')AND
partsupp.availqty(SELECT0.5*(sum(lineitem.quantity)::FLOAT)FROMlineitemWHERElineitem.partkey=partsupp.partkeyAND
lineitem.suppkey=partsupp.partkeyAND
lineitem.shipdate=('1994-01-01')::DATEAND
lineitem.shipdate(('1994-01-01')::DATE+('1
year')::INTERVAL)::DATE))AND
supplier.nationkey=nation.nationkeyAND nation.name='CANADA'ORDER
BYsupplier.name;
explain results:
NOTICE: QUERY PLAN:
Sort (cost=2777810917708.17..2777810917708.17
rows=200 width=81) - Nested Loop
(cost=0.00..2777810917700.53 rows=200
width=81) - Seq Scan on
nation (cost=0.00..1.31 rows=1
width=4) - Index Scan
using snation_index on supplier (cost=0.00..2777810917696.72 rows=200
width=77)
SubPlan
- Materialize (cost=6944527291.72..6944527291.72 rows=13
width=4)
- Seq Scan on partsupp (cost=0.00..6944527291.72 rows=13
width=4)
SubPlan
- Materialize (cost=8561.00..8561.00 rows=1
width=4)
- Seq Scan on part (cost=0.00..8561.00 rows=1
width=4)
- Aggregate (cost=119.61..119.61 rows=1
width=4)
- Index Scan using lineitem_index on lineitem (cost=0.00..119.61
rows=1 width=4)
partsupp::80 tuples
Table
"partsupp" Column |
Type | Modifiers
++---partkey
| integer | not
nullsuppkey |
integer | not
nullavailqty |
integer | supplycost |
numeric(10,2) | comment | character(199) |
Primary key: partsupp_pkeyTriggers:
RI_ConstraintTrigger_16597,
RI_ConstraintTrigger_16603
tpch=# select attname,n_distinct,correlation from
pg_stats where tablename='partsupp'; attname | n_distinct
| correlation
++-partkey
| -0.195588 |
1suppkey | 9910
| 0.00868363availqty
| 9435 | -0.00788662supplycost
| -0.127722 | -0.0116864comment
| -1 |
0.0170702
I accept query changes, reordering, indexes ideas
and horizontal partitioning
thanks in advance.
Regards
Re: [HACKERS] please help on query
On Thu, 2002-07-11 at 11:22, Luis Alberto Amigo Navarro wrote: I can't improve performance on this query: Blame Canada! -- J. R. Nield [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] please help on query
On Thursday 11 July 2002 12:06, J. R. Nield wrote: On Thu, 2002-07-11 at 11:22, Luis Alberto Amigo Navarro wrote: I can't improve performance on this query: Blame Canada! Whatever ... How's that silver medal down there in the states? ;-) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] please help on query
On Thu, 2002-07-11 at 17:22, Luis Alberto Amigo Navarro wrote:
I can't improve performance on this query:
You could try rewriting the IN's into = joins
or even use explicit INNER JOIN syntax to force certain plans
with a select inside another and depending on value of partsupp.partkey
it is really hard for optimiser to do anything else than to perform the
query for each row.
But it may help to rewrite
SELECT
partsupp.suppkey
FROM
partsupp
WHERE
partsupp.partkey IN (
SELECT
part.partkey
FROM
part
WHERE
part.name like 'forest%'
)
AND partsupp.availqty(
SELECT
0.5*(sum(lineitem.quantity)::FLOAT)
FROM
lineitem
WHERE
lineitem.partkey=partsupp.partkey
AND lineitem.suppkey=partsupp.partkey
AND lineitem.shipdate=('1994-01-01')::DATE
AND lineitem.shipdate(('1994-01-01')::DATE+('1
year')::INTERVAL)::DATE
)
)
into
SELECT
partsupp.suppkey
FROM
partsupp,
(SELECT part.partkey as partkey
FROM part
WHERE part.name like 'forest%'
) fp,
(SELECT 0.5*(sum(lineitem.quantity)::FLOAT) as halfsum,
partkey
FROM lineitem
WHERE
lineitem.partkey=partsupp.partkey
AND lineitem.suppkey=partsupp.partkey
AND lineitem.shipdate=('1994-01-01')::DATE
AND lineitem.shipdate(('1994-01-01')::DATE+('1
year')::INTERVAL)::DATE
) li
WHERE partsupp.partkey = fp.partkey
AND partsupp.partkey = li.partkey
AND partsupp.availqty halfsum
if lineitem is significantly smaller than partsupp
But you really should tell us more, like how many lines does lineitem
and other tables have,
--
Hannu
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] please help on query
On Thu, 2002-07-11 at 17:22, Luis Alberto Amigo Navarro wrote:
I can't improve performance on this query:
You may also want to rewrite
lineitem.shipdate(('1994-01-01')::DATE+('1 year')::INTERVAL)::DATE
into
lineitem.shipdate(('1995-01-01')::DATE
if you can, as probably the optimiser will not recognize it else as a
constant and won't use index on lineitem.shipdate.
Hannu
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] please help on query
On Thu, 2002-07-11 at 17:22, Luis Alberto Amigo Navarro wrote: I can't improve performance on this query: This _may_ work. SELECT supplier.name, supplier.address FROM supplier, nation, WHERE supplier.suppkey IN ( SELECT part.partkey FROM part WHERE part.name like 'forest%' INNER JOIN partsupp ON part.partkey=partsupp.partkey INNER JOIN ( SELECT 0.5*(sum(lineitem.quantity)::FLOAT) as halfsum FROM lineitem WHERE lineitem.partkey=partsupp.partkey AND shipdate = '1994-01-01' AND shipdate '1995-01-01' ) li ON partsupp.availqty halfsum ) AND supplier.nationkey=nation.nationkey AND nation.name='CANADA' ORDER BY supplier.name; --- Hannu ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] PLEASE help with foreign key and inheritance problem
I stated this before, but I did not get a helpful answer. I might have
misunderstood tghe documentation on foreign keys:
create table global(id serial);
create table child(anything text) inherits(global);
insert into child(anything) values ('test);
Now, a select * from child shows
id anything
-
1 test
So far, so good.
create table dependend(globid int4 references child(id) on update cascade on
delete cascade);
gives me an error:
CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
ERROR: UNIQUE constraint matching given keys for referenced table "child"
not found
Once again, _why_ is this? What would inheritance be good for if I can't use
it this way? Bad enough that inheritance of triggers or constraints doesn't
work, but a simple refernce to a attribute should be possible, shouldn't it?
If there is a good reason not to allow it, I would like to know. If not, I
would be willing to help out implementing it, if somebody points me into the
right direction in the code (or documentation)
Horst
AW: AW: [HACKERS] PLEASE help with foreign key and inheritance proble m
create unique index child_id_index on child (id); Thanks a lot. You saved my day :-))) Always feels good to be able to help :-) CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: UNIQUE constraint matching given keys for referenced table "child" not found Then the above works. Actually the error message sounds sufficiently clear to me, no? I retrospect, yes. Still, I think inheritance could/should do that for me automatically. Is there a good reason why it doesn't ? None, other that 1. noone implemented it and 2nd there was no generally accepted plan on how this should work. e.g. should the unique index for the serial span the whole hierarchy, or should a separate index be created for each table ? As a hint I would keep my fingers off inheritance as it stands now, since all it is good for is to save you some typing for the create table statements. It currently has almost no other functionality except to give you the supertable columns for all rows in the hierarchy if you select * from supertable. Andreas
