Re: [HACKERS] 2nd Level Buffer Cache
On Mon, 21 Mar 2011 10:24:22 +, Greg Stark wrote: On Fri, Mar 18, 2011 at 11:55 PM, Josh Berkus j...@agliodbs.com wrote: To take the opposite approach... has anyone looked at having the OS just manage all caching for us? Something like MMAPed shared buffers? Even if we find the issue with large shared buffers, we still can't dedicate serious amounts of memory to them because of work_mem issues. Granted, that's something else on the TODO list, but it really seems like we're re-inventing the wheels that the OS has already created here... A lot of people have talked about it. You can find references to mmap going at least as far back as 2001 or so. The problem is that it would depend on the OS implementing things in a certain way and guaranteeing things we don't think can be portably assumed. We would need to mlock large amounts of address space which most OS's don't allow, and we would need to at least mlock and munlock lots of small bits of memory all over the place which would create lots and lots of mappings which the kernel and hardware implementations would generally not appreciate. Actually, just from curious, I done test with mmap, and I got 2% boost on data reading, maybe because of skipping memcpy in fread. I really curious how fast, if even, it will be if I add some good and needed stuff and how e.g. vacuum will work. snip 2-level caches work well for a variety of applications. I think 2-level caches with simple heuristics like pin all the indexes is unlikely to be helpful. At least it won't optimize the average case and I think that's been proven. It might be helpful for optimizing the worst-case which would reduce the standard deviation. Perhaps we're at the point now where that matters. Actually, 2nd level caches do not pin index buffer. It's just, in simple words, some set of reserved buffers' ids to be used for index pages, all logic with pining, etc. it's same, the difference is that default level operation will not touch 2nd level. I post some reports from my simple tests. When I was experimenting with 2nd level caches I saw that some operations may swap out system tables buffers, too. snip Regards, Radek -- 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] 2nd Level Buffer Cache
On Thu, 17 Mar 2011 16:02:18 -0500, Kevin Grittner wrote: Rados*aw Smogurarsmog...@softperience.eu wrote: I have implemented initial concept of 2nd level cache. Idea is to keep some segments of shared memory for special buffers (e.g. indices) to prevent overwrite those by other operations. I added those functionality to nbtree index scan. I tested this with doing index scan, seq read, drop system buffers, do index scan and in few places I saw performance improvements, but actually, I'm not sure if this was just random or intended improvement. I've often wondered about this. In a database I developed back in the '80s it was clearly a win to have a special cache for index entries and other special pages closer to the database than the general cache. A couple things have changed since the '80s (I mean, besides my waistline and hair color), and PostgreSQL has many differences from that other database, so I haven't been sure it would help as much, but I have wondered. I can't really look at this for a couple weeks, but I'm definitely interested. I suggest that you add this to the next CommitFest as a WIP patch, under the Performance category. https://commitfest.postgresql.org/action/commitfest_view/open There is few places to optimize code as well, and patch need many work, but may you see it and give opinions? For something like this it makes perfect sense to show proof of concept before trying to cover everything. -Kevin Yes, there is some change, and I looked at this more carefully, as my performance results wasn't such as I expected. I found PG uses BufferAccessStrategy to do sequence scans, so my test query took only 32 buffers from pool and didn't overwritten index pool too much. This BAS is really surprising. In any case when I end polishing I will send good patch, with proof. Actually idea of this patch was like this: Some operations requires many buffers, PG uses clock sweep to get next free buffer, so it may overwrite index buffer. From point of view of good database design We should use indices, so purging out index from cache will affect performance. As the side effect I saw that this 2nd level keeps pg_* indices in memory too, so I think to include 3rd level cache for some pg_* tables. Regards, Radek -- 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] Binary in/out for aclitem
On Thu, 24 Feb 2011 08:38:35 -0600, Merlin Moncure wrote: On Wed, Feb 23, 2011 at 3:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: =?utf-8?q?Rados=C5=82aw_Smogura?= rsmog...@softperience.eu writes: Here is extended version, has version field (N_ACL_RIGHTS*2) and reserved mask, as well definition is more general then def of PGSQL. In any way it require that rights mades bit array. You're going in quite the wrong direction here. The consensus as I understood it was that we should just use the text representation in binary mode too, rather than inventing a separate representation that's going to put a whole new set of constraints on what can happen to the internal representation. The proposal you have here has no redeeming social value whatever, because nobody cares about the I/O efficiency for aclitem (and even if anyone did, you've made no case that this would actually be more efficient to use on the client side). +1 on this. binary wire format is a win generally when one of the two properties is true: 1) the receiving application is putting it into a binary structure that is similar to what the backend sends, and conversion is non-trivial (timestamps, geo types, etc) 2) text format needs lots of escaping (bytea, arrays etc) Let's take the numeric type for example...if we were debating the binary wire format for that type, I would be arguing for the backend to send a string for the binary wire format unless someone could present a solid case that the postgres format dropped right into a popular numeric library in C, etc (AFAIK, it doesn't). Almost everyone that gets a numeric will directly translate it to a string or a hardware binary representation which the backend can't send. Even if you could make the case for aclitem on performance grounds, you still have to get past tom's objection (which I agree with) that the performance benefit outweighs having to deal with making and (especially) maintaining the binary wire format. It should be becoming obvious to everyone the binary formats are becoming increasingly popular, and sooner or later backwards compatibility issues and other unresolved issues pertaining to them have to be dealt with. Point being, let's not make that more difficult than it has to be. merlin Thanks, but actually I didn't realized final direction, pass to text or create something really extensive, I didn't treat aclitem IO as live or dead case, just all. I always treat performance really serious, but I'm not psychopathic to check aclitem IO!!! Btw, In my opinion binary format will be popular not for speed, but for that it is internal strict, and pass in many situations more useful informations (e.g. types for structs, arrays), it is just easier to maintain on driver side. But it is still unpopular maybe due to missing methods :), and few others. Regards, Radek -- 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] Binary in/out for aclitem
On Tue, 22 Feb 2011 20:20:39 -0500, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Tue, Feb 22, 2011 at 5:24 PM, Tom Lane t...@sss.pgh.pa.us wrote: It'd be more future-proof than this patch, but I'm still unconvinced about the use-case. Do we want to intentionally make binary format a second-class citizen? Well, it's not exactly a first-class citizen; compare for instance the amount of verbiage in the docs about text I/O formats versus the amount about binary formats. But my question isn't about that; it's about why aclitem should be considered a first-class citizen. It makes me uncomfortable that client apps are looking at it at all, because any that do are bound to get broken in the future, even assuming that they get the right answers today. I wonder how many such clients are up to speed for per-column privileges and non-constant default privileges for instance. And sepgsql is going to cut them off at the knees. regards, tom lane Technically, at eye glance, I didn't seen in sepgsql modifications to acl.h. So, I think, aclitem will be unaffected. In any way sepgsql needs some way to present access rights to administrator it may use own model, or aclitem, too. JDBC, and other applications may use aclitem to get just information about who has what access. I think psql does this in same manner as JDBC, by calling select from pg_class. But if user, through psql, JDBC or other driver. will invoke select * from pg_class it will fail with no binary output, because it is plain user query. Currently proposed binary output has space for 4 more privs. Am I right? One thing I realized, I do not pass flag if grant target is group or user. Regards, Radek -- 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] Void binary patch
On Tue, 22 Feb 2011 07:01:02 -0500, Robert Haas wrote: On Sun, Feb 20, 2011 at 5:20 AM, Radosław Smogura rsmog...@softperience.eu wrote: Just patch for missing procedures for void send/recv What problem does this fix? Can not execute stored procedures in JDBC with out arguments, I think function retuning void as well, and some other minors. Ofc with binary mode. Regards, Radek -- 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] Void binary patch
On Tue, 22 Feb 2011 08:12:23 -0600, Merlin Moncure wrote: On Tue, Feb 22, 2011 at 6:01 AM, Robert Haas robertmh...@gmail.com wrote: On Sun, Feb 20, 2011 at 5:20 AM, Radosław Smogura rsmog...@softperience.eu wrote: Just patch for missing procedures for void send/recv What problem does this fix? void returning functions may not be called when binary protocol is requested currently. this is annoying: some drivers that wrap libpq or the protocol directly use the binary mode exclusively and this causes headaches for them. put another way, 'void' is the only POD type missing send/recv. merlin Just curious what POD means? -- 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] Fwd: [JDBC] Weird issues when reading UDT from stored function
Something like this, Everything must be done on call, due to polymorphic signatures, this can be kept in short living cache, but bear in mind user can alter procedure in meantime. When JDBC driver will detect if procedure call statement is created. 1. Determine procedure oid - how? procedures may have not qualified name. Is any function on backend that will deal with schema search path? You may need to pass procedure parameters or at least types? or we need to mirror backend code to Java? 2. Download procedure signature and parse, determine what is input and what is output. 3. Determine how many output parameters user registered, if 1st parameter is ? = exec(?, ?) 4. If only 1 parameter is output (and its UDT, pure UDT due to relkind) use SELECT (RESULT) as your_param_name FROM f(params) AS RESULT, if I remember well using () puts all in on record Above will resolve some other problems in JDBC. Ad 3. Problem is with 1st parameter, actually result of such procedure may be record, so I think I should get in our address example, when call ? = ench(addres ?), result set like address, address But this is to discussion. Postgresql has own roads, far away from support of any standard. On Thu, 17 Feb 2011 13:14:46 +1300, Oliver Jowett wrote: On 17/02/11 04:23, Tom Lane wrote: Florian Pflug f...@phlo.org writes: Hm, I've browsed through the code and it seems that the current behaviour was implemented on purpose. Yes, it's 100% intentional. The idea is to allow function authors to use OUT-parameter notation (in particular, the convention of assigning to a named variable to set the result) without forcing them into the overhead of returning a record when all they want is to return a scalar. So a single OUT parameter is *supposed* to work just like a function that does returns whatever without any OUT parameters. Even if you think this was a bad choice, which I don't, it's far too late to change it. Any suggestions about how the JDBC driver can express the query to get the behavior that it wants? Specifically, the driver wants to call a particular function with N OUT or INOUT parameters (and maybe some other IN parameters too) and get a resultset with N columns back. The current approach is to say SELECT * FROM f(params) AS RESULT which works in all cases *except* for the case where there is exactly one OUT parameter and it has a record/UDT type. Oliver -- 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] Fwd: [JDBC] Weird issues when reading UDT from stored function
Yes new node should be created and added for 8.x and 9.x releases... On Thu, 17 Feb 2011 10:53:19 +0100, Pavel Stehule wrote: 2011/2/17 Florian Pflug f...@phlo.org: On Feb17, 2011, at 10:30 , rsmogura wrote: When JDBC driver will detect if procedure call statement is created. 1. Determine procedure oid - how? procedures may have not qualified name. Is any function on backend that will deal with schema search path? You may need to pass procedure parameters or at least types? or we need to mirror backend code to Java? That change of getting this correct without help from the backend is exactly zero. (Hint: You need to consider overloaded functions and implicit casts of parameters...) There is only one way - implementation of CALL statement. Any emulation on JDBC level is just way to hell. Now, we have to say - PostgreSQL doesn't support a CALL statement, support only functions - and everybody has to use a different pattern than in other databases. Any emulation on JDBC means, it will be slowly, it will be unpredictable. Regards Pavel Stehule best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] Fwd: [JDBC] Weird issues when reading UDT from stored function
Maybe change in backend to treat complex types marked in relation as COMPLEX in same way as scalar values is solution, actually I don't know. This can be determined by GUC variable so every one can be happy :) On Thu, 17 Feb 2011 23:08:13 +1300, Oliver Jowett wrote: Lukas Eder wrote: The result set meta data correctly state that there are 6 OUT columns. But only the first 2 are actually fetched (because of a nested UDT)... The data mangling was just a plpgsql syntactic issue, wasn't it? Oliver -- 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] Fwd: [JDBC] Weird issues when reading UDT from stored function
Yes, but driver checks number of declared out parameters and number of
resulted parameters (even check types of those), to prevent programming
errors.
On Thu, 17 Feb 2011 23:15:07 +1300, Oliver Jowett wrote:
Florian Pflug wrote:
On Feb17, 2011, at 01:14 , Oliver Jowett wrote:
Any suggestions about how the JDBC driver can express the query to
get
the behavior that it wants? Specifically, the driver wants to call
a
particular function with N OUT or INOUT parameters (and maybe some
other
IN parameters too) and get a resultset with N columns back.
There's no sane way to do that, I fear. You could of course look up
the
function definition in the catalog before actually calling it, but
with
overloading and polymorphic types finding the right pg_proc entry
seems
awfully complex.
Your best option is probably to just document this caveat...
Well, the JDBC driver does know how many OUT parameters there are
before execution happens, so it could theoretically do something
different for 1 OUT vs. many OUT parameters.
The problem is that currently the translation of the JDBC { call }
escape happens early on, well before we know which parameters are OUT
parameters. Moving that translation later is, at best, tricky, so I
was hoping there was one query form that would handle all cases.
Oliver
--
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] Fwd: [JDBC] Weird issues when reading UDT from stored function
On Fri, 18 Feb 2011 00:06:22 +1300, Oliver Jowett wrote: On 17/02/11 23:18, rsmogura wrote: Yes, but driver checks number of declared out parameters and number of resulted parameters (even check types of those), to prevent programming errors. And..? Oliver And it will throw exception when result will income. If you will remove this then you will lose check against programming errors, when number of expected parameters is different that number of actual parameters. Bear in mind that you will get result set of 6 columns, but only 1 is expected. I think you can't determine what should be returned and how to fix result without signature. -- 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] Fwd: [JDBC] Weird issues when reading UDT from stored function
On Fri, 18 Feb 2011 00:44:07 +1300, Oliver Jowett wrote: On 18/02/11 00:37, rsmogura wrote: On Fri, 18 Feb 2011 00:06:22 +1300, Oliver Jowett wrote: On 17/02/11 23:18, rsmogura wrote: Yes, but driver checks number of declared out parameters and number of resulted parameters (even check types of those), to prevent programming errors. And..? Oliver And it will throw exception when result will income. If you will remove this then you will lose check against programming errors, when number of expected parameters is different that number of actual parameters. Bear in mind that you will get result set of 6 columns, but only 1 is expected. I think you can't determine what should be returned and how to fix result without signature. You've completely missed the point. I am not suggesting we change those checks at all. I am suggesting we change how the JDBC driver translates call escapes to queries so that for N OUT parameters, we always get exactly N result columns, without depending on the datatypes of the parameters in any way. Oliver May You provide example select for this, and check behaviour with below procedure, too. CREATE OR REPLACE FUNCTION p_enhance_address3(OUT address u_address_type, OUT i1 integer) RETURNS record AS $BODY$ BEGIN SELECT t_author.address INTO address FROM t_author WHERE first_name = 'George'; i1 = 12; END; $BODY$ LANGUAGE plpgsql -- 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] Fwd: [JDBC] Weird issues when reading UDT from stored function
snip testdb=# CREATE FUNCTION p_enhance_address4 (address OUT u_address_type) AS $$ BEGIN address := (SELECT t_author.address FROM t_author WHERE first_name = 'George'); END; $$ LANGUAGE plpgsql; CREATE FUNCTION testdb=# SELECT * FROM p_enhance_address4(); street | zip | city| country | since | code ++---+-++-- (Parliament Hill,77) | NW31A9 | Hampstead | England | 1980-01-01 | (1 row) The second problem is that the JDBC driver always generates calls in the SELECT * FROM ... form, but this does not work correctly for one-OUT-parameter-that-is-a-UDT, as seen in the example immediately above. Here's how to do the call for that particular case: testdb=# SELECT p_enhance_address4(); p_enhance_address4 --- ((Parliament Hill,77),NW31A9,Hampstead,England,1980-01-01,) (1 row) The challenge is that the bare SELECT form doesn't work for multiple OUT parameters, so the driver has to select one form or the other based on the number of OUT parameters. Any questions? (I'm sure there will be questions. Sigh.) Oliver I don't want to blame or anything similar, any idea is good, as any effort as well, but if user will register one output parameter, but procedure will have two will it be possible to check this? I'm little lost in this nested records. If there will be no such check I suggest to configure this by connection parameter, because in any way UDTs aren't such popular, user should have choice to decide I want better checks, or I need this! Everything is on my side. -- 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] Fwd: [JDBC] Weird issues when reading UDT from stored function
If I may give some suggestion, I was tried to investigate this, and maybe some this will help When you create procedure with out parameters then return type of this is implicit calculated and may be record or base type (if exactly one out param is defined). In many places I saw comparison of return type to recordoid or complex type, but check against complex type is through pg_types only, if typtype is marked 'c'. Unfortunately both rows and STRUCT (complex) has there 'c' - and this is OK for situation when procedure will return table. But for complex types not being recordoid I think additional check should go. I mean to use get_rel_relkind() and e.g. check if it is pure complex type. By the way, Actually, based on above I saw funny things - I can create table with column type being other table :) And now If my one output parameter will be of complex type and relkind row type, what should I get? On Wed, 16 Feb 2011 09:30:43 +0100, Lukas Eder wrote: I'm not trying to fix the signature. I want exactly that signature. I want to return 1 UDT as an OUT parameter from a function. Somewhere between JDBC and the database, this signature is lost, and JDBC's internal code tells me that I have to bind 6 OUT parameters, instead of 1. It happens to be so, because the UDT contains 6 attributes, so somehow the JDBC/database protocol flattens the UDT, and I think that's a bug, either in JDBC or in the protocol or in the database. My findings were that I can correctly read the UDT OUT parameter using the pgAdmin III tool, so I excluded the database as a bug holder candidate. Cheers Lukas 2011/2/15 Robert Haas On Sat, Feb 12, 2011 at 6:16 AM, Lukas Eder wrote: I had tried that before. That doesn't seem to change anything. JDBC still expects 6 OUT parameters, instead of just 1... Oh, hrm. I thought you were trying to fix the return value, rather than the signature. I am not sure how to fix the signature. Can you just make it return RECORD? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com [2] The Enterprise PostgreSQL Company Links: -- [1] mailto:lukas.e...@gmail.com [2] http://www.enterprisedb.com [3] mailto:robertmh...@gmail.com -- 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] Fwd: [JDBC] Weird issues when reading UDT from stored function
Hi, I don't know if this is a bug, but at least I haven't found any clear statement in documentation about; this should be wrote with big and bold letters. In any way I think this is bug or big inconsistency, because of, as was stated in previous mail test=# CREATE FUNCTION p_enhance_address3 (address OUT u_address_type, i1 OUT int) AS $$ BEGIN SELECT t_author.address INTO address FROM t_author WHERE first_name = 'George'; i1 = 12; END; $$ LANGUAGE plpgsql; test=# select * from p_enhance_address3(); address | i1 + (((Parliament Hill,77),NW31A9),) | 12 (1 row), but if you will create above function without last, i1 parameter (SELECT * FROM p_enhance_address2();) then result will be street| zip | city | country | since | code -+-+--+-+---+-- ((Parliament Hill,77),NW31A9) | | | | | In last case, I think, result should be packed in one column, because of it clearly unpacked record. On Tue, 25 Jan 2011 14:39:51 +0700, Lukas Eder wrote: Here, we've somehow got the first two fields of u_address_type - street and zip - squashed together into one column named 'street', and all the other columns nulled out. I think this is the old problem of PL/pgsql having two forms of SELECT INTO. You can either say: SELECT col1, col2, col3, ... INTO recordvar FROM ... Or you can say: SELECT col1, col2, col3, ... INTO nonrecordvar1, nonrecordvar2, nonrecordvar3, ... FROM ... In this case, since address is a recordvar, it's expecting the first form - thus the first select-list item gets matched to the first column of the address, rather than to address as a whole. It's not smart enough to consider the types of the items involved - only whether they are records. :-( So what you're suggesting is that the plpgsql code is causing the issues? Are there any indications about how I could re-write this code? The important thing for me is to have the aforementioned signature of the plpgsql function with one UDT OUT parameter. Even if this is a bit awkward in general, in this case, I don't mind rewriting the plpgsql function content to create a workaround for this problem... -- 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] Fwd: [JDBC] Weird issues when reading UDT from stored function
Dear hackers :) Could you look at this thread from General.
---
I say the backend if you have one row type output result treats it as
the full output result, it's really bad if you use STRUCT types (in your
example you see few columns, but this should be one column!). I think
backend should return ROWDESC(1), then per row data describe this row
type data. In other words result should be as in my example but without
last column. Because this funny behaviour is visible in psql in JDBC I
think it's backend problem or some far inconsistency. I don't see this
described in select statement.
Kind regards,
Radek
On Tue, 11 Jan 2011 23:54:19 +0100, Lukas Eder wrote:
Hmm, you're right, the result seems slightly different. But still the
UDT record is not completely fetched as if it were selected directly
from T_AUTHOR in a PreparedStatement...
2011/1/11 Radosław Smogura
I've done:
test=# CREATE FUNCTION p_enhance_address3 (address OUT
u_address_type, i1 OUT
int)
AS $$
BEGIN
SELECT t_author.address
INTO address
FROM t_author
WHERE first_name = 'George';
i1 = 12;
END;
$$ LANGUAGE plpgsql;
test=# select *
from p_enhance_address3();
address
| i1
+
(((Parliament Hill,77),NW31A9),) | 12
(1 row)
Result is ok. Because UDT is described in same way as row, it's
looks like
that backand do this nasty thing and instead of 1 column, it sends
6 in your
case.
Forward to hackers. Maybe they will say something, because I don;t
see this in
docs.
Radek
Lukas Eder Tuesday 11 January 2011 16:55:52
Looks to me like you're getting each field of the UDT as a
separate
column. You printed only the first column i.e. the 'street'
part.
Exactly, that's what I'm getting
It might be informative to run with loglevel=2 and see how the
server is
returning results. If the driver is reporting 6 columns, that
means that
the server is reporting 6 fields in its RowDescription message.
Here's what I get (there really is a RowDescription(6)):
===
08:15:44.914 (1) PostgreSQL 9.0 JDBC4 (build 801)
08:15:44.923 (1) Trying to establish a protocol version 3
connection to
localhost:5432
08:15:44.941 (1) FE= StartupPacket(user=postgres,
database=postgres,
client_encoding=UNICODE, DateStyle=ISO, extra_float_digits=2)
08:15:44.962 (1) 08:15:44.968 (1) FE=
Password(md5digest=md5ea57d63c7d2afaed5abb3f0bb88ae7b8)
08:15:44.970 (1) 08:15:44.980 (1) 08:15:44.980 (1)
08:15:44.980 (1) 08:15:44.980 (1) 08:15:44.981 (1)
08:15:44.981 (1) 08:15:44.981 (1) 08:15:44.981 (1)
08:15:44.981 (1) 08:15:44.981 (1) 08:15:44.981 (1)
08:15:44.981 (1) 08:15:44.981 (1) 08:15:44.981 (1)
compatible = 9.0
08:15:44.981 (1) loglevel = 2
08:15:44.981 (1) prepare threshold = 5
getConnection returning
driver[className=org.postgresql.Driver,org.postgresql.dri...@77ce3fc5]
08:15:45,021 DEBUG [org.jooq.impl.StoredProcedureImpl
] - Executing query : { call public.p_enhance_address2(?) }
08:15:45.035 (1) simple execute,
handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@
2eda2cef, maxRows=0, fetchSize=0, flags=17
08:15:45.036 (1) FE= Parse(stmt=null,query=select * from
public.p_enhance_address2() as result,oids={2278})
08:15:45.037 (1) FE= Bind(stmt=null,portal=null,=)
08:15:45.038 (1) FE= Describe(portal=null)
08:15:45.038 (1) FE= Execute(portal=null,limit=0)
08:15:45.038 (1) FE= Sync
08:15:45.043 (1) 08:15:45.044 (1) 08:15:45.045 (1)
08:15:45.046 (1) 08:15:45.046 (1) 08:15:45.062 (1)
org.postgresql.util.PSQLException: Ein CallableStatement wurde mit
einer
falschen Anzahl Parameter ausgeführt.
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2S
tatement.java:408) at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.
java:381) at
org.jooq.impl.StoredProcedureImpl.execute(StoredProcedureImpl.java:125)
at
org.jooq.test.postgres.generatedclasses.Procedures.pEnhanceAddress2(Procedu
res.java:91) [...]
SQLException: SQLState(42601)
08:15:45.074 (1) FE= Terminate
===
Oops, looking closer I see what you mean, that's actually 2
columns of the
surrounding type - street + zip?
Yes, exactly. Somehow the driver stops at the second type element
of the
surrounding type. This may be correlated to the fact that the
inner type
has exactly 2 elements?
What are the values of the other 5 columns reported by the
driver?
The other 5 columns are reported as null (always).
In pgAdmin III, I correctly get a single column in the result
set. Also,
the postgres information_schema only holds one parameter:
===
select parameter_mode, parameter_name, udt_name
from information_schema.parameters
where specific_name like 'p_enhance_address2%'
