Обсуждение: BUG #5389: Column order on dump/reload broken from defined setof function
The following bug has been logged online: Bug reference: 5389 Logged by: Timothy Seever Email address: tim.seever@gmail.com PostgreSQL version: 8.3.5, others Operating system: Linux Description: Column order on dump/reload broken from defined setof function Details: Adding a column to an inherited table in-place works as expected with the combined table/etc. However on dump/reload the physical order changes, so any? predefined select from a setof function pulling from the combined table will result in a mismatch. For example: table a: col1 text table b: col2 date table c: (inherits a&b) ALTER table a add column col3 int; table c now has col3 on the end physically On dump/restore, it's reordered to the correct place based on the inherited table... which is fine, except in the following: --Yes, the following is pointless as is, but with arguments less so CREATE FUNCTION testfunc() RETURNS setof tablec AS $BODY$ SELECT * FROM tablec; $BODY$ LANGUAGE 'sql' STABLE; CREATE VIEW xyz AS select * FROM testfunc() c(col1, col2, col3) where col2='2010-01-01'::date; pg_dump dumps the view with the existing label/column order, so upon restore it's misaligned since it will now be col1, col3, col2 in table c I'm not sure if there's some additional syntax causing it, but that's basically what appears to be happening
"Timothy Seever" <tim.seever@gmail.com> writes: > Adding a column to an inherited table in-place works as expected with the > combined table/etc. However on dump/reload the physical order changes, so > any? predefined select from a setof function pulling from the combined table > will result in a mismatch. Yup. This is not pg_dump's fault, and there's no way for pg_dump to fix it for you. Sorry, but you'll need to correct the queries yourself. regards, tom lane
Re: BUG #5389: Column order on dump/reload broken from defined setof function
От
Dimitri Fontaine
Дата:
"Timothy Seever" <tim.seever@gmail.com> writes: > CREATE FUNCTION testfunc() RETURNS setof tablec AS $BODY$ SELECT * FROM > tablec; $BODY$ LANGUAGE 'sql' STABLE; Common wisdom saith to never ever use "SELECT *" in your code. You just found out a reason why. Regards, -- dim
On Fri, Mar 26, 2010 at 7:00 AM, Dimitri Fontaine <dfontaine@hi-media.com> wrote: > "Timothy Seever" <tim.seever@gmail.com> writes: >> CREATE FUNCTION testfunc() RETURNS setof tablec AS $BODY$ SELECT * FROM >> tablec; $BODY$ LANGUAGE 'sql' STABLE; > > Common wisdom saith to never ever use "SELECT *" in your code. You just > found out a reason why. Still pretty annoying though. ...Robert