Re: [GENERAL] using COPY and PARTITON
Hi, I'd recommend coping directly to child tables (partitions). It's much faster. Regards, foo Scott Marlowe wrote: I believe triggers can do it. On Sun, Aug 9, 2009 at 11:15 PM, cjm1010cjm1...@naver.com wrote: hi~ I want to insert DATA by using the COPY making PARTITON TABLE. By the way, DATA is inserted only in PARENT TABLE. Can DATA be inserted in CHILD TABLE by using the COPY? -- 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] using COPY and PARTITON
thanks for reply I am using rules with libpq instead of triggers now. When INSERT was used, it was possible to insert it in CHILD TABLE well. However, if COPY is used, it is possible to insert it only in PARENT TABLE. Can triggers be used with libpq? And, if trigger is used, can it straighten out that problem? JM CHAE Scott Marlowe-2 wrote: I believe triggers can do it. On Sun, Aug 9, 2009 at 11:15 PM, cjm1010cjm1...@naver.com wrote: hi~ I want to insert DATA by using the COPY making PARTITON TABLE. By the way, DATA is inserted only in PARENT TABLE. Can DATA be inserted in CHILD TABLE by using the COPY? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- View this message in context: http://www.nabble.com/using-COPY-and-PARTITON-tp24894446p24895140.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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: XLogFlush: request AF/5703EDC8 is not satisfied --- flushed only to AF/50F15ABC
Dear All, I am using postgres 7.3 version on RHEL 4.0. My database has been restored. All tables all working fine i.e select , update but on a particular table its showing error ERROR: XLogFlush: request AF/5703EDC8 is not satisfied --- flushed only to AF/50F15ABC I have searched other threads, it shows the problem may be due to garbage in the LSN field of a page header. Now what is the solution to this problem. Utsav Turray -- View this message in context: http://www.nabble.com/ERROR%3A--XLogFlush%3A-request-AF-5703EDC8-is-not-satisfied-flushed-only-to-AF-50F15ABC-tp24895088p24895088.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] psql crashing - don't know why
On Mon, Aug 10, 2009 at 11:04:24AM +1200, Brent Wood wrote: Hi... I have a view across 3 tables, total some 5m rows. I can extract parts of the view, entire rows, with a where clause (eg: select * from view where cell_id=10;) If I try to select the entire view (eg: select * from view;) it runs for a while then gives the error msg Killed and returns to the system prompt, having exited psql. The log says: 2009-08-10 00:19:01 NZST ben200601 woodb LOG: could not send data to client: Broken pipe 2009-08-10 00:19:48 NZST ben200601 woodb LOG: unexpected EOF on client connection Your problem is that you're trying to load the entire table into memory in psql, that is, the client side. I don't know how big your dataset is, but perhaps you should compare that with the amount of memory you actually have. Do you actually need to read all the data at once or can you accept it in chunks? In that you can simply declare a a cursor and then do FETCH 1 to successively read the rows as needed. Have a nice day, -- Martijn van Oosterhout klep...@svana.org 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
R: [GENERAL] batch inserts in python libpq
Is there a Python driver that uses the same protocol form of addBatch in Java? I'll answer my own question: the method to be used should be cursor.executemany() -- 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] Postgres memory question
In response to Kobus Wolvaardt kobusw...@gmail.com: 2009/8/9 Scott Marlowe scott.marl...@gmail.com On Sun, Aug 9, 2009 at 4:06 AM, Kobus Wolvaardtkobusw...@gmail.com wrote: Hi, We have software deployed on our network that need postgres, we have server that hosts the server and all worked fine until we crossed about 200 users. The application is written so that it makes a connection right at the start and keeps it alive for the duration of the app. The app is written in Delphi. The postgres server runs on a windows 2008 server with quad core cpu and 4 GB of ram. Is this an app you can fix yourself, or are you stuck with this mis-step in design? It is our app but it is not going to be easy to change it. It will get changed, but the time frame is a bit long and we need a solution to hold us over. We have servers using about 200 connections on average ... it climbs up to 300+ during busy use. I've seen it peak as high as 450, and we've seen no performance issues. This is a quad core with 4G of RAM. Of course the OS isn't windows, it's 64 bit FreeBSD. However, my point is that your server _can_ be tuned to handle loads like this. I can't say for sure how much the OS makes a difference in this case, but I expect any POSIX system is going to scale better than Windows. As far as tuning, I just went through the config file and tuned everything logically based on published best practices. Aside from the FSM settings, I don't think I've had to fine tune anything else, post. And for those who may want to jump in -- we have investigated pgpool several times, we just can justify the added complexity when the system just works as is, but we're ready to add it on quickly should problems arise. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Multiple foreign keys with the same name and information_schema
Hello everyone, I have a question regarding foreign keys and information_schema. Given the following valid schema: CREATE TABLE Cat ( IdCat serial NOT NULL, CONSTRAINT PK_Cat PRIMARY KEY (IdCat) ); CREATE TABLE Art ( IdArt serial NOT NULL, IdCat integer NOT NULL, CONSTRAINT PK_Art PRIMARY KEY (IdArt), CONSTRAINT FK_Art_Cat FOREIGN KEY (IdCat) REFERENCES Cat (IdCat) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ); CREATE TABLE Cat2 ( IdCat2 serial NOT NULL, CONSTRAINT PK_Cat2 PRIMARY KEY (IdCat2) ); CREATE TABLE Art2 ( IdArt2 serial NOT NULL, IdCat2 integer NOT NULL, CONSTRAINT PK_Art2 PRIMARY KEY (IdArt2), CONSTRAINT FK_Art_Cat FOREIGN KEY (IdCat2) REFERENCES Cat2 (IdCat2) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ); PostgreSQL, unlike other DBMSs, allows foreign keys on different tables to have the same name (note FK_Art_Cat on Art and Art2). I need to make a query to the information_schema catalog to get the table referenced by a given field in a given table (eg: Art, IdCat references Cat; Art2, IdCat2 references Cat2). I was a able to do it using the pg_catalog tables, but I haven't found a way to do it using information_schema since it relies on foreign keys names being unique in the same catalog. Is this a known limitation? Is there any way to do what I need with the information_schema catalog? I want to make generic queries to use them across different DBMSs that support the ANSI information_schema. A possible solution would be adding the foreign key table_name to all the tables on the information_schema that rely on foreign keys names being unique, for the case I am talking about it would be enough to have it the table referential_contraints. Thanks, Jonathan -- 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] Multiple foreign keys with the same name and information_schema
Jonathan Tapicer tapi...@gmail.com writes: I was a able to do it using the pg_catalog tables, but I haven't found a way to do it using information_schema since it relies on foreign keys names being unique in the same catalog. Is this a known limitation? Actually, the information_schema supposes that constraint names are unique within a *schema*, not within a *catalog* (a/k/a database). Don't know if that distinction can help you or not. You are correct that Postgres is less rigid. We do not consider that to be a deficiency on the Postgres side ;-) If you want to use the information_schema to deal with this stuff, the answer is to make sure that your application follows the SQL-standard rule of not duplicating constraint names within a schema. 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] Multiple foreign keys with the same name and information_schema
Actually, the information_schema supposes that constraint names are unique within a *schema*, not within a *catalog* (a/k/a database). Don't know if that distinction can help you or not. You are correct that Postgres is less rigid. We do not consider that to be a deficiency on the Postgres side ;-) Yes, my bad. Anyway, it doesn't help, I have both tables on the same schema. If you want to use the information_schema to deal with this stuff, the answer is to make sure that your application follows the SQL-standard rule of not duplicating constraint names within a schema. regards, tom lane Yes, I know that following the SQL standards is the way to go, but sometimes this has to be done in databases I don't design, so I have to be prepared for every case. I think I'll use the pg_catalog for this case. Thank you for you answer, Jonathan -- 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 Backups
Hello all Thank you very much for your responses! I realised that the restores were not working as the databases were not being...backed up!...oops! After I did the reinstallation of the OS I forgot to give permissions in postgresql for the user doing the backup in ubuntu! I have fixed this. I have also listened to all your advice and decided to do single dumps of each of the databases as well as a pg_dumpall of the globals. But still the issue remains how do you perfom a restore (not using the command line that I know how to do and works successfully now!), but via pgAdmin. As when I click on restore after selecting the file the okay button is still disabledany ideas Thanks again! t. -- View this message in context: http://www.nabble.com/Postgresql-Backups-tp24845786p24898427.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] NOTICE: there is no transaction in progress
We have two machines that run a C application that interfaces with a Postgres database. They are our development and production machines. The version is PostgreSQL 8.3.7 on i686-redhat-linux-gnu, complied by GCC gcc (GCC) 4.1.2.20071124 (Red Hat 4.1.2-42) . On the development machine, I don't get notices and warnings related to Postgres SQL commands. COMMIT and ABORT are likely the culprits. On the production machine I do. I understand these errors get sent to the 'stderr' stream by default. I don't know which command exactly is causing the notices and warnings in the program and why the behavior is only happening on one machine. Are there some settings on the database that have been set for displaying such warnings and notices? Why does it happen one and not the other? Any suggestions would be greatly appreciated. Thanks. Rodrick Hales State Tax Commission Office Of Information Technology (601) 923.7427 rha...@mstc.state.ms.us
[GENERAL] PQstatus does not seem to work
Hi I used PQstatus(conn) function to check connection status, but I found that it still returns CONNECTION_OK even after postgres is restarted. Does anyone know if there is another command that I can use to check connection status? What other solution is available to check whether a connection is still alive? Thanks, JB
[GENERAL] Accessing a database form another database
I've got some GIS tables that I want to use across several projects and currently I've got each project in a separate database. So say I've got two databases, one for the Spacely Sprookets project and one for the Cogswell Cogs contract, but I want to access my WorldMap tables in my GIS database to make maps for both of these projects. How do I reference a table that's in another database? Or should I organize my PostgreSQL data differently? Thanks, - Bill Thoen -- 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] NOTICE: there is no transaction in progress
Rodrick Hales wrote: We have two machines that run a C application that interfaces with a Postgres database. They are our development and production machines. The version is PostgreSQL 8.3.7 on i686-redhat-linux-gnu, complied by GCC gcc (GCC) 4.1.2.20071124 (Red Hat 4.1.2-42) . On the development machine, I don't get notices and warnings related to Postgres SQL commands. COMMIT and ABORT are likely the culprits. On the production machine I do. I understand these errors get sent to the 'stderr' stream by default. I don't know which command exactly is causing the notices and warnings in the program and why the behavior is only happening on one machine. Are there some settings on the database that have been set for displaying such warnings and notices? Why does it happen one and not the other? Any suggestions would be greatly appreciated. Thanks. logging is controlled by various settings in postgresql.conf -- 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] Accessing a database form another database
On Mon, Aug 10, 2009 at 10:48:10AM -0600, Bill Thoen wrote: I've got some GIS tables that I want to use across several projects and currently I've got each project in a separate database. So say I've got two databases, one for the Spacely Sprookets project and one for the Cogswell Cogs contract, but I want to access my WorldMap tables in my GIS database to make maps for both of these projects. How do I reference a table that's in another database? Or should I organize my PostgreSQL data differently? That last is much easier. Create a schema each for Spacely and Cogswell, then one for WorldMap. http://www.postgresql.org/docs/current/static/sql-createschema.html Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com 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] PQstatus does not seem to work
Juan Backson juanback...@gmail.com writes: I used PQstatus(conn) function to check connection status, but I found that it still returns CONNECTION_OK even after postgres is restarted. Does anyone know if there is another command that I can use to check connection status? PQstatus isn't going to get updated until you try to do some operation with the connection object. Otherwise it wouldn't be a simple inquiry function, but some exceedingly expensive operation involving attempting to contact the server. What other solution is available to check whether a connection is still alive? Send a query. 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] PQstatus does not seem to work
On Tue, Aug 11, 2009 at 12:41:47AM +0800, Juan Backson wrote: I used PQstatus(conn) function to check connection status, but I found that it still returns CONNECTION_OK even after postgres is restarted. Does anyone know if there is another command that I can use to check connection status? Yes, PQstatus just gives back the last status. It doesn't go off and check anything. What other solution is available to check whether a connection is still alive? As a connection can go down at any time, this doesn't seem useful. Just send off your request as normal and if it fails because the connection was closed then you can open a new one and try again. -- Sam http://samason.me.uk/ -- 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] Postgres memory question
On Mon, Aug 10, 2009 at 9:46 AM, Bill Moranwmo...@potentialtech.com wrote: We have servers using about 200 connections on average ... it climbs up to 300+ during busy use. I've seen it peak as high as 450, and we've seen no performance issues. This is a quad core with 4G of RAM. Of course the OS isn't windows, it's 64 bit FreeBSD. I too run 64bit FreeBSD 7.2. However in my primary use case, anything short of 20GB of RAM makes the application very unresponsive when many customers are online. We usually don't have more than about 45 to 50 connections simultaneously. My general-use Pg server has 4GB and that is more than adequate for the miscellaneous uses of blogs, ad servers, and drupal installations. -- 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] using COPY and PARTITON
On Mon, Aug 10, 2009 at 12:58 AM, cjm1010cjm1...@naver.com wrote: thanks for reply I am using rules with libpq instead of triggers now. When INSERT was used, it was possible to insert it in CHILD TABLE well. However, if COPY is used, it is possible to insert it only in PARENT TABLE. Can triggers be used with libpq? And, if trigger is used, can it straighten out that problem? Yep, triggers work just fine with libpq (if they didn't FK constraints wouldn't work). I switched from rules to triggers a while back for my reporting / stats database and they work a charm. -- 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] PQstatus does not seem to work
On 8/10/09 12:08 PM, Sam Mason s...@samason.me.uk wrote: On Tue, Aug 11, 2009 at 12:41:47AM +0800, Juan Backson wrote: I used PQstatus(conn) function to check connection status, but I found that it still returns CONNECTION_OK even after postgres is restarted. Does anyone know if there is another command that I can use to check connection status? Yes, PQstatus just gives back the last status. It doesn't go off and check anything. What other solution is available to check whether a connection is still alive? As a connection can go down at any time, this doesn't seem useful. Just send off your request as normal and if it fails because the connection was closed then you can open a new one and try again. Depending on your situation, connection pooling might be a reasonable option. Instead of managing the connections yourself, you leave that to another process entirely. http://www.revsys.com/writings/postgresql-performance.html Look at the section on Stateless Applications I spend a lot of time writing stateless apps that server many 'users' concurrently. For me, the pooling idea is much simpler because I only interact with the 'pool', and the pool manages opening and closing connections on my behalf. Of course, this is not a good option if you're writing a stateful app. Your original email didn't say either way, so this is a take on the other side of the problem. -- 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] libpq
these are straight dll calls as outlined in Using Run-Time Dynamic Linking (Windows) that's why they look funny. it is impossible to link VC++ .lib files with mingw(gcc) .a libraries. yeah, I just found the PQntuples bug myself too, and got the program finished. thank you for the tips on using libpq, I may still need to implement those. I didn't remember seeing anywhere in the docs that you were supposed to check for pqr==NULL, I wish they would document that in PQexec. must be a documentation bug. There is no mention of return values! http://www.postgresql.org/docs/8.4/interactive/libpq-exec.html still doesn't solve the need for MingW *.a libraries version of libpq. I wouldn't have had to rewrite the whole thing for this to work with mingw. The DLL's are VC++/MinGW compatible, but the .lib files are not. Jim Michaels jmich...@yahoo.com http://JesusnJim.com From: Scott Ribe scott_r...@killerbytes.com To: Jim Michaels jmich...@yahoo.com; pgsql general pgsql-general@postgresql.org Sent: Sunday, August 9, 2009 9:16:34 AM Subject: Re: [GENERAL] libpq That's pretty confused C code. The most obvious problem is that you're not calling the Pqntuples function; you're just examining the value of a variable called ntuples, when you haven't set that value after calling Pqexec (and maybe have never set it). Take it step by step, and check error returns at each step--including connecting to the database, and of course especially check errors after calling PQexec--first checking that pgr is not null, then if not null using the PQresultStatus, PQresStatus, PQresultErroMessage functions, otherwise the PQstatus, PQerrorMessage functions. Then if you still have problems, post more complete code that includes important things like connecting to the database, and declarations assignments to key variables. Also, what is this (function)(args) stuff? Normally, the PQ functions are plain C functions, called as function(args). Do you really have some setup where you have function pointer variables and your compiler requires that outdated syntax? Or is this more basic C confusion? -- Scott Ribe scott_r...@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice
Re: [GENERAL] libpq
On Mon, Aug 10, 2009 at 4:03 PM, Jim Michaelsjmich...@yahoo.com wrote: these are straight dll calls as outlined in Using Run-Time Dynamic Linking (Windows) that's why they look funny. it is impossible to link VC++ .lib files with mingw(gcc) .a libraries. that is not why they look funny...they look funny because you made them so :-). function pointers mapped at runtime via dynamic linking do not need to be wrapped with (). val = (PQgetvalue)(pgr,index, 0);strcpy(row.firstname, val); could be re-written as: strcpy(row.firstname, PQgetvalue(pgr,index, 0)); ...which is still asking for trouble...you're not checking the length and blindly copying your results into the receiving structure. Also, you are mixing 'SELECT *' with assumed column positions. This is IMO very bad style. either use explicit column list in your select statement or pull your data from the result using name instead of position. Also, use a compiler which tells you about uninitialized variables, don't top post, and avoid html emails when posting to public lists :-). 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] Postgres memory question
On Mon, 10 Aug 2009 13:49:02 -0400 Vick Khera vi...@khera.org wrote: On Mon, Aug 10, 2009 at 9:46 AM, Bill Moranwmo...@potentialtech.com wrote: We have servers using about 200 connections on average ... it climbs up to 300+ during busy use. I've seen it peak as high as 450, and we've seen no performance issues. This is a quad core with 4G of RAM. Of course the OS isn't windows, it's 64 bit FreeBSD. I too run 64bit FreeBSD 7.2. However in my primary use case, anything short of 20GB of RAM makes the application very unresponsive when many customers are online. We usually don't have more than about 45 to 50 connections simultaneously. My general-use Pg server has 4GB and that is more than adequate for the miscellaneous uses of blogs, ad servers, and drupal installations. Results will obviously vary by installation, usage, and application type. Are you saying you have performance issues with the application when there are many idle connections? Because that was the original discussion. Performance under heavy concurrent load is another topic, and a much more complex one. -Bill -- 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] Postgres memory question
On Mon, Aug 10, 2009 at 7:49 PM, Vick Kheravi...@khera.org wrote: On Mon, Aug 10, 2009 at 9:46 AM, Bill Moranwmo...@potentialtech.com wrote: We have servers using about 200 connections on average ... it climbs up to 300+ during busy use. I've seen it peak as high as 450, and we've seen no performance issues. This is a quad core with 4G of RAM. Of course the OS isn't windows, it's 64 bit FreeBSD. I too run 64bit FreeBSD 7.2. However in my primary use case, anything short of 20GB of RAM makes the application very unresponsive when many customers are online. We usually don't have more than about 45 to 50 connections simultaneously. My general-use Pg server has 4GB and that is more than adequate for the miscellaneous uses of blogs, ad servers, and drupal installations. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Hi Kobus, My simple (perhaps wrong) understanding of how DB connection pooling works is that no active connection(s) can be shared. The connection pool manager may however assign a free connection from the connection pool to service a connection request, and a connection is usually declared as free when the application having the active connection closes it (or returns it to the pool in a connection pool specific manner). If this is correct, having an application request for a connection at the start of a session then holding on to it for the duration of the session may yield at least as many connections to the DB as there are sessions. It may then be advisable to (re)write the application to open (request for a connection from the pool) and close (return a connection to the pool) DB connections for each logical data transaction (or group of closely associated transactions). Since in your case a code rewrite seems unfavourable (due to time pressure) the DB connection pooling option may not be viable as you may have noticed. Allan. -- 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] Postgres memory question
On Mon, Aug 10, 2009 at 3:16 PM, Allan Kamaukamaual...@gmail.com wrote: On Mon, Aug 10, 2009 at 7:49 PM, Vick Kheravi...@khera.org wrote: On Mon, Aug 10, 2009 at 9:46 AM, Bill Moranwmo...@potentialtech.com wrote: We have servers using about 200 connections on average ... it climbs up to 300+ during busy use. I've seen it peak as high as 450, and we've seen no performance issues. This is a quad core with 4G of RAM. Of course the OS isn't windows, it's 64 bit FreeBSD. I too run 64bit FreeBSD 7.2. However in my primary use case, anything short of 20GB of RAM makes the application very unresponsive when many customers are online. We usually don't have more than about 45 to 50 connections simultaneously. My general-use Pg server has 4GB and that is more than adequate for the miscellaneous uses of blogs, ad servers, and drupal installations. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Hi Kobus, My simple (perhaps wrong) understanding of how DB connection pooling works is that no active connection(s) can be shared. The connection pool manager may however assign a free connection from the connection pool to service a connection request, and a connection is usually declared as free when the application having the active connection closes it (or returns it to the pool in a connection pool specific manner). If this is correct, having an application request for a connection at the start of a session then holding on to it for the duration of the session may yield at least as many connections to the DB as there are sessions. It may then be advisable to (re)write the application to open (request for a connection from the pool) and close (return a connection to the pool) DB connections for each logical data transaction (or group of closely associated transactions). Since in your case a code rewrite seems unfavourable (due to time pressure) the DB connection pooling option may not be viable as you may have noticed. Yeah, from what the OP stated as requirements, I'm guessing the one they can push back on the easiest is to get more memory and run run Linux / BSD / OpenSolaris instead of Windows server. -- 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] Accessing a database form another database
Bill Thoen wrote: I've got some GIS tables that I want to use across several projects and currently I've got each project in a separate database. So say I've got two databases, one for the Spacely Sprookets project and one for the Cogswell Cogs contract, but I want to access my WorldMap tables in my GIS database to make maps for both of these projects. How do I reference a table that's in another database? Or should I organize my PostgreSQL data differently? You can use dblink for this purpose: http://www.postgresonline.com/journal/index.php?/archives/44-Using-DbLink-to-access-other-PostgreSQL-Databases-and-Servers.html -- Guy Rouillier -- 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] Postgres memory question
Hi, Thanks for all the answers. I think I need to first look into the windows connection issue (where some memory pool gets used up and then new connections cannot be made). If that does not help, upping the ram by a bit might help (at least buy some time). And looking into tuning the options that result in increased memory size to be slightly lower (does anybody know which options will lower the per connection memory usages, the only one that seems clear to me is work_mem). But ultimately we need to rewrite the code to not keep connections open, or too not do queries that upset transaction pooling (ala pgbouncer). And we need to get that client onto Linux so that memory increases can be taken advantage of. If there are other suggestions that would be much appreciated. Regards, Kobus 2009/8/10 Scott Marlowe scott.marl...@gmail.com On Mon, Aug 10, 2009 at 3:16 PM, Allan Kamaukamaual...@gmail.com wrote: On Mon, Aug 10, 2009 at 7:49 PM, Vick Kheravi...@khera.org wrote: On Mon, Aug 10, 2009 at 9:46 AM, Bill Moranwmo...@potentialtech.com wrote: We have servers using about 200 connections on average ... it climbs up to 300+ during busy use. I've seen it peak as high as 450, and we've seen no performance issues. This is a quad core with 4G of RAM. Of course the OS isn't windows, it's 64 bit FreeBSD. I too run 64bit FreeBSD 7.2. However in my primary use case, anything short of 20GB of RAM makes the application very unresponsive when many customers are online. We usually don't have more than about 45 to 50 connections simultaneously. My general-use Pg server has 4GB and that is more than adequate for the miscellaneous uses of blogs, ad servers, and drupal installations. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Hi Kobus, My simple (perhaps wrong) understanding of how DB connection pooling works is that no active connection(s) can be shared. The connection pool manager may however assign a free connection from the connection pool to service a connection request, and a connection is usually declared as free when the application having the active connection closes it (or returns it to the pool in a connection pool specific manner). If this is correct, having an application request for a connection at the start of a session then holding on to it for the duration of the session may yield at least as many connections to the DB as there are sessions. It may then be advisable to (re)write the application to open (request for a connection from the pool) and close (return a connection to the pool) DB connections for each logical data transaction (or group of closely associated transactions). Since in your case a code rewrite seems unfavourable (due to time pressure) the DB connection pooling option may not be viable as you may have noticed. Yeah, from what the OP stated as requirements, I'm guessing the one they can push back on the easiest is to get more memory and run run Linux / BSD / OpenSolaris instead of Windows server. -- 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] libpq
I didn't remember seeing anywhere in the docs that you were supposed to check for pqr==NULL, I wish they would document that in PQexec. It's right there in the first sentence of the discussion. There is no mention of return values! ??? -- Scott Ribe scott_r...@killerbytes.com 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] Accessing a database form another database
Just a thought, but would it not be possible to add a trigger written in python that makes a connection to another database and does what it needs to? I have not done this, but if it is possible it should solve your problem and one that I have. It should allow updates and selects. I am sure the docs will tell you how to do perl or python based triggers and if you can use external modules in them...if you can, it would be a rather straight forward python app that accesses the GIS tables using a DB connector (postgres or mysql or what ever). Since I have not read much (more or less nothing) about triggers I could be way off base and entirely wrong. Thanks, Kobus 2009/8/10 Guy Rouillier guyr-...@burntmail.com Bill Thoen wrote: I've got some GIS tables that I want to use across several projects and currently I've got each project in a separate database. So say I've got two databases, one for the Spacely Sprookets project and one for the Cogswell Cogs contract, but I want to access my WorldMap tables in my GIS database to make maps for both of these projects. How do I reference a table that's in another database? Or should I organize my PostgreSQL data differently? You can use dblink for this purpose: http://www.postgresonline.com/journal/index.php?/archives/44-Using-DbLink-to-access-other-PostgreSQL-Databases-and-Servers.html -- Guy Rouillier -- 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] NOTICE: there is no transaction in progress
On Tue, Aug 11, 2009 at 2:51 AM, John R Pierce pie...@hogranch.com wrote: Rodrick Hales wrote: We have two machines that run a C application that interfaces with a Postgres database. They are our development and production machines. The version is PostgreSQL 8.3.7 on i686-redhat-linux-gnu, complied by GCC gcc (GCC) 4.1.2.20071124 (Red Hat 4.1.2-42) . On the development machine, I don't get notices and warnings related to Postgres SQL commands. COMMIT and ABORT are likely the culprits. On the production machine I do. I understand these errors get sent to the 'stderr' stream by default. I don't know which command exactly is causing the notices and warnings in the program and why the behavior is only happening on one machine. Are there some settings on the database that have been set for displaying such warnings and notices? Why does it happen one and not the other? Any suggestions would be greatly appreciated. Thanks. logging is controlled by various settings in postgresql.conf Best is to go through the manual at -- http://www.postgresql.org/docs/8.3/static/runtime-config-logging.html and then take a difference of settings between the two. -- Shoaib Mir http://shoaibmir.wordpress.com/
[GENERAL] xlog flus not satisfied
While doing # VACUUM VERBOSE ANALYZE d_trr_dfh; INFO: vacuuming xmms.d_trr_dfh ERROR: xlog flush request 21F/9F57DF88 is not satisfied --- flushed only to 21F/924CE76C CONTEXT: writing block 2919652 of relation 17461/17462/17668 I see this in the logs user= CONTEXT: writing block 2919680 of relation 17461/17462/17668 user= WARNING: could not write block 2919680 of 17461/17462/17668 DETAIL: Multiple failures --- write error might be permanent. user= LOG: checkpoint starting: time xlog flush request 21F/9F67DA80 is not satisfied --- flushed only to 21F/924CE76C Does this mean I have disk issues? As background, this is a new box mirrored from a separate box via rsync. I've basically copied/rsync the entire postgresql server and data files over to create a mirror copy. After which, I've tried to do the vacuum and gotten into the above trouble. Any ideas from the PG community? (or is rsync not the way to go for this?)
Re: [GENERAL] xlog flus not satisfied
Ow Mun Heng ow.mun.h...@wdc.com writes: As background, this is a new box mirrored from a separate box via rsync. I've basically copied/rsync the entire postgresql server and data files over to create a mirror copy. After which, I've tried to do the vacuum and gotten into the above trouble. Did you shut down the old postmaster while mirroring its files? I could believe seeing this type of problem as a consequence of getting out-of-sync copies of different parts of the database. 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] xlog flus not satisfied
-Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Ow Mun Heng ow.mun.h...@wdc.com writes: As background, this is a new box mirrored from a separate box via rsync. I've basically copied/rsync the entire postgresql server and data files over to create a mirror copy. After which, I've tried to do the vacuum and gotten into the above trouble. Did you shut down the old postmaster while mirroring its files? I could believe seeing this type of problem as a consequence of getting out-of-sync copies of different parts of the database. You've caught me. I'm actually planning to down the master server during lunch to re-sync it. Aside : I wonder how pgcluster does it then, cos I thought they use rsync to do replication. Thanks. Will report back in couple hours. -- 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] PQstatus does not seem to work
Hi Tim, Thank you for your suggestion. In my application, it is a multi-thread and each thread will need to query 5 select statements. Right now, I am having my own pool of 500 PgConn inside the code. For each connection that I obtain from the connection pool, I am using direct socket into querying the database, without ODBC. That way, I can get the data much faster. Does PGpool II has c api that I can use inside my code? Also, can I use direct socket connection to query the db with PgpoolI? The way I am executing query is by using : res = PGexec(conn, BEGIN); res = PQexec(pgconn, DECLARE CURSOR select * ); res = PGexec(conn, END); Could someone help me out? What is the best way for 1) using connectin pooling in my situation and 2) it is the right way to do BEGIN; DECLARE CURSOR... ; END; for each select query? Thanks for all your help. JB On Tue, Aug 11, 2009 at 2:02 AM, Tim Hart tjh...@mac.com wrote: On 8/10/09 12:08 PM, Sam Mason s...@samason.me.uk wrote: On Tue, Aug 11, 2009 at 12:41:47AM +0800, Juan Backson wrote: I used PQstatus(conn) function to check connection status, but I found that it still returns CONNECTION_OK even after postgres is restarted. Does anyone know if there is another command that I can use to check connection status? Yes, PQstatus just gives back the last status. It doesn't go off and check anything. What other solution is available to check whether a connection is still alive? As a connection can go down at any time, this doesn't seem useful. Just send off your request as normal and if it fails because the connection was closed then you can open a new one and try again. Depending on your situation, connection pooling might be a reasonable option. Instead of managing the connections yourself, you leave that to another process entirely. http://www.revsys.com/writings/postgresql-performance.html Look at the section on Stateless Applications I spend a lot of time writing stateless apps that server many 'users' concurrently. For me, the pooling idea is much simpler because I only interact with the 'pool', and the pool manages opening and closing connections on my behalf. Of course, this is not a good option if you're writing a stateful app. Your original email didn't say either way, so this is a take on the other side of the problem.
