[ADMIN] problems with pg

Kuhn, Dylan K (4520500D)

unread,

Jul 15, 2003, 10:59:13 AM7/15/03

to

This is a multi-part message in MIME format.

------_=_NextPart_001_01C34AE1.1D2DA270
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I have a 200GB archive I created like this:

% pg_dump -f db1.pga -Fc -b db1

To check that it is good, I tried to restore to another database:

% pg_restore -d db2 db1.pga
[...quickly restores sequences, then...]
pg_restore: [archiver (db)] could not execute query: ERROR: function plpgsq=
l_call_handler already exists with same argument types

Ok, I had installed PLPGSQL in template1, which has been useful in other si=
tuations. I drop db2, recreate it, and drop the PLPGSQL language and call =
handler, then try again:

% pg_restore -d db2 db1.pga
[...restores seqs, tables, keys, then spends several quiet hours (on blobs =
I assume)...]
pg_restore: [archiver (db)] could not execute query: ERROR: Database commen=
ts may only be applied to the current database

I'm not sure how to get around this one. Can an archived database with com=
ments be restored to a database with a different name? Are the comments th=
e only thing missing from the restored database, or could there be other th=
ings?

thanks,
Dylan Kuhn

------_=_NextPart_001_01C34AE1.1D2DA270
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; charset=3Diso-8859-=
1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version 6.0.5770.91">
<TITLE>problems with pg_restore</TITLE>
</HEAD>
<BODY>

I have a 200GB archive I created like this=
:

% pg_dump -f db1.pga -Fc -b db1

To check that it is good, I tried to resto=
re to another database:

% pg_restore -d db2 db1.pga

[...quickly restores sequences, then...]<=
/FONT>

pg_restore: [archiver (db)] could not exe=
cute query: ERROR: function plpgsql_call_handler already exists with same a=
rgument types

Ok, I had installed PLPGSQL in template1, =
which has been useful in other situations.  I drop db2, recreate it, a=
nd drop the PLPGSQL language and call handler, then try again:

% pg_restore -d db2 db1.pga

[...restores seqs, tables, keys, then spe=
nds several quiet hours (on blobs I assume)...]

pg_restore: [archiver (db)] could not exe=
cute query: ERROR: Database comments may only be applied to the current dat=
abase

I'm not sure how to get around this one.&n=
bsp; Can an archived database with comments be restored to a database with =
a different name?  Are the comments the only thing missing from the re=
stored database, or could there be other things?

thanks,

Dylan Kuhn

</BODY>
</HTML>
------_=_NextPart_001_01C34AE1.1D2DA270--

Tom Lane

unread,

Jul 15, 2003, 12:29:41 PM7/15/03

to

"Kuhn, Dylan K (4520500D)" <Dylan...@navy.mil> writes:
> [ tries to restore a dump into a database with a different name ]

> pg_restore: [archiver (db)] could not execute query: ERROR: Database commen=
> ts may only be applied to the current database

> I'm not sure how to get around this one. Can an archived database with com=
> ments be restored to a database with a different name?

Hm. Evidently not :-(. The COMMENT ON DATABASE facility is a bit bogus
anyway (since there's no way to make the comments visible across
databases). You might be best advised not to use it.

Hackers: this seems like an extremely bad side-effect of what we thought
was a simple addition of a helpful check. I am thinking we should
either remove the check again, or downgrade it to a WARNING (though I'm
not quite sure how to phrase the warning ...). Any thoughts?

regards, tom lane

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

http://archives.postgresql.org

Kuhn, Dylan K (4520500D)

unread,

Jul 15, 2003, 12:58:08 PM7/15/03

to

=20
> Hm. Evidently not :-(. The COMMENT ON DATABASE facility is=20

> a bit bogus
> anyway (since there's no way to make the comments visible across
> databases). You might be best advised not to use it.

I agree. I plan to delete it and try again, and avoid using database comme=
nts in the future. This seems to work:

DELETE FROM ONLY pg_description
WHERE pg_description.objoid=3D
(SELECT pg_database.oid FROM pg_database WHERE pg_database.datname=3Dc=
urrent_database())
AND pg_description.classoid=3D
(SELECT pg_class.relfilenode FROM pg_class WHERE pg_class.relname=3D'p=
g_database');

> (though I'm
> not quite sure how to phrase the warning ...). Any thoughts?

I would have been happy enough with the existing message as a warning - pro=
bably anyone who tries to use database comments has noticed the limitations.

-dylan-

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Tom Lane

unread,

Jul 15, 2003, 4:08:03 PM7/15/03

to

Rod Taylor <r...@rbt.ca> writes:
>> Hackers: this seems like an extremely bad side-effect of what we thought
>> was a simple addition of a helpful check. I am thinking we should

>> either remove the check again, or downgrade it to a WARNING (though I'm

>> not quite sure how to phrase the warning ...). Any thoughts?

> How about going the other way and removing the requirement to explicitly
> state the database?
> COMMENT ON DATABASE IS 'This comment is on the current database.';

Won't help us for reading existing pg_dump scripts, although perhaps it
would be useful going forward.

Given the current implementation, it seems like there are three possible
behaviors for COMMENT ON DATABASE when the database name isn't the same
as the current database:

1. Raise error (what we're doing now). Simple but breaks dump scripts
for the restore-into-different-DB scenario.

2. Do nothing, store the comment in the current DB's pg_description
(what we did in 7.2). Now that I think about it, this also fails
for different-database restore, since very possibly the attempt
to look up the DB name will fail --- you'll get a no-such-database
error instead of the present error, but it's still unhelpful.

3. Ignore the specified DB name, store the comment as the description
of the current DB; possibly give a warning saying we're doing so.
This would allow correct restoration of dumps into different DBs,
but I think people would find it awfully surprising :-(

regards, tom lane

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

Kuhn, Dylan K (4520500D)

unread,

Jul 15, 2003, 4:31:46 PM7/15/03

to

> Given the current implementation, it seems like there are=20
> three possible
> behaviors for COMMENT ON DATABASE when the database name=20

> isn't the same
> as the current database:

>=20

> 1. Raise error (what we're doing now). Simple but breaks dump scripts
> for the restore-into-different-DB scenario.

>=20

> 2. Do nothing, store the comment in the current DB's pg_description
> (what we did in 7.2). Now that I think about it, this also fails
> for different-database restore, since very possibly the attempt
> to look up the DB name will fail --- you'll get a no-such-database
> error instead of the present error, but it's still unhelpful.

>=20

> 3. Ignore the specified DB name, store the comment as the description
> of the current DB; possibly give a warning saying we're doing so.
> This would allow correct restoration of dumps into different DBs,
> but I think people would find it awfully surprising :-(

>=20

The behavior and syntax of COMMENT ON DATABASE was surprising to me from th=
e start. IMHO, a warning that doesn't affect my current transaction is a m=
ore pleasant surprise than an error that aborts it. It would be nice if th=
ere was an easier way to undo a mistake, but again I would rather finish my=
transaction with an incorrect comment than have to do it over again, so I'=
d take door #3.

Could there be a #4, drop support for COMMENT ON DATABASE, ignoring it and =
issuing a warning that it is no longer supported? It's hard to argue that =
it's very useful as it stands, and you've already recommended against using=
it.

-dylan-=20=20

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

[ADMIN] problems with pg_restore

Kuhn, Dylan K (4520500D)

Tom Lane

Kuhn, Dylan K (4520500D)

Tom Lane

Kuhn, Dylan K (4520500D)