Re: [Aplaws-devel] Binary Asset Streaming for APLAWS+ 1.0.5 using SQL Blobs

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

Hi Tim,

Am Dienstag, den 24.11.2009, 14:02 +0800 schrieb Aplaws Developer List:
> we have recently upgraded our local instance of APLAWS+ 1.0.5 to
> enable streaming of binary assets to and from the database using SQL
> Blobs.

I think it is very advantageous to update APLAWS to use BLOBs for binary
data as images, binary files, etc.!

This should be true for Oracle which seems to have a sophisticated
implementation of BLOB.

How do you judge the Postgres implementation? I read several
documentation which lets me doubt a little bit. It's implementation
looks to me a kind of lukewarm, e.g. in terms of security, managing the
OID and deleting the last instance, 
see http://lab.lonerunners.net/blog/binary-data-fetching-through-sqli
http://jdbc.postgresql.org/documentation/84/binary-data.html 

I upcomming 8.5  the project will introduce a new hex format for bytea
to avoid all the excaping requirements, see 
http://developer.postgresql.org/pgdocs/postgres/datatype-binary.html 
(instead of polishing the BLOB implementation)

Or did I misread that stuff? 

> The bulk of our testing has been with Oracle SE/XE 10.2.0, Profiling
> has shown a substantial reduction to memory usage in the JVM when
> uploading and downloading large binary assets.  However, we have only
> undertaken limited testing with PostGres 8.4, so results may vary.

Does "limited testing" refer to performance or to basic functions? 
In the latter case it may be better you send it as a patch so others
could test it with postgres without disturbing the trunk. Otherwise you
should commit it.

By the way, three other related issues:

a) Could you resolve the size limitation for filestorage items by using
streaming? See https://fedorahosted.org/aplaws/ticket/30 

We closed it wontfix because of the amount of work to switch to
streaming.

b) Could you work with Postgres 8.4 out of the box? If I try to use 8.4
I get an error (see attached listing) when I try to open the default
start page (load-bundle does work).

c) We had a discussion of the forum some times ago regarding dead lock
situations with postgres and a possible patch created by you and your
colleagues
( https://sourceforge.net/projects/aplaws/forums/forum/368401/topic/1721796 )

Peter

>----------------- Error listing --------------------------------------<

2009-12-06 23:14:41,064 [0:0:1] ERROR rdbms.RDBMSEngine - select
t5.privilege as "this.privilege",
       t5.inherited_p as "this.isInherited",
       t5.grantee_id as "this.granteeID",
       t5.user_p as "this.granteeIsUser",
       t5.name as "this.granteeName",
       t5.primary_email as "this.granteeEmail",
       t5.name_id as "this.granteePersonName.id",
       t5.given_name as "this.granteePersonName.givenN7",
       t5.family_name as "this.granteePersonName.family8"
from (
        select acs_permissions.privilege,
               CASE
                 WHEN acs_permissions.object_id = ? THEN
                    0
                 ELSE
                    1
               END as inherited_p,
               acs_permissions.grantee_id,
               parties.user_p,
               parties.name,
               parties.primary_email,
               parties.given_name,
               parties.family_name,
               parties.name_id
        from
             (select
                    p.privilege,
                    p.object_id,
                    p.grantee_id
             from
                  acs_permissions p,
                  dnm_object_1_granted_context dogc,
                  dnm_granted_context dgc
             where dogc.pd_object_id = ?
               and dogc.pd_context_id = dgc.pd_object_id
               and p.object_id = dgc.pd_context_id) acs_permissions,
             (select groups.group_id as party_id,
                     0 as user_p,
                     groups.name as name,
                     parties.primary_email as primary_email,
                     '' as given_name,
                     '' as family_name,
                     0 as name_id
              from groups, parties
              where groups.group_id = parties.party_id) parties
        where acs_permissions.grantee_id = parties.party_id
        UNION ALL
        select acs_permissions.privilege,
               CASE
                 WHEN acs_permissions.object_id = ? THEN
                    0
                 ELSE
                    1
               END as inherited_p,
               acs_permissions.grantee_id,
               parties.user_p,
               parties.name,
               parties.primary_email,
               parties.given_name,
               parties.family_name,
               parties.name_id
        from 
             (select
                    p.privilege,
                    p.object_id,
                    p.grantee_id
             from
                  acs_permissions p,
                  dnm_object_1_granted_context dogc,
                  dnm_granted_context dgc
             where dogc.pd_object_id = ?
               and dogc.pd_context_id = dgc.pd_object_id
               and p.object_id = dgc.pd_context_id) acs_permissions,
             (select users.user_id as party_id,
                     1 as user_p,
                     '' as name,
                     parties.primary_email as primary_email,
                     person_names.given_name as given_name,
                     person_names.family_name as family_name,
                     users.name_id
              from users, parties, person_names
              where users.user_id = parties.party_id
                and users.name_id = person_names.name_id) parties
        where acs_permissions.grantee_id = parties.party_id) t5
where t5.user_p = '1'
order by t5.grantee_id
org.postgresql.util.PSQLException: FEHLER: failed to find conversion
function from unknown to character varying
	at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
	at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
	at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
	at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
	at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:351)
	at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:344)
	at
com.redhat.persistence.engine.rdbms.RDBMSEngine.execute(RDBMSEngine.java:525)
	at
com.redhat.persistence.engine.rdbms.RDBMSEngine.execute(RDBMSEngine.java:451)
	at
com.redhat.persistence.engine.rdbms.RDBMSEngine.execute(RDBMSEngine.java:283)
	at com.redhat.persistence.Cursor.execute(Cursor.java:141)
	at com.redhat.persistence.Cursor.next(Cursor.java:125)
	at com.arsdigita.persistence.DataQueryImpl.next(DataQueryImpl.java:663)
	at com.arsdigita.domain.DomainQuery.next(DomainQuery.java:97)
	at
com.arsdigita.kernel.security.SecurityConfig.getSystemAdministratorEmailAddress(SecurityConfig.java:179)
	at
com.arsdigita.kernel.security.SecurityConfig.getAdminContactEmail(SecurityConfig.java:168)
	at com.arsdigita.ui.SiteBanner.generateXML(SiteBanner.java:39)
	at
com.arsdigita.bebop.SimpleContainer.generateChildrenXML(SimpleContainer.java:246)
	at
com.arsdigita.bebop.SimpleContainer.generateXML(SimpleContainer.java:263)
	at com.arsdigita.bebop.Page.generateXML(Page.java:633)
	at com.arsdigita.bebop.jsp.DefinePage.doEndTag(DefinePage.java:292)
	at
org.apache.jsp.packages.navigation.templates.shp_002ddefault_jsp._jspService(shp_002ddefault_jsp.java:212)

-- 
Dr. Peter Boy

University of Bremen
Center for Social Policy Research

Department for Research Methods, Statistical Analysis, 
and IT Management

pb...@ze...
phone: +49 421 218 4374  /  4362

Web:  http://www.zes.uni-bremen.de