Hi, Dnia czwartek, 11 maja 2023 10:10:50 CEST Rodolfo Alonso Hernandez pisze:

Hi Julia:

Let me open a LP bug for this issue. I'll check if this call is being tested in our CI and with the specific DB backend.

Regards.

On Wed, May 10, 2023 at 7:32 PM Julia Kreger <juliaashleykreger@gmail.com> wrote:

...

Greetings folks,

I have spent a substantial amount of time investigating CI issues as of recent, and I noticed that at some point the legacy "ironic-tempest-pxe_ipmitool-postgres" CI job which has been kept around as a non-voting postgres support canary, is now failing on the master branch.

Specifically, looking through the logs[0], it appears that structurally the queries are no longer compatible.

np0033933847 neutron-server[90677]: WARNING oslo_db.sqlalchemy.exc_filters [None req-155d0299-07ef-433a-8576-467127c82be4 None None] DBAPIError exception wrapped.: psycopg2.errors.GroupingError: column "subnetpools.address_scope_id" must appear in the GROUP BY clause or be used in an aggregate function May 03 18:41:57.893335 np0033933847 neutron-server[90677]: LINE 2: ...standard_attr_id AS floatingips_standard_attr_id, subnetpool... May 03 18:41:57.893335 np0033933847 neutron-server[90677]: ^ May 03 18:41:57.893335 np0033933847 neutron-server[90677]: ERROR oslo_db.sqlalchemy.exc_filters Traceback (most recent call last): May 03 18:41:57.893335 np0033933847 neutron-server[90677]: ERROR oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context May 03 18:41:57.893335 np0033933847 neutron-server[90677]: ERROR oslo_db.sqlalchemy.exc_filters self.dialect.do_execute( May 03 18:41:57.893335 np0033933847 neutron-server[90677]: ERROR oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/default.py", line 736, in do_execute May 03 18:41:57.893335 np0033933847 neutron-server[90677]: ERROR oslo_db.sqlalchemy.exc_filters cursor.execute(statement, parameters) May 03 18:41:57.893335 np0033933847 neutron-server[90677]: ERROR oslo_db.sqlalchemy.exc_filters psycopg2.errors.GroupingError: column "subnetpools.address_scope_id" must appear in the GROUP BY clause or be used in an aggregate function May 03 18:41:57.893335 np0033933847 neutron-server[90677]: ERROR oslo_db.sqlalchemy.exc_filters LINE 2: ...standard_attr_id AS floatingips_standard_attr_id, subnetpool... May 03 18:41:57.893335 np0033933847 neutron-server[90677]: ERROR oslo_db.sqlalchemy.exc_filters ^

From Ironic's point of view, we're wondering if this is expected? Is it likely to be fixed? If there are no plans to fix the DB queries, Ironic will have no choice but to drop the CI job.

Thanks in advance!

-Julia

[0]: https://storage.bhs.cloud.ovh.net/v1/AUTH_dcaab5e32b234d56b626f72581e3644c/z...

We have only one postgres related job in our periodic queue neutron-ovn-tempest-postgres-full and it seems to be pretty stable [1]. I don't know however if it somehow skips this path but I don't see any errors like that in the neutron's log file. [1] https://zuul.openstack.org/builds?job_name=neutron-ovn-tempest-postgres-full&project=openstack/neutron -- Slawek Kaplonski Principal Software Engineer Red Hat

Awesome! Thanks for the prompt reply and action on a patch for Neutron. The test patch proposed against Ironic passed, which is definitely a good sign! Thanks again! -Julia On Thu, May 11, 2023 at 2:41 AM Rodolfo Alonso Hernandez < ralonsoh@redhat.com> wrote:

LP bug: https://bugs.launchpad.net/neutron/+bug/2019186 Patch: https://review.opendev.org/c/openstack/neutron/+/882935 Ironic patch: https://review.opendev.org/c/openstack/ironic/+/882936

Hey Christian, You're 100% correct to use resolutions from the TC and governance policies to help you determine what is officially supported for an integrated OpenStack release. However, these guidelines, from a project perspective, are minimums. Many projects; Ironic included, run jobs to ensure postgresql support continues working. This is absolutely best-effort, and I believe is mostly grown out of trying to continue to support operators who made the choice to use postgresql. With my Ironic PTL hat on, I appreciate that we explicitly ensure we're not limiting our project to a single DBMS forever. We test Ironic against the following DBMS: mysql/mariadb (used by most users), postgresql, and sqlite (used by metal3, primarily). In the case of postgresql (as mentioned in this thread), we test more than Ironic, we ensure a full deployment is possible which tests the basic functionality of Ironic, Neutron, Nova, and many other projects. This doesn't mean postgresql or sqlite is supported from the perspective of an integrated OpenStack install, but it's there and available to use for deployers who might choose to use Ironic standalone (metal3/bifrost) or for operators with clouds that predate OpenStack's statement of non-support for postgres. I apologize if this appears inconsistent from the outside, but we want to continue to serve as many use cases as possible -- sometimes that means going above and beyond the minimum supported platforms. Thanks, Jay Faulkner Ironic PTL TC Vice-Chair On Thu, May 11, 2023 at 12:02 PM Christian Rohmann < christian.rohmann@inovex.de> wrote:

On 11/05/2023 15:54, Julia Kreger wrote:

...

Awesome! Thanks for the prompt reply and action on a patch for Neutron. The test patch proposed against Ironic passed, which is definitely a good sign!

While it's really cool to see how quickly this PostgreSQL issue was fixed for Ironic, I believe there is no widespread testing and validation of PostgreSQL for other projects (anymore).

There even was an official communication from the TC in 2017 about not "supporting" anything but MySQL / MariaDB:

https://governance.openstack.org/tc/resolutions/20170613-postgresql-status.h...

I myself ran into multiple issues, some during schema upgrades and some only at runtime because certain side-effect such as improper booleans or "free solo SQL" where used within the code, rendering the ORM abstraction of the actually used DBMS useless. This is also making it very hard to fully validate the DBMS is interchangeable without particularly testing for those kind of cases in all sorts of database fields or by doing code reviews.

What I am saying is, that as a an operator I appreciate clear communication and clear deprecation processes over "best-effort" commitments. It does not help if there is a minefield of issues and raised bugs about incompatibilities are not considered really important since the user-base for PostgreSQL installs has diminished.

If I use a set of services just fine, but as soon as I add another OS project to my cloud I could run into all sorts of problems, just because I chose the "wrong" DBMS.

Regards

Christian