Re: LWLock optimization

Hi, On 2019-07-23 16:28:52 +, Alexander Pyhalov wrote: > I was asked to bring up this topic on maling lists after asking question on > IRC. > > The issue is the following. > On one of our servers (10.5), I see a lot of queries with > wait_event_type='LWLock', wait_event='lock_manager' Co

Re: Default ordering option

On 7/24/19 2:23 AM, Adrian Klaver wrote: On 7/23/19 8:43 AM, Cyril Champier wrote: Hi, In this documentation , it is said:     If sorting is not chosen, the rows will be returned in an     unspecified order. The actual order in that case

Re: pg_dump schema in pg11 without global permissions

On 7/23/19 4:32 PM, Sergey Koposov wrote: On Tue, 2019-07-23 at 19:24 -0400, Tom Lane wrote: [ hey guys, please trim your replies ] sorry Experimenting, however, I see that that only works as intended if I use v11 pg_restore.  I can replicate your result if I use v10 pg_restore.  So ... don't

Re: pg_dump schema in pg11 without global permissions

On 7/23/19 4:17 PM, Sergey Koposov wrote: For some reason I see when I just have a command  like this it doesnt' have a grant connect on database ~/soft/pgsql_install/bin/pg_dump -U skoposov -h localhost -n xx   --no-tablespaces test1 but when I add  '-Fc' flag to pg_dump, I can see 'grant conn

Re: pg_dump schema in pg11 without global permissions

On Tue, 2019-07-23 at 19:24 -0400, Tom Lane wrote: > [ hey guys, please trim your replies ] sorry > > Experimenting, however, I see that that only works as intended if > I use v11 pg_restore.  I can replicate your result if I use v10 > pg_restore.  So ... don't do that. Thanks very much!  I've s

Re: pg_dump schema in pg11 without global permissions

[ hey guys, please trim your replies ] Sergey Koposov writes: > On Tue, 2019-07-23 at 16:04 -0700, Adrian Klaver wrote: >> That is only supposed to happen if you use -C(--create) and I am seeing  >> that in your examples. > For some reason I see when I just have  > a command  like this it doesnt

Re: pg_dump schema in pg11 without global permissions

On Tue, 2019-07-23 at 16:04 -0700, Adrian Klaver wrote: > On 7/23/19 3:58 PM, Sergey Koposov wrote: > > > > On Tue, 2019-07-23 at 15:52 -0700, Adrian Klaver wrote: > > > > > > On 7/23/19 3:42 PM, Sergey Koposov wrote: > > > > > > > > > > > > On Tue, 2019-07-23 at 15:37 -0700, Adrian Klaver wrot

Re: pg_dump schema in pg11 without global permissions

On 7/23/19 4:04 PM, Adrian Klaver wrote: On 7/23/19 3:58 PM, Sergey Koposov wrote: On Tue, 2019-07-23 at 15:52 -0700, Adrian Klaver wrote: On 7/23/19 3:42 PM, Sergey Koposov wrote: On Tue, 2019-07-23 at 15:37 -0700, Adrian Klaver wrote: On 7/23/19 3:23 PM, Sergey Koposov wrote: On Tue, 2

Re: pg_dump schema in pg11 without global permissions

On 7/23/19 3:58 PM, Sergey Koposov wrote: On Tue, 2019-07-23 at 15:52 -0700, Adrian Klaver wrote: On 7/23/19 3:42 PM, Sergey Koposov wrote: On Tue, 2019-07-23 at 15:37 -0700, Adrian Klaver wrote: On 7/23/19 3:23 PM, Sergey Koposov wrote: On Tue, 2019-07-23 at 15:21 -0700, Adrian Klaver wr

Re: pg_dump schema in pg11 without global permissions

On Tue, 2019-07-23 at 15:52 -0700, Adrian Klaver wrote: > On 7/23/19 3:42 PM, Sergey Koposov wrote: > > > > On Tue, 2019-07-23 at 15:37 -0700, Adrian Klaver wrote: > > > > > > On 7/23/19 3:23 PM, Sergey Koposov wrote: > > > > > > > > > > > > On Tue, 2019-07-23 at 15:21 -0700, Adrian Klaver wrot

Re: pg_dump schema in pg11 without global permissions

On 7/23/19 3:42 PM, Sergey Koposov wrote: On Tue, 2019-07-23 at 15:37 -0700, Adrian Klaver wrote: On 7/23/19 3:23 PM, Sergey Koposov wrote: On Tue, 2019-07-23 at 15:21 -0700, Adrian Klaver wrote: On 7/23/19 3:19 PM, Sergey Koposov wrote: Hi, I'm trying to copy a schema from one PG databa

Re: pg_dump schema in pg11 without global permissions

On Tue, 2019-07-23 at 15:37 -0700, Adrian Klaver wrote: > On 7/23/19 3:23 PM, Sergey Koposov wrote: > > > > On Tue, 2019-07-23 at 15:21 -0700, Adrian Klaver wrote: > > > > > > On 7/23/19 3:19 PM, Sergey Koposov wrote: > > > > > > > > > > > > Hi, > > > > > > > > I'm trying to copy a schema from

Re: pg_dump schema in pg11 without global permissions

On 7/23/19 3:23 PM, Sergey Koposov wrote: On Tue, 2019-07-23 at 15:21 -0700, Adrian Klaver wrote: On 7/23/19 3:19 PM, Sergey Koposov wrote: Hi, I'm trying to copy a schema from one PG database (ver 11) to PG 10. Previously the first database version was 9.6 and the way I did the copying was

Re: pg_dump schema in pg11 without global permissions

On Tue, 2019-07-23 at 15:21 -0700, Adrian Klaver wrote: > On 7/23/19 3:19 PM, Sergey Koposov wrote: > > > > Hi, > > > > I'm trying to copy a schema from one PG database (ver 11) to PG 10. > > Previously the first database version was 9.6 and the way I did the copying > > was > > > > ssh -o Comp

Re: pg_dump schema in pg11 without global permissions

On 7/23/19 3:19 PM, Sergey Koposov wrote: Hi, I'm trying to copy a schema from one PG database (ver 11) to PG 10. Previously the first database version was 9.6 and the way I did the copying was ssh -o Compression=no user@host '/opt/pgsql/bin/pg_dump --no-tablespaces -n schemaname -Fc  -U dbadm

pg_dump schema in pg11 without global permissions

Hi,  I'm trying to copy a schema from one PG database (ver 11) to PG 10.  Previously the first database version was 9.6 and the way I did the copying was  ssh -o Compression=no user@host '/opt/pgsql/bin/pg_dump --no-tablespaces -n schemaname -Fc  -U dbadmin dbname'  | pg_restore -U dbadmin -h lo

Re: Query plan: SELECT vs INSERT from same select

[ please keep the list cc'd ] Alexander Voytsekhovskyy writes: > Sorry again > here is both links: > https://explain.depesz.com/s/AEWj > https://explain.depesz.com/s/CHwF Don't think I believe that those are the same query --- there's a CTE in the second one that doesn't appear in the first, and

Re: Query plan: SELECT vs INSERT from same select

Alexander Voytsekhovskyy writes: > You can see explain analyze verbose here: > https://explain.depesz.com/s/AEWj > The problem is, when i wrap it to > A) > INSERT INTO norepl_1542_result (axis_x1, axis_y1, v_1) > SELECT SAME QUERY > OR even > B) > WITH rows AS ( > ... SAME SELECT QUERY ..

Re: Query plan: SELECT vs INSERT from same select

Hi, On Tue, Jul 23, 2019 at 3:29 PM Alexander Voytsekhovskyy wrote: > > I have quite complicated query: > > SELECT axis_x1, axis_y1, SUM(delivery_price) as v_1 FROM ( > SELECT to_char(delivery_data.delivery_date, '-MM') as axis_x1, > clients.id_client as axis_y1, delivery_data.amount * prod

Query plan: SELECT vs INSERT from same select

I have quite complicated query: SELECT axis_x1, axis_y1, SUM(delivery_price) as v_1 FROM ( SELECT to_char(delivery_data.delivery_date, '-MM') as axis_x1, clients.id_client as axis_y1, delivery_data.amount * production_price.price * groups.discount as delivery_price FROM delivery_data JOIN cl

Re: Too slow to create new schema and their tables, functions, triggers.

On Tue, Jul 23, 2019 at 1:36 PM Adrian Klaver wrote: > On 7/23/19 12:20 PM, PegoraroF10 wrote: > > We have in a single database 190 identical schemas. Now, when we create > a new > > one, with exactly same structure as the previous ones, it takes 20 or 30 > > minutes to finish. Usual time to fini

Re: Too slow to create new schema and their tables, functions, triggers.

On 7/23/19 12:20 PM, PegoraroF10 wrote: We have in a single database 190 identical schemas. Now, when we create a new one, with exactly same structure as the previous ones, it takes 20 or 30 minutes to finish. Usual time to finish that script was 30 seconds. Basically, my script creates an entir

Too slow to create new schema and their tables, functions, triggers.

We have in a single database 190 identical schemas. Now, when we create a new one, with exactly same structure as the previous ones, it takes 20 or 30 minutes to finish. Usual time to finish that script was 30 seconds. Basically, my script creates an entire structure for a new customer: - Create s

Re: pg_upgrade : 9.X to 11.X issue CentoOS 7.6

Sorry i missed to refer link. https://dba.stackexchange.com/questions/50135/pg-upgrade-unrecognized-configuration-parameter-unix-socket-directory On Tue, Jul 23, 2019 at 9:20 AM Tom Lane wrote: > Perumal Raj writes: > > Finally upgrade completed successfully after implementing the following >

Re: Default ordering option

On 7/23/19 8:43 AM, Cyril Champier wrote: Hi, In this documentation , it is said: If sorting is not chosen, the rows will be returned in an unspecified order. The actual order in that case will depend on the scan and join pla

LWLock optimization

Hi. I was asked to bring up this topic on maling lists after asking question on IRC. The issue is the following. On one of our servers (10.5), I see a lot of queries with wait_event_type='LWLock', wait_event='lock_manager' This is a stat gathering/analyzing application with tables which have

Re: pg_upgrade : 9.X to 11.X issue CentoOS 7.6

Perumal Raj writes: > Finally upgrade completed successfully after implementing the following > Workaround. You didn't say where you got these executables from, but if the 9.2 and 11 packages were from the same packager, you should complain to them about it. If they're patching 9.2 to have unix_

Re: pg_upgrade : 9.X to 11.X issue CentoOS 7.6

Hi All, Finally upgrade completed successfully after implementing the following Workaround. mv /usr/bin/pg_ctl{,-orig} echo '#!/bin/bash' > /usr/bin/pg_ctl echo '"$0"-orig "${@/unix_socket_directory/unix_socket_directories}"' >> /usr/bin/pg_ctl chmod +x /usr/bin/pg_ctl *Special thanks to ''Z

Default ordering option

Hi, In this documentation , it is said: > If sorting is not chosen, the rows will be returned in an unspecified > order. The actual order in that case will depend on the scan and join plan > types and the order on disk, but it must not be re

Re: pg_upgrade : 9.X to 11.X issue CentoOS 7.6

Hi All, Yes, , bin file correct only as it came up with CentOS 7.6 . Anyhow , thanks for the pointers which helped me to look at hacking solution :-) Currently , upgrade is running. will keep u posted with results. Thanks, On Tue, Jul 23, 2019 at 7:43 AM Tom Lane wrote: > Adrian Klaver writ

Re: pg_upgrade : 9.X to 11.X issue CentoOS 7.6

Adrian Klaver writes: > On 7/23/19 7:17 AM, Perumal Raj wrote: >> command: "/usr/pgsql-9.2/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D >> "/data/db/data" -o "-p 5432 -b -c listen_addresses='' -c >> unix_socket_permissions=0700 -c unix_socket_directory='/var/lib/pgsql'" >> start >> "pg_upgrade

Re: pg_upgrade : 9.X to 11.X issue CentoOS 7.6

On 7/23/19 7:17 AM, Perumal Raj wrote: Hi Luca -bash-4.2$ "/usr/pgsql-9.2/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/data/db/data" -o "-p 5432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directory='/var/lib/pgsql'" start waiting for server to start stopp

Re: pg_upgrade : 9.X to 11.X issue CentoOS 7.6

Hi Luca -bash-4.2$ "/usr/pgsql-9.2/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/data/db/data" -o "-p 5432 -b -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directory='/var/lib/pgsql'" start waiting for server to start stopped waiting pg_ctl: could not start server Exa

Re: pg_upgrade : 9.X to 11.X issue CentoOS 7.6

On Tue, Jul 23, 2019 at 3:56 PM Perumal Raj wrote: > could not connect to source postmaster started with the command: > "/usr/pgsql-9.2/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/data/db/data" > -o "-p 5432 -b -c listen_addresses='' -c unix_socket_permissions=0700 -c > unix_socket_directory

pg_upgrade : 9.X to 11.X issue CentoOS 7.6

Hi Team, Please give me some pointers to resolve this issue. -bash-4.2$ export OLDCLUSTER=/usr/pgsql-9.2 -bash-4.2$ export NEWCLUSTER=/usr/pgsql-11 -bash-4.2$ /usr/pgsql-11/bin/pg_upgrade --old-bindir=$OLDCLUSTER/bin --new-bindir=$NEWCLUSTER/bin --old-datadir=/data/db/data --new-datadir=/pgdata/