estimateguestbookresponsetablesize Postgres function call fails (No procedure matches the given name and argument types)

[michael-conway]

What steps does it take to reproduce the issue?

Clean install of dataverse (I tried 6.8 and then tried stepping back to 6.71).

Install on RedHat, Postgres 16 as a development server

• When does this issue occur?

On initial install

• Which page(s) does it occurs on?

'root' page would show "some exception has occurred, no further information.

• What happens?

Initial install per prereqs and install instructions on a development server. After install dataverse comes up, able to set initial admin password, then error screen is displayed as above.

partial stack trace:

Caused by: jakarta.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 4.0.7.payara-p1.v202509010857-ff7dcebd9d4eec4ff0c85f78950a3719609b36cc): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: procedure estimateguestbookresponsetablesize(unknown) does not exist
  Hint: No procedure matches the given name and argument types. You might need to add explicit type casts.
  Position: 6
Error Code: 0
Call: CALL estimateGuestBookResponseTableSize(?)
	bind => [1 parameter bound]
Query: ResultSetMappingQuery(name="GuestbookResponse.estimateGuestBookResponseTableSize" )
	at org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:392)
	at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:265)
	at org.eclipse.persistence.internal.jpa.StoredProcedureQueryImpl.execute(StoredProcedureQueryImpl.java:315)
	at edu.harvard.iq.dataverse.GuestbookResponseServiceBean.getTotalDownloadCount(GuestbookResponseServiceBean.java:956)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:569)
	at org.glassfish.ejb.security.application.EJBSecurityManager.runMethod(EJBSecurityManager.java:589)
	at org.glassfish.ejb.security.application.EJBSecurityManager.invoke(EJBSecurityManager.java:409)
	at com.sun.ejb.containers.BaseContainer.invokeBeanMethod(BaseContainer.java:4835)
	at com.sun.ejb.EjbInvocation.invokeBeanMethod(EjbInvocation.java:654)
	at com.sun.ejb.containers.interceptors.AroundInvokeChainImpl.invokeNext(InterceptorManager.java:834)
	at com.sun.ejb.EjbInvocation.proceed(EjbInvocation.java:604)
	at com.sun.ejb.containers.interceptors.SystemInterceptorProxy.doCall(SystemInterceptorProxy.java:163)
	at com.sun.ejb.containers.interceptors.SystemInterceptorProxy.aroundInvoke(SystemInterceptorProxy.java:140)
	at jdk.internal.reflect.GeneratedMethodAccessor157.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:569)
	at com.sun.ejb.containers.interceptors.AroundInvokeInterceptor.intercept(InterceptorManager.java:888)
	at com.sun.ejb.containers.interceptors.AroundInvokeChainImpl.invokeNext(InterceptorManager.java:833)
	at com.sun.ejb.EjbInvocation.proceed(EjbInvocation.java:604)
	at org.jboss.weld.module.ejb.AbstractEJBRequestScopeActivationInterceptor.aroundInvoke(AbstractEJBRequestScopeActivationInterceptor.java:78)
	at org.jboss.weld.module.ejb.SessionBeanInterceptor.aroundInvoke(SessionBeanInterceptor.java:52)

• To whom does it occur (all users, curators, superusers)?

All

• What did you expect to happen?

Which version of Dataverse are you using?

6.8 and then 6.71

Any related open or closed issues to this bug report?

Are you thinking about creating a pull request for this issue?
Help is always welcome, is this bug something you or your organization plan to fix?

The fix appears to be related to the stored procedure for estimateguestbookresponsetablesize

With the help of ChatGPT here's what I ended up at:

the DB object exists and works when you pass the OUT placeholder. The app is still failing because it binds one parameter, but PostgreSQL requires an argument placeholder for every parameter that has no default (including OUT). Fix it by redefining the routine so a single-arg CALL is valid and still returns a result set.

The fix, which so far seems to hold, is as follows

-- 1) Replace the current OUT signature with INOUT (works on PG 11–18)
DROP PROCEDURE IF EXISTS public.estimateguestbookresponsetablesize(text, bigint);

CREATE OR REPLACE PROCEDURE public.estimateguestbookresponsetablesize(
    IN tablename text,
    INOUT total_bytes bigint DEFAULT NULL
)
LANGUAGE plpgsql AS $$
BEGIN
  total_bytes := pg_total_relation_size(tablename::regclass);
END;
$$;

-- 2) Sanity test: now a single-arg CALL works and returns the OUT column
CALL public.estimateguestbookresponsetablesize('public.guestbookresponse');

[qqmyers]

Are you using Payara 6.2025.10 by chance? There are at least two changes required for Dataverse to work with it - see #11827 which will go into Dataverse v6.9.

[qqmyers]

@michael-conway FYI: #11827 is now passing all the tests so it looks like the change to the query and renaming the mime.types file are all that's needed for Payara 6.21025.10.

[qqmyers]

With #11827 merged and planned for the 6.9 release, I'd suggest we close this issue as resolved.

[pdurbin]

Sounds good. Closing. @michael-conway please let us know if we should re-open it!