MIME-Version: 1.0
References: <CAFCRh-8NwJd0jq6P=R3qhHyqU7hw0BTor3W0SvUcii24et+zAw@mail.gmail.com>
 <CAFCRh-93hsRvoJqQCwXq06=AJJJ_s783gVsGHKc-QjgOO6jGVg@mail.gmail.com> <77a63548783dd4007ee479a4c5ed300629aaa776.camel@cybertec.at>
In-Reply-To: <77a63548783dd4007ee479a4c5ed300629aaa776.camel@cybertec.at>
From: Dominique Devienne <ddevienne@gmail.com>
Date: Fri, 12 Sep 2025 15:11:10 +0200
Message-ID: <CAFCRh--DPWEHpRmdr-Jfw4guYqjc9TM_QyVH7SR4B=Le5gGe3Q@mail.gmail.com>
Subject: Re: Latest patches break one of our unit-test, related to RLS
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-general@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAFCRh--DPWEHpRmdr-Jfw4guYqjc9TM_QyVH7SR4B%3DLe5gGe3Q%40mail.gmail.com>
Precedence: bulk

On Fri, Sep 12, 2025 at 2:45=E2=80=AFPM Laurenz Albe <laurenz.albe@cybertec=
.at> wrote:
> On Fri, 2025-09-12 at 14:22 +0200, Dominique Devienne wrote:
> > On Thu, Sep 4, 2025 at 5:03=E2=80=AFPM Dominique Devienne <ddevienne@gm=
ail.com> wrote:
> > > OK with 16.9 and 17.5 (we cannot test on beta2 anymore)
> > > KO with 16.10 and 17.6 (and beta3 too, released at the same time)
> >
> > I've tracked down the regression to this particular query, FWIW:
> >
> > select rolname, rolsuper, rolinherit, rolcreaterole,
> >        rolcreatedb, rolcanlogin, rolreplication, rolbypassrls,
> >        oid, shobj_description(oid, 'pg_authid')
> >   from pg_roles
> >  where rolname SIMILAR TO $1 AND pg_has_role(oid, 'SET')
> >  order by rolname
> >
> > In 17.5, returns 3 rows.
> > In 17.6, returns 0 rows.
>
> That must be commit e3ffc3e91d.
>
> That commit fixed a bug in the conversion from SIMILAR TO
> expressions to POSIX regular expressions.
>
> You don't show us that data that match the pattern in 17.5, but
> not in 17.6.  Unless you show us a counterexample, I'd say that
> the behavior in 17.6 is correct.
>
> Minor releases shouldn't change the behavior EXCEPT when the
> behavior is buggy.

Can't get any simpler than the repro below, can it?
So is this buggy or not?
Clearly, there's a change in behavior.
I tend to call a change in behavior as a regression myself :)
But if someone can explain to me how what used to work was incorrect,
compared to the documented behavior,
I'm willing to change it of course. Any takers? --DD

postgres=3D# show server_version;
 server_version
----------------
 16.9
(1 row)


postgres=3D# with t(v) as (values ('foo:bar'), ('foo/bar'), ('foo0bar'))
select v from t where v similar to 'foo[\d\w]_%';
    v
---------
 foo0bar
(1 row)


postgres=3D# \c - - - 5416;
psql (17.6, server 16.10)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
compression: off, ALPN: none)
You are now connected to database "postgres" as user "postgres" on
host "sr-pau-db" (address "10.65.53.13") at port "5416".
postgres=3D# show server_version;
 server_version
----------------
 16.10
(1 row)


postgres=3D# with t(v) as (values ('foo:bar'), ('foo/bar'), ('foo0bar'))
select v from t where v similar to 'foo[\d\w]_%';
 v
---
(0 rows)


postgres=3D# \c - - - 5475
psql (17.6, server 17.5)
You are now connected to database "postgres" as user "postgres" on
host "sr-pau-db" (address "10.65.53.13") at port "5475".
postgres=3D# show server_version;
 server_version
----------------
 17.5
(1 row)


postgres=3D# with t(v) as (values ('foo:bar'), ('foo/bar'), ('foo0bar'))
select v from t where v similar to 'foo[\d\w]_%';
    v
---------
 foo0bar
(1 row)


postgres=3D# \c - - - 5417
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
compression: off, ALPN: postgresql)
You are now connected to database "postgres" as user "postgres" on
host "sr-pau-db" (address "10.65.53.13") at port "5417".
postgres=3D# show server_version;
 server_version
----------------
 17.6
(1 row)


postgres=3D# with t(v) as (values ('foo:bar'), ('foo/bar'), ('foo0bar'))
select v from t where v similar to 'foo[\d\w]_%';
 v
---
(0 rows)


postgres=3D# \c - - - 5481
psql (17.6, server 18rc1)
WARNING: psql major version 17, server major version 18.
         Some psql features might not work.
You are now connected to database "postgres" as user "postgres" on
host "sr-pau-db" (address "10.65.53.13") at port "5481".
postgres=3D# show server_version;
 server_version
----------------
 18rc1
(1 row)


postgres=3D# with t(v) as (values ('foo:bar'), ('foo/bar'), ('foo0bar'))
select v from t where v similar to 'foo[\d\w]_%';
 v
---
(0 rows)


postgres=3D#