Thread: pg_dump problem?
Am I right in saying that the -o and -D arguments to pg_dump cannot work together? Any chance of this getting fixed? 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).
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: > > 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"
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: > > 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: > > > > 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"
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 CChild A B C D E
Then you did ALTER Parent ADD COLUMN F:
Parent A B C FChild 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 FChild 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 FChild 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. > 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.
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
> 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, Pennsylvania19026
> 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, Pennsylvania19026
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
Tom Lane wrote: > > 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. There is currently no (fast) way to go from oid to the relation containing that oid. the only way seems to find all relations that inherit from the base and do select * from base_or_derived_relation where oid=the_oid_i_search_for; until you get back the row. I would propose a pseudo column (or funtion) so that one could do: select rowrelname() as class_name, * from person*; and then work from there on. Unfortunately I am too ignorant on the internals to implement it ;( ------------- Hannu
> until you get back the row. > > I would propose a pseudo column (or funtion) so that one could do: > > select rowrelname() as class_name, * from person*; > > and then work from there on. Basicly that's what I want to implement, except that instead of returning the relname() I think the rel_classoid (oid of pg_class) is a better choice. Then obtaining the relname a simple join with pg_class. -- Chris Bitmead http://www.bigfoot.com/~chris.bitmead mailto:chris.bitmead@bigfoot.com
Chris Bitmead <chris.bitmead@bigfoot.com> writes:
>> I would propose a pseudo column (or funtion) so that one could do:
>> select rowrelname() as class_name, * from person*;
>> and then work from there on.
> Basicly that's what I want to implement, except that instead of
> returning the relname() I think the rel_classoid (oid of pg_class) is a
> better choice. Then obtaining the relname a simple join with pg_class.
OK, I'm starting to get the picture, and I agree there's no way to get
the system to give you this info now. (You could store a user field
that provides the same info, of course, but that's kind of ugly.)
I think you'd have to implement it as a system attribute (like oid,
xid, etc) rather than as a function, because in a join scenario you
need to be able to indicate which tables you are talking about.
For example, to find men with wives named Sheila in your database:
select p1.classoid, p1.firstname, p1.lastname
from person* as p1, person* as p2
where p1.spouse = p2.oid and p2.firstname = 'Sheila';
If it were "select classoid(), ..." then you'd have no way to indicate
which person's classoid you wanted.
regards, tom lane
Added to TODO list. > > Am I right in saying that the -o and -D arguments to pg_dump cannot work > together? Any chance of this getting fixed? > > 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). > > -- 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, Pennsylvania19026
Hi guys, I've had a long discussion with the timezone people about this time zone abbreviation issue. In their words, the way Postgres works is broken :-( While to us mere mortals it may appear sensible that zone designations are unique, this is apparently not the case, and this is not unique to Australia. Any code which relies on them being unique is designated "broken". I argued strongly that timezones abbreviations should be changed to be unique, but without a great deal of success, partly because (a) that's just the way it is (b) it's based on official government of local areas and (c) there's no reason to change them. I personally disagree, but I wouldn't be holding my breath for anything to change on that front. So according to them, the way postgres should work is that it should dump times with a time and a specific UT offset, as in 10:00am UT-10 for example. I'm not 100% sure why Postgres has a lot of code for timezone issues currently. I'm guessing that Postgres is trying to work around this zoneinfo ``problem'' by recognising say "AEST" in lieu of australia's EST zone. But unless you're going to do a proper job of it and also output "AEST" on postgres dumps, it seems like a futile thing. The other option would be to dump the full locale name, like instead of outputing "EST", output "Australia/Sydney" which is the full name for that locale. Unfortunately I don't think there's a portable way of getting that information on different systems, and also it's rather wordy output. So basicly the timezone experts are saying that the time zone abbrevs are useless and this problem is not just limited to Australia. It looks to me then like Postgres should stop outputting timezone abbrevs and start outputting UT offsets. The argument is that without any timezone - well that just means local time. If you do specify a timezone it should be the full locale name - as in Australia/Sydney. There are several other arguments. For example some areas sometimes change their zone. Apparently the state of Georgia (?) once changed the zone they are in. In such a case Georgia would need their own locale file. To output dates using the generic abbreviation could be incorrect. The other thing that occurs to me is that I don't know what would happen in that phantom hour once a year when you change over to summer time (or was it when you change back). UT offsets solve this, I'm not sure if anybody has solved it for abbrevs. Timezones are a lot more complex than they look, and I'd like to understand more about how Postgres regards them. Does anybody else have any thoughts on this? Thomas Lockhart wrote: > > > 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 -- Chris Bitmead http://www.bigfoot.com/~chris.bitmead mailto:chris.bitmead@bigfoot.com
> In their words, the way Postgres works is broken :-(
... as is the rest of the world :)
> So according to them, the way postgres should work is that it should
> dump times with a time and a specific UT offset, as in 10:00am UT-10 for
> example.
Use the ISO format setting, and you'll be a happy camper:
postgres=> set datestyle='iso';
SET VARIABLE
postgres=> select datetime 'now';
?column?
----------------------
1999-05-11 07:20:30-07
(1 row)
postgres=> show time zone;
NOTICE: Time zone is PST8PDT
SHOW VARIABLE
> I'm not 100% sure why Postgres has a lot of code for timezone issues
> currently. I'm guessing that Postgres is trying to work around this
> zoneinfo ``problem'' by recognising say "AEST" in lieu of australia's
> EST zone. But unless you're going to do a proper job of it and also
> output "AEST" on postgres dumps, it seems like a futile thing.
We rely on the OS to provide timezone offsets for *output*, so we
don't have to figure out how to do daylight savings time (and for
other reasons). There is no standard interface to do the same thing
for input outside of Unix system time, so we do it ourself for input.
And there is no standard interface to get direct access to the
timezone database itself. If'n you don't like the output conventions
for your system, do your own timezone database or learn to like it ;)
> The other thing that occurs to me is that I don't know what would happen
> in that phantom hour once a year when you change over to summer time (or
> was it when you change back). UT offsets solve this, I'm not sure if
> anybody has solved it for abbrevs.
? Since you would be relying on a timezone database for interpretation
of the abbrevs, you might run the risk of dissimilar systems doing
things inconsistantly. And we've seen lots of differences on Unix
boxes once you start dealing with times before 1960 or so (those damn
kids doing development nowadays :) Sun does a great job (you can learn
a bit of history looking at their timezone database) while some other
systems don't bother trying. The zic utilities used by Linux and some
other systems do a pretty good job, but are not as rigorous as Sun's
database.
> Timezones are a lot more complex than they look, and I'd like to
> understand more about how Postgres regards them. Does anybody else have
> any thoughts on this?
Uh, sure!
Anyway, your observations are correct, but we are trying to work in
the real world, which doesn't seem much interested in going
exclusively toward the ISO-8601 date/time representation. But we do
support it, and I've toyed with making it the default format. Maybe
for postgres-7.0. In the meantime you can build your server to use it
by default, you can fire up your server with PGDATESTYLE defined, or
you can set PGDATESTYLE for any client using libpq.
- Tom
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
Chris Bitmead <chris.bitmead@bigfoot.com> writes:
> In their words, the way Postgres works is broken :-(
Thomas will have to provide the detailed response to this, but as far
as I've seen there is nothing "broken" about Postgres' handling of
timezones. You're assuming that portability of dump scripts across
locales is more important than showing dates in the style(s) people
want to read ... in the real world that isn't so.
> So according to them, the way postgres should work is that it should
> dump times with a time and a specific UT offset, as in 10:00am UT-10 for
> example.
SET DATESTYLE = 'ISO'.
(It might be a worthwhile idea for pg_dump to use this datestyle always,
since indeed some of the other ones are locale-dependent. Comments?)
regards, tom lane
Tom Lane wrote: > > Chris Bitmead <chris.bitmead@bigfoot.com> writes: > > In their words, the way Postgres works is broken :-( > > Thomas will have to provide the detailed response to this, but as far > as I've seen there is nothing "broken" about Postgres' handling of > timezones. You're assuming that portability of dump scripts across > locales not across locales, within the same locale! > is more important than showing dates in the style(s) people > want to read ... in the real world that isn't so. Well I'm not assuming it, it is the timezone database which assumes it. Also the problem is not "across locales", but rather within a single locale. Like if someone installs a standard RedHat system with Postgres and starts using it, depending on where they are in the world it may not function correctly. As far as people seeing dates in the "style they want to read", the timezone people made the not-unreasonable observation that if you just want to see local-time, you shouldn't show any zone at all. Only when you are not talking about the current zone should you show something specific. Given that zone ids are not unique that sounds reasonable. As I said, I think they should be unique, but they're not. Ok, you have the AUSTRALIAN_RULES compilation option, so people over here have to rebuild the whole of postgres from scratch. Doesn't worry me, but a lot of people don't want to have to bother with that. Also there are probably some other locales in the world with the same problem that you havn't considered yet. > > > So according to them, the way postgres should work is that it should > > dump times with a time and a specific UT offset, as in 10:00am UT-10 for > > example. > > SET DATESTYLE = 'ISO'. > > (It might be a worthwhile idea for pg_dump to use this datestyle always, > since indeed some of the other ones are locale-dependent. Comments?) > > regards, tom lane
Thomas Lockhart wrote:
>
> > In their words, the way Postgres works is broken :-(
>
> ... as is the rest of the world :)
Yep :-)
> Use the ISO format setting, and you'll be a happy camper:
>
> postgres=> set datestyle='iso';
Ok. I think though that you should consider making it the default,
simply because something that always works is a good default. Something
that only sometimes works is not a very good default.
> We rely on the OS to provide timezone offsets for *output*,
> so we
> don't have to figure out how to do daylight savings time
> (and for
> other reasons). There is no standard interface to do the same thing
> for input outside of Unix system time, so we do it ourself > for input.
That might be ok if what comes out of the database works when you stick
it back in. Like you accept AEST as australian eastern standard time as
input. But if you don't print AEST on output then it's inconsistent. I
think the output should be either no time zone info, the full locale
("Australia/Sydney") or UT offset since they will always work.
I'm not sure what you mean when you say there is no standard interface
to input times. Various combinations of setenv("TZ="), mktime() etc etc
seem to be able to do everything one would need in my experience.
> And there is no standard interface to get direct access to > the timezone database itself. If'n you don't like the
> output conventions for your system, do your own timezone
> database or learn to like it ;)
I'm not sure why you would require any more interface than
mktime(),localtime() and friends. The only thing I can think of is to
have a list of the valid locales but that's a different problem.
> > The other thing that occurs to me is that I don't know what would happen
> > in that phantom hour once a year when you change over to summer time (or
> > was it when you change back). UT offsets solve this, I'm not sure if
> > anybody has solved it for abbrevs.
>
> ? Since you would be relying on a timezone database for interpretation
> of the abbrevs, you might run the risk of dissimilar systems doing
> things inconsistantly.
What happens for those times that occur twice? Like if the clocks go
back 1 hour at 3:00am on a particular day, then that time happens twice.
In other words 3/3/1999 2:30am EST may be an ambigous time because that
time occurs twice. How is that handled?
Re: [HACKERS] Date/Time Flaw in pg_dump ?
From
reedstrm@wallace.ece.rice.edu (Ross J. Reedstrom)
Date:
> > What happens for those times that occur twice? Like if the clocks go > back 1 hour at 3:00am on a particular day, then that time happens twice. > In other words 3/3/1999 2:30am EST may be an ambigous time because that > time occurs twice. How is that handled? Actually, not. The first time 2:30am occurs, it's EST, the second time, its EDT. Ambiguity only occurs if you present local time without a timezone. :-( Ross -- Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
I want to stay up to date with all the latest changes. Is it possible to get read CVS access? -- Chris Bitmead http://www.bigfoot.com/~chris.bitmead mailto:chris.bitmead@bigfoot.com
On Fri, 14 May 1999, Chris Bitmead wrote: > I want to stay up to date with all the latest changes. Is it possible to > get read CVS access? export CVSROOT=":pserver:anonymous@anoncvs.gnome.org:/cvs/gnome" echo "Just press <enter>:" cvs login -- Todd Graham Lewis Postmaster, MindSpring Enterprises tlewis@mindspring.net (800) 719-4664, x22804 "A pint of sweat will save a gallon of blood." -- George S. Patton
On Fri, 14 May 1999, Todd Graham Lewis wrote: > On Fri, 14 May 1999, Chris Bitmead wrote: > > > I want to stay up to date with all the latest changes. Is it possible to > > get read CVS access? > > export CVSROOT=":pserver:anonymous@anoncvs.gnome.org:/cvs/gnome" > echo "Just press <enter>:" > cvs login Woops! Wrong list! Hang on a sec... -- Todd Graham Lewis Postmaster, MindSpring Enterprises tlewis@mindspring.net (800) 719-4664, x22804 "A pint of sweat will save a gallon of blood." -- George S. Patton
On Fri, 14 May 1999, Todd Graham Lewis wrote: > On Fri, 14 May 1999, Chris Bitmead wrote: > > > I want to stay up to date with all the latest changes. Is it possible to > > get read CVS access? > > export CVSROOT=":pserver:anonymous@anoncvs.gnome.org:/cvs/gnome" > echo "Just press <enter>:" > cvs login export CVSROOT=":pserver:anoncvs@postgresql.org:/usr/local/cvsroot" echo "Password is \"postgresql\" " cvs -d :pserver:anoncvs@postgresql.org:/usr/local/cvsroot login This was supposed to have been put on the web page, as I recall... -- Todd Graham Lewis Postmaster, MindSpring Enterprises tlewis@mindspring.net (800) 719-4664, x22804 "A pint of sweat will save a gallon of blood." -- George S. Patton
I want to try and really really understand the system catalogs as a prelude to figuring out how to make some enhancements. I've read everything in the doco about them (which isn't much that I can see). Is there anything else? Does it say somewhere what all the fields mean? I'm particularly interested in the basic catalogs - classes, attributes, types etc. -- Chris Bitmead http://www.bigfoot.com/~chris.bitmead mailto:chris.bitmead@bigfoot.com
Can somebody explain briefly what happens when you do an ALTER TABLE ADD COLUMN? Obviously it doesn't seem to go through the database and update every record with a new attribute there and then. Does it get updated the next time the record is retrieved or what is the story there? -- Chris Bitmead http://www.bigfoot.com/~chris.bitmead mailto:chris.bitmead@bigfoot.com
I found that the best way to figure out the system catalogs was to do the following steps (i) Stare at the diagram in the html web pages until you are cross-eyed. (ii) Look through the .h files in src/include/catalogs/ realising of course that many of the fields/attributes that are defined are not used. (iii) Use the \t command in a test database to inspect the actual tables, and try doing a bunch of SELECT queries with joins across catalogs to figure out the relational structure (Schema). Seriously, its not that bad once you get into the groove. One interesting feature that I stumbled on was that at least one of the methods that is required for the definition of indices requires more than 8 arguments, the maximum number for a poastgres function if it is entered with a CREATE FUNCTION command. This means that if you wish to dynamically load a new type of index you have to use INSERT INTO pg_proc commands to enter the index methods straight into the catalog table. Bernie
Todd Graham Lewis <tlewis@mindspring.net> writes: > [ CVS access info ] > This was supposed to have been put on the web page, as I recall... It *is* on the webpage --- I put it there myself. You can find this and other FAQ documents off http://www.postgresql.org/docs/. (I do need to update the CVS page, which still recommends cvs 1.9...) <rant> The "new improved" website design has made it a lot harder to find anything useful, IMHO. For instance, it is not an improvement that the FAQ docs are two levels down in a non-obvious place. The way that the frames-based design makes it impossible to bookmark anything once you have managed to find it just adds insult to injury. </rant> regards, tom lane
On Fri, 14 May 1999, Tom Lane wrote: > Todd Graham Lewis <tlewis@mindspring.net> writes: > > [ CVS access info ] > > This was supposed to have been put on the web page, as I recall... > > It *is* on the webpage --- I put it there myself. You can find this and > other FAQ documents off http://www.postgresql.org/docs/. (I do need to > update the CVS page, which still recommends cvs 1.9...) > > <rant> > The "new improved" website design has made it a lot harder to find > anything useful, IMHO. For instance, it is not an improvement that > the FAQ docs are two levels down in a non-obvious place. The way > that the frames-based design makes it impossible to bookmark anything > once you have managed to find it just adds insult to injury. > </rant> Dmitry and Vince are working on the new one that Dmitry prototyped...not sure what the escheduale is for getting that up though... Its still a work in progress, but it can be seen at http://www.postgresql.org/proto ... submit comments on what you do/dont' like... let them know while they are still working on it ... Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
> > I want to try and really really understand the system catalogs as a > prelude to figuring out how to make some enhancements. > > I've read everything in the doco about them (which isn't much that I can > see). Is there anything else? Does it say somewhere what all the fields > mean? I'm particularly interested in the basic catalogs - classes, > attributes, types etc. See src/include/catalog. There is a doc/src/graphics/catalog.gif, and contrib/pginterface has a utility to find all joins between tables using oids. -- 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, Pennsylvania19026
> > Can somebody explain briefly what happens when you do an ALTER TABLE ADD > COLUMN? Obviously it doesn't seem to go through the database and update > every record with a new attribute there and then. Does it get updated > the next time the record is retrieved or what is the story there? NULL fields take up no space in rows, so adding NULL to the end of a row really doesn't change the row, you just tell the catalog the column exists, and the system sees a NULL there by default. On updates, it remains the same unless you put something in the column. -- 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, Pennsylvania19026
Hi guys. I was trying to add a column to a class again. The class is low down in an inheritance hierarchy. This time, I remembered to add the * after the table name, so I thought that I was ok. Everything seemed ok, and the database went on working as expected for ages. Then one day I had to restore my database and I found again that pg_dump doesn't work with ERROR: pg_atoi: error in "1999-05-10 16:27:40+10": can't parse "-05-10 16:27:40+10" because I think it dumps columns in the wrong order. Fortunately I was able to restore the database by abandoning that column and removing it from the table definition. Fortunately I didn't have much data in that column that was too much loss to lose (yet). I know I mentioned this problem before, but I thought it was because I had forgotten the "*" on the ALTER TABLE ADD COLUMN statement. Now I realise that even when you remember it, you can be bitten. Worse, you can be bitten much later after you've forgotten what was the cause. I'm not sure what to do now. I really do need to add that extra column. If I thought really really hard, I might be able to figure out how to do it with Perl, re-arrangement of columns etc. But I've got a lot of tables and it sounds all too hard. The frustrating thing is that adding the columns actually works. It's just that it can't be restored properly after a catastrophy. -- Chris Bitmead http://www.bigfoot.com/~chris.bitmead mailto:chris.bitmead@bigfoot.com
I'm convinced that pg_dump / psql restore doesn't seem to restore VIEWs properly. Anybody else seen this? -- Chris Bitmead http://www.bigfoot.com/~chris.bitmead mailto:chris.bitmead@bigfoot.com
Chris Bitmead wrote:
>
>
> I'm convinced that pg_dump / psql restore doesn't seem to restore VIEWs
> properly. Anybody else seen this?
More details please!
There must be something wrong in the rule utilities when
backparsing the views CREATE RULE statement. I need the
definition of the view, the underlying tables and the
(schema) output of pg_dump to track it down.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
Jan Wieck wrote: > > I'm convinced that pg_dump / psql restore doesn't seem to restore VIEWs > > properly. Anybody else seen this? > > More details please! It seems to be extremely easy to reproduce... chris=> create table foo(a int4, b int4); CREATE chris=> insert into foo values(3, 4); INSERT 1484426 1 chris=> create view bar as SELECT a + b FROM foo; CREATE chris=> select * from bar; ?column? -------- 7 (1 row) EOFis=> chris@tech!26!bash:~$ pg_dump chris -o >foo chris@tech!27!bash:~$ createdb foobar chris@tech!28!bash:~$ psql !$ <foo psql foobar <foo CREATE TABLE pgdump_oid (dummy int4); CREATE COPY pgdump_oid WITH OIDS FROM stdin; DROP TABLE pgdump_oid; DROP CREATE TABLE "foo" ( "a" int4, "b" int4); CREATE CREATE TABLE "bar" ( "?column?" int4); CREATE COPY "foo" WITH OIDS FROM stdin; CREATE RULE "_RETbar" AS ON SELECT TO "bar" WHERE DO INSTEAD SELECT "a" + "b" F ROM "foo"; ERROR: parser: parse error at or near "do" EOF chris@tech!29!bash:~$ psql foobar Welcome to the POSTGRESQL interactive sql monitor: Please read the file COPYRIGHT for copyright terms of POSTGRESQL [PostgreSQL 6.5.0 on i686-pc-linux-gnu, compiled by gcc 2.7.2.3] type \? for help on slash commands type \q to quit type \g or terminate with semicolon to execute queryYou are currentlyconnected to the database: foobar foobar=> select * from foo; a|b -+- 3|4 (1 row) foobar=> select * from bar; ?column? -------- (0 rows) foobar=>
Chris Bitmead wrote:
>
> Jan Wieck wrote:
>
> > > I'm convinced that pg_dump / psql restore doesn't seem to restore VIEWs
> > > properly. Anybody else seen this?
> >
> > More details please!
>
> It seems to be extremely easy to reproduce...
> [...]
> CREATE RULE "_RETbar" AS ON SELECT TO "bar" WHERE DO INSTEAD SELECT "a"
^^^^^^^
I've fixed that at 1999/05/25 08:49:33. Update your sources
and do a clean build.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
> Fortunately I was able to restore the database by abandoning that column > and removing it from the table definition. Fortunately I didn't have > much data in that column that was too much loss to lose (yet). > > I know I mentioned this problem before, but I thought it was because I > had forgotten the "*" on the ALTER TABLE ADD COLUMN statement. Now I > realise that even when you remember it, you can be bitten. Worse, you > can be bitten much later after you've forgotten what was the cause. > > I'm not sure what to do now. I really do need to add that extra column. > If I thought really really hard, I might be able to figure out how to do > it with Perl, re-arrangement of columns etc. But I've got a lot of > tables and it sounds all too hard. The frustrating thing is that adding > the columns actually works. It's just that it can't be restored properly > after a catastrophy. Our TODO now has: * ALTER TABLE ADD COLUMN to inherited table put column in wrong place I don't think any of us understand the issues on this one. -- 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, Pennsylvania19026
Bruce Momjian wrote: > Our TODO now has: > > * ALTER TABLE ADD COLUMN to inherited table put column in wrong place > > I don't think any of us understand the issues on this one. Let me guess at the problem. When you add a column, it doesn't change all the records, therefore the column must be added at the end. This means that the columns will not be in the same order as if you had created them from scratch. There seem to be three solutions: a) Go to a much more sophisticated schema system, with versions and version numbers (fairly hard but desirable to fix other schema change problems). Then insert the column in the position it is supposed to be in. b) Fix the copy command to input and output the columns, not in the order they are in, but in the order they would be in on re-creation. c) make the copy command take arguments specifying the field names, like INSERT can do. I think it would be good if Postgres had all 3 features. Probably (b) is the least work.
Does the following indicate a bug in LIKE ? Using CVS from about a week ago. =>select oid,title from category* where title like 'Sigma%'; oid|title ---+----- (0 rows) =>select oid,title from category* where title like 'Sigma'; oid|title -----+----- 21211|Sigma (1 row)
On Mon, 7 Jun 1999, Chris Bitmead wrote:
>
> Does the following indicate a bug in LIKE ? Using CVS from about a week
> ago.
>
> =>select oid,title from category* where title like 'Sigma%';
If I understand this correctly, IMHO, this would be asking for '^Sigma'
with at least one character after the 'a' ...
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
> If I understand this correctly, IMHO, this would be asking for '^Sigma' > with at least one character after the 'a' ... Uhm.... I think the problem is a little worse: create table a ( b varchar(32) ); insert into a values ( 'foo' ); insert into a values ( 'bar' ); insert into a values ( 'foobar' ); insert into a values ( 'foobar2' ); PostgreSQL 6.4.2 tacacs=> select * from a where b like 'foo%'; b ------- foo foobar foobar2 (3 rows) PostgreSQL 6.5beta2 tacacs=> select * from a where b like 'foo%'; b - (0 rows) tacacs=> select * from a where b like '%foo'; b --- foo (1 row) tacacs=> select * from a where b ~ '^foo'; b ------- foo foobar foobar2 (3 rows) Bye. -- Daniele -------------------------------------------------------------------------------Daniele Orlandi - Utility Line Italia - http://www.orlandi.comViaMezzera 29/A - 20030 - Seveso (MI) - Italy -------------------------------------------------------------------------------
On Mon, 7 Jun 1999, Daniele Orlandi wrote: > Date: Mon, 07 Jun 1999 14:27:46 +0200 > From: Daniele Orlandi <daniele@orlandi.com> > To: The Hermit Hacker <scrappy@hub.org> > Subject: Re: [HACKERS] Bug in LIKE ? > > > > If I understand this correctly, IMHO, this would be asking for '^Sigma' > > with at least one character after the 'a' ... > > Uhm.... I think the problem is a little worse: > > create table a ( b varchar(32) ); > insert into a values ( 'foo' ); > insert into a values ( 'bar' ); > insert into a values ( 'foobar' ); > insert into a values ( 'foobar2' ); > > PostgreSQL 6.4.2 > > tacacs=> select * from a where b like 'foo%'; > b > ------- > foo > foobar > foobar2 > (3 rows) > > PostgreSQL 6.5beta2 > > tacacs=> select * from a where b like 'foo%'; > b > - > (0 rows) > > tacacs=> select * from a where b like '%foo'; > b > --- > foo > (1 row) > > tacacs=> select * from a where b ~ '^foo'; > b > ------- > foo > foobar > foobar2 > (3 rows) > Hmm, just tried on current 6.5 from cvs: test=> select version(); version ------------------------------------------------------------------------ PostgreSQL 6.5.0 on i586-pc-linux-gnulibc1, compiled by gcc egcs-2.91.66 (1 row) test=> select * from a where b like 'foo%'; b ------- foo foobar foobar2 (3 rows) test=> select * from a where b like '%foo'; b --- foo (1 row) test=> select * from a where b ~ '^foo'; b ------- foo foobar foobar2 (3 rows) Regards, Oleg > Bye. > > -- > Daniele > > ------------------------------------------------------------------------------- > Daniele Orlandi - Utility Line Italia - http://www.orlandi.com > Via Mezzera 29/A - 20030 - Seveso (MI) - Italy > ------------------------------------------------------------------------------- > > > _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Daniele Orlandi <daniele@orlandi.com> writes:
> Uhm.... I think the problem is a little worse:
It's a real bug, and I see the problem: someone changed the handling of
LIKE prefixes in gram.y, without understanding quite what they were
doing. 6.4.2 has:
if (n->val.val.str[pos] == '\\' || n->val.val.str[pos] == '%')
pos++;
where 6.5 has:
if (n->val.val.str[pos] == '\\' || n->val.val.str[pos+1] == '%')
pos++;
The first one is right and the second is not.
Unless we fix this, LIKE will be completely busted for any string
containing non-leading %. Shall I ... ?
regards, tom lane
On Mon, 7 Jun 1999, Tom Lane wrote:
> Daniele Orlandi <daniele@orlandi.com> writes:
> > Uhm.... I think the problem is a little worse:
>
> It's a real bug, and I see the problem: someone changed the handling of
> LIKE prefixes in gram.y, without understanding quite what they were
> doing. 6.4.2 has:
>
> if (n->val.val.str[pos] == '\\' ||
> n->val.val.str[pos] == '%')
> pos++;
>
> where 6.5 has:
>
> if (n->val.val.str[pos] == '\\' ||
> n->val.val.str[pos+1] == '%')
> pos++;
>
> The first one is right and the second is not.
>
> Unless we fix this, LIKE will be completely busted for any string
> containing non-leading %. Shall I ... ?
Please do...looking through the logs, any idea who changed this one? *gets
out billy club* *grin*
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
> Daniele Orlandi <daniele@orlandi.com> writes: > > Uhm.... I think the problem is a little worse: > > It's a real bug, and I see the problem: someone changed the handling of > LIKE prefixes in gram.y, without understanding quite what they were > doing. 6.4.2 has: > > if (n->val.val.str[pos] == '\\' || > n->val.val.str[pos] == '%') > pos++; > > where 6.5 has: > > if (n->val.val.str[pos] == '\\' || > n->val.val.str[pos+1] == '%') > pos++; > > The first one is right and the second is not. > > Unless we fix this, LIKE will be completely busted for any string > containing non-leading %. Shall I ... ? Yes, please. It was me that introduced the bug. -- 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, Pennsylvania19026
> > if (n->val.val.str[pos] == '\\' || > > n->val.val.str[pos] == '%') > > pos++; > > > > where 6.5 has: > > > > if (n->val.val.str[pos] == '\\' || > > n->val.val.str[pos+1] == '%') > > pos++; > > > > The first one is right and the second is not. > > > > Unless we fix this, LIKE will be completely busted for any string > > containing non-leading %. Shall I ... ? > > Please do...looking through the logs, any idea who changed this one? *gets > out billy club* *grin* Me, but months ago. Put down the club... -- 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, Pennsylvania19026
> > > Uhm.... I think the problem is a little worse:
> > It's a real bug, and I see the problem: someone changed
> > the handling of LIKE prefixes in gram.y,
> > Unless we fix this, LIKE will be completely busted for
> > any string containing non-leading %. Shall I ... ?
> Yes, please. It was me that introduced the bug.
How about adding some regression test queries to catch this kind of
thing? Looks like we don't have *anything* in this area at all except
for tests in the multi-byte string handling, from Tatsuo.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
Oleg Bartunov <oleg@sai.msu.su> writes:
> [ doesn't see a problem ]
I think the particular test case Daniele gave would only fail if you
do not have USE_LOCALE defined. But it's definitely busted: the parser
was transformingb LIKE 'foo%'
intob LIKE 'foo%' AND b >= 'fo%' AND b <= 'fo%\377'
with the third clause not present if USE_LOCALE is defined.
Anyway, it's fixed now. I also cleaned up some confusion about whether
"%%" in a LIKE pattern means a literal % (the SQL spec says not, and
some parts of the code knew it, but other parts didn't...)
regards, tom lane
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> How about adding some regression test queries to catch this kind of
> thing? Looks like we don't have *anything* in this area at all except
> for tests in the multi-byte string handling, from Tatsuo.
Yeah, I was thinking the same thing. I'll bet the MB tests don't catch
this bug either, because it's substantially less likely to get noticed
if USE_LOCALE is on...
regards, tom lane
> Anyway, it's fixed now. I also cleaned up some confusion about whether
> "%%" in a LIKE pattern means a literal % (the SQL spec says not, and
> some parts of the code knew it, but other parts didn't...)
Yeah, but until we have support for the ESCAPE clause on the LIKE
expression then there isn't a way to get a literal "%" into the query
:(
I would suggest we *do* allow "%%" to represent a literal "%" until we
get the full syntax.
imho we will eventually need to move all of this out of gram.y and put
it deeper into the parser code, since it is munging the query so early
it is difficult to know what was done for later stages.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
>> Anyway, it's fixed now. I also cleaned up some confusion about whether
>> "%%" in a LIKE pattern means a literal % (the SQL spec says not, and
>> some parts of the code knew it, but other parts didn't...)
> Yeah, but until we have support for the ESCAPE clause on the LIKE
> expression then there isn't a way to get a literal "%" into the query
> :(
Sure there is: \%. Of course, defaulting to ESCAPE \ rather than no
escape is not standards-compliant either, but it's a lot closer than
inventing a meaning for %% ...
More to the point, %% has not worked like gram.y thought it did for
a long time, if ever, and no one's complained ...
> imho we will eventually need to move all of this out of gram.y and put
> it deeper into the parser code, since it is munging the query so early
> it is difficult to know what was done for later stages.
Agreed. At the very least it should be postponed until we know that the
operator in question *is* textlike(), and not something else that
happens to be named ~~ ... but that's a job for another day.
regards, tom lane
> Sure there is: \%. Of course, defaulting to ESCAPE \ rather than no
> escape is not standards-compliant either, but it's a lot closer than
> inventing a meaning for %% ...
OK.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
On Mon, 7 Jun 1999, Thomas Lockhart wrote:
> > Sure there is: \%. Of course, defaulting to ESCAPE \ rather than no
> > escape is not standards-compliant either, but it's a lot closer than
> > inventing a meaning for %% ...
>
> OK.
I thought I had seen something before about this. In the Sybase 4.9
quick reference on page 21 it says:
To use %,_,[], or [^] as literal characters in a like match string rather
than as wildcards, use square brackets as escape characters for the
percent sign, the underscore and the open bracket. Use the close bracket
but itself. Use the dash as the first character inside a set of brackets.
like "5%" 5 followed by any string of 0 or more characters
like "5[%]" 5%
like "_n" an, in, on, etc.
like "[_]n" _n
like "[a-cdf]" a, b, c, d, or f
like "[-acdf]" -, a, c, d, or f
like "[[]" [
like "]" ]
Wildcards without like have no special meaning.
That help any?
Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com flame-mail: /dev/null # include <std/disclaimers.h>
TEAM-OS2 Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore
http://www.cloudninegifts.com
==========================================================================
> That help any?
Yes, it makes us feel better that we are not the only system with a
"non-standard" implementation :)
Since SQL92 has such limited pattern matching, almost everyone has
some extensions. Ours are pretty compatible with Sybase's, and with
anyone else who has full regular expressions...
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
> > Sure there is: \%. Of course, defaulting to ESCAPE \ rather than no > > escape is not standards-compliant either, but it's a lot closer than > > inventing a meaning for %% ... > > OK. But we have code in DoMatching that does %% to % already. Can we just leave it alone and put it back. I promise to implement ESCAPE for 6.6. -- 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, Pennsylvania19026
Bruce Momjian <maillist@candle.pha.pa.us> writes:
> But we have code in DoMatching that does %% to % already.
No, we don't --- take another look at what it's doing.
If we did make %% mean a literal %, it would be new behavior as far as
DoMatch is concerned. I have been playing with this issue using 6.4.2,
and find that its behavior is extremely inconsistent (ie buggy):
Given
play=> select * from a;
b
-------
foo
bar
foobar
foobar2
foo%bar
fooxbar
foo.bar
(7 rows)
6.4.2 produces
play=> select * from a where b like 'foo%%bar';
b
-------
foo%bar
(1 row)
which sure looks like it is treating %% as literal %, doesn't it? But
the selectivity comes from the parser's inserted conditionsb >= 'foo%bar' AND b <= 'foo%bar\377'
which eliminate things that DoMatch would take. With a little more
poking we find
play=> select * from a where b not like 'foo%%bar';
b
-------
foo
bar
foobar2
(3 rows)
and
play=> select * from a where b like 'foo%%';
b
-------
foo%bar
(1 row)
and
play=> create table pat (p text);
CREATE
play=> insert into pat values ('foo%%bar');
INSERT 1194153 1
play=> select * from a, pat where b like p;
b |p
-------+--------
foobar |foo%%bar
foo%bar|foo%%bar
fooxbar|foo%%bar
foo.bar|foo%%bar
(4 rows)
In these cases, the parser's range conditions don't mask the underlying
behavior of DoMatch.
Since 6.4.2's behavior with %% is clearly broken and in need of some
kind of fix, I think we should make it work like the standard says,
rather than paint ourselves into a corner we'll want to get out of
someday. If %% actually worked reliably, people would start relying
on it. Bad enough that we'll have to keep defaulting to ESCAPE \
for backwards-compatibility reasons; let's not add another deviation
from the spec.
BTW, this is not to discourage you from adding ESCAPE in 6.6 ;-)
regards, tom lane