Thread: Open 7.1 items

Open 7.1 items

From
Bruce Momjian
Date:
Here are my open 7.1 items.  Thanks for shrinking the list so far.

---------------------------------------------------------------------------

FreeBSD locale bug
Reorder INSERT firing in rules
Philip Warner UPDATE crash
JDBC LargeObject short read return value missing
SELECT cash_out(1) crashes all backends
LAZY VACUUM
FOREIGN KEY INSERT & UPDATE/DELETE in transaction "change violation"
Usernames limited in length
Does pg_dump preserve COMMENTs?
Failure of nested cursors in JDBC
JDBC setMaxRows() is global variable affecting other objects
Does JDBC Makefile need current dir?
Fix for pg_dump of bad system tables
Steve Howe failure query with rules
ODBC/JDBC not disconnecting properly?
Magnus Hagander ODBC issues?
Merge MySQL/PgSQL translation scripts
Fix ipcclean on Linux
Merge global and template BKI files?


--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@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

Re: Open 7.1 items

From
Oleg Bartunov
Date:
On Wed, 24 Jan 2001, Bruce Momjian wrote:

> Here are my open 7.1 items.  Thanks for shrinking the list so far.
>
> ---------------------------------------------------------------------------
>
> FreeBSD locale bug

AFAIK, Tom have fixed it, if this bug is about -funsigned-char

> Reorder INSERT firing in rules
> Philip Warner UPDATE crash
> JDBC LargeObject short read return value missing
> SELECT cash_out(1) crashes all backends
> LAZY VACUUM
> FOREIGN KEY INSERT & UPDATE/DELETE in transaction "change violation"
> Usernames limited in length
> Does pg_dump preserve COMMENTs?
> Failure of nested cursors in JDBC
> JDBC setMaxRows() is global variable affecting other objects
> Does JDBC Makefile need current dir?
> Fix for pg_dump of bad system tables
> Steve Howe failure query with rules
> ODBC/JDBC not disconnecting properly?
> Magnus Hagander ODBC issues?
> Merge MySQL/PgSQL translation scripts
> Fix ipcclean on Linux
> Merge global and template BKI files?
>
>
>

    Regards,
        Oleg
_____________________________________________________________
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


Re: Open 7.1 items

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Here are my open 7.1 items.  Thanks for shrinking the list so far.

> SELECT cash_out(1) crashes all backends

This isn't a "must fix for 7.1", any more than it was for 7.0, 6.5,
or any other release back to the beginning of time.  It's always been
possible to crash the backend by passing an incompatible argument to
a type input or output function.  The type-checking system cannot
detect the error because these functions are (mostly) declared to
take "any" input type (zero entry in proargtypes[]).

The only clean way to fix this is to declare I/O functions honestly.
That will require (a) a type-system representation for "C string"
and (b) a solution to the circularity issue for user-defined types.
If the I/O functions have to be declared first, how can they refer
to the type?

Quite aside from the time involved, this will require an initdb.
It's a bit late in the cycle for that.
        regards, tom lane


Re: Open 7.1 items

From
Peter Eisentraut
Date:
Bruce Momjian writes:

> FOREIGN KEY INSERT & UPDATE/DELETE in transaction "change violation"

You're certainly not going to want to fix this now after having stared at
it for a year?  It's not trivial.

> Usernames limited in length

Yeah, they are. ;-)

If this is referring to pg_passwd, I just had a closer look and it's
really a desaster.  Both password and username as well as line length and
file length (in lines) have arbitrary limits, sometimes not even
consistent ones.  To fix this to a point where one is confident that
everything works one essentially would have to rewrite the whole thing.

> Does pg_dump preserve COMMENTs?

Sure

> Fix ipcclean on Linux

Consider it done.

> Merge global and template BKI files?

Not this release.

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/



Re: Open 7.1 items

From
Tatsuo Ishii
Date:
> Fix for pg_dump of bad system tables

Ok. I have made patches for fixing some of pg_dump problems(see
attached patches). The patches address the problem with user defined
functions, operators and aggregates. Could someone please review and
commit them if they look ok?  (I'm now in US and have only very
expensive internet connection through an international phone call to
Japan in a hotel! Also I'm not quite sure "#arg" (stringification) is
portable enough in all platforms.)  Or I could commit after going back
to Japan planned on Feb 2 if that's not too late.

However I have not address what Tom Lane said yet(actually I do not
understand what he says).

> The other flavor of problems that pg_dump
> has in this area are in doing inner joins across system catalogs ...
>
>             regards, tom lane

Index: common.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/bin/pg_dump/common.c,v
retrieving revision 1.49
diff -c -r1.49 common.c
*** common.c    2001/01/12 15:41:29    1.49
--- common.c    2001/01/21 01:38:48
***************
*** 86,95 ****
          }
      }

!     /* should never get here */
!     fprintf(stderr, "failed sanity check, type with oid %s was not found\n",
!             oid);
!     exit(2);
  }

  /*
--- 86,93 ----
          }
      }

!     /* no suitable type name was found */
!     return(NULL);
  }

  /*
***************
*** 114,120 ****
      /* should never get here */
      fprintf(stderr, "failed sanity check, opr with oid %s was not found\n",
              oid);
!     exit(2);
  }


--- 112,120 ----
      /* should never get here */
      fprintf(stderr, "failed sanity check, opr with oid %s was not found\n",
              oid);
!
!     /* no suitable operator name was found */
!     return(NULL);
  }


*** pg_dump.c.orig    Fri Jan 26 06:56:09 2001
--- pg_dump.c    Fri Jan 26 06:35:26 2001
***************
*** 2928,2933 ****
--- 2928,2942 ----
              char       *elemType;

              elemType = findTypeByOid(tinfo, numTypes, tinfo[i].typelem, zeroAsOpaque);
+             if (elemType == NULL)
+             {
+                 fprintf(stderr, "Notice: type for oid %s is not dumped.\n",
+                         tinfo[i].typelem);
+                 resetPQExpBuffer(q);
+                 resetPQExpBuffer(delq);
+                 continue;
+             }
+
              appendPQExpBuffer(q, ", element = %s, delimiter = ", elemType);
              formatStringLiteral(q, tinfo[i].typdelim);
          }
***************
*** 3086,3091 ****
--- 3095,3101 ----
      char        *listSep;
      char        *listSepComma = ",";
      char        *listSepNone = "";
+     char        *rettypename;

      if (finfo[i].dumped)
          return;
***************
*** 3147,3152 ****
--- 3157,3177 ----
          char            *typname;

          typname = findTypeByOid(tinfo, numTypes, finfo[i].argtypes[j], zeroAsOpaque);
+         if (typname == NULL)
+         {
+             fprintf(stderr, "Notice: function \"%s\" is not dumped.\n",
+                     finfo[i].proname);
+
+             fprintf(stderr, "Reason: the %d th argument type name (oid %s) not found.\n",
+                     j, finfo[i].argtypes[j]);
+             resetPQExpBuffer(q);
+             resetPQExpBuffer(fn);
+             resetPQExpBuffer(delqry);
+             resetPQExpBuffer(fnlist);
+             resetPQExpBuffer(asPart);
+             return;
+         }
+
          appendPQExpBuffer(fn, "%s%s",
                              (j > 0) ? "," : "",
                              typname);
***************
*** 3159,3169 ****
      resetPQExpBuffer(delqry);
      appendPQExpBuffer(delqry, "DROP FUNCTION %s;\n", fn->data );

      resetPQExpBuffer(q);
      appendPQExpBuffer(q, "CREATE FUNCTION %s ", fn->data );
      appendPQExpBuffer(q, "RETURNS %s%s %s LANGUAGE ",
                        (finfo[i].retset) ? "SETOF " : "",
!                       findTypeByOid(tinfo, numTypes, finfo[i].prorettype, zeroAsOpaque),
                        asPart->data);
      formatStringLiteral(q, func_lang);

--- 3184,3211 ----
      resetPQExpBuffer(delqry);
      appendPQExpBuffer(delqry, "DROP FUNCTION %s;\n", fn->data );

+     rettypename = findTypeByOid(tinfo, numTypes, finfo[i].prorettype, zeroAsOpaque);
+
+     if (rettypename == NULL)
+     {
+         fprintf(stderr, "Notice: function \"%s\" is not dumped.\n",
+                 finfo[i].proname);
+
+         fprintf(stderr, "Reason: return type name (oid %s) not found.\n",
+                 finfo[i].prorettype);
+             resetPQExpBuffer(q);
+             resetPQExpBuffer(fn);
+             resetPQExpBuffer(delqry);
+             resetPQExpBuffer(fnlist);
+             resetPQExpBuffer(asPart);
+             return;
+     }
+
      resetPQExpBuffer(q);
      appendPQExpBuffer(q, "CREATE FUNCTION %s ", fn->data );
      appendPQExpBuffer(q, "RETURNS %s%s %s LANGUAGE ",
                        (finfo[i].retset) ? "SETOF " : "",
!                       rettypename,
                        asPart->data);
      formatStringLiteral(q, func_lang);

***************
*** 3208,3213 ****
--- 3250,3261 ----
  dumpOprs(Archive *fout, OprInfo *oprinfo, int numOperators,
           TypeInfo *tinfo, int numTypes)
  {
+ #define OPR_NOTICE(arg) {\
+         fprintf(stderr, "Notice: operator \"%s\"(oid %s) is not dumped.\n",oprinfo[i].oprname, oprinfo[i].oid);\
+     fprintf(stderr, "Reason: " #arg);\
+     fprintf (stderr, " (oid %s) not found.\n",oprinfo[i].arg);\
+     }
+
      int            i;
      PQExpBuffer q = createPQExpBuffer();
      PQExpBuffer delq = createPQExpBuffer();
***************
*** 3222,3227 ****
--- 3270,3276 ----

      for (i = 0; i < numOperators; i++)
      {
+         char *name;

          resetPQExpBuffer(leftarg);
          resetPQExpBuffer(rightarg);
***************
*** 3250,3271 ****
          if (strcmp(oprinfo[i].oprkind, "r") == 0 ||
              strcmp(oprinfo[i].oprkind, "b") == 0)
          {
!             appendPQExpBuffer(leftarg, ",\n\tLEFTARG = %s ",
!                                 findTypeByOid(tinfo, numTypes, oprinfo[i].oprleft, zeroAsOpaque) );
          }
          if (strcmp(oprinfo[i].oprkind, "l") == 0 ||
              strcmp(oprinfo[i].oprkind, "b") == 0)
          {
!             appendPQExpBuffer(rightarg, ",\n\tRIGHTARG = %s ",
!                               findTypeByOid(tinfo, numTypes, oprinfo[i].oprright, zeroAsOpaque) );
          }
          if (!(strcmp(oprinfo[i].oprcom, "0") == 0))
!             appendPQExpBuffer(commutator, ",\n\tCOMMUTATOR = %s ",
!                  findOprByOid(oprinfo, numOperators, oprinfo[i].oprcom));

          if (!(strcmp(oprinfo[i].oprnegate, "0") == 0))
!             appendPQExpBuffer(negator, ",\n\tNEGATOR = %s ",
!               findOprByOid(oprinfo, numOperators, oprinfo[i].oprnegate));

          if (!(strcmp(oprinfo[i].oprrest, "-") == 0))
              appendPQExpBuffer(restrictor, ",\n\tRESTRICT = %s ", oprinfo[i].oprrest);
--- 3299,3348 ----
          if (strcmp(oprinfo[i].oprkind, "r") == 0 ||
              strcmp(oprinfo[i].oprkind, "b") == 0)
          {
!             name = findTypeByOid(tinfo, numTypes,
!                                   oprinfo[i].oprleft, zeroAsOpaque);
!             if (name == NULL)
!             {
!                 OPR_NOTICE(oprleft);
!                 continue;
!             }
!             appendPQExpBuffer(leftarg, ",\n\tLEFTARG = %s ",name);
          }
+
          if (strcmp(oprinfo[i].oprkind, "l") == 0 ||
              strcmp(oprinfo[i].oprkind, "b") == 0)
          {
!             name = findTypeByOid(tinfo, numTypes,
!                                   oprinfo[i].oprright, zeroAsOpaque);
!             if (name == NULL)
!             {
!                 OPR_NOTICE(oprright);
!                 continue;
!             }
!             appendPQExpBuffer(rightarg, ",\n\tRIGHTARG = %s ", name);
          }
+
          if (!(strcmp(oprinfo[i].oprcom, "0") == 0))
!         {
!             name = findOprByOid(oprinfo, numOperators, oprinfo[i].oprcom);
!             if (name == NULL)
!             {
!                 OPR_NOTICE(oprcom);
!                 continue;
!             }
!             appendPQExpBuffer(commutator, ",\n\tCOMMUTATOR = %s ", name);
!         }

          if (!(strcmp(oprinfo[i].oprnegate, "0") == 0))
!         {
!             name = findOprByOid(oprinfo, numOperators, oprinfo[i].oprnegate);
!             if (name == NULL)
!             {
!                 OPR_NOTICE(oprnegate);
!                 continue;
!             }
!             appendPQExpBuffer(negator, ",\n\tNEGATOR = %s ", name);
!         }

          if (!(strcmp(oprinfo[i].oprrest, "-") == 0))
              appendPQExpBuffer(restrictor, ",\n\tRESTRICT = %s ", oprinfo[i].oprrest);
***************
*** 3274,3285 ****
              appendPQExpBuffer(join, ",\n\tJOIN = %s ", oprinfo[i].oprjoin);

          if (!(strcmp(oprinfo[i].oprlsortop, "0") == 0))
!             appendPQExpBuffer(sort1, ",\n\tSORT1 = %s ",
!              findOprByOid(oprinfo, numOperators, oprinfo[i].oprlsortop));

          if (!(strcmp(oprinfo[i].oprrsortop, "0") == 0))
!             appendPQExpBuffer(sort2, ",\n\tSORT2 = %s ",
!              findOprByOid(oprinfo, numOperators, oprinfo[i].oprrsortop));

          resetPQExpBuffer(delq);
          appendPQExpBuffer(delq, "DROP OPERATOR %s (%s", oprinfo[i].oprname,
--- 3351,3376 ----
              appendPQExpBuffer(join, ",\n\tJOIN = %s ", oprinfo[i].oprjoin);

          if (!(strcmp(oprinfo[i].oprlsortop, "0") == 0))
!         {
!             name =     findOprByOid(oprinfo, numOperators, oprinfo[i].oprlsortop);
!             if (name == NULL)
!             {
!                 OPR_NOTICE(oprlsortop);
!                 continue;
!             }
!             appendPQExpBuffer(sort1, ",\n\tSORT1 = %s ", name);
!         }

          if (!(strcmp(oprinfo[i].oprrsortop, "0") == 0))
!         {
!             name =     findOprByOid(oprinfo, numOperators, oprinfo[i].oprrsortop);
!             if (name == NULL)
!             {
!                 OPR_NOTICE(oprrsortop);
!                 continue;
!             }
!             appendPQExpBuffer(sort2, ",\n\tSORT2 = %s ", name);
!         }

          resetPQExpBuffer(delq);
          appendPQExpBuffer(delq, "DROP OPERATOR %s (%s", oprinfo[i].oprname,
***************
*** 3317,3322 ****
--- 3408,3419 ----
  dumpAggs(Archive *fout, AggInfo *agginfo, int numAggs,
           TypeInfo *tinfo, int numTypes)
  {
+ #define AGG_NOTICE(arg) {\
+         fprintf(stderr, "Notice: aggregate \"%s\"(oid %s) is not dumped.\n",agginfo[i].aggname, agginfo[i].oid);\
+     fprintf(stderr, "Reason: " #arg);\
+     fprintf (stderr, " (oid %s) not found.\n",agginfo[i].arg);\
+     }
+
      int            i;
      PQExpBuffer q = createPQExpBuffer();
      PQExpBuffer delq = createPQExpBuffer();
***************
*** 3325,3344 ****

      for (i = 0; i < numAggs; i++)
      {
          resetPQExpBuffer(details);

          /* skip all the builtin oids */
          if (atooid(agginfo[i].oid) <= g_last_builtin_oid)
              continue;

!         appendPQExpBuffer(details,
!                           "BASETYPE = %s, ",
!                           findTypeByOid(tinfo, numTypes, agginfo[i].aggbasetype, zeroAsAny + useBaseTypeName));

          appendPQExpBuffer(details,
                            "SFUNC = %s, STYPE = %s",
!                           agginfo[i].aggtransfn,
!                           findTypeByOid(tinfo, numTypes, agginfo[i].aggtranstype, zeroAsOpaque + useBaseTypeName));

          if (agginfo[i].agginitval)
          {
--- 3422,3452 ----

      for (i = 0; i < numAggs; i++)
      {
+         char *name;
+
          resetPQExpBuffer(details);

          /* skip all the builtin oids */
          if (atooid(agginfo[i].oid) <= g_last_builtin_oid)
              continue;

!         name = findTypeByOid(tinfo, numTypes, agginfo[i].aggbasetype, zeroAsAny + useBaseTypeName);
!         if (name == NULL)
!         {
!             AGG_NOTICE(aggbasetype);
!             continue;
!         }
!         appendPQExpBuffer(details, "BASETYPE = %s, ", name);

+         name = findTypeByOid(tinfo, numTypes, agginfo[i].aggtranstype, zeroAsOpaque + useBaseTypeName);
+         if (name == NULL)
+         {
+             AGG_NOTICE(aggtranstype);
+             continue;
+         }
          appendPQExpBuffer(details,
                            "SFUNC = %s, STYPE = %s",
!                           agginfo[i].aggtransfn, name);

          if (agginfo[i].agginitval)
          {

Re: Open 7.1 items

From
Hiroshi Inoue
Date:
Peter Eisentraut wrote:
> 
> Bruce Momjian writes:
> 
> > FOREIGN KEY INSERT & UPDATE/DELETE in transaction "change violation"
> 
> You're certainly not going to want to fix this now after having stared at
> it for a year?  It's not trivial.
> 

What does this item mean ?

> > Usernames limited in length
> 
> Yeah, they are. ;-)
> 
> If this is referring to pg_passwd, I just had a closer look and it's
> really a desaster.  Both password and username as well as line length and
> file length (in lines) have arbitrary limits, sometimes not even
> consistent ones.  To fix this to a point where one is confident that
> everything works one essentially would have to rewrite the whole thing.
> 
> > Does pg_dump preserve COMMENTs?
> 
> Sure
> 
> > Fix ipcclean on Linux
> 
> Consider it done.
> 
> > Merge global and template BKI files?
> 
> Not this release.
> 
> --
> Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/


Re: Open 7.1 items

From
Hiroshi Inoue
Date:
Sorry for my previous incomplete posting.

Peter Eisentraut wrote:
> 
> Bruce Momjian writes:
> 
> > FOREIGN KEY INSERT & UPDATE/DELETE in transaction "change violation"
> 
> You're certainly not going to want to fix this now after having stared at
> it for a year?  It's not trivial.
> 

What does this item mean ?
Is it the following ?
begin;insert into pk (id) values (1);update(delete from) pk where id=1;ERROR:  triggered data change violation on
relationpk"
 

If so, isn't it a simple bug ?

Regards,
Hiroshi Inoue


Re: [JDBC] Open 7.1 items

From
Peter T Mount
Date:
Quoting Bruce Momjian <pgman@candle.pha.pa.us>:

> Here are my open 7.1 items.  Thanks for shrinking the list so far.
>
> ---------------------------------------------------------------------------
>
> FreeBSD locale bug
> Reorder INSERT firing in rules
> Philip Warner UPDATE crash
> JDBC LargeObject short read return value missing

Working on this on Saturday.

> SELECT cash_out(1) crashes all backends
> LAZY VACUUM
> FOREIGN KEY INSERT & UPDATE/DELETE in transaction "change violation"
> Usernames limited in length
> Does pg_dump preserve COMMENTs?
> Failure of nested cursors in JDBC

JDBC doesn't support cursors full stop yet.

> JDBC setMaxRows() is global variable affecting other objects
> Does JDBC Makefile need current dir?

No as it's obsolete in 7.1 ;-)

> Fix for pg_dump of bad system tables
> Steve Howe failure query with rules
> ODBC/JDBC not disconnecting properly?

Client code not calling Connection.close() method.

> Magnus Hagander ODBC issues?
> Merge MySQL/PgSQL translation scripts
> Fix ipcclean on Linux
> Merge global and template BKI files?

Peter

--
Peter Mount peter@retep.org.uk
PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/
RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/

Re: Open 7.1 items

From
Peter Eisentraut
Date:
Hiroshi Inoue writes:

> What does this item mean ?
> Is it the following ?
>
>     begin;
>     insert into pk (id) values (1);
>     update(delete from) pk where id=1;
>     ERROR:  triggered data change violation on relation pk"
>
> If so, isn't it a simple bug ?

Depends on the definition of "bug".  It's not spec compliant and it's not
documented and it's annoying.  But it's been like this for a year and the
issue is well known and can normally be avoided.  It looks like a
documentation to-do to me.

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/



Re: Open 7.1 items

From
Jan Wieck
Date:
Bruce Momjian wrote:
> Here are my open 7.1 items.  Thanks for shrinking the list so far.
>
> ---------------------------------------------------------------------------
>
> FreeBSD locale bug
> Reorder INSERT firing in rules
   I  don't  recall  why this is wanted. AFAIK there's no reason   NOT to do so, except for the actual state of beeing
far too   close to a release candidate.
 

> Philip Warner UPDATE crash
> JDBC LargeObject short read return value missing
> SELECT cash_out(1) crashes all backends
> LAZY VACUUM
> FOREIGN KEY INSERT & UPDATE/DELETE in transaction "change violation"
   A well known issue, and I've asked multiple times how exactly   we want to define the behaviour for deferred
constraints. Do   foreign keys reference just to a key value and are happy with   it's existance, or do they refer to a
particularrow?
 
   Consider you have a deferred "ON DELETE  CASCADE"  constraint   and  do  a  DELETE, INSERT of a PK. Do the FK rows
needto be   deleted or not?
 
   Consider you have a deferred "ON  DELETE  RESTRICT"  and  "ON   UPDATE  CASCADE" constraint. If you DELETE PK1 and
UPDATEPK2   to PK1, the FK2 rows need to follow, but does PK2 inherit all   FK1 rows now so it's the master of both
groups?
   These  are  only two possible combinations. There are many to   think of.  As said, I've asked before, but noone
voted yet.   Move  the item to 7.2 anyway, because changing this behaviour   would require massive changes in the
triggerqueue *and*  the   generic  RI triggers, which cannot be tested enough any more.
 


Jan

> Usernames limited in length
> Does pg_dump preserve COMMENTs?
> Failure of nested cursors in JDBC
> JDBC setMaxRows() is global variable affecting other objects
> Does JDBC Makefile need current dir?
> Fix for pg_dump of bad system tables
> Steve Howe failure query with rules
> ODBC/JDBC not disconnecting properly?
> Magnus Hagander ODBC issues?
> Merge MySQL/PgSQL translation scripts
> Fix ipcclean on Linux
> Merge global and template BKI files?
>
>
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@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
>


--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com



RE: Open 7.1 items

From
"Mikheev, Vadim"
Date:
> > FOREIGN KEY INSERT & UPDATE/DELETE in transaction "change violation"
> 
>     A well known issue, and I've asked multiple times how exactly
>     we want to define the behaviour for deferred constraints.  Do
>     foreign keys reference just to a key value and are happy with
>     it's existance, or do they refer to a particular row?

I think first. The last is closer to OODBMS world, not to [O]RDBMS one.

>     Consider you have a deferred "ON DELETE  CASCADE"  constraint
>     and  do  a  DELETE, INSERT of a PK. Do the FK rows need to be
>     deleted or not?

Good example. I think FK should not be deleted. If someone really
want to delete "old" FK then he can do 

DELETE PK;
SET CONSTRAINT ... IMMEDIATE; -- FK need to be deleted here
INSERT PK;

>     Consider you have a deferred "ON  DELETE  RESTRICT"  and  "ON
>     UPDATE  CASCADE" constraint. If you DELETE PK1 and UPDATE PK2
>     to PK1, the FK2 rows need to follow, but does PK2 inherit all
>     FK1 rows now so it's the master of both groups?

Yes. Again one can use SET CONSTRAINT to achieve desirable results.
It seems that SET CONSTRAINT was designed for these purposes - ie
for better flexibility.

Though, it would be better to look how other DBes handle all these
cases -:)

Vadim


Re: Open 7.1 items

From
Tom Lane
Date:
Jan Wieck <janwieck@Yahoo.com> writes:
> Bruce Momjian wrote:
>> Reorder INSERT firing in rules

>     I  don't  recall  why this is wanted. AFAIK there's no reason
>     NOT to do so, except for the actual state of beeing  far  too
>     close to a release candidate.

I think I've been the main person arguing for this change, but I
agree with Jan that this late in the cycle is not the time to be
making such decisions.  Let's discuss it again during 7.2 cycle.
        regards, tom lane


Re: Re: [JDBC] Open 7.1 items

From
Bruce Momjian
Date:
[ Charset ISO-8859-1 unsupported, converting... ]
> Quoting Bruce Momjian <pgman@candle.pha.pa.us>:
>
> > Here are my open 7.1 items.  Thanks for shrinking the list so far.
> >
> > ---------------------------------------------------------------------------
> >
> > FreeBSD locale bug
> > Reorder INSERT firing in rules
> > Philip Warner UPDATE crash
> > JDBC LargeObject short read return value missing
>
> Working on this on Saturday.

OK.

>
> > SELECT cash_out(1) crashes all backends
> > LAZY VACUUM
> > FOREIGN KEY INSERT & UPDATE/DELETE in transaction "change violation"
> > Usernames limited in length
> > Does pg_dump preserve COMMENTs?
> > Failure of nested cursors in JDBC
>
> JDBC doesn't support cursors full stop yet.

Removed from list.  Doesn't even seem worth adding to TODO.

>
> > JDBC setMaxRows() is global variable affecting other objects
> > Does JDBC Makefile need current dir?
>
> No as it's obsolete in 7.1 ;-)

Removed.

>
> > Fix for pg_dump of bad system tables
> > Steve Howe failure query with rules
> > ODBC/JDBC not disconnecting properly?
>
> Client code not calling Connection.close() method.

Removed.  ODBC seems to have a problem, though.

Thanks.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@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

Re: Open 7.1 items

From
Bruce Momjian
Date:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Here are my open 7.1 items.  Thanks for shrinking the list so far.
> 
> > SELECT cash_out(1) crashes all backends
> 

OK, removed from 'open' list and added to TODO.  Actually, I can't get
the crash to happen except with cash_out.  Is there another *out
function you can get to fail.


> This isn't a "must fix for 7.1", any more than it was for 7.0, 6.5,
> or any other release back to the beginning of time.  It's always been
> possible to crash the backend by passing an incompatible argument to
> a type input or output function.  The type-checking system cannot
> detect the error because these functions are (mostly) declared to
> take "any" input type (zero entry in proargtypes[]).
> 
> The only clean way to fix this is to declare I/O functions honestly.
> That will require (a) a type-system representation for "C string"
> and (b) a solution to the circularity issue for user-defined types.
> If the I/O functions have to be declared first, how can they refer
> to the type?
> 
> Quite aside from the time involved, this will require an initdb.
> It's a bit late in the cycle for that.
> 
>             regards, tom lane
> 


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


Re: Open 7.1 items

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> SELECT cash_out(1) crashes all backends
>> 

> OK, removed from 'open' list and added to TODO.  Actually, I can't get
> the crash to happen except with cash_out.  Is there another *out
> function you can get to fail.

Any pass-by-reference type; also most if not all input functions.
        regards, tom lane


Re: Open 7.1 items

From
Bruce Momjian
Date:
> Bruce Momjian writes:
> 
> > FOREIGN KEY INSERT & UPDATE/DELETE in transaction "change violation"
> 
> You're certainly not going to want to fix this now after having stared at
> it for a year?  It's not trivial.

Moved to TODO.

> 
> > Usernames limited in length
> 
> Yeah, they are. ;-)
> 
> If this is referring to pg_passwd, I just had a closer look and it's
> really a desaster.  Both password and username as well as line length and
> file length (in lines) have arbitrary limits, sometimes not even
> consistent ones.  To fix this to a point where one is confident that
> everything works one essentially would have to rewrite the whole thing.

Added to TODO:
* Fix username/password length limits in all areas
> 
> > Does pg_dump preserve COMMENTs?
> 
> Sure

OK, thanks.  Someone submitted a patch, and I wasn't sure how to handle
it.  I thought it did it already.

> 
> > Fix ipcclean on Linux
> 
> Consider it done.

Thanks.

> 
> > Merge global and template BKI files?
> 
> Not this release.

Added to TODO.

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


Database tables disappeared.

From
Stefan Klopp
Date:
I was woundering if any of you have seen this problem.

I have been running a system using a postgres 6.5 database. After a while
I realized the system wasn't very active so I looked into it. I tryed to view
all the tables in the database using the \d command, but it return:

- Couldn't find any tables, sequences or indices!

So I then tried to do a select statement from the table, which worked,
although no data was returned, but the table structure was correct.

I have backed up my database and have been trying to recover my data with no
luck. I am able to vacuum the tables and have it tell me that there are
several those tuples in each table, yet I still cant view the data.


Any suggestions?


Thanks,

Stefan Klopp

ecmarket



Re: Open 7.1 items

From
Philip Warner
Date:
At 07:24 26/01/01 +0900, Tatsuo Ishii wrote:
>> Fix for pg_dump of bad system tables
>
>Ok. I have made patches for fixing some of pg_dump problems(see
>attached patches). 
...
>Also I'm not quite sure "#arg" (stringification) is
>portable enough in all platforms.)  

The patch looks fine to me, but I have no idea how portable #arg is - does
anybody else have some facts on the matter: 

+#define AGG_NOTICE(arg) {\
+               fprintf(stderr, "Notice: aggregate \"%s\"(oid %s) is not
dumped.\n",agginfo[i].aggname, a
+       fprintf(stderr, "Reason: " #arg);\
+       fprintf (stderr, " (oid %s) not found.\n",agginfo[i].arg);\
+       }
+

It's easy enough to change the macros to take 2 params, and that would be
my inclination if it's not a solid part of the C standard (for any
non-trivial definintion of 'solid').


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/


Re: Open 7.1 items

From
Tom Lane
Date:
Philip Warner <pjw@rhyme.com.au> writes:
>> Also I'm not quite sure "#arg" (stringification) is
>> portable enough in all platforms.)  

> The patch looks fine to me, but I have no idea how portable #arg is

Use the CppAsString macro from our c.h ... that's what it's for.

It's actually fairly unlikely that anyone still uses a compiler that
doesn't grok #arg and yet can handle the other ANSI-isms that Postgres
requires.  However, we may as well stick to the coding conventions
we have...
        regards, tom lane


Re: Open 7.1 items

From
Philip Warner
Date:
At 07:24 26/01/01 +0900, Tatsuo Ishii wrote:
>> Fix for pg_dump of bad system tables
>
>Ok. I have made patches for fixing some of pg_dump problems(see
>attached patches). The patches address the problem with user defined
>functions, operators and aggregates.

These have now been applied with minor modifications.


>However I have not address what Tom Lane said yet(actually I do not
>understand what he says).
>
>> The other flavor of problems that pg_dump
>> has in this area are in doing inner joins across system catalogs ...

This refers to things like:

        SELECT c.relname
        FROM pg_index i, pg_class c
        WHERE i.indrelid = %s
        AND   i.indisprimary
        AND   c.oid = i.indexrelid

ie. where two or more relations are crossed (pg_index and pg_class in this
case). It assumes that the metadata is valid, and will not highlight
missing data in the secondary table. We should be doing outer joins:

        SELECT c.relname
        FROM pg_index i LEFT OUTER JOIN pg_class c on c.oid = i.indexrelid
        WHERE i.indrelid = %s
        AND   i.indisprimary

and checking for nulls using PQgetisnull. I have actually done this for a
couple of SELECTs (including the above one), and marked all the others as
'XXXX: use LOJ'. There are only 2 or 3 more, but they require a little more
thought (an inderstanding) before I change them.

In my view this should be considered a 'work-in-progress' and not hold up
7.1 since the problem has been there for a long time.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Re: Open 7.1 items

From
Bruce Momjian
Date:
I assume this addresses the open item:

    Fix for pg_dump of bad system tables

I will remove it from the list.  Thanks.



> At 07:24 26/01/01 +0900, Tatsuo Ishii wrote:
> >> Fix for pg_dump of bad system tables
> >
> >Ok. I have made patches for fixing some of pg_dump problems(see
> >attached patches). The patches address the problem with user defined
> >functions, operators and aggregates.
>
> These have now been applied with minor modifications.
>
>
> >However I have not address what Tom Lane said yet(actually I do not
> >understand what he says).
> >
> >> The other flavor of problems that pg_dump
> >> has in this area are in doing inner joins across system catalogs ...
>
> This refers to things like:
>
>         SELECT c.relname
>         FROM pg_index i, pg_class c
>         WHERE i.indrelid = %s
>         AND   i.indisprimary
>         AND   c.oid = i.indexrelid
>
> ie. where two or more relations are crossed (pg_index and pg_class in this
> case). It assumes that the metadata is valid, and will not highlight
> missing data in the secondary table. We should be doing outer joins:
>
>         SELECT c.relname
>         FROM pg_index i LEFT OUTER JOIN pg_class c on c.oid = i.indexrelid
>         WHERE i.indrelid = %s
>         AND   i.indisprimary
>
> and checking for nulls using PQgetisnull. I have actually done this for a
> couple of SELECTs (including the above one), and marked all the others as
> 'XXXX: use LOJ'. There are only 2 or 3 more, but they require a little more
> thought (an inderstanding) before I change them.
>
> In my view this should be considered a 'work-in-progress' and not hold up
> 7.1 since the problem has been there for a long time.
>
>
> ----------------------------------------------------------------
> Philip Warner                    |     __---_____
> Albatross Consulting Pty. Ltd.   |----/       -  \
> (A.B.N. 75 008 659 498)          |          /(@)   ______---_
> Tel: (+61) 0500 83 82 81         |                 _________  \
> Fax: (+61) 0500 83 82 82         |                 ___________ |
> Http://www.rhyme.com.au          |                /           \|
>                                  |    --________--
> PGP key available upon request,  |  /
> and from pgp5.ai.mit.edu:11371   |/
>


--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@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

Re: Open 7.1 items

From
Philip Warner
Date:
At 23:14 27/01/01 -0500, Bruce Momjian wrote:
>I assume this addresses the open item:
>
>    Fix for pg_dump of bad system tables
>
>I will remove it from the list.  Thanks.
>

I'd remove it from the 'Required for 7.1' list, but leave it on the TODO
list, since the task is not quite complete.


>> At 07:24 26/01/01 +0900, Tatsuo Ishii wrote:
>> >> Fix for pg_dump of bad system tables
>> >
>> >Ok. I have made patches for fixing some of pg_dump problems(see
>> >attached patches). The patches address the problem with user defined
>> >functions, operators and aggregates.
>>
>> These have now been applied with minor modifications.
>>
...
>>
>> In my view this should be considered a 'work-in-progress' and not hold up
>> 7.1 since the problem has been there for a long time.
>>

----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Re: Open 7.1 items

From
Bruce Momjian
Date:
> At 23:14 27/01/01 -0500, Bruce Momjian wrote:
> >I assume this addresses the open item:
> >
> >    Fix for pg_dump of bad system tables
> >
> >I will remove it from the list.  Thanks.
> >
>
> I'd remove it from the 'Required for 7.1' list, but leave it on the TODO
> list, since the task is not quite complete.

OK, do you have some text for me.

FYI, the "open" list are not all must-do items for 7.1.  They are merely
items that either need to be done, or moved to the TODO list.  It is up
to the group to decide which one they want.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@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

Re: Open 7.1 items

From
Tatsuo Ishii
Date:
> >Ok. I have made patches for fixing some of pg_dump problems(see
> >attached patches). The patches address the problem with user defined
> >functions, operators and aggregates.
>
> These have now been applied with minor modifications.

Thanks. BTW, are you going to make a back patch for the 7.0.x tree?
I'm sure we are going to have lots of complaints from users who are
developing their own user defined functions and about to use pg_dump
to upgrade to 7.1 after it is officially released...
--
Tatsuo Ishii

Re: Open 7.1 items

From
Tatsuo Ishii
Date:
> >> Also I'm not quite sure "#arg" (stringification) is
> >> portable enough in all platforms.)  
> 
> > The patch looks fine to me, but I have no idea how portable #arg is
> 
> Use the CppAsString macro from our c.h ... that's what it's for.
> 
> It's actually fairly unlikely that anyone still uses a compiler that
> doesn't grok #arg and yet can handle the other ANSI-isms that Postgres
> requires.  However, we may as well stick to the coding conventions
> we have...

Oh I see.
--
Tatsuo Ishii


Re: Open 7.1 items

From
Tom Lane
Date:
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> Thanks. BTW, are you going to make a back patch for the 7.0.x tree?
> I'm sure we are going to have lots of complaints from users who are
> developing their own user defined functions and about to use pg_dump
> to upgrade to 7.1 after it is officially released...

No more than in any prior release, AFAICS; these issues have been there
for a long time.  I doubt that a back-patch is warranted.
        regards, tom lane


Re: Open 7.1 items

From
Philip Warner
Date:
At 02:17 30/01/01 +0900, Tatsuo Ishii wrote:
>> >Ok. I have made patches for fixing some of pg_dump problems(see
>> >attached patches). The patches address the problem with user defined
>> >functions, operators and aggregates.
>>
>> These have now been applied with minor modifications.
>
>Thanks. BTW, are you going to make a back patch for the 7.0.x tree?
>I'm sure we are going to have lots of complaints from users who are
>developing their own user defined functions and about to use pg_dump
>to upgrade to 7.1 after it is officially released...

Sorry for the delay, but this set of patches were targetted at trapping bad
metadata and as such are probably not appropriate for backporting. However,
some months ago, Tom did modify pg_dump for the new function manager
interface. It might be worth making a backpatch for this available.



----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/