Re: How clear the cache on postgresql?

2017-11-25 Thread Adam Tauno Williams
On Fri, 2017-11-24 at 11:58 -0800, John R Pierce wrote:
> On 11/24/2017 11:43 AM, Michael Nolan wrote:
> > There are so many different levels of caching going on--within 
> > Postgresql, within the OS, within a disk array or SAN, and at the 
> > individual drive--that there may no longer be a meaningful way to 
> > perform this measurement.
> generally, power cycling the server will flush all the hardware
> caches AND the OS cache.

Given that a real-world application will almost never experience an
empty-cache scenario I question the usefulness of clearing the
cache(s).   I would capture transactions from a production database in
order to create a load test that mimics real-world load.

-- 
Meetings Coordinator, Michigan Association of Railroad Passengers
537 Shirley St NE Grand Rapids, MI 49503-1754 Phone: 616.581.8010
E-mail: awill...@whitemice.org GPG#D95ED383 Web: http://www.marp.org



Re: update field in jsonb

2017-11-25 Thread Sven R. Kunze

On 24.11.2017 12:19, Oleg Bartunov wrote:

btw, in PG 11 we expect subscription index
  update test set data[age]= '30';


Out of curiosity:

Why not?

update test set data->age= 30;

It reminds me more of how to query json in PG

select data->age from test;


Cheer,
Sven



Roles and security

2017-11-25 Thread nikhil raj
Hi

I am Nikhil currently working as a MS SQL DBA . Currently my company is
shifting from MS SQL to postgres

 check the roles of user I want to give user roles only select, insert,
update, execute and create database to all users Permission


The users should not have these permission
 drop database , delete table permission


**(Example --like in MS SQL we have like DB_roles-- ddladmin,data
writer,data reader ,etc like these do we have in postgres).***

Thanks


Re: backends stuck in "startup"

2017-11-25 Thread Tom Lane
Justin Pryzby  writes:
> We never had any issue during the ~2 years running PG96 on this VM, until
> upgrading Monday to PG10.1, and we've now hit it 5+ times.

> BTW this is a VM run on a hypervisor managed by our customer:
> DMI: VMware, Inc. VMware Virtual Platform/440BX Desktop Reference Platform, 
> BIOS 6.00 06/22/2012

> Linux TS-DB 2.6.32-431.el6.x86_64 #1 SMP Fri Nov 22 03:15:09 UTC 2013 x86_64 
> x86_64 x86_64 GNU/Linux

Actually ... I was focusing on the wrong part of that.  It's not
your hypervisor, it's your kernel.  Running four-year-old kernels
is seldom a great idea, and in this case, the one you're using
contains the well-reported missed-futex-wakeups bug:

https://bugs.centos.org/view.php?id=8371

While rebuilding PG so it doesn't use POSIX semaphores will dodge
that bug, I think a kernel update would be a far better idea.
There are lots of other known bugs in that version.

Relevant to our discussion, the fix involves inserting a memory
barrier into the kernel's futex call handling:

https://github.com/torvalds/linux/commit/76835b0ebf8a7fe85beb03c75121419a7dec52f0

regards, tom lane



Re: Roles and security

2017-11-25 Thread John R Pierce

On 11/25/2017 12:03 PM, nikhil raj wrote:
 check the roles of user I want to give user roles only select, 
insert, update, execute and create database to all users Permission




those permissions can be assigned on a table by table basis, except 
create database, thats a special permission.    if you own a database 
(the creator owns it by default) then you can drop it, if you don't, you 
can't.




The users should not have these permission
 drop database , delete table permission


generally the owners of objects can grant/revoke permissions on said 
objects.






**(Example --like in MS SQL we have like DB_roles-- ddladmin,data 
writer,data reader ,etc like these do we have in postgres).***


you could create roles like these, make users members of these roles,a 
nd grant permissions to the roles.



for more information, see

https://www.postgresql.org/docs/current/static/user-manag.html
https://www.postgresql.org/docs/current/static/sql-grant.html
https://www.postgresql.org/docs/current/static/sql-revoke.html
https://www.postgresql.org/docs/current/static/sql-alterdefaultprivileges.html


--
john r pierce, recycling bits in santa cruz




Re: backends stuck in "startup"

2017-11-25 Thread Justin Pryzby
On Sat, Nov 25, 2017 at 05:45:59PM -0500, Tom Lane wrote:
> Justin Pryzby  writes:
> > We never had any issue during the ~2 years running PG96 on this VM, until
> > upgrading Monday to PG10.1, and we've now hit it 5+ times.
> 
> > BTW this is a VM run on a hypervisor managed by our customer:
> > DMI: VMware, Inc. VMware Virtual Platform/440BX Desktop Reference Platform, 
> > BIOS 6.00 06/22/2012
> 
> > Linux TS-DB 2.6.32-431.el6.x86_64 #1 SMP Fri Nov 22 03:15:09 UTC 2013 
> > x86_64 x86_64 x86_64 GNU/Linux
> 
> Actually ... I was focusing on the wrong part of that.  It's not
> your hypervisor, it's your kernel.  Running four-year-old kernels
> is seldom a great idea, and in this case, the one you're using
> contains the well-reported missed-futex-wakeups bug:
> 
> https://bugs.centos.org/view.php?id=8371
> 
> While rebuilding PG so it doesn't use POSIX semaphores will dodge
> that bug, I think a kernel update would be a far better idea.
> There are lots of other known bugs in that version.
> 
> Relevant to our discussion, the fix involves inserting a memory
> barrier into the kernel's futex call handling:

Ouch !  Thanks for the heads up and sorry for the noise.

I'm still trying to coax 3 customers off centos5.x, so the 2 customers left
running centos6.5 weren't on any of my mental lists..

Justin