On Thu, Apr 05, 2001 at 04:08:48AM -0400, Peter T Mount wrote:
> Quoting Kyle VanderBeek <kylev@yaga.com>:
> 
> 
> > Please consider applying my patch to the 7.0 codebase as a stop-gap 
> > measure until such time as the optimizer can be improved to notice 
> > indecies on INT8 columns and cast INT arguments up.
> 
> This will have to wait until after 7.1 is released. As this is a "new" feature, 
> this can not be included into 7.1 as it's now in the final Release Candidate 
> phase.

This is a new feature?  Using indecies is "new"?  I guess I really beg to 
differ.  Seems like a bugfix to me (in the "workaround" category).

I'm going to start digging around in the optimizer code so such hacks as 
mine aren't needed.  It's really haenous to find out your production 
server is freaking out and doing sequential scans for EVERYTHING.

Another hack I need to work on (or someone else can) is to squish in a
layer of filesystem hashing for large objects.  We tried to use large
objects and got destroyed.  40,000 rows and the server barely functioned.
I think this is because of 2 things:

1) Filehandles not being closed.  This was an oversite I've seen covered 
in the list archives somewhere.

2) The fact that all objects are stored in a the single data directory.  
Once you get up to a good number of objects, directory scans really take a 
long, long time.  This slows down any subsequent openings of large 
objects.  Is someone working on this problem?  Or have a patch already?

-- 
Kyle.
   "I hate every ape I see, from chimpan-A to chimpan-Z" -- Troy McClure

Mon, 9 Apr 2001 18:30:56 -0700
Kyle VanderBeek <kylev@yaga.com>
Re: JDBC int8 hack
At 18:30 09/04/01 -0700, Kyle VanderBeek wrote:
>On Thu, Apr 05, 2001 at 04:08:48AM -0400, Peter T Mount wrote:
> > Quoting Kyle VanderBeek <kylev@yaga.com>:
> >
> >
> > > Please consider applying my patch to the 7.0 codebase as a stop-gap
> > > measure until such time as the optimizer can be improved to notice
> > > indecies on INT8 columns and cast INT arguments up.
> >
> > This will have to wait until after 7.1 is released. As this is a "new" 
> feature,
> > this can not be included into 7.1 as it's now in the final Release 
> Candidate
> > phase.
>
>This is a new feature?  Using indecies is "new"?  I guess I really beg to
>differ.  Seems like a bugfix to me (in the "workaround" category).

Yes they are. INT8 is not a feature/type yet supported by the driver, hence 
it's "new".

Infact the jdbc driver supports no array's at this time (as PostgreSQL & 
SQL3 arrays are different beasts).

If it's worked in the past, then that was sheer luck.

>I'm going to start digging around in the optimizer code so such hacks as
>mine aren't needed.  It's really haenous to find out your production
>server is freaking out and doing sequential scans for EVERYTHING.

Are you talking about the optimiser in the backend as there isn't one in 
the jdbc driver.


>Another hack I need to work on (or someone else can) is to squish in a
>layer of filesystem hashing for large objects.  We tried to use large
>objects and got destroyed.  40,000 rows and the server barely functioned.
>I think this is because of 2 things:
>
>1) Filehandles not being closed.  This was an oversite I've seen covered
>in the list archives somewhere.

Ok, ensure you are closing the large objects within JDBC. If you are then 
this is a backend problem.

One thing to try is to commit the transaction a bit more often (if you are 
running within a single transaction for all 40k objects). Committing the 
transaction will force the backend to close all open large objects on that 
connection.

>2) The fact that all objects are stored in a the single data directory.
>Once you get up to a good number of objects, directory scans really take a
>long, long time.  This slows down any subsequent openings of large
>objects.  Is someone working on this problem?  Or have a patch already?

Again not JDBC. Forwarding to the hackers list on this one. The naming 
conventions were changed a lot in 7.1, and it was for more flexability.

Peter


Tue, 10 Apr 2001 14:24:24 +0100
Peter Mount <peter@retep.org.uk>
Large Object problems (was Re: JDBC int8 hack)
On Tue, Apr 10, 2001 at 02:24:24PM +0100, Peter Mount wrote:
> At 18:30 09/04/01 -0700, Kyle VanderBeek wrote:
> >This is a new feature?  Using indecies is "new"?  I guess I really beg to
> >differ.  Seems like a bugfix to me (in the "workaround" category).
> 
> Yes they are. INT8 is not a feature/type yet supported by the driver, hence 
> it's "new".
> 
> Infact the jdbc driver supports no array's at this time (as PostgreSQL & 
> SQL3 arrays are different beasts).
> 
> If it's worked in the past, then that was sheer luck.

Alright man, you've got me confused.  Are you saying that despite the 
existance of INT8 as a column type, and PreparedStatement.setLong(), that 
these ought not be used?  If so, there is a really big warning missing 
from the documentation!

I guess I'm asking this: I've got an enterprise database runnign 7.0.3
ready to go using INT8 primary keys and being accessed through my
re-touched JDBC driver.  Am I screwed?  Is it going to break?  If so, I
need to fix this all very, very fast.

-- 
Kyle.
   "I hate every ape I see, from chimpan-A to chimpan-Z" -- Troy McClure

Tue, 10 Apr 2001 13:39:16 -0700
Kyle VanderBeek <kylev@yaga.com>
Re: Large Object problems (was Re: JDBC int8 hack)
Sorry, meant to hit all of these.

On Tue, Apr 10, 2001 at 02:24:24PM +0100, Peter Mount wrote:
> >I'm going to start digging around in the optimizer code so such hacks as
> >mine aren't needed.  It's really haenous to find out your production
> >server is freaking out and doing sequential scans for EVERYTHING.
> 
> Are you talking about the optimiser in the backend as there isn't one in 
> the jdbc driver.

Yeah, in the backend.  My patch to the JDBC driver only helps people using
JDBC to get to a database (obviously).  From any other access method, a
statement like:

SELECT * FROM Foo where bar=1234

will do a sequential scan even if there is an index Foo_bar_idx on "bar"  
if bar is INT8.  It seems to me that the optimizer should be able to
notice the index Foo_bar_idx and convert the argument "1234" to an INT8 in 
order to use Foo_bar_idx over doing a sequential scan (in which case, 
"1234" probably gets converted to INT8 anyhow to do comparisons).

Granted, I'm theorizing.  I should probably shut up and RTFS.

Anyhow, all my patch did was tack the "::int8" cast onto parameters that 
were set by PreparedStatement.setLong().  We did this after finding that 
EXPLAIN'ing this:

SELECT * FROM Foo where bar=1234::int8

didn't degrade to a sequential scan like the other SELECT statement (w/o 
the cast).

> >Another hack I need to work on (or someone else can) is to squish in a
> >layer of filesystem hashing for large objects.  We tried to use large
> >objects and got destroyed.  40,000 rows and the server barely functioned.
> >I think this is because of 2 things:
> >
> >1) Filehandles not being closed.  This was an oversite I've seen covered
> >in the list archives somewhere.
> 
> Ok, ensure you are closing the large objects within JDBC. If you are then 
> this is a backend problem.
> 
> One thing to try is to commit the transaction a bit more often (if you are 
> running within a single transaction for all 40k objects). Committing the 
> transaction will force the backend to close all open large objects on that 
> connection.

We were using setBytes(), as we were trying to minimize porting work from 
the previous database we were using.  And we were comitting after every 
transaction.  We switched to Base64 encoding and storing strings, so we're 
in better shape now.

I'm going to write some more test code in my evenings and see if I can get 
current PostgreSQL to suck up filehandles.  I'll post again if I can put 
together a coherent bug report or patch.

> >2) The fact that all objects are stored in a the single data directory.
> >Once you get up to a good number of objects, directory scans really take a
> >long, long time.  This slows down any subsequent openings of large
> >objects.  Is someone working on this problem?  Or have a patch already?
> 
> Again not JDBC. Forwarding to the hackers list on this one. The naming 
> conventions were changed a lot in 7.1, and it was for more flexability.

Right, cool.  I'll check out the new codebase.  Thanks.

This is *so* the curse of open source: now I'm going to be using up my 
personal time to look for bugs I find at work.  Oh well, I didn't need 
to sleep anyhow. ;-)

-- 
Kyle.
   "I hate every ape I see, from chimpan-A to chimpan-Z" -- Troy McClure

Tue, 10 Apr 2001 14:08:22 -0700
Kyle VanderBeek <kylev@yaga.com>
Re: Large Object problems (was Re: JDBC int8 hack)
> > >This is a new feature?  Using indecies is "new"?  I guess I really beg to
> > >differ.  Seems like a bugfix to me (in the "workaround" category).
> > Yes they are. INT8 is not a feature/type yet supported by the driver, hence
> > it's "new".
> > Infact the jdbc driver supports no array's at this time (as PostgreSQL &
> > SQL3 arrays are different beasts).
> > If it's worked in the past, then that was sheer luck.
> Alright man, you've got me confused.  Are you saying that despite the
> existance of INT8 as a column type, and PreparedStatement.setLong(), that
> these ought not be used?  If so, there is a really big warning missing
> from the documentation!

Ah, it just dawned on me what might be happening: Peter, I'm guessing
that you are thinking of "INT48" or some such, the pseudo-integer array
type. Kyle is referring to the "int8" 8 byte integer type.

> I guess I'm asking this: I've got an enterprise database runnign 7.0.3
> ready to go using INT8 primary keys and being accessed through my
> re-touched JDBC driver.  Am I screwed?  Is it going to break?  If so, I
> need to fix this all very, very fast.

btw, it might be better to use a syntax like

  ... where col = '1234';

or

 ... where col = int8 '1234';

If the former works, then that is a bit more generic that slapping a
"::int8" onto the constant field.

I'd imagine that this could also be coded into the app; if so that may
be where it belongs since then the driver does not have to massage the
queries as much and it will be easier for the *driver* to stay
compatible with applications.

                   - Thomas

Wed, 11 Apr 2001 02:57:16 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: Large Object problems (was Re: JDBC int8 hack)
On Wed, Apr 11, 2001 at 02:57:16AM +0000, Thomas Lockhart wrote:
> > Alright man, you've got me confused.  Are you saying that despite the
> > existance of INT8 as a column type, and PreparedStatement.setLong(), that
> > these ought not be used?  If so, there is a really big warning missing
> > from the documentation!
> 
> Ah, it just dawned on me what might be happening: Peter, I'm guessing
> that you are thinking of "INT48" or some such, the pseudo-integer array
> type. Kyle is referring to the "int8" 8 byte integer type.

Yes!

> > I guess I'm asking this: I've got an enterprise database runnign 7.0.3
> > ready to go using INT8 primary keys and being accessed through my
> > re-touched JDBC driver.  Am I screwed?  Is it going to break?  If so, I
> > need to fix this all very, very fast.
> 
> btw, it might be better to use a syntax like
> 
>   ... where col = '1234';
> 
> or
> 
>  ... where col = int8 '1234';
> 
> If the former works, then that is a bit more generic that slapping a
> "::int8" onto the constant field.

It seems like a wash to me; either way gets the desired result.  Tacking 
on ::int8 was the quickest.  It also seems neater than this:

   set(parameterIndex, ("int8 '" + new Long(x)).toString() + "'");

in PreparedStatement.setLong().

> I'd imagine that this could also be coded into the app; if so that may
> be where it belongs since then the driver does not have to massage the
> queries as much and it will be easier for the *driver* to stay
> compatible with applications.

This seems to be the wrong idea to me.  The idea is that JDBC allows you 
to be a little bit "backend agnostic".  It'd be pretty disappointing if 
this wasn't true for even the base types.  Application programmers should 
just call setLong() they're dealing with an 8-byte (Long or long) integer.  
It'd be a shame to have a PostgreSQL-specific call to setString("int8 '" + 
x.toString() + "'") littering your code.  That seems to fly in the face of 
everything that JDBC/DBI/ODBC (etc) are about.

-- 
Kyle.
   "I hate every ape I see, from chimpan-A to chimpan-Z" -- Troy McClure

Wed, 11 Apr 2001 13:46:44 -0700
Kyle VanderBeek <kylev@yaga.com>
Re: Re: Large Object problems (was Re: JDBC int8 hack)
Kyle VanderBeek <kylev@yaga.com> writes:
> Please consider applying my patch to the 7.0 codebase as a stop-gap 
> measure until such time as the optimizer can be improved to notice 
> indecies on INT8 columns and cast INT arguments up.

I think it's an extremely bad idea to hack up JDBC to get around a
backend shortcoming.  The hack will persist and cause problems long
after the real issue has been fixed.

			regards, tom lane

Fri, 13 Apr 2001 12:51:59 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Re: JDBC int8 hack 
Quoting Kyle VanderBeek <kylev@yaga.com>:

> On Tue, Apr 10, 2001 at 02:24:24PM +0100, Peter Mount wrote:
> > At 18:30 09/04/01 -0700, Kyle VanderBeek wrote:
> > >This is a new feature?  Using indecies is "new"?  I guess I really
> beg to
> > >differ.  Seems like a bugfix to me (in the "workaround" category).
> > 
> > Yes they are. INT8 is not a feature/type yet supported by the driver,
> hence 
> > it's "new".
> > 
> > Infact the jdbc driver supports no array's at this time (as PostgreSQL
> & 
> > SQL3 arrays are different beasts).
> > 
> > If it's worked in the past, then that was sheer luck.
> 
> Alright man, you've got me confused.  Are you saying that despite the 
> existance of INT8 as a column type, and PreparedStatement.setLong(),
> that 
> these ought not be used?  If so, there is a really big warning missing 
> from the documentation!

Erm, int8 isn't long, but an array of 8 int's (unless it's changed).

> I guess I'm asking this: I've got an enterprise database runnign 7.0.3
> ready to go using INT8 primary keys and being accessed through my
> re-touched JDBC driver.  Am I screwed?  Is it going to break?  If so, I
> need to fix this all very, very fast.
> 
> -- 
> Kyle.
>    "I hate every ape I see, from chimpan-A to chimpan-Z" -- Troy
> McClure
> 



-- 
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/

Tue, 17 Apr 2001 09:11:54 -0400 (EDT)
Peter T Mount <peter@retep.org.uk>
Re: Large Object problems (was Re: JDBC int8 hack)
Quoting Thomas Lockhart <lockhart@alumni.caltech.edu>:

> > > >This is a new feature?  Using indecies is "new"?  I guess I really
> beg to
> > > >differ.  Seems like a bugfix to me (in the "workaround" category).
> > > Yes they are. INT8 is not a feature/type yet supported by the
> driver, hence
> > > it's "new".
> > > Infact the jdbc driver supports no array's at this time (as
> PostgreSQL &
> > > SQL3 arrays are different beasts).
> > > If it's worked in the past, then that was sheer luck.
> > Alright man, you've got me confused.  Are you saying that despite the
> > existance of INT8 as a column type, and PreparedStatement.setLong(),
> that
> > these ought not be used?  If so, there is a really big warning
> missing
> > from the documentation!
> 
> Ah, it just dawned on me what might be happening: Peter, I'm guessing
> that you are thinking of "INT48" or some such, the pseudo-integer array
> type. Kyle is referring to the "int8" 8 byte integer type.

Ah, that would explain it. However int8 (as in 8 byte int) has not been 
implemented AFAIK (which is why I've said it's "new"). Until now, I've taken 
int8 to be the one that used to be used (probably still is) in system tables 
etc.

> > I guess I'm asking this: I've got an enterprise database runnign
> 7.0.3
> > ready to go using INT8 primary keys and being accessed through my
> > re-touched JDBC driver.  Am I screwed?  Is it going to break?  If so,
> I
> > need to fix this all very, very fast.
> 
> btw, it might be better to use a syntax like
> 
>   ... where col = '1234';
> 
> or
> 
>  ... where col = int8 '1234';
> 
> If the former works, then that is a bit more generic that slapping a
> "::int8" onto the constant field.
> 
> I'd imagine that this could also be coded into the app; if so that may
> be where it belongs since then the driver does not have to massage the
> queries as much and it will be easier for the *driver* to stay
> compatible with applications.

I agree.

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/

Tue, 17 Apr 2001 09:27:33 -0400 (EDT)
Peter T Mount <peter@retep.org.uk>
Re: Large Object problems (was Re: JDBC int8 hack)
> Erm, int8 isn't long, but an array of 8 int's (unless it's changed).

int8 is a 64-bit integer. There used to be a type (maybe called int48
??) which was 8 4-byte integers. afaicr that is now called oidvector
(and there is an int2vector also). The name changes for these latter
types were fairly recent.

Kyle is asking about the 64-bit integer type called int8 in the catalog
and int64 in the backend source code.

                       - Thomas

Tue, 17 Apr 2001 13:30:57 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: Large Object problems (was Re: JDBC int8 hack)
Peter T Mount <peter@retep.org.uk> writes:
>> Ah, it just dawned on me what might be happening: Peter, I'm guessing
>> that you are thinking of "INT48" or some such, the pseudo-integer array
>> type. Kyle is referring to the "int8" 8 byte integer type.

> Ah, that would explain it. However int8 (as in 8 byte int) has not been 
> implemented AFAIK (which is why I've said it's "new"). Until now, I've taken 
> int8 to be the one that used to be used (probably still is) in system tables 
> etc.

Say what?  "int8" has been a 64-bit-integer type since release 6.4.
I think it existed in contrib even before that, but certainly that is
what "int8" has meant for the last three or so years.

			regards, tom lane

Tue, 17 Apr 2001 10:53:18 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Re: Large Object problems (was Re: JDBC int8 hack) 
On Tue, Apr 17, 2001 at 09:11:54AM -0400, Peter T Mount wrote:
> Erm, int8 isn't long, but an array of 8 int's (unless it's changed).

http://postgresql.readysetnet.com/users-lounge/docs/7.0/user/datatype.htm#AEN942

It is very much an 8-byte integer, the correlary to Java's Long/long.

-- 
Kyle.
   "I hate every ape I see, from chimpan-A to chimpan-Z" -- Troy McClure

Tue, 17 Apr 2001 11:29:27 -0700
Kyle VanderBeek <kylev@yaga.com>
Re: Large Object problems (was Re: JDBC int8 hack)