Обсуждение: Pl/PgsSQL array
Hi all, Can anyone tell me if Pl/PgSQL can support a multi dimensional array (of up to 5 levels top I guess) with about 100,000 values? and does it stress the system too much? Thanks! Ben-Nes Yonatan Canaan Surfing ltd.
Ben-Nes Yonatan wrote: > Hi all, > > Can anyone tell me if Pl/PgSQL can support a multi dimensional array (of > up to 5 levels top I guess) with about 100,000 values? > and does it stress the system too much? I can't imagine it being wonderful - you probably want a different procedural language. Or a different approach come to think of it - can I ask what you are using 5D arrays with 100,000 values for? -- Richard Huxton Archonet Ltd
Richard Huxton wrote: > Ben-Nes Yonatan wrote: > >> Hi all, >> >> Can anyone tell me if Pl/PgSQL can support a multi dimensional array >> (of up to 5 levels top I guess) with about 100,000 values? >> and does it stress the system too much? > > > I can't imagine it being wonderful - you probably want a different > procedural language. Or a different approach come to think of it - can I > ask what you are using 5D arrays with 100,000 values for? > Indeed you can my good sir :) Im going to insert into one table a list of about 3.5 million products each day, thouse products also contain data about their own categories. Now I receive all of this data every day and I need to delete all of the previous data each time and insert the new one. In order to create the category table (tree table) I want to create an array out of the categories information at the products and then insert it into the the categories table (the tree :)). I also thought about retrieving the data from the products table and inserting it first into a temporary table and from that information create the categories table (tree :)), but I prefer to save the next selects from that table and just create an array. Maybe you or anyone else got a better idea? Thanks again!
Ben-Nes Yonatan wrote: > Richard Huxton wrote: > >>> Can anyone tell me if Pl/PgSQL can support a multi dimensional array >>> (of up to 5 levels top I guess) with about 100,000 values? >>> and does it stress the system too much? >> >> I can't imagine it being wonderful - you probably want a different >> procedural language. Or a different approach come to think of it - can >> I ask what you are using 5D arrays with 100,000 values for? > > Indeed you can my good sir :) > > Im going to insert into one table a list of about 3.5 million products > each day, thouse products also contain data about their own categories. > > Now I receive all of this data every day and I need to delete all of the > previous data each time and insert the new one. > In order to create the category table (tree table) I want to create an > array out of the categories information at the products and then insert > it into the the categories table (the tree :)). I would be tempted use an external perl script to receive the data and insert it into the database, building the categories tree as it did so. -- Richard Huxton Archonet Ltd
Richard Huxton wrote: > Ben-Nes Yonatan wrote: > >> Richard Huxton wrote: >> >>>> Can anyone tell me if Pl/PgSQL can support a multi dimensional array >>>> (of up to 5 levels top I guess) with about 100,000 values? >>>> and does it stress the system too much? >>> >>> >>> I can't imagine it being wonderful - you probably want a different >>> procedural language. Or a different approach come to think of it - >>> can I ask what you are using 5D arrays with 100,000 values for? >> >> >> Indeed you can my good sir :) >> >> Im going to insert into one table a list of about 3.5 million products >> each day, thouse products also contain data about their own categories. >> >> Now I receive all of this data every day and I need to delete all of >> the previous data each time and insert the new one. >> In order to create the category table (tree table) I want to create an >> array out of the categories information at the products and then >> insert it into the the categories table (the tree :)). > > > I would be tempted use an external perl script to receive the data and > insert it into the database, building the categories tree as it did so. > > -- > Richard Huxton > Archonet Ltd Sadly im not familiar with Perl...
On Thu, 2005-07-07 at 08:30 +0100, Richard Huxton wrote: > Ben-Nes Yonatan wrote: > > Richard Huxton wrote: > > > >>> Can anyone tell me if Pl/PgSQL can support a multi dimensional array > >>> (of up to 5 levels top I guess) with about 100,000 values? > >>> and does it stress the system too much? > >> > >> I can't imagine it being wonderful - you probably want a different > >> procedural language. Or a different approach come to think of it - can > >> I ask what you are using 5D arrays with 100,000 values for? > > > > Indeed you can my good sir :) > > > > Im going to insert into one table a list of about 3.5 million products > > each day, thouse products also contain data about their own categories. > > > > Now I receive all of this data every day and I need to delete all of the > > previous data each time and insert the new one. > > In order to create the category table (tree table) I want to create an > > array out of the categories information at the products and then insert > > it into the the categories table (the tree :)). > > I would be tempted use an external perl script to receive the data and > insert it into the database, building the categories tree as it did so. > "Perl and multidimensional arrays" eq "Yikes!" I just beat down a three dimensional hash yesterday, and it was a nasty blood fest. I almost considered rewriting the application in PHP but figured I had enough invested to just push through. I think my Programming perl by O'Reilly is getting too old in the tooth, and I don't write in perl often enough to know the CPAN modules very well. If this is a fresh project, I personally would use PHP it dead easy to manipulate arrays. As a mater of fact almost everything I do in PHP uses arrays, it's the main reason I use it. The main problem I have run into is the 8MB data limit, but that can be changed and if run as a serial processor, it may not be a problem.