pldbgapi error

Hi Team, We are trying to install the pldbgapi extension in postgresql 11.3 server but we getting below error,Please do the needful, ==> make USE_PGXS=1 gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-st

Bug in documentation (trim(...))?

The following illustrates the problem (removing double quotes from a string): *js=# select regexp_replace(regexp_replace( '"University of Cape Town"', '^"', ''),'"$', ''); regexp_replace - University of Cape Town(1 row)js=# trim(both '"' from '"University of C

pg_upgrade can result in early wraparound on databases with high transaction load

Hello, This week I upgraded one of my large(2.8TB), high-volume databases from 9 to 11. The upgrade itself went fine. About two days later, we unexpectedly hit transaction ID wraparound. What was perplexing about this was that the age of our oldest `datfrozenxid` was only 1.2 billion - far away fr

Re: Bug in documentation (trim(...))?

On Mon, May 20, 2019 at 12:09 PM Johann Spies wrote: > The following illustrates the problem (removing double quotes from a string): Missing "select " in your examples, is it intentional? > js=# trim(both '"' from '"University of Cape Town"'); > ERROR: syntax error at or near "trim" > LINE 1: t

Re: Bug in documentation (trim(...))?

Thanks! I was a bit stupid. Johann On Mon, 20 May 2019 at 12:18, Francisco Olarte wrote: > On Mon, May 20, 2019 at 12:09 PM Johann Spies > wrote: > > The following illustrates the problem (removing double quotes from a > string): > > Missing "select " in your examples, is it intentional? > >

Re: pldbgapi error

On 5/20/19 7:07 PM, Prakash Ramakrishnan wrote:> Hi Team, > > We are trying to install the pldbgapi extension in postgresql 11.3 server but we getting below error,Please do the needful, > > ==> make USE_PGXS=1 > gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif

Re: pldbgapi error

Hi Ian, Thanks for replying can you please help me to solve this problem which code will be support for postgresql 11 can you please share me the link or file. Regards, Prakash.R On Mon, May 20, 2019 at 5:25 PM Ian Barwick wrote: > On 5/20/19 7:07 PM, Prakash Ramakrishnan wrote:> Hi Team, > >

Re: pldbgapi error

On 5/20/19 8:57 PM, Prakash Ramakrishnan wrote: On Mon, May 20, 2019 at 5:25 PM Ian Barwick mailto:ian.barw...@2ndquadrant.com>> wrote: On 5/20/19 7:07 PM, Prakash Ramakrishnan wrote:> Hi Team,  >  > We are trying to install the pldbgapi extension in postgresql 11.3 server but w

Re: pldbgapi error

Prakash Ramakrishnan wrote: > which code > will be support for postgresql 11 can you please share me the link or file. It appears indeed from the error messages that you're trying to compile an old version. The lastest version here: git://git.postgresql.org/git/pldebugger.git does compil

Re: Loading table with indexed jsonb field is stalling

On 5/20/19 9:55 AM, Will Hartung wrote: Please reply to list also. Ccing list. On Sat, May 18, 2019 at 6:55 AM Adrian Klaver > wrote: In addition to what Tim asked: Is the jsonb field the only field in the table? Can we see the table schema? D

Re: Loading table with indexed jsonb field is stalling

I sent these earlier, but they didn’t get to the list, so I’m resending — sorry about that. > On May 17, 2019, at 7:53 PM, Tim Cross wrote: > > Which version of postgres? PostgreSQL 10.8 (Ubuntu 10.8-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.3.0-16ubuntu3) 7.3.0, 64

Re: Loading table with indexed jsonb field is stalling

I send this earlier, but it did not make it to the list, so I’m resending it. > On May 18, 2019, at 6:55 AM, Adrian Klaver wrote: > Is the jsonb field the only field in the table? > > Can we see the table schema? # \d eis_entity Table "public.eis_entity" Column

Re: Loading table with indexed jsonb field is stalling

I send this earlier, but it did not make it to the list, so I’m resending it. > On May 18, 2019, at 11:02 AM, Tom Lane wrote: > > In addition to the questions about what PG version you're using, is > the backend process that's doing the load actually consuming CPU time, > or is it just sitting?

INSERT where not exists with foreign key

My Google foo isn't working on this question, probably because I don't understand the question well enough. I'm using Postgres 11.3 on Centos 7. I'm trying to insert a record in table A with a foreign key to table B, but only where there is not already a foreign key in A to B. So assume this simple

Re: Loading table with indexed jsonb field is stalling

Will Hartung writes: >> On May 18, 2019, at 11:02 AM, Tom Lane wrote: >> In addition to the questions about what PG version you're using, is >> the backend process that's doing the load actually consuming CPU time, >> or is it just sitting? > It’s consuming both CPU and disk space. Hmm, so not

Re: Loading table with indexed jsonb field is stalling

On 5/20/19 11:05 AM, Will Hartung wrote: I send this earlier, but it did not make it to the list, so I’m resending it. On May 18, 2019, at 11:02 AM, Tom Lane wrote: In addition to the questions about what PG version you're using, is the backend process that's doing the load actually consuming

Re: Loading table with indexed jsonb field is stalling

> On May 20, 2019, at 11:13 AM, Adrian Klaver wrote: > > What do the below show: > > 1) ps ax | grep postgres > > 2) As superuser: > select * from pg_stat_activity ; I’ll restart the process and report back. Thanks.

Re: Loading table with indexed jsonb field is stalling

> On May 20, 2019, at 11:12 AM, Tom Lane wrote: > > Hmm, so not a deadlock (plus you're running a version that has that > bug fixed). It sounds like you might have hit an actual bug, perhaps > one that causes it to endlessly re-split an index page, or something > along that line. But it's goi

Re: INSERT where not exists with foreign key

On 5/20/19 11:11 AM, Chuck Martin wrote: My Google foo isn't working on this question, probably because I don't understand the question well enough. I'm using Postgres 11.3 on Centos 7. I'm trying to insert a record in table A with a foreign key to table B, but only where there is not already a

Re: INSERT where not exists with foreign key

On 5/20/19 11:11 AM, Chuck Martin wrote: My Google foo isn't working on this question, probably because I don't understand the question well enough. I'm using Postgres 11.3 on Centos 7. I'm trying to insert a record in table A with a foreign key to table B, but only where there is not already a

Re: Refresh Publication takes hours and doesn´t finish

I tried sometime ago ... but with no responses, I ask you again. pg_publication_tables is a view that is used to refresh publication, but as we have 15.000 tables, it takes hours and doesn´t complete. If I change that view I can have an immediate result. The question is: Can I change that view ? Th

Re: Loading table with indexed jsonb field is stalling

> On May 20, 2019, at 11:13 AM, Adrian Klaver wrote: > What do the below show: > > 1) ps ax | grep postgres $ ps -ax | grep postgres 1171 ?S 0:04 /usr/lib/postgresql/10/bin/postgres -D /var/lib/postgresql/10/main -c config_file=/etc/postgresql/10/main/postgresql.conf 1420 ?

Re: Refresh Publication takes hours and doesn´t finish

Em seg, 20 de mai de 2019 às 17:18, PegoraroF10 escreveu: > > I tried sometime ago ... but with no responses, I ask you again. > pg_publication_tables is a view that is used to refresh publication, but as > we have 15.000 tables, it takes hours and doesn´t complete. If I change that > view I can h

Re: Re: Refresh Publication takes hours and doesn´t finish

PegoraroF10 writes: > I tried sometime ago ... but with no responses, I ask you again. > pg_publication_tables is a view that is used to refresh publication, but as > we have 15.000 tables, it takes hours and doesn´t complete. If I change that > view I can have an immediate result. The question is

Re: Loading table with indexed jsonb field is stalling

On 5/20/19 4:14 PM, Will Hartung wrote: [snip] Also to note, I tried just loading the table with no indexes, and I was getting a solid 22MB/s via iostat of just raw data load (just to proof that I/O system, while certainly not extraordinary, was functional). I think you answered this earlier,

Re: Re: Refresh Publication takes hours and doesn´t finish

Em seg, 20 de mai de 2019 às 18:30, Tom Lane escreveu: > > Hmm ... given that pg_get_publication_tables() shouldn't return any > duplicate OIDs, it does seem unnecessarily inefficient to put it in > an IN-subselect condition. Peter, is there a reason why this isn't > a straight lateral join? I g

Re: Loading table with indexed jsonb field is stalling

> On May 20, 2019, at 2:36 PM, Ron wrote: > I think you answered this earlier, but does the same stalling happen when > indexes are dropped? No, the data loads fine. The way I originally stumbled upon this was that I had off loaded the data for some other kinds of testing. There are severa

Re: Loading table with indexed jsonb field is stalling

On 5/20/19 4:48 PM, Will Hartung wrote: On May 20, 2019, at 2:36 PM, Ron wrote: I think you answered this earlier, but does the same stalling happen when indexes are dropped? No, the data loads fine. The way I originally stumbled upon this was that I had off loaded the data for some other ki

Re: Loading table with indexed jsonb field is stalling

> On May 20, 2019, at 2:55 PM, Ron wrote: > And it pathologically loads even when there's just a PK on the numeric field? Yea, that works fine. > Drop all indexes, load data, recreate indexes? No, I use the incremental load as it gives a much better indication when the process has gone off

Re: Loading table with indexed jsonb field is stalling

On 5/20/19 5:43 PM, Will Hartung wrote: On May 20, 2019, at 2:55 PM, Ron wrote: And it pathologically loads even when there's just a PK on the numeric field? Yea, that works fine. Drop all indexes, load data, recreate indexes? No, I use the incremental load as it gives a much better indicat

Re: Loading table with indexed jsonb field is stalling

On 5/20/19 3:43 PM, Will Hartung wrote: On May 20, 2019, at 2:55 PM, Ron wrote: And it pathologically loads even when there's just a PK on the numeric field? Yea, that works fine. Drop all indexes, load data, recreate indexes? No, I use the incremental load as it gives a much better i

Re: Loading table with indexed jsonb field is stalling

> On May 20, 2019, at 4:39 PM, Adrian Klaver wrote: > > So the issue is building the index(s) not loading the data, correct? Correct. Since I have been exploring this, I have not been able to successfully create a gin jsonb_path_ops index on a jsonb field either as a new index on a table with

Re: Loading table with indexed jsonb field is stalling

> On May 20, 2019, at 4:27 PM, Ron wrote: > > I'm confused. You wrote above that loading without indexes and with just the > PK works just fine; if you really need it loaded in Aurora or production, > just drop the indexes beforehand? Because the jsonb_path_ops index does not rebuild, and it

Re: Loading table with indexed jsonb field is stalling

On 5/20/19 4:49 PM, Will Hartung wrote: On May 20, 2019, at 4:39 PM, Adrian Klaver > wrote: So the issue is building the index(s) not loading the data, correct? Correct. Since I have been exploring this, I have not been able to successfully create a gin js

Re: Loading table with indexed jsonb field is stalling

On 5/20/19 6:51 PM, Will Hartung wrote: On May 20, 2019, at 4:27 PM, Ron > wrote: I'm confused.  You wrote above that loading without indexes and with just the PK works just fine; if you *really* need it loaded in Aurora or production, just drop the indexes befo

Re: Loading table with indexed jsonb field is stalling

> On May 20, 2019, at 5:15 PM, Ron wrote: > > Are there a sufficiently small number of elements in each traits object that > you can do something like this, on the UNINDEXED table? > SELECT traits->element1, traits->element2, count(*) > from eis_entry > group by traits->element1, traits->eleme

Re: Loading table with indexed jsonb field is stalling

> On May 20, 2019, at 5:14 PM, Adrian Klaver wrote: > > Well looks like you are down to Tom's suggestion of creating a test case. > Given that it seems confined to the jsonb field and corresponding index, I > would think that is all that is needed for the test case. Start with some > smaller

Re: Loading table with indexed jsonb field is stalling

Will Hartung writes: >> On May 20, 2019, at 5:14 PM, Adrian Klaver wrote: >> Well looks like you are down to Tom's suggestion of creating a test case. >> Given that it seems confined to the jsonb field and corresponding index, I >> would think that is all that is needed for the test case. Start

Re: Loading table with indexed jsonb field is stalling

On 5/20/19 5:22 PM, Will Hartung wrote: On May 20, 2019, at 5:14 PM, Adrian Klaver wrote: Well looks like you are down to Tom's suggestion of creating a test case. Given that it seems confined to the jsonb field and corresponding index, I would think that is all that is needed for the test

Re: Loading table with indexed jsonb field is stalling

On 5/20/19 5:33 PM, Adrian Klaver wrote: On 5/20/19 5:22 PM, Will Hartung wrote: On May 20, 2019, at 5:14 PM, Adrian Klaver wrote: Well looks like you are down to Tom's suggestion of creating a test case. Given that it seems confined to the jsonb field and corresponding index, I would thi

Re: Loading table with indexed jsonb field is stalling

On 5/20/19 7:21 PM, Will Hartung wrote: On May 20, 2019, at 5:15 PM, Ron wrote: Are there a sufficiently small number of elements in each traits object that you can do something like this, on the UNINDEXED table? SELECT traits->element1, traits->element2, count(*) from eis_entry group by trait