OFBiz › OFBiz - Dev

[jira] Created: (OFBIZ-1920) Patch to add support for Postgresql with TEXT type fields

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

10 messages Options

Nicolas Malin (Jira)

[jira] Created: (OFBIZ-1920) Patch to add support for Postgresql with TEXT type fields

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

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.

Nicolas Malin (Jira)

[jira] Updated: (OFBIZ-1920) Patch to add support for Postgresql with TEXT type fields

[ https://issues.apache.org/jira/browse/OFBIZ-1920?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Philip W. Dalrymple III updated OFBIZ-1920:
-------------------------------------------

Attachment: patch-text.01

This was done with
diff -U 5 -rN ofbiz ofbiz-dist/ > patch-text.01
where ofbiz had the change in it and ofbiz-dist was
an export of SVN at r685532 (on 2008-08-13)

This is the first patch I have submitted to OfBiz, I hope that the format is
OK.

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

Nicolas Malin (Jira)

[jira] Commented: (OFBIZ-1920) Patch to add support for Postgresql with TEXT type fields

In reply to this post by Nicolas Malin (Jira)

[ https://issues.apache.org/jira/browse/OFBIZ-1920?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12623009#action_12623009 ]

Jacques Le Roux commented on OFBIZ-1920:
----------------------------------------

Sorry, but this patch makes no sense at all.
Please, have, at least, 1st a look at http://docs.ofbiz.org/display/OFBADMIN/OFBiz+Contributors+Best+Practices

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

Nicolas Malin (Jira)

[jira] Commented: (OFBIZ-1920) Patch to add support for Postgresql with TEXT type fields

In reply to this post by Nicolas Malin (Jira)

[ 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.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

Nicolas Malin (Jira)

[jira] Commented: (OFBIZ-1920) Patch to add support for Postgresql with TEXT type fields

In reply to this post by Nicolas Malin (Jira)

[ https://issues.apache.org/jira/browse/OFBIZ-1920?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12623076#action_12623076 ]

Jacques Le Roux commented on OFBIZ-1920:
----------------------------------------

OK, I should have been more specific : had you have a look *inside* your patch ?

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

Nicolas Malin (Jira)

[jira] Commented: (OFBIZ-1920) Patch to add support for Postgresql with TEXT type fields

In reply to this post by Nicolas Malin (Jira)

[ https://issues.apache.org/jira/browse/OFBIZ-1920?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12623086#action_12623086 ]

BJ Freeman commented on OFBIZ-1920:
-----------------------------------

the comments from Jacques, is about the way the patch was done.
not why the patch was done.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

Nicolas Malin (Jira)

[jira] Updated: (OFBIZ-1920) Patch to add support for Postgresql with TEXT type fields

In reply to this post by Nicolas Malin (Jira)

[ https://issues.apache.org/jira/browse/OFBIZ-1920?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Philip W. Dalrymple III updated OFBIZ-1920:
-------------------------------------------

Attachment: postgres-text.patch

This is a new patch for the same changes, The differences are:

This patch was built using svn diff framework/entity
from a checkout of r686734 (2008-08-18 AM EDT).

This will apply WITHOUT enabling the "postnew" fieldtypes (leaves
them at localderby...) so it can be applied clean BUT to test
framework/entity/config/entityengine.xml will have to be edited to
enable the localpostnew datasource.

Some of the comments where edited.

> 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, postgres-text.patch
>
> 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.

Nicolas Malin (Jira)

[jira] Commented: (OFBIZ-1920) Patch to add support for Postgresql with TEXT type fields

In reply to this post by Nicolas Malin (Jira)

[ https://issues.apache.org/jira/browse/OFBIZ-1920?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12632917#action_12632917 ]

Jacques Le Roux commented on OFBIZ-1920:
----------------------------------------

I think I will commit this patch (with slight modifications) if nobody see a problem with that. It is harmless and allow people interested to use text instead of varchar

> 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, postgres-text.patch
>
> 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.

Nicolas Malin (Jira)

[jira] Assigned: (OFBIZ-1920) Patch to add support for Postgresql with TEXT type fields

In reply to this post by Nicolas Malin (Jira)

[ https://issues.apache.org/jira/browse/OFBIZ-1920?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Jacques Le Roux reassigned OFBIZ-1920:
--------------------------------------

Assignee: Jacques Le Roux

> 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
> Assignee: Jacques Le Roux
> Attachments: patch-text.01, postgres-text.patch
>
> 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.

Nicolas Malin (Jira)

[jira] Closed: (OFBIZ-1920) Patch to add support for Postgresql with TEXT type fields

In reply to this post by Nicolas Malin (Jira)

[ https://issues.apache.org/jira/browse/OFBIZ-1920?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Jacques Le Roux closed OFBIZ-1920.
----------------------------------

Resolution: Fixed
Fix Version/s: SVN trunk

Thanks Philip,

Your, slightly modified, patch is in trunk revision: 697967

> 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
> Assignee: Jacques Le Roux
> Fix For: SVN trunk
>
> Attachments: patch-text.01, postgres-text.patch
>
> 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.