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
