Clean build MINGW64 CreateProcessAsUserA could not be located

[Yannick Collette]

Hello,

I have compiled postgres 11.11 using the following configuration without
errors.

# gcc --version
gcc.exe (Rev6, Built by MSYS2 project) 10.2.0

./configure --host=x86_64-w64-mingw32 --prefix=/c/postgresql/pg11/
--with-openssl --with-libxml

make without errors
make install without errors also.

initdb.exe error
The procedure entry point CreateProcessAsUserA could not be located in
dynamic link library...

Found related bug and applied solution without any success.

CFLAGS="-D WINVER=0x0600 -D _WIN32_WINNT=0x0600" LIBS="-ladvapi32"
./configure --host=x86_64-w64-mingw32 --prefix=/c/postgresql/pg11/
--with-openssl --with-libxml

https://www.postgresql-archive.org/BUG-16774-PostgreSQL-clean-build-MINGW64-gcc-but-initdb-fails-cannot-find-startadress-CreateProcessAA-td6167336.html

Thanks in advance for your inputs.

Yannick

Re: Batch update million records in prd DB

[Michael Lewis]

1) Don't pretend it is a left join when your where clause will turn it into
an INNER join.
LEFT JOIN pol gp ON gab.policy_id = gp.id
WHERE

*AND gp.name  LIKE 'Mobile backup%'
AND gp.deleted_at IS NOT NULL;*

2) It is interesting to me that the row estimates are stable, but the
number of rows filtered out and that are found by those two index
scans changes so dramatically. Is your underlying data changing
significantly during this run? Maybe I am not seeing something that should
be obvious.

3) What is the execution plan for the update based on the temp table? It is
hard to believe it takes 2 seconds to update 1000 rows. By the way, that
temp table needs to be analyzed after it is created & populated with data,
or the planner won't know how many rows it contains or any other stats
about it. One advantage of the temp table should be that you have already
found all the candidate rows and so the time that locks are held to update
the 1000 target rows is smaller. Given you are doing a order by & limit in
the use of the temp table, I might actually create an index on the id
column to help the later runs. The temp table should likely remain in
memory (temp_buffers) but still, btree is nice for ordered use.

>

Re: Code of Conduct: Russian Translation for Review

[Alexander Lakhin]

Hello,

27.02.2021 03:51, Stacey Haysler wrote:
> The PostgreSQL Community Code of Conduct Committee has received a
> draft of the Russian translation of the Code of Conduct Policy updated
> August 18, 2020 for review.
>
> The English version of the Policy is at:
> https://www.postgresql.org/about/policies/coc/
>
> The translation was created by:
> Anastasia Raspopina
>
> The translation was reviewed by:
> Anastasia Lubennikova (Please note: she is a member of the CoC Committee)
> Alexander Lakhin
Please look at my additional comments.

Best regards,
Alexander


PostgreSQL Code of Conduct - Russian Translation Feb 26 2021+.docx
Description: MS-Word 2007 document

Re: Batch update million records in prd DB

[Yi Sun]

Hi Michael

This is the script and explain plan info, please check, seems  Filter
remove more records took more time

DO $MAIN$
DECLARE
affect_count integer := 1000;
processed_row_count integer := 0;
BEGIN
LOOP
exit
WHEN affect_count = 0;
UPDATE
app gaa
SET
deleted_at = (
SELECT
CAST(extract(epoch FROM now() at time zone 'utc') *
10 AS bigint))
WHERE
gaa.id IN (
SELECT
gab.id
FROM
app gab
LEFT JOIN pol gp ON gab.policy_id = gp.id
WHERE
gab.policy_type = 'policy.protection.total'
AND gp.name LIKE 'Mobile backup%'
AND gab.deleted_at IS NULL
AND gp.deleted_at IS NOT NULL
LIMIT 1000);
GET DIAGNOSTICS affect_count = ROW_COUNT;
COMMIT;
processed_row_count = processed_row_count + affect_count;
END LOOP;
RAISE NOTICE 'total processed rows %', processed_row_count;
END;
$MAIN$;

--early explain plan, 1000 records update take 156.488 ms

 Update on app gaa  (cost=3307.57..6085.41 rows=1000 width=3943) (actual
time=156.347..156.347 rows=0 loops=1)
   InitPlan 1 (returns $0)
 ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.007..0.008
rows=1 loops=1)
   ->  Nested Loop  (cost=3307.54..6085.39 rows=1000 width=3943) (actual
time=18.599..33.987 rows=1000 loops=1)
 ->  HashAggregate  (cost=3306.99..3316.99 rows=1000 width=98)
(actual time=18.554..19.085 rows=1000 loops=1)
   Group Key: ("ANY_subquery".id)::text
   ->  Subquery Scan on "ANY_subquery"  (cost=2.17..3304.49
rows=1000 width=98) (actual time=0.041..18.052 rows=1000 loops=1)
 ->  Limit  (cost=2.17..3294.49 rows=1000 width=37)
(actual time=0.030..17.827 rows=1000 loops=1)
   ->  Merge Join  (cost=2.17..877396.03
rows=266497 width=37) (actual time=0.029..17.764 rows=1000 loops=1)
 Merge Cond: ((gab.policy_id)::text = (gp.id
)::text)
 ->  Index Scan using
tmp_uq_policy_id_context2 on app gab  (cost=0.56..487631.06 rows=3151167
width=74) (actual time=0.018..9.192 rows=3542 loops=1)
   Filter: ((policy_type)::text =
'policy.protection.total'::text)
   Rows Removed by Filter: 2064
 ->  Index Scan using pol_pkey on pol gp
 (cost=0.56..378322.78 rows=361105 width=37) (actual time=0.008..7.380
rows=1006 loops=1)
   Filter: ((deleted_at IS NOT NULL)
AND (name ~~ 'Mobile backup%'::text))
   Rows Removed by Filter: 3502
 ->  Index Scan using app2_pkey on app gaa  (cost=0.56..2.77 rows=1
width=3874) (actual time=0.014..0.014 rows=1 loops=1000)
   Index Cond: ((id)::text = ("ANY_subquery".id)::text)
 Planning Time: 0.852 ms
 Execution Time: 156.488 ms

--later explain plan, 1000 records update take 13301.600 ms
--
 Update on app gaa  (cost=3789.35..6567.19 rows=1000 width=3980) (actual
time=13301.466..13301.466 rows=0 loops=1)
   InitPlan 1 (returns $0)
 ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.007..0.007
rows=1 loops=1)
   ->  Nested Loop  (cost=3789.32..6567.17 rows=1000 width=3980) (actual
time=12881.004..12896.440 rows=1000 loops=1)
 ->  HashAggregate  (cost=3788.77..3798.77 rows=1000 width=98)
(actual time=12880.958..12881.378 rows=1000 loops=1)
   Group Key: ("ANY_subquery".id)::text
   ->  Subquery Scan on "ANY_subquery"  (cost=2.17..3786.27
rows=1000 width=98) (actual time=12850.663..12880.505 rows=1000 loops=1)
 ->  Limit  (cost=2.17..3776.27 rows=1000 width=37)
(actual time=12850.656..12880.233 rows=1000 loops=1)
   ->  Merge Join  (cost=2.17..862421.74
rows=228510 width=37) (actual time=12850.655..12880.162 rows=1000 loops=1)
 Merge Cond: ((gab.policy_id)::text = (gp.id
)::text)
 ->  Index Scan using
tmp_uq_policy_id_context2 on app gab  (cost=0.56..474159.31 rows=2701994
width=74) (actual time=0.017..6054.269 rows=2302988 loops=1)
   Filter: ((policy_type)::text =
'policy.protection.total'::text)
   Rows Removed by Filter: 1822946
 ->  Index Scan using pol_pkey on pol gp
 (cost=0.56..378322.78 rows=361105 width=37) (actual time=0.007..5976.346
rows=936686 loops=1)
   Filter: ((deleted_at IS NOT NULL)
AND (name ~~ 'Mobile