Thread: Reliability Stability of PgSQL & it's JDBC driver

Reliability Stability of PgSQL & it's JDBC driver

From
"Nikhil G. Daddikar"
Date:
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.



Re: Reliability Stability of PgSQL & it's JDBC driver

From
Peter T Mount
Date:
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/

COPY doesnt work on views

From
Steve Heaven
Date:
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

Re: Reliability Stability of PgSQL & it's JDBC driver

From
"George Johnson"
Date:
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/
>


RE: Reliability Stability of PgSQL & it's JDBC driver

From
"Nathan Barnett"
Date:
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.




Re: Reliability Stability of PgSQL & it's JDBC driver

From
"Nikhil G. Daddikar"
Date:
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.



Re: COPY doesnt work on views

From
"rob"
Date:
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
>


RE: Reliability Stability of PgSQL & it's JDBC driver

From
"Nathan Barnett"
Date:
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.




Re: COPY doesnt work on views

From
Anand Raman
Date:
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

Re: Reliability Stability of PgSQL & it's JDBC driver

From
"Robert B. Easter"
Date:
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/ ------------

RE: Reliability Stability of PgSQL & it's JDBC driver

From
"Nathan Barnett"
Date:
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/ ------------