Different memory allocation strategy in Postgres 11?

I have a Postgres instance running on my Windows laptop for testing purposes. I typically configure "shared_buffers = 4096MB" on my 16GB system as sometimes when testing, it pays off to have a bigger cache. With Postgres 10 and earlier, the Postgres process(es) would only allocate that

Re: ERROR: type "myrowtype" does not exist

On 10/26/18 4:54 AM, Arup Rakshit wrote: I was reading the json_populate_record function from the official doc https://www.postgresql.org/docs/10/static/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE. The doc mentioned examples are giving me errors psql (11.0, server 10.5) Type "help"

Re: Shell Command within function

> "Mike" == Mike Martin writes: Mike> Is this possible? In an untrusted pl language, yes, but it's a bad idea because: Mike> I have a script which imports csvlogs into a table, and it would Mike> be useful to truncate the log files after import If you had an error after truncating the

Re: Should pg 11 use a lot more memory building an spgist index?

Alvaro Herrera writes: > On 2018-Oct-26, Tom Lane wrote: >> After a quick look around, I think that making systable_begin/endscan >> do this is a nonstarter; there are just too many call sites that would >> be affected. Now, you could imagine specifying that indexes on system >> catalogs (in

Re: Shell Command within function

On 26 October 2018 13:22:19 WEST, Mike Martin wrote: >Is this possible? >I have a script which imports csvlogs into a table, and it would be >useful >to truncate the log files after import > You can use an untrusted language (pl/perlu, pl/sh, ...). Regards, Andreas -- 2ndQuadrant - The

Re: Should pg 11 use a lot more memory building an spgist index?

Bruno Wolff III writes: > Tom Lane wrote: >> Hmm, in my hands this produces the same size leak (~28GB) in either v10 >> or v11. In HEAD, somebody's made it even worse (~43GB). So this is >> certainly pretty broken, but I'm not sure why it seems worse to you in >> v11 than before. > As a

Re: Should pg 11 use a lot more memory building an spgist index?

On Fri, Oct 26, 2018 at 13:44:07 +0100, Tom Lane wrote: Bruno Wolff III writes: As a short term work around, could I create the index first and use insert statements, each in their own transaction, to get the table loaded with the index? Yes; it might also be that you don't even need to

Re: Different memory allocation strategy in Postgres 11?

On Fri, Oct 26, 2018 at 9:12 AM Thomas Kellerer wrote: > I have a Postgres instance running on my Windows laptop for testing > purposes. > > I typically configure "shared_buffers = 4096MB" on my 16GB system as > sometimes when testing, it pays off to have a bigger cache. > > With Postgres 10 and

Shell Command within function

Is this possible? I have a script which imports csvlogs into a table, and it would be useful to truncate the log files after import thanks Mike

Re: Should pg 11 use a lot more memory building an spgist index?

On 2018-Oct-26, Tom Lane wrote: > After a quick look around, I think that making systable_begin/endscan > do this is a nonstarter; there are just too many call sites that would > be affected. Now, you could imagine specifying that indexes on system > catalogs (in practice, only btree) have to

Re: GIN Index for low cardinality

Jeff, Great info! Your example on Mr. Mrs. Miss, etc. is there a good rule of thumb that if the data is under "x"KB an index is overhead not help? I am not worried about space, more interested in performance.

RE: GIN Index for low cardinality

From: Ozz Nixon Sent: Friday, October 26, 2018 12:50 PM To: jeff.ja...@gmail.com Cc: spl...@ya.ru; srkrish...@aol.com; pgsql-general@lists.postgresql.org Subject: Re: GIN Index for low cardinality Jeff, Great info! Your example on Mr. Mrs. Miss, etc. is there a good rule of thumb that if

ERROR: type "myrowtype" does not exist

I was reading the json_populate_record function from the official doc https://www.postgresql.org/docs/10/static/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE . The doc mentioned examples are

Re: GIN Index for low cardinality

On Thu, Oct 25, 2018 at 9:36 AM Олег Самойлов wrote: > > 17 окт. 2018 г., в 13:46, Ravi Krishna написал(а): > > In > https://www.cybertec-postgresql.com/en/ideas-for-scaling-postgresql-to-multi-terabyte-and-beyond/ > > it is mentioned: > > "GIN, the most know non-default index type perhaps, has

How to get partition info for a partition table?

HI Dear PostgreSQL, I am pretty new for this DB. I have created two partition tables as bellow: *CREATE TABLE* measurement_year_month ( logdate date not null, peaktemp int, unitsales int ) PARTITION BY RANGE (EXTRACT(YEAR FROM logdate), EXTRACT(MONTH FROM logdate)); *create table*

Re: GIN Index for low cardinality

On Wed, Oct 17, 2018 at 6:47 AM Ravi Krishna wrote: > In > https://www.cybertec-postgresql.com/en/ideas-for-scaling-postgresql-to-multi-terabyte-and-beyond/ > > it is mentioned: > > "GIN, the most know non-default index type perhaps, has been actually > around for ages (full-text search) and in

Re: Different memory allocation strategy in Postgres 11?

Jeff Janes schrieb am 26.10.2018 um 17:42: I typically configure "shared_buffers = 4096MB" on my 16GB system as sometimes when testing, it pays off to have a bigger cache. With Postgres 10 and earlier, the Postgres process(es) would only allocate that memory from the operating system

Question about partition table

HI Dear PostgreSQL team, I have created a partition table as bellow: *CREATE TABLE* measurement_year_month ( logdate date not null, peaktemp int, unitsales int ) PARTITION BY RANGE (EXTRACT(YEAR FROM logdate), EXTRACT(MONTH FROM logdate)); so the content for this column *partexprs*

Re: Different memory allocation strategy in Postgres 11?

On Sat, Oct 27, 2018 at 6:10 AM Thomas Kellerer wrote: > Jeff Janes schrieb am 26.10.2018 um 17:42: > > I typically configure "shared_buffers = 4096MB" on my 16GB system as > > sometimes when testing, it pays off to have a bigger cache. > > > > With Postgres 10 and earlier, the Postgres

Re: rw_redis_fdw: SQL Errors when statement is within a function

## GPT (gptmailingli...@gmail.com): > I have searched in > https://github.com/nahanni/rw_redis_fdw/blob/master/redis_fdw.c for > PREPARE and EXECUTE keywords. There are not any of them, except in > comments. Of course not - the FDW does not execute SQL on the PostgreSQL side, but sends commands

Re: GIN Index for low cardinality

On Fri, Oct 26, 2018 at 12:27 PM Jeff Janes wrote: Here is a real-world example from one of my databases where each value is > about 17 characters long, and is present about 20 times: > > gin: 411 MB > btree: 2167 MB > hash: 2159 MB > For what it is worth, that was 9.6 with freshly rebuilt

Re: Postgresql issue: FATAL: dsa_allocate could not find 7 free pages

Thanks for your response ! I’ll try next week to build a test environment and I’ll send you the results. Does it make any difference to set up a VM vs a dedicated machine ? Thanks for your help ! > Le 26 oct. 2018 à 00:58, Thomas Munro a écrit > : > > On Fri, Oct 26, 2018 at 2:21 AM Alexandre

Re: rw_redis_fdw: SQL Errors when statement is within a function

I have searched in https://github.com/nahanni/rw_redis_fdw/blob/master/redis_fdw.c for PREPARE and EXECUTE keywords. There are not any of them, except in comments. So, the developer doesn´t use any PREPARE, EXECUTE statements. So, this change occurs internally. If I am correct then the PG fails

Re: Should pg 11 use a lot more memory building an spgist index?

On 2018/10/26 18:59, Tom Lane wrote: > Amit Langote writes: >> On 2018/10/26 18:16, Tom Lane wrote: >>> A quick review of the other index AM endscan methods seems to indicate >>> that they all try to clean up their mess. So maybe we should just make >>> spgendscan do likewise. Alternatively, we

Re: Should pg 11 use a lot more memory building an spgist index?

Bruno Wolff III writes: > I have something that seems to produce it on rhel7. Fedora isn't working > well either, but the difference may be due to postgresql.conf being > different or some difference in the Fedora build. Hmm, in my hands this produces the same size leak (~28GB) in either v10

Re: Postgresql issue: FATAL: dsa_allocate could not find 7 free pages

On Fri, Oct 26, 2018 at 9:17 PM Alexandre Assouad wrote: > I’ll try next week to build a test environment and I’ll send you the results. > Does it make any difference to set up a VM vs a dedicated machine ? Thanks. Probably not, the important thing is probably the same data, settings (work_mem

Re: Should pg 11 use a lot more memory building an spgist index?

On 2018/10/26 18:16, Tom Lane wrote: > The core of the problem I see is that check_exclusion_or_unique_constraint > does index_beginscan/index_rescan/index_endscan in a long-lived context, > while spgendscan seems to have employed dice while deciding which of > spgbeginscan's allocations it would

Re: Should pg 11 use a lot more memory building an spgist index?

Amit Langote writes: > On 2018/10/26 18:16, Tom Lane wrote: >> A quick review of the other index AM endscan methods seems to indicate >> that they all try to clean up their mess. So maybe we should just make >> spgendscan do likewise. Alternatively, we could decide that requiring >> endscan

Re: Should pg 11 use a lot more memory building an spgist index?

Amit Langote writes: > On 2018/10/26 18:59, Tom Lane wrote: >> After a quick look around, I think that making systable_begin/endscan >> do this is a nonstarter; there are just too many call sites that would >> be affected. Now, you could imagine specifying that indexes on system >> catalogs (in

Re: Strange behavior of the random() function

> 26 сент. 2018 г., в 6:35, Tom Lane написал(а): > > r.zhar...@postgrespro.ru writes: >> Can anybody explain me that strange behavior? > > It's a squishiness in the SQL language, if you ask me. Consider this > simplified query: > > select random() from generate_series(1, 3) order by

Re: Should pg 11 use a lot more memory building an spgist index?

On Fri, Oct 26, 2018 at 10:16:09 +0100, Tom Lane wrote: Bruno Wolff III writes: I have something that seems to produce it on rhel7. Fedora isn't working well either, but the difference may be due to postgresql.conf being different or some difference in the Fedora build. Hmm, in my hands