global current_setting does not return value

Question

Engine: asyncpg Postgres version: 14

Hi! I need to set global variables when I am creating pool. I use set_config in init function for each connection:

async def init(con):
    configs = [
        f"SELECT set_config('element_type.boolean', '{ElementsTypes.Boolean.value}', False)",
        f"SELECT set_config('entity_types.element', '{EntityTypes.Elements.value}', False)",
        ...
    ]

    for config in configs:
        await con.execute(config)

db_pool = await asyncpg.create_pool(
   database=os.getenv("POSTGRES_DBNAME" if not is_test_db else "POSTGRES_DBNAME_TEST"),
   user=os.getenv("POSTGRES_USER"),
   password=os.getenv("POSTGRES_PASSWORD"),
   host=host,
   init=init,
   loop=loop
)

And when I try to get some variable inside procedure or query, I get empty string:

 RAISE LOG 'ELEMENT TYPE BOOLEAN %',  current_setting('element_type.boolean');
 -- OUTPUT: 'ELEMENT TYPE BOOLEAN ' 

 RAISE LOG 'ELEMENT TYPE VAR TYPE %', pg_typeof(current_setting('element_type.boolean'));
-- OUTPUT: 'ELEMENT TYPE BOOLEAN VAR TYPE text'

UPD: I tried to set and get variable in python console and I get empty string again

>>>loop.run_until_complete(pool.fetchval("SELECT set_config('myapp.group_types_element', '1', False)"))
'1'
>>>loop.run_until_complete(pool.fetchval("SELECT current_setting('myapp.group_types_element')"))
''

No, I do not think so. I use python enums and I'm sure the value is not empty string. In general it is int-enum. `ElementsTypes.Boolean.value = 1` — Prosto_Oleg, Jun 22 '22 at 20:35

score 0 · Answer 1 · answered Jun 23 '22 at 07:26

Okay, I do not know what is it and why it works that way, but I found solution(ugly):

I need to ALTER DATABASE first, then I need to reset value in config by SET TO

I do it in this way:

 configs = [
        {"name": "myapp.element_type_entry", "value": ElementsTypes.Entry.value},
        {"name": "myapp.element_type_option", "value": ElementsTypes.Option.value},
        {"name": "myapp.entity_types_element", "value": EntityTypes.Elements.value},
        {"name": "myapp.group_types_element", "value": GroupTypes.Element.value},
        {"name": "myapp.entity_types_entry", "value": EntityTypes.Entries.value},
        {"name": "myapp.group_types_entry", "value": GroupTypes.Entry.value},
        {"name": "myapp.element_type_boolean", "value": ElementsTypes.Boolean.value},
        {"name": "myapp.element_type_geo", "value": ElementsTypes.Geo.value},
        {"name": "myapp.element_type_divider", "value": ElementsTypes.Divider.value},
        {"name": "myapp.entity_types_entry_type", "value": EntityTypes.Types.value},
        {"name": "myapp.group_types_entry_type", "value": GroupTypes.Type.value},
        {"name": "myapp.element_type_file", "value": ElementsTypes.File.value},
        {"name": "myapp.entity_types_file", "value": EntityTypes.Files.value},
        {"name": "myapp.group_types_file", "value": GroupTypes.File.value},
        {"name": "myapp.entity_types_group", "value": EntityTypes.Groups.value}
    ]

    for config in configs:
        await conn.execute(text(f"ALTER DATABASE {os.getenv('POSTGRES_DBNAME')} SET {config['name']}={config['value']};"))
        await conn.execute(text(f"SET {config['name']} TO {config['value']};"))

global current_setting does not return value

1 Answers1