az postgres flexible-server execute.. no longer works

[cookiecurse]

Describe the bug

az postgres flexible-server execute no longer working due to pip version.
https://learn.microsoft.com/en-us/cli/azure/postgres/flexible-server?view=azure-cli-latest#az-postgres-flexible-server-execute

Azure CLI version 2.58.0

Azure CLI extension: rdbms-connect version 1.0.4
Last updated 2 years ago: https://github.com/Azure/azure-cli-extensions/tree/main/src/rdbms-connect

Last time this command worked successfully was on February 7th 2024 that I know of with azure cli version 2.28.0

Related command

Example:

az postgres flexible-server execute -n -u -p -d postgres -q --output tsv

Errors

ERROR: cli.azure.cli.core.azclierror: An error occurred. Pip failed with status code 2. Use --debug for more information.
ERROR: az_command_data_logger: An error occurred. Pip failed with status code 2. Use --debug for more information.

Issue script & Debug output

DEBUG: cli.knack.cli: Command arguments: ['postgres', 'flexible-server', 'execute', '-n', 'azr-', '-u', '', '-p', '', '-d', '', '-q', 'alter user *** with login;', '--output', 'tsv', '--debug']
DEBUG: cli.knack.cli: init debug log:
Cannot enable color.
DEBUG: cli.knack.cli: Event: Cli.PreExecute []
DEBUG: cli.knack.cli: Event: CommandParser.OnGlobalArgumentsCreate [<function CLILogging.on_global_arguments at 0x01176CF8>, <function OutputProducer.on_global_arguments at 0x01495DE8>, <function CLIQuery.on_global_arguments at 0x014BFBB8>]
DEBUG: cli.knack.cli: Event: CommandInvoker.OnPreCommandTableCreate []
DEBUG: cli.azure.cli.core: Modules found from index for 'postgres': ['azure.cli.command_modules.rdbms']
DEBUG: cli.azure.cli.core: Loading command modules:
DEBUG: cli.azure.cli.core: Name Load Time Groups Commands
DEBUG: cli.azure.cli.core: rdbms 0.486 49 202
DEBUG: cli.azure.cli.core: Total (1) 0.486 49 202
DEBUG: cli.azure.cli.core: Loaded 49 groups, 202 commands.
DEBUG: cli.azure.cli.core: Could not find a match in the command or command group table for 'postgres flexible-server execute'. The index may be outdated.
DEBUG: cli.azure.cli.core: Loading all modules and extensions
#TLDR#
WARNING: cli.azure.cli.core.extension.dynamic_install: The command requires the extension rdbms-connect. It will be installed first.
WARNING: cli.azure.cli.core.extension.operations: Default enabled including preview versions for extension installation now. Disabled in May 2024. Use '--allow-preview true' to enable it specifically if needed. Use '--allow-preview false' to install stable version only.
DEBUG: urllib3.connectionpool: Starting new HTTPS connection (1): aka.ms:443
DEBUG: urllib3.connectionpool: https://aka.ms:443 "GET /azure-cli-extension-index-v1 HTTP/1.1" 301 0
DEBUG: urllib3.connectionpool: Starting new HTTPS connection (1): azcliextensionsync.blob.core.windows.net:443
DEBUG: urllib3.connectionpool: https://azcliextensionsync.blob.core.windows.net:443 "GET /index1/index.json HTTP/1.1" 200 3868308
DEBUG: cli.azure.cli.core.extension._resolve: Candidates ['rdbms_connect-0.1.0-py2.py3-none-any.whl', 'rdbms_connect-0.1.1-py2.py3-none-any.whl', 'rdbms_connect-0.1.2-py2.py3-none-any.whl', 'rdbms_connect-0.1.3-py2.py3-none-any.whl', 'rdbms_connect-0.1.4-py2.py3-none-any.whl', 'rdbms_connect-1.0.0-py2.py3-none-any.whl', 'rdbms_connect-1.0.1-py2.py3-none-any.whl', 'rdbms_connect-1.0.2-py2.py3-none-any.whl', 'rdbms_connect-1.0.3-py2.py3-none-any.whl', 'rdbms_connect-1.0.4-py2.py3-none-any.whl']
DEBUG: cli.azure.cli.core.extension._resolve: Candidates ['rdbms_connect-0.1.0-py2.py3-none-any.whl', 'rdbms_connect-0.1.1-py2.py3-none-any.whl', 'rdbms_connect-0.1.2-py2.py3-none-any.whl', 'rdbms_connect-0.1.3-py2.py3-none-any.whl', 'rdbms_connect-0.1.4-py2.py3-none-any.whl', 'rdbms_connect-1.0.0-py2.py3-none-any.whl', 'rdbms_connect-1.0.1-py2.py3-none-any.whl', 'rdbms_connect-1.0.2-py2.py3-none-any.whl', 'rdbms_connect-1.0.3-py2.py3-none-any.whl', 'rdbms_connect-1.0.4-py2.py3-none-any.whl']
DEBUG: cli.azure.cli.core.extension._resolve: Candidates ['rdbms_connect-0.1.0-py2.py3-none-any.whl', 'rdbms_connect-0.1.1-py2.py3-none-any.whl', 'rdbms_connect-0.1.2-py2.py3-none-any.whl', 'rdbms_connect-0.1.3-py2.py3-none-any.whl', 'rdbms_connect-0.1.4-py2.py3-none-any.whl', 'rdbms_connect-1.0.0-py2.py3-none-any.whl', 'rdbms_connect-1.0.1-py2.py3-none-any.whl', 'rdbms_connect-1.0.2-py2.py3-none-any.whl', 'rdbms_connect-1.0.3-py2.py3-none-any.whl', 'rdbms_connect-1.0.4-py2.py3-none-any.whl']
DEBUG: cli.azure.cli.core.extension._resolve: Candidates ['rdbms_connect-0.1.0-py2.py3-none-any.whl', 'rdbms_connect-0.1.1-py2.py3-none-any.whl', 'rdbms_connect-0.1.2-py2.py3-none-any.whl', 'rdbms_connect-0.1.3-py2.py3-none-any.whl', 'rdbms_connect-0.1.4-py2.py3-none-any.whl', 'rdbms_connect-1.0.0-py2.py3-none-any.whl', 'rdbms_connect-1.0.1-py2.py3-none-any.whl', 'rdbms_connect-1.0.2-py2.py3-none-any.whl', 'rdbms_connect-1.0.3-py2.py3-none-any.whl', 'rdbms_connect-1.0.4-py2.py3-none-any.whl']
DEBUG: cli.azure.cli.core.extension._resolve: Chosen {'downloadUrl': 'https://azcliprod.blob.core.windows.net/cli-extensions/rdbms_connect-1.0.4-py2.py3-none-any.whl', 'filename': 'rdbms_connect-1.0.4-py2.py3-none-any.whl', 'metadata': {'azext.minCliCoreVersion': '2.19.0', 'classifiers': ['Development Status :: 4 - Beta', 'Intended Audience :: Developers', 'Intended Audience :: System Administrators', 'Programming Language :: Python', 'Programming Language :: Python :: 3', 'Programming Language :: Python :: 3.6', 'Programming Language :: Python :: 3.7', 'Programming Language :: Python :: 3.8', 'License :: OSI Approved :: MIT License'], 'extensions': {'python.details': {'contacts': [{'email': '[email protected]', 'name': 'Microsoft Corporation', 'role': 'author'}], 'document_names': {'description': 'DESCRIPTION.rst'}, 'project_urls': {'Home': 'https://github.com/Azure/azure-cli-extensions/tree/main/src/rdbms-connect'}}}, 'extras': [], 'generator': 'bdist_wheel (0.30.0)', 'license': 'MIT', 'metadata_version': '2.0', 'name': 'rdbms-connect', 'run_requires': [{'requires': ['mycli (==1.26.1)', 'pgcli (==3.4.1)', 'psycopg2 (==2.9.3)', 'setproctitle (=1.2.2)']}], 'summary': 'Support for testing connection to Azure Database for MySQL & PostgreSQL servers.', 'version': '1.0.4'}, 'sha256Digest': '8dc15a70881c71f21ba000854960b1be851a057b403dc8de156b73883efb543d'}
DEBUG: cli.azure.cli.core.extension.operations: Extension source is url? True
DEBUG: cli.azure.cli.core.extension.operations: Downloading https://azcliprod.blob.core.windows.net/cli-extensions/rdbms_connect-1.0.4-py2.py3-none-any.whl to C:\Users\TFSPBW2\AppData\Local\Temp\tmpaky9q9xx\rdbms_connect-1.0.4-py2.py3-none-any.whl
DEBUG: urllib3.connectionpool: Starting new HTTPS connection (1): azcliprod.blob.core.windows.net:443
DEBUG: urllib3.connectionpool: https://azcliprod.blob.core.windows.net:443 "GET /cli-extensions/rdbms_connect-1.0.4-py2.py3-none-any.whl HTTP/1.1" 200 12935
DEBUG: cli.azure.cli.core.extension.operations: Downloaded to C:\Users\TFSPBW2\AppData\Local\Temp\tmpaky9q9xx\rdbms_connect-1.0.4-py2.py3-none-any.whl
DEBUG: cli.azure.cli.core.extension.operations: Validating the extension C:\Users\TFSPBW2\AppData\Local\Temp\tmpaky9q9xx\rdbms_connect-1.0.4-py2.py3-none-any.whl
DEBUG: cli.azure.cli.core.extension.operations: Checksum of C:\Users\TFSPBW2\AppData\Local\Temp\tmpaky9q9xx\rdbms_connect-1.0.4-py2.py3-none-any.whl is OK
DEBUG: cli.azure.cli.core.extension.operations: Validation successful on C:\Users\TFSPBW2\AppData\Local\Temp\tmpaky9q9xx\rdbms_connect-1.0.4-py2.py3-none-any.whl
DEBUG: cli.azure.cli.core.extension.operations: Executing pip with args: ['install', '--target', 'F:\DA_Def3\_work\_temp\.azclitask\cliextensions\rdbms-connect', 'C:\Users\TFSPBW2\AppData\Local\Temp\tmpaky9q9xx\rdbms_connect-1.0.4-py2.py3-none-any.whl']
DEBUG: cli.azure.cli.core.extension.operations: Running: ['C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\python.exe', '-m', 'pip', 'install', '--target', 'F:\DA_Def3\_work\_temp\.azclitask\cliextensions\rdbms-connect', 'C:\Users\TFSPBW2\AppData\Local\Temp\tmpaky9q9xx\rdbms_connect-1.0.4-py2.py3-none-any.whl', '-vv', '--disable-pip-version-check', '--no-cache-dir']
DEBUG: cli.azure.cli.core.extension.operations: Using pip 24.0 from C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\Lib\site-packages\pip (python 3.11)

File "D:\a_work\1\s\build_scripts\windows\artifacts\cli\Lib\site-packages\azure/cli/core/parser.py", line 281, in parse_known_args
File "argparse.py", line 1902, in parse_known_args
File "argparse.py", line 2096, in _parse_known_args
File "argparse.py", line 2073, in consume_positionals
File "argparse.py", line 1978, in take_action
File "argparse.py", line 1241, in call
File "D:\a_work\1\s\build_scripts\windows\artifacts\cli\Lib\site-packages\azure/cli/core/parser.py", line 281, in parse_known_args
File "argparse.py", line 1902, in parse_known_args
File "argparse.py", line 2096, in _parse_known_args
File "argparse.py", line 2073, in consume_positionals
File "argparse.py", line 1978, in take_action
File "argparse.py", line 1241, in call
File "D:\a_work\1\s\build_scripts\windows\artifacts\cli\Lib\site-packages\azure/cli/core/parser.py", line 281, in parse_known_args
File "argparse.py", line 1902, in parse_known_args
File "argparse.py", line 2096, in _parse_known_args
File "argparse.py", line 2073, in consume_positionals
File "argparse.py", line 1962, in take_action
File "D:\a_work\1\s\build_scripts\windows\artifacts\cli\Lib\site-packages\azure/cli/core/parser.py", line 272, in _get_values
File "argparse.py", line 2506, in _get_values
File "D:\a_work\1\s\build_scripts\windows\artifacts\cli\Lib\site-packages\azure/cli/core/parser.py", line 300, in _check_value
File "D:\a_work\1\s\build_scripts\windows\artifacts\cli\Lib\site-packages\azure/cli/core/extension/dynamic_install.py", line 119, in try_install_extension
File "D:\a_work\1\s\build_scripts\windows\artifacts\cli\Lib\site-packages\azure/cli/core/extension/dynamic_install.py", line 227, in _check_value_in_extensions
File "D:\a_work\1\s\build_scripts\windows\artifacts\cli\Lib\site-packages\azure/cli/core/extension/operations.py", line 349, in add_extension
File "D:\a_work\1\s\build_scripts\windows\artifacts\cli\Lib\site-packages\azure/cli/core/extension/operations.py", line 172, in _add_whl_ext
knack.util.CLIError: An error occurred. Pip failed with status code 2. Use --debug for more information.

ERROR: cli.azure.cli.core.azclierror: An error occurred. Pip failed with status code 2. Use --debug for more information.
ERROR: az_command_data_logger: An error occurred. Pip failed with status code 2. Use --debug for more information.
DEBUG: cli.knack.cli: Event: Cli.PostExecute [<function AzCliLogging.deinit_cmd_metadata_logging at 0x02316C08>]
INFO: az_command_data_logger: exit code: 1
INFO: cli.main: Command ran in 11.972 seconds (init: 0.436, invoke: 11.536)
INFO: telemetry.main: Begin splitting cli events and extra events, total events: 1
INFO: telemetry.client: Accumulated 0 events. Flush the clients.
INFO: telemetry.main: Finish splitting cli events and extra events, cli events: 1
INFO: telemetry.save: Save telemetry record of length 3845 in cache
INFO: telemetry.main: Begin creating telemetry upload process.
INFO: telemetry.process: Creating upload process: "C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\python.exe C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\Lib\site-packages\azure\cli\telemetry_init_.pyc F:\DA_Def1_work_temp.azclitask"
INFO: telemetry.process: Return from creating process
INFO: telemetry.main: Finish creating telemetry upload process.

Expected behavior

az postgres flex exectute command runs without errors.

Environment Summary

azure-cli 2.58.0

core 2.58.0
telemetry 1.1.0

Extensions:
azure-devops 1.0.0

Dependencies:
msal 1.26.0
azure-mgmt-resource 23.1.0b2

Python location 'C:\Program Files\Microsoft SDKs\Azure\CLI2\python.exe'
Extensions directory 'C:\Program Files\Common Files\AzureCliExtensionDirectory'

Python (Windows) 3.11.7 (tags/v3.11.7:fa7a6f2, Dec 4 2023, 19:24:49) [MSC v.1937 64 bit (AMD64)]

Additional context

I have tried downgrading az CLI version to last working (difficult with self-hosted agent without permissions to do that), upgrading Python version, enabling preview on extensions and doublechecking that they are up to date, changing Build Agents even to Microsoft-hosted.

[yonzhan]

Thank you for opening this issue, we will look into it.

[ManjulaChintharla]

Same issue .

[p6lidia]

@sunilagarwal @sr-msft @niklarin Do you by chance have any updates when this might be fixed? We cannot use az postgres commands at all and are forced to look at alternatives at this point.

[MDendura]

I've been having similar issues. Seems related to Azure/azure-cli#27896 and Azure/azure-cli#24494

[IamSiDean]

Everything works fine in a pipeline using an ubuntu-latest agent, but I can't get az postgres flexible-server commands to work in windows-latest at all because of the rdbms-connect issue mentioned above. Are the az postgres flexible-server commands not available at all in Windows now?

[IamSiDean]

Thank you for opening this issue, we will look into it.

@yonzhan Can you tell us if anyone is looking into this at the moment please?

[ViktorDimitrievski]

Same issue here. I can not use

az login --service-principal -u $servicePrincipalId -p $servicePrincipalKey --tenant $tenantId

az postgres flexible-server execute --name "$(dbcServer)" `
    --admin-user "$(dbcLogin)" --admin-password "$(dbcPassword)" `
    --database-name "$(dbcDatabase)" --file-path "$sqlScriptPath" `
    --extension-name rdbms-connect `
    --query-exit-on-error `
    --query-exit-on-stderr `
    --azcli-arg "--no-cache-dir --force-reinstall psycopg2-binary==2.9.1"

or

az postgres flexible-server execute --name "$(dbcServer)" `
    --admin-user "$(dbcLogin)" --admin-password "$(dbcPassword)" `
    --database-name "$(dbcDatabase)" --file-path "$sqlScriptPath"

What can we find as an alternative for this issue?

[cookiecurse]

@sunilagarwal, @sr-msft, @niklarin could someone please take a look at this issue?

[gbowerman]

Hi @cookiecurse , apologies for the delay. The people you referenced no longer work on PostgreSQL flexible server.

I just tried "az postgres flexible-server execute" in Azure Cloud shell and it successfully executed a SQL command against my PostgreSQL database.

azure-cli 2.65.0 *

Extensions:
ai-examples 0.2.5
ml 2.30.1
rdbms-connect 1.0.7
ssh 2.0.5

Dependencies:
msal 1.31.0
azure-mgmt-resource 23.1.1

Python (Linux) 3.9.19 (main, Aug 23 2024, 00:07:48)
[GCC 11.2.0]

Can you still repro this with the latest version of Azure CLI? If so, please can you provide the latest environment details, and we'll see if we can recreate it. @jasomaning FYI

[cookiecurse]

Hey @gbowerman thank you so much for testing and looking into this!

Trying this command as a simple select statement to troubleshoot:

az postgres flexible-server execute -n $server -u $username -p $pass -d $db -q "SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT LIKE 'pg_%' AND schema_name <> 'information_schema'" --debug

Some info:
This is a postgresql flex server which is VNET integrated with private DNS integration so we have to use a Self-hosted build agent to communicate with it.
Using a Windows self-hosted agent.
azure-cli 2.65.0 *
Python (Windows) 3.11.8 (tags/v3.11.8:db85d51, Feb 6 2***24, 21:52:***7) [MSC v.1937 32 bit (Intel)]

rdbms-connect is not pre-installed on the agent. When not specifying or pre-installing rdbms it will detect that it is not installed and try to install it. While trying to install it, eventually the script will error out on this library:

Successfully installed setuptools-75.6.***
Removed build tracker: 'C:\\Users\\tfspbwascorp\\AppData\\Local\\Temp\\pip-build-tracker-5wkngau_'
Installing build dependencies: finished with status 'done'
Getting requirements to build wheel: started
Running command Getting requirements to build wheel
Getting requirements to build wheel: finished with status 'done'
ERROR: Exception:
Traceback (most recent call last):
File "C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\Lib\site-packages\pip\_internal\cli\base_command.py", line 1***5, in _run_wrapper
status = _inner_run()

...TLDR more errors

pip._vendor.pyproject_hooks._impl.BackendUnavailable: Traceback (most recent call last):
  File "C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\Lib\site-packages\pip\_vendor\pyproject_hooks\_in_process\_in_process.py", line 77, in _build_backend
    obj = import_module(mod_path)
          ^^^^^^^^^^^^^^^^^^^^^^^
  File "importlib\__init__.py", line 126, in import_module
  File "<frozen importlib._bootstrap>", line 12***4, in _gcd_import
  File "<frozen importlib._bootstrap>", line 1176, in _find_and_load
  File "<frozen importlib._bootstrap>", line 1126, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
  File "<frozen importlib._bootstrap>", line 12***4, in _gcd_import
  File "<frozen importlib._bootstrap>", line 1176, in _find_and_load
  File "<frozen importlib._bootstrap>", line 1147, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 69***, in _load_unlocked
  File "<frozen importlib._bootstrap_external>", line 94***, in exec_module
  File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
  File "C:\Users\tfspbwascorp\AppData\Local\Temp\pip-build-env-nze1ukg_\overlay\Lib\site-packages\setuptools\__init__.py", line 27, in <module>
    from .dist import Distribution
  File "C:\Users\tfspbwascorp\AppData\Local\Temp\pip-build-env-nze1ukg_\overlay\Lib\site-packages\setuptools\dist.py", line 19, in <module>
    from . import (
  File "C:\Users\tfspbwascorp\AppData\Local\Temp\pip-build-env-nze1ukg_\overlay\Lib\site-packages\setuptools\_entry_points.py", line 6, in <module>
    from jaraco.text import yield_lines
  File "C:\Users\tfspbwascorp\AppData\Local\Temp\pip-build-env-nze1ukg_\overlay\Lib\site-packages\setuptools\_vendor\jaraco\text\__init__.py", line 12, in <module>
    from jaraco.context import ExceptionTrap
  File "C:\Users\tfspbwascorp\AppData\Local\Temp\pip-build-env-nze1ukg_\overlay\Lib\site-packages\setuptools\_vendor\jaraco\context.py", line 11, in <module>
  import urllib.request
  File "urllib\request.py", line 88, in <module>
  File "http\client.py", line 71, in <module>
  File "email\parser.py", line 12, in <module>
  File "email\feedparser.py", line 27, in <module>
  File "email\_policybase.py", line 9, in <module>
  File "email\utils.py", line 29, in <module>
  File "socket.py", line 51, in <module>
ModuleNotFoundError: No module named '_socket'

DEBUG: cli.azure.cli.core.extension.operations: Command '['C:\\Program Files (x86)\\Microsoft SDKs\\Azure\\CLI2\\python.exe', '-m', 'pip', 'install', '--target', 'F:\\DA_Def1\\_work\\_temp\\.azclitask\\cliextensions\\rdbms-connect', 'C:\\Users\\TFSPBW~2\\AppData\\Local\\Temp\\tmpy3sdv4t9\\rdbms_connect-1.***.7-py2.py3-none-any.whl', '-vv', '--disable-pip-version-check', '--no-cache-dir']' returned non-zero exit status 2.
DEBUG: cli.azure.cli.core.extension.operations: Pip failed so deleting anything we might have installed at F:\DA_Def1\_work\_temp\.azclitask\cliextensions\rdbms-connect
DEBUG: cli.azure.cli.core.azclierror: Traceback (most recent call last):
  File "D:\a\_work\1\s\build_scripts\windows\artifacts\cli\Lib\site-packages\knack/cli.py", line 233, in invoke
  File "D:\a\_work\1\s\build_scripts\windows\artifacts\cli\Lib\site-packages\azure/cli/core/commands/__init__.py", line 591, in execute
  File "D:\a\_work\1\s\build_scripts\windows\artifacts\cli\Lib\site-packages\knack/parser.py", line 261, in parse_args
  File "argparse.py", line 1869, in parse_args
  File "D:\a\_work\1\s\build_scripts\windows\artifacts\cli\Lib\site-packages\azure/cli/core/parser.py", line 281, in parse_known_args
  File "argparse.py", line 19***2, in parse_known_args
  File "argparse.py", line 2***96, in _parse_known_args
  File "argparse.py", line 2***73, in consume_positionals
  File "argparse.py", line 1978, in take_action
  File "argparse.py", line 1241, in __call__
  File "D:\a\_work\1\s\build_scripts\windows\artifacts\cli\Lib\site-packages\azure/cli/core/parser.py", line 281, in parse_known_args
  File "argparse.py", line 19***2, in parse_known_args
  File "argparse.py", line 2***96, in _parse_known_args
  File "argparse.py", line 2***73, in consume_positionals
  File "argparse.py", line 1978, in take_action
  File "argparse.py", line 1241, in __call__
  File "D:\a\_work\1\s\build_scripts\windows\artifacts\cli\Lib\site-packages\azure/cli/core/parser.py", line 281, in parse_known_args
  File "argparse.py", line 19***2, in parse_known_args
  File "argparse.py", line 2***96, in _parse_known_args
  File "argparse.py", line 2***73, in consume_positionals
  File "argparse.py", line 1962, in take_action
  File "D:\a\_work\1\s\build_scripts\windows\artifacts\cli\Lib\site-packages\azure/cli/core/parser.py", line 272, in _get_values
  File "argparse.py", line 25***6, in _get_values
  File "D:\a\_work\1\s\build_scripts\windows\artifacts\cli\Lib\site-packages\azure/cli/core/parser.py", line 3***, in _check_value
  File "D:\a\_work\1\s\build_scripts\windows\artifacts\cli\Lib\site-packages\azure/cli/core/extension/dynamic_install.py", line 132, in try_install_extension
  File "D:\a\_work\1\s\build_scripts\windows\artifacts\cli\Lib\site-packages\azure/cli/core/extension/dynamic_install.py", line 242, in _check_value_in_extensions
  File "D:\a\_work\1\s\build_scripts\windows\artifacts\cli\Lib\site-packages\azure/cli/core/extension/operations.py", line 349, in add_extension
  File "D:\a\_work\1\s\build_scripts\windows\artifacts\cli\Lib\site-packages\azure/cli/core/extension/operations.py", line 172, in _add_whl_ext
knack.util.CLIError: An error occurred. Pip failed with status code 2. Use --debug for more information.

I have tried installing rdbms-connect ahead of the psql execute as well using:
az extension add --name rdbms-connect --version 1.0.7 --yes

that had resulted in:
ERROR: An error occurred. Pip failed with status code 2. Use --debug for more information.

With debug on, the error is identical as one above.

Hope that helps for troubleshooting, thank you again for looking at it!

[gbowerman]

@cookiecurse thanks for testing this again with the latest CLI version and for providing the detailed output. Our engineering team is investigating and consulting with the CLI core team. We'll keep you updated.

[nachoalonsoportillo]

@cookiecurse would you, please, try the solution described here and get back to us with the results?

[cookiecurse]

What is interesting is that it ran the command, provided the output but still failed. Someone above said that they tried it on a windows-latest agent with same result. Have you been able to replicate the issue on your end?

This is what I ran before the normal postgrsql command:

python --version
echo "-------------------------------------------------"
echo $PYTHONPATH
echo "-------------------------------------------------"
export PYTHONPATH="/usr/local/python3.8.11/lib/python3.8/site-packages/:/usr/local/python3.8.11/:/usr/local/python3.8.11/lib64/python3.8/lib-dynload/"

Then the postgrsql command with TLDR debug output:

postgrsql execute
WARNING: Preview version of extension is enabled by default for extension installation now. Will be disabled in future release. 
WARNING: Please run 'az config set extension.dynamic_install_allow_preview=true or false' to config it specifically. 
WARNING: The command requires the extension rdbms-connect. It will be installed first.
WARNING: This extension depends on gcc, postgresql-devel, python3-devel and they will be installed first if not exist.
Updating Subscription Management repositories.
Repository packages-microsoft-com-prod is listed more than once in the configuration
Red Hat CodeReady Linux Builder for RHEL 9 x86_  85 kB/s | 4.5 kB     ***:***    
Red Hat Satellite Client 6 for RHEL 9 x86_64 (R  78 kB/s | 3.8 kB     ***:***    
Red Hat Enterprise Linux 9 for x86_64 - AppStre  86 kB/s | 4.5 kB     ***:***    
Red Hat Enterprise Linux 9 for x86_64 - Supplem  72 kB/s | 3.7 kB     ***:***    
Red Hat Enterprise Linux 9 for x86_64 - BaseOS   89 kB/s | 4.1 kB     ***:***    
Package gcc-11.4.1-3.el9.x86_64 is already installed.
Package libpq-devel-13.11-1.el9.x86_64 is already installed.
Package python3-devel-3.9.18-3.el9.x86_64 is already installed.
Dependencies resolved.
Nothing to do.
Complete!
WARNING: Successfully connected to azr-test-pgflxsvr.
WARNING: Ran Database Query: 'SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT LIKE 'pg_%' AND schema_name <> 'information_schema''
WARNING: Retrieving first 3*** rows of query output, if applicable.
WARNING: Closed the connection to azr-test-pgflxsvr
[
  {
    "schema_name": "public"
  },
  {
    "schema_name": "test"
  }
]

##[error]WARNING: You have 2 update(s) available. Consider updating your CLI installation with 'az upgrade'
WARNING: Command group 'config' is experimental and under development. Reference and support levels: https://aka.ms/CLI_refstatus
WARNING: The default value of '--allow-preview' will be changed to 'false' from 'true' in next breaking change release(2.67.***).
WARNING: The installed extension 'interactive' is in preview.
DEBUG: cli.knack.cli: Command arguments: ['postgres', 'flexible-server', 'execute', '-n', 'azr-test-pgflxsvr', '-u', '***', '-p', '***', '-d', '***', '-q', "SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT LIKE 'pg_%' AND schema_name <> 'information_schema'", '--debug']
DEBUG: cli.knack.cli: __init__ debug log:
Cannot enable color.
DEBUG: cli.knack.cli: Event: Cli.PreExecute []
DEBUG: cli.knack.cli: Event: CommandParser.OnGlobalArgumentsCreate [<function CLILogging.on_global_arguments at ***x7fb8d41f37***>, <function OutputProducer.on_global_arguments at ***x7fb8d3fef16***>, <function CLIQuery.on_global_arguments at ***x7fb8d4***979***>]
DEBUG: cli.knack.cli: Event: CommandInvoker.OnPreCommandTableCreate []
2024-12-03T15:18:36.8293896Z ##[error]Script has output to stderr. Failing as failOnStdErr is set to true.
2024-12-03T15:18:36.8296309Z [command]/root/.pyenv/shims/az account clear

[nasc17]

Hello @cookiecurse,

Could you please confirm that you are seeing failure using az postgres flexible-server execute command and workaround with latest cli version?
Your debug response mentions 2 updates for CLI being available, we are currently at 2.67.0

[cookiecurse]

@nasc17 yes I can confirm that the failure occurs when using the az postgres flexible-server execute -n $server -u $username -p $pass -d $db -q "SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT LIKE 'pg_%' AND schema_name <> 'information_schema'" command.

The azure-cli is on version 2.66.0, I am unable to update the version on the agent at this time, but can work with my team to do so. Do you think that updating it will resolve the issue?

[cookiecurse]

@IamSiDean are you still having issues running the az postgres flexible-server execute commands on windows-latest agents?

[IamSiDean]

@IamSiDean are you still having issues running the az postgres flexible-server execute commands on windows-latest agents?

Hi, sorry I got fed up with waiting for a fix so I wrote my own Python code to do the same thing.
I believe it was Python library conflicts with the rdbms code, but only for Windows agents, I've not used the azure cli postgres stuff for months, just my own code.

[nasc17]

@cookiecurse in the TLDR debug output when you tried the workaround, the 2 updates available warning appears to be taken as an error: ##[error]WARNING: You have 2 update(s) available. Consider updating your CLI installation with 'az upgrade'

If using version 2.66.0 and still getting error on update warning, is failOnStdErr being set as true? This might have it take warnings as errors.

[cookiecurse]

@nasc17 i will try that but that feels more like a workaround than an actual solution?