Thread: Known problem? Column mixup in sql-function

Hi,
I found a weired issue with a rather simple sql-function.
There are tables for projects, users and a m:n-relation between users
and projects where an user-id and a project-id say that this user has
acces to this project.
The function should filter all projects a user has access to.

This works nicely with a PG 8.4.8 on Windows but fails with a PG 8.4.0
on openSuse 11.1.
Well I know, OpenSuse 11.1 is out of support allread but I can't help it
for now.

On Suse the columns are mixed up.
The column names are in the same order as on windows but the data apears
in the wrong columns and some of the columns to the right show no data
at all.
I can run the query outside the function and it looks ok.
It seems that the "returns setof projects" doesn't work.
Is this a known problem?

CREATE OR REPLACE FUNCTION projects_with_access(integer)
   RETURNS SETOF projects AS
$BODY$
     SELECT  projects.*
     FROM    projects
             JOIN projectaccessrights USING ( project_id )
     WHERE   user_id = $1;
$BODY$

Andreas <maps.on@gmx.net> writes:
> This works nicely with a PG 8.4.8 on Windows but fails with a PG 8.4.0
> on openSuse 11.1.

Well, without a concrete test case it's hard to say, but certainly there
have been a lot of bugs fixed between 8.4.0 and 8.4.8.

If I had to bet based on the small amount of information you've shown
us, I'd wonder whether the projects table has had any columns dropped
from it.  If this is related to one of the dropped-column bugs we've
fixed, you could presumably work around it by dropping and recreating
the projects table.  But updating to 8.4.current would be a much better
idea.

            regards, tom lane

Am 29.08.2011 18:10, schrieb Tom Lane:
> Andreas<maps.on@gmx.net>  writes:
>> This works nicely with a PG 8.4.8 on Windows but fails with a PG 8.4.0
>> on openSuse 11.1.
> Well, without a concrete test case it's hard to say, but certainly there
> have been a lot of bugs fixed between 8.4.0 and 8.4.8.
>
> If I had to bet based on the small amount of information you've shown
> us, I'd wonder whether the projects table has had any columns dropped
> from it.  If this is related to one of the dropped-column bugs we've
> fixed, you could presumably work around it by dropping and recreating
> the projects table.  But updating to 8.4.current would be a much better
> idea.

This table was one of the first in the db and got added columns and
dropped, too.
The 8.4.0 was part of the distribution and they keep the version.
I'm a wee bit worried to manually upgrade since the OS release is out of
service for some time and I can't physically get to the box, yet. But
there is a new setup coming soon Then with 9.0.3 AFAIK. So there is hope.

I can't drop the table b/c there are a number of references.
Would a backup & restore of the db cure those lurking issues even with
8.4.0?
The thing is running over a year I'd say without a restore but daily
vacuum analyse.

Thanks for your reply.  :)

Andreas <maps.on@gmx.net> writes:
> Am 29.08.2011 18:10, schrieb Tom Lane:
>> Well, without a concrete test case it's hard to say, but certainly there
>> have been a lot of bugs fixed between 8.4.0 and 8.4.8.

> This table was one of the first in the db and got added columns and
> dropped, too.

That could be it then ...

> The 8.4.0 was part of the distribution and they keep the version.

You need to rattle some cages a bit harder.  There are multiple known
security and data-loss bugs in 8.4.0; the one you've hit here is minor
by comparison.  It's frankly irresponsible, not to say bordering on
incompetent, to refuse to update to a more recent bug-fix release.

> I can't drop the table b/c there are a number of references.

Well, if a dump and reload is acceptable, that should fix it too ---
assuming it is what I'm guessing it is.

            regards, tom lane