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
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 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. 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
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 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. > >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: [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, 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!
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 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?? 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: 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
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
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
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
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
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: [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
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
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: [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
[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
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])
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
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
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
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 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
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:
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 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 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
