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$

Search Discussions

  • Tom Lane at Aug 29, 2011 at 4:10 pm

    Andreas 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
  • Andreas at Aug 30, 2011 at 3:54 am

    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. :)
  • Tom Lane at Aug 30, 2011 at 4:06 am

    Andreas 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedAug 29, '11 at 3:26p
activeAug 30, '11 at 4:06a
posts4
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Tom Lane: 2 posts Andreas: 2 posts

People

Translate

site design / logo © 2022 Grokbase