Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qWd6S-003r2T-IJ for pgsql-general@arkaria.postgresql.org; Thu, 17 Aug 2023 13:31:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qWd6Q-00HVkE-O9 for pgsql-general@arkaria.postgresql.org; Thu, 17 Aug 2023 13:31:54 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qWd6Q-00HVk3-BI for pgsql-general@lists.postgresql.org; Thu, 17 Aug 2023 13:31:54 +0000 Received: from mout-u-107.mailbox.org ([80.241.59.207]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qWd6M-000dJl-9l for pgsql-general@lists.postgresql.org; Thu, 17 Aug 2023 13:31:53 +0000 Received: from smtp2.mailbox.org (smtp2.mailbox.org [10.196.197.2]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (4096 bits) server-digest SHA256) (No client certificate requested) by mout-u-107.mailbox.org (Postfix) with ESMTPS id 4RRQrx1chWz9sRD; Thu, 17 Aug 2023 15:31:45 +0200 (CEST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ewie.name; s=MBO0001; t=1692279105; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:mime-version:mime-version:content-type:content-type: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=bzVEMU9bp0kHVF5OWCTIIBD4772PRuoQVPv+9eCTtgk=; b=qz3Lip7v9ozAmIaaHw2mvLnfrZniL5BMT+4nCHJnoVNz64STKjf8dUpeGjzaVTFaasFxAY ysSDdjKo+3JHA4hW+ZEvb9kcZd4Kx1tVB+/UYyH980D8cTQOwS5GSmrpmrutjofzVUWslJ yu7z+h6QZvqnHxHQCHft8VGh54VO2DVG1W4fQWGBpkPRKheQwAJDwzEZXwjie+wFv1XJ16 O0RrCAZyW4d0jLhI9F35bRrd5rwFW5rOxMWEf+Q626PVOEg/wRSCNZdGqVIuvqabAfZeU/ +tTHrAHqIWTJiskBL91qnUS7JWAiEvMb/yOvvYaeozfsyYy4MGfEm+mfDDddZQ== Date: Thu, 17 Aug 2023 15:31:44 +0200 (CEST) From: Erik Wienhold To: "[Quipsy] Markus Karg" , "pgsql-general@lists.postgresql.org" Message-ID: <347149651.358104.1692279104782@office.mailbox.org> In-Reply-To: <015a3586e6dc40e0b1c3a4a1f3b1e44c@quipsy.de> References: <533487381.212078.1692100096551@office.mailbox.org> <84bd46dba5ec4097ba9663f46a1e5754@quipsy.de> <400164688.228966.1692109673749@office.mailbox.org> <015a3586e6dc40e0b1c3a4a1f3b1e44c@quipsy.de> Subject: Re: AW: AW: Cast INTEGER to BIT confusion MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Priority: 3 Importance: Normal List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 17/08/2023 09:31 CEST [Quipsy] Markus Karg wrote: > > I am giving up. While even `SELECT current_schemas(true)` correctly print= s > `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, a= nd > we have to stick with a different RDBMS. Very sad. > > -----Urspr=C3=BCngliche Nachricht----- > Von: Erik Wienhold =20 > Gesendet: Dienstag, 15. August 2023 16:28 > An: [Quipsy] Markus Karg ; pgsql-general@lists.postgresql= .org > Betreff: Re: AW: Cast INTEGER to BIT confusion > > > On 15/08/2023 14:02 CEST [Quipsy] Markus Karg wrote: > > > > I just tried out your proposal on PostgreSQL 15.3 and this is the resul= t: > > > > 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=C3=BCngliche Nachricht----- > > Von: Erik Wienhold > > Gesendet: Dienstag, 15. August 2023 13:48 > > An: [Quipsy] Markus Karg ;=20 > > 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= =20 > > and 1 and don't use bit string functions. The search path must be=20 > > changed so that domain bit overrides pg_catalog.bit: > > > > =09=3D# CREATE SCHEMA xxx; > > =09=3D# CREATE DOMAIN xxx.bit AS int; > > =09=3D# SET search_path =3D xxx, pg_catalog; > > =09=3D# CREATE TABLE t (c bit); > > =09=3D# INSERT INTO t VALUES (1); > > =09INSERT 0 1 > > > > But I would do that only if the third-party code cannot be tweaked=20 > > because the custom domain could be confusing. It's also prone to=20 > > errors as it relies on a specific search path order. Also make sure=20 > > that regular users cannot create objects in schema xxx that would overr= ide 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 h= aven't tested the search path feature before posting.) > > Neither [1] or [2] mention that special (?) case or if there's a distinct= ion 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-SEA= RCH-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 stan= dard bit type. In the following script I create several domains in schema xxx: bit and flo= at 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. =09BEGIN; =09 =09CREATE SCHEMA xxx; =09GRANT USAGE ON SCHEMA xxx TO current_user; =09 =09CREATE DOMAIN xxx.bit AS int; =09CREATE DOMAIN xxx.float AS int; =09CREATE DOMAIN xxx.foo AS int; =09CREATE DOMAIN xxx.inet AS int; =09CREATE DOMAIN pg_catalog.foo AS int; =09\dD *.(bit|float|foo|inet) =09 =09SET LOCAL search_path =3D xxx, pg_catalog; =09SELECT current_schemas(true); =09 =09CREATE TABLE public.t (f1 bit, f2 float, f3 inet, f4 foo); =09 =09SET LOCAL search_path =3D ''; =09\d public.t =09 =09ROLLBACK; 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 se= arch path. =09BEGIN =09CREATE SCHEMA =09GRANT =09CREATE DOMAIN =09CREATE DOMAIN =09CREATE DOMAIN =09CREATE DOMAIN =09CREATE DOMAIN =09 List of domains =09 Schema | Name | Type | Collation | Nullable | Default | Check =09------------+-------+---------+-----------+----------+---------+------- =09 pg_catalog | foo | integer | | | | =09 xxx | bit | integer | | | | =09 xxx | float | integer | | | | =09 xxx | foo | integer | | | | =09 xxx | inet | integer | | | | =09(4 rows) =09 =09SET =09 current_schemas =09------------------ =09 {xxx,pg_catalog} =09(1 row) =09 =09CREATE TABLE =09SET =09 Table "public.t" =09 Column | Type | Collation | Nullable | Default =09--------+------------------+-----------+----------+--------- =09 f1 | bit(1) | | | =09 f2 | double precision | | | =09 f3 | xxx.inet | | | =09 f4 | xxx.foo | | | =09 =09ROLLBACK -- Erik