pg_dump problem?

Hi!

I'm trying to dump and restore my database which is a 6.5 May 2nd
snapshot, but psql is barfing on pg_dump's output. Naturally I find that
quite disturbing! I'd like to find out how I can salvage my data,
because right now I havn't got a way of backing it up properly. pg_dump
-D |psql can re-insert my data, but with the loss of oids, and my schema
relies on oids. If anyone wants the full pg_dump data let me know.
pg_dump -o |psql results in the errors.....

The first one, it looks

COPY "urllink" WITH OIDS FROM stdin;
ERROR: pg_atoi: error in "http://www.photogs.com/bwworld/f5.html":
can't parse
"http://www.photogs.com/bwworld/f5.html"
PQendcopy: resetting connection

This was caused by the following input
COPY "urllink" WITH OIDS FROM stdin;
24265 \N Review of Nikon F5 \N \N \N 24065
http://www.photogs.com/bwworld/f5.html t

It looks like maybe postgres is expecting an integer and getting a
string maybe?

One thing I did which was a little unusual is that I did an ALTER TABLE
foo ADD COLUMN, but I should have said ALTER TABLE foo* ADD COLUMN to
get the column on inherited attributes. The only solution I could think
of was to go and add the attribute to all the sub-classes too. This
seemed to work (is this what I should have done?), but I don't know if
this might be related to this problem. Maybe postgres is confused now
about column orders?? So I wanted desperately to do a pg_dump -D -o, but
-D stops -o from working (Yuk! This really need to be fixed!)

(Please give us DROP COLUMN soon! :-)

The other error looks to be something to do with views...

CREATE RULE "_RETproductv" AS ON SELECT TO "productv" WHERE DO INSTEAD
SELECT "
oid" AS "oidv", "type", "title", "summary", "body", "image", "category",
"mfrcod
e", "mfr", "costprice", "taxrate", "profit", "rrprice", "taxrate" *
"costprice"
AS "tax", "costprice" + "profit" AS "exsaleprice", "costprice" +
"profit" + "tax
rate" * "costprice" AS "saleprice" FROM "product";
ERROR: parser: parse error at or near "do"
CREATE RULE "_RETorderitemv" AS ON SELECT TO "orderitemv" WHERE DO
INSTEAD SELE
CT "oid" AS "oidv", "product", "webuser", "quantity", "price",
"taxfree", "order
status", "orderdatetime", "shipdatetime", "price" * "quantity" AS
"totalprice" F
ROM "orderitem";
ERROR: parser: parse error at or near "do"

As a follow-up to this, I tried creating a new database from the
original CREATE TABLE statements, with the additional field added to the
CREATE TABLE which I had previously used an ALTER TABLE to add.

I found that the fields came out in a different order when I do a SELECT
* FROM urllink.

This re-enforces my theory that postgres is confused about field orders,
and that there is a bad interaction between ALTER TABLE ADD COLUMN and
any database use which assumes a particular column ordering. In my
opinion, any useful SQL must specify columns in order to be reliable
(even COPY). Unfortunately, COPY does not allow you to specify column
names, and INSERT does not allow you to retain oids, thus I am screwed
right now. Any suggestions on how to salvage my data still welcome :-).

Chris Bitmead wrote:

Oh yeah, I'm using a fairly complex inheritance hierarchy, so it may be
related to a difference between the order COPY may output fields and the
order fields may be deemed when re-created via a CREATE TABLE,
especially with regard to inheritance and possibly ALTER TABLE ADD
COLUMN.

Because of the complex inheritance, I can't just reorder the columns in
the CREATE TABLE of the pg_dump, because it is mostly postgresql which
is determining field order somehow according to inheritance. In general,
the anonymous field nature of COPY seems particularly bad in conjunction
with inheritance where field order is determined by the database rather
than the user, especially since it seems postgresql doesn't necessarily
re-create the same order after a pg_dump.

I'm pretty sure that the ALTER TABLE ADD COLUMN is still part of the
problem though, because if I re-create the schema from scratch I can
dump and restore properly. It seems to be my use of ADD COLUMN which has
made postgres inconsistent in its column orderings.

Chris Bitmead wrote:

Thus spake Chris Bitmead

Am I right in saying that the -o and -D arguments to pg_dump cannot work
together? Any chance of this getting fixed?

I suspect that the problem is that you can't insert an OID into the
system using standard SQL statements but I'm not sure about that. I
do know that the following crashed the backend.

darcy=> insert into x (oid, n) values (1234567, 123.456);

Otherwise is there any other way of deleting a column from a table
whilst retaining oids? In general there seems there are problems with
various scheme changes that you may want to do if you need to retain
oids. Various SELECT INTO options don't work any more unless there is
some way to set the oid in conjunction with named fields (like the -D
option).

Ultimately I think you need to get away from using OIDs in your top
level applications. Depending on them causes these kinds of problems
and moves you farther from standard SQL in your app. Use of the OID
(IMNSHO) should be limited to temporary tracking of rows and even then
it should be in middle level code, not the top level application. I
offer the use of OIDs in pg.py in the Python interface as an example
of middle code.

I suggest that you replace the use of OID in your database with a serial
type primary key. That allows you to dump and reload without losing
the information and it performs the same function as OID in your code.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

Ultimately I think you need to get away from using OIDs in your
top level applications.

I don't give a rip about standard SQL. What I care about is real object
databases. A fundamental principle of object theory is that objects have
a unique identity. In C++ it is a pointer. In other languages it is a
reference. In an object database it is an oid. In the NSHO of a fellow
called Stonebraker, you should be using oids for everything.

BTW, I was looking through the original 4.2 docs, and I noted that in
Postgres 4.2 every class had not only an oid, but an implicit classoid,
allowing you to identify the type of an object. What happened to this?
It would solve just a ton of problems I have, because I'm using a very
OO data model. It sounds like Postgres used to be a real object
database. Now everybody seems to want to use it as yet another sucky rdb
and a lot of essential OO features have undergone bit-rot. What happened
to building a better mouse trap?

Have a read of shared_object_hierarchy.ps in the original postgres doco
to see how things should be done. Sorry for the flames, but I used to
work for an ODBMS company and I'm passionate about the benefits of
properly supporting objects.

Depending on them causes these kinds of problems

and moves you farther from standard SQL in your app. Use of the OID
(IMNSHO) should be limited to temporary tracking of rows and even then
it should be in middle level code, not the top level application. I
offer the use of OIDs in pg.py in the Python interface as an example
of middle code.

I suggest that you replace the use of OID in your database with a serial
type primary key. That allows you to dump and reload without losing
the information and it performs the same function as OID in your code.

--
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

--
Chris Bitmead
http://www.bigfoot.com/~chris.bitmead
mailto:chris.bitmead@bigfoot.com

Then <chris.bitmead@bigfoot.com> spoke up and said:

I don't give a rip about standard SQL. What I care about is real object
databases. A fundamental principle of object theory is that objects have
a unique identity. In C++ it is a pointer. In other languages it is a
reference. In an object database it is an oid. In the NSHO of a fellow
called Stonebraker, you should be using oids for everything.

Unfortunately, the implementation within PostgreSQL suffered from both
bugs and severe logic errors. Further there was no facility for
manipulating OIDs (can you say dump/reload?). Thanks to the efforts
of the PostgreSQL community, many of these items have been fixed, but
sometimes at a cost to OO.

BTW, I was looking through the original 4.2 docs, and I noted that in
Postgres 4.2 every class had not only an oid, but an implicit classoid,
allowing you to identify the type of an object. What happened to this?
It would solve just a ton of problems I have, because I'm using a very
OO data model. It sounds like Postgres used to be a real object
database. Now everybody seems to want to use it as yet another sucky rdb
and a lot of essential OO features have undergone bit-rot. What happened
to building a better mouse trap?

We (not really me, but the others who are actually writing code) are
working very hard to make PostgreSQL SQL92 compliant and stable.
Further, more features are being added all the time. If you want a
particular feature set, then get off your butt and contribute some
code. When I wanted PostgreSQL to work on my AViiON, I did the
necessary work and contributed it back to the community.

Have a read of shared_object_hierarchy.ps in the original postgres doco
to see how things should be done. Sorry for the flames, but I used to
work for an ODBMS company and I'm passionate about the benefits of
properly supporting objects.

Cool. Take your experience and write some code. BTW, you might want
to notice that document was never a description of how things *really*
worked in PostgreSQL, only how it was *supposed* to work. We
inherited some seriously broken, dysfunctional code and have done some
beautiful work with it (again, not actually me here). It's a work in
progress, and therefore should be looked at by the users as
a) needing work, and
b) an opportunity to excell, by showing off your talents as you submit
new code.

--
=====================================================================
| JAVA must have been developed in the wilds of West Virginia. |
| After all, why else would it support only single inheritance?? |
=====================================================================
| Finger geek@cmu.edu for my public key. |
=====================================================================

"D'Arcy" "J.M." Cain <darcy@druid.net> writes:

Thus spake Chris Bitmead

Am I right in saying that the -o and -D arguments to pg_dump cannot work
together? Any chance of this getting fixed?

I suspect that the problem is that you can't insert an OID into the
system using standard SQL statements but I'm not sure about that.

Since COPY WITH OIDS works, I think there's no fundamental reason why
an INSERT couldn't specify a value for the OID field. Certainly,
persuading pg_dump to do this would be pretty trivial --- the only
question is whether the backend will accept the resulting script.
Unfortunately you say:

I do know that the following crashed the backend.
darcy=> insert into x (oid, n) values (1234567, 123.456);

This is definitely a bug --- it should either do it or give an
error message...

Ultimately I think you need to get away from using OIDs in your top
level applications.

I concur fully with this advice. I think it's OK to use an OID as
a working identifier for a record; for example, my apps do lots
of this:
SELECT oid,* FROM table WHERE ...;
UPDATE table SET ... WHERE oid = 12345;
But the OID will be forgotten at app shutdown. I never ever use an
OID as a key referred to by another database entry (I use serial columns
for unique keys). So, I don't have to worry about preserving OIDs
across database reloads.

regards, tom lane

Chris Bitmead <chris.bitmead@bigfoot.com> writes:

As a follow-up to this, I tried creating a new database from the
original CREATE TABLE statements, with the additional field added to the
CREATE TABLE which I had previously used an ALTER TABLE to add.

I found that the fields came out in a different order when I do a SELECT
* FROM urllink.

This re-enforces my theory that postgres is confused about field orders,

I'm actually a tad surprised that ALTER TABLE ADD COLUMN works at all in
an inheritance context (or maybe the true meaning of your report is that
it doesn't work). See, ADD COLUMN always wants to *add* the column, at
the end of the list of columns for your table. What you had was
something like this:

Table Columns

Parent A B C
Child A B C D E

Then you did ALTER Parent ADD COLUMN F:

Parent A B C F
Child A B C D E

Ooops, you should have done ALTER Parent*, so you tried to recover by
altering the child separately with ALTER Child ADD COLUMN F:

Parent A B C F
Child A B C D E F

Do you see the problem here? Column F is not correctly inherited,
because it is not in the same position in parent and child. If you
do something like "SELECT F FROM Parent*" you will get D data out of
the child table (or possibly even a coredump, if F and D are of
different datatypes) because the inheritance code presumes that F's
definition in Parent applies to all its children as well. And the
column's position is part of its definition.

I'd say it is a bug that ALTER TABLE allowed you to do an ADD COLUMN
(or any other mod for that matter) on Parent without also changing its
children to match. I am not sure whether ADD COLUMN is capable of
really working right in an inheritance scenario; it'd have to put the
new column in the middle of the existing columns for child tables,
and I don't know how hard that is. But the system should not accept
a command that makes the parent and child tables inconsistent.

Anyway, to get back to your immediate problem of rebuilding your
database, the trouble is that once you recreate Parent and Child
using correct declarations, they will look like

Parent A B C F
Child A B C F D E

and since the column order of Child is different from before,
a plain COPY won't reload it correctly (neither will an INSERT
without explicit column labels). What I'd suggest doing is
dumping the old DB with pg_dump -o and then using a sed script
or a quick little perl program to reorder the fields in the COPY
data before you reload.

regards, tom lane

geek+@cmu.edu wrote:

Cool. Take your experience and write some code. BTW, you might want
to notice that document was never a description of how things *really*
worked in PostgreSQL, only how it was *supposed* to work.

Yeah, sorry I didn't want to be critical. I'm grateful of all the great
work that's been done to make it a working stable product. I just wanted
to raise some awareness of what Postgres was originally meant to be.
I've been following the research being done at Berkeley in early times
always hoping that some of the OO features would mature more.

I will try and come to terms with the code to try and add some of these
features myself, I've just spent a few hours browsing the code, but
there is certainly a big learning curve there, especially as the doco is
minimal. But I'll see what I can do.

Tom Lane wrote:

Ooops, you should have done ALTER Parent*, so you tried to recover by
altering the child separately with ALTER Child ADD COLUMN F:

Parent A B C F
Child A B C D E F

Do you see the problem here? Column F is not correctly inherited,
because it is not in the same position in parent and child. If you
do something like "SELECT F FROM Parent*" you will get D data out of
the child table (or possibly even a coredump, if F and D are of
different datatypes) because the inheritance code presumes that F's
definition in Parent applies to all its children as well.

Well, in my brief testing, it appears as if what I did actually works as
far as having a working database is concerned. It seemed as if SELECT F
FROM Parent* actually did the right thing. Sort-of anyway. If I didn't
add F to the child, then F seemed to be some random number on a SELECT.

And the
column's position is part of its definition.

I'd say it is a bug that ALTER TABLE allowed you to do an ADD COLUMN
(or any other mod for that matter) on Parent without also changing its
children to match.

I tend to agree. I'd say that you should say table* if table has
children.

I am not sure whether ADD COLUMN is capable of
really working right in an inheritance scenario; it'd have to put the
new column in the middle of the existing columns for child tables,
and I don't know how hard that is.

I'm pretty sure it does the right thing already, but I havn't done much
testing.

What I'd suggest doing is
dumping the old DB with pg_dump -o and then using a sed script
or a quick little perl program to reorder the fields in the
COPY data before you reload.

Ok, I tried that and it worked.

Any thoughts on the other error mesg I had that seemed to be about
views? I doesn't seem to have caused any problem.

--
Chris Bitmead
http://www.bigfoot.com/~chris.bitmead
mailto:chris.bitmead@bigfoot.com

Then <chris.bitmead@bigfoot.com> spoke up and said:

I will try and come to terms with the code to try and add some of these
features myself, I've just spent a few hours browsing the code, but
there is certainly a big learning curve there, especially as the doco is
minimal. But I'll see what I can do.

Great! It's wonderful to see new talent coming on board!

--
=====================================================================
| JAVA must have been developed in the wilds of West Virginia. |
| After all, why else would it support only single inheritance?? |
=====================================================================
| Finger geek@cmu.edu for my public key. |
=====================================================================

I wrote:

"D'Arcy" "J.M." Cain <darcy@druid.net> writes:

I do know that the following crashed the backend.
darcy=> insert into x (oid, n) values (1234567, 123.456);

This is definitely a bug --- it should either do it or give an
error message...

Actually, with recent sources you get:

regression=> insert into x (oid, n) values (1234567, 123.456);
ERROR: Cannot assign to system attribute 'oid'

I had put in a patch to defend against "UPDATE table SET oid = ...",
and it evidently catches the INSERT case too.

I am not sure how much work it would take to actually accept an INSERT/
UPDATE that sets the OID field. There is a coredump in the parser if
you take out the above check; it wouldn't be hard to fix that coredump
but I haven't looked to see what else may lurk beyond it.
(preprocess_targetlist is a danger zone that comes to mind.)

Anyway, this definitely looks like a "new feature" that is not going to
get done for 6.5. Perhaps someone will get interested in making it work
for 6.6 or later.

regards, tom lane

and since the column order of Child is different from before,
a plain COPY won't reload it correctly (neither will an INSERT
without explicit column labels). What I'd suggest doing is
dumping the old DB with pg_dump -o and then using a sed script
or a quick little perl program to reorder the fields in the COPY
data before you reload.

Good summary. Another idea is to create temp uninherited copies of the
tables using SELECT A,B INTO TABLE new FROM ... and make the orderings
match, delete the old tables, recreate with inheritance, and do INSERT
.. SELECT, except you say you can't load oids. Oops, that doesn't help.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

I will try and come to terms with the code to try and add some of these
features myself, I've just spent a few hours browsing the code, but
there is certainly a big learning curve there, especially as the doco is
minimal. But I'll see what I can do.

We
inherited some seriously broken, dysfunctional code and have done some
beautiful work with it (again, not actually me here). It's a work in
progress, and therefore should be looked at by the users as
a) needing work, and
b) an opportunity to excell, by showing off your talents as you submit
new code.

Most of us are not walking away from OID's. We want them to work 100%
of the time. Also, make sure you read the backend flowchard and
developers FAQ on the docs page.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Using May 2nd snapshot...

If I do a pg_dump <database> | psql <newdatabase>

Any datetime fields are different. I think it's a timezone problem. I
think pg_dump is dumping in local time, and psql is interpreting it as
GMT.

The dump includes the timezone as part of the dump, so I'm guessing that
the problem is on the part of psql not noticing that. I'm using the
Australian "EST" zone if that's useful.

Is there an immediate work-around?

I guess one thing I'm frustrated about is that I'm ready willing and
able to write an ODMG compliant interface, which is chiefly a client
side exercise, but I've been kind of hanging out looking for postgres to
get one or two backend features necessary to make that happen. Ok, I'm
going to try and figure out how to do it myself.

Q1. I need to have a virtual field which describes the class membership.

So I want to be able to find the class name of various objects by doing
something like
SELECT relname FROM person*, pg_class where person.classoid =
pg_class.oid;
relname
-------------------------------
person
employee
student
empstudent
person
student
(6 rows)

So the critical thing I need here is the imaginary field "classoid".
Postgres knows obviously which relation a particular object belongs to.
The question is how to turn this knowledge into an imaginary field that
can be queried.

Can anybody point me to which areas of the backend I need to be looking
to implement this? I see that there is a data structure called
"Relation" which has an oid field which is the thing I think I need to
be grabbing, but I'm not sure how to make this all come together.

--
Chris Bitmead
http://www.bigfoot.com/~chris.bitmead
mailto:chris.bitmead@bigfoot.com

Chris Bitmead <chris.bitmead@bigfoot.com> writes:

Q1. I need to have a virtual field which describes the class membership.

So I want to be able to find the class name of various objects by doing
something like
SELECT relname FROM person*, pg_class where person.classoid =
pg_class.oid;

I am not sure what you mean by "class membership" here. There is type
information for each column of every relation in pg_attribute and
pg_type. There is also a pg_type entry for each relation, which can be
thought of as the type of the rows of the relation. The query you show
above looks like maybe what you really want to get at is the inheritance
hierarchy between relations --- if so see pg_inherits.

I suspect that whatever you are looking for is already available in the
system tables, but I'm not quite certain about what semantics you want.

regards, tom lane

What I want is that when I get objects back from multiple relations
(usually because of inheritance using "*" although I guess it could be a
union too), is to know the name of the relation (or class) from which
that object came.

So if I do a select * from person*, some of the resulting rows will have
come from person objects, but some may have come from employee objects,
others from the student relation.

So the query...
SELECT relname FROM person*, pg_class where person.classoid =
pg_class.oid;

does a join between a particular inheritance hierarchy (person in this
case), and the pg_class system table which contains a string name for
each relation.

In an ODMG interface library, what would really happen is at startup I
would find all the classes available from the system tables and cache
their structure. Then some application using the ODMG library would,
let's say it's C++, execute something like...

List<Person> = query("SELECT oid, classoid, * FROM person*");
and get a C++ array of objects, some of which may be Student objects
some of which may Employee objects etc. The internals of the ODMG
library would figure out which results were students and which were
employees by the classoid attribute of each resulting row and
instantiate the appropriate type of class.

The way I think this should probably be done is by having each row in
the entire database have an imaginary attribute called classoid which is
the oid of the class to which that object belongs.

In my own application right now, I actually have a real attribute called
(class oid) in a common base class, which is a foreign key into the
pg_class system table. This is wasteful and potentially error prone
though, since postgres knows which tables the rows came from (since each
relation is stored in a different file).

I don't think this can be done now within postgresql. Do you see what I
mean?

Tom Lane wrote:

I am not sure what you mean by "class membership" here. There is type
information for each column of every relation in pg_attribute and
pg_type. There is also a pg_type entry for each relation, which can be
thought of as the type of the rows of the relation. The query you show
above looks like maybe what you really want to get at is the inheritance
hierarchy between relations --- if so see pg_inherits.

I suspect that whatever you are looking for is already available in the
system tables, but I'm not quite certain about what semantics you want.

regards, tom lane

--
Chris Bitmead
http://www.bigfoot.com/~chris.bitmead
mailto:chris.bitmead@bigfoot.com

Any datetime fields are different. I think it's a timezone problem.
The dump includes the timezone as part of the dump, so I'm guessing that
the problem is on the part of psql not noticing that. I'm using the
Australian "EST" zone if that's useful.
Is there an immediate work-around?

Yeah, move to the east coast of the US :)

EST is the US-standard designation for "Eastern Standard Time" (5
hours off of GMT). If you compile your backend with the flag
-DUSE_AUSTRALIAN_RULES=1 you will instead get this to match the
Australian convention, but will no longer handle the US timezone of
course.

This is used in backend/utils/adt/dt.c, and is done with an #if rather
than an #ifdef. Perhaps I should change that...

btw, Australia has by far the largest "timezone space" I've ever seen!
There are 17 Australia-specific timezones supported by the Postgres
backend. I know it's a big place, but the "timezone per capita" leads
the world ;)

- Tom

--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California