Thread: Reliability Stability of PgSQL & it's JDBC driver
Hi Folks, I am a fan of pgsql and have been using it for lots personal projects where stability of PgSQL was not an issue and most of the time the machine was close to me, so that I could restart the daemons whenever there was a problem. However, now I am writing an ERP solution and am thinking of using EJB along with Postgresql. What I am concerned about is the reliability and stability of Postgresql and it's JDBC driver. Is anybody using Postgresql and JDBC in a "production" environment? Has anybody faced any wierd problems? For example, when I was using the tool TableGen (generates java code from database table), I found out that some of it was not working because PgSQL's JDBC driver didn't support getImportedKeys() and getExportedKeys(). Now are there any such issues? I wouldn't like to hear sometime later that, for example, there is no support for BLOBs as well. Any personal experiences will be greatly appreciated. Thanks for your time. -ngd.
Quoting "Nikhil G. Daddikar" <ngd@iname.com>: > Hi Folks, > > I am a fan of pgsql and have been using it for lots personal projects > where > stability of PgSQL was not an issue and most of the time the machine > was > close to me, so that I could restart the daemons whenever there was a > problem. However, now I am writing an ERP solution and am thinking of > using > EJB along with Postgresql. What I am concerned about is the reliability > and > stability of Postgresql and it's JDBC driver. Is anybody using > Postgresql > and JDBC in a "production" environment? Has anybody faced any wierd > problems? For example, when I was using the tool TableGen (generates > java > code from database table), I found out that some of it was not working > because PgSQL's JDBC driver didn't support getImportedKeys() and > getExportedKeys(). I needed some example on how to retrieve this information. Also, getTables() still has some problems, but this is partially due to what types to accept (not everything seems to use the same names like "TABLES" etc). The main one is large ResultSet's can slow things down, but careful planning of any queries can help that. > Now are there any such issues? I wouldn't like to > hear > sometime later that, for example, there is no support for BLOBs as > well. Large objects have been supported since version 6.2 some 2-3 years ago, although the BLOB interface only appeared this April. Do not confuse getInputStream() to indicate that large objects are not supported. There's about 6 other ways to use them within JDBC. > Any personal experiences will be greatly appreciated. Peter -- Peter Mount peter@retep.org.uk PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/ RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/
The COPY .... TO filename doesnt seem to work.
Copying a table works OK. If Copy from a view isnt allowed how can I get
the results of a query into an external file?
Example:
=> \d top_hits
View = top_hits
Query = SELECT "company"."name", "co_hits"."hits" FROM "company",
"co_hits" WHERE ("co_hits"."number" = "company"."number");
=>copy top_hits to STDOUT;
Copy command returns...
Thanks
Steve
--
thorNET - Internet Consultancy, Services & Training
Phone: 01454 854413
Fax: 01454 854412
http://www.thornet.co.uk
Hi, I started using the JDBC driver and have been slamming it with production level queries, quite a number of simultaneous threads etc. I have noticed some oddities as far as large resultsets returning off the database close to each other (threads finishing around the same time) and some ArrayStoreException's being thrown. I'm checking to see if this is driver related or java related. Appears to be on the java side of things. Otherwise, I don't use any of the driver's bells and whistles and it seems to be working very nicely. George Johnson ----- Original Message ----- From: "Peter T Mount" <peter@retep.org.uk> To: "Nikhil G. Daddikar" <ngd@iname.com> Cc: <pgsql-general@postgresql.org> Sent: Tuesday, December 12, 2000 6:33 AM Subject: Re: [GENERAL] Reliability Stability of PgSQL & it's JDBC driver > Quoting "Nikhil G. Daddikar" <ngd@iname.com>: > > > Hi Folks, > > > > I am a fan of pgsql and have been using it for lots personal projects > > where > > stability of PgSQL was not an issue and most of the time the machine > > was > > close to me, so that I could restart the daemons whenever there was a > > problem. However, now I am writing an ERP solution and am thinking of > > using > > EJB along with Postgresql. What I am concerned about is the reliability > > and > > stability of Postgresql and it's JDBC driver. Is anybody using > > Postgresql > > and JDBC in a "production" environment? Has anybody faced any wierd > > problems? For example, when I was using the tool TableGen (generates > > java > > code from database table), I found out that some of it was not working > > because PgSQL's JDBC driver didn't support getImportedKeys() and > > getExportedKeys(). > > I needed some example on how to retrieve this information. Also, getTables() > still has some problems, but this is partially due to what types to accept (not > everything seems to use the same names like "TABLES" etc). > > The main one is large ResultSet's can slow things down, but careful planning of > any queries can help that. > > > Now are there any such issues? I wouldn't like to > > hear > > sometime later that, for example, there is no support for BLOBs as > > well. > > Large objects have been supported since version 6.2 some 2-3 years ago, > although the BLOB interface only appeared this April. > > Do not confuse getInputStream() to indicate that large objects are not > supported. There's about 6 other ways to use them within JDBC. > > > Any personal experiences will be greatly appreciated. > > Peter > > -- > Peter Mount peter@retep.org.uk > PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/ > RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/ >
I am currently using PostgreSQL with a production system using JSP's and Servlets. The system currently services 250,000 SQL hits a day. The only problem that I have had is with the database pooling. I had to write my own custom database pooling code because I wasn't able to find any that worked appropriately for my situation. This is certainly not a Postgres issue though. Postgres has performed excellently for my project. The main problem that you have in a production system is the VACUUM of the database. This will in most cases severely inhibit performance. If your system isn't 24x7, then you should be fine as you can schedule this for off times. I believe that the need to vacuum the database will be removed in the next version due to WAL functionality, but I'm sure someone else is better qualified to explain that. I just wanted to vouch for the stability of Postgres in a production environment. --------------------------------------------- Nathan Barnett -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Nikhil G. Daddikar Sent: Tuesday, December 12, 2000 8:42 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Reliability Stability of PgSQL & it's JDBC driver Hi Folks, I am a fan of pgsql and have been using it for lots personal projects where stability of PgSQL was not an issue and most of the time the machine was close to me, so that I could restart the daemons whenever there was a problem. However, now I am writing an ERP solution and am thinking of using EJB along with Postgresql. What I am concerned about is the reliability and stability of Postgresql and it's JDBC driver. Is anybody using Postgresql and JDBC in a "production" environment? Has anybody faced any wierd problems? For example, when I was using the tool TableGen (generates java code from database table), I found out that some of it was not working because PgSQL's JDBC driver didn't support getImportedKeys() and getExportedKeys(). Now are there any such issues? I wouldn't like to hear sometime later that, for example, there is no support for BLOBs as well. Any personal experiences will be greatly appreciated. Thanks for your time. -ngd.
Nathan, Thanks for your response. It is great to know that Postgres is working out fine. However, I don't understand what "Vaccum of the DB" means nor what the "WAL" functionality is. If you could briefly explain these, that would be great. Thanks again! -ngd. > The main problem that you have in a production system is the VACUUM of the database. > This will in most cases severely inhibit performance. If your system isn't > 24x7, then you should be fine as you can schedule this for off times. I > believe that the need to vacuum the database will be removed in the next > version due to WAL functionality, but I'm sure someone else is better > qualified to explain that. I just wanted to vouch for the stability of > Postgres in a production environment.
I use DBD/DBI in perl.
use DBI;
my $dbh =
DBI->connect("dbi:Pg:dbname=YourDatabase;host=localhost",'username','passwor
d');
my $sth = $dbh->prepare("select * from my_view");
$sth->execute;
my @row;
while (@ row = $sth->fetchrow_array) {
print join("\t",@row); #or whatever
}
$sth->finish;
$dbh->disconnect;
I'm sure the other postgres/perl interfaces have similar capabilities.
--rob
----- Original Message -----
From: "Steve Heaven" <steve@thornet.co.uk>
To: "rob" <rob@cabrion.com>
Sent: Wednesday, December 13, 2000 2:35 AM
Subject: Re: COPY doesnt work on views
> At 23:55 12/12/00 -0500, you wrote:
> >Try "\o somefile" , then run select & from your_view. See also \pset
> >
>
> Yes I know that I can do it from psql, but I want to achieve the same
thing
> from a Perl CGI script.
>
> Steve
>
> --
> thorNET - Internet Consultancy, Services & Training
> Phone: 01454 854413
> Fax: 01454 854412
> http://www.thornet.co.uk
>
Nikhil, Vacuuming the DB removes deleted entries from the database as they are not actually deleted when you use SQL to delete them, just marked as deleted. This operation clears up space and rearranges the data. If you don't perform many deletes, you may not need to run this. The only wrinkle is that you have to use the ANALYZE command to allow the database to generate statistics used in query planning. You should run this perdiodically after a large number of inserts or deletes has taken place. This command must be used in conjunction with VACUUM which slows down the database. There was talk of allowing the ANALYZE command to run independently of the VACUUM command, but I'm not sure when this will happen. I'm not completely clear on WAL functionality, but I believe that it automatically reuses the space taken up by deleted rows in the database. Someone on the list can probably correct me if any of this is incorrect. --------------------------------------------- Nathan Barnett -----Original Message----- From: Nikhil G. Daddikar [mailto:ngd@iname.com] Sent: Wednesday, December 13, 2000 3:21 AM To: Nathan Barnett; pgsql-general@postgresql.org Subject: Re: [GENERAL] Reliability Stability of PgSQL & it's JDBC driver Nathan, Thanks for your response. It is great to know that Postgres is working out fine. However, I don't understand what "Vaccum of the DB" means nor what the "WAL" functionality is. If you could briefly explain these, that would be great. Thanks again! -ngd. > The main problem that you have in a production system is the VACUUM of the database. > This will in most cases severely inhibit performance. If your system isn't > 24x7, then you should be fine as you can schedule this for off times. I > believe that the need to vacuum the database will be removed in the next > version due to WAL functionality, but I'm sure someone else is better > qualified to explain that. I just wanted to vouch for the stability of > Postgres in a production environment.
hi
If getting the output of the query is all u want then u can spol the
output of the query to a file..
I think it is done using
\o ..
Hope this helps
Annad
On Tue, Dec 12, 2000 at 04:36:10PM +0000, Steve Heaven wrote:
>The COPY .... TO filename doesnt seem to work.
>Copying a table works OK. If Copy from a view isnt allowed how can I get
>the results of a query into an external file?
>
>Example:
>
>=> \d top_hits
>View = top_hits
>Query = SELECT "company"."name", "co_hits"."hits" FROM "company",
>"co_hits" WHERE ("co_hits"."number" = "company"."number");
>
>
>=>copy top_hits to STDOUT;
>Copy command returns...
>
>Thanks
>
>Steve
>
>--
>thorNET - Internet Consultancy, Services & Training
>Phone: 01454 854413
>Fax: 01454 854412
>http://www.thornet.co.uk
On Wednesday 13 December 2000 10:05, Nathan Barnett wrote: > Nikhil, > > Vacuuming the DB removes deleted entries from the database as they are not > actually deleted when you use SQL to delete them, just marked as deleted. > This operation clears up space and rearranges the data. If you don't > perform many deletes, you may not need to run this. The only wrinkle is > that you have to use the ANALYZE command to allow the database to generate > statistics used in query planning. You should run this perdiodically after > a large number of inserts or deletes has taken place. This command must be > used in conjunction with VACUUM which slows down the database. There was > talk of allowing the ANALYZE command to run independently of the VACUUM > command, but I'm not sure when this will happen. I'm not completely clear > on WAL functionality, but I believe that it automatically reuses the space > taken up by deleted rows in the database. Someone on the list can probably > correct me if any of this is incorrect. > > --------------------------------------------- > Nathan Barnett Do UPDATEs also leave the old tuple behind to be cleaned out by VACUUM? If so, then VACUUM is needed not only if you do many deletes, but also if you do lots of updates. > > -----Original Message----- > From: Nikhil G. Daddikar [mailto:ngd@iname.com] > Sent: Wednesday, December 13, 2000 3:21 AM > To: Nathan Barnett; pgsql-general@postgresql.org > Subject: Re: [GENERAL] Reliability Stability of PgSQL & it's JDBC driver > > > Nathan, > > Thanks for your response. It is great to know that Postgres is working out > fine. However, I don't understand what "Vaccum of the DB" means nor what > the "WAL" functionality is. If you could briefly explain these, that would > be great. > > Thanks again! > -ngd. > > > The main problem that you have in a production system is the VACUUM of > > the > > database. > > > This will in most cases severely inhibit performance. If your system > > isn't > > > 24x7, then you should be fine as you can schedule this for off times. I > > believe that the need to vacuum the database will be removed in the next > > version due to WAL functionality, but I'm sure someone else is better > > qualified to explain that. I just wanted to vouch for the stability of > > Postgres in a production environment. -- -------- Robert B. Easter reaster@comptechnews.com --------- - CompTechNews Message Board http://www.comptechnews.com/ - - CompTechServ Tech Services http://www.comptechserv.com/ - ---------- http://www.comptechnews.com/~reaster/ ------------
Robert, I believe you are correct. An UPDATE is combination of INSERT and DELETE and so would require vacuuming if you do it frequently. --------------------------------------------- Nathan Barnett -----Original Message----- From: Robert B. Easter [mailto:reaster@comptechnews.com] Sent: Wednesday, December 13, 2000 1:19 PM To: Nathan Barnett; 'Nikhil G. Daddikar'; pgsql-general@postgresql.org Subject: Re: [GENERAL] Reliability Stability of PgSQL & it's JDBC driver On Wednesday 13 December 2000 10:05, Nathan Barnett wrote: > Nikhil, > > Vacuuming the DB removes deleted entries from the database as they are not > actually deleted when you use SQL to delete them, just marked as deleted. > This operation clears up space and rearranges the data. If you don't > perform many deletes, you may not need to run this. The only wrinkle is > that you have to use the ANALYZE command to allow the database to generate > statistics used in query planning. You should run this perdiodically after > a large number of inserts or deletes has taken place. This command must be > used in conjunction with VACUUM which slows down the database. There was > talk of allowing the ANALYZE command to run independently of the VACUUM > command, but I'm not sure when this will happen. I'm not completely clear > on WAL functionality, but I believe that it automatically reuses the space > taken up by deleted rows in the database. Someone on the list can probably > correct me if any of this is incorrect. > > --------------------------------------------- > Nathan Barnett Do UPDATEs also leave the old tuple behind to be cleaned out by VACUUM? If so, then VACUUM is needed not only if you do many deletes, but also if you do lots of updates. > > -----Original Message----- > From: Nikhil G. Daddikar [mailto:ngd@iname.com] > Sent: Wednesday, December 13, 2000 3:21 AM > To: Nathan Barnett; pgsql-general@postgresql.org > Subject: Re: [GENERAL] Reliability Stability of PgSQL & it's JDBC driver > > > Nathan, > > Thanks for your response. It is great to know that Postgres is working out > fine. However, I don't understand what "Vaccum of the DB" means nor what > the "WAL" functionality is. If you could briefly explain these, that would > be great. > > Thanks again! > -ngd. > > > The main problem that you have in a production system is the VACUUM of > > the > > database. > > > This will in most cases severely inhibit performance. If your system > > isn't > > > 24x7, then you should be fine as you can schedule this for off times. I > > believe that the need to vacuum the database will be removed in the next > > version due to WAL functionality, but I'm sure someone else is better > > qualified to explain that. I just wanted to vouch for the stability of > > Postgres in a production environment. -- -------- Robert B. Easter reaster@comptechnews.com --------- - CompTechNews Message Board http://www.comptechnews.com/ - - CompTechServ Tech Services http://www.comptechserv.com/ - ---------- http://www.comptechnews.com/~reaster/ ------------