Re: Can we directly upgrade postgresql from 13 to 15.4
> On Nov 8, 2024, at 10:12, jayesh thakare wrote: > Can we upgrade postresql from 13 to 15.4 directly? > Can we upgrade postgresql from 13 to 16 directly? Yes. > Ideally how many major version can we jump to from older version ? There's no practical limit, assuming the old version is not an ancient version of PostgreSQL (pre-9.0, say). That being said, PostgreSQL's behavior changes with every major release, and there is always the possibility of query plan changes or other surprises. It's essential to test your application thoroughly on the target version, and do dry runs of pg_upgrade.
Re: Why plpython functions increase transaction counter much more then plpgsql functions?
> > A quick look at PLy_spi_execute_query shows that it runs each command > in a subtransaction. It pretty much has to, because the coding rules > for a Python method don't permit it to just longjmp out of the Python > interpreter, so it has to set up a subtransaction so it can catch any > error. In this example, each subtransaction will consume an XID. > > That makes sense. Thank you very much. Regards, Michał Albrycht
Re: Can we directly upgrade postgresql from 13 to 15.4
On 11/8/24 10:12, jayesh thakare wrote: Hi all, Can we upgrade postresql from 13 to 15.4 directly? Can we upgrade postgresql from 13 to 16 directly? Yes either dump/restore: https://www.postgresql.org/docs/current/app-pgdump.html https://www.postgresql.org/docs/current/app-pgrestore.html Or pg_upgrade: https://www.postgresql.org/docs/current/pgupgrade.html Pay particular attention to the Notes section at each of the above links Ideally how many major version can we jump to from older version ? Some of that is covered in the Notes section. You should also look at the Release Notes: https://www.postgresql.org/docs/release/ The important ones are for Postgres version 10+ those for X.0 where X is the major release e.g. 10, 11, 12, etc. For 9- the release to look for is X.X.0 where X.X is 9.6, 9.5, 9.4, etc. These will show you the major changes that you might need to deal with. For example in Postgres 15 the langauges plpythonu/plpython2u where removed, so if you are using plpython* functions you need to make sure they have been migrated to plpython3u. Regards, Jayeshthakare 8828986182 Postgresql dba | Clover Infotech -- Adrian Klaver adrian.kla...@aklaver.com
Re: Performance Issue with Hash Partition Query Execution in PostgreSQL 16
Sorry, it was typo. Bind variable is bigint only. Thanks On Fri, 8 Nov, 2024, 7:09 pm David Mullineux, wrote: > Just spotted a potential problem. The indexed column is a bigint. Are you, > in your prepared statement passing a string or a big int ? > I notice your plan is doing an implicit type conversion when you run it > manually. > Sometimes the wrong type will make it not use the index. > > On Fri, 8 Nov 2024, 03:07 ravi k, wrote: > >> Hi , >> >> Thanks for the suggestions. >> >> Two more observations: >> >> 1) no sequence scan noticed from pg_stat_user_tables ( hope stats are >> accurate in postgres 16) if parameter sniffing happens the possibility of >> going to sequence scan is more right. >> >> 2) no blockings or IO issue during the time. >> >> 3) even with limit clause if touch all partitions also it could have been >> completed in milliseconds as this is just one record. >> >> 4) auto_explain in prod we cannot enable as this is expensive and with >> high TPS we may face latency issues and lower environment this issue cannot >> be reproduced,( this is happening out of Million one case) >> >> This looks puzzle to us, just in case anyone experianced pls share your >> experience. >> >> Regards, >> Ravi >> >> On Thu, 7 Nov, 2024, 3:41 am David Mullineux, wrote: >> >>> It might be worth eliminating the use of cached plans here. Is your app >>> using prepared statements at all? >>> Point is that if the optimizer sees the same prepared query , 5 times, >>> the it locks the plan that it found at that time. This is a good trade off >>> as it avoids costly planning-time for repetitive queries. But if you are >>> manually querying, the a custom plan will be generated anew. >>> A quick analyze of the table should reset the stats and invalidate any >>> cached plans. >>> This may not be your problem just worth eliminating it from the list of >>> potential causes. >>> >>> On Wed, 6 Nov 2024, 17:14 Ramakrishna m, wrote: >>> Hi Team, One of the queries, which retrieves a single record from a table with 16 hash partitions, is taking more than 10 seconds to execute. In contrast, when we run the same query manually, it completes within milliseconds. This issue is causing exhaustion of the application pools. Do we have any bugs in postgrs16 hash partitions? Please find the attached log, table, and execution plan. size of the each partitions : 300GB Index Size : 12GB Postgres Version : 16.x Shared Buffers : 75 GB Effective_cache : 175 GB Work _mem : 4MB Max_connections : 3000 OS : Ubuntu 22.04 Ram : 384 GB CPU : 64 Please let us know if you need any further information or if there are additional details required. Regards, Ram. >>>
Re: Can we directly upgrade postgresql from 13 to 15.4
Στις 8/11/24 20:16, ο/η Christophe Pettus έγραψε: On Nov 8, 2024, at 10:12, jayesh thakare wrote: Can we upgrade postresql from 13 to 15.4 directly? Can we upgrade postgresql from 13 to 16 directly? Yes. Ideally how many major version can we jump to from older version ? There's no practical limit, assuming the old version is not an ancient version of PostgreSQL (pre-9.0, say). In which case I guess it is possible to use pg_upgrade of the maximum version in the same supported "window". e.g. pg_upgrade of 9.4 to upgrade from 8.3, and so forth, in iterations till someone hits the maximum desired version. IMHO one should pay attention with pre-PGXS modules (such as intarray) , I stumped across some ugly situations with that. That being said, PostgreSQL's behavior changes with every major release, and there is always the possibility of query plan changes or other surprises. It's essential to test your application thoroughly on the target version, and do dry runs of pg_upgrade.
pgsql_tmp consuming most of the space.
Hi all, In one of our production environment.. pgsql_tmp is taking about half of the space in FS. And temp files are from Jan 2024. Our data fs is about to reach 98% percent.. Kindly suggest us what should be done to resolve this issue? pgsql_tmp contains temperorary files during sub query execution. Then why files are not getting deleted automatically after query completion? What are parameters that can be tuned to solve such issues in future? Regards, Jayeshthakare Postgresql dba Clover infotech
Re: pgsql_tmp consuming most of the space.
On Fri, Nov 8, 2024 at 11:51 PM jayesh thakare wrote: > Hi all, > > In one of our production environment.. > > pgsql_tmp is taking about half of the space in FS. > And temp files are from Jan 2024. > > Our data fs is about to reach 98% percent.. > > Kindly suggest us what should be done to resolve this issue? > Delete the pgsql_tmp file from *before* the last time the postmaster started. > pgsql_tmp contains temperorary files during sub query execution. Then why > files are not getting deleted automatically after query completion? > Queries crashing can cause it. What version of PG are you running? > What are parameters that can be tuned to solve such issues in future? > -- Death to , and butter sauce. Don't boil me, I'm still alive. lobster!
Can we directly upgrade postgresql from 13 to 15.4
Hi all, Can we upgrade postresql from 13 to 15.4 directly? Can we upgrade postgresql from 13 to 16 directly? Ideally how many major version can we jump to from older version ? Regards, Jayeshthakare 8828986182 Postgresql dba | Clover Infotech
Why plpython functions increase transaction counter much more then plpgsql functions?
I'm trying to understand why plpython function has much bigger impact on
transaction counter in Postgres than plpgSQL function. Below is example
which uses 2 functions:
Version with plpgSQL (each part done in separate transactions one after
another)
- check txid_current
- SQL query which calls the `f1_plpgsql` function which calls the
`insert_row_to_db` function 100 times
- check txid_current
Then we compare txid_currnent values and difference is 2 which means that
whole sql with 100 calls to `f1_plpgsql` and `insert_row_to_db` increased
transaction counter only by 1.
Here is the code:
```
CREATE TABLE insert_rows_table(
i BIGINT
);
CREATE OR REPLACE FUNCTION insert_row_to_db(i BIGINT)
RETURNS VOID
AS $$
BEGIN
INSERT INTO insert_rows_table SELECT i;
END
$$ LANGUAGE plpgsql SECURITY DEFINER VOLATILE PARALLEL UNSAFE;
CREATE OR REPLACE FUNCTION f1_plpgsql(i BIGINT)
RETURNS bigint
AS $$
BEGIN
PERFORM insert_row_to_db(i);
RETURN i;
END
$$ LANGUAGE plpgsql SECURITY DEFINER VOLATILE PARALLEL UNSAFE;
SELECT txid_current();
SELECT f1_plpgsql(i::BIGINT) FROM generate_series(1,100) as i;
SELECT txid_current();
```
Example output:
txid_current
500
f1_plpgsql
1
2
...
99
100
txid_current
502
Here is a code reproduction on db-fiddle:
https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/15135
Now let's replace `f1_plpgsql` with function written in plpython:
```
CREATE OR REPLACE FUNCTION f1_plpython(i BIGINT)
RETURNS bigint
AS $$
rows = plpy.execute("SELECT insert_row_to_db(" + str(i) + ")")
return i
$$ LANGUAGE plpython3u SECURITY DEFINER VOLATILE PARALLEL UNSAFE;
```
I get:
txid_current
500
f1_plpgsql
1
2
...
99
100
txid_current
602
This proves that the plpython function affects the transaction counter much
more. Does anyone know why? Is there anything I can do about it?
What's interesting it happens only if the function called by plpyhon makes
changes to DB. When I replace `INSERT INTO insert_rows_table SELECT i;`
with `SELECT i` both plpython and plpgsql functions behave the same.
Regards,
Michał Albrycht
Re: Performance Issue with Hash Partition Query Execution in PostgreSQL 16
Just spotted a potential problem. The indexed column is a bigint. Are you, in your prepared statement passing a string or a big int ? I notice your plan is doing an implicit type conversion when you run it manually. Sometimes the wrong type will make it not use the index. On Fri, 8 Nov 2024, 03:07 ravi k, wrote: > Hi , > > Thanks for the suggestions. > > Two more observations: > > 1) no sequence scan noticed from pg_stat_user_tables ( hope stats are > accurate in postgres 16) if parameter sniffing happens the possibility of > going to sequence scan is more right. > > 2) no blockings or IO issue during the time. > > 3) even with limit clause if touch all partitions also it could have been > completed in milliseconds as this is just one record. > > 4) auto_explain in prod we cannot enable as this is expensive and with > high TPS we may face latency issues and lower environment this issue cannot > be reproduced,( this is happening out of Million one case) > > This looks puzzle to us, just in case anyone experianced pls share your > experience. > > Regards, > Ravi > > On Thu, 7 Nov, 2024, 3:41 am David Mullineux, wrote: > >> It might be worth eliminating the use of cached plans here. Is your app >> using prepared statements at all? >> Point is that if the optimizer sees the same prepared query , 5 times, >> the it locks the plan that it found at that time. This is a good trade off >> as it avoids costly planning-time for repetitive queries. But if you are >> manually querying, the a custom plan will be generated anew. >> A quick analyze of the table should reset the stats and invalidate any >> cached plans. >> This may not be your problem just worth eliminating it from the list of >> potential causes. >> >> On Wed, 6 Nov 2024, 17:14 Ramakrishna m, wrote: >> >>> Hi Team, >>> >>> One of the queries, which retrieves a single record from a table with 16 >>> hash partitions, is taking more than 10 seconds to execute. In contrast, >>> when we run the same query manually, it completes within milliseconds. This >>> issue is causing exhaustion of the application pools. Do we have any bugs >>> in postgrs16 hash partitions? Please find the attached log, table, and >>> execution plan. >>> >>> size of the each partitions : 300GB >>> Index Size : 12GB >>> >>> Postgres Version : 16.x >>> Shared Buffers : 75 GB >>> Effective_cache : 175 GB >>> Work _mem : 4MB >>> Max_connections : 3000 >>> >>> OS : Ubuntu 22.04 >>> Ram : 384 GB >>> CPU : 64 >>> >>> Please let us know if you need any further information or if there are >>> additional details required. >>> >>> >>> Regards, >>> Ram. >>> >>
Re: Why plpython functions increase transaction counter much more then plpgsql functions?
Because the plpython function is executing dynamic SQL?
On Fri, Nov 8, 2024 at 2:59 AM Michał Albrycht
wrote:
> I'm trying to understand why plpython function has much bigger impact on
> transaction counter in Postgres than plpgSQL function. Below is example
> which uses 2 functions:
>
> Version with plpgSQL (each part done in separate transactions one after
> another)
> - check txid_current
> - SQL query which calls the `f1_plpgsql` function which calls the
> `insert_row_to_db` function 100 times
> - check txid_current
>
> Then we compare txid_currnent values and difference is 2 which means that
> whole sql with 100 calls to `f1_plpgsql` and `insert_row_to_db` increased
> transaction counter only by 1.
>
> Here is the code:
> ```
> CREATE TABLE insert_rows_table(
> i BIGINT
> );
>
> CREATE OR REPLACE FUNCTION insert_row_to_db(i BIGINT)
> RETURNS VOID
> AS $$
> BEGIN
> INSERT INTO insert_rows_table SELECT i;
> END
> $$ LANGUAGE plpgsql SECURITY DEFINER VOLATILE PARALLEL UNSAFE;
>
>
> CREATE OR REPLACE FUNCTION f1_plpgsql(i BIGINT)
> RETURNS bigint
> AS $$
> BEGIN
> PERFORM insert_row_to_db(i);
> RETURN i;
> END
> $$ LANGUAGE plpgsql SECURITY DEFINER VOLATILE PARALLEL UNSAFE;
>
>
> SELECT txid_current();
> SELECT f1_plpgsql(i::BIGINT) FROM generate_series(1,100) as i;
> SELECT txid_current();
> ```
>
> Example output:
>
> txid_current
> 500
>
> f1_plpgsql
> 1
> 2
> ...
> 99
> 100
>
> txid_current
> 502
>
>
> Here is a code reproduction on db-fiddle:
> https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/15135
>
> Now let's replace `f1_plpgsql` with function written in plpython:
>
> ```
> CREATE OR REPLACE FUNCTION f1_plpython(i BIGINT)
> RETURNS bigint
> AS $$
> rows = plpy.execute("SELECT insert_row_to_db(" + str(i) + ")")
> return i
> $$ LANGUAGE plpython3u SECURITY DEFINER VOLATILE PARALLEL UNSAFE;
> ```
>
> I get:
>
> txid_current
> 500
>
> f1_plpgsql
> 1
> 2
> ...
> 99
> 100
>
> txid_current
> 602
>
>
> This proves that the plpython function affects the transaction counter
> much more. Does anyone know why? Is there anything I can do about it?
>
> What's interesting it happens only if the function called by plpyhon makes
> changes to DB. When I replace `INSERT INTO insert_rows_table SELECT i;`
> with `SELECT i` both plpython and plpgsql functions behave the same.
> Regards,
>
> Michał Albrycht
>
>
--
Death to , and butter sauce.
Don't boil me, I'm still alive.
lobster!
Re: Why plpython functions increase transaction counter much more then plpgsql functions?
=?UTF-8?Q?Micha=C5=82_Albrycht?= writes: > This proves that the plpython function affects the transaction counter much > more. Does anyone know why? A quick look at PLy_spi_execute_query shows that it runs each command in a subtransaction. It pretty much has to, because the coding rules for a Python method don't permit it to just longjmp out of the Python interpreter, so it has to set up a subtransaction so it can catch any error. In this example, each subtransaction will consume an XID. The plpgsql example is different because it doesn't trap errors, hence no subtransaction needed, and all the rows will get inserted under the XID of the outer command's main transaction. If you were to wrap the insert_row_to_db call in BEGIN ... EXCEPTION then it'd consume the same number of XIDs as plpython, for the same reason. > Is there anything I can do about it? Batch the DB updates, perhaps? > What's interesting it happens only if the function called by plpyhon makes > changes to DB. Totally unsurprising. XIDs are acquired only when the current transaction or subtransaction first needs to change the DB. regards, tom lane
