No function matches the given name and argument types.
Dear All, I'm facing a general problem and I'm looking the best, fastest, way how to identify the problem and solve it. As example assume we have a function like that: CREATE OR REPLACE FUNCTION testBinding01 ( p_in01 bigint, p_in02 bigint, p_in03 bigint, p_in04 bigint, p_in05 bigint, p_in06 bigint, p_text7 text ) RETURNS text LANGUAGE sql AS $$ select 'ciao'; $$; I can call the function in some of the variant below: select testBinding01(1,2,3,4,5,6,7); select testBinding01(p_in01 => 1,p_in02 => 2,p_in03 => 3,p_in04 => 4,p_in05 => 5,p_in06 => 6,p_text7 => 7); select testBinding01(p_in01 => 1,p_in02 => 2,p_in03 => 3,p_in04 => 4,p_in05 => 5,p_in06 => 6,p_text9 => 'some txt'); All of the above, produce the error: *No function matches the given name and argument types.* My question is: how is the best way to identify the problem? Is a parameter name? is a parameter type? is the function name? An especially in case is a parameter type how is the easy way to identify which parameter is causing the problem? In case a function has a lot of parameters (and in even worst case has some overloading) going trough all parameters to check its type/name costs a lot of time. Thanks for any help Renzo
Re: No function matches the given name and argument types.
Why the error happen is clear to me, in the example is also easy to see that the 7th parameter is the problem. But I'm searching a more general way to find easily which of the parameter is the problem. Suppose you have a function with 30 parameters with mixed sort of types. They only way I know right now is to compare the position, name and type one parameter after the other until I found the one that do not match. A sort of brute force. Is there any better way to do that? Thanks On Mon, Jan 16, 2023 at 5:21 PM Adrian Klaver wrote: > On 1/16/23 08:17, Adrian Klaver wrote: > > On 1/16/23 08:04, arons wrote: > >> Dear All, > >> I'm facing a general problem and I'm looking the best, fastest, way > >> how to identify the problem and solve it. > >> > >> As example assume we have a function like that: > >> > >> CREATE OR REPLACE FUNCTION testBinding01 ( > >> > >> p_in01 bigint, > >> > >> p_in02 bigint, > >> > >> p_in03 bigint, > >> > >> p_in04 bigint, > >> > >> p_in05 bigint, > >> > >> p_in06 bigint, > >> > >> p_text7 text > >> > >> ) RETURNS text > >> > >> LANGUAGE sql > >> > >> AS $$ > >> > >> select 'ciao'; > >> > >> $$; > >> > >> > >> > >> I can call the function in some of the variant below: > >> > >> select testBinding01(1,2,3,4,5,6,7); > >> > >> select testBinding01(p_in01 => 1,p_in02 => 2,p_in03 => 3,p_in04 => > >> 4,p_in05 => 5,p_in06 => 6,p_text7 => 7); > >> > >> select testBinding01(p_in01 => 1,p_in02 => 2,p_in03 => 3,p_in04 => > >> 4,p_in05 => 5,p_in06 => 6,p_text9 => 'some txt'); > >> > >> > >> All of the above, produce the error: > >> > >> *No function matches the given name and argument types.* > > > > In psql what does: > > > > \df test* > > > > return for the function name. > > > > I'm going to guess it might be testBinding01, in other words mixed case. > > > > Have you tried?: > > > > select "testBinding01"(1,2,3,4,5,6,7); > > Forget the above. Instead: > > select testBinding01(1,2,3,4,5,6,7); > ERROR: function testbinding01(integer, integer, integer, integer, > integer, integer, integer) does not exist > LINE 1: select testBinding01(1,2,3,4,5,6,7); > > > select testBinding01(1,2,3,4,5,6,'7'); > > testbinding01 > --- > ciao > > The complete error shows what the function is receiving, all integers > when it needs a text parameter for the last value. > > >> * > >> * > >> * > >> * > >> * > >> * > >> My question is: how is the best way to identify the problem? > >> > >> Is a parameter name? is a parameter type? is the function name? > >> > >> An especially in case is a parameter type how is the easy way to > >> identify which parameter is causing the problem? > >> > >> In case a function has a lot of parameters (and in even worst case has > >> some overloading) going trough all parameters to check its type/name > >> costs a lot of time. > >> > >> > >> Thanks for any help > >> > >> Renzo > >> > >> > >> > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
Re: Issue with pg_dump due to Schema OID Error
I forgot to attache the script.
On Thu, Dec 19, 2024 at 10:41 AM Renzo Dani wrote:
> Hi,
>
>
> Recently, I encountered a problem during a database export using pg_dump.
>
>
> Here is the error message:
>
>
> pg_dump: last built-in OID is 16383
>
> pg_dump: reading extensions
>
> pg_dump: identifying extension members
>
> pg_dump: reading schemas
>
> pg_dump: reading user-defined tables
>
> pg_dump: reading user-defined functions
>
> pg_dump: error: schema with OID 41960442 does not exist
>
>
> To investigate the issue, I ran the following query:
>
>
> SELECT * FROM pg_proc WHERE pronamespace = 41960442;
>
>
> The result:
>
>
> 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
>
> 41966618;remapprotocoltypeids
> ;41960442;19214494;13547;100;0;0;-;f;f;f;f;f;v;u;1;0;25;25;;;{
> pprotocoltypeids};;;
>
>
> I resolved the issue by removing the problematic record (admin privileges
> required):
>
>
> DELETE FROM pg_proc WHERE oid = 41966618;
>
>
> This situation seems inconsistent and likely should not occur under normal
> conditions.
>
>
> While I’m unsure exactly when this issue originated in our environment, I
> was able to reproduce it by performing concurrent modifications on the
> schema.
>
>
> To demonstrate, I wrote a bash script (test_bug.sh) that starts two
> threads running in parallel.
>
> Each thread drops the schema with CASCADE and recreates it using the SQL
> script search_bug.sql.
>
>
> To use the script, you’ll need to adapt two variables at the beginning of
> the script: PGPASSWORD and URL.
>
>
> Using this script, I reproduced the problem on PostgreSQL versions 16.1
> and 17.1.
>
> It typically takes less than a minute to trigger the issue.
>
> The script terminates automatically as soon as the problem is detected.
>
>
> Here are additional references that might be related to this issue:
>
>
>
> https://www.postgresql.org/message-id/flat/20110209003823.GA93840%40mr-paradox.net
>
>
> https://www.postgresql.org/message-id/flat/BB8AF37F-E3D9-4DE0-B398-AF89748704F5%40bandwidth.com
>
>
>
> Let me know if you need additional information.
>
>
> Best regards
>
> Renzo
>
search_bug.sql
Description: Binary data
test_bug.sh
Description: Binary data
