pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
AW: AW: Cast INTEGER to BIT confusion
6+ messages / 4 participants
[nested] [flat]

* AW: AW: Cast INTEGER to BIT confusion
@ 2023-08-17 07:31  [Quipsy] Markus Karg <karg@quipsy.de>
  0 siblings, 2 replies; 6+ messages in thread

From: [Quipsy] Markus Karg @ 2023-08-17 07:31 UTC (permalink / raw)
  To: Erik Wienhold <ewie@ewie.name>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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.
-Markus


-----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

--
Erik


^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: AW: AW: Cast INTEGER to BIT confusion
@ 2023-08-17 13:18  Tom Lane <tgl@sss.pgh.pa.us>
  parent: [Quipsy] Markus Karg <karg@quipsy.de>
  1 sibling, 1 reply; 6+ messages in thread

From: Tom Lane @ 2023-08-17 13:18 UTC (permalink / raw)
  To: [Quipsy] Markus Karg <karg@quipsy.de>; +Cc: Erik Wienhold <ewie@ewie.name>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

"[Quipsy] Markus Karg" <karg@quipsy.de> writes:
> 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.

That's probably because the grammar uses SystemTypeName (effectively
prepending "pg_catalog.") for any type that has special syntax called
out in the SQL standard.  You could get around that in various ways,
but they all involve changing the way the CREATE TABLE command is
written, because just plain "BIT" is a SQL-mandated special case.

> It seems it is simply impossible to run this application on PostgreSQL, and we have to stick with a different RDBMS. Very sad.

You could leave the type alone and instead fool with the properties of
the cast (see [1]).  As superuser:

regression=# create table t (f1 bit);
CREATE TABLE
regression=# insert into t values (1);
ERROR:  column "f1" is of type bit but expression is of type integer
LINE 1: insert into t values (1);
                              ^
HINT:  You will need to rewrite or cast the expression.
regression=# select * from pg_cast where castsource = 'integer'::regtype and casttarget = 'bit'::regtype;
  oid  | castsource | casttarget | castfunc | castcontext | castmethod 
-------+------------+------------+----------+-------------+------------
 10186 |         23 |       1560 |     1683 | e           | f
(1 row)

regression=# update pg_cast set castcontext = 'a' where castsource = 'integer'::regtype and casttarget = 'bit'::regtype;
UPDATE 1
regression=# select * from pg_cast where castsource = 'integer'::regtype and casttarget = 'bit'::regtype;
  oid  | castsource | casttarget | castfunc | castcontext | castmethod 
-------+------------+------------+----------+-------------+------------
 10186 |         23 |       1560 |     1683 | a           | f
(1 row)

regression=# insert into t values (1);
INSERT 0 1

The main disadvantage of this approach is you'd have to remember to
perform that UPDATE in any new database, since pg_dump wouldn't
preserve it for you.

On the whole though I think this project is a lost cause.  If you
are insisting on bug-compatibility with non-SQL-compliant details
of some other DBMS, and you can't adjust the application at all,
there is going to be some new showstopper problem biting you
every day.

			regards, tom lane

[1] https://www.postgresql.org/docs/current/catalog-pg-cast.html





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: AW: AW: Cast INTEGER to BIT confusion
@ 2023-08-17 13:31  Erik Wienhold <ewie@ewie.name>
  parent: [Quipsy] Markus Karg <karg@quipsy.de>
  1 sibling, 0 replies; 6+ messages in thread

From: Erik Wienhold @ 2023-08-17 13:31 UTC (permalink / raw)
  To: [Quipsy] Markus Karg <karg@quipsy.de>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

> 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





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: AW: AW: Cast INTEGER to BIT confusion
@ 2023-08-17 13:37  Erik Wienhold <ewie@ewie.name>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: Erik Wienhold @ 2023-08-17 13:37 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

> On 17/08/2023 15:18 CEST Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> That's probably because the grammar uses SystemTypeName (effectively
> prepending "pg_catalog.") for any type that has special syntax called
> out in the SQL standard.  You could get around that in various ways,
> but they all involve changing the way the CREATE TABLE command is
> written, because just plain "BIT" is a SQL-mandated special case.

The answer I was looking for.  Thanks Tom.

--
Erik





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: AW: AW: Cast INTEGER to BIT confusion
@ 2023-08-17 17:09  GF <phabriz@gmail.com>
  parent: Erik Wienhold <ewie@ewie.name>
  0 siblings, 1 reply; 6+ messages in thread

From: GF @ 2023-08-17 17:09 UTC (permalink / raw)
  To: Erik Wienhold <ewie@ewie.name>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

Erik,
The could be a possible solution, if you could play with search_path...
You could mirror all the original schema onto another one with views that
have the original table names.
For all tables that need some special treatment you can then write
instead-of triggers, while all other views are updatable.

E.g., let's say they gave you a schema like:
create schema original;
create table original.t(f1 bit);
create table original.u(f2 int);

You can mirror it in this way:
create schema mirror;
create view mirror.t as select f1::int from original.t;
create view mirror.u as select * from original.u;
create function mirror.ins_t() returns trigger as $$ begin insert into
original.t(f1) values((new.f1)::bit); return null; end; $$ language plpgsql;
create trigger ins_t instead of insert on mirror.t for each row execute
function mirror.ins_t();
-- And something like that for upd and del...

The user that you use to connect must be granted the rights on mirror views
and on original tables that need conversion. Its default search_path must
be altered to the mirror schema (if you are not already able to do so on
the connection string):
create user foo;
alter user foo set search_path to mirror;
grant usage on schema original, mirror to foo;
grant insert, update, delete on original.t, mirror.t, mirror.u to foo;  --
you need original.t but not original.u...

And now you can do:
psql -U foo -c "insert into t values(1)" -c "insert into u values(42)"

Best,
Giovanni

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: AW: AW: Cast INTEGER to BIT confusion
@ 2023-08-17 17:12  GF <phabriz@gmail.com>
  parent: GF <phabriz@gmail.com>
  0 siblings, 0 replies; 6+ messages in thread

From: GF @ 2023-08-17 17:12 UTC (permalink / raw)
  To: Erik Wienhold <ewie@ewie.name>; karg@quipsy.de; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

On Thu, 17 Aug 2023 at 19:09, GF <phabriz@gmail.com> wrote:

>
> Erik,
> The could be a possible solution, if you could play with search_path...
>
>
  (sorry, Erik, obviously my message was intended to Markus)

^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2023-08-17 17:12 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-08-17 07:31 AW: AW: Cast INTEGER to BIT confusion [Quipsy] Markus Karg <karg@quipsy.de>
2023-08-17 13:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-08-17 13:37   ` Erik Wienhold <ewie@ewie.name>
2023-08-17 17:09     ` GF <phabriz@gmail.com>
2023-08-17 17:12       ` GF <phabriz@gmail.com>
2023-08-17 13:31 ` Erik Wienhold <ewie@ewie.name>

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