[BUGS] pg_upgrade 9.0->9.2 failure: Mismatch of relation OID in database
On upgrading a 9.0 database to 9.2 using pg_upgrade, I got this:
# pg_upgradecluster -m upgrade 9.0 main /psql/data-9.2
[...]
Performing Upgrade
--
[...]
Restoring database schema to new clusterok
Removing support functions from new cluster ok
Copying user relation files
Mismatch of relation OID in database "hisrm": old OID 18804, new OID 18803
Failure, exiting
Error: pg_upgrade run failed
This is a cluster that was running with 9.0.12 (compiled locally). For
the upgrade, I installed postgresql-9.0 and -9.2 from
apt.postgresql.org (9.0.13, 9.2.4), so pg_upgrade was using these
versions. OS is Ubuntu 12.04 amd64 now and was 8.04 while the cluster
was still running on 9.0.12.
In the 9.0 cluster, 18804 is the relation oid of glm_lrahm_to_se.
pg_upgrade_dump_all.sql:
--
-- Name: glm_lrahm_to_se; Type: TABLE; Schema: mbs; Owner: fsv; Tablespace:
--
-- For binary upgrade, must preserve pg_type oid
SELECT binary_upgrade.set_next_pg_type_oid('18806'::pg_catalog.oid);
-- For binary upgrade, must preserve pg_type array oid
SELECT binary_upgrade.set_next_array_pg_type_oid('18805'::pg_catalog.oid);
-- For binary upgrade, must preserve pg_class oids
SELECT binary_upgrade.set_next_heap_pg_class_oid('18804'::pg_catalog.oid);
CREATE TABLE glm_lrahm_to_se (
id integer NOT NULL,
lrahm integer NOT NULL,
se integer NOT NULL
);
-- For binary upgrade, set heap's relfrozenxid
UPDATE pg_catalog.pg_class
SET relfrozenxid = '14118'
WHERE oid = 'glm_lrahm_to_se'::pg_catalog.regclass;
ALTER TABLE mbs.glm_lrahm_to_se OWNER TO fsv;
pg_upgrade_restore.sql:
SELECT binary_upgrade.set_next_pg_type_oid('18806'::pg_catalog.oid);
set_next_pg_type_oid
--
(1 Zeile)
SELECT binary_upgrade.set_next_array_pg_type_oid('18805'::pg_catalog.oid);
set_next_array_pg_type_oid
(1 Zeile)
SELECT binary_upgrade.set_next_heap_pg_class_oid('18804'::pg_catalog.oid);
set_next_heap_pg_class_oid
(1 Zeile)
CREATE TABLE glm_lrahm_to_se (
id integer NOT NULL,
lrahm integer NOT NULL,
se integer NOT NULL
);
CREATE TABLE
UPDATE pg_catalog.pg_class
SET relfrozenxid = '14118'
WHERE oid = 'glm_lrahm_to_se'::pg_catalog.regclass;
UPDATE 1
ALTER TABLE mbs.glm_lrahm_to_se OWNER TO fsv;
ALTER TABLE
(I can provide more info on request.)
Christoph
--
c...@df7cb.de | http://www.df7cb.de/
signature.asc
Description: Digital signature
[BUGS] BUG #8471: subquery where not being applied until outer query
The following bug has been logged on the website:
Bug reference: 8471
Logged by: Dan Rickner
Email address: dnrick...@taylor.edu
PostgreSQL version: 9.2.4
Operating system: CentOS
Description:
Our ERP stores student GPA values as a text string. I am trying to select
only valid gpa values (a number between 0 and 4.0). I have a function
called numeric that returns a bool if the value can be converted to a
number. When I run my query I get errors about values that should not be
considered in my outer where statement. The inner where is supposed to
filter out the bad data values:
-- function to return if a character string can be converted to a number
create or replace function isnumeric(varchar) returns boolean as $$
declare x numeric;
begin
x = $1::numeric;
return true;
exception when others then
return false;
end
$$
language plpgsql
immutable;
-- test table
create table tbl
(
id serial not null,
gpa varchar(6) null
);
-- insert bad data
insert into tbl (gpa) values ('A'), ('2.0'), ('12.5'), ('3.45'), (''),
('-'), ('-2.3'), ('-5');
select *
from
( -- this subquery returrns only numeric values
select a.id, trunc(a.gpa::numeric, 2) as gpa
from
(
select id, gpa, isnumeric(gpa) as num
from tbl
) as a
where a.num = true
) as b
-- filter the numeric values to the 4.0 range
where b.gpa between 0.0 and 4.0
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] select table indicate missing chunk number 0 for toast value 96635 in pg_toast_2619
David Fetter fetter.org> writes: > > Upgrade to 9.1.3 and let us know whether that fixes the problem. I've run into this issue as well on postgres 8.4.14. Aside from upgrading to a newer release is there any manual fixup that can be done ? Thanks, David -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] select table indicate missing chunk number 0 for toast value 96635 in pg_toast_2619
I have seen this issue on a slave although it was in version 9.2. I ran this select 2619::regclass; regclass -- pg_statistic (1 row) I was able to fix my select issue by running analyze on the database On Thu, Sep 26, 2013 at 11:47 AM, David Rennalls wrote: > David Fetter fetter.org> writes: > > > > Upgrade to 9.1.3 and let us know whether that fixes the problem. > > I've run into this issue as well on postgres 8.4.14. Aside from upgrading > to a > newer release is there any manual fixup that can be done ? > > Thanks, > David > > > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs >
Re: [BUGS] select table indicate missing chunk number 0 for toast value 96635 in pg_toast_2619
On Thu, Sep 26, 2013 at 3:40 PM, Kim Applegate wrote: > I have seen this issue on a slave although it was in version 9.2. I ran oh ok. Looks like the issue was fixed in 8.2.23 according to these release notes http://www.postgresql.org/docs/8.2/static/release-8-2-23.html ... o Fix race condition during toast table access from stale syscache entries (Tom Lane) The typical symptom was transient errors like "missing chunk number 0 for toast value N in pg_toast_2619", where the cited toast table would always belong to a system catalog. .. but maybe there's a different flavour of this bug ? > this > > select 2619::regclass; >regclass > -- > pg_statistic > (1 row) > > > I was able to fix my select issue by running analyze on the database Yes I tried that but didn't seem to help... mydb=# ANALYZE verbose; INFO: analyzing "pg_catalog.pg_type" INFO: "pg_type": scanned 13 of 13 pages, containing 590 live rows and 0 dead rows; 590 rows in sample, 590 estimated total rows INFO: analyzing "pg_catalog.pg_attribute" INFO: "pg_attribute": scanned 78 of 78 pages, containing 4633 live rows and 0 dead rows; 4633 rows in sample, 4633 estimated total rows INFO: analyzing "information_schema.sql_features" INFO: "sql_features": scanned 7 of 7 pages, containing 649 live rows and 0 dead rows; 649 rows in sample, 649 estimated total rows ERROR: missing chunk number 0 for toast value 33255 in pg_toast_2619 - David -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] select table indicate missing chunk number 0 for toast value 96635 in pg_toast_2619
On Thu, Sep 26, 2013 at 4:19 PM, David Rennalls wrote: > On Thu, Sep 26, 2013 at 3:40 PM, Kim Applegate wrote: >> I have seen this issue on a slave although it was in version 9.2. I ran > > oh ok. Looks like the issue was fixed in 8.2.23 according to these > release notes http://www.postgresql.org/docs/8.2/static/release-8-2-23.html > ... > o Fix race condition during toast table access from stale syscache > entries (Tom Lane) > >The typical symptom was transient errors like "missing chunk number > 0 for toast value N in pg_toast_2619", where the > cited toast table would always belong to a system catalog. > > > .. but maybe there's a different flavour of this bug ? Actually forgot to mention in my case there were filesystem issues... so that might explain it. fsck was run and did some repairs after which the error above started happening. >> this >> >> select 2619::regclass; >>regclass >> -- >> pg_statistic >> (1 row) >> >> >> I was able to fix my select issue by running analyze on the database > > Yes I tried that but didn't seem to help... > mydb=# ANALYZE verbose; > INFO: analyzing "pg_catalog.pg_type" > INFO: "pg_type": scanned 13 of 13 pages, containing 590 live rows and > 0 dead rows; 590 rows in sample, 590 estimated total rows > INFO: analyzing "pg_catalog.pg_attribute" > INFO: "pg_attribute": scanned 78 of 78 pages, containing 4633 live > rows and 0 dead rows; 4633 rows in sample, 4633 estimated total rows > INFO: analyzing "information_schema.sql_features" > INFO: "sql_features": scanned 7 of 7 pages, containing 649 live rows > and 0 dead rows; 649 rows in sample, 649 estimated total rows > ERROR: missing chunk number 0 for toast value 33255 in pg_toast_2619 ..Tried reindexing pg_statistic based on some other posts I saw, but was getting this error... mydb=# REINDEX TABLE pg_statistic; ERROR: could not create unique index "pg_statistic_relid_att_index" DETAIL: Table contains duplicated values. ..came across this thread http://www.spinics.net/lists/pgsql-admin/msg05911.html and I tried simply deleting all the stuff in pg_statistic, reindex then vacuum and that seems to have worked. mydb=# delete from pg_statistic; DELETE 610 mydb=# reindex table pg_statistic; REINDEX mydb=# vacuum analyze; - David -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8455: spanish pgadmin3.mo
On Mon, Sep 16, 2013 at 08:40:57AM +, j.rom...@salsa.es wrote: > The following bug has been logged on the website: > > Bug reference: 8455 > Logged by: Jesus Romero > Email address: j.rom...@salsa.es > PostgreSQL version: 9.1.9 > Operating system: Ubuntu server 12.04 > Description: > > The actual version of pgadmin3 1.18 includes a wrong file pgadmin3.mo for > the spanish languaje. The file included is catalan languaje not the spanish > one. You should report this to the pgadmin developers on one of their email lists: http://www.pgadmin.org/support/list.php -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #8472: could not find a \"%s\" to execute. When Directory On %PATH% Has Double Quotes.
The following bug has been logged on the website: Bug reference: 8472 Logged by: Luis Gonzalo Constantini Rickel Email address: gonc...@gmail.com PostgreSQL version: 9.2.4 Operating system: Windows Description: Hi, I think that I found the reason why in Windows you receive the message 'could not find a \"%s\" to execute' (eg. 'could not find a "psql" to execute') when you execute any of the Postgresql tools in the command line (psql, pg_config, etc.). If the %PATH% environment variable has double quotes for the directory where postgres is installed (eg. "C:\Program Files (x86)\PostgreSQL\9.2\bin", that is valid for Windos), the function int find_my_exec(const char *argv0, char *retpath) (in exec.c); interpret incorrectly the file path, it try to check the file like eg.: "C:\Program Files (x86)\PostgreSQL\9.2\bin"\psql.exe instead of: C:\Program Files (x86)\PostgreSQL\9.2\bin\psql.exe or: "C:\Program Files (x86)\PostgreSQL\9.2\bin\psql.exe" This bug coud be duplicated in this way: 1). Add the Directory with double quotes to the %PATH% environment variable. 2). execute psql. 3). You receive the message 'could not find a "psql" to execute'. 4). Add the Directory without double quotes to the %PATH% environment variable. 5). execute psql. 6). You do not receive the message 'could not find a "psql" to execute'. I hope this information is useful for you. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
