Re: [HACKERS] reprise on Linux overcommit handling
Bruce Momjian wrote: > > Thanks. Interesting. Hard to imagine what they were thinking when they > put this code in. Way back in the day, when dinosaurs ruled the earth, or at least the server room, many applications were written with rather bad memory allocation semantics: they'd grab a bunch of memory and not necessarily use it for anything. Typically you could specify a maximum memory allocation amount for the program but the problem was that it would grab exactly that amount, and it's obviously better for it to be a bit more dynamic. That in itself isn't a terribly bad thing ... if you have enough actual memory to deal with it. Problem is, back then most systems didn't have enough memory to deal with multiple programs behaving that way. Overcommit was designed to account for that behavior. It's not ideal at all but it's better to have that option than not. Overcommit isn't really necessary today because of the huge amount of memory that you can put into a system for cheap (HP servers excluded, they want some serious cash for memory). -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] table-level and row-level locks.
Once more unto the breach - Could you please abstain from sending HTML email to the list ? Many thanks ! -- Pip-pip Sailesh http://www.cs.berkeley.edu/~sailesh ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] php with postgres
> > Surely PHP can be modified so as to use the new 3.0 protocol feature to > > detect whether it's in a transaction or not, so as to avoid unnecssary > > querying? > > Yes, you could, but it hardly seems worth it because they have to > support old and new protocols. Eventually, yes, they could use that to > eliminate the BEGIN;COMMIT. Just use a configure test to see if you have the appropriate function call in your libpq... Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Unsubscribe
---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] compile failure in hba.c
Kurt Roeckx <[EMAIL PROTECTED]> writes: > In hba.c, line 1394 is missing two ) Yeah, my fault :-(. Should be fixed as of a few hours ago --- do you see any others? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] tsearch2 for 7.3.X
On Wed, 23 Jul 2003, Christopher Kings-Lynne wrote: > > > I mean, the end user - the person entering the search string on the > website > > > won't know that syntax... > > > > You always could rewrite user query to that syntax. > > That's why I said it would be a cool feature! Saves every web designer from > writing the conversion code for their site - you just provide a function to > do it... feel free to write such conversion fuction and share with us. The problem is that there are many user oriented query languages and it's difficult to recognize which conversion funcion to use. > > Chris > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] php with postgres
Marcus B?rger wrote: > >> However it may be very usefull to terminate any open transaction before > >> reusing a persisten connection. Typically this happens when the same script > >> runs again. But anyway using transactions together with persistent conenctions > >> in a multithreaded environment isn't the best thing you could do. So our > >> options are > >> 1) tell the users to do 'auto commit mode' > >> 2) nested transactions > >> 3) locking > >> > >> >From my perspective 2) and 3) are bad ideas for the web environment. In other > >> words i guess we should leave it as is with transaction rollback only when the > >> client terminates (e.g. the webserver stops). > > BM> I don't see why you wouldn't just do BEGIN;COMMIT;RESET ALL; when you > BM> pass the connection to a new client. > > > Bruce you said RESET ALL is available since 7.2. I am currently checking for > the lib version but it would be more correct to check something on the server. > So the question what do i check? We usually use SELECT version(). -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] php with postgres
Christopher Kings-Lynne wrote: > > > DEBUG: InitPostgres > > > DEBUG: StartTransactionCommand > > > DEBUG: query: select getdatabaseencoding() > > > DEBUG: ProcessQuery > > > DEBUG: CommitTransactionCommand > > > DEBUG: StartTransactionCommand > > > DEBUG: query: RESET ALL > > > DEBUG: ProcessUtility: RESET ALL > > > DEBUG: CommitTransactionCommand > > > DEBUG: StartTransactionCommand > > > DEBUG: query: BEGIN;ROLLBACK; > > > DEBUG: ProcessUtility: BEGIN;ROLLBACK; > > > DEBUG: CommitTransactionCommand > > > DEBUG: StartTransactionCommand > > > DEBUG: ProcessUtility: BEGIN;ROLLBACK; > > > DEBUG: CommitTransactionCommand > > > DEBUG: pq_recvbuf: unexpected EOF on client connection > > > > > > > And this is the wrong order of things. The BEGIN;ROLLBACK; has to be > > done first, otherwise if the connection was left in an aborted open > > transaction by the previous script, the other two actions will fail. > > Surely PHP can be modified so as to use the new 3.0 protocol feature to > detect whether it's in a transaction or not, so as to avoid unnecssary > querying? Yes, you could, but it hardly seems worth it because they have to support old and new protocols. Eventually, yes, they could use that to eliminate the BEGIN;COMMIT. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] pg_conn not declared in libpq-fe.h?
Hauke Joachim Zuehl <[EMAIL PROTECTED]> writes: > I want to compile a program which uses libpq-fe.h but it seems that > structure pg_conn ist not declared. The typedef is named PGconn. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostgreSQL 7.3.3 and Intel C compiler
But the snapshots only are grabbing the xids from each proc, right? Doesn't seem that would take very long. If this is the bottleneck, maybe we need a shared proc lock. I had a hard day testing and verifying this kind of stuff. We have run several hundred benchmarks at the customer using many different settings. SERIALIZABLE was the key to high-performance. I have run dozens of different benchmarks today (cursors, simple selects, concurrent stuff, ...). I have not found a difference. I have no idea why the customer's system was so much faster in SERIALIZABLE mode. They use a native C++ implementation of the FE/BE protocol but as far as I have seen their database layer does not care about transaction isolation too much. I will continue testing this kind of stuff because this is a very strange yet important issue. I will try to get some code from the customer. This is mostly non-disclosure stuff so I am not sure what we can use. I just wanted to ask if somebody has a reasonable explanation and if somebody can verify this behaviour. Maybe we will find the reason some day :(. Sorry that I cannot provide more information at the moment. Regards, Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706; +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] php with postgres
Hello Bruce, Tuesday, July 22, 2003, 3:20:27 AM, you wrote: BM> Jan Wieck wrote: >> Bruce Momjian wrote: >> > Marcus B?rger wrote: >> >> BM> Marcus, would you check if PHP is using RESET ALL when passing >> >> BM> persistent connection to new clients? We added that capability a few >> >> BM> releases ago, specifically for PHP persistent connections, but I don't >> >> BM> think that ever got into the PHP code. >> >> >> >> Unfortunately we don't do so yet. Do i need to check for errors or can i do it >> >> unconditionally on conenction start? And i'd need to know how to check if it >> >> is available (like starting with which version). >> > >> > It first appeared in PostgreSQL version 7.2. It doesn't generate any >> > failures. It just resets all SET settting to their defaults, in case >> > the previous client modified them. >> > >> >> It does generate the usual error if the current transaction block is in >> ABORT state. So the correct querystring to send would be something like >> >> "ROLLBACK; RESET ALL" BM> Oh, I remember that now as part of the persistent connection code. As I BM> remember, we told them to do BEGIN;COMMIT; to clear any open transaction BM> state passed to the new client. Is that in there? If not, it has to be BM> added too. ROLLBACK will generate an error if you are not in a BM> transaction, so it would fill the logs with errors. Here's the current log while reusing the persistent connection: DEBUG: InitPostgres DEBUG: StartTransactionCommand DEBUG: query: select getdatabaseencoding() DEBUG: ProcessQuery DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: RESET ALL DEBUG: ProcessUtility: RESET ALL DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: BEGIN;ROLLBACK; DEBUG: ProcessUtility: BEGIN;ROLLBACK; DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: ProcessUtility: BEGIN;ROLLBACK; DEBUG: CommitTransactionCommand DEBUG: pq_recvbuf: unexpected EOF on client connection -- Best regards, Marcusmailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] php with postgres
Hello Jan, Tuesday, July 22, 2003, 1:28:18 PM, you wrote: JW> Marcus Börger wrote: >> >> Here's the current log while reusing the persistent connection: >> >> DEBUG: InitPostgres >> DEBUG: StartTransactionCommand >> DEBUG: query: select getdatabaseencoding() >> DEBUG: ProcessQuery >> DEBUG: CommitTransactionCommand >> DEBUG: StartTransactionCommand >> DEBUG: query: RESET ALL >> DEBUG: ProcessUtility: RESET ALL >> DEBUG: CommitTransactionCommand >> DEBUG: StartTransactionCommand >> DEBUG: query: BEGIN;ROLLBACK; >> DEBUG: ProcessUtility: BEGIN;ROLLBACK; >> DEBUG: CommitTransactionCommand >> DEBUG: StartTransactionCommand >> DEBUG: ProcessUtility: BEGIN;ROLLBACK; >> DEBUG: CommitTransactionCommand >> DEBUG: pq_recvbuf: unexpected EOF on client connection >> JW> And this is the wrong order of things. The BEGIN;ROLLBACK; has to be JW> done first, otherwise if the connection was left in an aborted open JW> transaction by the previous script, the other two actions will fail. Yeah well it seems i gave a wrong impression what is actually happening now. We don't handle any transaction at the moment but starting with the second call to pg_pconnect on the same db we do RESET ALL. The following log is from a three connections, so you can see two RESET ALL. DEBUG: BackendStartup: forked pid=28253 socket=8 LOG: query: select getdatabaseencoding() LOG: query: RESET ALL LOG: query: RESET ALL LOG: query: BEGIN;ROLLBACK; LOG: unexpected EOF on client connection However it may be very usefull to terminate any open transaction before reusing a persisten connection. Typically this happens when the same script runs again. But anyway using transactions together with persistent conenctions in a multithreaded environment isn't the best thing you could do. So our options are 1) tell the users to do 'auto commit mode' 2) nested transactions 3) locking >From my perspective 2) and 3) are bad ideas for the web environment. In other words i guess we should leave it as is with transaction rollback only when the client terminates (e.g. the webserver stops). Best regards, Marcusmailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [GENERAL] Checkpoints
I mean, what is getting buffered? What performance effects is it supposed allow? Nailah Ogeer wrote: Hello, I have written code to support multiple buffer pools in postgres 7.3.2. Now i am looking at changing the sizes of these buffer pools, but first i need to write all pages to disk. I also need to incorporate this code into the backend instead of it being a sql statement as it is now. I noticed that at a checkpoint, dirty pages are written to disk. I was just wondering if someone can tell me how postgres handles checkpoints and if it is advisable to do the sizing right after a checkpoint? Nailah MSc Student ---(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 ---(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] [GENERAL] Checkpoints
pool of what? Nailah Ogeer wrote: Hello, I have written code to support multiple buffer pools in postgres 7.3.2. Now i am looking at changing the sizes of these buffer pools, but first i need to write all pages to disk. I also need to incorporate this code into the backend instead of it being a sql statement as it is now. I noticed that at a checkpoint, dirty pages are written to disk. I was just wondering if someone can tell me how postgres handles checkpoints and if it is advisable to do the sizing right after a checkpoint? Nailah MSc Student ---(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 ---(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] php with postgres
Hello Bruce, Tuesday, July 22, 2003, 10:16:53 PM, you wrote: BM> Marcus B?rger wrote: >> Yeah well it seems i gave a wrong impression what is actually happening now. >> We don't handle any transaction at the moment but starting with the second >> call to pg_pconnect on the same db we do RESET ALL. The following log is from >> a three connections, so you can see two RESET ALL. >> >> DEBUG: BackendStartup: forked pid=28253 socket=8 >> LOG: query: select getdatabaseencoding() >> LOG: query: RESET ALL >> LOG: query: RESET ALL >> LOG: query: BEGIN;ROLLBACK; >> LOG: unexpected EOF on client connection >> >> However it may be very usefull to terminate any open transaction before >> reusing a persisten connection. Typically this happens when the same script >> runs again. But anyway using transactions together with persistent conenctions >> in a multithreaded environment isn't the best thing you could do. So our >> options are >> 1) tell the users to do 'auto commit mode' >> 2) nested transactions >> 3) locking >> >> >From my perspective 2) and 3) are bad ideas for the web environment. In other >> words i guess we should leave it as is with transaction rollback only when the >> client terminates (e.g. the webserver stops). BM> I don't see why you wouldn't just do BEGIN;COMMIT;RESET ALL; when you BM> pass the connection to a new client. Bruce you said RESET ALL is available since 7.2. I am currently checking for the lib version but it would be more correct to check something on the server. So the question what do i check? -- Best regards, Marcusmailto:[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] autocommit in 7.4
Hello Bruce, Tuesday, July 22, 2003, 11:26:32 PM, you wrote: BM> I see autocommit as implemented only in psql, not in libpq. Is that BM> what we want to do for 7.4? Autocommit with libpq could be a good idea for web applications, especially when not so expirienced users connect from a scripting language. They often have really no idea what a transaction is. -- Best regards, Marcusmailto:[EMAIL PROTECTED] ---(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
[HACKERS] DBD::Pg, schema support
Hi there, is't possible to specify default search path for dbh in DBD::Pg ? I don't want to set variable before every select. Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] php with postgres
Hello Jan, Tuesday, July 22, 2003, 10:57:56 PM, you wrote: JW> Bruce Momjian wrote: >> Marcus B?rger wrote: >>> However it may be very usefull to terminate any open transaction before >>> reusing a persisten connection. Typically this happens when the same script >>> runs again. But anyway using transactions together with persistent conenctions >>> in a multithreaded environment isn't the best thing you could do. So our >>> options are >>> 1) tell the users to do 'auto commit mode' >>> 2) nested transactions >>> 3) locking >>> >>> >From my perspective 2) and 3) are bad ideas for the web environment. In other >>> words i guess we should leave it as is with transaction rollback only when the >>> client terminates (e.g. the webserver stops). >> >> I don't see why you wouldn't just do BEGIN;COMMIT;RESET ALL; when you >> pass the connection to a new client. >> JW> Right, and I don't see why using transactions in a multithreaded JW> environment would be a bad idea. However an application is designed, one JW> logical unit of changes, called a business transaction, has to have one JW> database transaction modifying the business relevant information. JW> There could be other transactions involved for dialog handling and JW> advisory locking. So i'll update to "BEGIN;COMMIT;RESET ALL;" :-) -- Best regards, Marcusmailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] tsearch2 for 7.3.X
On Wed, 23 Jul 2003, Christopher Kings-Lynne wrote: > > > SELECT intindex, strtopic FROM tblmessages > > > WHERE idxfti @@ 'jam and > > > toast -guitar'::userquery::tsquery; > > > > idxfti @@ 'jam & toast & ! guitar' > > I mean, the end user - the person entering the search string on the website > won't know that syntax... You always could rewrite user query to that syntax. > > Chris > Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] PostgreSQL 7.3.3 and Intel C compiler
They do the backend protocol using a custom implementation. Why would they do that? It seems as if their implemenation provides 20% more throughput. I haven't benchmarked with lib pq personally so I cannot tell you more. Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706; +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] tsearch2 for 7.3.X
> > I mean, the end user - the person entering the search string on the website > > won't know that syntax... > > You always could rewrite user query to that syntax. That's why I said it would be a cool feature! Saves every web designer from writing the conversion code for their site - you just provide a function to do it... Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] reprise on Linux overcommit handling
Thanks. Interesting. Hard to imagine what they were thinking when they put this code in. --- Andrew Dunstan wrote: > The current developer docs say this: > > --- > Linux has poor default memory overcommit behavior. Rather than failing if it > can not reserve enough memory, it returns success, but later fails when the > memory can't be mapped and terminates the application with kill -9. To > prevent unpredictable process termination, use: > > sysctl -w vm.overcommit_memory=3 > - > > This would be true if the kernel being used had the paranoid mode compiled > in. This is not true, AFAICS, of either the stock 2.4 kernels nor of the > latest RH kernels. It is true of 2.4.21 *with* the -ac4 (and posibly earlier > -ac*) patch. In fact, Alan's patch apparently allows tuning of the amount of > overcommitting allowed. As I read the kernel source I got from RH today > (2.4.20-19.9), doing this will in fact make the kernel freely allow > overcommiting of memory, rather than it trying in a rather unsatisfactory > way to avoid it. IOW, with many kernels the advice would make things worse, > not better - e.g. the RH source says this in mm/mmap.c: > > if (sysctl_overcommit_memory) > return 1; > > > Rather than give bad advice, it might be better to advise users (1) to run > Pg on machines that are likely to be stable and not run into OOM situations, > and (2) to check with their vendors about proper overcommit handling. > > Personally, my advice would be to avoid Linux for mission critical apps > until this is fixed, but that's just my opinion, and I'm happily developing > on Linux, albeit for something that is not mission critical. > > cheers > > andrew > > > > > ---(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 > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] reprise on Linux overcommit handling
Andrew Dunstan wrote: Rather than give bad advice, it might be better to advise users (1) to run Pg on machines that are likely to be stable and not run into OOM situations, and (2) to check with their vendors about proper overcommit handling. Would it be possible (or trivial?) to write a small C program to test for memory overcommit behaviour? Might be useful to put in contrib, and mention it in the Admin docs. There are just too many Linux variants and settings to be sure of what exactly the memory overcommit policy is for a particular kernel and distribution. Linux 2.6 will apparently behave better. I guess they have learnt the lesson. :) http://kniggit.net/wwol26.html (Under "Other Improvements"). -- Linux homer 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386 GNU/Linux 10:30am up 209 days, 1:35, 5 users, load average: 5.08, 5.08, 5.08 pgp0.pgp Description: PGP signature
Re: [HACKERS] suggestions to improve postgresql suitability for data-mining
> II) SQL > --- > > The first idea is to ask SQL to do the job with a 'group by' clause: > > SELECT area, type, month, SUM(amount), COUNT(*) > FROM client AS c, invoice AS i > WHERE c.id=i.client > GROUP BY area, type, month; > > As I am just interested in reading the data, without any transaction, I > tuned a little bit the database parameters (fsync=false, more shared_mem > and sort_mem). > > It works, but it is quite slow and it requires a lot of disk space. > Indeed, the result of the join is big, and the aggregation seems to > require an external sort step so as to sum up data one group after the > other. > > As the resulting table is very small, I wish the optimizer would have > skipped the sort phase, so as to aggregate the data as they come after the > join. All may be done on the fly without much additionnal storage (well, > with some implementation efforts). Maybe it is the "hash evaluation of > group by aggregates" item listed in the todo list. As of 7.4CVS it does do this. You will find this much faster in 7.4 release. Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] pg_conn not declared in libpq-fe.h?
Hello :) I want to compile a program which uses libpq-fe.h but it seems that structure pg_conn ist not declared. I have SuSE 8.2 and postgresql-devel-7.3.2-42 Thanks for any hints :) Kind regards, Hauke -- Aus Ben Hur (1959): Wer nicht fuer Rom ist, ist gegen Rom George W. Bush (2002): Wer nicht fuer uns ist, ist gegen uns ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] table-level and row-level locks.
Iam trying to acquire rowlevel locks in postgresql. I try doing this: 'select * from students where name='Larry' for update; But by looking at the holding array of proclock , I've noticed that by doing this only AccessShareLock gets acquired which is a table level lock. How do I acquire rowlevelock and what fields of Lock or Proclock datastructures indicate it. Thanks JennyMSN 8 helps ELIMINATE E-MAIL VIRUSES. Get 2 months FREE*.
Re: [HACKERS] initdb fails: problem with array?
Tom Lane wrote: Andreas Pflug <[EMAIL PROTECTED]> writes: I just checked out (at the moment hba.c changed, so I had to redo it), make clean, make, pgsql stop, make install, pgsql start and it's still there. It works fine for me too. Try removing src/backend/parser/gram.c and src/backend/parser/parse.h to force bison to get re-run when you build. Thanks Tom, rerunning bison did the job. Regards, Andreas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] tsearch2 for 7.3.X
> > SELECT intindex, strtopic FROM tblmessages > > WHERE idxfti @@ 'jam and > > toast -guitar'::userquery::tsquery; > > idxfti @@ 'jam & toast & ! guitar' I mean, the end user - the person entering the search string on the website won't know that syntax... Chris ---(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] php with postgres
> > DEBUG: InitPostgres > > DEBUG: StartTransactionCommand > > DEBUG: query: select getdatabaseencoding() > > DEBUG: ProcessQuery > > DEBUG: CommitTransactionCommand > > DEBUG: StartTransactionCommand > > DEBUG: query: RESET ALL > > DEBUG: ProcessUtility: RESET ALL > > DEBUG: CommitTransactionCommand > > DEBUG: StartTransactionCommand > > DEBUG: query: BEGIN;ROLLBACK; > > DEBUG: ProcessUtility: BEGIN;ROLLBACK; > > DEBUG: CommitTransactionCommand > > DEBUG: StartTransactionCommand > > DEBUG: ProcessUtility: BEGIN;ROLLBACK; > > DEBUG: CommitTransactionCommand > > DEBUG: pq_recvbuf: unexpected EOF on client connection > > > > And this is the wrong order of things. The BEGIN;ROLLBACK; has to be > done first, otherwise if the connection was left in an aborted open > transaction by the previous script, the other two actions will fail. Surely PHP can be modified so as to use the new 3.0 protocol feature to detect whether it's in a transaction or not, so as to avoid unnecssary querying? Chris ---(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] initdb fails: problem with array?
Andreas Pflug <[EMAIL PROTECTED]> writes: > I just checked out (at the moment hba.c changed, so I had to redo it), > make clean, make, pgsql stop, make install, pgsql start and it's still > there. It works fine for me too. Try removing src/backend/parser/gram.c and src/backend/parser/parse.h to force bison to get re-run when you build. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] initdb fails: problem with array?
Joe Conway wrote: Andreas Pflug wrote: The current snapshot won't initdb, because running information_schema.sql fails. The two occurences of "WHERE u.usesysid = ANY( g.grolist )" are the problem. Replacing the ANY clause with some dummy will let everything run ok. select usename from pg_user, pg_group where usesysid = ANY (grolist) will yield ERROR: syntaxt error at or near "grolist", so I believe that the ANY(ARRAY) support is broken. I'm not seeing any problems with a fresh copy of cvs from 15 minutes after you posted. Are you still having the problem? I just checked out (at the moment hba.c changed, so I had to redo it), make clean, make, pgsql stop, make install, pgsql start and it's still there. Regards, Andreas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] compile failure in hba.c
In hba.c, line 1394 is missing two )
if (pass == NULL)
{
ereport(LOG,
(errmsg("local user with uid %d is not known to
getpwuid",
(int) peercred.uid);
^^
return false;
}
Kurt
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] initdb fails: problem with array?
Andreas Pflug wrote: The current snapshot won't initdb, because running information_schema.sql fails. The two occurences of "WHERE u.usesysid = ANY( g.grolist )" are the problem. Replacing the ANY clause with some dummy will let everything run ok. select usename from pg_user, pg_group where usesysid = ANY (grolist) will yield ERROR: syntaxt error at or near "grolist", so I believe that the ANY(ARRAY) support is broken. I'm not seeing any problems with a fresh copy of cvs from 15 minutes after you posted. Are you still having the problem? Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] php with postgres
Marcus B?rger wrote: > Hello Jan, > > Tuesday, July 22, 2003, 10:57:56 PM, you wrote: > > JW> Bruce Momjian wrote: > >> Marcus B?rger wrote: > >>> However it may be very usefull to terminate any open transaction before > >>> reusing a persisten connection. Typically this happens when the same script > >>> runs again. But anyway using transactions together with persistent conenctions > >>> in a multithreaded environment isn't the best thing you could do. So our > >>> options are > >>> 1) tell the users to do 'auto commit mode' > >>> 2) nested transactions > >>> 3) locking > >>> > >>> >From my perspective 2) and 3) are bad ideas for the web environment. In other > >>> words i guess we should leave it as is with transaction rollback only when the > >>> client terminates (e.g. the webserver stops). > >> > >> I don't see why you wouldn't just do BEGIN;COMMIT;RESET ALL; when you > >> pass the connection to a new client. > >> > > JW> Right, and I don't see why using transactions in a multithreaded > JW> environment would be a bad idea. However an application is designed, one > JW> logical unit of changes, called a business transaction, has to have one > JW> database transaction modifying the business relevant information. > JW> There could be other transactions involved for dialog handling and > JW> advisory locking. > > > So i'll update to "BEGIN;COMMIT;RESET ALL;" :-) Yes, I think that is it. The RESET ALL has to be at the end because you have to make sure the RESET is in a valid transaction. The only way to make sure autocommit doesn't effect you would be to do: BEGIN;COMMIT;BEGIN;RESET ALL;COMMIT; but the autocommit GUC option was only in 7.3 and will not be in 7.4, so this seems like overkill. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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
[HACKERS] reprise on Linux overcommit handling
The current developer docs say this: --- Linux has poor default memory overcommit behavior. Rather than failing if it can not reserve enough memory, it returns success, but later fails when the memory can't be mapped and terminates the application with kill -9. To prevent unpredictable process termination, use: sysctl -w vm.overcommit_memory=3 - This would be true if the kernel being used had the paranoid mode compiled in. This is not true, AFAICS, of either the stock 2.4 kernels nor of the latest RH kernels. It is true of 2.4.21 *with* the -ac4 (and posibly earlier -ac*) patch. In fact, Alan's patch apparently allows tuning of the amount of overcommitting allowed. As I read the kernel source I got from RH today (2.4.20-19.9), doing this will in fact make the kernel freely allow overcommiting of memory, rather than it trying in a rather unsatisfactory way to avoid it. IOW, with many kernels the advice would make things worse, not better - e.g. the RH source says this in mm/mmap.c: if (sysctl_overcommit_memory) return 1; Rather than give bad advice, it might be better to advise users (1) to run Pg on machines that are likely to be stable and not run into OOM situations, and (2) to check with their vendors about proper overcommit handling. Personally, my advice would be to avoid Linux for mission critical apps until this is fixed, but that's just my opinion, and I'm happily developing on Linux, albeit for something that is not mission critical. cheers andrew ---(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
[HACKERS] autocommit in 7.4
I see autocommit as implemented only in psql, not in libpq. Is that what we want to do for 7.4? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] did you read my mails ?
> This allows functions to read/write operating system files. Is this of > interest to anyone? Is this something that requires untrusted status, as it would if I wrote a function in perl to do the same thing? -- Mike Nolan ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] php with postgres
Bruce Momjian wrote: Marcus B?rger wrote: However it may be very usefull to terminate any open transaction before reusing a persisten connection. Typically this happens when the same script runs again. But anyway using transactions together with persistent conenctions in a multithreaded environment isn't the best thing you could do. So our options are 1) tell the users to do 'auto commit mode' 2) nested transactions 3) locking >From my perspective 2) and 3) are bad ideas for the web environment. In other words i guess we should leave it as is with transaction rollback only when the client terminates (e.g. the webserver stops). I don't see why you wouldn't just do BEGIN;COMMIT;RESET ALL; when you pass the connection to a new client. Right, and I don't see why using transactions in a multithreaded environment would be a bad idea. However an application is designed, one logical unit of changes, called a business transaction, has to have one database transaction modifying the business relevant information. There could be other transactions involved for dialog handling and advisory locking. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] did you read my mails ?
This allows functions to read/write operating system files. Is this of interest to anyone? --- ivan wrote: > > if you want to see this , there is the src : > http://www.psycho.pl/public/src/pgsql/files.tar.bz2 > > and sample > > On Mon, 21 Jul 2003, Bruce Momjian wrote: > > > > > I haven't seen those myself. > > > > --- > > > > ivan wrote: > > > > > > functions to open,read,write etc files > > > > > > On Mon, 21 Jul 2003, Bruce Momjian wrote: > > > > > > > > > > > What functions are they? > > > > > > > > --- > > > > > > > > ivan wrote: > > > > > > > > > > someone looked at my files function ?? > > > > > > > > > > > > > > > > > > > > ---(end of broadcast)--- > > > > > TIP 7: don't forget to increase your free space map settings > > > > > > > > > > > > > -- > > > > Bruce Momjian| http://candle.pha.pa.us > > > > [EMAIL PROTECTED] | (610) 359-1001 > > > > + If your life is a hard drive, | 13 Roberts Road > > > > + Christ can be your backup.| Newtown Square, Pennsylvania 19073 > > > > > > > > ---(end of broadcast)--- > > > > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > > > > > > > > > > > -- > > Bruce Momjian| http://candle.pha.pa.us > > [EMAIL PROTECTED] | (610) 359-1001 > > + If your life is a hard drive, | 13 Roberts Road > > + Christ can be your backup.| Newtown Square, Pennsylvania 19073 > > > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Why select * from function doesn't work when function
On Tue, 22 Jul 2003, Francisco Figueiredo Jr. wrote: > > How's this for an alternative if you really don't want any rows returned: > > > > create function fincF ( ) returns setof integer as ' > > begin > >delete from blah; > >return; > > end; > > ' language 'plpgsql'; > > > > > > This works, but what I really want is not to return any rows. I mean, > the problem is not return null, but the error I get if I select * from > voidfunction. > > I just wanted void functions behave like others when called as select * > from voidfunction So I dont have to do select voidfunction. :) But that last does exactly that. Doesn't even return a null. Give it a quick go, skip the delete statement obviously, and see. You'll get something like: ? --- (0 rows) -- Nigel J. Andrews ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] new idea for COPY and MOVE
hi what do you tink about : COPY [ namespace1. ] table [ STRUCT ONLY ] TO [namespace2.] table2; MOVE [ namepsace1. ] table TO [namespace2.] table2; and this same whit types, functions etc and : ALERT FUNCTION func (ARGS) SET OWNER new_owner; and : CREATE TYPE new_type AS table; this can by replaced by LIKE : CREATE TYPE new_type AS ( LIKE table ) what i saw in one mail on this maillist can be ? bye iv ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Why select * from function doesn't work when function
Nigel J. Andrews wrote: On Tue, 22 Jul 2003, Francisco Figueiredo Jr. wrote: select * from funcF(); Yeap, it works, but you specified integer as the return type :) Yes, that's because I knew the void wouldn't work. :] :) How's this for an alternative if you really don't want any rows returned: create function fincF ( ) returns setof integer as ' begin delete from blah; return; end; ' language 'plpgsql'; This works, but what I really want is not to return any rows. I mean, the problem is not return null, but the error I get if I select * from voidfunction. I just wanted void functions behave like others when called as select * from voidfunction So I dont have to do select voidfunction. :) -- Regards, Francisco Figueiredo Jr. -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Is Patch Ok for deferred trigger disk queue?
I assume this will not be completed for 7.4. I will keep the emails for 7.5. One idea I had was to use the existing sort_mem parameter to control when to force the deferred trigger queue to disk --- it doesn't have anything to do with sorting, but it does have the same purpose, to force thing to disk when we consume enough RAM. --- Bruce Momjian wrote: > > Stuart, were are on this patch? Seems we need GUC additions, though I > can do that for you, and changes to write the head to disk. Was that > completed? > > --- > > Stuart wrote: > > Tom Lane wrote: > > > > > Stephan Szabo <[EMAIL PROTECTED]> writes: > > > > > >>As a side question, it looks to me that the code stores the first trigger > > >>records in memory and then after some point starts storing all new records > > >>on disk. Is this correct? I'd wonder if that's really what you want in > > >>general, since I'd think that the earliest ones are the ones you're least > > >>likely to need until end of transaction (or set constraints in the fk > > >>case) whereas the most recent ones are possibly going to be immediate > > >>triggers which you're going to need as soon as the statement is done. > > > > > > > > > Good point. It would be better to push out stuff from the head of the > > > queue, hoping that stuff near the end might never need to be written > > > at all. > > > > > > regards, tom lane > > Hmmm I see your point. I will change the patch to write the head to > > disk and reenter when the development branch splits off. > > Also I've noticed that there is an fd.h which has file routines which I > > should be using rather than the stdio routines. > > I will also clean up those errors. > > Thank you, > > - Stuart > > > > > > > > ---(end of broadcast)--- > > TIP 6: Have you searched our list archives? > > > >http://archives.postgresql.org > > > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup.| Newtown Square, Pennsylvania 19073 > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] did you read my mails ?
if you want to see this , there is the src : http://www.psycho.pl/public/src/pgsql/files.tar.bz2 and sample On Mon, 21 Jul 2003, Bruce Momjian wrote: > > I haven't seen those myself. > > --- > > ivan wrote: > > > > functions to open,read,write etc files > > > > On Mon, 21 Jul 2003, Bruce Momjian wrote: > > > > > > > > What functions are they? > > > > > > --- > > > > > > ivan wrote: > > > > > > > > someone looked at my files function ?? > > > > > > > > > > > > > > > > ---(end of broadcast)--- > > > > TIP 7: don't forget to increase your free space map settings > > > > > > > > > > -- > > > Bruce Momjian| http://candle.pha.pa.us > > > [EMAIL PROTECTED] | (610) 359-1001 > > > + If your life is a hard drive, | 13 Roberts Road > > > + Christ can be your backup.| Newtown Square, Pennsylvania 19073 > > > > > > ---(end of broadcast)--- > > > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > > > > > > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup.| Newtown Square, Pennsylvania 19073 > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Why select * from function doesn't work when function
On Tue, 22 Jul 2003, Francisco Figueiredo Jr. wrote: > > select * from funcF(); > > > > Yeap, it works, but you specified integer as the return type :) Yes, that's because I knew the void wouldn't work. :] > > I'd like to have the return type as void and be possible to call it with > select * from funcF(); I don't believe it is possible. Makes sense since void doesn't really make sense in that position in the statment. How's this for an alternative if you really don't want any rows returned: create function fincF ( ) returns setof integer as ' begin delete from blah; return; end; ' language 'plpgsql'; -- Nigel J. Andrews ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PostgreSQL 7.3.3 and Intel C compiler
Well, it seems it would be easier for them to just improve our existing libpq and send us the patches, rather than rewriting it from scratch. --- Hans-Jürgen Schönig wrote: > > They do the backend protocol using a custom implementation. Why would > > they do that? > > > > > It seems as if their implemenation provides 20% more throughput. I > haven't benchmarked with lib pq personally so I cannot tell you more. > > > Hans > > > -- > Cybertec Geschwinde u Schoenig > Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria > Tel: +43/2952/30706; +43/664/233 90 75 > www.cybertec.at, www.postgresql.at, kernel.cybertec.at > > > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] php with postgres
Marcus B?rger wrote: > Yeah well it seems i gave a wrong impression what is actually happening now. > We don't handle any transaction at the moment but starting with the second > call to pg_pconnect on the same db we do RESET ALL. The following log is from > a three connections, so you can see two RESET ALL. > > DEBUG: BackendStartup: forked pid=28253 socket=8 > LOG: query: select getdatabaseencoding() > LOG: query: RESET ALL > LOG: query: RESET ALL > LOG: query: BEGIN;ROLLBACK; > LOG: unexpected EOF on client connection > > However it may be very usefull to terminate any open transaction before > reusing a persisten connection. Typically this happens when the same script > runs again. But anyway using transactions together with persistent conenctions > in a multithreaded environment isn't the best thing you could do. So our > options are > 1) tell the users to do 'auto commit mode' > 2) nested transactions > 3) locking > > >From my perspective 2) and 3) are bad ideas for the web environment. In other > words i guess we should leave it as is with transaction rollback only when the > client terminates (e.g. the webserver stops). I don't see why you wouldn't just do BEGIN;COMMIT;RESET ALL; when you pass the connection to a new client. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] Criteria for contrib/ versus gborg?
On Tue, Jul 22, 2003 at 04:05:46PM -0300, The Hermit Hacker wrote: > > Did I miss part of a thread here? :) I don't think so. Josh was responding to my post, id 19f22n-0007Gm-00. A -- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada <[EMAIL PROTECTED]> M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Why select * from function doesn't work when function
Nigel J. Andrews wrote: Try returning an integer but returning a null for that integer...on the other hand I see you're using sql as the language and I don't know how that would work. I tried that and it works. I changed the function body to do a query which returns null. The problem only appears if the return type is void. Have you looked at plpgsql? Perhaps that is acceptable for you, in which case: create function funcF ( ) returns integer as ' begin delete from blah; return null; end; ' as language 'plpgsql'; select * from funcF(); Yeap, it works, but you specified integer as the return type :) I'd like to have the return type as void and be possible to call it with select * from funcF(); Thanks Nigel. -- Regards, Francisco Figueiredo Jr. -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Why select * from function doesn't work when function
On Tue, 22 Jul 2003, Francisco Figueiredo Jr. wrote: > > Hi all, > > I would like to know why does calling a function with select * from > function doesn't work when its return type is set to void. > > I'm asking this because I have a code which uses this syntax to add > support for returning resultsets from functions. This way, regardless > the function returns a resultset or a single value, I could do select * > from function and it works very well. > > The problem appears when the function has its returns type to void. > I get the following error message: > > npgsql_tests=> select * from funcF(); > ERROR: function funcf() in FROM has unsupported return type > ERROR: function funcf() in FROM has unsupported return type > > > where funcF is defined as: > > npgsql_tests=> create function funcF() returns void as 'delete from > tablea where field_serial > 5' language 'sql'; > > CREATE FUNCTION > > But it does work if I call it as: > > select funcF(); > > > > I'd like to know if would be possible to change this behaviour to return > an empty result set with a null value. This way, there would be > consistency in calling all functions regardless of its return type with > select * from function. Try returning an integer but returning a null for that integer...on the other hand I see you're using sql as the language and I don't know how that would work. Have you looked at plpgsql? Perhaps that is acceptable for you, in which case: create function funcF ( ) returns integer as ' begin delete from blah; return null; end; ' as language 'plpgsql'; select * from funcF(); I believe that would work but don't quote me :) -- Nigel J. Andrews ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] suggestions to improve postgresql suitability for
On Tue, 22 Jul 2003, Fabien COELHO wrote: > ... > > III) PL/pgSQL > - > > Ok, if postgresql does not want to do it my way, let us make it do it. > Thus I wrote some PL/pgSQL function for my purpose, something like: > > CREATE TEMPORARY TABLE tmp ( > area INTEGER, > type INTEGER, > month INTEGER, > amount INTEGER, > count INTEGER, > PRIMARY KEY(area, type, month) > ); > -- initialize tmp > FOR i IN 0..99 LOOP FOR j IN 0..3 LOOP FOR k IN 0..11 LOOP > INSERT INTO tmp VALUES(i,j,k,0,0); > END all LOOPs; > -- fill tmp > FOR tuple IN > SELECT area, type, month, amount FROM client, invoice WHERE id=client > LOOP > UPDATE tmp SET amount=amount+tuple.amount, count=count+1 > WHERE area=tuple.area AND type=tuple.type AND month=tuple.month > END LOOP; > ... > > It is very SLOOOW... 10 to 100 times slower than the > previous one. Exit PL/pgSQL. It will be, first you're doing the same join that generates the large result set you were complaining about in the plain SQL example and then you're looping over it generating a delete/insert for every tuple in that result set. > > IV) Basic client side (JDBC, DBI, libpq) > > > Then I wrote the same stuff on the client side in java with JDBC, perl > with DBI and C with libpq, by browsing the above SELECT in a simple > loop and aggregating the data directly in the language. In all 3 > cases, the process attempts to allocate the full result of the client > and invoice join in memory... a **very** bad idea indeed! But what about doing that in the server? > I checked that the postgres client-server protocol does not allow to > chunk the result of a select, as only one response is sent for one > query. > > I suggest that this behavior should be changed, as the ODBC/DBI/JDBC > interfaces are designed to allow the client to process data as the > come out of the database, even if the query is not finished yet. > > The library should do the chunking on its own automatically, either by > doing a CURSOR/FETCH's manually in the library implementation on > SELECT, or by changing the protocol so that results are sent by chunks > when required. > > This is listed in the todo list of the JDBC interface, but there is > nothing about the perl interface nor the libpq interface. > > > V) Less basic client side (DBI, libpq) > -- > > I've redone the previous stuff, but with an explicit CURSOR and a > FETCH loop. It worked better, but it is still slow and still requires > a lot of disk space. Indeed, the database seems to first generate the > join in a temporary table on disk (I need twice as much disk space > available as the original base), which is then sent back to the client. > Thus I pay a read/write/read of the whole tables although > I had hoped that reading the data only once would have been enough. > > I would suggest to make processing data on the fly be done really > on the fly, not with an intermediate storage and providing just > an on-the-fly interface without the real thing behind. I haven't seen > any item in the todo list about this issue. I'm not sure it is really > easy to implement. I thought it necessary for the result set to be generated before any data can be returned, in the general case and in your grouped by example specifically. The latter if only because if you're not using the hash aggregates then the sort is required and that of course requires all the result data to be known. > > Conclusion > -- > > I have not succeeded in getting from postgresql the performances > I was expecting for data-mining. > > I could get them if postgresql could be improved on some or all > of the following items: > > (1) the execution engine may aggregate grouped data without a sort in > some cases. As other's have said, this is in 7.4 > (2) the PL/pgSQL interpreter would be a great deal faster. It did what you told it to do. > > (3) the client programming interfaces would provide a real on-the-fly > (without intermediate storage) fetching mecanism. > > (4) Also, I noticed that temporary tables/indexes created by postgresql > when processing a request are stored in the same partition as the > database in use. What about "/tmp" or other partitions? Maybe > a set of other directories could be designated for this purpose? > > Hope this help... at least to add new items to the postgresql todo list;-) > > Have a nice day, > > -- Nigel J. Andrews ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Why select * from function doesn't work when function has returntype void??
Hi all, I would like to know why does calling a function with select * from function doesn't work when its return type is set to void. I'm asking this because I have a code which uses this syntax to add support for returning resultsets from functions. This way, regardless the function returns a resultset or a single value, I could do select * from function and it works very well. The problem appears when the function has its returns type to void. I get the following error message: npgsql_tests=> select * from funcF(); ERROR: function funcf() in FROM has unsupported return type ERROR: function funcf() in FROM has unsupported return type where funcF is defined as: npgsql_tests=> create function funcF() returns void as 'delete from tablea where field_serial > 5' language 'sql'; CREATE FUNCTION But it does work if I call it as: select funcF(); I'd like to know if would be possible to change this behaviour to return an empty result set with a null value. This way, there would be consistency in calling all functions regardless of its return type with select * from function. Thanks in advance. -- Regards, Francisco Figueiredo Jr. -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Criteria for contrib/ versus gborg?
Did I miss part of a thread here? :) On Tue, 22 Jul 2003, Josh Berkus wrote: > Andrew, > > > Arguments? None of those three address the obvious marketing benefit > > of having replication shipping with the main tarball, I know. > > Those are pretty strong arguments ... and we can't let PostgreSQL new > "marketing awareness" sway us to the point that we start making technically > unfeasable decisions. So, I'm backing down ... I think despite the PR > argument in favor of including eRServer as a download is seriously outweighed > by Andrew's technical argument. > > So that we can get some PR milage out of it though, can we make sure that > eRServer gets posted to GBorg about the same day as the 7.4 release? That > way we can at least include eRServer in our announcement. > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: [EMAIL PROTECTED] secondary: [EMAIL PROTECTED]|postgresql}.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] php with postgres
Yes, but you probably want old clients to be able to use the new nested transaction code. --- Rod Taylor wrote: -- Start of PGP signed section. > > > Won't that break when we have nested transactions implemented? i.e. > > > begin;commit; would just open a sub transaction and have no effect on the > > > outer transaction... > > > > Yes, it would break. I am not sure how we are going to flag that we > > want to rollback all nested transactions, maybe ROLLBACK ALL. > > Shouldn't the results of PQtransactionStatus() override any 'pre-canned' > guess about how to abort a potential transaction since you know the > exact state of the protocol? > > If PQprotocolVersion() == 2 then do things the old way (always begin / > rollback). > > If either of the above functions are not present (pre-7.4 version of > PostgreSQL) then always begin / rollback. -- End of PGP section, PGP failed! -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Criteria for contrib/ versus gborg?
Andrew, > Arguments? None of those three address the obvious marketing benefit > of having replication shipping with the main tarball, I know. Those are pretty strong arguments ... and we can't let PostgreSQL new "marketing awareness" sway us to the point that we start making technically unfeasable decisions. So, I'm backing down ... I think despite the PR argument in favor of including eRServer as a download is seriously outweighed by Andrew's technical argument. So that we can get some PR milage out of it though, can we make sure that eRServer gets posted to GBorg about the same day as the 7.4 release? That way we can at least include eRServer in our announcement. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Criteria for contrib/ versus gborg?
On Tue, Jul 15, 2003 at 04:19:34PM -0400, Andrew Sullivan wrote: > I thought that now would be a good time to ask whether it should > live as a separate project, or whether it should be in contrib. I I have run into a number of arguments for putting the project on gborg. Unless I hear strong arguments against that option, I think I'll set up a project on gborg. Here are the arguments I've got: 1. The code as it stands is not really a full replication system, but more a toolkit. We at Liberty had a whole bunch of bespoke stuff that we used with the eRServer code in order to make it useful to us (various scripts that ran outside, for instance). That can't be released, because (a) it doesn't help anyone else and (b) it might reveal something about our internal databases (which would get me in Big Heck). 2. Building the code is a pain, because it is real sensitive to JDK versions, Ant installations, &c. As a result, the current CVS tree actually has a specific version of ant in it; I'm sure I don't have time to revisit the build scripts even if I knew what to do. So we have to ship all this ancillary code around too, and that will bloat the tarball and possibly cause a lot of additional support requests. 3. The code as it stands works with everything back to 7.2, and it'd be a shame to make people download a whole postgres tarball just to get the replication code. Arguments? None of those three address the obvious marketing benefit of having replication shipping with the main tarball, I know. I have most of a basic installation doc written, and a FAQ of gotchas and "why doesn't this work" answers. I'll be delivering that as soon as it's done to PostgresSQL, Inc; I hope in the next couple days. A -- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada <[EMAIL PROTECTED]> M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS]
Peter Eisentraut wrote: > I've been thinking some about how exactly to provide the new option of > thread-safe clients (libpq, ecpg). Let me state the following goals: > > a. Thread-safeness, where it makes a difference, is generally thought to > be a performance hit, so the user needs to have a choice to use > thread-safe libraries or not. OK. > b. The user needs to be able to make the choice at the time he builds his > application, *not* at the time the PostgreSQL distribution is built or > installed. Agreed. > Clearly, a thread-safe ecpg library is always going to be significantly > different from the "normal" one, with all the mutex things that get pulled > in, so it seems reasonable to always offer a libecpg_r alongside the > libecpg. True. And we have lots of platforms that we don't know how to enable thread-safeness. > The question is whether a libpq_r should be provided if libpq is > thread-safe by default (no *_r functions, libc_r, or special flags). I > think yes. It could be a symlink, so it doesn't really waste space. But > it would convenience users: Those who want to be sure to always link > against a thread-safe version can point to libpq_r and don't have to > create complicated detection mechanisms. Those who know that their system > is thread-safe by default can simply use libpq to follow that convention. > And of course it creates consistency with libecpg_r and does not bother > the user with complicated internal artifacts. I think adding a libpq_r on a platform that doesn't use *_r libraries is just too confusing. > A final note on the name of the configure option, --with-threads. First, > it does not control an external package but an internal feature, so it > should be --enable-. Secondly, it does not use threads, only enable > thread-safeness. So --enable-thread-safe might be a better name. Or if > you want to be more precise, --enable-thread-safe-client. The latter is > what MySQL uses, in case anyone cares about that. Peter, you are good with these subtle distinctions. How about --enable-thread-safeness? I have an idea. Seems we have two issues, libpq and ecpg. For ecpg, we need a command-line flag to specify if we want threading in the application. For libpq and libpecpg, we will just produce whatever therading library should be created on that platform, meaning either just one library, or one with _r and one without. So we tell them, if you want the ability to do threaded libpq and ecpg, use the --enable-thread-safeness configure flag. If it succeeds, it means we know how to do it for that operating system, and you get threading capability. If it fails, you don't have threading. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] PostgreSQL 7.3.3 and Intel C compiler
Bruce Momjian <[EMAIL PROTECTED]> writes: > But the snapshots only are grabbing the xids from each proc, right? > Doesn't seem that would take very long. Yeah, we've never seen any previous indication that grabbing a snapshot is a bottleneck. Also, I think it uses a shared (read) lock, so there shouldn't really be contention, unless perhaps they are starting and stopping backends very frequently as well. > If this is the bottleneck, maybe we need a shared proc lock. We need to understand what's happening before speculating about how to fix it ... 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
[HACKERS] Checkpoints
Hello, I have written code to support multiple buffer pools in postgres 7.3.2. Now i am looking at changing the sizes of these buffer pools, but first i need to write all pages to disk. I also need to incorporate this code into the backend instead of it being a sql statement as it is now. I noticed that at a checkpoint, dirty pages are written to disk. I was just wondering if someone can tell me how postgres handles checkpoints and if it is advisable to do the sizing right after a checkpoint? Nailah MSc Student ---(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] php with postgres
> > Won't that break when we have nested transactions implemented? i.e. > > begin;commit; would just open a sub transaction and have no effect on the > > outer transaction... > > Yes, it would break. I am not sure how we are going to flag that we > want to rollback all nested transactions, maybe ROLLBACK ALL. Shouldn't the results of PQtransactionStatus() override any 'pre-canned' guess about how to abort a potential transaction since you know the exact state of the protocol? If PQprotocolVersion() == 2 then do things the old way (always begin / rollback). If either of the above functions are not present (pre-7.4 version of PostgreSQL) then always begin / rollback. signature.asc Description: This is a digitally signed message part
Re: [HACKERS] suggestions to improve postgresql suitability for data-mining
> You want to process all invoices to count them > and to sum up the amounts on a per month/area/type > basis. The initial data size is in GB, but the > size of the expected result is in KB (namely 2 data > for each 100 areas * 12 months * 4 types). The key to handling large datasets for data mining is pre-aggregation based on the smallest time frame needed for details. I'd suggest running these large queries and storing the results in other tables, and then writing a set of functions to work with those aggregate tables. No sense in summing up the same set of static data more than once if you can help it. Darren ---(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] suggestions to improve postgresql suitability for data-mining
On Tue, Jul 22, 2003 at 18:39:33 +0200, Fabien COELHO <[EMAIL PROTECTED]> wrote: > > As the resulting table is very small, I wish the optimizer would have > skipped the sort phase, so as to aggregate the data as they come after the > join. All may be done on the fly without much additionnal storage (well, > with some implementation efforts). Maybe it is the "hash evaluation of > group by aggregates" item listed in the todo list. Yes the hash aggregate addition may help you. This has been implemented in 7.4 which will be in beta shortly. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] php with postgres
Thomas Swan wrote: > >>Oh, I remember that now as part of the persistent connection code. As I > >>remember, we told them to do BEGIN;COMMIT; to clear any open transaction > >>state passed to the new client. Is that in there? If not, it has to be > >>added too. ROLLBACK will generate an error if you are not in a > >>transaction, so it would fill the logs with errors. > >> > >> > > > >Won't that break when we have nested transactions implemented? i.e. > >begin;commit; would just open a sub transaction and have no effect on the > >outer transaction... > > > > > I was just about to mention that one. Perhaps a ROLLBACK ALL would be > of benefit to allow a clean state and start to work again. > > Doesn't autocommit behavior affect this as well? No, autocommit is now in the clients, but even if it was in the server, BEGIN;COMMIT would be OK. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] initdb fails: problem with array?
The current snapshot won't initdb, because running information_schema.sql fails. The two occurences of "WHERE u.usesysid = ANY( g.grolist )" are the problem. Replacing the ANY clause with some dummy will let everything run ok. select usename from pg_user, pg_group where usesysid = ANY (grolist) will yield ERROR: syntaxt error at or near "grolist", so I believe that the ANY(ARRAY) support is broken. Regards, Andreas ---(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] php with postgres
On 7/22/2003 11:18 AM, scott.marlowe wrote: >On Mon, 21 Jul 2003, Bruce Momjian wrote: > > > >>Jan Wieck wrote: >> >> >>>Bruce Momjian wrote: >>> >>> Marcus B?rger wrote: >BM> Marcus, would you check if PHP is using RESET ALL when passing >BM> persistent connection to new clients? We added that capability a few >BM> releases ago, specifically for PHP persistent connections, but I don't >BM> think that ever got into the PHP code. > >Unfortunately we don't do so yet. Do i need to check for errors or can i do it >unconditionally on conenction start? And i'd need to know how to check if it >is available (like starting with which version). > > It first appeared in PostgreSQL version 7.2. It doesn't generate any failures. It just resets all SET settting to their defaults, in case the previous client modified them. >>>It does generate the usual error if the current transaction block is in >>>ABORT state. So the correct querystring to send would be something like >>> >>> "ROLLBACK; RESET ALL" >>> >>> >>Oh, I remember that now as part of the persistent connection code. As I >>remember, we told them to do BEGIN;COMMIT; to clear any open transaction >>state passed to the new client. Is that in there? If not, it has to be >>added too. ROLLBACK will generate an error if you are not in a >>transaction, so it would fill the logs with errors. >> >> > >Won't that break when we have nested transactions implemented? i.e. >begin;commit; would just open a sub transaction and have no effect on the >outer transaction... > > I was just about to mention that one. Perhaps a ROLLBACK ALL would be of benefit to allow a clean state and start to work again. Doesn't autocommit behavior affect this as well? > >---(end of broadcast)--- >TIP 4: Don't 'kill -9' the postmaster > > ---(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
[HACKERS] Beta date on Monday?
FYI, we are currently scheduled for beta on Monday. Tom and I are both still cleaning up things. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] dblink_ora - a first shot on Oracle ...
Josh Berkus wrote: > Hans, Bruce, > > We're drafting the press release for 7.4 right now. What's the odds that > dblink_ora will be ready in time for 7.4? There seems to be some disagreement on whether the Oracle lib checks should be in configure for a /contrib module, and I don't know how far Hans is. I will say we are probably looking at 7/28 for beta. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] PostgreSQL 7.3.3 and Intel C compiler
Hans-Jürgen Schönig wrote: > > But the snapshots only are grabbing the xids from each proc, right? > > Doesn't seem that would take very long. > > > > If this is the bottleneck, maybe we need a shared proc lock. > > > > > I had a hard day testing and verifying this kind of stuff. We have run > several hundred benchmarks at the customer using many different > settings. SERIALIZABLE was the key to high-performance. I have run > dozens of different benchmarks today (cursors, simple selects, > concurrent stuff, ...). I have not found a difference. I have no idea > why the customer's system was so much faster in SERIALIZABLE mode. They > use a native C++ implementation of the FE/BE protocol but as far as I > have seen their database layer does not care about transaction isolation > too much. They do the backend protocol using a custom implementation. Why would they do that? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] threads.c
Peter Eisentraut wrote: > I have a few problems with the file threads.c: > > 1. It doesn't contain any threads, so the name is slightly inappropriate. > > 2. It's not a porting module (since pqStrerror, etc. are not system > functions), so it doesn't belong into src/port. > > 3. It's only used by libpq, so why not put it into the libpq directory? Tom suggested I move it to port because other interfaces might need it, and in fact if they do, they will need to call those functions. I can rename it to 'threaded', or move it back to libpq. Votes? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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
[HACKERS] suggestions to improve postgresql suitability for data-mining
Dear PostgreSQL developers, I have considered postgresql for data-mining, and I would like to share some comments on the issues involved, that result in several specific suggestions for improvements in various areas. I've notice that some of the issues seems listed in the todo list, so it may be an encouragement to implement them earlier;-) I) Data-Mining -- First, I call data-mining the bulk processing of a large amount of data to compute some small summary for analysis. Let us say you have a "client" table with some data about 10M clients and an "invoice" table with 120M invoices for these clients. It may look like this (I put INTEGER everywhere to simplify): CREATE TABLE client ( id INTEGER PRIMARY KEY, area INTEGER, -- from 0 to 99 type INTEGER -- from 0 to 3 ); CREATE TABLE invoice ( client INTEGER REFERENCES client, amount INTEGER, -- how much money was asked for month INTEGER -- the month the invoice was sent, from 0 to 11 ); You want to process all invoices to count them and to sum up the amounts on a per month/area/type basis. The initial data size is in GB, but the size of the expected result is in KB (namely 2 data for each 100 areas * 12 months * 4 types). II) SQL --- The first idea is to ask SQL to do the job with a 'group by' clause: SELECT area, type, month, SUM(amount), COUNT(*) FROM client AS c, invoice AS i WHERE c.id=i.client GROUP BY area, type, month; As I am just interested in reading the data, without any transaction, I tuned a little bit the database parameters (fsync=false, more shared_mem and sort_mem). It works, but it is quite slow and it requires a lot of disk space. Indeed, the result of the join is big, and the aggregation seems to require an external sort step so as to sum up data one group after the other. As the resulting table is very small, I wish the optimizer would have skipped the sort phase, so as to aggregate the data as they come after the join. All may be done on the fly without much additionnal storage (well, with some implementation efforts). Maybe it is the "hash evaluation of group by aggregates" item listed in the todo list. III) PL/pgSQL - Ok, if postgresql does not want to do it my way, let us make it do it. Thus I wrote some PL/pgSQL function for my purpose, something like: CREATE TEMPORARY TABLE tmp ( area INTEGER, type INTEGER, month INTEGER, amount INTEGER, count INTEGER, PRIMARY KEY(area, type, month) ); -- initialize tmp FOR i IN 0..99 LOOP FOR j IN 0..3 LOOP FOR k IN 0..11 LOOP INSERT INTO tmp VALUES(i,j,k,0,0); END all LOOPs; -- fill tmp FOR tuple IN SELECT area, type, month, amount FROM client, invoice WHERE id=client LOOP UPDATE tmp SET amount=amount+tuple.amount, count=count+1 WHERE area=tuple.area AND type=tuple.type AND month=tuple.month END LOOP; ... It is very SLOOOW... 10 to 100 times slower than the previous one. Exit PL/pgSQL. IV) Basic client side (JDBC, DBI, libpq) Then I wrote the same stuff on the client side in java with JDBC, perl with DBI and C with libpq, by browsing the above SELECT in a simple loop and aggregating the data directly in the language. In all 3 cases, the process attempts to allocate the full result of the client and invoice join in memory... a **very** bad idea indeed! I checked that the postgres client-server protocol does not allow to chunk the result of a select, as only one response is sent for one query. I suggest that this behavior should be changed, as the ODBC/DBI/JDBC interfaces are designed to allow the client to process data as the come out of the database, even if the query is not finished yet. The library should do the chunking on its own automatically, either by doing a CURSOR/FETCH's manually in the library implementation on SELECT, or by changing the protocol so that results are sent by chunks when required. This is listed in the todo list of the JDBC interface, but there is nothing about the perl interface nor the libpq interface. V) Less basic client side (DBI, libpq) -- I've redone the previous stuff, but with an explicit CURSOR and a FETCH loop. It worked better, but it is still slow and still requires a lot of disk space. Indeed, the database seems to first generate the join in a temporary table on disk (I need twice as much disk space available as the original base), which is then sent back to the client. Thus I pay a read/write/read of the whole tables although I had hoped that reading the data only once would have been enough. I would suggest to make processing data on the fly be done really on the fly, not with an intermediate storage and providing just an on-the-fly interface without the real thing behind. I haven't seen any item in the todo list about this issue. I'm not sure it is really easy to implement. Conclusion -- I have not succeeded in getting from postgresql the performances I was exp
Re: [HACKERS] Thread-safe questions in configure
Peter Eisentraut wrote:
> I have two questions about the support for thread-safe clients in
> configure:
>
> configure sets NEED_REENTRANT_FUNC_NAMES=no, overwriting the template
> setting. Why?
Forgot to remove it when I moved the stuff to templates.
> The check for reentrant functions says:
>
> # Include special flags if threads are enabled _and_ if required for
> # threading on this platform. Some platforms have *_r functions but
> # their natively named funcs are threadsafe, and should be used instead.
>
> But the actual check does not check if "threads are enabled", only if it's
> required on the platform (which doesn't work -- see above).
>
> Is this some kind of transition state "to be fixed later"?
The only transition state is in my head. :-)
Patch attached and applied.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
Index: configure
===
RCS file: /cvsroot/pgsql-server/configure,v
retrieving revision 1.276
diff -c -c -r1.276 configure
*** configure 23 Jun 2003 23:51:57 - 1.276
--- configure 22 Jul 2003 16:37:15 -
***
*** 12807,12813
# For each platform, we need to know about any special compile and link
# libraries, and whether the normal C function names are thread-safe.
#
- NEED_REENTRANT_FUNC_NAMES=no
if test "$with_threads" = yes; then
if test "${ac_cv_header_pthread_h+set}" = set; then
echo "$as_me:$LINENO: checking for pthread.h" >&5
--- 12807,12812
***
*** 12924,12936
{ { echo "$as_me:$LINENO: error:
Cannot enable threads on your platform.
Please report your platform threading info to the PostgreSQL mailing lists
! so it can be added to the next release. Report any compile flags, link flags,
functions, or libraries required for threading support.
" >&5
echo "$as_me: error:
Cannot enable threads on your platform.
Please report your platform threading info to the PostgreSQL mailing lists
! so it can be added to the next release. Report any compile flags, link flags,
functions, or libraries required for threading support.
" >&2;}
{ (exit 1); exit 1; }; }
--- 12923,12935
{ { echo "$as_me:$LINENO: error:
Cannot enable threads on your platform.
Please report your platform threading info to the PostgreSQL mailing lists
! so it can be added to the next release. Report all compile flags, link flags,
functions, or libraries required for threading support.
" >&5
echo "$as_me: error:
Cannot enable threads on your platform.
Please report your platform threading info to the PostgreSQL mailing lists
! so it can be added to the next release. Report all compile flags, link flags,
functions, or libraries required for threading support.
" >&2;}
{ (exit 1); exit 1; }; }
***
*** 12947,12956
# threading on this platform. Some platforms have *_r functions but
# their natively named funcs are threadsafe, and should be used instead.
#
! # One trick here is that if the don't call AC_CHECK_FUNCS, the
# functions are marked "not found", which is perfect.
#
! if test "$NEED_REENTRANT_FUNC_NAMES" = yes ; then
_CFLAGS="$CFLAGS"
_LIB="$LIBS"
CFLAGS="$CFLAGS $TREAD_CFLAGS"
--- 12946,12955
# threading on this platform. Some platforms have *_r functions but
# their natively named funcs are threadsafe, and should be used instead.
#
! # One trick here is that if we don't call AC_CHECK_FUNCS, the
# functions are marked "not found", which is perfect.
#
! if test "$with_threads" = yes -a "$NEED_REENTRANT_FUNC_NAMES" = yes ; then
_CFLAGS="$CFLAGS"
_LIB="$LIBS"
CFLAGS="$CFLAGS $TREAD_CFLAGS"
Index: configure.in
===
RCS file: /cvsroot/pgsql-server/configure.in,v
retrieving revision 1.267
diff -c -c -r1.267 configure.in
*** configure.in23 Jun 2003 23:51:59 - 1.267
--- configure.in22 Jul 2003 16:37:16 -
***
*** 965,971
# For each platform, we need to know about any special compile and link
# libraries, and whether the normal C function names are thread-safe.
#
- NEED_REENTRANT_FUNC_NAMES=no
if test "$with_threads" = yes; then
AC_CHECK_HEADER(pthread.h, [], [AC_MSG_ERROR([pthread.h not found, required for
--with-threads])])
--- 965,970
***
*** 973,979
AC_MSG_ERROR([
Cannot enable threads on your platform.
Please report your platform threading info to the PostgreSQL mailing lists
! so it can be added to the next release. Report any compile flags, link flags,
functions, or libraries required for threading support.
])
fi
--- 972,978
AC_MSG_ERROR([
Cannot enable threads on your platform.
Please report your platf
Re: [HACKERS] php with postgres
scott.marlowe wrote: > > > It does generate the usual error if the current transaction block is in > > > ABORT state. So the correct querystring to send would be something like > > > > > > "ROLLBACK; RESET ALL" > > > > Oh, I remember that now as part of the persistent connection code. As I > > remember, we told them to do BEGIN;COMMIT; to clear any open transaction > > state passed to the new client. Is that in there? If not, it has to be > > added too. ROLLBACK will generate an error if you are not in a > > transaction, so it would fill the logs with errors. > > Won't that break when we have nested transactions implemented? i.e. > begin;commit; would just open a sub transaction and have no effect on the > outer transaction... Yes, it would break. I am not sure how we are going to flag that we want to rollback all nested transactions, maybe ROLLBACK ALL. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] php with postgres
On Mon, 21 Jul 2003, Bruce Momjian wrote: > Jan Wieck wrote: > > Bruce Momjian wrote: > > > Marcus B?rger wrote: > > >> BM> Marcus, would you check if PHP is using RESET ALL when passing > > >> BM> persistent connection to new clients? We added that capability a few > > >> BM> releases ago, specifically for PHP persistent connections, but I don't > > >> BM> think that ever got into the PHP code. > > >> > > >> Unfortunately we don't do so yet. Do i need to check for errors or can i do it > > >> unconditionally on conenction start? And i'd need to know how to check if it > > >> is available (like starting with which version). > > > > > > It first appeared in PostgreSQL version 7.2. It doesn't generate any > > > failures. It just resets all SET settting to their defaults, in case > > > the previous client modified them. > > > > > > > It does generate the usual error if the current transaction block is in > > ABORT state. So the correct querystring to send would be something like > > > > "ROLLBACK; RESET ALL" > > Oh, I remember that now as part of the persistent connection code. As I > remember, we told them to do BEGIN;COMMIT; to clear any open transaction > state passed to the new client. Is that in there? If not, it has to be > added too. ROLLBACK will generate an error if you are not in a > transaction, so it would fill the logs with errors. Won't that break when we have nested transactions implemented? i.e. begin;commit; would just open a sub transaction and have no effect on the outer transaction... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] dblink_ora - a first shot on Oracle ...
Hans, Bruce, We're drafting the press release for 7.4 right now. What's the odds that dblink_ora will be ready in time for 7.4? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] [PATCHES] PATCH: Memory leaks on start-up
Tom Lane writes: > Lee Kindness <[EMAIL PROTECTED]> writes: > > I'd have to disagree with regards to the memory leaks not being worth > > a mention - any such leak can cause problems when the PostgreSQL > > installation is either unattended, long-living andor has very high > > connection levels. > I don't see how. We are talking about two strings, no more, no less, > that live for exactly the duration of the postmaster run. Explain to me > how any of your above conditions will affect this code in the slightest? For the lock file leak yeah - it's just 2 strings once in a blue moon. However in the more general case of freeaddrinfo and the eniron wizardry (which is also leaking, and is done every new instance, yeah?) it does build up. Anyway, it's no reason to be lazy/messy, is it? L. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] tsearch2 for 7.3.X
Tom Lane wrote: > Oleg Bartunov <[EMAIL PROTECTED]> writes: > > OK. I'll suggest people to try new tsearch2 in README file of old > > tsearch. > > Okay, that works for me. Please patch the old tsearch README file in > both HEAD and REL7_3_STABLE branch as soon as possible --- we are going > to wrap a 7.3.4 release as soon as Bruce finds the time to make release > notes, possibly today. Yes, today. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] tsearch2 for 7.3.X
Patched. Tom Lane wrote: Oleg Bartunov <[EMAIL PROTECTED]> writes: OK. I'll suggest people to try new tsearch2 in README file of old tsearch. Okay, that works for me. Please patch the old tsearch README file in both HEAD and REL7_3_STABLE branch as soon as possible --- we are going to wrap a 7.3.4 release as soon as Bruce finds the time to make release notes, possibly today. 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 -- Teodor Sigaev E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] PostgreSQL 7.3.3 and Intel C compiler
Tom Lane wrote: > =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <[EMAIL PROTECTED]> writes: > > There is one nifty detail which seems VERY strange to me: If > > serializable mode is set in postgresql.conf the system was 3 times > > faster (~ 7.5 sec. vs. 2.5sec). If serializable mode was set for every > > transaction (using set at the beginning of the transaction) serializable > > mode was as fast as read committed. > > Seems pretty strange to me too. I can believe that taking a new > snapshot for each command (as READ COMMITTED mode does) might take a > significant amount of time, especially if you have a large number of > backends connected. (I think the time to get the snapshot data is > linear in the number of live backends; also there is the possibility > of contention on the PROC array when multiple backends need to fetch > snapshots at the same time.) But if that's where the performance > difference is, it wouldn't matter whether you start in serializable > mode by default or issue a SET command to select it. But the snapshots only are grabbing the xids from each proc, right? Doesn't seem that would take very long. If this is the bottleneck, maybe we need a shared proc lock. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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]
On 22 Jul 2003 at 16:55, Peter Eisentraut wrote: > I've been thinking some about how exactly to provide the new option of > thread-safe clients (libpq, ecpg). Let me state the following goals: > > a. Thread-safeness, where it makes a difference, is generally thought to > be a performance hit, so the user needs to have a choice to use > thread-safe libraries or not. On linux and freeBSD, that is not an issue. I can attest that from my experiments with a webserver. Thread function calls often time less than or of the order of 1us even with CPU capped. I would really like to know for what platforms, locking mutex while selecting from connection list or some such object, is going to be such a great performance hit. Performance degradation in threaded programs comes from contentions. It goes for frequency of contention and number of threads fighting over it. I doubt any threaded ecpg program would reach that level of contention anytime. If a lock canbe obtained/released in less than 10us and subsequent database query is going to take at least a ms, IMO that performance degradation is not worth that much trouble. But that is for linux and freeBSD. What other platforms have serious thread issues? > Clearly, a thread-safe ecpg library is always going to be significantly > different from the "normal" one, with all the mutex things that get pulled > in, so it seems reasonable to always offer a libecpg_r alongside the > libecpg. I would say, it should be thread-safe by default. No point polluting possible linkages. I repeat what I have said earlier. If there are two libraries A using libecpg_r and B, using libecpg, then program linking against both of them is going to have tough time living with symbol conflicts. I suppose problem will be reproducible even under freeBSD if you try to create a postgresql function in C which uses threads. Link the library against libc_r and link postgresql against libc. It would run into problems. I am just stating my experiences.I might have missed solution to this problem. But overall I like GNU libc approach of everything thread safe by default. If thread performance is an issue, then it should be improved. Not worked around with two libraries. Just a thought.. Bye Shridhar -- 2180, U.S. History question:What 20th Century U.S. President was almost impeached and what office did he later hold? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] a couple of suggestions
As I was documenting my current project the other day, a couple of things occurred to me: 1. It would be nice to be able to dump my schema in some XML format that I could then process using XSLT into whatever format I desired. If there is not already some easy way to do this, and there is interest, I am prepared to put some work into such a project, (based, I guess, on pg_dump, but I am open to suggestions on how to approach this). I am slightly unclear about the current roadmap for releases - is 7.5 targeted at the end of the year, roughly? 2. Although I can comment on a constraint, none of the psql metacommands appear to show me the relevant comment, which is a pity. cheers andrew ---(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]
--On Tuesday, July 22, 2003 11:11:33 -0400 Tom Lane <[EMAIL PROTECTED]> wrote: Peter Eisentraut <[EMAIL PROTECTED]> writes: I've been thinking some about how exactly to provide the new option of thread-safe clients (libpq, ecpg). Let me state the following goals: Sounds good to me. Do you have time to make these things happen? Please see my post to -hackers last week about -K[p]thread on UnixWare. There are some issues there for us. Also, Peter, can you look at the forwards I've made from Kean Johnson of SCO on -Patches about the OSR5 patch, and DT_SONAME? Thanks, LER regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- 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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] CVS: compilation failed
On Tue, Jul 22, 2003 at 10:31:36AM -0400, Tom Lane wrote: > Karel Zak <[EMAIL PROTECTED]> writes: > > ./configure --prefix=/usr/lib/postgresql --enable-nls > > I think you must have done --enable-recode too. Oops.. you're right :-) Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS]
Peter Eisentraut <[EMAIL PROTECTED]> writes: > I've been thinking some about how exactly to provide the new option of > thread-safe clients (libpq, ecpg). Let me state the following goals: Sounds good to me. Do you have time to make these things happen? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] tsearch2 for 7.3.X
On Tue, 22 Jul 2003, Tom Lane wrote: > Oleg Bartunov <[EMAIL PROTECTED]> writes: > > OK. I'll suggest people to try new tsearch2 in README file of old > > tsearch. > > Okay, that works for me. Please patch the old tsearch README file in > both HEAD and REL7_3_STABLE branch as soon as possible --- we are going > to wrap a 7.3.4 release as soon as Bruce finds the time to make release > notes, possibly today. I don't expect you wouldn't put in the information but just to highlight that it'd be a good idea to put the location the tsearch2 tarball can picked up from in the README. -- Nigel Andrews ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [PATCHES] PATCH: Memory leaks on start-up
Lee Kindness <[EMAIL PROTECTED]> writes: > I'd have to disagree with regards to the memory leaks not being worth > a mention - any such leak can cause problems when the PostgreSQL > installation is either unattended, long-living andor has very high > connection levels. I don't see how. We are talking about two strings, no more, no less, that live for exactly the duration of the postmaster run. Explain to me how any of your above conditions will affect this code in the slightest? If UnlinkLockFile ever got invoked before postmaster exit, then this would be worth doing, and I'll accept the change as a matter of future-proofing that routine against such use. But on the argument of preventing resource leakage today, this is just a waste of code space. 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
[HACKERS]
I've been thinking some about how exactly to provide the new option of thread-safe clients (libpq, ecpg). Let me state the following goals: a. Thread-safeness, where it makes a difference, is generally thought to be a performance hit, so the user needs to have a choice to use thread-safe libraries or not. b. The user needs to be able to make the choice at the time he builds his application, *not* at the time the PostgreSQL distribution is built or installed. Clearly, a thread-safe ecpg library is always going to be significantly different from the "normal" one, with all the mutex things that get pulled in, so it seems reasonable to always offer a libecpg_r alongside the libecpg. The question is whether a libpq_r should be provided if libpq is thread-safe by default (no *_r functions, libc_r, or special flags). I think yes. It could be a symlink, so it doesn't really waste space. But it would convenience users: Those who want to be sure to always link against a thread-safe version can point to libpq_r and don't have to create complicated detection mechanisms. Those who know that their system is thread-safe by default can simply use libpq to follow that convention. And of course it creates consistency with libecpg_r and does not bother the user with complicated internal artifacts. A final note on the name of the configure option, --with-threads. First, it does not control an external package but an internal feature, so it should be --enable-. Secondly, it does not use threads, only enable thread-safeness. So --enable-thread-safe might be a better name. Or if you want to be more precise, --enable-thread-safe-client. The latter is what MySQL uses, in case anyone cares about that. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] CVS: compilation failed
Karel Zak <[EMAIL PROTECTED]> writes: > ./configure --prefix=/usr/lib/postgresql --enable-nls I think you must have done --enable-recode too. > gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations > -I../../../../src/include -c -o miscinit.o miscinit.c -MMD > miscinit.c: In function `SetCharSet': > miscinit.c:205: error: structure has no member named `in' > make[3]: *** [miscinit.o] Error 1 The recode stuff is broken due to IPv6 changes. I'm inclined to rip it out rather than try to upgrade it to handle IPv6 addresses; it seems to me that that feature has been superseded by the client/server encoding conversion features. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] PATCH: Memory leaks on start-up
Tom, happier with the attached patch?
I'd have to disagree with regards to the memory leaks not being worth
a mention - any such leak can cause problems when the PostgreSQL
installation is either unattended, long-living andor has very high
connection levels. Half a kilobyte on start-up isn't negligible in
this light.
Regards, Lee.
Tom Lane writes:
> Lee Kindness <[EMAIL PROTECTED]> writes:
> > Guys, attached is a patch to fix two memory leaks on start-up.
>
> I do not like the changes to miscinit.c. In the first place, it is not
> a "memory leak" to do a one-time allocation of state for a proc_exit
> function. A bigger complaint is that your proposed change introduces
> fragile coupling between CreateLockFile and its callers, in order to
> save no resources worth mentioning. More, it introduces an assumption
> that the globals directoryLockFile and socketLockFile don't change while
> the postmaster is running. UnlinkLockFile should unlink the file that
> it was originally told to unlink, regardless of what happens to those
> globals.
>
> If you are intent on spending code to free stuff just before the
> postmaster exits, a better fix would be for UnlinkLockFile to free its
> string argument after using it.
Index: src/backend/libpq/pqcomm.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/libpq/pqcomm.c,v
retrieving revision 1.157
diff -u -r1.157 pqcomm.c
--- src/backend/libpq/pqcomm.c 12 Jun 2003 07:36:51 - 1.157
+++ src/backend/libpq/pqcomm.c 22 Jul 2003 14:16:46 -
@@ -363,7 +363,7 @@
added++;
}
- freeaddrinfo(addrs);
+ freeaddrinfo2(family, addrs);
if (!added)
{
Index: src/backend/utils/init/miscinit.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/init/miscinit.c,v
retrieving revision 1.104
diff -u -r1.104 miscinit.c
--- src/backend/utils/init/miscinit.c 27 Jun 2003 19:08:37 - 1.104
+++ src/backend/utils/init/miscinit.c 22 Jul 2003 14:16:46 -
@@ -673,8 +673,15 @@
static void
UnlinkLockFile(int status, Datum filename)
{
- unlink((char *) DatumGetPointer(filename));
- /* Should we complain if the unlink fails? */
+ char *fname = (char *)DatumGetPointer(filename);
+ if( fname != NULL )
+{
+ if( unlink(fname) != 0 )
+ {
+ /* Should we complain if the unlink fails? */
+ }
+ free(fname);
+}
}
/*
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] PATCH: Memory leaks on start-up
Lee Kindness <[EMAIL PROTECTED]> writes: > Guys, attached is a patch to fix two memory leaks on start-up. I do not like the changes to miscinit.c. In the first place, it is not a "memory leak" to do a one-time allocation of state for a proc_exit function. A bigger complaint is that your proposed change introduces fragile coupling between CreateLockFile and its callers, in order to save no resources worth mentioning. More, it introduces an assumption that the globals directoryLockFile and socketLockFile don't change while the postmaster is running. UnlinkLockFile should unlink the file that it was originally told to unlink, regardless of what happens to those globals. If you are intent on spending code to free stuff just before the postmaster exits, a better fix would be for UnlinkLockFile to free its string argument after using it. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] create domain ... with check
On Tue, 2003-07-22 at 09:06, Merlin Moncure wrote: > I looked on the to-do list and noticed adding check constraints to > domains is not on it. IMO, being able to add a validation constraint > is one of the very best things about domains. Is anybody planning to > work on this? 7.4 has descent domain support, including check constraints, alter domain, and a number of coercion fixes. signature.asc Description: This is a digitally signed message part
[HACKERS] CVS: compilation failed
./configure --prefix=/usr/lib/postgresql --enable-nls gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include -c -o miscinit.o miscinit.c -MMD miscinit.c: In function `SetCharSet': miscinit.c:205: error: structure has no member named `in' make[3]: *** [miscinit.o] Error 1 It's problem with: MyProcPort->raddr.in.sin_addr.s_addr ^^ Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] create domain ... with check
I looked on the to-do list and noticed adding check constraints to domains is not on it. IMO, being able to add a validation constraint is one of the very best things about domains. Is anybody planning to work on this? Regards, Merlin
Re: [HACKERS] tsearch2 for 7.3.X
Oleg Bartunov <[EMAIL PROTECTED]> writes: > OK. I'll suggest people to try new tsearch2 in README file of old > tsearch. Okay, that works for me. Please patch the old tsearch README file in both HEAD and REL7_3_STABLE branch as soon as possible --- we are going to wrap a 7.3.4 release as soon as Bruce finds the time to make release notes, possibly today. 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] tsearch2 for 7.3.X
Christopher Kings-Lynne wrote: I think it's production quality. Actually, we use it in our projects with 7.3.3. You may read docs on tsearch2 home page http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ You know what would be an AWESOME feature? The ability to process user queries directly: eg. SELECT intindex, strtopic FROM tblmessages WHERE idxfti @@ 'jam and toast -guitar'::userquery::tsquery; idxfti @@ 'jam & toast & ! guitar' Or be able to understand stuff like: 'jam and toast or (jam -guitar) '(jam & toast) | ( jam & !guitar )' Sintax of query is boolean expression. Look at 'The tsearch2 Guide' (http://rhodesmill.org/brandon/projects/tsearch2-guide.html). -- Teodor Sigaev E-mail: [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] php with postgres
Marcus Börger wrote: Here's the current log while reusing the persistent connection: DEBUG: InitPostgres DEBUG: StartTransactionCommand DEBUG: query: select getdatabaseencoding() DEBUG: ProcessQuery DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: RESET ALL DEBUG: ProcessUtility: RESET ALL DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: BEGIN;ROLLBACK; DEBUG: ProcessUtility: BEGIN;ROLLBACK; DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: ProcessUtility: BEGIN;ROLLBACK; DEBUG: CommitTransactionCommand DEBUG: pq_recvbuf: unexpected EOF on client connection And this is the wrong order of things. The BEGIN;ROLLBACK; has to be done first, otherwise if the connection was left in an aborted open transaction by the previous script, the other two actions will fail. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] threads.c
It was in libpq in my original patch, not port. L. Peter Eisentraut writes: > I have a few problems with the file threads.c: > > 1. It doesn't contain any threads, so the name is slightly inappropriate. > > 2. It's not a porting module (since pqStrerror, etc. are not system > functions), so it doesn't belong into src/port. > > 3. It's only used by libpq, so why not put it into the libpq directory? > > Comments? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] PATCH: Memory leaks on start-up
Guys, attached is a patch to fix two memory leaks on start-up. The
first is when freeaddrinfo has been used to free memory allocated by
getaddrinfo2 (so freeaddrinfo2 should be used). The second is 2 leaks
when creating the lock (PostgreSQL and socket) files.
The diff is against last night's CVS HEAD.
L.
diff -cr pgsql.old/src/backend/libpq/pqcomm.c pgsql/src/backend/libpq/pqcomm.c
*** pgsql.old/src/backend/libpq/pqcomm.c2003-06-12 08:36:51.0 +0100
--- pgsql/src/backend/libpq/pqcomm.c2003-07-21 22:58:39.0 +0100
***
*** 363,369
added++;
}
! freeaddrinfo(addrs);
if (!added)
{
--- 363,369
added++;
}
! freeaddrinfo2(family, addrs);
if (!added)
{
diff -cr pgsql.old/src/backend/utils/init/miscinit.c
pgsql/src/backend/utils/init/miscinit.c
*** pgsql.old/src/backend/utils/init/miscinit.c 2003-06-27 20:08:37.0 +0100
--- pgsql/src/backend/utils/init/miscinit.c 2003-07-22 00:08:39.0 +0100
***
*** 842,848
/*
* Arrange for automatic removal of lockfile at proc_exit.
*/
! on_proc_exit(UnlinkLockFile, PointerGetDatum(strdup(filename)));
return true;/* Success! */
}
--- 842,848
/*
* Arrange for automatic removal of lockfile at proc_exit.
*/
! on_proc_exit(UnlinkLockFile, PointerGetDatum(filename));
return true;/* Success! */
}
***
*** 850,875
bool
CreateDataDirLockFile(const char *datadir, bool amPostmaster)
{
! charlockfile[MAXPGPATH];
!
! snprintf(lockfile, sizeof(lockfile), "%s/postmaster.pid", datadir);
! if (!CreateLockFile(lockfile, amPostmaster, true, datadir))
! return false;
! /* Save name of lockfile for RecordSharedMemoryInLockFile */
! strcpy(directoryLockFile, lockfile);
return true;
}
bool
CreateSocketLockFile(const char *socketfile, bool amPostmaster)
{
! charlockfile[MAXPGPATH];
!
! snprintf(lockfile, sizeof(lockfile), "%s.lock", socketfile);
! if (!CreateLockFile(lockfile, amPostmaster, false, socketfile))
! return false;
! /* Save name of lockfile for TouchSocketLockFile */
! strcpy(socketLockFile, lockfile);
return true;
}
--- 850,873
bool
CreateDataDirLockFile(const char *datadir, bool amPostmaster)
{
! snprintf(directoryLockFile, sizeof(directoryLockFile), "%s/postmaster.pid",
datadir);
! if (!CreateLockFile(directoryLockFile, amPostmaster, true, datadir))
! {
! *directoryLockFile = '\0';
! return false;
! }
return true;
}
bool
CreateSocketLockFile(const char *socketfile, bool amPostmaster)
{
! snprintf(socketLockFile, sizeof(socketLockFile), "%s.lock", socketfile);
! if (!CreateLockFile(socketLockFile, amPostmaster, false, socketfile))
! {
! *socketLockFile = '\0';
! return false;
! }
return true;
}
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] threads.c
I have a few problems with the file threads.c: 1. It doesn't contain any threads, so the name is slightly inappropriate. 2. It's not a porting module (since pqStrerror, etc. are not system functions), so it doesn't belong into src/port. 3. It's only used by libpq, so why not put it into the libpq directory? Comments? -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Thread-safe questions in configure
I have two questions about the support for thread-safe clients in configure: configure sets NEED_REENTRANT_FUNC_NAMES=no, overwriting the template setting. Why? The check for reentrant functions says: # Include special flags if threads are enabled _and_ if required for # threading on this platform. Some platforms have *_r functions but # their natively named funcs are threadsafe, and should be used instead. But the actual check does not check if "threads are enabled", only if it's required on the platform (which doesn't work -- see above). Is this some kind of transition state "to be fixed later"? -- Peter Eisentraut [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] tsearch2 for 7.3.X
> I think it's production quality. Actually, we use it in our > projects with 7.3.3. You may read docs on tsearch2 home page > http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ You know what would be an AWESOME feature? The ability to process user queries directly: eg. SELECT intindex, strtopic FROM tblmessages WHERE idxfti @@ 'jam and toast -guitar'::userquery::tsquery; Or be able to understand stuff like: 'jam and toast or (jam -guitar) etc., etc. Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
