Re: Inserts restricted to a trigger

On 6/18/19 10:14 AM, Miles Elam wrote: Thanks for the suggestion. Unfortunately we only have a single login role (it's a web app) and then we SET ROLE according to the contents of a JSON Web Token. So we end up with SESSION_USER as the logged in user and the active role as CURRENT_USER. Have

Re: psql UPDATE field [tab] expands to DEFAULT?

On 6/18/19 3:23 PM, Ken Tanzer wrote: On Mon, Jun 17, 2019 at 4:24 PM Adrian Klaver > wrote: My cheat for dealing with many/long column names is: test=# \d up_test                Table "public.up_test"   Column |  Type   | Collation | Nullabl

Re: psql UPDATE field [tab] expands to DEFAULT?

On Mon, Jun 17, 2019 at 4:24 PM Adrian Klaver wrote: > > My cheat for dealing with many/long column names is: > > test=# \d up_test >Table "public.up_test" > Column | Type | Collation | Nullable | Default > +-+---+--+- > id | inte

Re: delete inside for plpgsql loop on same relation?

Rob Nikander writes: > Are there guarantees about how this plpgsql behaves? It’s deleting from a > table while it loops over it. So far it seems like the delete is logically > after the select, as I hoped, and doesn’t interfere. > for row in select * from some_stuff loop > delete f

Re: perf tuning for 28 cores and 252GB RAM

Hi, On 2019-06-18 17:13:20 +0100, Fabio Ugo Venchiarutti wrote: > Does the backend mmap() data files when that's possible? No. That doesn't allow us to control when data is written back to disk, which is crucial for durability/consistency. > I've heard the "use the page cache" suggestion before

Re: perf tuning for 28 cores and 252GB RAM

Thanks to everyone for all the detailed responses so far. The dataset we're working with, in its original form, is roughly a terabyte; I would guess in the database it will come out to about 2TB-ish. It will definitely not fit in RAM, unfortunately, and our access patterns will be quite random, at

delete inside for plpgsql loop on same relation?

Hi, Are there guarantees about how this plpgsql behaves? It’s deleting from a table while it loops over it. So far it seems like the delete is logically after the select, as I hoped, and doesn’t interfere. for row in select * from some_stuff loop delete from some_stuff where …

Re: Is array_append O(n)?

Rob Nikander writes: > On Jun 18, 2019, at 7:37 PM, Alvaro Herrera wrote: >> On 2019-Jun-18, Rob Nikander wrote: >>> Does `array_append(arr, elt)` create a new array and copy everything? >>> In other words, is it O(n) or O(1)? […] >> Starting with 9.5, there's an optimization that I think is sup

Re: Inserts restricted to a trigger

Thanks for the suggestion. Unfortunately we only have a single login role (it's a web app) and then we SET ROLE according to the contents of a JSON Web Token. So we end up with SESSION_USER as the logged in user and the active role as CURRENT_USER. It may be that we're just stuck with a gap and ne

Re: perf tuning for 28 cores and 252GB RAM

Hi, On 2019-06-18 12:33:30 -0400, Alvaro Herrera wrote: > On 2019-Jun-18, Andres Freund wrote: > > > On 2019-06-17 19:45:41 -0400, Jeff Janes wrote: > > > If not, I would set the value small (say, 8GB) and let the OS do the > > > heavy lifting of deciding what to keep in cache. > > > > FWIW, in

Re: Is array_append O(n)?

> On Jun 18, 2019, at 7:37 PM, Alvaro Herrera wrote: > > On 2019-Jun-18, Rob Nikander wrote: > >> Does `array_append(arr, elt)` create a new array and copy everything? >> In other words, is it O(n) or O(1)? […] > > Starting with 9.5, there's an optimization that I think is supposed to > make

Re: Inserts restricted to a trigger

Have you tried session_user? create function xx() returns table (cur text, sess text) security definer language sql as $$ select current_user::text, session_user::text; $$; Then log in as different user and: => select (xx()).*; cur| sess --+--- postgres | write On Tue,

Re: Is array_append O(n)?

On 2019-Jun-18, Rob Nikander wrote: > Does `array_append(arr, elt)` create a new array and copy everything? > In other words, is it O(n) or O(1)? I’m trying to use plpgsql and > realizing I don’t really have my usual data structures for basic > algorithms the way I’d normally write them. I probabl

Re: perf tuning for 28 cores and 252GB RAM

Hello On 2019-Jun-18, Andres Freund wrote: > On 2019-06-17 19:45:41 -0400, Jeff Janes wrote: > > If not, I would set the value small (say, 8GB) and let the OS do the > > heavy lifting of deciding what to keep in cache. > > FWIW, in my opinion this is not a good idea in most cases. E.g. linux's >

RE: Connection refused (0x0000274D/10061)

From: Sourav Majumdar [mailto:souravmajumdar2...@gmail.com] Sent: Tuesday, June 18, 2019 4:06 AM To: Adrian Klaver Cc: Ray O'Donnell ; pgsql-gene...@postgresql.org Subject: Re: Connection refused (0x274D/10061) Hii, I have checked the logged file. I am attaching it with this mail. PFA On Fr

Re: Inserts restricted to a trigger

That seems straightforward. Unfortunately I also want to know the user/role that performed the operation. If I use SECURITY DEFINER, I get the superuser account back from CURRENT_USER, not the actual user. Sorry, should have included that in the original email. How do I restrict access while still

Is array_append O(n)?

Hi, Does `array_append(arr, elt)` create a new array and copy everything? In other words, is it O(n) or O(1)? I’m trying to use plpgsql and realizing I don’t really have my usual data structures for basic algorithms the way I’d normally write them. I probably shouldn’t be treating arrays like J

Re: perf tuning for 28 cores and 252GB RAM

Hi, On 2019-06-17 19:45:41 -0400, Jeff Janes wrote: > If not, I would set the value small (say, 8GB) and let the OS do the > heavy lifting of deciding what to keep in cache. FWIW, in my opinion this is not a good idea in most cases. E.g. linux's pagecache doesn't scale particularly gracefully to

Re: perf tuning for 28 cores and 252GB RAM

On 18/06/2019 00:45, Jeff Janes wrote: On Mon, Jun 17, 2019 at 4:51 PM Michael Curry > wrote: I am using a Postgres instance in an HPC cluster, where they have generously given me an entire node. This means I have 28 cores and 252GB RAM. I have to assume that

Re: perf tuning for 28 cores and 252GB RAM

On Mon, Jun 17, 2019 at 6:46 PM Jeff Janes wrote: > > On Mon, Jun 17, 2019 at 4:51 PM Michael Curry wrote: >> >> I am using a Postgres instance in an HPC cluster, where they have generously >> given me an entire node. This means I have 28 cores and 252GB RAM. I have to >> assume that the very c

Re: perf tuning for 28 cores and 252GB RAM

> > If your entire database can comfortably fit in RAM, I would make > shared_buffers large enough to hold the entire database. If not, I would > set the value small (say, 8GB) and let the OS do the heavy lifting of > deciding what to keep in cache. If you go with the first option, you > probably

Re: Connection refused (0x0000274D/10061)

Il 18/06/19 15:57, Adrian Klaver ha scritto: On 6/18/19 1:06 AM, Sourav Majumdar wrote: Hii, I have checked the logged file. I am attaching it with this mail. PFA In future please do not attach a 1.4MB file. Most of it was Unpacking info. The relevant part was at the end: " fixing permissio

Re: Connection refused (0x0000274D/10061)

On 6/18/19 1:06 AM, Sourav Majumdar wrote: Hii, I have checked the logged file. I am attaching it with this mail. PFA In future please do not attach a 1.4MB file. Most of it was Unpacking info. The relevant part was at the end: " fixing permissions on existing directory C:/PostgreSQL/data ..

Re: How to return ARRAY from SQL function?

Alexander Farber writes: > And then I shuffle the letters by - > CREATE OR REPLACE FUNCTION words_shuffle(in_array text[]) > RETURNS text[] AS > $func$ > SELECT array_agg(x ORDER BY RANDOM()) FROM UNNEST(in_array) x; > $func$ LANGUAGE sql STABLE; Hmm ... that's not really "stable

Re: How to return ARRAY from SQL function?

Thank you, Laurenz and Tom - On Fri, Jun 14, 2019 at 3:25 PM Tom Lane wrote: > > Laurenz Albe writes: > > > You'll have to specify an array of which type you want, probably > > ... RETURNS text[] > > Right. Also, I don't recall the exact rules in this area, but I think > that SQL functions are

Re: Copy Bulk Ignore Duplicated

On 2019-06-17 16:01:57 -0300, Leandro Guimarães wrote: > I've installed all dependencies, but when I try to "make install" in > pg_bulkload folder if have some errors like this: > > In file included from pgut/pgut.h:24:0, >                  from pgut/pgut-fe.h:13, >                  from pg_bulklo