BUG #3876: Problems migrating databases
The following bug has been logged online:
Bug reference: 3876
Logged by: Ruben Camargo Gomez
Email address: rubencamargogomez@hotmail.com
PostgreSQL version: 8.1
Operating system: Linux Red Hat Enterprice 5
Description: Problems migrating databases
Details:
Hi, frist of all thanks in advance.
I have a postgres service running in a red hat enterpise 4; this server is
8.0.13 version and is
working fine.
Now I whant to migrate my database to my new server running red hat
enterprise 5; this operating system comes with a postgres 8.1.9 pre
instaled; I did restore a backup of my database into this version but some
of my triggers and stored procedures do not work
I had this error from my log file:
ERRORROR: missing FROM-clause entry for table "tbldishot"
And this other one:
CONTEXT: SQL statement "update tblalohot set alohot_con =
coalesce(alohot_con,0)+8 where tbldishot.dishot_cod = tblalohot.dishot_cod
and tbldishot.establ_cod = 8699 and tbldishot.tiphah_cod=7 and
tblalohot.establ_cod = 7713 and tbldishot.dishot_fec >= '2007-10-23' and
tbldishot.dishot_fec < '2007-10-24'"
PL/pgSQL function "fun_reg_dis" line 34 at
execute statement
SQL statement "SELECT fun_reg_dis(cast( $1 as integer),cast( $2
as integer),cast( $3 as integer),cast( $4 as integer),cast( $5 as
integer), $6 , $7 )"
I really whant to know if I can do any thing to make my database work well
in my new server or if I have to install in this server my old postgres
version
Hi,
Dis you take a look at the Postgres release notes and at the section of
postgresql.conf on compatibility with previous releases ? (see below)
#---------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#---------------------------------------------------------------------------
# - Previous Postgres Versions -
#add_missing_from = off
#backslash_quote = safe_encoding # on, off, or safe_encoding
#default_with_oids = off
#escape_string_warning = off
#regex_flavor = advanced # advanced, extended, or basic
#sql_inheritance = on
It is hence probably possible to bypass some of your problems, giving you
time to rewrite the offending queries/functions.
----- Original Message -----
From: "Ruben Camargo Gomez" <rubencamargogomez@hotmail.com>
To: <pgsql-bugs@postgresql.org>
Sent: Tuesday, January 15, 2008 5:52 PM
Subject: [BUGS] BUG #3876: Problems migrating databases
Show quoted text
The following bug has been logged online:
Bug reference: 3876
Logged by: Ruben Camargo Gomez
Email address: rubencamargogomez@hotmail.com
PostgreSQL version: 8.1
Operating system: Linux Red Hat Enterprice 5
Description: Problems migrating databases
Details:Hi, frist of all thanks in advance.
I have a postgres service running in a red hat enterpise 4; this server is
8.0.13 version and is
working fine.Now I whant to migrate my database to my new server running red hat
enterprise 5; this operating system comes with a postgres 8.1.9 pre
instaled; I did restore a backup of my database into this version but some
of my triggers and stored procedures do not workI had this error from my log file:
ERRORROR: missing FROM-clause entry for table "tbldishot"And this other one:
CONTEXT: SQL statement "update tblalohot set alohot_con =
coalesce(alohot_con,0)+8 where tbldishot.dishot_cod = tblalohot.dishot_cod
and tbldishot.establ_cod = 8699 and tbldishot.tiphah_cod=7 and
tblalohot.establ_cod = 7713 and tbldishot.dishot_fec >= '2007-10-23' and
tbldishot.dishot_fec < '2007-10-24'"
PL/pgSQL function "fun_reg_dis" line 34 atexecute statement
SQL statement "SELECT fun_reg_dis(cast( $1 as integer),cast( $2
as integer),cast( $3 as integer),cast( $4 as integer),cast( $5 as
integer), $6 , $7 )"I really whant to know if I can do any thing to make my database work well
in my new server or if I have to install in this server my old postgres
version---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
On Jan 15, 2008 5:52 PM, Ruben Camargo Gomez
<rubencamargogomez@hotmail.com> wrote:
I had this error from my log file:
ERRORROR: missing FROM-clause entry for table "tbldishot"
CONTEXT: SQL statement "update tblalohot set alohot_con =
coalesce(alohot_con,0)+8 where tbldishot.dishot_cod = tblalohot.dishot_cod
and tbldishot.establ_cod = 8699 and tbldishot.tiphah_cod=7 and
tblalohot.establ_cod = 7713 and tbldishot.dishot_fec >= '2007-10-23' and
tbldishot.dishot_fec < '2007-10-24'"
PL/pgSQL function "fun_reg_dis" line 34 at
Prior to 8.1, PostgreSQL adds tables missing in the FROM clause
automatically. This behaviour has been removed because it could lead
to unexpected results.
You should fix your query by using the UPDATE FROM syntax and by
having tbldishot in the FROM clause. You have several enlightening
examples in http://www.postgresql.org/docs/8.1/static/sql-update.html
.
You can also use add_missing_from = on in your postgresql.conf if you
really can't fix your query but it's not recommended.
--
Guillaume