This is a bug in project normalization, exposed by using a subquery inside a case expression with a non-deterministic function.
To explain, we need to note two things up front:
SQL Server cannot execute subqueries directly, so they are always unrolled or converted to an apply.
The semantics of CASE are such that a THEN expression should only be evaluated ...
Forrest is mostly right, but the finer details are:
SQL Server can't parallelize modifications to table variables, which your function uses.
Prior to SQL Server 2017's Interleaved Execution, row estimates from Multi-Statement Table Valued Functions were very low.
One side effect of this is that plans were costed very poorly on the low end, and often ...
SQL Server is trying to inline the function but failing due to the complexity.
Using so much memory while doing so is unexpected and almost certainly a bug.
A definition for the nested function dbo.IstFeiertag would be needed for a full repro.
Add WITH INLINE = OFF to the function(s) definition. Once this issue is resolved, you should ...
Instead of using xp_cmdshell to execute queries against SQL Server, change your code to use stored procedures, user-defined functions, or even dynamic T-SQL called using sp_executesql. If you need to execute code against a different server, use a linked server.
Finally, consider rewriting your code not to use a cursor if possible. Set-based operations in ...
On SQL Server 2012 and later you can use TRY_CONVERT to check to see if the input can be converted. If it can't, a NULL value is returned so then you can then do an COALESCE to get either the converted value or the fixed date.
declare @result date
set @result = COALESCE(TRY_CONVERT(date, @date, 111), '2012-01-01')
You're encountering a known bug with scalar UDF inlining. You can disable inlining using one of the methods here (or by using a lower compat level, as you've discovered yourself):
Disabling Scalar UDF Inlining without changing the compatibility level
Or install CU2 for the permanent fix.
...even running a simple scalar function (which internally uses ...
What version(s) of SQL Server are you using?
I do recall seeing a slight change in behavior in SQL Server 2017 not too long ago. I will have to go back and see if I can find where I made a note of it, but I think it had to do with a schema lock being initiated when a SQLCLR object was being accessed.
While I am looking for that, I will say the following ...
The LEFT() function in your example is not part of the EXEC statement. It is inside the string representing part of the dynamic SQL to be executed by your EXEC statement.
In contrast, the CAST() function is used to build that string. If you use the correct quotation marks – ' instead of ‘ & ’ – then perhaps you can also see the difference in the syntax ...
In Postgres 11 proisagg was replaced with prokind in the system catalog pg_proc:
prokind | char | f for a normal function, p for a procedure, a for an aggregate function, or w for a window function
The query needs to be adapted. Like:
FROM pg_catalog.pg_proc p
WHERE p.prokind = 'f' -- to only get plain functions
How to ...
Yes, an in-lined function can show different results than its out-lined (!?) counterpart. The following reliably reproduces the situation on my machine (Windows 10, 4 cores + HT @ 2GHz, 16GB RAM, SSD).
Configure the database and session to use Read Committed Snapshot Isolation (RCSI):
alter database Sandpit
set read_committed_snapshot on
with rollback ...
is it possible to convert my scalar function to Inline TVF?
Yes. Something like the below would do it.
It is still pretty hefty and if run correlated would likely be quite inefficient. As Aaron points out in the comments you are calling this with constant values though so hopefully the query plan reflects this and only runs it once.
CREATE FUNCTION [dbo].[...
The link by @Ross will definitely help you determine syntax and what all of your options are, but for a little more guidance:
There are two database roles that will probably be useful to you, they are db_datawriter and db_datareader. The two of them will probably handle the majority of your CRUD (Create, Read, Update, Delete) operations.
ALTER ROLE ...
You can create a function and use it as the default for the column:
create function f() returns text language sql as $$
SELECT string_agg (substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', ceil (random() * 62)::integer, 1), '')
FROM generate_series(1, 20)
create table foo(id serial primary key, url_prefix text not null ...
I think you need:
for _tbl in(select DISTINCT child_table from not_deferrable_constraints) loop
If a table has more than one constraints, your code will try to recreate each one of them many times.
Or just use a single for loop, I don't think you need the nesting.
for add_constraint in (select constraint_name, ...
You don't really need a function for that, you can use conditional aggregation:
count(*) filter (where event = 'start') AS start,
count(*) filter (where event = 'end') AS end
GROUP BY "user";
You can pass a complete row to a function when you declare a parameter with the type of the table:
CREATE OR REPLACE ...
In the case of phpPgAdmin, you must modify the faulty queries within \classes\database\Postgres.php.
pg_proc.proisagg (PostgreSQL 10 and before) is a boolean set at TRUE when function is an aggregate function.
pg_proc.prokind (PostgreSQL 11) is a char taking the values 'f', 'p', 'a' or 'w' (see Erwin's answer above).
Thus, for every occurence of WHERE NOT ...
You can simply supply the fifth argument with Mixed Notation using the => to separate the parameter name from the argument,
From the docs on mixed notation with the one caveat (from the docs),
Named and mixed call notations currently cannot be used when calling an aggregate ...
Not really, however in Postgres and many other DBMS vendors you can comment on various database objects like:
COMMENT ON FUNCTION my_function (timestamp) IS 'Returns Roman Numeral';
As far as I can tell there is no way you can comment on individual arguments for a function.
For the presentation of the documentation, you may want to have a look at:
That will not work like that. test_table is a table of test_row objects, not 2 columns, so you need to create test_row objects from the 2 columns, like below:
create or replace FUNCTION test_function
) RETURN test_table AS
Since the function is VOLATILE (by default), PostgreSQL doesn't know that it will return the same value for every row in central.cliente, so it is evaluated repeatedly.
Set the volatility to IMMUTABLE and PostgreSQL knows that it has to be evaluated only once:
ALTER FUNCTION ext.uf_converte_numerico(varchar(30)) IMMUTABLE;
Consider this simplified equivalent:
CREATE OR REPLACE FUNCTION ext.uf_converte_numerico(_input varchar(30))
RETURNS bigint LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
SELECT NULLIF(regexp_replace(_input, '[^0-9]+', '', 'g'), '')::bigint;
IMMUTABLE, because it is, and for the reasons Laurenz explained.
PARALLEL SAFE in Postgres 10 or later, ...
It sounds like it thinks DateColumn is already being used in a computed column, but that's not true. I checked the sys.columns system view for this table, and it has no computed columns at the moment.
I realize that you checked sys.columns to verify whether or not "DateColumn" was a computed column and it doesn't show as being one, so I am going to ...
You should definitely find a better way than doing DML through a function. You don't need to attach this process to a SELECT statement. You can just dump the results of the query to a temp table and use a CURSOR to iterate over that (just like you are doing in this function), calling a stored procedure to do what you are currently doing in this function.
Initially, it seems like this was a bug related to the new Scalar UDF Inlining feature added in SQL Server 2019, since you mentioned that disabling inlining resolved the problem. On further inspection, the function cannot be inlined due to the presence of a CTE in the function definition.
Here's my (failed) attempt to reproduce the issue:
Kathi Kellenberger did a comparison of FIFO costing for Redgate in 2010. I used the winning entry method to calculate monthly inventory quantities and values for our Mexico operations. You can search for Set based speed PHreakery if this link does not work. https://www.red-gate.com/simple-talk/sql/performance/set-based-speed-phreakery-the-fifo-stock-...
I had the same issue and ran these 2 commands to fix it
sed -i "s/NOT pp.proisagg/pp.prokind='f'/g" /usr/share/phpPgAdmin/classes/database/Postgres.php
sed -i "s/NOT p.proisagg/p.prokind='f'/g" /usr/share/phpPgAdmin/classes/database/Postgres.php
Try something like:
to_timestamp('21/12/2008 12:34:23','dd/mm/yyyy HH:MI:SS')
I assume you want to catch any exception and return null instead, so something like:
create or replace function my_to_timestamp(arg text)
returns timestamp language plpgsql
return to_timestamp(arg, 'dd/mm/yyyy HH:MI:SS');
exception when others ...