Feedback-ID: i76984098:Fastmail
Message-ID: <22563b64-de45-4131-b6b1-8498abb6c718@aklaver.com>
Date: Sun, 14 Apr 2024 17:50:46 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: constant crashing
To: jack <jack4pg@a7q.com>
References: <RFS5d0T3zgpLa3Rd39K96mcUndlPZD7ktJmzEXkPTrcIwO3U0MDFC0StbozM0qW9tl7X1YDVVYGFQhCOMFi9r4kp3OohQCFIh198LV1HZvQ=@a7q.com>
 <311b047d-01f1-4ce3-86de-09ff0181ba00@aklaver.com>
 <gtqwQ9NTVWCQaWUta110L-ZketsanYrjsFqdJ6VThAv9xPs6WO292iE57STq4N2sQUCoRMWv_Fd4gAs8SzS5t9wwxnGc4E8qhpPin_x72H0=@a7q.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
In-Reply-To: <gtqwQ9NTVWCQaWUta110L-ZketsanYrjsFqdJ6VThAv9xPs6WO292iE57STq4N2sQUCoRMWv_Fd4gAs8SzS5t9wwxnGc4E8qhpPin_x72H0=@a7q.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/22563b64-de45-4131-b6b1-8498abb6c718%40aklaver.com>
Precedence: bulk

On 4/14/24 14:50, jack wrote:

Reply to list also
Ccing list

> Hello,
> I am not sure what "locale" means.

Go to the settings App for whatever version of Windows you are on and 
search for locale.

> The Windows app is an inhouse application which uses Actian-Zen SQL.
> The data is exported to simple ASCII in a tab delimited format similar to CSV.

And you know it is ASCII for a fact?

> Those files are then imported into the PostgreSQL table using COPY.
> Importing the data is not an issue.
> I am able to load all the data without any problems, even into 1 table which ends up with about 1.2 billion records.
> But when I try to update the data in that table I get many errors, essentially crashes.

Repeating what has been asked and answered it not really going anywhere.

> There may be some control characters (garbage) in the data but that should not crash postgresql, especially if it can import the data without issues.

Unless it does. That is the point of the questions, getting to what is 
actually causing the issue. Until the problem can be boiled down to a 
reproducible test case there really is not much hope of anything more 
then the the 'yes you have a problem' answer. And there is a difference 
between dumping data into a table and then doing an UPGRADE where the 
data strings are manipulated by functions.

> Anyway, I hope I answered your questions.
> Thanks for your help.
> 
> 
> 
> On Sunday, April 14th, 2024 at 4:28 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> 
>>
>>
>> On 4/14/24 13:18, jack wrote:
>>
>>> The CSV files are being produced by another system, a WIndows app on a
>>> Windows machine. I then copy them to a USB key and copy them onto the
>>> ubuntu machine. The data is then imported via the COPY command.
>>
>>
>> The app?
>>
>> The locale in use on the Windows machine?
>>
>> The locale in use in the database?
>>
>>> COPY master (field01,field02..fieldX) FROM '/data/file.text' DELIMITER E'\t'
>>> The fields are tab delimited.
>>>
>>> But importing the data works. I can get all the data into a single table
>>> without any problems. The issue is only when I start to update the
>>> single table. And that is why I started using smaller temporary tables
>>> for each CSV file, to do the updates in the smaller tables before I move
>>> them all to a single large table.
>>
>>
>> The import is just dumping the data in, my suspicion is the problem is
>> related to using string functions on the data.
>>
>>> After all the data is loaded and updated, I run php programs on the
>>> large table to generate reports. All of which works well EXCEPT for
>>> performing the updates on the data. And I do not want to use perl or any
>>> outside tool. I want it all one in SQL because I am required to document
>>> all my steps so that someone else can take over, so everything needs to
>>> be as simple as possible.
>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com

-- 
Adrian Klaver
adrian.klaver@aklaver.com