Скачать или смотреть Why DefineCustomBoolVariable is Re-Executed After ereport(ERROR, ...) in PostgreSQL Extensions

Understand why PostgreSQL re-executes your custom GUC variable definition when an error is thrown and how to prevent multiple executions in your extension initialization.
---
This video is based on the question https://stackoverflow.com/q/79474605/ asked by the user '기현성' ( https://stackoverflow.com/u/29735298/ ) and on the answer https://stackoverflow.com/a/79474648/ provided by the user 'Laurenz Albe' ( https://stackoverflow.com/u/6464308/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Why does DefineCustomBoolVariable get re-executed when ereport(ERROR, ...) is thrown?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to drop me a comment under this video.
---
The Problem: Re-Execution of DefineCustomBoolVariable on Error

When defining a custom GUC (Grand Unified Configuration) boolean variable in a PostgreSQL extension using DefineCustomBoolVariable, you may notice that if you call ereport(ERROR, ...) during your _PG_init function, your variable definition gets executed again on subsequent extension loads.

For example:

[[See Video to Reveal this Text or Code Snippet]]

When the error condition triggers, the next time you try to load the extension, PostgreSQL complains about redefining the parameter.

Why Does This Happen?

PostgreSQL executes your _PG_init function every time the extension is loaded.

When ereport(ERROR, ...) is called, PostgreSQL aborts and rolls back the current transaction, undoing all database changes.

However, the call to DefineCustomBoolVariable is not transactional.

If your function partially completes (defines the variable) before the error, the parameter remains registered globally, but the initialization function is retried again on the next load.

On retry, DefineCustomBoolVariable sees the parameter is already defined and throws the "attempt to redefine parameter" error.

In simple terms, PostgreSQL does not roll back registration of your GUC variables when you trigger an error, leading to this tricky behavior.

How to Prevent Multiple Executions and Errors

1. Perform Validation Before Defining the Variable

Instead of defining your GUC variable then checking conditions that might trigger an error, validate early.

If the invalid condition is met, call ereport(ERROR, ...) before the call to DefineCustomBoolVariable.

Example:

[[See Video to Reveal this Text or Code Snippet]]

2. Check if the GUC Variable Is Already Defined Using find_option()

Use the PostgreSQL internal function find_option() (declared in utils/guc_tables.h) to detect if your GUC variable is already registered.

Only call DefineCustomBoolVariable() if it has not been defined yet.

Example:

[[See Video to Reveal this Text or Code Snippet]]

This avoids the redefinition error after a failed initialization.

Summary

DefineCustomBoolVariable() is not transactional and persists through errors.

When an error occurs during extension load, PostgreSQL retries _PG_init, causing redefinition attempts.

Mitigate this by checking error conditions before defining GUC variables or by checking for existing parameters with find_option().

By carefully ordering your initialization and making your GUC registration idempotent, you can safely handle errors without causing redefinition conflicts.