ERROR: table row type and query-specified row type do not match

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

Hi Tom,

we are running PostgreSQL 8.2.4.

Tom Lane wrote:

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

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)