No title

On Mon, 9 Sep 2002, Laurette Cisneros wrote:

I am trying move my development database to 7.3b1.

However, when I try to restore from a 7.2.2 dump to the 7.3.b1 server I get
the following error:

pg_restore -U nbadmin -h lnc -p 5432 -d stats -Fc /tmp/stats.pgdmp

pg_restore: [archiver (db)] could not execute query: ERROR: function
plpgsql_call_handler() does not return type language_handler

I sounds like there's a language installed on your 7.2.2 server that your
7.3 server doesn't have installed.

On Mon, 2002-09-09 at 21:34, Laurette Cisneros wrote:

I am trying move my development database to 7.3b1.

However, when I try to restore from a 7.2.2 dump to the 7.3.b1 server I get
the following error:

pg_restore -U nbadmin -h lnc -p 5432 -d stats -Fc /tmp/stats.pgdmp

pg_restore: [archiver (db)] could not execute query: ERROR: function
plpgsql_call_handler() does not return type language_handler

Any ideas?

At the moment, you have to edit the dump. Where the language handler
function is declared, change "RETURNS opaque" to "RETURNS
language_handler".

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Submit yourselves therefore to God. Resist the devil,
and he will flee from you." James 4:7

Thanks!

On 9 Sep 2002, Oliver Elphick wrote:

On Mon, 2002-09-09 at 21:34, Laurette Cisneros wrote:

I am trying move my development database to 7.3b1.

However, when I try to restore from a 7.2.2 dump to the 7.3.b1 server I get
the following error:

pg_restore -U nbadmin -h lnc -p 5432 -d stats -Fc /tmp/stats.pgdmp

pg_restore: [archiver (db)] could not execute query: ERROR: function
plpgsql_call_handler() does not return type language_handler

Any ideas?

At the moment, you have to edit the dump. Where the language handler
function is declared, change "RETURNS opaque" to "RETURNS
language_handler".

--
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
----------------------------------
A wiki we will go...

Ok, am I missing somethig here?

In 7.3, the -Fp option has been removed which leaves the -Fc (which we use
in our 7.2 dumps) or -Ft.

How does one edit a compressed or tar file?

Also, is this problem going to be fixed in a later beta or regular release
of 7.3? This could pose a problem to restore full database dumps.

Thanks,

L.
On 9 Sep 2002, Oliver Elphick wrote:

On Mon, 2002-09-09 at 21:34, Laurette Cisneros wrote:

I am trying move my development database to 7.3b1.

However, when I try to restore from a 7.2.2 dump to the 7.3.b1 server I get
the following error:

pg_restore -U nbadmin -h lnc -p 5432 -d stats -Fc /tmp/stats.pgdmp

pg_restore: [archiver (db)] could not execute query: ERROR: function
plpgsql_call_handler() does not return type language_handler

Any ideas?

At the moment, you have to edit the dump. Where the language handler
function is declared, change "RETURNS opaque" to "RETURNS
language_handler".

--
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
----------------------------------
A wiki we will go...

Ok, I made the changes in the compressed pg_dump file. Now pg_restore crashes:

pg_restore: [archiver] out of memory

*sigh*

L.
On 9 Sep 2002, Oliver Elphick wrote:

On Mon, 2002-09-09 at 21:34, Laurette Cisneros wrote:

I am trying move my development database to 7.3b1.

However, when I try to restore from a 7.2.2 dump to the 7.3.b1 server I get
the following error:

pg_restore -U nbadmin -h lnc -p 5432 -d stats -Fc /tmp/stats.pgdmp

pg_restore: [archiver (db)] could not execute query: ERROR: function
plpgsql_call_handler() does not return type language_handler

Any ideas?

At the moment, you have to edit the dump. Where the language handler
function is declared, change "RETURNS opaque" to "RETURNS
language_handler".

--
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
----------------------------------
A wiki we will go...

At 03:54 PM 9/09/2002 -0700, Laurette Cisneros wrote:

Ok, I made the changes in the compressed pg_dump file.

That's probably a very bad idea.

It's a little more long-winded, but try:

pg_restore -l dumpfile > dump1.lis

copy dump1.lis to dump2.lis

delete everything from dump1.lis at and after the definition that causes
the problem.

delete everything from dump2.lis at and before the definition that causes
the problem.

pg_restore -L dump1.lis

manually define the language

pg_restore -L dump2.lis

ALTERNATIVELY, define the language in template1, then just edit dump1.lis
to remove the line for the language definition, and run pg_restore -L
dump1.lis.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

On Tue, 2002-09-10 at 00:50, Philip Warner wrote:

ALTERNATIVELY, define the language in template1, then just edit dump1.lis
to remove the line for the language definition, and run pg_restore -L
dump1.lis.

That doesn't work for a dump and reload, because 7.3's pg_dumpall writes
a script to create the databases from template0 rather than template1.

The 7.3 documentation for pg_dump says:

Notes

If your installation has any local additions to the template1
database, be careful to restore the output of pg_dump into a truly
empty database; otherwise you are likely to get errors due to
duplicate definitions of the added objects. To make an empty
database without any local additions, copy from template0 not
template1, for example:

CREATE DATABASE foo WITH TEMPLATE = template0;

but this seems to be out of date. pg_dumpall actually uses template0
itself.

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Draw near to God and he will draw near to you.
Cleanse your hands, you sinners; and purify your
hearts, you double minded." James 4:8

I am confused. This wording seems fine to me.

---------------------------------------------------------------------------

Oliver Elphick wrote:

On Tue, 2002-09-10 at 00:50, Philip Warner wrote:

ALTERNATIVELY, define the language in template1, then just edit dump1.lis
to remove the line for the language definition, and run pg_restore -L
dump1.lis.

That doesn't work for a dump and reload, because 7.3's pg_dumpall writes
a script to create the databases from template0 rather than template1.

The 7.3 documentation for pg_dump says:

Notes

If your installation has any local additions to the template1
database, be careful to restore the output of pg_dump into a truly
empty database; otherwise you are likely to get errors due to
duplicate definitions of the added objects. To make an empty
database without any local additions, copy from template0 not
template1, for example:

CREATE DATABASE foo WITH TEMPLATE = template0;

but this seems to be out of date. pg_dumpall actually uses template0
itself.

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Draw near to God and he will draw near to you.
Cleanse your hands, you sinners; and purify your
hearts, you double minded." James 4:8

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

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

I do this to begin with (createdb -T template0 db).

FYI: Here's what I've determined is the best thing to do:

1. create the database from template0
2. create the needed languages (plpgsql, plperl, plpython) in the database
3. create the needed tables, functions, types, etc. from script files.
4. restore only the data from the dump.

Seems to be the "easiest" and safest way to convert the database(s) to
7.3b1 (we have a mirad of databases for different needs each having their
own set of types, functions and languages that they use). I'll let you
know if I run into problems with this - as this, in my opinion, should not!

Thanks to all for the help,

L.
On Tue, 10 Sep 2002, Bruce Momjian wrote:

I am confused. This wording seems fine to me.

---------------------------------------------------------------------------

Oliver Elphick wrote:

On Tue, 2002-09-10 at 00:50, Philip Warner wrote:

ALTERNATIVELY, define the language in template1, then just edit dump1.lis
to remove the line for the language definition, and run pg_restore -L
dump1.lis.

That doesn't work for a dump and reload, because 7.3's pg_dumpall writes
a script to create the databases from template0 rather than template1.

The 7.3 documentation for pg_dump says:

Notes

If your installation has any local additions to the template1
database, be careful to restore the output of pg_dump into a truly
empty database; otherwise you are likely to get errors due to
duplicate definitions of the added objects. To make an empty
database without any local additions, copy from template0 not
template1, for example:

CREATE DATABASE foo WITH TEMPLATE = template0;

but this seems to be out of date. pg_dumpall actually uses template0
itself.

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Draw near to God and he will draw near to you.
Cleanse your hands, you sinners; and purify your
hearts, you double minded." James 4:8

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

--
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
----------------------------------
A wiki we will go...

On Tue, 2002-09-10 at 18:38, Bruce Momjian wrote:

I am confused. This wording seems fine to me.

The confusion was mine. Of course, pg_dump doesn't create the
database. I was mixing it up with pg_dumpall.

However, there is a problem in that recent changes have made it quite
likely that an upgrade will fail and will requre the dump script to be
edited. There are some issues in pg_dump / pg_dumpall that need
addressing before final release.

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Draw near to God and he will draw near to you.
Cleanse your hands, you sinners; and purify your
hearts, you double minded." James 4:8

Oliver Elphick wrote:

On Tue, 2002-09-10 at 18:38, Bruce Momjian wrote:

I am confused. This wording seems fine to me.

The confusion was mine. Of course, pg_dump doesn't create the
database. I was mixing it up with pg_dumpall.

However, there is a problem in that recent changes have made it quite
likely that an upgrade will fail and will requre the dump script to be
edited. There are some issues in pg_dump / pg_dumpall that need
addressing before final release.

OK, can you specifically list them?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Tue, 2002-09-10 at 23:09, Bruce Momjian wrote:

Oliver Elphick wrote:

edited. There are some issues in pg_dump / pg_dumpall that need
addressing before final release.

OK, can you specifically list them?

Message yesterday to pgsql-hackers

Subject: [HACKERS] pg_dump problems in upgrading
Date: 09 Sep 2002 12:31:39 +0100
Message-Id: <1031571099.24419.199.camel@linda>

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Draw near to God and he will draw near to you.
Cleanse your hands, you sinners; and purify your
hearts, you double minded." James 4:8

Oliver Elphick <olly@lfix.co.uk> writes:

However, there is a problem in that recent changes have made it quite
likely that an upgrade will fail and will requre the dump script to be
edited. There are some issues in pg_dump / pg_dumpall that need
addressing before final release.

AFAIK, we did what we could on that front in 7.2.1. If you have ideas
on how we can retroactively make things better, I'm all ears ...

regards, tom lane

On Tuesday 10 September 2002 11:43 pm, Tom Lane wrote:

Oliver Elphick <olly@lfix.co.uk> writes:

However, there is a problem in that recent changes have made it quite
likely that an upgrade will fail and will requre the dump script to be
edited. There are some issues in pg_dump / pg_dumpall that need
addressing before final release.

AFAIK, we did what we could on that front in 7.2.1. If you have ideas
on how we can retroactively make things better, I'm all ears ...

So this release is going to be the royal pain release to upgrade to? Not
good. People may just not upgrade at all in that case.

My datasets aren't complicated enough to trigger some of these problems;
people who have complex datasets need to report all failures so that we can
at least write a sed/perl/awk script to massage the things that need
massaging, if it can be done that easily.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Lamar Owen <lamar.owen@wgcr.org> writes:

On Tuesday 10 September 2002 11:43 pm, Tom Lane wrote:

AFAIK, we did what we could on that front in 7.2.1. If you have ideas
on how we can retroactively make things better, I'm all ears ...

So this release is going to be the royal pain release to upgrade to?

pg_dumpall from a 7.2 db, and reload into 7.2, is broken if you have
mixed-case DB names. AFAIK it's okay if you use a later-than-7.2
pg_dumpall, or reload with a later-than-7.2 psql. If Oliver's got
info to the contrary then he'd better be more specific about what
he thinks should be fixed for 7.3. Griping about the fact that 7.2.0
is broken is spectacularly unproductive at this point.

regards, tom lane

On Wed, 2002-09-11 at 05:20, Tom Lane wrote:

Lamar Owen <lamar.owen@wgcr.org> writes:

On Tuesday 10 September 2002 11:43 pm, Tom Lane wrote:

AFAIK, we did what we could on that front in 7.2.1. If you have ideas
on how we can retroactively make things better, I'm all ears ...

So this release is going to be the royal pain release to upgrade to?

pg_dumpall from a 7.2 db, and reload into 7.2, is broken if you have
mixed-case DB names. AFAIK it's okay if you use a later-than-7.2
pg_dumpall, or reload with a later-than-7.2 psql. If Oliver's got
info to the contrary then he'd better be more specific about what
he thinks should be fixed for 7.3. Griping about the fact that 7.2.0
is broken is spectacularly unproductive at this point.

I ran pg_dumpall from 7.3 on the 7.2 database. So I am talking about
the pg_dump that is now being beta-tested. Because of the major changes
in 7.3, the 7.2 dump is not very useful. I am *not* complaining about
7.2's pg_dump!

Let me reiterate. I got these problems dumping 7.2 data with 7.3's
pg_dumpall:

1. The language handlers were dumped as opaque; that needs to be
changed to language_handler.

2. The dump produced:
CREATE TABLE cust_alloc_history (
...
"year" integer DEFAULT date_part('year'::text,
('now'::text)::timestamp(6) with time zone) NOT NULL,
...
ERROR: Column "year" is of type integer but default expression is
of type double precision
You will need to rewrite or cast the expression

3. A view was created before one of the tables to which it referred.

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"I am crucified with Christ; nevertheless I live; yet
not I, but Christ liveth in me; and the life which I
now live in the flesh I live by the faith of the Son
of God, who loved me, and gave himself for me."
Galatians 2:20

-----Original Message-----
From: Oliver Elphick [mailto:olly@lfix.co.uk]
Sent: 11 September 2002 07:29
To: Tom Lane
Cc: Lamar Owen; Bruce Momjian; Philip Warner; Laurette
Cisneros; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS]

Let me reiterate. I got these problems dumping 7.2 data with 7.3's
pg_dumpall:

I wonder how many people would do something more like:

pg_dumpall > db.sql
make install
psql -e template1 < db.sql

rather than manually installing pg_dumpall from 7.3 first?

Regards, Dave.

Oliver Elphick <olly@lfix.co.uk> writes:

Let me reiterate. I got these problems dumping 7.2 data with 7.3's
pg_dumpall:

1. The language handlers were dumped as opaque; that needs to be
changed to language_handler.

Okay, we need to do something about that, though I'm not sure I see
a clean solution offhand.

2. The dump produced:
CREATE TABLE cust_alloc_history (
...
"year" integer DEFAULT date_part('year'::text,
('now'::text)::timestamp(6) with time zone) NOT NULL,
...
ERROR: Column "year" is of type integer but default expression is
of type double precision
You will need to rewrite or cast the expression

Hmm ... what was the original coding of the default?

3. A view was created before one of the tables to which it referred.

This has been a problem all along and will continue to be a problem
for awhile longer. Sorry.

regards, tom lane

On Wed, 2002-09-11 at 14:59, Tom Lane wrote:

Oliver Elphick <olly@lfix.co.uk> writes:

Let me reiterate. I got these problems dumping 7.2 data with 7.3's
pg_dumpall:

1. The language handlers were dumped as opaque; that needs to be
changed to language_handler.

Okay, we need to do something about that, though I'm not sure I see
a clean solution offhand.

In 7.2, this will identify the functions that need to be dumped as
language handlers:

junk=# SELECT p.proname
junk-# FROM pg_proc AS p, pg_language AS l
junk-# WHERE l.lanplcallfoid = p.oid AND l.lanplcallfoid != 0;
proname
----------------------
plperl_call_handler
plpgsql_call_handler
pltcl_call_handler
(3 rows)

2. The dump produced:
CREATE TABLE cust_alloc_history (
...
"year" integer DEFAULT date_part('year'::text,
('now'::text)::timestamp(6) with time zone) NOT NULL,
...
ERROR: Column "year" is of type integer but default expression is
of type double precision
You will need to rewrite or cast the expression

Hmm ... what was the original coding of the default?

year INTEGER DEFAULT date_part('year',CURRENT_TIMESTAMP)

3. A view was created before one of the tables to which it referred.

This has been a problem all along and will continue to be a problem
for awhile longer. Sorry.

Is it not enough to defer all views until the end? Why would they be
needed any sooner?

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"I am crucified with Christ; nevertheless I live; yet
not I, but Christ liveth in me; and the life which I
now live in the flesh I live by the faith of the Son
of God, who loved me, and gave himself for me."
Galatians 2:20