Re: postgresql 9.5 has ocuuered OOM

[Uwe]

On Wednesday, December 20, 2017 11:08:51 PM PST mark wrote:
> I have set shared_buffers is 1/4 of memory.
> work_mem is 2% of memory.
> max_connections is 50.
> momery size is 16GB.
> postgresql process used over 70% of memory and occuered OOM.
> what should I do to deal with this problem?

IIRC work_mem is a PER CONNECTION setting, that means every connection can use 
2% of your total memory.  I'd try setting work_mem to something like 16MB or 
32MB and see if the queries still execute properly. You want to set work_mem 
as high as needed to allow proper query execution but as low as possible to 
avoid running out of memory.

Re: postgresql 9.5 has ocuuered OOM

[Uwe]

On Wednesday, December 20, 2017 9:59:24 AM PST David G. Johnston wrote:
> On Wed, Dec 20, 2017 at 9:44 AM, Uwe  wrote:
> > IIRC work_mem is a PER CONNECTION setting,
> 
> ​The docs for this setting clearly state that a single connection/session
> can itself use multiple times this values for a single query.
> 
> https://www.postgresql.org/docs/10/static/runtime-config-resource.html#RUNTI
> ME-CONFIG-RESOURCE-MEMORY
> 

So even worse for the OP. No wonder he runs out of memory. 
Thanks for the correction.

Uwe

Re: Work hours?

[Uwe Seher]

*select sum(case when extract(dow from t.d) in (1,2,3,4,5) then 1 else 0
end) * 8 as hours*
*  from generate_series(current_date::date, (current_date + '10
days'::interval), '1 day'::interval) as t(d)*

*This calculates the working days/hours between 2 dates. You can make your
firt/lastr day of the month/year to a date and feed it into the series.*


*Bye Uwe*



Am Mi., 28. Aug. 2019 um 00:27 Uhr schrieb stan :

> I am just starting to explore the power of PostgreSQL's time and date
> functionality. I must say they seem very powerful.
>
> I need to write a function that, given a month, and a year as input returns
> the "work hours" in that month. In other words something like
>
> 8 * the count of all days in the range Monday to Friday) within that
> calendar month.
>
> Any thoughts as to the best way to approach this?
>
>
> --
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
> -- Benjamin Franklin
>
>
>

crosstab

[Rosebrock, Uwe (Environment, Hobart)]

Hi List
I like to use crosstab to pivot a long list of rows into columns, however the 
column labels are created dynamically with the resulting column type known.

Is there a way pass a list of names and types to crosstab (‘query’) as (<‘type 
list as strings in array’> ) ?
In other words,
Can I pass an array to crosstab row labels

Cheers
Uwe