Search Postgresql Archives

Re: Issue with pg_dump due to Schema OID Error

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 <arons7@xxxxxxxxx> 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.klaver@xxxxxxxxxxx> 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.klaver@xxxxxxxxxxx

...
[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









Attachment: wholetest.sh
Description: application/shellscript


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux