ERP Next - psycopg2.errors.InFailedSqlTransaction:

Hello , im new here, im install erp next on my frappe but always getting error. its new site with new installed apps. but always got

Server Error

psycopg2.errors.InFailedSqlTransaction: current transaction is aborted, commands ignored until end of transaction block
Possible source of error: erpnext (app)

can someone help me, here is error details

App Versions

{
	"erpnext": "16.0.0-dev",
	"frappe": "16.0.0-dev",
	"hrms": "16.0.0-dev"
}

Route

Workspaces/Buying

Traceback

Traceback (most recent call last):
  File "apps/frappe/frappe/desk/query_report.py", line 223, in run
    result = generate_report_result(report, filters, user, custom_columns, is_tree, parent_field)
  File "apps/frappe/frappe/__init__.py", line 963, in wrapper_fn
    retval = fn(*args, **get_newargs(fn, kwargs))
  File "apps/frappe/frappe/desk/query_report.py", line 84, in generate_report_result
    res = get_report_result(report, filters) or []
  File "apps/frappe/frappe/desk/query_report.py", line 65, in get_report_result
    res = report.execute_script_report(filters)
  File "apps/frappe/frappe/core/doctype/report/report.py", line 162, in execute_script_report
    res = self.execute_module(filters)
  File "apps/frappe/frappe/core/doctype/report/report.py", line 179, in execute_module
    return frappe.get_attr(method_name)(frappe._dict(filters))
  File "apps/erpnext/erpnext/buying/report/purchase_order_trends/purchase_order_trends.py", line 15, in execute
    data = get_data(filters, conditions)
  File "apps/erpnext/erpnext/controllers/trends.py", line 191, in get_data
    data = frappe.db.sql(
  File "apps/frappe/frappe/database/postgres/database.py", line 220, in sql
    return super().sql(modify_query(query), modify_values(values), *args, **kwargs)
  File "apps/frappe/frappe/database/database.py", line 236, in sql
    self._cursor.execute(query, values)
psycopg2.errors.UndefinedFunction: function if(boolean, numeric, unknown) does not exist
LINE 1: select t2.item_code, t2.item_name,SUM(IF(t1.transaction_date...
                                              ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.


During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "apps/frappe/frappe/app.py", line 114, in application
    response = frappe.api.handle(request)
  File "apps/frappe/frappe/api/__init__.py", line 49, in handle
    data = endpoint(**arguments)
  File "apps/frappe/frappe/api/v1.py", line 36, in handle_rpc_call
    return frappe.handler.handle()
  File "apps/frappe/frappe/handler.py", line 49, in handle
    data = execute_cmd(cmd)
  File "apps/frappe/frappe/handler.py", line 85, in execute_cmd
    return frappe.call(method, **frappe.form_dict)
  File "apps/frappe/frappe/__init__.py", line 1814, in call
    return fn(*args, **newargs)
  File "apps/frappe/frappe/utils/typing_validations.py", line 32, in wrapper
    return func(*args, **kwargs)
  File "apps/frappe/frappe/__init__.py", line 963, in wrapper_fn
    retval = fn(*args, **get_newargs(fn, kwargs))
  File "apps/frappe/frappe/desk/query_report.py", line 226, in run
    frappe.log_error("Report Error")
  File "apps/frappe/frappe/utils/error.py", line 62, in log_error
    error_log = frappe.get_doc(
  File "apps/frappe/frappe/__init__.py", line 1379, in get_doc
    doc = frappe.model.document.get_doc(*args, **kwargs)
  File "apps/frappe/frappe/model/document.py", line 85, in get_doc
    controller = get_controller(doctype)
  File "apps/frappe/frappe/model/base_document.py", line 67, in get_controller
    return import_controller(doctype)
  File "apps/frappe/frappe/model/base_document.py", line 82, in import_controller
    doctype_info = frappe.db.get_value("DocType", doctype, fieldname="*")
  File "apps/frappe/frappe/database/database.py", line 524, in get_value
    result = self.get_values(
  File "apps/frappe/frappe/database/database.py", line 628, in get_values
    out = self._get_values_from_table(
  File "apps/frappe/frappe/database/database.py", line 901, in _get_values_from_table
    return query.run(as_dict=as_dict, debug=debug, update=update, run=run, pluck=pluck)
  File "apps/frappe/frappe/query_builder/utils.py", line 84, in execute_query
    result = frappe.db.sql(query, params, *args, **kwargs)  # nosemgrep
  File "apps/frappe/frappe/database/postgres/database.py", line 220, in sql
    return super().sql(modify_query(query), modify_values(values), *args, **kwargs)
  File "apps/frappe/frappe/database/database.py", line 236, in sql
    self._cursor.execute(query, values)
psycopg2.errors.InFailedSqlTransaction: current transaction is aborted, commands ignored until end of transaction block


Request Data

{
	"type": "POST",
	"args": {
		"report_name": "Purchase Order Trends",
		"filters": "{\"period\":\"Monthly\",\"period_based_on\":\"posting_date\",\"based_on\":\"Item\",\"company\":\"Test Skel\",\"fiscal_year\":\"2024\"}",
		"ignore_prepared_report": 1
	},
	"headers": {},
	"error_handlers": {},
	"url": "/api/method/frappe.desk.query_report.run",
	"request_id": null
}

Response Data

{
	"exception": "",
	"exc_type": "InFailedSqlTransaction",
	"_exc_source": "erpnext (app)",
	"_debug_messages": "[\"Error in query:\\nfunction if(boolean, numeric, unknown) does not exist\\nLINE 1: select t2.item_code, t2.item_name,SUM(IF(t1.transaction_date...\\n                                              ^\\nHINT:  No function matches the given name and argument types. You might need to add explicit type casts.\\n\", \"Error in query:\\ncurrent transaction is aborted, commands ignored until end of transaction block\\n\"]"
}

@Widiskel use version-15 version currently version-16 is beta version so throw error so user

1 Like

solved, the problem is im using postgress, it work normally using mariadb