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

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?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

> > 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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