Обсуждение: request for enhancement of protocol
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/
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/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
>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');
returnnext '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/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
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