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/

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

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

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/

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

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

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

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

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

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

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

[ 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

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

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

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

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

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

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

At 20:40 18/02/01 -0500, 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.

Probably a reasonably defensible position, too.

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, ...;

Yes, but condition information (eg. SPI RESULT or SQLCODE), needs a
separate statement to row information (eg. ROW_COUNT). ie.

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

but it's not much of a problem, really. And I agree the 'x = y' syntax is
better.

Unfortunately, I don't have an awful lot of free time at the moment, so I
won't be able to look at this for at *least* as week.

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