Re: [GENERAL] I want to search my project source code
Matthew Wilson wrote: I have a lot of code -- millions of lines at this point, written over the last 5 years. Everything is in a bunch of nested folders. At least once a week, I want to find some code that uses a few modules, so I have to launch a find + grep at the top of the tree and then wait for it to finish. I wonder if I could store our source code in a postgresql table and then use full text searching to index. Then I hope I could run a query where I ask for all files that use modules X, Y, and Z. DBMSs are great tools for the right job, but IMO this is not the right job. I can't see how a database engine, with all it's transactional overhead and many other layers, will ever beat a simple grep performance-wise. I've used Eclipse for refactoring, but having done it once, I'm sticking with grep. -- Guy Rouillier ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Postgres+Apache+PHP (again, but in different setup)
Hi all, I've read everything in this subject you guys has discussed already (including topic started by Max Zorloff) but I haven't found my answer yet. I'm looking for hints on best configuration for my specific needs: I'm having complied program written in C, which reads lots of data from cellular network. Then my C code is running some complex transformations and finally inserts/updates data in main partitioned Postgres table and few standard tables. In production, the data will be coming all the time, around 30.000inserts/minute. Then, I have set of WebServices, which are invoked by limited number of customers (3-6 different logins) and runs some selects/DMLs against my database, no more then ~50queries/minute. Now, the question is: how my C code should connect to Postgres? You've recommended pgpool and pgbouncer, but I believe these connection poolers are rather to be used for webpages, where there is a lot of querying from different users. Am I wrong? Now I'm using pgconnect (yes, I've read it's "broke" but is working fine for me .).. Do you see reasons why I, in this setup, should move to any of connection poolers? Or there is a better way? The second question is should I go for connection poolers for my WebServices as well? If so, what are the reasons? From configuration POV, I'm using Apache, PHP and Postgres 8.2 server. Final hardware setup is yet to be decided. Will appreciate all good comments. BR, [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Fragments in tsearch2 headline
On Sat, 27 Oct 2007, Tom Lane wrote:
"Catalin Marinas" <[EMAIL PROTECTED]> writes:
Is there an easy way to generate a headline from separate fragments
containing the search words and maybe separated by "..."?
Hmm, the documentation for ts_headline claims it does this already:
ts_headline accepts a document along
with a query, and returns one or more ellipsis-separated excerpts from
the document in which terms from the query are highlighted.
However, a quick look at the code suggests this is a lie --- I see no
evidence whatever that there's any smarts for putting in ellipses.
Oleg, Teodor, is there something I missed here? Or do we need to change
the documentation?
Probably documentation is not correct here. 'ellipsis-separated' should be
treated as a general wording. Default highlighting is .. as it
stated below in docs.
postgres=# select ts_headline('this is a highlighted
text','highlight'::tsquery, 'StartSel=...,StopSel=...')
postgres-# ;
ts_headline
--
this is a ...highlighted... text
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Fragments in tsearch2 headline
"Catalin Marinas" <[EMAIL PROTECTED]> writes: > Is there an easy way to generate a headline from separate fragments > containing the search words and maybe separated by "..."? Hmm, the documentation for ts_headline claims it does this already: ts_headline accepts a document along with a query, and returns one or more ellipsis-separated excerpts from the document in which terms from the query are highlighted. However, a quick look at the code suggests this is a lie --- I see no evidence whatever that there's any smarts for putting in ellipses. Oleg, Teodor, is there something I missed here? Or do we need to change the documentation? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How can I easily and effectively support (language) translation of column values?
On Sun, Oct 28, 2007 at 01:59:22AM +0200, Dennis Brakhane wrote: > I have some problems supporting "translatable columns" in a way that > is both efficient and comfortable for the programmer who has to write > SQL-Queries (we use iBatis) Maybe this helps a bit: http://salaam.homeunix.com/~ncq/gnumed/schema/gnumed-schema.html Look at the tables und functions under the i18n schema. Basically it provides a _() reimplementation in SQL, so select _(translatable_column) as translated_column from table_with_translatable_columns ... works as expected. Which language to translate to is set in another table which is evaluated by the _() function at runtime. You could evaluate that table from a view base on current_user() if you don't want to write 50+ views for the different languages. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] How can I easily and effectively support (language) translation of column values?
Hi, I have some problems supporting "translatable columns" in a way that is both efficient and comfortable for the programmer who has to write SQL-Queries (we use iBatis) Suppose I have a simple table that contains an id field and (amongst others) a name field. Let's call this table foo. The names are english names. These english names need to be translatable (and the translations have to be "by id", not "by name"; for example: the pair (1, 'foobar') has to be translated as (1, 'barfoo'), but (2, 'foobar') might be (2, 'baz') and not (2, 'foobar') I've tried to solve the problem in the following way (if you know of a better method, please tell me) I created a table foo_translations, that contains the columns foo_id, lang_id, and (translated) name: SELECT * FROM foo WHERE id = 1; id | name + 1 | foobar SELECT * FROM foo_translations WHERE foo_id=1; foo_id | lang_id | name +-+ 1 | 1 | barfoo 1 | 2 | boofoo Now, whenever I create a query (I use iBatis and therefore more or less write each query by hand), I can get the result I want by writing something like this: SELECT foo.id, COALESCE(foo_translations.name, foo.name) AS name FROM foo LEFT JOIN foo_translations ON foo.id=foo_id AND lang_id=? WHERE id = ? While this works, it's quite cumbersome having to do this for every query that involves tables with "translatable names". So my first idea was to create a view that will give me the correctly translated fields, like CREATE VIEW foo1 AS SELECT foo.id, COALESCE(foo_translations.name, foo.name) AS name FROM foo LEFT JOIN foo_translations ON foo.id = foo_translations.foo_id AND foo_translations.lang_id = 1; This way I could rewrite the last SELECT-statement (assuming the language-code is 1) as simply SELECT * FROM foo1 WHERE id = ? But this would mean I'd have to define (potentially) 50+ VIEWS for every table with translatable fields and I'd have the create a new SQL-Query everytime I use it (because I'd have to insert the language code right after "FROM foo", and I don't think PreparedStatement (we use Java) can handle this kind of placeholder "FROM foo?" ) So what I really need is a view that takes a parameter. Unfortunately, it seems like these don't exist. I can work around it by using a stored proc: CREATE FUNCTION foo(int) RETURNS SETOF foo AS $$ SELECT foo.id, COALESCE(foo_translations.name, foo.name) AS name FROM foo LEFT JOIN foo_translations ON foo.id=foo_id AND lang_id=$1$$ LANGUAGE SQL; While this works - I can now define a query like SELECT * FROM foo(?) WHERE id=? - the planner has no clue how to optimize this (dummy table contains 1 million entries): EXPLAIN ANALYSE SELECT * FROM foo(1) WHERE id=1; QUERY PLAN -- Function Scan on foo (cost=0.00..15.00 rows=5 width=36) (actual time=2588.982..3088.498 rows=1 loops=1) Filter: (id = 1) Total runtime: 3100.398 ms (3 rows) which obviosly is intolerable. So, do I have to bite the bullet and use the ugly "COALESCE/JOIN" statements everywhere or is there a better way? Any help is appreciated. Thanks in advance, Dennis ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] select count() out of memory
On Friday 26 October 2007 8:56 am, [EMAIL PROTECTED] wrote: > > Serious engineering does not imply perfect engineering, I have analyzed it > and made my tradeoffs. What you are forgetting here is that you clearly > dont understand the enire solution, So I will try to explain it again. And > if you still think its bonkers, the I urge you to come up with a solution > that works with the requirements. > > Every predefined X seconds (e.g. 3,6,9,12 etc ) a bunch of data arrives, > which must be stored by descrete time groups, e.g. second 3,6,9,12. The > data that arrives is approx 4MB per second, so in this case its 12MB. This > has to be processed by the server and written to the db, within 1 second. > There can be up to 5 writers at the same time. Within that same second, at > least 16 readers should be able to read all the data, *each*. Writers and > readers are only concerned with the latest data, i.e. data from the latest > time group, e.g. second 9. > This has to go on every predefined seconds for the next 6-12 weeks, > without stop, pause or any errors. These are the requirements. > > When I performed performance tests I found several unwanted effects from > several test scenarios. Here are the most important ones: > > - single large table, with indexes created when table is created. >- this leads to the performance of an insert degrading as more data is > added, when I get > to 1 billion rows it took 50 seconds to add the data. > > My lesson from this is that > - single inserts can never be efficient enough > - indexes cause linear performance drop as data volume increases > > So I tried a different approach, which would address both issues: > > - separate tables for each bulk of data >- use of bulk insert through jdbc COPY. >- add indexes to the newly create table after the copy is finished. > >My lesson from this is: >- insert take constant time, no matter how much data is in the base >- adding the indexes after insert takes constant time, i.e. some > milliseconds. > > From this I realised that using either single tables or partitions is the > way to go, since I only need to access the latest data, i.e. the newest > table, in normal situations. > > After thinking about it and discussing with this group, I found that using > partitions would be more practical for two reasons: > - changes to the parent table is automatically propagated to all child > tables, so the schema remains consistent and the server wont brake because > of differences in the tables. > - it is more maintainable to use "create with inheritance" sql in source > code than the entire ddl of the table. > > So now I have tested the server 24/7 for a week and a half, with 1 writer > and 16 readers writing all the mentioned data, and everything works fine. > Expect for the select on the parent table, which now runs out of memory. > Which in it self is not a problem since I will never use the parent table > in production in any case. > > regards > > tom I might be missing the point, but couldn't you do a Copy to a single table instead of multiple inserts and avoid the index overhead. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Version 8.3
On 10/27/07, Bob Pawley <[EMAIL PROTECTED]> wrote: > I would like to try PostgreSQL 8.3 without uninstalling version 8.1. > > Is this possible?? Sure. If you're on Unix, download the source and install it somewhere in, say, your home directory. This requires a certain familiarity with the shell, however, but it's fairly straightforward as such things go. If you're on Windows I believe you will need Microsoft's compiler. Alexander. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Version 8.3
On 27/10/2007 22:05, Bob Pawley wrote: I would like to try PostgreSQL 8.3 without uninstalling version 8.1. Is this possible?? Hi Bob, Yes, it is - it's usually a matter of putting the two versions to listen on two different ports. I haven't done it myself, but this crops up from time to time on this list, so it'd be worth your while having a trawl through the archives. HTH, Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Version 8.3
I would like to try PostgreSQL 8.3 without uninstalling version 8.1. Is this possible?? Bob Pawley ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] createdb argument question
On Sat, Oct 27, 2007 at 12:56:37PM -0500, Perry Smith wrote: > On my Mac systems, this work. On my AIX system it does not. I get: > > createdb dog -E utf8 > createdb: too many command-line arguments (first is "utf8") > Try "createdb --help" for more information. > > Has anyone else bumped in to this? Is putting the arguments after > the database name known to work? Looking at the code, it seems > rather magical that it works but I just looked at the main routine in > createdb.c and not the routines it calls. It's been a while since I've dealt with AIX, but try putting your options before the option-less arguments. How it works is not magical, it's just using getopt_long, and some getopts stop checking for options after the first non-option argument it receives. -- Seneca [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] createdb argument question
Michael Glaesemann <[EMAIL PROTECTED]> writes: > On Oct 27, 2007, at 12:56 , Perry Smith wrote: >> On my Mac systems, this work. On my AIX system it does not. I get: > Versions? I think the old shell-script version of createdb, in 7.3 and before, might have taken that ordering of arguments ... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] createdb argument question
On Oct 27, 2007, at 1:41 PM, Michael Glaesemann wrote: On Oct 27, 2007, at 12:56 , Perry Smith wrote: On my Mac systems, this work. On my AIX system it does not. I get: Versions? My AIX is 5.3 close to the latest. Postgres is 8.2.4 I built all of the open source stuff myself using gcc 4.0.2. (but, as I recall, Postgres just builds without any real coaxing or libs, etc from me). My working Mac is 10.4. Postgres I have 8.2.4 on one system and 8.1.3 on another (both work with the -E after the database argument). Thanks Perry ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] createdb argument question
On Oct 27, 2007, at 12:56 , Perry Smith wrote: On my Mac systems, this work. On my AIX system it does not. I get: Versions? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Stored Procs Vs User Defined Functions vis-a-vis UDF's in Postgresql
Hi, lately I have been looking at difference between a Stored Proc and User Defined Functions in other RDBMS like Sql Server / Oracle. However, in postgresql, I think Stored Procs are wrapped around in User Defined functions, if I am not wrong. The following is the list of main differences b/w a Stored Proc and a UDF in general. Can anyone please comment on how a postgresql UDF would behave for each of these difference mentioned below ? 1. Stored Procedures are parsed and compiled and stored in compiled format in the database. We can also say that Stored Procedures are stored as pseudo code in the database i.e. compiled form. On the other hand, User Defined Functions are parsed, and compiled at runtime. 2. A User Defined Function must return a value where as a Stored Procedure doesn't need to (they definitely can, if required). 3. A User Defined Function can be used with any Sql statement. For example, we have a function 'FuncSal(int)' that returns the salary of a person. This function can be used in a Sql statement as follows:- . SELECT * FROM tbl sal WHERE salary = FuncSal(x) Here internally, a call would be made to User Defined Function 'FuncSal' with any integer x, as desired, and compared with the 'salary' field of database Table tbl sal. We can have Data Manipulation Language (DML) statements like insert, update, delete in a function. However, we can't call such a function (having insert, update, delete) in a Sql query. For example, if we have a function (FuncUpdate(int)) that updates a table, then we can't call that function from a Sql query. . SELECT FuncUpdate(field) FROM sometable; will throw error. On the other hand, Stored Procedures can't be called inside a Sql statement. 4. Operationally, when an error is encountered, the function stops, while an error is ignored in a Stored Procedure and proceeds to the next statement in the code (provided one has included error handling support). 5. Functions return values of the same type, Stored Procedures return multiple type values. 6. Stored Procedures support deferred name resolution. To explain this, lets say we have a stored procedure in which we use named tables tbl x and tbl y but these tables actually don't exist in the database at the time of this stored procedure creation. Creating such a stored procedure doesn't throw any error. However, at runtime, it would definitely throw error it tables tbl x and tbl y are still not there in the database. On the other hand, User Defined Functions don't support such deferred name resolution. Thanks in advance, ~Harpreet
[GENERAL] createdb argument question
For some odd reason, Rails decided to call createdb as: createdb foo_database -E utf8 On my Mac systems, this work. On my AIX system it does not. I get: createdb dog -E utf8 createdb: too many command-line arguments (first is "utf8") Try "createdb --help" for more information. Has anyone else bumped in to this? Is putting the arguments after the database name known to work? Looking at the code, it seems rather magical that it works but I just looked at the main routine in createdb.c and not the routines it calls. Thank you for your help, Perry ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Selecting tree data
Pat Maddox wrote: On 10/26/07, brian <[EMAIL PROTECTED]> wrote: Pat Maddox wrote: On 10/26/07, brian <[EMAIL PROTECTED]> wrote: SELECT * FROM posts ORDER BY root_id, id; brian ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ Okay, but what if I want to order by created_at? btw created_at is a timestamp, I just wrote '4pm' to make it a bit easier to read. SELECT * FROM posts ORDER BY created_a, root_id, id; That doesn't work because it just sorts by created_at, and then if two records are the same it goes to root_id. That's not what I want. You have been a little vague on what you are doing and what you want to achieve - I think that if you describe exactly what you want to achieve you will find some more help - but then at that stage you may click as to how to achieve it anyway. I am guessing that root_id is the id of the first post starting the discussion parent_id is the the post that is being responded to root_id would be NOT NULL and would be the same as id for the first post. I would say that parent_id SHOULD also be NOT NULL and be the same as the id for the parent post. The lack of NULL's here would help your sorting to go the way you want. My guess is you want SELECT * FROM posts ORDER BY root_id, parent_id, created_at, id; which you would reverse as SELECT * FROM posts ORDER BY root_id desc, parent_id desc, created_at desc, id desc; By the way - timestamp is accurate to 1 microsecond (I am guessing that would be the creation timestamp) and if you manage to have two identical timestamps the id column would tell the exact order of creation if you wanted to be that picky. You could actually not use the created_at in the sorting as the id will give the same effect by itself. -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] I want to search my project source code
openfts.sf.net is tool for you. It has even example scripts for indexing/searching file system. Oleg On Sat, 27 Oct 2007, Matthew Wilson wrote: I have a lot of code -- millions of lines at this point, written over the last 5 years. Everything is in a bunch of nested folders. At least once a week, I want to find some code that uses a few modules, so I have to launch a find + grep at the top of the tree and then wait for it to finish. I wonder if I could store our source code in a postgresql table and then use full text searching to index. Then I hope I could run a query where I ask for all files that use modules X, Y, and Z. I'm looking for something sort of like the locate utility, except that instead of building a quickly-searchable list of file names, I want to be able to search file contents also. Matt ---(end of broadcast)--- TIP 6: explain analyze is your friend Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] I want to search my project source code
Matthew Wilson <[EMAIL PROTECTED]> writes: > At least once a week, I want to find some code that uses a few modules, > so I have to launch a find + grep at the top of the tree and then wait > for it to finish. Personally I use glimpse for this. It's a bit old and creaky but it performs wonders. There might be something better out there by now. I wouldn't recommend trying to use a standard FTS to index code: code is not a natural language and the kinds of searches you usually want to perform are a lot different. As an example, I glimpse for "foo" when looking for references to a function foo, but "^foo" when seeking its definition (this relies on the coding conventions about function layout, of course). An FTS doesn't think start-of-line is significant so it can't do that. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] WAL archiving idle database
Simon Riggs <[EMAIL PROTECTED]> writes: > We *could* force it to perform a log switch whether or not new WAL has > been written. That would be a truly awful idea. I think the right answer for Kevin's situation really is to be doing a dummy transaction every few minutes as one component of his system monitoring. That way he gets the behavior he wants, and it needn't preclude us from fixing the default behavior to be less wasteful. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Postgres+Apache+PHP (again, but in different setup)
Wojtek Mach wrote: Hi all, I've read everything in this subject you guys has discussed already (including topic started by Max Zorloff) but I haven't found my answer yet. I'm looking for hints on best configuration for my specific needs: I'm having complied program written in C, which reads lots of data from cellular network. Then my C code is running some complex transformations and finally inserts/updates data in main partitioned Postgres table and few standard tables. In production, the data will be coming all the time, around 30.000inserts/minute. Then, I have set of WebServices, which are invoked by limited number of customers (3-6 different logins) and runs some selects/DMLs against my database, no more then ~50queries/minute. Now, the question is: how my C code should connect to Postgres? You've recommended pgpool and pgbouncer, but I believe these connection poolers are rather to be used for webpages, where there is a lot of querying from different users. Am I wrong? Now I'm using pgconnect (yes, I've read it's "broke" but is working fine for me .).. Do you see reasons why I, in this setup, should move to any of connection poolers? Or there is a better way? The second question is should I go for connection poolers for my WebServices as well? If so, what are the reasons? From configuration POV, I'm using Apache, PHP and Postgres 8.2 server. Final hardware setup is yet to be decided. Will appreciate all the good comments. BR, [EMAIL PROTECTED] So... you have about 8 connections? Your C program that reads lots of data, does it just connect once? By your description above it sounds like that is one connection. If its more than one connection, are they connecting and disconnecting? I assume not, because they have so much to insert, they just stay connected then connection pooling wont help you. I'd say (with what little info you have given) drop the connection pooling all together. Does not sound like you need it. -Andy ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] I want to search my project source code
I have a lot of code -- millions of lines at this point, written over the last 5 years. Everything is in a bunch of nested folders. At least once a week, I want to find some code that uses a few modules, so I have to launch a find + grep at the top of the tree and then wait for it to finish. I wonder if I could store our source code in a postgresql table and then use full text searching to index. Then I hope I could run a query where I ask for all files that use modules X, Y, and Z. I'm looking for something sort of like the locate utility, except that instead of building a quickly-searchable list of file names, I want to be able to search file contents also. Matt ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] INDEX and JOINs
Reg Me Please <[EMAIL PROTECTED]> schrieb: > Il Saturday 27 October 2007 08:51:09 Andreas Kretschmer ha scritto: > > Reg Me Please <[EMAIL PROTECTED]> schrieb: > > > How can I "Increasing the statistics target for the larger table"? > > > I'ìm sorry for asking, but I'm not that deep into RDBMS. > > > > alter table alter column SET STATISTICS ; > > > > Andreas > > How can I display the statistics for a table/column/index/whatever applies? select attstattarget from pg_attribute where attname = and attrelid=::regclass; for instance: select attstattarget from pg_attribute where attname = 'ts' and attrelid='foo'::regclass; -1 means the default statistics value. 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."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] WAL archiving idle database
On Fri, 2007-10-26 at 14:39 -0700, Jeff Davis wrote: > On Fri, 2007-10-26 at 15:08 -0600, Brian Wipf wrote: > > I have a test PG 8.2.5 installation that has been left idle with no > > connections to it whatsoever for the last 24 hours plus. WALs are > > being archived exactly 5 minutes apart, even though archive_timeout > > is set to 60. Is this the expected behavior for a database with no > > changes? > > > > If it's set to just "60" that means 60 seconds. > > What's happening is that you have a checkpoint_timeout of 5 minutes, and > that checkpoint must write a checkpoint record in the WAL, prompting the > archiving. archive_timeout is the maximum time to wait for a log switch that contains newly written WAL. That interval is not the same thing as how often WAL records are written. On the idle server a checkpoint is being written every checkpoint_timeout. Then archive_timeout kicks in 60 seconds later, switches the log which then archives that file. Then four minutes later a checkpoint occurs, sees that there is no immediately preceding checkpoint because of the log switch and writes a new checkpoint record. Then 60 seconds later... Overall this produces one WAL file every checkpoint_timeout during idle times, yet without relaxing the guarantee that WAL will be archived every archive_timeout seconds. We *could* force it to perform a log switch whether or not new WAL has been written. If that's what people think is wanted. I'd seen the behaviour as beneficial up til now. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Postgres+Apache+PHP (again, but in different setup)
Hi all, I've read everything in this subject you guys has discussed already (including topic started by Max Zorloff) but I haven't found my answer yet. I'm looking for hints on best configuration for my specific needs: I'm having complied program written in C, which reads lots of data from cellular network. Then my C code is running some complex transformations and finally inserts/updates data in main partitioned Postgres table and few standard tables. In production, the data will be coming all the time, around 30.000inserts/minute. Then, I have set of WebServices, which are invoked by limited number of customers (3-6 different logins) and runs some selects/DMLs against my database, no more then ~50queries/minute. Now, the question is: how my C code should connect to Postgres? You've recommended pgpool and pgbouncer, but I believe these connection poolers are rather to be used for webpages, where there is a lot of querying from different users. Am I wrong? Now I'm using pgconnect (yes, I've read it's "broke" but is working fine for me .).. Do you see reasons why I, in this setup, should move to any of connection poolers? Or there is a better way? The second question is should I go for connection poolers for my WebServices as well? If so, what are the reasons? From configuration POV, I'm using Apache, PHP and Postgres 8.2 server. Final hardware setup is yet to be decided. Will appreciate all the good comments. BR, [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] INDEX and JOINs
Il Saturday 27 October 2007 08:51:09 Andreas Kretschmer ha scritto: > Reg Me Please <[EMAIL PROTECTED]> schrieb: > > How can I "Increasing the statistics target for the larger table"? > > I'ìm sorry for asking, but I'm not that deep into RDBMS. > > alter table alter column SET STATISTICS ; > > Andreas How can I display the statistics for a table/column/index/whatever applies? Thanks again. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
