Postgresql + containerization possible use case

[Achilleas Mantzios]

Hi
we are running some 140 remote servers (in the 7 seas via satellite 
connections), and in each one of them we run:
- jboss
- postgresql
- uucp (not as a daemon)
- gpsd
- samba
- and possibly some other services

Hardware and software upgrades are very hard since there is no physical access 
to those servers by trained personnel, and also there is a diversity of 
software versions.

The idea for future upgrades is to containerize certain aspects of the 
software. The questions are (I am not skilled in docker, only minimal contact 
with lxd) :
- is this a valid use case for containerization?
- are there any gotchas around postgersql, the reliability of the system ?
- since we are talking about 4+ basic services (pgsqk, jboss, uucp, samba), is 
docker a good fit or should we be looking into lxd as well?
- are there any success stories of other after following a similar path?

Thank you!

PS

For those who wonder about UUCP, UUCP was our comms solution prior we installed TCP/IP on the vessels. However, to this day, it provides a nice management layer, a sort of protection before data leave 
the vessel or reach the vessel, in a user controlled manner. So uucp stayed as it matched exactly the business as far data transfers and emails are concerned. It would be hard to uniformly manage data 
transfers and emails in/out in a plain TCP/IP setup (rsync, ftp, etc, sendmail/exim4/postfix or other MTA).


--
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt

What is the best way to redefine a trigger? (lock issue)

[Marc Mamin]

What is the best way to redefine a trigger? (lock issue)


Hello,
I have deployment/migration scripts that require to be idempotent.

When (re)defining or deleting triggers,  I've lately observed locked statements 
that seemed never to release (waited for a few hours).
affected version: PG 10 (and probably PG 12 ?)

My case is similar to that old description and I wonder if the recommendation 
to first change the trigger function to a no-op function still make sense.

https://stackoverflow.com/questions/24738354/how-to-drop-a-trigger-in-a-resilient-manner-in-postgresql.


In the first observed case, with a test db, I did kill all  existing 
connections to the db and tried to drop the trigger with a fresh new connection.
This again resulted in a long lasting lock and I gave up, tipping on a db 
corruption.

What does happen in the background, that can make a trigger deletion fail? 
Are there situation where row level locks instead of table level locks are 
acquired?
Coul background processeslike vacuumplay a role here?

As I've observed this problem only a very few times, I guess it is not easily 
reproducable.

attached is an picture of pg_stat_activity during such a lock,

thanks,
Marc Mamin

here an example of a such a deployment/migration script, all of these scripts 
are applied  sequentially in separate transactions:
===
SET client_min_messages=error;

CREATE OR REPLACE FUNCTION block_item_cancel()
  RETURNS TRIGGER AS
$BODY$
DECLARE
blockedItemLevel int;
client int;

BEGIN
WITH RECURSIVE rec as
(
SELECT s.id as clientref, s."parentRef", 
a."fruitRef" 
FROM "ClientDO" s LEFT JOIN "Fruit2ClientDO" a 
ON (s.id=a."clientRef" and a."fruitRef" = NEW."fruitRef")
WHERE s.id = (select "clientRef" from "SeenDO" 
where "id" = NEW."SeenRef")
UNION ALL
SELECT s2.id as clientref, s2."parentRef", 
a2."fruitRef" 
FROM rec 
JOIN "ClientDO" s2 on (s2.id=rec."parentRef")
LEFT JOIN LATERAL (select"fruitRef" from 
"Fruit2ClientDO" ax WHERE rec."parentRef"=ax."clientRef" and ax."fruitRef" = 
NEW."fruitRef")  a2 
   ON TRUE
WHERE rec."parentRef" IS NOT NULL
--Only first matching client should be used
AND rec."fruitRef" IS NULL
)
SELECT clientref
FROM rec 
WHERE "fruitRef" is not null
INTO client;

blockedItemLevel = (NEW."quantitySeened" - NEW."quantityCanceled");
IF blockedItemLevel > 0 THEN

UPDATE "BlockedItemAO" SET
"blockedItem" = blockedItemLevel,
"modificationDate" = now()
WHERE "SeenPosRef" = NEW."id";
ELSE
DELETE FROM "BlockedItemAO" WHERE "SeenPosRef" = NEW."id";
END IF;
RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql
  COST 100;


DROP TRIGGER IF EXISTS block_item_cancel ON "SeenPosDO";

CREATE TRIGGER block_item_cancel
  AFTER UPDATE OF "quantityCanceled"
  ON "SeenPosDO"
  FOR EACH ROW
  WHEN ( NEW."providerRef" <> 1
 AND 
   (
  NEW."quantityCanceled" IS DISTINCT FROM OLD."quantityCanceled"
  
   )
   )
  EXECUTE PROCEDURE block_item_cancel();

Re: What is the best way to redefine a trigger? (lock issue)

[Achilleas Mantzios]

On 10/12/21 11:27 π.μ., Marc Mamin wrote:

What is the best way to redefine a trigger? (lock issue)


Hello,
I have deployment/migration scripts that require to be idempotent.

When (re)defining or deleting triggers,  I've lately observed locked statements 
that seemed never to release (waited for a few hours).
affected version: PG 10 (and probably PG 12 ?)

My case is similar to that old description and I wonder if the recommendation 
to first change the trigger function to a no-op function still make sense.

https://stackoverflow.com/questions/24738354/how-to-drop-a-trigger-in-a-resilient-manner-in-postgresql.


In the first observed case, with a test db, I did kill all  existing 
connections to the db and tried to drop the trigger with a fresh new connection.
This again resulted in a long lasting lock and I gave up, tipping on a db 
corruption.

What does happen in the background, that can make a trigger deletion fail?

A DROP TRIGGER will try to acquire an AccessExclusiveLock on the table, which 
conflicts with any table level lock (e.g a select acquires an access share 
lock, so it would cause the DROP TRIGGER to wait.

Unfortunately I don't see this in the official docs: 
https://www.postgresql.org/docs/11/explicit-locking.html .


Are there situation where row level locks instead of table level locks are 
acquired?
Coul background processeslike vacuumplay a role here?

As I've observed this problem only a very few times, I guess it is not easily 
reproducable.

It is very easily reproducible. begin; select .. in one session, begin; drop 
trigger in a second session.

Do you need to drop/create the trigger or a CREATE OR REPLACE function would 
suffice?



attached is an picture of pg_stat_activity during such a lock,

thanks,
Marc Mamin

here an example of a such a deployment/migration script, all of these scripts 
are applied  sequentially in separate transactions:
===
SET client_min_messages=error;

CREATE OR REPLACE FUNCTION block_item_cancel()
   RETURNS TRIGGER AS
$BODY$
DECLARE
blockedItemLevel int;
client int;

BEGIN
WITH RECURSIVE rec as
(
SELECT s.id as clientref, s."parentRef", 
a."fruitRef"
FROM "ClientDO" s LEFT JOIN "Fruit2ClientDO" a ON 
(s.id=a."clientRef" and a."fruitRef" = NEW."fruitRef")
WHERE s.id = (select "clientRef" from "SeenDO" where "id" 
= NEW."SeenRef")
UNION ALL
SELECT s2.id as clientref, s2."parentRef", 
a2."fruitRef"
FROM rec
JOIN "ClientDO" s2 on (s2.id=rec."parentRef")
LEFT JOIN LATERAL (select"fruitRef" from "Fruit2ClientDO" ax WHERE 
rec."parentRef"=ax."clientRef" and ax."fruitRef" = NEW."fruitRef")  a2
   ON TRUE
WHERE rec."parentRef" IS NOT NULL
--Only first matching client should be used
AND rec."fruitRef" IS NULL
)
SELECT clientref
FROM rec
WHERE "fruitRef" is not null
INTO client;

blockedItemLevel = (NEW."quantitySeened" - NEW."quantityCanceled");
IF blockedItemLevel > 0 THEN

UPDATE "BlockedItemAO" SET
"blockedItem" = blockedItemLevel,
"modificationDate" = now()
WHERE "SeenPosRef" = NEW."id";
ELSE
DELETE FROM "BlockedItemAO" WHERE "SeenPosRef" = NEW."id";
END IF;
RETURN NEW;
END;
$BODY$
   LANGUAGE plpgsql
   COST 100;


DROP TRIGGER IF EXISTS block_item_cancel ON "SeenPosDO";

CREATE TRIGGER block_item_cancel
   AFTER UPDATE OF "quantityCanceled"
   ON "SeenPosDO"
   FOR EACH ROW
   WHEN ( NEW."providerRef" <> 1
  AND
(
   NEW."quantityCanceled" IS DISTINCT FROM OLD."quantityCanceled"
)
)
   EXECUTE PROCEDURE block_item_cancel();





--
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt

RE: What is the best way to redefine a trigger? (lock issue)

[Marc Mamin]

   >> Hello,
   >> I have deployment/migration scripts that require to be idempotent.
   >>
   >> When (re)defining or deleting triggers,  I've lately observed locked 
statements that seemed never to release (waited for a few hours).
   >> affected version: PG 10 (and probably PG 12 ?)
   >>
   >> My case is similar to that old description and I wonder if the 
recommendation to first change the trigger function to a no-op function still 
make sense.
   >>
   >> 
https://stackoverflow.com/questions/24738354/how-to-drop-a-trigger-in-a-resilient-manner-in-postgresql.
   >>
   >>
   >> In the first observed case, with a test db, I did kill all  existing 
connections to the db and tried to drop the trigger with a fresh new connection.
   >> This again resulted in a long lasting lock and I gave up, tipping on a db 
corruption.
   >>
   >> What does happen in the background, that can make a trigger deletion fail?
   >A DROP TRIGGER will try to acquire an AccessExclusiveLock on the table, 
which conflicts with any table level lock (e.g a select acquires an access 
share lock, so it would cause the DROP TRIGGER to wait.
   >
   >Unfortunately I don't see this in the official docs: 
https://www.postgresql.org/docs/11/explicit-locking.html .
   >
   >> Are there situation where row level locks instead of table level locks 
are acquired?
   >> Coul background processeslike vacuumplay a role here?
   >>
   >> As I've observed this problem only a very few times, I guess it is not 
easily reproducable.
 
   >It is very easily reproducible. begin; select .. in one session, begin; 
drop trigger in a second session.

You can see in the attachment, that the lock exists without any other apparent 
conflicting  session.

   >Do you need to drop/create the trigger or a CREATE OR REPLACE function 
would suffice?

There are different use cases. Sometimes I only need to drop a trigger or 
modify its definition (not the function)


   >>
   >> attached is an picture of pg_stat_activity during such a lock,
   >>
   >> thanks,
   >> Marc Mamin
   >>
   >> here an example of a such a deployment/migration script, all of these 
scripts are applied  sequentially in separate transactions:
   >> ===
   >> SET client_min_messages=error;
   >>
   >> CREATE OR REPLACE FUNCTION block_item_cancel()
   >>RETURNS TRIGGER AS
   >> $BODY$
   >> DECLARE
   >>   blockedItemLevel int;
   >>   client int;
   >>
   >> BEGIN
   >>   WITH RECURSIVE rec as
   >>   (
   >>   SELECT s.id as clientref, s."parentRef", 
a."fruitRef"
   >>   FROM "ClientDO" s LEFT JOIN "Fruit2ClientDO" a 
ON (s.id=a."clientRef" and a."fruitRef" = NEW."fruitRef")
   >>   WHERE s.id = (select "clientRef" from "SeenDO" 
where "id" = NEW."SeenRef")
   >>   UNION ALL
   >>   SELECT s2.id as clientref, s2."parentRef", 
a2."fruitRef"
   >>   FROM rec
   >>   JOIN "ClientDO" s2 on (s2.id=rec."parentRef")
   >>   LEFT JOIN LATERAL (select"fruitRef" from 
"Fruit2ClientDO" ax WHERE rec."parentRef"=ax."clientRef" and ax."fruitRef" = 
NEW."fruitRef")  a2
   >>  ON TRUE
   >>   WHERE rec."parentRef" IS NOT NULL
   >>   --Only first matching client should be used
   >>   AND rec."fruitRef" IS NULL
   >>   )
   >>   SELECT clientref
   >>   FROM rec
   >>   WHERE "fruitRef" is not null
   >>   INTO client;
   >>
   >>   blockedItemLevel = (NEW."quantitySeened" - NEW."quantityCanceled");
   >>   IF blockedItemLevel > 0 THEN
   >>
   >>   UPDATE "BlockedItemAO" SET
   >>   "blockedItem" = blockedItemLevel,
   >>   "modificationDate" = now()
   >>   WHERE "SeenPosRef" = NEW."id";
   >>   ELSE
   >>   DELETE FROM "BlockedItemAO" WHERE "SeenPosRef" = NEW."id";
   >>   END IF;
   >>   RETURN NEW;
   >> END;
   >> $BODY$
   >>LANGUAGE plpgsql
   >>COST 100;
   >>
   >>
   >> DROP TRIGGER IF EXISTS block_item_cancel ON "SeenPosDO";
   >>
   >> CREATE TRIGGER block_item_cancel
   >>AFTER UPDATE OF "quantityCanceled"
   >>ON "SeenPosDO"
   >>FOR EACH ROW
   >>WHEN ( NEW."providerRef" <> 1
   >>   AND
   >> (
   >>NEW."quantityCanceled" IS DISTINCT FROM 
OLD."quantityCanceled"
   >> )
   >> )
   >>EXECUTE PROCEDURE block_item_cancel();
   >>
   >>
   >
   >
   >-- 
   >Achilleas Mantzios
   >DBA, Analyst, IT Lead
   >IT DEPT
   >Dynacom Tankers Mgmt
   >
   >
   >
   >

Re: What is the best way to redefine a trigger? (lock issue)

[Achilleas Mantzios]

On 10/12/21 12:20 μ.μ., Marc Mamin wrote:


>> Hello,
>> I have deployment/migration scripts that require to be idempotent.
>>
>> When (re)defining or deleting triggers,  I've lately observed locked 
statements that seemed never to release (waited for a few hours).
>> affected version: PG 10 (and probably PG 12 ?)
>>
>> My case is similar to that old description and I wonder if the 
recommendation to first change the trigger function to a no-op function still make 
sense.
>>
>> 
https://stackoverflow.com/questions/24738354/how-to-drop-a-trigger-in-a-resilient-manner-in-postgresql.
>>
>>
>> In the first observed case, with a test db, I did kill all  existing 
connections to the db and tried to drop the trigger with a fresh new connection.
>> This again resulted in a long lasting lock and I gave up, tipping on a 
db corruption.
>>
>> What does happen in the background, that can make a trigger deletion 
fail?
>A DROP TRIGGER will try to acquire an AccessExclusiveLock on the table, 
which conflicts with any table level lock (e.g a select acquires an access share 
lock, so it would cause the DROP TRIGGER to wait.
>
>Unfortunately I don't see this in the official docs: 
https://www.postgresql.org/docs/11/explicit-locking.html .
>
>> Are there situation where row level locks instead of table level locks 
are acquired?
>> Coul background processeslike vacuumplay a role here?
>>
>> As I've observed this problem only a very few times, I guess it is not 
easily reproducable.
  
>It is very easily reproducible. begin; select .. in one session, begin; drop trigger in a second session.


You can see in the attachment, that the lock exists without any other apparent 
conflicting  session.

It takes two or more to tango. Next time it happens query the pg_locks view, it 
contains info about locks on objects.
The holding lock is shown as granted, the waiting lock as not granted.


>Do you need to drop/create the trigger or a CREATE OR REPLACE function 
would suffice?

There are different use cases. Sometimes I only need to drop a trigger or 
modify its definition (not the function)

Are you using pgbouncer or some other means of suspending traffic into the DB?


>>
>> attached is an picture of pg_stat_activity during such a lock,
>>
>> thanks,
>> Marc Mamin
>>
>> here an example of a such a deployment/migration script, all of these 
scripts are applied  sequentially in separate transactions:
>> ===
>> SET client_min_messages=error;
>>
>> CREATE OR REPLACE FUNCTION block_item_cancel()
>>RETURNS TRIGGER AS
>> $BODY$
>> DECLARE
>>blockedItemLevel int;
>>client int;
>>
>> BEGIN
>>WITH RECURSIVE rec as
>>(
>>SELECT s.id as clientref, s."parentRef", 
a."fruitRef"
>>FROM "ClientDO" s LEFT JOIN "Fruit2ClientDO" a ON 
(s.id=a."clientRef" and a."fruitRef" = NEW."fruitRef")
>>WHERE s.id = (select "clientRef" from "SeenDO" where 
"id" = NEW."SeenRef")
>>UNION ALL
>>SELECT s2.id as clientref, s2."parentRef", 
a2."fruitRef"
>>FROM rec
>>JOIN "ClientDO" s2 on (s2.id=rec."parentRef")
>>LEFT JOIN LATERAL (select"fruitRef" from "Fruit2ClientDO" ax WHERE 
rec."parentRef"=ax."clientRef" and ax."fruitRef" = NEW."fruitRef")  a2
>>   ON TRUE
>>WHERE rec."parentRef" IS NOT NULL
>>--Only first matching client should be used
>>AND rec."fruitRef" IS NULL
>>)
>>SELECT clientref
>>FROM rec
>>WHERE "fruitRef" is not null
>>INTO client;
>>
>>blockedItemLevel = (NEW."quantitySeened" - NEW."quantityCanceled");
>>IF blockedItemLevel > 0 THEN
>>
>>UPDATE "BlockedItemAO" SET
>>"blockedItem" = blockedItemLevel,
>>"modificationDate" = now()
>>WHERE "SeenPosRef" = NEW."id";
>>ELSE
>>DELETE FROM "BlockedItemAO" WHERE "SeenPosRef" = NEW."id";
>>END IF;
>>RETURN NEW;
>> END;
>> $BODY$
>>LANGUAGE plpgsql
>>COST 100;
>>
>>
>> DROP TRIGGER IF EXISTS block_item_cancel ON "SeenPosDO";
>>
>> CREATE TRIGGER block_item_cancel
>>AFTER UPDATE OF "quantityCanceled"
>>ON "SeenPosDO"
>>FOR EACH ROW
>>WHEN ( NEW."providerRef" <> 1
>>   AND
>> (
>>NEW."quantityCanceled" IS DISTINCT FROM 
OLD."quantityCanceled"
>> )
>> )
>>EXECUTE PROCEDURE b

RE: What is the best way to redefine a trigger? (lock issue)

[Marc Mamin]

   >
   >-Original Message-
   >From: Achilleas Mantzios  
   >Sent: Freitag, 10. Dezember 2021 11:36
   >To: pgsql-general@lists.postgresql.org
   >Subject: Re: What is the best way to redefine a trigger? (lock issue)
   >
   >On 10/12/21 12:20 μ.μ., Marc Mamin wrote:
   >>
   >> >> Hello,
   >> >> I have deployment/migration scripts that require to be idempotent.
   >> >>
   >> >> When (re)defining or deleting triggers,  I've lately observed 
locked statements that seemed never to release (waited for a few hours).
   >> >> affected version: PG 10 (and probably PG 12 ?)
   >> >>
   >> >> My case is similar to that old description and I wonder if the 
recommendation to first change the trigger function to a no-op function still 
make sense.
   >> >>
   >> >> 
https://stackoverflow.com/questions/24738354/how-to-drop-a-trigger-in-a-resilient-manner-in-postgresql.
   >> >>
   >> >>
   >> >> In the first observed case, with a test db, I did kill all  
existing connections to the db and tried to drop the trigger with a fresh new 
connection.
   >> >> This again resulted in a long lasting lock and I gave up, tipping 
on a db corruption.
   >> >>
   >> >> What does happen in the background, that can make a trigger 
deletion fail?
   >> >A DROP TRIGGER will try to acquire an AccessExclusiveLock on the 
table, which conflicts with any table level lock (e.g a select acquires an 
access share lock, so it would cause the DROP TRIGGER to wait.
   >> >
   >> >Unfortunately I don't see this in the official docs: 
https://www.postgresql.org/docs/11/explicit-locking.html .
   >> >
   >> >> Are there situation where row level locks instead of table level 
locks are acquired?
   >> >> Coul background processeslike vacuumplay a role here?
   >> >>
   >> >> As I've observed this problem only a very few times, I guess it is 
not easily reproducable.
   >>   
   >> >It is very easily reproducible. begin; select .. in one session, 
begin; drop trigger in a second session.
   >>
   >> You can see in the attachment, that the lock exists without any other 
apparent conflicting  session.
   >It takes two or more to tango. Next time it happens query the pg_locks 
view, it contains info about locks on objects.
   >The holding lock is shown as granted, the waiting lock as not granted.
   
Yes, It looks for me like a situation which should not be possible at all.
I'll try to get more informations if the problem pop up again.
Thanks.
   
   >> >Do you need to drop/create the trigger or a CREATE OR REPLACE 
function would suffice?
   >>
   >> There are different use cases. Sometimes I only need to drop a trigger or 
modify its definition (not the function)
   >Are you using pgbouncer or some other means of suspending traffic into the 
DB?

No. In my tries to repare the situation, there were no other clients but psql 
and DBaever.

   >> >>
   >> >> attached is an picture of pg_stat_activity during such a lock,
   >> >>
   >> >> thanks,
   >> >> Marc Mamin
   >> >>
   >> >> here an example of a such a deployment/migration script, all of 
these scripts are applied  sequentially in separate transactions:
   >> >> ===
   >> >> SET client_min_messages=error;
   >> >>
   >> >> CREATE OR REPLACE FUNCTION block_item_cancel()
   >> >>RETURNS TRIGGER AS
   >> >> $BODY$
   >> >> DECLARE
   >> >>blockedItemLevel int;
   >> >>client int;
   >> >>
   >> >> BEGIN
   >> >>WITH RECURSIVE rec as
   >> >>(
   >> >>SELECT s.id as clientref, 
s."parentRef", a."fruitRef"
   >> >>FROM "ClientDO" s LEFT JOIN 
"Fruit2ClientDO" a ON (s.id=a."clientRef" and a."fruitRef" = NEW."fruitRef")
   >> >>WHERE s.id = (select "clientRef" from 
"SeenDO" where "id" = NEW."SeenRef")
   >> >>UNION ALL
   >> >>SELECT s2.id as clientref, 
s2."parentRef", a2."fruitRef"
   >> >>FROM rec
   >> >>JOIN "ClientDO" s2 on 
(s2.id=rec."parentRef")
   >> >>LEFT JOIN LATERAL (select"fruitRef" 
from "Fruit2ClientDO" ax WHERE rec."parentRef"=ax."clientRef" and ax."fruitRef" 
= NEW."fruitRef")  a2
   >> >>   ON TRUE
   >> >>WHERE rec."parentRef" IS NOT NULL
   >> >>--Only first matching client should be 
used
   >> >>AND rec."fruitRef" IS NULL
   >> >>)
   >> >>SELECT clientref
   >> >>FROM rec
   >> >>WHERE "fruitRef" is not null
   >> >>INTO client;
   >> >>
   >> >>blockedItemLevel = (NEW."quantitySeened" - 
NEW."quantityCanceled");
   >> >>   

Re: What is the best way to redefine a trigger? (lock issue)

[Achilleas Mantzios]

On 10/12/21 12:56 μ.μ., Marc Mamin wrote:

>
>-Original Message-
>From: Achilleas Mantzios 
>Sent: Freitag, 10. Dezember 2021 11:36
>To: pgsql-general@lists.postgresql.org
>Subject: Re: What is the best way to redefine a trigger? (lock issue)
>
>On 10/12/21 12:20 μ.μ., Marc Mamin wrote:
>>
>> >> Hello,
>> >> I have deployment/migration scripts that require to be idempotent.
>> >>
>> >> When (re)defining or deleting triggers,  I've lately observed 
locked statements that seemed never to release (waited for a few hours).
>> >> affected version: PG 10 (and probably PG 12 ?)
>> >>
>> >> My case is similar to that old description and I wonder if the 
recommendation to first change the trigger function to a no-op function still make sense.
>> >>
>> >> 
https://stackoverflow.com/questions/24738354/how-to-drop-a-trigger-in-a-resilient-manner-in-postgresql.
>> >>
>> >>
>> >> In the first observed case, with a test db, I did kill all  
existing connections to the db and tried to drop the trigger with a fresh new connection.
>> >> This again resulted in a long lasting lock and I gave up, tipping 
on a db corruption.
>> >>
>> >> What does happen in the background, that can make a trigger 
deletion fail?
>> >A DROP TRIGGER will try to acquire an AccessExclusiveLock on the 
table, which conflicts with any table level lock (e.g a select acquires an access share 
lock, so it would cause the DROP TRIGGER to wait.
>> >
>> >Unfortunately I don't see this in the official docs: 
https://www.postgresql.org/docs/11/explicit-locking.html .
>> >
>> >> Are there situation where row level locks instead of table level 
locks are acquired?
>> >> Coul background processeslike vacuumplay a role here?
>> >>
>> >> As I've observed this problem only a very few times, I guess it 
is not easily reproducable.
>>
>> >It is very easily reproducible. begin; select .. in one session, 
begin; drop trigger in a second session.
>>
>> You can see in the attachment, that the lock exists without any other 
apparent conflicting  session.
>It takes two or more to tango. Next time it happens query the pg_locks 
view, it contains info about locks on objects.
>The holding lock is shown as granted, the waiting lock as not granted.

Yes, It looks for me like a situation which should not be possible at all.

I'll try to get more informations if the problem pop up again.
Thanks.

>> >Do you need to drop/create the trigger or a CREATE OR REPLACE function would suffice?

>>
>> There are different use cases. Sometimes I only need to drop a trigger 
or modify its definition (not the function)
>Are you using pgbouncer or some other means of suspending traffic into the 
DB?

No. In my tries to repare the situation, there were no other clients but psql 
and DBaever.

DBeaver is notorious for keeping transactions open.


>> >>
>> >> attached is an picture of pg_stat_activity during such a lock,
>> >>
>> >> thanks,
>> >> Marc Mamin
>> >>
>> >> here an example of a such a deployment/migration script, all of 
these scripts are applied  sequentially in separate transactions:
>> >> ===
>> >> SET client_min_messages=error;
>> >>
>> >> CREATE OR REPLACE FUNCTION block_item_cancel()
>> >>RETURNS TRIGGER AS
>> >> $BODY$
>> >> DECLARE
>> >>   blockedItemLevel int;
>> >>   client int;
>> >>
>> >> BEGIN
>> >>   WITH RECURSIVE rec as
>> >>   (
>> >>   SELECT s.id as clientref, s."parentRef", 
a."fruitRef"
>> >>   FROM "ClientDO" s LEFT JOIN "Fruit2ClientDO" a ON 
(s.id=a."clientRef" and a."fruitRef" = NEW."fruitRef")
>> >>   WHERE s.id = (select "clientRef" from "SeenDO" where 
"id" = NEW."SeenRef")
>> >>   UNION ALL
>> >>   SELECT s2.id as clientref, s2."parentRef", 
a2."fruitRef"
>> >>   FROM rec
>> >>   JOIN "ClientDO" s2 on 
(s2.id=rec."parentRef")
>> >>   LEFT JOIN LATERAL (select"fruitRef" from "Fruit2ClientDO" ax WHERE 
rec."parentRef"=ax."clientRef" and ax."fruitRef" = NEW."fruitRef")  a2
>> >>  ON TRUE
>> >>   WHERE rec."parentRef" IS NOT NULL
>> >>   --Only first matching client should 
be used
>> >>   AND rec."fruitRef" IS NULL
>> >>   )
>> >>   SELECT

Re: Postgresql + containerization possible use case

[o1bigtenor]

On Fri, Dec 10, 2021 at 3:24 AM Achilleas Mantzios <
ach...@matrix.gatewaynet.com> wrote:

> Hi
> we are running some 140 remote servers (in the 7 seas via satellite
> connections), and in each one of them we run:
> - jboss
> - postgresql
> - uucp (not as a daemon)
> - gpsd
> - samba
> - and possibly some other services
>
> Hardware and software upgrades are very hard since there is no physical
> access to those servers by trained personnel, and also there is a diversity
> of software versions.
>
> The idea for future upgrades is to containerize certain aspects of the
> software. The questions are (I am not skilled in docker, only minimal
> contact with lxd) :
> - is this a valid use case for containerization?
> - are there any gotchas around postgersql, the reliability of the system ?
> - since we are talking about 4+ basic services (pgsqk, jboss, uucp,
> samba), is docker a good fit or should we be looking into lxd as well?
> - are there any success stories of other after following a similar path?
>
>
My experience with LXD is that upon install you are now on a regular update
plan that is impossible to change.
This means that your very expensive data connection will be preempted for
updates at the whim of the
canonical crew. Suggest not using such (most using such on wireless
connections seem to have found
the resultant issues less than wonderful - - cost (on the data connection)
being #1 and the inability to achieve
solid reliability crowding it for #2).

IMO HTH

Re: Postgresql + containerization possible use case

[Achilleas Mantzios]

On 10/12/21 1:24 μ.μ., o1bigtenor wrote:



On Fri, Dec 10, 2021 at 3:24 AM Achilleas Mantzios mailto:ach...@matrix.gatewaynet.com>> wrote:

Hi
we are running some 140 remote servers (in the 7 seas via satellite 
connections), and in each one of them we run:
- jboss
- postgresql
- uucp (not as a daemon)
- gpsd
- samba
- and possibly some other services

Hardware and software upgrades are very hard since there is no physical 
access to those servers by trained personnel, and also there is a diversity of 
software versions.

The idea for future upgrades is to containerize certain aspects of the 
software. The questions are (I am not skilled in docker, only minimal contact 
with lxd) :
- is this a valid use case for containerization?
- are there any gotchas around postgersql, the reliability of the system ?
- since we are talking about 4+ basic services (pgsqk, jboss, uucp, samba), 
is docker a good fit or should we be looking into lxd as well?
- are there any success stories of other after following a similar path?



Thanks

My experience with LXD is that upon install you are now on a regular update 
plan that is impossible to change.

Ehhmmm we are running some old versions there already (jboss, pgsql), LXD would 
not differ in this regard.
What do you mean? that the updates for LXD are huge? short spaced/very regular?
Can you pls elaborate some more on that?

This means that your very expensive data connection will be preempted for 
updates at the whim of the
canonical crew. Suggest not using such (most using such on wireless connections 
seem to have found
the resultant issues less than wonderful - - cost (on the data connection) 
being #1 and the inability to achieve
solid reliability crowding it for #2).

Crew has their own paid service. Business connection is for business not crew.
What I am interested is, could docker be of any use in the above scenario? 
Containerization in general?
The guys (admins/mgmt) here seem to be dead set on docker, but I have to 
guarantee some basic data safety requirements.


IMO     HTH



--
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt

request to support "conflict on(col1 or col2) do update xxx" feature

[sai]

I think this feature is a very common requirement.

For example. I created  a table,  which username and email columns are
unique separately

CREATE TABLE test (
usernameVARCHAR(255) NOT NULL UNIQUE,
email   VARCHAR(255) NOT NULL UNIQUE,
status  VARCHAR(127)
);
I want to do an "update" when any of these two columns triggers conflict.

But postgres doesn't  support "conflict(col1 or col2)",  it only supports
"conflict(col1)", like this:

insert into test (username, email, status) values('u1','e1','s1') on
conflict(username) do  update set status=CURRENT_TIMESTAMP;

Many guys said you can create a unique index on a combination of two
columns like "unique(username, email)",  this is an absolutely incorrect
answer !

Assume I insert two records:
1. u1, e1, s1
2. u2, e2 ,s2
Now when I insert (u1, e3, s3),  the combination (u1, e3) is unique,  but
if you use "on conflict(username, email) do update xxx", you will still get
an exception  !  it can not process conflict on any one of the columns !

So I think we should have a simple syntax to support it? (I don't want to
write a Stored Procedure or use any Complex/hacker solution, thanks.).

Can the postgres team implement this feature?

-- 
Best Regards,
Jin

Re: Postgresql + containerization possible use case

[o1bigtenor]

On Fri, Dec 10, 2021 at 6:02 AM Achilleas Mantzios <
ach...@matrix.gatewaynet.com> wrote:

> On 10/12/21 1:24 μ.μ., o1bigtenor wrote:
>
>
>
> On Fri, Dec 10, 2021 at 3:24 AM Achilleas Mantzios <
> ach...@matrix.gatewaynet.com> wrote:
>
>> Hi
>> we are running some 140 remote servers (in the 7 seas via satellite
>> connections), and in each one of them we run:
>> - jboss
>> - postgresql
>> - uucp (not as a daemon)
>> - gpsd
>> - samba
>> - and possibly some other services
>>
>> Hardware and software upgrades are very hard since there is no physical
>> access to those servers by trained personnel, and also there is a diversity
>> of software versions.
>>
>> The idea for future upgrades is to containerize certain aspects of the
>> software. The questions are (I am not skilled in docker, only minimal
>> contact with lxd) :
>> - is this a valid use case for containerization?
>> - are there any gotchas around postgersql, the reliability of the system ?
>> - since we are talking about 4+ basic services (pgsqk, jboss, uucp,
>> samba), is docker a good fit or should we be looking into lxd as well?
>> - are there any success stories of other after following a similar path?
>>
>>
> Thanks
>
> My experience with LXD is that upon install you are now on a regular
> update plan that is impossible to change.
>
> Ehhmmm we are running some old versions there already (jboss, pgsql), LXD
> would not differ in this regard.
> What do you mean? that the updates for LXD are huge? short spaced/very
> regular?
> Can you pls elaborate some more on that?
>

Updates seem to happen very very regularly.
That means that the system is often tied up with the updating - - - NOT on
doing the function(s).
If there are any issues with the newest and bestest version - - - - well
you get to deal with not
only a hung system (happened a few times whilst I was trying this out (over
a longer period of
time as well)) but a system that isn't doing what you want it to be doing.
I chose to space the updates out to once a month - - - then followed senior
dev team
suggestions to control that and achieved a system that would not update
anything. To make
things even more interesting it was not possible to even remove snapd and
LXD. I was using
rm -r carefully and there was some error message that I no longer remember.
End result was
that I had to blow the system away and reinstall. I'm not a fan of doing
this nor a need to do
such to remove any program I choose to remove. My experiences told me that
the idea behind
this central management (ubuntu controlled updating and upgrading) was most
likely designed
to facilitate a paid serviced from Canonical which income from would cause
a very nice spike
in value to Canonical benefiting only a very tiny number of hands. The dev
team at LXD was
almost shrill in its defense of the 'we know best' thinking that this
behavior depicted. Somehow
running bleeding edge hasn't ever given me reliability. When it comes to
business - - - well I
want things to work - - - I'm not a programmer geek who is forever trying
to 'improve' something.
My existence is not validated by the umpteen hundred versions of my
software available out
there. My existence is better validated by what I can get done - - - - and
not necessarily what
someone else says I have to do right now (even in the middle of the
night!!!).
Does that help?




> This means that your very expensive data connection will be preempted for
> updates at the whim of the
> canonical crew. Suggest not using such (most using such on wireless
> connections seem to have found
> the resultant issues less than wonderful - - cost (on the data connection)
> being #1 and the inability to achieve
> solid reliability crowding it for #2).
>
> Crew has their own paid service. Business connection is for business not
> crew.
> What I am interested is, could docker be of any use in the above scenario?
> Containerization in general?
>

Know nothing about Docker and as a result of my foray into containerization
- - - - well - - - - I'm not a
fan at present. Much more likely to do something like set up a
RaspberryPi and then use that to do things
and if more is needed well - - - I'm considering micro-controllers linked
into SoCs (not necessarily RaspberryPi
but similar) and then possible one central perhaps full size server - - -
but then that server would be busy.
I also am using test systems for any level of system so I'm experimenting
on testing systems and things
don't move to the 'real work horses' until I'm happy that things are stable
and do what I want them to do.
Doesn't necessarily make for cheap but it has upped reliability and reduced
stress (when a primary use
system gets borked - - - whatever the reason - - - - life isn't fun until
its fixed - - - right?).


> The guys (admins/mgmt) here seem to be dead set on docker, but I have to
> guarantee some basic data safety requirements.
>

The 'book' says everything is wonderful - - - - if it were me - - - no
guarantees until 'I' am su

Re: Postgresql + containerization possible use case

[Adrian Klaver]

On 12/10/21 01:24, Achilleas Mantzios wrote:

Hi




The idea for future upgrades is to containerize certain aspects of the 
software. The questions are (I am not skilled in docker, only minimal 
contact with lxd) :

- is this a valid use case for containerization?
- are there any gotchas around postgersql, the reliability of the system ?
- since we are talking about 4+ basic services (pgsqk, jboss, uucp, 
samba), is docker a good fit or should we be looking into lxd as well?

- are there any success stories of other after following a similar path?


My question is what is it going to change? The software in the 
containers still need to be maintained/upgraded and now you have added 
maintenance and management of the container software and communication 
between containers.




Thank you!

PS

For those who wonder about UUCP, UUCP was our comms solution prior we 
installed TCP/IP on the vessels. However, to this day, it provides a 
nice management layer, a sort of protection before data leave the vessel 
or reach the vessel, in a user controlled manner. So uucp stayed as it 
matched exactly the business as far data transfers and emails are 
concerned. It would be hard to uniformly manage data transfers and 
emails in/out in a plain TCP/IP setup (rsync, ftp, etc, 
sendmail/exim4/postfix or other MTA).





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

RE: [EXTERNAL] Re: performance expectations for table(s) with 2B recs

[Godfrin, Philippe E]

On 2021-12-08 14:44:47 -0500, David Gauthier wrote:
> So far, the tables I have in my DB have relatively low numbers of records 
> (most
> are < 10K, all are < 10M).  Things have been running great in terms of
> performance.  But a project is being brainstormed which may require some 
> tables
> to contain a couple billion records.
[...]
> What else should I be worried about ?
>
> I suspect that part of why things are running really well so far is that the
> relatively small amounts of data in these tables ends up in the DB cache and
> disk I/O is kept at a minimum.  Will that no longer be the case once queries
> start running on these big tables ?

>
>Depends a lot on how good the locality of your queries is. If most read
>only the same parts of the same indexes, those will still be in the
>cache. If they are all over the place or if you have queries which need
>to read large parts of your tables, cache misses will make your
>performance a lot less predictable, yes. That stuff is also hard to
>test, because when you are testing a query twice in a row, the second
>time it will likely hit the cache and be quite fast.
>
>But in my experience the biggest problem with large tables are unstable
>execution plans - for most of the parameters the optimizer will choose
>to use an index, but for some it will erroneously think that a full
>table scan is faster. That can lead to a situation where a query
>normally takes less than a second, but sometimes (seemingly at random)
>it takes several minutes - users will understandably be upset about such
>behaviour. It is in any case a good idea to monitor execution times to
>find such problems (ideally before users complain), but each needs to be
>treated on an individual basis, and sometimes there seems to be no good
>solution.

To the OP, that's is a tall order to answer - basically that's wjhy DBA's still 
have
Jobs...

For Peter I have a question. What exactly causes 'unstable execution plans' ??

Besides not using bind variables, bad statistics, would you elaborate in what 
would
contribute to that instability?

Re: Postgresql + containerization possible use case

[Michael Lewis]

On Fri, Dec 10, 2021, 2:24 AM Achilleas Mantzios <
ach...@matrix.gatewaynet.com> wrote:

> Hi
> we are running some 140 remote servers (in the 7 seas via satellite
> connections)


How are they used? What is in Postgres? Should that all have the exact same
read only data at all times?

>

Re: Postgresql + containerization possible use case

[Adrian Klaver]

On 12/10/21 12:06, Michael Lewis wrote:
On Fri, Dec 10, 2021, 2:24 AM Achilleas Mantzios 
mailto:ach...@matrix.gatewaynet.com>> wrote:


Hi
we are running some 140 remote servers (in the 7 seas via satellite
connections)


How are they used? What is in Postgres? Should that all have the exact 
same read only data at all times?




A previous explanation from Achilleas Mantzios , not sure if this is 
still exactly how it works:


https://www.postgresql.org/message-id/3124961.tmAbAIay6W%40smadev.internal.net



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

Re: [EXTERNAL] Re: performance expectations for table(s) with 2B recs

[Peter J. Holzer]

On 2021-12-10 18:04:07 +, Godfrin, Philippe E wrote:
> >But in my experience the biggest problem with large tables are unstable
> >execution plans - for most of the parameters the optimizer will choose
> >to use an index, but for some it will erroneously think that a full
> >table scan is faster. That can lead to a situation where a query
> >normally takes less than a second, but sometimes (seemingly at random)
> >it takes several minutes
[...]
> For Peter I have a question. What exactly causes ‘unstable execution plans’ ??
> 
> Besides not using bind variables, bad statistics, would you elaborate
> in what would contribute to that instability?

Not using bind variables and bad statistics are certainly big factors:

On one hand not using bind variables gives a lot more information to the
optimizer, so it can choose a better plan at run time. On the other hand
that makes hard to predict what plan it will choose.

Bad statistics come in many flavours: They might just be wrong, that's
usually easy to fix. More problematic are statistics which just don't
describe reality very well - they may not show a correlation, causing
the optimizer to assume that two distributions are independent when they
really aren't (since PostgreSQL 10 you can create statistics on multiple
columns which helps in many but not all cases) or not show some other
peculiarity of the data. Or they may be just so close to a flipping
point that a small change causes the optimizer to choose a wildly
different plan.

Another source is dynamically generated SQL. Your application may just
put together SQL from fragments or it might use something like
SQLalchemy or an ORM. In any of these cases what looks like one query
from a user's perspective may really be a whole family of related
queries - and PostgreSQL will try to find the optimal plan for each of
them. Which is generally a good thing, but it adds opportunities to mess
up.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: CTE Materialization

[Richard Michael]

On Thu, 9 Dec 2021 at 10:29, Paul van der Linden 
wrote:

> This one quite nicely explains it:
> https://stackoverflow.com/questions/14897816/how-can-i-prevent-postgres-from-inlining-a-subquery
>

Given indexes applicable to multiple expressions in a WHERE condition, how
does postgres decide which index is most beneficial to use?

The author of that SO post tried to adjust the default statistics target,
presumably to convince postgres to use the faster primary key index,
instead of the slower gist index on the hstore values, but this didn't work.

thanks,
richard


> On Wed, Dec 8, 2021 at 3:14 AM David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Tue, Dec 7, 2021 at 6:40 PM Дмитрий Иванов 
>> wrote:
>>
>>> I beg your pardon.
>>> The problem is more or less clear to me, but the solution is not. What
>>> does the "hack is to add an "offset 0" to the query" suggest? Thank you.
>>>
>>>
>> A subquery with a LIMIT clause cannot have where clause expressions in
>> upper parts of the query tree pushed down it without changing the overall
>> query result - something the planner is not allowed to do.  For the hack,
>> since adding an actual LIMIT clause doesn't make sense you omit it, but
>> still add the related OFFSET clause so the planner still treats the
>> subquery as a LIMIT subquery.  And since you don't want to skip any rows
>> you specify 0 for the offset.
>>
>> David J.
>>
>>

Difference in execution plans pg12 vs pg14

[Дмитрий Иванов]

Afternoon. I was able to make the necessary changes to my base needed to
migrate win_pg12 to debian pg14.
But there is a new problem, which was not there at the initial stage so I
checked:

win_pg12:
->  Index Scan using index_class_tree_full on class c  (cost=0.28..2.50
rows=1 width=235) (actual time=0.030..0.032 rows=1 loops=1)
 Index Cond: (id = 650)
debian_pg14:
->  Index Scan using index_class_tree_full on class c  (cost=0.28..2.50
rows=1 width=235) (actual time=842.795..842.796 rows=1 loops=1)
 Index Cond: (id = 650)

I'm wondering where to dig next...
--
Best regards, Dmitry!

 QUERY PLAN
-
 Nested Loop Left Join  (cost=26599.87..856768.44 rows=4348 width=311) (actual 
time=931.755..931.773 rows=1 loops=1)
   Join Filter: (c_2.id = c.id)
   ->  Nested Loop Left Join  (cost=2.82..96.05 rows=1 width=244) (actual 
time=844.751..844.757 rows=1 loops=1)
 Join Filter: (con.id = c.id_con)
 Rows Removed by Join Filter: 1
 ->  Nested Loop Left Join  (cost=2.82..94.66 rows=1 width=236) (actual 
time=844.743..844.749 rows=1 loops=1)
   Join Filter: (c_1.id = c.id)
   ->  Index Scan using index_class_tree_full on class c  
(cost=0.28..2.50 rows=1 width=235) (actual time=842.795..842.796 rows=1 loops=1)
 Index Cond: (id = 650)
   ->  GroupAggregate  (cost=2.54..92.14 rows=1 width=9) (actual 
time=1.943..1.948 rows=1 loops=1)
 Group Key: c_1.id
 ->  Nested Loop Left Join  (cost=2.54..91.81 rows=1 
width=233) (actual time=0.174..1.939 rows=20 loops=1)
   ->  Nested Loop Left Join  (cost=2.26..91.51 rows=1 
width=233) (actual time=0.171..1.917 rows=20 loops=1)
 ->  Nested Loop Left Join  (cost=1.97..23.17 
rows=1 width=225) (actual time=0.063..0.197 rows=20 loops=1)
   ->  Nested Loop Left Join  
(cost=1.70..21.59 rows=1 width=213) (actual time=0.059..0.173 rows=20 loops=1)
 ->  Nested Loop Left Join  
(cost=1.42..21.29 rows=1 width=208) (actual time=0.057..0.154 rows=20 loops=1)
   ->  Nested Loop Left Join  
(cost=1.13..18.88 rows=1 width=192) (actual time=0.053..0.121 rows=20 loops=1)
 ->  Nested Loop Left 
Join  (cost=0.86..17.67 rows=1 width=131) (actual time=0.048..0.096 rows=20 
loops=1)
   ->  Nested Loop 
Left Join  (cost=0.57..15.17 rows=1 width=26) (actual time=0.037..0.049 rows=20 
loops=1)
 Join 
Filter: (c_1.id = cp.id_class)
 ->  Index 
Scan using index_class_tree_full on class c_1  (cost=0.28..2.50 rows=1 width=9) 
(actual time=0.020..0.020 rows=1 loops=1)
   
Index Cond: (id = 650)
 ->  Index 
Scan using index_class_prop_class on class_prop cp  (cost=0.29..12.52 rows=12 
width=25) (actual time=0.014..0.021 rows=20 loops=1)
   
Index Cond: (id_class = 650)
   ->  Index Scan 
using class_prop_small_val_pkey on class_prop_user_small_val sv  
(cost=0.29..2.51 rows=1 width=113) (actual time=0.002..0.002 rows=0 loops=20)
 Index 
Cond: (id_class_prop = cp.id)
 ->  Index Scan using 
class_prop_big_val_pkey on class_prop_user_big_val bv  (cost=0.27..1.21 rows=1 
width=69) (actual time=0.001..0.001 rows=0 loops=20)
   Index Cond: 
(id_class_prop = cp.id)
   ->  Index Scan using 
class_prop_enum_val_pkey on class_prop_enum_val ev  (cost=0.29..2.41 rows=1 
width=24) (actual time=0.001..0.001 rows=0 loops=20)
 Index Cond: 
(id_class_prop = cp.id)
 ->  Index Scan using 
prop_enum_val_pkey on prop_enum_val pev  (cost=0.28..0.30 rows=1 width=21) 
(actual time=0.001..0.001 rows=0 loops=20)
   Index Cond: (id = 
ev.id_prop_enum_val)
   -> 

Re: Difference in execution plans pg12 vs pg14

[Adrian Klaver]

On 12/10/21 17:00, Дмитрий Иванов wrote:
Afternoon. I was able to make the necessary changes to my base needed to 
migrate win_pg12 to debian pg14.
But there is a new problem, which was not there at the initial stage so 
I checked:


win_pg12:
->  Index Scan using index_class_tree_full on class c  (cost=0.28..2.50 
rows=1 width=235) (actual time=0.030..0.032 rows=1 loops=1)

                      Index Cond: (id = 650)
debian_pg14:
->  Index Scan using index_class_tree_full on class c  (cost=0.28..2.50 
rows=1 width=235) (actual time=842.795..842.796 rows=1 loops=1)

                      Index Cond: (id = 650)

I'm wondering where to dig next...


Did you run ANALYZE(updates table stats) on the 14 instance after doing 
the upgrade?



--
Best regards, Dmitry!



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

Re: Difference in execution plans pg12 vs pg14

[Дмитрий Иванов]

Yes, I did.
I reset table statistics, did (VACUUM) ANALYZE, recreated index. Nothing
changes.
I've deleted the database many times, dozens of times. Maybe something is
broken?
--
Regards, Dmitry!


сб, 11 дек. 2021 г. в 06:13, Adrian Klaver :

> On 12/10/21 17:00, Дмитрий Иванов wrote:
> > Afternoon. I was able to make the necessary changes to my base needed to
> > migrate win_pg12 to debian pg14.
> > But there is a new problem, which was not there at the initial stage so
> > I checked:
> >
> > win_pg12:
> > ->  Index Scan using index_class_tree_full on class c  (cost=0.28..2.50
> > rows=1 width=235) (actual time=0.030..0.032 rows=1 loops=1)
> >   Index Cond: (id = 650)
> > debian_pg14:
> > ->  Index Scan using index_class_tree_full on class c  (cost=0.28..2.50
> > rows=1 width=235) (actual time=842.795..842.796 rows=1 loops=1)
> >   Index Cond: (id = 650)
> >
> > I'm wondering where to dig next...
>
> Did you run ANALYZE(updates table stats) on the 14 instance after doing
> the upgrade?
>
> > --
> > Best regards, Dmitry!
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Difference in execution plans pg12 vs pg14

[Adrian Klaver]

On 12/10/21 17:51, Дмитрий Иванов wrote:

Yes, I did.
I reset table statistics, did (VACUUM) ANALYZE, recreated index. Nothing 
changes.
I've deleted the database many times, dozens of times. Maybe something 
is broken?


How did you do the upgrade?


--
Regards, Dmitry!


сб, 11 дек. 2021 г. в 06:13, Adrian Klaver >:


On 12/10/21 17:00, Дмитрий Иванов wrote:
 > Afternoon. I was able to make the necessary changes to my base
needed to
 > migrate win_pg12 to debian pg14.
 > But there is a new problem, which was not there at the initial
stage so
 > I checked:
 >
 > win_pg12:
 > ->  Index Scan using index_class_tree_full on class c
  (cost=0.28..2.50
 > rows=1 width=235) (actual time=0.030..0.032 rows=1 loops=1)
 >                       Index Cond: (id = 650)
 > debian_pg14:
 > ->  Index Scan using index_class_tree_full on class c
  (cost=0.28..2.50
 > rows=1 width=235) (actual time=842.795..842.796 rows=1 loops=1)
 >                       Index Cond: (id = 650)
 >
 > I'm wondering where to dig next...

Did you run ANALYZE(updates table stats) on the 14 instance after doing
the upgrade?

 > --
 > Best regards, Dmitry!


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

PostgreSQL 14 Slaves setup - Question about WAL Files recovery

[Lucas]

Hi guys. I’m in the process of migrating a PG 9.2 cluster to PG 14.

There are a lot of differences on the configuration files between PG 9.2 and PG 
14, and I have a question that hopefully you’ll be able to help me out.

My servers are deployed in AWS on EC2 instances and I use /pgsql to store PG 
data and /data to store PG Logs, Wal files, etc. My /pgsql/14/main/pg_wal 
folder is a symlink to /data/postgresql/pg_xlogs (done this to minimize IO on 
the /pgsql EBS volume).

The restore command in the postgresql.conf file is restore_command = 'cp 
/data/wal_archive/%f %p’ - /data/wal_archive is where the master is shipping 
the WAL Files to.

—

When I deployed the slave instance and started the recovery process, I got this 
messages:

2021-12-11 02:11:52 UTC [22700]: [3-1] user=,db=,app=,client= LOG:  starting 
PostgreSQL 14.1 (Ubuntu 14.1-2.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by 
gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
2021-12-11 02:11:52 UTC [22700]: [4-1] user=,db=,app=,client= LOG:  listening 
on IPv4 address "0.0.0.0", port 5432
2021-12-11 02:11:52 UTC [22700]: [5-1] user=,db=,app=,client= LOG:  listening 
on IPv6 address "::", port 5432
2021-12-11 02:11:52 UTC [22700]: [6-1] user=,db=,app=,client= LOG:  listening 
on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-12-11 02:11:52 UTC [22702]: [1-1] user=,db=,app=,client= LOG:  database 
system was interrupted; last known up at 2021-12-10 14:57:44 UTC
2021-12-11 02:11:52 UTC [22702]: [2-1] user=,db=,app=,client= LOG:  creating 
missing WAL directory "pg_wal/archive_status"
cp: cannot stat '/data/wal_archive/0002.history': No such file or directory
2021-12-11 02:11:52 UTC [22702]: [3-1] user=,db=,app=,client= LOG:  entering 
standby mode
2021-12-11 02:11:52 UTC [22702]: [4-1] user=,db=,app=,client= LOG:  invalid 
primary checkpoint record
2021-12-11 02:11:52 UTC [22702]: [5-1] user=,db=,app=,client= PANIC:  could not 
locate a valid checkpoint record

—

However, the wal files were present in the /data/wal_archive/ directory.

When I moved the same wal files to /pgsql/14/main/pg_wal/, it started working:

2021-12-11 02:15:35 UTC [23103]: [3-1] user=,db=,app=,client= LOG:  starting 
PostgreSQL 14.1 (Ubuntu 14.1-2.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by 
gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
2021-12-11 02:15:35 UTC [23103]: [4-1] user=,db=,app=,client= LOG:  listening 
on IPv4 address "0.0.0.0", port 5432
2021-12-11 02:15:35 UTC [23103]: [5-1] user=,db=,app=,client= LOG:  listening 
on IPv6 address "::", port 5432
2021-12-11 02:15:35 UTC [23103]: [6-1] user=,db=,app=,client= LOG:  listening 
on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-12-11 02:15:35 UTC [23105]: [1-1] user=,db=,app=,client= LOG:  database 
system was interrupted; last known up at 2021-12-10 14:57:44 UTC
cp: cannot stat '/data/wal_archive/0002.history': No such file or directory
2021-12-11 02:15:35 UTC [23105]: [2-1] user=,db=,app=,client= LOG:  entering 
standby mode
2021-12-11 02:15:35 UTC [23105]: [3-1] user=,db=,app=,client= LOG:  database 
system was not properly shut down; automatic recovery in progress
2021-12-11 02:15:35 UTC [23105]: [4-1] user=,db=,app=,client= LOG:  redo starts 
at 6FB/D928
2021-12-11 02:15:35 UTC [23105]: [5-1] user=,db=,app=,client= LOG:  consistent 
recovery state reached at 6FB/DA00
2021-12-11 02:15:35 UTC [23103]: [7-1] user=,db=,app=,client= LOG:  database 
system is ready to accept read-only connections
cp: cannot stat '/data/wal_archive/000106FB00DA': No such file or 
directory
2021-12-11 02:15:35 UTC [23113]: [1-1] user=,db=,app=,client= LOG:  started 
streaming WAL from primary at 6FB/DA00 on timeline 1

—

Why? Why is PG looking for the wal files in the “wrong” directory? What am I 
missing here?

Thanks in advance.
Lucas

Re: Difference in execution plans pg12 vs pg14

[Дмитрий Иванов]

Yes, I did.
Step1
sudo /usr/lib/postgresql/14/bin/pg_dump --file
"/home/dismay/uchet/Uchet.backup" --host "server" --port "5999" --username
"back" --no-password --verbose --format=c --quote-all-identifiers --blobs
 --disable-triggers  --encoding="UTF8" "Uchet"
Step2
Manual DROP/CREATE BASE from template 0 (We have to do this because of the
difference in locales Win EDB 'Russian_Russia.1251' <> Debian 'ru_RU.UTF-8')
Step3
sudo /usr/lib/postgresql/14/bin/pg_restore --host "127.0.0.1" --port "5998"
--username "back" --no-password --dbname "Uchet" --disable-triggers
--format=c --verbose "/home/dismay/uchet/Uchet.backup" 2>
"/home/dismay/uchet/uchet_all.log"
--
Regards, Dmitry!


сб, 11 дек. 2021 г. в 06:59, Adrian Klaver :

> On 12/10/21 17:51, Дмитрий Иванов wrote:
> > Yes, I did.
> > I reset table statistics, did (VACUUM) ANALYZE, recreated index. Nothing
> > changes.
> > I've deleted the database many times, dozens of times. Maybe something
> > is broken?
>
> How did you do the upgrade?
>
> > --
> > Regards, Dmitry!
> >
> >
> > сб, 11 дек. 2021 г. в 06:13, Adrian Klaver  > >:
> >
> > On 12/10/21 17:00, Дмитрий Иванов wrote:
> >  > Afternoon. I was able to make the necessary changes to my base
> > needed to
> >  > migrate win_pg12 to debian pg14.
> >  > But there is a new problem, which was not there at the initial
> > stage so
> >  > I checked:
> >  >
> >  > win_pg12:
> >  > ->  Index Scan using index_class_tree_full on class c
> >   (cost=0.28..2.50
> >  > rows=1 width=235) (actual time=0.030..0.032 rows=1 loops=1)
> >  >   Index Cond: (id = 650)
> >  > debian_pg14:
> >  > ->  Index Scan using index_class_tree_full on class c
> >   (cost=0.28..2.50
> >  > rows=1 width=235) (actual time=842.795..842.796 rows=1 loops=1)
> >  >   Index Cond: (id = 650)
> >  >
> >  > I'm wondering where to dig next...
> >
> > Did you run ANALYZE(updates table stats) on the 14 instance after
> doing
> > the upgrade?
> >
> >  > --
> >  > Best regards, Dmitry!
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Difference in execution plans pg12 vs pg14

[Imre Samu]

Hi Dmitry,

pg12:
> Execution Time: 44.123 ms

pg14:
> JIT:
>   Functions: 167
>   Options: Inlining true, Optimization true, Expressions true, Deforming
true
>   Timing: Generation 9.468 ms, Inlining 55.237 ms, Optimization 507.548
ms, Emission 347.932 ms, Total 920.185 ms
> Execution Time: 963.258 ms

please check the JIT settings ;
and test with:   jit = off;

regards,
 Imre


Дмитрий Иванов  ezt írta (időpont: 2021. dec. 11.,
Szo, 2:01):

> Afternoon. I was able to make the necessary changes to my base needed to
> migrate win_pg12 to debian pg14.
> But there is a new problem, which was not there at the initial stage so I
> checked:
>
> win_pg12:
> ->  Index Scan using index_class_tree_full on class c  (cost=0.28..2.50
> rows=1 width=235) (actual time=0.030..0.032 rows=1 loops=1)
>  Index Cond: (id = 650)
> debian_pg14:
> ->  Index Scan using index_class_tree_full on class c  (cost=0.28..2.50
> rows=1 width=235) (actual time=842.795..842.796 rows=1 loops=1)
>  Index Cond: (id = 650)
>
> I'm wondering where to dig next...
> --
> Best regards, Dmitry!
>