Now _that_ is a bug. :-)
On 2011-11-10, at 10:09 AM, Alexander Spohr wrote:
> Hi Chuck,
>
> you have to use useBundledJdbcInfo=true with PostgreSQL. Otherwise your
> PostgreSQLAdaptor will hold an open transaction into the database. That will
> prevent commits from getting closed and written (kicking the old values),
> therefore bloating your database and finally kill your performance.
>
> We learned the hard way.
>
> atze
>
>
> Am 09.11.2011 um 23:39 schrieb Chuck Hill:
>
>> Hi Lars,
>>
>> Can you check your connection URL? It should not be using that file, that
>> is mostly for Entity Modeler to generate the schema in the absence of a
>> database connection.
>>
>> See:
>>
>> /**
>> * <P>This method returns true if the connection URL for the
>> * database has a special flag on it which indicates to the
>> * system that the jdbcInfo which has been bundled into the
>> * plugin is acceptable to use in place of actually going to
>> * the database and getting it.
>> */
>> protected boolean shouldUseBundledJdbcInfo() {
>> boolean shouldUseBundledJdbcInfo = false;
>> String url = connectionURL();
>> if (url != null) {
>> shouldUseBundledJdbcInfo = url.toLowerCase().matches(".*(\\?|\\?.*&)" +
>> PostgresqlPlugIn.QUERY_STRING_USE_BUNDLED_JDBC_INFO.toLowerCase() +
>> "=(true|yes)(\\&|$)");
>> }
>> return shouldUseBundledJdbcInfo;
>> }
>>
>>
>> private static final String QUERY_STRING_USE_BUNDLED_JDBC_INFO =
>> "useBundledJdbcInfo";
>>
>>
>>
>> Chuck
>>
>>
>> On 2011-11-09, at 11:41 AM, Lars Sonchocky-Helldorf wrote:
>>
>>>
>>> Am 08.11.2011 um 20:56 schrieb Lars Sonchocky-Helldorf:
>>>
>>>> Hi folks!
>>>>
>>>>
>>>> For reason not to be discussed here my coworker has created a PostgreSQL 9
>>>> database table like follows:
>>>>
>>>> CREATE TABLE systemmessagecontent
>>>> (
>>>> id integer NOT NULL,
>>>> "language" character(2) NOT NULL,
>>>> message text NOT NULL,
>>>> subject character varying(255) NOT NULL,
>>>> systemmessageid integer NOT NULL,
>>>> CONSTRAINT systemmessagecontent_pkey PRIMARY KEY (id),
>>>> CONSTRAINT systemmessagecontent_systemmessage_fk FOREIGN KEY
>>>> (systemmessageid)
>>>> REFERENCES systemmessage (id) MATCH SIMPLE
>>>> ON UPDATE NO ACTION ON DELETE NO ACTION
>>>> )
>>>>
>>>> The interesting point here is column "language" which is a two character
>>>> fixed string.
>>>>
>>>> In the corresponding EOModel the property for this has "char" as External
>>>> Type and an External Width of 2 and no prototype (since there is nothing
>>>> matching).
>>>>
>>>>
>>>>
>>>> When running the application and trying to update that property/column the
>>>> following SQL is generated:
>>>>
>>>> Nov 08 18:28:34 BKAdmin[55555] DEBUG NSLog - === Begin Internal
>>>> Transaction
>>>> Nov 08 18:28:34 BKAdmin[55555] DEBUG NSLog - evaluateExpression:
>>>> <com.webobjects.jdbcadaptor.PostgresqlExpression: "UPDATE
>>>> SystemMessageContent SET language = ?::char WHERE (id = ?::int4 AND
>>>> systemMessageID = ?::int4)" withBindings: 1:"de"(language), 2:28(id),
>>>> 3:48(systemMessageID)>
>>>> Nov 08 18:28:34 BKAdmin[55555] DEBUG NSLog - === Commit Internal
>>>> Transaction
>>>>
>>>> Remarkable here is the "?::char" Obviously this is a cast into a single
>>>> character type of PostgreSQL.
>>>>
>>>> see http://www.postgresql.org/docs/9.1/interactive/datatype-character.html
>>>> table 8-5 for this.
>>>>
>>>>
>>>>
>>>> Correct would have been to use character(2) or char(2) as my tests with
>>>> PGAdmin revealed:
>>>>
>>>> At first I fired the following SQL:
>>>>
>>>> UPDATE SystemMessageContent SET language = 'de'::char WHERE (id = 28::int4
>>>> AND systemMessageID = 48::int4)
>>>>
>>>> The result was the same like the one from the WOApp itself, after updating
>>>> the database contained this: "d "
>>>>
>>>>
>>>> When I changed the query accordingly ('de'::char to 'de'::char(2)) I got
>>>> what I expected:
>>>>
>>>> UPDATE SystemMessageContent SET language = 'de'::char(2) WHERE (id =
>>>> 28::int4 AND systemMessageID = 48::int4)
>>>>
>>>> After this the database contained a "de".
>>>>
>>>>
>>>> Trying to use "char(2)" as External Type in my EOModel (instead of "char")
>>>> I've got the following exception:
>>>>
>>>> Nov 08 18:40:36 BKAdmin[55555] WARN NSLog - *** JDBCAdaptor : no type
>>>> info found for char(2)
>>>> Nov 08 18:40:36 BKAdmin[55555] DEBUG NSLog - === Begin Internal
>>>> Transaction
>>>> Nov 08 18:46:49 BKAdmin[55555] INFO er.transaction.adaptor.Exceptions -
>>>> Database Exception occured:
>>>> com.webobjects.eoaccess.EOGeneralAdaptorException: Unable to find type
>>>> information for external type 'char(2)' in attribute 'language' of entity
>>>> 'SystemMessageContent'. Check spelling and capitalization.
>>>>
>>>>
>>>> Now I think that maybe "char" was the right External Type to begin with.
>>>> Only the JDBCAdaptor did not respect the external with of "2" and did not
>>>> mangle "char" into "char(2)"
>>>>
>>>>
>>>> Is there something I can do about this?
>>>
>>> Adding the following to PostgresqlPlugIn.framework/Resources/JDBCInfo.plist:
>>>
>>> "character" = {
>>> "defaultJDBCType" = (
>>> "CHAR"
>>> );
>>> "createParams" = "1";
>>> "maxScale" = "0";
>>> "minScale" = "0";
>>> "isNullable" = "T";
>>> "isSearchable" = "T";
>>> "precision" = "10485760";
>>> };
>>>
>>> and setting "character" as External Type in the corresponding EOModel did
>>> help.
>>>
>>> The result then is:
>>>
>>> Nov 09 13:13:18 BKAdmin[55555] DEBUG NSLog - === Begin Internal
>>> Transaction
>>> Nov 09 13:13:18 BKAdmin[55555] DEBUG NSLog - evaluateExpression:
>>> <com.webobjects.jdbcadaptor.PostgresqlExpression: "UPDATE
>>> SystemMessageContent SET language = ?::character(2) WHERE (id = ?::int4 AND
>>> systemMessageID = ?::int4)" withBindings: 1:"de"(language), 2:28(id),
>>> 3:48(systemMessageID)>
>>> Nov 09 13:13:18 BKAdmin[55555] DEBUG NSLog - === Commit Internal
>>> Transaction
>>>
>>> Could somebody with commit access to the PostgresqlPlugIn.framework please
>>> add this code upstream?
>>>
>>>
>>>
>>> cheers,
>>>
>>> Lars _______________________________________________
>>> Do not post admin requests to the list. They will be ignored.
>>> Webobjects-dev mailing list ([email protected])
>>> Help/Unsubscribe/Update your Subscription:
>>> http://lists.apple.com/mailman/options/webobjects-dev/chill%40global-village.net
>>>
>>> This email sent to [email protected]
>>
>> --
>> Chuck Hill Senior Consultant / VP Development
>>
>> Practical WebObjects - for developers who want to increase their overall
>> knowledge of WebObjects or who are trying to solve specific problems.
>> http://www.global-village.net/products/practical_webobjects
>>
>>
>>
>>
>>
>>
>>
>> _______________________________________________
>> Do not post admin requests to the list. They will be ignored.
>> Webobjects-dev mailing list ([email protected])
>> Help/Unsubscribe/Update your Subscription:
>> http://lists.apple.com/mailman/options/webobjects-dev/atze%40freeport.de
>>
>> This email sent to [email protected]
>
--
Chuck Hill Senior Consultant / VP Development
Practical WebObjects - for developers who want to increase their overall
knowledge of WebObjects or who are trying to solve specific problems.
http://www.global-village.net/products/practical_webobjects
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list ([email protected])
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to [email protected]
