[
https://issues.apache.org/jira/browse/OFBIZ-1920?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12623072#action_12623072
]
Philip W. Dalrymple III commented on OFBIZ-1920:
------------------------------------------------
OK Let me try again.
Many database system (MS SQL for an example) are much faster if you limit the
size of text fields hence
"varchar(n)" where N is small. One problem this triggers is that one often has
problems with the length that
was chosen. As an example I noted in the mailing list a thread about the name
of one of the types where
because the length of the filed IN THE DATA BASE was limited the full name
could not be used. Postgres
before some version around 7.x (may have been 6.x I don't remember) in fact
could ONLY handle rather short
text fields unless the programmer used a very odd technique called "Large
Objects" this limitation was removed
in a way that made the performance of the system depend NOT on the configured
size of a field but on
the size of the actual data stored, THIS IS VERY UNLIKE MOST DATABASES where
DEFINING a field
with a varchar above some size (255 is common) make the database performance
fall, therefore it is common
and correct for those databases to limit the size of fields. Postgresql treats
text field in a very different way from
most databases, in PostgreSQL the storage system for text fields shifts based
on the size of the field IN EACH
RECORD so if you use a varchar(20) in one place and a varchar(5000000000) in
another but only store
the string "foobar" (length of 6) the performance WILL NOT BE DIFFERENT; now if
you store say 20 pages of
the bible in the second field there will be a performance impact.
In OfBiz each field type has two storage types, the java type and the database
type, in the case of the java
types for strings there are no limits while for all of the fieldtype files
currently in the release there are
limits on each type (and some can cause problems in edge data cases). This is
necessary for some
(maybe all except for PostgreSQL) as using a unlimited string data type can
cause the data base to
perform poorly but as the postgres manual says there is no reason to use
"varchar(n)" rather than text.
I have been using PostgreSQL for a number of years in production systems, this
feature of the system
with respect to string field is something that has been a godsend.
Given that OfBiz already has abstracted the fieldtypes from the java and
database storage it was easy
to build a new fieldtype file (under the first do no harm rule) so that except
for one additional file that
is not used no-one who does not enable the localpostnew engine is impacted but
for those who are starting
now they can enable this fieldtype list and get the advantages of having the
database and java fields shorter
length limits than the database (as the data must first be in java and
exception will trigger if memory is
overused the user will get a quicker error and the database will not be touched
with data that is bigger
that it is expecting)
While nothing is unlimited (in fact in PostgreSQL TEXT files are limited to
something around a billion)
this change removes a rather tight limit on a number of important fields.
While I have been reading the source code for OfBiz for some time I can't be
sure that I understand the
database update process (change of data base schema) nor can I find any data on
changing the fieldtype
file for a database that already has data in it but I was able to trace the
code well enough (and to be frank
try it out to see if it broke) to understand the case for a brand new
installation. So I put in a warning to
the effect that if someone wants to change the fieldfile on a running system
they should test first.
> Patch to add support for Postgresql with TEXT type fields
> ---------------------------------------------------------
>
> Key: OFBIZ-1920
> URL: https://issues.apache.org/jira/browse/OFBIZ-1920
> Project: OFBiz
> Issue Type: Improvement
> Components: framework
> Affects Versions: SVN trunk
> Environment: Centos 5.3 Postgres 8.1
> Reporter: Philip W. Dalrymple III
> Attachments: patch-text.01
>
> Original Estimate: 24h
> Remaining Estimate: 24h
>
> Postgres does not require any extra cycles to support very long text
> fields, see the manuals at 8.3. Character Types.
> Tip: There are no performance differences between these three types,
> apart from increased storage
> size when using the blank-padded type, and a few extra cycles to check
> the length when storing
> into a length-constrained column. While character(n) has performance
> advantages in some other
> database systems, it has no such advantages in PostgreSQL. In most
> situations text or character
> varying should be used instead.
> This is a feature that I have always (well from 7.x where it was added) and
> in general use TEXT for
> all of my strings.
> Attached is a patch that I have build wrt OfBiz r685532 that adds a new field
> type file that is a
> copy of the postgresql types except that all of the varchar fields are
> changed to text.
> I have installed this on a new copy of OfBiz and ran the system in demo/test
> and it appears not to
> have any problems. The only limit on this is that data dumps from such a
> system may not be loadable
> on a system running another fieldtype file.
> The nature of this change is very simple but if it is necessary for me to
> file a copyright assigment
> let me know.
> Testing, Review and Commitment is all that is needed to close this issue.
> NOTE that I don't know what effect changing the field type from postgers to
> postnew will have, it MIGHT change all of the field types and it might break
> th
> DB, I HAVE NOT TESTED THIS CONDITION!!!!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
