Обсуждение: re: constant crashing
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.
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.
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.
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
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 importthe 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
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> 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.
IIUC, the crashes are not reproducible for the OP either. I hesitate
to suggest "maybe the hardware is flaky" because that seems like
passing the buck. Still, from the info we have the table definitions
are extremely simple and so are the queries, making it hard to believe
that some rare Postgres bug is being tripped over. It makes no sense
to be seeing this level of instability in mainstream usage on a stable
OS and stable Postgres release. So I'm starting to think that a
hardware problem might be the most plausible explanation.
In any case, there is no information we have that suggests any
plausible line of investigation towards a software problem.
If we can see a crash stack trace or two, maybe that would change.
regards, tom lane
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 machineis a machine from only 4 years ago which should have no issues. On Sunday, April 14th, 2024 at 8:50 PM, Adrian Klaver <adrian.klaver@aklaver.com> 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 canimport 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
On Sun, Apr 14, 2024 at 6:58 PM jack <jack4pg@a7q.com> 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.
Jack: On Sun, 14 Apr 2024 at 22:18, jack <jack4pg@a7q.com> 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 andcopy them onto the ubuntu machine. The data is then imported via the COPY command. > > COPY master (field01,field02..fieldX) FROM '/data/file.text' DELIMITER E'\t' > The fields are tab delimited. Then call them tab-delimited, not CSV. It may be important. You have a tendency of sending your interpretation of information which hampers people trying to help you. As an example, only about three messages above did we see the "signal 11" in the server logs which indicates a probable bug. Signal 11 is normally due to stray pointers or corrupted memory ( although faulty ram can lead to it too ), which hints at a bug, not a resource problem. > But importing the data works. I can get all the data into a single table without any problems. The issue is only when Istart to update the single table. And that is why I started using smaller temporary tables for each CSV file, to do theupdates in the smaller tables before I move them all to a single large table. > After all the data is loaded and updated, I run php programs on the large table to generate reports. All of which workswell EXCEPT for performing the updates on the data. And I do not want to use perl or any outside tool. I want it allone in SQL because I am required to document all my steps so that someone else can take over, so everything needs to beas simple as possible. I suggested perl ( or any similar thing, php should be equally fine ) as it normally makes your importing / debugging much easier / lighter. On the documentation side, you can have the data and some transformations in a big sql file, but having some steps in a php file will have them documented too. But if you want to do text processing in SQL, go ahead, the only problems are going to be making it harder to debug and probably harder to document. Now people know the signal 11 stuff and the problem can probably be tracked. Francisco Olarte.