Table & functions not creating under Azure Postgre Flexible Server

[lliegeois]

Using azure postgre flexible server, the schema does not create itself (neither table & functions)

Strange thing we can see the following script (our table name is sessions) submitted to the server (using query store) but seems to have no effect and we have an exception because functions are not created

-- Table: public."DistCache"

-- DROP TABLE public."DistCache";

CREATE TABLE IF NOT EXISTS public.sessions ( "Id" text COLLATE pg_catalog."default" NOT NULL, "Value" bytea, "ExpiresAtTime" timestamp with time zone, "SlidingExpirationInSeconds" double precision, "AbsoluteExpiration" timestamp with time zone, CONSTRAINT "DistCache_pkey" PRIMARY KEY ("Id") ) WITH ( OIDS = FALSE ) TABLESPACE pg_default

Exception

Microsoft.AspNetCore.Session.DistributedSession: Error: Session cache read exception, Key:ecbae211-7fc6-3621-24d5-e09e7b2f914c

Npgsql.PostgresException (0x80004005): 42883: function public.updatecacheitemformat(SchemaName => text, TableName => text, DistCacheId => text, UtcNow => timestamp with time zone) does not exist at Npgsql.NpgsqlConnector.g__ReadMessageLong|194_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage) at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken) at Npgsql.NpgsqlDataReader.NextResult() at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteNonQuery() at Community.Microsoft.Extensions.Caching.PostgreSql.DatabaseOperations.GetCacheItem(String key, Boolean includeValue) at Community.Microsoft.Extensions.Caching.PostgreSql.DatabaseOperations.GetCacheItem(String key) at Community.Microsoft.Extensions.Caching.PostgreSql.PostgreSqlCache.Get(String key) at Microsoft.AspNetCore.Session.DistributedSession.Load() Exception data: Severity: ERROR SqlState: 42883 MessageText: function public.updatecacheitemformat(SchemaName => text, TableName => text, DistCacheId => text, UtcNow => timestamp with time zone) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 15 File: parse_func.c Line: 627 Routine: ParseFuncOrColumn

[lliegeois]

Additional info

during table creation exception catch should log the actual issue

                catch (Exception)
                {
                    //
                    transaction.Rollback();

                }

[leonibr]

Where did you added the initial configuration? Is CreateInfraestructure = true:

services.AddDistributedPostgreSqlCache(setup => 
{
    setup.ConnectionString = configuration["ConnectionString"];
    setup.SchemaName = configuration["SchemaName"];
    setup.TableName = configuration["TableName"];
    setup.DisableRemoveExpired = configuration["DisableRemoveExpired"];
    // Optional - DisableRemoveExpired default is FALSE
    setup.CreateInfrastructure = configuration["CreateInfrastructure"];
    // CreateInfrastructure is optional, default is TRUE
    // This means que every time starts the application the 
    // creation of table and database functions will be verified.
})

[lliegeois]

Yes, create infrastructure is true. I think there is an issue sending SQL request and the failure is silently catched. I will try to get the code and run it to see if an exception occurs. below the config. Tried with and without Search Path, no effect.

builder.Services.AddDistributedPostgreSqlCache(setup => { setup.ConnectionString = "Server = 1; Database = ; User ID = *; password = **; timeout = 1000; Ssl Mode = Require; TrustServerCertificate = true; Search Path=public"; setup.SchemaName = "public"; setup.TableName = "sesscache"; setup.CreateInfrastructure = true; });

[lliegeois]

Additional info : I reproduced the issue and debugged it.

When I run the sql (below) I get : 42501: leakproof attribute is allowed only in ALTER FUNCTION'

-- Table: public."DistCache"

CREATE SCHEMA IF NOT EXISTS public;

CREATE TABLE IF NOT EXISTS public.sesscache ( "Id" text COLLATE pg_catalog."default" NOT NULL, "Value" bytea, "ExpiresAtTime" timestamp with time zone, "SlidingExpirationInSeconds" double precision, "AbsoluteExpiration" timestamp with time zone, CONSTRAINT "DistCache_pkey" PRIMARY KEY ("Id") ) WITH ( OIDS = FALSE ) TABLESPACE pg_default; -- FUNCTION: public.datediff(character varying, timestamp with time zone, timestamp with time zone)

-- DROP FUNCTION public.datediff(character varying, timestamp with time zone, timestamp with time zone);

CREATE OR REPLACE FUNCTION public.datediff( units character varying, start_t timestamp with time zone, end_t timestamp with time zone) RETURNS integer LANGUAGE 'plpgsql' COST 100.0 VOLATILE NOT LEAKPROOF AS $function$

DECLARE diff_interval INTERVAL; diff INT = 0; years_diff INT = 0; BEGIN IF units IN ('yy', 'yyyy', 'year', 'mm', 'm', 'month') THEN years_diff = DATE_PART('year', end_t) - DATE_PART('year', start_t);

   IF units IN ('yy', 'yyyy', 'year') THEN
     -- SQL Server does not count full years passed (only difference between year parts)
     RETURN years_diff;
   ELSE
     -- If end month is less than start month it will subtracted
     RETURN years_diff * 12 + (DATE_PART('month', end_t) - DATE_PART('month', start_t)); 
   END IF;
 END IF;

 -- Minus operator returns interval 'DDD days HH:MI:SS'  
 diff_interval = end_t - start_t;

 diff = diff + DATE_PART('day', diff_interval);

 IF units IN ('wk', 'ww', 'week') THEN
   diff = diff/7;
   RETURN diff;
 END IF;

 IF units IN ('dd', 'd', 'day') THEN
   RETURN diff;
 END IF;

 diff = diff * 24 + DATE_PART('hour', diff_interval); 

 IF units IN ('hh', 'hour') THEN
    RETURN diff;
 END IF;

 diff = diff * 60 + DATE_PART('minute', diff_interval);

 IF units IN ('mi', 'n', 'minute') THEN
    RETURN diff;
 END IF;

 diff = diff * 60 + DATE_PART('second', diff_interval);

 RETURN diff;

END;

$function$; -- FUNCTION: public.getcacheitemformat(text, text, text, timestamp with time zone)

-- DROP FUNCTION public.getcacheitemformat(text, text, text, timestamp with time zone);

CREATE OR REPLACE FUNCTION public.getcacheitemformat( "SchemaName" text, "TableName" text, "DistCacheId" text, "UtcNow" timestamp with time zone) RETURNS TABLE(distcache_id text, distcache_value bytea, distcache_expiresattime timestamp with time zone, distcache_slidingexpirationinseconds bigint, distcache_absoluteexpiration timestamp with time zone) LANGUAGE 'plpgsql' COST 100.0 VOLATILE NOT LEAKPROOF ROWS 1000.0 AS $function$

DECLARE v_Query Text; DECLARE var_r record;

BEGIN

v_Query := format('SELECT "Id", "Value", "ExpiresAtTime", "SlidingExpirationInSeconds", "AbsoluteExpiration" ' || 'FROM %I.%I WHERE "Id" = $1 AND $2 <= "ExpiresAtTime"', "SchemaName", "TableName");

FOR var_r IN EXECUTE v_Query USING "DistCacheId", "UtcNow" LOOP DistCache_Id := var_r."Id" ; DistCache_Value := var_r."Value"; DistCache_ExpiresAtTime := var_r."ExpiresAtTime"; DistCache_SlidingExpirationInSeconds := var_r."SlidingExpirationInSeconds"; DistCache_AbsoluteExpiration := var_r."AbsoluteExpiration"; RETURN NEXT; END LOOP; END

$function$;

-- FUNCTION: public.setcache(text, text, text, bytea, double precision, timestamp with time zone, timestamp with time zone)

-- DROP FUNCTION public.setcache(text, text, text, bytea, double precision, timestamp with time zone, timestamp with time zone);

CREATE OR REPLACE FUNCTION public.setcache( "SchemaName" text, "TableName" text, "DistCacheId" text, "DistCacheValue" bytea, "DistCacheSlidingExpirationInSeconds" double precision, "DistCacheAbsoluteExpiration" timestamp with time zone, "UtcNow" timestamp with time zone) RETURNS void LANGUAGE 'plpgsql' COST 100.0 VOLATILE NOT LEAKPROOF AS $function$

DECLARE v_ExpiresAtTime TIMESTAMP(6) WITH TIME ZONE; DECLARE v_RowCount INT; DECLARE v_Query Text; BEGIN

CASE WHEN ("DistCacheSlidingExpirationInSeconds" IS NUll) THEN v_ExpiresAtTime := "DistCacheAbsoluteExpiration"; ELSE v_ExpiresAtTime := "UtcNow" + "DistCacheSlidingExpirationInSeconds" * interval '1 second'; END CASE;

v_Query := format('UPDATE %I.%I SET "Value" = $1, "ExpiresAtTime" = $2, "SlidingExpirationInSeconds" = $3, "AbsoluteExpiration" = $4 WHERE "Id" = $5', "SchemaName", "TableName"); EXECUTE v_Query using "DistCacheValue", v_ExpiresAtTime, "DistCacheSlidingExpirationInSeconds", "DistCacheAbsoluteExpiration", "DistCacheId";

GET DIAGNOSTICS v_RowCount := ROW_COUNT; v_Query := format('INSERT INTO %I.%I ("Id", "Value", "ExpiresAtTime", "SlidingExpirationInSeconds", "AbsoluteExpiration") VALUES($1, $2, $3, $4, $5)', "SchemaName", "TableName");

IF(v_RowCount = 0) THEN EXECUTE v_Query using "DistCacheId", "DistCacheValue", v_ExpiresAtTime, "DistCacheSlidingExpirationInSeconds", "DistCacheAbsoluteExpiration"; END IF; END

$function$; -- FUNCTION: public.updatecacheitemformat(text, text, text, timestamp with time zone)

-- DROP FUNCTION public.updatecacheitemformat(text, text, text, timestamp with time zone);

CREATE OR REPLACE FUNCTION public.updatecacheitemformat( "SchemaName" text, "TableName" text, "DistCacheId" text, "UtcNow" timestamp with time zone) RETURNS void LANGUAGE 'plpgsql' COST 100.0 VOLATILE NOT LEAKPROOF AS $function$

DECLARE v_Query Text; BEGIN

v_Query := format('UPDATE %I.%I ' || 'SET "ExpiresAtTime" = ' 'CASE ' || 'WHEN (SELECT %I.datediff(''seconds''::varchar, $1, "AbsoluteExpiration")) <= "SlidingExpirationInSeconds" ' || 'THEN "AbsoluteExpiration" ' || 'ELSE $1 + "SlidingExpirationInSeconds" * interval ''1 second'' ' || 'END ' || 'WHERE "Id" = $2 ' || 'AND $1 <= "ExpiresAtTime" ' || 'AND "SlidingExpirationInSeconds" IS NOT NULL ' || 'AND ("AbsoluteExpiration" IS NULL OR "AbsoluteExpiration" <> "ExpiresAtTime")', "SchemaName", "TableName", "SchemaName"); EXECUTE v_Query using "UtcNow", "DistCacheId";

END $function$;

-- FUNCTION: public.deletecacheitemformat(text, text, text)

-- DROP FUNCTION public.deletecacheitemformat(text, text, text);

CREATE OR REPLACE FUNCTION public.deletecacheitemformat( "SchemaName" text, "TableName" text, "DistCacheId" text) RETURNS void LANGUAGE 'plpgsql' COST 100.0 VOLATILE NOT LEAKPROOF AS $function$

DECLARE v_Query Text; BEGIN

v_Query := format('DELETE FROM %I.%I WHERE "Id" = $1', "SchemaName", "TableName"); EXECUTE v_Query using "DistCacheId";

END

$function$;

-- FUNCTION: public.deleteexpiredcacheitemsformat(text, text, timestamp with time zone)

-- DROP FUNCTION public.deleteexpiredcacheitemsformat(text, text, timestamp with time zone);

CREATE OR REPLACE FUNCTION public.deleteexpiredcacheitemsformat( "SchemaName" text, "TableName" text, "UtcNow" timestamp with time zone) RETURNS void LANGUAGE 'plpgsql' COST 100.0 VOLATILE NOT LEAKPROOF AS $function$

DECLARE v_Query Text;

BEGIN

v_Query := format('DELETE FROM %I.%I WHERE $1 > "ExpiresAtTime"', "SchemaName", "TableName"); EXECUTE v_Query USING "UtcNow";

END

$function$;

[leonibr]

funny this never happened before, what version of postgres you are using? I will investigate this right away

[leonibr]

Well, I cannot reproduce the error, using versions 14, 13 and 12 all without trouble... I also checked the postgres documentation and it is possible to use LEAKPROOF attribute on CREATE FUNCTION statements since version 9.2; Maybe you are using some custom build of postgres with different set of implementations... I am not comfortable removing from the lib since this is a protection, but I suggest that you get in touch with Azure support to check that out first, keep me informed of your findings please, I will update the script to add an ALTER FUNCTION with the attribute LEAKPROOF as the error message pointed out and do some tests for while.

[lliegeois]

Hi, sorry for reply delay, I will send a request to Azure; and in all case I will be available if some script execution test is needed on the server. I think there is probable some option activated on the server that prevent this.

[DELAY]To send a support request I need approval from our Azure vendor. Due to holidays, this will be delayed to beginning of January, 2022. Merry Christmas & Happy new Year !

[UPDATE 01/04/2022]Support created at our reseller (TechData) who is currently investigating the issue. If needed they will escalate to Microsoft. Will keep you informed.

[lliegeois]

Here is the reply from Microsoft :

“LEAKPROOF attribute it’s not currently supported on Flexible Server and will be supported in next release.”

The goal now is to know... when is the next release :-) will try to get the information and give it here

[lliegeois]

Here is the reply from Microsoft, we are currently in January 2022 :

We don't have an guaranteed date, but it's expected to be towards end of this month

==> LEAKPROOF should be supported in february 2022; I will check

[leonibr]

Thanks for the update!

[lliegeois]

Guys, good news ! LEAKPROOF works with Azure Postgre Flexible Server 13.7, tested in France Central Area. Not testing the complete provider though, but I'm confident :-)

Sorry for long delay, had to wait new version release of Postgre Flexible Server by Microsoft...