Re: PROCEDURES was: TODO items

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

* Have standalone backend read postgresql.conf
* Prevent whole-row references from leaking memory, e.g. SELECT
COUNT(tab.*)
* Use index to restrict rows returned by multi-key index when used with
non-consecutive keys or OR clauses, so fewer heap accesses
* Prevent index uniqueness checks when UPDATE does not modify the column
* Return proper effected tuple count from complex commands [return]
o Allow SHOW of non-modifiable variables, like pg_controldata
o Allow array declarations and other data types in PL/PgSQL DECLARE
o Add PL/PgSQL PROCEDURES that can return multiple values
o Add table function support to pltcl, plperl, plpython
o Allow PL/PgSQL to support array element assignment
* Allow psql to do table completion for SELECT * FROM schema_part and
table completion for SELECT * FROM schema_name.
o Support jdbc both 'make' and 'ant'
* Make blind writes go through the file descriptor cache
* Improve Subplan list handling
* Precompile SQL functions to avoid overhead (Neil)
* Add optional CRC checksum to heap and index pages
o Add optional textual message to NOTIFY

The current TODO is attached with completed items marked.

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

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(at)candle(dot)pha(dot)pa(dot)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(at)joeconway(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)joeconway(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073