Thread: Moving a database between servers
I am trying to move a postgresql database from one
server to another. The original server is 7.1.3, and
the new one is 7.3.4.
I went on the old and used the command:
pg_dumpall > dump
On the new:
psql -e < dump
I get this for multiple functions. (Error follows
surrounded by *****'s)
CREATE FUNCTION "transaction_visit" (integer) RETURNS
integer AS '
select visit_id
from (
select *
from visit_history as vh
where sequence = (
select max(sequence)
from visit_history
where vh.firm_id = firm_id
and vh.patient_id =
patient_id
and vh.visit_id = visit_id
)
) as v
where v.firm_id = (select firm_id from
current_transaction)
and v.patient_id = $1
and v.person_id = (select person_id from
current_transaction)
and v.inked is null
' LANGUAGE 'sql';
****ERROR: Relation "current_transaction" does not
exist****
The current_transaction is a TEMPORARY table created
like this:
CREATE FUNCTION "open_session" (integer,integer)
RETURNS integer AS '
create temporary table current_transaction as
select $1 as firm_id,
$2 as person_id,
timeofday()::timestamp as
transaction_timestamp,
timeofday()::timestamp as
history_timestamp;
select 1;
' LANGUAGE 'sql';
How do I get these functions to import correctly if it
fails due to this "current_transaction" table not
being found? (only due to it being a temporarily table
created by a different command)
__________________________________
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/
Quoting "Michael ." <phifli@yahoo.com>: > I am trying to move a postgresql database from one > server to another. The original server is 7.1.3, and > the new one is 7.3.4. > > I went on the old and used the command: > pg_dumpall > dump > > On the new: > psql -e < dump > > I get this for multiple functions. (Error follows > surrounded by *****'s) > > CREATE FUNCTION "transaction_visit" (integer) RETURNS > integer AS ' > select visit_id > from ( > select * > from visit_history as vh > where sequence = ( > select max(sequence) > from visit_history > where vh.firm_id = firm_id > and vh.patient_id = > patient_id > and vh.visit_id = visit_id > ) > ) as v > where v.firm_id = (select firm_id from > current_transaction) > and v.patient_id = $1 > and v.person_id = (select person_id from > current_transaction) > and v.inked is null > ' LANGUAGE 'sql'; > > ****ERROR: Relation "current_transaction" does not > exist**** > > > The current_transaction is a TEMPORARY table created > like this: > CREATE FUNCTION "open_session" (integer,integer) > RETURNS integer AS ' > create temporary table current_transaction as > select $1 as firm_id, > $2 as person_id, > timeofday()::timestamp as > transaction_timestamp, > timeofday()::timestamp as > history_timestamp; > select 1; > ' LANGUAGE 'sql'; > > > > > > How do I get these functions to import correctly if it > fails due to this "current_transaction" table not > being found? (only due to it being a temporarily table > created by a different command) I had problem moving form 7.1.3 to 7.4 too. The easiest thing to do is to (if you can) use 7.4's pg_dump. Just setup the TCP/IP connectivity if you don't have it already and dump using a superuser account. -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com ____________________________________ This email account is being host by: VCSN, Inc : http://vcsn.com
"Michael ." <phifli@yahoo.com> writes:
> [ SQL function that references a TEMP table ]
> How do I get these functions to import correctly if it
> fails due to this "current_transaction" table not
> being found?
I don't think there is any good solution in 7.3, because it will insist
on trying to validate the function body. A hack workaround is to create
the "current_transaction" table (as a plain, not TEMP, table), then
import your dump, then drop the table.
7.4 has a better solution (you can turn off function body checking in
CREATE FUNCTION), and 7.4's pg_dump will use it.
regards, tom lane
Unfortunately I do not have the ability to update to 7.4, although I did try the quick "hack." It worked for a few functions but later on in the import it begins to say that current_transaction does not exist again. Could you think of any reasonf or this? I don't see any sort of DROP/DELETE for this table. (The one I added as a hack to get everytihng to import correctly, that is...) Appreciate the help, Mike --- Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Michael ." <phifli@yahoo.com> writes: > > [ SQL function that references a TEMP table ] > > > How do I get these functions to import correctly > if it > > fails due to this "current_transaction" table not > > being found? > > I don't think there is any good solution in 7.3, > because it will insist > on trying to validate the function body. A hack > workaround is to create > the "current_transaction" table (as a plain, not > TEMP, table), then > import your dump, then drop the table. > > 7.4 has a better solution (you can turn off function > body checking in > CREATE FUNCTION), and 7.4's pg_dump will use it. > > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster __________________________________ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/