Re: [HACKERS] [JDBC] Optimize postgres protocol for fixed size arrays
On 25 November 2011 07:54, Mikko Tiihonen mikko.tiiho...@nitorcreations.com wrote: =BE ParameterStatus(binary_minor = 23) FE= Execute(SET binary_minor = 20) Yeah this was almost exactly what I was thinking about how to retrofit it, except it might be clearer to have, say, supported_binary_minor (read-only, advertised by the server on startup) vs. binary_minor (read-write, defaults to 0) as otherwise you have special behavior for just one parameter where the advertised version doesn't actually match the currently-set version. Re list vs. always-incrementing minor version, you could just use an integer and set bits to represent features, which would keep it simple but also let clients be more selective about which features they implement (you could support feature 21 and 23 without supporting 22) 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] [JDBC] Optimize postgres protocol for fixed size arrays
On 24 November 2011 05:36, Tom Lane t...@sss.pgh.pa.us wrote: Now it's possible we could do that without formally calling it a protocol version change, but I don't care at all for the idea of coming up with one-off hacks every time somebody decides that some feature is important enough that they have to have it Right Now instead of waiting for a sufficient accumulation of reasons to have a protocol flag day. I think but we made arrays a bit smaller! is a pretty lame response to have to give when somebody complains that Postgres 9.2 broke their client software. When we do it, I want to have a *long* list of good reasons. Can we get a mechanism for minor protocol changes in this future version? Something as simple as exchanging a list of protocol features during the initial handshake (then use only features that are present on both sides) would be enough. The difficulty of making any protocol changes at the moment is a big stumbling block. (You could probably retrofit that to the current protocol version) 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] [JDBC] Optimize postgres protocol for fixed size arrays
On 23 November 2011 10:47, Mikko Tiihonen mikko.tiiho...@nitorcreations.com wrote: Here is a patch that adds a new flag to the protocol that is set when all elements of the array are of same fixed size. When the bit is set the 4 byte length is only sent once and not for each element. Another restriction is that the flag can only be set when there are no NULLs in the array. How does a client detect that this feature is supported? At a glance the JDBC patch doesn't use it on the send path, but presumably clients could use this when sending binary-format arrays to the server - but only if the server understood the format. (Ideally a pair of settings would be useful here - one to say the server understands the new format and another the client sets to say please use the new format that defaults to off - then you could avoid confusing old clients, too) 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
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
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 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 -- 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 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 -- 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 18/02/11 00:52, rsmogura wrote: 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 Oh god I'm going round and round in circles repeating myself! There are two problems. The first problem is a plpgsql problem in that particular function. It's broken regardless of how you call it. Here's how to fix it: 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 -- 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 18/02/11 01:08, Florian Pflug wrote:
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.
Right, I had forgotten that JDBC must be told about OUT parameter with
registerOutputType()
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.
Hm, now I'm confused. Even leaving the single-OUT-parameter problem aside,
the JDBC statement {call f(?,?)} either translates to
SELECT * FROM f($1)
or
SELECT * FROM f($1, $2)
depending on whether one of the parameter is OUT. Without knowing the number
of output parameters, how do you distinguish these two cases?
Currently it always includes *all* parameters in the call, regardless of
the number of OUT parameters (as mentioned, it doesn't even know how
many OUT parameters there are at that point). As we discover OUT
parameters, we bind void types to them, and the server does the rest of
the heavy lifting. Something roughly equivalent to this:
testdb=# PREPARE s1(void) AS SELECT * FROM p_enhance_address4($1); --
function has no IN parameters, one OUT parameter
PREPARE
testdb=# EXECUTE s1(null);
street | zip | city| country | since| code
++---+-++--
(Parliament Hill,77) | NW31A9 | Hampstead | England | 1980-01-01 |
(1 row)
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 17/02/11 00:58, Robert Haas wrote: On Wed, Feb 16, 2011 at 3:30 AM, Lukas Eder lukas.e...@gmail.com 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. Oh, OK. Sorry, I can't help you any with the JDBC side... Well, the underlying problem is that SELECT * from function_with_one_out_parameter() is returning *6* columns, not 1 column. I don't know if that's expected or not on the plpgsql side, but the JDBC driver has no way of distinguishing that sort of result from a function that has 6 OUT parameters. 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 17/02/11 01:10, Robert Haas wrote:
If you do SELECT function_with_one_out_parameter() rather than SELECT
* FROM function_with_one_out_parameter(), you'll get just one
argument. Does that help at all?
Unfortunately, not really, because it doesn't work for cases where
there's more than one OUT parameter (if you use the SELECT f() form in
that case, you get one gigantic result column, not one column per OUT
parameter)
I dug into the code and it's actually slightly different to what I
originally described. Currently given a JDBC escape of the form
{ call f(?,?,?,?) }
it will rewrite that to:
SELECT * FROM f($1,$2,$3,$4) AS RESULT
and this rewriting happens before we know which parameters are bound as
OUT parameters. So we can't special-case the one-OUT-parameter case
without quite a rewrite (no pun intended).
Once we get to the point of query execution, we know which parameters
are OUT parameters, and we bind void parameter values to those (v3
protocol). You have to do a PREPARE/EXECUTE to pass in void parameter
types to get the equivalent via psql, as far as I can tell.
Anyway, it's a bit counterintuitive that
SELECT * FROM f($1,$2) AS RESULT
where f() takes two OUT parameters always returns two columns, but
SELECT * FROM f($1) AS RESULT
might return any number of columns! Is that really the correct behavior
here?
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 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
On 17/01/11 17:27, Robert Haas wrote: On Wed, Jan 12, 2011 at 5:12 AM, rsmogurarsmog...@softperience.eu wrote: 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. I've read this report over a few times now, and I'm still not understanding exactly what is happening that you're unhappy about. If I understand it correctly, the problem is this: Given the schema and data from the OP (summary: t_author is a TABLE t_author.address is of type u_address_type u_address_type is a TYPE with fields: street, zip, city, country, since, code u_address_type.street is of type u_street_type u_street_type is a TYPE with fields: street, no) A bare SELECT works as expected: test_udt=# SELECT t_author.address FROM t_author WHERE first_name = 'George'; address --- ((Parliament Hill,77),NW31A9,Hampstead,England,1980-01-01,) (1 row) However, doing the same via a plpgsql function with an OUT parameter produces something completely mangled: test_udt=# CREATE FUNCTION p_enhance_address2 (address OUT u_address_type) AS $$ BEGIN SELECT t_author.address INTO address FROM t_author WHERE first_name = 'George'; END; $$ LANGUAGE plpgsql; CREATE FUNCTION test_udt=# SELECT * FROM p_enhance_address2(); street| zip | city | country | since | code -+-+--+-+---+-- ((Parliament Hill,77),NW31A9) | | | | | (1 row) 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. Unsurprisingly the JDBC driver produces confusing results when faced with this, so it was originally reported as a JDBC problem, but the underlying problem can be seen via psql too. 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] [JDBC] server process (PID 1188) exited with exit code
Tom Lane wrote:
Dave Cramer [EMAIL PROTECTED] writes:
This is a server bug, I will post to hackers for you, it has little
to do with JDBC, however the ? can't be a column in a prepared statement
I cannot reproduce any problem using what I think is equivalent in libpq:
I thought we got this one sorted out already, it was a server bug fixed
in beta2? At the time you said:
JEAN-PIERRE PELLETIER [EMAIL PROTECTED] writes:
08:47:19.296 (1) FE= Parse(stmt=null,query=select $1 from (select * from
pg_database) t,oids={23})
Actually, now that I look closely, this command is almost certainly
triggering this beta1 bug:
http://archives.postgresql.org/pgsql-committers/2006-10/msg00107.php
Please try beta2 and see if it isn't fixed.
regards, tom lane
(and the problem went away in beta2)
-O
---(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] [JDBC] server process (PID 1188) exited with exit code
Tom Lane wrote: NULL,/* let the backend deduce param type */ I think the JDBC driver will be passing the int4 OID for the param type in this case. Best thing is probably for the OP to run with loglevel=2 and see exactly what's being sent, though. -O ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [JDBC] prepareThreshold=1 and statement.executeBatch() ??
On Sun, 13 Nov 2005, Joost Kraaijeveld wrote: I have a connection that is created with prepareThreshold=1 in the connection string. I use a prepared statement that I fill with addbatch() and that I execute with executeBatch() (for full source: see application.java attachment). LOG: statement: PREPARE S_2 AS update prototype.customers set title= $1 , defaultcurrency=$2, defaulttermsofdelivery=$3 , defaulttermsofpayment=$4 where customernumber=$5 LOG: statement: BIND LOG: statement: EXECUTE unnamed [PREPARE: update prototype.customers set title=$1 , defaultcurrency=$2, defaultter msofdelivery=$3, defaulttermsofpayment=$4 where customernumber=$5] LOG: duration: 773.841 ms LOG: statement: BIND LOG: statement: EXECUTE unnamed [PREPARE: update prototype.customers set title=$1 , defaultcurrency=$2, defaultter msofdelivery=$3, defaulttermsofpayment=$4 where customernumber=$5] LOG: duration: 377.981 ms Does this output mean that the prepared statement with the name S_2 is not used in the following 2 EXECUTE statements and that therefor each execute statement is planned again? The driver does not actually issue PREPARE or EXECUTE statements; the server is pretending that the protocol-level Prepare/Bind/Execute messages are actually something issuing PREPARE/EXECUTE at the SQL level (but in reality, nothing is issuing precisely the queries that are being logged -- the query that is submitted is just your plain update ... query). The PREPARE S_2 AS .. logs that a Prepare message was processed (for the query update ...). This does parsing/planning work and creates a named prepared statement called S_2 on the server. The BIND means that some previously prepared statement (you can't tell which statement from what is logged! -- but it's S_2 in this case) is being bound to parameter values via a Bind message, creating an unnamed portal. The EXECUTE unnamed means the unnamed portal is being executed via an Execute message. It also logs the underlying statement at that point, but not the statement name (!). So if I read the logs right, the single prepared statement S_2 *is* being reused in the case above. Yes, it's a horribly confusing way for the server to log things. I raised it on -hackers earlier in the 8.1 cycle, but I've not had time to work on it myself. -O ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] statement logging / extended query protocol issues
Bruce Momjian wrote: Simon's page is in the patches queue. What would you like changed, exactly? I'm not going to have time to comment on this any time soon, sorry :( .. I guess I will try to look at it for 8.2. -O ---(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] stack depth limit exceeded problem.
Thomas Hallgren wrote: PL/Java runs a JVM. Since a JVM is multi threaded, PL/Java goes to fairly extreme measures to ensure that only one thread at a time can access the backend. So far, this have worked well but there is one small problem. [...] I assume this means you have a single lock serializing requests to the backend? If you can't solve the depth checking problem (Tom doesn't seem to like the idea of multiple threads calling into the backend..), what about turning the original thread (i.e. the main backend thread) into a backend interface thread that does nothing but feed callbacks into the backend on request? Then run all the user code in a separate thread that passes backend requests to the interface thread rather than directly executing them. If it starts extra threads which makes DB requests, the mechanism stays the same.. -O ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] statement logging / extended query protocol issues
Bruce Momjian wrote: We don't have a log_statement = verbose mode. Please see my earlier email where I suggested adding one if you really wanted all this protocol-level detail logged. -O ---(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] statement logging / extended query protocol issues
Bruce Momjian wrote:
I think it is more verbose because no FETCH is logged in this type of
prepare/execute. The goal, I think, is for these type of queries to
look as similar to normal PREPARE/EXECUTE and DECLARE/FETCH as possible.
I do not understand why this is a useful thing to do as part of
log_statement.
My point is that given JDBC code like this:
Statement s = connection.createStatement();
ResultSet rs = s.executeQuery(SELECT * FROM pg_proc);
while (rs.next()) {
// Process results
}
it seems that the least surprising thing to get logged is simply SELECT
* FROM pg_proc.
I don't see how logging a synthetic PREPARE/EXECUTE/FETCH sequence (and
DECLARE, now?) is useful. They're not necessarily syntactically correct,
and they're certainly not queries that were actually sent to the
backend. I thought log_statement was meant to answer the question what
queries were submitted to the backend?, rather than to provide a trace
of protocol-level activity..
-O
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] statement logging / extended query protocol issues
Bruce Momjian wrote: Well, from the application writer perspective, you are right it doesn't make sense, This is exactly what the end user is going to say. but this is only because jdbc is using prepare internally. Isn't this mostly irrelevant to the result we want to see? It's a detail of how the interface layer chooses to execute its queries, and 90% of the time the end user is not going to know or care about it. If you were to have written it in libpq, it would make sense, I think, and internally, this is what is happening. We can't assume only interface libraries like jdbc are using this feature. Wait, so is the extended query protocol the poor cousin of what libpq does, or what? You can do Parse/Bind using libpq, can't you? The *meaning* of the Parse/Bind/Execute sequence is quite clear regardless of what interface library is used. I still think that logging just the queries that were actually executed, once per execution, is the sensible thing to do here. I can't see a sequence of protocol messages that would produce a strange result if we used the rules I suggested -- do you have an example where it breaks? As far as I understand things, the protocol-level prepare/execute is identical to the SQL-level prepare/execute, except that there is no need to parse the execute, so it should log like the SQL-level statements, if possible. You can Parse any SQL statement, but you can't PREPARE any SQL statement. So, no, they're not equivalent. That's one aspect of what I meant about generating synthetic statements that weren't syntactially correct (the strange FETCH syntax with ROWS/MAXROWS that Simon was suggesting is another case). -O ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] parameterized fetch
Merlin Moncure wrote: I've noticed that trying to parameterize a fetch statement via ExecParams returns a syntax error: fetch $1 from my_cursor; This is not really a big deal, but maybe it should be documented which statements can be parameterized and which can't Currently the documentation is the backend's grammar. You can only put parameters where there is a PARAM node, which currently means anywhere you can put a c_expr. So if you can replace something with an expression, you can probably also replace it with a parameter. -O ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] statement logging / extended query protocol issues
Simon Riggs wrote: Are we sure there is just 3 cases? I haven't exhaustively checked, but I think those are the main cases. Even if case (3) is not that common, I still want to know it is occurring, to see what effect or overhead it has. I don't want it to be more verbose than the other cases when I set log_statement = all. We'll only see the output for case (3) when someone has programmed things that way by using setFetchSize. Can we put extra output in this case into log_statement = verbose only please? (1) jdbc parse jdbc bind jdbc execute LOG: statement: SELECT * from pg_proc Notice that the parse of the unnamed statement does *not* now generate a log record. What about the syntax error case? (2) jdbc parse S_1 LOG: statement: PREPARE S_1 AS SELECT * from pg_proc (perhaps this should be logged at BIND time, just like the optimization?) jdbc bind S_1 jdbc execute LOG: statement: EXECUTE unnamed [PREPARE: SELECT * from pg_proc] I do not like logging queries that the driver never sent (the driver sends neither PREPARE nor EXECUTE). I also don't see why it's useful to log the statement and portal names. Can we reword this to what I suggested previously? LOG: parse statement: SELECT * from pg_proc LOG: execute statement: SELECT * from pg_proc (3) jdbc prepare S_2 LOG: statement: PREPARE S_2 AS SELECT * from pg_proc jdbc bind S_2 to C_2 jdbc execute C_2 LOG: statement: EXECUTE C_2 ROWS 42 [PREPARE: SELECT * from pg_proc] jdbc next (after cache has run out on 42nd row) v3 protocol sends E for Execute, execution halts at 49 rows for this set of bind parameters LOG: statement: FETCH C_2 ROWS 7 Again I do not like logging synthetic queries that the driver never sent (PREPARE / EXECUTE / FETCH). BTW, if you do it this way, you could get the bizarre PREPARE S_2 AS PREPARE xyz AS SELECT .. result if the application used PREPARE itself. I think that logging the second and subsequent Executes is not normally useful and shouldn't happen when log_statement = all. In that case you don't need to log the portal name either. So for the normal case: LOG: parse statement: SELECT * from pg_proc LOG: execute statement: SELECT * from pg_proc and for the verbose case perhaps something like: LOG: parse statement: SELECT * from pg_proc LOG: execute statement (C_2, 42 rows): SELECT * from pg_proc LOG: fetch statement results (C_2, 7 rows) -O ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] PQ versions request message
James William Pye wrote: The use case primarily applies to custom clients(non-libpq, atm) that support multiple PQ versions that may be implemented in separate modules/libraries. (Avoid loading client-2.0 code for a 3.0 connection, and/or future versions.) libpq automatically negotiates the version using trial and error, effectively(assume 3.0 by sending 'S', if 'E', fallback to 2.0, and reestablish the connection, apparently). The JDBC driver does exactly the same (or you can explicitly specify a protocol version to use) and is effectively loading code on demand anyway, being Java -- but I've seen no problems with the current approach. I think you're trying tho fix a problem that doesn't exist. -O ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] statement logging / extended query protocol issues
Simon Riggs wrote: Looking more closely, I don't think either is correct. Both can be reset according to rewind operations - see DoPortalRewind(). We'd need to add another bool onto the Portal status data structure. AFAIK this is only an issue with SCROLLABLE cursors, which v3 portals aren't. If queries are short and yet there is much fetching, we may see a program whose main delay is because of program-to-server delay because of fetching. So, I'd like to see that in the log, but I agree with your earlier comments that it should be a shorter log line. I'm coming from the point of view of a user who wants to just turn on query logging. The mechanics of the portals aren't of interest to them. Currently, log_statement = all produces markedly different output depending on whether the extended query protocol is used or not, which is very much an implementation detail.. How about log_statement = verbose or something similar to enable logging of all the details, and have all just log Parse and the first Execute? Ideally, even Parse wouldn't be logged, but then we'd need a way to log statements that error during Parse. -O ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] statement logging / extended query protocol issues
Simon Riggs wrote: Subsequent calls to the same portal are described as FETCHes rather than as EXECUTEs. The portal name is still given and number of rows is provided also. I wonder if it might be better to only log the first Execute.. It's not immediately clear to me that it's useful to see all the individual fetches when they're logically part of a single query. -O ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] statement logging / extended query protocol issues
8.1-beta1 produces some odd results with statement logging enabled when
the extended query protocol is used (e.g. when using the JDBC driver).
Repeatedly running a simple query with log_statement = 'all' produces this:
LOG: statement: PREPARE AS SELECT 'dummy statement'
LOG: statement: BIND
LOG: statement: EXECUTE [PREPARE: SELECT 'dummy statement']
[...]
LOG: statement: PREPARE S_2 AS SELECT 'dummy statement'
LOG: statement: BIND
LOG: statement: EXECUTE [PREPARE: SELECT 'dummy statement']
LOG: statement: BIND
LOG: statement: EXECUTE [PREPARE: SELECT 'dummy statement']
LOG: statement: BIND
[...]
Comments:
- The PREPARE lines are misleading as the query actually sent does not
include PREPARE at all.
- The driver never sends EXECUTE as a statement, but it is logged as one.
- PREPARE AS is a confusing way of saying the unnamed statement
- The BIND lines are content-free.
Secondly, running a query that uses portals produces output like this:
LOG: statement: PREPARE S_3 AS SELECT * from pg_proc
LOG: statement: BIND C_4
LOG: statement: EXECUTE C_4 [PREPARE: SELECT * from pg_proc]
LOG: statement: EXECUTE C_4 [PREPARE: SELECT * from pg_proc]
LOG: statement: EXECUTE C_4 [PREPARE: SELECT * from pg_proc]
LOG: statement: EXECUTE C_4 [PREPARE: SELECT * from pg_proc]
LOG: statement: EXECUTE C_4 [PREPARE: SELECT * from pg_proc]
LOG: statement: EXECUTE C_4 [PREPARE: SELECT * from pg_proc]
LOG: statement: EXECUTE C_4 [PREPARE: SELECT * from pg_proc]
Comments:
- The BIND is still fairly content-free.
- The EXECUTEs are a bit misleading as the SELECT was actually only run
once (there are multiple Execute messages for the same portal). You
could infer that there is only one SELECT from the repeated portal name
and the lack of an intervening BIND, I suppose.
8.1 is admittedly better than 8.0 here (8.0 had no logging in this case
at all).. but it's not very user-friendly as it stands. I'm sure the
JDBC list is going to get lots of why does statement logging give me
this weird output questions :/
I've attached the Java code I used to produce this. It expects a single
argument, the JDBC URL to use, e.g.
'jdbc:postgresql://localhost:8101/test?user=oliver'
-O
import java.sql.*;
import java.util.*;
public class TestStatementLogging {
public static void main(String[] args) throws Exception {
Class.forName(org.postgresql.Driver);
Connection conn = DriverManager.getConnection(args[0]);
conn.setAutoCommit(false);
PreparedStatement stmt = conn.prepareStatement(SELECT 'dummy
statement');
for (int j = 0; j 10; ++j)
stmt.executeQuery();
stmt.close();
stmt = conn.prepareStatement(SELECT * from pg_proc);
stmt.setFetchSize(1);
ResultSet rs = stmt.executeQuery();
while (rs.next())
;
stmt.close();
conn.createStatement().execute(I am a syntax error);
}
}
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] API like mysql_ping
Sivakumar K wrote: Do we have an API like mysql_ping to check whether the server is up and running after the connection has been established? At the protocol level, you could send Sync and wait for ReadyForQuery. -O ---(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] TODO questions
Tom Lane wrote: So, the low-tech solution to these gripes seems to be: * uncomment all the entries in postgresql.conf * add comments to flag the values that can't be changed by SIGHUP Can we agree on taking these measures? Doesn't this still mean that a SIGHUP may give you a different configuration to a full restart, even if you don't touch any of the values that can't be changed via SIGHUP? Perhaps we could stick with the current commented-out-defaults scheme if SIGHUP reset all settings to their compiled-in defaults before rereading the config file, i.e. the same logic as normal startup would do (except for those cases where we can't change a setting at runtime). -O ---(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] #escape_string_warning = off
Peter Eisentraut wrote: Also, let's say I have apps now in 7.4/8.0, and I want them to be forward-compatible. Should I make a type called E so that the E'' notation will work, and then use that for strings? What is the right way to do it? To be standards-conforming, don't use any backslash escapes. If you must use them, use the E'' notation. That doesn't really answer the question, though, since none of 7.4/8.0/8.1 interprets '' strings in a strictly standards-conforming way as I understand it. The impression I got from previous discussion was that you need to check the value of the standard_compliant_strings GUC, and double backslashes inside '' only if it was false or missing. -O ---(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] [COMMITTERS] pgsql: Add GUC variables to control keep-alive
Tom Lane wrote: Oliver Jowett [EMAIL PROTECTED] writes: per my linux/socket.h: /* Setsockoptions(2) level. Thanks to BSD these must match IPPROTO_xxx */ #define SOL_IP 0 /* #define SOL_ICMP 1 No-no-no! Due to Linux :-) we cannot use SOL_ICMP=1 */ #define SOL_TCP 6 (I won't get into why using wire-level-protocol constants for syscall option numbering is a bad idea.. :) [ raised eyebrow... ] You think the wire protocol numbers are more likely to change than the syscall assignments? Consider the range of systems that each change would affect. I'm not worried about changing values; I think that representing the option level as an IP protocol number, in an interface that encompasses non-IP protocols, is a bad API design decision. If the rule for setsockopt is pass the IP protocol number as the level argument, then what value should I pass to manipulate, say, X.25-specific settings on an X.25 socket? In practice you use a synthetic value which is outside the range of valid IP protocols. getprotoent() won't know about this value and there's no IPPROTO_ define for it -- just a SOL_ one. So, er, why were you using IP protocol constants as levels again? A clear design would have them as two separate namespaces even if they happened to share values. Also, that assignment is not mere BSD-ism, it's specified by the Single Unix Spec, as well as every other document I could lay my hands on today. Whoever wrote the comments in linux/socket.h is just clueless. That seems a bit unfair, the comment is accurate (the SOL_* constants do need to match the protocol number assignments for compatibility with code that expects the BSD behaviour) and might even predate SUS. From a portability point of view, it certainly seems better to use IPPROTO_* and I have no problem with doing that. It just grates at a design level. Anyway, this doesn't affect the patch one way or the other, which is why I didn't really want to get into it in the first place.. -O ---(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] [COMMITTERS] pgsql: Add GUC variables to control keep-alive
Andrew - Supernews wrote: On 2005-07-31, Oliver Jowett [EMAIL PROTECTED] wrote: I'm not worried about changing values; I think that representing the option level as an IP protocol number, in an interface that encompasses non-IP protocols, is a bad API design decision. The interpretation of that parameter, if not equal to SOL_SOCKET, is clearly protocol-dependent and therefore driven by the protocol family of the socket. It's *not* clearly protocol dependent, that's my point about the API not being clearly designed. SUS just says this: The level argument specifies the protocol level at which the option resides. To set options at the socket level, specify the level argument as SOL_SOCKET. To set options at other levels, supply the appropriate level identifier for the protocol controlling the option. For example, to indicate that an option is interpreted by the TCP (Transport Control Protocol), set level to IPPROTO_TCP as defined in the netinet/in.h header. There's no dependency on socket PF mentioned there, and the obvious reading of that text is that a level identifier uniquely identifies the protocol controlling the option -- so IPPROTO_TCP unambiguously means the TCP protocol. Having multiple socket-PF-dependent namespaces which might overlap is just asking for hard-to-find bugs (if you accidentally manage to use the wrong namespace for the socket, you run the risk of getting weird behaviour rather than an error). Still counts as badly designed in my book, sorry. -O ---(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] [COMMITTERS] pgsql: Add GUC variables to control keep-alive
Larry Rosenman wrote: I think Tom's fix to use IPPROTO_TCP will fix firefly. Ah, I forgot about the we'll just use IP protocol numbers as socket option levels behaviour (BSD-derived?). My Linux man page only talks about SOL_TCP, but I have run into this before and should have remembered.. my bad. per my linux/socket.h: /* Setsockoptions(2) level. Thanks to BSD these must match IPPROTO_xxx */ #define SOL_IP 0 /* #define SOL_ICMP 1 No-no-no! Due to Linux :-) we cannot use SOL_ICMP=1 */ #define SOL_TCP 6 (I won't get into why using wire-level-protocol constants for syscall option numbering is a bad idea.. :) -O ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC
Simon Riggs wrote: I agree we *must* have the GUC, but we also *must* have a way for crash recovery to tell us for certain that it has definitely worked, not just maybe worked. Doesn't the same argument apply to the existing fsync = off case? i.e. we already have a case where we don't provide a crash-recovery guarantee. -O ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] HEAD doesn't cope with libraries in non-default locations
Tom Lane wrote: It appears that somebody has changed things so that the -L switches appear after the -l switches (ie, too late). I'm too tired to investigate now, but my money is on Autoconf 2.59 being the problem ... Perhaps this: http://archives.postgresql.org/pgsql-hackers/2005-07/msg00085.php -O ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] 2PC transaction id
Heikki Linnakangas wrote: On Fri, 1 Jul 2005, Oliver Jowett wrote: Heikki Linnakangas wrote: branch id: Branch Identifier. Every RM involved in the global transaction is given a *different* branch id. Hm, I am confused then -- the XA spec definitely talks about enlisting multiple RMs in a single transaction branch. Can you explain? I oversimplified a bit. The TM *can* enlist multiple threads of control (= connection in JTA) to the same transaction branch. That's called tightly-coupled threads, and they should then be treated as one local transaction in the RM. Ok, I understand that case. What I'm confused about is, for example, 3.3.1 in the DTP:XA spec: 3.3.1 Registration of Resource Managers Normally, a TM involves all associated RMs in a transaction branch. (The TM’s set of RM switches, described in Section 4.3 on page 21 tells the TM which RMs are associated with it.) The TM calls all these RMs with xa_start(), xa_end(), and xa_prepare (), although an RM that is not active in a branch need not participate further (see Section 2.3.2 on page 8). A technique to reduce overhead for infrequently-used RMs is discussed below. That implies it's valid (in fact, normal!) to enlist many different RMs in the same transaction branch. Am I interpreting that correctly? -O ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] 2PC transaction id
Tom Lane wrote: regression=# \h commit prepared Command: COMMIT PREPARED Description: commit a transaction that was earlier prepared for two-phase commit Syntax: COMMIT PREPARED transaction_id Ah, I was looking under '\h commit', '\h prepare' etc. -O ---(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] 2PC transaction id
Heikki Linnakangas wrote: On Fri, 1 Jul 2005, Oliver Jowett wrote: That implies it's valid (in fact, normal!) to enlist many different RMs in the same transaction branch. Am I interpreting that correctly? I see. No, I don't think that's the correct interpretation, though now that you point it out, that paragraph is a bit confusing. What it means, is that the TM always calls xa_start(), xa_end() and xa_prepare() for a RM, even if the transaction doesn't actually have any work to do for the RM. It has to be like that in the XA world, because the TM doesn't know which RMs the application really uses in the transaction. Sorry to keep beating on this, but I still don't see where the spec says that you must have only one RM per transaction branch. 2.2.6 says: 2.2.6 Transaction Branches A global transaction has one or more transaction branches (or branches). A branch is a part of the work in support of a global transaction for which the TM and the RM engage in a separate but coordinated transaction commitment protocol (see Section 2.3 on page 8). Each of the RM’s internal units of work in support of a global transaction is part of exactly one branch. A global transaction might have more than one branch when, for example, the AP uses multiple processes or is involved in the same global transaction by multiple remote APs. So it seems to me that branches are intended to allow independent processes / APs to each have an independent set of tightly coupled threads (as all work on a particular branch is tightly-coupled). There's no mention of having only one RM per branch, which I'd expect to see here if it was a requirement. One implication of the second paragraph is that a single-threaded AP can use a single transaction branch for all the work it does. Disclaimer: I've never used an XA implementation, and I have only little experience with JTA. Mostly the same here. It'd be useful to get input from someone who's actually written XA code.. -O ---(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] 2PC transaction id
Dave Cramer wrote: Do the transaction id's used in 2PC need to be unique across all sessions? They are global IDs, yes. Do we provide a mechanism for this ? If not shouldn't we provide a way to create a unique transaction id ? Well, in XA the XIDs are assigned by the TM, the individual resources (e.g. a postgresql backend) just get *given* an XID to use. -O ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 2PC transaction id
Dave Cramer wrote: I'm thinking of the situation where one transaction occurs on more than one backend, and there is more than one transaction manager. XA XIDs are *global* IDs, i.e. they are unique even with more than one TM involved. It's the responsibility of the TM to generate a globally-unique XID. If you have two different databases involved in the same global transaction, then yes, the two backends could be told to use the same global XID. That's normal. (they don't *have* to be given the same XID as they could be participating in two independent branches of the same global transaction, and in that case the global XIDs will have different branch qualifiers) It's even possible for one resource to do two different independent (local) transactions that are part of the same global transaction -- in that case, the local transactions will be given different XIDs though. But all of this allocation / management of XIDs is done by the TM, the individual resources don't need to do anything beyond associating particular transactions with client-supplied XIDs, which we already do AFAIK. -O ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 2PC transaction id
Oliver Jowett wrote: If you have two different databases involved in the same global transaction, then yes, the two backends could be told to use the same global XID. That's normal. (they don't *have* to be given the same XID as they could be participating in two independent branches of the same global transaction, and in that case the global XIDs will have different branch qualifiers) Thinking about this some more -- it may be necessary for the same XID to be associated with more than one backend transaction at once, possibly even in the same database. This could happen if there are two clients involved in the same global transaction with no branch qualifier change, or if one client manages to get two separate resources that point at the same database. [... experiments ...] Ok, so the second case is actually even more general, since pg_prepared_xacts is scoped cluster-wide not database-wide. So any global transaction that involves two databases on the same cluster could be affected. It seems that you can't PREPARE TRANSACTION more than once (per cluster) with the same GID. That's a bit painful.. Can we make the GID-to-internal-xid mapping for prepared transactions 1:N rather than the current 1:1? COMMIT PREPARED and ROLLBACK PREPARED would need either syntax or behaviour changes: either we need to identify a particular transaction (perhaps via the xid from pg_prepared_xacts.transaction), or they need to operate on *all* transactions with the given GID. I have no idea on how nasty it is to implement this though :) Heikki, any thoughts? -O PS: noticed in passing: psql's help doesn't seem to know about the 2PC command syntax yet. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 2PC transaction id
Tom Lane wrote: Oliver Jowett [EMAIL PROTECTED] writes: Can we make the GID-to-internal-xid mapping for prepared transactions 1:N rather than the current 1:1? No. Ok, so how do we get XA working when a single global transaction involves two databases on the same cluster? The scenario is: - there are two independent resource managers participating in a single global transaction - each resource manager has a connection to the database it is managing, and a SQL-level transaction running against that database - the global TM tells both resource managers to prepare their part of the global transaction, passing the same XID to both - the resource manager translates the xa_prepare() call to a PREPARE TRANSACTION query, using the passed XID as the GID. Currently, one of the PREPARE TRANSACTIONs is going to fail if the two databases happen to be running under the same postmaster. For this particular case we could embed the database name in the GID, but unfortunately that doesn't work in the more general case where you could have two RMs (perhaps in different processes) talking to the same database. Perhaps the second and subsequent RM to prepare could detect the duplicate GID and add a sequence number or something similar to the end -- and reverse this process on commit/rollback/recovery -- but I don't see how you'd do this atomically with the PREPARE TRANSACTION. -O ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 2PC transaction id
Tom Lane wrote: Oliver Jowett [EMAIL PROTECTED] writes: Ok, so how do we get XA working when a single global transaction involves two databases on the same cluster? It's the TM's responsibility to deal with that. I would expect it to hand out transaction IDs that consist of a common prefix and a per-database suffix, if it does not know which resources it's dealing with might share a common GID namespace. Hm, that's not how I read the spec :( Throughout the API is the implication that you can have more than one RM associated with a transaction branch. For example, 3.3.1 says: 3.3.1 Registration of Resource Managers Normally, a TM involves all associated RMs in a transaction branch. (The TM’s set of RM switches, described in Section 4.3 on page 21 tells the TM which RMs are associated with it.) The TM calls all these RMs with xa_start(), xa_end(), and xa_prepare (), although an RM that is not active in a branch need not participate further (see Section 2.3.2 on page 8). A technique to reduce overhead for infrequently-used RMs is discussed below. I don't know if we can reasonably expect TMs not to hand out an identical XID to different RMs in the same global transaction. (anyone with experience with how existing TMs behave want to chime in?) -O ---(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] 2PC transaction id
Oliver Jowett wrote: Tom Lane wrote: It's the TM's responsibility to deal with that. I would expect it to hand out transaction IDs that consist of a common prefix and a per-database suffix, if it does not know which resources it's dealing with might share a common GID namespace. I don't know if we can reasonably expect TMs not to hand out an identical XID to different RMs in the same global transaction. Hm, I suppose we *can* assume that a TM won't hand out the same XID to the same RM twice (except for the special case of TMJOIN), so we could append a per-database suffix in the RM itself (e.g. JDBC driver) to avoid conflicts within a database cluster. -O ---(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] pg_terminate_backend idea
Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: But it still requires me to send some data (such as a dummy query) to the backend before it exits. This is because server side libpq blocks when reading and ignores signals at this time. I believe the fix for this would be to pass a flag down to the libpq routines that we want to be abort in case of signal+flag, set only when doing the main call to recv, so we can kill idle process. Yech! That code is messy enough already, lets not pile another kluge atop it in order to handle something that's not even being requested AFAIR. I ran into the same problem back when I was trying to implement an idle-in-transaction timeout, so solving this might be useful in more than one place.. -O ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Escape handling in strings
Bruce Momjian wrote: I have received very few replies to my suggestion that we implement E'' for escaped strings, so eventually, after a few major releases, we can have '' treat backslashes literally like the SQL standard requires. Just checking: with this plan, a client needs to know what server version is in use to correctly escape strings, correct? That is, there is no escape mechanism that works correctly for both old and new servers? -O ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [PATCHES] Implementing RESET CONNECTION ...
Christopher Kings-Lynne wrote: What would be absolutely ideal is a reset connection command, plus some way of knowing via the protocol if it's needed or not. And a way of notifying the client that a reset has happened. -O ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] NOLOGGING option, or ?
Alon Goldshuv wrote: I think that the basic issue is that there are some database users that would like to take their data and put it into the database without pre-processing it [...] The only responsibility of these users is to explicitly escape any delimiter or 0x0A (LF) characters that they intend to have as DATA. that's all. Haven't you just replaced one preprocessing step with another, then? -O ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] NOLOGGING option, or ?
Luke Lonergan wrote: I propose an extended syntax to COPY with a change in semantics to remove the default of WITH ESCAPE '\'. Er, doesn't this break existing database dumps? -O ---(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] SO_KEEPALIVE
Tom Lane wrote: On the other hand, it seems to me a client-side SO_KEEPALIVE would only be interesting for completely passive clients (perhaps one that sits waiting for NOTIFY messages?) A normal client will try to issue some kind of database command once in awhile, and as soon as that happens, there is a reasonably short timeout before connection failure is reported. If you're unlucky, the server could go down while you're blocked waiting for a query response.. -O ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] SQL_ASCII vs. 7-bit ASCII encodings
Tom Lane wrote: Oliver Jowett [EMAIL PROTECTED] writes: Peter Eisentraut wrote: That would cripple a system that many users are perfectly content with now. Well, I wasn't thinking of using a 7-bit encoding always, just as a replacement for the cases where we currently choose SQL_ASCII. Does that sound reasonable? I agree with what (I think) Peter is saying: that would break things for many people for whom the default works fine now. We are currently seeing a whole lot of complaints due to the fact that 8.0 tends to default to Unicode encoding in environments where previous versions defaulted to SQL-ASCII. That says to me that a whole lot of people were getting along just fine in SQL-ASCII, and therefore that moving further away from that behavior is the wrong thing. In particular, there is not any single one of those complainants who would be happier with a 7-bit-only default; if they were using 7-bit-only data, they'd not have noticed a problem anyway. This is exactly the case where JDBC has problems, and the case I'd like to prevent happening in the first place where possible: SQL_ASCII with non-7-bit data. How do you propose that the JDBC driver converts from SQL_ASCII to UTF-16 (the internal Java String representation)? Changing client_encoding does not help. Requiring the JDBC client to specify the right encoding to use is error-prone at best, and impossible at worst (who says that only one encoding has been used?) I'm not suggesting that a 7-bit encoding is necessarily useful to everyone. I'm saying that we should make it a setting that users have to think about and correctly set before they can insert 8-bit data. If they decide they want SQL_ASCII and the associated client_encoding problems, rather than an appropriate encoding the database understands, so be it; but it's on their head, and requires active intervention before the database starts losing encoding information. If SQL_ASCII plus 8-bit data is considered the right thing to do, then I'd consider the ability to change client_encoding on a SQL_ASCII database without an error to be a bug -- you've asked the server to give you (for example) UTF8, but it isn't doing that. In that case, can we get this to generate an error when client_encoding is set instead of producing invalid output? -O ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SQL_ASCII vs. 7-bit ASCII encodings
Tom Lane wrote: We should wait and see what field experience is like with that, rather than insisting on anything as anal-retentive as disallowing 8-bit data in SQL_ASCII. I didn't suggest changing the behaviour of SQL_ASCII.. -O ---(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] SQL_ASCII vs. 7-bit ASCII encodings
Peter Eisentraut wrote: Am Donnerstag, 12. Mai 2005 04:42 schrieb Oliver Jowett: I suppose that we can't change the semantics of SQL_ASCII without backwards compatibility problems. I wonder if introducing a new encoding that only allows 7-bit ascii, and making that the default, is the way to go. In 8.0, the de facto default encoding is no longer SQL_ASCII, so that problem should go away over time. My 8.0.0 (what I happen to have on hand) initdb creates a SQL_ASCII cluster by default unless I specify -E. Certainly, making 7-bit ASCII the default encoding is not an option. Why is that? -O ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] SQL_ASCII vs. 7-bit ASCII encodings
Peter Eisentraut wrote: Am Donnerstag, 12. Mai 2005 14:57 schrieb Oliver Jowett: My 8.0.0 (what I happen to have on hand) initdb creates a SQL_ASCII cluster by default unless I specify -E. Then you use the locale C. We could create a 7-bit encoding and map it to locale C, I suppose. Ok, I think that's what I intended :) Do we choose SQL_ASCII in any case other than a C locale? That would cripple a system that many users are perfectly content with now. I compare this to the occasional requests to make pg_hba.conf reject everyone by default. We have to err a little on the side of usablity. Anyway, the issue here is the mismatch between the C locale and the SQL_ASCII encoding. The solution is to fix that mismatch, not cripple the entire system. Well, I wasn't thinking of using a 7-bit encoding always, just as a replacement for the cases where we currently choose SQL_ASCII. Does that sound reasonable? -O ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] SQL_ASCII vs. 7-bit ASCII encodings
The SQL_ASCII-breaks-JDBC issue just came up yet again on the JDBC list, and I'm wondering if we can do something better on the server side to help solve it. The problem is that people have SQL_ASCII databases with non-7-bit data in them under some encoding known only to a (non-JDBC) application. Changing client_encoding has no effect on a SQL_ASCII database, it's always passthrough. So when a JDBC client is later written, and the JDBC driver sets client_encoding=UNICODE, we get data corruption and/or complaints from the driver that the server is sending it invalid unicode (because it's really LATIN1 or whatever the original inserter happened to use). At this point the user has real problems as there is existing data in their database in one or more encodings, but the encoding info associated with that data has been lost. Converting such a database to a single database-wide encoding is painful at best. I suppose that we can't change the semantics of SQL_ASCII without backwards compatibility problems. I wonder if introducing a new encoding that only allows 7-bit ascii, and making that the default, is the way to go. This new encoding would be treated like any other normal encoding, i.e. setting client_encoding does transcoding (I expect that'd be a 1:1 mapping in most or all cases) and rejects unmappable characters as soon as they're encountered. Then the problem is visible as soon as problematic strings are given to the server, rather than when a client that depends on having proper encoding information (such as JDBC) happens to be used. If the DB is only using simple 7-bit ASCII, then there's no change in behaviour. If the DB does need to store additional characters, the user is forced to choose an appropriate encoding before any encoding info is lost. Any thoughts on this? -O ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [GENERAL] Invalid unicode in COPY problem
Madison Kelly wrote: Is there a way to store the name in raw binary? Yes: bytea. -O ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Feature freeze date for 8.1
Dave Held wrote: So it seems that a possible solution to that problem is to have a separate connection for keepalive packets that doesn't block and doesn't interfere with normal client/server communication. What does this do that TCP keepalives don't? (other than add extra connection management complexity..) -O ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Feature freeze date for 8.1
Tom Lane wrote: Oliver Jowett [EMAIL PROTECTED] writes: Tom Lane wrote: I'm not convinced that Postgres ought to provide a way to second-guess the TCP stack ... Would you be ok with a patch that allowed configuration of the TCP_KEEPCNT / TCP_KEEPIDLE / TCP_KEEPINTVL socket options on backend sockets? [ shrug... ] As long as it doesn't fail to build on platforms that don't offer those options, I couldn't complain too hard. But do we really need all that? I can't see how you'd aggregate or discard any of those options without losing useful tuning knobs.. if you're going to have one, you might as well have them all. -O ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Feature freeze date for 8.1
Neil Conway wrote: Is there a way to change the socket timeout for some subset of the processes on the machine without hacking the client or server source? The only ways I can see of tuning the TCP idle parameters on Linux are globally via sysfs, or per-socket via setsockopt(). You could LD_PRELOAD something to wrap accept(), I suppose, but that seems needlessly ugly.. -O ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Feature freeze date for 8.1
Peter Eisentraut wrote: Neil Conway wrote: The specific scenario this feature is intended to resolve is idle-in-transaction backends holding on to resources while the network connection times out; I was under the impression that the specific scenario is busy-in-transaction backends continuing to produce and send data while the client has disappeared. Why does the backend ignore network errors and keep sending data? The scenario I need to deal with is this: There are multiple nodes, network-separated, participating in a cluster. One node is selected to talk to a particular postgresql instance (call this node A). A starts a transaction and grabs some locks in the course of that transaction. Then A falls off the network before committing because of a hardware or network failure. A's connection might be completely idle when this happens. The cluster liveness machinery notices that A is dead and selects a new node to talk to postgresql (call this node B). B resumes the work that A was doing prior to failure. B has to wait for any locks held by A to be released before it can make any progress. Without some sort of tunable timeout, it could take a very long time (2+ hours by default on Linux) before A's connection finally times out and releases the locks. -O ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Feature freeze date for 8.1
Tom Lane wrote: Wouldn't it be reasonable to expect the cluster liveness machinery to notify the database server's kernel that connections to A are now dead? No, because it's a node-level liveness test, not a machine-level liveness. It's possible that all that happened is the node's VM crashed. The clustering is all done in userspace. -O ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Feature freeze date for 8.1
Tom Lane wrote: Wouldn't it be reasonable to expect the cluster liveness machinery to notify the database server's kernel that connections to A are now dead? I find it really unconvincing to suppose that the above problem should be solved at the database level. Actually, if you were to implement this as you suggest, you either put full-blown group communication in the kernel (ow, no thanks!) or you implement a system where the DB server's kernel has a heartbeat to each peer (e.g. A) and if that heartbeat stops, it kills the corresponding connections. But that functionality already exists: it is SO_KEEPALIVE. (I think we're arguing in circles here..) -O ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Feature freeze date for 8.1
Chuck McDevitt wrote: Why not just use SO_KEEPALIVE on the TCP socket? We already do, but the default keepalive interval makes it next to useless. -O ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Feature freeze date for 8.1
Neil Conway wrote: [EMAIL PROTECTED] wrote: statement_timeout is not a solution if many processes are waiting the resource. Why not? I think the only problem with using statement_timeout for this purpose is that the client connection might die during a long-running transaction at a point when no statement is currently executing. Tom's suggested transaction_timeout would be a reasonable way to fix this. Adnan, if you think this is such a significant problem (I can't say that I agree), I'd encourage you to submit a patch. I raised this a while back on -hackers: http://archives.postgresql.org/pgsql-hackers/2005-02/msg00397.php but did not get much feedback. Does anyone have comments on that email? It's a problem that is unlikely to happen in normal operation, but you do need to deal with it to cover the network failure cases if you have an otherwise failure-tolerant cluster.. -O ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Feature freeze date for 8.1
Tom Lane wrote: I'm not convinced that Postgres ought to provide a way to second-guess the TCP stack ... this looks to me like I can't convince the network software people to provide me an easy way to override their decisions, so I'll beat up on the database people to override 'em instead. Perhaps the database people don't know the issues and can be browbeaten more easily. Would you be ok with a patch that allowed configuration of the TCP_KEEPCNT / TCP_KEEPIDLE / TCP_KEEPINTVL socket options on backend sockets? -O ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] prepared statements don't log arguments?
Simon Riggs wrote: I assume this replaces the current logging on Parse to avoid duplicate logging? Well, I'm open to discussion, but that isn't what the patch does. I guess I'll wait for your patch and take a look rather than try to guess about what it does, then. My thinking was to add functionality, not take it away. We currently support V2 and V3 connections, so we need to continue to log V2 statements as well as V3 exec phase. V2 is like the V3 simple query case.. -O ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Unicode problems on IRC
Tom Lane wrote: Yeah? Cool. Does John's proposed patch do it correctly? http://candle.pha.pa.us/mhonarc/patches2/msg00076.html Some comments on that patch: Doesn't pg_utf2wchar_with_len need changes for the longer sequences? UtfToLocal also appears to need changes. If we support sequences 4 bytes (U+10), then UtfToLocal/LocalToUtf and the associated translation tables need a redesign as they currently assume the sequence fits in an unsigned int. (IIRC, Unicode doesn't use U+10, but UTF-8 can encode it?) -O ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] prepared statements don't log arguments?
Simon Riggs wrote: I've got a patch to submit that logs the EXEC phase, so you get just the SQL, not the parameters. [...] I assume this replaces the current logging on Parse to avoid duplicate logging? What happens on syntax errors? It's useful to log the statement that failed, but you will need some trickery there since if the Parse logging goes away, we won't have logged anything at the point the error is generated. -O ---(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] prepared statements don't log arguments?
Simon Riggs wrote: OK, thats what I hoped you'd say. With a prepared query all of the statements execute the same plan, so you don't need to know the exact parameters. This isn't true in 8.0 if you are using the unnamed statement (as the JDBC driver does in some cases): the plan chosen depends on the parameter values given in the first Bind. -O ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] prepared statements don't log arguments?
Neil Conway wrote: Christopher Kings-Lynne wrote: I think he has a really excellent point. It should log the parameters as well. neilc=# prepare foo(int, int) as select $1 + $2; PREPARE neilc=# execute foo(5, 10); ... neilc=# execute foo(15, 20); ... % tail /usr/local/pgsql/postmaster.log LOG: statement: prepare foo(int, int) as select $1 + $2; LOG: statement: execute foo(5, 10); LOG: statement: execute foo(15, 20); Query-level EXECUTE is logged, but Bind/Execute via the V3 extended query protocol (which is what the JDBC driver does) isn't. In fact, the logging for the extended query protocol really sucks: the server logs only the Parse, and is silent about Bind/Execute, so there are all sorts of strange cases where your statement logs do not reflect what was actually executed at all. For example, the JDBC driver issues a Parse (but no Execute!) when an application asks for type metadata from a query, and it can issue multiple Bind/Executes for a single Parse. I've raised this before on -hackers but haven't had time to do anything about it myself yet. -O ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] prepared statements don't log arguments?
Greg Stark wrote: Palle Girgensohn [EMAIL PROTECTED] writes: When setting log_statement = all, and using JDBC PreparedStatements, I get $n in the log where the real arguments used to be in previous versions of postgresql: You might want to look into JDBC options to disable use of prepared statements. The old emulation code must still be there in case it runs against a =7.4 database so perhaps there's an option to use it. You can do this by appending '?protocolVersion=2' to the JDBC URL you use (or 'protocolVersion=2' if you already have other URL parameters). If you do this you also lose any features that need V3 protocol support (e.g. query parameter metadata and some resultset metadata). -O ---(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] read-only planner input
Tom Lane wrote: You could make a good case that we just ought to save query text and start from there in any replanning; it'd be the most compact representation, the easiest to copy around, and the least likely to break. What happens if (for example) DateStyle changes between the two parses? (not that I'm sure what the expected behaviour is in that case anyway..) -O ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] invalidating cached plans
Neil Conway wrote: - it is the responsibility of the call site managing the prepared plan to check whether a previously prepared plan is invalid or not -- and to take the necessary steps to replan it when needed. Does this mean that clients that use PREPARE/Parse need to handle plan invalidated as a possible response to EXECUTE/Bind, or will the backend keep the query string / parse tree around and replan on next execution? -O ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Execute and PortalSuspended needs explicit transaction
Francisco Figueiredo Jr. wrote: After some testing, I could send an Execute message with 2 as the manx number of rows. After the second execute I get the following: portal does not exist Severity: ERROR Code: 34000 I noticed that I could only get it working if I explicitly create a transaction. I thought it could be some Sync() messages I was sending after the first execute, but when I removed them, I still get the problems. If you're sending any Sync messages at all between the two Executes, it will indeed cause problems as Sync causes any implicitly-opened transaction to be closed, which will in turn invalidate any non-holdable portals. Do you have a trace of all the messages sent? -O ---(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] UTF8 or Unicode
Karel Zak wrote: Yes, I think we should fix it and remove UNICODE and WIN encoding names from PG code. The JDBC driver asks for a UNICODE client encoding before it knows the server version it is talking to. How do you avoid breaking this? -O ---(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] UTF8 or Unicode
Karel Zak wrote: On Sat, 2005-02-19 at 00:27 +1300, Oliver Jowett wrote: Karel Zak wrote: Yes, I think we should fix it and remove UNICODE and WIN encoding names from PG code. The JDBC driver asks for a UNICODE client encoding before it knows the server version it is talking to. How do you avoid breaking this? Fix JDBC driver as soon as possible. How, exactly? Ask for a 'utf8' client encoding instead of 'UNICODE'? Will this work if the driver is connecting to an older server? Add to 8.1 release notes: encoding names 'UNICODE' and 'WIN' are deprecated and it will removed in next release. Please, use correct names UTF-8 and WIN1215. 8.0 appears to spell it 'utf8'. Removing the existing aliases seems like a fairly gratuitous incompatibility to introduce to me. -O ---(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] win32 performance - fsync question
Evgeny Rodichev wrote: Write cache is enabled under Linux by default all the time I make deal with it (since 1993). It doesn't interfere with fsync(), as linux kernel uses cache flush for fsync. The problem is that most IDE drives lie (or perhaps you could say the specification is ambiguous) about completion of the cache-flush command -- they say Yeah, I've flushed when they have not actually written the data to the media and have no provision for making sure it will get there in the event of power failure. So Linux is indeed doing a cache flush on fsync, but the hardware is not behaving as expected. By turning off the write-cache on the disk via hdparm, you manage to get the hardware to behave better. The kernel is caching anyway, so the loss of the drive's write cache doesn't make a big difference. There was some work done for better IDE write-barrier support (related to TCQ/SATA support?) in the kernel, but I'm not sure how far that has progressed. -O ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] win32 performance - fsync question
Greg Stark wrote: Oliver Jowett [EMAIL PROTECTED] writes: So Linux is indeed doing a cache flush on fsync Actually I think the root of the problem was precisely that Linux does not issue any sort of cache flush commands to drives on fsync. There was some talk on linux-kernel of what how they could take advantage of new ATA features planned on new SATA drives coming out now to solve this. But they didn't seem to think it was urgent or worth the performance hit of doing a complete cache flush. Oh, ok. I haven't really kept up to date with it; I just run with write-cache disabled on my IDE drives as a matter of course. I did see this: http://www.ussg.iu.edu/hypermail/linux/kernel/0304.1/0471.html which implies you're never going to get an implementation that is safe across all IDE hardware :( -O ---(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] Dealing with network-dead clients
Richard Huxton wrote: Oliver Jowett wrote: I'm currently trying to find a clean way to deal with network-dead clients that are in a transaction and holding locks etc. Have you come across the pgpool connection-pooling project? http://pgpool.projects.postgresql.org/ I've looked at it, haven't used it. Might be easier to put a timeout+disconnect in there. It seems like I have the same design issues even if the code lives in pgpool. Also, I'm reluctant to introduce another bit of software into the system just for the sake of timeouts; we have no other need for pgpool functionality. -O ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Dealing with network-dead clients
I'm currently trying to find a clean way to deal with network-dead clients that are in a transaction and holding locks etc. The normal client closes socket case works fine. The scenario I'm worried about is when the client machine falls off the network entirely for some reason (ethernet problem, kernel panic, machine catches fire..). From what I can see, if the connection is idle at that point, the server won't notice this until TCP-level SO_KEEPALIVE kicks in, which by default takes over 2 hours on an idle connection. I'm looking for something more like a 30-60 second turnaround if the client is holding locks. The options I can see are: 1) tweak TCP keepalive intervals down to a low value, system-wide 2) use (nonportable) setsockopt calls to tweak TCP keepalive settings on a per-socket basis. 3) implement an idle timeout on the server so that open transactions that are idle for longer than some period are automatically aborted. (1) is very ugly because it is system-wide. (2) is not portable. Also I'm not sure how well extremely low keepalive settings behave. (3) seems like a proper solution. I've searched the archives a bit and transaction timeouts have been suggested before, but there seems to be some resistance to them. I was thinking along the lines of a SIGALRM-driven timeout that starts at the top of the query-processing loop when in a transaction and is cancelled when client traffic is received. I'm not sure exactly what should happen when the timeout occurs, though. Should it kill the entire connection, or just roll back the current transaction? If the connection stays alive, the fun part seems to be in avoiding confusing the client about the current transaction state. Any suggestions on what I should do here? -O ---(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] Patch Count?
Marc G. Fournier wrote: On Sat, 5 Feb 2005, Matthew T. O'Connor wrote: Well I'm positive I submitted all my pg_autovacuum patches to the patches list, however searching the archives for autovacuum I can't find anything that old. How far back to the searchable archives go? back to 96 or so ... :) I have noticed holes in the archives in the past though; mail that I sent and was delivered back to me wasn't appearing in the archives. I seem to remember it was a particular time period that was missing but I can't remember the exact details. -O ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [NOVICE] Last ID Problem
Tom Lane wrote: Greg Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: How is what you're suggesting more portable? Well, the driver would be free to implement $sth-last_insert_id() using whatever proprietary extensions it has available. The non-portableness would at least be hidden in the driver layer. Are you asserting that last_insert_id() is a portable function? I doubt it. I'm not familiar with the Perl interface, but JDBC has a standardized interface for this: http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Statement.html#executeUpdate(java.lang.String,%20int) http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Statement.html#getGeneratedKeys() I tend to agree that a protocol-level change is easier to support in a driver. If it's done by extending INSERT/UPDATE, the driver will need to parse and modify queries which is hairy at the best of times. -O ---(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] bug w/ cursors and savepoints
Alvaro Herrera wrote: On Wed, Jan 26, 2005 at 05:10:09PM -0500, Tom Lane wrote: I don't think we have a lot of choices: we have to destroy (or at least mark FAILED) all such cursors for the time being. I don't see a lot of difference between marking the portal FAILED and destroying it (maybe I'm looking at the wrong code). So I just took the simpler approach; patch attached. I assume that you can CLOSE a failed portal, but you can't CLOSE a destroyed portal (because it's not there any more)? This is important for the JDBC driver as it creates portals internally, does fetches as the application code demands, then closes the portal at some point after the application is done with it. Having the close fail because of an intervening savepoint rollback isn't great -- the error will cause an unexpected failure of the current transaction. This can happen even if the application doesn't try to use the portal (via ResultSet) after the savepoint rollback at all. It wouldn't be so bad if the driver could track savepoint boundaries, but the current protocol doesn't make that easy.. -O ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] bug w/ cursors and savepoints
Oliver Jowett wrote: Having the close fail because of an intervening savepoint rollback isn't great -- the error will cause an unexpected failure of the current transaction. Never mind -- I just reread the protocol docs, and it's safe to close a nonexistant portal. Did this previously issue a warning, or something similar? I'm sure I had seen problems in this area in the past.. -O ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [PATCHES] Implementing RESET CONNECTION ...
(cc'ing -hackers) Karel Zak wrote: I think command status is common and nice feedback for client. I think it's more simple change something in JDBC than change protocol that is shared between more tools. There is a bit of a queue of changes that would be nice to have but require a protocol version change. If we're going to change the protocol for any of those we might as well handle RESET CONNECTION cleanly too. We need some common way how detect on client what's happen on server -- a way that doesn't mean change protocol always when we add some feature/command to backend. The command status is possible use for this. Command status only works if commands are directly executed. If you can execute the command indirectly, e.g. via a PL, then you'll miss the notification. Making RESET a top-level-only command isn't unreasonable, but using command status won't work as a general approach for notifying clients. We have a mechanism for GUC changes that uses a separate message (ParameterStatus). Perhaps that should be generalized to report different sorts of connection-related changes. -O ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Call for port reports
8.0.0rc1 builds and passes 'make check' on Gentoo Linux (amd64) with the dependencies I have to hand (no tcl or kerberos): $ ./configure --prefix=/home/oliver/pg/8.0.0rc1 --with-pgport=5800 -enable-thread-safety --with-perl --with-python --with-pam -with-openssl $ uname -a Linux extrashiny 2.6.9-gentoo-r3-patched #3 Sun Nov 14 15:18:33 NZDT 2004 x86_64 AMD Athlon(tm) 64 Processor 3500+ AuthenticAMD GNU/Linux $ 8.0.0rc1/bin/psql template1 -t -c 'select version()' PostgreSQL 8.0.0rc1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.3.4 20040623 (Gentoo Linux 3.3.4-r1, ssp-3.3.2-2, pie-8.7.6) -O ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 800RC1 valgrind-detected bug ?
Tom Lane wrote: strk [EMAIL PROTECTED] writes: ==15489== Syscall param write(buf) contains uninitialised or unaddressable byte(s) Valgrind is fairly useless for debugging postgres, because it doesn't know the difference between alignment-pad bytes in a struct and real data. What you've got here is a gripe arising from writing out a struct containing padding. Is there any risk of leaking sensitive data to a file or the network via those uninitialized alignment padding bytes? -O ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] libpq and psql not on same page about SIGPIPE
Bruce Momjian wrote: [... SIGPIPE suppression in libpq ...] Linux also has MSG_NOSIGNAL as a send() flag that might be useful. It suppresses generation of SIGPIPE for just that call. No, it doesn't work for SSL and it's probably not very portable, but it might be a good platform-specific optimization for the common case. -O ---(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] libpq and psql not on same page about SIGPIPE
Tom Lane wrote: If the C library does support queued signals then we will read the existing SIGPIPE condition and leave our own signal in the queue. This is no problem to the extent that one pending SIGPIPE looks just like another --- does anyone know of platforms where there is additional info carried by a SIGPIPE event? POSIX.1b / SA_SIGINFO? SIGPIPE does not fill much of siginfo_t, but the 3rd handler arg has the interrupted execution context. -O ---(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] [JDBC] Strange server error with current 8.0beta driver
Barry Lind wrote:
I also have the test case (in java) down to the bare minimum that
generated the following output (that test case is attached). (Note that
if the FETCH in the test case is not executed then the backend crashes;
with the FETCH you get an error: ERROR: unrecognized node type: 0)
I narrowed this down to:
while (true) {
l_stmtDeclare.execute();
}
producing:
FE= Parse(stmt=S_1,query=BEGIN,oids={})
FE= Bind(stmt=S_1,portal=null)
FE= Execute(portal=null,limit=0)
FE= Parse(stmt=S_2,query=DECLARE CUR CURSOR FOR SELECT 1,oids={})
FE= Bind(stmt=S_2,portal=null)
FE= Describe(portal=null)
FE= Execute(portal=null,limit=0)
FE= Sync
=BE ParseComplete [S_1]
=BE BindComplete [null]
=BE CommandStatus(BEGIN)
=BE ParseComplete [S_2]
=BE BindComplete [null]
=BE NoData
=BE CommandStatus(DECLARE CURSOR)
=BE ReadyForQuery(T)
simple execute, [EMAIL PROTECTED], maxRows=0, fetchSize=0, flags=0
FE= Bind(stmt=S_2,portal=null)
FE= Describe(portal=null)
FE= Execute(portal=null,limit=0)
FE= Sync
=BE BindComplete [null]
=BE NoData
=BE ErrorMessage(ERROR: unrecognized node type: 2139062143
Location: File: clauses.c, Routine: expression_tree_mutator, Line: 3237
Server SQLState: XX000)
Valgrind says this is the culprit:
==26451== Invalid read of size 4
==26451==at 0x8185C86: eval_const_expressions_mutator (clauses.c:1185)
==26451==by 0x8185C32: eval_const_expressions (clauses.c:1152)
==26451==by 0x817D1A6: preprocess_expression (planner.c:415)
==26451==by 0x817CEBF: subquery_planner (planner.c:240)
==26451==by 0x817CD59: planner (planner.c:129)
==26451==by 0x810DF03: PerformCursorOpen (portalcmds.c:87)
==26451==by 0x81C1402: PortalRunUtility (pquery.c:934)
==26451==by 0x81C1762: PortalRunMulti (pquery.c:1001)
==26451==by 0x81C0D8E: PortalRun (pquery.c:617)
==26451==by 0x81BDDA7: exec_execute_message (postgres.c:1673)
==26451==by 0x81BF6E1: PostgresMain (postgres.c:3035)
==26451==by 0x818FC39: BackendRun (postmaster.c:2817)
==26451==by 0x818F642: BackendStartup (postmaster.c:2453)
==26451==by 0x818D989: ServerLoop (postmaster.c:1198)
==26451==by 0x818CDBA: PostmasterMain (postmaster.c:917)
==26451==by 0x81570F4: main (main.c:268)
==26451== Address 0x1BBBF704 is 260 bytes inside a block of size 1024 free'd
==26451==at 0x1B905460: free (vg_replace_malloc.c:153)
==26451==by 0x8245706: AllocSetDelete (aset.c:466)
==26451==by 0x82468B8: MemoryContextDelete (mcxt.c:193)
==26451==by 0x8247BCF: PortalDrop (portalmem.c:384)
==26451==by 0x82475B5: CreatePortal (portalmem.c:179)
==26451==by 0x81BD735: exec_bind_message (postgres.c:1369)
==26451==by 0x81BF4EF: PostgresMain (postgres.c:3023)
==26451==by 0x818FC39: BackendRun (postmaster.c:2817)
==26451==by 0x818F642: BackendStartup (postmaster.c:2453)
==26451==by 0x818D989: ServerLoop (postmaster.c:1198)
==26451==by 0x818CDBA: PostmasterMain (postmaster.c:917)
==26451==by 0x81570F4: main (main.c:268)
With a bit of gdb work, I think what is happening is this:
The first Execute of S_2, running in portal context, calls the planner
on the query contained in S_2's DeclareCursorStmt. The planner modifies
the query tree in the course of planning it (specifically, it modifies
parse-targetList). Memory allocated for the modified query comes from
the portal context.
The portal context is freed implicitly by the second Bind of S_2 (second
stack trace above).
The second Execute of S_2 then tries to use parse-targetList when
planning (first stack trace above), but that's now pointing to freed
memory. Boom.
Perhaps PerformCursorOpen should copy the query tree before planning, or
plan in a different memory context?
-O
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [JDBC] Strange server error with current 8.0beta driver
Oliver Jowett wrote: Perhaps PerformCursorOpen should copy the query tree before planning, or plan in a different memory context? Patch attached. It moves query planning inside the new portal's memory context. With this applied I can run Barry's testcase without errors, and valgrind seems OK with it too. -O Index: src/backend/commands/portalcmds.c === RCS file: /projects/cvsroot/pgsql/src/backend/commands/portalcmds.c,v retrieving revision 1.36 diff -c -r1.36 portalcmds.c *** src/backend/commands/portalcmds.c 16 Sep 2004 16:58:28 - 1.36 --- src/backend/commands/portalcmds.c 24 Nov 2004 09:28:34 - *** *** 67,73 * query, so we are not expecting rule rewriting to do anything * strange. */ ! rewritten = QueryRewrite((Query *) stmt-query); if (list_length(rewritten) != 1 || !IsA(linitial(rewritten), Query)) elog(ERROR, unexpected rewrite result); query = (Query *) linitial(rewritten); --- 67,86 * query, so we are not expecting rule rewriting to do anything * strange. */ ! ! /* Create a new portal, and do all query planning on a copy of !* the query allocated in the new portal's memory context. The !* planner may modify the query, and it is not safe to have !* those modifications persist as we are ourselves running in a !* transient portal context. !*/ ! portal = CreatePortal(stmt-portalname, false, false); ! ! oldContext = MemoryContextSwitchTo(PortalGetHeapMemory(portal)); ! ! query = copyObject(stmt-query); ! ! rewritten = QueryRewrite(query); if (list_length(rewritten) != 1 || !IsA(linitial(rewritten), Query)) elog(ERROR, unexpected rewrite result); query = (Query *) linitial(rewritten); *** *** 86,102 plan = planner(query, true, stmt-options, NULL); - /* -* Create a portal and copy the query and plan into its memory -* context. -*/ - portal = CreatePortal(stmt-portalname, false, false); - - oldContext = MemoryContextSwitchTo(PortalGetHeapMemory(portal)); - - query = copyObject(query); - plan = copyObject(plan); - PortalDefineQuery(portal, NULL, /* unfortunately don't have sourceText */ SELECT, /* cursor's query is always a SELECT */ --- 99,104 ---(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
[HACKERS] valgrind complaints in pgstat_write_statsfile
Seen in passing when running valgrind against a CVS HEAD build: ==28598== Syscall param write(buf) contains uninitialised or unaddressable byte(s) ==28598==at 0x1BABC558: write (in /lib/libc-2.3.4.so) ==28598==by 0x1BA7165D: (within /lib/libc-2.3.4.so) ==28598==by 0x1BA715FE: _IO_do_write (in /lib/libc-2.3.4.so) ==28598==by 0x1BA70E61: _IO_file_close_it (in /lib/libc-2.3.4.so) ==28598==by 0x1BA67B07: _IO_fclose (in /lib/libc-2.3.4.so) ==28598==by 0x819369B: pgstat_write_statsfile (pgstat.c:2275) ==28598==by 0x8192A44: PgstatCollectorMain (pgstat.c:1576) ==28598==by 0x8192368: PgstatBufferMain (pgstat.c:1398) ==28598==by 0x8191656: pgstat_start (pgstat.c:617) ==28598==by 0x818EB81: reaper (postmaster.c:2096) ==28598==by 0x52BFEFFF: ??? ==28598==by 0x818CDAA: PostmasterMain (postmaster.c:917) ==28598==by 0x81570E4: main (main.c:268) Anything to be concerned about? I guess that the uninitialized bytes were actually provided in an earlier write but it's only on close that they get written out and noticed. -O ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] 64Bit Solaris Compile
Rod Taylor wrote: Our local admin tried compiling a 64bit PostgreSQL on Solaris 9 using the below environment: export PATH=:/usr/bin/sparcv9:/usr/ccs/bin/sparcv9:/usr/sfw/bin/sparcv9:/usr/local/bin/sparcv9:/usr/bin:/usr/local/bin:/usr/sfw/bin:/usr/ccs/bin export LD_LIBRARY_PATH=/usr/lib/sparcv9:/usr/ccs/lib/sparcv9:/usr/local/lib/sparcv9:/usr/sfw/lib/sparcv9 export CFLAGS='-m64' I seem to remember building with CC='gcc -m64' without any pain (haven't tried it recently though). -O ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [JDBC] Strange server error with current 8.0beta driver
Barry Lind wrote: Environment #1: WinXP 8.0beta4 server, 8.0jdbc client 2004-11-19 12:19:06 ERROR: unrecognized node type: 25344832 Environment #2: Sun Solaris 7.4.3 server, 8.0jdbc client ERROR: no value found for parameter 1 From memory the 7.4.3 behaviour you see can happen if you DECLARE CURSOR with a parameterized query (executes OK) then try to FETCH from it (fails with the above error, as the original parameter values from DECLARE execution are not stored with the portal). The parameterization can happen either via V3 protocol Parse/Bind or via function execution, IIRC. I put together a patch to fix this that made it into 8.0 (with some changes IIRC), perhaps the bug lies in there somewhere. -O ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ambiguous column names in subqueries
Greg Stark wrote: What purpose is there to returning both columns to the outer query? The columns become effectively inaccessible. There's no syntax for disambiguating any reference. I think postgres should treat the second alias as hiding the first. Currently there's no way to selectively override a single output column. The only way to do is to put your query in a subquery and list every single output column again except the one you want to override. Note that I'm not saying Postgres should remove ambiguous columns from different tables for the inner query. Only for subsequent layers where they have no way to access them anyways. Please don't. JDBC (for example) has no problem with ambiguous columns, you just access them by index, and you have resultset metadata available if you want to implement your own rules for finding those indexes. It sounds like your problem really lies in the API you are using to access the results. -O ---(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] Increasing the length of pg_stat_activity.current_query...
Tom Lane wrote: It's really a performance issue: do you want to pay the penalty associated with reassembling messages that exceed the loopback MTU [...] BTW, the loopback MTU here is quite large: [EMAIL PROTECTED]:~$ /sbin/ifconfig lo | grep MTU UP LOOPBACK RUNNING MTU:16436 Metric:1 [EMAIL PROTECTED]:~$ uname -a Linux flood 2.6.8.1-flood #1 Wed Sep 29 21:58:09 NZST 2004 i686 GNU/Linux so at least on Linux 2.6 it seems like the risk of fragmentation is minimal. -O ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] CVS should die
Tom Lane wrote: AFAICS the only nondestructive way to do this is to cvs delete and cvs add, with a commit comment saying where the files were moved from. Then when you are looking at them in CVS, you'd have to navigate over to the previous location (by hand, probably; the commit comment isn't going to automate this for you) and look in the Attic to read the prior CVS history. It's not impossible, certainly, but it discourages moving files for less than the very best of reasons. You can also do a repository-side copy of the ,v file to the new location, remove old tags branches from that new copy, and 'cvs delete' the old copy. That preserves history but the file should still show up in the old location (and not also in the new location) when older versions are checked out. In theory. It's all very hairy.. (I'm rather interested to know whether any other SCMs have a better solution to this problem, and if so what it is. It's not obvious how to do better.) Subversion deals with this reasonably well. The main difference to CVS is that it does not try to track multiple lines of development in a particular file; instead, you make (internally cheap) copies *within* the repository tree when you branch or tag. Once you have that, it's much easier to track file copies and deletions, as each path in the repository effectively has a linear history. A rename is just a copy and delete. See http://svnbook.red-bean.com/svnbook-1.0/ch04s02.html for some more detail. -O ---(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] fsync, ext2 on Linux
Heikki Linnakangas wrote:
The Linux fsync man page says:
It does not necessarily ensure that the entry in the directory
containing the file has also reached disk. For that an explicit fsync on
the file descriptor of the directory is also needed.
AFAIK, we don't care about it at the moment. The actual behaviour
depends on the filesystem, reiserfs and other journaling filesystems
probably don't need the explicit fsync on the parent directory, but at
least ext2 does.
I've experimented with a user-mode-linux installation, crashing it at
specific points. It seems that on ext2, it's possible to get the
database in non-consistent state.
Have you experimented with mounting the filesystem with the dirsync
option ('-o dirsync') or marking the log directory as synchronous with
'chattr +D'? (no, it's not a real fix, just another data point..)
-O
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] V3 protocol gets out of sync on messages that cause allocation
Tom Lane wrote: I wrote: Yeah. The intent of the protocol design was that the recipient could skip over the correct number of bytes even if it didn't have room to buffer them, but the memory allocation mechanism in the backend makes it difficult to actually do that. Now that we have PG_TRY, though, it might not be out of reach to do it right. And indeed it wasn't. Patch committed. Thanks! Re your commit comment: I'm a bit dubious that this is a real problem, since the client likely doesn't have any more space available than the server, but it's not hard to make it behave according to the protocol intention. It's quite possible that the client isn't keeping the whole parameter in memory. For example, JDBC has a method that allows a streamable parameter (with prespecified length) to be set, and the stream contents could be coming from disk or computed on demand. That is actually where I came across the problem in the first place. -O ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] V3 protocol gets out of sync on messages that cause allocation failures
(Tom: this is not as severe a problem as I first thought)
If a client sends a V3 message that is sufficiently large to cause a
memory allocation failure on the backend when allocating space to read
the message, the backend gets out of sync with the protocol stream.
For example, sending this:
FE= Parse(stmt=null,query=SELECT $1,oids={17})
FE= Bind(stmt=null,portal=null,$1=stream of 10 bytes)
provokes this:
ERROR: out of memory
DETAIL: Failed on request of size 1073741823.
FATAL: invalid frontend message type 0
What appears to be happening is that the backend goes into error
recovery as soon as the allocation fails (just after reading the message
length), and never does the read() of the body of the Bind message. So
it falls out of sync, and tries to interpret the guts of the Bind as a
new message. Bad server, no biscuit.
I was concerned that this was exploitable in applications that pass
hostile binary parameters as protocol-level parameters, but it doesn't
seem possible as the bytes at the start of a Bind are not under the
control of the attacker and don't form a valid message.
The CopyData message could probably be exploited, but it seems unlikely
that (security-conscious) applications will pass hostile data directly
in a CopyData message.
I haven't looked at a fix to this in detail (I'm not really familiar
with the backend's error-recovery path), but it seems like one easy
option is to treate all errors that occur while a message is in the
process of being read as FATAL?
-O
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
