Concurrent CTE

Hello! We have a lot of big CTE (~40 statements, ~1000 lines) for very dynamic OLTP content and avg response time 50-300ms. Our setup has 96 threads (Intel Xeon Gold 6128), 256 GB RAM and 12 SSD (3 tablespaces). DB size < RAM. Simplifying the problem: WITH aa as (   SELECT * FROM table1 ), bb

Re: Concatenate of values in hierarchical data

> On 2 Apr 2018, at 19:23, Mr. Baseball 34 wrote: > > I have the data below, returned from a PostgreSQL table using this SQL: > > SELECT ila.treelevel, >ila.app, >ila.lrflag, >ila.ic, >ila.price, >ila.treelevel-1 as parent, >

PgUpgrade bumped my XIDs by ~50M?

We have a large >20TB system just pg_upgraded from 9.5 to 9.6 as per the versions shown below. The system does <5M transactions/day based on sum(commit + abort) from pg_stat_database. Autovac is running all possible threads now and upon investigating I see that thousands of tables are now above t

Re: PgUpgrade bumped my XIDs by ~50M?

On Wed, Apr 4, 2018 at 05:29:46PM -0500, Jerry Sievers wrote: > We have a large >20TB system just pg_upgraded from 9.5 to 9.6 as per the > versions shown below. > > The system does <5M transactions/day based on sum(commit + abort) from > pg_stat_database. > > Autovac is running all possible thre

SQL statement in an error report for deferred constraint violation.

Hi, While PQresultErrorField() from libpq allows to get context in which an error occurred for immediate constraints, and thus an SQL statement which caused the constraint violation, I cannot see any way to find out which SQL statement caused an error in case of deferred constraints, in particular

Re: PgUpgrade bumped my XIDs by ~50M?

Bruce Momjian writes: > On Wed, Apr 4, 2018 at 05:29:46PM -0500, Jerry Sievers wrote: > >> We have a large >20TB system just pg_upgraded from 9.5 to 9.6 as per the >> versions shown below. >> >> The system does <5M transactions/day based on sum(commit + abort) from >> pg_stat_database. >> >> A

Extension make installcheck: include update/insert feedback?

Hello, I have a custom extension that uses the usual REGRESS Makefile variable to indicate files in {sql,expected} that should be used when you say `make installcheck`. I've noticed that if my test code does an INSERT or DELETE, the usual `INSERT 0 1` and `UPDATE 2` messages don't appear in

Re: PgUpgrade bumped my XIDs by ~50M?

On Wed, Apr 4, 2018 at 07:13:36PM -0500, Jerry Sievers wrote: > Bruce Momjian writes: > > Is it possible that pg_upgrade used 50M xids while upgrading? > > Hi Bruce. > > Don't think so, as I did just snap the safety snap and ran another > upgrade on that. > > And I also compared txid_current f

Re: LDAP Bind Password

On 4/3/18 16:12, Kumar, Virendra wrote: > Is anybody aware of how to encrypt bind password for ldap authentication > in pg_hba.conf. Anonymous bind is disabled in our organization so we > have to use bind ID and password but to keep them as plaintext in > pg_hba.conf defeat security purposes. We wa

Re: Extension make installcheck: include update/insert feedback?

Paul Jungwirth writes: > I've noticed that if my test code does an INSERT or DELETE, the usual > `INSERT 0 1` and `UPDATE 2` messages don't appear in the *.out files, > even though those otherwise mirror psql. I thought maybe there was some > psql switch that turns those on/off, but I couldn't

Re: PgUpgrade bumped my XIDs by ~50M?

Bruce Momjian writes: > On Wed, Apr 4, 2018 at 07:13:36PM -0500, Jerry Sievers wrote: > >> Bruce Momjian writes: >> > Is it possible that pg_upgrade used 50M xids while upgrading? >> >> Hi Bruce. >> >> Don't think so, as I did just snap the safety snap and ran another >> upgrade on that. >>

Re: Concurrent CTE

On Wed, Apr 4, 2018 at 3:20 AM Artur Formella wrote: > Hello! > We have a lot of big CTE (~40 statements, ~1000 lines) for very dynamic > OLTP content and avg response time 50-300ms. Our setup has 96 threads > (Intel Xeon Gold 6128), 256 GB RAM and 12 SSD (3 tablespaces). DB size < > RAM. > Simpl

Re: Concurrent CTE

On Tuesday, April 3, 2018, Artur Formella wrote: > > And the question: is it possible to achieve more concurrent execution plan > to reduce the response time? For example: > Thread1: aa | dd | ff | primary > Thread2: bb | ee | gg > Thread3: cc | -- | hh > If and how depends greatly on your versi

Re: Concurrent CTE

On Wed, Apr 4, 2018 at 8:01 AM, Artur Formella wrote: > Execution now: > time--> > Thread1: aa | bb | cc | dd | ee | ff | gg | hh | primary > > And the question: is it possible to achieve more concurrent execution plan > to reduce the response time? For example: > Thread1: aa | dd | ff | primary >

Re: Concurrent CTE

On Wed, Apr 4, 2018 at 10:12 PM, Thomas Munro wrote: > Parallel query can't be used for CTE queries currently. ​A pointer to the location in the docs covering this limitation would be appreciated. It isn't covered here: https://www.postgresql.org/docs/10/static/when-can-parallel-query-be-used

Re: Concurrent CTE

On Thu, Apr 5, 2018 at 5:16 PM, David G. Johnston wrote: > On Wed, Apr 4, 2018 at 10:12 PM, Thomas Munro > wrote: >> >> Parallel query can't be used for CTE queries currently. > > A pointer to the location in the docs covering this limitation would be > appreciated. It isn't covered here: > > ht

AW: Concurrent CTE

Did you look at this approach using dblink already? https://gist.github.com/mjgleaso/8031067 In your situation, you will have to modify the example but it may give an idea where to start. Klaus > -Ursprüngliche Nachricht- > Von: Artur Formella > Gesendet: Dienstag, 3. April 2018 22: