Re: how to slow down parts of Pg

[Virendra Kumar]

Hi Adrian,

Here is test case, basically when autovacuum runs it did release the space to 
disk since it had may be continuous blocks which can be released to disk but 
the space used by index is still being held until I ran the reindex on the 
table (I assume reindex for index would work as well). Subsequent insert 
statement did not utilize the blocks in index segment as we can see below so 
index bloats are still not addressed or may be I am doing something wrong:

postgres=# select version();
 version
     
-
 PostgreSQL 12.2 on x86_64-apple-darwin18.7.0, compiled by Apple LLVM version 
10.0.1 (clang-1001.0.46.4), 64-bit
(1 row)

postgres=# 
postgres=# CREATE TABLE validate_pg_repack
postgres-# (
postgres(#    effectivedate  timestamp,
postgres(#    masterentityid integer not null,
postgres(#    primaryissueid varchar(65535),
postgres(#    longshortindicator varchar(65535),
postgres(#    pg_repack_id   varchar(65535)
postgres(# );
CREATE TABLE
postgres=# CREATE SEQUENCE validate_pg_repack_masterentityid_seq INCREMENT 1 
START 1 OWNED BY validate_pg_repack.masterentityid;
CREATE SEQUENCE
postgres=# 
postgres=# CREATE unique INDEX idx_pg_repack_masterentityid ON 
validate_pg_repack USING btree (masterentityid);
CREATE INDEX
postgres=# 
postgres=# CREATE INDEX idx_pg_repack_effectivedate ON validate_pg_repack USING 
btree (effectivedate);
CREATE INDEX
postgres=# 
postgres=# INSERT INTO validate_pg_repack 
(effectivedate,masterentityid,primaryissueid,longshortindicator,pg_repack_id) 
SELECT 
postgres-# now() + round(random() * 1000) * '1 second' :: interval, 
postgres-# nextval('validate_pg_repack_masterentityid_seq'), 
postgres-# 'some-phone-' || round(random() * 65000),
postgres-# 'some-phone-' || round(random() * 1000),  
postgres-# 'some-phone-' || round(random() * 1000)
postgres-# FROM
postgres-# generate_series(1, 90);
INSERT 0 90
postgres=# 
postgres=# select pg_sleep(30);
 pg_sleep 
--
 
(1 row)

postgres=# select 
relname,n_tup_ins,n_tup_del,last_autoanalyze,autoanalyze_count from 
pg_stat_all_tables where relname ='validate_pg_repack';
  relname   | n_tup_ins | n_tup_del |   last_autoanalyze    | 
autoanalyze_count 
+---+---+---+---
 validate_pg_repack |    90 | 0 | 2020-04-21 19:34:09.579475-07 |   
  1
(1 row)

postgres=# select pg_size_pretty(pg_relation_size('validate_pg_repack'));
 pg_size_pretty 

 80 MB
(1 row)

postgres=# \di+ idx_pg_repack_*
  List of relations
 Schema | Name | Type  |  Owner   |   Table    
| Size  | Description 
+--+---+--++---+-
 public | idx_pg_repack_effectivedate  | index | vvikumar | validate_pg_repack 
| 24 MB | 
 public | idx_pg_repack_masterentityid | index | vvikumar | validate_pg_repack 
| 19 MB | 
(2 rows)

postgres=# delete from validate_pg_repack where masterentityid > 45;
DELETE 45
postgres=# select pg_sleep(30);
 pg_sleep 
--
 
(1 row)

postgres=# select 
relname,n_tup_ins,n_tup_del,last_autoanalyze,autoanalyze_count from 
pg_stat_all_tables where relname ='validate_pg_repack';
  relname   | n_tup_ins | n_tup_del |   last_autoanalyze    | 
autoanalyze_count 
+---+---+---+---
 validate_pg_repack |    90 |    45 | 2020-04-21 19:35:11.029405-07 |   
  2
(1 row)

postgres=# 
postgres=# select pg_size_pretty(pg_relation_size('validate_pg_repack'));
 pg_size_pretty 

 40 MB
(1 row)

postgres=# \di+ idx_pg_repack_*
  List of relations
 Schema | Name | Type  |  Owner   |   Table    
| Size  | Description 
+--+---+--++---+-
 public | idx_pg_repack_effectivedate  | index | vvikumar | validate_pg_repack 
| 24 MB | 
 public | idx_pg_repack_masterentityid | index | vvikumar | validate_pg_repack 
| 19 MB | 
(2 rows)

postgres=# INSERT INTO validate_pg_repack 
(effectivedate,masterentityid,primaryissueid,longshortindicator,pg_repack_id) 
SELECT 
postgres-# now() + round(random() * 1000) * '1 second' :: interval, 
postgres-# nextval('validate_pg_repack_masterentityid_seq'), 
postgres-# 'some-phone-' || round(random() * 65000),
postgres-# 'some-phone-' || round(random() * 1000),  
postgres-# 'some-phone-' || 

Re: Connection Refused

[Adrian Klaver]

On 4/21/20 4:56 PM, Dummy Account wrote:

Hello,
I installed pgAdmin4, I believe the postgesSQL version is 12.  I'm 
running Mac OS X High Sierra 10.13.6.
After logging into pgAdmin and successfully entering "master password". 
  While clicking on the only instance there, in this case it is 
"PostgreSQL 12" that is where I get the following error:


could not connect to server: Connection refused Is the server running on 
host "localhost" (::1) and accepting TCP/IP connections on port 5432? 
could not connect to server: Connection refused Is the server running on 
host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5432?


Looks like the Postgres server is not running.

Can you confirm whether the server is up?



I can say that a multitude of programs that were accessing this database 
can no-longer access the database; they all run the same password needed 
to access this cluster, I think it is a cluster.  I tried right-clicking 
"PostgreSQL 12" and clearing stored password, so I think I will need to 
reset that if you could advise on that too.


Lastly, I will also add that I recently restored my Operating System 
from a backup because I swapped a HDD out for a SSD.


Please see screenshot for reference.

Thanks




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

Re: how to slow down parts of Pg

[Adrian Klaver]

On 4/21/20 2:32 PM, Virendra Kumar wrote:
Autovacuum does takes care of dead tuples and return space to table's 
allocated size and can be re-used by fresh incoming rows or any updates.


Index bloat is still not being taken care of by autovacuum process. You 
should use pg_repack to do index rebuild. Keep in mind that pg_repack 
requires double the space of indexes, since there will be two indexes 
existing during rebuild processes.


You sure about that? On Postgres 12:

--2020-04-21 15:47:27.452 PDT-0DEBUG:  plant1: vac: 5154 (threshold 
1081), anl: 5154 (threshold 565)
--2020-04-21 15:47:27.452 PDT-0DEBUG:  autovac_balance_cost(pid=18701 
db=25092, rel=26497, dobalance=yes cost_limit=200, cost_limit_base=200, 
cost_delay=2)
--2020-04-21 15:47:27.452 PDT-0DEBUG:  CommitTransaction(1) name: 
unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
--2020-04-21 15:47:27.452 PDT-0DEBUG:  StartTransaction(1) name: 
unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0

--2020-04-21 15:47:27.452 PDT-0DEBUG:  vacuuming "public.plant1"
--2020-04-21 15:47:27.504 PDT-0DEBUG:  scanned index "p_no_pkey" to 
remove 5114 row versions
--2020-04-21 15:47:27.504 PDT-0DETAIL:  CPU: user: 0.00 s, system: 0.00 
s, elapsed: 0.00 s
--2020-04-21 15:47:27.514 PDT-0DEBUG:  scanned index "common_idx" to 
remove 5114 row versions
--2020-04-21 15:47:27.514 PDT-0DETAIL:  CPU: user: 0.00 s, system: 0.00 
s, elapsed: 0.01 s
--2020-04-21 15:47:27.515 PDT-0DEBUG:  scanned index "genus_idx" to 
remove 5114 row versions
--2020-04-21 15:47:27.515 PDT-0DETAIL:  CPU: user: 0.00 s, system: 0.00 
s, elapsed: 0.00 s
--2020-04-21 15:47:27.517 PDT-0DEBUG:  "plant1": removed 5114 row 
versions in 121 pages
--2020-04-21 15:47:27.517 PDT-0DETAIL:  CPU: user: 0.00 s, system: 0.00 
s, elapsed: 0.00 s
--2020-04-21 15:47:27.517 PDT-0DEBUG:  index "p_no_pkey" now contains 
5154 row versions in 31 pages
--2020-04-21 15:47:27.517 PDT-0DETAIL:  5114 index row versions were 
removed.

0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
--2020-04-21 15:47:27.517 PDT-0DEBUG:  index "common_idx" now contains 
5154 row versions in 60 pages
--2020-04-21 15:47:27.517 PDT-0DETAIL:  5114 index row versions were 
removed.

0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
--2020-04-21 15:47:27.517 PDT-0DEBUG:  index "genus_idx" now contains 
5154 row versions in 47 pages
--2020-04-21 15:47:27.517 PDT-0DETAIL:  5114 index row versions were 
removed.

0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
--2020-04-21 15:47:27.518 PDT-0DEBUG:  "plant1": found 5154 removable, 
5154 nonremovable row versions in 195 out of 195 pages
--2020-04-21 15:47:27.518 PDT-0DETAIL:  0 dead row versions cannot be 
removed yet, oldest xmin: 9715

There were 256 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.06 s.
--2020-04-21 15:47:27.518 PDT-0LOG:  automatic vacuum of table 
"production.public.plant1": index scans: 1
pages: 0 removed, 195 remain, 0 skipped due to pins, 0 skipped 
frozen
tuples: 5154 removed, 5154 remain, 0 are dead but not yet 
removable, oldest xmin: 9715

buffer usage: 753 hits, 0 misses, 255 dirtied
avg read rate: 0.000 MB/s, avg write rate: 30.586 MB/s
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.06 s





Regards,
Virendra Kumar



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

Re: how to slow down parts of Pg

[Michael Lewis]

Reviewing pg_stat_user_tables will give you an idea of how often autovacuum
is cleaning up those tables that "need" that vacuum full on a quarterly
basis. You can tune individual tables to have a lower threshold ratio of
dead tuples so the system isn't waiting until you have 20% dead rows before
vacuuming a table with millions of rows that occupies a GB or more on disk.
You might consider changing your nightly analyze to a nightly vacuum
analyze, at least for the tables you know can be problematic. The more
dense a table is packed, the better cache_hits and other such metrics. Like
making dinner, cleanup as you go.

One thing that I think is interesting is that the default cost_delay has
been updated with PG12 from 20ms down to 2ms such that all things being
equal, much much more work is done by autovacuum in a given second. It may
be worth taking a look at.

Another great thing coming to you in PG12 is the option to do reindex
concurrently. Then there's no need for pg_repack on indexes.

Good luck sir.

RE: how to slow down parts of Pg

[Kevin Brannen]

From: Virendra Kumar 

>Autovacuum does takes care of dead tuples and return space to table's 
>allocated size and can be re-used by fresh incoming rows or any updates.
>
>Index bloat is still not being taken care of by autovacuum process. You should 
>use pg_repack to do index rebuild. Keep in mind that pg_repack requires double 
>the space of indexes, since there will be two indexes existing during rebuild 
>processes.


Ha! I knew there was a reason I was doing the full, I just couldn't remember 
indexes was why. Pg_repack needs to move higher on the ToDo list too. I need a 
clone to do all of this. :)


From: David G. Johnston 
> Imagine you have an auto-expanding array and also that individual cells can 
> be reused if the data in them is removed first…

Yes, the concepts aren't that hard, the issue is how to apply them in the most 
effective manner. Still, nice explanation, I'll use that when explaining the 
work to the group so I can pass the info along.

Thanks!
Kevin
This e-mail transmission, and any documents, files or previous e-mail messages 
attached to it, may contain confidential information. If you are not the 
intended recipient, or a person responsible for delivering it to the intended 
recipient, you are hereby notified that any disclosure, distribution, review, 
copy or use of any of the information contained in or attached to this message 
is STRICTLY PROHIBITED. If you have received this transmission in error, please 
immediately notify us by reply e-mail, and destroy the original transmission 
and its attachments without reading them or saving them to disk. Thank you.

Re: how to slow down parts of Pg

[David G. Johnston]

On Tue, Apr 21, 2020 at 2:25 PM Kevin Brannen  wrote:

> Sometimes I need the disk space back. It also makes me feel better. (OK,
> this may not a good reason but there is a hint of truth in this.) What this
> probably means is that I need to get a better understanding of vacuuming.
>

Imagine you have an auto-expanding array and also that individual cells can
be reused if the data in them is removed first.  Deletion marks a cell as
needing to be cleaned up (i.e., data removed).  Vacuum actually performs
the cleaning.  Insertion causes the system to either provide an existing,
cleaned/empty, cell OR to add a new cell to the end of the array and
provide that.  The longer the deleted cells go uncleaned the more cells
that are added onto the end of the array and the more physical space the
array takes up.  If you clean up the deleted cells more frequently they can
be reused in lieu of expanding the array.

Vacuum full counts the number of non-empty cells in the array, creates a
new array with that many cells, and writes the non-empty values into it -
then removes the old array.

Immediately after you perform a normal vacuum you have lots of empty cells
- but you know that the space is going to be filled in again soon so it
doesn't normally make sense to "resize the array".

David J.

Re: how to slow down parts of Pg

[Virendra Kumar]

Autovacuum does takes care of dead tuples and return space to table's allocated 
size and can be re-used by fresh incoming rows or any updates. 

Index bloat is still not being taken care of by autovacuum process. You should 
use pg_repack to do index rebuild. Keep in mind that pg_repack requires double 
the space of indexes, since there will be two indexes existing during rebuild 
processes.

Regards,
Virendra Kumar
On Tuesday, April 21, 2020, 2:26:11 PM PDT, Kevin Brannen 
 wrote:  
 
 
From: Michael Loftis 
 
  
 
>>From: Kevn Brannen
 
>> I don't particularly like doing the vacuum full, but when it will release 
>> 20-50% of disk space for a large table, then it's something we live with. As 
>> I understand, a normal vacuum won't release all the old pages that a "full" 
>> does, hence why we have to do that. It's painful enough I've restricted it 
>> to once quarter; I'd do it only once a year if I thought I could get away 
>> with it. Still this is something I'll put on the list to go research with 
>> practical trials. I don't think the lock for the vacuuming hurts us, but 
>> I've heard of pg_repack and I'll look into that too.
 
  
 
  
 
> Why do vacuum full at all? A functional autovacuum will return the free pages 
> to be reused. You just won’t see the reduction in disk usage at the OS level. 
> Since the pages are clearly going to be used it doesn’t really make sense to 
> do a vacuum full at all. Let autovacuum do it’s job or if that’s not keeping 
> up a normal vacuum without the full. The on dusk sizes will stabilize and 
> you’ll not be doing a ton of extra I/O to rewrite tables.
 
  
 
  
 
Sometimes I need the disk space back. It also makes me feel better. (OK, this 
may not a good reason but there is a hint of truth in this.) What this probably 
means is that I need to get a better understanding of vacuuming.

  
 
Thanks!
 
Kevin
 This e-mail transmission, and any documents, files or previous e-mail messages 
attached to it, may contain confidential information. If you are not the 
intended recipient, or a person responsible for delivering it to the intended 
recipient, you are hereby notified that any disclosure, distribution, review, 
copy or use of any of the information contained in or attached to this message 
is STRICTLY PROHIBITED. If you have received this transmission in error, please 
immediately notify us by reply e-mail, and destroy the original transmission 
and its attachments without reading them or saving them to disk. Thank you.  

RE: how to slow down parts of Pg

[Kevin Brannen]

From: Michael Loftis 

>>From: Kevn Brannen
>> I don't particularly like doing the vacuum full, but when it will release 
>> 20-50% of disk space for a large table, then it's something we live with. As 
>> I understand, a normal vacuum won't release all the old pages that a "full" 
>> does, hence why we have to do that. It's painful enough I've restricted it 
>> to once quarter; I'd do it only once a year if I thought I could get away 
>> with it. Still this is something I'll put on the list to go research with 
>> practical trials. I don't think the lock for the vacuuming hurts us, but 
>> I've heard of pg_repack and I'll look into that too.


> Why do vacuum full at all? A functional autovacuum will return the free pages 
> to be reused. You just won’t see the reduction in disk usage at the OS level. 
> Since the pages are clearly going to be used it doesn’t really make sense to 
> do a vacuum full at all. Let autovacuum do it’s job or if that’s not keeping 
> up a normal vacuum without the full. The on dusk sizes will stabilize and 
> you’ll not be doing a ton of extra I/O to rewrite tables.


Sometimes I need the disk space back. It also makes me feel better. (OK, this 
may not a good reason but there is a hint of truth in this.) What this probably 
means is that I need to get a better understanding of vacuuming.

Thanks!
Kevin
This e-mail transmission, and any documents, files or previous e-mail messages 
attached to it, may contain confidential information. If you are not the 
intended recipient, or a person responsible for delivering it to the intended 
recipient, you are hereby notified that any disclosure, distribution, review, 
copy or use of any of the information contained in or attached to this message 
is STRICTLY PROHIBITED. If you have received this transmission in error, please 
immediately notify us by reply e-mail, and destroy the original transmission 
and its attachments without reading them or saving them to disk. Thank you.

RE: how to slow down parts of Pg

[Kevin Brannen]

From: Michael Loftis 

>>From: Kevn Brannen
>>I have an unusual need:  I need Pg to slow down. I know, we all want our DB 
>>to go faster, but in this case it's speed is working against me in 1 area.
>>
>>We have systems that are geo-redundant for HA, with the redundancy being 
>>handled by DRBD to keep the disks in sync...

> drbdsetup allows you to control the sync rates.

I was hoping not to have to do that, but the more I think about this I'm 
realizing that it won't hurt because the network cap is effectively limiting me 
anyway. :)

I can & will do this, maybe at 90% of our bandwidth, so thanks for the 
suggestion. Still, this is sort of a last resort thing as I believe controlling 
the DB to be the ultimate need.

Thanks!
Kevin
.
This e-mail transmission, and any documents, files or previous e-mail messages 
attached to it, may contain confidential information. If you are not the 
intended recipient, or a person responsible for delivering it to the intended 
recipient, you are hereby notified that any disclosure, distribution, review, 
copy or use of any of the information contained in or attached to this message 
is STRICTLY PROHIBITED. If you have received this transmission in error, please 
immediately notify us by reply e-mail, and destroy the original transmission 
and its attachments without reading them or saving them to disk. Thank you.

Re: how to slow down parts of Pg

[Michael Loftis]

On Tue, Apr 21, 2020 at 15:05 Kevin Brannen  wrote:

> *From:* Michael Lewis 
>
> > You say 12.2 is in testing but what are you using now? Have you tuned
> configs much? Would you be able to implement partitioning such that your
> deletes become truncates or simply a detaching of the old partition?
> Generally if you are doing a vacuum full, you perhaps need to tune
> autovacuum to be more aggressive. Consider pg_repack at least to avoid
> taking an exclusive lock for the entire duration. If partitioning is not an
> option, could you delete old records hourly rather than daily?
>
>
>
> Good questions, it's always hard to know how much to include. 
>
>
>
> Current production is 9.6, so things like partitioning aren't available
> there, but will be in the future.
>
>
>
> We've tuned the configs some and don't having any issues with Pg at the
> moment. This does need to be relooked at; I have a few notes of things to
> revisit as our hardware changes.
>
>
>
> Partitioning our larger tables by time is on the ToDo list. I hadn't
> thought about that helping with maintenance, so thanks for bringing that
> up. I'll increase the priority of this work as I can see this helping with
> the archiving part.
>
>
>
> I don't particularly like doing the vacuum full, but when it will release
> 20-50% of disk space for a large table, then it's something we live with.
> As I understand, a normal vacuum won't release all the old pages that a
> "full" does, hence why we have to do that. It's painful enough I've
> restricted it to once quarter; I'd do it only once a year if I thought I
> could get away with it. Still this is something I'll put on the list to go
> research with practical trials. I don't think the lock for the vacuuming
> hurts us, but I've heard of pg_repack and I'll look into that too.
>


Why do vacuum full at all? A functional autovacuum will return the free
pages to be reused. You just won’t see the reduction in disk usage at the
OS level. Since the pages are clearly going to be used it doesn’t really
make sense to do a vacuum full at all. Let autovacuum do it’s job or if
that’s not keeping up a normal vacuum without the full. The on dusk sizes
will stabilize and you’ll not be doing a ton of extra I/O to rewrite tables.

>
>
> I have considered (like they say with vacuuming) that more often might be
> better. Of course that would mean doing some of this during the day when
> the DB is busier. Hmm, maybe 1000/minute wouldn't hurt and that would
> shorten the nightly run significantly. I may have to try that and see if it
> just adds to background noise or causes problems.
>
>
>
> Thanks!
>
> Kevin
> This e-mail transmission, and any documents, files or previous e-mail
> messages attached to it, may contain confidential information. If you are
> not the intended recipient, or a person responsible for delivering it to
> the intended recipient, you are hereby notified that any disclosure,
> distribution, review, copy or use of any of the information contained in or
> attached to this message is STRICTLY PROHIBITED. If you have received this
> transmission in error, please immediately notify us by reply e-mail, and
> destroy the original transmission and its attachments without reading them
> or saving them to disk. Thank you.
>
-- 

"Genius might be described as a supreme capacity for getting its possessors
into trouble of all kinds."
-- Samuel Butler

RE: how to slow down parts of Pg

[Kevin Brannen]

From: Michael Lewis 

> You say 12.2 is in testing but what are you using now? Have you tuned configs 
> much? Would you be able to implement partitioning such that your deletes 
> become truncates or simply a detaching of the old partition? Generally if you 
> are doing a vacuum full, you perhaps need to tune autovacuum to be more 
> aggressive. Consider pg_repack at least to avoid taking an exclusive lock for 
> the entire duration. If partitioning is not an option, could you delete old 
> records hourly rather than daily?

Good questions, it's always hard to know how much to include. 

Current production is 9.6, so things like partitioning aren't available there, 
but will be in the future.

We've tuned the configs some and don't having any issues with Pg at the moment. 
This does need to be relooked at; I have a few notes of things to revisit as 
our hardware changes.

Partitioning our larger tables by time is on the ToDo list. I hadn't thought 
about that helping with maintenance, so thanks for bringing that up. I'll 
increase the priority of this work as I can see this helping with the archiving 
part.

I don't particularly like doing the vacuum full, but when it will release 
20-50% of disk space for a large table, then it's something we live with. As I 
understand, a normal vacuum won't release all the old pages that a "full" does, 
hence why we have to do that. It's painful enough I've restricted it to once 
quarter; I'd do it only once a year if I thought I could get away with it. 
Still this is something I'll put on the list to go research with practical 
trials. I don't think the lock for the vacuuming hurts us, but I've heard of 
pg_repack and I'll look into that too.

I have considered (like they say with vacuuming) that more often might be 
better. Of course that would mean doing some of this during the day when the DB 
is busier. Hmm, maybe 1000/minute wouldn't hurt and that would shorten the 
nightly run significantly. I may have to try that and see if it just adds to 
background noise or causes problems.

Thanks!
Kevin
This e-mail transmission, and any documents, files or previous e-mail messages 
attached to it, may contain confidential information. If you are not the 
intended recipient, or a person responsible for delivering it to the intended 
recipient, you are hereby notified that any disclosure, distribution, review, 
copy or use of any of the information contained in or attached to this message 
is STRICTLY PROHIBITED. If you have received this transmission in error, please 
immediately notify us by reply e-mail, and destroy the original transmission 
and its attachments without reading them or saving them to disk. Thank you.

Re: how to slow down parts of Pg

[Michael Loftis]

drbdsetup allows you to control the sync rates.

On Tue, Apr 21, 2020 at 14:30 Kevin Brannen  wrote:

> I have an unusual need:  I need Pg to slow down. I know, we all want our
> DB to go faster, but in this case it's speed is working against me in 1
> area.
>
>
>
> We have systems that are geo-redundant for HA, with the redundancy being
> handled by DRBD to keep the disks in sync, which it does at the block
> level. For normal operations, it actually works out fairly well. That said,
> we recognize that what we really need to do is one of the forms of
> streaming (ch 26 of the manual) which I believe would help this problem a
> lot if not solve it -- but we don't have the time to do that at the moment.
> I plan and hope to get there by the end of the year. The part that hurts so
> bad is when we do maintenance operations that are DB heavy, like deleting
> really old records out of archives (weekly), moving older records from
> current tables to archive tables plus an analyze (every night), running
> pg_backup (every night), other archiving (weekly), and vacuum full to
> remove bloat (once a quarter). All of this generates a lot of disk writes,
> to state the obvious.
>
>
>
> The local server can handle it all just fine, but the network can't handle
> it as it tries to sync to the other server. Sometimes we can add network
> bandwidth, many times we can't as it depends on others. To borrow a phrase
> from the current times, we need to flatten the curve. 
>
>
>
> A few parts of our maintenance process I've tamed by doing "nice -20" on
> the process (e.g. log rotation); but I can't really do that for Pg because
> the work gets handed off to a background process that's not a direct child
> process … and I don't want to slow the DB as a whole because other work is
> going on (like handling incoming data).
>
>
>
> Part of the process I've slowed down by doing the work in chunks of 10K
> rows at a time with a pause between each chunk to allow the network to
> catch up (instead of an entire table in 1 statement). This sort of works,
> but some work/SQL is between hard to next-to-impossible to break up like
> that. That also produces some hard spikes, but that's better than the
> alternative (next sentence). Still, large portions of the process are hard
> to control and just punch the network to full capacity and hold it there
> for far too long.
>
>
>
> So, do I have any other options to help slow down some of the Pg
> operations? Or maybe some other short-term mitigations we can do with Pg
> configurations? Or is this a case where we've already done all we can do
> and the only answer is move to WAL streaming as fast as possible?
>
>
>
> If it matters, this is being run on Linux servers. Pg 12.2 is in final
> testing and will be rolled out to production soon -- so feel free to offer
> suggestions that only apply to 12.x.
>
>
>
> Thanks,
>
> Kevin
> This e-mail transmission, and any documents, files or previous e-mail
> messages attached to it, may contain confidential information. If you are
> not the intended recipient, or a person responsible for delivering it to
> the intended recipient, you are hereby notified that any disclosure,
> distribution, review, copy or use of any of the information contained in or
> attached to this message is STRICTLY PROHIBITED. If you have received this
> transmission in error, please immediately notify us by reply e-mail, and
> destroy the original transmission and its attachments without reading them
> or saving them to disk. Thank you.
>
-- 

"Genius might be described as a supreme capacity for getting its possessors
into trouble of all kinds."
-- Samuel Butler

Re: how to slow down parts of Pg

[Michael Lewis]

You say 12.2 is in testing but what are you using now? Have you tuned
configs much? Would you be able to implement partitioning such that your
deletes become truncates or simply a detaching of the old partition?
Generally if you are doing a vacuum full, you perhaps need to tune
autovacuum to be more aggressive. Consider pg_repack at least to avoid
taking an exclusive lock for the entire duration. If partitioning is not an
option, could you delete old records hourly rather than daily?

>

how to slow down parts of Pg

[Kevin Brannen]

I have an unusual need:  I need Pg to slow down. I know, we all want our DB to 
go faster, but in this case it's speed is working against me in 1 area.

We have systems that are geo-redundant for HA, with the redundancy being 
handled by DRBD to keep the disks in sync, which it does at the block level. 
For normal operations, it actually works out fairly well. That said, we 
recognize that what we really need to do is one of the forms of streaming (ch 
26 of the manual) which I believe would help this problem a lot if not solve it 
-- but we don't have the time to do that at the moment. I plan and hope to get 
there by the end of the year. The part that hurts so bad is when we do 
maintenance operations that are DB heavy, like deleting really old records out 
of archives (weekly), moving older records from current tables to archive 
tables plus an analyze (every night), running pg_backup (every night), other 
archiving (weekly), and vacuum full to remove bloat (once a quarter). All of 
this generates a lot of disk writes, to state the obvious.

The local server can handle it all just fine, but the network can't handle it 
as it tries to sync to the other server. Sometimes we can add network 
bandwidth, many times we can't as it depends on others. To borrow a phrase from 
the current times, we need to flatten the curve. 

A few parts of our maintenance process I've tamed by doing "nice -20" on the 
process (e.g. log rotation); but I can't really do that for Pg because the work 
gets handed off to a background process that's not a direct child process … and 
I don't want to slow the DB as a whole because other work is going on (like 
handling incoming data).

Part of the process I've slowed down by doing the work in chunks of 10K rows at 
a time with a pause between each chunk to allow the network to catch up 
(instead of an entire table in 1 statement). This sort of works, but some 
work/SQL is between hard to next-to-impossible to break up like that. That also 
produces some hard spikes, but that's better than the alternative (next 
sentence). Still, large portions of the process are hard to control and just 
punch the network to full capacity and hold it there for far too long.

So, do I have any other options to help slow down some of the Pg operations? Or 
maybe some other short-term mitigations we can do with Pg configurations? Or is 
this a case where we've already done all we can do and the only answer is move 
to WAL streaming as fast as possible?

If it matters, this is being run on Linux servers. Pg 12.2 is in final testing 
and will be rolled out to production soon -- so feel free to offer suggestions 
that only apply to 12.x.

Thanks,
Kevin
This e-mail transmission, and any documents, files or previous e-mail messages 
attached to it, may contain confidential information. If you are not the 
intended recipient, or a person responsible for delivering it to the intended 
recipient, you are hereby notified that any disclosure, distribution, review, 
copy or use of any of the information contained in or attached to this message 
is STRICTLY PROHIBITED. If you have received this transmission in error, please 
immediately notify us by reply e-mail, and destroy the original transmission 
and its attachments without reading them or saving them to disk. Thank you.

Re: Triggers and Full Text Search *

[Laurenz Albe]

On Tue, 2020-04-21 at 12:24 -0500, Malik Rumi wrote:
> More than a year ago, I implemented full text search on one of my sites.
> From the beginning, there was one problem (or at least, what I perceive
> to be a problem): when I use a script to insert many documents at once,
> they do *not* get indexed in fts. If a document is created or inserted
> one at a time, fts indexes immediately. The workaround I came up with
> was just to open each of those script inserted documents and then close
> them. As soon as they are opened, they get indexed. 

A trigger will fire and update the index immediately.

That opening and closing you are talking about does not sound like
a database activity.  Rather, it sounds like your software is delaying
the actual insert into the database, which would of course explain
why you cannot find it in the index.

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

Re: Triggers and Full Text Search *

[Adrian Klaver]

On 4/21/20 11:21 AM, Malik Rumi wrote:

@Ericson,
Forgive me for seeming dense, but how does COPY help or hurt here?

@Andreas,
I had to laugh at your reference to "prose". Would you believe I am 
actually a published playwright? Long before I started coding, of 
course. Old habits die hard.


The script code via Python/Django/psycopg2 would be helpful as my 
suspicion is that you are seeing the effects of open transactions.




entry_search_vector_trigger
             BEGIN
               SELECT setweight(to_tsvector(NEW.title), 'A') ||
                      setweight(to_tsvector(NEW.content), 'B') ||
                      setweight(to_tsvector(NEW.category), 'D') ||
                      setweight(to_tsvector(COALESCE(string_agg(tag.tag, 
', '), '')), 'C')

               INTO NEW.search_vector
               FROM ktab_entry AS entry
                 LEFT JOIN ktab_entry_tags AS entry_tags ON 
entry_tags.entry_id = entry.id 
                 LEFT JOIN ktab_tag AS tag ON tag.id  = 
entry_tags.tag_id

               WHERE entry.id  = NEW.id
               GROUP BY entry.id , category;
               RETURN NEW;
             END;

tag_search_vector_trigger
             BEGIN
               UPDATE ktab_entry SET id = id WHERE id IN (
                 SELECT entry_id FROM ktab_entry_tags WHERE tag_id = NEW.id
               );
               RETURN NEW;
             END;

tags_search_vector_trigger
             BEGIN
               IF (TG_OP = 'DELETE') THEN
                 UPDATE ktab_entry SET id = id WHERE id = OLD.entry_id;
                 RETURN OLD;
               ELSE
                 UPDATE ktab_entry SET id = id WHERE id = NEW.entry_id;
                 RETURN NEW;
               END IF;
             END;

search_vector_update
             BEGIN
               SELECT setweight(to_tsvector(NEW.title), 'A') ||
                      setweight(to_tsvector(NEW.content), 'B') ||
                      setweight(to_tsvector(NEW.category), 'D') ||
                      setweight(to_tsvector(COALESCE(string_agg(tag.tag, 
', '), '')), 'C')

               INTO NEW.search_vector
               FROM ktab_entry AS entry
                 LEFT JOIN ktab_entry_tags AS entry_tags ON 
entry_tags.entry_id = entry.id 
                 LEFT JOIN ktab_tag AS tag ON tag.id  = 
entry_tags.tag_id

               WHERE entry.id  = NEW.id
               GROUP BY entry.id , category;
               RETURN NEW;
             END;

search_vector_update  (tags)
             BEGIN
               IF (TG_OP = 'DELETE') THEN
                 UPDATE ktab_entry SET id = id WHERE id = OLD.entry_id;
                 RETURN OLD;
               ELSE
                 UPDATE ktab_entry SET id = id WHERE id = NEW.entry_id;
                 RETURN NEW;
               END IF;
             END;

Thank you!



*/“None of you has faith until he loves for his brother or his neighbor 
what he loves for himself.”/*



On Tue, Apr 21, 2020 at 1:05 PM Ericson Smith > wrote:


I think COPY bypasses the triggers.

Best Regards
- Ericson Smith
+1 876-375-9857 (whatsapp)
+1 646-483-3420 (sms)



On Wed, Apr 22, 2020 at 12:32 AM Andreas Joseph Krogh
mailto:andr...@visena.com>> wrote:

På tirsdag 21. april 2020 kl. 19:24:10, skrev Malik Rumi
mailto:malik.a.r...@gmail.com>>:

[...]

I am not (yet) posting the trigger code because this post is
long already, and if your answers are 1) yes, 2) no and 3)
triggers often work / fail like this, then there’s no point
and we can wrap this up. But if not, I will happily post
what I have. Thank you.

This is too much prose for the regular programmer, show us the
code, and point out what doesn't work for you, then we can help:-)
--
Andreas Joseph Krogh




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

Re: DB Link returning Partial data rows

[Adrian Klaver]

On 4/21/20 11:18 AM, AJ Rao wrote:
Hi - I setup dblink in my PostgreSQL 9.6.14 db and reading data from 
PostgreSQL 9.6.11 db. My query returns 3600 rows when I run it in the 
Source Db, but returns only 2365 rows when I run it from the Target db 
through dblink. Is there a setting that I need to update or is there a 
limitation with dblink? Your help will be greatly appreciated.


Not that I know of.

1) Are you sure you are pointing at the same database from your target 
database?


2) What are the queries?

3) Have you tried postgres_fdw?:
https://www.postgresql.org/docs/9.6/postgres-fdw.html



Summary:

1.SourceDB = PostgreSQL 9.6.11

2.TargetDB = PostgreSQL 9.6.14

3.Source Query = 15 columns with 3600 rows

4.Target Query via dblink: return 2365 rows only

Thanks, AJ




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

Re: Triggers and Full Text Search *

[Ericson Smith]

My apologies - I did not look closely at the manual. Many many years ago
(6.xx days I had a similar problem and leapt to answer).

Could you post your CREATE TRIGGER statements as well?


On Wed, Apr 22, 2020 at 1:21 AM Malik Rumi  wrote:

> @Ericson,
> Forgive me for seeming dense, but how does COPY help or hurt here?
>
> @Andreas,
> I had to laugh at your reference to "prose". Would you believe I am
> actually a published playwright? Long before I started coding, of course.
> Old habits die hard.
>
> entry_search_vector_trigger
> BEGIN
>   SELECT setweight(to_tsvector(NEW.title), 'A') ||
>  setweight(to_tsvector(NEW.content), 'B') ||
>  setweight(to_tsvector(NEW.category), 'D') ||
>  setweight(to_tsvector(COALESCE(string_agg(tag.tag, ',
> '), '')), 'C')
>   INTO NEW.search_vector
>   FROM ktab_entry AS entry
> LEFT JOIN ktab_entry_tags AS entry_tags ON
> entry_tags.entry_id = entry.id
> LEFT JOIN ktab_tag AS tag ON tag.id = entry_tags.tag_id
>   WHERE entry.id = NEW.id
>   GROUP BY entry.id, category;
>   RETURN NEW;
> END;
>
> tag_search_vector_trigger
> BEGIN
>   UPDATE ktab_entry SET id = id WHERE id IN (
> SELECT entry_id FROM ktab_entry_tags WHERE tag_id = NEW.id
>   );
>   RETURN NEW;
> END;
>
> tags_search_vector_trigger
> BEGIN
>   IF (TG_OP = 'DELETE') THEN
> UPDATE ktab_entry SET id = id WHERE id = OLD.entry_id;
> RETURN OLD;
>   ELSE
> UPDATE ktab_entry SET id = id WHERE id = NEW.entry_id;
> RETURN NEW;
>   END IF;
> END;
>
> search_vector_update
> BEGIN
>   SELECT setweight(to_tsvector(NEW.title), 'A') ||
>  setweight(to_tsvector(NEW.content), 'B') ||
>  setweight(to_tsvector(NEW.category), 'D') ||
>  setweight(to_tsvector(COALESCE(string_agg(tag.tag, ',
> '), '')), 'C')
>   INTO NEW.search_vector
>   FROM ktab_entry AS entry
> LEFT JOIN ktab_entry_tags AS entry_tags ON
> entry_tags.entry_id = entry.id
> LEFT JOIN ktab_tag AS tag ON tag.id = entry_tags.tag_id
>   WHERE entry.id = NEW.id
>   GROUP BY entry.id, category;
>   RETURN NEW;
> END;
>
> search_vector_update  (tags)
> BEGIN
>   IF (TG_OP = 'DELETE') THEN
> UPDATE ktab_entry SET id = id WHERE id = OLD.entry_id;
> RETURN OLD;
>   ELSE
> UPDATE ktab_entry SET id = id WHERE id = NEW.entry_id;
> RETURN NEW;
>   END IF;
> END;
>
> Thank you!
>
>
>
> *“None of you has faith until he loves for his brother or his neighbor
> what he loves for himself.”*
>
>
> On Tue, Apr 21, 2020 at 1:05 PM Ericson Smith 
> wrote:
>
>> I think COPY bypasses the triggers.
>>
>> Best Regards
>> - Ericson Smith
>> +1 876-375-9857 (whatsapp)
>> +1 646-483-3420 (sms)
>>
>>
>>
>> On Wed, Apr 22, 2020 at 12:32 AM Andreas Joseph Krogh 
>> wrote:
>>
>>> På tirsdag 21. april 2020 kl. 19:24:10, skrev Malik Rumi <
>>> malik.a.r...@gmail.com>:
>>>
>>> [...]
>>>
>>> I am not (yet) posting the trigger code because this post is long
>>> already, and if your answers are 1) yes, 2) no and 3) triggers often work /
>>> fail like this, then there’s no point and we can wrap this up. But if not,
>>> I will happily post what I have. Thank you.
>>>
>>>
>>> This is too much prose for the regular programmer, show us the code, and
>>> point out what doesn't work for you, then we can help:-)
>>>
>>> --
>>> Andreas Joseph Krogh
>>>
>>

Re: Triggers and Full Text Search *

[Malik Rumi]

@Ericson,
Forgive me for seeming dense, but how does COPY help or hurt here?

@Andreas,
I had to laugh at your reference to "prose". Would you believe I am
actually a published playwright? Long before I started coding, of course.
Old habits die hard.

entry_search_vector_trigger
BEGIN
  SELECT setweight(to_tsvector(NEW.title), 'A') ||
 setweight(to_tsvector(NEW.content), 'B') ||
 setweight(to_tsvector(NEW.category), 'D') ||
 setweight(to_tsvector(COALESCE(string_agg(tag.tag, ',
'), '')), 'C')
  INTO NEW.search_vector
  FROM ktab_entry AS entry
LEFT JOIN ktab_entry_tags AS entry_tags ON
entry_tags.entry_id = entry.id
LEFT JOIN ktab_tag AS tag ON tag.id = entry_tags.tag_id
  WHERE entry.id = NEW.id
  GROUP BY entry.id, category;
  RETURN NEW;
END;

tag_search_vector_trigger
BEGIN
  UPDATE ktab_entry SET id = id WHERE id IN (
SELECT entry_id FROM ktab_entry_tags WHERE tag_id = NEW.id
  );
  RETURN NEW;
END;

tags_search_vector_trigger
BEGIN
  IF (TG_OP = 'DELETE') THEN
UPDATE ktab_entry SET id = id WHERE id = OLD.entry_id;
RETURN OLD;
  ELSE
UPDATE ktab_entry SET id = id WHERE id = NEW.entry_id;
RETURN NEW;
  END IF;
END;

search_vector_update
BEGIN
  SELECT setweight(to_tsvector(NEW.title), 'A') ||
 setweight(to_tsvector(NEW.content), 'B') ||
 setweight(to_tsvector(NEW.category), 'D') ||
 setweight(to_tsvector(COALESCE(string_agg(tag.tag, ',
'), '')), 'C')
  INTO NEW.search_vector
  FROM ktab_entry AS entry
LEFT JOIN ktab_entry_tags AS entry_tags ON
entry_tags.entry_id = entry.id
LEFT JOIN ktab_tag AS tag ON tag.id = entry_tags.tag_id
  WHERE entry.id = NEW.id
  GROUP BY entry.id, category;
  RETURN NEW;
END;

search_vector_update  (tags)
BEGIN
  IF (TG_OP = 'DELETE') THEN
UPDATE ktab_entry SET id = id WHERE id = OLD.entry_id;
RETURN OLD;
  ELSE
UPDATE ktab_entry SET id = id WHERE id = NEW.entry_id;
RETURN NEW;
  END IF;
END;

Thank you!



*“None of you has faith until he loves for his brother or his neighbor what
he loves for himself.”*


On Tue, Apr 21, 2020 at 1:05 PM Ericson Smith  wrote:

> I think COPY bypasses the triggers.
>
> Best Regards
> - Ericson Smith
> +1 876-375-9857 (whatsapp)
> +1 646-483-3420 (sms)
>
>
>
> On Wed, Apr 22, 2020 at 12:32 AM Andreas Joseph Krogh 
> wrote:
>
>> På tirsdag 21. april 2020 kl. 19:24:10, skrev Malik Rumi <
>> malik.a.r...@gmail.com>:
>>
>> [...]
>>
>> I am not (yet) posting the trigger code because this post is long
>> already, and if your answers are 1) yes, 2) no and 3) triggers often work /
>> fail like this, then there’s no point and we can wrap this up. But if not,
>> I will happily post what I have. Thank you.
>>
>>
>> This is too much prose for the regular programmer, show us the code, and
>> point out what doesn't work for you, then we can help:-)
>>
>> --
>> Andreas Joseph Krogh
>>
>

DB Link returning Partial data rows

[AJ Rao]

Hi - I setup dblink in my PostgreSQL 9.6.14 db and reading data from PostgreSQL 9.6.11 db. My query returns 3600 rows when I run it in the Source Db, but returns only 2365 rows when I run it from the Target db through dblink. Is there a setting that I need to update or is there a limitation with dblink? Your help will be greatly appreciated.Summary:1. SourceDB = PostgreSQL 9.6.112. TargetDB = PostgreSQL 9.6.143. Source Query = 15 columns with 3600 rows4. Target Query via dblink: return 2365 rows only Thanks, AJ  

Re: Triggers and Full Text Search *

[Adrian Klaver]

On 4/21/20 11:04 AM, Ericson Smith wrote:

I think COPY bypasses the triggers.


No:

https://www.postgresql.org/docs/12/sql-copy.html

"COPY FROM will invoke any triggers and check constraints on the 
destination table. However, it will not invoke rules."




Best Regards
- Ericson Smith
+1 876-375-9857 (whatsapp)
+1 646-483-3420 (sms)



On Wed, Apr 22, 2020 at 12:32 AM Andreas Joseph Krogh 
mailto:andr...@visena.com>> wrote:


På tirsdag 21. april 2020 kl. 19:24:10, skrev Malik Rumi
mailto:malik.a.r...@gmail.com>>:

[...]

I am not (yet) posting the trigger code because this post is
long already, and if your answers are 1) yes, 2) no and 3)
triggers often work / fail like this, then there’s no point and
we can wrap this up. But if not, I will happily post what I
have. Thank you.

This is too much prose for the regular programmer, show us the code,
and point out what doesn't work for you, then we can help:-)
--
Andreas Joseph Krogh




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

Re: Triggers and Full Text Search *

[Ericson Smith]

I think COPY bypasses the triggers.

Best Regards
- Ericson Smith
+1 876-375-9857 (whatsapp)
+1 646-483-3420 (sms)



On Wed, Apr 22, 2020 at 12:32 AM Andreas Joseph Krogh 
wrote:

> På tirsdag 21. april 2020 kl. 19:24:10, skrev Malik Rumi <
> malik.a.r...@gmail.com>:
>
> [...]
>
> I am not (yet) posting the trigger code because this post is long already,
> and if your answers are 1) yes, 2) no and 3) triggers often work / fail
> like this, then there’s no point and we can wrap this up. But if not, I
> will happily post what I have. Thank you.
>
>
> This is too much prose for the regular programmer, show us the code, and
> point out what doesn't work for you, then we can help:-)
>
> --
> Andreas Joseph Krogh
>

Sv: Triggers and Full Text Search *

[Andreas Joseph Krogh]

På tirsdag 21. april 2020 kl. 19:24:10, skrev Malik Rumi <
malik.a.r...@gmail.com >: 


[...]

I am not (yet) posting the trigger code because this post is long already, and 
if your answers are 1) yes, 2) no and 3) triggers often work / fail like this, 
then there’s no point and we can wrap this up. But if not, I will happily post 
what I have. Thank you.


This is too much prose for the regular programmer, show us the code, and point 
out what doesn't work for you, then we can help:-) 


--
 Andreas Joseph Krogh

Triggers and Full Text Search *

[Malik Rumi]

More than a year ago, I implemented full text search on one of my sites.
>From the beginning, there was one problem (or at least, what I perceive to
be a problem): when I use a script to insert many documents at once, they
do *not* get indexed in fts. If a document is created or inserted one at a
time, fts indexes immediately. The workaround I came up with was just to
open each of those script inserted documents and then close them. As soon
as they are opened, they get indexed.

I assume this has to do with the trigger, which is set to BEFORE, and which
I carefully followed from the blog post that I got the code from. I wrote
to that author at the time, but he was of no help. My thought was that the
trigger was not firing, and thus the documents were not getting indexed,
because until the document was actually there, there was nothing to index.
Therefore, I thought a simple switch from BEFORE to AFTER would solve my
problem. However, in the example in the official docs, BEFORE is used as
well, so I abandoned that idea and decided to post this question.

Another solution I had in mind was to simply include an additional step in
my insert script to sleep for one second, during which the current document
would be opened, and hopefully indexed, and then closed, so the script
could go on to the next document. Note my insert script is in Python and
goes through Django. This is not a ‘pure’ postgresql operation, if that
matters.

My questions are:

   1.

   Does this sleep / open / close / proceed idea seem like a workable
   solution?
   2.

   Is there a better workaround?
   3.

   At first blush, I would think the speed of insertion would not be an
   issue for any trigger - it would seem to defeat the purpose - but am I
   wrong about that?


I am not (yet) posting the trigger code because this post is long already,
and if your answers are 1) yes, 2) no and 3) triggers often work / fail
like this, then there’s no point and we can wrap this up. But if not, I
will happily post what I have. Thank you.




*“None of you has faith until he loves for his brother or his neighbor what
he loves for himself.”*

Access control on the read replica

[Мазлов Владимир]

Hi, What I've been trying to do: write a web app that, upon receiving a request, automatically gets a DB connection with only the permissions it needs. In order to do that I'd like to create a mechanism for dynamically granting a role a set of permissions necessary for the given request and then revoking them (alternatively, we could pre-create a number of roles for each set of permissions but I'd like to avoid that solution, as it's very incovenient). There are a number of solutions to this problem: you could use triggers/views, you could use security definer functions to grant/revoke permissions for a pool of roles. None of the solutions I've been able to come up with work on connections to the standby. Does anything I've stated above strike you as obviously wrong? If not, do you perhaps have any ideas on how this problem could be tackled? I would very much appreciate any ideas. Cheers,Vladimir

Access control on the read replica

[Мазлов Владимир]

Hi, What I've been trying to do: write a web app that, upon receiving a request, automatically gets a DB connection with only the permissions it needs. There are a number of solutions to this problem: you could use triggers/view, you could use security definer functions to grant/revoke permissions for a pool of roles. None of the solutions I've been able to come up with work on connections to the standby. Does anything I've stated above strike you as obviously wrong? If not, do you perhaps have any ideas on how this problem could be tackled? I would very much appreciate any ideas. Cheers,Vladimir

1GB of maintenance work mem

[pinker]

Hi,
is this limit for maintenance work mem still there? or it has been patched?

https://www.postgresql-archive.org/Vacuum-allow-usage-of-more-than-1GB-of-work-mem-td5919221i180.html



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

a prefix searching

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

I found an interesting information, which I want to share. When I analysed the 
ChangeLog I found:

Add prefix-match operator text ^@ text, which is supported by SP-GiST (Ildus 
Kurbangaliev)
This is similar to using var LIKE 'word%' with a btree index, but it is more 
efficient.

It was interesting for me, because and searching for prefix is enough often 
happened and I knew nothing about such operator.. This operator is based on 
internal function starts_with() and supported by SP-GiST.

The information in the official documentation is really poor.

https://www.postgresql.org/docs/12/functions-string.html
In the section about string operators said nothing about "^@" , only about 
internal function starts_with(), which can not be accelerated by index.

https://www.postgresql.org/docs/12/functions-matching.html
But in the section about pattern matching in subsection about "LIKE" mentioned 
both "^@" operator and internal starts_width(). This is surprise, because "^@" 
has nothing with LIKE or pattern matching. Also nothing said that it is useful 
only with SP-GiST index.

So I decided investigate by myself. I used data from pg_proc for my tests.

First, check btree index:

=> create table test_btree (oid oid primary key,proname text not null);
CREATE TABLE
=> insert into test_btree select oid,proname from pg_proc;
INSERT 0 2960
=> create index on test_btree (proname);
CREATE INDEX
=> analyze test_btree;
ANALYZE
=> explain select * from test_btree where proname ^@ 'bool';
 QUERY PLAN
-
 Seq Scan on test_btree  (cost=0.00..55.00 rows=27 width=17)
   Filter: (proname ^@ 'bool'::text)
(2 rows)
=> explain select * from test_btree where starts_with(proname,'bool');
  QUERY PLAN
--
 Seq Scan on test_btree  (cost=0.00..55.00 rows=987 width=17)
   Filter: starts_with(proname, 'bool'::text)
(2 rows)
=> explain select * from test_btree where proname like 'bool%';
 QUERY PLAN
-
 Seq Scan on test_btree  (cost=0.00..55.00 rows=27 width=17)
   Filter: (proname ~~ 'bool%'::text)
(2 rows)

=> explain select * from test_btree where proname similar to 'bool%';
 QUERY PLAN
-
 Seq Scan on test_btree  (cost=0.00..55.00 rows=27 width=17)
   Filter: (proname ~ '^(?:bool.*)$'::text)
(2 rows)
=> explain select * from test_btree where proname ~ '^bool.*';
 QUERY PLAN
-
 Seq Scan on test_btree  (cost=0.00..55.00 rows=27 width=17)
   Filter: (proname ~ '^bool.*'::text)
(2 rows)

This is surprise, but any prefix searching don't work with common btree index 
based on the local collation. But why not? The prefix searching is expected to 
be used with local collation, as all other text searching.

Next is a lifehack to use "C" collation. I read about this in the very old 
official PostgreSQL documentation.

=> create table test_btree_c (oid oid primary key,proname text not null);
CREATE TABLE
=> insert into test_btree_c select oid,proname from pg_proc;
INSERT 0 2960
=> create index on test_btree_c (proname collate "C");
CREATE INDEX
=> analyze test_btree_c;
ANALYZE
=> explain select * from test_btree_c where proname collate "C" ^@ 'bool';
  QUERY PLAN
---
 Seq Scan on test_btree_c  (cost=0.00..55.00 rows=27 width=17)
   Filter: ((proname)::text ^@ 'bool'::text)
(2 rows)
=> explain select * from test_btree_c where starts_with(proname collate 
"C",'bool');
   QUERY PLAN

 Seq Scan on test_btree_c  (cost=0.00..55.00 rows=987 width=17)
   Filter: starts_with((proname)::text, 'bool'::text)
(2 rows)
=> explain select * from test_btree_c where proname collate "C" like 'bool%';
  QUERY PLAN
---
 Index Scan using test_btree_c_proname_idx on test_btree_c  (cost=0.28..3.26 
rows=27 width=17)
   Index Cond: (((proname)::text >= 'bool'::text) AND ((proname)::text < 
'boom'::text))
   Filter: ((proname)::text ~~ 'bool%'::text)
(3 rows)
=> explain select * from test_btree_c where proname collate "C" similar to 
'bool%';
  QUERY PLAN
---
 Index Scan using test_btree_c_proname_idx on test_btree_c  (cost=0.28..3.26 
rows=27 width=17)
   Index Cond: (((proname)::text >= 'bool'::text) AND ((proname)::text < 
'boom'::text))
   Filter: ((proname)::text ~ '^(?:bool.*)$'::text)
(3 rows)
=> explain select * from 

Re: Unable to connect to the database: TypeError: net.Socket is not a constructor

[Marco Ippolito]

Thank you very much Tim and Adrian for your very kind and valuable
information and suggestions.

Marco

Il giorno mar 21 apr 2020 alle ore 00:47 Tim Cross 
ha scritto:

>
> Marco Ippolito  writes:
>
> > I'm trying to connect to a postgres database (Postgresql-11) within my
> > nodejs-vue.js app, but in console I'm getting this error:
> >
> > [HMR] Waiting for update signal from WDS...
> > pg.js?c8c2:27 Unable to connect to the database: TypeError:
> net.Socket
> > is not a constructor
> > at new Connection
> > (webpack-internal:///./node_modules/pg/lib/connection.js:22:34)
> > at new Client
> > (webpack-internal:///./node_modules/pg/lib/client.js:55:37)
> > at Promise.tap.query
> > (webpack-internal:///./node_modules/sequelize/lib/dialects/postgres
> > /connection-manager.js:124:26)
> > at ConnectionManager.connect
> > (webpack-internal:///./node_modules/sequelize/lib/dialects
> > /postgres/connection-manager.js:121:12)
> > at eval
> >
> (webpack-internal:///./node_modules/sequelize/lib/dialects/abstract/connection-
> > manager.js:318:50)
> > From previous event:
> > at ConnectionManager._connect
> > (webpack-internal:///./node_modules/sequelize/lib/dialects
> > /abstract/connection-manager.js:318:8)
> > at ConnectionManager.getConnection
> > (webpack-internal:///./node_modules/sequelize/lib/dialects
> > /abstract/connection-manager.js:254:46)
> > at eval
> > (webpack-internal:///./node_modules/sequelize/lib/sequelize.js:640:36)
> > From previous event:
> > at eval
> > (webpack-internal:///./node_modules/sequelize/lib/sequelize.js:631:53)
> > at eval
> > (webpack-internal:///./node_modules/retry-as-promised/index.js:70:21)
> > at new Promise ()
> > at retryAsPromised
> > (webpack-internal:///./node_modules/retry-as-promised/index.js:60:10)
> > at eval
> > (webpack-internal:///./node_modules/sequelize/lib/sequelize.js:631:30)
> > From previous event:
> > at Sequelize.query
> > (webpack-internal:///./node_modules/sequelize/lib/sequelize.js:580:23)
> > at Sequelize.authenticate
> > (webpack-internal:///./node_modules/sequelize/lib/sequelize.js:892:17)
> > at eval (webpack-internal:///./src/plugins/db/pg.js:23:11)
> > at Object../src/plugins/db/pg.js (
> https://ggc.world/js/app.js:1128:1
> > )
> > at __webpack_require__ (https://ggc.world/js/app.js:785:30)
> > at fn (https://ggc.world/js/app.js:151:20)
> > at eval (webpack-internal:///./src/main.js:16:72)
> > at Module../src/main.js (https://ggc.world/js/app.js:1083:1)
> > at __webpack_require__ (https://ggc.world/js/app.js:785:30)
> > at fn (https://ggc.world/js/app.js:151:20)
> > at Object.1 (https://ggc.world/js/app.js:1141:18)
> > at __webpack_require__ (https://ggc.world/js/app.js:785:30)
> > at checkDeferredModules (https://ggc.world/js/app.js:46:23)
> > at https://ggc.world/js/app.js:861:18
> > at https://ggc.world/js/app.js:864:10
> >
> >  In /src/main.js :
> >
> > import '@/plugins/db/pg';
> >
> > (base) /src/plugins/db$ ls -lah
> > total 28K
> > drwxr-xr-x 6 marco marco 4,0K apr 20 15:42 .
> > drwxr-xr-x 3 marco marco 4,0K apr 20 15:41 ..
> > drwxr-xr-x 2 marco marco 4,0K apr 20 17:20 config
> > drwxr-xr-x 2 marco marco 4,0K apr 20 15:48 migrations
> > drwxr-xr-x 2 marco marco 4,0K apr 20 15:48 models
> > -rw-r--r-- 1 marco marco  819 apr 20 08:53 pg.js
> > drwxr-xr-x 2 marco marco 4,0K apr 20 17:21 seeders
> >
> >
> > nano pg.js
> >
> > const { Pool } = require('pg');
> > const { Sequelize } = require('sequelize');
> > const pool = new Pool();
> > const sequelize = new Sequelize('pusers', 'postgres', 'pwd', {
> >   host: 'localhost',
> >   dialect: 'postgres',
> >   pool: {
> > max: 5,
> > min: 0,
> > acquire: 3,
> > idle: 1
> >   }
> > });
> > sequelize
> >   .authenticate()
> >   .then(() => {
> > console.log('Connection has been established successfully.');
> >   })
> >   .catch(err => {
> > console.log('Unable to connect to the database:', err);
> >   });
> >
> > module.exports = {
> >   query: (text, params, callback) => {
> > return pool.query(text, params, callback);
> >   },
> > }
> >
> > Populated the sample database with a row:
> >
> > pusers=# SELECT schemaname,relname,n_live_tup
> > pusers-#   FROM pg_stat_user_tables
> > pusers-#   ORDER BY n_live_tup DESC;
> >  schemaname |relname| n_live_tup
> > +---+
> >  public | pusers|  1
> >  public | SequelizeMeta |  1
> > (2 rows)
> >
> > I read here:
> >
> https://stackoverflow.com/questions/40599069/node-js-net-socket-is-not-a-constructor
> > that
>