As you can see from the current open items list, there isn't much left
to do for the 7.1 release.  I am going to suggest we remove the LAZY
VACUUM option at this point.  I know Tom Lane posted an item about the
join visibility issue, so hopefully this can be resolved soon.  Not sure
what to do about the "Stuck spinlocks" but we may have to leave that for
7.2 or see what problem reports we get from the current code.

The documentation list is pretty much done.  It would be nice to have
some more items completed, but I haven't see any comments about them.

So, where are we in the release cycle?  Are we ready to start looking at
dates to issue release candidates for testing?

Thomas Lockhart needs the docs frozen for a while so he can package
them.

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

                              P O S T G R E S Q L

                          7 . 1  O P E N    I T E M S


Current at ftp://candle.pha.pa.us/pub/postgresql/open_items.


Source Code Changes
-------------------
LAZY VACUUM (Vadim)
visibility of joined columns in JOIN clauses
Stuck btree spinlocks


Documentation Changes
---------------------
JDBC improvements (Peter, Travis Bauer, Christopher Cain, William Webber,
    Gunnar)
ODBC cleanups/improvements (Nick Gorham, Stephan Szabo, Zoltan Kovacs, 
    Michael Fork)
New PL/pgSQL GET DIAGNOSTICS statement for SPI value access (Jan)
Improve PL/PgSQL documentation (?)
Store tables as files named by OID (Vadim)
New /contrib/rserv replication toolkit (Vadim)
New /contrib/oid2name to map numeric files to table names
New pg_class.relkind value for views (Mark Hollomon)

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

Wed, 14 Feb 2001 11:24:51 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Open 7.1 items
Bruce Momjian writes:

> New PL/pgSQL GET DIAGNOSTICS statement for SPI value access (Jan)

If someone can show me an example of how it operates I can write up
something.

> Improve PL/PgSQL documentation (?)

I agree with the "(?)"...  Certainly a bit late to start an "improvement"
project.

> Store tables as files named by OID (Vadim)

This has never been documented to the contrary AFAIK.  There's an empty
"Storage" chapter, which would be a good place to put this --- eventually.

> New /contrib/rserv replication toolkit (Vadim)
> New /contrib/oid2name to map numeric files to table names

These both have their appropriate READMEs.

> New pg_class.relkind value for views (Mark Hollomon)

Documented.

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


Wed, 14 Feb 2001 18:27:23 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: Open 7.1 items
> Bruce Momjian writes:
> 
> > New PL/pgSQL GET DIAGNOSTICS statement for SPI value access (Jan)
> 
> If someone can show me an example of how it operates I can write up
> something.

I found:

  > Quoting a recent message by Jan Wieck <janwieck@Yahoo.com>:
  > :Do a
  > :
  > :    GET DIAGNOSTICS SELECT PROCESSED INTO <int4_variable>;  
  > :
  > :directly  after  an  INSERT,  UPDATE  or DELETE statement and you'll know
  > :how many rows have been hit.
  > :
  > :Also you can get the OID of an inserted row with
  > :
  > :    GET DIAGNOSTICS SELECT RESULT INTO <int4_variable>;
  > 

Looking at plpgsql/src/gram.y, it only supports PROCESSED (rows
returned/affected) and RESULT (OID).  The grammar indicates that only
SELECT is allowed in GET DIAGNOSTICS SELECT.  Jan says it works for
INSERT/UPDATE/DELETE too, but I guess you still use GET DIAGNOSTICS
SELECT.


> 
> > Improve PL/PgSQL documentation (?)
> 
> I agree with the "(?)"...  Certainly a bit late to start an "improvement"
> project.

I heard someone was working on that and was not sure how far they were. 
As I remember, docs can pretty much be done anytime before doc freeze.
Probably will not happen in 7.1.  Item removed.

> > Store tables as files named by OID (Vadim)
> 
> This has never been documented to the contrary AFAIK.  There's an empty
> "Storage" chapter, which would be a good place to put this --- eventually.

OK, Removed.

> 
> > New /contrib/rserv replication toolkit (Vadim)
> > New /contrib/oid2name to map numeric files to table names
> 
> These both have their appropriate READMEs.

Yes, I kept rserv in there in case we wanted to more prominently mention
it in the HISTORY file and give an overview.  Guess not.  Seems like a
pretty cool thing to keep hidden in /contrib.  The /rserv README doesn't
adequately describe its usefulness.  All removed.


> > New pg_class.relkind value for views (Mark Hollomon)
> 
> Documented.

Removed.

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

Wed, 14 Feb 2001 12:46:46 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Open 7.1 items
> ...join visibility issue...

I'm not sure if the "table shape for natural joins issue" has been
formalized, but afaik it isn't covered in the scoping patch. Tom?

                     - Thomas

Wed, 14 Feb 2001 19:03:13 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: Open 7.1 items
Bruce Momjian writes:

> > Bruce Momjian writes:
> >
> > > New PL/pgSQL GET DIAGNOSTICS statement for SPI value access (Jan)
> >
> > If someone can show me an example of how it operates I can write up
> > something.
>
> I found:
>
>   > Quoting a recent message by Jan Wieck <janwieck@Yahoo.com>:
>   > :Do a
>   > :
>   > :    GET DIAGNOSTICS SELECT PROCESSED INTO <int4_variable>;
>   > :
>   > :directly  after  an  INSERT,  UPDATE  or DELETE statement and you'll know
>   > :how many rows have been hit.
>   > :
>   > :Also you can get the OID of an inserted row with
>   > :
>   > :    GET DIAGNOSTICS SELECT RESULT INTO <int4_variable>;
>   >
>
> Looking at plpgsql/src/gram.y, it only supports PROCESSED (rows
> returned/affected) and RESULT (OID).  The grammar indicates that only
> SELECT is allowed in GET DIAGNOSTICS SELECT.  Jan says it works for
> INSERT/UPDATE/DELETE too, but I guess you still use GET DIAGNOSTICS
> SELECT.

May I suggest that this is the wrong syntax?  It should be

GET DIAGNOSTICS <variable> = ROW_COUNT;

See SQL99 part 2, clause 19.1.

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


Wed, 14 Feb 2001 21:28:07 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: Open 7.1 items
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
>> ...join visibility issue...
> I'm not sure if the "table shape for natural joins issue" has been
> formalized, but afaik it isn't covered in the scoping patch. Tom?

Far as I know, we were OK on that before.

test=# create table a(f1 int, f2 int);
CREATE
test=# create table b(f1 int, f3 int);
CREATE
test=# select * from a natural join b;
 f1 | f2 | f3
----+----+----
(0 rows)

test=# select * from a join b using(f1);
 f1 | f2 | f3
----+----+----
(0 rows)

test=# select * from a join b on (a.f1=b.f1);
 f1 | f2 | f1 | f3
----+----+----+----
(0 rows)

This is per spec, no?

			regards, tom lane

Wed, 14 Feb 2001 16:16:19 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Open 7.1 items 
Bruce Momjian wrote:


> P O S T G R E S Q L
> 
> 7 . 1  O P E N    I T E M S
> 
> Current at ftp://candle.pha.pa.us/pub/postgresql/open_items.

Any possibility of putting my getlocale into contrib ?

I agree that it should in fact be in the pg_locale.c but that would be a
feature and we don't add new features this late.

OTOH it is helpful if users (specially those that use rpm's or other
packaged binaries) have an easy way to find out
what locale they happen to run in (as it often it just happens ;)

--------------
Hannu

Thu, 15 Feb 2001 00:27:08 +0200
Hannu Krosing <hannu@tm.ee>
Re: Open 7.1 items
> Far as I know, we were OK on that before.

We weren't last time I tested (there was a thread on this a while ago),
but...

> This is per spec, no?

... it sure is. Looks great!

                    - Thomas

Wed, 14 Feb 2001 22:49:04 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: Open 7.1 items
> Bruce Momjian wrote:
> 
> 
> > P O S T G R E S Q L
> > 
> > 7 . 1  O P E N    I T E M S
> > 
> > Current at ftp://candle.pha.pa.us/pub/postgresql/open_items.
> 
> Any possibility of putting my getlocale into contrib ?
> 
> I agree that it should in fact be in the pg_locale.c but that would be a
> feature and we don't add new features this late.
> 
> OTOH it is helpful if users (specially those that use rpm's or other
> packaged binaries) have an easy way to find out
> what locale they happen to run in (as it often it just happens ;)

Actually, I have something from Oliver Elphick called pg_controldata:

	$ pg_controldata
	Log file id:                          0
	Log file segment:                     5
	Last modified:                        Wed Feb  7 19:35:47 2001
	Database block size:                  8192
	Blocks per segment of large relation: 131072
	Catalog version number:               200101061
	LC_COLLATE:                           en_GB
	LC_CTYPE:                             en_GB
	Log archive directory:                

This looks quite valuable.  Do people want this in /contrib?  How does
this compare to your utility?

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

Wed, 14 Feb 2001 17:55:00 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Open 7.1 items
Bruce Momjian wrote:
> 
> > Bruce Momjian wrote:
> >
> >
> > > P O S T G R E S Q L
> > >
> > > 7 . 1  O P E N    I T E M S
> > >
> > > Current at ftp://candle.pha.pa.us/pub/postgresql/open_items.
> >
> > Any possibility of putting my getlocale into contrib ?
> >
> > I agree that it should in fact be in the pg_locale.c but that would be a
> > feature and we don't add new features this late.
> >
> > OTOH it is helpful if users (specially those that use rpm's or other
> > packaged binaries) have an easy way to find out
> > what locale they happen to run in (as it often it just happens ;)
> 
> Actually, I have something from Oliver Elphick called pg_controldata:

Could you send it to me so that I can find out how he gets the
LC_COLLATE 
and LC_CTYPE from backend (assuming it tells backend locale not cients)
?
 
>         $ pg_controldata
>         Log file id:                          0
>         Log file segment:                     5
>         Last modified:                        Wed Feb  7 19:35:47 2001
>         Database block size:                  8192
>         Blocks per segment of large relation: 131072
>         Catalog version number:               200101061
>         LC_COLLATE:                           en_GB
>         LC_CTYPE:                             en_GB
>         Log archive directory:
> 
> This looks quite valuable.  Do people want this in /contrib?  How does
> this compare to your utility?

Mine is an external C funtion, so it can easily be used from any client.
And I intend to propose it into pg_locale.c ealy in 7.2 development.

-----------
Hannu

Thu, 15 Feb 2001 00:59:28 +0200
Hannu Krosing <hannu@tm.ee>
Re: Open 7.1 items
> > Actually, I have something from Oliver Elphick called pg_controldata:
> 
> Could you send it to me so that I can find out how he gets the
> LC_COLLATE 
> and LC_CTYPE from backend (assuming it tells backend locale not cients)
> ?
>  
> >         $ pg_controldata
> >         Log file id:                          0
> >         Log file segment:                     5
> >         Last modified:                        Wed Feb  7 19:35:47 2001
> >         Database block size:                  8192
> >         Blocks per segment of large relation: 131072
> >         Catalog version number:               200101061
> >         LC_COLLATE:                           en_GB
> >         LC_CTYPE:                             en_GB
> >         Log archive directory:
> > 
> > This looks quite valuable.  Do people want this in /contrib?  How does
> > this compare to your utility?
> 
> Mine is an external C funtion, so it can easily be used from any client.
> And I intend to propose it into pg_locale.c ealy in 7.2 development.

His is an external C program also.  The C source is attached.

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

/* pg_controldata
 *
 * reads the data from $PGDATA/global/pg_control
 *
 * copyright (c) Oliver Elphick <olly@lfix.co.uk>, 2001;
 * licence: BSD
 *
*/

#include <stdio.h>
#include <stdlib.h>
#include <unistd.h>
#include <time.h>
#include <sys/types.h>
#include <sys/stat.h>
#include <fcntl.h>


typedef unsigned int uint32;

#include "config.h"
#include "access/xlogdefs.h"

/*
 * #include "access/xlog.h"
 * #include "c.h"
 */

/* The following definitions are extracted from access/xlog.h and its
 * recursive includes. There is too much initialisation needed if
 * they are included direct. Perhaps someone more knowledgeable can
 * fix that.
 */
typedef struct crc64
{
	uint32      crc1;
	uint32      crc2;
} crc64;

#define LOCALE_NAME_BUFLEN  128

typedef enum DBState
{
	DB_STARTUP = 0,
	DB_SHUTDOWNED,
	DB_SHUTDOWNING,
	DB_IN_RECOVERY,
	DB_IN_PRODUCTION
} DBState;


typedef struct ControlFileData
{
   crc64    crc;
   uint32      logId;         /* current log file id */
   uint32      logSeg;        /* current log file segment (1-based) */
   struct 
	XLogRecPtr	checkPoint;    /* last check point record ptr */
   time_t      time;       /* time stamp of last modification */
   DBState     state;         /* see enum above */

   /*
    * this data is used to make sure that configuration of this DB is
    * compatible with the backend executable
    */
   uint32      blcksz;        /* block size for this DB */
   uint32      relseg_size;   /* blocks per segment of large relation */
   uint32      catalog_version_no;     /* internal version number */
   /* active locales --- "C" if compiled without USE_LOCALE: */
   char     lc_collate[LOCALE_NAME_BUFLEN];
   char     lc_ctype[LOCALE_NAME_BUFLEN];

   /*
    * important directory locations
    */
   char     archdir[MAXPGPATH];     /* where to move offline log files */
} ControlFileData;

int main() {
	ControlFileData ControlFile;
	int fd;
	char ControlFilePath[MAXPGPATH];
	char *DataDir;
	char tmdt[32];

	DataDir = getenv("PGDATA");
	if ( DataDir == NULL ) {
		fprintf(stderr,"PGDATA is not defined\n");
		exit(1);
	}

	snprintf(ControlFilePath, MAXPGPATH, "%s/global/pg_control", DataDir);

	if ((fd = open(ControlFilePath, O_RDONLY)) == -1) {
		perror("Failed to open $PGDATA/global/pg_control for reading");
		exit(2);
	}

	read(fd, &ControlFile, sizeof(ControlFileData));
	strftime(tmdt, 32, "%c", localtime(&(ControlFile.time)));

	printf("Log file id:                          %u\n"
	       "Log file segment:                     %u\n"
			 "Last modified:                        %s\n"
			 "Database block size:                  %u\n"
			 "Blocks per segment of large relation: %u\n"
			 "Catalog version number:               %u\n"
			 "LC_COLLATE:                           %s\n"
			 "LC_CTYPE:                             %s\n"
			 "Log archive directory:                %s\n",
			 ControlFile.logId,
			 ControlFile.logSeg,
			 tmdt,
			 ControlFile.blcksz,
			 ControlFile.relseg_size,
			 ControlFile.catalog_version_no,
			 ControlFile.lc_collate,
			 ControlFile.lc_ctype,
			 ControlFile.archdir);
	
	return (0);
}
Wed, 14 Feb 2001 18:05:14 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Open 7.1 items
> As you can see from the current open items list, there isn't much left
> to do for the 7.1 release.  I am going to suggest we remove the LAZY
> VACUUM option at this point.  I know Tom Lane posted an item about the

Well, leaving for vacation tomorrow I have to agree -:(
LAZY patch will be available in a few days after 7.1 release.

Vadim



Wed, 14 Feb 2001 19:04:54 -0800
"Vadim Mikheev" <vmikheev@sectorbase.com>
Re: Open 7.1 items
[ Charset ISO-8859-1 unsupported, converting... ]
> > As you can see from the current open items list, there isn't much left
> > to do for the 7.1 release.  I am going to suggest we remove the LAZY
> > VACUUM option at this point.  I know Tom Lane posted an item about the
> 
> Well, leaving for vacation tomorrow I have to agree -:(
> LAZY patch will be available in a few days after 7.1 release.

Seems we should keep it as an option outside the tree for a while. 
Remember, pgindent will be done before final.  Is that OK?

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

Wed, 14 Feb 2001 22:30:08 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Open 7.1 items
Hi there...

   I would like to inquire of possible support for running PostgreSQL on a
Linux Cluster. How would i implement and configure PostgreSQL as a
distributed database i.e. replicated on several servers?

   I am anxious to hear from you guys.

-- 
                              Manny C. Cabido
                              ====================================
                              e-mail:manny@tinago.msuiit.edu.ph
                                     manny@sun.msuiit.edu.ph
                              =====================================


Thu, 15 Feb 2001 12:29:39 +0800 (PHT)
Manuel Cabido <manny@msuiit.edu.ph>
Re: Open 7.1 items

Can someone comment on this?  Seems GET DIAGNOSTICS is wrong from
Peter's reading of SQL92, and mine too.


> Bruce Momjian writes:
> 
> > > Bruce Momjian writes:
> > >
> > > > New PL/pgSQL GET DIAGNOSTICS statement for SPI value access (Jan)
> > >
> > > If someone can show me an example of how it operates I can write up
> > > something.
> >
> > I found:
> >
> >   > Quoting a recent message by Jan Wieck <janwieck@Yahoo.com>:
> >   > :Do a
> >   > :
> >   > :    GET DIAGNOSTICS SELECT PROCESSED INTO <int4_variable>;
> >   > :
> >   > :directly  after  an  INSERT,  UPDATE  or DELETE statement and you'll know
> >   > :how many rows have been hit.
> >   > :
> >   > :Also you can get the OID of an inserted row with
> >   > :
> >   > :    GET DIAGNOSTICS SELECT RESULT INTO <int4_variable>;
> >   >
> >
> > Looking at plpgsql/src/gram.y, it only supports PROCESSED (rows
> > returned/affected) and RESULT (OID).  The grammar indicates that only
> > SELECT is allowed in GET DIAGNOSTICS SELECT.  Jan says it works for
> > INSERT/UPDATE/DELETE too, but I guess you still use GET DIAGNOSTICS
> > SELECT.
> 
> May I suggest that this is the wrong syntax?  It should be
> 
> GET DIAGNOSTICS <variable> = ROW_COUNT;
> 
> See SQL99 part 2, clause 19.1.
> 
> -- 
> Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/
> 
> 


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

Fri, 16 Feb 2001 12:15:35 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Open 7.1 items
At 12:15 16/02/01 -0500, Bruce Momjian wrote:
>
>Can someone comment on this?  Seems GET DIAGNOSTICS is wrong from
>Peter's reading of SQL92, and mine too.
>

He's quite correct.


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

Sat, 17 Feb 2001 10:45:14 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Open 7.1 items
Peter Eisentraut <peter_e@gmx.net> writes:
> Quoting a recent message by Jan Wieck <janwieck@Yahoo.com>:
> :Do a
> :
> :    GET DIAGNOSTICS SELECT PROCESSED INTO <int4_variable>;
> :
> :directly  after  an  INSERT,  UPDATE  or DELETE statement and you'll know
> :how many rows have been hit.
> :
> :Also you can get the OID of an inserted row with
> :
> :    GET DIAGNOSTICS SELECT RESULT INTO <int4_variable>;

> May I suggest that this is the wrong syntax?  It should be
>
> GET DIAGNOSTICS <variable> = ROW_COUNT;
>
> See SQL99 part 2, clause 19.1.

Hmm, that's definitely what SQL99 uses for the syntax.  I wonder where
Jan got the SELECT INTO syntax --- did he borrow it from Oracle?
Anyone have an Oracle manual to check?

I'd be more inclined to follow the spec than Oracle, anyway.  But
if we're going to change it, we'd better do so before 7.1 release,
else we'll have a backwards-compatibility problem.

We'd need to come up with a name for the inserted-OID result,
since that's not one of the spec-listed items.  I'd suggest just
	GET DIAGNOSTICS <variable> = OID;
which seems unlikely to conflict with any future spec extension.
But maybe someone has a better idea.

			regards, tom lane

Sun, 18 Feb 2001 18:49:22 -0500
Tom Lane <tgl@sss.pgh.pa.us>
GET DIAGNOSTICS (was Re: Open 7.1 items)
At 18:49 18/02/01 -0500, Tom Lane wrote:
>Peter Eisentraut <peter_e@gmx.net> writes:
>> :
>> :    GET DIAGNOSTICS SELECT RESULT INTO <int4_variable>;
>
>> May I suggest that this is the wrong syntax?  It should be
>
>Hmm, that's definitely what SQL99 uses for the syntax.  I wonder where
>Jan got the SELECT INTO syntax --- did he borrow it from Oracle?
>Anyone have an Oracle manual to check?

Sadly, we made it up. I needed to get the SPI variables and we came up with
this. I sent the patches for PROCESSED and RESULT in 1999 (I think) - pre
V6.5, anyway. I had no idea that they had been applied until people started
asking questions about them.


>I'd be more inclined to follow the spec than Oracle, anyway.

I agree.

However, there is a case for keeping the 'select' version as well; you have
already raised the need to handle the 'OID' case, and I can imagine there
might be a few other PG-specific things we might want in the future -
keeping the two separate may be a good option:

We *do* need to support ROW_COUNT, but allowing

    GET DIAGNOSTICS Select ROW_COUNT, SQLCODE, OID Into :a,:b:,:c;

is a lot friendlier than the standard:

    GET DIAGNOSTICS :a = ROW_COUNT;
    GET DIAGNOSTICS EXCEPTION 1 :b = SQLCODE;
    GET DIAGNOSTICS :c = OID;

(not that we even support SQLCODE at this stage).


>if we're going to change it, we'd better do so before 7.1 release,
>else we'll have a backwards-compatibility problem.

*If* you accept the desirability of 'select', then this is not true.


P.S. Is Jan around? He's been very quiet recently...



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

Mon, 19 Feb 2001 12:33:26 +1100
Philip Warner <pjw@rhyme.com.au>
Re: GET DIAGNOSTICS (was Re: Open 7.1 items)
Philip Warner <pjw@rhyme.com.au> writes:
>> Hmm, that's definitely what SQL99 uses for the syntax.  I wonder where
>> Jan got the SELECT INTO syntax --- did he borrow it from Oracle?

> Sadly, we made it up.

Ah so.  Well, friendliness aside, I'd go with the spec's syntax.

> We *do* need to support ROW_COUNT, but allowing

>     GET DIAGNOSTICS Select ROW_COUNT, SQLCODE, OID Into :a,:b:,:c;

> is a lot friendlier than the standard:

>     GET DIAGNOSTICS :a = ROW_COUNT;
>     GET DIAGNOSTICS EXCEPTION 1 :b = SQLCODE;
>     GET DIAGNOSTICS :c = OID;

It looks to me like SQL99 allows

	GET DIAGNOSTICS :a = ROW_COUNT, :b = OID, ...;

which is at least as good as the SELECT syntax, if not better since each
target variable and info spec are kept together.

> P.S. Is Jan around? He's been very quiet recently...

He's still engaged in moving from Germany to Norfolk, VA.  I think his
net-access is somewhat erratic :-(

			regards, tom lane

Sun, 18 Feb 2001 20:40:10 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: GET DIAGNOSTICS (was Re: Open 7.1 items) 
Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > Quoting a recent message by Jan Wieck <janwieck@Yahoo.com>:
> > :Do a
> > :
> > :    GET DIAGNOSTICS SELECT PROCESSED INTO <int4_variable>;
> > :
> > :directly  after  an  INSERT,  UPDATE  or DELETE statement and you'll know
> > :how many rows have been hit.
> > :
> > :Also you can get the OID of an inserted row with
> > :
> > :    GET DIAGNOSTICS SELECT RESULT INTO <int4_variable>;
>
> > May I suggest that this is the wrong syntax?  It should be
> >
> > GET DIAGNOSTICS <variable> = ROW_COUNT;
> >
> > See SQL99 part 2, clause 19.1.
>
> Hmm, that's definitely what SQL99 uses for the syntax.  I wonder where
> Jan got the SELECT INTO syntax --- did he borrow it from Oracle?
> Anyone have an Oracle manual to check?

    Got  it  as  a  patch  from - um - forgotten. Didn't new that
    there is something in the SQL99.

> I'd be more inclined to follow the spec than Oracle, anyway.  But
> if we're going to change it, we'd better do so before 7.1 release,
> else we'll have a backwards-compatibility problem.

    Agreed.

> We'd need to come up with a name for the inserted-OID result,
> since that's not one of the spec-listed items.  I'd suggest just
>    GET DIAGNOSTICS <variable> = OID;
> which seems unlikely to conflict with any future spec extension.
> But maybe someone has a better idea.

    The problem here is that the PL/pgSQL parser doesn't have the
    mechanisms  for  enabling  keywords  as identifiers, the main
    parser has. So using an existing type name might  cause  some
    trouble. What about INSERTED_OID?


Jan

--

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


Mon, 19 Feb 2001 14:54:30 -0500 (EST)
Jan Wieck <janwieck@Yahoo.com>
Re: GET DIAGNOSTICS (was Re: Open 7.1 items)
Philip Warner wrote:
>
> P.S. Is Jan around? He's been very quiet recently...

He is,

    just a little quiet. I can live with it either way.  The main
    problem, as said, is that we need to allow some  keywords  as
    identifiers  in PL/pgSQL like in the main parser. Actually we
    added RESULT as a reserved word,  what's  a  likely  variable
    name  inside of functions. So I think we have something to do
    anyway.


Jan

--

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


Mon, 19 Feb 2001 15:06:21 -0500 (EST)
Jan Wieck <janwieck@Yahoo.com>
Re: GET DIAGNOSTICS (was Re: Open 7.1 items)
Tom Lane wrote:
> Philip Warner <pjw@rhyme.com.au> writes:
> >> Hmm, that's definitely what SQL99 uses for the syntax.  I wonder where
> >> Jan got the SELECT INTO syntax --- did he borrow it from Oracle?
>
> > Sadly, we made it up.
>
> Ah so.  Well, friendliness aside, I'd go with the spec's syntax.
>
> > We *do* need to support ROW_COUNT, but allowing
>
> >     GET DIAGNOSTICS Select ROW_COUNT, SQLCODE, OID Into :a,:b:,:c;
>
> > is a lot friendlier than the standard:
>
> >     GET DIAGNOSTICS :a = ROW_COUNT;
> >     GET DIAGNOSTICS EXCEPTION 1 :b = SQLCODE;
> >     GET DIAGNOSTICS :c = OID;
>
> It looks to me like SQL99 allows
>
>    GET DIAGNOSTICS :a = ROW_COUNT, :b = OID, ...;

    I  like  this one - except for the OID which should IMHO read
    INSERTED_OID.

> > P.S. Is Jan around? He's been very quiet recently...
>
> He's still engaged in moving from Germany to Norfolk, VA.  I think his
> net-access is somewhat erratic :-(

    Actually it's more the "custody for my children"  thing  than
    the   movement   itself.  For  the  net-access:  my  mother's
    appartment has a phone line, so I can use my 33K6  modem  for
    dialup - that's all.


Jan

--

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


Mon, 19 Feb 2001 15:11:46 -0500 (EST)
Jan Wieck <janwieck@Yahoo.com>
Re: GET DIAGNOSTICS (was Re: Open 7.1 items)
Jan Wieck <janwieck@yahoo.com> writes:
>     I  like  this one - except for the OID which should IMHO read
>     INSERTED_OID.

I just committed changes that make it RESULT_OID, but if you like
INSERTED_OID better, we could change it...

			regards, tom lane

Mon, 19 Feb 2001 15:21:48 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: GET DIAGNOSTICS (was Re: Open 7.1 items) 
Tom Lane wrote:
> Jan Wieck <janwieck@yahoo.com> writes:
> >     I  like  this one - except for the OID which should IMHO read
> >     INSERTED_OID.
>
> I just committed changes that make it RESULT_OID, but if you like
> INSERTED_OID better, we could change it...

Oh boy,

    at  your  current  speed,  my  limited  internet access seems
    really to become kinda problem.  Before I participate in  the
    discussion you already committed. That aint fair :-)

    RESULT_OID  is  OK,  or  make it ONE_OF_THE_NEW_CREATED_OIDS,
    because in the case of an INSERT  ...  SELECT  it  isn't  the
    entire story either way.


Jan

--

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


Mon, 19 Feb 2001 15:41:59 -0500 (EST)
Jan Wieck <janwieck@Yahoo.com>
Re: GET DIAGNOSTICS (was Re: Open 7.1 items)
> Jan Wieck <janwieck@yahoo.com> writes:
> >     I  like  this one - except for the OID which should IMHO read
> >     INSERTED_OID.
> 
> I just committed changes that make it RESULT_OID, but if you like
> INSERTED_OID better, we could change it...

I think I like RESULT_OID because the standard uses RESULT.

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

Mon, 19 Feb 2001 15:47:47 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: GET DIAGNOSTICS (was Re: Open 7.1 items)
Peter Eisentraut <peter_e@gmx.net> writes:
> Bruce Momjian writes:
> I just committed changes that make it RESULT_OID, but if you like
> INSERTED_OID better, we could change it...
>> 
>> I think I like RESULT_OID because the standard uses RESULT.

> RESULT* is used for SELECT statements, but RESULT_OID is for INSERT
> commands.  It sounds a bit like that results get oids assigned.  Maybe.

I don't have a strong feeling either way, but it occurred to me that
RESULT_OID might be better since it could be used for more purposes,
eg, returning a last-affected-row OID for UPDATE or DELETE.  Not that
we do that now, but we wouldn't have to chew up additional keywords
if we wanted to start doing it.

			regards, tom lane

Tue, 20 Feb 2001 10:34:12 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: GET DIAGNOSTICS (was Re: Open 7.1 items) 
Bruce Momjian writes:

> > I just committed changes that make it RESULT_OID, but if you like
> > INSERTED_OID better, we could change it...
>
> I think I like RESULT_OID because the standard uses RESULT.

RESULT* is used for SELECT statements, but RESULT_OID is for INSERT
commands.  It sounds a bit like that results get oids assigned.  Maybe.

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


Tue, 20 Feb 2001 16:38:20 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: GET DIAGNOSTICS (was Re: Open 7.1 items)