Обсуждение: BUG #10836: Rule with RETURNING claims incorrect type
The following bug has been logged on the website: Bug reference: 10836 Logged by: Matthias Raffelsieper Email address: hoschiraffel@freenet.de PostgreSQL version: 9.3.4 Operating system: Mac OSX Description: Hi all. While trying to create rules, I hit a situation that I figure might be a bug. In essence, I observe that when explicitly listing columns in a RETURNING statement of a rule, the type checking is somehow thrown off. When using the '*' all is fine. Please see below for a small example session that exhibits this bug. $ psql test psql (9.3.4) Type "help" for help. test=# \set VERBOSITY verbose test=# create table foobar (id int, info text); CREATE TABLE test=# create view test_me as select id as foo, info as bar from foobar; CREATE VIEW test=# create rule test_me_ins as on insert to test_me do instead insert into foobar values (new.foo,new.bar) returning (id,info); ERROR: 42P17: RETURNING list's entry 1 has different type from column "foo" LOCATION: checkRuleResultList, rewriteDefine.c:682 test=# create rule test_me_ins as on insert to test_me do instead insert into foobar values (new.foo,new.bar) returning *; CREATE RULE test=# insert into test_me VALUES (1,'hello'); INSERT 0 1 test=# insert into test_me VALUES (2,'world') returning bar; bar ------- world (1 row) INSERT 0 1 test=# select * from test_me; foo | bar -----+------- 1 | hello 2 | world (2 rows) test=# Cheers, Matt
On 7/2/14 3:05 PM, hoschiraffel@freenet.de wrote: > In essence, I observe that when explicitly listing columns in a RETURNING > statement of a rule, the type checking is somehow thrown off. When using the > '*' all is fine. Please see below for a small example session that exhibits > this bug. > > $ psql test > psql (9.3.4) > Type "help" for help. > > test=# \set VERBOSITY verbose > test=# create table foobar (id int, info text); > CREATE TABLE > test=# create view test_me as select id as foo, info as bar from foobar; > CREATE VIEW > test=# create rule test_me_ins as on insert to test_me do instead insert > into foobar values (new.foo,new.bar) returning (id,info); > ERROR: 42P17: RETURNING list's entry 1 has different type from column > "foo" > LOCATION: checkRuleResultList, rewriteDefine.c:682 You're actually only returning a single column. RETURNING (id, info) is the same as RETURNING ROW(id, info), which is very different from RETURNING id, info. See http://www.postgresql.org/docs/9.3/static/rowtypes.html, for example. Note that this behaviour is the same as in SELECT lists; SELECT (id, info) FROM foobar; will also only give you a single column. .marko
hoschiraffel@freenet.de writes:
> test=# create rule test_me_ins as on insert to test_me do instead insert
> into foobar values (new.foo,new.bar) returning (id,info);
> ERROR: 42P17: RETURNING list's entry 1 has different type from column
> "foo"
This is a syntax mistake. What you wrote is equivalent to
returning row(id,info);
so it returns a single composite column rather than the required int and
text columns, so the complaint is correct (if unhelpful :-().
Leave off the parens:
returning id,info;
I'm not sure if there's anything much we could do about improving the
situation. Allowing a row constructor with just parens is required by
SQL standard, but I've seen people confused before by thinking that
outer parens are just syntactic decoration that don't change the meaning
of the expression. Maybe if we just mentioned the two types (record vs
int, in this case) the user's mind would be led in the right direction?
regards, tom lane
On 7/2/14 3:51 PM, Marko Tiikkaja wrote: > On 7/2/14 3:05 PM, hoschiraffel@freenet.de wrote: >> [...] > > You're actually only returning a single column. RETURNING (id, info) is > the same as RETURNING ROW(id, info), which is very different from > RETURNING id, info. See > http://www.postgresql.org/docs/9.3/static/rowtypes.html, for example. > > Note that this behaviour is the same as in SELECT lists; SELECT (id, > info) FROM foobar; will also only give you a single column. Hello Marko. Thanks for the explanation and sorry for not spotting my mistake. Please close this bug as invalid. It would however have helped if I had gotten back the expected type and the actually provided type; even in verbose mode this was not supplied. Would this be possible, or is there already a configuration option to enable this? Cheers, Matt
Matthias Raffelsieper <hoschiraffel@freenet.de> writes: > It would however have helped if I had gotten back the expected type and > the actually provided type; even in verbose mode this was not supplied. Yeah, I came to the same conclusion. I've done something about this: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=7980ab30ecf36162699f138697e2ff5589d9063e regards, tom lane
On 07/02/2014 18:45, Tom Lane wrote: > Matthias Raffelsieper <hoschiraffel@freenet.de> writes: >> It would however have helped if I had gotten back the expected type and >> the actually provided type; even in verbose mode this was not supplied. > > Yeah, I came to the same conclusion. I've done something about this: > http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=7980ab30ecf36162699f138697e2ff5589d9063e > Wow, that was fast, thanks a lot. I just had the time to check it out and I believe that this also helps in other cases where one might mix up the RETURNING columns. Thanks again, Matt