Обсуждение: 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