
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).

Fri, 07 May 1999 03:36:59 +0000
Chris Bitmead <chris.bitmead@bigfoot.com>
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"

Fri, 07 May 1999 04:54:51 +0000
Chris Bitmead <chris.bitmead@bigfoot.com>
Re:pg_dump barfs?

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"

Fri, 07 May 1999 05:14:45 +0000
Chris Bitmead <chris.bitmead@bigfoot.com>
Re: [HACKERS] Re:pg_dump barfs?

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"

Fri, 07 May 1999 07:20:19 +0000
Chris Bitmead <chris.bitmead@bigfoot.com>
Re: [HACKERS] Re:pg_dump barfs?
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.

Fri, 7 May 1999 07:48:32 -0400 (EDT)
"D'Arcy" "J.M." Cain <darcy@druid.net>
Re: [HACKERS] pg_dump problem?
> 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

Fri, 07 May 1999 12:53:20 +0000
Chris Bitmead <chris.bitmead@bigfoot.com>
Re: [HACKERS] pg_dump problem?
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.                            |
=====================================================================
7 May 1999 09:47:54 -0400
geek+@cmu.edu
Re: [HACKERS] pg_dump problem?
"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

Fri, 07 May 1999 10:18:53 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] pg_dump problem? 
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

Fri, 07 May 1999 10:39:36 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Re:pg_dump barfs? 
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.

Fri, 07 May 1999 15:12:04 +0000
Chris Bitmead <chris.bitmead@bigfoot.com>
Re: [HACKERS] pg_dump problem?
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

Fri, 07 May 1999 15:21:22 +0000
Chris Bitmead <chris.bitmead@bigfoot.com>
Re: [HACKERS] Re:pg_dump barfs?
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.                            |
=====================================================================
7 May 1999 11:25:35 -0400
geek+@cmu.edu
Re: [HACKERS] pg_dump problem?
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

Fri, 07 May 1999 17:16:58 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] pg_dump problem? 
> 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

Fri, 7 May 1999 18:58:08 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re:pg_dump barfs?
> 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

Fri, 7 May 1999 18:59:34 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] pg_dump problem?
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?

Sat, 08 May 1999 09:12:20 +0000
Chris Bitmead <chris.bitmead@bigfoot.com>
Date/Time Flaw in pg_dump ?

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

Sat, 08 May 1999 09:32:22 +0000
Chris Bitmead <chris.bitmead@bigfoot.com>
ODMG interface
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

Sat, 08 May 1999 11:26:38 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] ODMG interface 

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

Sat, 08 May 1999 16:03:26 +0000
Chris Bitmead <chris.bitmead@bigfoot.com>
Re: [HACKERS] ODMG interface
> 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

Sat, 08 May 1999 16:55:43 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] Date/Time Flaw in pg_dump ?
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

Sun, 09 May 1999 13:56:24 +0300
Hannu Krosing <hannu@trust.ee>
Re: [HACKERS] ODMG interface

> 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

Sun, 09 May 1999 12:03:16 +0000
Chris Bitmead <chris.bitmead@bigfoot.com>
Re: [HACKERS] ODMG interface
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

Sun, 09 May 1999 13:21:59 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] ODMG interface 

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, Pennsylvania 19026

Mon, 10 May 1999 13:24:02 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] pg_dump problem?

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

Tue, 11 May 1999 11:19:41 +0000
Chris Bitmead <chris.bitmead@bigfoot.com>
Re: [HACKERS] Date/Time Flaw in pg_dump ?
> 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

Tue, 11 May 1999 14:42:13 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] Date/Time Flaw in pg_dump ?
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

Tue, 11 May 1999 10:43:56 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Date/Time Flaw in pg_dump ? 
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

Tue, 11 May 1999 23:53:21 +0000
Chris Bitmead <chris.bitmead@bigfoot.com>
Re: [HACKERS] Date/Time Flaw in pg_dump ?
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?

Wed, 12 May 1999 00:12:26 +0000
Chris Bitmead <chris.bitmead@bigfoot.com>
Re: [HACKERS] Date/Time Flaw in pg_dump ?
> 
> 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

Wed, 12 May 1999 10:19:45 -0500 (CDT)
reedstrm@wallace.ece.rice.edu (Ross J. Reedstrom)
Re: [HACKERS] Date/Time Flaw in pg_dump ?

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

Fri, 14 May 1999 11:31:57 +0000
Chris Bitmead <chris.bitmead@bigfoot.com>
CVS
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


Fri, 14 May 1999 07:38:05 -0400 (EDT)
Todd Graham Lewis <tlewis@mindspring.net>
Re: [HACKERS] CVS
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


Fri, 14 May 1999 07:38:26 -0400 (EDT)
Todd Graham Lewis <tlewis@mindspring.net>
Re: [HACKERS] CVS
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


Fri, 14 May 1999 07:43:23 -0400 (EDT)
Todd Graham Lewis <tlewis@mindspring.net>
Re: [HACKERS] CVS

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

Fri, 14 May 1999 12:22:42 +0000
Chris Bitmead <chris.bitmead@bigfoot.com>
System Catalogs

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

Fri, 14 May 1999 12:34:15 +0000
Chris Bitmead <chris.bitmead@bigfoot.com>
ADD COLUMN
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

Fri, 14 May 1999 13:37:44 +0000
Bernard Frankpitt <frankpit@pop.dn.net>
Re: [HACKERS] System Catalogs
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

Fri, 14 May 1999 10:38:34 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] CVS 
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 


Fri, 14 May 1999 13:09:47 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] CVS 
> 
> 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, Pennsylvania 19026

Sat, 15 May 1999 04:54:08 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] System Catalogs
> 
> 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, Pennsylvania 19026

Sat, 15 May 1999 04:55:50 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] ADD COLUMN

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

Tue, 01 Jun 1999 21:10:17 +1000
Chris Bitmead <chris.bitmead@bigfoot.com>
ALTER TABLE ADD COLUMN

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

Tue, 01 Jun 1999 21:13:13 +1000
Chris Bitmead <chris.bitmead@bigfoot.com>
pg_dump
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) #



Tue, 1 Jun 1999 14:20:35 +0200 (MET DST)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] pg_dump
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 query
 You are currently connected to the database: foobar

foobar=> select * from foo;
a|b
-+-
3|4
(1 row)

foobar=> select * from bar;
?column?
--------
(0 rows)

foobar=>

Tue, 01 Jun 1999 22:37:13 +1000
Chris Bitmead <chris.bitmead@bigfoot.com>
Re: [HACKERS] pg_dump
> 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, Pennsylvania 19026

Tue, 1 Jun 1999 10:36:33 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] ALTER TABLE ADD COLUMN
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) #



Tue, 1 Jun 1999 16:40:47 +0200 (MET DST)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] pg_dump
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.

Wed, 02 Jun 1999 10:50:11 +1000
Chris Bitmead <chris.bitmead@bigfoot.com>
Re: [HACKERS] ALTER TABLE ADD COLUMN

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)

Mon, 07 Jun 1999 17:22:44 +1000
Chris Bitmead <chris.bitmead@bigfoot.com>
Bug in LIKE ?
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 


Mon, 7 Jun 1999 04:35:37 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
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)

Bye.
 
-- 
 Daniele

-------------------------------------------------------------------------------
 Daniele Orlandi - Utility Line Italia - http://www.orlandi.com
 Via Mezzera 29/A - 20030 - Seveso (MI) - Italy
-------------------------------------------------------------------------------



Mon, 07 Jun 1999 14:27:46 +0200
Daniele Orlandi <daniele@orlandi.com>
Re: [HACKERS] Bug in LIKE ?
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



Mon, 7 Jun 1999 17:00:11 +0400 (MSD)
Oleg Bartunov <oleg@sai.msu.su>
Re: [HACKERS] Bug in LIKE ?
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

Mon, 07 Jun 1999 09:44:07 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Bug in LIKE ? 
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 


Mon, 7 Jun 1999 10:50:47 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Bug in LIKE ? 
> 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, Pennsylvania 19026

Mon, 7 Jun 1999 10:26:30 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Bug in LIKE ?
> >                  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, Pennsylvania 19026

Mon, 7 Jun 1999 10:28:52 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Bug in LIKE ?
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 transforming
	b LIKE 'foo%'
into
	b 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

Mon, 07 Jun 1999 10:33:43 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Bug in LIKE ? 
> > > 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

Mon, 07 Jun 1999 14:47:51 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] Bug in LIKE ?
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

Mon, 07 Jun 1999 11:07:39 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Bug in LIKE ? 
> 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

Mon, 07 Jun 1999 15:27:26 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] Bug in LIKE ?
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

Mon, 07 Jun 1999 11:33:04 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Bug in LIKE ? 
> 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

Mon, 07 Jun 1999 15:47:48 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] Bug in LIKE ?
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
==========================================================================




Mon, 7 Jun 1999 12:11:57 -0400 (EDT)
Vince Vielhaber <vev@michvhf.com>
Re: [HACKERS] Bug in LIKE ?
> 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

Mon, 07 Jun 1999 16:31:23 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] Bug in LIKE ?
> > 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, Pennsylvania 19026

Mon, 7 Jun 1999 13:20:52 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Bug in LIKE ?
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 conditions
	b >= '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

Mon, 07 Jun 1999 13:59:08 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Bug in LIKE ? 