Re: [DOCS] Huge pages section needs to describe hugetlb_shm_group, memlock limit

[Bruce Momjian]

On Sat, Sep 20, 2014 at 10:24:51PM +0100, Laurence Parry wrote:
> Regarding the coverage of huge pages (added in 9.4):
> http://www.postgresql.org/docs/9.4/static/kernel-resources.html#LINUX-HUGE-PAGES
> 
> This section does not call out the need to add the PostgreSQL user
> to a group which has been given access to shared memory by setting
> its number as vm.hugetlb_shm_group, nor that the "memlock" limit
> needs to be raised or set to unlimited for this user.
> 
> If these are not done, postgres would need to be running as a user
> with CAP_IPC_LOCK (e.g. root) to take advantage of large pages.
> These restrictions exist because reserving a large page locks it in
> physical memory, which can lead to resource exhaustion.
> 
> Compare
> http://dev.mysql.com/doc/refman/5.7/en/large-page-support.html which
> mentions both of these issues (although it does not show adding the
> user to the group, just setting the group as vm.hugetlb_shm_group).
> 
> An example command sequence:
> groupadd hugepages
> gpasswd -a postgres hugepages
> id postgres [user finds "hugepages" group has id 1234]
> 
> sysctl -w vm.hugetlb_shm_group=1234
> sysctl -w vm.nr_hugepages=5678 [as estimated in documentation]
> 
> Edit /etc/sysctl.conf and add:
>vm.hugetlb_shm_group=1234
>vm.nr_hugepages=5678
> 
> Add to /etc/security/limits.conf:
>@hugepages  softmemlock unlimited
>@hugepages  hardmemlock unlimited
> 
> In some cases (e.g. Debian's start-stop-daemon) the command "ulimit
> -l unlimited" may be needed in the PostgreSQL startup scripts
> instead of the limits.conf edit, because PAM limits are not called
> for those scripts.

I have applied the attached doc patch.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +
diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml
new file mode 100644
index 639b288..026850a
*** a/doc/src/sgml/runtime.sgml
--- b/doc/src/sgml/runtime.sgml
*** $ sysctl -w vm.nr_hugepages=3
*** 1361,1366 
--- 1361,1373 
 
  
 
+ It is also necessary to give the database server operating system
+ user permission to use huge pages by setting
+ vm.hugetlb_shm_group via sysctl, and
+ permission to lock memory with ulimit -l.
+
+ 
+
  The default behavior for huge pages in
  PostgreSQL is to use them when possible and
  to fallback to normal pages when failing. To enforce the use of huge

-- 
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs

Re: [DOCS] [BUGS] BUG #11661: CASE ELSE is evaluated although condition is true

[Marti Raudsepp]

On Mon, Oct 13, 2014 at 2:05 PM,   wrote:
> case when sum( column1 ) = 0
> then 0
> else round( sum(   price
>  * hours
>  / column1 ), 2 )

> I would expect that the else part would never be evaluated.
> Documentation only describes that this happens for immediates.

I explained this behavior in the Stack Overflow answer, it is caused
by way aggregates are evaluated in a SELECT (or HAVING) clause.

Our documentation should be clearer about the fact that all aggregates
are evaluated strictly before their surrounding SELECT expressions,
the attached documentation patch adds a note about this to the
"general processing of SELECT" explanation.

I also created a section "Implementation Notes" in the "Conditional
Expressions" chapter to explain this behavior, since conditionals is
where this behavior is most visible.

This similarly affects the HAVING clause:
db=# select sum(1/i) from unnest(array[0])i having min(i)>0;
ERROR:  division by zero

Regards,
Marti


0001-doc-Clarify-evaluation-order-of-aggregates-wrt-condi.patch
Description: binary/octet-stream

-- 
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs