Will there ever be support for Row Level Security on Materialized Views?

2018-08-14 Thread Ken Tanzer
Hi. My question is similar to one that was asked but apparently never answered a couple of years ago on this list. ( https://www.postgresql.org/message-id/20160112023419.GA30965%40moraine.isi.edu ) Basically, I'm wondering whether materialized views are likely to ever support row-level security.

Re: Immutable function WAY slower than Stable function?

2018-08-14 Thread Michal
On 2018.08.07 18:10, Tom Lane wrote: I've had a to-do item to rewrite and improve the SQL function cache mechanism for a long time, but I hadn't thought it was high priority. Maybe it should be. In the meantime, I think you could dodge the issue by converting either level of function into plpgsq

Re: How to revoke privileged from PostgreSQL's superuser

2018-08-14 Thread Bruce Momjian
On Fri, Aug 10, 2018 at 04:06:40PM -0400, Benedict Holland wrote: > I also would take Bruce's comment with a massive grain of salt. Everything > that > everyone does on a database is logged somewhere assuming proper logging. Now > do > you have the person-power to go through gigs of plain text lo

Re: Duplicating data folder without tablespace, for read access

2018-08-14 Thread Jack Cushman
Thanks for such quick and helpful answers! My plan sounds probably better to avoid, but if it turns out to be necessary, you all gave me some helpful avenues and things to look out for. Best, Jack On Tue, Aug 14, 2018 at 1:06 PM, Stephen Frost wrote: > Greetings, > > * Jack Cushman (jcush...@gm

Re: How to revoke privileged from PostgreSQL's superuser

2018-08-14 Thread Bruce Momjian
On Fri, Aug 10, 2018 at 10:34:26PM -0400, Rui DeSousa wrote: > With that logic then you should use flat files for encrypted data and > unencrypted data. It’s what was done many moons ago; and its unstructured > haphazard approach gave rise to RDBMS systems. > > You cannot say that encrypted data

Re: Best Practices for Extensions, limitations and recommended use for monitoring

2018-08-14 Thread Alvar Freude
Hi Tom, > Am 14.08.2018 um 17:07 schrieb Tom Lane : > > I'd say that's generally deprecated. Per the documentation, you can write > CREATE USER commands in an extension script if you like, but the roles > won't be considered to "belong" to the extension, and won't be dropped > when it is. This

Re: upgrading from pg 9.3 to 10

2018-08-14 Thread Martín Marqués
El 14/08/18 a las 14:44, Edmundo Robles escribió: > Is safe  to upgrade from pg 9.3 to pg 10 directly using pg_upgrade or > is better upgrade, with pg_upgrade,  from  9.3 -> 9.4 ->9.5 -> 9.6 -> 10. That is quiet a jump. But not imposible with pg_upgrade. I'd recommend testing with a clone of the

Re: pg_basebackup failed to read a file

2018-08-14 Thread Joshua D. Drake
On 08/14/2018 09:14 AM, Tom Lane wrote: Mike Cardwell writes: It'd be nice to have a more coherent theory about what needs to be copied or not, and not fail on files that could simply be ignored. Up to now we've resisted having any centrally defined knowledge of what can be inside a PG data di

Re: Duplicating data folder without tablespace, for read access

2018-08-14 Thread Peter J. Holzer
On 2018-08-14 11:57:38 -0400, Jack Cushman wrote: > I have a large database of text, with a 600GB table and a 100GB table > connected > by a join table. They both see occasional updates throughout the week. Once a > week I want to "cut a release," meaning I will clone just the 100GB table and > co

Re: pg_basebackup failed to read a file

2018-08-14 Thread Stephen Frost
Greetings, * Ron (ronljohnso...@gmail.com) wrote: > On 08/14/2018 11:14 AM, Tom Lane wrote: > >Mike Cardwell writes: > >>pg_basebackup: could not get write-ahead log end position from server: > >>ERROR:  could not open file "./postgresql.conf~": Permission denied > >>Now, I know what this error m

Re: pg_upgrade with large pg_largeobject table

2018-08-14 Thread Tom Lane
Mate Varga writes: >> Using the large-object API for things that tend to not actually be very >> large (which they aren't, if you've got hundreds of millions of 'em) is an >> antipattern, I'm afraid. > I know :( So maybe I need to do some refactoring in the application and > inline the lobs. The

Re: pg_upgrade with large pg_largeobject table

2018-08-14 Thread Mate Varga
Thanks. > You mean 250M rows in pg_largeobject itself, or 250M large objects (that is, 250M rows in pg_largeobject_metadata)? 250M large objects. > Are you sure you're using a 64-bit build of pg_dump? file /usr/lib/postgresql/10/bin/pg_dump /usr/lib/postgresql/10/bin/pg_dump: ELF 64-bit LSB s

Re: Duplicating data folder without tablespace, for read access

2018-08-14 Thread Jerry Sievers
Tom Lane writes: > Jack Cushman writes: > >> Now here's the situation where I want to do what Christophe said not to do: >> :) > >> I have a large database of text, with a 600GB table and a 100GB table >> connected by a join table. They both see occasional updates throughout the >> week. Once a

Re: pg_upgrade with large pg_largeobject table

2018-08-14 Thread Tom Lane
Mate Varga writes: > We have a PSQL 9.5 DB with 16G physical RAM and ~ 1 TB data mostly stored > in the pg_largeobject system table. This table has 250M rows at the moment. You mean 250M rows in pg_largeobject itself, or 250M large objects (that is, 250M rows in pg_largeobject_metadata)? > This

upgrading from pg 9.3 to 10

2018-08-14 Thread Edmundo Robles
Is safe to upgrade from pg 9.3 to pg 10 directly using pg_upgrade or is better upgrade, with pg_upgrade, from 9.3 -> 9.4 ->9.5 -> 9.6 -> 10. --

pg_upgrade with large pg_largeobject table

2018-08-14 Thread Mate Varga
Hi. hanks in advance for any advice. We have a PSQL 9.5 DB with 16G physical RAM and ~ 1 TB data mostly stored in the pg_largeobject system table. This table has 250M rows at the moment. We're trying to upgrade this to 10.x with an in-place upgrade. The command I'm using is:sudo -u postgres /usr/

Re: pg_basebackup failed to read a file

2018-08-14 Thread Dimitri Maziuk
On 08/14/2018 12:14 PM, Ron wrote: > Why is checking a bunch of file permissions anywhere close to being as > expensive as transferring 1.5TB over a WAN link? Normally it shouldn't be but I recently had postgres create ~13M .snap files and just opendir() took longer than anyone would care to wait

Re: pg_basebackup failed to read a file

2018-08-14 Thread Ron
On 08/14/2018 11:14 AM, Tom Lane wrote: Mike Cardwell writes: pg_basebackup: could not get write-ahead log end position from server: ERROR:  could not open file "./postgresql.conf~": Permission denied Now, I know what this error means. There was a root owned file at "/var/lib/pgsql/10/data/p

Re: Duplicating data folder without tablespace, for read access

2018-08-14 Thread Stephen Frost
Greetings, * Jack Cushman (jcush...@gmail.com) wrote: > I have a large database of text, with a 600GB table and a 100GB table > connected by a join table. They both see occasional updates throughout the > week. Once a week I want to "cut a release," meaning I will clone just the > 100GB table and

Re: Duplicating data folder without tablespace, for read access

2018-08-14 Thread Tom Lane
Jack Cushman writes: > Now here's the situation where I want to do what Christophe said not to do: > :) > I have a large database of text, with a 600GB table and a 100GB table > connected by a join table. They both see occasional updates throughout the > week. Once a week I want to "cut a release

Re: pg_basebackup failed to read a file

2018-08-14 Thread Tom Lane
Mike Cardwell writes: > pg_basebackup: could not get write-ahead log end position from server: > ERROR:  could not open file "./postgresql.conf~": Permission denied > Now, I know what this error means. There was a root owned file at > "/var/lib/pgsql/10/data/postgresql.conf~" which contained an o

Duplicating data folder without tablespace, for read access

2018-08-14 Thread Jack Cushman
Hi -- I'm wondering whether, in my specific situation, it would be safe to copy a database cluster's data folder, and bring up the copy for read access, without copying a tablespace linked from it. My situation (described below) involves a database with a 100GB table and a 600GB table where I want

Re: Best Practices for Extensions, limitations and recommended use for monitoring

2018-08-14 Thread Tom Lane
Alvar Freude writes: > I have a question about best practices writing PostgreSQL extensions. Is it > OK to write extensions which create users and grant/revoke rights on the > created functions to this users? I'd say that's generally deprecated. Per the documentation, you can write CREATE USER

Re: What is the use case for UNLOGGED tables

2018-08-14 Thread Ravi Krishna
> > I use them for "ELT" oriented processing where the final results get stored > on permanently logged tables but I want to manipulate tables while > transforming from the original input. Yeah I see the use case. Basically raw data -> rolled up data -> to final results in normal tables. Howe

Re: What is the use case for UNLOGGED tables

2018-08-14 Thread David G. Johnston
On Tuesday, August 14, 2018, Ravi Krishna wrote: > Then what exactly is the benefit of UNLOGGED tables, unless we have > permanent unlogged tables for disposable > data. > I use them for "ELT" oriented processing where the final results get stored on permanently logged tables but I want to manip

What is the use case for UNLOGGED tables

2018-08-14 Thread Ravi Krishna
I am trying to understand the use case for UNLOGGED tables in PG. I am specifically talking about normal tables which need to be turned into UNLOGGED for a specific purpose like bulk loading because generating WAL logs during the load makes no sense, even when we take into consideration that

Re: Copying data from a CSV file into a table dynamically

2018-08-14 Thread David G. Johnston
On Tuesday, August 14, 2018, Ruiqiang Chen wrote: > CSV file has no limitation of # of rows, excel max row is 2^20. Am I > correct? > It's considered bad form to hijack threads in this manner. Text files have no inherent limits. You can check the Excel documentation for the version you care ab

pg_basebackup failed to read a file

2018-08-14 Thread Mike Cardwell
Hi, I was just setting up streaming replication for the first time. I ran pg_basebackup on the slave. It copied 1.5TB of data. Then it errored out with: ``` 1498215035/1498215035 kB (100%), 1/1 tablespace pg_basebackup: could not get write-ahead log end position from server: ERROR:  could not ope

Re: Vacuum process waiting on BufferPin

2018-08-14 Thread Don Seiler
On Tue, Aug 14, 2018 at 8:58 AM, Vik Fearing wrote: > > There is an idle_in_transaction_session_timeout parameter to kill > connections that are idle (in transaction) for too long. It was > implemented specifically for cases like this. Thanks for reminding me of this. I'll probably look to set

Re: Copying data from a CSV file into a table dynamically

2018-08-14 Thread Adrian Klaver
On 08/14/2018 06:38 AM, pavan95 wrote: Hi Adrian, I tried to use *"COPY postgres_log1 FROM '/tmp/abc/xyz/postgresql-`date --date="0 days ago" +%Y-%m-%d`_*.csv' WITH csv;"* But it resulted in an error. How to issue such that it is understandable by psql? And I am completely unaware of python &

Re: Copying data from a CSV file into a table dynamically

2018-08-14 Thread Ruiqiang Chen
CSV file has no limitation of # of rows, excel max row is 2^20. Am I correct? On Tue, Aug 14, 2018 at 9:46 AM, Ron wrote: > On 08/14/2018 08:38 AM, pavan95 wrote: > >> Hi Adrian, >> >> I tried to use >> *"COPY postgres_log1 FROM '/tmp/abc/xyz/postgresql-`date --date="0 days >> ago" >> +%Y-%m-%d`

Re: Vacuum process waiting on BufferPin

2018-08-14 Thread Vik Fearing
On 14/08/18 15:26, Don Seiler wrote: > On Tue, Aug 14, 2018 at 8:23 AM, Vick Khera > wrote: > > > The general fix is to never sit idle in transaction, but this > specific case closing the cursor seems like it will also do it. > > > Of course. I've let developmen

Re: Copying data from a CSV file into a table dynamically

2018-08-14 Thread Ron
On 08/14/2018 08:38 AM, pavan95 wrote: Hi Adrian, I tried to use *"COPY postgres_log1 FROM '/tmp/abc/xyz/postgresql-`date --date="0 days ago" +%Y-%m-%d`_*.csv' WITH csv;"* But it resulted in an error. How to issue such that it is understandable by psql? And I am completely unaware of python &

Re: Copying data from a CSV file into a table dynamically

2018-08-14 Thread pavan95
Hi Adrian, I tried to use *"COPY postgres_log1 FROM '/tmp/abc/xyz/postgresql-`date --date="0 days ago" +%Y-%m-%d`_*.csv' WITH csv;"* But it resulted in an error. How to issue such that it is understandable by psql? And I am completely unaware of python & psycopg2. Anything which suits my requir

Re: Vacuum process waiting on BufferPin

2018-08-14 Thread Don Seiler
On Tue, Aug 14, 2018 at 8:23 AM, Vick Khera wrote: > > > The general fix is to never sit idle in transaction, but this specific > case closing the cursor seems like it will also do it. > Of course. I've let development know that they need to sort out why it's left that way. But if they're also NO

Re: Vacuum process waiting on BufferPin

2018-08-14 Thread Vick Khera
On Tue, Aug 14, 2018 at 9:21 AM, Don Seiler wrote: > On Mon, Aug 13, 2018 at 5:55 PM, Vick Khera wrote: > >> On Mon, Aug 13, 2018 at 5:19 PM, Don Seiler wrote: >> >>> >>> I don't quite follow this. What circumstances would lead to this >>> situation? >>> >> >> BEGIN WORK; >> DECLARE CURSOR ...

Re: Vacuum process waiting on BufferPin

2018-08-14 Thread Don Seiler
On Mon, Aug 13, 2018 at 5:55 PM, Vick Khera wrote: > On Mon, Aug 13, 2018 at 5:19 PM, Don Seiler wrote: > >> >> I don't quite follow this. What circumstances would lead to this >> situation? >> > > BEGIN WORK; > DECLARE CURSOR ... ; > FETCH ...; -- for some number of fetches, which does not rea

Re: Copying data from a CSV file into a table dynamically

2018-08-14 Thread Adrian Klaver
On 08/14/2018 06:10 AM, pavan95 wrote: Hi all, I am well versed with the COPY command for copying the contents of a csv file into a table. I am used to the below mentioned command: * COPY postgres_log1 FROM '/tmp/abc/xyz/postgresql-2018-08-14_00.csv' WITH csv;* But in the location "/tmp/ab

Re: Copying data from a CSV file into a table dynamically

2018-08-14 Thread pavan95
Hi all, I am well versed with the COPY command for copying the contents of a csv file into a table. I am used to the below mentioned command: * COPY postgres_log1 FROM '/tmp/abc/xyz/postgresql-2018-08-14_00.csv' WITH csv;* But in the location "/tmp/abc/xyz/" daily a file(with header postgres

Re: Uncaught PHP Exception Doctrine\DBAL\Exception\UniqueConstraintViolationException: "An exception occurred while executing 'UPDATE

2018-08-14 Thread rob stone
Hello, On Tue, 2018-08-14 at 07:48 +, Jarosław Torbicki wrote: > Hello, > I used PostgreSQL 9.3 but I executed upgrade few days ago. > Now, I am using 10.4 PostgreSQL and: > doctrine/annotations v1.2.7 > doctrine/cache v1.4.2

Uncaught PHP Exception Doctrine\DBAL\Exception\UniqueConstraintViolationException: "An exception occurred while executing 'UPDATE

2018-08-14 Thread Jarosław Torbicki
Hello, I used PostgreSQL 9.3 but I executed upgrade few days ago. Now, I am using 10.4 PostgreSQL and: doctrine/annotations v1.2.7 doctrine/cache v1.4.2 doctrine/collections v1.3.0 doctrine/common v2.7.3 doctrine/dbal

Best Practices for Extensions, limitations and recommended use for monitoring

2018-08-14 Thread Alvar Freude
Hi all, I have a question about best practices writing PostgreSQL extensions. Is it OK to write extensions which create users and grant/revoke rights on the created functions to this users? Is it possible to add options to CREATE EXTENSION by the extension itself e.g. to make user names configu

Re: 'Identifier' columns

2018-08-14 Thread Laurenz Albe
David Favro wrote: > A couple of questions about auto-assigned identifier columns, > forgive my ignorance, I'm used to other methods to create IDs... > > 1. If creating a new application [i.e. no "legacy" reasons to do > anything] using PostgreSQL 10, when creating an "auto-assigned > integer I