Re: [HACKERS] fixing PQsetvalue()
you are creating as you iterate through. This behavior was unnecessary in terms of what libpqtypes and friends needed and may (as Tom suggested) come back to bite us at some point. As it turns out, PQsetvalue's operation on results that weren't created via PQmakeEmptyResult was totally busted because of the bug, so we have a unique opportunity to tinker with libpq here: you could argue that you +1 Exactly at this moment I am thinking about using modifiable (via PQsetvalue) PGresult instead of std::map in my C++ library for store parameters for binding to executing command. I am already designed how to implement it, and I supposed that PQsetvalue is intended to work with any PGresult and not only with those which has been created via PQmakeEmptyResult... So, I am absolutely sure, that PQsetvalue should works with any PGresult. All PGresults are created via PQmakeEmptyPGresult, including libpqtypes. Actually, libpqtypes calls PQcopyResult which calls PQmakeEmptyPGresult. It might be better to say a server result vs. a client result. Currently, PQsetvalue is broken when provided a server generated result. -- Andrew Chernow eSilo, LLC global backup http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Libpq enhancement
On 6/19/2011 11:04 AM, Jeff Shanab wrote: I am wondering If I am missing something obvious. If not, I have a suggestion for plpgsql. Stored procedures can accept rows. Libpq can receive rows (PQResult). Wouldn’t it be a great interface if PQResult was “bi-directional”? Create a result set on the client then call the database with a command. Perhaps… PQinsert(PQResult,”schema.table”); //iterate thru rows inserting PQupdate(PQResult,”schema.table”); //iterate thru rows updateing PQexec(connection,”scheme.function”,PQResult) //iterate thru rows passing row as arg to stored procedure. Have you looked into libpqtypes? It allows you to pack nested structures/arrays and pass them as query/function parameters. http://pgfoundry.org/projects/libpqtypes/ http://libpqtypes.esilo.com/ (docs) -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Error in PQsetvalue
On 6/8/2011 12:03 PM, Tom Lane wrote: Merlin Moncuremmonc...@gmail.com writes: On Wed, Jun 8, 2011 at 10:18 AM, Tom Lanet...@sss.pgh.pa.us wrote: Merlin Moncuremmonc...@gmail.com writes: I went ahead and tested andrew's second patch -- can we get this reviewed and committed? Add it to the upcoming commitfest. It's a client crashing bug in PQsetvalue that goes back to 9.0 :(. I was under the impression that this was extending PQsetvalue to let it be used in previously unsupported ways, ie, to modify a server-returned Well, it was supposed to support that but the implementation is busted (sorry) and core dumps instead. PGresult. That's a feature addition, not a bug fix. I'm not even sure it's a feature addition I approve of. I think serious consideration ought to be given to locking down returned results so PQsetvalue refuses I don't disagree at all, but I do think this is a bit more involved of a change. Several functions like PQmakeEmptyPGresult, PQsetvalue, PQcopyResult (one used by libpqtypes), the PGresult object needs a bool member and probably others things all must be aware of the distinction bwteen client and server generated results. That is a little tricky because both use PQmakeEmptyPGresult that has no argument to indicate that. Since libpqtypes only calls PQcopyResult, you could just set a flag on the result in that function that PQsetvalue uses as a guard. However, this hard codes knowledge about the libpqtypes calling pattern which is rather weak. Maybe it would be better to just apply the patch (since it also removes duplicate code from pqAddTuple in addition to fixing a crash) and update the docs to say this is an unsupported feature, don't do it. If it happens to work forever or just for a while, than so be it. -- Andrew Chernow eSilo, LLC global backup http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Error in PQsetvalue
On 6/3/2011 3:03 PM, Pavel Golub wrote: Hello. Reproduced under Windows XP SP3 using Visual C++ 2008 and Delphi. If PQsetvalue is called with second parameter equals to PQntuples then memory corruption appears. But it should grow internal tuples array and populate the last item with provided data. Please see the code: At first glance (have not tested this theory), looks like pqAddTuple() doesn't zero the newly allocated tuples slots like PQsetvalue() does. PQsetvalue is depending on the unassigned tuple table slots to be NULL to detect when a tuple must be allocated. Around line 446 on fe-exec.c. I never tested this case since libpqtypes never tried to call PQsetvalue on a PGresult created by the standard libpq library. The solution I see would be to zero the new table slots within pqAddTuple. Any other ideas? -- Andrew Chernow eSilo, LLC global backup http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Error in PQsetvalue
On 6/3/2011 4:06 PM, Andrew Chernow wrote: On 6/3/2011 3:03 PM, Pavel Golub wrote: Hello. Reproduced under Windows XP SP3 using Visual C++ 2008 and Delphi. If PQsetvalue is called with second parameter equals to PQntuples then memory corruption appears. But it should grow internal tuples array and populate the last item with provided data. Please see the code: At first glance (have not tested this theory), looks like pqAddTuple() doesn't zero the newly allocated tuples slots like PQsetvalue() does. PQsetvalue is depending on the unassigned tuple table slots to be NULL to detect when a tuple must be allocated. Around line 446 on fe-exec.c. I never tested this case since libpqtypes never tried to call PQsetvalue on a PGresult created by the standard libpq library. The solution I see would be to zero the new table slots within pqAddTuple. Any other ideas? Eeekks. Found an additional bug. PQsetvalue only allocates the actual tuple if the provided tup_num equals the number of tuples (append) and that slot is NULL. This is wrong. The original idea behind PQsetvalue was you can add tuples in any order and overwrite existing ones. Also, doing res-ntups++ whenever a tuple is allocated is incorrect as well; since we may first add tuple 3. In that case, if ntups is currently = 3 we need to set it to 3+1, otherwise do nothing. In other words, while adding tuples via PQsetvalue the ntups should always be equal to (max_supplied_tup_num + 1) with all unset slots being NULL. PQsetvalue(res, 3, 0, x, 1); // currently sets res-ntups to 1 PQsetvalue(res, 2, 0, x, 1); // as code is now, this returns FALSE due to bounds checking -- Andrew Chernow eSilo, LLC global backup http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Error in PQsetvalue
At first glance (have not tested this theory), looks like pqAddTuple() doesn't zero the newly allocated tuples slots like PQsetvalue() does. PQsetvalue is depending on the unassigned tuple table slots to be NULL to detect when a tuple must be allocated. Around line 446 on fe-exec.c. I never tested this case since libpqtypes never tried to call PQsetvalue on a PGresult created by the standard libpq library. The solution I see would be to zero the new table slots within pqAddTuple. Any other ideas? It might not be necessary to do that. AIUI the tuple table slot guard is there essentially to let setval know if it needs to allocate tuple attributes, which always has to be done after a new tuple is created after a set. Trying to append a tuple to a libpq generated PGresult will core dump due to the pqAddTuple issue, unless the append operation forces PQsetvalue to grow the tuple table; in which case new elements are zero'd. OP attempted to append. res = PQexec(returns 2 tuples); PQsetvalue(res, PQntuples(res), ...); -- Andrew Chernow eSilo, LLC global backup http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Error in PQsetvalue
On 6/3/2011 5:54 PM, Merlin Moncure wrote: On Fri, Jun 3, 2011 at 3:38 PM, Andrew Chernowa...@esilo.com wrote: Eeekks. Found an additional bug. PQsetvalue only allocates the actual tuple if the provided tup_num equals the number of tuples (append) and that slot is NULL. This is wrong. The original idea behind PQsetvalue was you can add tuples in any order and overwrite existing ones. That was by design -- you are only supposed to be able to add a tuple if you pass in exactly the insertion position (which is the same as PQntuples()). If you pass less than that, you will overwrite the value at that position. If you pass greater, you should get an error. Actually, the original idea, as I stated UT, was to allow adding tuples in any order as well as overwriting them. Obviously lost that while trying to get libpqtypes working, which only appends. This is also how the function is documented. That's why you don't have to zero out the tuple slots at all -- the insertion position is always known and you just need to make sure the tuple atts are not allocated more than one time per inserted tuple. Meaning, PQsetvalue needs to work more like pqAddTuple (and the insertion code should probably be abstracted). You need to have insertion point zero'd in either case. Look at the code. It only allocates when appending *AND* the tuple at that index is NULL. If pqAddTuple allocated the table, the tuple slots are uninitialized memory, thus it is very unlikely that the next tuple slot is NULL. It is trivial to make this work the way it was initially intended (which mimics PQgetvalue in that you can fetch values in any order, that was the goal). Are there any preferences? I plan to supply a patch that allows setting values in any order as well as overwriting unless someone speaks up. I fix the docs as well. -- Andrew Chernow eSilo, LLC global backup http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Error in PQsetvalue
On 6/3/2011 7:14 PM, Merlin Moncure wrote: On Fri, Jun 3, 2011 at 6:22 PM, Andrew Chernowa...@esilo.com wrote: Actually, the original idea, as I stated UT, was to allow adding tuples in any order as well as overwriting them. Obviously lost that while trying to get libpqtypes working, which only appends. Well, that may or not be the case, but that's irrelevant. This has to be backpatched to 9.0 and we can't use a bug to sneak in a behavior change...regardless of what's done, it has to work as documented -- the current behavior isn't broken. If we want PQsetvalue to support creating tuples past the insertion point, that should be proposed for 9.2. Well, not really irrelevant since understanding the rationale behind changes is important. I actually reversed my opinion on this and was preparing a patch that simply did a memset in pqAddTuple. See below for why You need to have insertion point zero'd in either case. Look at the code. It only allocates when appending *AND* the tuple at that index is NULL. If pqAddTuple allocated the table, the tuple slots are uninitialized memory, thus it is very unlikely that the next tuple slot is NULL. I disagree -- I think the fix is a one-liner. line 446: if (tup_num == res-ntups !res-tuples[tup_num]) should just become if (tup_num == res-ntups) also the memset of the tuple slots when the slot array is expanded can be removed. (in addition, the array tuple array expansion should really be abstracted, but that isn't strictly necessary here). All true. This is a cleaner fix to something that was in fact broken ;) You want to apply it? The fact that the tuples were being zero'd plus the NULL check is evidence of the original intent; which is what confused me. I found internal libpqtypes notes related to this change, it actually had to do with producing a result with dead tuples that would cause PQgetvalue and others to crash. Thus, it seemed better to only allow creating a result that is always *valid*. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Error in PQsetvalue
I disagree -- I think the fix is a one-liner. line 446:
if (tup_num == res-ntups !res-tuples[tup_num])
should just become
if (tup_num == res-ntups)
also the memset of the tuple slots when the slot array is expanded can
be removed. (in addition, the array tuple array expansion should
really be abstracted, but that isn't strictly necessary here).
All true. This is a cleaner fix to something that was in fact broken ;) You want
Attached a patch that fixes the OP's issue. PQsetvalue now uses pqAddTuple to
grow the tuple table and has removed the remnants of an older idea that caused
the bug.
--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/
diff --git a/src/interfaces/libpq/fe-exec.c b/src/interfaces/libpq/fe-exec.c
index 83c5ea3..9f013ed 100644
--- a/src/interfaces/libpq/fe-exec.c
+++ b/src/interfaces/libpq/fe-exec.c
@@ -424,28 +424,8 @@ PQsetvalue(PGresult *res, int tup_num, int field_num, char *value, int len)
if (tup_num 0 || tup_num res-ntups)
return FALSE;
- /* need to grow the tuple table? */
- if (res-ntups = res-tupArrSize)
- {
- int n = res-tupArrSize ? res-tupArrSize * 2 : 128;
- PGresAttValue **tups;
-
- if (res-tuples)
- tups = (PGresAttValue **) realloc(res-tuples, n * sizeof(PGresAttValue *));
- else
- tups = (PGresAttValue **) malloc(n * sizeof(PGresAttValue *));
-
- if (!tups)
- return FALSE;
-
- memset(tups + res-tupArrSize, 0,
- (n - res-tupArrSize) * sizeof(PGresAttValue *));
- res-tuples = tups;
- res-tupArrSize = n;
- }
-
- /* need to allocate a new tuple? */
- if (tup_num == res-ntups !res-tuples[tup_num])
+ /* need to allocate a new tuple. */
+ if (tup_num == res-ntups)
{
PGresAttValue *tup;
int i;
@@ -457,6 +437,12 @@ PQsetvalue(PGresult *res, int tup_num, int field_num, char *value, int len)
if (!tup)
return FALSE;
+ if (!pqAddTuple(res, tup))
+ {
+ free(tup);
+ return FALSE;
+ }
+
/* initialize each column to NULL */
for (i = 0; i res-numAttributes; i++)
{
@@ -464,11 +450,12 @@ PQsetvalue(PGresult *res, int tup_num, int field_num, char *value, int len)
tup[i].value = res-null_field;
}
- res-tuples[tup_num] = tup;
- res-ntups++;
+ attval = tup[tup_num][field_num];
+ }
+ else
+ {
+ attval = res-tuples[tup_num][field_num];
}
-
- attval = res-tuples[tup_num][field_num];
/* treat either NULL_LEN or NULL value pointer as a NULL field */
if (len == NULL_LEN || value == NULL)
--
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] Error in PQsetvalue
On 6/3/2011 10:26 PM, Andrew Chernow wrote:
I disagree -- I think the fix is a one-liner. line 446:
if (tup_num == res-ntups !res-tuples[tup_num])
should just become
if (tup_num == res-ntups)
also the memset of the tuple slots when the slot array is expanded can
be removed. (in addition, the array tuple array expansion should
really be abstracted, but that isn't strictly necessary here).
All true. This is a cleaner fix to something that was in fact broken ;) You want
Attached a patch that fixes the OP's issue. PQsetvalue now uses pqAddTuple to
grow the tuple table and has removed the remnants of an older idea that caused
the bug.
Sorry, I attached the wrong patch. Here is the correct one.
--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/
diff --git a/src/interfaces/libpq/fe-exec.c b/src/interfaces/libpq/fe-exec.c
index 83c5ea3..b4a394f 100644
--- a/src/interfaces/libpq/fe-exec.c
+++ b/src/interfaces/libpq/fe-exec.c
@@ -424,28 +424,8 @@ PQsetvalue(PGresult *res, int tup_num, int field_num, char *value, int len)
if (tup_num 0 || tup_num res-ntups)
return FALSE;
- /* need to grow the tuple table? */
- if (res-ntups = res-tupArrSize)
- {
- int n = res-tupArrSize ? res-tupArrSize * 2 : 128;
- PGresAttValue **tups;
-
- if (res-tuples)
- tups = (PGresAttValue **) realloc(res-tuples, n * sizeof(PGresAttValue *));
- else
- tups = (PGresAttValue **) malloc(n * sizeof(PGresAttValue *));
-
- if (!tups)
- return FALSE;
-
- memset(tups + res-tupArrSize, 0,
- (n - res-tupArrSize) * sizeof(PGresAttValue *));
- res-tuples = tups;
- res-tupArrSize = n;
- }
-
- /* need to allocate a new tuple? */
- if (tup_num == res-ntups !res-tuples[tup_num])
+ /* need to allocate a new tuple. */
+ if (tup_num == res-ntups)
{
PGresAttValue *tup;
int i;
@@ -457,15 +437,18 @@ PQsetvalue(PGresult *res, int tup_num, int field_num, char *value, int len)
if (!tup)
return FALSE;
+ if (!pqAddTuple(res, tup))
+ {
+ free(tup);
+ return FALSE;
+ }
+
/* initialize each column to NULL */
for (i = 0; i res-numAttributes; i++)
{
tup[i].len = NULL_LEN;
tup[i].value = res-null_field;
}
-
- res-tuples[tup_num] = tup;
- res-ntups++;
}
attval = res-tuples[tup_num][field_num];
--
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] PQdeleteTuple function in libpq
On 6/2/2011 4:28 AM, Pavel Golub wrote: Hello, Andrew. You wrote: AC On 6/1/2011 11:43 AM, Pavel Golub wrote: Hello. I'm some kind of PQdeleteTuple function will be very usefull in libpq. Because right now after deleting some record I need refetch result set, or mark tuple as deleted and this is headache for me. AC IMHO, this should be handled by the application. You could track tuples AC removed in an int[] or copy the result set into an application defined AC array of C structures. I've always been under the impression that AC PGresult objects are immutable once delivered to the application. Andrew, why we have PQmakeEmptyPGresult, PQcopyResult, PQsetResultAttrs, PQsetvalue and PQresultAlloc in this case? Of course there's no big deal with their absence but let's be consistent. I'm not entirely sure what you are trying to do, but can't you use PQmakeEmptyPGresult, PQsetResultAttrs and PQsetvalue to construct a result that excludes the tuples you don't want followed by a PQclear(initial_result)? -- Andrew Chernow eSilo, LLC global backup http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PQdeleteTuple function in libpq
AC IMHO, this should be handled by the application. You could track tuples AC removed in an int[] or copy the result set into an application defined AC array of C structures. I've always been under the impression that AC PGresult objects are immutable once delivered to the application. Andrew, why we have PQmakeEmptyPGresult, PQcopyResult, PQsetResultAttrs, PQsetvalue and PQresultAlloc in this case? Of course there's no big deal with their absence but let's be consistent. I'm not entirely sure what you are trying to do, but can't you use PQmakeEmptyPGresult, PQsetResultAttrs and PQsetvalue to construct a result that excludes the tuples you don't want followed by a PQclear(initial_result)? Actually the best solution would be to call PQcopyResult with all PG_COPYRES_XXX flags enabled except PG_COPYRES_TUPLES. Now call PQsetvalue for each tuple you want to add. -- Andrew Chernow eSilo, LLC global backup http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PQdeleteTuple function in libpq
On 6/2/2011 11:02 AM, Alvaro Herrera wrote: Excerpts from Andrew Chernow's message of jue jun 02 10:12:40 -0400 2011: Andrew, why we have PQmakeEmptyPGresult, PQcopyResult, PQsetResultAttrs, PQsetvalue and PQresultAlloc in this case? Of course there's no big deal with their absence but let's be consistent. I'm not entirely sure what you are trying to do, but can't you use PQmakeEmptyPGresult, PQsetResultAttrs and PQsetvalue to construct a result that excludes the tuples you don't want followed by a PQclear(initial_result)? Seems pretty wasteful if you want to delete a single tuple from a large result. I think if you desired to compact the result to free some memory after deleting a large fraction of the tuples in the result it could be useful to do that, otherwise just live with the unused holes in the storage area as suggested by Pavel. Another solution is to manually cursor through the set (like grab 1000 tuples at a time) and copy the set to your own structure. That way, the temporary double memory to perform the copy is not as big of a hit. By using your own structure, you can organize the memory in a fashion that is optimized for your requirement. -- Andrew Chernow eSilo, LLC global backup http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PQdeleteTuple function in libpq
On 6/1/2011 11:43 AM, Pavel Golub wrote: Hello. I'm some kind of PQdeleteTuple function will be very usefull in libpq. Because right now after deleting some record I need refetch result set, or mark tuple as deleted and this is headache for me. IMHO, this should be handled by the application. You could track tuples removed in an int[] or copy the result set into an application defined array of C structures. I've always been under the impression that PGresult objects are immutable once delivered to the application. -- Andrew Chernow eSilo, LLC global backup http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] windows consolidated cleanup
On 4/24/2011 1:29 AM, Andrew Dunstan wrote: The attached patch is intended to clean up a bunch of compiler warnings seen on Windows due to mismatches of signedness or constness, unused variables, redefined macros and a missing prototype. It doesn't clean up all the warnings by any means, but it fixes quite a few. One thing I'm a bit confused about is this type of warning: src\backend\utils\misc\guc-file.c(977): warning C4003: not enough actual parameters for macro 'GUC_yywrap' If someone can suggest a good fix That would be nice. The macro is defined as taking one argument. // guc-file.c line 354 #define GUC_yywrap(n) 1 The macro is overriding the prototype declared at line 627, which has a void argument list (assuming YY_SKIP_YYWRAP is !defined). Since all code references to this do not provide an argument, I'd say the macro is incorrect. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Itanium HP-UX build failure, register stack
I wish we could get some buildfarm coverage for HPUX. I've whined about this in the past, but nobody's ever made an offer to provide suitable platform(s) that I know of. cheers I've got two HP-UX 11 boxes, PA-RISC and IA64. From uname: HP-UX B.11.23 U 9000/785 HP-UX B.11.23 U ia64 They are build farm boxes (which already build postgres) so my guess is they wouldn't require much fiddling. I don't time to help with this, but am more than willing to give you all the access you need to get it going. I also have HP-UX 10.20 on a PA-RISC for the courageous. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Correctly producing array literals for prepared statements
Binary mode had serious limitations, such as portability. What are the other limitations? As far as portability is concerned, we are using it on many different operating systems and architectures without issue. Even our most recent bump to 9.0.1 and 9.0.3 was flawless in regard to libpq/libpqtypes. We do need some support in libpq for constructing and deconstructing arrays (and probably for composites too, although that will be harder, I suspect). [sigh...] -- Andrew Chernow eSilo, LLC global backup http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Correctly producing array literals for prepared statements
On 2/23/2011 3:06 PM, Peter Geoghegan wrote: On 23 February 2011 15:34, Merlin Moncuremmonc...@gmail.com wrote: You can send nested arrays safely. You just have to be very formal about escaping *everything* both as you get it and as it goes into the container. This is what postgres does on the backend as it sends arrays out the door in text. It might be instructive to see what the server does in terms of escaping. Note that the way this works it's not impossible to see 128+ consecutive backslashes when dealing with arrays of composites. Sounds tedious. It is tedious, which is one reason why libpqtypes went binary. There are some compelling performance reasons as well that affect both client and server. libpqtypes was originally developed to serve a very particular need and wasn't aiming to be general purpose. That came about along the way trying to solve the problem. Personally, PQexec is dead to me as well as text results from a C/C++ app. I see no advantage over libpqtypes in that context. Unless I am missing your ultimate goal, you'd probably get what you want by wrapping libpqtypes. -- Andrew Chernow eSilo, LLC global backup http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Correctly producing array literals for prepared statements
It's probably fine if you can control both ends. But there is no guarantee of portability, nor does it seem likely to me there ever will be, so I don't find your assertion terribly useful. The fact that it hasn't broken for you doesn't mean it can't or won't be. All true. If you change the protocol, libpqtypes needs to be adjusted. I think that is a very fair statement. It already toggles on server version around a few changes in the past ... like the money data type. So far, since 8.1, the number of changes to the binary protocol has put me to sleep :) The other downside I see is that binary protocols are often a lot harder to debug, but maybe that's just me. Also very true. However, libpqtypes addresses this by abstracting the end user from the binary transformation or preparation. Instead, users are presented with a printf/scanf style interface. PQexecf(conn, select %int4 + %int4, 4, 4) is pretty far removed from the underlying byte swapping, parallel array setup for PQexecParams and other nastiness. But yes, the maintainer of the library must deal with protocol changes and provide backward compatibility. -- Andrew Chernow eSilo, LLC global backup http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Visual Studio 2010/Windows SDK 7.1 support
On 1/15/2011 1:42 PM, Magnus Hagander wrote: On Mon, Jan 3, 2011 at 16:45, Brar Pieningb...@gmx.de wrote: On Mon, 3 Jan 2011 10:44:19 +0100, Magnus Hagandermag...@hagander.net wrote: Yeah, it looks that way - it's missing the ordering of the contrib I'll run it once for that now, and then please rebase your patch on top of that - makes it easier to review it. The rebased patch can be grabbed from http://www.piening.info/VS2010v2.patch Hi! Please make sure this goes on the commitfest page (https://commitfest.postgresql.org/action/commitfest_view?id=9), so it's not missed. I'm currently lacking an env where I can put VS2010 on it (given that the amazon cloud no longer works reasonably for windows machines, and I can't put another version of VS on the other VM I'm using right now), so it'll be a while before I can look at this. I can provide a windows box with VS2010 if you'd like. Wouldn't be until Monday or Tuesday. Any preference on windows version? Maybe Windows 7? You want 64-bit? Send a private email. -- Andrew Chernow eSilo, LLC global backup http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Suggesting a libpq addition
to consider libpqtypes for contrib (which we don't have time for atm). ... or as a libpq sibling :) -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] queriing the version of libpq
And it *could* be used in exactly the case you're outlining as long as you load the function dynamically. Detecting the presence of a function does not require a version number. If the symbol is in the library, use it. The only reason the version number would come into play is if you were attempting to detect behavior differences. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Suggesting a libpq addition
That would be a *HUGE* piece of software compared the relatively small thing I am suggesting... Sometimes complex and large solutions are required for the simplest of ideas. I believe this is one of those cases. You can't solve the printf style PQexec properly by merely implementing a sprintf wrapper. As for escaping (or not escaping) of string arguments, that can be seen as a bug or a feature. I do not wan't automatic escaping of string arguments in all cases, e.g. I might to construct an SQL statement with dynamic parts WHERE xy or AND a = b. hypothetical example: filter = WHERE name like 'Balmer%'; if (sort == SORT_DESC) sort = ORDER BY name DESCENDING; PQvexec(conn, SELECT name, nr, id FROM address %s%s, filter, sort); So what I am aiming at right now is a PQvexec() function that basically has printf() like semantics, but adds an additional token to the format string (printf uses %s and %b to produce strings.) I am thinking of adding %S and %B, which produce strings that are escaped. This suffers from becoming cryptic over time, see Tom Lane's comments back in 2007 on this (http://archives.postgresql.org/pgsql-hackers/2007-12/msg00362.php). libpqtypes uses the human readable %schema.typename (schema is optional) to specify format specifiers. There is no learning curve or ambiguity, if you want a point than use %point, or %my_type libpqtypes allows you to register aliases (PQregisterSubClasses) so that you can map %text to %s to make it feel more like C.. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Suggesting a libpq addition
On 12/6/2010 11:40 AM, Dmitriy Igrishin wrote: IMO, it would be better to implement some utility functions to make it easy to construct arrays dynamically for PQexecParams and PQexecPrepared. This seems to me more universal solution and it is useful for both -- high level libpq-libraries authors and for those who like to use libpq directly. Hmm, your idea isn't better, it is identical to what libpqtypes already does :) http://libpqtypes.esilo.com/browse_source.html?file=exec.c We wrap PQexecParams and friends. You are coding libpq. We extended much effort to provide the same result interface (PGresult), including handling composites and arrays. You getf composites and arrays as PGresults; where a composite is a single tuple multiple field result, an array is a multiple tuple single field result and composite arrays are multiple tuples and multiple fields. We've just made a more formal set of utility functions, typically called an API, in an attempt to match the coding standards of the postgresql project. There is no libpq param interface like results, so we added PGparam stuff. This allows you to pack parameters (PQputf) and than execute it. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Suggesting a libpq addition
On 12/5/2010 4:22 AM, Marc Balmer wrote: I am suggesting adding a function to libpq: PGresult *PQvexec(PGconn *conn, const char *fmt, ...); It behaves similar to PQexec, but it allows for printf style varargs and does connection re-establishing if the connection fails (it can be discussed if this already to much magic, maybe remove this part). It has been carefully designed to handle memory the right way. We use this since a long time. What do you think? I think it is a wonderful idea. Check out libpqtypes. It has a PQexecf, PQexecvf, PQsendf and PQsendvf. But that is just the beginning http://libpqtypes.esilo.com http://pgfoundry.org/projects/libpqtypes/ -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Suggesting a libpq addition
A varargs version of PQexecParams() would be handy, though. Imagine being able to do: PQexecVParams(SELECT * FROM mytable WHERE foo = $1 AND bar = $2, foovar, barvar); instead of constructing an array for the variables. http://libpqtypes.esilo.com/man3/PQexecf.html -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] returning multiple result sets from a stored procedure
On 9/5/2010 2:05 PM, Heikki Linnakangas wrote: On 04/09/10 17:16, Merlin Moncure wrote: Curious: is mulitset handling as you see it supported by the current v3 protocol? The manual says: The response to a SELECT query (or other queries that return row sets, such as EXPLAIN or SHOW) normally consists of RowDescription, zero or more DataRow messages, and then CommandComplete. COPY to or from the frontend invokes special protocol as described in Section 46.2.5. All other query types normally produce only a CommandComplete message. Since a query string could contain several queries (separated by semicolons), there might be several such response sequences before the backend finishes processing the query string. ReadyForQuery is issued when the entire string has been processed and the backend is ready to accept a new query string. If a multiple return sets from a procedure are returned just like multiple return sets from multiple queries, that's already covered by the protocol. Just as a side note, libpqtypes can emulate this using composite arrays; a feature we abuse internally. It is actually the primary justification we had for developing that portion of libpqtypes; initially we stayed clear of arrays and composites. create table fork_t (fork_id, rev_id, size, block_ids int8[], ...) create table rev_t (rev_id, blah, blah, fork_t[]); /* this is my favorite part of libpqtypes */ PGarray arr; PQgetf(result, tup_num, %rev_t[], field_num, arr); Now loop the array arr and getf(arr.res) for each rev_t, which allows you to getf each fork_t in the fork_t[], etc I *know* it is not pure multiset'n, but it sure gets the job done (in a completely different way, I know). However, I'm sure those reading this list can see the possiblities ;) Andrew Chernow eSilo, LLC. -- 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] Keepalives win32
It seems faily simple to yank these values out, no? Even easier if you use the all-in-wonder shell function SHGetValue(). We don't want to use that function, because it brings in a bunch of extra dependencies. This makes libpq.dll more heavyweight and more importantly, decreases the number of parallell connections we can deal with on the server side (on win32 at least, not sure about win64). Oh, didn't know that. Are the standard reg functions, open/query/close really that bad? Can't be any worse than the security api or MAPI hell ;) -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Keepalives win32
The way I see it, we have two options: 1) Read the default value from the registry. That's some fairly ugly code, imho. It seems faily simple to yank these values out, no? Even easier if you use the all-in-wonder shell function SHGetValue(). HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Tcpip\Parameters Values: KeepAliveTime, KeepAliveInterval Type: DWORD The only annoying thing is that the values may not exist. Well, it is also rather annoying there is no way to set the counter. The API used is documented at: http://msdn.microsoft.com/en-us/library/dd877220(v=VS.85).aspx Patch as it looks now (libpq only, and with obvious problems with this issue): http://github.com/mhagander/postgres/compare/master...win32keepalive and here :) http://archives.postgresql.org/pgsql-hackers/2009-05/msg01099.php -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq, PQexecPrepared, data size sent to FE vs. FETCH_COUNT
On 05/25/2010 07:35 AM, Alex Goncharov wrote: ,--- I/Alex (Mon, 24 May 2010 12:25:18 -0400) * | No equivalent of FETCH_COUNT is available at the libpq level, so I | assume that the interface I am using is smart enough not to send | gigabytes of data to FE. | | Where does the result set (GBs of data) reside after I call | PQexecPrepared? On BE, I hope? Sorry for asking again... No sarcasm meant: is there no straightforward answer here? Or nobody is certain? Or a wrong list? Issue multiple queries and make use of LIMIT/OFFSET. You'll have to go manual on this one. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] OpenVMS?
Marc G. Fournier wrote: On Tue, 16 Feb 2010, Bruce Momjian wrote: I hate to pour cold water on this, but why is it worth adding support for a platform that has such marginal usage. Because someone feels like dedicating their resources to it ... ? That's step one. Step two is community approval. Otherwise, anyone with an idea would be a committer. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TCP keepalive support for libpq
ISTM that the default behavior should be keep alives disabled, as it is now, and those wanting it can just set it in their apps: setsockopt(PQsocket(conn), SOL_SOCKET, SO_KEEPALIVE, ...) I disagree. I have clients who have problems with leftover client connections due to server host failures. They do not write apps in C. For a non-default change to be effective we would need to have all the client drivers, eg JDBC, psycopg, DBD-DBI, and the apps like psql make changes to turn it on. Adding this option as a non-default will not really help. Yes, that's definitely the use-case. PQsocket() will work fine for C apps only. But it should work fine as an option, no? As long as you can specify it on the connection string - I don't think there are any interfaces that won't take a connection string? Perl and python appear to have the same abilities as C. I don't use either of these drivers but I *think* the below would work: DBD:DBI setsockopt($dbh-pg_socket(), ...); psycopg conn.cursor().socket().setsockopt(...); Although, I think Dave's comments have made me change my mind about this patch. Looks like it serves a good purpose. That said, there is no guarentee the driver will implement the new feature ... JDBC seems to lack the ability to get the backing Socket object but java can set socket options. Maybe a JDBC kong fu master knows how to do this. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TCP keepalive support for libpq
Robert Haas wrote: On Thu, Feb 11, 2010 at 2:15 AM, Tollef Fog Heen tollef.fog.h...@collabora.co.uk wrote: ]] daveg | I disagree. I have clients who have problems with leftover client connections | due to server host failures. They do not write apps in C. For a non-default | change to be effective we would need to have all the client drivers, eg JDBC, | psycopg, DBD-DBI, and the apps like psql make changes to turn it on. Adding | this option as a non-default will not really help. FWIW, this is my case. My application uses psycopg, which provides no way to get access to the underlying socket. Sure, I could hack my way around this, but from the application writer's point of view, I have a connection that I expect to stay around and be reliable. Whether that connection is over a UNIX socket, a TCP socket or something else is something I would rather not have to worry about; it feels very much like an abstraction violation. I've sometimes wondered why keepalives aren't the default for all TCP connections. They seem like they're usually a Good Thing (TM), but I wonder if we can think of any situations where someone might not want them? The only case I can think of are systems that send application layer keepalive-like packets; I've worked on systems like this. The goal wasn't to reinvent keepalives but to check-in every minute or two to meet a different set of requirements, thus TCP keepalives weren't needed. However, I don't think they would of caused any harm. The more I think about this the more I think it's a pretty non-invasive change to enable keepalives in libpq. I don't think this has any negative impact on clients written while the default was disabled. This is really a driver setting. There is no way to ensure libpq, DBI, psycopg, JDBC, etc... all enable or disable keepalives by default. I only bring this up because it appears there are complaints from non-libpq clients. This patch wouldn't fix those cases. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TCP keepalive support for libpq
Tollef Fog Heen wrote: (please Cc me on replies, I am not subscribed) Hi, libpq currently does not use TCP keepalives. This is a problem in our case where we have some clients waiting for notifies and then the connection is dropped on the server side. The client never gets the FIN and thinks the connection is up. The attached patch unconditionally adds keepalives. I chose unconditionally as this is what the server does. We didn't need the ability to tune the timeouts, but that could be added with reasonable ease. ISTM that the default behavior should be keep alives disabled, as it is now, and those wanting it can just set it in their apps: setsockopt(PQsocket(conn), SOL_SOCKET, SO_KEEPALIVE, ...) If you really want libpq to manage this, I think you need to expose the probe interval and timeouts. There should be some platform checks as well. Check out... http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg128603.html -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify - what to do when the queue is full
Jeff Davis wrote: On Tue, 2010-02-09 at 16:51 -0500, Tom Lane wrote: Limiting NOTIFY payloads to 7-bit would definitely avoid the issue. The question is if that's more of a pain than a benefit. I don't see any alternative. If one backend sends a NOTIFY payload that Wouldn't binary payloads be an alternative? NOTE: I may have missed this discussion. Sorry if it has already been covered. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.5 vs. 9.0, Postgres vs. PostgreSQL
Tom Lane wrote: David E. Wheeler da...@kineticode.com writes: On Jan 22, 2010, at 4:54 PM, Mark Mielke wrote: MS SQL, MySQL, SQLite - do they have advocacy problems due to the SQL in their name? I think it is the opposite. SQL in the name almost grants legitimacy to them as products. Dropping the SQL has the potential to increase confusion. What is a Postgres? :-) Something that comes after black, but before white. Yeah. As best I can tell, most newbies think that PostgreSQL means Postgre-SQL --- they're not too sure what Postgre is, but they guess it must be the specific name of the product. And that annoys those of us who would rather they pronounced it Postgres. But in terms of recognizability of the product it's not a liability. The business about pronunciation is a red herring. It's just as unclear whether MySQL is My personal experience has shown that people not familiar with the project can't remember it's name (even 10 minutes after I said it). It doesn't really roll off your tongue, unless you count tree nodes in your sleep. This may have an affect on the project's reach. I am not really advocating a name change, but if a different name makes postgresql more popular, however silly that may seem, then I am all for it. This is a difficult marketing decision. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.5 vs. 9.0
9.0. You don't have a code-name. All the cool kids have code-names for their projects. Black Dog yup, I'm a zeppelin fan :) -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed tables
Peter Eisentraut wrote: On mån, 2010-01-11 at 19:27 -0500, Andrew Chernow wrote: Peter Eisentraut wrote: On mån, 2010-01-11 at 15:02 -0500, Andrew Chernow wrote: ISTM that the ultimate would be a 'create table (_) without storage' (or some'm) and make 'create type' an alternate syntax for SQL conformance. I don't really understand the purpose of that. What is the point of CREATE TYPE name AS () syntax? Why would one use create type when there is create table? Does it provide additional functionality I am unaware of or does it exist for comformance reasons? Well, that is a very deep question. ;-) I suppose a concise answer would be that types are for passing data around between functions, and tables are for storing data on disk. In practice, tables can be used for passing data around or storing it on disk. So, I guess my question remains unanswered as to what the composite type offers that a table doesn't; other than a name that better suits the task. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed tables
What is the point of this discussion? We're not going to remove the facility for composite types, regardless of whether or not some people regard them as unnecessary. And a name that better suits the task is not to be sneered at anyway. I never asked for anything to be removed nor do I sneer :) Honestly, I was only trying to understand why it existed. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify - what to do when the queue is full
Arnaud Betremieux wrote: A use case : use NOTIFY in a rule to send the primary key of a row that has been updated (for instance to manage a cache). This requires a patch on top of this one, and it really is a separate concern, but I thought I'd give the use case anyway, since I believe it is relevant to the issues here. I can see four kinds of NOTIFY statements : 1) The existing case: NOTIFY channel 2) With Joachim's patch : NOTIFY channel 'payload' 3) My use case : NOTIFY channel 'pay'||'load' (actually NOTIFY channel 'table_name#'||OLD.id) 4) Taken one step further : NOTIFY channel (SELECT payload FROM payloads WHERE ...) I know I'd be looking to send utf8 and byteas. Can notify as it stands today take an expression for the payload (#4)? The other issue is that libpq expects a string, so if non-c-string safe data is to be sent a protocol change is needed or the server must hex encode all payloads before transit and libpq must decode it; also requiring an 'payload_len' member be added to PGnotify. The latter is better IMHO as protocol changes are nasty. Although, only needed to support bytea. If all we want is utf8, then there is no issue with libpq. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed tables
Peter Eisentraut wrote: On tor, 2009-11-05 at 19:24 +0200, Peter Eisentraut wrote: I'm planning to work on typed tables support. The idea is that you create a table out of a composite type (as opposed to the other way around, which is currently done automatically). CREATE TYPE persons_type AS (name text, bdate date); CREATE TABLE persons OF persons_type; Or the fancy version: CREATE TABLE persons OF persons_type ( PRIMARY KEY (name) ); And here is the first patch for that. The feature is complete as far as I had wanted it. I would like to add ALTER TYPE support, but that can come as a separate patch. +1 ISTM that the ultimate would be a 'create table (_) without storage' (or some'm) and make 'create type' an alternate syntax for SQL conformance. For various reasons, we've internally adopted using create table for all composites and use a c-like naming convenstion of appending _t to such beasts. I'll just throw a little meat into the pack wolvesconstraints? -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed tables
Peter Eisentraut wrote: On mån, 2010-01-11 at 15:02 -0500, Andrew Chernow wrote: ISTM that the ultimate would be a 'create table (_) without storage' (or some'm) and make 'create type' an alternate syntax for SQL conformance. I don't really understand the purpose of that. What is the point of CREATE TYPE name AS () syntax? Why would one use create type when there is create table? Does it provide additional functionality I am unaware of or does it exist for comformance reasons? -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify - what to do when the queue is full
conversion problems in some circumstances - what about bytea (or why _do_ we have to limit this to something?). I agree with bytea. Zero conversions and the most flexible. Payload encoding/format should be decided by the user. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify - what to do when the queue is full
That is, if the queue overflows what you should do is drop the payloads and condense all the messages for a given class into a single notification for that class with unknown payload. That way if a cache which wants to invalidate specific objects gets a queue overflow condition then at least it knows it should rescan the original data and rebuild the cache and not just serve invalid data. That's far more complicated than throwing an error and it discards user payload information. Let the error indicate a rescan is needed. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify - what to do when the queue is full
We should probably also log the fact that we ran out of room, so that the DBA knows that they ahve a design issue. Can't they just bump allowed memory and avoid a redesign? Alternately, it would be great to have a configuration option which would allow the DBA to choose any of 3 behaviors via GUC: drop-oldest (as above) drop-largest (if we run out of room, drop the largest payloads first to save space) error (if we run out of room, error and rollback) I mentioned this up thread. I completely agree that overflow behavior should be tunable. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify - what to do when the queue is full
Tom Lane wrote: Andrew Chernow a...@esilo.com writes: I mentioned this up thread. I completely agree that overflow behavior should be tunable. There is only one correct overflow behavior. I count three. 1. wait 2. error 3. skip #1 and #2 are very similar to a file system. If FS buffers are full on write, it makes you wait. In non-blocking mode, it throws an EAGAIN error. IMHO those two behaviors are totally acceptable for handling notify overflow. #3 is pretty weak but I *think* there are uses for it. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify - what to do when the queue is full
Tom Lane wrote: Andrew Chernow a...@esilo.com writes: Tom Lane wrote: There is only one correct overflow behavior. I count three. Waiting till you can insert is reasonable (especially if we have some behavior that nudges other backends to empty the queue). If by skip you mean losing the notify but still committing, that's incorrect. There is no room for debate about that. Yeah like I said, skip felt weak. In regards to waiting, what would happen if other backends couldn't help empty the queue because they to are clogged? ISTM that any attempt to flush to other non-disk queues is doomed to possible overflows as well. Then what? Personally, I would just wait until room became available or the transaction was canceled. We could get fancy and tack a timeout value onto the wait. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify - what to do when the queue is full
Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 We still need to decide what to do with queue full situations in the proposed listen/notify implementation. I have a new version of the patch to allow for a variable payload size. However, the whole notification must fit into one page so the payload needs to be less than 8K. That sounds fine to me, FWIW. Agreed. Thank you for all your work. 1) drop new notifications if the queue is full (silently or with rollback) I like this one best, but not with silence of course. While it's not the most polite thing to do, this is for a super extreme edge case. I'd rather just throw an exception if the queue is full rather than start messing with the +1 -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
Andrew Dunstan wrote: At Tom's suggestion I am looking at allowing use of parameter names in SQL functions instead of requiring use of $1 etc. That raises the question of how we would disambiguate a parameter name from a column name. Essentially, ISTM, we could use some special marker such as @ (c.f. SQL Server) or : (c.f. ecpg) or else we could have some rule that says which name takes precedence. I think I prefer a special marker, other things being equal. Is there a standard on this? I like the special marker idea. A '$' would be nice because its already in use for similar purposes, but I think that would lead to ambiguity with dollar quoting. Would this be limited to sql functions? I only ask because for non-sql functions we currently prefix parameter names with an underscore, but a built-in special marker would be much more desirable. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
I like the special marker idea. A '$' would be nice because its already in use for similar purposes, but I think that would lead to ambiguity with dollar quoting. no, it should be safe (if you don't use for dollar quoting some like $variablename$) Actually, I was thinking of something like $abc$def, where abc and def are variables. Although, this is much less likely than column name conflicts. Other possibles are: $(var), @var@, or %var%. I'd perfer a single character marker but that may not fly. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
The point is that $ is a perfectly valid SQL identifier character and $foo is a perfectly valid identifier. You can always quote any identifier (yes, after case smashing) so you would expect if $foo is a valid identifier then $foo would refer to the same identifier. This case already exists via $1 and $1. Making '$' a marker for parameters wouldn't introduce it. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Sun, Nov 15, 2009 at 8:22 PM, Andrew Dunstan and...@dunslane.net wrote: Well, if the funcname.varname gadget will work, as you suggest elsewhere it could, I think that would suffice. I had assumed that was just something in the plpgsql engine. That gadget isn't horribly convenient for me since my function names tend to be 30 or 40 characters long. I wish we had something shorter, and maybe constant. But I guess that's a topic for a separate (inevitably rejected) patch. You're only going to need that if you insist on choosing parameter names that conflict with columns of the tables the function manipulates. Even then, attaching column aliases to the tables could be used instead. I don't see that this is any different from or worse than the extra typing you'll incur if you insist on using 40-character table names. (But having said that, an alternate qualification name is something that could be implemented if there were any agreement on what to use.) Would something like ARG.name be acceptable? -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
Tom Lane wrote: Andrew Chernow a...@esilo.com writes: Tom Lane wrote: (But having said that, an alternate qualification name is something that could be implemented if there were any agreement on what to use.) Would something like ARG.name be acceptable? It all depends on how likely you think it is that the function would use a table name or alias matching ARG (or any other proposal). It's certainly true that the function name itself is not immune from conflicts of that sort ... in fact I think we saw a bug report recently from someone who had intentionally chosen a plpgsql function name equal to a table name used in the function :-(. So I'm not wedded to the function name entirely. But it has precedent in plpgsql, and that precedent came from Oracle, so I don't think we should lightly make SQL functions do something different. If the concern is portability, (ANYTHING).name won't work. You would have to stick with function.name or support both styles. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify rewrite
spill to disk and need an efficient storage mechanism. The natural implementation of this in Postgres would be a table, not the slru. If This is what I think the people's real problem is, the implementation becomes a more complex when including payloads (larger ones even more so). I think its a side-track to discuss queue vs condition variables. Whether a notify is 20 bytes through the network or 8192 bytes doesn't change its design or purpose, only its size. Calling this a creeping feature is quite a leap. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify rewrite
Calling this a creeping feature is quite a leap. It's true that the real creep is having the payload at all rather than not having it. Not having the payload at all is like santa showing up without his bag of toys. Instead, you have to drive/fly to the north pole where he just came from to get them. One person described stuffing the payload with the primary key of the record being invalidated. This means the requirements have just gone from holding at most some small fixed number of records bounded by the number of tables or other shared data structures to holding a large number of records bounded only by the number of records in their tables which is usually much much larger. Now you're talking about making the payloads variable size, which means you need to do free space management within shared pages to keep track of how much space is free and available for reuse. So we've gone from a simple hash table of fixed size entries containing an oid or name datum where we expect the hash table to fit in memory and a simple lru can handle old pages that aren't part of the working set to something that's going to look a lot like a database table -- it has to handle reusing space in collections of variable size data and scale up to millions of entries. And I note someone else in the thread was suggesting it needed ACID properties which makes space reuse even more complex and will need something like vacuum to implement it. I think the original OP was close. The structure can still be fixed length but maybe we can bump it to 8k (BLCKSZ)? -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify rewrite
Tom Lane wrote: Greg Sabino Mullane g...@turnstep.com writes: Talk of efficiency also seems silly here - using shared memory is already way more efficient than our current listen/notify system. Except that the proposed implementation spills to disk. Particularly if it has to have support for large payloads, it could very well end up being a lot SLOWER than what we have now. True, but do you really consider it to be a common case that the notify system gets soo bogged down that it starts to crawl? The problem would be the collective size of notify structures + payloads and whether that would fit in memory or not. This leads me to believe that the only safety in smaller payloads is *possibly* a smaller chance of bogging it down, but that all depends on the usage pattern of smaller vs. larger payloads which is system specific. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify rewrite
My original intention was to have the queue as a circular buffer where the size of the entries was variable, but possibly bounded. Certainly using fixed length records of large size seems somewhat wasteful. Maybe we should do away with 'spill to disk' all together and either hard-code an overflow behavior or make it a knob. Possible overflow behaviors could be block until space is available, throw an error or silently drop it. Can the size of the shared memory segment for notifications be configurable? That would allow those with large payloads or a huge number of notifications to bump memory to avoid overflow cases. By removing the disk and making shmem usage configurable, I think the notify system would be flexible and could scale nicely. Another added benefit is the payload limit can be much higher than previously considered, because memory/performance concerns are now in the hands of the DBA. Incidentally, I'd like to thank Joachim personally for having done this work, +1 -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify rewrite
2. The payload parameter is optional. A notifying client can either call NOTIFY foo; or NOTIFY foo 'payload';. The length of the payload is currently limited to 128 characters... Not sure if we should allow longer payload strings... Might be a good idea to make the max the same as the max length for prepared transaction GUIDs? Not sure anyone would be shipping those around, but it's a pre-existing limit of about the same size. Yes, sounds reasonable to have the same limit for user-defined identifiers... [..begging..] Can this be increased significantly? I don't get it, is there any technical reason to make the limit soo small? This drastically reduces the usefulness of the payload. I've wanted this feature for quite sometime and it is quite disappointing that I could not even use it because it is unjustifiably limited. One use case I need is making the payload an absolute path, which saves us a round trip (commonly internet latency) and a query in a section of the system that's extremely performance sensitive. That sure ain't going to fit in 128 bytes. I'm sure I'm not the only one who finds this limit too small. I can almost guarentee complaints would come in if released that way. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify rewrite
What advantage is there in limiting it to a tiny size? This is a 'payload' after all...an arbitrary data block. Looking at the patch I noticed the payload structure (AsyncQueueEntry) is fixed length and designed to lay into QUEUE_PAGESIZE (set to) BLCKSZ sized pages. H. Looks like the limitation comes from slru. The true payload limit is (8K - struct members) the way this is implemented. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify rewrite
Now you might say that yeah, that's the point, we're trying to enable using NOTIFY in a different style. The problem is that if you are trying to use NOTIFY as a queue, you will soon realize that it has the wrong semantics for that --- in particular, losing notifies across a system crash or client crash is OK for a condition notification, not so OK for a message queue. The difference is that the former style assumes that the authoritative data is in a table somewhere, so you can still find out what you need to know after reconnecting. If you are doing messaging you are likely to think that you don't need any backing store for the system state. I simply don't agree that the semantics have to change. You call it a queue, I call it sesison data. There is no reason why the documentation can't state that notifies may not be delivered due to crashes, so make sure to use persistent storage for any payload worth keeping post-session. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify rewrite
However I share Greg's concerns that people are trying to use NOTIFY as a message queue which it is not designed to be. When you have an established libpq connection waiting for notifies it is not unreasonable to expect/desire a payload. ISTM, the problem is that the initial design was half-baked. NOTIFY is event-driven, ie. no polling! -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify rewrite
and we should stop. The world contains infinite amounts of lameness, but that's the world's problem, not ours. There is zero evidence that +1 this feature is only useful for stupid purposes, and some evidence (namely, the opinions of esteemed community members) that it is useful for at least some non-stupid purposes. The unexpected application of a feature can be creative or innovative, which I firmly believe is something this community embraces. How many ways can a screw driver be used ... think MacGyver :) Deteriming whether it's creative vs. stupid would require an understanding of the context in which it was applied. For example, using our screw driver to remove a splinter would be rather stupid, IMHO ;) -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify rewrite
2. adds the possibility to specify a payload parameter, i.e. executing in SQL NOTIFY foo 'payload'; and 'payload' will be delivered to any listening backend. Thank you for implementing this- LISTEN/NOTIFY without a payload has been a major problem to work around for me. +1 -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify rewrite
/* + * This function is executed for every notification found in the queue in order + * to check if the current backend is listening on that channel. Not sure if we + * should further optimize this, for example convert to a sorted array and + * allow binary search on it... + */ + static bool + IsListeningOn(const char *channel) I think a bsearch would be needed. Very busy servers that make heavy use of notifies would be quite a penalty. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify rewrite
Joachim Wieland wrote: On Thu, Nov 12, 2009 at 1:04 AM, Andrew Chernow a...@esilo.com wrote: I think a bsearch would be needed. Very busy servers that make heavy use of notifies would be quite a penalty. In such an environment, how many relations/channels is a backend typically listening to? Do you have average / maximal numbers? We have a system where many libpq clients, ~2000 - 4000 per server (depends on hardware), maintain a persistent backend connection. Each connection listens for notifies, LISTEN 'client_xxx'. There are maybe 10 different reasons why a NOTIFY 'client_xxx' is fired. Sometimes, notifies are broadcasted to all client connections, or just portions of them. The goal is real-time messaging to a large groups of computers/devices. Past 4000, the problem is distributed to a second, third, etc... server. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify rewrite
Premature optimization is the root of all evil ;-). Unless you've done some profiling and can show that this is a hot spot, making it more complicated isn't the thing to be doing now. I'm thinking of how our system uses/abuses notifies, and began wondering if several thousand backends listening with a large queue would perform decently behind a linear search. At this point, I have no data either way; only an assumption based off being burnt by sequential scans in the past ;) -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify rewrite
I thought of a compromise: add the number of times a notification was generated (coalesced count+1) to the callback data. That would satisfy any backwards compatibility concerns and my use case too! If you are suggesting that the server poke data into the notifier's opaque payload, I vote no. Maybe the NOTIFY command can include a switch to enable this behavior. No syntax suggestions at this point. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify rewrite
2. The payload parameter is optional. A notifying client can either call NOTIFY foo; or NOTIFY foo 'payload';. The length of the payload is currently limited to 128 characters... Not sure if we should allow longer payload strings... Might be a good idea to make the max the same as the max length for prepared transaction GUIDs? Not sure anyone would be shipping those around, but it's a pre-existing limit of about the same size. I don't see any reason to impose such a small limit on payload size. Surely some limit must exist, but 128 characters seems awfully small. I already have at few places in mind that would require more bytes. Why not 8K, 64K, 256K, 1M or even more? Is there some other factor in play forcing this limitation? -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq - extending PQexecParams/PQexecPrepared to specify resultFormat for individual result columns
Ivo Raisr wrote: Hi guys, I hacked PostgreSQL 8.4.1 libpq and added a new function to specify resultFormat for individual result columns. Are you interested in a patch? (http://www.postgresql.org/docs/8.4/interactive/libpq-exec.html says: There is not currently a provision to obtain different result columns in different formats, although that is possible in the underlying protocol. So I made this possible.) Can you explain the use case for this? Have you investigated libpqtypes? -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] postgres 8.3.8 and Solaris 10_x86 64 bit problems?
I'll hack the makefile and give it a shot. No need to hack it, set CFLAGS during configure: shell]# CFLAGS=-m64 ./configure (options) -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] postgres 8.3.8 and Solaris 10_x86 64 bit problems?
Zdenek Kotala wrote: Andrew Chernow píše v ne 18. 10. 2009 v 21:09 -0400: I'm curious if this is a lost hope. My boss is recommending we flatten the Sun box and install redhat linux (which I'm fine with). I'd rather not as threading in Solaris is better. Maybe solaris threads were better 10-15 years ago, but I'm not convinced that is still the case. Any data supporting that argument, solaris 10 threads vs. linux 2.6.11+ kernel (p)threads? I can point on this article: http://tweakers.net/reviews/649/all/database-test-sun-ultrasparc-t1-vs-punt-amd-opteron.html Zdenek For starters, the original poster is using AMD64, so whether an ultrasparc improves thread performance is immaterial here. OP said: Solaris 10 x86_64 with postgres 8.3.8 and openssl 98k using gcc version 3.4.3 Although the article is interesting, the data came from (or passed through) Sun employees. I'm not saying the article's claims are not true or intentionally misleading, but rather that I am skeptical about the findings; especially since it reads more like a marketing piece than a technical analysis. BTW, I have nothing against Sun or Solaris (spent a few years on Solaris 7 8 sparc servers a while back and found them quite stable). I'm just a hard sell do to endless exaggerated claims by all the top vendors and techy outlets. I find myself weeding through all the hype with a machete :) -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] postgres 8.3.8 and Solaris 10_x86 64 bit problems?
# ./pg_ctl ld.so.1: pg_ctl: fatal: relocation error: R_AMD64_32: file /usr/local/postgres64/lib/libpq.so.5: symbol (unknown): value 0xfd7fff1cf210 does not fit Killed symbol (unknown). Can you turn on debugging symbols? Knowing the symbol may point to a library that was not compiled properly. So I run 'ldd pg_ctl' to see if everything is linking ok. And I'm wondering if there is a problem with libpq.so.5 as mentioned in the original error # file /usr/local/postgres64/lib/libpq.so.5 /usr/local/postgres64/lib/libpq.so.5: ELF 64-bit LSB dynamic lib AMD64 Version 1 [SSE CMOV], dynamically linked, not stripped Ok. So looking good. Maybe there is a library or header libpq needs that I'm missing in 64 bit? # ldd /usr/local/postgres64/lib/libpq.so.5 Are you sure that all pg_ctl referenced libraries and all libpq.so referenced libraries were built as 64-bit using PIC? Are you linking with any static library that may contain 32-bit objects? That error is most commonly PIC or arch-mismatch. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] postgres 8.3.8 and Solaris 10_x86 64 bit problems?
I'm curious if this is a lost hope. My boss is recommending we flatten the Sun box and install redhat linux (which I'm fine with). I'd rather not as threading in Solaris is better. Maybe solaris threads were better 10-15 years ago, but I'm not convinced that is still the case. Any data supporting that argument, solaris 10 threads vs. linux 2.6.11+ kernel (p)threads? Another thing to consider in your decision is that Sun was just bought by oracle, leaving the solaris road map up in the air. At least for me, Linux is a bit more reassuring ... or freebsd. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] man pages
David Fetter wrote: Folks, I'd like to see about creating man pages for the following: - libpq - SPI - the built-in functions That would be really helpful and convenient. I've often wanted libpq man pages. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] postgres 8.3.8 and Solaris 10_x86 64 bit problems?
u235sentinel wrote: So I compiled postgres with Solaris 10 and have problems running it. # ./pg_ctl ld.so.1: pg_ctl: fatal: relocation error: R_AMD64_32: file /usr/local/postgres64/lib/libpq.so.5: symbol (unknown): value 0xfd7fff1cf210 does not fit Killed Maybe libpq.so wasn't built with the PIC option (gcc -fPIC, sun compiler -Kpic). -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] happy birthday Tom Lane ...
Hans-Juergen Schoenig -- PostgreSQL wrote: Tom, On behalf of the entire PostgreSQL team here in Austria I want to wish you a happy birthday. We hope that you fill be a vital part of PostgreSQL for many years to come. Best regards, Hans-Jürgen Schönig + team Happy 30th Tom ;) -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] new version of PQconnectdb was:(Re: [HACKERS] Determining client_encoding from client locale)
Jaime Casanova wrote: On Thu, Sep 10, 2009 at 12:01 AM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Mon, Jul 6, 2009 at 10:00 AM, Heikki Linnakangasheikki.linnakan...@enterprisedb.com wrote: Could we have a version of PQconnectdb() with an API more suited for setting the params programmatically? The PQsetdbLogin() approach doesn't scale as parameters are added/removed in future versions, but we could have something like this: PGconn *PQconnectParams(const char **params) Where params is an array with an even number of parameters, forming key/value pairs. Usage example: i extracted the functions to connect that Heikki put on psql in his patch for determining client_encoding from client locale and put it in libpq so i follow the PQconnectdbParams(* params[]) approach. I was following this and never saw any firm decision on the prototype for this function. Although, I can say the single argument version did not appear to win any votes. The below posts agreed on a two argument version of parallel arrays (keywords, values): http://archives.postgresql.org/pgsql-hackers/2009-09/msg00533.php http://archives.postgresql.org/pgsql-hackers/2009-09/msg00559.php There is also the idea of passing an array of structs floating around, NULL terminated list or include an additional argument specifying element count. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] new version of PQconnectdb was:(Re: [HACKERS] Determining client_encoding from client locale)
Jaime Casanova wrote:
On Mon, Sep 14, 2009 at 1:34 PM, Andrew Chernow a...@esilo.com wrote:
Jaime Casanova wrote:
i extracted the functions to connect that Heikki put on psql in his
patch for determining client_encoding from client locale and put it in
libpq so i follow the PQconnectdbParams(* params[]) approach.
[...]
The below posts agreed on a two argument version of parallel arrays
(keywords, values):
http://archives.postgresql.org/pgsql-hackers/2009-09/msg00533.php
http://archives.postgresql.org/pgsql-hackers/2009-09/msg00559.php
actually, Tom said: it's hard to be sure which way is
actually more convenient without having tried coding some likely
calling scenarios both ways.
Aahhh, correct you are Daniel son :)
personally, i think it will cause more problems than solve because you
have to be sure your arrays have relationship between them...
A strict relationship exists either way.
There is also the idea of passing an array of structs floating around, NULL
terminated list or include an additional argument specifying element count.
one more variable to the equation, more innecesary complexity and
another source of errors, IMO...
one more variable or one more element, both of which cause problems if
omitted/incorrect.
const char *params[] =
{host, blah.com, port, 6262, NULL, NULL};
// compiler enforces relationship
const PGopotion opts[] =
{{host, blah.com}, {port, 6262}, {NULL, NULL}};
IMHO, the struct approach seems like a cleaner solution.
Any chance of using a term other than params? Maybe options or
props?
--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] new version of PQconnectdb was:(Re: [HACKERS] Determining client_encoding from client locale)
actually, Tom said: it's hard to be sure which way is actually more convenient without having tried coding some likely calling scenarios both ways. Aahhh, correct you are Daniel son :) ??? don't understand you ??? From the movie karate kid; oopps, should be Daniel San. I was trying to be cute but that apparently failed :( -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] new version of PQconnectdb was:(Re: [HACKERS] Determining client_encoding from client locale)
PGconn *PQconnectParams(const char **params) Where params is an array with an even number of parameters, forming key/value pairs. Usage example: Maybe use the term properties (props for short) or options instead of params? Params is already in heavy use. How about PQconnectProps(...) or PQconnectOptions(...)? Another idea is to use an array of PQconninfoOption structs: PQconn *PQconnectParams(PQconninfoOption *params); this sounds like a good idea, specially if we add new parameters to Here's another idea, parallel arrays: PGconn *PQconnectProps(const char **names, const char **values); PGconn *PQconnectOptions(const char **names, const char **values); To build on the struct idea, maybe PGprop or PGoption instead of PQconninfoOption. Also, add an argument specifying the number of props/options. PGconn *PQconnectProps(const PGprop *props, int nProps); PGconn *PQconnectOptions(const PGoption *options, int nOptions); any one have a preference here? I like the struct approach. I personally prefer specifying the element count of an array, rather than using a NULL terminating element. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] _WIN32_WINNT should be 0x0501 in win32.h
Magnus Hagander wrote: On Monday, September 7, 2009, Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp wrote: We should define _WIN32_WINNT as 0x0500 in src/include/port/win32.h, but it should be 0x0501 (Windows XP) because IPPROTO_IPV6 will be defined only if _WIN32_WINNT = 0x0501 in the recent Micosoft SDKs. Hi! Have you verified that binaries compiled that way still run on windows 2000? I had checking that on my list before making this change... Yes they do. Our current project uses 0x0501. Windows 2000 is part of our build farm and a target platform for that project. We set WINVER to the same value as well. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] _WIN32_WINNT should be 0x0501 in win32.h
Magnus Hagander wrote: On Mon, Sep 7, 2009 at 15:14, Andrew Chernowa...@esilo.com wrote: Magnus Hagander wrote: On Monday, September 7, 2009, Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp wrote: We should define _WIN32_WINNT as 0x0500 in src/include/port/win32.h, but it should be 0x0501 (Windows XP) because IPPROTO_IPV6 will be defined only if _WIN32_WINNT = 0x0501 in the recent Micosoft SDKs. Hi! Have you verified that binaries compiled that way still run on windows 2000? I had checking that on my list before making this change... Yes they do. Our current project uses 0x0501. Windows 2000 is part of our build farm and a target platform for that project. We set WINVER to the same value as well. Thanks, then we should be good to go. If you're actively working with win2k, any chance you can get us a buildfarm machine on it? :-) Sure. I think its an image running on one of our ESXi servers. I'll look into it tomorrow, not supposed to be working today ;) -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] New types for transparent encryption
Encrypting lots of small chunks of data with the same key is a very dangerous thing to do and it's very tricky to get right. Using an initialization vector (IV) is the way to go, recommend using CBC or CFB mode. Although, an IV is never supposed to be used more than once with the same key; that can leak hints about the plaintext. Where is the randomly generated IV stored for use during decryption? -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] New types for transparent encryption
Andrew Dunstan wrote: Andrew Chernow wrote: Encrypting lots of small chunks of data with the same key is a very dangerous thing to do and it's very tricky to get right. Using an initialization vector (IV) is the way to go, recommend using CBC or CFB mode. Although, an IV is never supposed to be used more than once with the same key; that can leak hints about the plaintext. Where is the randomly generated IV stored for use during decryption? Well, you can store it along with the encrypted data. The IV doesn't need to be secret, just random. I do that for one of my clients. That's correct. Duh!?! Probably the first N bytes of the cipher text. Would the IV be regenerated every time the plaintext is updated, to avoid using it twice? For instace: update t set text = 'abc' where id = 1 . ISTM that the IV for OLD.text should be thrown away. Where would the key come from? Where would it be stored? What cipher is used? -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [pgsql-www] commitfest.postgresql.org
Robert Treat wrote: On Friday 03 July 2009 11:50:29 Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Fri, Jul 3, 2009 at 10:35 AM, Tom Lanet...@sss.pgh.pa.us wrote: The current URL seems to be http://commitfest.postgresql.org/action/commitfest_view?id=2 which is both opaque as can be and not looking like it's intended to be stable over the long term. I'm not sure why you would think that it's not stable. Because it's exposing three or four details of your implementation, which you might wish to change later. I'm also not sure what you would think that it's not self-explanatory, since it looks pretty self explanatory to me. It's impossible to know that this is commitfest 2009-07. commitfest.postgresql.org/2009/07 ? That, or any similar scheme, seems easily doable with a little apache rewrite magic and some programming. See my blog urls for one such example. I believe Tom wants details removed from the URL, so future implementation changes don't either a) break bookmarks because more stuff is needed in the URL or b) don't break bookmarks but be limited to existing sutff in the URL (ie. hacky work arounds). If that's the case, your best best is to use some kind of key, like 16 random bytes displayed in hex, that looks up the data. IMHO, I don't see much gain to encoding the date into the url either. This is not a great way of telling the user when something occurred. A lookup is going to occur either way, so why not get all data at once using a single method? -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [pgsql-www] commitfest.postgresql.org
I *am* using some kind of key. Specifically, in integer derived from a serial column. It's just as stable as 16 random bytes displayed in hex, but a lot shorter and easier to remember, if you're the sort of person who likes to remember URLs. :-) Wasn't aware of exately what you were doing. It sounded like multiple things were in the query_string. If its already a single key, than there is no need to use a different key. And no, I don't like remebering URLs ... thus all the fuss about breaking bookmarks ;-) It's impossible to know that this is commitfest 2009-07. commitfest.postgresql.org/2009/07 ? That, or any similar scheme, seems easily doable with a little apache rewrite magic and some programming. See my blog urls for one such example. IMHO, I don't see much gain to encoding the date into the url either. This is not a great way of telling the user when something occurred. A lookup is going to occur either way, so why not get all data at once using a single method? Sorry, I'm not following this part. Using a URL to encode when something occurred was being offered as a solution to know what commitfest it is. I'm not sure where your confusion is? -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] check for missing tablespaces?
Andrew Dunstan wrote: I am in the middle of helping a customer recover from a situation where a tablespace was missing when a machine was rebooted and postgres restarted, Have you uncovered why the tablespace when missing? and I'm wondering if we should not have some sort of check for this on startup +1 Andrew Chernow -- 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] check for missing tablespaces?
Andrew Dunstan wrote: I am in the middle of helping a customer recover from a situation where a tablespace was missing when a machine was rebooted and postgres restarted, Have you uncovered why the tablespace went missing? and I'm wondering if we should not have some sort of check for this on startup +1 Andrew Chernow -- 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] check for missing tablespaces?
Anyway, from this POV all we really need to know is that the device hosting this tablespace failed to mount when the machine was rebooted, and then postgres restarted. Good to know postgresql had nothing to do with the missing data. I wasn't sure if it was user error, config problem or hardware. From my experience, it doesn't really matter if you have a sophisticated SAN or put together an ad-hoc nas/das solution, storage likes to bite you. The only thing that helps me sleep at night is sound replication and backups (in some cases triple redundancy with aging data sets). Andrew -- 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] Keep alive in libpq
Pavel Golub wrote:
Hello, postgresmen.
I found solution how to implement keep alive through sockets in
archive: http://archives.postgresql.org/pgsql-interfaces/2006-11/msg00014.php
However, it is dated 2006 year and I am wonder if this is for real?
setsockopt has been around since at least the early 90s. It is for real.
At least in Windows environment?
If not are there any solutions?
Use WSAIoctl(SIO_KEEPALIVE_VALS). SIO_KEEPALIVE_VALS is supported on
Windows 2000 and later.
http://msdn.microsoft.com/en-us/library/ms741621(VS.85).aspx
Search the page for SIO_KEEPALIVE_VALS.
Make sure to test the below because I didn't :)
#include Winsock2.h
#include Mstcpip.h /* struct tcp_keepalive */
int r;
DWORD dw;
struct tcp_keepalive ka;
/* enable or disable (same as SO_KEEPALIVE) */
ka.onoff = 1;
/* milliseconds (same as TCP_KEEPIDLE) */
ka.keepalivetime = 6;
/* milliseconds (same as TCP_KEEPINTVL) */
ka.keepaliveinterval = 3000;
/* configure keep-alives for 'conn' */
r = WSAIoctl((SOCKET) PQsocket(conn), SIO_KEEPALIVE_VALS,
ka, (DWORD) sizeof(ka), NULL, 0, dw, NULL, NULL);
if (r == SOCKET_ERROR)
{
// failed, check WSAGetLastError()
}
Apparently, you can also enable keep-alives using the standard
setsockopt(...SO_KEEPALIVE...); Although, the other knobs are only
exposed through WSAIoctl. When using SO_KEEPALIVE, the default
keep-alive settings are used ... keep-alive timeout of 2 hours followed
by a 1 second probe. They can still be adjusted via WSAIoctl.
--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] bytea vs. pg_dump
Andrew Dunstan wrote:
another nit with base64 is that properly encoded data requires
newlines according to the standard.
er, no, not as I read rfc 3548 s 2.1.
cheers
andrew
Why does encode('my text', 'base64') include newlines in its output? I
think MIME requires text to be broken into 76 char lines but why does
encode do this?
--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] change oid of a pg_type
Tom Lane wrote: Merlin Moncure mmonc...@gmail.com writes: Is there any way to adjust the oid of an already created pg_type No. Then why are oids sent in some type's external binary format? If you can't fix oids, external format is useless to the outside world. Since you CAN modify a type's name all day long, shouldn't schema.typname be the external way to reference types? Or, perhaps, another mechanism. There are some very powerful things that could be achieved, with a more portable way of referencing types. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] change oid of a pg_type
Tom Lane wrote: Andrew Chernow a...@esilo.com writes: Tom Lane wrote: Merlin Moncure mmonc...@gmail.com writes: Is there any way to adjust the oid of an already created pg_type No. Then why are oids sent in some type's external binary format? How is that related? Well, the format is called external, yet the oid is internal. It seem like a contradiction. Maybe I'm being to literal but i think external format should present an identifier other servers can understand/map. If you can't fix oids, external format is useless to the outside world. You have to be prepared to find out what type the OID belongs to. There's at least one client library (maybe JDBC) that maintains a cache of a SELECT from pg_type for exactly this purpose. Most Yeah, libpqtypes looks them up by schema.name as one registers them, PQregisterTypeHandler. Although this works, it feel more like a work around. I think it would be cleaner if a dns-like resolution was not required. Andrew -- 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] Array types
John Lister wrote: Following this up, is there any docs on the binary wire format for arrays? None that I know of. Check out the backend source: (array_recv() and array_send() functions) http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/arrayfuncs.c?rev=1.154 Or, look at libpqtypes array.c: http://libpqtypes.esilo.com/browse_source.html?file=array.c -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Array types
Andrew Chernow wrote: John Lister wrote: Following this up, is there any docs on the binary wire format for arrays? None that I know of. Check out the backend source: (array_recv() and array_send() functions) http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/arrayfuncs.c?rev=1.154 Or, look at libpqtypes array.c: http://libpqtypes.esilo.com/browse_source.html?file=array.c Forgot to mention, this is not as simple as understanding the array format. You have to understand the wire format for all types that can be array elements. The array wire format serializes its array elements as [elem_len][elem_data]. elem_data is the wire format of the array element type, like an int, timestamp, polygon, bytea, etc... So once you unravel the array container format, you still have to demarshal the type data. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Array types
John Lister wrote: Cheers, nice to know it is possible... Now to see if i can get java/python to do the same :) or to use a modified libpq somehow... If performance is your concern, you would probably get the best results using the languages C glue interfrace. For instance, in java I think you would want to create a JNI wrapper to libpqtypes that converts PGarray to a java array. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Array types
John Lister wrote: They all return arrays as text, is it possible to configure postgresql to return an array in native form (does postgresql support such a thing)? This is using both the simple and extended query forms - i couldn't see a way to say what return type i wanted in the protocol docs... You need libpqtypes (client-side library). It requires a new 8.4 feature called libpq-events, but there is an 8.3 patch available. If you willing to patch your 8.3.5 libpq client, than this should meet your needs. Downloads: http://pgfoundry.org/projects/libpqtypes/ Documentation: http://libpqtypes.esilo.com/ For arrays, libpqtypes gives you a PGresult where each tuple is an array item. For composite arrays, each composite field is a PGresult field. This would seem much better in terms of performance, both size and speed(conversion). That is true. Our testing has proven this. It also reduces the overall coding effort. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PQinitSSL broken in some use casesf
Tom Lane wrote:
I personally would be happy with the two-argument function solution.
I modified my previous patch to use a two-argument function solution.
--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/
Index: doc/src/sgml/libpq.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/libpq.sgml,v
retrieving revision 1.280
diff -C6 -r1.280 libpq.sgml
*** doc/src/sgml/libpq.sgml 28 Mar 2009 01:36:11 - 1.280
--- doc/src/sgml/libpq.sgml 30 Mar 2009 13:29:48 -
***
*** 6179,6190
--- 6179,6219
!-- If this URL changes replace it with a URL to www.archive.org. --
See ulink
url=http://h71000.www7.hp.com/doc/83final/BA554_90007/ch04.html;/ulink
for details on the SSL API.
/para
+ para
+variablelist
+ varlistentry
+ term
+ functionPQinitSecure/function
+ indexterm
+primaryPQinitSecure/primary
+ /indexterm
+ /term
+
+ listitem
+ para
+Allows applications to select which secure components to initialize.
+synopsis
+ void PQinitSecure(int do_ssl, init do_crypto);
+/synopsis
+ /para
+
+ para
+When do_ssl is non-zero, OpenSSL's SSL library will be initialized.
+When do_crypto is non-zero, OpenSSL's Crypto library will be initialized.
+By default, both libraries are initialized. When SSL support is not
+compiled in, this function does nothing.
+ /para
+ /listitem
+ /varlistentry
+/variablelist
+ /para
+
table id=libpq-ssl-file-usage
titleLibpq/Client SSL File Usage/title
tgroup cols=3
thead
row
entryFile/entry
Index: src/interfaces/libpq/exports.txt
===
RCS file: /projects/cvsroot/pgsql/src/interfaces/libpq/exports.txt,v
retrieving revision 1.22
diff -C6 -r1.22 exports.txt
*** src/interfaces/libpq/exports.txt 22 Sep 2008 13:55:14 - 1.22
--- src/interfaces/libpq/exports.txt 30 Mar 2009 13:29:48 -
***
*** 149,154
--- 149,155
PQinstanceData147
PQsetInstanceData 148
PQresultInstanceData 149
PQresultSetInstanceData 150
PQfireResultCreateEvents 151
PQconninfoParse 152
+ PQinitSecure 153
Index: src/interfaces/libpq/fe-secure.c
===
RCS file: /projects/cvsroot/pgsql/src/interfaces/libpq/fe-secure.c,v
retrieving revision 1.121
diff -C6 -r1.121 fe-secure.c
*** src/interfaces/libpq/fe-secure.c 28 Mar 2009 18:48:55 - 1.121
--- src/interfaces/libpq/fe-secure.c 30 Mar 2009 13:29:49 -
***
*** 96,107
--- 96,108
static PostgresPollingStatusType open_client_SSL(PGconn *);
static void close_SSL(PGconn *);
static char *SSLerrmessage(void);
static void SSLerrfree(char *buf);
static bool pq_init_ssl_lib = true;
+ static bool pq_init_crypto_lib = true;
static SSL_CTX *SSL_context = NULL;
#ifdef ENABLE_THREAD_SAFETY
static int ssl_open_connections = 0;
#ifndef WIN32
***
*** 170,182
* initialized OpenSSL.
*/
void
PQinitSSL(int do_init)
{
#ifdef USE_SSL
! pq_init_ssl_lib = do_init;
#endif
}
/*
* Initialize global context
*/
--- 171,196
* initialized OpenSSL.
*/
void
PQinitSSL(int do_init)
{
#ifdef USE_SSL
! PQinitSecure(do_init, do_init);
! #endif
! }
!
! /*
! * Exported function to allow application to tell us which secure
! * components to initialize.
! */
! void
! PQinitSecure(int do_ssl, int do_crypto)
! {
! #ifdef USE_SSL
! pq_init_ssl_lib = do_ssl;
! pq_init_crypto_lib = do_crypto;
#endif
}
/*
* Initialize global context
*/
***
*** 820,831
--- 834,847
* message - no connection local setup is made.
*/
static int
init_ssl_system(PGconn *conn)
{
#ifdef ENABLE_THREAD_SAFETY
+ int num_ssl_conns = 0;
+
#ifdef WIN32
/* Also see similar code in fe-connect.c, default_threadlock() */
if (ssl_config_mutex == NULL)
{
while (InterlockedExchange(win32_ssl_create_mutex, 1) == 1)
/* loop, another thread own the lock */ ;
***
*** 837,849
InterlockedExchange(win32_ssl_create_mutex, 0);
}
#endif
if (pthread_mutex_lock(ssl_config_mutex))
return -1;
! if (pq_init_ssl_lib)
{
/*
* If necessary, set up an array to hold locks for OpenSSL. OpenSSL will
* tell us how big to make this array.
*/
if (pq_lockarray == NULL)
--- 853,874
InterlockedExchange(win32_ssl_create_mutex, 0);
}
#endif
if (pthread_mutex_lock(ssl_config_mutex))
return -1;
! /*
! * Increment connection count if we are responsible for
! * intiializing the SSL or Crypto library. Currently, only
! * crypto needs this during setup and cleanup. That may
Re: [HACKERS] PQinitSSL broken in some use casesf
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Tom Lane wrote: It sounds like everyone has converged on agreeing that this way is okay for 8.4? Object now or hold your peace ... What are we doing with PQinitSSL()? Nothing, except improving the documentation. My patch didn't change the docs at all. I wasn't sure what to do there. However, I did change the implementation but left behavior alone. It now calls PQinitSecure(do_init, do_init); -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PQinitSSL broken in some use casesf
Bruce Momjian wrote: Merlin Moncure wrote: On Mon, Mar 30, 2009 at 10:22 AM, Robert Haas robertmh...@gmail.com wrote: On Mon, Mar 30, 2009 at 9:36 AM, Andrew Chernow a...@esilo.com wrote: Tom Lane wrote: I personally would be happy with the two-argument function solution. I modified my previous patch to use a two-argument function solution. This looks OK to me, except I think we should modify the documentation to PQinitSSL() to say that it you must not use both that function and PQinitSecure(), and explain that if you need to control initialization of libcrypto and libssl, you should use that function instead. do you think PQinitSSL should be deprecated? Well, I think having duplicate capability in an API is confusing, so yea. Maybe document that PQinitSSL(do_init) is now the same as PQinitSecure(do_init, do_init). -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
