Pl/PgsSQL array

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Pl/PgsSQL array

Ben-Nes Yonatan
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.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [hidden email])
Reply | Threaded
Open this post in threaded view
|

Re: Pl/PgsSQL array

Richard Huxton
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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Reply | Threaded
Open this post in threaded view
|

Re: Pl/PgsSQL array

Ben-Nes Yonatan
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!

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: Pl/PgsSQL array

Richard Huxton
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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: Pl/PgsSQL array

Ben-Nes Yonatan
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...

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Reply | Threaded
Open this post in threaded view
|

Re: Pl/PgsSQL array

Guy Fraser
In reply to this post by Richard Huxton
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.




---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org