Re: AW: AW: Cast INTEGER to BIT confusion - Mailing list pgsql-general

> On 17/08/2023 09:31 CEST [Quipsy] Markus Karg <karg@quipsy.de> wrote:
>
> I am giving up. While even `SELECT current_schemas(true)` correctly prints
> `xxx, pg_catalog` it still uses the original bit type. This is completely
> NOT as described in the documentation, where it is clearly told that
> pg_catalog only is searched immediately if NOT found in the search path.
> It seems it is simply impossible to run this application on PostgreSQL, and
> we have to stick with a different RDBMS. Very sad.
>
> -----Ursprüngliche Nachricht-----
> Von: Erik Wienhold <ewie@ewie.name>
> Gesendet: Dienstag, 15. August 2023 16:28
> An: [Quipsy] Markus Karg <karg@quipsy.de>; pgsql-general@lists.postgresql.org
> Betreff: Re: AW: Cast INTEGER to BIT confusion
>
> > On 15/08/2023 14:02 CEST [Quipsy] Markus Karg <karg@quipsy.de> wrote:
> >
> > I just tried out your proposal on PostgreSQL 15.3 and this is the result:
> >
> > ERROR:  column "c" is of type bit but expression is of type integer
> > LINE 5:   INSERT INTO t VALUES (1);
> >                                 ^
> > HINT:  You will need to rewrite or cast the expression.
> >
> > Apparently the search path is ignored?!
> >
> > -----Ursprüngliche Nachricht-----
> > Von: Erik Wienhold <ewie@ewie.name>
> > Gesendet: Dienstag, 15. August 2023 13:48
> > An: [Quipsy] Markus Karg <karg@quipsy.de>;
> > pgsql-general@lists.postgresql.org
> > Betreff: Re: Cast INTEGER to BIT confusion
> >
> > You could create a custom domain if you're only interested in values 0
> > and 1 and don't use bit string functions.  The search path must be
> > changed so that domain bit overrides pg_catalog.bit:
> >
> >     =# CREATE SCHEMA xxx;
> >     =# CREATE DOMAIN xxx.bit AS int;
> >     =# SET search_path = xxx, pg_catalog;
> >     =# CREATE TABLE t (c bit);
> >     =# INSERT INTO t VALUES (1);
> >     INSERT 0 1
> >
> > But I would do that only if the third-party code cannot be tweaked
> > because the custom domain could be confusing.  It's also prone to
> > errors as it relies on a specific search path order.  Also make sure
> > that regular users cannot create objects in schema xxx that would override objects in pg_catalog.
>
> Hmm, I thought that Postgres resolves all types through the search path, but apparently that is not the case for
built-intypes.  I never used this to override built-in types so this is a surprise to me.  (And obviously I haven't
testedthe search path feature before posting.) 
>
> Neither [1] or [2] mention that special (?) case or if there's a distinction between built-in types and user-defined
types. The USAGE privilege is required according to [2] but I was testing as superuser anyway. 
>
> [1] https://www.postgresql.org/docs/15/ddl-schemas.html
> [2] https://www.postgresql.org/docs/15/runtime-config-client.html#GUC-SEARCH-PATH

It looks like Postgres resolves standard SQL types without considering the
search path.  This could be a feature (perhaps mandated by the SQL standard?)
and not a bug if the rationale here is to avoid shadowing of standard types
with custom types so that type bit, for example, always behaves as the standard
bit type.

In the following script I create several domains in schema xxx: bit and float
match standard SQL type names, domain foo does not conflict with any built-in
type name, and inet is a built-in type but not a standard type.

    BEGIN;

    CREATE SCHEMA xxx;
    GRANT USAGE ON SCHEMA xxx TO current_user;

    CREATE DOMAIN xxx.bit AS int;
    CREATE DOMAIN xxx.float AS int;
    CREATE DOMAIN xxx.foo AS int;
    CREATE DOMAIN xxx.inet AS int;
    CREATE DOMAIN pg_catalog.foo AS int;
    \dD *.(bit|float|foo|inet)

    SET LOCAL search_path = xxx, pg_catalog;
    SELECT current_schemas(true);

    CREATE TABLE public.t (f1 bit, f2 float, f3 inet, f4 foo);

    SET LOCAL search_path = '';
    \d public.t

    ROLLBACK;

We see that table t is created with the standard SQL types instead of our
custom domains.  Only xxx.inet and xxx.foo are resolved according to the search
path.

    BEGIN
    CREATE SCHEMA
    GRANT
    CREATE DOMAIN
    CREATE DOMAIN
    CREATE DOMAIN
    CREATE DOMAIN
    CREATE DOMAIN
                                List of domains
       Schema   | Name  |  Type   | Collation | Nullable | Default | Check
    ------------+-------+---------+-----------+----------+---------+-------
     pg_catalog | foo   | integer |           |          |         |
     xxx        | bit   | integer |           |          |         |
     xxx        | float | integer |           |          |         |
     xxx        | foo   | integer |           |          |         |
     xxx        | inet  | integer |           |          |         |
    (4 rows)

    SET
     current_schemas
    ------------------
     {xxx,pg_catalog}
    (1 row)

    CREATE TABLE
    SET
                          Table "public.t"
     Column |       Type       | Collation | Nullable | Default
    --------+------------------+-----------+----------+---------
     f1     | bit(1)           |           |          |
     f2     | double precision |           |          |
     f3     | xxx.inet         |           |          |
     f4     | xxx.foo          |           |          |

    ROLLBACK

--
Erik