Thread: inserting user defined types through a rule?

I recently discovered a problem inserting a user-defined type when 
going through a rule.  I'm not sure if it's a -hackers or -users question,
but since it involves the interaction of a user-defined type and rules
I thought it envitable that I would end up here anyway.

The object in question is my X.509 type.  For compelling reasons
beyond the scope of this discussion, I need to define a table as:

create table certs (   name            varchar(20),   cert            x509,
   -- fields used with CRL lookups   serial_number   hugeint not null     constraint c1 check (serial_number =
serial_number(cert)),  issuer          principal not null     constraint c2 check (issuer = issuer(cert)),   subject
    principal not null unique     constraint c3 check (subject = subject(cert)),
 
   ...
);

where the constraints guarantee that the cached attributes accurately
reflect the contents of the cert (but these fields can be indexed and
searched).  In practice it's impossible to get those fields right in
a query so I also defined:
 create view cert_insert as select name, cert from certs;
 create rule certi as on insert to cert_insert do instead     insert into certs (name, cert, serial_number, subject,
issuer,...    )     values (new.name, new.cert,         serial_number(new.cert), subject(new.cert),
issuer(new.cert),...);
 

The problem is that I can insert literal text:
 create table t ( cert x509 ); insert into t values ('---- BEGIN CERTIFICATE ---- ....');

but when I try the same with cert_insert it's clear that "new.cert" 
isn't getting initialized properly.  (It works fine when the cert is
already in the database.)  Trying to explicitly cast the literal to 
as part of the query doesn't help - it seems that the rule just rewrites
the query and the cast is getting lost.

Workarounds don't seem to be viable.  I can't use a trigger on a temporary
table since there doesn't seem to be a clean way to trigger a rule from
one.  (I need to get parameters from the trigger to the SQL function to
the rule, and SQL functions don't seem to be able to take parameters --
or its undocumented if it can take something like $1, $2, etc.)  I can't
use a rule on the temporary table since it appears a rule still looks
at the original parameters, not the temp table.

Any ideas?  Is this something addressed in 7.2?  (I'm trying to stick
with the oldest useable version to avoid forcing DB upgrades.)  Or is
this a genuine hole in the user type/rules/triggers model?

Bear

Bear Giles <bgiles@coyotesong.com> writes:
> I recently discovered a problem inserting a user-defined type when 
> going through a rule. ...

> The problem is that I can insert literal text:
>   create table t ( cert x509 );
>   insert into t values ('---- BEGIN CERTIFICATE ---- ....');
> but when I try the same with cert_insert it's clear that "new.cert" 
> isn't getting initialized properly.  (It works fine when the cert is
> already in the database.)  Trying to explicitly cast the literal to 
> as part of the query doesn't help - it seems that the rule just rewrites
> the query and the cast is getting lost.

This seems like a bug, but I don't have much hope of being able to find
it without a test case to step through.  Could you boil things down to a
reproducible test case?

FWIW, it seems unlikely that the issue is your user-defined type per se;
the rule rewriter mechanisms are quite type-ignorant.  You may be able
to develop a test case that doesn't use your own type at all.

> Any ideas?  Is this something addressed in 7.2?

Can't tell at this point.  What version are you using, anyway?
        regards, tom lane

I'm using 7.1.3 currently, but am building and installing 7.2.1 tonight
to see if this fixes the problem.

I don't know the standard types and functions well enough to be able to
whip out a test case, but I think I do have an idea on what the problem
is.  If I'm right, the problem is triggered by any rule with a function 
that operates on one of the parameters.  If the parameter is already the
type then the rule succeeds.  If the parameter needs to be cast (e.g.,
because it's a literal value) then the rule fails.

E.g., if there is a function like
  function strlen(varchar) returns int4 ...

try
  create table test (s varchar(20), len int4);
  create view test_view as select s from test;
  create rule test_rule as on insert to test_view     do instead insert into test (s, strlen(s));

then
  insert into test_view values ('crash-n-burn!');

will fail.

Taken even further, you could probably use
  create rule test_rule2 as on insert to test_view    do instead insert into test2 (strlen(s));

The earlier example is just an updateable view with the tweak that
some of hidden underlying fields are also updated.  Strictly speaking
this breaks 3NF, but with the consistency checks it's a useful way of 
caching derived values while ensuring that they can't get out of sync
with the objects they cache.

Bear

P.S., it just occured to me that rules can allow multiple statements.
Maybe the workaround is
  create rule...    do instead (      insert into temporary table;      insert into final table from temporary table
usingfunctions;      clear temporary table  );
 

Bear Giles <bgiles@coyotesong.com> writes:
> I don't know the standard types and functions well enough to be able to
> whip out a test case, but I think I do have an idea on what the problem
> is.  If I'm right, the problem is triggered by any rule with a function 
> that operates on one of the parameters.  If the parameter is already the
> type then the rule succeeds.  If the parameter needs to be cast (e.g.,
> because it's a literal value) then the rule fails.

I tried this, but apparently there's more to it than that; AFAICT it
works in the cases where I'd expect it to work (viz, where there is a
suitable cast function available).

test71=# create function strlen(varchar) returns int as
test71-# 'select length($1)::int' language 'sql';
CREATE
test71=# create table test (s varchar(20), len int4);
CREATE
test71=# create view test_view as select s from test;
CREATE
test71=# create rule test_rule as on insert to test_view
test71-# do instead insert into test values (new.s, strlen(new.s));
CREATE
test71=# insert into test_view values ('crash-n-burn!');
INSERT 1610948 1
test71=# insert into test_view values (33::int);
INSERT 1610949 1
test71=# insert into test_view values (33::numeric);
ERROR:  Attribute 's' is of type 'varchar' but expression is of type 'numeric'       You will need to rewrite or cast
theexpression
 
test71=# select * from test;      s       | len
---------------+-----crash-n-burn! |  1333            |   2
(2 rows)

Perhaps there's a particular case where it fails, but you'll have to
give us more of a clue...
        regards, tom lane