request for enhancement of protocol

On Sat, Nov 19, 2005 at 11:07:58AM +0100, Pavel Stehule wrote:

Hello

Meybe is time for some changes. Maybe. I haven't courage for it. But maybe
is good time for discussion. What I miss in protocol?

1. debug. support + other level for elog. Current elog is too heavy
(sometimes)

What do you mean? There are already 10 levels for elog, including five
levels of DEBUG. How many more do you want?

2. multi result sets. This is necessery for support procedures in DB2,
MySQL, "ANSI", MsSQL style.

The protocol already supports this and libpq does also. However, I
think that unless you are using async mode you may have difficulty
retrieving it. There's also a comment there about whether the backend
can actually do it, so maybe some work need to be done there.

3. session (package) variables and calling procedures with OUT, INOUT in
normal style, tj. stmt CALL. - heavy task, because I can write function
a(IN int, IN int), and a(OUT int, OUT int) now. This is problem, and need
restriction.

I can understand the CALL but what's the confusing between the two
functions a? One is a(1,2), the other is a().

4. ping

You mean, a ping command without requiring a login?

What is my motivation for 2?
1. I can write "solution" - stored application. Example: info about
growing of database. Output is n tables: first table is info about
database, others about top n - 1 tables, ...

So you mean a function that can return anything (and hence cannot be
used in normal queries). And thus define a special interface for it
(CALL). Still, SELECT function() would work just as well, no?

2. easy reporting. I haven't possibility write stored procedure for
generating cross table now. I have to do all in two steps (example):
generate view, select from view.

Why do you need a view, why can't you use a subquery?

This is difference between procedures and functions. Function have to
have exactly defined interface. Procedures can't.

So essentially, "procedures" here are functions that return "unknown"
rather than functions that return nothing?

3. easy porting from databases which support this style.

Ok, valid point.

Interesting points all, but they seem to be more backend related than
protocol related.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

i think if the protocol is enhanced again we should also consider adding
protocol level support for RESET CONNECTION.
i have sent this patch some time ago but i think it is not worth to do
the remaining protocol level changes (sql level support is finished) if
this is the only change on the protocol level.

best regards,

hans

Pavel Stehule wrote:

Hello

Meybe is time for some changes. Maybe. I haven't courage for it. But
maybe is good time for discussion. What I miss in protocol?

1. debug. support + other level for elog. Current elog is too heavy
(sometimes)
2. multi result sets. This is necessery for support procedures in DB2,
MySQL, "ANSI", MsSQL style.
3. session (package) variables and calling procedures with OUT, INOUT in
normal style, tj. stmt CALL. - heavy task, because I can write function
a(IN int, IN int), and a(OUT int, OUT int) now. This is problem, and
need restriction.
4. ping

What is my motivation for 2?
1. I can write "solution" - stored application. Example: info about
growing of database. Output is n tables: first table is info about
database, others about top n - 1 tables, ...
2. easy reporting. I haven't possibility write stored procedure for
generating cross table now. I have to do all in two steps (example):
generate view, select from view. This is difference between procedures
and functions. Function have to have exactly defined interface.
Procedures can't.
3. easy porting from databases which support this style.

sorry for my wrong english.

best regards
Pavel Stehule

_________________________________________________________________
Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com.
http://www.msn.cz/

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

--
Cybertec Geschwinde & Sch�nig GmbH
Sch�ngrabern 134; A-2020 Hollabrunn
Tel: +43/1/205 10 35 / 340
www.postgresql.at, www.cybertec.at

What do you mean? There are already 10 levels for elog, including five
levels of DEBUG. How many more do you want?

sometimes I need show only some text. Now I get stack info.
lighter elog ~ sending text, not. proc, stack info.

2. multi result sets. This is necessery for support procedures in DB2,
MySQL, "ANSI", MsSQL style.

The protocol already supports this and libpq does also. However, I
think that unless you are using async mode you may have difficulty
retrieving it. There's also a comment there about whether the backend
can actually do it, so maybe some work need to be done there.

libpq is "black box" for me :-(. I need support in psql and plpgsql. And not
in
async mode, or I need wraper over async mode:

multih = execute_multi('call somestoredproc');
while not (rec = fetch_rs(multih))
{
...
}

3. session (package) variables and calling procedures with OUT, INOUT in
normal style, tj. stmt CALL. - heavy task, because I can write function
a(IN int, IN int), and a(OUT int, OUT int) now. This is problem, and

need

restriction.

I can understand the CALL but what's the confusing between the two
functions a? One is a(1,2), the other is a().

when I can use variables (in plpgsql now, in sql in future - package
variables) I have to remember form of function. I can't to call a(@x1, @x2).
Why. Caller don't know if I mean variant one or variant two. And I have to
use nonstandard convension select into a(). Nonstandard in separation in and
out variables. I prefere some restriction here.

4. ping

You mean, a ping command without requiring a login?

yes

What is my motivation for 2?
1. I can write "solution" - stored application. Example: info about
growing of database. Output is n tables: first table is info about
database, others about top n - 1 tables, ..

So you mean a function that can return anything (and hence cannot be
used in normal queries). And thus define a special interface for it
(CALL). Still, SELECT function() would work just as well, no?

SELECT works well if I expect scalar value. But if I expect table I have to
use diff. form
SELECT * FROM ... I see two modes of calling a) select - typed result, b)
call - untyped result. For point a I have different requirements than for
point b. And I see difference between statement call (clauses where, from,
.....) and statement call. PostgreSQL don't support procedures now, only
functions.

2. easy reporting. I haven't possibility write stored procedure for
generating cross table now. I have to do all in two steps (example):
generate view, select from view.

Why do you need a view, why can't you use a subquery?

if you have to solve creating cross table for normal interactive using in
console, you have two possibilities: 1. call stored procedure which generate
temp wiew and user will do select from view, or procedure can create cursor
and user will do select from cursor. But you can't do in one procedure now.

This is difference between procedures and functions. Function have to
have exactly defined interface. Procedures can't.

So essentially, "procedures" here are functions that return "unknown"
rather than functions that return nothing?

yes. This is reason why procedures can't to use in select statement

3. easy porting from databases which support this style.

Ok, valid point.

Interesting points all, but they seem to be more backend related than
protocol related.

I spent some time for looking way for implementing this into plpgsql. I
didn't find it. It's part of SPI too.

Pavel

_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today - it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/

On Sat, Nov 19, 2005 at 12:40:23PM +0100, Pavel Stehule wrote:

What do you mean? There are already 10 levels for elog, including five
levels of DEBUG. How many more do you want?

sometimes I need show only some text. Now I get stack info.
lighter elog ~ sending text, not. proc, stack info.

You could turn the log_error_verbosity down so it doesn't show stack
info.

The protocol already supports this and libpq does also. However, I
think that unless you are using async mode you may have difficulty
retrieving it. There's also a comment there about whether the backend
can actually do it, so maybe some work need to be done there.

Actually, I played with some functions in 8.1 and this is possible:

test=# create or replace function test(int4) returns setof unknown as
$$declare
a record;
begin
select into a * from pg_attribute limit 1;
return next a;
return next (1,2,4);
return next ('hello','world');
return next 'plain string';
return; end$$
language plpgsql;
CREATE FUNCTION
test=# select * from test(1);
test
---------------------------------------------------
(1247,typname,19,-1,64,1,0,-1,-1,f,p,i,t,f,f,t,0)
(1,2,4)
(hello,world)
plain string
(4 rows)

Ok, not maybe the neatest way of doing it, but it works right now.

3. session (package) variables and calling procedures with OUT, INOUT in
normal style, tj. stmt CALL. - heavy task, because I can write function
a(IN int, IN int), and a(OUT int, OUT int) now. This is problem, and

need

restriction.

I can understand the CALL but what's the confusing between the two
functions a? One is a(1,2), the other is a().

when I can use variables (in plpgsql now, in sql in future - package
variables) I have to remember form of function. I can't to call a(@x1,
@x2). Why. Caller don't know if I mean variant one or variant two. And I
have to use nonstandard convension select into a(). Nonstandard in
separation in and out variables. I prefere some restriction here.

Hmm, I searched the standard for package variable but couldn't find it.
Does the syntax have to do that? What if you have an INOUT parameter
and you want the output to go to a different place than the input.
Wouldn't:

SELECT INTO @x1, @x2 from a();
SELECT * from a(@x1,@x2);

be less ambiguous?

SELECT works well if I expect scalar value. But if I expect table I have to
use diff. form
SELECT * FROM ... I see two modes of calling a) select - typed result, b)
call - untyped result. For point a I have different requirements than for
point b. And I see difference between statement call (clauses where, from,
.....) and statement call. PostgreSQL don't support procedures now, only
functions.

As pointed out above, PostgreSQL does support untyped results, just as
long as you don't try to pass it to any other functions.

if you have to solve creating cross table for normal interactive using in
console, you have two possibilities: 1. call stored procedure which
generate temp wiew and user will do select from view, or procedure can
create cursor and user will do select from cursor. But you can't do in one
procedure now.

You mean a crosstab query like in contrib/tablefunc? That doesn't
require creating a view. But then, that may not be what you want.

I spent some time for looking way for implementing this into plpgsql. I
didn't find it. It's part of SPI too.

I imagine in SPI you could unpack the sets returned by functions
returing "unknown", but I havn't tried that.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/