How to log pg_terminate_backend() calls

[Zwettler Markus (OIZ)]

We see some "FATAL:  terminating connection due to administrator command" error 
messages in the Postgres logfile.

We assume someone did pg_terminate_backend() calls.

How can we log such calls, especially who did the call?

ECPG call interface && filename

[Matthias Apitz]

Hello,

Actually, the ecpg pre-compiler resolves ESQL/C statements like

EXEC SQL SELECT 

into a function call to the executer in the ecpglib as:

ECPGdo(__LINE__, ...);

where __LINE__ is later substituted by the C-precompiler by the current
line number as an integer. The purpose is mostly for logging features
like:

[24304] [14.01.2020 12:05:18:433]: ECPGtrans on line 1108: action "rollback"; 
connection "sisis71"

i.e. to have the line number in the log statement
(btw: the date and timestamp is already a local modification we did to
bring the log line in sync with other logs our application is writing).

In a real world server application written in some hundreds foo.pgc
files (a generated application interface with the pgc files based on
the 400 tables in the database), it would be nice to have the call done
like this:

ECPGdo(__LINE__, __FILE__, ...)

and the resulting log line like this:

[24304] [14.01.2020 12:05:18:433]: ECPGtrans on line 1108 in file D01buch.c: 
action "rollback"; connection "sisis71"

Before hacking this into the ecpglib code by my own, I wanted discuss this here
or even file somewhere a structured change request for further development.

Thanks

matthias
-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!

Is it possible to replicate through an http proxy?

[Iban Rodriguez]

Hi all,

for a project we are working on we have the following requirements:
* PostgreSQL server on cloud
* Lot of edge devices that need access to DB
* Edges will only execute SELECT queries. No modification allowed on them.
* Internet access on edges can be lost and data must be accessible while
connection is reestablished.

Our first approach to solve this was to setup a postgreSQL server on each
edge and configure a logical replication from cloud server. This way our
clients always connect to local server who automatically syncs with cloud
server while Internet connection is up and also automatically reconnects
when Internet connection is recovered after connection lost.

However, we facing a new requirement that we do not know how to solve. This
requirement is that access to Internet from edge devices must be done
through a http proxy server. Reading documentation we have not been able to
find a way to create a replication with this limitation. The questions we
would like to ask are:
* Is it possible to create a logical replication with this limitation?
* Is there an alternative solution to our problem bearing in mind this
limitation?

Thank you very much!

Re: How to log pg_terminate_backend() calls

[Tom Lane]

"Zwettler Markus (OIZ)"  writes:
> We see some "FATAL:  terminating connection due to administrator command" 
> error messages in the Postgres logfile.
> We assume someone did pg_terminate_backend() calls.
> How can we log such calls, especially who did the call?

There's no built-in facility for that, short of log_statement = all.

You could try making a wrapper function: rename pg_terminate_backend
to something else and then make a plpgsql equivalent that issues
RAISE LOG and then calls the renamed function.

regards, tom lane

Re: WaitForMultipleObjects in C Extension

[Merlin Moncure]

On Tue, Jan 14, 2020 at 1:39 AM İlyas Derse  wrote:
>
>  Hi guys I need your experiences. I made an Extension in C. When I stop the 
> query on PostgreSQL,I want to control my extension's result with use cases in 
> C.So I think , I should use WaitForMultipleObjects. Is there a like a 
> SignalObjectHandle() ? By the way I'm using Windows.

Are you using threading in your extension?  This is something to avoid
except in very specific cases, particularly using native calls.

merlin

Error retrieving PostgreSQL DB information with Coturn

[Marco Ippolito]

In order to understand how to use Postgresql-11 with Coturn,
https://github.com/coturn/coturn ,
I created a postgresql-11 dabatase using
/usr/local/share/turnserver/schema.sql  :

CREATE TABLE turnusers_lt (
realm varchar(127) default '',
name varchar(512),
hmackey char(128),
PRIMARY KEY (realm,name)
);

CREATE TABLE turn_secret (
realm varchar(127) default '',
value varchar(256),
primary key (realm,value)
);

CREATE TABLE allowed_peer_ip (
realm varchar(127) default '',
ip_range varchar(256),
primary key (realm,ip_range)
);

CREATE TABLE denied_peer_ip (
realm varchar(127) default '',
ip_range varchar(256),
primary key (realm,ip_range)
);

CREATE TABLE turn_origin_to_realm (
origin varchar(127),
realm varchar(127),
primary key (origin)
);

CREATE TABLE turn_realm_option (
realm varchar(127) default '',
opt varchar(32),
value varchar(128),
primary key (realm,opt)
);

CREATE TABLE oauth_key (
kid varchar(128),
ikm_key varchar(256),
timestamp bigint default 0,
lifetime integer default 0,
as_rs_alg varchar(64) default '',
realm varchar(127),
primary key (kid)
);


But when trying to execute secure_relay_with_db_psql.sh :

(base) 
marco@marco-U36SG:~/turnserver-4.5.0.8/examples/scripts/longtermsecuredb$

./secure_relay_with_db_psql.sh
0: WARNING: Cannot find config file: turnserver.conf. Default and
command-line settings will be
used.
0: Listener address to use: 127.0.0.1
0: Listener address to use: ::1
0: Relay address to use: 127.0.0.1
0: Relay address to use: ::1
0: 300 bytes per second allowed per session
0: WARNING: Cannot find config file: turnserver.conf. Default and
command-line settings will
   be used.
0: RFC 3489/5389/5766/5780/6062/6156 STUN/TURN Server
Version Coturn-4.5.0.8 'dan Eider'
0: Max number of open files/sockets allowed for this process: 4096
0: Due to the open files/sockets limitation,
max supported number of TURN Sessions possible is: 2000 (approximately)
0:  Show him the instruments, Practical Frost: 

0: TLS supported
0: DTLS supported
0: DTLS 1.2 supported
0: TURN/STUN ALPN supported
0: Third-party authorization (oAuth) supported
0: GCM (AEAD) supported
0: OpenSSL compile-time version: OpenSSL 1.1.1  11 Sep 2018 (0x1010100f)
0: SQLite is not supported
0: Redis supported
0: PostgreSQL supported
0: MySQL supported
0: MongoDB is not supported
0:
0: Default Net Engine version: 3 (UDP thread per CPU core)

=

0: Domain name:
0: Default realm: north.gov
0: oAuth server name: blackdow.carleon.gov
0: WARNING: cannot find certificate file: turn_server_cert.pem (1)
0: WARNING: cannot start TLS and DTLS listeners because certificate
file is not set properly
0: WARNING: cannot find private key file: turn_server_pkey.pem (1)
0: WARNING: cannot start TLS and DTLS listeners because private key
file is not set properly
Cannot create pid file: /var/run/turnserver.pid: Permission denied
0: Cannot create pid file: /var/run/turnserver.pid
0: pid file created: /var/tmp/turnserver.pid
0: IO method (main listener thread): epoll (with changelist)
0: Wait for relay ports initialization...
0:   relay 127.0.0.1 initialization...
0:   relay 127.0.0.1 initialization done
0:   relay ::1 initialization...
0:   relay ::1 initialization done
0: Relay ports initialization done
0: IO method (general relay thread): epoll (with changelist)
0: turn server id=0 created
0: IPv4. SCTP listener opened on : 127.0.0.1:3478
0: IPv4. TCP listener opened on : 127.0.0.1:3478
0: IPv4. SCTP listener opened on : 127.0.0.1:3479
0: IPv4. TCP listener opened on : 127.0.0.1:3479
0: IPv6. SCTP listener opened on : ::1:3478
0: IPv6. TCP listener opened on : ::1:3478
0: IPv6. SCTP listener opened on : ::1:3479
0: IPv6. TCP listener opened on : ::1:3479
0: IO method (general relay thread): epoll (with changelist)
0: turn server id=1 created
0: IPv6. TCP listener opened on : ::1:3479
0: IPv6. UDP listener opened on: ::1:3479
0: Total General servers: 3
0: IO method (auth thread): epoll (with changelist)
0: IO method (auth thread): epoll (with changelist)
0: IO method (admin thread): epoll (with changelist)
0: IPv4. CLI listener opened on : 127.0.0.1:5766
0: PostgreSQL DB connection success: host=localhost dbname=coturn
user=turn password=turn
connect_timeout=30
0: ERROR: Error retrieving PostgreSQL DB information: ERROR:
 permission denied for

Re: Error retrieving PostgreSQL DB information with Coturn

[Adrian Klaver]

On 1/14/20 8:33 AM, Marco Ippolito wrote:
In order to understand how to use Postgresql-11 with Coturn, 
https://github.com/coturn/coturn ,
I created a postgresql-11 dabatase using 
/usr/local/share/turnserver/schema.sql  :


     CREATE TABLE turnusers_lt (
         realm varchar(127) default '',
         name varchar(512),
         hmackey char(128),
         PRIMARY KEY (realm,name)
     );

     CREATE TABLE turn_secret (
             realm varchar(127) default '',
             value varchar(256),
             primary key (realm,value)
     );

     CREATE TABLE allowed_peer_ip (
             realm varchar(127) default '',
             ip_range varchar(256),
             primary key (realm,ip_range)
     );

     CREATE TABLE denied_peer_ip (
             realm varchar(127) default '',
             ip_range varchar(256),
             primary key (realm,ip_range)
     );

     CREATE TABLE turn_origin_to_realm (
             origin varchar(127),
             realm varchar(127),
             primary key (origin)
     );

     CREATE TABLE turn_realm_option (
             realm varchar(127) default '',
             opt varchar(32),
             value varchar(128),
             primary key (realm,opt)
     );

     CREATE TABLE oauth_key (
             kid varchar(128),
             ikm_key varchar(256),
             timestamp bigint default 0,
             lifetime integer default 0,
             as_rs_alg varchar(64) default '',
             realm varchar(127),
             primary key (kid)
     );


But when trying to execute secure_relay_with_db_psql.sh :

     (base) 
marco@marco-U36SG:~/turnserver-4.5.0.8/examples/scripts/longtermsecuredb$

     ./secure_relay_with_db_psql.sh
     0: WARNING: Cannot find config file: turnserver.conf. Default and 
command-line settings will be

     used.
     0: Listener address to use: 127.0.0.1
     0: Listener address to use: ::1
     0: Relay address to use: 127.0.0.1
     0: Relay address to use: ::1
     0: 300 bytes per second allowed per session
     0: WARNING: Cannot find config file: turnserver.conf. Default and 
command-line settings will

        be used.
     0: RFC 3489/5389/5766/5780/6062/6156 STUN/TURN Server
     Version Coturn-4.5.0.8 'dan Eider'
     0: Max number of open files/sockets allowed for this process: 4096
     0: Due to the open files/sockets limitation,
     max supported number of TURN Sessions possible is: 2000 (approximately)
     0:  Show him the instruments, Practical Frost: 

     0: TLS supported
     0: DTLS supported
     0: DTLS 1.2 supported
     0: TURN/STUN ALPN supported
     0: Third-party authorization (oAuth) supported
     0: GCM (AEAD) supported
     0: OpenSSL compile-time version: OpenSSL 1.1.1  11 Sep 2018 
(0x1010100f)

     0: SQLite is not supported
     0: Redis supported
     0: PostgreSQL supported
     0: MySQL supported
     0: MongoDB is not supported
     0:
     0: Default Net Engine version: 3 (UDP thread per CPU core)

     =

     0: Domain name:
     0: Default realm: north.gov 
     0: oAuth server name: blackdow.carleon.gov 


     0: WARNING: cannot find certificate file: turn_server_cert.pem (1)
     0: WARNING: cannot start TLS and DTLS listeners because certificate 
file is not set properly

     0: WARNING: cannot find private key file: turn_server_pkey.pem (1)
     0: WARNING: cannot start TLS and DTLS listeners because private key 
file is not set properly

     Cannot create pid file: /var/run/turnserver.pid: Permission denied
     0: Cannot create pid file: /var/run/turnserver.pid
     0: pid file created: /var/tmp/turnserver.pid
     0: IO method (main listener thread): epoll (with changelist)
     0: Wait for relay ports initialization...
     0:   relay 127.0.0.1 initialization...
     0:   relay 127.0.0.1 initialization done
     0:   relay ::1 initialization...
     0:   relay ::1 initialization done
     0: Relay ports initialization done
     0: IO method (general relay thread): epoll (with changelist)
     0: turn server id=0 created
     0: IPv4. SCTP listener opened on : 127.0.0.1:3478 

     0: IPv4. TCP listener opened on : 127.0.0.1:3478 

     0: IPv4. SCTP listener opened on : 127.0.0.1:3479 

     0: IPv4. TCP listener opened on : 127.0.0.1:3479 


     0: IPv6. SCTP listener opened on : ::1:3478
     0: IPv6. TCP listener opened on : ::1:3478
     0: IPv6. SCTP listener opened on : ::1:3479
     0: IPv6. TCP listener opened on : ::1:3479
     0: IO method (general relay thread): epoll (with changelist)
     0: turn server id=1 created
     0: IPv6. TCP listener opened on : ::1:3479
     0: IPv6. UDP listener opened on: ::1:3479
     0: Total General servers: 3
     0: IO method (auth thread): epoll (with changelist)
     0: IO method (auth thread): epoll (with changelist)

Re: WaitForMultipleObjects in C Extension

[George Neuner]

On Tue, 14 Jan 2020 10:35:09 +0300, ?lyas Derse 
wrote:

> Hi guys I need your experiences. I made an Extension in C. When I stop the
>query on PostgreSQL,I want to control my extension's result with use cases
>in C.So I think , I should use WaitForMultipleObjects.

Not really understanding this.


>Is there a like a SignalObjectHandle() ? By the way I'm using Windows.

Not exactly.  Windows does has some signaling objects like Unix/Linux,
but they exist only in the filesystem and network stack, and they are
signaled only by events within those subsystems.  IOW, they can't be
signaled manually.

For general communication/synchronization purposes Windows uses an
event based notification system.  See:
https://docs.microsoft.com/en-us/windows/win32/sync/using-synchronization
https://docs.microsoft.com/en-us/windows/win32/sync/using-event-objects

George

Re: Worse performance with higher work_mem?

[Israel Brewster]

> On Jan 13, 2020, at 6:34 PM, Dilip Kumar  wrote:
> 
> On Tue, Jan 14, 2020 at 5:29 AM Israel Brewster  > wrote:
>> 
>> I was working on diagnosing a “slow” (about 6 second run time) query:
>> 
>> SELECT
>>to_char(bucket,'-MM-DD"T"HH24:MI:SS') as dates,
>>x_tilt,
>>y_tilt,
>>rot_x,
>>rot_y,
>>date_part('epoch', bucket) as timestamps,
>>temp
>>FROM
>>(SELECT
>>  time_bucket('1 week', read_time) as bucket,
>>  avg(tilt_x::float) as x_tilt,
>>  avg(tilt_y::float) as y_tilt,
>>  avg(rot_x::float) as rot_x,
>>  avg(rot_y::float) as rot_y,
>>  avg(temperature::float) as temp
>>FROM tilt_data
>>WHERE station='c08883c0-fbe5-11e9-bd6e-aec49259cebb'
>>AND read_time::date<='2020-01-13'::date
>>GROUP BY bucket) s1
>>ORDER BY bucket;
>> 
>> In looking at the explain analyze output, I noticed that it had an “external 
>> merge Disk” sort going on, accounting for about 1 second of the runtime 
>> (explain analyze output here: https://explain.depesz.com/s/jx0q). Since the 
>> machine has plenty of RAM available, I went ahead and increased the work_mem 
>> parameter. Whereupon the query plan got much simpler, and performance of 
>> said query completely tanked, increasing to about 15.5 seconds runtime 
>> (https://explain.depesz.com/s/Kl0S), most of which was in a HashAggregate.
>> 
>> I am running PostgreSQL 11.6 on a machine with 128GB of ram (so, like I 
>> said, plenty of RAM)
>> 
>> How can I fix this? Thanks.
> 
> I have noticed that after increasing the work_mem your plan has
> switched from a parallel plan to a non-parallel plan.  Basically,
> earlier it was getting executed with 3 workers.  And, after it becomes
> non-parallel plan execution time is 3x.  For the analysis can we just
> reduce the value of parallel_tuple_cost and parallel_setup_cost and
> see how it behaves?

That was it. Setting the parallel_tuple_cost parameter to .05 and the 
parallel_setup_cost parameter to 500 (so, both to half their default values) 
caused this query to run in parallel again with the higher work_mem setting 
(and do the sorts in memory, as was the original goal). New explain output at 
https://explain.depesz.com/s/rX3m  Granted, 
doing the sorts in memory didn’t speed things up as much as I would have hoped 
- 5.58 seconds vs 5.9 - but at least the higher work_mem setting isn’t slowing 
down the query any more.

Would be nice if the query could run a little faster - even six seconds is a 
relatively long time to wait - but I can live with that at least. So thanks! 
Hmmm… now I wonder how things would change if I increased the 
max_parallel_workers value? Might be something to play around with. Maybe grab 
a few more cores for the VM.
---
Israel Brewster
Software Engineer
Alaska Volcano Observatory 
Geophysical Institute - UAF 
2156 Koyukuk Drive 
Fairbanks AK 99775-7320
Work: 907-474-5172
cell:  907-328-9145

> 
> -- 
> Regards,
> Dilip Kumar
> EnterpriseDB: http://www.enterprisedb.com 

Re: Error retrieving PostgreSQL DB information with Coturn

[Marco Ippolito]

Thank you very much Adrian!

I feel ashamed for such a silly mistake

coturn=# \dt
   List of relations
 Schema | Name | Type  | Owner
+--+---+---
 public | admin_user   | table | turn
 public | allowed_peer_ip  | table | turn
 public | denied_peer_ip   | table | turn
 public | oauth_key| table | turn
 public | turn_origin_to_realm | table | turn
 public | turn_realm_option| table | turn
 public | turn_secret  | table | turn
 public | turnusers_lt | table | turn
(8 rows)

1: IPv4. CLI listener opened on : 127.0.0.1:5766
1: PostgreSQL DB connection success: host=localhost dbname=coturn user=turn
password=turn connect_timeout=30

Marco



Il giorno mar 14 gen 2020 alle ore 17:53 Adrian Klaver <
adrian.kla...@aklaver.com> ha scritto:

> On 1/14/20 8:33 AM, Marco Ippolito wrote:
> > In order to understand how to use Postgresql-11 with Coturn,
> > https://github.com/coturn/coturn ,
> > I created a postgresql-11 dabatase using
> > /usr/local/share/turnserver/schema.sql  :
> >
> >  CREATE TABLE turnusers_lt (
> >  realm varchar(127) default '',
> >  name varchar(512),
> >  hmackey char(128),
> >  PRIMARY KEY (realm,name)
> >  );
> >
> >  CREATE TABLE turn_secret (
> >  realm varchar(127) default '',
> >  value varchar(256),
> >  primary key (realm,value)
> >  );
> >
> >  CREATE TABLE allowed_peer_ip (
> >  realm varchar(127) default '',
> >  ip_range varchar(256),
> >  primary key (realm,ip_range)
> >  );
> >
> >  CREATE TABLE denied_peer_ip (
> >  realm varchar(127) default '',
> >  ip_range varchar(256),
> >  primary key (realm,ip_range)
> >  );
> >
> >  CREATE TABLE turn_origin_to_realm (
> >  origin varchar(127),
> >  realm varchar(127),
> >  primary key (origin)
> >  );
> >
> >  CREATE TABLE turn_realm_option (
> >  realm varchar(127) default '',
> >  opt varchar(32),
> >  value varchar(128),
> >  primary key (realm,opt)
> >  );
> >
> >  CREATE TABLE oauth_key (
> >  kid varchar(128),
> >  ikm_key varchar(256),
> >  timestamp bigint default 0,
> >  lifetime integer default 0,
> >  as_rs_alg varchar(64) default '',
> >  realm varchar(127),
> >  primary key (kid)
> >  );
> >
> >
> > But when trying to execute secure_relay_with_db_psql.sh :
> >
> >  (base)
> > marco@marco-U36SG
> :~/turnserver-4.5.0.8/examples/scripts/longtermsecuredb$
> >  ./secure_relay_with_db_psql.sh
> >  0: WARNING: Cannot find config file: turnserver.conf. Default and
> > command-line settings will be
> >  used.
> >  0: Listener address to use: 127.0.0.1
> >  0: Listener address to use: ::1
> >  0: Relay address to use: 127.0.0.1
> >  0: Relay address to use: ::1
> >  0: 300 bytes per second allowed per session
> >  0: WARNING: Cannot find config file: turnserver.conf. Default and
> > command-line settings will
> > be used.
> >  0: RFC 3489/5389/5766/5780/6062/6156 STUN/TURN Server
> >  Version Coturn-4.5.0.8 'dan Eider'
> >  0: Max number of open files/sockets allowed for this process: 4096
> >  0: Due to the open files/sockets limitation,
> >  max supported number of TURN Sessions possible is: 2000
> (approximately)
> >  0:  Show him the instruments, Practical Frost: 
> >
> >  0: TLS supported
> >  0: DTLS supported
> >  0: DTLS 1.2 supported
> >  0: TURN/STUN ALPN supported
> >  0: Third-party authorization (oAuth) supported
> >  0: GCM (AEAD) supported
> >  0: OpenSSL compile-time version: OpenSSL 1.1.1  11 Sep 2018
> > (0x1010100f)
> >  0: SQLite is not supported
> >  0: Redis supported
> >  0: PostgreSQL supported
> >  0: MySQL supported
> >  0: MongoDB is not supported
> >  0:
> >  0: Default Net Engine version: 3 (UDP thread per CPU core)
> >
> >  =
> >
> >  0: Domain name:
> >  0: Default realm: north.gov 
> >  0: oAuth server name: blackdow.carleon.gov
> > 
> >  0: WARNING: cannot find certificate file: turn_server_cert.pem (1)
> >  0: WARNING: cannot start TLS and DTLS listeners because certificate
> > file is not set properly
> >  0: WARNING: cannot find private key file: turn_server_pkey.pem (1)
> >  0: WARNING: cannot start TLS and DTLS listeners because private key
> > file is not set properly
> >  Cannot create pid file: /var/run/turnserver.pid: Permission denied
> >  0: Cannot create pid file: /var/run/turnserver.pid
> >  0: pid file created: /var/tmp/turnserver.pid
> >  0:

Re: Worse performance with higher work_mem?

[Israel Brewster]

> 
> On Jan 13, 2020, at 3:46 PM, Rob Sargent  wrote:
> 
> 
> 
>> On Jan 13, 2020, at 5:41 PM, Israel Brewster > > wrote:
>> 
>>> On Jan 13, 2020, at 3:19 PM, Tom Lane >> > wrote:
>>> 
>>> Israel Brewster mailto:ijbrews...@alaska.edu>> 
>>> writes:
 In looking at the explain analyze output, I noticed that it had an 
 “external merge Disk” sort going on, accounting for about 1 second of the 
 runtime (explain analyze output here: https://explain.depesz.com/s/jx0q 
  >). Since the machine has plenty of RAM 
 available, I went ahead and increased the work_mem parameter. Whereupon 
 the query plan got much simpler, and performance of said query completely 
 tanked, increasing to about 15.5 seconds runtime 
 (https://explain.depesz.com/s/Kl0S  
 >), 
 most of which was in a HashAggregate.
 How can I fix this? Thanks.
>>> 
>>> Well, the brute-force way not to get that plan is "set enable_hashagg =
>>> false".  But it'd likely be a better idea to try to improve the planner's
>>> rowcount estimates.  The problem here seems to be lack of stats for
>>> either "time_bucket('1 week', read_time)" or "read_time::date".
>>> In the case of the latter, do you really need a coercion to date?
>>> If it's a timestamp column, I'd think not.  As for the former,
>>> if the table doesn't get a lot of updates then creating an expression
>>> index on that expression might be useful.
>>> 
>> 
>> Thanks for the suggestions. Disabling hash aggregates actually made things 
>> even worse: (https://explain.depesz.com/s/cjDg 
>> ), so even if that wasn’t a brute-force 
>> option, it doesn’t appear to be a good one. Creating an index on the 
>> time_bucket expression didn’t seem to make any difference, and my data does 
>> get a lot of additions (though virtually no changes) anyway (about 1 
>> additional record per second). As far as coercion to date, that’s so I can 
>> do queries bounded by date, and actually have all results from said date 
>> included. That said, I could of course simply make sure that when I get a 
>> query parameter of, say, 2020-1-13, I expand that into a full date-time for 
>> the end of the day. However, doing so for a test query didn’t seem to make 
>> much of a difference either: https://explain.depesz.com/s/X5VT 
>> 
>> 
>> So, to summarise:
>> 
>> Set enable_hasagg=off: worse
>> Index on time_bucket expression: no change in execution time or query plan 
>> that I can see
>> Get rid of coercion to date: *slight* improvement. 14.692 seconds instead of 
>> 15.5 seconds. And it looks like the row count estimates were actually worse.
>> Lower work_mem, forcing a disk sort and completely different query plan: 
>> Way, way better (around 6 seconds)
>> 
>> …so so far, it looks like the best option is to lower the work_mem, run the 
>> query, then set it back?
>> ---
> 
> I don’t see that you’ve updated the statistics?

U….no. I know nothing about that :-)

Some research tells me that a) it should happen as part of the autovacuum 
process, and that b) I may not be running autovacuum enough, since it is a 
large table and doesn’t change often. But I don’t really know.

---
Israel Brewster
Software Engineer
Alaska Volcano Observatory 
Geophysical Institute - UAF 
2156 Koyukuk Drive 
Fairbanks AK 99775-7320
Work: 907-474-5172
cell:  907-328-9145

Re: Worse performance with higher work_mem?

[Justin]

Updating the stats can be done via vacuum or analyze command,

https://www.postgresql.org/docs/12/sql-analyze.html.   To just analyze a
table typically does not take much time. and can be scheduled to run so the
stats update instead of waiting on auto-vacuum to deal with it  which could
be some time on an insert only table

Seeing the difference in speed between first run with low work memory vs
high work memory with parallelization,  I suspect the temp tables never
actually got written to disk they just hung out in the OS IO cache.

The query in all examples is hung up doing Index scan and running the avg()
aggregate.

Maybe you can look at creating summary table for time periods to work
against,  maybe a Weekly or Daily summary of these values could cut down on
the number of records being processed.  It would not affect the result




On Tue, Jan 14, 2020 at 12:08 PM Israel Brewster 
wrote:

>
> On Jan 13, 2020, at 3:46 PM, Rob Sargent  wrote:
>
>
>
> On Jan 13, 2020, at 5:41 PM, Israel Brewster 
> wrote:
>
> On Jan 13, 2020, at 3:19 PM, Tom Lane  wrote:
>
> Israel Brewster  writes:
>
> In looking at the explain analyze output, I noticed that it had an
> “external merge Disk” sort going on, accounting for about 1 second of the
> runtime (explain analyze output here: https://explain.depesz.com/s/jx0q <
> https://explain.depesz.com/s/jx0q>). Since the machine has plenty of RAM
> available, I went ahead and increased the work_mem parameter. Whereupon the
> query plan got much simpler, and performance of said query completely
> tanked, increasing to about 15.5 seconds runtime (
> https://explain.depesz.com/s/Kl0S ),
> most of which was in a HashAggregate.
> How can I fix this? Thanks.
>
>
> Well, the brute-force way not to get that plan is "set enable_hashagg =
> false".  But it'd likely be a better idea to try to improve the planner's
> rowcount estimates.  The problem here seems to be lack of stats for
> either "time_bucket('1 week', read_time)" or "read_time::date".
> In the case of the latter, do you really need a coercion to date?
> If it's a timestamp column, I'd think not.  As for the former,
> if the table doesn't get a lot of updates then creating an expression
> index on that expression might be useful.
>
>
> Thanks for the suggestions. Disabling hash aggregates actually made things
> even worse: (https://explain.depesz.com/s/cjDg), so even if that wasn’t a
> brute-force option, it doesn’t appear to be a good one. Creating an index
> on the time_bucket expression didn’t seem to make any difference, and my
> data does get a lot of additions (though virtually no changes) anyway
> (about 1 additional record per second). As far as coercion to date, that’s
> so I can do queries bounded by date, and actually have all results from
> said date included. That said, I could of course simply make sure that when
> I get a query parameter of, say, 2020-1-13, I expand that into a full
> date-time for the end of the day. However, doing so for a test query didn’t
> seem to make much of a difference either:
> https://explain.depesz.com/s/X5VT
>
> So, to summarise:
>
> Set enable_hasagg=off: worse
> Index on time_bucket expression: no change in execution time or query plan
> that I can see
> Get rid of coercion to date: *slight* improvement. 14.692 seconds instead
> of 15.5 seconds. And it looks like the row count estimates were actually
> worse.
> Lower work_mem, forcing a disk sort and completely different query plan:
> Way, way better (around 6 seconds)
>
> …so so far, it looks like the best option is to lower the work_mem, run
> the query, then set it back?
> ---
>
>
> I don’t see that you’ve updated the statistics?
>
>
> U….no. I know nothing about that :-)
>
> Some research tells me that a) it should happen as part of the autovacuum
> process, and that b) I may not be running autovacuum enough, since it is a
> large table and doesn’t change often. But I don’t really know.
>
> ---
> Israel Brewster
> Software Engineer
> Alaska Volcano Observatory
> Geophysical Institute - UAF
> 2156 Koyukuk Drive
> Fairbanks AK 99775-7320
> Work: 907-474-5172
> cell:  907-328-9145
>
>

Re: Worse performance with higher work_mem?

[Tom Lane]

Israel Brewster  writes:
> On Jan 13, 2020, at 3:46 PM, Rob Sargent  wrote:
>> I don’t see that you’ve updated the statistics?

> U….no. I know nothing about that :-)

Just do a manual ANALYZE on the table, if you don't want to wait
around for autovacuum to get to it.

regards, tom lane

Multiple Aggregations Order

[João Haas]

Hi there,

I'm working on a query where I need to fetch information from a table along
with some data from a many-to-many connection table in a single query. My
idea is to do an outer join with the connection query and aggregate the
needed data in multiple 'array_agg's, and then handle this aggregated data
later in code.

The issue is, there are a lot of aggs (4 by now, may increase later), and I
need to order these by a 'order' field on the connection table. I can put
an 'ORDER BY "order"' statement inside each 'array_agg', but I don't think
that would be the most efficient way. Doing the join with a sorted
connection table didn't work for me as well, probably due to other joins on
the query. I tried doing some stuff with subqueries, but all attempts ended
up in either failure or increased query time.

The structure is a graph based structure, and both query and structure
themselves are quite complex, but a simplified example would look like this:

CREATE TABLE tb (
id SERIAL PRIMARY KEY
);
CREATE TABLE conn (
parent_id INTEGER,
child_id INTEGER,
"order" INTEGER,
kind INTEGER
);

INSERT INTO tb VALUES (1), (2), (3);
INSERT INTO conn VALUES (1, 2, 2, 10), (1, 3, 1, 20);

SELECT tb.*, array_agg(conn.child_id), array_agg(conn.kind)
FROM tb
LEFT OUTER JOIN conn
ON conn.parent_id = tb.id
GROUP BY tb.id;

Not sure if there's really a solution, but does anyone have any idea on how
to solve this issue without doing multiple 'ORDER BY's inside each
aggregation?

Thank you!
-- 
João C. Haas

Re: Multiple Aggregations Order

[Michael Lewis]

"handle this aggregated data later in code"

What is your end goal though? Also, approx how many rows in these tables?
Can you share an example query and plan? What version are you using?

>

Re: Multiple Aggregations Order

[David G. Johnston]

On Tuesday, January 14, 2020, João Haas  wrote:
>
> SELECT tb.*, array_agg(conn.child_id), array_agg(conn.kind)
>
>
Create a custom type (using row(...) might work...?) with the relevant
fields and “...array_agg((child_id, kind)::custom_type order by ...”?

David J.

Re: Is it possible to replicate through an http proxy?

[Justin]

Another solution to the problem instead of logical replication would be
utilize wal_shipping  and have the edge servers replay the wal using the
restore_command

The wal files can be downloaded from from HTTP server via a proxy and
placed on the edge servers wal_archive directory to be replayed

see
https://www.postgresql.org/docs/12/runtime-config-wal.html#GUC-RESTORE-COMMAND
and
https://www.postgresql.org/docs/12/warm-standby.html

this is twisting  the warm_standby and restore functions into odd shapes
This  gives up  the fine control that logical replication provides and its
not as seamless

On Tue, Jan 14, 2020 at 7:48 AM Iban Rodriguez  wrote:

> Hi all,
>
> for a project we are working on we have the following requirements:
> * PostgreSQL server on cloud
> * Lot of edge devices that need access to DB
> * Edges will only execute SELECT queries. No modification allowed on them.
> * Internet access on edges can be lost and data must be accessible while
> connection is reestablished.
>
> Our first approach to solve this was to setup a postgreSQL server on each
> edge and configure a logical replication from cloud server. This way our
> clients always connect to local server who automatically syncs with cloud
> server while Internet connection is up and also automatically reconnects
> when Internet connection is recovered after connection lost.
>
> However, we facing a new requirement that we do not know how to solve.
> This requirement is that access to Internet from edge devices must be done
> through a http proxy server. Reading documentation we have not been able to
> find a way to create a replication with this limitation. The questions we
> would like to ask are:
> * Is it possible to create a logical replication with this limitation?
> * Is there an alternative solution to our problem bearing in mind this
> limitation?
>
> Thank you very much!
>

Fwd: Postgresql Data corruption

[Tulqin Navruzov]

Hi team

Could you help us to sort this out please?

We had some hardware problems with Datacenter and could not using
postgresql from restored snapshots it showing "Structure needs to be
cleaning " during startup
, on centos7 did xfs_repair and postgresql started successfully. But could
not select datas from it . showing below message .

This is when want to login to db with postgres user .
psql: PANIC:  could not open critical system index 2662

but can logged in with another user and trying to select from some tables ,
showing this message :

ERROR:  catalog is missing 11 attribute(s) for relid 113971

Could you help us to solve this problem? or could you give contact who can
help with this?

Thanks in advance.

Re: Multiple Aggregations Order

[João Haas]

I'm aiming to serialize some graph data into a JSON format, and some of the
data needed for the serialization is in the relation tables, like, "this
node connects to this other node in this way". These are served to IOT
devices and the data changes a lot, so there's a ton of requests and
caching is not that efficient. Due to that, it would be ideal if I could
fetch everything in a single query, with the data needed aggregated in
arrays, so that I can denormalize them later in code (eg.:
zip(data['child_id_set'], data['child_kind_set']) in python).

Each query should have from 100~1000 items from the 'tb' table. The
amount of child relations each item has vary a lot depending on the node,
so it can be from 1~1.

The tables themselves have some millions of rows, but I don't have access
to the production database to check how many exactly. Due to this, although
I can share the query plan, it wouldn't be so meaningful, since it is
considering my dev env, which only have ~100 rows, and it's throwing seq
scans for everything.

The query is the following:
WITH RECURSIVE tree(tree_id, tree_path) AS (
SELECT "conn"."child_id",
   ARRAY["conn"."parent_id"]::integer[]
  FROM "conn"
 WHERE "parent_id" IN (643) -- Starting point of graph
 UNION
SELECT DISTINCT ON ("conn"."child_id")
   "conn"."child_id",
   "tree"."tree_path" || "conn"."parent_id"
  FROM "tree", "conn"
 WHERE "conn"."parent_id" = "tree"."tree_id"
   AND NOT "conn"."child_id" = ANY("tree"."tree_path")
) SELECT "tb".*,
 array_length("tree"."tree_path", 1) AS "depth",
 array_agg("conn"."child_id" ORDER BY ("conn"."order",
"conn"."kind")) FILTER (WHERE "conn"."child_id" IS NOT NULL) AS
"child_id_set",
 array_agg("conn"."kind" ORDER BY ("conn"."order", "conn"."kind"))
FILTER (WHERE "conn"."child_id" IS NOT NULL) AS "child_kind_set",
 array_agg("conn"."restrictions" ORDER BY ("conn"."order",
"conn"."kind")) FILTER (WHERE "conn"."child_id" IS NOT NULL) AS
"child_restrictions_set",
 array_agg("conn"."meta" ORDER BY ("conn"."order", "conn"."kind"))
FILTER (WHERE "conn"."child_id" IS NOT NULL) AS "child_meta_set"
FROM "tb"
LEFT OUTER JOIN "conn"
  ON "tb"."id" = "conn"."parent_id"
   INNER JOIN (SELECT DISTINCT ON ("tree_id") * FROM "tree") AS "tree"
  ON "tree"."tree_id" = "tb"."id"
GROUP BY "tb"."id", "tree"."tree_path";

I'm currently using Postgres 11

On Tue, Jan 14, 2020 at 4:49 PM Michael Lewis  wrote:

> "handle this aggregated data later in code"
>
> What is your end goal though? Also, approx how many rows in these tables?
> Can you share an example query and plan? What version are you using?
>
>>

Re: Multiple Aggregations Order

[Thomas Kellerer]

João Haas schrieb am 14.01.2020 um 18:26:

I'm working on a query where I need to fetch information from a table
along with some data from a many-to-many connection table in a single
query. My idea is to do an outer join with the connection query and
aggregate the needed data in multiple 'array_agg's, and then handle
this aggregated data later in code.

The issue is, there are a lot of aggs (4 by now, may increase later),
and I need to order these by a 'order' field on the connection table.
I can put an 'ORDER BY "order"' statement inside each 'array_agg',
but I don't think that would be the most efficient way. Doing the
join with a sorted connection table didn't work for me as well,
probably due to other joins on the query. I tried doing some stuff
with subqueries, but all attempts ended up in either failure or
increased query time.



What about aggregating into a single jsonb array?
You lose some of the data type information, but maybe that's OK for the backend 
that processes the data.

Something along the lines:

  SELECT tb.*,
 array_length(tree.tree_path, 1) AS depth,
 jsonb_agg(jsonb_build_object('child_id', conn.child_id, 'kind', conn.kind, 
'restrictions', conn.restrictions) order by conn."order")
  FROM tb
  ...
  GROUP BY ...

Re: Postgresql Data corruption

[Rene Romero Benavides]

On Tue, Jan 14, 2020 at 2:55 PM Tulqin Navruzov <
tulqin.navru...@finnetlimited.com> wrote:

>
>
> Hi team
>
> Could you help us to sort this out please?
>
> We had some hardware problems with Datacenter and could not using
> postgresql from restored snapshots it showing "Structure needs to be
> cleaning " during startup
> , on centos7 did xfs_repair and postgresql started successfully. But could
> not select datas from it . showing below message .
>
> This is when want to login to db with postgres user .
> psql: PANIC:  could not open critical system index 2662
>
> but can logged in with another user and trying to select from some tables
> , showing this message :
>
> ERROR:  catalog is missing 11 attribute(s) for relid 113971
>
> Could you help us to solve this problem? or could you give contact who can
> help with this?
>
> Thanks in advance.
>

First of all, please attend to these instructions:
https://wiki.postgresql.org/wiki/Corruption

from what you describe looks like you need to restore from backups or
promote a slave to master if you have such a setup and the slave(s) were
not affected by these hardware problems.

-- 
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/

Re: Postgresql Data corruption

[Rene Romero Benavides]

Can't help you personally right now due to work, but try these guys:
https://pgexperts.com/services/emergency_help/

On Tue, Jan 14, 2020 at 4:08 PM Rene Romero Benavides <
rene.romer...@gmail.com> wrote:

> On Tue, Jan 14, 2020 at 2:55 PM Tulqin Navruzov <
> tulqin.navru...@finnetlimited.com> wrote:
>
>>
>>
>> Hi team
>>
>> Could you help us to sort this out please?
>>
>> We had some hardware problems with Datacenter and could not using
>> postgresql from restored snapshots it showing "Structure needs to be
>> cleaning " during startup
>> , on centos7 did xfs_repair and postgresql started successfully. But
>> could not select datas from it . showing below message .
>>
>> This is when want to login to db with postgres user .
>> psql: PANIC:  could not open critical system index 2662
>>
>> but can logged in with another user and trying to select from some tables
>> , showing this message :
>>
>> ERROR:  catalog is missing 11 attribute(s) for relid 113971
>>
>> Could you help us to solve this problem? or could you give contact who
>> can help with this?
>>
>> Thanks in advance.
>>
>
> First of all, please attend to these instructions:
> https://wiki.postgresql.org/wiki/Corruption
>
> from what you describe looks like you need to restore from backups or
> promote a slave to master if you have such a setup and the slave(s) were
> not affected by these hardware problems.
>
> --
> El genio es 1% inspiración y 99% transpiración.
> Thomas Alva Edison
> http://pglearn.blogspot.mx/
>
>

-- 
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/

Re: Fwd: Postgresql Data corruption

[Adrian Klaver]

On 1/14/20 12:44 PM, Tulqin Navruzov wrote:



Hi team

Could you help us to sort this out please?

We had some hardware problems with Datacenter and could not using 
postgresql from restored snapshots it showing "Structure needs to be 
cleaning " during startup
, on centos7 did xfs_repair and postgresql started successfully. But 
could not select datas from it . showing below message .


This is when want to login to db with postgres user .
psql: PANIC:  could not open critical system index 2662

but can logged in with another user and trying to select from some 
tables , showing this message :


ERROR:  catalog is missing 11 attribute(s) for relid 113971

Could you help us to solve this problem? or could you give contact who 
can help with this?


Do you have space to back up the $DATADIR or do you have a recent backup?

Asking because that would be helpful before you try the below.

I would take a look at:
https://www.postgresql.org/docs/12/sql-reindex.html

In section:

Notes

...

Things are more difficult if you need to recover from corruption of an 
index on a system table. ...


For more information on single-user mode:

https://www.postgresql.org/docs/12/app-postgres.html#APP-POSTGRES-SINGLE-USER



Thanks in advance.



--
Adrian Klaver
adrian.kla...@aklaver.com

Lock leaking out of Transaction?

[James Sewell]

Hi all,

I am trying to chase down a locking issue - it looks like a materialized
view refresh is being held up by a relation  lock which is held by an out
of transaction session. My understanding was that this was not possible
(see SQL output below).

The locking session is making progress (I can see query_start advancing),
which makes it even more confusing.

Any advice?

# select * from pg_locks l join pg_stat_activity a on l.pid = a.pid where
relation = 1438729::regclass;
-[ RECORD 1
]--+-
locktype | relation
database | 16428
relation | 1438729
page |
tuple |
virtualxid |
transactionid |
classid |
objid |
objsubid |
virtualtransaction | 3/26588281
pid | 88955
mode | ShareUpdateExclusiveLock
granted | f
fastpath | f
datid | 16428
datname | monitoring
pid | 88955
usesysid |
usename |
application_name |
client_addr |
client_hostname |
client_port |
backend_start | 14-JAN-20 11:50:25.139819 +11:00
xact_start | 14-JAN-20 16:27:40.534726 +11:00
query_start | 14-JAN-20 16:27:40.534726 +11:00
state_change | 14-JAN-20 16:27:40.534726 +11:00
wait_event_type | Lock
wait_event | relation
state | active
backend_xid |
backend_xmin | 1655752595
query | autovacuum: VACUUM supply_nodes (to prevent wraparound)
backend_type | autovacuum worker
-[ RECORD 2
]--+-
locktype | relation
database | 16428
relation | 1438729
page |
tuple |
virtualxid |
transactionid |
classid |
objid |
objsubid |
virtualtransaction | 254/8624453
pid | 6839
mode | ExclusiveLock
granted | f
fastpath | f
datid | 16428
datname | monitoring
pid | 6839
usesysid | 10
usename | postgres
application_name | psql.bin
client_addr |
client_hostname |
client_port | -1
backend_start | 14-JAN-20 17:02:53.860451 +11:00
xact_start | 14-JAN-20 18:01:49.211728 +11:00
query_start | 14-JAN-20 18:01:49.211728 +11:00
state_change | 14-JAN-20 18:01:49.21173 +11:00
wait_event_type | Lock
wait_event | relation
state | active
backend_xid |
backend_xmin | 1689815577
query | REFRESH MATERIALIZED VIEW CONCURRENTLY supply_nodes ;
backend_type | client backend
-[ RECORD 3
]--+-
locktype | relation
database | 16428
relation | 1438729
page |
tuple |
virtualxid |
transactionid |
classid |
objid |
objsubid |
virtualtransaction | 355/0
pid | 65447
mode | ExclusiveLock
granted | t
fastpath | f
datid | 16428
datname | monitoring
pid | 65447
usesysid | 169436
usename | f_process
application_name | PostgreSQL JDBC Driver
client_addr | 10.153.154.36
client_hostname |
client_port | 40899
backend_start | 14-JAN-20 18:00:02.784211 +11:00
xact_start |
query_start | 14-JAN-20 18:02:26.831979 +11:00
state_change | 14-JAN-20 18:02:26.833197 +11:00
wait_event_type | Client
wait_event | ClientRead
state | idle
backend_xid |
backend_xmin |
query | COMMIT
backend_type | client backend

James Sewell,

-- 
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: Fwd: Postgresql Data corruption

[Michael Paquier]

On Tue, Jan 14, 2020 at 02:34:04PM -0800, Adrian Klaver wrote:
> On 1/14/20 12:44 PM, Tulqin Navruzov wrote:
>> but can logged in with another user and trying to select from some
>> tables , showing this message :
>> 
>> ERROR:  catalog is missing 11 attribute(s) for relid 113971
>> 
>> Could you help us to solve this problem? or could you give contact who
>> can help with this?
> 
> Do you have space to back up the $DATADIR or do you have a recent backup?
> 
> Asking because that would be helpful before you try the below.
> 
> I would take a look at:
> https://www.postgresql.org/docs/12/sql-reindex.html

That's a bad data corruption pattern, and rolling in a backup is
recommended (make sure that they are actually safe to use!).  A
REINDEX should help you to retrieve data and move it somewhere else if
you don't have a backup, but nothing more as there is no guarantee
that other parts of the system are not broken.  As others have already
pointed out, take the time necessary to read that:
https://wiki.postgresql.org/wiki/Corruption

But first, you should take three, slow, deep breaths.  Rushing can
only make things worse.
--
Michael


signature.asc
Description: PGP signature

Re: Lock leaking out of Transaction?

[Laurenz Albe]

On Wed, 2020-01-15 at 10:42 +1100, James Sewell wrote:
> I am trying to chase down a locking issue - it looks like a materialized view 
> refresh is being
> held up by a relation  lock which is held by an out of transaction session. 
> My understanding was that
> this was not possible (see SQL output below).
> 
> The locking session is making progress (I can see query_start advancing), 
> which makes it even more confusing.
> 
> Any advice?
> 
> # select * from pg_locks l join pg_stat_activity a on l.pid = a.pid where 
> relation = 1438729::regclass;
> -[ RECORD 1 
> ]--+-
> locktype | relation
> database | 16428
> relation | 1438729
> mode | ShareUpdateExclusiveLock
> granted | f
> fastpath | f
> pid | 88955
> wait_event_type | Lock
> wait_event | relation
> state | active
> query | autovacuum: VACUUM supply_nodes (to prevent wraparound)
> backend_type | autovacuum worker
> -[ RECORD 2 
> ]--+-
> locktype | relation
> database | 16428
> relation | 1438729
> mode | ExclusiveLock
> granted | f
> pid | 6839
> wait_event_type | Lock
> wait_event | relation
> state | active
> query | REFRESH MATERIALIZED VIEW CONCURRENTLY supply_nodes ;
> backend_type | client backend
> -[ RECORD 3 
> ]--+-
> locktype | relation
> database | 16428
> relation | 1438729
> mode | ExclusiveLock
> granted | t
> pid | 65447
> application_name | PostgreSQL JDBC Driver
> wait_event_type | Client
> wait_event | ClientRead
> state | idle
> query | COMMIT
> backend_type | client backend

I cannot explain that either; could it be shared memory corruption?

What I would try is

   SELECT pg_terminate_backend(65447);

and see if the session and its lock go away.

If that does not do the trick, I would restart PostgreSQL, which should get
rid of any possible memory corruption.

Then perhaps the anti-wraparoung autovacuum can succeed.
This autovacuum would also block you, but you should let it finish, since
it is an important system task.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Multiple Aggregations Order

[João Haas]

I also thought about that. The 'issue', is that when you call array_agg in
a row/type, it casts the entire thing in a string. But some of the
aggregation fields are jsonb fields, which are also casted to strings, with
extra escapes. Then, I'm also using Django, which seems to mess the string
even more. I could try to denormalize this whole mess, but I don't think
the overhead of sorting each agg is enough for this. Also, Django handles
these extra fields really well on raw queries, so it's not an issue having
a lot of fields.

On Tue, Jan 14, 2020 at 5:43 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Tuesday, January 14, 2020, João Haas  wrote:
>>
>> SELECT tb.*, array_agg(conn.child_id), array_agg(conn.kind)
>>
>>
> Create a custom type (using row(...) might work...?) with the relevant
> fields and “...array_agg((child_id, kind)::custom_type order by ...”?
>
> David J.
>

Lock acquisition for partition table when setting generic plan

[yotsunaga.na...@fujitsu.com]

Hi all,

I have a question.

I did the following.
Even though I accessed one partition table(test2 table), I also acquired locks 
on other partition tables(test1 table).
I expected to acquire locks on the parent table(test table) and the partition 
table to access(test2 table).
Why does this happen?

At the first execution, to create a generic plan, I thought it was accessing 
all partition tables.
However, the following event occur after second time too.
* Only occurs when plan_cache_mode = force_generic_plan.

postgres=# create table test(id int) partition by range (id);
CREATE TABLE
postgres=# create table test1 partition of test for values from (1) to (2);
CREATE TABLE
postgres=# create table test2 partition of test for values from (2) to (3);
CREATE TABLE
postgres=# prepare hoge(int) as select * from test where id = $1;
PREPARE
postgres=# set plan_cache_mode = force_generic_plan ;
SET
postgres=# begin;
BEGIN
postgres=# execute hoge(2);
 id 

(0 rows)

postgres=# SELECT 
l.pid,l.granted,d.datname,l.locktype,relation,relation::regclass,transactionid,l.mode
 FROM pg_locks l  LEFT JOIN pg_database d ON l.database = d.oid WHERE  l.pid != 
pg_backend_pid() ORDER BY l.pid;
  pid  | granted | datname  |  locktype  | relation | relation | transactionid 
|  mode   
---+-+--++--+--+---+-
 16921 | t   | postgres | relation   |16562 | test2|   
| AccessShareLock
 16921 | t   | postgres | relation   |16559 | test1|   
| AccessShareLock
 16921 | t   | postgres | relation   |16556 | test |   
| AccessShareLock
 16921 | t   |  | virtualxid |  |  |   
| ExclusiveLock
(4 rows)
 
Regards
Naoki Yotsunaga