Postgres function calls do not work any more for postgres versions < 17.

[chicko12345]

Hi Community,

the change introduced by
#2308
leads to unexpected behavior in our environment. Postgres functions cannot be triggered as it was in eclipselink 4.0.4
The commit removes specific handling of postgres function calls which seem to be necessary for postgres versions < 17.

Local revert of:

• Revert "Fix calling stored procs on Postgres, and enable some tests (Fix calling stored procs on Postgres, and enable some tests #2308)"

This reverts commit 452ffdd.

fixes the problem.

Our environment:

• Glassfish 7.0.22
• EclipseLink 4.0.5
• Java/JDK version JavaSE 21
• Postgres 16.6

To Reproduce
If a postgres function is defined

create function some_fuction(param1 text, param2 integer, param3 integer) returns void
    language plpgsql
as
$$
BEGIN
 // Do something
end;
$$;

Both following versions of the function call throw SQL Exceptions:

//def and set param1, param2, param3, open transaction
StoredProcedureQuery storedProcedure = perServ.createStoredProcedureQuery("SOME_FUNCTION", void.class);
storedProcedure.registerStoredProcedureParameter("param1", String.class, ParameterMode.IN);
storedProcedure.registerStoredProcedureParameter("param2", Integer.class, ParameterMode.IN);
storedProcedure.registerStoredProcedureParameter("param3", Integer.class, ParameterMode.IN);
storedProcedure.setParameter("param1", param1);
storedProcedure.setParameter("param2", param2);
storedProcedure.setParameter("param3", param3);
storedProcedure.execute();

//def and set param1, param2, param3, open transaction
try (Connection c = /*create connection*/)){ 
  c.setAutoCommit(false);
  try (CallableStatement cs = c.prepareCall("{call SOME_FUNCTION(?,?,?)}")) {
	cs.setString("param1", param1);
	cs.setInt("param2", param2);
	cs.setInt("param3", param3);
	cs.execute();      
  }
  c.commit();
}
//Exception handling			
...

Kind Regards

[rfelcman]

To @chicko12345 @rdicroce
Hello as I don't know all PostrgreSQL tricks. How is possible to call this kind of stored function
CREATE FUNCTION test_fuction1(param1 text, param2 INTEGER, param3 INTEGER) RETURNS VOID
and stored procedure
CREATE PROCEDURE test_procedure1(param1 text, param2 INTEGER, param3 INTEGER)
with same call statement like CALL " + <FUNCTION_NAME OR PROCEDURE_NAME> + "(?,?,?).
CALL PROCEDURE_NAME(?,?,?) works for stored procedures, but without curly brackets around
{CALL FUNCTION_NAME(?,?,?)} works for stored function, but with curly brackets around

[rdicroce]

I'm not an expert on PG. My company just started using it, and I've since been pulled off to work on other things. But I know that stored function != stored procedure in PG, and you can't call them the same way. IIRC for stored procs, you need to do what my patch does. For stored funcs, you need to use SELECT.

[chicko12345]

@rfelcman @rdicroce
Hi Guys,

sorry for the late answer!

That was a weird one. :-D! We found it. Both works on postgresql 16.6 with eclipselink 4.0.4 IF AND ONLY IF following setting is set:
#725

With eclipselink 4.0.5 it doesn't work any more. If the commit is reverted as already described, it works again.

Update:
The second call doesn't work with named parameters, but with indexed:

try {
			em.getTransaction().begin();
			String newName = "Name set by call with curly braces";
			int id = (int) GROUP_ID;
			group = em.find((Group.class), GROUP_ID);
			System.out.println("Group name before\t call with curly braces:\t" +
					group.getName());
			try (java.sql.Connection c = em.unwrap(java.sql.Connection.class)) {
				c.setAutoCommit(false);
				try (CallableStatement cs = c.prepareCall("{call SOME_FUNCTION(?,?)}")) {
					cs.setString(1, param1);
					cs.setInt(2, param2); 
					cs.setInt(3,param3);
					cs.execute();
				}
				c.commit();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException();
		} finally {
			em.close();
		}

Kind Regards

[rdicroce]

I am not surprised that named params do not work without that setting, because pgjdbc does not natively support named params. See pgjdbc/pgjdbc#2486.

Is UPDATE_GROUP a stored function or a stored procedure? Again, those are different things in PG. Calling a procedure should be fine with my patch. If calling a function as if it were a procedure happened to work in 4.0.4 and doesn't in 4.0.5, then IMO that was undocumented behavior, and I'd suggest changing your code to do SELECT instead since that is what is shown in the PG documentation.

[chicko12345]

I am not surprised that named params do not work without that setting, because pgjdbc does not natively support named params. See pgjdbc/pgjdbc#2486.

Is UPDATE_GROUP a stored function or a stored procedure? Again, those are different things in PG. Calling a procedure should be fine with my patch. If calling a function as if it were a procedure happened to work in 4.0.4 and doesn't in 4.0.5, then IMO that was undocumented behavior, and I'd suggest changing your code to do SELECT instead since that is what is shown in the PG documentation.

HI @rdicroce,

thanks for the quick response.

UPDATE_GROUP is a function. I've created it for testing purposes only and the calls work until eclipselink 4.0.4. We are aware of the difference between procedures and functions. There is a function which was called in the manner i've described since it was introduced. It woked from glassfish 5.0.1 (eclipselink 2.7.X) to glassfish 7.0.21 (eclipselink 4.0.3). During the tests on glassfish 7.0.22 (eclipselink 4.0.5) we had to deal with the problem for the first time .

We know, that the function isn't an appropriate construct here to be called in that way. However, we wanted to understand the reason, why it was even possible. So yes, it was with high probability just a side effect of eclipselink with logically unexpected (but in our case practically right :-)) behavior.

Kind Regards

[simartn]

I also have problems calling stored procedures with 4.0.5.

With 4.0.4 I could call a PostgreSQL function create function test_eclipselink_func(message text, num integer) returns void [...] with eclipselink.jpa.naming_into_indexed=true and named parameters.

With 4.0.5 I can call a PostgreSQL stored procedure create procedure expl_update_func(message text, num1 integer, num2 integer) [...] with numbered parameters, but not with named parameters.

jakarta.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 4.0.5.v202412231137-a96b873527f305f932543045c8679bb1de8d3a43): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: column "message" does not exist
  Position: 23
Error Code: 0
Call: CALL expl_update_func(message = ?, num= ?)
	bind => [2 parameters bound]
[...]

Postgresql Needs CALL expl_update_func(?, ?) for indexed parameters.

I adapted org.eclipse.persistence.jpa.test.storedproc.TestStoredProcedures EntityManagerFactory with the nameing_to_indexed parameter to prove my finding.

Is there a chance to fix this bug?