Re: [Gnumed-devel] pg_upgrade problem on Windows XP and Windows 7

[Karsten Hilbert]

> Am Montag, 18. November 2013, 22:51:00 schrieb Karsten Hilbert:
> > I tried to use pg_upgrade but guess what it does not work. Here is the error
> > I have no idea how to fix this. 
> > c:\Windows\Temp>pg_upgrade.exe --old-datadir "C:/Program Files
> > (x86)/PostgresPlu s/8.4SS/data" --new-datadir "C:/Program Files
> > (x86)/PostgreSQL/9.3/data" --old-b indir "C:/Program Files
> > (x86)/PostgresPlus/8.4SS/bin" --new-bindir "C:/Program F iles
> > (x86)/PostgreSQL/9.3/bin"
> >  
> > SQL command failed
> > CREATE TEMPORARY TABLE info_rels (reloid) AS SELECT c.oid FROM
> > pg_catalog.pg_cla ss c JOIN pg_catalog.pg_namespace n        ON
> > c.relnamespace = n.oid LEFT OUTER JOIN pg_catalog.pg_index i         ON
> > c.oid = i.indexrelid WHERE relkind IN ('r' , 'm', 'i', 'S') AND 
> > i.indisvalid IS DISTINCT FROM false AND  i.indisready IS D ISTINCT FROM
> > false AND   ((n.nspname !~ '^pg_temp_' AND     n.nspname !~ '^pg_to
> > ast_temp_' AND     n.nspname NOT IN ('pg_catalog', 'information_schema',
> > 'binary_upgrade', 'pg_toast') AND c.oid >= 16384)   OR (n.nspname =
> > 'pg_catalog' AND     relname IN ('pg_largeob ject',
> > 'pg_largeobject_loid_pn_index') ));
> > ERROR:  transaction is read-only
> > 
> > 
> > Well, this is a bug in pg_upgrade and should be reported as such.
> > 
> > Since databases CAN be readonly for default transactions pg_upgrade
> > needs to deal with that.
> > 
> Great. Wasted hours on this.

Sorry to hear that. For the record this likely is not a *known* bug.
Also, some might argue it is not a bug at all (since pg_upgrade is
working just fine, except under certain environmental conditions -
such as a default-readonly database). I do think it is at least a
deficiency since pg_upgrade is a PG-internal tool which should work
under any PG-internal circumstances.

I guess I'll raise the issue on a PG mailing list and see
what they think.

> Found a workaround which actually produces a working database.

One might

  ALTER DATABASE SET DEFAULT_TRANSACTION_READ_ONLY to off/on;

before/after the pg_upgrade run.

Karsten