Postgres howto enhancements for review.

[Scott Eade <se...@backstagetech.com.au>]

It seems that whenever somebody has a go at using postgresql 
with turbine a new thread develops along these lines:

1. I can't get newapp to work with postgresql
2. Read the postgresql howto
3. There is some other problem

I thought it would be good to add some further detail 
to the postgresql howto to clarify some of the commonly 
encountered scenarios.

Since I'm new to postgresql, hopefully someone with a 
little more experience can provide me with some feedback 
on this list.

Here are the issues:

1. The postgresql jdbc driver reports its OID columns 
as type INTEGER in the metadata that is used by village 
and thus Torque runs into problems when storing the 
OBJECTDATA columns used in the tables defined by 
turbine-schema.xml.  [The existing document explains 
this very well.]

You can correct this using a couple of different methods:

a. Patch the postgresql jdbc driver so that it reports
VARBINARY in its metadata rather than INTEGER.
[The existing document provides some info but the cvs 
details are incorrect - refer to:
http://marc.theaimsgroup.com/?l=turbine-user&m=100473208129503&w=2
and the patch now has to be applied to a different file -
refer to:
http://marc.theaimsgroup.com/?l=turbine-user&m=100434715720657&w=2
for details.]
I think it would be much easier if we included a patched 
postgres.jar in the tdk or at least in cvs.  Is there a reason 
we can't do this?

b. Patch org.apache.turbine.om.security.TurbineUser so 
that the method setPermStorage() is commented out - this 
will stop the OBJECTDATA from being written to the 
database.  Remember that this means that setPerm() will 
effectively become temporary rather than permanent.  You 
should only need to do this for TURBINE_USER as the 
other tables do not currently use their OBJECTDATA 
columns.

2. When using large objects postgresql requires the use
of transactions.  For this you need the updated 
org.apache.turbine.om.peer.BasePeer from cvs.
[The bytea datatype in postgresql 7.2 may provide some
relief here: 
http://marc.theaimsgroup.com/?l=turbine-user&m=100434715720800&w=2]

3. With the current implementation of torque (tdk 2.1) the
idmethod "sequence" can be used to have postgresql generate
sequential ids for primary keys.  With the next release this will
change to "native" in order for a single value to work across
multiple database implementations.

4. postgresql database creation is not supported by turbine 
on windows.


I would also like to include a comment as to where things 
should stand when turbine 2.2 is released.  I get the
impression that all of the postgresql issues will be dealt
with by the updated torque (except for windows support).
I assume this will no longer require the update to the 
postgresql jdbc driver (torque must keep track of the 
VARBINARY columns and handle them correctly even 
though the jdbc driver will continue to report than as 
being INTEGER).  Can someone please correct me if 
this statement is incorrect.

If I can get some feedback to validate the above comments 
I will add this information to the postgresql howto.

Thanks,

Scott



--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>

Re: Postgres howto enhancements for review.

[Jason van Zyl <jv...@zenplex.com>]

On 11/12/01 8:57 AM, "Scott Eade" <se...@backstagetech.com.au> wrote:

> It seems that whenever somebody has a go at using postgresql
> with turbine a new thread develops along these lines:

It's a problem we are trying to address permanently in the Torque testbed
and soon Turbine 2.x and 3.x will use the decoupled Torque and this problem
should go away. 

You are more than welcome to setup a test profile in Torque.  Take a peek at
the testbed, there are example profiles for postgres. The problem is
definitely with blobs requiring transactions which needs to work properly.

There is also another postres driver at sourceforge if you want to give that
a whirl.

But I will check the body of this email in the torque repository as a note
on the status of postgres and blobs. I remember someone started patching
this but never heard of any closure.
 
> 1. I can't get newapp to work with postgresql
> 2. Read the postgresql howto
> 3. There is some other problem
> 
> I thought it would be good to add some further detail
> to the postgresql howto to clarify some of the commonly
> encountered scenarios.
> 
> Since I'm new to postgresql, hopefully someone with a
> little more experience can provide me with some feedback
> on this list.
> 
> Here are the issues:
> 
> 1. The postgresql jdbc driver reports its OID columns
> as type INTEGER in the metadata that is used by village
> and thus Torque runs into problems when storing the
> OBJECTDATA columns used in the tables defined by
> turbine-schema.xml.  [The existing document explains
> this very well.]
> 
> You can correct this using a couple of different methods:
> 
> a. Patch the postgresql jdbc driver so that it reports
> VARBINARY in its metadata rather than INTEGER.
> [The existing document provides some info but the cvs
> details are incorrect - refer to:
> http://marc.theaimsgroup.com/?l=turbine-user&m=100473208129503&w=2
> and the patch now has to be applied to a different file -
> refer to:
> http://marc.theaimsgroup.com/?l=turbine-user&m=100434715720657&w=2
> for details.]
> I think it would be much easier if we included a patched
> postgres.jar in the tdk or at least in cvs.  Is there a reason
> we can't do this?
> 
> b. Patch org.apache.turbine.om.security.TurbineUser so
> that the method setPermStorage() is commented out - this
> will stop the OBJECTDATA from being written to the
> database.  Remember that this means that setPerm() will
> effectively become temporary rather than permanent.  You
> should only need to do this for TURBINE_USER as the
> other tables do not currently use their OBJECTDATA
> columns.

This is going to disappear anyway, but doesn't address the real problem.
 
> 2. When using large objects postgresql requires the use
> of transactions.  For this you need the updated
> org.apache.turbine.om.peer.BasePeer from cvs.
> [The bytea datatype in postgresql 7.2 may provide some
> relief here: 
> http://marc.theaimsgroup.com/?l=turbine-user&m=100434715720800&w=2]

This is probably the best solution IMO.
 
> 3. With the current implementation of torque (tdk 2.1) the
> idmethod "sequence" can be used to have postgresql generate
> sequential ids for primary keys.  With the next release this will
> change to "native" in order for a single value to work across
> multiple database implementations.

Things have to work with the IDBroker too.
 
> 4. postgresql database creation is not supported by turbine
> on windows.
> 
> 
> I would also like to include a comment as to where things
> should stand when turbine 2.2 is released.  I get the
> impression that all of the postgresql issues will be dealt
> with by the updated torque (except for windows support).
> I assume this will no longer require the update to the
> postgresql jdbc driver (torque must keep track of the
> VARBINARY columns and handle them correctly even
> though the jdbc driver will continue to report than as
> being INTEGER).  Can someone please correct me if
> this statement is incorrect.
> 
> If I can get some feedback to validate the above comments
> I will add this information to the postgresql howto.
> 
> Thanks,
> 
> Scott
> 
> 
> 
> --
> To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
> For additional commands, e-mail: <ma...@jakarta.apache.org>

-- 

jvz.

Jason van Zyl

http://tambora.zenplex.org
http://jakarta.apache.org/turbine
http://jakarta.apache.org/velocity
http://jakarta.apache.org/alexandria
http://jakarta.apache.org/commons



--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>