Re: archive file "00000001000000000000006F" has wrong size: 67118648 instead of 16777216

[Magnus Hagander]

On Wed, Nov 25, 2020 at 3:02 AM 江川潔  wrote:
>
> Hi,
>
> WAL log recovery was failed on wrong log record size. Could you please advise 
> me what is wrong in the setting ? Any suggestions will be highly appreciated.
>
> Thanks,
> Kiyoshi
>
> postgres.conf:
> wal_level = replica
> archive_mode = on
> archive_command = 'copy "%p" "D:\\BKUP\\pg_archivedir\\PostgreSQL_DEV\\%f"'
>
> recover.conf:
> restore_command = 'copy "D:\\BKUP\\pg_archivedir\\PostgreSQL_DEV" "%p"'

Does your restore command really not have a %f in it anywhere? That
definitely seems wrong... But it does seem to copy some files correct,
which would be weird if it doesn't. Mistake in the report, or is there
something really weird going on with that PostgreSQL_DEV not being a
directory but instead some "magic file"?

-- 
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/

Re: Performance hit if I create multiple DBs on same instance

[Laurenz Albe]

On Tue, 2020-11-24 at 11:34 -0600, Ron wrote:
> And if you're afraid of autovacuum and autoanalyze stealing resources, then 
> disable them (at the table level).

Ugh, bad advice.

Better would be to VACUUM (FREEZE) these static table once, then autovacuum
won't ever perform resource consuming activities on them again.

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

Re: INSERT Trigger to check for existing records : Does this do what I hope it is doing?

[Hagen Finley]

Folks,

Just a quick question. *Using this FUNCTION:*

   CREATE OR REPLACE FUNCTION same_test(did numeric)
   RETURNS numeric AS $$
   BEGIN
  IF $1 IN
      (SELECT dealid from hygiene_112)
  THEN
    UPDATE hygiene_119 SET paid = 'SAME';
  ELSE
      UPDATE hygiene_119 SET paid = 'NEW';
  END IF;
   RETURN NULL;
   END;
   $$ LANGUAGE plpgsql;

*Does the following query input the the dealids that result from the 
SELECT statement into the parameter of the sames_test() FUNCTION?*


Select dealid sametest(dealid) FROM hygiene_123;

I doubt it does (my query runs a /long time)/ :-). I know I can utilize 
python to push SELECT results into a array and then run a 'FOR d in 
dealids' LOOP to feed the FUNCTION parameter but I'd like to learn how 
to do that with nested SQL statements or FUNCTIONS.


Thanks!


Hagen


On 11/22/20 4:28 PM, Hagen Finley wrote:


Adrian and Michael,

My current insert logic (which works) is in a psycopg2 python script 
which reads a spreadsheet row into an array, so for the moment I 
didn't want to add that integration to my struggle.


cur = conn.cursor()
\
query = "INSERT INTO

sfdc(theater,country,account,smotion,opname,cprod,opid,*ndealid,*qnum,*stage,revusd*,cdate,bdate,age,opown,opnum,sonum,fbdate,region,dqnum,pid,closed,won,onum,repdate)

VALUES

(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"

cur.executemany(query, frecords)
conn.commit()
conn.close()


However, the following is something of a stepping stone towards the 
destination and, (dare I say it? ;-) it works:


CREATE OR REPLACE FUNCTION same_test(did numeric)
RETURNS numeric AS $$
BEGIN
  IF $1 IN
      (SELECT ndealid from hygiene_112)
  THEN
    UPDATE hygiene_119 SET paid = 'SAME';
  ELSE
      UPDATE hygiene_119 SET paid = 'NEW';
  END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;


I changed the dealid to something that doesn't exist (14593030) in the 
11-2 table and the function updates the 11-9 table.status field to "NEW":


sales=# UPDATE hygiene_112 SET ndealid = 14593030 WHERE ndealid = 
14593039;

UPDATE 1

SELECT same_test(14593039);

+--+---+--++--+--+--+
|repdate   |ndealid |revusd    |stage |status  |
+--+---+--++--+--+--+
|2020-11-09|14593039|1015624.23|Propose - 60% *|NEW * |
+--+---+--++--+--+--+

When I changed it back I get the proper "SAME" update:

sales=# UPDATE hygiene_112 SET ndealid = 14593039 WHERE ndealid = 
14593030;

UPDATE 1

SELECT same_test(14593039);

+--+---+--++--+--+--+
|repdate   |ndealid |revusd    |stage |status  |
+--+---+--++--+--+--+
|2020-11-09|14593039|1015624.23|Propose - 60% |*SAME * |
+--+---+--++--+--+--+


I'm generally amazed when ANYTHING works so this is good news (to me). 
It seems logical I could replace the UPDATE statement with an INSERT 
statement at this point.


However, that only addresses one of the /data rules /on my checklist.

I'll keep forging ahead here and see what additional progress I can 
attain. Very much appreciate your patient assistance here.


Best,

Hagen


On 11/22/20 11:26 AM, Adrian Klaver wrote:

On 11/22/20 9:53 AM, Hagen Finley wrote:

Hello Michael,

Thanks so much for this advice. As I mentioned previously, I'm not 
very good at this yet, so forgive me if my response is obtuse. I 
really love databases but my sentiments may be unrequited.


The overriding issue is lack of a plan. From your first post:

"Here’s what I (REALLY) want:


Trigger looks at three fields prior to new insert: Deal ID (numeric),
revenue (numeric), stage(char) Example: 19743576 22072.37 Commit
- 90%


 1. If the NEW dealid doesn't match any of the OLD dealids, insert the
    new row
 2. if the NEW dealid, revenue and stage fields ALL match the OLD
    dealid, revenue and stage, skip (don't insert the NEW row)
 3. If the NEW dealid matches an OLD dealid but either the NEW revenue
    OR the stage fields have changed (don't match OLD record) insert new
    row (I'll review both rows manually)
"

And from later post:

" I figure I could
use the chk field to delete the new inserts I didn't need."


From this I come

Re: Performance hit if I create multiple DBs on same instance

[Ron]

On 11/25/20 8:59 AM, Laurenz Albe wrote:

On Tue, 2020-11-24 at 11:34 -0600, Ron wrote:

And if you're afraid of autovacuum and autoanalyze stealing resources, then 
disable them (at the table level).

Ugh, bad advice.

Better would be to VACUUM (FREEZE) these static table once, then autovacuum
won't ever perform resource consuming activities on them again.


Good to know.

--
Angular momentum makes the world go 'round.

Re: INSERT Trigger to check for existing records : Does this do what I hope it is doing?

[Adrian Klaver]

On 11/25/20 7:41 AM, Hagen Finley wrote:

Folks,

Just a quick question. *Using this FUNCTION:*

CREATE OR REPLACE FUNCTION same_test(did numeric)
RETURNS numeric AS $$
BEGIN
   IF $1 IN
       (SELECT dealid from hygiene_112)
   THEN
     UPDATE hygiene_119 SET paid = 'SAME';
   ELSE
       UPDATE hygiene_119 SET paid = 'NEW';
   END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;


The above is broken in multiple ways:

1) You have RETURNS numeric and then RETURN NULL; This means you will 
not actually return anything


2) You have the input argument did but you never use it to restrict your 
UPDATEs.


3) Not sure the logic in the IF actually works even if you filtered by 
did. This assumes that there will always be a row in hygiene_119 that 
matches one in hygiene_112. Given that you setting a 'NEW' flag I'm 
guessing that is not the case.


You will need to sketch out the thought process at work here before we 
can go any further with this.






*Does the following query input the the dealids that result from the 
SELECT statement into the parameter of the sames_test() FUNCTION?*




Select dealid sametest(dealid) FROM hygiene_123;


Have no idea what that is supposed to do?

If you want to use the function(after fixing it) you would have to do:

select * from some_test(some_number);



I doubt it does (my query runs a /long time)/ :-). I know I can utilize 
python to push SELECT results into a array and then run a 'FOR d in 
dealids' LOOP to feed the FUNCTION parameter but I'd like to learn how 
to do that with nested SQL statements or FUNCTIONS.


Thanks!


Hagen



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

Number of parallel workers chosen by the optimizer for parallel append

[Laurenz Albe]

I have a partitioned table, each partition has "parallel_workers = 10" set.

  SET max_parallel_workers_per_gather = 8;

  SET enable_partitionwise_aggregate = on;

  EXPLAIN (COSTS OFF)
  SELECT applicant_name, count(ipc_4)
  FROM laurenz.z_flat
  GROUP BY applicant_name;

  QUERY PLAN
  --
   Gather
 Workers Planned: 4
 ->  Parallel Append
   ->  HashAggregate
 Group Key: z_flat_3.applicant_name
 ->  Seq Scan on xyz_4 z_flat_3
   ->  HashAggregate
 Group Key: z_flat.applicant_name
 ->  Seq Scan on xyz_1 z_flat
   [8 more such partition scans]
  (33 rows)

How does the optimizer decide to use 4 parallel workers?

No matter what I try, I cannot influence that number.

Yours,
Laurenz Albe

postgres_fdw insert extremely slow

[Mats Julian Olsen]

Helle pgsql-general,

We have just set up postgres_fdw between two postgres databases, x and y,
with the plan to periodically insert data from x into y.

We've successfully set up the connection with a few options:
`use_remote_estimate 'true'` and `fetch_size '5'` (the latter we've
played around with). We've run ANALYZE on the foreign server.

SELECTs against the foreign table returns in milliseconds, however an
INSERT of 100 rows takes 10 seconds. we have roughly 200 000 rows for the
initial sync, which translates into ~6 hours.

Is this the expected performance of postgre_fdw? Is there anything we've
overlooked when setting this up? Very curious to hear experiences from the
community when doing read/write and not just read from foreign sources.

Best regards,

-- 
Mats
CTO @ Dune Analytics
We're hiring: https://careers.duneanalytics.com

Re: postgres_fdw insert extremely slow

[Adrian Klaver]

On 11/25/20 8:37 AM, Mats Julian Olsen wrote:

Helle pgsql-general,

We have just set up postgres_fdw between two postgres databases, x and 
y, with the plan to periodically insert data from x into y.


We've successfully set up the connection with a few options: 
`use_remote_estimate 'true'` and `fetch_size '5'` (the latter we've 
played around with). We've run ANALYZE on the foreign server.


SELECTs against the foreign table returns in milliseconds, however an 
INSERT of 100 rows takes 10 seconds. we have roughly 200 000 rows for 
the initial sync, which translates into ~6 hours.


Postgres version(s)?

Without the actual query and EXPLAIN ANALYZE on it this will be 
difficult to answer with any detail.


Also would be nice to have the table schema.



Is this the expected performance of postgre_fdw? Is there anything we've 
overlooked when setting this up? Very curious to hear experiences from 
the community when doing read/write and not just read from foreign sources.


Best regards,

--
Mats
CTO @ Dune Analytics
We're hiring: https://careers.duneanalytics.com 




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

RE: INSERT Trigger to check for existing records : Does this do what I hope it is doing?

[hagen]

Adrian,

Thanks for your detailed response. That's very kind and much appreciated.

1. OK that's just me groping for a RETURN statement that doesn't throw a rod. I 
don't actually need to return anything as the goal of the FUNCTION (for the 
moment)  is to perform updates to a table. It might be nice to return some sort 
of confirmation but it's not necessary. Apparently I don't fully understand the 
RETURN concept (oh really? 😉. Any suggestions where to research or read?

2. I have two tables:
a) sfdc which is the baseline - doesn't change -  isn't updated by this 
FUNTION
b) hygiene_119 a new table which has some records (~80%) which are 
identical to those already in sfdc. 

The logic flow is:
i) SELECT the dealids from hygiene_119 (latest or new  report 
dated 11/9)
ii) compare those hygiene_119.dealids with the existing 
sfdc.dealids  -  hence the IF $1 (one result from the hygiene_119.dealdid 
SELECT) is IN (matches) any of the sfdc.dealids THEN
iii) UPDATE hygiene_119 SET status = 'SAME' WHERE dealid = $1; 
--flag that hygiene_119 record as the SAME or a duplicate record
iv) ELSE UPDATE hygiene_119 SET status = 'NEW' WHERE dealid = 
$1; --flag that hygiene_119 record as NEW  or a new record
Once I have inspected the "NEW" records in hygiene_119 I will INSERT 
then into sfdc. Then rinse and repeat each week with a new report.

3. Not positive the IF is doing what I want,  but if I copy a sfdc.dealid into 
the same_test() parameter field the FUNTION does update the hygiene_119.status 
field properly. To me, it appears I just need a way to iterate through and  
insert one hygiene_119.dealid in the same_test parameter field. Then the UPDATE 
should flag all the hygiene_119 records as SAME or NEW. Obviously I don't 
REALLY need both flags as the absence of a flag would indicate status too.

Does that articulate the thought process adequately?

Best,

Hagen


-Original Message-
From: Adrian Klaver  
Sent: Wednesday, November 25, 2020 9:07 AM
To: Hagen Finley ; pgsql-general@lists.postgresql.org
Subject: Re: INSERT Trigger to check for existing records : Does this do what I 
hope it is doing?

On 11/25/20 7:41 AM, Hagen Finley wrote:
> Folks,
> 
> Just a quick question. *Using this FUNCTION:*
> 
> CREATE OR REPLACE FUNCTION same_test(did numeric)
> RETURNS numeric AS $$
> BEGIN
>IF $1 IN
>(SELECT dealid from sfdc)
>THEN
>  UPDATE hygiene_119 SET status = 'SAME';
>ELSE
>UPDATE hygiene_119 SET status = 'NEW';
>END IF;
> RETURN NULL;
> END;
> $$ LANGUAGE plpgsql;

The above is broken in multiple ways:

1) You have RETURNS numeric and then RETURN NULL; This means you will not 
actually return anything

2) You have the input argument did but you never use it to restrict your 
UPDATEs.

3) Not sure the logic in the IF actually works even if you filtered by did. 
This assumes that there will always be a row in hygiene_119 that matches one in 
hygiene_112. Given that you setting a 'NEW' flag I'm guessing that is not the 
case.

You will need to sketch out the thought process at work here before we can go 
any further with this.



> 
> *Does the following query input the the dealids that result from the 
> SELECT statement into the parameter of the sames_test() FUNCTION?*

> 
> Select dealid sametest(dealid) FROM hygiene_123;

Have no idea what that is supposed to do?

If you want to use the function(after fixing it) you would have to do:

select * from some_test(some_number);

> 
> I doubt it does (my query runs a /long time)/ :-). I know I can utilize 
> python to push SELECT results into a array and then run a 'FOR d in 
> dealids' LOOP to feed the FUNCTION parameter but I'd like to learn how 
> to do that with nested SQL statements or FUNCTIONS.
> 
> Thanks!
> 
> 
> Hagen
> 

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

Re: postgres_fdw insert extremely slow

[Mats Julian Olsen]

Apologies for the sloppiness!

Postgres version(s)?


x: Aurora PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 
4.9.3, 64-bit (RDS)


y: PostgreSQL 12.2 (Ubuntu 12.2-2.pgdg19.10+1) on x86_64-pc-linux-gnu, 
compiled by gcc (Ubuntu 9.2.1-9ubuntu2) 9.2.1 20191008, 64-bit (GCP)




Without the actual query and EXPLAIN ANALYZE on it this will be 
difficult to answer with any detail.


Also would be nice to have the table schema.



Here's the query, schema and the EXPLAIN ANALYZE

query: https://gist.github.com/mewwts/704663e19a21bc51864f3298f09a18c0

plan: https://explain.depesz.com/s/RQFQ


Best,

--
Mats
CTO @ Dune Analytics
We're hiring: https://careers.duneanalytics.com

Re: postgres_fdw insert extremely slow

[Adrian Klaver]

On 11/25/20 8:48 AM, Mats Julian Olsen wrote:

Apologies for the sloppiness!

Postgres version(s)?


x: Aurora PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 
4.9.3, 64-bit (RDS)


y: PostgreSQL 12.2 (Ubuntu 12.2-2.pgdg19.10+1) on x86_64-pc-linux-gnu, 
compiled by gcc (Ubuntu 9.2.1-9ubuntu2) 9.2.1 20191008, 64-bit (GCP)


So they are separated by what network distance?





Without the actual query and EXPLAIN ANALYZE on it this will be 
difficult to answer with any detail.


Also would be nice to have the table schema.



Here's the query, schema and the EXPLAIN ANALYZE


FYI, you will get more responses by including below information directly 
in the email. To that end:


On x

CREATE TABLE labels ( 

id integer NOT NULL, 

address_id bytea NOT NULL, 

name text NOT NULL, 

author character varying(50) NOT NULL, 

type text NOT NULL, 

source text, 

updated_at timestamp with time zone DEFAULT now() NOT NULL, 

CONSTRAINT lowercase_name CHECK ((name = lower(name))), 

CONSTRAINT lowercase_type CHECK ((type = lower(type))), 

CONSTRAINT whitespace_name CHECK ((regexp_replace(btrim(name, ' 
'::text), '(\s+)'::text, ' '::text, 'g'::text) = name)),
CONSTRAINT whitespace_type CHECK ((regexp_replace(btrim(type, ' 
'::text), '(\s+)'::text, ' '::text, 'g'::text) = type))
); 


CREATE INDEX labels_label ON labels USING BTREE (name);
CREATE INDEX labels_label_type ON labels USING BTREE (type);
CREATE INDEX labels_address ON labels USING HASH (address_id);
CREATE INDEX labels_source ON labels USING BTREE (source);

On y

CREATE TABLE labels.labels (
address bytea PRIMARY KEY,
labels  text[]
);



query: https://gist.github.com/mewwts/704663e19a21bc51864f3298f09a18c0


INSERT INTO foreign.labels (address, labels)
SELECT address_id, ARRAY_AGG(name) AS labels
FROM labels
GROUP BY 1
LIMIT 100;



plan: https://explain.depesz.com/s/RQFQ


Insert on labels  (cost=0.42..26.19 rows=100 width=53) (actual 
time=11541.205..11541.205 rows=0 loops=1)
   ->  Limit  (cost=0.42..25.19 rows=100 width=53) (actual 
time=0.068..3.549 rows=100 loops=1)
 ->  GroupAggregate  (cost=0.42..26502.02 rows=106996 width=53) 
(actual time=0.066..3.449 rows=100 loops=1)

   Group Key: labels_1.address_id
   ->  Index Only Scan using 
labels_address_id_type_name_key on labels labels_1  (cost=0.42..24068.85 
rows=219145 width=31) (actual time=0.054..0.414 rows=201 loops=1)

 Heap Fetches: 0
 Planning Time: 0.102 ms
 Execution Time: 12797.143 ms





Best,




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

Re: postgres_fdw insert extremely slow

[Tom Lane]

Mats Julian Olsen  writes:
>> Postgres version(s)?

> x: Aurora PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 
> 4.9.3, 64-bit (RDS)
> y: PostgreSQL 12.2 (Ubuntu 12.2-2.pgdg19.10+1) on x86_64-pc-linux-gnu, 
> compiled by gcc (Ubuntu 9.2.1-9ubuntu2) 9.2.1 20191008, 64-bit (GCP)

Hmm, I wonder if Aurora could be doing something funny here?

Anyway, to answer your question, no that's not the expected level of
performance.  postgres_fdw is certainly not inexpensive compared to
local table access, but I'd still think inserts should take only a
small number of milliseconds.  It seems like something must be
blocking the query.  Have you tried looking into pg_locks on the
remote server while this query is running?

regards, tom lane

Re: postgres_fdw insert extremely slow

[Mats Julian Olsen]

On 25.11.2020 17:57, Adrian Klaver wrote:

On 11/25/20 8:48 AM, Mats Julian Olsen wrote:

Apologies for the sloppiness!

Postgres version(s)?


x: Aurora PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc 
(GCC) 4.9.3, 64-bit (RDS)


y: PostgreSQL 12.2 (Ubuntu 12.2-2.pgdg19.10+1) on 
x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.2.1-9ubuntu2) 9.2.1 
20191008, 64-bit (GCP)


So they are separated by what network distance?

By quite a lot! One server is in eu-west-1 and the other in gcp 
us-central1-a. Note however, that SELECTs across the network are very 
fast, so it doesn't seem like this is a network issue.




Without the actual query and EXPLAIN ANALYZE on it this will be 
difficult to answer with any detail.


Also would be nice to have the table schema.



Here's the query, schema and the EXPLAIN ANALYZE


FYI, you will get more responses by including below information 
directly in the email. To that end:


On x

CREATE TABLE labels (
    id integer NOT NULL,
    address_id bytea NOT NULL,
    name text NOT NULL,
    author character varying(50) NOT NULL,
    type text NOT NULL,
    source text,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT lowercase_name CHECK ((name = lower(name))),
    CONSTRAINT lowercase_type CHECK ((type = lower(type))),
    CONSTRAINT whitespace_name CHECK ((regexp_replace(btrim(name, ' 
'::text), '(\s+)'::text, ' '::text, 'g'::text) = name)),
    CONSTRAINT whitespace_type CHECK ((regexp_replace(btrim(type, ' 
'::text), '(\s+)'::text, ' '::text, 'g'::text) = type))

);
CREATE INDEX labels_label ON labels USING BTREE (name);
CREATE INDEX labels_label_type ON labels USING BTREE (type);
CREATE INDEX labels_address ON labels USING HASH (address_id);
CREATE INDEX labels_source ON labels USING BTREE (source);

On y

CREATE TABLE labels.labels (
    address bytea PRIMARY KEY,
    labels  text[]
);



query: https://gist.github.com/mewwts/704663e19a21bc51864f3298f09a18c0


INSERT INTO foreign.labels (address, labels)
SELECT address_id, ARRAY_AGG(name) AS labels
FROM labels
GROUP BY 1
LIMIT 100;



plan: https://explain.depesz.com/s/RQFQ


Insert on labels  (cost=0.42..26.19 rows=100 width=53) (actual 
time=11541.205..11541.205 rows=0 loops=1)
   ->  Limit  (cost=0.42..25.19 rows=100 width=53) (actual 
time=0.068..3.549 rows=100 loops=1)
 ->  GroupAggregate  (cost=0.42..26502.02 rows=106996 
width=53) (actual time=0.066..3.449 rows=100 loops=1)

   Group Key: labels_1.address_id
   ->  Index Only Scan using 
labels_address_id_type_name_key on labels labels_1 
(cost=0.42..24068.85 rows=219145 width=31) (actual time=0.054..0.414 
rows=201 loops=1)

 Heap Fetches: 0
 Planning Time: 0.102 ms
 Execution Time: 12797.143 ms





Best,

Thank you for inlining this!

--
Mats
CTO @ Dune Analytics
We're hiring: https://careers.duneanalytics.com

Re: postgres_fdw insert extremely slow

[Mats Julian Olsen]

On 25.11.2020 17:58, Tom Lane wrote:

Mats Julian Olsen  writes:

Postgres version(s)?

x: Aurora PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC)
4.9.3, 64-bit (RDS)
y: PostgreSQL 12.2 (Ubuntu 12.2-2.pgdg19.10+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 9.2.1-9ubuntu2) 9.2.1 20191008, 64-bit (GCP)

Hmm, I wonder if Aurora could be doing something funny here?

Anyway, to answer your question, no that's not the expected level of
performance.  postgres_fdw is certainly not inexpensive compared to
local table access, but I'd still think inserts should take only a
small number of milliseconds.  It seems like something must be
blocking the query.  Have you tried looking into pg_locks on the
remote server while this query is running?

regards, tom lane
Thanks Tom, I'll try to spin up a regular Postgres instance on both rds 
and ec2 and see if that helps. As for the locks, I can not see any 
blocked activity on the remote server while the query runs.


--
Mats
CTO @ Dune Analytics
We're hiring: https://careers.duneanalytics.com

Re: Performance hit if I create multiple DBs on same instance

[Bruce Momjian]

On Wed, Nov 25, 2020 at 03:59:06PM +0100, Laurenz Albe wrote:
> On Tue, 2020-11-24 at 11:34 -0600, Ron wrote:
> > And if you're afraid of autovacuum and autoanalyze stealing resources, then 
> > disable them (at the table level).
> 
> Ugh, bad advice.
> 
> Better would be to VACUUM (FREEZE) these static table once, then autovacuum
> won't ever perform resource consuming activities on them again.

Yes, also, even if you never do that, autovacuum will eventually freeze
those tables and never access them again.

-- 
  Bruce Momjian  https://momjian.us
  EnterpriseDB https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee

Re: postgres_fdw insert extremely slow

[Tom Lane]

Mats Julian Olsen  writes:
> On 25.11.2020 17:58, Tom Lane wrote:
>> ... Have you tried looking into pg_locks on the
>> remote server while this query is running?

> Thanks Tom, I'll try to spin up a regular Postgres instance on both rds 
> and ec2 and see if that helps. As for the locks, I can not see any 
> blocked activity on the remote server while the query runs.

Another place to check is pg_stat_activity, specifically wait_event_type
and wait_event.

regards, tom lane

Re: INSERT Trigger to check for existing records : Does this do what I hope it is doing?

[Adrian Klaver]

On 11/25/20 8:43 AM, ha...@datasundae.com wrote:

Adrian,

Thanks for your detailed response. That's very kind and much appreciated.

1. OK that's just me groping for a RETURN statement that doesn't throw a rod. 


Things can still work, sort of. I once cranked up and ran(for a short 
time) a JD 4020 that had a rod coming through the block. It was smoky 
and rough, but it ran. OT I know but that image came back clear as day.


I don't actually need to return anything as the goal of the FUNCTION 
(for the moment)  is to perform updates to a table. It might be nice to 
return some sort of confirmation but it's not necessary. Apparently I 
don't fully understand the RETURN concept (oh really? 😉. Any 
suggestions where to research or read?


2. I have two tables:
a) sfdc which is the baseline - doesn't change -  isn't updated by this 
FUNTION
b) hygiene_119 a new table which has some records (~80%) which are 
identical to those already in sfdc.

The logic flow is:
i) SELECT the dealids from hygiene_119 (latest or new  report 
dated 11/9)


Not seeing where that is done?


ii) compare those hygiene_119.dealids with the existing 
sfdc.dealids  -  hence the IF $1 (one result from the hygiene_119.dealdid 
SELECT) is IN (matches) any of the sfdc.dealids THEN


Again not seeing any comparison to sfdc?


iii) UPDATE hygiene_119 SET status = 'SAME' WHERE dealid = $1; 
--flag that hygiene_119 record as the SAME or a duplicate record
iv) ELSE UPDATE hygiene_119 SET status = 'NEW' WHERE dealid = 
$1; --flag that hygiene_119 record as NEW  or a new record
Once I have inspected the "NEW" records in hygiene_119 I will INSERT 
then into sfdc. Then rinse and repeat each week with a new report.


Until the previous questions are addressed the above is not doable.



3. Not positive the IF is doing what I want,  but if I copy a sfdc.dealid into 
the same_test() parameter field the FUNTION does update the hygiene_119.status 
field properly. To me, it appears I just need a way to iterate through and  
insert one hygiene_119.dealid in the same_test parameter field. Then the UPDATE 
should flag all the hygiene_119 records as SAME or NEW. Obviously I don't 
REALLY need both flags as the absence of a flag would indicate status too.


Before continuing with the function I would try some SELECT functions 
that do what you want.




Does that articulate the thought process adequately?

Best,

Hagen


-Original Message-
From: Adrian Klaver 
Sent: Wednesday, November 25, 2020 9:07 AM
To: Hagen Finley ; pgsql-general@lists.postgresql.org
Subject: Re: INSERT Trigger to check for existing records : Does this do what I 
hope it is doing?

On 11/25/20 7:41 AM, Hagen Finley wrote:

Folks,

Just a quick question. *Using this FUNCTION:*

 CREATE OR REPLACE FUNCTION same_test(did numeric)
 RETURNS numeric AS $$
 BEGIN
IF $1 IN
(SELECT dealid from sfdc)
THEN
  UPDATE hygiene_119 SET status = 'SAME';
ELSE
UPDATE hygiene_119 SET status = 'NEW';
END IF;
 RETURN NULL;
 END;
 $$ LANGUAGE plpgsql;


The above is broken in multiple ways:

1) You have RETURNS numeric and then RETURN NULL; This means you will not 
actually return anything

2) You have the input argument did but you never use it to restrict your 
UPDATEs.

3) Not sure the logic in the IF actually works even if you filtered by did. 
This assumes that there will always be a row in hygiene_119 that matches one in 
hygiene_112. Given that you setting a 'NEW' flag I'm guessing that is not the 
case.

You will need to sketch out the thought process at work here before we can go 
any further with this.





*Does the following query input the the dealids that result from the
SELECT statement into the parameter of the sames_test() FUNCTION?*




Select dealid sametest(dealid) FROM hygiene_123;


Have no idea what that is supposed to do?

If you want to use the function(after fixing it) you would have to do:

select * from some_test(some_number);



I doubt it does (my query runs a /long time)/ :-). I know I can utilize
python to push SELECT results into a array and then run a 'FOR d in
dealids' LOOP to feed the FUNCTION parameter but I'd like to learn how
to do that with nested SQL statements or FUNCTIONS.

Thanks!


Hagen






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

RE: INSERT Trigger to check for existing records : Does this do what I hope it is doing?

[hagen]

[Hagen] Answers inline

-Original Message-
From: Adrian Klaver  
Sent: Wednesday, November 25, 2020 10:13 AM
To: ha...@datasundae.com; pgsql-general@lists.postgresql.org
Subject: Re: INSERT Trigger to check for existing records : Does this do what I 
hope it is doing?

On 11/25/20 8:43 AM, ha...@datasundae.com wrote:
> Adrian,
> 
> Thanks for your detailed response. That's very kind and much appreciated.
> 
> 1. OK that's just me groping for a RETURN statement that doesn't throw a rod. 

Things can still work, sort of. I once cranked up and ran(for a short
time) a JD 4020 that had a rod coming through the block. It was smoky and 
rough, but it ran. OT I know but that image came back clear as day.

I don't actually need to return anything as the goal of the FUNCTION (for the 
moment)  is to perform updates to a table. It might be nice to return some sort 
of confirmation but it's not necessary. Apparently I don't fully understand the 
RETURN concept (oh really? 😉. Any suggestions where to research or read?
> 
> 2. I have two tables:
>   a) sfdc which is the baseline - doesn't change -  isn't updated by this 
> FUNTION
>   b) hygiene_119 a new table which has some records (~80%) which are 
> identical to those already in sfdc.
> 
> The logic flow is:
>   i) SELECT the dealids from hygiene_119 (latest or new  report 
> dated 
> 11/9)

Not seeing where that is done?

[Hagen] I was hoping to do the SELECT from hygiene_119 when I called the 
FUNCTION same_test() by SELECTING hygiene_119.dealid and then using that list 
as an input via same_test (hygiene_119.dealid)

[Hagen] SELECT dealid sametest(dealid) FROM hygiene_123; (more precisely SELECT 
hygiene_119.dealid, same_test(hygiene_119.dealid) FROM hygiene_119;  ).

>   ii) compare those hygiene_119.dealids with the existing 
> sfdc.dealids  
> -  hence the IF $1 (one result from the hygiene_119.dealdid SELECT) is 
> IN (matches) any of the sfdc.dealids THEN

Again not seeing any comparison to sfdc?

[Hagen] Assuming the same_test(hygiene_119.dealid) call worked - IF $1 = 
hygiene_119.dealid[0] in python array vernacular would be compared against the 
list of the SELECT sfdc.dealid results.

[Hagen] Spelling it out more clearly isn't exactly boosting my confidence in my 
approach 😉

>   iii) UPDATE hygiene_119 SET status = 'SAME' WHERE dealid = $1; 
> --flag that hygiene_119 record as the SAME or a duplicate record
>   iv) ELSE UPDATE hygiene_119 SET status = 'NEW' WHERE dealid = 
> $1; --flag that hygiene_119 record as NEW  or a new record
>   Once I have inspected the "NEW" records in hygiene_119 I will INSERT 
> then into sfdc. Then rinse and repeat each week with a new report.

Until the previous questions are addressed the above is not doable.

> 
> 3. Not positive the IF is doing what I want,  but if I copy a sfdc.dealid 
> into the same_test() parameter field the FUNTION does update the 
> hygiene_119.status field properly. To me, it appears I just need a way to 
> iterate through and  insert one hygiene_119.dealid in the same_test parameter 
> field. Then the UPDATE should flag all the hygiene_119 records as SAME or 
> NEW. Obviously I don't REALLY need both flags as the absence of a flag would 
> indicate status too.

Before continuing with the function I would try some SELECT functions that do 
what you want.

> 
> Does that articulate the thought process adequately?
> 
> Best,
> 
> Hagen
> 
> 
> -Original Message-
> From: Adrian Klaver 
> Sent: Wednesday, November 25, 2020 9:07 AM
> To: Hagen Finley ; 
> pgsql-general@lists.postgresql.org
> Subject: Re: INSERT Trigger to check for existing records : Does this do what 
> I hope it is doing?
> 
> On 11/25/20 7:41 AM, Hagen Finley wrote:
>> Folks,
>>
>> Just a quick question. *Using this FUNCTION:*
>>
>>  CREATE OR REPLACE FUNCTION same_test(did numeric)
>>  RETURNS numeric AS $$
>>  BEGIN
>> IF $1 IN
>> (SELECT dealid from sfdc)
>> THEN
>>   UPDATE hygiene_119 SET status = 'SAME';
>> ELSE
>> UPDATE hygiene_119 SET status = 'NEW';
>> END IF;
>>  RETURN NULL;
>>  END;
>>  $$ LANGUAGE plpgsql;
> 
> The above is broken in multiple ways:
> 
> 1) You have RETURNS numeric and then RETURN NULL; This means you will 
> not actually return anything
> 
> 2) You have the input argument did but you never use it to restrict your 
> UPDATEs.
> 
> 3) Not sure the logic in the IF actually works even if you filtered by did. 
> This assumes that there will always be a row in hygiene_119 that matches one 
> in hygiene_112. Given that you setting a 'NEW' flag I'm guessing that is not 
> the case.
> 
> You will need to sketch out the thought process at work here before we can go 
> any further with this.
> 
> 
> 
>>
>> *Does the following query input the the dealids that result from the 
>> SELECT statement into the parameter of the sames_test() FUNCTION?*
> 
>>

Re: INSERT Trigger to check for existing records : Does this do what I hope it is doing?

[Adrian Klaver]

On 11/25/20 9:13 AM, Adrian Klaver wrote:

On 11/25/20 8:43 AM, ha...@datasundae.com wrote:

Adrian,





Until the previous questions are addressed the above is not doable.



3. Not positive the IF is doing what I want,  but if I copy a 
sfdc.dealid into the same_test() parameter field the FUNTION does 
update the hygiene_119.status field properly. To me, it appears I just 
need a way to iterate through and  insert one hygiene_119.dealid in 
the same_test parameter field. Then the UPDATE should flag all the 
hygiene_119 records as SAME or NEW. Obviously I don't REALLY need both 
flags as the absence of a flag would indicate status too.


Before continuing with the function I would try some SELECT functions 
that do what you want.





Would probably help if I expanded on that. So to find dealids that are 
new, something like:


SELECT
new_data.dealid
FROM
hygiene_119 AS new_data
LEFT JOIN
sfdc sp AS old_data
ON
new_data.dealid = old_data.dealid
WHERE
old_data.dealid IS NULL;

The LEFT JOIN will return all the dealids from hygiene_119 and if a 
dealid does not exist in sfdc the fields for it will be set to NULL. So 
by filtering on old_data.dealid IS NULL you find the dealids that exist 
in the new data but not the old.




Best,

Hagen





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

Re: postgres_fdw insert extremely slow

[Mats Julian Olsen]

On 25.11.2020 18:12, Tom Lane wrote:

Mats Julian Olsen  writes:

On 25.11.2020 17:58, Tom Lane wrote:

... Have you tried looking into pg_locks on the
remote server while this query is running?

Thanks Tom, I'll try to spin up a regular Postgres instance on both rds
and ec2 and see if that helps. As for the locks, I can not see any
blocked activity on the remote server while the query runs.

Another place to check is pg_stat_activity, specifically wait_event_type
and wait_event.


Thanks Tom, I've now setup the fdw from a local postgres instance and 
seeing similar timings for the selects and inserts.


I've got some more numbers here:

x (aurora aws eu-west-1) => y  (gcp gce us-central-1):  ~15 s

local (eu) => y: ~15 s

local (eu) => test 1 (postgres aws rds eu-west-1): ~4 s

local (eu) => test 2 (postgres google cloud sql, us-central-1): ~15s

local (eu) => local (eu): < 1s

SELECTs in all instances are sub-second.


To me this does indicate some sort of networking issue, but I'm 
wondering if INSERTs are treated differently than SELECTs in 
postgres_fdw? The only feasibly explanation I have is that postgres_fdw 
does many more network calls for INSERT than for SELECT, e.g. something 
like 1 for SELECT and `n` for INSERT?


Best,


Mats

Re: postgres_fdw insert extremely slow

[Tom Lane]

Mats Julian Olsen  writes:
> I've got some more numbers here:
> ...
> To me this does indicate some sort of networking issue, but I'm 
> wondering if INSERTs are treated differently than SELECTs in 
> postgres_fdw? The only feasibly explanation I have is that postgres_fdw 
> does many more network calls for INSERT than for SELECT, e.g. something 
> like 1 for SELECT and `n` for INSERT?

I don't have the code in front of me, but from memory, postgres_fdw
will issue an INSERT statement to the remote for each row it has to
insert.  Maybe you are indeed just dealing with spectacularly bad
network round trip times.

You could try turning on log_statement and/or log_duration on the
remote to see if that sheds any more light about how much time is
spent executing each insertion vs. the network delay.

There's been some recent discussion about teaching postgres_fdw to
batch insertions, which would likely be helpful in your situation.
I don't know how close that is to committable, but in any case
it couldn't see the light of day earlier than v14.  In the meantime,
if you're sufficiently desperate maybe you could switch to using
dblink with manually-issued multi-row INSERTs.  (This needn't
preclude continuing to access the table with postgres_fdw when
that's suitable.)

regards, tom lane

Re: Number of parallel workers chosen by the optimizer for parallel append

[Michael Lewis]

What have you tried? Changing the relevant cost parameters I assume?
Nothing else going on that may be taking up those workers, right?

https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-PARALLEL-SETUP-COST

limit of data type character varying

[Mark Phillips]

Ran into a problem with SymmetricDS when attempting to sync two postgres 12 
databases. 

The problem occurs when Symmetric builds the XML for a table that has the 
character varying datatype with an undefined length. SymmetricDS creates in the 
xml character field that is 2,147,483,647 length. Subsequently, when 
SymmetricDS attempts to create the table on the client, an error is raised that 
says the character varying field exceeds the maximum length. 

The actual maximum length supported by postgresql 12 is 10,485,760. We dug into 
the postgres code and found a limit in the config with a comment dating it to 
the pg 8.5 era. Being the simple folk that we are, we changed the setting to 
the higher value. Unfortunately, pg 12 raised some errors of its own.

This seems like a bug in SymmetricDS, which ought to set the maximum length to 
that supported by the target database.

On the other hand, one could argue that a higher limit in postgres is sensible 
these days. 

Additional Information  
1. symmetricds version 3.12.4
2. Postgresql Version 12
3. OS's Windows 10, Mac Catalina and SME Server

Regards,

Mark Phillips
Mophilly Technology Inc.
Telephone: (619) 296-0114
On the web at http://www.mophilly.com

Re: limit of data type character varying

[David G. Johnston]

On Wed, Nov 25, 2020 at 1:43 PM Mark Phillips 
wrote:

> The actual maximum length supported by postgresql 12 is 10,485,760. We dug
> into the postgres code and found a limit in the config with a comment
> dating it to the pg 8.5 era. Being the simple folk that we are, we changed
> the setting to the higher value. Unfortunately, pg 12 raised some errors of
> its own.
>
> This seems like a bug in SymmetricDS, which ought to set the maximum
> length to that supported by the target database.
>
> On the other hand, one could argue that a higher limit in postgres is
> sensible these days.
>
>
I find the documentation's recommendation relevant here:

In any case, the longest possible character string that can be stored is
about 1 GB. (The maximum value that will be allowed for n in the data type
declaration is less than that. It wouldn't be useful to change this because
with multibyte character encodings the number of characters and bytes can
be quite different. If you desire to store long strings with no specific
upper limit, use text or character varying without a length specifier,
rather than making up an arbitrary length limit.)

https://www.postgresql.org/docs/current/datatype-character.html

David J.

Potential BRIN Index Corruption

[Huan Ruan]

Hi All

We have a table with 623 million records. It appears a BRIN index of this
table on a timestamp column is missing some records, as illustrated below
in a cut-down version with additional columns and indices omitted.

We cannot work out a reproducible case but we have a copy of the offending
database. I was hoping to know

   1. if anyone else has experienced similar issues
   2. if anyone can shed some light on what to collect in order to fire a
   useful bug report

Version

   - centos-release-7-7.1908.0.el7.centos.x86_64
   - PostgreSQL 10.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
   20150623 (Red Hat 4.8.5-39), 64-bit


Table DDL
CREATE TABLE large_table_with_623m_records (
  date_with_btree_index date,
  ts_with_brin_index timestamp without time zone not null
);

CREATE INDEX date_bree_index ON large_table_with_623m_records
  USING btree (date_with_btree_index COLLATE pg_catalog."default"
, date_with_btree_index);

CREATE INDEX ts_brin_index ON large_table_with_623m_records
  USING brin (ts_with_brin_index);


Query
SELECT
  *
FROM large_table_with_623m_records
WHERE
  ts_with_brin_index >= '2018-06-29 12:12:50' AND ts_with_brin_index <
'2018-06-29 12:13:00'
  AND date_with_btree_index = '2013-05-21'

This query uses Index Scan on date_bree_index and correctly returns 1
record that has ts_with_brin_index = '2018-06-29 12:12:58:081'.

If I remove the last line (AND date_with_btree_index = '2013-05-21'), the
query uses Bitmap Index Scan on ts_brin_index and incorrectly returns 0
record.

After a reindex of ts_brin_index, both variations of the query correctly
return 1 record.

Thanks
Huan

Re: Potential BRIN Index Corruption

[Alvaro Herrera]

On 2020-Nov-26, Huan Ruan wrote:

> Hi All
> 
> We cannot work out a reproducible case but we have a copy of the offending
> database. I was hoping to know

I think the way to debug this would be to see what WAL records have been
emitted for the index, using pageinspect to find the problem index
tuple.

Use 'select ctid rrom large_table_with_623m_records where ...' to
pinpoint the unindexed tuple's page number; see when (in LSN) was that
tuple written; inspect WAL surroundings looking for updates (or lack
thereof) for the BRIN index.  Use pageinspect to examine raw brin data.

Re: archive file "00000001000000000000006F" has wrong size: 67118648 instead of 16777216

[江川潔]

Hi
I have corrected it on "restore_command = 'copy
"D:\\BKUP\\pg_archivedir\\PostgreSQL_DEV\\%f" "%p"'".
Thanks,
Kiyoshi

2020年11月25日(水) 19:35 Magnus Hagander :

> On Wed, Nov 25, 2020 at 3:02 AM 江川潔  wrote:
> >
> > Hi,
> >
> > WAL log recovery was failed on wrong log record size. Could you please
> advise me what is wrong in the setting ? Any suggestions will be highly
> appreciated.
> >
> > Thanks,
> > Kiyoshi
> >
> > postgres.conf:
> > wal_level = replica
> > archive_mode = on
> > archive_command = 'copy "%p"
> "D:\\BKUP\\pg_archivedir\\PostgreSQL_DEV\\%f"'
> >
> > recover.conf:
> > restore_command = 'copy "D:\\BKUP\\pg_archivedir\\PostgreSQL_DEV" "%p"'
>
> Does your restore command really not have a %f in it anywhere? That
> definitely seems wrong... But it does seem to copy some files correct,
> which would be weird if it doesn't. Mistake in the report, or is there
> something really weird going on with that PostgreSQL_DEV not being a
> directory but instead some "magic file"?
>
> --
>  Magnus Hagander
>  Me: https://www.hagander.net/
>  Work: https://www.redpill-linpro.com/
>

meaning of (to prevent wraparound) ..??

[Atul Kumar]

Hi,

I have autovacuum running in background from last 30-40 minutes but I
noticed that it is with "(to prevent wraparound)" say for example
autovacuum: VACUUM trk.move (to prevent wraparound).

So what is the meaning of "(to prevent wraparound)" here ?

Will it impact the autovacuum anyhow ?

Please help me.



Regards,
Atul

Re: meaning of (to prevent wraparound) ..??

[Jessica Sharp]

Hi Atul,

This means autovacuum is doing it’s job — most likely no need to be alarmed
here based on this alone. The autovacuum is helping avoid a txid limit.
This is part of PostgreSQL MVCC. You may check that section of the
documentation for more information on this.

On Wed, Nov 25, 2020 at 22:54 Atul Kumar  wrote:

> Hi,
>
> I have autovacuum running in background from last 30-40 minutes but I
> noticed that it is with "(to prevent wraparound)" say for example
> autovacuum: VACUUM trk.move (to prevent wraparound).
>
> So what is the meaning of "(to prevent wraparound)" here ?
>
> Will it impact the autovacuum anyhow ?
>
> Please help me.
>
>
>
> Regards,
> Atul
>
>
> --
Kind regards,
Jessica Sharp
-
(469) 602-2363

Re: meaning of (to prevent wraparound) ..??

[Atul Kumar]

Thanks Jessica. Could help me out by sharing documents that can help me
understand “to prevent wraparound “ in simplest way, postgres doc is little
bit harder for a newbee like me.




Regards
Atul




On Thursday, November 26, 2020, Jessica Sharp 
wrote:

> Hi Atul,
>
> This means autovacuum is doing it’s job — most likely no need to be
> alarmed here based on this alone. The autovacuum is helping avoid a txid
> limit. This is part of PostgreSQL MVCC. You may check that section of the
> documentation for more information on this.
>
> On Wed, Nov 25, 2020 at 22:54 Atul Kumar  wrote:
>
>> Hi,
>>
>> I have autovacuum running in background from last 30-40 minutes but I
>> noticed that it is with "(to prevent wraparound)" say for example
>> autovacuum: VACUUM trk.move (to prevent wraparound).
>>
>> So what is the meaning of "(to prevent wraparound)" here ?
>>
>> Will it impact the autovacuum anyhow ?
>>
>> Please help me.
>>
>>
>>
>> Regards,
>> Atul
>>
>>
>> --
> Kind regards,
> Jessica Sharp
> -
> (469) 602-2363
>

Re: meaning of (to prevent wraparound) ..??

[Jessica Sharp]

Absolutely. This may be helpful:
https://youtu.be/bA1SgWn6Gv4

On Wed, Nov 25, 2020 at 23:09 Atul Kumar  wrote:

> Thanks Jessica. Could help me out by sharing documents that can help me
> understand “to prevent wraparound “ in simplest way, postgres doc is little
> bit harder for a newbee like me.
>
>
>
>
> Regards
> Atul
>
>
>
>
> On Thursday, November 26, 2020, Jessica Sharp 
> wrote:
>
>> Hi Atul,
>>
>> This means autovacuum is doing it’s job — most likely no need to be
>> alarmed here based on this alone. The autovacuum is helping avoid a txid
>> limit. This is part of PostgreSQL MVCC. You may check that section of the
>> documentation for more information on this.
>>
>> On Wed, Nov 25, 2020 at 22:54 Atul Kumar  wrote:
>>
>>> Hi,
>>>
>>> I have autovacuum running in background from last 30-40 minutes but I
>>> noticed that it is with "(to prevent wraparound)" say for example
>>> autovacuum: VACUUM trk.move (to prevent wraparound).
>>>
>>> So what is the meaning of "(to prevent wraparound)" here ?
>>>
>>> Will it impact the autovacuum anyhow ?
>>>
>>> Please help me.
>>>
>>>
>>>
>>> Regards,
>>> Atul
>>>
>>>
>>> --
>> Kind regards,
>> Jessica Sharp
>> -
>> (469) 602-2363
>>
> --
Kind regards,
Jessica Sharp
-
(469) 602-2363

Re: meaning of (to prevent wraparound) ..??

[Michael Paquier]

On Wed, Nov 25, 2020 at 11:10:50PM -0700, Jessica Sharp wrote:
> On Wed, Nov 25, 2020 at 23:09 Atul Kumar  wrote: 
>> Thanks Jessica. Could help me out by sharing documents that can help me
>> understand “to prevent wraparound “ in simplest way, postgres doc is little
>> bit harder for a newbee like me.
>
> Absolutely. This may be helpful:
> https://youtu.be/bA1SgWn6Gv4

If you have any suggestions on how to improve this part of the docs,
please feel free:
https://www.postgresql.org/docs/devel/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

Note that due to the critical work that such vacuum jobs do, they
cannot be cancelled.
--
Michael


signature.asc
Description: PGP signature