Re: [BUGS] pg_upgrade issues
I have encountered another problem with pg_upgrade, while migrating from 8.4 to 9.0 (beta2, as well as beta3) on Windows XP Pro. I have a table with a regclass column, which references other tables in the same database: CREATE TABLE common_inst.reg_asset ( asset_id integer NOT NULL, table_name regclass, CONSTRAINT asset_registered_pkey PRIMARY KEY (asset_id) ) Sometimes after I migrate the database, the values in the table_name column show integer numbers (e.g. '284551' for a table named 'common_inst.asset_spot_equity_index') instead of table references. These numbers are the OIDs of the tables in the old database, but in the new database these OIDs have no referent. FWIW, when looking at the pg_class entries for the referenced tables, I have noticed that in the old database the table OID and the column relfilenode have different values. In the migrated database the values are the same and coincide with relfilenode in the old database. For example, Old database: Table name: common_inst.asset_spot_equity_index pg_class.oid = 284551 pg_class.relfilenode = 288011 Migrated database: Table name: common_inst.asset_spot_equity_index pg_class.oid = 288011 pg_class.relfilenode = 288011 I am trying to obtain a binary dump of a small test database where this issue could be reproduced, but so far, no luck. At present, the least such database is 1.5 GB compressed and contains a lot of proprietary info. I would welcome any suggestions on how to do this. Thanks, Dmitry
Re: [BUGS] BUG #5567: will not install
Alex, Yes -- I am aware that we are on an old release -- when you say - my hunch is they do not ship binaries for AIX (7.1). what binaries are you taking about --- we use the same installer and files on AIX 6.1 and it works -- it appears that there is something different in AIX 7.1 -- either something that is not longer in the AIX 7.1 OS base (is that what you where thinking) or someing in posgres that need to change to accomidate this new OS? thanks, Denise Denise Kanyuh kan...@us.ibm.com TBSM L3 Manager (919) 224-1997 Alex Hunsaker 07/22/2010 11:24 AM To Denise Kanyuh/Raleigh/i...@ibmus cc pgsql-bugs@postgresql.org Subject Re: [BUGS] BUG #5567: will not install On Thu, Jul 22, 2010 at 06:13, Denise Kanyuh wrote: > PostgreSQL version: 8.0.15 [ BTW the latest version of 8.0 is 8.0.25, also note 8.0 is getting EOL'ed within the next month or so, see http://www.postgresql.org/about/news.1214 ] > When trying to test our product which incorporates postgress on the newest > release of AIX we get this error -- will AIX 7.1 be supported on any release > of postgres? Skimming http://www.postgresql.org/docs/8.4/interactive/installation-platform-notes.html-- while it does not say anything specific about AIX 7.1, the biggest problems seem to be with old gcc versions. I would think if 7.1 ships with a decent version of gcc it will 'just work'. In any event you may want to give that page a look. > The installation failed at step: PostGreSOL Installation. > For additional information, please review the log files stored in the > archive: /opt/IBM/tivoli/tip/logs.zip > logs.zip include the initDB.out file which found a message: > # pwd > /opt/IBM/tivoli/tip/logs/tbsm > # more initDB.out > Error: Unknown architecture for shared libraries This looks like its coming from a 3rd party installer, my hunch is they do not ship binaries for AIX (7.1).
[BUGS] BUG #5569: Select in trigger don't retrive true record
The following bug has been logged online: Bug reference: 5569 Logged by: Alessio Email address: a.burga...@gmail.com PostgreSQL version: 8.4.3 build1400 Operating system: Windows 2008 Web Description:Select in trigger don't retrive true record Details: Hi, My trigger is executed on insert in a table. After some check ther is a schema change. Afeter schema change saltuary and random time the select on set FOUND = false when the record exist! if i execute a full analizy and vacum the problem is solved. var_azienda_schema := 'azienda_'|| var_azienda_codice; rec:=NULL; SELECT nspname INTO rec FROM pg_catalog.pg_namespace WHERE nspname = var_azienda_schema LIMIT 1; IF NOT FOUND THEN -- SE NON TROVO LO SCHEMA ESCO var_err:= var_trigger_name || ' ' || 'ERR4 schema '|| COALESCE(CAST(var_azienda_schema as TEXT),'NULL') ||' inesistente. CODICE='|| NEW.codice; INSERT INTO public.log(data, errore, pagina, priorita) VALUES ( NOW(), var_err, '', -1); UPDATE public.chiamata_evento_temp SET data_elaborazione = NOW(),note = var_err WHERE codice = NEW.codice; EXECUTE 'SET search_path = public'; RETURN NEW; END IF; --CAMBIO SCHEMA EXECUTE 'SET search_path = '||var_azienda_schema; -- -- FIND INFO CODICEID -- -- SE codiceid E' SETTATO CERCO LE INFORMAZIONI rec:=NULL; SELECT account_codiceid.codice_protocollo,account_codiceid.descrizione,account_codi ceid.area,account_codiceid.codice, account_codiceid.codice_account AS codice_account FROM public.account_codiceid INTO rec WHERE account_codiceid.codice = NEW.codiceid AND account_codiceid.codice_azienda = var_azienda_codice LIMIT 1 IF FOUND THEN -- 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 #5569: Select in trigger don't retrive true record
Hello your trigger is AFTER or BEFORE trigger? Because BEFORE trigger is executed before new value is propagated to table. Regards Pavel Stehule 2010/7/23 Alessio : > > The following bug has been logged online: > > Bug reference: 5569 > Logged by: Alessio > Email address: a.burga...@gmail.com > PostgreSQL version: 8.4.3 build1400 > Operating system: Windows 2008 Web > Description: Select in trigger don't retrive true record > Details: > > Hi, > > My trigger is executed on insert in a table. > After some check ther is a schema change. > Afeter schema change saltuary and random time the select on set FOUND = > false when the record exist! > if i execute a full analizy and vacum the problem is solved. > > var_azienda_schema := 'azienda_'|| var_azienda_codice; > rec:=NULL; > SELECT nspname INTO rec FROM pg_catalog.pg_namespace WHERE nspname = > var_azienda_schema LIMIT 1; > IF NOT FOUND THEN > -- SE NON TROVO LO SCHEMA ESCO > var_err:= var_trigger_name || ' ' || 'ERR4 schema '|| > COALESCE(CAST(var_azienda_schema as TEXT),'NULL') ||' inesistente. > CODICE='|| NEW.codice; > INSERT INTO public.log(data, errore, pagina, priorita) VALUES ( NOW(), > var_err, '', -1); > UPDATE public.chiamata_evento_temp SET data_elaborazione = NOW(),note = > var_err WHERE codice = NEW.codice; > EXECUTE 'SET search_path = public'; > RETURN NEW; > END IF; > --CAMBIO SCHEMA > EXECUTE 'SET search_path = '||var_azienda_schema; > > -- > -- FIND INFO CODICEID > -- > -- SE codiceid E' SETTATO CERCO LE INFORMAZIONI > rec:=NULL; > SELECT > account_codiceid.codice_protocollo,account_codiceid.descrizione,account_codi > ceid.area,account_codiceid.codice, account_codiceid.codice_account AS > codice_account FROM public.account_codiceid INTO rec WHERE > account_codiceid.codice = NEW.codiceid AND account_codiceid.codice_azienda = > var_azienda_codice LIMIT 1 > IF FOUND THEN > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs > -- 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 #5570: global hash %_SHARED does not work in the new version
The following bug has been logged online: Bug reference: 5570 Logged by: Milen Email address: m...@avangardsolutions.com PostgreSQL version: v9.0beta2 Operating system: Linux Description:global hash %_SHARED does not work in the new version Details: Hello, We can't find the global hash %_SHARED in the new version of plperl.c. The error that we receive from the server when we try to use the global hash %_SHARED is : ERROR: Global symbol %_SHARED requires explicit package name Please inform us for any progress on this issue. Thanks in advance -- 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 #5567: will not install
On 07/22/2010 07:51 PM, Denise Kanyuh wrote: Alex, Yes -- I am aware that we are on an old release -- any plans to upgrade? when you say - my hunch is they do not ship binaries for AIX (7.1). what binaries are you taking about --- we use the same installer and files on AIX 6.1 and it works -- it appears that there is something different in AIX 7.1 -- either something that is not longer in the AIX 7.1 OS base (is that what you where thinking) or someing in posgres that need to change to accomidate this new OS? so you just copied the binary files over from AIX 6.1 .have you tried what happens when you actually compile on AIX 7.1? The filename in your "installers" error suggests that initdb fails - so what happens when you do a manual initdb with your binaries? On a different note - if IBM is using PostgreSQL on AIX internally you might want to consider providing buildfarm support on different AIX versions, that would help you and the community in supporting AIX. regards Stefan -- 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 #5570: global hash %_SHARED does not work in the new version
On Fri, Jul 23, 2010 at 10:17, Milen wrote:
>
> The following bug has been logged online:
>
> Bug reference: 5570
> Logged by: Milen
> Email address: m...@avangardsolutions.com
> PostgreSQL version: v9.0beta2
> Operating system: Linux
> Description: global hash %_SHARED does not work in the new version
> Details:
>
> Hello,
> We can't find the global hash %_SHARED in the new version of plperl.c.
Huh, can we get more info? are you using plperl or plperlu? A
complete testcase would also help.
It works for me:
=> SELECT version();
version
PostgreSQL 9.0beta3 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 4.5.0 20100610 (prerelease), 64-bit
=> create or replace function perl_shared() returns void as $$
elog(INFO, $_SHARED{'stuff'});
$_SHARED{'stuff'} = '1';
for my $k (keys %_SHARED)
{
elog(INFO, $k);
}
$$ language plperl;
=> select perl_shared();
INFO:
CONTEXT: PL/Perl function "perl_shared"
INFO: stuff
CONTEXT: PL/Perl function "perl_shared"
perl_shared
-
(1 row)
=> select perl_shared();
INFO: 1
CONTEXT: PL/Perl function "perl_shared"
INFO: stuff
CONTEXT: PL/Perl function "perl_shared"
perl_shared
-
(1 row)
--
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 #5567: will not install
On 07/23/2010 07:24 PM, Stefan Kaltenbrunner wrote: On 07/22/2010 07:51 PM, Denise Kanyuh wrote: Alex, Yes -- I am aware that we are on an old release -- any plans to upgrade? when you say - my hunch is they do not ship binaries for AIX (7.1). what binaries are you taking about --- we use the same installer and files on AIX 6.1 and it works -- it appears that there is something different in AIX 7.1 -- either something that is not longer in the AIX 7.1 OS base (is that what you where thinking) or someing in posgres that need to change to accomidate this new OS? so you just copied the binary files over from AIX 6.1 .have you tried what happens when you actually compile on AIX 7.1? The filename in your "installers" error suggests that initdb fails - so what happens when you do a manual initdb with your binaries? On a different note - if IBM is using PostgreSQL on AIX internally you might want to consider providing buildfarm support on different AIX versions, that would help you and the community in supporting AIX. That seems especially important in the light of AIX 7.1 not even beeing available/released yet so it is not really possible that the community can actually do any tests on that platform or even provide an estimation on what it would take to support it... Stefan -- 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 #5563: Odd behavior with aggregate_func(DISTINCT foo ORDER BY foo)
On Fri, Jul 16, 2010 at 18:04, Daniel Grace wrote: > However, in some circumstances Postgres will fail How exactly? this is what I get: => SELECT STRING_AGG(DISTINCT t::text order by t::text) FROM foo; string_agg abc (1 row) => SELECT STRING_AGG(DISTINCT t::text order by t::text desc) FROM foo; string_agg cba (1 row) Maybe its so obvious I missed it? -- 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] page corruption after moving tablespace
On Thu, 2010-07-22 at 23:50 -0700, Jeff Davis wrote: > I think the simple fix would be to have copy_relation_data call > PageInit() if it's a new page. On second thought, why are PageSetLSN and PageSetTLI being called from log_newpage(), anyway? It says that all of the callers use smgr directly, rather than the buffer cache. Regards, Jeff Davis -- 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 #5563: Odd behavior with aggregate_func(DISTINCT foo ORDER BY foo)
On Fri, Jul 23, 2010 at 10:42 AM, Alex Hunsaker wrote: > On Fri, Jul 16, 2010 at 18:04, Daniel Grace wrote: >> However, in some circumstances Postgres will fail > > How exactly? > > Maybe its so obvious I missed it? > Please see BUG #5564 -- I accidentally submitted this one before I was finished typing the details. -- Daniel -- 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 #5563: Odd behavior with aggregate_func(DISTINCT foo ORDER BY foo)
On Fri, Jul 23, 2010 at 12:19, Daniel Grace wrote: > Please see BUG #5564 -- I accidentally submitted this one before I was > finished typing the details. Ahh, sorry for the noise. I was just trolling for bug reports that had no replys yet :-) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] installing Postgres 9.0 beta 3 fails on windows 2003 32bit
Hi, I am testing Postgres 9.0: I installed it on: - Win2008 64bit ( the 64bit version) - no problems -Win2003 32bit ( the 32bit version) - no problem on one machine, problem on another machine of the same configuration On the problem machine: Error: Problem running post-install step. Installation may not complete correctly The database cluster initialisation failed. The Data folder is empty There is no error log in the temp folder The only thing I can see is an Error in the Even Viewer that says: Faulting Application postgresql-9.0.0-beta3-windows.exe, version 1.0.0.0, faulting module TCL7.tmp, version 0.0.0.0, fault address . What prevents the initialization on the faulting machine? It has to be something on my machine rather than the installer since it works on one machine as opposed to the other. Thank you, Kasia
Re: [BUGS] pg_upgrade issues
depst...@alliedtesting.com wrote: > I have encountered another problem with pg_upgrade, while migrating > from 8.4 to 9.0 (beta2, as well as beta3) on Windows XP Pro. Wow, your testing of pg_upgrade has been excellent! I hope you can continue and test other areas of our system too. I am actually curious how you are so good at this. > I have a table with a regclass column, which references other tables > in the same database: > > CREATE TABLE common_inst.reg_asset > ( > asset_id integer NOT NULL, > table_name regclass, > CONSTRAINT asset_registered_pkey PRIMARY KEY (asset_id) > ) > > Sometimes after I migrate the database, the values in the table_name > column show integer numbers (e.g. '284551' for a table named > 'common_inst.asset_spot_equity_index') instead of table references. > These numbers are the OIDs of the tables in the old database, but in > the new database these OIDs have no referent. Ah, I never thought of the migrations issues of user tables using the reg* data types: pg_catalog | regclass| registered class pg_catalog | regconfig | registered text search configuration pg_catalog | regdictionary | registered text search dictionary pg_catalog | regoper | registered operator pg_catalog | regoperator | registered operator (with args) pg_catalog | regproc | registered procedure pg_catalog | regprocedure| registered procedure (with args) pg_catalog | regtype | registered type In fact, I never even considered that user tables would be using these data types. The basic problem is that we don't preserve most of these oids when recreating them in the new cluster --- we only preserve pg_type.oid, pg_class.relfilenode, and pg_enum.oid. > FWIW, when looking at the pg_class entries for the referenced tables, > I have noticed that in the old database the table OID and the column > relfilenode have different values. In the migrated database the values > are the same and coincide with relfilenode in the old database. > > For example, > > Old database: > > Table name: common_inst.asset_spot_equity_index > pg_class.oid = 284551 > pg_class.relfilenode = 288011 > > Migrated database: > > Table name: common_inst.asset_spot_equity_index > pg_class.oid = 288011 > pg_class.relfilenode = 288011 > > > I am trying to obtain a binary dump of a small test database where this > issue could be reproduced, but so far, no luck. At present, the least > such database is 1.5 GB compressed and contains a lot of proprietary > info. I would welcome any suggestions on how to do this. Your diagnosis is 100% on target, and very perceptive. Because we preserve pg_class.relfilenode, if the table has not been rebuilt, for example by CLUSTER, the old system the pg_class.oid and pg_class.relfilenode are the same, and hence pg_class.oid is preserved through pg_class.relfilenode during the migration. If they are different, e.g. they ran CLUSTER, pg_upgrade will be wrong because the oid has changed, and you will see the errors you are reporting. I am inclined to prevent pg_upgrade from migrating any database that uses any of these reg* data types, and document this restriction. I probably could allow regtype because that pg_type is preserved. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
