Re: Latest patches break one of our unit-test, related to RLS - Mailing list pgsql-general

On Fri, Sep 12, 2025 at 2:45 PM 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 PM Dominique Devienne <ddevienne@gmail.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=# show server_version;
 server_version
----------------
 16.9
(1 row)


postgres=# 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=# \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=# show server_version;
 server_version
----------------
 16.10
(1 row)


postgres=# 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=# \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=# show server_version;
 server_version
----------------
 17.5
(1 row)


postgres=# 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=# \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=# show server_version;
 server_version
----------------
 17.6
(1 row)


postgres=# 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=# \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=# show server_version;
 server_version
----------------
 18rc1
(1 row)


postgres=# 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=#