rw_redis_fdw: SQL Errors when statement is within a function

[GPT]

Hi,

I have faced an incident which, according to the developer, "The
problem seems to be that pgsql switches from a CONST sub-expression
into a FUNCEXPR after a few goes, ..."

Please have a look at the following two links which describe the problem:

https://github.com/nahanni/rw_redis_fdw/issues/13
https://github.com/nahanni/rw_redis_fdw/issues/14

The first link (13) has to do with PG10.x version.
The second one (14) has to do with PG11 version in which the developer
traces the "problem".

Please check and comment.

Tia

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

[Andres Freund]

Hi,

On 2018-10-25 11:43:39 +0200, GPT wrote:
> I have faced an incident which, according to the developer, "The
> problem seems to be that pgsql switches from a CONST sub-expression
> into a FUNCEXPR after a few goes, ..."
> 
> Please have a look at the following two links which describe the problem:
> 
> https://github.com/nahanni/rw_redis_fdw/issues/13
> https://github.com/nahanni/rw_redis_fdw/issues/14
> 
> The first link (13) has to do with PG10.x version.
> The second one (14) has to do with PG11 version in which the developer
> traces the "problem".
> 
> Please check and comment.

The developer of the FDW seems to have made the requisite fixes. I'm not
sure what you'd like us to do?

Greetings,

Andres Freund

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

[Bruno Wolff III]

It looks like it got past creating the exclude constraint based on the 
ordering of commands in the dump file. However creating a more normal 
spgist index is taking a very long time with a lot of disk wait time. 
CPU usage seems pretty low for the amount of time it has been working 
on building that index, but maybe that is normal for building indexes.
I used the almost the default postgresql.conf on my workstation. I bumped 
up the limits in a few places on the server that could have allowed a lot 
more memory to be used especially if the index creation was parallelized. 
While the load is running I'll see if I can tell if there is a memory leak 
with this index build. Once it finishes, I can dump a specific table 
and test building the exclude spgist index with some different settings 
to see if I can reproduce the out of memory error with a lot less data 
then is in the whole database.

Re: Compile and build portable postgresql for mac

[Олег Самойлов]

> 25 окт. 2018 г., в 3:21, Pratik Parikh  написал(а):
> 
> Hi all,
> 
>  How to compile and build portable postgresql for mac.  Can someone point 
> me how the builds are done for general distribution.  A script would be 
> appreciated if anyone has it.  
> 
> Regards,
> Pratik

You can try 
https://postgresapp.com 
I don’t have experience much about this app. Looked like working, but psql 
demonstrate bugs in the text terminal ( iTerm and official Terminal).

Also you can try postgres from brew. It will compiled on your mac in this case. 

Re: Compile and build portable postgresql for mac

[Don Seiler]

On Wed, Oct 24, 2018 at 7:22 PM Pratik Parikh 
wrote:

>  How to compile and build portable postgresql for mac.  Can someone
> point me how the builds are done for general distribution.  A script would
> be appreciated if anyone has it.
>

I'm not sure what you mean by "portable"? I'm going to answer assuming you
just want to run postgres on your own Mac.

There are couple options. Peter Eisentraut has a great collection of
Homebrew scripts that you can plug into:
https://github.com/petere/homebrew-postgresql

I've recently switched to pgenv, which provides a clean CLI for building
and using all the various supported versions:
https://github.com/theory/pgenv

Don.
-- 
Don Seiler
www.seiler.us

Re: GIN Index for low cardinality

[Олег Самойлов]

> 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 been actually around 
> for ages (full-text search) and in short is perfect for indexing columns 
> where there are lot of repeating values – think all kinds of statuses or good 
> old Mr/Mrs/Miss. GIN only stores every unique column value only once as for 
> the default B-tree you’ll have e.g. 1 millon leaf nodes with the integer “1” 
> in it."
> 
> 
> Does it mean that GIN is a very good choice for low cardinality columns.  

Not necessary. There is other index which also don’t keep column value in an 
every leaf. Hash, for instance. 

Re: GIN Index for low cardinality

[Ravi Krishna]

>>Does it mean that GIN is a very good choice for low cardinality columns.  
>Not necessary. There is other index which also don’t keep column value in an 
>every leaf. Hash, for instance. 

Well I asked about GIN's usefulness for low cardinality.  Good to know that 
Hash can also be considered.
BTW until recently wasn't Hash unsafe for production since it was not WAL 
logged.

Re: Compile and build portable postgresql for mac

[Pratik Parikh]

Thanks, I'll check them out.  But what I am trying to produce is a zip
distribution fo Mac is x similar to the one available on postgresql
download site. Homebrew works but it ties the libpg to prefix if the path
where libs are generated. I am trying to do some unit testing across more
then one machine on an ongoing basis.

Hope that helps clear uo what I mean by portable.

On Thu, Oct 25, 2018, 9:31 AM Don Seiler  wrote:

> On Wed, Oct 24, 2018 at 7:22 PM Pratik Parikh 
> wrote:
>
>>  How to compile and build portable postgresql for mac.  Can someone
>> point me how the builds are done for general distribution.  A script would
>> be appreciated if anyone has it.
>>
>
> I'm not sure what you mean by "portable"? I'm going to answer assuming you
> just want to run postgres on your own Mac.
>
> There are couple options. Peter Eisentraut has a great collection of
> Homebrew scripts that you can plug into:
> https://github.com/petere/homebrew-postgresql
>
> I've recently switched to pgenv, which provides a clean CLI for building
> and using all the various supported versions:
> https://github.com/theory/pgenv
>
> Don.
> --
> Don Seiler
> www.seiler.us
>

Re: Compile and build portable postgresql for mac

[Ravi Krishna]

Your best bet in mac is to use docker.

On Thursday, October 25, 2018 Pratik Parikh  wrote:

Thanks, I'll check them out.  But what I am trying to produce is a zip 
distribution fo Mac is x similar to the one available on postgresql download 
site. Homebrew works but it ties the libpg to prefix if the path where libs are 
generated. I am trying to do some unit testing across more then one machine on 
an ongoing basis.

Hope that helps clear uo what I mean by portable.

On Thu, Oct 25, 2018, 9:31 AM Don Seiler  wrote:
On Wed, Oct 24, 2018 at 7:22 PM Pratik Parikh  wrote:
     How to compile and build portable postgresql for mac.  Can someone point 
me how the builds are done for general distribution.  A script would be 
appreciated if anyone has it.  

I'm not sure what you mean by "portable"? I'm going to answer assuming you just 
want to run postgres on your own Mac.

There are couple options. Peter Eisentraut has a great collection of Homebrew 
scripts that you can plug into: https://github.com/petere/homebrew-postgresql

I've recently switched to pgenv, which provides a clean CLI for building and 
using all the various supported versions: https://github.com/theory/pgenv 

Don.
-- 
Don Seiler
www.seiler.us

--
Sent from tablet.

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

[GPT]

So, this kind of switch after a few goes is a normal behavior or
something unexpected which will change in future?

I am asking in order to have my mind on incidents with similar behavior.

Tia


On 10/25/18, Andres Freund  wrote:
> Hi,
>
> On 2018-10-25 11:43:39 +0200, GPT wrote:
>> I have faced an incident which, according to the developer, "The
>> problem seems to be that pgsql switches from a CONST sub-expression
>> into a FUNCEXPR after a few goes, ..."
>>
>> Please have a look at the following two links which describe the problem:
>>
>> https://github.com/nahanni/rw_redis_fdw/issues/13
>> https://github.com/nahanni/rw_redis_fdw/issues/14
>>
>> The first link (13) has to do with PG10.x version.
>> The second one (14) has to do with PG11 version in which the developer
>> traces the "problem".
>>
>> Please check and comment.
>
> The developer of the FDW seems to have made the requisite fixes. I'm not
> sure what you'd like us to do?
>
> Greetings,
>
> Andres Freund
>

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

[Bruno Wolff III]

On Wed, Oct 24, 2018 at 10:21:11 +0100,
 Tom Lane  wrote:

Bruno Wolff III  writes:

If I have a substantial database dump file to provide for reproducing this
do you prefer it on a web server somewhere? I expect that mailing very
large attachments to the lists is a bad idea.


No, don't do that.  If you can make sample data available for download,
or point to some accessible dataset somewhere, that'd work.

regards, tom lane


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.


http://wolff.to/iplocation is a bit under 400mb. It should download at about 
1MB/sec. It is a plain text dump of the iplocation table with the alter table 
for the constaint / exclude index added at the end.


http://wolff.to/postgresql.conf is the config file I use on the server.

The server has the following installed (but you don't need plperl for the 
test):

postgresql11-plperl-11.0-1PGDG.rhel7.x86_64
postgresql11-libs-11.0-1PGDG.rhel7.x86_64
postgresql11-docs-11.0-1PGDG.rhel7.x86_64
postgresql11-11.0-1PGDG.rhel7.x86_64
postgresql11-server-11.0-1PGDG.rhel7.x86_64

The output of
createdb -U postgres test
psql -U postgres -f iplocation test
is:
SET
SET
SET
SET
SET
set_config 



(1 row)

SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 4398722
psql:iplocation:4398789: ERROR:  out of memory
DETAIL:  Failed on request of size 6264 in memory context "PortalContext".

It is certainly possible that my postgresql.conf file is bad and that I 
just got away with it under 10.5 by the. The server is a vm with 32GB of 
memory allocated to it. I set vm.overcommit_memory = 2 to avoid the oom 
killer after upgrading to 11. Before that I didn't have a problem.


On Fedora with a more vanilla postgresql.conf the exclude constraint 
built fine, but creating an spgist index file is taking forever (near a 
day now) creating a normal spgist index on an ip address column for a 
table with a lot of rows (I think around 150 million), that ran in a 
reasonable amount of time on the server.

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

[Christoph Moench-Tegeder]

## GPT (gptmailingli...@gmail.com):

> So, this kind of switch after a few goes is a normal behavior or
> something unexpected which will change in future?

It's expected, and even documented (when you look at the user-level
interface):
https://www.postgresql.org/docs/current/static/sql-prepare.html
  Prepared statements can use generic plans rather than re-planning
  with each set of supplied EXECUTE values. This occurs immediately
  for prepared statements with no parameters; otherwise it occurs
  only after five or more executions produce plans whose estimated
  cost average (including planning overhead) is more expensive than
  the generic plan cost estimate.

Regards,
Christoph

-- 
Spare Space.

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

[Thomas Munro]

On Fri, Oct 26, 2018 at 2:21 AM Alexandre Assouad
 wrote:
> FATAL:  dsa_allocate could not find 7 free pages

> Some users have faced this issue which seems to be a bug in postgresql query 
> planner which should be solved :
> https://www.postgresql.org/message-id/CAEepm%3D1k7sYJbxoOSJcS-4ti2MHOnBXBfLf%3D-gtuFLTXPqvTDg%40mail.gmail.com

Hello Alexandre,

Thanks for the report.  Yes, that bug does seem like a plausible
explanation for that error.  The fix was in commit 8ffc3be1, which
will be included in 10.6 (target release date:  November 8th).  It's
also in 11.0, out now.

If you are able to reproduce the problem easily on a copy of your
database, and you have the time/inclination to investigate, is there
any chance you could test the query on a local build of REL_10_STABLE
(the branch that will produce 10.6 soon), instructions below, or the
released v11.0 (if Timescale is available for that, it doesn't look
like it)?  If not, no worries.

> But I’m still facing this issue.
> I’m using postgresql 10.5 on ubuntu 18.04
> With timescaledb extension (which could be involved in this bug but I 
> couldn’t find any related issue on their side)

It's interesting that reports came from users of Citus and Timescale.
There doesn't seem to be any reason to think it's caused by anything
these extension are doing, other than just having a lot of data, big
queries and the right access pattern to hit that bug.

=== How to set up a throw-away REL_10_STABLE cluster:

On an Ubuntu developer machine, check out, build, install into
temporary directory:
sudo apt-get install git make gcc flex bison libz-dev libreadline-dev
git clone https://github.com/postgres/postgres.git
cd postgres
git checkout REL_10_STABLE
./configure --prefix=$HOME/tmp_install
make -s -j8 && make -s install

Initialise and start a database cluster:
~/tmp_install/bin/initdb -D ~/tmp_pgdata
~/tmp_install/bin/postgres -D ~/tmp_pgdata
... now postgres is running in the foreground, until you hit ^C
... do whatever you need to do to install Timescale extension, schema,
data, reproduce problem

To check that you can reproduce the problem in 10.5 with a server
built that way, stop that and:
git checkout REL_10_5
make -s clean && make -s -j8 && make -s install
~/tmp_install/bin/postgres -D ~/tmp_pgdata

To install Timescale it's probably the instructions from
https://github.com/timescale/timescaledb, using ./bootstrap
-DPG_CONFIG=~/tmp_install/bin/pgconfig but i haven't tried that
myself.

(You don't have to run initdb again or reload data when switching
between tags/branches in the 10.x series).

-- 
Thomas Munro
http://www.enterprisedb.com