Thread

1. pg_dump problem?

   Chris Bitmead <chris.bitmead@bigfoot.com> — 1999-05-07T03:36:59Z

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

2. Re:pg_dump barfs?

   Chris Bitmead <chris.bitmead@bigfoot.com> — 1999-05-07T04:54:51Z

   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"
   
   

3. Re: [HACKERS] Re:pg_dump barfs?

   Chris Bitmead <chris.bitmead@bigfoot.com> — 1999-05-07T05:14:45Z

   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"
   
   

4. Re: [HACKERS] Re:pg_dump barfs?

   Chris Bitmead <chris.bitmead@bigfoot.com> — 1999-05-07T07:20:19Z

   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"
   
   

5. Re: [HACKERS] pg_dump problem?

   D'Arcy Cain <darcy@druid.net> — 1999-05-07T11:48:32Z

   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.
   
   

6. Re: [HACKERS] pg_dump problem?

   Chris Bitmead <chris.bitmead@bigfoot.com> — 1999-05-07T12:53:20Z

   > 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
   
   

7. Re: [HACKERS] pg_dump problem?

   Brian E Gallew <geek+@cmu.edu> — 1999-05-07T13:47:54Z

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

8. Re: [HACKERS] pg_dump problem?

   Chris Bitmead <chris.bitmead@bigfoot.com> — 1999-05-07T15:12:04Z

   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.
   
   

9. Re: [HACKERS] pg_dump problem?

   Brian E Gallew <geek+@cmu.edu> — 1999-05-07T15:25:35Z

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

10. Re: [HACKERS] pg_dump problem?

    Bruce Momjian <maillist@candle.pha.pa.us> — 1999-05-07T22:59:34Z

    > 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
    
    

11. Date/Time Flaw in pg_dump ?

    Chris Bitmead <chris.bitmead@bigfoot.com> — 1999-05-08T09:12:20Z

    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?
    
    

12. ODMG interface

    Chris Bitmead <chris.bitmead@bigfoot.com> — 1999-05-08T09:32:22Z

    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
    
    

13. Re: [HACKERS] Date/Time Flaw in pg_dump ?

    Thomas Lockhart <lockhart@alumni.caltech.edu> — 1999-05-08T16:55:43Z

    > 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
    
    

14. Re: [HACKERS] pg_dump problem?

    Bruce Momjian <maillist@candle.pha.pa.us> — 1999-05-10T17:24:02Z

    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
    
    

15. Re: [HACKERS] Date/Time Flaw in pg_dump ?

    Chris Bitmead <chris.bitmead@bigfoot.com> — 1999-05-11T11:19:41Z

    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
    
    

16. Re: [HACKERS] Date/Time Flaw in pg_dump ?

    Thomas Lockhart <lockhart@alumni.caltech.edu> — 1999-05-11T14:42:13Z

    > 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
    
    

17. Re: [HACKERS] Date/Time Flaw in pg_dump ?

    Chris Bitmead <chris.bitmead@bigfoot.com> — 1999-05-12T00:12:26Z

    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?
    
    

18. Re: [HACKERS] Date/Time Flaw in pg_dump ?

    Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu> — 1999-05-12T15:19:45Z

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

19. CVS

    Chris Bitmead <chris.bitmead@bigfoot.com> — 1999-05-14T11:31:57Z

    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
    
    

20. Re: [HACKERS] CVS

    Todd Graham Lewis <tlewis@mindspring.net> — 1999-05-14T11:38:05Z

    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
    
    
    

21. Re: [HACKERS] CVS

    Todd Graham Lewis <tlewis@mindspring.net> — 1999-05-14T11:38:26Z

    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
    
    
    

22. Re: [HACKERS] CVS

    Todd Graham Lewis <tlewis@mindspring.net> — 1999-05-14T11:43:23Z

    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
    
    
    

23. System Catalogs

    Chris Bitmead <chris.bitmead@bigfoot.com> — 1999-05-14T12:22:42Z

    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
    
    

24. ADD COLUMN

    Chris Bitmead <chris.bitmead@bigfoot.com> — 1999-05-14T12:34:15Z

    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
    
    

25. Re: [HACKERS] System Catalogs

    Bernard Adrian Frankpitt <frankpit@pop.dn.net> — 1999-05-14T13:37:44Z

    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
    
    

26. Re: [HACKERS] System Catalogs

    Bruce Momjian <maillist@candle.pha.pa.us> — 1999-05-15T08:54:08Z

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

27. Re: [HACKERS] ADD COLUMN

    Bruce Momjian <maillist@candle.pha.pa.us> — 1999-05-15T08:55:50Z

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

28. ALTER TABLE ADD COLUMN

    Chris Bitmead <chris.bitmead@bigfoot.com> — 1999-06-01T11:10:17Z

    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
    
    

29. pg_dump

    Chris Bitmead <chris.bitmead@bigfoot.com> — 1999-06-01T11:13:13Z

    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
    
    

30. Re: [HACKERS] pg_dump

    Jan Wieck <wieck@debis.com> — 1999-06-01T12:20:35Z

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

31. Re: [HACKERS] pg_dump

    Chris Bitmead <chris.bitmead@bigfoot.com> — 1999-06-01T12:37:13Z

    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=>
    
    

32. Re: [HACKERS] ALTER TABLE ADD COLUMN

    Bruce Momjian <maillist@candle.pha.pa.us> — 1999-06-01T14:36:33Z

    > 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
    
    

33. Re: [HACKERS] pg_dump

    Jan Wieck <wieck@debis.com> — 1999-06-01T14:40:47Z

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

34. Re: [HACKERS] ALTER TABLE ADD COLUMN

    Chris Bitmead <chris.bitmead@bigfoot.com> — 1999-06-02T00:50:11Z

    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.
    
    

35. Bug in LIKE ?

    Chris Bitmead <chris.bitmead@bigfoot.com> — 1999-06-07T07:22:44Z

    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)
    
    

36. Re: [HACKERS] Bug in LIKE ?

    Marc G. Fournier <scrappy@hub.org> — 1999-06-07T07:35:37Z

    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 
    
    
    

37. Re: [HACKERS] Bug in LIKE ?

    Daniele Orlandi <daniele@orlandi.com> — 1999-06-07T12:27:46Z

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

38. Re: [HACKERS] Bug in LIKE ?

    Oleg Bartunov <oleg@sai.msu.su> — 1999-06-07T13:00:11Z

    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