HI Adrian, I did additional tests and I can reproduce the problem also without the extensions. I did the test multiple time with a script that I put as attachment (wholetest.sh). I added also the file log_different_run.txt that contains the results of some test and the times it takes to reproduce.
BR Renzo On Thu, Dec 19, 2024 at 9:42 PM Renzo Dani <aro...@gmail.com> wrote: > Hi Adrian, > Thanks for the test, I installed also version 17.2 and test with a fresh > newly created db. > At the beginning I cannot reproduce the problem as well. > Than I start adding the extension we use, one after one, and testing in > between. > > I found out that the problem appear immediately as soon as I add the > extension pg_stat_statements: > > create extension pg_stat_statements WITH SCHEMA public; > > alter system set shared_preload_libraries = 'pg_stat_statements'; > > Can you maybe test as well with this extension? > the other that I installed before that are: pgcrypto and pg_prewarm. > > here the current installed one: > > dbtest=# \dx > List of installed extensions > Name | Version | Schema | > Description > > --------------------+---------+------------+------------------------------------------------------------------------ > pg_prewarm | 1.2 | public | prewarm relation data > pg_stat_statements | 1.11 | public | track planning and execution > statistics of all SQL statements executed > pgcrypto | 1.3 | public | cryptographic functions > plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language > (4 rows) > > dbtest=# select version(); > version > > > ------------------------------------------------------------------------------------------------------------------ > PostgreSQL 17.2 reda build on x86_64-pc-linux-gnu, compiled by gcc > (Ubuntu 13.3.0-6ubuntu2~24.04) 13.3.0, 64-bit > (1 row) > > > I'll do some additional tests. > > BR > Renzo > > > > On Thu, Dec 19, 2024 at 6:21 PM Adrian Klaver <adrian.kla...@aklaver.com> > wrote: > >> On 12/19/24 08:50, Adrian Klaver wrote: >> > On 12/19/24 08:46, Renzo Dani wrote: >> > >> > Again. >> > >> > Reply to list also using Reply All. >> > Ccing list. >> > >> >> Hi Adrian, >> >> here a new version of the script that I just tested produce the same >> >> problem. >> >> The script do not rely now on any additional relation than the ones >> >> defined into the script. >> >> I could not replicate using Ubuntu 22.04 and Postgres 16.6. >> >> From your original post: >> >> "I reproduced the problem on PostgreSQL versions 16.1 and 17.1." >> >> The latest bug fixes of each are 16.6 and 17.2. I would upgrade to those >> and then try again. >> >> >> >> >> BR >> >> Renzo >> >> >> >> -- >> Adrian Klaver >> adrian.kla...@aklaver.com >> >>
...
[Thread1] Restarting loop.
[Thread2] Sleeping for 0.609 seconds.
[Thread1] Sleeping for 0.443 seconds.
[Thread1] Thu Dec 19 10:01:36 PM CET 2024
[Thread1] Running install script
psql:search_bug.sql:1: NOTICE: drop cascades to function
mytestbugschema02.afunction(text)
DROP SCHEMA
[Thread2] Thu Dec 19 10:01:36 PM CET 2024
[Thread2] Running install script
CREATE SCHEMA
CREATE FUNCTION
psql:search_bug.sql:14: NOTICE: drop cascades to function
mytestbugschema01.baseproc()
psql:search_bug.sql:1: NOTICE: drop cascades to function
mytestbugschema02.afunction(text)
DROP SCHEMA
DROP SCHEMA
CREATE SCHEMA
CREATE SCHEMA
CREATE PROCEDURE
CREATE FUNCTION
psql:search_bug.sql:14: NOTICE: drop cascades to function
mytestbugschema01.baseproc()
DROP SCHEMA
DROP SCHEMA
CREATE SCHEMA
CREATE SCHEMA
CREATE FUNCTION
oid | proname | pronamespace | proowner | prolang | procost | prorows |
provariadic | prosupport | prokind | prosecdef | proleakproof | proisstrict |
proretset | provolatile | proparallel | pronargs | pronargdefaults | prorettype
| proargtypes | proallargtypes | proargmodes | proargnames | proargdefaults |
protrftypes | prosrc | probin | prosqlbody | proconfig | proacl
-------+-----------+--------------+----------+---------+---------+---------+-------------+------------+---------+-----------+--------------+-------------+-----------+-------------+-------------+----------+-----------------+------------+-------------+----------------+-------------+-------------+----------------+-------------+------------------+--------+------------+-----------+--------
17057 | afunction | 17055 | 16385 | 13655 | 100 | 0 |
0 | - | f | f | f | f | f
| v | u | 1 | 0 | 25 | 25
| | | {pinput} | |
| +| | | |
CREATE PROCEDURE
| | | | | | |
| | | | | |
| | | | | |
| | | | |
| begin +| | | |
| | | | | | |
| | | | | |
| | | | | |
| | | | |
| return 'base'; +| | | |
| | | | | | |
| | | | | |
| | | | | |
| | | | |
| END | | | |
(1 row)
psql:search_bug.sql:29: NOTICE: drop cascades to function
mytestbugschema02.afunction(text)
[Thread1] Checking result of check_sql
DROP SCHEMA
CREATE SCHEMA
CREATE FUNCTION
oid | proname | pronamespace | proowner | prolang | procost | prorows |
provariadic | prosupport | prokind | prosecdef | proleakproof | proisstrict |
proretset | provolatile | proparallel | pronargs | pronargdefaults | prorettype
| proargtypes | proallargtypes | proargmodes | proargnames | proargdefaults |
protrftypes | prosrc | probin | prosqlbody | proconfig | proacl
-------+-----------+--------------+----------+---------+---------+---------+-------------+------------+---------+-----------+--------------+-------------+-----------+-------------+-------------+----------+-----------------+------------+-------------+----------------+-------------+-------------+----------------+-------------+------------------+--------+------------+-----------+--------
17057 | afunction | 17055 | 16385 | 13655 | 100 | 0 |
0 | - | f | f | f | f | f
| v | u | 1 | 0 | 25 | 25
| | | {pinput} | |
| +| | | |
| | | | | | |
| | | | | |
| | | | | |
| | | | |
| begin +| | | |
| | | | | | |
| | | | | |
| | | | | |
| | | | |
| return 'base'; +| | | |
| | | | | | |
| | | | | |
| | | | | |
| | | | |
| END | | | |
(1 row)
[Thread1] Found problem, stopping thread.
[Thread2] Checking result of check_sql
[Thread2] Found problem, stopping thread.
Both threads have completed.
real 1m31.097s
user 0m1.170s
sys 0m2.597s
...
[Thread1] Restarting loop.
[Thread1] Sleeping for 0.778 seconds.
[Thread2] Sleeping for 0.84 seconds.
[Thread1] Thu Dec 19 10:02:48 PM CET 2024
[Thread1] Running install script
psql:search_bug.sql:1: NOTICE: drop cascades to function
mytestbugschema02.afunction(text)
DROP SCHEMA
CREATE SCHEMA
CREATE FUNCTION
psql:search_bug.sql:14: NOTICE: drop cascades to function
mytestbugschema01.baseproc()
DROP SCHEMA
CREATE SCHEMA
CREATE PROCEDURE
psql:search_bug.sql:29: NOTICE: drop cascades to function
mytestbugschema02.afunction(text)
[Thread2] Thu Dec 19 10:02:48 PM CET 2024
[Thread2] Running install script
DROP SCHEMA
CREATE SCHEMA
CREATE FUNCTION
oid | proname | pronamespace | proowner | prolang | procost | prorows |
provariadic | prosupport | prokind | prosecdef | proleakproof | proisstrict |
proretset | provolatile | proparallel | pronargs | pronargdefaults | prorettype
| proargtypes | proallargtypes | proargmodes | proargnames | proargdefaults |
protrftypes | prosrc | probin | prosqlbody | proconfig | proacl
-----+---------+--------------+----------+---------+---------+---------+-------------+------------+---------+-----------+--------------+-------------+-----------+-------------+-------------+----------+-----------------+------------+-------------+----------------+-------------+-------------+----------------+-------------+--------+--------+------------+-----------+--------
(0 rows)
[Thread1] Checking result of check_sql
DROP SCHEMA
CREATE SCHEMA
CREATE FUNCTION
psql:search_bug.sql:14: NOTICE: drop cascades to function
mytestbugschema01.baseproc()
[Thread1] Found problem, stopping thread.
DROP SCHEMA
CREATE SCHEMA
CREATE PROCEDURE
psql:search_bug.sql:29: NOTICE: drop cascades to function
mytestbugschema02.afunction(text)
DROP SCHEMA
CREATE SCHEMA
CREATE FUNCTION
oid | proname | pronamespace | proowner | prolang | procost | prorows |
provariadic | prosupport | prokind | prosecdef | proleakproof | proisstrict |
proretset | provolatile | proparallel | pronargs | pronargdefaults | prorettype
| proargtypes | proallargtypes | proargmodes | proargnames | proargdefaults |
protrftypes | prosrc | probin | prosqlbody | proconfig | proacl
-------+-----------+--------------+----------+---------+---------+---------+-------------+------------+---------+-----------+--------------+-------------+-----------+-------------+-------------+----------+-----------------+------------+-------------+----------------+-------------+-------------+----------------+-------------+-----------------------+--------+------------+-----------+--------
16733 | afunction | 16732 | 16385 | 13655 | 100 | 0 |
0 | - | f | f | f | f | f
| v | u | 1 | 0 | 25 | 25
| | | {pinput} | |
| +| | | |
| | | | | | |
| | | | | |
| | | | | |
| | | | |
| begin +| | | |
| | | | | | |
| | | | | |
| | | | | |
| | | | |
| return 'overwrite'; +| | | |
| | | | | | |
| | | | | |
| | | | | |
| | | | |
| END | | | |
(1 row)
[Thread2] Checking result of check_sql
[Thread2] Found problem, stopping thread.
Both threads have completed.
real 0m46.865s
user 0m0.612s
sys 0m1.399s
...
[Thread2] Sleeping for 0.656 seconds.
[Thread1] Sleeping for 0.258 seconds.
[Thread1] Thu Dec 19 10:05:14 PM CET 2024
[Thread1] Running install script
psql:search_bug.sql:1: NOTICE: drop cascades to function
mytestbugschema02.afunction(text)
DROP SCHEMA
[Thread2] Thu Dec 19 10:05:14 PM CET 2024
[Thread2] Running install script
CREATE SCHEMA
CREATE FUNCTION
psql:search_bug.sql:14: NOTICE: drop cascades to function
mytestbugschema01.baseproc()
psql:search_bug.sql:1: NOTICE: drop cascades to function
mytestbugschema02.afunction(text)
DROP SCHEMA
DROP SCHEMA
CREATE SCHEMA
CREATE SCHEMA
CREATE PROCEDURE
CREATE FUNCTION
psql:search_bug.sql:14: NOTICE: drop cascades to function
mytestbugschema01.baseproc()
DROP SCHEMA
DROP SCHEMA
CREATE SCHEMA
CREATE SCHEMA
CREATE FUNCTION
CREATE PROCEDURE
psql:search_bug.sql:29: NOTICE: drop cascades to function
mytestbugschema02.afunction(text)
oid | proname | pronamespace | proowner | prolang | procost | prorows |
provariadic | prosupport | prokind | prosecdef | proleakproof | proisstrict |
proretset | provolatile | proparallel | pronargs | pronargdefaults | prorettype
| proargtypes | proallargtypes | proargmodes | proargnames | proargdefaults |
protrftypes | prosrc | probin | prosqlbody | proconfig | proacl
-------+-----------+--------------+----------+---------+---------+---------+-------------+------------+---------+-----------+--------------+-------------+-----------+-------------+-------------+----------+-----------------+------------+-------------+----------------+-------------+-------------+----------------+-------------+------------------+--------+------------+-----------+--------
17033 | afunction | 17031 | 16385 | 13655 | 100 | 0 |
0 | - | f | f | f | f | f
| v | u | 1 | 0 | 25 | 25
| | | {pinput} | |
| +| | | |
| | | | | | |
| | | | | |
| | | | | |
| | | | |
| begin +| | | |
| | | | | | |
| | | | | |
| | | | | |
| | | | |
| return 'base'; +| | | |
| | | | | | |
| | | | | |
| | | | | |
| | | | |
| END | | | |
(1 row)
[Thread1] Checking result of check_sql
DROP SCHEMA
CREATE SCHEMA
CREATE FUNCTION
oid | proname | pronamespace | proowner | prolang | procost | prorows |
provariadic | prosupport | prokind | prosecdef | proleakproof | proisstrict |
proretset | provolatile | proparallel | pronargs | pronargdefaults | prorettype
| proargtypes | proallargtypes | proargmodes | proargnames | proargdefaults |
protrftypes | prosrc | probin | prosqlbody | proconfig | proacl
-------+-----------+--------------+----------+---------+---------+---------+-------------+------------+---------+-----------+--------------+-------------+-----------+-------------+-------------+----------+-----------------+------------+-------------+----------------+-------------+-------------+----------------+-------------+------------------+--------+------------+-----------+--------
17033 | afunction | 17031 | 16385 | 13655 | 100 | 0 |
0 | - | f | f | f | f | f
| v | u | 1 | 0 | 25 | 25
| | | {pinput} | |
| +| | | |
| | | | | | |
| | | | | |
| | | | | |
| | | | |
| begin +| | | |
| | | | | | |
| | | | | |
| | | | | |
| | | | |
| return 'base'; +| | | |
| | | | | | |
| | | | | |
| | | | | |
| | | | |
| END | | | |
(1 row)
[Thread2] Checking result of check_sql
[Thread1] Found problem, stopping thread.
[Thread2] Found problem, stopping thread.
Both threads have completed.
real 1m28.489s
user 0m1.225s
sys 0m2.601s
wholetest.sh
Description: application/shellscript
