Re: [HACKERS] Function call hierarchy/path since getting the buffer until access its data
Thank you for the reply Robert. I think I am getting the idea about reading buffers but I am confused about the writing part, can you give me a function name where it does some write operations like creating a table or inserting a tuple for me read as a example. Best Regards , -Waldecir Date: Tue, 29 May 2012 11:33:59 -0400 Subject: Re: [HACKERS] Function call hierarchy/path since getting the buffer until access its data From: robertmh...@gmail.com To: fighter2...@hotmail.com CC: pgsql-hackers@postgresql.org On Mon, May 28, 2012 at 8:15 AM, Waldecir Faria fighter2...@hotmail.com wrote: Good morning, I am doing a study about buffer management to improve the performance of one program that does heavy I/O operations. After looking and reading from different softwares' source codes/texts one friend suggested me to take a look at the PostgreSQL code. I already took a look at the PostgreSQL buffer management modules ( freelist.c and cia ) but now I am a bit confused how the buffer read/write works, I tried to see how PostgreSQL does to get, for example, a char array from one buffer. Looking at rawpage.c I think that I found a good example using the following function calls sequence starting at function get_raw_page_internal(): StrategyGetBuffer-BufferAlloc-ReadBuffer_Common -ReadBufferExtended-BufferGetPage- memcpy page to buf BufferGetPage() doesn't copy anything; it just takes the buffer number and returns a pointer to the address of that buffer in memory. More generally, that whole chain of function calls has to do with how a page ends up inside PostgreSQL's buffer cache, not with how anything on the page is actually decoded. Each buffer contains zero or more tuples; each tuple contains multiple attributes. So after you get the buffer you have to iterate over the tuples and then decode each tuple to get the values that you want. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Function call hierarchy/path since getting the buffer until access its data
On Wed, May 30, 2012 at 9:37 AM, Waldecir Faria fighter2...@hotmail.com wrote: Thank you for the reply Robert. I think I am getting the idea about reading buffers but I am confused about the writing part, can you give me a function name where it does some write operations like creating a table or inserting a tuple for me read as a example. heap_insert might be a good place to start. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Function call hierarchy/path since getting the buffer until access its data
On Mon, May 28, 2012 at 8:15 AM, Waldecir Faria fighter2...@hotmail.com wrote: Good morning, I am doing a study about buffer management to improve the performance of one program that does heavy I/O operations. After looking and reading from different softwares' source codes/texts one friend suggested me to take a look at the PostgreSQL code. I already took a look at the PostgreSQL buffer management modules ( freelist.c and cia ) but now I am a bit confused how the buffer read/write works, I tried to see how PostgreSQL does to get, for example, a char array from one buffer. Looking at rawpage.c I think that I found a good example using the following function calls sequence starting at function get_raw_page_internal(): StrategyGetBuffer-BufferAlloc-ReadBuffer_Common -ReadBufferExtended-BufferGetPage- memcpy page to buf BufferGetPage() doesn't copy anything; it just takes the buffer number and returns a pointer to the address of that buffer in memory. More generally, that whole chain of function calls has to do with how a page ends up inside PostgreSQL's buffer cache, not with how anything on the page is actually decoded. Each buffer contains zero or more tuples; each tuple contains multiple attributes. So after you get the buffer you have to iterate over the tuples and then decode each tuple to get the values that you want. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Function call hierarchy/path since getting the buffer until access its data
Good morning, I am doing a study about buffer management to improve the performance of one program that does heavy I/O operations. After looking and reading from different softwares' source codes/texts one friend suggested me to take a look at the PostgreSQL code. I already took a look at the PostgreSQL buffer management modules ( freelist.c and cia ) but now I am a bit confused how the buffer read/write works, I tried to see how PostgreSQL does to get, for example, a char array from one buffer. Looking at rawpage.c I think that I found a good example using the following function calls sequence starting at function get_raw_page_internal(): StrategyGetBuffer-BufferAlloc-ReadBuffer_Common -ReadBufferExtended-BufferGetPage- memcpy page to buf But I need more examples or explanations to understand it better. Does anyone have more examples or can recommend me some article that says something about this? Thanks, -Waldecir
[HACKERS] Function call order dependency
Is there a knowable order in which functions are called within a query in PostgreSQL? For example I'll use the Oracle contains function, though this is not exactly what I'm doing, it just illustrates the issue clearly. select *, score(1) from mytable where contains(mytable.title, 'Winding Road', 1) order by score(1); The contains function does a match against mytable.title for the term 'Winding Road' and both returns and saves an integer score which may be retrieved later using the score(...) function. The integer used as a parameter in score(...) and contains(...) is an index to reference which score you need as more than one contains(...) call may be used in single query. This sets up an interesting issue, how can one ensure that contains() is called prior to any score() function on each row? Is this possible? Is there a specific order on which you can count? Would it be something like: where clause first, left to right, followed by select terms, left to right, and lastly the order by clause? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Function call order dependency
[EMAIL PROTECTED] writes: For example I'll use the Oracle contains function, though this is not exactly what I'm doing, it just illustrates the issue clearly. select *, score(1) from mytable where contains(mytable.title, 'Winding Road', 1) order by score(1); The contains function does a match against mytable.title for the term 'Winding Road' and both returns and saves an integer score which may be retrieved later using the score(...) function. This is just a bad, bad idea. Side-effects in a WHERE-clause function are guaranteed to cause headaches. When (not if) it breaks, you get to keep both pieces. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Function call order dependency
[EMAIL PROTECTED] wrote: Would it be something like: where clause first, left to right, followed by select terms, left to right, and lastly the order by clause? I don't know what ANSI says, nor do I know what PostgreSQL exactly does at the moment, but, the only thing you can reasonably count on is that the WHERE clause is evaluated before the SELECT-result-rows and the ORDER BY clause (in any SQL database). You cannot depend on any left to right order, and you cannot depend on ORDER BY being evaluated after the SELECT-result-rows. -- Sincerely, Stephen R. van den Berg. Clarions sounding *No one* expects the Spanish inquisition! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Function call order dependency
[EMAIL PROTECTED] writes: For example I'll use the Oracle contains function, though this is not exactly what I'm doing, it just illustrates the issue clearly. select *, score(1) from mytable where contains(mytable.title, 'Winding Road', 1) order by score(1); The contains function does a match against mytable.title for the term 'Winding Road' and both returns and saves an integer score which may be retrieved later using the score(...) function. This is just a bad, bad idea. Side-effects in a WHERE-clause function are guaranteed to cause headaches. When (not if) it breaks, you get to keep both pieces. I was kind of afraid of that. So, how could one implement such a function set? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Function call order dependency
I was kind of afraid of that. So, how could one implement such a function set? Write a function (say, score_contains) that returns NULL whenever contains would return false, and the score otherwise. SELECT * FROM ( SELECT *, score_contains(mytable.title, 'Winding Road', 1) AS score FROM mytable ) x WHERE x.score IS NOT NULL ORDER BY x.score ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Function call order dependency
[EMAIL PROTECTED] writes: For example I'll use the Oracle contains function, though this is not exactly what I'm doing, it just illustrates the issue clearly. select *, score(1) from mytable where contains(mytable.title, 'Winding Road', 1) order by score(1); The contains function does a match against mytable.title for the term 'Winding Road' and both returns and saves an integer score which may be retrieved later using the score(...) function. This is just a bad, bad idea. Side-effects in a WHERE-clause function are guaranteed to cause headaches. When (not if) it breaks, you get to keep both pieces. Well, I guess I need to alter the question a bit. I need to perform an operation during query time and there are multiple results based on the outcome. For instance: (Lets try this) select myrank(t1.column1, t2.column2, 1) as rank, myscore(t1.column1,t2.column2, 1) as score from t1, t2 where myrank(t1.column1,t2.column2) 10 order by myscore(t1.column1, t2.column2, 1) desc; This is a bit messier, and I wanted to resist this approach as it is ugly. The underlying code will check the values of the first and second parameters and only perform the operation if a previous call did not already act on the current parameters. Now, can I assume that in the above select statement, that each permutation of t1.column1 and t2.column2 will only be evaluated once and that myscore(...) and myrank(...) will all be called before the next permutation is evaluated? So, basically, I don't want to recalculate the values for each and every function call as that would make the system VERY slow. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Function call order dependency
[EMAIL PROTECTED] writes: I need to perform an operation during query time and there are multiple results based on the outcome. For instance: (Lets try this) select myrank(t1.column1, t2.column2, 1) as rank, myscore(t1.column1,t2.column2, 1) as score from t1, t2 where myrank(t1.column1,t2.column2) 10 order by myscore(t1.column1, t2.column2, 1) desc; Why not have one function that produces multiple output columns? Now, can I assume that in the above select statement, that each permutation of t1.column1 and t2.column2 will only be evaluated once and that myscore(...) and myrank(...) will all be called before the next permutation is evaluated? You can assume that functions in the SELECT target list are evaluated exactly once per output row (at least as long as no SRFs-in-the-targetlist are involved). I don't think it'd be wise to assume anything about order of evaluation, though it's probably true that it's left-to-right at the moment. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Function call order dependency
I was kind of afraid of that. So, how could one implement such a function set? Write a function (say, score_contains) that returns NULL whenever contains would return false, and the score otherwise. SELECT * FROM ( SELECT *, score_contains(mytable.title, 'Winding Road', 1) AS score FROM mytable ) x WHERE x.score IS NOT NULL ORDER BY x.score That could work, and while it fits my example, my actual need is a bit more complex. For instance, say I have two variables (I actually have a few that I need) select myvar1(1), myvar2(1), myvar3(1) from mytable where myfunction(mytable.column, 'some text to search for', 1) 2; How could I ensure that (1) myfunction is called prior to myvar1(), myvar2(), and myvar3()? I think the answer is that I can't. So, the obvious solution is to pass all the variables to all the functions and have it first come first served. The next issue is something like this: select *, myvar1(t1.col1,t2.col2,1), myvar2(t1.col1.t2.col2,1) from t1,t2 where myfunction(t1.col1,t2.col2,1) 10 order by myvar3(t1.col1,t2.col2,1) desc; Using a first come first served strategy, is there any discontinuity between the function calls for t1.col1 and t2.col2. Will they all be called for a particular combination of t1.col1 and t2.col2, in some unpredictable order before the next row(s) combination is evaluated or will I have to execute the underlying algorithm for each and every call? ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Function call order dependency
[EMAIL PROTECTED] writes: I need to perform an operation during query time and there are multiple results based on the outcome. For instance: (Lets try this) select myrank(t1.column1, t2.column2, 1) as rank, myscore(t1.column1,t2.column2, 1) as score from t1, t2 where myrank(t1.column1,t2.column2) 10 order by myscore(t1.column1, t2.column2, 1) desc; Why not have one function that produces multiple output columns? I was sort of trying to make this a fairly generic SQL extension who's methodology could be moved to other databases if needed. I guess multiple columns could work. I've got some code in another extension that does that. Now, can I assume that in the above select statement, that each permutation of t1.column1 and t2.column2 will only be evaluated once and that myscore(...) and myrank(...) will all be called before the next permutation is evaluated? You can assume that functions in the SELECT target list are evaluated exactly once per output row (at least as long as no SRFs-in-the-targetlist are involved). I don't think it'd be wise to assume anything about order of evaluation, though it's probably true that it's left-to-right at the moment. But are all the items targeted in close proximity to each other BEFORE moving on to the next row? What about the where clause? would that be called out of order of the select target list? I'm doing a fairly large amount of processing and doing it once is important. / -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Function call with offset and limit
Hi
I've just tried it, and it works. So it's a good work-around.
Though, is it a wanted feature to have a function being performed on
each row before the offset ?
Le mercredi 21 décembre 2005 à 13:41 -0600, Jim C. Nasby a écrit :
Have you tried
SELECT *, test_func(idkeyword)
FROM (SELECT * FROM tag OFFSET 5000 LIMIT 1)
;
?
This should probably have been on -general, btw.
On Wed, Dec 21, 2005 at 06:44:33PM +0100, REYNAUD Jean-Samuel wrote:
Hi all,
We need to find a solution for a strange problem.
We have a plpgsql FUNCTION which performs an heavy job (named
test_func).
CREATE or replace function test_func(z int) returns integer as $$
declare
tst integer;
begin
--
-- Large jobs with z
--
tst := nextval('test_truc');
return tst;
end;
$$ LANGUAGE plpgsql;
So I made this test:
test=# select setval('test_truc',1);
setval
1
(1 row)
test=# select currval('test_truc') ;
currval
-
1
(1 row)
test=# select *,test_func(idkeyword) from tag offset 5000 limit 1;
idkeyword | test_func
---+-
5001 | 5002
(1 row)
test=# select currval('test_truc') ;
currval
-
5002
(1 row)
This demonstrates that the function is called 5001 times though only one
row is returned. Problem is that this heavy job is performed much, much
more than needed.
But, If I do:
test=# select *,(select test_func(1)) from tag offset 5000 limit 1;
My function is called only once.
Is there any work around ?
Thanks
--
REYNAUD Jean-Samuel [EMAIL PROTECTED]
Elma
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
--
REYNAUD Jean-Samuel [EMAIL PROTECTED]
Elma
---(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
Re: [HACKERS] Function call with offset and limit
On Thu, Dec 22, 2005 at 10:52:58AM +0100, REYNAUD Jean-Samuel wrote: Hi I've just tried it, and it works. So it's a good work-around. Though, is it a wanted feature to have a function being performed on each row before the offset ? Well, saying offset 5000 pretty much means to calculate the first 5000 rows and throw away the result. To calculate that it needs to execute the function each time. What happens if the function has side-effects like in your case? What if you had a WHERE clause that depended on the result of that function? If the function has no side-effects, like say pow() then the backend could skip but that should be transparent to the user. SQL allows you specify the way you want it and PostgreSQL is simply executing what you wrote down... 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. pgpyWoANLmTEz.pgp Description: PGP signature
Re: [HACKERS] Function call with offset and limit
On Thu, Dec 22, 2005 at 11:18:22AM +0100, Martijn van Oosterhout wrote: On Thu, Dec 22, 2005 at 10:52:58AM +0100, REYNAUD Jean-Samuel wrote: Hi I've just tried it, and it works. So it's a good work-around. Though, is it a wanted feature to have a function being performed on each row before the offset ? Well, saying offset 5000 pretty much means to calculate the first 5000 rows and throw away the result. To calculate that it needs to execute the function each time. What happens if the function has side-effects like in your case? What if you had a WHERE clause that depended on the result of that function? If the function has no side-effects, like say pow() then the backend could skip but that should be transparent to the user. SQL allows you specify the way you want it and PostgreSQL is simply executing what you wrote down... Well, it would be a good optimization to make if the function is immutable and isn't otherwise referenced (ie: by WHERE or ORDER BY), there's no reason I can think of to execute it as you read through the rows. Might be able to do this with STABLE functions as well. TODO? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Function call with offset and limit
Hi all,
We need to find a solution for a strange problem.
We have a plpgsql FUNCTION which performs an heavy job (named
test_func).
CREATE or replace function test_func(z int) returns integer as $$
declare
tst integer;
begin
--
-- Large jobs with z
--
tst := nextval('test_truc');
return tst;
end;
$$ LANGUAGE plpgsql;
So I made this test:
test=# select setval('test_truc',1);
setval
1
(1 row)
test=# select currval('test_truc') ;
currval
-
1
(1 row)
test=# select *,test_func(idkeyword) from tag offset 5000 limit 1;
idkeyword | test_func
---+-
5001 | 5002
(1 row)
test=# select currval('test_truc') ;
currval
-
5002
(1 row)
This demonstrates that the function is called 5001 times though only one
row is returned. Problem is that this heavy job is performed much, much
more than needed.
But, If I do:
test=# select *,(select test_func(1)) from tag offset 5000 limit 1;
My function is called only once.
Is there any work around ?
Thanks
--
REYNAUD Jean-Samuel [EMAIL PROTECTED]
Elma
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] Function call with offset and limit
Have you tried
SELECT *, test_func(idkeyword)
FROM (SELECT * FROM tag OFFSET 5000 LIMIT 1)
;
?
This should probably have been on -general, btw.
On Wed, Dec 21, 2005 at 06:44:33PM +0100, REYNAUD Jean-Samuel wrote:
Hi all,
We need to find a solution for a strange problem.
We have a plpgsql FUNCTION which performs an heavy job (named
test_func).
CREATE or replace function test_func(z int) returns integer as $$
declare
tst integer;
begin
--
-- Large jobs with z
--
tst := nextval('test_truc');
return tst;
end;
$$ LANGUAGE plpgsql;
So I made this test:
test=# select setval('test_truc',1);
setval
1
(1 row)
test=# select currval('test_truc') ;
currval
-
1
(1 row)
test=# select *,test_func(idkeyword) from tag offset 5000 limit 1;
idkeyword | test_func
---+-
5001 | 5002
(1 row)
test=# select currval('test_truc') ;
currval
-
5002
(1 row)
This demonstrates that the function is called 5001 times though only one
row is returned. Problem is that this heavy job is performed much, much
more than needed.
But, If I do:
test=# select *,(select test_func(1)) from tag offset 5000 limit 1;
My function is called only once.
Is there any work around ?
Thanks
--
REYNAUD Jean-Samuel [EMAIL PROTECTED]
Elma
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Function call
On Tue, 27 Jan 2004, Tom Lane wrote:
each candidate. func_select_candidate depends on having some notion of
the same argument position, but what does that mean in such a case?
While it is true that I don't know everything about the current code I
still claim that it can not be such a big problem as you try to paint
a picture of here. And even more importantly, even if it is it should not
affect the current behaviour, more about that later.
If you have function:
f(x int, y text)
f(y float, x text);
and you make a call
f(2,'foo');
then for each candidate above there is some matching going on based on the
order of the arguments. I just assume that this works as it should today,
even if I don't know the details. Not everyone have worked with PG before
and knows everything directly.
Now, lets assume there is a call
f (y = 2, x = 'foo')
then for each candidate the order is fixed again. I hoped to treat it
differently for each candidate, so the possible calls are
f('foo',2)
f(2, 'foo')
and these orders are the same every time we look at a candidate.
Now, the above is just my plan before coding and before understanding
everything. It might work and it might not. So far I've got no reason to
thing that it wont work,
Let's assume that I can't make something like the above to work as fast
as today. For functions calls without named arguments then the current
fast function can still be used. Only for the new kind of calls do you
need to do something more fancy. That would make named calls be a lot
slower (relatively speaking) then calls without named and a fixed order of
the arguments. The problem here is not speed but code duplication.
There are also some difficult questions raised by schemas and search
paths. s1.f1(text, text) masks s2.f1(text, text) if s1 appears before
s2 in your search path.
But does s1.f1(foo text, bar text) mask s2.f1(baz text, xyzzy text)?
Does your answer change depending on whether the actual call has
parameter names or not?
That is an open question, one can go either way. I think both will work
and both will be understandable/predictable from the programmers point of
view.
For that matter, should f1(foo text, bar text) and f1(baz text, xyzzy
text) be considered to be different function signatures that ought to be
permitted to coexist in a single schema? If actual parameter names are
going to affect resolution of search-path ambiguity, it's hard to argue
that the parameter names aren't part of the signature.
At first I plan to not have the argument names as part of the signature.
Mainly because if one start without one can add it later if needed. To
have it part of the signature only lets you define more functions then
today. The other database that implements this does have the argument
names as part of the signature.
I think that the value of having it is no that big. Just don't name your
functions and arguments like that. Rejecting cases like that above will
not make life harder for the programmer. It would rather help him/her
designing better functions. If it hurts, don't do it.
What might be the best compromise is to treat parameter names as
documentation *only*, that is, we insist that the parameters have to
appear in the declared order in any case.
That would suck big time.
About the speed, how many functions do you have with the same name.
Try select proname, count(*) from pg_proc group by 1 order by 2 desc;
Note that the ones at the top are pretty popular in usage, not only in
having lots of variants. I don't think it's acceptable to take major
speed hits in parsing them
There will be no hit at all since the functions calls for these don't use
named arguments, the exact same method of function resolution as today
should work fine. You just need to detect at the start if this function
call is with or without named arguments. I have never had any plans of
slowing down the current method, including the fast case where all
arguments have the correct types. I'm sure I will run into problems, like
the above and/or others. I'll have to deal with it when I run into it.
There is of course another way all this can turn out also, that I don't
manage to make it work in a good way. In that case there will be no named
parameter function calls (unless somebody else makes them).
In any case, let me try to make it work before we throw it away. I work on
pg on some free hours here and there. It might take some time until I have
something working, but when I do I would love for you to review the patch
pointing out all errors!
The worst that can happen is that it doesn't work. So what, I can live
with that :-)
--
/Dennis Björklund
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] Function call
Dennis Bjorklund [EMAIL PROTECTED] writes: Now, the above is just my plan before coding and before understanding everything. It might work and it might not. So far I've got no reason to thing that it wont work, Before you start writing anything, I suggest you read http://www.postgresql.org/docs/7.4/static/typeconv-func.html I can see at least three assumptions in there that will be broken by allowing different candidate functions to have arguments matched in different orders. That's not even counting the questions about whether we should allow the names of parameters to affect which functions are considered to be potential candidates. What might be the best compromise is to treat parameter names as documentation *only*, that is, we insist that the parameters have to appear in the declared order in any case. That would suck big time. I don't think you should reject it out of hand. It's simple and understandable, and it is guaranteed not to break any existing code when the programmer simply adds names to the parameter declarations of a function without changing any call sites. If the presence of parameter names changes the ambiguity resolution rules at all, I'm doubtful that we could guarantee not to break things. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Function call
On Wed, 28 Jan 2004, Tom Lane wrote: when the programmer simply adds names to the parameter declarations of a function without changing any call sites. If the presence of parameter names changes the ambiguity resolution rules at all, I'm doubtful that we could guarantee not to break things. Agreed. Calls without argument names shall work exactly as today. If you are saying that a call like foo(x = 14, y = 'text') shall always call the same function even if you add parameter names to a function who did not have it before. Then that is wrong. -- /Dennis Björklund ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Function call
I've been looking in the sql200x draft and there are no function calls with named arguments. Thinking more about it, I'm not sure if it really is an important addition at all. I've got a number of requests for the feature. so there are people that want it, that much I know. I don't think it's very hard to add. I've been playing a little with it, making myself familiar with the code. Before making an implementation I just want to make sure that we really want an implementation. It's a neat feture, but it's not that very important so I wanted to bring it up. If one combine it with default values on parameters, then it's more useful since you easier can leave out arguments when you use the ident = expr syntax. Default values is also not in the standard. -- /Dennis Björklund ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Function call
Dennis Bjorklund wrote: Thinking more about it, I'm not sure if it really is an important addition at all. I've got a number of requests for the feature. so there are people that want it, that much I know. I like it very much, and I think mostly everyone else does, too. It's just a question of what syntax to use. Personally, I would be OK with =. The SQL standard already attaches a special meaning to - (object dereference, like in C), so I'd just avoid all arrows as operator names. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Function call
Peter Eisentraut [EMAIL PROTECTED] writes: Dennis Bjorklund wrote: Thinking more about it, I'm not sure if it really is an important addition at all. I've got a number of requests for the feature. so there are people that want it, that much I know. I like it very much, and I think mostly everyone else does, too. It's just a question of what syntax to use. There are some pretty severe implementation problems that I haven't seen mentioned yet. In particular, how will you avoid individually trawling through every function with a matching name to try to match up the arguments? The index on proargtypes won't help you if you don't know what order the arguments are actually in. And I think the heuristics in func_select_candidate() that involve comparing matches at the same argument position will break down completely. (Adding default values would make overloaded functions an order of magnitude slower yet, not to mention outright ambiguous.) regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Function call
On Tue, 27 Jan 2004, Tom Lane wrote: In particular, how will you avoid individually trawling through every function with a matching name to try to match up the arguments? I don't think you can avoid that. But it's just done once to find the oid of the real function, so if it's used multiple times in the same query it's not that bad. In most cases you don't have that many functions with the same name anyway. I've looked at the current code that finds the correct function and it looked doable. But, I wont know until I make the implementation. And don't hold your breath, I can't work all the time on this, so a couple of days work might take a couple of weeks. I have some code already, but it's not nearly done. func_select_candidate() that involve comparing matches at the same argument position will break down completely. I was planning to reorder the arguments before the matching according to the function prototype so I can reuse the old select_candidate (more or less, the reordering needs to be done for each matching). But if it's not that simple I will just have to solve it in some more difficult way. In any case, the currect semantics will stay the same. (Adding default values would make overloaded functions an order of magnitude slower yet, not to mention outright ambiguous.) The ambigious part i've complained about to the people that have asked me for the feature. Now I've come to the conclusion that it doesn't really matter if it's ambigious. If I can't find one function that match then I'll just throw an error. There are still a lot of cases where it is useful and where there are no amiguities. About the speed, how many functions do you have with the same name. I don't think I've ever seen more then 10 or something. It should not be that slow to iterate over that a couple of times (I hope). It will never be as fast as a direct call, with the correct types of course. Of course I still like it to be fast, but it can never be as fast. -- /Dennis Björklund ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Function call
On Tue, 27 Jan 2004, Peter Eisentraut wrote: just a question of what syntax to use. Personally, I would be OK with =. That's also what I'm leaning towards now. As Greg suggested, just making = a special case as a function parameter. And if one want's to call a function with an expression containing a = one have to write foo((x=23)) and not foo(x=23). That's the current plan I have, I think it's implementable in a not so ugly way. If that works out the symbol is not stolen, I would just borrow it a little when it's the top level of an expression in a function call position. -- /Dennis Björklund ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Function call
Dennis Bjorklund [EMAIL PROTECTED] writes: On Tue, 27 Jan 2004, Tom Lane wrote: func_select_candidate() that involve comparing matches at the same argument position will break down completely. I was planning to reorder the arguments before the matching according to the function prototype so I can reuse the old select_candidate (more or less, the reordering needs to be done for each matching). You're not thinking this through. The reordering might be different for each candidate. func_select_candidate depends on having some notion of the same argument position, but what does that mean in such a case? There are also some difficult questions raised by schemas and search paths. s1.f1(text, text) masks s2.f1(text, text) if s1 appears before s2 in your search path. But does s1.f1(foo text, bar text) mask s2.f1(baz text, xyzzy text)? Does your answer change depending on whether the actual call has parameter names or not? For that matter, should f1(foo text, bar text) and f1(baz text, xyzzy text) be considered to be different function signatures that ought to be permitted to coexist in a single schema? If actual parameter names are going to affect resolution of search-path ambiguity, it's hard to argue that the parameter names aren't part of the signature. What might be the best compromise is to treat parameter names as documentation *only*, that is, we insist that the parameters have to appear in the declared order in any case. All we do with the names in a call (if supplied) is check that they match the function declaration after we have resolved which function is meant using the existing methods. (Adding default values would make overloaded functions an order of magnitude slower yet, not to mention outright ambiguous.) The ambigious part i've complained about to the people that have asked me for the feature. Now I've come to the conclusion that it doesn't really matter if it's ambigious. Yes it does. About the speed, how many functions do you have with the same name. Try select proname, count(*) from pg_proc group by 1 order by 2 desc; Note that the ones at the top are pretty popular in usage, not only in having lots of variants. I don't think it's acceptable to take major speed hits in parsing them, especially not if the hit occurs whether one uses the named-parameters feature or not ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Function call
On Tue, 27 Jan 2004, Tom Lane wrote: speed hits in parsing them, especially not if the hit occurs whether one uses the named-parameters feature or not ... I'll read the rest of the mail more careful tomorrow moring, I just want to point out directly that for calls that doesn't use named arguments you get the exact same speed as before. Except for an extra if() to check if there are named arguments. I don't understand why you think that this will affect the current behaviour. That is more or less the only thing I feel sure about, that it should not hurt anything that we have today. -- /Dennis Björklund ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Function call
Dennis Bjorklund [EMAIL PROTECTED] writes: I'll read the rest of the mail more careful tomorrow moring, I just want to point out directly that for calls that doesn't use named arguments you get the exact same speed as before. Except for an extra if() to check if there are named arguments. I don't understand why you think that this will affect the current behaviour. It looked to me like you were talking about a major redesign of func_select_candidate and friends. I'll be interested to see how you do it without that. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Function call crashes server
Removing the special-case logic in ExecTypeFromTL yields regression=# SELECT update_pg_pwd(); ERROR: getTypeOutputInfo: Cache lookup of type 0 failed Wouldn't it be nice to make this a feature that allows stored procedures (void update_pg_pwd ()) ? Correctly register this function to not return anything ? This is what the 0 is actually supposed to mean here, no ? Such a proc would need a fmgr, that generates an empty resultset. Andreas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Function call crashes server
Bruce Momjian [EMAIL PROTECTED] writes: If I do this as any user: SELECT update_pg_pwd(); it crashes all backends and causes a server-wide restart. Is this acceptable behavior? There are a number of things we might blame this on, all having to do with the overuse of type OID zero to mean too many different things. But my attention is currently focused on this tidbit in ExecTypeFromTL: TupleDescInitEntry(typeInfo, resdom-resno, resdom-resname, /* fix for SELECT NULL ... */ (restype ? restype : UNKNOWNOID), resdom-restypmod, 0, false); Had ExecTypeFromTL rejected restype = 0 rather than substituting UNKNOWNOID (a pretty durn random response, IMHO), we'd not see this crash. The fix for SELECT NULL appears to have been committed by you on 7 Dec 1996. Care to explain it? (AFAICT, SELECT NULL does not produce a zero at this point now, though perhaps it did in 1996. Or was there some other case you were defending against back then?) regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Function call crashes server
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: If I do this as any user: SELECT update_pg_pwd(); it crashes all backends and causes a server-wide restart. Is this acceptable behavior? There are a number of things we might blame this on, all having to do with the overuse of type OID zero to mean too many different things. But my attention is currently focused on this tidbit in ExecTypeFromTL: TupleDescInitEntry(typeInfo, resdom-resno, resdom-resname, /* fix for SELECT NULL ... */ (restype ? restype : UNKNOWNOID), resdom-restypmod, 0, false); Had ExecTypeFromTL rejected restype = 0 rather than substituting UNKNOWNOID (a pretty durn random response, IMHO), we'd not see this crash. The fix for SELECT NULL appears to have been committed by you on 7 Dec 1996. Care to explain it? (AFAICT, SELECT NULL does not produce a zero at this point now, though perhaps it did in 1996. Or was there some other case you were defending against back then?) That was 6 months into the Internet-based project. We were just patching things to prevent crashes. My guess is that I was trying to fix the much more common case of SELECT NULL and had no idea how it would affect functions that return no value. Feel free to wack it around. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
