Bruce Momjian wrote:
Tom Lane wrote:
Bruce Momjian <bruce@momjian.us> writes:
Tom Lane wrote:
That isn't going to work. At least not unless you start trying to force
roles to have the same OIDs in the new installation.
If so I can use the CREATE ROLE ... SYSID clause when doing a binary
upgrade.
Oh, I had forgotten we still had that wart in the grammar.
It doesn't actually work:

else if (strcmp(defel->defname, "sysid") == 0)
{
ereport(NOTICE,
(errmsg("SYSID can no longer be specified")));
}

Not sure if it's better to try to make that work again than to add
another hack in pg_upgrade_support. On the whole that's a keyword
I'd rather see us drop someday soon.
OK, let me work on adding it to pg_upgrade_support. Glad you saw this.
I have fixed the bug by using pg_upgrade_support. It was a little
complicated because you need to install the pg_upgrade_support functions
in the super-user database so it is available when you create the users
in the first step of restoring the pg_dumpall file.

I am afraid we have to batckpatch this to fix to 9.0 for 9.0 to 9.0
upgrades. It does not apply when coming from pre-9.0 because there was
no pg_largeobject_metadata.

For testing I did this:

CREATE DATABASE lo;
\c lo
SELECT lo_import('/etc/motd');
\set loid `psql -qt -c 'select loid from pg_largeobject' lo`
CREATE ROLE user1;
CREATE ROLE user2;
-- force user2 to have a different user id on restore
DROP ROLE user1;
GRANT ALL ON LARGE OBJECT :loid TO user2;

The fixed version shows:

lo=> select * from pg_largeobject_metadata;
lomowner | lomacl
----------+------------------------------------------
10 | {postgres=rw/postgres,user2=rw/postgres}
(1 row)

In the broken version, 'user2' was a raw oid, obviously wrong.

Fortunately this was found during my testing and not reported as a bug
by a pg_upgrade user.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Search Discussions

  • Bruce Momjian at Jan 8, 2011 at 3:01 am
    Patch applied.

    I did not backpatch to 9.0 because you can't migrate from 9.0 to 9.0
    with the same catversion (because of tablespace conflict), and a pre-9.0
    migration to 9.0 has not large object permissions to migrate. In
    summary, it didn't seem worth the risk, and was hard to test.

    ---------------------------------------------------------------------------

    Bruce Momjian wrote:
    Bruce Momjian wrote:
    Tom Lane wrote:
    Bruce Momjian <bruce@momjian.us> writes:
    Tom Lane wrote:
    That isn't going to work. At least not unless you start trying to force
    roles to have the same OIDs in the new installation.
    If so I can use the CREATE ROLE ... SYSID clause when doing a binary
    upgrade.
    Oh, I had forgotten we still had that wart in the grammar.
    It doesn't actually work:

    else if (strcmp(defel->defname, "sysid") == 0)
    {
    ereport(NOTICE,
    (errmsg("SYSID can no longer be specified")));
    }

    Not sure if it's better to try to make that work again than to add
    another hack in pg_upgrade_support. On the whole that's a keyword
    I'd rather see us drop someday soon.
    OK, let me work on adding it to pg_upgrade_support. Glad you saw this.
    I have fixed the bug by using pg_upgrade_support. It was a little
    complicated because you need to install the pg_upgrade_support functions
    in the super-user database so it is available when you create the users
    in the first step of restoring the pg_dumpall file.

    I am afraid we have to batckpatch this to fix to 9.0 for 9.0 to 9.0
    upgrades. It does not apply when coming from pre-9.0 because there was
    no pg_largeobject_metadata.

    For testing I did this:

    CREATE DATABASE lo;
    \c lo
    SELECT lo_import('/etc/motd');
    \set loid `psql -qt -c 'select loid from pg_largeobject' lo`
    CREATE ROLE user1;
    CREATE ROLE user2;
    -- force user2 to have a different user id on restore
    DROP ROLE user1;
    GRANT ALL ON LARGE OBJECT :loid TO user2;

    The fixed version shows:

    lo=> select * from pg_largeobject_metadata;
    lomowner | lomacl
    ----------+------------------------------------------
    10 | {postgres=rw/postgres,user2=rw/postgres}
    (1 row)

    In the broken version, 'user2' was a raw oid, obviously wrong.

    Fortunately this was found during my testing and not reported as a bug
    by a pg_upgrade user.
    --
    Bruce Momjian <bruce@momjian.us> http://momjian.us
    EnterpriseDB http://enterprisedb.com

    + It's impossible for everything to be true. +

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedJan 6, '11 at 2:28a
activeJan 8, '11 at 3:01a
posts2
users1
websitepostgresql.org...
irc#postgresql

1 user in discussion

Bruce Momjian: 2 posts

People

Translate

site design / logo © 2021 Grokbase