Thread: possible replace() bug - postgres 7.3.1

I am running PostgreSQL 7.3.1 on i686-pc-linux-gnu, compiled by GCC 2.96

the following query fails:

SELECT 'UPDATE pa_sales_lead SET details = COALESCE(details, \'\') ||
\'' || replace(data, '\'', '\'\'') || '\' WHERE foreign_sales_lead_id =
\'' || id || '\';' FROM temp_sales_lead ORDER BY id, row LIMIT 10;

with the error:

ERROR:  repalloc: invalid request size 1073741824

but, the following query (without replace):
SELECT 'UPDATE pa_sales_lead SET details = COALESCE(details, \'\') ||
\'' || data || '\' WHERE foreign_sales_lead_id = \'' || id || '\';' FROM
temp_sales_lead ORDER BY id, row LIMIT 10;

works just fine, producing output like:

UPDATE pa_sales_lead SET details = COALESCE(details, '') || 'EMPLOY: 50'
WHERE foreign_sales_lead_id = '10000004';

any ideas?

-r


On Thu, 2003-11-06 at 12:52, Carlos wrote:
> Hello Forum,
> 
> Could someone please send me the link and the names of the files to
> install the version 7.4 in Windows 2000 server and XP Pro?  I cannot
> discern the files that one is supposed to download..
> 
> Thanks in advance for your response.
> 
> Carlos Oliva
> Senior Programmer/Analyst
> Positive Business Solutions, Inc.
> Cincinnati, OH 45240-1640
> (513) 772 - 2255 ext 129
> carlos@pbsinet.com
> Yahoo ID: ramboid_1997
-- 
Ryan Mahoney <ryan@paymentalliance.net>

Ryan Mahoney kirjutas N, 06.11.2003 kell 23:03:
> I am running PostgreSQL 7.3.1 on i686-pc-linux-gnu, compiled by GCC 2.96
> 
> the following query fails:
> 
> SELECT 'UPDATE pa_sales_lead SET details = COALESCE(details, \'\') ||
> \'' || replace(data, '\'', '\'\'') || '\' WHERE foreign_sales_lead_id =
> \'' || id || '\';' FROM temp_sales_lead ORDER BY id, row LIMIT 10;
> 
> with the error:
> 
> ERROR:  repalloc: invalid request size 1073741824
> 
> but, the following query (without replace):
> SELECT 'UPDATE pa_sales_lead SET details = COALESCE(details, \'\') ||
> \'' || data || '\' WHERE foreign_sales_lead_id = \'' || id || '\';' FROM
> temp_sales_lead ORDER BY id, row LIMIT 10;
> 
> works just fine, producing output like:
> 
> UPDATE pa_sales_lead SET details = COALESCE(details, '') || 'EMPLOY: 50'
> WHERE foreign_sales_lead_id = '10000004';
> 
> any ideas?

what does just

SELECT replace(data, '\'', '\'\'') FROM temp_sales_leadORDER BY id, row LIMIT 10; 

produce ?

----------------
Hannu

Hannu Krosing wrote:
> Ryan Mahoney kirjutas N, 06.11.2003 kell 23:03:
>>I am running PostgreSQL 7.3.1 on i686-pc-linux-gnu, compiled by GCC 2.96
> what does just
> 
> SELECT replace(data, '\'', '\'\'')
>   FROM temp_sales_lead
>  ORDER BY id, row LIMIT 10; 
> 
> produce ?

There was a bug in replace(), fixed by Tom in 7.3.3 I think (for sure by 
7.3.4). If you have any embedded "%" characters in data, it would 
trigger the bug. Try upgrading to 7.3.4.

HTH,

Joe