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/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

re: Moving a database between servers

Quoting "Michael ." <phifli@yahoo.com>:
[color=blue]
> 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)[/color]


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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

re: Moving a database between servers

"Michael ." <phifli@yahoo.com> writes:[color=blue]
> [ SQL function that references a TEMP table ][/color]
[color=blue]
> How do I get these functions to import correctly if it
> fails due to this "current_transaction" table not
> being found?[/color]

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

re: Moving a database between servers

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:[color=blue]
> "Michael ." <phifli@yahoo.com> writes:[color=green]
> > [ SQL function that references a TEMP table ][/color]
>[color=green]
> > How do I get these functions to import correctly[/color]
> if it[color=green]
> > fails due to this "current_transaction" table not
> > being found?[/color]
>
> 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[/color]


__________________________________
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org