Thread: ERROR: table row type and query-specified row type do not match
Hi, can someone help/explain me why i get this error when i try to make an update on that view? It seems that something is wrong with the subselect. http://fooby.imosnet.de/error.txt Thanks, Patryk
Patryk Kordylewski <pk@imos.net> writes: > can someone help/explain me why i get this error when i try to make an > update on that view? It seems that something is wrong with the subselect. > http://fooby.imosnet.de/error.txt AFAICT, this works for me, so either you left something important out of your example or you're looking at an already-fixed bug. What PG version is that? regards, tom lane
Patryk Kordylewski wrote: > Hi Tom, > > we are running PostgreSQL 8.2.4. > > Tom Lane wrote: >> Patryk Kordylewski <pk@imos.net> writes: >>> can someone help/explain me why i get this error when i try to make >>> an update on that view? It seems that something is wrong with the >>> subselect. >>> http://fooby.imosnet.de/error.txt >> >> AFAICT, this works for me, so either you left something important out of >> your example or you're looking at an already-fixed bug. What PG version >> is that? >> >> regards, tom lane >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 3: Have you checked our extensive FAQ? >> >> http://www.postgresql.org/docs/faq >> I made a test-case which is not working for me. Perhaps something wrong with the SELECT DISTINCT ON () syntax when i specify more then 1 column? At first this was working when i specified only one column in the SELECT DISTINCT ON-clause, then i inserted a dummy column "second_distinct" and put it into the clause and it's not working anymore. PG Version: 8.2.4 http://fooby.imosnet.de/not_working.txt Thanks for help, Patryk
Patryk Kordylewski wrote: > I made a test-case which is not working for me. Perhaps something wrong > with the SELECT DISTINCT ON () syntax when i specify more then 1 column? > > At first this was working when i specified only one column in the SELECT > DISTINCT ON-clause, then i inserted a dummy column "second_distinct" and > put it into the clause and it's not working anymore. > > PG Version: 8.2.4 > > http://fooby.imosnet.de/not_working.txt > > Thanks for help, > Patryk I think i found the problem and made 2 sql files to reproduce it. The only difference between this 2 files is the order by-clause of the view... http://fooby.imosnet.de/order_by_working.txt http://fooby.imosnet.de/order_by_not_working.txt
Patryk Kordylewski <pk@imos.net> writes: > I think i found the problem and made 2 sql files to reproduce it. The > only difference between this 2 files is the order by-clause of the view... > http://fooby.imosnet.de/order_by_working.txt > http://fooby.imosnet.de/order_by_not_working.txt Hmm, actually you don't even need the rule, any use of a whole-row Var for that view will fail: regression=# select v_baz from v_baz; ERROR: table row type and query-specified row type do not match DETAIL: Table row contains 3 attributes, but query expects 2. The problem is that the ORDER BY on a value that's not part of the output list generates a hidden "resjunk" column in the view, which confuses ExecEvalVar. I've applied a patch that will appear in 8.2.5. regards, tom lane
Hi Tom, we are running PostgreSQL 8.2.4. Tom Lane wrote: > Patryk Kordylewski <pk@imos.net> writes: >> can someone help/explain me why i get this error when i try to make an >> update on that view? It seems that something is wrong with the subselect. >> http://fooby.imosnet.de/error.txt > > AFAICT, this works for me, so either you left something important out of > your example or you're looking at an already-fixed bug. What PG version > is that? > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq >
Patryk Kordylewski wrote: > Hi Tom, > > we are running PostgreSQL 8.2.4. Maybe this has something to do with dropped columns? Patryk: please run select * from pg_attribute where attrelid = 'foo'::regclass and attisdropped; Replacing 'foo' with the names of all the tables and views involved in the query. If any show up, let us know. > Tom Lane wrote: >> Patryk Kordylewski <pk@imos.net> writes: >>> can someone help/explain me why i get this error when i try to make an >>> update on that view? It seems that something is wrong with the subselect. >>> http://fooby.imosnet.de/error.txt >> AFAICT, this works for me, so either you left something important out of >> your example or you're looking at an already-fixed bug. What PG version >> is that? -- Alvaro Herrera http://www.PlanetPostgreSQL.org/ "Just treat us the way you want to be treated + some extra allowance for ignorance." (Michael Brusser)