Обсуждение: Composite type versus Domain constraints.

I'm trying to experiment with domains and composite types under 8.0.2.
It seems that domain constraints don't fire when the domain is embedded
within a composite type:

---
create domain simple as smallint default 0 constraint limits check
(VALUE IN (0,1,2,3));
create type comp_simple as ( simp_val simple);

create table simple_table
(
    s1 simple,
    s2 comp_simple
);

insert into simple_table values (2, null);    -- works fine -- 2 is legal
value
insert into simple_table values (43, null); -- errors out correctly --
43 fails the constraint test.
insert into simple_table values (null, '(43)'); -- GRR works!!! It'll
let any smallint in. What happened to the constraint?

select * from simple_table;
social=# select * from simple_table;
  s1 |  s2
----+------
   2 |
     | (43)
(2 rows)

----

The 8.0.2 docs for composite types (doc/html/rowtypes.html) mention
using domain types as members of composite types to actually gain
constraint testing capability within composite types used outside of
tables.

We've also tried inverting the relationship between the domain and
composite type:
----
social=# create type simple as ( val int2);
CREATE TYPE
social=# create domain simple_checked as simple default '(0)'
constraint limits check ((VALUE).val IN (0,1,2,3));
ERROR:  "simple" is not a valid base type for a domain
social=#


Any way I can get a composite type with constraint tests? I need an
additional type with a separate oid for object / relational mapping.


----
James Robinson
Socialserve.com

James Robinson <jlrobins@socialserve.com> writes:
> insert into simple_table values (null, '(43)'); -- GRR works!!! It'll
> let any smallint in. What happened to the constraint?

The composite-type input routine doesn't check any constraints ...
and that includes domains.  You can make it work if you don't use
a composite literal:

egression=# insert into simple_table values (null, row(43));
ERROR:  value for domain "simple" violates check constraint "limits"

Obviously this whole area needs improvement.  Domain constraints in
particular fail to be enforced in many places where they should be,
such as plpgsql variables.

            regards, tom lane

On Apr 12, 2005, at 4:48 PM, Tom Lane wrote:

> James Robinson <jlrobins@socialserve.com> writes:
>> insert into simple_table values (null, '(43)'); -- GRR works!!! It'll
>> let any smallint in. What happened to the constraint?
>
> The composite-type input routine doesn't check any constraints ...
> and that includes domains.  You can make it work if you don't use
> a composite literal:
>
> egression=# insert into simple_table values (null, row(43));
> ERROR:  value for domain "simple" violates check constraint "limits"

Thank you for the great info. If I may, here's another question. I am in
the need of new scalar types, essentially domain'd smallints, hence
why my composite type had but one composite member. Domain'd
smallints would be great, but it seems when they get returned in a
result set to the client, they come shipped with the oid of smallint
(21 on my box), not the oid of the domain. I'm experimenting with
a client driver (Python's psycopg) which allows you to register
handlers for arbitrary oids -- but if the result set's metadata
contains the oid for smallint, this does not bode well -- if I register
for the domain's oid, it never gets returned to me in a select, and
if I register for int2's oid hilarity ensues.

Is there an easy path to creating (many) scalar types which piggyback
on int2's functions. Naive experimentation fails:

social=# create type MyType (
     INTERNALLENGTH = 2,
     INPUT = int2in,
    OUTPUT = int2out
);
ERROR:  function int2out(mytype) does not exist

Which is reasonable.

In short, I need a domain'd smallint with a different type oid returned
from selects. Is there a short path? The composite type solution works
at a purely SQL level, although something feels not quite right.


----
James Robinson
Socialserve.com

James Robinson <jlrobins@socialserve.com> writes:
> Thank you for the great info. If I may, here's another question. I am in
> the need of new scalar types, essentially domain'd smallints, hence
> why my composite type had but one composite member. Domain'd
> smallints would be great, but it seems when they get returned in a
> result set to the client, they come shipped with the oid of smallint
> (21 on my box), not the oid of the domain.

Yeah.  IIRC that was a deliberate decision on the grounds that most
client software would probably break if we sent the domain OID.
Maybe we should reconsider, but I think the answer would be the same.

> Is there an easy path to creating (many) scalar types which piggyback
> on int2's functions. Naive experimentation fails:

> social=# create type MyType (
>      INTERNALLENGTH = 2,
>      INPUT = int2in,
>     OUTPUT = int2out
> );
> ERROR:  function int2out(mytype) does not exist

> Which is reasonable.

What you'd have to do is also create dummy I/O functions as aliases
for the internal int2in/out functions.

regression=# create function mytypein(cstring) returns mytype
regression-# as 'int2in' language internal strict immutable;
NOTICE:  type "mytype" is not yet defined
DETAIL:  Creating a shell type definition.
CREATE FUNCTION
regression=# create function mytypeout(mytype) returns cstring
regression-# as 'int2out' language internal strict immutable;
NOTICE:  argument type mytype is only a shell
CREATE FUNCTION
regression=# create type mytype( input = mytypein, output = mytypeout,
regression(# internallength = 2, passedbyvalue, alignment = int2 );
CREATE TYPE
regression=# select '42'::mytype;
 mytype
--------
 42
(1 row)

regression=#

Note that it's absolutely critical that you get the size/alignment/byval
properties right ;-)

With suitable casts to/from int2 (probably implicit to int2, but not
implicit from), this would probably work fairly well.

regression=# select '42'::mytype + '42'::mytype;
ERROR:  operator does not exist: mytype + mytype
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.
regression=# create cast (mytype as int2) without function as implicit;
CREATE CAST
regression=# select '42'::mytype + '42'::mytype;
 ?column?
----------
       84
(1 row)

regression=# create table zit(f1 mytype primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "zit_pkey" for table "zit"
CREATE TABLE
regression=# \d zit
     Table "public.zit"
 Column |  Type  | Modifiers
--------+--------+-----------
 f1     | mytype | not null
Indexes:
    "zit_pkey" PRIMARY KEY, btree (f1 int2_ops)

regression=#

Sweet ... I wasn't actually expecting the index to work without more
hacking than that ...

            regards, tom lane

On Apr 13, 2005, at 11:50 AM, Tom Lane wrote:

>> Thank you for the great info. If I may, here's another question. I am
>> in
>> the need of new scalar types, essentially domain'd smallints, hence
>> why my composite type had but one composite member. Domain'd
>> smallints would be great, but it seems when they get returned in a
>> result set to the client, they come shipped with the oid of smallint
>> (21 on my box), not the oid of the domain.
>
> Yeah.  IIRC that was a deliberate decision on the grounds that most
> client software would probably break if we sent the domain OID.
> Maybe we should reconsider, but I think the answer would be the same.

[snipped fantastic PG type trickery -- thanks! We'll run with that style
for now]

Those wrapper in/out functions and casts to int2 look great and will
work
for what we absolutely need, but still seem a bit on the wordy side.
What
about, for databases + client applications which expect it, an option to
have a domain expose its oid in result set metadata. Domains created
without the extra syntactical flag would operate exactly as they do now
-- returning the oid of the wrapped type. But if created like:

CREATE DOMAIN mytype AS int2 EXPOSE OID
        CONSTRAINT test CHECK (VALUE IN (0,1,2,3));

Then when these guys are returned in queries, the domain's oid is
eturned as the metadata for the column.

Would psql or pg_dump care? Our client apps would be expecting it
and would love it.

I would suspect that an additional boolean column in pg_type, something
along the lines of 'tyobscuresbasetype', defaulting to false, but set to
true if 'EXPOSE OID' was provided would be enough for the system to
decide which oid to send back.

That seems less fragile and error prone than casts, wrapping in/out
functions, etc. Reduces the barrier of effort towards making lots and
lots of these little guys and harnessing easy extra value on the client
side of things. We've essentially got hordes of enumerated types
on the client side adding lots of value and functionality to the
database-stored smallint, and having only two lines of SQL to build
to educate the database about each one would be a real winner.

Not to sound ungrateful -- the scalar type tricks can work, but with
more SQL-level effort.

----
James Robinson
Socialserve.com