Re: [GENERAL] epqa; postgres performance optimizer support tool; opensource.
Sure , i 'll try with our database log Regards sathish On Tue, Nov 4, 2008 at 5:55 PM, sathiya psql [EMAIL PROTECTED] wrote: Dear All, Recently i have released the next version of the epqa. which is a very useful tool for, gives input for optimizing psql queries, and fine tuning it. epqa is tool similar like, pqa. But designed and implemented to parse log files which is in GB's. Report is similar like that. More information can be got from http://epqa.sourceforge.net/ Expecting suggestions, feedbacks, clarfications @ [EMAIL PROTECTED] Note: This is to propagate the open source which can help for postgres users. This is not a spam, or advertisement. Regards SathiyaMoorthy -- BSG LeatherLink Pvt Limited, Mail To : [EMAIL PROTECTED] Website : http://www.leatherlink.net Contact : +91 44 65191757
Re: [GENERAL] Hot standby stops after a few days of inactivity (i.e. no new WAL)
Marc Schablewski wrote: Hi, we are running a PostgreSQL 8.3.3 on a Linux box (SuSE 10.3, 2.6.22 kernel) as a hot standby. After some maintenances work the WAL files couldn't be shipped to that system (which had nothing to do with postgres, as we found out later). The problem was not noticed for about a week. When looking for a reason why the WAL weren't shipped, we found the following error message: 2008-10-31 17:07:52 CET 9162LOG: received smart shutdown request 2008-10-31 17:07:52 CET 9178FATAL: could not restore file 000100860018 from archive: return code 15 This server was stopped intentionally by someone or something, external to Postgres itself. Smart shutdown means the postmaster got SIGTERM. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] don't use GIN index, when i use =any
Hi all! I have a problem. I have a field with type: bigint[], and I create a GIN index on it but don't use the index when i use '=any'. When I try it with '@' operator, then use index. I don't understand why doesn't work with '=any'? Does anybody knows why doesn't work it? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hot standby stops after a few days of inactivity (i.e. no new WAL)
On Tue, Nov 4, 2008 at 5:50 AM, Marc Schablewski [EMAIL PROTECTED] wrote: Hi, we are running a PostgreSQL 8.3.3 on a Linux box (SuSE 10.3, 2.6.22 kernel) as a hot standby. After some maintenances work the WAL files I'm assuming you meant 'warm standby'...hot standby servers can be served for queries. This feature is proposed for PostgreSQL 8.4 merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hot standby stops after a few days of inactivity (i.e. no new WAL)
Ah, ok. I somehow missed the first line of the message an the rest of it left the impression that something must be wrong with replication. I guess one of my colleagues might have shut down the database by accident and forgot to tell me. Anyway, thanks for your reply. Marc Alvaro Herrera wrote: Marc Schablewski wrote: Hi, we are running a PostgreSQL 8.3.3 on a Linux box (SuSE 10.3, 2.6.22 kernel) as a hot standby. After some maintenances work the WAL files couldn't be shipped to that system (which had nothing to do with postgres, as we found out later). The problem was not noticed for about a week. When looking for a reason why the WAL weren't shipped, we found the following error message: 2008-10-31 17:07:52 CET 9162LOG: received smart shutdown request 2008-10-31 17:07:52 CET 9178FATAL: could not restore file 000100860018 from archive: return code 15 This server was stopped intentionally by someone or something, external to Postgres itself. Smart shutdown means the postmaster got SIGTERM. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Installation Error of postgresql-8.1.5 with perl.
Hello , I am trying to install postgresql-8.1.5 and postgresql-8.2.5 in linux (Linux version 2.6.25-14.fc9.i686 (mockbuild@) (gcc version 4.3.0 20080428 (Red Hat 4.3.0-8) (GCC) ) #1 SMP Thu May 1 06:28:41 EDT 2008).but during compilation it is showing following error. make[3]: *** [plperl.o] Error 1 make[3]: Leaving directory `/home/postgres/postgresql-8.1.5/src/pl/plperl' make[2]: *** [all] Error 1 make[2]: Leaving directory `/home/postgres/postgresql-8.1.5/src/pl' make[1]: *** [all] Error 2 make[1]: Leaving directory `/home/postgres/postgresql-8.1.5/src' make: *** [all] Error 2 Please tell me how I can avoid this kind of error. Thanks regard Praveen kumar. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hot standby stops after a few days of inactivity (i.e. no new WAL)
Yes, 'warm standby' was what I intended to write. This must have been some kind of wishful thinking. ;) But I'd really appreciate 'hot standby' in a future version of postgres. Marc Merlin Moncure wrote: On Tue, Nov 4, 2008 at 5:50 AM, Marc Schablewski [EMAIL PROTECTED] wrote: Hi, we are running a PostgreSQL 8.3.3 on a Linux box (SuSE 10.3, 2.6.22 kernel) as a hot standby. After some maintenances work the WAL files I'm assuming you meant 'warm standby'...hot standby servers can be served for queries. This feature is proposed for PostgreSQL 8.4 merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [ADMIN] Installation Error of postgresql-8.1.5 with perl.
Hello , I am trying to install postgresql-8.1.5 and postgresql-8.2.5 in linux (Linux version 2.6.25-14.fc9.i686 (mockbuild@) (gcc version 4.3.0 20080428 (Red Hat 4.3.0-8) (GCC) ) #1 SMP Thu May 1 06:28:41 EDT 2008).but during compilation it is showing following error. I configure with following options. ./configure --prefix=/home/local/pgsql/ --without-readline --with-perl --with-python --with-tcl --with-tclconfig=/usr/src/tcl8.4.16/unix --enable-nls but when I execute command make that time I got following errors. make[3]: *** [plperl.o] Error 1 make[3]: Leaving directory `/home/postgres/postgresql-8.1.5/src/pl/plperl' make[2]: *** [all] Error 1 make[2]: Leaving directory `/home/postgres/postgresql-8.1.5/src/pl' make[1]: *** [all] Error 2 make[1]: Leaving directory `/home/postgres/postgresql-8.1.5/src' make: *** [all] Error 2 Please tell me how I can avoid this kind of error. Thanks regard Praveen kumar. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT with RETURNING clause inside SQL function
On Tue, Nov 4, 2008 at 9:57 AM, Lennin Caro [EMAIL PROTECTED] wrote: Hi all, I'm re-writing some functions and migrating bussines logic from a client application to PostgreSQL. I expected something like this to work, but it doesn't: -- simple table CREATE TABLE sometable ( id SERIAL PRIMARY KEY, text1 text, text2 text ); CREATE OR REPLACE FUNCTION add_something(text, text) RETURNS INTEGER AS $$ INSERT INTO sometable (id, foo, bar ) VALUES (DEFAULT, $1, $2 ) RETURNING id ; $$ LANGUAGE SQL ; Please note the use of RETURNING clause. If I put a SELECT 1; after the INSERT, the function works (but doesn't returns any useful value :) I need the function to return the last insert id. And yes, I'm aware that the same can be achieved by selecting the greatest id in the SERIAL secuence, but is not as readable as RETURNING syntax. And no, for me it's not important that RETURNING is not standard SQL. Does anyone knows why RETURNING doesn't works inside SQL functions? Any advise will be very appreciated. TIA. diego Hi.. what version of postgres you have? I'm using 8.3.3. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] gin creation and previous history of server
It seems that gin creation is triggering something nasty in the server that depends on previous history of the server. If I vacuum full than drop the index and recreate it even with maintenance_work_mem='200MB' index creation may take forever. Stopping the execution may make vacuuming very slow or stopping the server very slow etc... I know that the server may be cleaning the new partially created index etc... but gin creation even with 200MB of maintenance_work_mem is having a too strange and to big hit on the server. I can *occasionally* succede to create a gin index in reasonable time if I shut down the server, vacuum full and drop and recreate the index separately from the transaction that load the data. gist creation is *predictably* much faster even inside the transaction that load the data and doesn't have side effect outside index creation. Even when gin creation succede it is definitively slower than 3x gist/gin index creation looks more cpu bounded than memory bounded. I'm checking if I made some mistake in other cfg parameters that may have some impact on index creation... Any further clue? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] gin creation and previous history of server
Ivan Sergio Borgonovo [EMAIL PROTECTED] writes: It seems that gin creation is triggering something nasty in the server that depends on previous history of the server. Can you put together a self-contained test case that illustrates this? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] perl-DBD-Pg package for CentOS 5?
On Fri, 2008-10-31 at 17:31 +0200, Devrim GÜNDÜZ wrote: Hi, Have you considered installing directlly from CPAN ? # perl -MCPAN -e 'install DBD::Pg;' joao On Fri, 2008-10-31 at 09:20 -0400, Kevin Murphy wrote: My life would be complete if it offered perl-DBD-Pg for CentOS 5! We had an up2date package, but it broke many apps inside RHEL/CentOS 5, so I removed EL-4 and EL-5 branches from SVN. If you want, you can grab Fedora 9 SRPM and rebuild it on your system -- but it will probably be broken since it will try to gra some dependencies that RHEL/CentOS 5 does not have. Regards, -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] GEQO randomness?
This is in a sense a followup to my post with subject Wildly erratic query performance. The more I think about it the only thing that makes sense of my results is if the query planner really WAS choosing my join order truly randomly each time. I went digging into the manual and Section 49.3.1. Generating Possible Plans with GEQO says In the initial stage, the GEQO code simply generates some possible join sequences at random. Now ordinarily I would interpret this use of the word random loosely, to mean arbitrarily or using some non-meaningful selection criteria. But given what I am seeing, this leads me to consider that random is meant literally, and that it uses a random number generate to pick paths. Can someone confirm that this is the case? Is this really a good idea? Is non-deterministic behavior really acceptable? I would think it would be much more sensible to have it operate deterministically (such as with some predetermined random sequence of numbers used repeatedly). Eric -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[Fwd: Re: [GENERAL] GEQO randomness?]
My problem with GEQO using a random number generator is that non-deterministic behavior is really hard to debug, and problems can go undiagnosed for ages. Frankly I would rather something fail all the time, than it work most of the time and fail just now and then. Never getting a good plan for a query would be an improvement because I would immediately be aware there's a problem and be forced to something about it, as opposed to maybe realizing there is going to *sometimes* be a problem. Suppose a complex query, like mine, had an even more rarely occurring bad plan result, where as with mine, now and then the query would simply go out to lunch for all intents and purposes and bog down the server for the next 30 minutes. But suppose that result was rarer than in my case, and the developer never saw it, and blithely sent it out into production. Every now and then the system would start performing horribly and no one would know why. The developers might hear of it and bring in the debugger, and perhaps simply never duplicate it because it's so erratic. In fact, I'd be willing to bet there are any number of production applications out in the wild using postgresql with that very problem and the problem is just never traced back to postgresql. I'm sorry if I sound strident, but I feel strongly about non-determinacy in system being a Bad Thing, and wish to convey why. I understand from the documentation that the postgresql team is aware the algorithm is not ideal, and appreciate the non-triviality of replacing it. I do appreciate your responses and your suggestions. For my own case, I'll certainly be doing one or more of the alternatives you mentioned (#1 for the short term, at least), and I've had #3 in mind even before I ran into this problem (the only question is when I will have time to do it). Thanks again, Eric Tom Lane wrote: Eric Schwarzenbach [EMAIL PROTECTED] writes: Now ordinarily I would interpret this use of the word random loosely, to mean arbitrarily or using some non-meaningful selection criteria. But given what I am seeing, this leads me to consider that random is meant literally, and that it actually uses a random number generator to choose paths. Can someone confirm that this really is the case? What it's doing is searching a subset of the space of all possible join orders. It still picks the best (according to cost estimate) plan within that subset, but if you're unlucky there may be no very good plan in that subset. And yes, there is a random number generator in there. If so, I is this really a good idea? The alternatives are not very appealing either ... I would think it would be much more sensible to have it operate deterministically (such as with some predetermined random sequence of numbers used repeatedly). ... in particular, that one's hardly a panacea. For one thing, a not-unlikely outcome would be that you *never* get a good plan and thus don't even get a hint that you might be missing something. For another, the data values used in the query and the current ANALYZE statistics also affect the search, which means that in the real world where those things change, you'd still be exposed to getting the occasional unexpectedly bad plan. There are a number of alternatives you can consider though: 1. Disable geqo or bump up the threshold enough that it's not used for your query. Whether this is a feasible answer is impossible to say with the limited detail you've provided. (Remember that potentially exponential search time.) 2. Increase geqo_effort to make the randomized search run a bit longer and examine more plans. This just decreases the probability of losing, but maybe it will do so enough that you won't care anymore. 3. Figure out what's a good join order, rewrite your query to explicitly join in that order, and *decrease* join_collapse_limit to force the planner to follow that order instead of searching. Permanent solution but the initial development effort is high, especially if you have a lot of different queries that need this treatment. 4. Write a better randomized-search algorithm and submit a patch ;-) We have good reason to think that the GEQO code is not a really intelligent approach to doing randomized plan searching --- it's based on an algorithm designed to solve traveling-salesman problems, which is not such a good match to join-order problems --- but no one's yet gotten motivated to replace it. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] time interval format srting
Hello, I've been searching the docs on a simple way to convert a time _duration_ in seconds to the format dd:hh:mm:ss, but I can't find it. 90061 -- 1d 1h 1m 1s (90061=24*3600+3600+60+1) any ideas ? I've been using to_char and to_timestamp to format dates/timestamps... but this is diferent... I want to format time intervals, durations.. cheers Joao -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] JDBC and setting statement_timeout
Kris Jurka wrote: On Mon, 3 Nov 2008, Jason Long wrote: *Would someone please comment on the status of setQueryTimeout in the JDBC driver? Is there any workaround if this is still not implemented?* setQueryTimeout is not implemented, the workaround is to manually issue SET statement_timeout = xxx calls via Statement.execute. Kris Jurka 1. Could you provide a code sample to work with straight JDBC? 2. Can someone advise how this might work with EJB3/Hibernate? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Semi-customized queries? Subset of SQL? Accessing the parser? Injection?
Hi all,
I am writing an application that allows users to analyze demographic
and economic data, and I would like the users to be able to pick
columns, transform columns with functions (economists take the
logarithm of everything), and write customized WHERE and GROUP-BY
clauses. This is kind of like passing through a query to the DB in a
library catalog.
Has anybody found a good way to do this, especially inside the
database from a plpgsql function (select * from custom_query('table1',
'col1 100')) ? I don't want to just concatenate a user supplied
WHERE clause, at least without somehow checking the resulting
statement for (1) only one statement, (2) no data modification
clauses, and (3) only one level in the tree.
It seems like if I could interact with an SQL parser through a script,
I could accomplish this relatively easily. Perhaps SPI can help me
(give me hints!), though I don't really want to write any C. Perhaps
I am wrong about the possibility of this at all.
I realize that roles and permissions can help protect the system, but
I still feel nervous.
Has anybody done a similar thing, or tried? The problem is that if we
try to parameterize everything, then we don't really allow the kind of
data exploration that we are shooting for and these guys / gals are
smart enough to deal with a little syntax.
Thanks!
-W
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Hot standby stops after a few days of inactivity (i.e. no new WAL)
Hi, we are running a PostgreSQL 8.3.3 on a Linux box (SuSE 10.3, 2.6.22 kernel) as a hot standby. After some maintenances work the WAL files couldn't be shipped to that system (which had nothing to do with postgres, as we found out later). The problem was not noticed for about a week. When looking for a reason why the WAL weren't shipped, we found the following error message: 2008-10-31 17:07:52 CET 9162LOG: received smart shutdown request 2008-10-31 17:07:52 CET 9178FATAL: could not restore file 000100860018 from archive: return code 15 2008-10-31 17:07:52 CET 9162LOG: startup process (PID 9178) exited with exit code 1 2008-10-31 17:07:52 CET 9162LOG: aborting startup due to startup process failure This message occurred about 3 1/2 days after the last log was shipped. I searched the postgres docs and Google for the meaning of return code 15 but couldn't find anything. After copying the missing WAL from our master system and restarting postgres, everything worked fine again, but I'm still curious what made postgres stop waiting for WAL. It seems to me that there is some kind of timeout that triggers if there are no new WAL for a couple of days, but that would seem a bit strange. I'd expect postgres to wait forever if it is not told to wake up from recovery mode manually. The manual's Recovery Settings section didn't help either. I'm not sure if it is a bug, at least it's strange. Regards, Marc -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installation Error of postgresql-8.1.5 with perl.
praveen [EMAIL PROTECTED] writes: but when I execute command make that time I got following errors. make[3]: *** [plperl.o] Error 1 make[3]: Leaving directory `/home/postgres/postgresql-8.1.5/src/pl/plperl' make[2]: *** [all] Error 1 make[2]: Leaving directory `/home/postgres/postgresql-8.1.5/src/pl' make[1]: *** [all] Error 2 make[1]: Leaving directory `/home/postgres/postgresql-8.1.5/src' make: *** [all] Error 2 1. You've snipped away the actual error message, so no one can tell what went wrong. 2. It is completely inappropriate to cross-post to four different mailing lists. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] time interval format srting
On Tue, Nov 4, 2008 at 10:06 AM, Joao Ferreira gmail [EMAIL PROTECTED] wrote: Hello, I've been searching the docs on a simple way to convert a time _duration_ in seconds to the format dd:hh:mm:ss, but I can't find it. 90061 -- 1d 1h 1m 1s (90061=24*3600+3600+60+1) select number*interval '1 sec'; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] time interval format srting
On Tue, Nov 4, 2008 at 10:53 AM, Scott Marlowe [EMAIL PROTECTED] wrote: On Tue, Nov 4, 2008 at 10:06 AM, Joao Ferreira gmail [EMAIL PROTECTED] wrote: Hello, I've been searching the docs on a simple way to convert a time _duration_ in seconds to the format dd:hh:mm:ss, but I can't find it. 90061 -- 1d 1h 1m 1s (90061=24*3600+3600+60+1) select number*interval '1 sec'; OK, that just gives hours:minutes:seconds. You can add and subtract the same timestamp to get something like an interval select ((9084000*interval '1 sec')+timestamp '2008-01-01')-timestamp '2008-01-01'; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Semi-customized queries? Subset of SQL? Accessing the parser? Injection?
On Nov 4, 2008, at 9:21 AM, Webb Sprague wrote:
Hi all,
I am writing an application that allows users to analyze demographic
and economic data, and I would like the users to be able to pick
columns, transform columns with functions (economists take the
logarithm of everything), and write customized WHERE and GROUP-BY
clauses. This is kind of like passing through a query to the DB in a
library catalog.
Has anybody found a good way to do this, especially inside the
database from a plpgsql function (select * from custom_query('table1',
'col1 100')) ? I don't want to just concatenate a user supplied
WHERE clause, at least without somehow checking the resulting
statement for (1) only one statement, (2) no data modification
clauses, and (3) only one level in the tree.
It seems like if I could interact with an SQL parser through a script,
I could accomplish this relatively easily. Perhaps SPI can help me
(give me hints!), though I don't really want to write any C. Perhaps
I am wrong about the possibility of this at all.
I realize that roles and permissions can help protect the system, but
I still feel nervous.
Has anybody done a similar thing, or tried? The problem is that if we
try to parameterize everything, then we don't really allow the kind of
data exploration that we are shooting for and these guys / gals are
smart enough to deal with a little syntax.
If they're that smart, they're smart enough to deal with SQL, and
likely to be frustrated by a like-sql-but-not command language or
a GUI query designer.
Instead, create a user that only has enough access to read data (and
maybe create temporary tables) and use that user to give them
a sql commandline.
It'll be drastically less development effort for you, and the end result
is less likely to frustrate your users.
When I've done this I've also provided some useful plpgsql and sql
functions for users to use, to wrap commonly needed transformations,
and some views to hide parts of the data model they didn't need
to know about.
Cheers,
Steve
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql and Mac OS X
On Tue, Nov 4, 2008 at 8:21 AM, Tom Allison [EMAIL PROTECTED] wrote: Grzegorz Jaśkiewicz wrote: I feel good about control here, and I certainly don't have any problems. So, please don't whine :) Especially since I want to run cvs head, and be able to actually update it from cvs when I want to, that's the only choice. Postgresql is so easy to get from sources, compared to other software packages, I can't understand people even with slightest expierence in unix to have any problems with it. I tried getting a source install on my mac book yesterday and today. It's not a normal *nix installation. The location of the files are all non-standard. 'make' is prefixed by /Developer/usr/bin/. I added /Developer/usr/bin to PATH and tried ./configure. checking build system type... i386-apple-darwin9.5.0 checking host system type... i386-apple-darwin9.5.0 checking which template to use... darwin checking whether to build with 64-bit integer date/time support... no checking whether NLS is wanted... no checking for default port number... 5432 checking for gcc... gcc checking for C compiler default output file name... configure: error: C compiler cannot create executables See `config.log' for more details. config.log shows an exit code of 77 with a statement that compiler cannot create executables. ??? configure:2213: $? = 0 configure:2215: gcc -v /dev/null 5 Using built-in specs. Target: i686-apple-darwin9 Configured with: /var/tmp/gcc/gcc-5488~2/src/configure --disable-checking -enabl e-werror --prefix=/usr --mandir=/share/man --enable-languages=c,objc,c++,obj-c++ --program-transform-name=/^[cg][^.-]*$/s/$/-4.0/ --with-gxx-include-dir=/includ e/c++/4.0.0 --with-slibdir=/usr/lib --build=i686-apple-darwin9 --with-arch=apple --with-tune=generic --host=i686-apple-darwin9 --target=i686-apple-darwin9 Thread model: posix gcc version 4.0.1 (Apple Inc. build 5488) configure:2218: $? = 0 configure:2220: gcc -V /dev/null 5 gcc-4.0: argument to `-V' is missing configure:2223: $? = 1 configure:2246: checking for C compiler default output file name configure:2249: gccconftest.c 5 ld: library not found for -lcrt1.10.5.o collect2: ld returned 1 exit status configure:2252: $? = 1 configure: failed program was: I think he questin is, what lib was missing so I can go find it and add it to some path/dir variable? I think you need to install the developer tools. I compile postgresql from sources with no problem on osx 10.5.4 but I installed developer tools before. The library which is missing is the following: configure:2246: checking for C compiler default output file name configure:2249: gccconftest.c 5 ld: library not found for -lcrt1.10.5.o - crt1.10.5.o I hope it helps. -- Regards, Francisco Figueiredo Jr. Npgsql Lead Developer http://fxjr.blogspot.com http://www.npgsql.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] time interval format srting
On Tue, Nov 04, 2008 at 05:06:37PM +, Joao Ferreira gmail wrote: I've been searching the docs on a simple way to convert a time _duration_ in seconds to the format dd:hh:mm:ss, but I can't find it. 90061 -- 1d 1h 1m 1s (90061=24*3600+3600+60+1) any ideas ? I've been using to_char and to_timestamp to format dates/timestamps... but this is diferent... I want to format time intervals, durations.. How about doing: SELECT justify_interval(90061 * '1 second'::INTERVAL); The reason PG makes it a bit difficult is because of things like daylight savings means that a day can be longer, or shorter, than 24 hours. Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] time interval format srting
am Tue, dem 04.11.2008, um 17:06:37 + mailte Joao Ferreira gmail folgendes:
Hello,
I've been searching the docs on a simple way to convert a time
_duration_ in seconds to the format dd:hh:mm:ss, but I can't find it.
90061 -- 1d 1h 1m 1s
(90061=24*3600+3600+60+1)
any ideas ?
Something like this?
test=*# select (90061 / (24*3600))::text || ' days ' ||
to_char('3661'::interval, 'hh h mi m ss s')::text;
?column?
---
1 days 01 h 01 m 01 s
(1 Zeile)
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] dumbofs - a postgresql filesystem
hi all, i've created a filesystem that lets you view databases, schemas and tables of pg server as regular directories, and records as files. it is still in its infancy, though. more info at http://yiannnos.com/dumbofs cheers, yiannis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Semi-customized queries? Subset of SQL? Accessing the parser? Injection?
On Tue, Nov 4, 2008 at 10:59 AM, Steve Atkins [EMAIL PROTECTED] wrote:
On Nov 4, 2008, at 9:21 AM, Webb Sprague wrote:
Hi all,
I am writing an application that allows users to analyze demographic
and economic data, and I would like the users to be able to pick
columns, transform columns with functions (economists take the
logarithm of everything), and write customized WHERE and GROUP-BY
clauses. This is kind of like passing through a query to the DB in a
library catalog.
Has anybody found a good way to do this, especially inside the
database from a plpgsql function (select * from custom_query('table1',
'col1 100')) ? I don't want to just concatenate a user supplied
WHERE clause, at least without somehow checking the resulting
statement for (1) only one statement, (2) no data modification
clauses, and (3) only one level in the tree.
It seems like if I could interact with an SQL parser through a script,
I could accomplish this relatively easily. Perhaps SPI can help me
(give me hints!), though I don't really want to write any C. Perhaps
I am wrong about the possibility of this at all.
I realize that roles and permissions can help protect the system, but
I still feel nervous.
Has anybody done a similar thing, or tried? The problem is that if we
try to parameterize everything, then we don't really allow the kind of
data exploration that we are shooting for and these guys / gals are
smart enough to deal with a little syntax.
If they're that smart, they're smart enough to deal with SQL, and
likely to be frustrated by a like-sql-but-not command language or
a GUI query designer.
Instead, create a user that only has enough access to read data (and
maybe create temporary tables) and use that user to give them
a sql commandline.
It'll be drastically less development effort for you, and the end result
is less likely to frustrate your users.
When I've done this I've also provided some useful plpgsql and sql
functions for users to use, to wrap commonly needed transformations,
and some views to hide parts of the data model they didn't need
to know about.
This... Also, look into setting up replicant slave dbs for users to
hammer on so the main one doesn't get killed by a rogue query.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] I'm puzzled by a foreign key constraint problem
On Tue, Nov 4, 2008 at 11:18 AM, Jonathan Guthrie [EMAIL PROTECTED] wrote: On Tue, 2008-11-04 at 07:49 +, Richard Huxton wrote: Jonathan Guthrie wrote: When I create a project, entries in the project table and the resource table are created in a single function. Then, separate functions are called to set the owner's access to the new project. These other functions are failing because of the resourceid foreign key constraint. Have you turned statement logging on? Your message suggests that's the case, but didn't say so explicitly. Are the two steps: 1. Create project, resource 2. Set access-rights done in separate connections by any chance? If so it's possible (due to MVCC) that #2 is still seeing the database as it was before #1 committed. It's possible, likely even. We use a connection pool to manage connections to the database and they're doled out as the system sees fit. However, at some point every update has to finish such that any view of the database will see that update as finished, right? Sure. But, if the query to add the permissions is running under an already active transaction, and you're running in serializable mode, it can't see the changes because it started before they were committed. Anyway, I need for these operations to succeed because the lack of permissions causes odd problems in other parts of the system. If you want both steps to succeed or fail together though, they need to be in the same transaction. That's what Mr Ringer said, and although I understand that answer and I understand the reason that two people have independently responded with it, I'm dissatisfied with it. Mr Ringer was right. So was Jonathan. There are two reasons why I'm dissatisfied with that answer. First, my mandate is basically to create an interface layer for Postgres and then port the SQL Server stored procedures without changing how they work. Even if they're broken? If I change the logic in this part, it will be different not only from the mechanism used in the original SQL Server stored procedure, but also different from the logic used in other stored procedures that do similar things. Then they might be broken and need fixing as well. Blindly converting broken code that just happened to work is not the best way to approach a project. The second reason is because adding permissions doesn't just happen at project creation time. Is there some reason you can call the external permission setting function from within the function that adds the users? This would seem the simplest and cleanest solution, since they would then automatically share a transaction. Other operations could still call the permissions setting function as they used to. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] I'm puzzled by a foreign key constraint problem
Jonathan Guthrie wrote: On Tue, 2008-11-04 at 07:49 +, Richard Huxton wrote: Jonathan Guthrie wrote: When I create a project, entries in the project table and the resource table are created in a single function. Then, separate functions are called to set the owner's access to the new project. These other functions are failing because of the resourceid foreign key constraint. Have you turned statement logging on? Your message suggests that's the case, but didn't say so explicitly. Are the two steps: 1. Create project, resource 2. Set access-rights done in separate connections by any chance? If so it's possible (due to MVCC) that #2 is still seeing the database as it was before #1 committed. It's possible, likely even. We use a connection pool to manage connections to the database and they're doled out as the system sees fit. However, at some point every update has to finish such that any view of the database will see that update as finished, right? You'll need to read the section of the manuals regarding transaction isolation and how it impacts MVCC for full details, but the short answer is no. A pre-existing transaction might well see the database as it was when its snapshot was first taken. More likely to happen if you have a connection pool that issues BEGINs too early... Anyway, I need for these operations to succeed because the lack of permissions causes odd problems in other parts of the system. If you want both steps to succeed or fail together though, they need to be in the same transaction. That's what Mr Ringer said, and although I understand that answer and I understand the reason that two people have independently responded with it, I'm dissatisfied with it. There are two reasons why I'm dissatisfied with that answer. First, my mandate is basically to create an interface layer for Postgres and then port the SQL Server stored procedures without changing how they work. If I change the logic in this part, it will be different not only from the mechanism used in the original SQL Server stored procedure, but also different from the logic used in other stored procedures that do similar things. The logic is wrong regardless of whether you use PostgreSQL, SQL Server, Oracle or any other DB though. If you want a guarantee that both actions succeed or fail together you'll need to wrap them in a transaction. What you're saying is that at the moment there is no such guarantee with SQL Server as your database, it just happens to work most (e.g. 99.99%) of the time. The second reason is because adding permissions doesn't just happen at project creation time. The software I work on is middleware for the actual client applications and the client can assign any user permissions to access the project just as soon as it knows the project's ID, which is one of the values returned by the project creation function. If the issue is a difference in views because the requests come in on different connections, then there's a time window during which a valid and unanticipatable request from the client could fail if the request happens to use a connection to communicate with the database that is different from the one used to create the project. This is separate from the issue of both actions succeeding or failing. Anyway, while I agree that adding the logic to set permissions to the project create function seems the simplest approach to dealing with the issue, I'd really rather not change the logic until I've thoroughly explored all other options. I CAN guarantee that the other operations on a project definitely won't begin until the create is committed. So, is there any way of causing a commit to not return until all the views are consistent? It doesn't. They are. But I think your second connection is fixed to an older snapshot. Set aside an hour, read through the concurrency control / transaction-isolation section of the manuals and experiment with two psql screens open at the same time until you're clear how it all works. It'll probably take 5 mins to find the problem then (in consunction with statment logging turned on at the server side). I might be wrong about the cause, but since (1) foreign-keys work in PG, (2) you seem to know what you're doing, I'm guessing it's a combination of the subtleties of mvcc and your connection-pool interacting. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hot standby stops after a few days of inactivity (i.e. no new WAL)
Marc Schablewski [EMAIL PROTECTED] writes: ... When looking for a reason why the WAL weren't shipped, we found the following error message: 2008-10-31 17:07:52 CET 9162LOG: received smart shutdown request 2008-10-31 17:07:52 CET 9178FATAL: could not restore file 000100860018 from archive: return code 15 Something sent SIGTERM to both your postmaster (hence the smart shutdown message) and the recovery_command script (causing it to exit with code 15, which is probably SIGTERM though you might want to check kill -l to be sure). You need to find out what's doing that and make it stop. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] epqa; postgres performance optimizer support tool; opensource.
On Tue, Nov 04, 2008 at 05:55:51PM +0530, sathiya psql wrote: Dear All, Recently i have released the next version of the epqa. which is a very useful tool for, gives input for optimizing psql queries, and fine tuning it. Generally, it's good to send announcements like this to pgsql-announce, which has much lower traffic. :) Sending it to all the lists isn't your best move. epqa is tool similar like, pqa. But designed and implemented to parse log files which is in GB's. Report is similar like that. More information can be got from http://epqa.sourceforge.net/ Expecting suggestions, feedbacks, clarfications @ [EMAIL PROTECTED] Note: This is to propagate the open source which can help for postgres users. This is not a spam, or advertisement. Regards SathiyaMoorthy -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Semi-customized queries? Subset of SQL? Accessing the parser? Injection?
If they're that smart, they're smart enough to deal with SQL, and likely to be frustrated by a like-sql-but-not command language or a GUI query designer. Instead, create a user that only has enough access to read data (and maybe create temporary tables) and use that user to give them a sql commandline. It'll be drastically less development effort for you, and the end result is less likely to frustrate your users. Can't do that. (Or I wouldn't have asked the question.) Need a WWW interface, period. Thanks for the comment, though. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT with RETURNING clause inside SQL function
Hi all, I'm re-writing some functions and migrating bussines logic from a client application to PostgreSQL. I expected something like this to work, but it doesn't: -- simple table CREATE TABLE sometable ( id SERIAL PRIMARY KEY, text1 text, text2 text ); CREATE OR REPLACE FUNCTION add_something(text, text) RETURNS INTEGER AS $$ INSERT INTO sometable (id, foo, bar ) VALUES (DEFAULT, $1, $2 ) RETURNING id ; $$ LANGUAGE SQL ; Please note the use of RETURNING clause. If I put a SELECT 1; after the INSERT, the function works (but doesn't returns any useful value :) I need the function to return the last insert id. And yes, I'm aware that the same can be achieved by selecting the greatest id in the SERIAL secuence, but is not as readable as RETURNING syntax. And no, for me it's not important that RETURNING is not standard SQL. Does anyone knows why RETURNING doesn't works inside SQL functions? Any advise will be very appreciated. TIA. diego Hi.. what version of postgres you have? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Semi-customized queries? Subset of SQL? Accessing the parser? Injection?
On Tue, Nov 4, 2008 at 2:12 PM, Webb Sprague [EMAIL PROTECTED] wrote: Can't do that. (Or I wouldn't have asked the question.) Need a WWW interface, period. A WWW interface doesn't preclude the suggestion of simply relying on permissions to maintain safety and providing what amounts to a query command line; I've got that exact thing in php for one of my DBs. The user can't make db changes, and just from paranoia I check the query for certain bad keywords (delete, insert, into, update, drop, create, alter, etc) before passing it on. On return, some simple php functions create a table with the appropriate column names and such. It's surely the simplest solution, and it definitely will work. -- - David T. Wilson [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Semi-customized queries? Subset of SQL? Accessing the parser? Injection?
On Tue, Nov 04, 2008 at 11:12:05AM -0800, Webb Sprague wrote: If they're that smart, they're smart enough to deal with SQL, and likely to be frustrated by a like-sql-but-not command language or a GUI query designer. Instead, create a user that only has enough access to read data (and maybe create temporary tables) and use that user to give them a sql commandline. It'll be drastically less development effort for you, and the end result is less likely to frustrate your users. Can't do that. (Or I wouldn't have asked the question.) Need a WWW interface, period. Why not just write a web interface that accepts SQL and renders the results into an HTML table? If you wanted to pretty it up a bit, you could write an AJAX ditty to present a nice GUI query builder for those that want it. The fun thing, in my eyes, would be to sit down and define a new DSL that exposes some subset of SQL that you're interested in. Once you've learnt about parsing and lexing, transforming the result into SQL will be easy. Coming up with an appropriately specific language would be a good research project for someone, it'd be interesting to see how much better than SQL it could be. You should be able to get the language a bit more regular and tidy, but it would be interesting to see what your users thought. Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Semi-customized queries? Subset of SQL? Accessing the parser? Injection?
On Nov 4, 2008, at 11:12 AM, Webb Sprague wrote: If they're that smart, they're smart enough to deal with SQL, and likely to be frustrated by a like-sql-but-not command language or a GUI query designer. Instead, create a user that only has enough access to read data (and maybe create temporary tables) and use that user to give them a sql commandline. It'll be drastically less development effort for you, and the end result is less likely to frustrate your users. Can't do that. (Or I wouldn't have asked the question.) Need a WWW interface, period. Thanks for the comment, though. That wasn't mentioned in your original question at all. (If your constraint is just has to be via a web browser then that's what anyterm is for, or even just a text field that accepts a sql query. If you really want them to build queries via a gui web form then you may well be able to find something pre-built, depending on your constraints - what clients you need to support, what web framework you're using and so on. Or do it with simple combo boxes if you want to limit the users to crippled queries.) I don't see anything that suggests hacking the SQL parser is going to be a useful thing to do. If you really think that's what you need then you might want to be a bit more specific about what your application constraints are. I'm guessing that roles, constraints, resource limits and possibly a sacrificial replicated database will provide the answer to your actual problem, but we'd need to know what that is first. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Best memory/planner settings for Postgres
We've got a dedicated database server running PostgresSQL 8.0.9 (yes, I know it needs upgrading), but I've noticed it looks criminally under-configured. Basically it's running on a server with 2 dual-core Intel Xeon 2.33 Ghz processors and 4Gb memory, but has the following settings in postgresql.conf shared_buffers = 1000 work_mem = 1024 effective_cache_size = 2500 default_statistics_target = 100 Maybe other settings should be the subject of focus too. The type of data we have uses quite extensive use of IN lists (e.g. WHERE target_id IN (3423452, 65465, 6523, 2436, 26464, 2646464, 4, 2644624, 264642, 5344342, 65746735, 25332, 6435375, 251353, 3573573, 357363634, 252523523, 235235235, 5688282, 28647532564, 452525, 335745, 376357357, 375757357, 3573735735) That's just an example as lists can often be a longer than that, and I've noticed it doesn't appear to be using the index on the column being queried. Queries such as that are used very frequently. We also make at least a couple joins on most queries and often use DISTINCT. Has anyone got recommendations on what the config settings should be set to? And also any other settings I have neglected to highlight? I feels like PostgreSQL is the only resident in a mansion, but is locked in a room on the ground floor. Any help would be appreciated. Thanks Thom -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] dumbofs - a postgresql filesystem
On Tue, Nov 4, 2008 at 10:36 AM, Yiannos Pericleous [EMAIL PROTECTED] wrote: hi all, i've created a filesystem that lets you view databases, schemas and tables of pg server as regular directories, and records as files. it is still in its infancy, though. more info at http://yiannnos.com/dumbofs Neat. No idea what I'd use it for, but it's an interesting idea! BTW, there's a typo on the webpage: here's a list of this that *could* go into dumbofs should be here's a list of things that *could* go into dumbofs -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] I'm puzzled by a foreign key constraint problem
Jonathan Guthrie wrote: It's possible, likely even. We use a connection pool to manage connections to the database and they're doled out as the system sees fit. However, at some point every update has to finish such that any view of the database will see that update as finished, right? So that any /new/ snapshot of the state of the database sees it, yes. If you have long-lived transactions at the SERIALIZABLE isolation level, they won't (by design) see changes committed by other transactions after the first statement in the SERIALIZABLE transaction has been issued. See the manual for more information on concurrency, MVCC, and transaction isolation. There are two reasons why I'm dissatisfied with that answer. First, my mandate is basically to create an interface layer for Postgres and then port the SQL Server stored procedures without changing how they work. This is unlikely to be possible. The two databases are extremely different in some important ways. In particular, MS SQL Server uses a locking approach to concurrency, whereas PostgreSQL uses an multi-version approach (more like Oracle). You should usually be able to make the locking approach work in PostgreSQL, but there *will* be differences you need to think about in the way procedures interact when run concurrently. You may need to add more explicit locking to correct for assumptions that're valid under MS SQL Server but not under PostgreSQL, or adjust your logic to exploit multi-versioning properly instead. You will get much better performance if you adapt your code to the MVCC model instead of trying to stick to using locking for concurrency control. It doesn't help that PostgreSQL does not at present support true stored procedures. There is no top-level CALLable procedure support; instead PostgreSQL has very powerful functions. The most important difference this makes is that you CAN NOT perform transaction control operations (BEGIN, ROLLBACK, COMMIT) within any procedural function in PostgreSQL. They are inherently wrapped in a transaction. You *can* RAISE EXCEPTION from PL/PgSQL to trigger a rollback (unless the caller traps and handles the exception), but there's no way to force a commit or begin a new and distinct transaction. OK, that's not absolutely 100% true. You can do it with dblink. You just don't want to. Anyway, if your MS SQL server stored procedures expect to be able to BEGIN a transaction, do some work, COMMIT it, then BEGIN another and do some more work before COMMITTING that second piece of work, you're going to have to do some redesign. The second reason is because adding permissions doesn't just happen at project creation time. That's fine. Nothing stops you from issuing something like: BEGIN; SELECT create_it(blah); SELECT set_permissions(blah, perms); COMMIT; and later using: SELECT set_permissions(blah, otherperms); standalone or inside another, unrelated transaction. The point is that if your initial create and the setting of the initial permissions must succeed or fail together, they MUST be done within a single transaction. That is, in fact, the fundamental point of database transactions. What you should avoid doing is: TRANSACTION 1 TRANSACTION 2 BEGIN; BEGIN; SELECT create_it(blah); SELECT set_permissions(blah, perms); COMMIT; COMMIT; ... because that just won't work. It sounds like you've got that right, but you might be doing this: TRANSACTION 1 TRANSACTION 2 BEGIN; BEGIN; SET transaction_isolation = SERIALIZABLE; -- do something else that triggers -- freezing of the transaction's snapshot, -- even something like: SELECT 1; SELECT create_it(blah); COMMIT; SELECT set_permissions(blah, perms); COMMIT; ... which will also fail. The software I work on is middleware for the actual client applications and the client can assign any user permissions to access the project just as soon as it knows the project's ID, which is one of the values returned by the project creation function. When the project's creation function returns, the created project is not yet visible to other transactions, even ones begun after the function returns. It only becomes visible after the transaction in which the create function was called COMMITs. This is fine if your later manipulations of the permissions etc happen within the same transaction as the initial create (as they should). However, if you're trying to refer to the created record from another transaction before the one that created the record has committed, it won't yet be visible. Furthermore, if the transaction trying to refer to the record created by some other transaction is at the
Re: [GENERAL] postgresql and Mac OS X
Grzegorz Jaśkiewicz wrote: I feel good about control here, and I certainly don't have any problems. So, please don't whine :) Especially since I want to run cvs head, and be able to actually update it from cvs when I want to, that's the only choice. Postgresql is so easy to get from sources, compared to other software packages, I can't understand people even with slightest expierence in unix to have any problems with it. I tried getting a source install on my mac book yesterday and today. It's not a normal *nix installation. The location of the files are all non-standard. 'make' is prefixed by /Developer/usr/bin/. I added /Developer/usr/bin to PATH and tried ./configure. checking build system type... i386-apple-darwin9.5.0 checking host system type... i386-apple-darwin9.5.0 checking which template to use... darwin checking whether to build with 64-bit integer date/time support... no checking whether NLS is wanted... no checking for default port number... 5432 checking for gcc... gcc checking for C compiler default output file name... configure: error: C compiler cannot create executables See `config.log' for more details. config.log shows an exit code of 77 with a statement that compiler cannot create executables. ??? configure:2213: $? = 0 configure:2215: gcc -v /dev/null 5 Using built-in specs. Target: i686-apple-darwin9 Configured with: /var/tmp/gcc/gcc-5488~2/src/configure --disable-checking -enabl e-werror --prefix=/usr --mandir=/share/man --enable-languages=c,objc,c++,obj-c++ --program-transform-name=/^[cg][^.-]*$/s/$/-4.0/ --with-gxx-include-dir=/includ e/c++/4.0.0 --with-slibdir=/usr/lib --build=i686-apple-darwin9 --with-arch=apple --with-tune=generic --host=i686-apple-darwin9 --target=i686-apple-darwin9 Thread model: posix gcc version 4.0.1 (Apple Inc. build 5488) configure:2218: $? = 0 configure:2220: gcc -V /dev/null 5 gcc-4.0: argument to `-V' is missing configure:2223: $? = 1 configure:2246: checking for C compiler default output file name configure:2249: gccconftest.c 5 ld: library not found for -lcrt1.10.5.o collect2: ld returned 1 exit status configure:2252: $? = 1 configure: failed program was: I think he questin is, what lib was missing so I can go find it and add it to some path/dir variable? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Semi-customized queries? Subset of SQL? Accessing the parser? Injection?
Or do it with simple combo boxes if you want to limit the users to crippled queries.) I want to limit my users to *half* crippled queries -- arbitrary column lists, where clauses, group by lists, and sort by lists. I want to make sure that they aren't doing any data modifications nested inside a where clause or a column definition as a subquery. I don't see anything that suggests hacking the SQL parser is going to be a useful thing to do. I would think that I could *use* (definitely not hack -- good god!) the parser to ask how deep the nested subqueries are, etc. I'm guessing that roles, constraints, resource limits and possibly a sacrificial replicated database will provide the answer to your actual problem, but we'd need to know what that is first. I am thinking that I may need to give them all, as in all or nothing..., and kind of follow David Wilson's plan above. I was hoping someone had already done what Sam Mason suggested as being the fun thing, though ... Oh -- I think query builders are a thing of the devil. Thanks to all for putting up with my lack of good of writing. -W -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] epqa; postgres performance optimizer support tool; opensource.
Dear All, Recently i have released the next version of the epqa. which is a very useful tool for, gives input for optimizing psql queries, and fine tuning it. epqa is tool similar like, pqa. But designed and implemented to parse log files which is in GB's. Report is similar like that. More information can be got from http://epqa.sourceforge.net/ Expecting suggestions, feedbacks, clarfications @ [EMAIL PROTECTED] Note: This is to propagate the open source which can help for postgres users. This is not a spam, or advertisement. Regards SathiyaMoorthy
Re: [GENERAL] postgresql and Mac OS X
On 4 nov 2008, at 11.21, Tom Allison wrote: I tried getting a source install on my mac book yesterday and today. It's not a normal *nix installation. The location of the files are all non-standard. 'make' is prefixed by /Developer/usr/bin/. That's not right. It should definately live in /usr/bin on a normal Mac OS X install. What versions of Mac OS X and the developer tools do you have? Did you make some non-standard choice during the installation of the dev tools? Sincerely, Niklas Johansson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] time interval format srting
Sam Mason [EMAIL PROTECTED] schrieb: On Tue, Nov 04, 2008 at 05:06:37PM +, Joao Ferreira gmail wrote: I've been searching the docs on a simple way to convert a time _duration_ in seconds to the format dd:hh:mm:ss, but I can't find it. 90061 -- 1d 1h 1m 1s (90061=24*3600+3600+60+1) any ideas ? I've been using to_char and to_timestamp to format dates/timestamps... but this is diferent... I want to format time intervals, durations.. How about doing: SELECT justify_interval(90061 * '1 second'::INTERVAL); Nice, didn't know this function. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] time interval format srting
Andreas Kretschmer replied: I wrote: How about doing: SELECT justify_interval(90061 * '1 second'::INTERVAL); Nice, didn't know this function. Yup, PG does everything! Not sure when I discovered it; also not sure if I've ever had to use it in anger before. I am, however, slightly embarrassed that I used the ugly form of interval literals, it looks prettier to me as: SELECT justify_interval(90061 * INTERVAL '1 second'); Ah well! Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Semi-customized queries? Subset of SQL? Accessing the parser? Injection?
On Nov 4, 2008, at 11:46 AM, Webb Sprague wrote: Or do it with simple combo boxes if you want to limit the users to crippled queries.) I want to limit my users to *half* crippled queries -- arbitrary column lists, where clauses, group by lists, and sort by lists. I want to make sure that they aren't doing any data modifications nested inside a where clause or a column definition as a subquery. I don't see anything that suggests hacking the SQL parser is going to be a useful thing to do. I would think that I could *use* (definitely not hack -- good god!) the parser to ask how deep the nested subqueries are, etc. Have you looked at the output from explain? That'll give you cost estimates, and fairly detailed data on how the query will be executed, including nested queries, index usage and so on. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] I'm puzzled by a foreign key constraint problem
On Wed, 2008-11-05 at 04:40 +0900, Craig Ringer wrote: The point is that if your initial create and the setting of the initial permissions must succeed or fail together, they MUST be done within a single transaction. That is, in fact, the fundamental point of database transactions. I understand that. Honestly, I do. If I hadn't ever said that odd things happen when the permissions aren't set, then maybe I could find out what I'm doing wrong. What you should avoid doing is: TRANSACTION 1 TRANSACTION 2 BEGIN; BEGIN; SELECT create_it(blah); SELECT set_permissions(blah, perms); COMMIT; COMMIT; ... because that just won't work. It sounds like you've got that right, but you might be doing this: TRANSACTION 1 TRANSACTION 2 BEGIN; BEGIN; SET transaction_isolation = SERIALIZABLE; -- do something else that triggers -- freezing of the transaction's snapshot, -- even something like: SELECT 1; SELECT create_it(blah); COMMIT; SELECT set_permissions(blah, perms); COMMIT; ... which will also fail. The thing is, the C++ code does this BEGIN transaction 1 INSERT project COMMIT BEGIN transaction 2 SET permissions COMMIT or, at least, it's supposed to. Those two operations are not supposed to overlap at all even if they're on two different connections. I thought I had verified this by looking at the log file. I mean, I can look at the log file and see things like 2008-11-03 16:29:22 CST DEBUG: 0: StartTransactionCommand and 2008-11-03 16:29:22 CST DEBUG: 0: CommitTransactionCommand where I would expect them to if what I'm expecting is going on, but the log file doesn't appear to have enough information to see a transaction created, proceed, and then end. That is, how do I know which transaction was started and which one was committed? I'm kind of confused by lines like this: 2008-11-03 16:29:22 CST DEBUG: 0: name: unnamed; blockState: INPROGRESS; state: INPROGR, xid/subid/cid: 678145/1/4, nestlvl: 1, children: 678146 678147 Is there an easy explanation somewhere? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql and Mac OS X
Francisco Figueiredo Jr. wrote: On Tue, Nov 4, 2008 at 8:21 AM, Tom Allison [EMAIL PROTECTED] wrote: Grzegorz Jaśkiewicz wrote: I feel good about control here, and I certainly don't have any problems. So, please don't whine :) Especially since I want to run cvs head, and be able to actually update it from cvs when I want to, that's the only choice. Postgresql is so easy to get from sources, compared to other software packages, I can't understand people even with slightest expierence in unix to have any problems with it. I tried getting a source install on my mac book yesterday and today. It's not a normal *nix installation. The location of the files are all non-standard. 'make' is prefixed by /Developer/usr/bin/. I added /Developer/usr/bin to PATH and tried ./configure. checking build system type... i386-apple-darwin9.5.0 checking host system type... i386-apple-darwin9.5.0 checking which template to use... darwin checking whether to build with 64-bit integer date/time support... no checking whether NLS is wanted... no checking for default port number... 5432 checking for gcc... gcc checking for C compiler default output file name... configure: error: C compiler cannot create executables See `config.log' for more details. config.log shows an exit code of 77 with a statement that compiler cannot create executables. ??? configure:2213: $? = 0 configure:2215: gcc -v /dev/null 5 Using built-in specs. Target: i686-apple-darwin9 Configured with: /var/tmp/gcc/gcc-5488~2/src/configure --disable-checking -enabl e-werror --prefix=/usr --mandir=/share/man --enable-languages=c,objc,c++,obj-c++ --program-transform-name=/^[cg][^.-]*$/s/$/-4.0/ --with-gxx-include-dir=/includ e/c++/4.0.0 --with-slibdir=/usr/lib --build=i686-apple-darwin9 --with-arch=apple --with-tune=generic --host=i686-apple-darwin9 --target=i686-apple-darwin9 Thread model: posix gcc version 4.0.1 (Apple Inc. build 5488) configure:2218: $? = 0 configure:2220: gcc -V /dev/null 5 gcc-4.0: argument to `-V' is missing configure:2223: $? = 1 configure:2246: checking for C compiler default output file name configure:2249: gccconftest.c 5 ld: library not found for -lcrt1.10.5.o collect2: ld returned 1 exit status configure:2252: $? = 1 configure: failed program was: I think he questin is, what lib was missing so I can go find it and add it to some path/dir variable? I think you need to install the developer tools. I compile postgresql from sources with no problem on osx 10.5.4 but I installed developer tools before. The library which is missing is the following: configure:2246: checking for C compiler default output file name configure:2249: gccconftest.c 5 ld: library not found for -lcrt1.10.5.o - crt1.10.5.o I hope it helps. It confirms what I'm working through. crt1.o located at /Developer/SDKs/MacOSX10.5.sdk/usr/lib/crt1.o crt1.10.5.0 at /Developer/SDKs/MacOSX10.5.sdk/usr/lib/crt1.10.5.o So I'm trying to find how to get these directories included in the compilation. I thought --with-libs and/or --with-includes would have helped. But it didn't. This is what I ran (I'm running this from a script so I can repeat it) -- cd /Users/tom/src/postgresql-8.3.4 export PATH=$PATH:/Developer/usr/bin/ ./configure \ --with-libs=/Developer/SDKs/MacOSX10.5.sdk/usr/lib/ \ --with-includes=/Developer/SDKs/MacOSX10.5.sdk/usr/lib/ But I'm on the same error... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] I'm puzzled by a foreign key constraint problem
Jonathan Guthrie [EMAIL PROTECTED] writes: ... or, at least, it's supposed to. Those two operations are not supposed to overlap at all even if they're on two different connections. I thought I had verified this by looking at the log file. I mean, I can look at the log file and see things like 2008-11-03 16:29:22 CST DEBUG: 0: StartTransactionCommand and 2008-11-03 16:29:22 CST DEBUG: 0: CommitTransactionCommand where I would expect them to if what I'm expecting is going on, but the log file doesn't appear to have enough information to see a transaction created, proceed, and then end. That is, how do I know which transaction was started and which one was committed? You need to add more identification info to your log_line_prefix. The PID would be the most reliable way to tie those entries together, but I think there's also an option that writes the transaction ID. I'm kind of confused by lines like this: 2008-11-03 16:29:22 CST DEBUG: 0: name: unnamed; blockState: INPROGRESS; state: INPROGR, xid/subid/cid: 678145/1/4, nestlvl: 1, children: 678146 678147 Is there an easy explanation somewhere? You'd have to look at the source code to figure out most of the DEBUG-level messages. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql and Mac OS X
Tom Allison [EMAIL PROTECTED] writes: I tried getting a source install on my mac book yesterday and today. It's not a normal *nix installation. The location of the files are all non-standard. 'make' is prefixed by /Developer/usr/bin/. The question is *why* the location is nonstandard. Other people's Macs are not set up that way (mine seems to have these files in the expected place, for example). I added /Developer/usr/bin to PATH and tried ./configure. That would help configure find the stuff in /Developer/usr/bin, but it does nothing for files that ought to be in /usr/lib, /usr/include, etc. I am not sure whether adding these to the configure command would be sufficient: --with-includes=/Developer/usr/include --with-libraries=/Developer/usr/lib On the whole the best thing would be to toss /Developer and reinstall your devtools in the standard places. The nonstandard location is going to bite you for every package you work with, not only Postgres. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql and Mac OS X
On Nov 4, 2008, at 1:02 PM, Tom Allison wrote: I tried getting a source install on my mac book yesterday and today. It's not a normal *nix installation. The location of the files are all non-standard. 'make' is prefixed by /Developer/usr/bin/. It's in /usr/bin/make on my OS X box (as well as in /Developer/usr/bin/ make) If I recall correctly there's an option during the XCode install to include the commandline tools, which may be what you're missing It confirms what I'm working through. crt1.o located at /Developer/SDKs/MacOSX10.5.sdk/usr/lib/crt1.o crt1.10.5.0 at /Developer/SDKs/MacOSX10.5.sdk/usr/lib/crt1.10.5.o So I'm trying to find how to get these directories included in the compilation. I thought --with-libs and/or --with-includes would have helped. But it didn't. This is what I ran (I'm running this from a script so I can repeat it) That's the runtime. If that's not being included then your development environment is utterly broken, and messing with configure flags won't fix it. Give up on postgresql/configure for now, reinstall XCode with the commandline tools and check that you can build hello world from the commandline. Then start over with a clean postgresql tarball. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Semi-customized queries? Subset of SQL? Accessing the parser? Injection?
On Tue, Nov 4, 2008 at 12:46 PM, Webb Sprague [EMAIL PROTECTED] wrote: Or do it with simple combo boxes if you want to limit the users to crippled queries.) I want to limit my users to *half* crippled queries -- arbitrary column lists, where clauses, group by lists, and sort by lists. I want to make sure that they aren't doing any data modifications nested inside a where clause or a column definition as a subquery. Well, setting proper permissions will prevent them from making changes. So I do think the generic throw a query at the db and turn the result into a table will probably work ok. As long as you aren't talking millions of rows. You could detect result sets over x number of rows and just give the user a link to download the data in a csv file if it's over that threshold. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Error al crear una base en español.....
hola quiero crear una base que soporte caracteres en español y le doy este comando pero no crea la base sino me manda este error: -bash-3.2$ ./createdb --encoding=LATIN1 sig_spa_prueba Password: createdb: database creation failed: ERROR: encoding LATIN1 does not match server's locale en_US.UTF-8 DETAIL: The server's LC_CTYPE setting requires encoding UTF8. gracias
Re: [GENERAL] INSERT with RETURNING clause inside SQL function
On Tue, Nov 4, 2008 at 2:38 AM, Tom Lane [EMAIL PROTECTED] wrote: Diego Schulz [EMAIL PROTECTED] writes: On Mon, Nov 3, 2008 at 10:24 PM, Raymond O'Donnell [EMAIL PROTECTED] wrote: Just curious - what have you got against currval()? It seems to me that it would make your life easier I simply don't like having to cast from BIGINT to INTEGER, Under what circumstances do you need an explicit cast? regards, tom lane When I want the function to return the same type as the index of the table (normally SERIAL), and I have other functions that rely on the datatype returned. To avoid casting I can simply change the function's signature to return BIGINT (to match currval() return type) and the problem vanishes but.. then I have more functions that needs to be adapted. Maybe I'm a bit paranoid of BIGINT's performance penalty too, as the set of functions will be heavily used, but honestly, this fear completely lacks foundation. Just to make it clear, the main reason for this thread was curiosity :) Thank you for your time. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] I'm puzzled by a foreign key constraint problem
On Tue, 2008-11-04 at 07:49 +, Richard Huxton wrote: Jonathan Guthrie wrote: When I create a project, entries in the project table and the resource table are created in a single function. Then, separate functions are called to set the owner's access to the new project. These other functions are failing because of the resourceid foreign key constraint. Have you turned statement logging on? Your message suggests that's the case, but didn't say so explicitly. Are the two steps: 1. Create project, resource 2. Set access-rights done in separate connections by any chance? If so it's possible (due to MVCC) that #2 is still seeing the database as it was before #1 committed. It's possible, likely even. We use a connection pool to manage connections to the database and they're doled out as the system sees fit. However, at some point every update has to finish such that any view of the database will see that update as finished, right? Anyway, I need for these operations to succeed because the lack of permissions causes odd problems in other parts of the system. If you want both steps to succeed or fail together though, they need to be in the same transaction. That's what Mr Ringer said, and although I understand that answer and I understand the reason that two people have independently responded with it, I'm dissatisfied with it. There are two reasons why I'm dissatisfied with that answer. First, my mandate is basically to create an interface layer for Postgres and then port the SQL Server stored procedures without changing how they work. If I change the logic in this part, it will be different not only from the mechanism used in the original SQL Server stored procedure, but also different from the logic used in other stored procedures that do similar things. The second reason is because adding permissions doesn't just happen at project creation time. The software I work on is middleware for the actual client applications and the client can assign any user permissions to access the project just as soon as it knows the project's ID, which is one of the values returned by the project creation function. If the issue is a difference in views because the requests come in on different connections, then there's a time window during which a valid and unanticipatable request from the client could fail if the request happens to use a connection to communicate with the database that is different from the one used to create the project. Anyway, while I agree that adding the logic to set permissions to the project create function seems the simplest approach to dealing with the issue, I'd really rather not change the logic until I've thoroughly explored all other options. I CAN guarantee that the other operations on a project definitely won't begin until the create is committed. So, is there any way of causing a commit to not return until all the views are consistent? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Error al crear una base en español.....
On Tue, Nov 4, 2008 at 6:32 PM, Eduardo Arévalo [EMAIL PROTECTED] wrote: hola quiero crear una base que soporte caracteres en español y le doy este comando pero no crea la base sino me manda este error: -bash-3.2$ ./createdb --encoding=LATIN1 sig_spa_prueba Password: createdb: database creation failed: ERROR: encoding LATIN1 does not match server's locale en_US.UTF-8 DETAIL: The server's LC_CTYPE setting requires encoding UTF8. gracias Perdón por responder con una pregunta, pero cual es el problema con UTF-8 ? Deberías poder lograr lo mismo con UTF-8. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Best memory/planner settings for Postgres
-Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-general- [EMAIL PROTECTED] On Behalf Of Thom Brown Sent: Tuesday, November 04, 2008 1:45 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Best memory/planner settings for Postgres We've got a dedicated database server running PostgresSQL 8.0.9 (yes, I know it needs upgrading), but I've noticed it looks criminally under-configured. Basically it's running on a server with 2 dual-core Intel Xeon 2.33 Ghz processors and 4Gb memory, but has the following settings in postgresql.conf shared_buffers = 1000 work_mem = 1024 effective_cache_size = 2500 default_statistics_target = 100 Maybe other settings should be the subject of focus too. The type of data we have uses quite extensive use of IN lists (e.g. WHERE target_id IN (3423452, 65465, 6523, 2436, 26464, 2646464, 4, 2644624, 264642, 5344342, 65746735, 25332, 6435375, 251353, 3573573, 357363634, 252523523, 235235235, 5688282, 28647532564, 452525, 335745, 376357357, 375757357, 3573735735) That's just an example as lists can often be a longer than that, and I've noticed it doesn't appear to be using the index on the column being queried. Queries such as that are used very frequently. We also make at least a couple joins on most queries and often use DISTINCT. Has anyone got recommendations on what the config settings should be set to? And also any other settings I have neglected to highlight? I feels like PostgreSQL is the only resident in a mansion, but is locked in a room on the ground floor. http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server http://www.powerpostgresql.com/Downloads/annotated_conf_80.html http://www.powerpostgresql.com/download/TFCKUpload/5.x-pdf http://www.postgresql.org/docs/8.3/interactive/runtime-config-resource.h tml http://www.scribd.com/doc/4846381/PostgreSQL-Performance-Tuning -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql and Mac OS X
On Nov 4, 2008, at 4:14 PM, Tom Lane wrote: Tom Allison [EMAIL PROTECTED] writes: I tried getting a source install on my mac book yesterday and today. It's not a normal *nix installation. The location of the files are all non-standard. 'make' is prefixed by /Developer/usr/bin/. The question is *why* the location is nonstandard. Other people's Macs are not set up that way (mine seems to have these files in the expected place, for example). I added /Developer/usr/bin to PATH and tried ./configure. That would help configure find the stuff in /Developer/usr/bin, but it does nothing for files that ought to be in /usr/lib, /usr/include, etc. I am not sure whether adding these to the configure command would be sufficient: --with-includes=/Developer/usr/include --with-libraries=/Developer/ usr/lib /Developer/usr/ shouldn't be linked against directly- this is the location for OS X SDKs, so that binaries can be built and linked which work on older versions of OS X than one is currently using. Cheers, M -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] FullText index
Hello, we have a table where is a column with language ISO code like en, de, ... How can i build the index, depending on this languagecode? From the manual: to_tsvector([ config regconfig , ] document text) Where is documented what config regconfig can be? Thanks Andreas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql and Mac OS X
Tom Lane wrote: Tom Allison [EMAIL PROTECTED] writes: I tried getting a source install on my mac book yesterday and today. It's not a normal *nix installation. The location of the files are all non-standard. 'make' is prefixed by /Developer/usr/bin/. The question is *why* the location is nonstandard. Other people's Macs are not set up that way (mine seems to have these files in the expected place, for example). I added /Developer/usr/bin to PATH and tried ./configure. That would help configure find the stuff in /Developer/usr/bin, but it does nothing for files that ought to be in /usr/lib, /usr/include, etc. I am not sure whether adding these to the configure command would be sufficient: --with-includes=/Developer/usr/include --with-libraries=/Developer/usr/lib On the whole the best thing would be to toss /Developer and reinstall your devtools in the standard places. The nonstandard location is going to bite you for every package you work with, not only Postgres. regards, tom lane I have installed xcode311_2517_developerdvd that I added after I installed the Leopard OS. This was an upgrade from Tiger but that puked so I installed Leopard from scratch. I will try installing this package again. (note: Unix Tools is checked) Running just ./configure I got past that part... And finished the configure. So, the answer seems to be that I did not install the Unix Tools portion of the XCode tools. Which naturally is so very obvious for installation of anything used to unix installations... I did strictly the default installation. Sorry to run everyone through these loops. But now we all know something new about Mac OSX -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Storage location of temporary files
On 2008-10-31 09:01, Christian Schröder wrote: We will now move the database to a raid5 (which should be faster than the raid1) This is wrong. RAID5 is slower than RAID1. You should go for RAID1+0 for fast and reliable storage. Or RAID0 for even faster but unreliable. Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgresql installation - PL/???
This should be a dumb question: --with-perl I don't see that I have to do this in order to load pl/perl as a function/trigger language option. So I should assume that this will compile pl/perl in rather than having it available as a loadable function. Nice for optimizations? I'm assuming this is the case because there is no option for something like PL/Ruby support, but Ruby is available as a loadable function. And should I also be able to assume that PL/PgSQL is compiled into postgresql? so I don't really need to use any particular flags, with the probable exception of --with-ssl? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] GEQO randomness?
Eric Schwarzenbach wrote: This is in a sense a followup to my post with subject Wildly erratic query performance. The more I think about it the only thing that makes sense of my results is if the query planner really WAS choosing my join order truly randomly each time. I went digging into the manual and Section 49.3.1. Generating Possible Plans with GEQO says In the initial stage, the GEQO code simply generates some possible join sequences at random. Now ordinarily I would interpret this use of the word random loosely, to mean arbitrarily or using some non-meaningful selection criteria. But given what I am seeing, this leads me to consider that random is meant literally, and that it uses a random number generate to pick paths. Can someone confirm that this is the case? Yes, random means random. Is this really a good idea? Is non-deterministic behavior really acceptable? I would think it would be much more sensible to have it operate deterministically (such as with some predetermined random sequence of numbers used repeatedly). Uh, no one has ever asked for that. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: pg_casts view (was Re: date_trun() with timezones? (was Re: [GENERAL] TIME column ...))
Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane escribió: There already is a \dC command in psql, which has nice enough output format but doesn't provide any way to select a subset of the table. Maybe we should just agree that its argument is a pattern for the castsource type's name? Yeah, that sounds good enough ... I seem to recall having used casttarget as condition a couple of times, but I think it's a strange enough case that it is OK to just modify the query when that's needed; normal usage would seem to be what you propose. Here's a draft patch for this. One possible objection is that the default behavior changes subtly: only casts whose source types are visible in the search path will be shown by default. In practice I doubt that will make any difference, so I didn't bother to try to avoid it --- we could special-case no pattern but I think it'd look like a wart before long. Comments? regards, tom lane Index: doc/src/sgml/ref/psql-ref.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v retrieving revision 1.211 diff -c -r1.211 psql-ref.sgml *** doc/src/sgml/ref/psql-ref.sgml 6 Sep 2008 20:18:08 - 1.211 --- doc/src/sgml/ref/psql-ref.sgml 4 Nov 2008 22:44:08 - *** *** 894,903 varlistentry ! termliteral\dC/literal/term listitem para Lists all available type casts. /para /listitem /varlistentry --- 894,906 varlistentry ! termliteral\dC [ replaceable class=parameterpattern/replaceable ]/literal/term listitem para Lists all available type casts. + If replaceable class=parameterpattern/replaceable + is specified, only casts whose source types match the pattern are + listed. /para /listitem /varlistentry Index: src/bin/psql/describe.c === RCS file: /cvsroot/pgsql/src/bin/psql/describe.c,v retrieving revision 1.186 diff -c -r1.186 describe.c *** src/bin/psql/describe.c 3 Nov 2008 19:08:56 - 1.186 --- src/bin/psql/describe.c 4 Nov 2008 22:44:08 - *** *** 2082,2091 initPQExpBuffer(buf); /* !* We need left join here for binary casts. Also note that we don't !* attempt to localize '(binary coercible)', because there's too much !* risk of gettext translating a function name that happens to match !* some string in the PO database. */ printfPQExpBuffer(buf, SELECT pg_catalog.format_type(castsource, NULL) AS \%s\,\n --- 2082,2091 initPQExpBuffer(buf); /* !* We need a left join to pg_proc for binary casts; the others are just !* paranoia. Also note that we don't attempt to localize '(binary !* coercible)', because there's too much risk of gettext translating a !* function name that happens to match some string in the PO database. */ printfPQExpBuffer(buf, SELECT pg_catalog.format_type(castsource, NULL) AS \%s\,\n *** *** 2099,2111 END as \%s\\n FROM pg_catalog.pg_cast c LEFT JOIN pg_catalog.pg_proc p\n ON c.castfunc = p.oid\n ! ORDER BY 1, 2, gettext_noop(Source type), gettext_noop(Target type), gettext_noop(Function), gettext_noop(no), gettext_noop(in assignment), gettext_noop(yes), gettext_noop(Implicit?)); res = PSQLexec(buf.data, false); termPQExpBuffer(buf); if (!res) --- 2099,2125 END as \%s\\n FROM pg_catalog.pg_cast c LEFT JOIN pg_catalog.pg_proc p\n ON c.castfunc = p.oid\n ! LEFT JOIN pg_catalog.pg_type t\n ! ON c.castsource = t.oid\n ! LEFT JOIN pg_catalog.pg_namespace n\n ! ON n.oid = t.typnamespace\n, gettext_noop(Source type), gettext_noop(Target type), gettext_noop(Function), gettext_noop(no), gettext_noop(in assignment), gettext_noop(yes),
Re: [GENERAL] postgresql and Mac OS X
'make' is prefixed by /Developer/usr/bin/. The question is *why* the location is nonstandard. Starting with Xcode 3, all the developer tools get installed under the Developer directory, in order to allow one to easily have multiple versions of Xcode installed alongside each other. The question then is why the OP doesn't also have make in /usr/bin, or why his path is configured so that it finds /Developer/usr/bin first--*that* is what is non-standard. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql and Mac OS X
Scott Ribe wrote: 'make' is prefixed by /Developer/usr/bin/. The question is *why* the location is nonstandard. Starting with Xcode 3, all the developer tools get installed under the Developer directory, in order to allow one to easily have multiple versions of Xcode installed alongside each other. The question then is why the OP doesn't also have make in /usr/bin, or why his path is configured so that it finds /Developer/usr/bin first--*that* is what is non-standard. There is an option during installation for a Unix Tools installation. Which puts everything where it's expected. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] I'm puzzled by a foreign key constraint problem
Jonathan Guthrie wrote: The thing is, the C++ code does this BEGIN transaction 1 INSERT project COMMIT BEGIN transaction 2 SET permissions COMMIT or, at least, it's supposed to. OK, and we know that if it is doing what it is supposed to, transaction 2 /must/ see the changes made by transaction 1. Either it's not doing what it's supposed to, or you've hit a mind bogglingly huge bug in PostgreSQL that's never been noticed before. I'm inclined to suspect the former. Tom Lane pointed out where to go from here re the server logging. It'll be interesting to see what turns up once you've got new logs that show the backend pid and the involved xid. Sorry about hammering on the point re transaction interleaving and so on. You do see people here who've misunderstood how MVCC visibility works, and I wasn't sure from your mail that your transactions didn't overlap. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installation Error of postgresql-8.1.5 with perl.
Hello Tom, During configure I find the error in config.log file checking for flags to link embedded Perl... Can't locate ExtUtils/Embed.pm in @INC (@INC contains: /usr/lib/perl5/5.10.0/i386-linux-thread-multi /usr/lib/perl5/5.10.0 /usr/lib/perl5/site_perl/5.10.0/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.10.0 /usr/lib/perl5/site_perl/5.8.8 /usr/lib/perl5/site_perl/5.8.7 /usr/lib/perl5/site_perl/5.8.6 /usr/lib/perl5/site_perl/5.8.5 /usr/lib/perl5/site_perl /usr/lib/perl5/vendor_perl/5.10.0/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.10.0 /usr/lib/perl5/vendor_perl/5.8.8 /usr/lib/perl5/vendor_perl/5.8.7 /usr/lib/perl5/vendor_perl/5.8.6 /usr/lib/perl5/vendor_perl/5.8.5 /usr/lib/perl5/vendor_perl .). and when I execute the make command that time I find the following errors. make -C error SUBSYS.o make[4]: Entering directory `/home/postgres/postgresql-8.1.5/src/backend/utils/error' gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fpic -I. -I../../../src/include -D_GNU_SOURCE -I/usr/lib/perl5/5.10.0/i386-linux-thread-multi/CORE -c -o plperl.o plperl.c plperl.c:67:20: error: EXTERN.h: No such file or directory plperl.c:68:18: error: perl.h: No such file or directory plperl.c:69:18: error: XSUB.h: No such file or directory ppport.h:174:24: error: patchlevel.h: No such file or directory ppport.h:177:44: error: could_not_find_Perl_patchlevel.h: No such file or directory ppport.h:371: error: expected â)â before â*â token ppport.h:563: warning: type defaults to âintâ in declaration of âSVâ ppport.h:563: error: expected â;â, â,â or â)â before â*â token plperl.c:122: error: expected â=â, â,â, â;â, âasmâ or â__attribute__â before â*â token plperl.c:123: error: expected â=â, â,â, â;â, âasmâ or â__attribute__â before â*â token plperl.c:145: error: expected â=â, â,â, â;â, âasmâ or â__attribute__â before â*â token plperl.c:147: error: expected â=â, â,â, â;â, âasmâ or â__attribute__â before â*â token plperl.c:324: error: âplperl_proc_hashâ undeclared (first use in this function) plperl.c:374: error: âSVâ undeclared (first use in this function) plperl.c:374: error: âresâ undeclared (first use in this function) plperl.c:417: error: expected â)â before â*â token plperl.c:451: error: expected â=â, â,â, â;â, âasmâ or â__attribute__â before â*â token plperl.c:480: error: expected â=â, â,â, â;â, âasmâ or â__attribute__â before â*â token plperl.c:576: error: expected â)â before â*â token plperl.c:736: error: expected â=â, â,â, â;â, âasmâ or â__attribute__â before â*â token plperl.c:831: error: expected â=â, â,â, â;â, âasmâ or â__attribute__â before âvoidâ plperl.c:832: error: expected â=â, â,â, â;â, âasmâ or â__attribute__â before âvoidâ plperl.c:844: error: expected â=â, â,â, â;â, âasmâ or â__attribute__â before â*â token plperl.c:939: error: expected â=â, â,â, â;â, âasmâ or â__attribute__â before â*â token plperl.c:1000: error: âSVâ undeclared (first use in this function) plperl.c:1000: error: âperlretâ undeclared (first use in this function) plperl.c:1001: warning: ISO C90 forbids mixed declarations and code plperl.c:1003: error: âarray_retâ undeclared (first use in this function) plperl.c:1032: warning: implicit declaration of function âplperl_call_perl_funcâ plperl.c:1051: warning: implicit declaration of function âSvTYPEâ plperl.c:1051: error: âSVt_RVâ undeclared (first use in this function) plperl.c:1052: warning: implicit declaration of function âSvRVâ plperl.c:1052: error: âSVt_PVAVâ undeclared (first use in this function) plperl.c:1055: error: âsvpâ undeclared (first use in this function) plperl.c:1056: error: âAVâ undeclared (first use in this function) plperl.c:1056: error: âravâ undeclared (first use in this function) plperl.c:1056: error: expected expression before â)â token plperl.c:1058: warning: implicit declaration of function âav_fetchâ plperl.c:1060: warning: implicit declaration of function âplperl_return_nextâ plperl.c:1064: error: âSVt_NULLâ undeclared (first use in this function) plperl.c:1095: warning: implicit declaration of function âSvOKâ plperl.c:1096: error: âSVt_PVHVâ undeclared (first use in this function) plperl.c:1114: warning: implicit declaration of function âplperl_build_tuple_resultâ plperl.c:1114: error: âHVâ undeclared (first use in this function) plperl.c:1114: error: expected expression before â)â token plperl.c:1114: warning: assignment makes pointer from integer without a cast plperl.c:1122: warning: implicit declaration of function âSvROKâ plperl.c:1125: warning: implicit declaration of function âplperl_convert_to_pg_arrayâ plperl.c:1126: warning: implicit declaration of function âSvREFCNT_decâ plperl.c:1130: warning: implicit declaration of function âSvPVâ plperl.c:1130: error: ânaâ undeclared (first use in this function) plperl.c:1130: warning: assignment makes pointer from integer without a cast plperl.c: In function âplperl_trigger_handlerâ:
Re: [GENERAL] Storage location of temporary files
Tomasz Ostrowski wrote: This is wrong. RAID5 is slower than RAID1. You should go for RAID1+0 for fast and reliable storage. Or RAID0 for even faster but unreliable. I did not find a clear statement about this. I agree that RAID10 would be better than RAID5, but in some situations RAID5 at least seems to be faster than RAID1. If I have 5 disks available, how should I use them to get best performance without the risk of severe data loss? If I use 4 of the disks to build a RAID10 then I will have only 1 remaining drive, e.g. to put the pgsql_tmp directories there. In this scenario I would not have the WAL on a separate disk. Or should I use 3 disks to build a RAID5, 1 disk for tempspace and 1 disk for WAL? How important is data integrity for the WAL? If the WAL disk fails, can this corrupt my data? Or would I just lose the data after the last checkpoint? Or maybe I should use 2 disks as RAID1 for the database, 2 disks as RAID1 for the WAL and the remaining disk for the tempspace? Regards, Christian -- Deriva GmbH Tel.: +49 551 489500-42 Financial IT and Consulting Fax: +49 551 489500-91 Hans-Böckler-Straße 2 http://www.deriva.de D-37079 Göttingen Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [Fwd: Re: [GENERAL] GEQO randomness?]
On Fri, Oct 31, 2008 at 06:57:25PM -0400, Eric Schwarzenbach wrote: My problem with GEQO using a random number generator is that non-deterministic behavior is really hard to debug, and problems can go undiagnosed for ages. Frankly I would rather something fail all the time, than it work most of the time and fail just now and then. Never getting a good plan for a query would be an improvement because I would immediately be aware there's a problem and be forced to something about it, as opposed to maybe realizing there is going to *sometimes* be a problem. I havn't seem any explains here showing wildly different results, but GEQO is not nearly as non-deterministic as you suggest. The problem is that if you are joining 100 tables it might take the length of the universe to check all combinations. GEQO is an algorithm to find a pretty good plan, and by and large it works well. If you don't like it, turn it off (it's called geqo_threshold of something like that). Or post some actual examples of it going wrong. Seems to me like your stats are out to lunch, which means postgres can't find the best planeven if it wanted to. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
