Thread: pg_dump/PgAdmin problem?
I was wondering if anyone else had problems using pg_dump on database with
PgAdmin. Specifically, if I do a pg_dump and then run the output back
through psql, it gives me the following errors:
psql:cns.sql:3697: ERROR: parser: parse error at or near "."
psql:cns.sql:3705: ERROR: Relation 'pgadmin_sequences' does not exist
psql:cns.sql:3706: ERROR: Relation 'pgadmin_sequences' does not exist
psql:cns.sql:3707: ERROR: Relation 'pgadmin_sequences' does not exist
psql:cns.sql:3715: ERROR: parser: parse error at or near "."
psql:cns.sql:3723: ERROR: Relation 'pgadmin_tables' does not exist
psql:cns.sql:3724: ERROR: Relation 'pgadmin_tables' does not exist
psql:cns.sql:3725: ERROR: Relation 'pgadmin_tables' does not exist
I've tracked down the problem and it seems it doesn't like two view
definitions (pgadmin_sequences and pgadmin_tables), and therefore the
subsequent GRANTs. The views are at the end of the email.
This hadn't been a huge problem for me (especially since I don't personally
use PgAdmin), but it worries me that I can't fully restore a database from a
backup. Has anyone else experienced this?
version
---------------------------------------------------------------------
PostgreSQL 7.1.3 on i386-unknown-freebsd4.3, compiled by GCC 2.95.3
Greg
CREATE VIEW "pgadmin_sequences" as SELECT c.oid AS sequence_oid, c.relname
AS sequence_name, pg_get_userbyid(c.relowner) AS sequence_owner, c.relacl AS
sequence_acl, pgadmin_get_sequence(c.oid).sequence_last_value AS
sequence_last_value, pgadmin_get_sequence(c.oid).sequence_increment_by AS
sequence_increment_by, pgadmin_get_sequence(c.oid).sequence_max_value AS
sequence_max_value, pgadmin_get_sequence(c.oid).sequence_min_value AS
sequence_min_value, pgadmin_get_sequence(c.oid).sequence_cache_value AS
sequence_cache_value, pgadmin_get_sequence(c.oid).sequence_is_cycled AS
sequence_is_cycled, CASE WHEN (c.oid <= 18655) THEN
pgadmin_get_pgdesc(c.oid) ELSE pgadmin_get_desc(c.oid) END AS
sequence_comments FROM pg_class c WHERE (c.relkind = 'S'::"char");
CREATE VIEW "pgadmin_tables" as SELECT c.oid AS table_oid, c.relname AS
table_name, pg_get_userbyid(c.relowner) AS table_owner, c.relacl AS
table_acl, CASE WHEN (c.relhasindex = 't'::bool) THEN 'Yes'::text ELSE
'No'::text END AS table_has_indexes, CASE WHEN (c.relhasrules = 't'::bool)
THEN 'Yes'::text ELSE 'No'::text END AS table_has_rules, CASE WHEN
(c.relisshared = 't'::bool) THEN 'Yes'::text ELSE 'No'::text END AS
table_is_shared, CASE WHEN (c.relhaspkey = 't'::bool) THEN 'Yes'::text ELSE
'No'::text END AS table_has_primarykey, CASE WHEN (c.reltriggers > 0) THEN
'Yes'::text ELSE 'No'::text END AS table_has_triggers,
pgadmin_get_rows(c.oid).table_rows AS table_rows, CASE WHEN (c.oid <= 18655)
THEN pgadmin_get_pgdesc(c.oid) ELSE pgadmin_get_desc(c.oid) END AS
table_comments, a.oid AS column_oid, a.attname AS column_name, a.attnum AS
column_position, t.typname AS column_type, CASE WHEN ((a.attlen = -1) AND
(a.atttypmod = -1)) THEN 0 ELSE CASE WHEN (a.attlen = -1) THEN CASE WHEN
(((t.typname = 'bpchar'::name) OR (t.typname = 'char'::name)) OR (t.typname
= 'varchar'::name)) THEN (a.atttypmod - 4) ELSE a.atttypmod END ELSE
int4(a.attlen) END END AS column_length, CASE WHEN (a.attnotnull =
't'::bool) THEN 'Yes'::text ELSE 'No'::text END AS column_not_null, CASE
WHEN (a.atthasdef = 't'::bool) THEN 'Yes'::text ELSE 'No'::text END AS
column_has_default, CASE WHEN (pgadmin_get_col_def(c.oid, int4(a.attnum))
NOTNULL) THEN pgadmin_get_col_def(c.oid, int4(a.attnum)) ELSE ''::text END
AS column_default, CASE WHEN (a.oid <= 18655) THEN pgadmin_get_pgdesc(a.oid)
ELSE pgadmin_get_desc(a.oid) END AS column_comments FROM pg_attribute a,
pg_type t, pg_class c WHERE (((a.atttypid = t.oid) AND (a.attrelid = c.oid))
AND (((bpchar(c.relkind) = 'r'::bpchar) OR (bpchar(c.relkind) = 's'::bpchar
)) AND (NOT (EXISTS (SELECT pg_rewrite.rulename FROM pg_rewrite WHERE
((pg_rewrite.ev_class = c.oid) AND (bpchar(pg_rewrite.ev_type) =
'1'::bpchar)))))));
"Gregory Wood" <gregw@com-stock.com> writes:
> I've tracked down the problem and it seems it doesn't like two view
> definitions (pgadmin_sequences and pgadmin_tables), and therefore the
> subsequent GRANTs. The views are at the end of the email.
The views are difficult to do much with when we can't run them through
the software ... which we can't do without the context of the underlying
table definitions. Could you provide the complete schema dump
(pg_dump -s)?
regards, tom lane
On Tue, 29 Jan 2002, Tom Lane wrote: > "Gregory Wood" <gregw@com-stock.com> writes: > > I've tracked down the problem and it seems it doesn't like two view > > definitions (pgadmin_sequences and pgadmin_tables), and therefore the > > subsequent GRANTs. The views are at the end of the email. > > The views are difficult to do much with when we can't run them through > the software ... which we can't do without the context of the underlying > table definitions. Could you provide the complete schema dump > (pg_dump -s)? My guess is the problem is that the views use column(func(params)) which get output by pg_dump as func(params).column. I can replicate in 7.2beta4 with a simpler case, but haven't looked any further. create table eee1( a int, b int); create function foo1(int) returns eee1 as 'select * from eee1 limit 1;' language 'sql'; create view vv1 as select b(foo1(3)); sszabo@bills:~ $ pg_dump -s -t vv1 sszabo -- -- Selected TOC Entries: -- \connect - sszabo -- -- TOC Entry ID 2 (OID 4938784) -- -- Name: vv1 Type: VIEW Owner: sszabo -- CREATE VIEW "vv1" as SELECT foo1(3).b AS b; sszabo@bills:~ $ pg_dump -s -t vv1 sszabo | psql You are now connected as new user sszabo. ERROR: parser: parse error at or near "."
As far as I can tell, those views only reference system tables (pg_class, pg_attribute, pg_type,pg_rewrite). But if you still need it I'll be happy to send it to you directly. Greg ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Gregory Wood" <gregw@com-stock.com> Cc: "PostgreSQL-General" <pgsql-general@postgresql.org> Sent: Tuesday, January 29, 2002 4:40 PM Subject: Re: [GENERAL] pg_dump/PgAdmin problem? > "Gregory Wood" <gregw@com-stock.com> writes: > > I've tracked down the problem and it seems it doesn't like two view > > definitions (pgadmin_sequences and pgadmin_tables), and therefore the > > subsequent GRANTs. The views are at the end of the email. > > The views are difficult to do much with when we can't run them through > the software ... which we can't do without the context of the underlying > table definitions. Could you provide the complete schema dump > (pg_dump -s)? > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 3: 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 >
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> My guess is the problem is that the views use column(func(params))
> which get output by pg_dump as func(params).column.
Um. Looks like you're right: I see
pgadmin_get_sequence(c.oid).sequence_last_value
and some similar bogosities in the sample views.
> I can replicate in
> 7.2beta4 with a simpler case, but haven't looked any further.
Er, what version do you have, again? I tweaked the backend to fix that
problem awhile ago --- according to the CVS logs the fix is in 7.2beta4:
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/ruleutils.c
regards, tom lane
On Tue, 29 Jan 2002, Tom Lane wrote: > Stephan Szabo <sszabo@megazone23.bigpanda.com> writes: > > My guess is the problem is that the views use column(func(params)) > > which get output by pg_dump as func(params).column. > > Um. Looks like you're right: I see > pgadmin_get_sequence(c.oid).sequence_last_value > and some similar bogosities in the sample views. > > > I can replicate in > > 7.2beta4 with a simpler case, but haven't looked any further. > > Er, what version do you have, again? I tweaked the backend to fix that > problem awhile ago --- according to the CVS logs the fix is in 7.2beta4: Err.. b2, oops, that was the machine I didn't upgrade, sorry about that, wrong terminal strikes again.
Gregory Wood wrote:
>
> As far as I can tell, those views only reference system tables (pg_class,
> pg_attribute, pg_type,pg_rewrite). But if you still need it I'll be happy to
> send it to you directly.
FYI there was the following announcement.
regards,
Hiroshi Inoue
Subject: [HACKERS] pgAdmin users upgrading to PostgreSQL 7.2
Date: Tue, 2 Oct 2001 08:45:19 +0100
From: Dave Page <dpage@vale-housing.co.uk>
Hi,
It's come to my attention that users of pgAdmin (the original, not
pgAdmin
II) will not be able to dump/reload their 7.1.x databases into 7.2
without
an additional step in the upgrade procedure.
This is because pgAdmin creates a number of views on the server which
include the oid column from tables such as pg_attribute - obviously
attempting to reload these view will cause an error as there is no
longer an oid column in pg_attribute in 7.2.
pgAdmin II is unaffected by this problem.
I will obviously try to assist anyone who suffers from this problem,
but if the following text can be added to the INSTALL file and
anywhere else that may be appropriate it might help ease the pain!
{at the end of #2 under 'If you are upgrading)
pgAdmin 7.x users will need to drop server side objects
before dumping their database, otherwise the reload will
fail. To do this, select 'Drop all pgAdmin Server Side
Objects' from the 'Advanced' menu. pgAdmin does not
support PostgreSQL 7.2, instead, please try pgAdmin II
from http://pgadmin.postgresql.org/.
Regards, Dave.