Subject: Re: constant crashing

From: jack <[email-masked]>
Date: Mon, 15 Apr 2024 01:58:18 +0000

I wrote the windows app.
I export all data to simple ASCII text where fields are delimited with a tab and then run the file through a UTF8 converter (convertcp_v8.3_x86).

I will try the entire process on a Xeon E5-1620 and let it run during the night to see what happens. But the current i9 machine is a machine from only 4 years ago which should have no issues.

On Sunday, April 14th, 2024 at 8:50 PM, Adrian Klaver <[email-masked]> wrote:

> 
> 
> 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 [email-masked] 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
> > > [email-masked]
> 
> 
> --
> Adrian Klaver
> [email-masked]

---

From: "David G. Johnston" <[email-masked]>
Date: Sun, 14 Apr 2024 19:08:56 -0700

On Sun, Apr 14, 2024 at 6:58 PM jack <[email-masked]> wrote:

> But the current i9 machine is a machine from only 4 years ago which should
> have no issues.
>
>
That is a non sequitur.  Besides, if the current code is a representative
example you've probably given it the effective usage of 8 years.

Whether related or not to this error you really should be doing all those
updates on a temporary or unlogged staging table and getting WAL generation
out of the equation.

David J.