From: Erik Wienhold <ewie@ewie.name>
To: [Quipsy] Markus Karg <karg@quipsy.de>
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: AW: AW: Cast INTEGER to BIT confusion
Date: Thu, 17 Aug 2023 15:31:44 +0200 (CEST)
Message-ID: <347149651.358104.1692279104782@office.mailbox.org> (raw)
In-Reply-To: <015a3586e6dc40e0b1c3a4a1f3b1e44c@quipsy.de>
References: <fbc716336d544e93b6af5e11af3e4aa0@quipsy.de>
<533487381.212078.1692100096551@office.mailbox.org>
<84bd46dba5ec4097ba9663f46a1e5754@quipsy.de>
<400164688.228966.1692109673749@office.mailbox.org>
<015a3586e6dc40e0b1c3a4a1f3b1e44c@quipsy.de>
> 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-in types. I never used this to override built-in types so this is a surprise to me. (And obviously I haven't tested the 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: ewie@ewie.name, karg@quipsy.de
Subject: Re: Cast INTEGER to BIT confusion
In-Reply-To: <347149651.358104.1692279104782@office.mailbox.org>
References: <fbc716336d544e93b6af5e11af3e4aa0@quipsy.de> <533487381.212078.1692100096551@office.mailbox.org> <84bd46dba5ec4097ba9663f46a1e5754@quipsy.de> <400164688.228966.1692109673749@office.mailbox.org> <015a3586e6dc40e0b1c3a4a1f3b1e44c@quipsy.de> <347149651.358104.1692279104782@office.mailbox.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox