Thread

1. Problem with Avg function and Money type in PostgreSQL 15

   Erki Eessaar <erki.eessaar@taltech.ee> — 2022-10-30T10:15:32Z

   CREATE TABLE Product(product_id SERIAL PRIMARY KEY,
   price MONEY NOT NULL);
   
   INSERT INTO Product(price) VALUES (30);
   INSERT INTO Product(price) VALUES (40);
   
   SELECT Avg(price) AS av FROM Product;
   
   PostgreSQL 14.5
   
   ERROR:  function avg(money) does not exist
   LINE 1: SELECT Avg(price) AS av FROM Product;
   HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
   
   
   PostgreSQL 15
   
   ERROR:  stack depth limit exceeded
   HINT:  Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.
   CONTEXT:  SQL function "avg" during inlining
   SQL function "avg" during startup
   SQL function "avg" statement 1 --this line is repeated more than 3000 times
   
   It seems to me that PostgreSQL 14.5 response is much better than in case of PostgreSQL 15.
   
   Best regards
   Erki Eessaar
   
   

2. Re: Problem with Avg function and Money type in PostgreSQL 15

   Tom Lane <tgl@sss.pgh.pa.us> — 2022-10-30T17:57:37Z

   Erki Eessaar <erki.eessaar@taltech.ee> writes:
   > CREATE TABLE Product(product_id SERIAL PRIMARY KEY,
   > price MONEY NOT NULL);
   
   > INSERT INTO Product(price) VALUES (30);
   > INSERT INTO Product(price) VALUES (40);
   
   > SELECT Avg(price) AS av FROM Product;
   
   > PostgreSQL 15
   
   > ERROR:  stack depth limit exceeded
   > HINT:  Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.
   > CONTEXT:  SQL function "avg" during inlining
   
   
   I get "function avg(money) does not exist" in all branches.
   Judging from the mention of a SQL function, you must have
   created a not-very-well-thought-out SQL function named "avg",
   and that function is recursing till stack overflow.
   
   			regards, tom lane
   
   
   
   

3. Re: Problem with Avg function and Money type in PostgreSQL 15

   Erki Eessaar <erki.eessaar@taltech.ee> — 2022-10-30T19:10:10Z

   Yes, this the case. I am sorry for the false report. I retract it and apologise for the inconvinience.
   
   Best regards
   Erki Eessaar
   ________________________________
   From: Tom Lane <tgl@sss.pgh.pa.us>
   Sent: Sunday, October 30, 2022 7:57 PM
   To: Erki Eessaar <erki.eessaar@taltech.ee>
   Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
   Subject: Re: Problem with Avg function and Money type in PostgreSQL 15
   
   Erki Eessaar <erki.eessaar@taltech.ee> writes:
   > CREATE TABLE Product(product_id SERIAL PRIMARY KEY,
   > price MONEY NOT NULL);
   
   > INSERT INTO Product(price) VALUES (30);
   > INSERT INTO Product(price) VALUES (40);
   
   > SELECT Avg(price) AS av FROM Product;
   
   > PostgreSQL 15
   
   > ERROR:  stack depth limit exceeded
   > HINT:  Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.
   > CONTEXT:  SQL function "avg" during inlining
   
   
   I get "function avg(money) does not exist" in all branches.
   Judging from the mention of a SQL function, you must have
   created a not-very-well-thought-out SQL function named "avg",
   and that function is recursing till stack overflow.
   
                           regards, tom lane