Re: Immutable function WAY slower than Stable function?

Ken Tanzer wrote: > On Mon, Aug 6, 2018 at 4:11 PM Tom Lane wrote: > > Ken Tanzer writes: > > > Hi. I was recently troubleshooting a function, and realized it had > > > incorrectly been declared as Immutable, when it should have been declared > > > Stable. When I changed it to Stable, the query

Re: Immutable function WAY slower than Stable function?

On Mon, Aug 6, 2018 at 7:42 PM Tom Lane wrote: > Ken Tanzer writes: > > Hi Adrian. Happy to provide this info. Though on a side note, I don't > > understand why it should matter, if functions are black box optimization > > fences. > > They aren't, at least not when they are SQL-language functi

Re: Immutable function WAY slower than Stable function?

Ken Tanzer writes: > Hi Adrian. Happy to provide this info. Though on a side note, I don't > understand why it should matter, if functions are black box optimization > fences. They aren't, at least not when they are SQL-language functions that meet the conditions for inlining. The reason that

Re: Re: How to revoke privileged from PostgreSQL's superuser

Very true - when you've been hitting credit card and health care nails you tend to forget that not every problem requires the same level of hammer! Ask me what's required for anything in the Hadoop ecosystem. shudder. However it's also true that there's no such thing as a site or database too smal

Re: Immutable function WAY slower than Stable function?

On 08/06/2018 04:44 PM, Ken Tanzer wrote: On Mon, Aug 6, 2018 at 4:36 PM Adrian Klaver > wrote: What is the definition for target_date()? Hi Adrian.  Happy to provide this info.  Though on a side note, I don't understand why it should matter, if function

Re: Immutable function WAY slower than Stable function?

On Mon, Aug 6, 2018 at 4:36 PM Adrian Klaver wrote: > > What is the definition for target_date()? > Hi Adrian. Happy to provide this info. Though on a side note, I don't understand why it should matter, if functions are black box optimization fences. But here are the definitions: CREATE OR R

Re: Immutable function WAY slower than Stable function?

On 08/06/2018 03:49 PM, Ken Tanzer wrote: Hi.  I was recently troubleshooting a function, and realized it had incorrectly been declared as Immutable, when it should have been declared Stable.  When I changed it to Stable, the query I was running ran dramatically faster. Digging into this a litt

Re: Immutable function WAY slower than Stable function?

On Mon, Aug 6, 2018 at 4:11 PM Tom Lane wrote: > Ken Tanzer writes: > > Hi. I was recently troubleshooting a function, and realized it had > > incorrectly been declared as Immutable, when it should have been declared > > Stable. When I changed it to Stable, the query I was running ran > > dram

Re: Immutable function WAY slower than Stable function?

Ken Tanzer writes: > Hi. I was recently troubleshooting a function, and realized it had > incorrectly been declared as Immutable, when it should have been declared > Stable. When I changed it to Stable, the query I was running ran > dramatically faster. Digging into this a little more, this is w

Immutable function WAY slower than Stable function?

Hi. I was recently troubleshooting a function, and realized it had incorrectly been declared as Immutable, when it should have been declared Stable. When I changed it to Stable, the query I was running ran dramatically faster. Digging into this a little more, this is what I found: I've got a fun

Re: Re: How to revoke privileged from PostgreSQL's superuser

Hi all, Thanks for giving a lot of points of view. I know superuser can not be revoked apart of privileges, because it does not like nosuperusers who's privileges can be made from GRANT statement. As you all mentioned, I will re-check more about our system designation. I am inclined to encry

Re: How to revoke privileged from PostgreSQL's superuser

bejita0...@yahoo.co.jp writes: > Hello, > > I am a newbie DBA. > > I have a request for revoking the access to user's data from DBA-user. > I think the request is right because users should be the only ones can access > their data. > But DBA-user also need full access to the other data? It mean

Re: PANIC: could not open critical system index 2662

Just curious, why can't you restore the db from the backup ?

Re: PANIC: could not open critical system index 2662

On Sat, Aug 4, 2018 at 11:13 AM, Tom Lane wrote: > C GG writes: > > When trying to restart the PostgreSQL 9.2 database for our JIRA > > installation, we're getting > > "PANIC: could not open critical system index 2662" ... I've tried > various > > things like turning on zero_damaged_pages but p

Re: Who and How is responsible for released installations packages and 3rd party packs? (e.g. on https://yum.postgresql.org/9.6/redhat/rhel-7.3-x86_64/)

Hi, On Mon, 2018-08-06 at 23:49 +0300, Alexandru Lazarev wrote: > Let me ask other dummy question: > plv8 RPMs were built by PostgreSQL Community for different OSes, or by > those OSes vendors/community (e.f. RedHat/Debian, etc)? > And the same question about postgresql-server install packages t

Re: Who and How is responsible for released installations packages and 3rd party packs? (e.g. on https://yum.postgresql.org/9.6/redhat/rhel-7.3-x86_64/)

On 08/06/2018 03:49 PM, Alexandru Lazarev wrote: > And the same question about postgresql-server install packages themselves > (RPMs, debs, etc) For this one, packages you get from "PGDG"@ https://www.postgresql.org/download/ are built by postgres. RedHat, for example, also has postgres built by

Re: Who and How is responsible for released installations packages and 3rd party packs? (e.g. on https://yum.postgresql.org/9.6/redhat/rhel-7.3-x86_64/)

Thanks all for responses. Let me ask other dummy question: plv8 RPMs were built by PostgreSQL Community for different OSes, or by those OSes vendors/community (e.f. RedHat/Debian, etc)? And the same question about postgresql-server install packages themselves (RPMs, debs, etc) Thanks in advance

Re: Who and How is responsible for released installations packages and 3rd party packs? (e.g. on https://yum.postgresql.org/9.6/redhat/rhel-7.3-x86_64/)

Thanks all for responses. Let me ask other dummy question: plv8 RPMs were built by PostgreSQL Community for different OSes, or by those OSes vendors/community (e.f. RedHat/Debian, etc)? And the same question about postgresql-server install packages themselves (RPMs, debs, etc) Thanks in advance

Re: How to revoke privileged from PostgreSQL's superuser

Bejita, I suggest you step back and think about the problem from the point of view of the desired security outcome — that of protecting data from improper use by administrators. Some of the elements that (to my mind) ought to be part of achieving that outcome are: 1. Determine and document yo

Re: How to revoke privileged from PostgreSQL's superuser

I should clarify something I said above - I know that some data must be encrypted within the database. My point was that during the design phase you should take the schema, cross out all columns that will be encrypted, and ask what's left. If it's nothing but the primary key and things you'll never

Re: How to revoke privileged from PostgreSQL's superuser

Thanks, I didn't realize that this was handled by a flag instead of just the standard permissions. On Mon, Aug 6, 2018 at 7:43 AM, Tom Lane wrote: > Bear Giles writes: > > In postgresql the equivalent user is 'postgres'. Nobody should ever be > > logged in as that user once you've created the i

Re: How to revoke privileged from PostgreSQL's superuser

Encrypting data within the database(*) severely limits its usability - you can't use it in queries, etc. In some cases it's not a problem since you'll never want to use it in a query anyway, or you can use a proxy(**). But as a general rule I think if you're encrypting much of your data then a trad

Re: How to revoke privileged from PostgreSQL's superuser

Bear Giles writes: > In postgresql the equivalent user is 'postgres'. Nobody should ever be > logged in as that user once you've created the initial user(s). What > postgresql calls a 'superuser' is just a user with a few permissions set by > default. It's easy to grant the same privileges to any

Re: How to revoke privileged from PostgreSQL's superuser

I think you may be conflating two things. The "superuser" (or root) in an operating system is a special user (with uid = 0 in unix/linux systems) that bypasses many checks. Ideally sysadmins and regular users will never log in as root - they'll either use the existing privilege framework (e.g., gr

Re: How to revoke privileged from PostgreSQL's superuser

On Monday, August 6, 2018, wrote: > > I have a request for revoking the access to user's data from DBA-user. > I think the request is right because users should be the only ones can > access their data. > User then needs to encrypt data prior to storing it. Superuser can still access the data b

RE: How to revoke privileged from PostgreSQL's superuser

Hello From: bejita0...@yahoo.co.jp [mailto:bejita0...@yahoo.co.jp] Sent: Montag, 6. August 2018 11:49 To: pgsql-ad...@lists.postgresql.org; pgsql-general@lists.postgresql.org Subject: How to revoke privileged from PostgreSQL's superuser Hello, I am a newbie DBA. I have a request fo

Re: [External] How to revoke privileged from PostgreSQL's superuser

I am not sure superuser can be selectively restricted via queries, but I am not sure, have not tried. But maybe you can try restricting the super user access to the db from all hosts via the pg_hba.conf. Fore eg. I have a user monitor| Superuser and in my /etc/postgresql/10/main/pg

How to revoke privileged from PostgreSQL's superuser

Hello,   I am a newbie DBA.   I have a request for revoking the access to user's data from DBA-user. I think the request is right because users should be the only ones can access their data. But DBA-user also need full access to the other data? It means that DBA-user also needs to be a superuser.

Re: How to avoid dead rows in tables.

On 6 August 2018 09:04:45 CEST, Raghavendra Rao J S V wrote: >Hi All, > > >auto vacuum is enabled in our database. But few of the tables contains >the >dead tuples more than 5,000 records. Number of dead rows are keep on >increasing day by day if we didn’t perform the vacuum full. Monthly >once w

How to avoid dead rows in tables.

Hi All, auto vacuum is enabled in our database. But few of the tables contains the dead tuples more than 5,000 records. Number of dead rows are keep on increasing day by day if we didn’t perform the vacuum full. Monthly once we are performing vacuum full by stopping application server process. La