TODO items

Bruce,

o Allow array declarations and other data types in PL/PgSQL DECLARE
o Allow PL/PgSQL to support array element assignment

AFAIK, these two are not done, but they are redundant. Either one requires
the implementation of the other.

o Add PL/PgSQL PROCEDURES that can return multiple values

Hmmm ... I know how this got on the TODO, but it's a fragment of a larger
suggestion about PROCEDURES vs. FUNCTIONS. I don't think it makes much
sense on its own without the other elements; maybe we should take it off
until I can make a full proposal?

o Add table function support to pltcl, plperl, plpython

If this was done, I would dearly love to know about it ...

--
Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus wrote:

o Allow array declarations and other data types in PL/PgSQL DECLARE
o Allow PL/PgSQL to support array element assignment

AFAIK, these two are not done, but they are redundant. Either one requires
the implementation of the other.

They are done (at least the array declarations and array element
assignment part):

create or replace function test() returns integer[] as '
declare
v_ret integer[] := ''{}'';
begin
v_ret[1] := 1;
v_ret[2] := 2;
return v_ret;
end;
' language plpgsql;
CREATE FUNCTION
regression=# select test();
test
-------
{1,2}
(1 row)

o Add PL/PgSQL PROCEDURES that can return multiple values

Hmmm ... I know how this got on the TODO, but it's a fragment of a larger
suggestion about PROCEDURES vs. FUNCTIONS. I don't think it makes much
sense on its own without the other elements; maybe we should take it off
until I can make a full proposal?

Is this somehow different from table functions (SRFs)?

o Add table function support to pltcl, plperl, plpython

If this was done, I would dearly love to know about it ...

Pretty much sure this has not been done. I'll be happy to work with
someone if they want to pick this up, but I don't use them enough to
feel comfortable doing it myself.

Joe

Bruce Momjian wrote:

I am marking the completed TODO items. Are these done?

Can we mark this one complete?
* Allow easy display of usernames in a group
regression=# SELECT g.grosysid, g.groname, s.usesysid, s.usename FROM
pg_shadow s, pg_group g WHERE s.usesysid = any (g.grolist);
grosysid | groname | usesysid | usename
----------+---------+----------+---------
100 | g1 | 100 | user1
101 | g2 | 100 | user1
100 | g1 | 101 | user2
101 | g2 | 101 | user2
101 | g2 | 102 | user3
(5 rows)

This one isn't done:
* -Delay resolution of array expression type so assignment coercion
can be performed on empty array expressions (Joe)

This one I don't understand:
o Support construction of array result values in expressions

I thought Peter did something with this one:
* Allow LIKE indexing optimization for non-ASCII locales

Joe

Joe,

They are done (at least the array declarations and array element
assignment part):

Way cool! How'd I miss that one?

Time to test ....

o Add PL/PgSQL PROCEDURES that can return multiple values

Hmmm ... I know how this got on the TODO, but it's a fragment of a larger
suggestion about PROCEDURES vs. FUNCTIONS. I don't think it makes much
sense on its own without the other elements; maybe we should take it off
until I can make a full proposal?

Is this somehow different from table functions (SRFs)?

Yes. Reference T-SQL's OUTPUT parameters.

Mind you, with the implementation of SRFs, it's not as necessary as it once
was.

Pretty much sure this has not been done. I'll be happy to work with
someone if they want to pick this up, but I don't use them enough to
feel comfortable doing it myself.

I'd be happy to test PL/Perl. I won't be any help with the others ...

--
Josh Berkus
Aglio Database Solutions
San Francisco

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I am marking the completed TODO items. Are these done?

* Have standalone backend read postgresql.conf

[looks] Nope. No ProcessConfigFile() call in postgres.c.

* Prevent whole-row references from leaking memory, e.g. SELECT
COUNT(tab.*)

Nope.

* Use index to restrict rows returned by multi-key index when used with
non-consecutive keys or OR clauses, so fewer heap accesses

Not sure what this means.

* Prevent index uniqueness checks when UPDATE does not modify the column

Not done.

* Return proper effected tuple count from complex commands [return]

I looked at that TODO.detail file, and it all seems to be ancient
history. Didn't we resolve those issues to peoples' satisfaction in 7.3?

o Allow SHOW of non-modifiable variables, like pg_controldata

I put in read-only variables for the things that seemed most interesting
(LC_COLLATE for example), but the coverage isn't complete.

o Allow array declarations and other data types in PL/PgSQL DECLARE

AFAIK this is pretty much fixed.

o Add PL/PgSQL PROCEDURES that can return multiple values

Not done (unless this is referring to RETURN NEXT, but I think it's
talking about multiple output parameters).

o Add table function support to pltcl, plperl, plpython

Not done.

o Allow PL/PgSQL to support array element assignment

Done.

* Make blind writes go through the file descriptor cache

Not done.

* Improve Subplan list handling

Dunno what this is referring to.

* Precompile SQL functions to avoid overhead (Neil)

Not done.

* Add optional CRC checksum to heap and index pages

Not done.

o Add optional textual message to NOTIFY

Not done, but there is room in the FE/BE protocol now for something like
this.

regards, tom lane

Joe Conway <mail@joeconway.com> writes:

This one I don't understand:
o Support construction of array result values in expressions

Not sure why you don't understand it, when you did it ;-). It's asking
for the ARRAY[] syntax. Bruce, that one should be marked done.

I thought Peter did something with this one:
* Allow LIKE indexing optimization for non-ASCII locales

Yeah, he did.

regards, tom lane

Josh Berkus wrote:

Bruce,

o Allow array declarations and other data types in PL/PgSQL DECLARE
o Allow PL/PgSQL to support array element assignment

AFAIK, these two are not done, but they are redundant. Either one requires
the implementation of the other.

OK.

o Add PL/PgSQL PROCEDURES that can return multiple values

Hmmm ... I know how this got on the TODO, but it's a fragment of a larger
suggestion about PROCEDURES vs. FUNCTIONS. I don't think it makes much
sense on its own without the other elements; maybe we should take it off
until I can make a full proposal?

Removed.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Joe Conway wrote:

Josh Berkus wrote:

o Allow array declarations and other data types in PL/PgSQL DECLARE
o Allow PL/PgSQL to support array element assignment

AFAIK, these two are not done, but they are redundant. Either one requires
the implementation of the other.

They are done (at least the array declarations and array element
assignment part):

create or replace function test() returns integer[] as '
declare
v_ret integer[] := ''{}'';
begin
v_ret[1] := 1;
v_ret[2] := 2;
return v_ret;
end;
' language plpgsql;
CREATE FUNCTION
regression=# select test();
test
-------
{1,2}
(1 row)

OK, marked as done.

o Add PL/PgSQL PROCEDURES that can return multiple values

Hmmm ... I know how this got on the TODO, but it's a fragment of a larger
suggestion about PROCEDURES vs. FUNCTIONS. I don't think it makes much
sense on its own without the other elements; maybe we should take it off
until I can make a full proposal?

Is this somehow different from table functions (SRFs)?

Yes, I am unsure what it is. Removed pending later proposal.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Joe Conway wrote:

Bruce Momjian wrote:

I am marking the completed TODO items. Are these done?

Can we mark this one complete?
* Allow easy display of usernames in a group
regression=# SELECT g.grosysid, g.groname, s.usesysid, s.usename FROM
pg_shadow s, pg_group g WHERE s.usesysid = any (g.grolist);
grosysid | groname | usesysid | usename
----------+---------+----------+---------
100 | g1 | 100 | user1
101 | g2 | 100 | user1
100 | g1 | 101 | user2
101 | g2 | 101 | user2
101 | g2 | 102 | user3
(5 rows)

OK, let me mark it as done, and add to the psql section:

* Allow psql \du to show groups, and add \dg for groups

This one isn't done:
* -Delay resolution of array expression type so assignment coercion
can be performed on empty array expressions (Joe)

OK.

This one I don't understand:
o Support construction of array result values in expressions

I don't either. Anyone?

I thought Peter did something with this one:
* Allow LIKE indexing optimization for non-ASCII locales

He added a special LIKE operator type, rather than allowing our existing
indexes to use LIKE, so I would say it is partially done.

I updated it to:

* -Allow LIKE indexing optimization for non-ASCII locales using
special index

and marked it as done.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Josh Berkus wrote:

Joe,

They are done (at least the array declarations and array element
assignment part):

Way cool! How'd I miss that one?

Time to test ....

o Add PL/PgSQL PROCEDURES that can return multiple values

Hmmm ... I know how this got on the TODO, but it's a fragment of a larger
suggestion about PROCEDURES vs. FUNCTIONS. I don't think it makes much
sense on its own without the other elements; maybe we should take it off
until I can make a full proposal?

Is this somehow different from table functions (SRFs)?

Yes. Reference T-SQL's OUTPUT parameters.

Mind you, with the implementation of SRFs, it's not as necessary as it once
was.

Do you have TODO to add for this? I removed the original one because,
as worded, it was complete.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:

o Add PL/PgSQL PROCEDURES that can return multiple values

Do you have TODO to add for this? I removed the original one because,
as worded, it was complete.

Actually, now that I look at it again, it is referring to procedures,
not functions. Maybe just make it:

o Add capability to create and call PROCEDURES

Joe

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I am marking the completed TODO items. Are these done?

* Have standalone backend read postgresql.conf

[looks] Nope. No ProcessConfigFile() call in postgres.c.

OK.

* Prevent whole-row references from leaking memory, e.g. SELECT
COUNT(tab.*)

Nope.

OK, I wasn't sure because you had done per-tuple memory contexts.

* Use index to restrict rows returned by multi-key index when used with
non-consecutive keys or OR clauses, so fewer heap accesses

Not sure what this means.

This is a Vadim idea. The idea was that if you had a multi-key index on
col1,col2,col3, and you wanted to do a lookup on col1,col3, you could
still use the index, and just run through all the matching col1 values
looking for a matching col3 in the index, rather than going to the heap
and looking for a col3 match? Is this item worth keeping?

* Prevent index uniqueness checks when UPDATE does not modify the column

Not done.

OK.

* Return proper effected tuple count from complex commands [return]

I looked at that TODO.detail file, and it all seems to be ancient
history. Didn't we resolve those issues to peoples' satisfaction in 7.3?

I think so. I think MOVE was our last one.

o Allow SHOW of non-modifiable variables, like pg_controldata

I put in read-only variables for the things that seemed most interesting
(LC_COLLATE for example), but the coverage isn't complete.

I modified the item to say "some" and marked it as done:

o -Allow SHOW of some non-modifiable variables, like
pg_controldata

Anyone want it kept?

o Allow array declarations and other data types in PL/PgSQL DECLARE

AFAIK this is pretty much fixed.

OK. Already reported by Joe.

o Add PL/PgSQL PROCEDURES that can return multiple values

Not done (unless this is referring to RETURN NEXT, but I think it's
talking about multiple output parameters).

I am asking Josh for a new item with clearer wording. Once we finish
some items, the wording often needs adjusting.

o Add table function support to pltcl, plperl, plpython

Not done.

OK.

o Allow PL/PgSQL to support array element assignment

Done.

OK.

* Make blind writes go through the file descriptor cache

Not done.

OK.

* Improve Subplan list handling

Dunno what this is referring to.

I assume it is related to the subquery stuff you did. I have marked it
as done.

* Precompile SQL functions to avoid overhead (Neil)

Not done.

OK.

* Add optional CRC checksum to heap and index pages

Not done.

OK.

o Add optional textual message to NOTIFY

Not done, but there is room in the FE/BE protocol now for something like
this.

OK.

Thanks. TODO updated.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tom Lane wrote:

Joe Conway <mail@joeconway.com> writes:

This one I don't understand:
o Support construction of array result values in expressions

Not sure why you don't understand it, when you did it ;-). It's asking
for the ARRAY[] syntax. Bruce, that one should be marked done.

Updated as done.

I thought Peter did something with this one:
* Allow LIKE indexing optimization for non-ASCII locales

Yeah, he did.

Yes, wording updated and marked as done.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

OK, TODO updated.

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

Joe Conway wrote:

Bruce Momjian wrote:

o Add PL/PgSQL PROCEDURES that can return multiple values

Do you have TODO to add for this? I removed the original one because,
as worded, it was complete.

Actually, now that I look at it again, it is referring to procedures,
not functions. Maybe just make it:

o Add capability to create and call PROCEDURES

Joe

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce,

Actually, now that I look at it again, it is referring to procedures,
not functions. Maybe just make it:

o Add capability to create and call PROCEDURES

OK. I need to put a full proposal behind this once 7.4 is in the can.
However, this is largely academic until we get someone who really wants to
take ownership of PL/pgSQL and has the coding skills (I have the former but
not the latter).

--
-Josh Berkus
Aglio Database Solutions
San Francisco

Bruce Momjian <pgman@candle.pha.pa.us> writes:

* Use index to restrict rows returned by multi-key index when used with
non-consecutive keys or OR clauses, so fewer heap accesses

Not sure what this means.

This is a Vadim idea. The idea was that if you had a multi-key index on
col1,col2,col3, and you wanted to do a lookup on col1,col3, you could
still use the index, and just run through all the matching col1 values
looking for a matching col3 in the index, rather than going to the heap
and looking for a col3 match? Is this item worth keeping?

Hmm. Maybe. Might as well leave it there awhile longer.

regards, tom lane

Josh Berkus wrote:

Actually, now that I look at it again, it is referring to procedures,
not functions. Maybe just make it:

o Add capability to create and call PROCEDURES

OK. I need to put a full proposal behind this once 7.4 is in the can.
However, this is largely academic until we get someone who really wants to
take ownership of PL/pgSQL and has the coding skills (I have the former but
not the latter).

This isn't isolated to just PL/pgSQL, just like the ability to create
and call functions isn't. Support for PROCEDUREs in the backend is a
prerequisite to being able to use PL/pgSQL to create procedures. It is
necessary but not sufficient.

Similarly, if we want to support IN/OUT or named parameters, it isn't a
PL/pgSQL issue per se, it is a general one.

Joe

Joe,

This isn't isolated to just PL/pgSQL, just like the ability to create
and call functions isn't. Support for PROCEDUREs in the backend is a
prerequisite to being able to use PL/pgSQL to create procedures. It is
necessary but not sufficient.

Similarly, if we want to support IN/OUT or named parameters, it isn't a
PL/pgSQL issue per se, it is a general one.

Sure. But the ability to call in/out parameters (which would also be tied to
calling the parameters by name, etc) is pretty useless without supporting
them in one of the PLs. And PL/pgSQL is the natural place to start, since it
gives a migration path to DBAs with Oracle or MSSQL applications which make
heavy use of procedures.

FWIW, my vision of "how procedures are different from functions" goes:
1) no overloading, permitting the calling of an SP with some but not all of
its params;
2) no implicit transaction, allowing (maybe requiring?) begin/commit/rollback,
and even vacuum, in an SP.
3) named parameters, callable by name from the client;
4) exception handling of some sort (either T-SQL's immediate-response model or
the more robust "on exception" model from PL/SQL).
5) Cannot be called as a part of a larger query (required by (2) above)

--
-Josh Berkus
Aglio Database Solutions
San Francisco

Tom Lane kirjutas R, 08.08.2003 kell 16:56:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

o Add optional textual message to NOTIFY

Not done, but there is room in the FE/BE protocol now for something like
this.

Were there any other changes to NOTIFY - there was talk about making
NOTIFY use some other structure instead of ordinary PG tables in
backend.

------
Hannu

No, I don't think any of that was done, particularly because there was
no discussion of the implemention.

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

Hannu Krosing wrote:

Tom Lane kirjutas R, 08.08.2003 kell 16:56:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

o Add optional textual message to NOTIFY

Not done, but there is room in the FE/BE protocol now for something like
this.

Were there any other changes to NOTIFY - there was talk about making
NOTIFY use some other structure instead of ordinary PG tables in
backend.

------
Hannu

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073