Re: Properly handling aggregate in nested function call

On 16/12/2021, at 2:43 PM, Tom Lane wrote: > Of course what this function is actually returning is numeric[]. > There is some code such as array_out that will look at the > element type OID embedded in the array value, and do the right > thing. But other code will believe the function's declared

Re: Best Strategy for Large Number of Images

PostgreSQL, thanks to extensions, allows a third non-obvious file strategy. Since many developers when working with small images don't want to overload their solutions with the complexities of interacting with file servers, and I didn't want to store files in a database, I found and tried a third

Re: Properly handling aggregate in nested function call

Matt Magoffin writes: > Any other ideas I could look into? Per the old saw, when you can't see the problem, it usually means you're looking in the wrong place. I looked at the SQL declaration of the function [1], and saw: CREATE OR REPLACE FUNCTION vec_agg_mean_finalfn(internal) RETURNS

Re: Why can't I have a "language sql" anonymous block?

On Wed, Dec 15, 2021 at 4:19 PM Bryn Llewellyn wrote: > Re your paragraph #2, I already made the case for anonymous procedures. > And I said that, to deserve the name, they must allow parameterization. > They bring their value in a certain kind of scripting where you want to do > stuff but leave

Re: Why can't I have a "language sql" anonymous block?

> adrian.kla...@aklaver.com wrote: > >> Bryn wrote: >> >>> mmonc...@gmail.com wrote: >>> >>> SQL language functions have one clear advantage in that they can be inlined >>> in narrow contexts; this can give dramatic performance advantages when it >>> occurs. They have a lot of disadvantages:

Re: Why can't I have a "language sql" anonymous block?

> david.g.johns...@gmail.com wrote: > >> adrian.kla...@aklaver.com wrote: >> >>> Bryn wrote: >>> >>> I used a procedure to test this because functions shouldn’t do DDL. I >>> started with a working “language plpgsql” example… >> >> Since procedures are relatively new to Postgres you are going

Re: Properly handling aggregate in nested function call

On 15/12/2021, at 11:51 AM, Tom Lane wrote: > You should > probably palloc temp arrays right here and then use construct_md_array > directly instead of dealing with an ArrayBuildState. OK, I gave that a go [1] this time in a vec_agg_sum() aggregate, that operates much the same as the

Re: Best Strategy for Large Number of Images

On Wed, Dec 15, 2021 at 1:12 PM Estevan Rech wrote: > > Related to this is the backup service that seems to me that in a disaster, > I can restore the database relatively quickly if it's in the database. On > the disk, I believe that the restoration is much slower and I don't think > it's

Re: Why can't I have a "language sql" anonymous block?

On Wed, Dec 15, 2021 at 2:37 PM Adrian Klaver wrote: > On 12/15/21 13:05, Bryn Llewellyn wrote: > >> mmonc...@gmail.com wrote: > > > — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — > > About your point #1… > > > > I used a procedure to test this because

Re: Properly handling aggregate in nested function call

On 15/12/2021, at 11:51 AM, Tom Lane wrote: > Hmm, I think you're abusing the ArrayBuildState API. In particular, > what guarantees that the astate->dvalues and astate->dnulls arrays > are long enough for what you're stuffing into them? The length is defined in the aggregate transition function

Re: Best Strategy for Large Number of Images

> I don't currently use PostgreSQL, but I plan to migrate and I have a question > about the best way/strategy for storing images. I have about 2 million images > with a growth trend of around 1 million images per year. I plan to store in bytea > format in an isolated table. Is this recommended? Is

Re: Best Strategy for Large Number of Images

I would recommend storing a link to the file rather than the file itself. Other than that, use BLOBS. I would probably recommend not storing any binary objects in a database for a variety of reasons but if you have to then bytea will work, assuming they are smaller than 2gb. Thanks, - Ben On

Re: Best Strategy for Large Number of Images

On Wed, Dec 15, 2021 at 02:45:15PM -0300, Estevan Rech wrote: > Hi, > > I don't currently use PostgreSQL, but I plan to migrate and I have a question > about the best way/strategy for storing images. I have about 2 million images > with a growth trend of around 1 million images per year. I plan

Re: Why can't I have a "language sql" anonymous block?

On 12/15/21 13:05, Bryn Llewellyn wrote: mmonc...@gmail.com wrote: Bryn wrote: david.g.johns...@gmail.com wrote: Bryn wrote: There must be a reason to prefer a “language sql” procedure over a “language plpgsql”

Re: Best Strategy for Large Number of Images

Hi, when I am doing this I store the data in one postgres cluster, with some kind of id for each image , and the images in another with the id as link. The customer app is written so that it issues a dedicated http request for each image. (I use nginx to create a dedicated "path". *pro*: the

Re: Best Strategy for Large Number of Images

On 16 Dec 2021, at 7:12, Estevan Rech wrote: > I have an application that takes pictures of items and uses them as > evidence in a report. > > These images are saved and then used to generate a report. > Once you start talking reports and evidence I think audit trail and verification a.k.a.

Re: Why can't I have a "language sql" anonymous block?

> mmonc...@gmail.com wrote: > >> Bryn wrote: >> >>> david.g.johns...@gmail.com wrote: >>> Bryn wrote: There must be a reason to prefer a “language sql” procedure over a “language plpgsql” procedure—otherwise the former wouldn’t be supported. >>> >>> I would say that is

Re: Best Strategy for Large Number of Images

Adrian, I have an application that takes pictures of items and uses them as evidence in a report. These images are saved and then used to generate a report. Each item averages 10 photos and I have about 2 million photos currently, with an average growth of 1 million photos over the next year.

Re: Best Strategy for Large Number of Images

On 12/15/21 11:22, Estevan Rech wrote: The possibilities are known, but does anyone have experience with this scenario? It would help if you elaborated on the scenerio is? There is no questioning that you can store images in the database. The issues are what you want to do with them once

Re: Best Strategy for Large Number of Images

On 12/15/21 12:22, Estevan Rech wrote: The possibilities are known, but does anyone have experience with this scenario? My previous employer dealt with radiology images.  These were in the file system with an image server as suggested up thread.

Re: Best Strategy for Large Number of Images

On 12/15/21 11:45 AM, Estevan Rech wrote: Hi, I don't currently use PostgreSQL, but I plan to migrate and I have a question about the best way/strategy for storing images. I have about 2 million images with a growth trend of around 1 million images per year. I plan to store in bytea format

Re: Best Strategy for Large Number of Images

The possibilities are known, but does anyone have experience with this scenario?

Re: Best Strategy for Large Number of Images

On Wednesday, December 15, 2021, Estevan Rech wrote: > But PostgreSQL can handle it? Does it have good search performance? and is > memory usage feasible? > Images of that size won’t be stored on the main table so performance when not asking for image data should be normal. Memory is one of

Re: Best Strategy for Large Number of Images

On 12/15/21 10:48 AM, Estevan Rech wrote: But PostgreSQL can handle it? Does it have good search performance? and is memory usage feasible? Search on what? -- Adrian Klaver adrian.kla...@aklaver.com

Re: Best Strategy for Large Number of Images

But PostgreSQL can handle it? Does it have good search performance? and is memory usage feasible?

Re: Best Strategy for Large Number of Images

On 12/15/21 10:17 AM, Estevan Rech wrote: I think about using it inside the database to facilitate the select and generation of reports... As long as the meta data about the images is stored in the database that is still possible. -- Adrian Klaver adrian.kla...@aklaver.com

Re: Best Strategy for Large Number of Images

I think about using it inside the database to facilitate the select and generation of reports... >

Re: Best Strategy for Large Number of Images

On Wed, Dec 15, 2021 at 10:57 AM Estevan Rech wrote: > 350kb > > Em qua., 15 de dez. de 2021 às 14:50, Rob Sargent > escreveu: > >> On 12/15/21 10:45, Estevan Rech wrote: >> >> Hi, >> >> I don't currently use PostgreSQL, but I plan to migrate and I have a >> question about the best way/strategy

Re: Best Strategy for Large Number of Images

350kb Em qua., 15 de dez. de 2021 às 14:50, Rob Sargent escreveu: > On 12/15/21 10:45, Estevan Rech wrote: > > Hi, > > I don't currently use PostgreSQL, but I plan to migrate and I have a > question about the best way/strategy for storing images. I have about 2 > million images with a growth

Re: Best Strategy for Large Number of Images

On 12/15/21 10:45, Estevan Rech wrote: Hi, I don't currently use PostgreSQL, but I plan to migrate and I have a question about the best way/strategy for storing images. I have about 2 million images with a growth trend of around 1 million images per year. I plan to store in bytea format in

Best Strategy for Large Number of Images

Hi, I don't currently use PostgreSQL, but I plan to migrate and I have a question about the best way/strategy for storing images. I have about 2 million images with a growth trend of around 1 million images per year. I plan to store in bytea format in an isolated table. Is this recommended? Is