Re: [HACKERS] .NET driver
Andrew Dunstan wrote: Well, contributions come in many forms, not just patches. Note too that almost all the requested features had nothing to do with core postgres, which is what this list is about Well, as a driver developer I can tell you that the core teams attitude toward driver driven requests can get frustrating. It usually boils down to that's the way it is, deal with it, often without even giving me the option to intelligently deal with it. I haven't been very active lately (and OLE DB has suffered as a result, to be sure), but there were some areas where it was not a matter of doing the coding. I offered initial code, and was willing to work on it to make sure it matures into a full patch, backwards compatible and without any significant performance costs. It was more a matter of it doesn't affect Postgresql's core, so it's not important attitude that really makes life difficult for a driver developer. Drivers are important, guys. There aren't enough people willing to dive into the mess that is OLE DB/.Net etc as is. Let's try to at least acknowledge that there is a need. Shachar p.s. If I may remark on someone else's turf. ODBC has a thing called dynamic view. As far as database design, it's a horrid mess, but it's in the specs, and it requires tracking what happens to specific rows of a query after a transaction has finished. The last time I checked (which was when Hiroshi was still the ODBC maintainer), ODBC was emulating it series of queries on the tid and oid of the rows. It was semantically correct, but required round trip for each row query, as well as not being able to work on rows returned from views and other non-table sources. I don't know who took over ODBC, and whether dynamic views were restored (the driver turned read-only for a while) or how, but if they come asking for some crazy scheme that includes tracking what happens to the rows of a query after the transaction in which it happened is over, please listen to them. It's not their crazyness, it's ODBC's. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Re: [Oledb-dev] double precision error with pg linux server, but not with windows pg server
Tom Lane wrote: Shachar Shemesh [EMAIL PROTECTED] writes: Before you explode at me (again :), I'm not arguing that you can do binary based calculations of decimal numbers without having rounding errors that come to bite you. I know you can't. What I'm saying is that we have two cases to consider. In one of them the above is irrelevant, and in the other I'm not so sure it's true. You're setting up a straw-man argument, though. I was answering your claim that it's impossible to convert decimal to binary based floats without rounding errors. The real-world problem cases here are not decimal, they are non-IEEE binary floating arithmetic. The typical difference from IEEE is slightly different tradeoffs in number of mantissa bits vs number of exponent bits within a 32- or 64-bit value. I answered that elsewhere while suggesting a different format that would address that. These numbers do not appear to be a concern in our situation, however. I seem to recall also that there are machines that treat the exponent as power-of-16 not power-of-2. I'm pretty sure I don't understand this. Maybe I misunderstood the format, but wouldn't that actually lose you precision with, at most, marginal gain in range? As far as I can see, the moment you no longer work in base 2 you lose the implicit bit, which means you have a one bit less starting point than base 2 notations (all number are denormalized). So depending on which way the tradeoffs went, the other format will have either more precision or more range than IEEE. Again, should that be a real concern, see my message at http://archives.postgresql.org/pgsql-hackers/2007-05/msg00892.php for details about what the suggestion actually is. Just be sure to read IEEE there as meaning IEEE like. I allowed different sizes for the fields. regards, tom lane Shachar ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Re: [Oledb-dev] double precision error with pg linux server, but not with windows pg server
Tom Lane wrote:
Okay, I spent some time googling this question, and I can't find any
suggestion that any ARM variant uses non-IEEE-compliant float format.
What *is* real clear is that depending on ARM model and a run time (!)
CPU endianness flag, there are three or four different possibilities
for the endianness of the data, including a PDP-endian-like alternative
in which the order of the high and low words is at variance with the
order of bytes within the words. (Pardon me while I go vomit...)
Welcome to the wonderful world of embedded CPUs. These buggers will do
ANYTHING, and I do mean anything, in order to squeeze a little more
performance with a little less power consumption, while keeping the end
price tag under 10$. The ARM9, for example, can switch, on the fly,
between 32 and 16 bit machine language in order to save a few bytes in
code size and gain a few MIPS in execution speed.
As an amusing side note, I have heard a claim that the only reason we
need endianity at all is because the Europeans didn't understand that
Arabic is written from right to left. In Arabic you read 17 as seven
and ten, which means that it is already little endian. Just one
request, please don't quote this story without also mentioning that this
story is wrong, and that 1234 is said, in Arabic, as one thousand two
hundred four and thirty.
Mixed endianity is usually relic of a 16bit processor that was enhanced
to 32bit. The parts that were atomic before would be big endian, but the
parts that the old CPU required to do in separate operations are stored
low to high.
So
I would concur with a patch that ensures that this is what happens
on the different ARM variants ... though I'll still be interested
to see how you make that happen given the rather poor visibility
into which model and endianness we are running on.
You do it semantically. Attached is the outline for the code (I can form
a patch only after we agree where it should go)
I should note a few things:
On IEEE platforms, the code will, of course, translate to/from the same
format. This can be verified by the dump at the end.
I have tested the code on several numbers, and it does work for normal
and for denormalized numbers. I have not tested whether the detection
whether we should generate one or the other actually works, so there may
be an off by one there.
The are a few corner cases that are not yet handled. Two are documented
(underflow and rounding on denormalized numbers). There is one
undocumented, of overflow.
The IEEE - native code is not yet written, but I think it should be
fairly obvious how it will look once it is.
There is also a function in the code called calcsize. It's the
beginning of a function to calculate the parameters for the current
platform, again, without knowing the native format. I was thinking of
putting it in the configure test, except, of course, the platforms we
refer to are, typically, ones for which you cross compile. See below.
Comments welcome.
PS: Of course this does not resolve the generic issue of what to do
with platforms that have outright non-IEEE-format floats. But at the
moment I don't see evidence that we need reach that issue for ARM.
The code above does detect when the float isn't being precisely
represented by the IEEE float. We could have another format for those
cases, and distinguish between the cases on import by testing its size.
PPS: I'm sort of wondering if the PDP-endian business doesn't afflict
int8 too on this platform.
It's likely. I would say that a configure test would be the best way to
test it, but I suspect that most programs for ARM are cross compiled.
I'm not sure how to resolve that. Maybe if there's a way to
automatically test what gets into memory when you let the compiler
create the constant 0123456789abcdef. At least for smaller than 8 bytes,
the hton functions SHOULD do the right thing always.
I COULD go back to my source (he's on vacation until Sunday anyways),
but I'll throw in a guess. Since the ARMs (at least the 7 and the 9) are
not 64 bit native, it's compiler dependent. There are two main compilers
for the ARM, with one of them being gcc. That's, more or less, where my
insights into this end.
Shachar
#include stdio.h
#include limits.h
#include math.h
#include assert.h
// What type would we be working on?
#if 1
// Double
#define TYPE double
#define FRAC_BITS 52
#define EXP_BITS 11
#define EXP_BIAS 1023
#else
// Float
#define TYPE float
#define FRAC_BITS 23
#define EXP_BITS 8
#define EXP_BIAS 127
#endif
union fp {
TYPE flt;
struct {
unsigned long low;
unsigned long high;
} i;
unsigned long long l;
struct {
unsigned long long int frac:FRAC_BITS;
unsigned long long int exp:EXP_BITS;
unsigned long long int sign:1;
} fp;
};
void dumpnum( TYPE n )
{
union fp val;
val.flt=n;
val.fp.sign=0;
val.fp.exp=0x7ff;
val.fp.frac=12;
printf(%g %08x%08x\n, val.flt, val.i.high, val.i.low );
Re: [HACKERS] Re: [Oledb-dev] double precision error with pg linux server, but not with windows pg server
Please note - I'm not trying to pick up a fight. Tom Lane wrote: Your source appears fairly ignorant of things-float. That is possible, and even likely, however If they really are using decimal FP, it's easy to demonstrate that a lossless conversion to/from binary representation of similar size is impossible. The set of exactly representable values is simply different. When I originally read this statement my initial response was *dough*. After having time to sleep over it, however, I'm no longer as certain as I was. Before you explode at me (again :), I'm not arguing that you can do binary based calculations of decimal numbers without having rounding errors that come to bite you. I know you can't. What I'm saying is that we have two cases to consider. In one of them the above is irrelevant, and in the other I'm not so sure it's true. The first case to consider is that of the client getting a number from the server and doing calculations on it. Since the client works in base 2, the inaccuracies are built into the model no matter what we'll do and how we export the actual number. As such, I don't think we need worry about it. If the client also works in base 10, see the second case. The second case is of a number being exported from the server, stored in binary (excuse the pun) format on the client, and then resent back to the server, where it is translated from base 2 to base 10 again. You will notice that no actual calculation will be performed on the number while in base 2. The only question is whether the number, when translated to base 2 and then back to base 10 is guaranteed to maintain its original value. I don't have a definite answer to that, but I did calculate the difference in representation. A 64 bit IEEE floating point has 1 bit of sign, 52 bit of mantissa and 11 bit of exponent. The number actually has 53 bits of mantissa for non-denormalized numbers, as there is another implied 1 at the beginning. I'm going to assume, however, that all binary numbers are denormalized, and only use 52. I'm allowed to assume that for two reasons. The first is that it decreases the accuracy of the base 2 representation, and thus makes my own argument harder to prove. If I can prove it under this assumption, it's obvious that it's still going to hold true with an extra bit of accuracy. The second reason I'm going to assume it is because I don't see how we can have normalized numbers under the base 10 representation. The assumed 1 is there because a base 2 number will have to have a leading 1 somewhere, and having it at the start will give best accuracy. The moment the leading number can be 1-9, it is no longer possible to assume it. In other words, I don't see how a base 10 representation can assume that bit, and it is thus losing it. Since this assumption may be wrong, I am penalizing the base 2 representation as well to compensate. To recap, then. With base 2 we have 52 bits of mantissa, which will get us as high as 4,503,599,627,370,500 combinations. These will have an effective exponent range (not including denormalized numbers) of 2,048 different combinations, which can get us (let's assume no fractions on both bases) as high as 2^2048, or 616.51 decimal digits. With decimal representation, each 4 bits are one digit, so the same 52 bits account for 13 digits, giving 10,000,000,000,000 possible mantissas, with an exponent range of 11 bits, but raised to the power of 10, so resulting in a range of 2048 decimal digits. Of course, we have no use for such a huge exponent range with such small mantissa, so we are likely to move bits from the exponent to the mantissa. Since we have no use for fractions of a decimal digit, we will move the bits in multiples of 4. I'm going now to assume an absurd assumption. I'll assume we move 8 bits from the exponent to the mantissa. This leaves us with only three bits of exponent, which will only cover 8 decimal digits, but give us 60 bits, or 15 decimal digits in the mantissa, or a range of 1,000,000,000,000,000 numbers. Please note that the base 2 representation still has 4.5 times more mantissas it can represent using only 52 bits. So what have we got so far? A 64 bit decimal based floating point can give up almost all of its exponent in order to create a mantissa that has, roughly, the same range as the base 2, and still be outnumbered by 2.17 bits worth ASSUMING WE DON'T USE THE IMPLIED BIT IN THE BASE 2 REPRESENTATION. Now, I suggest that even with just 2.17 bits extra, the binary representation will be accurate enough to hold the approximation of the decimal number to such precision that the back and forth translation will reliably produce the original number. Of course, if we do use the extra bit, it's 3.17 bits extra. If we don't give up 8, but only 4 bits from the exponent, we now have 6.49 bits extra (5.49 if you want the above assumption), while having an exponent range of only 128 decimal digits (as opposed to 616 with IEEE). Now, I am by no means
Re: [HACKERS] Re: [Oledb-dev] double precision error with pg linux server, but not with windows pg server
Csaba Nagy wrote: If you care about the +/- for +/-Infinity, you must also care about +/-0 too, so you get the right type of infinity if you divide with 0... so +0 and -0 are far from being semantically identical. Cheers, Csaba. My suggestion accommodates that. Shachar ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Re: [Oledb-dev] double precision error with pg linux server, but not with windows pg server
Peter Eisentraut wrote: I want to use the binary format but I don't like what it does. It doesn't do anything. The set of circumstances under which it is useful has been limited, on purpose, and, as far as I can see, without any good reason. Spending not much time (and I suggested to spend it myself, so it's not like I'm asking anyone to do this for me) this format can be made to allow transfer of data between installations. Why wouldn't we want that? We have fought many years to get closer to IEEE 754 conformance. Please notice that the format I offered *is* IEEE. In fact, what I'm offering is to export the binary in IEEE format EVEN IF THE NATIVE FORMAT ISN'T. As for the ARM architecture, I've pulled my sources, and the answer is this: ARM doesn't have one standard floating point format. Different ARM architectures will use different formats. Most architectures will actually use IEEE, but some will use decimal based and such. According to my source (a distributor of ARM based hardware), none of the other formats will lose precision if translated to IEEE. So, I will repeat my original question. I can write portable code that will translate the native format to IEEE (if it's not already the same). It seems that it will be good enough for all platforms discussed here. Failing that, we can adopt my later proposal which is IEEE + status for all places where that is good enough. Shachar ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Re: [Oledb-dev] double precision error with pg linux server, but not with windows pg server
Greg Smith wrote: On Sun, 20 May 2007, Shachar Shemesh wrote: This is not data given to store. It's data being exported. Data being exported has a funny way of turning around and being stored in the database again. It's kind of nice to know the damage done during that round trip is minimized. I agree. All I'm asking, and have not received an answer yet, is whether assuring that we don't have any SEMANTIC damage is enough. In other words, if I can assure that data exported and then imported will always, under all circumstances, compare the same to the original, would that be enough of a requirement? In other words, if I offer a format that is assured of preserving both mantissa and exponent precision and range, as well as all extra attributes (+/-Infinity and NaN), but does not guarantee that the semantically identical constructs are told apart (+0 vs. -0, and the different NaNs), would that format be acceptable? Tom seems to think this is not a goal (though, aside from his disbelief that such a goal is attainable, I have heard no arguments against it). If Tom thinks it's not attainable, the best way to convince him otherwise would be demonstrate that it's not. Granted. That's why I've been quite. I'm pulling my sources for the ARM FP format details, to make sure what I have in mind would work. One reason people use text formats for cross-platform exchanges is that getting portable binary compatibility for things like floating point numbers is much harder than you seem to think it is. I'll just point out that none of the things that Tom seems to be concerned about are preserved over text format. Stepping back for a second, your fundamental argument seem to be based on the idea that doing conversions to text is such a performance issue in a driver that it's worth going through these considerable contortions to avoid it. Converting to text adds a CPU overhead in both client and server, as well as a network transmission overhead. Even if it's not determental to performance, I'm wondering why insist on paying it. You are right that I offered no concrete implementation. I'll do it now, but it is dependent on an important question - what is the range for the ARM floating point. Not having either an ARM to test it on, nor the floating point specs, it may be that a simpler implementation is possible. I offer this implementation up because I see people think I'm talking up my ass. A 64 bit IEEE float can distinguish between almost all 2^64 distinct floats. It loses two combinations for the + and - infinity, one combination for the dual zero notation, and we also lose all of the NaNs, which means (2^mantissa)-2 combinations. Over all, an n bit IEEE float with m bits of mantissa will be able to represent 2^n - 2^m - 1 actual floating point numbers. That means that if we take a general signed floating point number, of which representation we know nothing but the fact it is n bits wide, and that it has a mantissa and an exponent, and we want to encode it as an IEEE number of the same width with mantissa size m and exponent of size e=n-m-1, we will have at most 2^m+1 unrepresentable numbers. In a nutshell, what I suggest is that we export floating points in binary form in IEEE format, and add a status word to it. The status word with dictate how many bits of mantissa there are in the IEEE format, what the exponent bias is, as well as add between one and two bits to the actual number, in case the number of floats the exported platform has is larger than the number of floats that can be represented in IEEE with the same word length. The nice thing about this format is that exporting from an IEEE platform is as easy as exporting the binary image of the float, plus a status word that is a constant. Virtually no overhead. Importing from an IEEE platform to an IEEE platform is, likewise, as easy as comparing the status word to your own constant, and if they match, just copy the binary. This maintains all of Tom's strict round trip requirements. In fact, for export/import on the same IEEE platform no data conversion of any kind takes place at all. There are questions that need to be answered. For example, what happens if you try to import a NaN into a platform that has no such concept? You'd have to put in a NULL or something similar. Similarly, how do you import Infinity. These, however, are questions that should be answered the same way for text imports, so there is nothing binary specific here. I hope that, at least, presents a workable plan. As I said before, I'm waiting for the specs for ARM's floating point before I can move forward. If, as I suspect, ARM's range is even more limited, then I may try and suggest a more compact export representation pending question of whether we have any other platform that is non-IEEE, and what is the situation there. Shachar ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project
[HACKERS] Re: [Oledb-dev] double precision error with pg linux server, but not with windows pg server
Hi guys of the pgsql-hackers list. I've received a bug report on the OLE DB list, which I suspect is actually a server bug. The correspondence so far is listed further on, but, in a nutshell, user runs an OLE DB client on windows (OLE DB uses the binary interface), and server version 8.1.9 on Windows, and all is fine. When the server is switched to 8.1.8 on Debian Etch on ARM9, float8 type is not received properly by OLE DB. Since OLE DB doesn't really care what version the server is running, the chances of this being a server side bug are really high. I don't know ARM9 well enough to comment on floating point format there. Julian Heeb wrote: Shachar Shemesh schrieb: Julian Heeb wrote: Hello Our acounting software can use the PostgreSQL OLE DB driver to access a postgreSQL database. With the pg server installed on windows, everything works fine. I moved now the database to a postgreSQL server on a linux server, but now every floating point number gets wrongly interpreted by the acounting software, either by replacing it with a 0 or a very large number (e.g. xxxE+308). Only the floating point numbers are affected, integer or characters are correct. pgAdmin shows even the fp numbers correctly, so I guess it has something to do with the pgoledb driver. Can someone give me a hint, how to solve the problem? It's hard to give a precise answer. Let's try a couple of venues. First of all, what platform is the Linux server? Is that an Intel, or something else? It is an ARM9 platform with Debian Etch (Linkstation Pro Fileserver with Freelink). Also, what is the precise type of the floating point var on the server? Can you give the SQL line that generated the table? The table has been generated by the following SQL line. The problem occures at the double precision fields. I have some bad news. This is the comment in the Postgresql source code. This seems to be a core problem at the server side of things: /* *pq_sendfloat8- append a float8 to a StringInfo buffer * * The point of this routine is to localize knowledge of the external binary * representation of float8, which is a component of several datatypes. * * We currently assume that float8 should be byte-swapped in the same way * as int8. This rule is not perfect but it gives us portability across * most IEEE-float-using architectures. * */ Could it be that ARM9 is not IEEE float standard? Can anyone from the hackers list give any insight into this? The function for the data type import on the client side seems to be in order (switch the byte order around, and assume it's a valid double C type). Shachar ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Re: [Oledb-dev] double precision error with pg linux server, but not with windows pg server
Stefan Kaltenbrunner wrote: Shachar Shemesh wrote: Hi guys of the pgsql-hackers list. I've received a bug report on the OLE DB list, which I suspect is actually a server bug. The correspondence so far is listed further on, but, in a nutshell, user runs an OLE DB client on windows (OLE DB uses the binary interface), and server version 8.1.9 on Windows, and all is fine. When the server is switched to 8.1.8 on Debian Etch on ARM9, float8 type is not received properly by OLE DB. if 8.1.8 is built based on the debian packages it is probably compiled with --enable-integer-datetimes. Is the oledb client library able to cope with that ? I'm calling GetPgStatus(stat, _bstr_t(integer_datetimes) ); to find out what the server representation is, and cope accordingly. This only doesn't work on 7.4 servers, but otherwise I'm fine. either way, this is off topic to this problem. From further reading, it seems that ARM indeed uses its own representation for IEEE floats. I'll try to poll my sources, try and find out what the %([EMAIL PROTECTED]$ this format actually is (google was no help), and try and formulate a patch for PG to export it in IEEE despite the discrepancies. Stefan Shachar ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Re: [Oledb-dev] double precision error with pg linux server, but not with windows pg server
Tom Lane wrote: Shachar Shemesh [EMAIL PROTECTED] writes: I've received a bug report on the OLE DB list, which I suspect is actually a server bug. The correspondence so far is listed further on, but, in a nutshell, user runs an OLE DB client on windows (OLE DB uses the binary interface), and server version 8.1.9 on Windows, and all is fine. When the server is switched to 8.1.8 on Debian Etch on ARM9, float8 type is not received properly by OLE DB. Perhaps OLE is trying to use binary instead of text transmission of data? Of course it does. That's what the OLE DB specs say. Said so in my original email. It's not a server bug if ARM has a different FP format than the client hardware; No. The server can use, internally, whatever it wants. it's the client's responsibility to either use text format or be prepared to cope with whatever the binary format is. I agree 100%. I'll reiterate - the problem is not that PG is exporting the internal ARM FP format. The problem is that the server is exporting the internal ARM FP format when the server is ARM, and the IEEE format when the server is Intel. It's not the format, it's the inconsistency. I can (and I do) handle, in PgOleDb, binary format that are vastly different than those that I need (anyone said timestamps?). Handling a format that is inconsistent across same version backends merely because of platform, now that's a server bug if I ever saw one. regards, tom lane Like I said elsewhere, I'm willing to write a patch to pq_sendfloat8 (and probably pq_getmsgfloat8 too) to make sure it does the conversion on ARM platforms. Hell, I think I can even write it portable enough so that it will work on all non-IEEE platforms (I'm not sure yet, but I have a general idea). What I'm hoping for, however, is that if I do, you (or another comitter) will get it in. Shachar ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Re: [Oledb-dev] double precision error with pg linux server, but not with windows pg server
Tom Lane wrote: Shachar Shemesh [EMAIL PROTECTED] writes: I'll reiterate - the problem is not that PG is exporting the internal ARM FP format. The problem is that the server is exporting the internal ARM FP format when the server is ARM, and the IEEE format when the server is Intel. It's not the format, it's the inconsistency. This is not a bug, it's intentional. While IEEE-spec floats are reasonably interchangeable these days (modulo the endianness issue), other FP formats tend to have different ranges, special cases, etc. If we try to force them to IEEE spec we may have problems with overflow, loss of precision, who knows what. Yes, but if we do not then we have a data interchange library that is useless for data interchange. I think overflow and precision loss is preferable. Please remember that I'm only trying to help Postgresql here. I have a spec to work with on the outside. I'm more than willing to do what's necessary (see the STRRRANGE date conversion code) in order to adapt whatever PG throws my way to the no less strange representation expected of me. That's what I do as a driver hacker. Sometimes, the specs don't help me. Windows' notion of timezone free timestamps is nothing short of a disgrace, and some of the hacks that are needed around that issues are, well, hacks. I don't come complaining here, because this has nothing to do with PG. It's bad design on the other end of the two ends that a driver has to make meet. But sometimes, like now, PG puts me in an impossible position. You are essentially telling me you will get the numbers in an unknown format, you will not have any way of knowing whether you got them in a strange format or not, nor will you have any docs on what that format is going to be. That is no way to treat your driver developers. Like I said elsewhere, I'm willing to write a patch to pq_sendfloat8 (and probably pq_getmsgfloat8 too) to make sure it does the conversion on ARM platforms. Hell, I think I can even write it portable enough so that it will work on all non-IEEE platforms Really? Will it be faster Absolutely. Do you honestly believe that turning a 64bit binary number into a 40 something byte decimal number will be quicker than turning a 64 bit binary number into another 64 bit number? For one thing, I really doubt that my technique will require division, modulo or, in fact, any math operations at all. It will likely be done with a few bit shifting and that's it. I also find it strange, though, that you berate me for using binary rather than text format, and then complain about speed. That's what makes OLE DB faster than ODBC - binary interface. and more reliable than conversion to text? Well, depends on how you define more reliable. If you define it to mean exactly represents what happens in the server internals, then the answer is no. If you define it to mean make more sense to the client, and have better chances of producing results that more closely approximate the right number than the current code, then the answer is a definite yes. (In this context reliable means can reproduce the original datum exactly when transmitted back.) Who cares? If you are using the same function for binary communication inside the server and for communications to the clients (or, for that matter, another server), then there is something wrong in your design. What are the send functions used for, beside server to client communication, anyways? You are asking me to treat the binary data as an opaque. Well, I'll counter with a question - what good is that to me? Please note that the current code is useless for communicating binary data between two servers, even if they are guaranteed to be of the same version! How much less reliable can you get? Please, give your own interface designers something to work with. Your attitude essentially leaves me out in the cold. regards, tom lane Shachar ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Re: [Oledb-dev] double precision error with pg linux server, but not with windows pg server
Tom Lane wrote: Binary format has other goals that are not always compatible with 100% platform independence --- that's unfortunate, sure, but it's reality. Maybe the misunderstanding is mine. What are the goals for the binary format? Shachar ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Re: [Oledb-dev] double precision error with pg linux server, but not with windows pg server
Heikki Linnakangas wrote: But sometimes, like now, PG puts me in an impossible position. You are essentially telling me you will get the numbers in an unknown format, you will not have any way of knowing whether you got them in a strange format or not, nor will you have any docs on what that format is going to be. That is no way to treat your driver developers. You seem to be ignoring the fact that the text format is platform-independent. That's what for example JDBC uses, and I'd imagine other interfaces as well. Is it not possible to use text format in OLE DB, for floating points? It is impossible to use text format for just floating point. I often don't know in advance what type the result is going to be. I can switch EVERYTHING to text mode, but as the other end of the interface requires me to get things out in binary format, I get the following absurd path: PG stores in binary PG translates to text PG sends to driver driver translates to binary As long as I get consistent binary format, I prefer to translate between binary formats than between text and binary. What are the send functions used for, beside server to client communication, anyways? You are asking me to treat the binary data as an opaque. Well, I'll counter with a question - what good is that to me? Imagine an application that does this: Issue query SELECT foofloat FROM footable, and store the value to a variable Issue INSERT INTO footable2 VALUES (?), and send back the same value. Why would I want to do that over insert into footable2 select foofloat from footable? I know, even if it makes no sense you'd want it to work. All I'm saying is that something has got to give, and there is no reason to assume that your usage is more likely than mine. For that reason, without looking into the ARM float implementation, it is just as likely that it contains LESS precision than the IEEE one. Would that change the objection? Don't you think footable and footable2 should now have the same value? If SELECT * FROM footable, footable2 WHERE foofloat=foofloat returns 0 rows, I'd be pissed. It's possible that the conversion within the driver loses some precision, depending on the data types supported by the language and platform, but the wire protocol should at least give the driver a chance to get it right. I'm not sure why there is a difference here. How is that above example different than the exact same example written in ADODB (and thus passing driver conversion)? I'll take this one step further. Does the text representation never lose precision? Ever? What if I send a patch that sends a 64bit float as 128bit number, containing two integers, one for mantissa and one for exponent. This format is guaranteed to never lose precision, but is wire compatible across platforms. Would that be considered a good solution? The current code is not used for communicating between two servers. And it works fine as long as the client and the server are on the same platform. So does the driver. And yet, a user complained! In my dictionary, this means that as long as the client and server are on the same platform is not a good enough requirement. Don't get me wrong, I agree that the binary format is broken as it is, but the cure mustn't be worse than the disease. Please, give your own interface designers something to work with. Your attitude essentially leaves me out in the cold. Design a wire protocol that 1. Doesn't lose information on any platform 2. Is more efficient than text format and I'm pretty sure it'll be accepted. I just offered one. I would hate it myself, and it would mean that pre 8.3 (or whenever it is that it will go in) will have a different representation than post the change, but it will live up to your requests. Shachar ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Re: [Oledb-dev] double precision error with pg linux server, but not with windows pg server
Tom Lane wrote: Sure it's possible. Send a Parse command, ask for Describe Statement output, then specify the column formats as desired in Bind. Now this does imply an extra server round trip, which might be annoying if your client code doesn't have another reason to need to peek at Describe output. No, it's far worse than annoying. It kills my performance. OLE DB does have a mechanism for explicit prepare, and I certainly COULD do it only for those cases, but it is meaningless. What if I send a patch that sends a 64bit float as 128bit number, containing two integers, one for mantissa and one for exponent. This format is guaranteed to never lose precision, but is wire compatible across platforms. Would that be considered a good solution? No, not unless you can make the case why this handles NaNs and denormalized numbers compatibly across platforms... NaNs and infinite (plus and minus) should not be a problem. I'm not sure what denormalized numbers are. If you mean (switching to base 10 for a second) that 2*10^3 vs. 20*10^2, then I would have to ask why you want them treated differently. What is the scenario in which you would want to tell them apart? Likewise, would you really want to tell +0 and -0 apart? If I have an export/import round trip that turns -0 into +0, is that really a problem? regards, tom lane Shachar ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [Oledb-dev] [HACKERS] Re: double precision error with pg linux server, but not with windows pg server
Tom Lane wrote: Obviously, if you are transporting the dump across platforms then that may be an impossibility. In that case you use a text dump and accept that you get an approximation. That's something that I've been meaning to ask about, but you all seemed so sure of yourself. What you are essentially saying is that no one aside from the server itself is allowed to get full precision. That seems like a strange decision to make. But there should be a way to ensure that you can have a lossless dump of whatever strange FP format your server may use, as long as you are restoring onto a similar machine. Personally (and I know my opinion doesn't count), I find the similar machine requirement a bit hard. It is usually accepted for HA or live load balancing, but is really inappropriate for backups or data transfers. Just my humble opinion. If there is a guaranteed-reversible transform between the ARM FP format and IEEE format, then I'd be interested in hacking things the way you suggest I find it highly likely that there will be. After all, the ARM format was not designed to be better packed than IEEE, just easier to hardware implement in an efficient way. However --- but what I suspect is that such a conversion must lose either range or precision. There are only so many bits in a double. Like I said elsewhere, a 64bit FP only has 64bits, but there is nothing constraining us to export a 64bit number to 64bits. regards, tom lane What I'm worried about in that regard is about other platforms that PG may be running on. Like I said above, I'm fairly sure (will get the specs and make sure) that there shouldn't be a problem in exporting ARM FP into 64bit IEEE with no loss at all. This says nothing, however, about other platforms. Unless we map all cases, we had better choose an export format that is capable of extension. Shachar ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [Oledb-dev] [HACKERS] Re: double precision error with pg linux server, but not with windows pg server
Andrew Dunstan wrote: Why the heck do the OLE DB specs care about the internals of the client-server prototocol? It is documented fairly clearly that text is the only portable way to transfer data. Is it? Perhaps we need to expand this sentence in the docs: Keep in mind that binary representations for complex data types may change across server versions; Where is that in the docs. It does not appear in the page discussing PQLIB and binary data transfers (http://www.postgresql.org/docs/8.2/interactive/libpq-exec.html). Even the original sentence does not describe the problem we're seeing here. It does not mention cross platform incompatibility. The COPY docs are probably more correct: The BINARY key word causes all data to be stored/read as binary format rather than as text. It is somewhat faster than the normal text mode, but a binary-format file is less portable across machine architectures and PostgreSQL versions. Again, to me this sentence spells you have a problem, we're not going to help you out, deal with it. This is especially true if what Tom said was true, that the text format does not maintain total precision. You are essentially telling the user you cannot move your data reliably even between servers of the same version. Shachar ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Re: [Oledb-dev] double precision error with pg linux server, but not with windows pg server
Tom Lane wrote: Shachar Shemesh [EMAIL PROTECTED] writes: Tom Lane wrote: No, not unless you can make the case why this handles NaNs and denormalized numbers compatibly across platforms... NaNs and infinite (plus and minus) should not be a problem. Really? Need I point out that these concepts, let alone their representation, are not standardized in non-IEEE float implementations? So what? They only need be standardtized in our export format, which we define. That's why we define it, after all I'm not sure what denormalized numbers are. You should find out before proposing representation replacements for floats. Yes, but we're still discussing the preliminary stages. At these stages, it is enough to know that they CAN be defined (and they can). What is the scenario in which you would want to tell them apart? Likewise, would you really want to tell +0 and -0 apart? IIRC (it's been fifteen or so years since I did any serious numerical analysis) the arguments in favor have mostly to do with preserving maximal accuracy for intermediate results in a series of calculations. So maybe you could claim that these arguments are not so relevant to storage in a database. But personally I don't think it's the province of a database to decide that it need not accurately preserve the data it's given to store. This is not data given to store. It's data being exported. I think you are failing to consider something. The simple truth of the matter is that drivers are used far more often to access the server than pqlib or direct TCP programming. OLE DB has been stagnant for over two years now, is only available for one platform, and the easiest install option for it is through the postgresql installer, and yet it is still the third most popular download on pgfoundry (with the .NET provider being #1). What I'm getting at is that drivers are important. It is important that they have good performance. It is important that they be stable. I really think the backend should take driver considerations more seriously. The suggested method, of switching to text mode, will surely work, but it will also hurt performance. I've said it before. I find it highly unlikely that the ARM FP format will have any problem with being exported, even to a 64bit IEEE number. Not knowing the FP format, but knowing the platform, it likely just got rid of all the corner cases (NaN, denormalized numbers) merely so they can implement it more efficiently in hardware. I find the chances that it will have a wider range than IEEE in either mantissa or exponent unlikely. The question here is a broader question, though. Should we strive for binary compatibility across all platforms of a given version? The benefit is faster drivers and being able to COPY across platforms (but, still, not across versions). The cost are a couple of types (I would really change timestamp too, while at it) that need a non-straight forward export/import function. Tom seems to think this is not a goal (though, aside from his disbelief that such a goal is attainable, I have heard no arguments against it). What do the other people think? Shachar ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Moving a project from gborg to pgfoundry?
Hi all, When pgfoundry was opened, there was some talk about moving the projects from gborg there. This has not, to date, happened. Is there any chance of this happening now, even if only for the specific project? I feel really bad about releasing a new version of ole db, with the news of the previous release not yet disappearing from the main page. I don't even need all of the infrastructure. Moving just CVS and the mailing lists will be more than enough. There is nothing important in the bug and other areas that cannot be reconstructed in a few minute's work. Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. Have you backed up today's work? http://www.lingnu.com/backup.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Moving a project from gborg to pgfoundry?
Marc G. Fournier wrote: On Sun, 20 Mar 2005, Thomas Hallgren wrote: Marc G. Fournier wrote: Once I've copied both over, I'll get Chris to mark the gborg project as being 'disabled' so that nobody will see it over there anymore ... Ok, I submitted a request for the project under pgfoundry. Same name (oledb). Actually, it would be very nice if the project was still visible at gborg with a single page explaining that the project has moved. People may get the wrong idea if it just disappears. Seconded. The address was in the files in the release. I'm still in status quo with PL/Java. I'm eager to help out to get the move done, but I still don't know what more I can do. My requirements are the same as Shachar's although a dump of the current bug database would be helpful. mailing lists and cvs are easy, as they are the same format under either ... its the 'database conversion' stuff that is the reason for the long hold up for the rest ... Yes, well. I never liked much the way gborg handled bugs. In fact, it's the fact that people started using the bug system under gborg that causes me to miss pgfoundry so much. Comments such as the one on http://gborg.postgresql.org/project/oledb/bugs/bugupdate.php?755 also don't help. The three bugs or so that actually need tracking I can transfer myself. I don't think it justifies writing automatic conversion. It does not even seem as if gborg is enjoying a lot of active projects anyways. Of the three projects that had anything new to say in over a month, two have just stated that they don't care about the rest of the info anyways. To summarize, just give me read only access to the old project's data and I'm set. Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. Have you backed up today's work? http://www.lingnu.com/backup.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Unstable timestamp binary representation?
Hi all, In the OLE DB code there is code for parsing timestamps received from the server. This code behaves erratically. Upon further examination, I found the following piece of code in Postgresql's timestamp2tm: #ifdef HAVE_INT64_TIMESTAMP dt -= CTimeZone * INT64CONST(100); #else dt -= CTimeZone; #endif In other words, it seems that I, as a client, needs to guess whether postgres was compiled with or without HAVE_INT64_TIMESTAMP. If it was, what I am getting is a big-endian int64. If it was not, this is a double that needs to be multiplied by 100. In my tests, Potgresql 8 running on Windows uses double, while Postgresql 7.4 running on Debian Linux SID uses int64. Is there any way I can remotely detect which is it? Reading from the actual code, it seems each instance of postgres just assumes that it was encoded in the same format as it was compiled with, not stopping to ask what the other side was compiled with. Is this a bug in postgres as well? Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. Have you backed up today's work? http://www.lingnu.com/backup.html ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Version 1.0.0.18 of OLE DB released
Hi all, Version 1.0.0.18 of OLE DB has just been uploaded to gborg. This is a major upgrade than the previous versions, with most types now handled (the #1 complaint about OLE DB thus far). As far as my understanding goes, this version solves almost all of the problems that surfaced with OLE DB in the near past. I strongly recommend upgrading, especially prior to filing any new bug reports. The new version can be obtained from http://gborg.postgresql.org/project/oledb/download/download.php. Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. Have you backed up today's work? http://www.lingnu.com/backup.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Unstable timestamp binary representation?
Tom Lane wrote: Shachar Shemesh [EMAIL PROTECTED] writes: In other words, it seems that I, as a client, needs to guess whether postgres was compiled with or without HAVE_INT64_TIMESTAMP. No, you need to inquire of the value of the integer_datetimes parameter. (At least as of 8.0, this is provided for free during connection startup, so you don't even need an extra network round trip to find it out.) Hmm. So I need to call |PQparameterStatus with |integer_datetimes. If I get TRUE, treat as integer, if I get FALSE, treat as double. What happens if I get NULL? The docs seem to suggest that this is what will happen for 7.4 back end, and the FALSE is the default. My experience shows, however, that at least 7.4 for Debian Sid should be true. No, it checks. See pg_control. Not what I have seen from the code, but I will take your word for it. regards, tom lane Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. Have you backed up today's work? http://www.lingnu.com/backup.html ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] type unknown - how important is it?
Tom Lane wrote: Dave Cramer [EMAIL PROTECTED] writes: Shachar Shemesh wrote: I don't know type 705 well enough to decide which would work best. If it's guaranteed to be a validly encoded text string, then I'll just put it in as DBTYPE_WSTR, and get it done with. I think it's safe to assume it will be encoded properly. AFAICS it will work exactly like 'text'. regards, tom lane I have some doubts. From textin: /* verify encoding */ len = strlen(inputText); pg_verifymbstr(inputText, len, false); unknownin has no such thing. I'm worried. I realize that unknownout and textout are precisely the same, but I can't say I understand completely. Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. Have you backed up today's work? http://www.lingnu.com/backup.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] type unknown - how important is it?
Dave Cramer wrote: Pre-7.4 servers used set autocommit on/off and that was the error they referred to, however after asking them to get me a test case I haven't heard back 9 times out of 10 this means that while creating their test case they found the problem. You do realize that OLE DB uses pqlib's exec with params to pass parameters around, and also to make sure that data is returned in binary format. As a result, it requires PG version 7.4 and above. It may well be that the answer is It's irrelevant for our supported backends. Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. Have you backed up today's work? http://www.lingnu.com/backup.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] type unknown - how important is it?
Dave Cramer wrote: Shachar, I think with type oid 705 (unknown) it's safe to treat it as text. Certainly better than punting. Question is what DBTYPE to report it as. Options are DBTYPE_WSTR (UTF-16 string, which means the input string must be a valid UTF-8 string), DBTYPE_STR (just dump it as I get it, and hope that client doesn't barf on the UTF-8 encoding), DBTYPE_BYTES (it's an array of bytes, just let the client figure out what to do with it. No promises on my part). I don't know type 705 well enough to decide which would work best. If it's guaranteed to be a validly encoded text string, then I'll just put it in as DBTYPE_WSTR, and get it done with. On another note are you aware of any issues with transactions? Specifically with using the dated autocommit mode ? I'm not sure what dated autocommit is. What are the issues you are seeing? Dave Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. Have you backed up today's work? http://www.lingnu.com/backup.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] type unknown - how important is it?
Shachar Shemesh wrote: Dave Cramer wrote: Shachar, I think with type oid 705 (unknown) it's safe to treat it as text. Certainly better than punting. Question is what DBTYPE to report it as. Options are DBTYPE_WSTR (UTF-16 string, which means the input string must be a valid UTF-8 string), DBTYPE_STR (just dump it as I get it, and hope that client doesn't barf on the UTF-8 encoding), DBTYPE_BYTES (it's an array of bytes, just let the client figure out what to do with it. No promises on my part). I don't know type 705 well enough to decide which would work best. If it's guaranteed to be a validly encoded text string, then I'll just put it in as DBTYPE_WSTR, and get it done with. Oh, if you want to look it up yourself: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oledb/htm/oledbdata_types.asp Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. Have you backed up today's work? http://www.lingnu.com/backup.html ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] type unknown - how important is it?
Tom Lane wrote: Dave Cramer [EMAIL PROTECTED] writes: I just had a customer complain about this. My understanding is that unkown is a constant which has not been typed yet. Is it possible for it to be a binary type, if so how ? I would think it should only ever be a string? You can read unknown as string literal for which the query does not provide sufficient context to assign a definite type. I dunno what the OLE driver really needs to do with the datatype info, but I suppose that treating this as equivalent to text is not unreasonable. Ask the complainant what *he* thinks it should do. regards, tom lane If JDBC can postpone the understanding what the type is until it's being requested, all I can say is lucky them. OLE DB isn't so lucky. We need to actually report what type each column of a query is. To make matters worse, there is almost not a single type that can simply be passed along from PG to the caller. PG sends numbers in network byte order, while OLE DB typically reports them in little endian. We do our queries in UTF-8, while we need to return them in UTF-16, and so on and so forth. Typically, just about any type you would care to mention would need conversion of one type or another. I shudder to remember what I had to do with dates. So OLE DB performs the query in binary mode. Anything else would not make much sense anyways. The unknown error may be a result of PG not recognizing the type, as Tom mentioned, but it may also be a result of OLE DB not recognizing the type. If the later is the case, OLE DB of sufficiently late versions should report what OID the missing type has, if not through the usual OLE error mechanisms, then through a log file you can set through the registry. Read the docs for more info. Most new types are actually fairly easy to add. With some types, however, one would need to understand what is the expected behavior. This is not as simple as one would expect, as most people use ADO rather than OLE DB directly. Still, a question (to the oledb-dev list, preferably) saying PG type #696 should be returned as DBTYPE_UINT4 would almost guarantee quick response, as it's fairly easy to handle (in most cases). If this is not a PG built in type then things are a little more complicated. Non-built in types have non-constant OIDs, and are identified by name on session startup. This means that OLE DB cannot handle a type that is only added to the database after the session started (not normally a problem). Also, I cannot possibly report non-standard types unless I know how to parse them. I'll probably add code to handle all unknown types as BLOBs or something, but I cannot give a time frame for that. I'm also not certain how helpful that would be for most cases. On the good news front, Version 1.0.0.17 is about ready to be released (initial schema support). I am resuming development after about half a year of doing other stuff. Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. Have you backed up today's work? http://www.lingnu.com/backup.html ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] type unknown - how important is it?
I have a complaint from an OLE DB user that when he does select 'a', he gets an unhanded type error. Since OLE DB uses a binary interface, it has to know about all variable types that pass through it. The debug information for the problem show that the returned type is 705, which is unknown. My question is - what is unknown used for? Is it important to support binary send and receives with this type? Does postgresql know how to convert it to other types? I was under the impression that 'a' would be text. Why is unknown used instead? Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. http://www.lingnu.com/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] type unknown - how important is it?
Joshua D. Drake wrote: Shachar Shemesh wrote: I have a complaint from an OLE DB user that when he does select 'a', he gets an unhanded type error. Since OLE DB uses a binary interface, it has to know about all variable types that pass through it. The debug information for the problem show that the returned type is 705, which is unknown. My question is - what is unknown used for? Is it important to support binary send and receives with this type? Does postgresql know how to convert it to other types? I was under the impression that 'a' would be text. Why is unknown used instead? Try casting a to varchar. Which is binary compatible but understood by ODBC/OLE DB. Hmm, I did not explain myself clearly enough. I maintain the OLE DB provider. My question was not that. My question was whether I should add support to unknown? If so, what type should it be represented to outside applications? If unknown is binary compatible to text, I can just pass it on as text and have it done with. The question is whether that makes sense, whether it makes more sense to pull and error if that happens, or whether there is something else which I should do. Thanks, Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. http://www.lingnu.com/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] No parameters support in create user?
Gaetano Mendola wrote: Shachar Shemesh wrote: Tom Lane wrote: Parameters are only supported in plannable statements (SELECT/INSERT/UPDATE/DELETE; I think there is some hack for DECLARE CURSOR these days too). That's a shame. Aside from executing prepared statements, parameters are also useful for preventing SQL injections. Under those cases, they are useful for all commands, not only those that can be prepared. Oh well. I'm not sure whether that's extremely clever or downright insane, but I'm solving this problem by calling Select quote_literal($1) and select quote_id($1), and then using the results. Create your own plpgsql function and call it. In a way you can say I did `-). This is what I'm using: http://gborg.postgresql.org/projects/oledb -- Shachar Shemesh Lingnu Open Source Consulting ltd. http://www.lingnu.com/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] libpq and prepared statements progress for 8.0
Harald Fuchs wrote: The first problem with this approach is that it requires libpq to be all things to all people. We've already had some discussion in this thread about the tension between supporting application programs written in C, which want one set of features, and drivers, which need some other ones. After awhile you end up with a bloated, probably buggy library. We're already some way down that path, and I don't care to go much further. I don't think that's what David meant, although he said so :-) What we should have is a C API especially for use by driver authors; probably this API is so far away from the rest of libpq that it should not be part of it. OLE DB is based on libpq. While the proposed function would be very nice to have (and, in fact, needed for some obscure semantics of the OLE DB protocol that no one really uses), at the moment there are NO major features missing from OLE DB that cannot be provided using the existing code. This may be a result of libpq going some way down bloat av., as Tom said, but personally I don't see the need for a separate API. I have not delved too deeply into the ODBC sources, so I can't attest to the feasibility of using libpq there. This API could make life easier for driver authours, resulting in more and better drivers for more languages. I'm really interested in what this would provide. It could be that I'm missing something painfully obvious here, but why are driver developers in such a different situation than end users? Don't get me wrong. Having an API to fill data from the server directly into user's buffers would be nice. However, as OLE DB transfers data in binary, as most data types require conversion, and as some of the OLD DB accessors are really weird, I doubt a sane API can be written that I'd use anyways. Likewise, having an API that does gradual delivery of data would be nice. However, things really can be achieved using the asynchronous libpq mechanism, and proper cursors can achieve most of the rest. In short, I may be missing something painfully simple here, but I don't see the real need for a driver oriented backend communication library. Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. http://www.lingnu.com/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] No parameters support in create user?
Hi list, When I try to create a user using the create user SQL command, where the command is being executed using the PQexecParams function from libpq, and the username and password are passed as text (oid 0x19) parameters (binary), I get a syntax error. The command I'm doing is: create user $1 with encrypted password $2 Any idea why this is not working? Is it supposed to work? Trying to pass only the password as a parameter does not work either. Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. http://www.lingnu.com/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] No parameters support in create user?
Tom Lane wrote: Parameters are only supported in plannable statements (SELECT/INSERT/UPDATE/DELETE; I think there is some hack for DECLARE CURSOR these days too). That's a shame. Aside from executing prepared statements, parameters are also useful for preventing SQL injections. Under those cases, they are useful for all commands, not only those that can be prepared. Oh well. I'm not sure whether that's extremely clever or downright insane, but I'm solving this problem by calling Select quote_literal($1) and select quote_id($1), and then using the results. Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. http://www.lingnu.com/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Compilation problems and extension on Windows
Hi list, I asked both these questions on the cygwin list, but as I've started to look into the native port as well, and as I didn't get any answer there, I would appreciate any answer here as well. I'm trying to compile an external library for use as a postgres extension. The project is available on pgfoundry (search for mssqlext). On Unix everything works great. On Windows, I'm trying for both a cygwin and a native version of the extension. I'm having several problems: 1. I cannot link the library without postgres.exe (that has several of the functions I need). However, I do not have access to the link library for it. 2. I have downloaded both the sources from the web and the sources available in the cygwin installer. They are not the same. Neither compiles on cygwin, however. 3. I have not located any instructions for compiling the native sources (8.0 beta 1). The makefiles available through the sources only compile libpq and psql and friends, not the actual database. I have not tried MSYS yet, but do I just run the configure script as usual? Any help with any of these problems would be greatly appretiated. Shachar -- Shachar Shemesh Lingnu Open Systems Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] NLS support for postgreSQL
Mahmoud Taghizadeh wrote: 3. we are planning to add locale per column for postgreSQL, but I found that you had said its impossible. am I right? do you have any paper about it? One of the ways to solve this is to have a special data type for this purpose. Check out the mssqlext project on pgfoundry (http://pgfoundry.org/projects/sql2pg/). In particular, check out the varcharci type, that has case preserving varchars. This may provide you with a starting point for what you are trying to do. Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. http://www.lingnu.com/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Custom type with width specifier
Hi list, I'm trying to create a varchar clone (called varcharci). I have defined new functions called varcharciin, varcharciout, varcharcisend and recv, using the varcharin etc. definitions (i.e. - builtin functions). I defined the type. Everything works, except that when I try to create a table it fails. This only happens when I try to give maximum length to the type. So the command: create table foo ( bar varcharci ); succeeds, but create table foo (bar varcharci(12) ); fails. What do I need to do in order to get the width specifier into my type? I'm not even sure what function is called in order to say that the type needs a width specifier. Many thanks Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. http://www.lingnu.com/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Casts question
Tom Lane wrote: Shachar Shemesh [EMAIL PROTECTED] writes: Tom Lane wrote: Yes, it can cast to varchar, but that doesn't help because there are no varchar operators ;-). To resolve the operator, it has to promote both sides to text, and you didn't offer a cast to text. I don't get it. When we look to see whether we can cast from type X to type Y, we look to see whether there is a pg_cast entry from type X to type Y. We do not look to see if we could get there by casting X to some other type Z and thence to Y (much less more-than-2-step sequences). There are a number of good reasons for this, but I'll just mention speed and surprise factors. Doing so would make for an exponential increase in the number of potential cast paths, thereby probably paralyzing the operator selection code instead of making it more useful. (Too many alternatives are as bad as none.) regards, tom lane That I got. What I don't understand is this. The cast from varchar to text is a no-function one. I.e. - they are defined to be memory-represented the same. If that is the case, one would expect them to also share the input and ouput functions. When looking at the catalog, however, it appears that textin is used to input text, while varcharin is used for varchars. Did I miss anything here? It gets wierder. The binary receive function for varchar (varcharrecv) is different than the one for text (textrecv), but the C implementation of the former simply calls the C implementation of the later. Why not define them to be the same at the SQL level? Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. http://www.lingnu.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Casts question
Tom Lane wrote: Shachar Shemesh [EMAIL PROTECTED] writes: I have defined a datatype called varcharci, shamelessly yanking the input, output, recv and send functions from varchar. This means (as far as I understand things) that this type is binary compatible with varchar. Use text, not varchar. Why is that? Being as it is that no operator = is defined for varcharci, and that the cast from varchar to varcharci is as assignment anyways, shouldn't postgres be able to do the cast implicitly? Yes, it can cast to varchar, but that doesn't help because there are no varchar operators ;-). To resolve the operator, it has to promote both sides to text, and you didn't offer a cast to text. regards, tom lane I don't get it. The cast from varchar to text is using a no function cast - i.e. - they are binary compatible. And yet, there are two seperate functions for receiving from text and from binary representation. Why not use the same function? Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. http://www.lingnu.com/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Using domains for case insensitivity
Hi all, A while back I asked about creating a case insensitive postgresql. Tom, at the time, suggested I create a case insensitive type instead. I am now trying to go that route, and am wondering whether domains will provide a shortcut for me. As far as I understand the task at hand, I am quite capable of using all of the existing varchar functions for input, output, storage, conversions and so on. The only thing I need to override is the comparison functions (and the resulting index creation, of course). According to the docs, domains are not meant for that purpose, but for changing constraints of a type. Is it possible to define a domain that will have the same defaults and constraints as the base type, but will have different comparison functions? Will that provide me with what I need? Many thanks, Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. http://www.lingnu.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] in out send recv functions - immutable or stable?
Hi all, When I look at the int4 functions (int4in, int4out, int4send, int4recv), they are all marked immutable. Then again, when I look at the varchar functions, the in and out functions are immutable, but the send and receive functions are stable. Is there a reason for this? Shachar P.S. This is PG 7.4 on Debian unstable, coming from the standard deb. -- Shachar Shemesh Lingnu Open Source Consulting ltd. http://www.lingnu.com/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] Casts question
Hi all, I have defined a datatype called varcharci, shamelessly yanking the input, output, recv and send functions from varchar. This means (as far as I understand things) that this type is binary compatible with varchar. As such, I used the following two lines: create cast ( varcharci AS varchar ) WITHOUT FUNCTION AS IMPLICIT; create cast ( varchar AS varcharci ) WITHOUT FUNCTION AS ASSIGNMENT; I defined two tables. Both have a column called name. One is a varchar, and the other is a varcharci. When I try to do the following select, I get an error: test=# select test2.id as id-1, test3.id as id-2, test2.name from test2 inner join test3 on test2.name=test3.name; ERROR: operator does not exist: character varying = varcharci HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. When I add an explicit cast, everything works: sun=# select test2.id as id-1, test3.id as id-2, test2.name from test2 inner join test3 on test2.name=cast(test3.name as varchar); results go here Why is that? Being as it is that no operator = is defined for varcharci, and that the cast from varchar to varcharci is as assignment anyways, shouldn't postgres be able to do the cast implicitly? Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. http://www.lingnu.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Case preserving - suggestions
Robert Treat wrote: On Sunday 06 June 2004 13:47, Shachar Shemesh wrote: Hi list, A postgresql migration I am doing (the same one for which the OLE DB driver was written) has finally passed the proof-of-concept stage (phew). I now have lots and lots of tidbits, tricks and tips for SQL Server migration, which I would love to put online. Is pgFoundry the right place? I understand that the code snippets section is not yet operative, but I would still love to put it online ASAP (i.e. - before I forget), and to have it all in one place. We have a couple of articles about sql server migration up on techdocs now, so that seems the most appropriate place for documenting the process you went through. Robert Treat We wrote a new data type (a tinyint replacement), that will not be part of postgresql any time soon. We need to put the code up somewhere. We wrote a perl script that takes the output of the database dump done by MS-SQL, and converts it into an SQL script for postgres (including yanking the data from the MS-SQL tables, and inlining it into the Postgresql script using copy). That needs to go somewhere. It is highly likely that people will find bugs, or want to make additions, to the above two, so source control seems like a necessary idea. In short, I think a pgFoundry project seems better suited for our needs than static documentation. Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Case preserving - suggestions
Hi list, A postgresql migration I am doing (the same one for which the OLE DB driver was written) has finally passed the proof-of-concept stage (phew). I now have lots and lots of tidbits, tricks and tips for SQL Server migration, which I would love to put online. Is pgFoundry the right place? I understand that the code snippets section is not yet operative, but I would still love to put it online ASAP (i.e. - before I forget), and to have it all in one place. One problem detected during that stage, however, was that the program pretty much relies on the collation being case insensitive. I am now trying to gather the info regarding adding case preserving to Postgresql. I already suggested that we do that by changing the procedures, and the idea was turned down. For example, a column UNIQUE constraint must enforce that only one instance of a string be present, case insensitive. Then again, making everything lower/upper case before putting it in was also rejected. Case preserving is what we are looking for. Now, one idea that floated through my mind, and I have not yet looked into how difficult it would be to implement was to define a new system wide collation, called, for example, en_USCI. Have that collation define 'a' and 'A' as the same character. I'm looking for someone with more experience with these things than me (i.e. - just about anyone) to say whether such a thing is doable. I know I can reorder sort criteria using collation, but can I make two characters be actually the same? As a side note, I'll mention that MsSQL uses the collation field to define case insensitivity. Assuming that fails, how hard would it be to create a case insensitive PostgreSQL? Would that be more like changing a couple of places (say, hash computation and string compares), or would that entail making hundreds of little changes all over the code? Is there anything in the regression testing infrastructure that can help check such a change? Many thanks, Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] OIDs, CTIDs, updateable cursors and friends
Hi all, I'm in the process of writing an OLE DB provider for postgres. I am, right now, at the point where updating an entry becomes an issue. Ideally, I would open an updateable cursor for a table/view, and use that. Unfortunetly, Postgres doesn't seem to support those at all. As an alternative, I was thinking of using some unique field to identify, with certanity, the line in question. This will allow me to use an update command to modify said line, in case there is a need with the OLE DB client. My quetion is this - what can I use? I have tried to find some docs regarding OIDs and CTIDs, but the docs seem scarce. If I understand this correctly - OID is meant to identify a row in a table, though it is not guarenteed to be unique across tables, or even inside a given table. A CTID is meant to identify the physical location at which a row is stored. Are these correct? Would adding OID to the rows returned by each Select call, and then doing update blah where oid=xxx when I'm requested to update the row sound like a reasonable stategy, in lieu of updateable cursors? Can anyone suggest a better way? Shachar -- Shachar Shemesh Lingnu Open Systems Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Probably security hole in postgresql-7.4.1
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Should we be thinking about a 7.4.3? I'm not panicking over this particular bug ... but it does seem like we've accumulated enough fixes since 7.4.2 that it may be time to start thinking about another dot-release. Maybe set a date towards the end of the month? regards, tom lane Industry practices dictate that we do issue SOMETHING now. The bug is now public, and can be exploited. This does not necessarily have to be 7.4.3. We can issue 7.4.2.1, containing only this fix, so that people who need to expose their database are not left open to attacks. Also, if we want greater flexibility in handling these cases in the future, we should set up an invite-only list for reporting security bugs, and advertise it on the web site as the place to report security issues. Had this vulnerability been reported there, we could reasonably hold on without releasing a fix until 7.4.3 was ready. If you need help in that list, I have a lot of experience with code security, but very little experience with the Postgresql code. Also, it would be a good idea to invite all the distro-packagers to be on that list. Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Probably security hole in postgresql-7.4.1
Bruno Wolff III wrote: On Wed, May 12, 2004 at 10:46:00 +0300, Shachar Shemesh [EMAIL PROTECTED] wrote: Industry practices dictate that we do issue SOMETHING now. The bug is now public, and can be exploited. The description of the problem indicates that it can only be exploited after you have authenticated to the database. Since people who can connect to a postgres database can already cause denial of service attacks, this problem isn't a huge deal. My take on this is different. To me, a DoS is a nuisance, but an arbitrary code execution vulnerability means information leak, and a major escalation (from which further escalation may be possible). It makes breaches in other programs (web server process especially) worse and provides another way for authorized users to cause problems. Not to mention being another chain. A release should probably be made soon, as a way to advertise the problem so that people are aware of it and can take appropiate steps. I don't think that this problem warrants bypassing normal minor release proceedure. Ok. How about an official patch against 7.4.2 that fixes it, so that packagers can make their own informed decision. Also, has anybody checked what other versions are affected? Is 7.3? 7.2? Some people can't afford to upgrade due to data inconsistancy. For those reasons I suggested a seperate mailing list. Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Probably security hole in postgresql-7.4.1
Tom Lane wrote: Shachar Shemesh [EMAIL PROTECTED] writes: Also, has anybody checked what other versions are affected? Nothing before 7.4, at least by the known implications of this issue. Again, if we wait a while and let Ken keep running his analysis tool, he might turn up other stuff we need to fix. Maybe even stuff that needs a fix much worse than this does. and also I frankly think that this discussion is emblematic of all the worst tendencies of the security community. Have you forgotten the fable about the boy who cried wolf? I totally agree. That's why I suggested preventing the automatic public disclosure for Ken's next bugs, as well as anyone else's. This way, if we do need a few extra days, we can have them while still limiting the window of exposure. I repeat: in my estimation this is not a bug that needs a fix yesterday. AFAICS it would be very difficult to cause more than a nuisance DOS with it, and there are plenty of other ways for authenticated database users to cause those. I'm sorry. Maybe it's spending too many years in the security industry (I've been Check Point's oh my god we have a security problem process manager for over two years). Maybe it's knowing how to actually exploit these problems. Maybe it's just seeing many of the good guys (OpenBSD's Theo included) fall flat on their faces after saying This is a DoS only. In my book, a buffer overrun=arbitrary code execution. For a now famous example of a bug declared non exploitable, followed by an exploit, see http://www.theinquirer.net/?article=4053. I have been on the mailing lists at the time. The problem was declared unexploitable on i386 by some of the best known names in the security industry of the time. regards, tom lane Please. I'm not saying Release now. I'm saying get a mechanism for smarter handling of future events. Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] tinyint
Gaetano Mendola wrote: Hi all, someone of you sent me an example of a new data type called tinyint. Hi. I seen that is using a well directory structure just for compile a tinyint.c. Yes. The idea was to have it do more than just this one type. Maybe a bad idea, on retrospect. Is there any empty structure that I have to use in order to delivery compile my email.c or I can just use a single line command: gcc email.c -I. -I /usr/include/pgsql/server Yes. I seen also that a library called libpgext.so is created. You need to create SOME library, and you need it to be of a unique name. Did I mention that the tinyint type was suboptimal in that respect? Shall I do the same ? Are guide line to use out there? There are in the docs. Start reading from http://www.postgresql.org/docs/7.4/static/extend.html Regards Gaetano Mendola I sent you these sources as a base to build from. You are most welcome to start anew if you don't like the structure. -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Relocatable installs
Dave Page wrote: Anyway, the point is that to do that you need to write to HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\EventLog\Appname (off the top of my head) otherwise, the messages logged in the event log are pretty unreadable. Service installation will also require admin privileges. Regards, Dave I don't think that's a problem at all: 1. Believe it or not, you can write to HKEY_CURRENT_USER even if you are an admin! New and improved. In fact, if you don't mind messing with the load hive interfaces, you can even write to someone else's HKCU if you are admin. It makes installs more complicated, but see 2 for why this will not be necessary. 2. Original suggestion talked about looking up at HKCU, and then (if not found) at HKEY_LOCAL_MACHINE 3. I doubt the same machine will require BOTH service and per-user installation Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Relocatable installs
Bruce Momjian wrote: Comments? What's wrong with the way it's done by everybody else? Have hardcoded paths (determined at configure time), and allow override using a config file. Have a command line option for saying where the config file should be. For Windows, replace config file with Registry. That is usually hardcoded for (depending on whether you want it changeable per-user) HKEY_LOCAL_MACHINE\Software\company name\product name (replace HKLM with HKEY_LOCAL_USER if you want per-user config). Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Relocatable installs
Bruce Momjian wrote: Shachar Shemesh wrote: Bruce Momjian wrote: Comments? What's wrong with the way it's done by everybody else? Have hardcoded paths (determined at configure time), and allow override using a config file. Have a command line option for saying where the config file should be. Where do we put the config file so it can be found? We can't assume root privilege to write into /etc. Put it in /etc (or wherever else user said in configure). Allow a command line parameter that says run the server with this config file instead. User can place override config file wherever she damn wishes. For Windows, replace config file with Registry. That is usually hardcoded for (depending on whether you want it changeable per-user) HKEY_LOCAL_MACHINE\Software\company name\product name (replace HKLM with HKEY_LOCAL_USER if you want per-user config). Doesn't registry require admin privilege on the Windows box? HKLM does. HKCU doesn't. Actually, HKLM often doesn't either, but that's a rant I won't start right now. Again, you can use command-line arguments to override, if you want. Alternatively, Postgres can look at HKCU, and if not found there, at HKLM. This means that there can be a system-wide default, overrideable per user by each user. The only reason I'm suggesting this (as it is unlikely that for production installs more than one user will be running postgres) is that editing user keys for other users is a task I'm not sure many admins know how to do. It requires the use of the not-well-known regedt32 rather than regedit, and involves loading the correct file into the registry hive. As such, placing stuff in HKLM allows admins to easilly edit it. If that is not an issue, I'd say place it at HKCU. Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Multiple selects returned from a single stored procedure
Joe Conway wrote: Shachar Shemesh wrote: I guess what I would like to suggest is for the thus far unused select command in PLPGSQL to be used, in some way, to return values outside the scope of the strict returns context. I guess out variables will also be nice, but that's besides the point. This has come up before (search the archives). I did. Found people who asked about it, but didn't find an actual answer. I think the answer is to implement actual stored procedures (as opposed to functions, which is what we now have). A stored procedure call, per SQL99/2003 would look something like: call sp_my_stored_proc(); (which in MSSQL looks like exec sp_my_stored_proc()) The current docs say, at least from within pgplsql, that call foo translates to select * from foo. psql doesn't seem to carry a call command at all. From PgOleDb, I just do select * from foo. The difference between this and an SRF is that the stored procedure cannot be used in a FROM clause, and therefore cannot be joined with other data or filtered with WHERE criteria. I don't see that as a problem. But that fact also means that we should be able to deal with projecting multiple heterogenous result sets, and the structure of the sets does not need to be known in advance. That would require some way of actually returning the results, wouldn't it? return a rowset, please let me know. Best I came up with so far was to create a temporary table for the out vars or the selects. I can then rig the OLE DB to make it look as if the function returned that. If anyone has any ideas on how to both modify called parameters, and I wonder if you could write an SRF that returns setof refcursor, and then expand the cursors one-by-one in the OLE DB layer. Yes, that seems to be the direction to go. THANKS! I'll see how easy it will be to implement in OLE DB, but returning setof refcursor certainly allows me to return several rowsets with different column info. I have several ways I can take this solution, and I would like your opinion: 1. Simply assume that if a command returned a single column of refcursors, that it meant to return several rowsets, and implement the corresponding OLE DB interface. 2. Require that the cursors be named a certain way, according to their intended usage. If the command returned cursors named unnamed portal 9, to just treat it as is, while if it returned a cursor named MultiResult1, treat it as above? The advantage of 2 is that it allows me to simulate out variables. If the cursor is called outputvars, I direct it to the output variables interface. The disadvantage is that I'm not sure what to do if only some of the rows in the result are named MultiResult. Whatever method I'll use, I may have to start a transaction for the purpose of the command, if we were not already in one. Otherwise, the ref-cursors are just useless strings. HTH, Joe Thanks, Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Multiple selects returned from a single stored procedure
is allocating quite a bit of memory on the client side. Reserving such an amount on the server side seems a little too much. Just out of curiosity, how do I manually destroy the cursor when it's no longer needed? Just do close refcursor? Also, does this copy take place when the table is changed, or as soon as the transaction ends? If the former, it may not matter. In any case, my original concern was that I'll be changing semantics by opening another transaction. Upon retrospect, however, that's precisely what PostgreSQL is doing if one is not active. Assuming the OLE DB user uses the Transaction interface, and does not send a begin command, I'm capable of knowing whether I'm inside a transaction or not. Joe Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] Multiple selects returned from a single stored procedure
Hi list, I'm in the process of porting an existing MS-SQL database to PostgreSQL. The application uses OLE DB (and that's why I'm writing the OLE DB for Postgresql). One of the requirements is that we will introduce as little changes to the application. It has to be able to work with both databases, as well as Access (which is not really a database). Now the question: MS-SQL has the capacity for both out variables from stored procedures, as well as running several selects inside the procedures, and then giving the results for all selects to the caller. Fortunetly for me, that specific application doesn't run more than one select per stored procedure. The way I handled out variables so far was to have the function return a compound type, with the variables as rows. With embedded selects, however, this will no longer work. I guess what I would like to suggest is for the thus far unused select command in PLPGSQL to be used, in some way, to return values outside the scope of the strict returns context. I guess out variables will also be nice, but that's besides the point. If anyone has any ideas on how to both modify called parameters, and return a rowset, please let me know. Best I came up with so far was to create a temporary table for the out vars or the selects. I can then rig the OLE DB to make it look as if the function returned that. Shachar -- Shachar Shemesh Lingnu Open Systems Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Bringing PostgreSQL torwards the standard regarding
scott.marlowe wrote: I think the answer to all of this would require a lot of code being touched to either make it case fold, costing performance, or the replacement of the default lower cased catalog with upper cased catalog. I'm not the one to decide, but it seems to me that this is not a good time to enter such a major change, and may never be. How terrible will it be if the system is per server (i.e. - one given to initdb?). A more comprehensive solution, one which allowed switching from upper folding to lower folding to no folding, to case insensitive, or some subset of those possibilities results in a: slower backend performance, due to folding case for system catalogs b: touching a helluva lot of backend code to make it possible to fold up or down. Well, if it's only the shared catalogs that are affected, this may not be such a big problem (very hopeful tone?) I'm not 100% sure on this all, but that seems to be the point Tom and I came to in our discussion, and neither of the two solutions seemed very good at the time. Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] License question
Hi Haim, Is the PostgreSQL license (http://www.postgresql.org/licence.html) LGPL compatible? I have adapted some code (for jdate manipulation) into the OLE DB project (LGPL). I have copied over the copyright notice, but now I'm thinking that this may not be enough. On the front page of the site (http://www.postgresql.org/) it says *Licence http://www.postgresql.org/licence.html* PostgreSQL is distributed under the flexible BSD licence. but the license is not the BSD license proper. It seems close, though not identical, to the X11 license (http://www.x.org/Downloads_terms.html), which the FSF define as GPL compatible (http://www.fsf.org/licenses/license-list.html). Yet, the X11 license requires an inclusion of a certain paragraph, which is not mandated by the LGPL, and which gives permissions not granted by the GPL. I'm confused. -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [pgsql-advocacy] Do we prefer software that works or software
Josh Berkus wrote: Shachar, Now, I'm intending to do the best I can on my end. This does have a pretty heavy cost. It means that the OLE DB driver will parse in details each query, and perform replacements on the query text. This is bug prone, difficult, hurts performance, and just plain wrong from a software design perspective. The current drift of wind, however, means that the PostgreSQL steering commite seems to prefer having a lesser quality driver to seeing ugly uppercase. Hey, now wait a minute. As far as I can tell, you've heard only from Tom Lane on the steering committee (I may have missed some, though, I've been sick) Exactly. Of the people I heard from, the wind was against. Unless the 5 of us take a vote, Tom Lane speaks for Tom Lane, not for Core.Also, usually this list or Patches determines by consensus what gets in; the Core only gets involved in very unusual cases. That's why we are holding an open thread on the how in hackers. I'm assuming that once the how is sufficiently resolved, and the implications understood, everyone can make a better decision on the do we at all. Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Bringing PostgreSQL torwards the standard regarding case folding
Josh Berkus wrote: I also didn't follow the discussion of why a client-side implementation was technically impossible; this seems like the most obvious course to me, and to have *considerable* benefit.It's also consistent with our other statement variables, such as datestyle, which are all client-side, per-session settings. But they are not client side, are they? The date is formatted by the server. The client is simply receiving whatever datestyle itselected. That is, assuming I understand the behaviour correctly. I would catagorize it as a server side per-session configuration. If that's what you mean, we thought it was too insane because it doesn't explain how you are supposed to handle all the catalog and other stuff where you are implictly assuming quoting is not necessary. I'l reiterate the example. Do you really expect to have to write select upper(field) from table and should it be upper or UPPER? A server-side implementation would possibly reqire touching every single source code file in Postgres, something that would justify a lot of effort to avoid. I think the concensus was that the runtime part was aprox. four lines where the case folding currently takes place. Obviously, you would have to get a var, and propogate that var to that place, but not actually change program flow. Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Bringing PostgreSQL torwards the standard regarding case folding
Josh Berkus wrote: Shachar, I think the concensus was that the runtime part was aprox. four lines where the case folding currently takes place. Obviously, you would have to get a var, and propogate that var to that place, but not actually change program flow. That's only if you ignore the system catalogs entirely, which maybe you're prepared to do. If you want to change case folding for the system catalogs, though, you'll need to update code in thousands of places, becuase the back-end code is expecting lower-case identifiers IF you want per session setting, yes. If you want per database setting, you only need to worry about the shared catalogs If you want server wide setting, you just create the catalogs with the correct name, and get it over with. That's why I said that per-session setting seems like too much trouble. -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Bringing PostgreSQL torwards the standard regarding case folding
I'm opening a new thread, as the previous one was too nested, and contained too much emotions. I'll start by my understanding of a summary of the thread so far. The solution we are seeking would have to satisfy the following conditions: 1. Setting should be on a per-database level. A per-server option is not good enough, and a per-session option is too difficult to implement, with no apparent justifiable return. 2. Old applications already working with PG's lowercase folding should have an option to continue working unmodified for the foreseeable future. Solutions offered so far, and their status: 1. Tri-state. Folder upper, if failes, fold lower, if succeeds, warn. 2. Dual state. Fold lower or upper. Break if client is broken. 3. Create a database conversion tool to change existing case. Solution 1: As currently the case folding is performed disjointed from the actual use of the identifier, this solution requires quite a big amount of work. On the other hand, and on second thought, it's main benefit - gradual transition of applications from one to the other, is not really necessary once you declare the current behaviour as there to stay. Existing applications can simply choose to continue using whatever method they currently use. No need for migration. As such, I think we can simply state that tri-state migration path solution can be discarded for the time being. Solution 2: Obviously, this is the way to go. We will have a dabase attribute that states whether things are lower or upper case there. Solution 3: (unrelated to the above) There seems to be some ambiguity about how to handle the translation. Such a tool seems to require guessing which identifiers are accessed quoted, unquoted, or both. The last option, of course, will never work. We may need such a tool, for some projects may wish to transform from one way to the other. It seems to me, however, that such a tool can wait a little. Open issues: 1. What do we do with identifiers in template1 upon database creation? 2. How do we handle queries to tables belonging the catalog that are shared between databases? Observation: on a lowercase folding DB, any identifier that is not composed only of lowercase characters MUST can be automatically assumed to be accessed only through quoted mode. I therefor suggest the following path to a solution: 1. CreateDB will be able to create databases from either type. 2. template1 will be defined to be one or the other. For the sake of this discussion, let's assume it's lowercase (current situation) 3. CreateDB, upon being asked to create a new DB that has uppercase folding, will copy over template1, as it currently does. 4. While copying, it will check each identifier. If the identifier is not lowercase only, it is safe to copy it verbatim. 5. If the identifier is lowercase only, convert it to uppercase only. I am assuming here that the authors of the client code chose an uppercase-folding database, so they should know what they are doing when accessing stuff from the standard offering. 6. I'm not sure what are the shared tables from the catalog. I don't think it so unreasonable to ask anyone doing catalog work to assume that catalog entries are case-sensitive. As such, maybe it's best to just leave the data as is. 7. Column headers, however, will have to have a solution. A point still open in current design. I'm hoping this summary helps in furthering the discussion. -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Bringing PostgreSQL torwards the standard regarding
Rod Taylor wrote: 5. If the identifier is lowercase only, convert it to uppercase only. I am assuming here that the authors of the client code chose an uppercase-folding database, so they should know what they are doing when accessing stuff from the standard offering. You've just broken one of my databases. In one project I quote nearly everything and do so in lower case only. This was done to ensure portability between PostgreSQL, Oracle, etc. -- but with my preference of lower case names. I'm not sure you understood me. First, if we don't convert lower-upper, how can anyone expect the following query to work: select lower(id) from table; Even if you quote everything, you'd still probably have: select lower(id) from table; Noone can expect you to do: select lower(id) from table; The problem is that lower is defined in template1. If we don't uppercase it when we create the database, the above won't work. Then again, I'm fairly sure that the identifiers you placed as lowercase in your database are not defined by template1. In short, I don't think this suggestion broke your database. Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Do we prefer software that works or software that looks good?
Stephan Szabo wrote: [ Tom, we know your opinion on the first part of the next paragraph, so you don't need to reply to that part. ;) ] Are we going to get rid of the current behavior entirely? I doubt that will be a good idea. You want to let applications created for previous versions of PostgreSQL continue to work. The idea, I think, is to have either a DB wide, or a session wide, option to have it either way. We may have to create a DB conversion tool, that converts a DB from one way to the other (and changes the case of functions, along the way). If so, how are we going to handle issues like current databases with names like foo and FOO (and what if the name was given as foo)? I think these are really rare. The conversion tool can warn about these cases. If not, when can one set the folding options and how do we (in the long term) make the database work properly in both settings. I don't think having the same DB work in both folding options is really a big issue. Having two databases on the same server, one this way and one the other is, however. You don't want to install two database servers, merely because you have two applications developed for two different PG versions. Things like don't worry about the catalog entries don't fly when your standard functions are defined and looked up there. Answer above. Depending on the answers to the above, we need to think about things like the transitional plans put forth. Do these plans actually help transition things. I think they do. The idea is to be as complaining and as verbose during transition as possible. Ideally, if some breakpoint can be triggered each time a double lookup takes place (thus knowing that the client app is calling the wrong way), this will allow converting apps in almost no time at all. The fold up and down compare one then the other on a failure of the first may be fairly invasive changes, In what way invasive? still has problems when quotes are used inconsistently The main issue, as far as I'm concerned, is not with PG apps that need to be ported to the new scheme. I don't have any qualm with never deprecating the lowercase folding. This, of course, puts a burden on utilities that work as infrastructure to always quote or always not-quote (depending on exact semantics), but that, I believe, is solveable. My problem is with applications written for other, more standard complient, databases, and with porting these into PG. As such, if the app uses inconsistent quoting, it today relies on uppercase folding, and will not have any problem. and can also silently change behavior from old versions (on that database mentioned above, what does select * from foo do, is it the same as before?). These may or may not be huge issues and it may or may not be easily solvable, but these things need to be figured out IMHO before something can be considered a solution. I agree. It's just that I don't think this is a big issue, given the fact that I don't think we intend to deprecate the lowercase folding any time soon. Shachar Remove advocacy from the CC. I don't think it's related there any more. -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Do we prefer software that works or software that looks good?
Robert Treat wrote: On Saturday 24 April 2004 01:23, Shachar Shemesh wrote: Tom Lane wrote: PS: I resisted the temptation to SET THIS MESSAGE IN ALL UPPER CASE to make the point about readability. But if you want to argue the point with me, I'll be happy to do that for the rest of the thread. Yes, it's a well known rhetoric technique. Take whatever argument your opponent say, and exagerate it to an absurd. Kind of like changing the subject line of a thread to imply your side of the argument is the one that has technical merit and the other side is being petty and/or frivolous? It is my understanding that the discussion with Tom was 100% about the question in the subject line. There is no question that the SQL standard dictates that unquoted identifiers should be folded to uppercase. There is no question (not from me) that upper case is ugly. The only question is whether we should prefer standard to asthetic. Anyone who has studied software useability will know that uppercase should, in general, be avoided as it hurts readability. You convinced me! let's change the SQL standard. It isn't about looking pretty, it's about being more usable. Robert Treat Ok. I'm willing to change the subject to are hurting eyes due to uppercase preferable to changing lots of code when migrating to PG from other database due to standard incomplience, if it would make you feel better. The point is that I am not against lower case, or pro uppercase. I HATE uppercase. I do think, however, that standards should be followed. The question is, when all is said and done, which is more useable. A DB that presents unquoted identifiers as uppercase, or one that allows easier migration of client apps from other DBs. I'll also mention that if asthetic/readability is all that bothers you, we can add a flag to psql that displays all caps as lowercase. Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] The case for preserving case.
Hi EMF, emf wrote: Hello, postgresql hackers. All of this breaks when I start to use postgresql, because all of the attributes become lowercased. Well, I'm afraid that something will have to change in the app. Your email arrives while we are in the middle of a small war around the subject of case changing, but none of the options going around at the moment will match your request. The two options considered are leaving things as they are (con: non standard complient) or folding to uppercase (con: ugly and requires transition, unfortunetly, in this order). Fixing this problem involves one of three things: 1.) rewriting all the code to have lowercased identifiers. This is effectively renaming everything, as long camel case attributes become much harder to read when they're lowercased. This also changes the clients' preferred coding standard. Ok, so don't do it. 2.) using double quotes around all identifiers in sql statements. As you're probably aware, there's no string format in PHP that lets you write double quote marks unescaped (and do variable substitution), so this involves rewriting hundreds of lines and imposing ongoing overhead for every SQL query. But is consistant with both SQL standard, and with current MySQL setup. This will also make porting the app to further database, such as commercial ones, easier. I think that is the proper way or you to go. 3.) escaping 4 lines in src/backend/parser/scansup.c , where identifiers are lowercased. You can do that for your own postgres installation, if you really like. I'm not a member of the steering commitee, but I doubt you'll manage to get such a patch accepted. Maybe as a per-session setting I understand that the reason for lowercasing is because odbc connections, etc expect case insensitivity, but the current behaviour isn't an SQL standard Neither is yours, though. If we are going to change it, we might as well change it to the standard, don't you think? nor is it really case insensitivity. It's as close as you can get without losing the benefits of doing hash lookups. I would love case insensitivity with case preservation, but since that evidently is a more complicated option, I would like to know how I can formulate the 'case preserving' option in a way to make it palatable for inclusion. Can't help you there, unless you want to compile Postgres yourself. Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Do we prefer software that works or software that looks good?
Robert Treat wrote: IMHO apps that apply quoted identifiers willy nilly are busted anyway, Not really. Sometimes the app itself will be very consistent, never applying quotes, but an underlying driver will always apply quotes. The result is a mixed behaviour. There is nothing you or me can do about that. Notice that in the above case, neither app nor driver are violating their mandate, and both are well within their right to do so. So long as the behaviour is regulated by a standard, there is nothing you and I can say against such practices. Oh well... let's see if we can find a way to support both... You are welcome to join the other leg of this thread, then. That one is not CCed to advocacy, as it is 100% technical. Robert Treat Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] What can we learn from MySQL?
Bruce Momjian wrote: Here is a blog about a recent MySQL conference with title, Why MySQL Grew So Fast: http://www.oreillynet.com/pub/wlg/4715 and a a Slashdot discussion about it: http://developers.slashdot.org/article.pl?sid=04/04/20/2229212mode=nestedtid=137tid=185tid=187tid=198 My question is, What can we learn from MySQL? I don't know there is anything, but I think it makes sense to ask the question. Questions I have are: o Are we marketing ourselves properly? o Are we focused enough on ease-of-use issues? o How do we position ourselves against a database that some say is good enough (MySQL), and another one that some say is too much (Oracle) o Are our priorities too technically driven? Do we care enough about interoperability? When I ask about non-standard complience of Pg (turning unquoted identifiers to lowercase instead of uppercase, violating the SQL standard, and requring an expensive rewrite of clients), and I get the answer uppercase is ugly, I think something is wrong. To be fair, I got a fair amount of legitimate problems with MIGRATING to standard compliency. I find these issues legitimate, though solveable. Getting a we prefer lowercase to the standard, however, means to me that even if I write a patch to start migration, I'm not likely to get it in. Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [pgsql-advocacy] [HACKERS] What can we learn from MySQL?
Stephan Szabo wrote: I've tried just changing the parser to unconditionally casefold to upper. First thing that happens is that initdb breaks. In addition, you have potential issues with comparisons against the catalog's versions of standard functions as such if you allow the case folding to be changed after the catalogs are setup. That's not the migration path I was thinking of. What I was thinking of was: 1. Have a setting, probably per-session. Per database works too. 2. Aside from the folder upper and folder lower, have a third option. This is fold upper, if fails, fold lower. If succeeds, issue a warning. This should allow programs that rely on the folding (such as initdb) to be debugged during the transition period. Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Do we prefer software that works or software that looks good?
Tom Lane wrote: Personally I don't think that this is a transitional issue and we will someday all be happy in upper-case-only-land. Upper-case-only sucks, by every known measure of readability, and I don't want to have to put up with a database that forces that 1960s-vintage-hardware mindset on me. And I was feeling apologetic that I was accusing without a base the good (and I'm not cynical about that last adjective) people of the PostgreSQL of making life more difficult for programmers just because they don't like the asthetics of something which an external standard dictates. I mean, sure, I understand the sentiment. I don't like seeing all-caps either. But allow me to give an allegory from another free software project, one where I am an actual active code contributer. Imagine that Alexandre Juliard, the benevolent dictator for the Wine project, would have had the same attitude. Each time someone would come around saying today function X calls function Y, and this breaks program Z. We need to reverse X and Y, he would reply with But it makes more asthetic/program design/whatever sense to do it the way we do it today. The result would be that Wine would never come to the point where it can run Word, IE and other prominant Windows only applications. The reality of things is that Wine, just like Postgres, work by an external standard. Wine's standard is more strict, less documented, and more broad. However, like it or not, the more you deviate from the standard, the more you require people who want to use your technology to adapt to whatever it is that you do. This doesn't make sense on any level. So what I'm holding out for is a design that lets me continue to see the current behavior if I set a GUC variable that says that's what I want. This seems possible (not easy, but possible) if we are willing to require the choice to be made at compile time ... but that sounds too restrictive to satisfy anybody ... what we need is a design that supports such a choice per-session, and I dunno how to do that. In other words, you are going to reject the simpler solutions that treat this as a transition problem, because of asthetic issue? Not even program design issue, mind you. Sounds strange to me, and also pretty much guarentees that this will never happen. That would be a shame. The reason this would be a shame is because postgres is the same reason this thread was CCed to advocacy to begin with. Databases form a pretty saturated field. If Postgres is to break forward, it needs a niche. The fully-featured databases role is taken (Oracle), and the free database role is taken (MySQL). Postgres CAN take the fuly featured free database niche, but that will need help. The time is ripe, however. The company we're doing my current OLE DB work for has contacted me about this, and they dictated Postgres (MySQL was not nearly enough). They still want to see proof of concept working, but that's my job. However, I'm afraid they might give up if things become too complicated to port. Under such circumstances, every little help Postgres can give may mean the difference between breaking through and staying behind. I really wouldn't like to see such an important help break merely because Tom Lane doesn't like to see uppercase on his database tables list. Now, I'm intending to do the best I can on my end. This does have a pretty heavy cost. It means that the OLE DB driver will parse in details each query, and perform replacements on the query text. This is bug prone, difficult, hurts performance, and just plain wrong from a software design perspective. The current drift of wind, however, means that the PostgreSQL steering commite seems to prefer having a lesser quality driver to seeing ugly uppercase. regards, tom lane PS: I resisted the temptation to SET THIS MESSAGE IN ALL UPPER CASE to make the point about readability. But if you want to argue the point with me, I'll be happy to do that for the rest of the thread. Yes, it's a well known rhetoric technique. Take whatever argument your opponent say, and exagerate it to an absurd. Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] License question
Hi all, Who can give an authorative answer regarding distributing PostgreSQL under a different license? In particular, the front page claims that PostgreSQL is under the BSD license. The problem is that there are two. The four clause license, which is not GPL compatible, and the three clause, which is. Then again, the license itself is neither three not four clause. It is somewhat similar to the X11 license, though not identical. The problem is this: I'm using some code adapted from the PostgreSQL sources in the OLE DB provider, which is LGPL. I am not sure whether the PostgreSQL license is LGPL compatible, or what I need to do in order to use it. I have already placed copyright notices copied from the file I copied the actual code from, but do I need to do anything else? Is it at all possible to do this relicensing? Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] License question
Tom Lane wrote: Shachar Shemesh [EMAIL PROTECTED] writes: In particular, the front page claims that PostgreSQL is under the BSD license. The problem is that there are two. We use the one shown in the COPYRIGHT file in the top directory of the source tree, which is also available for your reading pleasure by clicking on the license link on that same front page. regards, tom lane Ok, let me explain the issue in a finer point. The license given in the web link you mention seems to mandate all related work to be under the same license, which is nowhere near what BSD means. In particular, this puts some doubt on whether I can use the code in an LGPL project. Can anyone shed more light on this point for me? Am I misreading something? If it is possible to put code into an LGPL project, what is the requirement? -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] valgrind errors
Tom Lane wrote: Neil Conway [EMAIL PROTECTED] writes: Any thoughts on what could be causing these errors? I suspect valgrind is complaining because XLogInsert is memcpy'ing a struct that has allocation padding in it. Which of course is a bogus complaint ... As far as I remember (couldn't find modern documentation on the matter) Valgrind is resitant to this problem. When a block of memory is copied, the initialized/uninitialized status is copied along. It only complains when an actual operation is performed using uninitialized memory. This was developed for the explicit reason of avoiding the problem you describe. Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] valgrind errors
Shachar Shemesh wrote: Tom Lane wrote: I suspect valgrind is complaining because XLogInsert is memcpy'ing a struct that has allocation padding in it. Which of course is a bogus complaint ... As far as I remember (couldn't find modern documentation on the matter) Valgrind is resitant to this problem. When a block of memory is copied, the initialized/uninitialized status is copied along. It only complains when an actual operation is performed using uninitialized memory. This was developed for the explicit reason of avoiding the problem you describe. Shachar Found it: http://developer.kde.org/~sewardj/docs-2.0.0/mc_main.html, section 3.3.2 It is important to understand that your program can copy around junk (uninitialised) data to its heart's content. Memcheck observes this and keeps track of the data, but does not complain. A complaint is issued only when your program attempts to make use of uninitialised data. What IS possible, however, is that there is a bug in one of the underlying libraries. -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] License question
Neil Conway wrote: On Thu, 2004-04-22 at 09:19, Shachar Shemesh wrote: The license given in the web link you mention seems to mandate all related work to be under the same license, which is nowhere near what BSD means. What license text do you think implies this? -Neil provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies. I read that to mean that all copies must have the same license. -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] License question
Peter Eisentraut wrote: Shachar Shemesh wrote: Can anyone shed more light on this point for me? Am I misreading something? If it is possible to put code into an LGPL project, what is the requirement? You have to display the PostgreSQL license text in the source code or the binary, depending on what you ship. A strict interpretation would say that considering the combined work, this is an additional requirement on top of the [L]GPL, so it is not allowed by the terms of the [L]GPL. (But it is allowed by the terms of the PostgreSQL license.) A looser interpretation would say that what the PostgreSQL license text says is essentially a subset of what the [L]GPL already says anyway, so adding the requirements of the PostgreSQL license on top of it does not actually impose additional requirements, so there is no problem. Which one of these interpretations you believe is between you and the copyright holders of that LGPL'ed work, but the PostgreSQL copyright holders don't care. Unfortunetly, I'm the only LGPL copyright holder in this case. It's unfortunate because it means noone else has joined in on this project yet (OLE DB provider). I'll be more than happy to accept your explanation (and it does fit in with FSF's assertation that the X11 license is GPL compatible). The only paragraph that bothered me to begin with was the one claiming I need to copy verbatim the license. If all I am required to do is to make sure the license is ENFORCED by all of my derived work, then yes, the LGPL does that and I'm home free. I still think you should change the text on the front page to read, at the very least, PostgreSQL is distributed under a flexible X11 like license. BSD is too misleading, and most people know the X11 license by now. Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] valgrind errors
Min Xu (Hsu) wrote: I am confused by how valgrind define make use of data? Isn't copy data a type of make use? I mean, if valgrind checks if the data was used as inputs of memcpy(), it is fine. But if user uses his own memory_copy(), which loads the data into register, as if the data is going to be used in some useful computation, and then copy the register value to some other memory location to finish the copy (yeah, this IS slow), then valgrind is likely to be confused too. It may think the data is used. I guess all I am saying is that valgrind _can_ still make mistakes about it. -Min If I understand correctly, a data is defined to be used when anything other than copying is done on it. Arithmetic operations, branches, etc. will trigger the error. If you copy the data by adding and then subtracting a constant from it, valgrind will complain. If all you do (as in your example) is copy it around, and then copy it some more, it will not. Yes, it does keep uninitialized bits over your registers. Brrr. Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] License question
Tom Lane wrote: No, that says that you can't remove the copyright notice from files that have it. It doesn't say that nearby files have to have the same license. (Compare to the GPL, which *does* say that.) The bottom line here is that you cannot relicense code you didn't write; this is generally true no matter what license it is distributed under. No it isn't. If I write code under the LGPL, for example, all you have to do in order to relicense it is make sure you live up to all of my requirements. In particular, this means that you CAN relicense it as GPL, without asking for my permission. Distributing it as GPL makes sure all of my restrictions are met. If I relicense this code as LGPL, however, I cannot guarentee that all of my derived work will have the banners (the LGPL does guarentee that the copyright notice stay). Hence, I read it as you cannot use this code in an LGPL project. You can take some Postgres pieces and use them in a project with a different overall license, but those pieces are still under BSD license. But that's not the BSD license. regards, tom lane But that, in turn, means I cannot put them in an LGPL licensed project (or in a proprietary one, but that's not my problem). The LGPL requires that all files under the same project be under the LGPL. The BSD license, in contrast to PostgreSQL's, does NOT require me to copy license related texts around, only the copyrights themselves. It does pose certain restrictions on what I am allowed to do with the copyrights, but any modern free software license (GPL included) require that you keep the copyright notices around Now, I'm not trying to heal the world. It's enough to me that the current copyright owners give me permissions to use the code under the LGPL license. I am saying that calling the PostgreSQL license BSD license is misleading. I'll also mention that I am, very likely, wrong in my interpretation of the license. The PostgreSQL license is very similar to the X11 license (http://www.x.org/Downloads_terms.html), which is interpreted by the FSF to be GPL compatible (http://www.fsf.org/licenses/license-list.html#X11License). This means I'm defnitely missing something here. What, however? Oh, or is the license in my link the NEW X11 license, known to be non-GPL compatible? -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] valgrind errors
Tom Lane wrote: ==29929== Syscall param write(buf) contains uninitialised or unaddressable byte(s) The original pad bytes may be fairly far removed from the point of the error ... an example is that I was able to make one XLogInsert complaint go away by changing palloc to palloc0 at tupdesc.c line 413 (in TupleDescInitEntry), which is several memcpy's removed from the data that gets passed to XLogInsert. Anything asking valgrind to give more stack output might help? valgrind's habit of propagating undef'ness through copies isn't real helpful here. Well, considering the amount of false-positives you would get if you didn't... If I understand this correctly, that was a real bug there, wasn't it? BTW, valgrind's report about size 4 is actively misleading, because the only part of that struct that TupleDescInitEntry isn't careful to set explicitly is a one-byte pad between attislocal and attinhcount. You might want to report that to their bugs list. My browsing the docs just now leads me to believe valgrind is, generally, aware that only parts of a word can be uninitialized. You can even set it to report it at the point where uninitialized and initialized data are merged into a single operation. In fact, that may help with getting the errors closer to the place where the actual problem resides. Then again, it may cause it to generate way more false positives. -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] License question
Tom Lane wrote: Jeff Davis [EMAIL PROTECTED] writes: Also, can you license code at all if it isn't yours? I would assume you would have to make changes and license the changes you made, and distribute it along with the postgresql-licensed code. You can't relicense code you don't own Sure you can. (if Shachar thinks differently I suggest he talk to a lawyer). I have. And one who specializes in free software licenses, at that. Let's give an example which is simpler, and therefor may make the case a little clearer. Supposed you painted a picture and give out electronic versions of it (you have the copyright). You allow everyone to redistribute the picture, so long as the general tone of the background color remains blue (license, pretty permissive). Now let's suppose I take your picture and make several modifications on it, but none that violates your license (i.e. - the background is still blueish). Both of us have copyright over the new work, but I may only distribute it under a license that makes sure that the restrictions I received it under are met. This means I cannot public domain the picture. If I do, I am violating your copyright over the picture, as I can no longer guarentee that the background remains blue. In that sense, I cannot relicense the picture. This is the case even if my public domain copies still have a blue background, as I have now given people permission to change a work of art for which you hold a copyright, outside of the permission you have given either them or me. One obvious solution is to redistribute it under the original license - do whatever you like with it, so long as the background remains blue. That is, however, not the only one. I can also change the license to whatever I want, so long as I can assure you that your original requirements are met with any work derived from the new license. For example, it is perfectly ok to say you can redistribute this picture, but you are not allowed to change anything about the color scheme. The new requirement encapsulates the original requirement, and your license is therefor not violated. I have, in fact, relicensed your work. This applies even if I did not make any change to your original work at all. So long as I can show that all terms of your original license are met if people follow my new license, you have no quarrel with me. You requested that people don't change the background color theme. They can't if they can't change the color theme at all. Now, obviously, if people can get a picture to me under a certain restrictive license, and they can get the exact same picture from you under a more permissive license, they are unlikely to get the picture from me. That is, however, market forces, not copyright licensing. If you accept that, just replace blue with free. (at least, not from the BSD side --- see below). When working with GPL or LGPL base code you are constrained to use the same license as the base. You still own your own work, but you can't redistribute the combined work unless you use the same license. I don't think you could reasonably choose GPL as the license for your mods/additions, since by my reading of the GPL it would forbid you from redistributing a combined work that's not all GPL. But the (new, not old) BSD license is GPL compatible, which means that I can relicense your work released under the 3 clause BSD as GPL. The original 4 clause BSD is not GPL compatible, which means I cannot. Comparing restrictions, and whether license X can guarentee that all the restrictions imposed by license Y are still met, is what stands at the core of saying license X is compatible with license Y. But you could choose LGPL, or any of the other standard free licenses. Hmm, not really. As I'm talking about putting code from PostgreSQL into the OLE DB provider proper, the linking clause of the LGPL does not cover this. Let's make it clear - as the LGPL code is all mine, noone can do or say anything to me if I mix it with non-relicensed BSD code. I am not violating the BSD license, because it's still BSD, and the person doing the LGPL license violation is me, the copyright holder, so noone can have any qualm with this (it's the copyright holder that has to sue, and I won't sue myself). Doing this does mean that noone except me can touch this project without removing the BSD code (or relicensing, but I'm assuming here I accept your claim that I cannot relicense), except me. Creating free software with conflicting licenses code is legal but highly recommended against. regards, tom lane Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Remove MySQL Tools from Source?
Tom Lane wrote: These tools are a not insignificant part of our Plan for World Domination ;-) so it would be good if somebody stepped up to the plate and volunteered to take care of 'em. Anybody? Which brings me to another question I have a bunch of perl scripts, as well as one user-defined type, for porting from SQL Server. Where should I place these? Inside the PG source seemswrong. Then again, gborg does not seem to be accepting new projects at the moment. I can put them on sourceforge/berlios etc, and ask for a link, if you like. Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] postgres on windows page update
Hi, I'm wondering who's doing the PostgreSQL on Windows page (http://techdocs.postgresql.org/guides/Windows). I wanted to offer to add the OleDB project to the Connecting your Windows applications to PostgreSQL http://techdocs.postgresql.org/guides/PostgreSQL section. Current status is extremely preliminary, though somewhat working. It is being actively developed, however, so I'm hoping to see rapid improvements there. Shachar -- Shachar Shemesh Lingnu Open Systems Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] tinyint and type problems
Joe Conway wrote: Shachar Shemesh wrote: I'll stress again - I don't mind doing all the work associated with any once of the above choices. All I'm asking is that we agree on which one will be best for this project. As far as I'm concerned, Choice 2 involves the least amount of work, but I think Choice 1 will serve everyone better. Sorry for jumping into this thread so late, but if you don't really need arithmetic operators, index support, and casts to all other numeric types, why not use char? Joe On a totally unrelated issue - how are the quotes around char enforced? I have not seen anything special about it's definition. Hi Joe, Thanks for the offer. I actually tried to use char before embarking on the quest, but it didn't work out. While my specific client (and I realize that others do need it) don't need many of the above, they do need some of it. As such, the lack of ability to type insert into foo (char_col) values (5) meant I couldn't use it. In any case, I now have (already commited to the OLE DB sources) a solution to this problem. The provider has a list of special types it supports. When first establishing a connection to the database, I query the pg_type table for all of those types, and cache their OIDs. When I need to perform parsing, I look for all the OIDs I know in advance using a switch, and in the default clause, look for all the OIDs of the non preknown types. It's a reasonable solution, I think. I would still love it if the definitions for PG_GETARG_UINT8, PG_RETURN_UINT8 and DatumGetInt8 made it into the main tree, as I don't think there is any real reason to define them in my so. That aside, however, my specific problem is solved. I also renamed the type utinyint, to leave room for a signed 1 byte value if we decide to go that route. (http://gborg.postgresql.org/project/oledb/cvs/co.php/oledb/PgSess.cpp?r=1.1). As far as I'm concerned, the discussion just turned academic. I'll still love to add the missing feature to get this type in, as I do think it's generally useful. My implementation, however, currently works over a vanilla postgres without a problem. Shachar -- Shachar Shemesh Lingnu Open Systems Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] tinyint and type problems
Hi all, I have sent a patch to implement tinyint, an unsigned one byte integer, for MS SQL compatibility. The replies I got were under two categories. One was our type system is complicated enough, and the other was it should be signed and we should have a general unsigned keyword. Now, I'm not against neither simplifying the type system nor having a unsigned keyword. The thing is that between these two remarks, my patch was not applied and I don't know why. I need this new type because the OLE DB provider I'm writing (for a client) works in binary mode by-definition, which means that the client's software, currently designed to work with MS SQL, relies on certain table entries to be one byte wide. Tricks such as using char don't work, as it uses insert ... from select... , which fails because the numbers are not translateable to char. It also does explicit insert, which also will not work with char. I would really love to know what needs to be done in order to get a one byte type, either signed or not (not really important at this stage) into postgres. If this is impossible, would it be at least possible to reserve an OID for this type, and decide what it actually is later? If that would be possible, I can go on, in the mean time, with my development. The problem is that pglib really has no way of identifying the types except by OID. Shachar -- Shachar Shemesh Lingnu Open Systems Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Scalable postgresql using sys_epoll
Anthony_Barker wrote: IBM has rewritten their Domino database system to use the new sys_epoll call available in the Linux 2.6 kernel. Would Postgresql benefit from using this API? Is anyone looking at this? Anthony http://xminc.com/mt/ I'm not familiar enough with the postgres internals, but is using libevent (http://monkey.org/~provos/libevent/) an option? It uses state triggered, rather than edge triggered, interface, and it automatically selects the best API for the job (epoll, poll, select). I'm not sure whether it's available for all the platforms postgres is available for. Shachar -- Shachar Shemesh Lingnu Open Systems Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] one byte data type
Hi gang, Is there a datatype that means one byte? I'm importing a database from SQL Server, and some of the rows there are of this type. When I convert them to int2, I have discrepancies with the program that uses the database (OLE DB uses binary mode transfers, so the size of the variable is important beyond it's legal range). Shachar -- Shachar Shemesh Lingnu Open Systems Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] one byte data type
Tom Lane wrote: Shachar Shemesh [EMAIL PROTECTED] writes: Is there a datatype that means one byte? You might be able to use the char type (note the quotes). I am not sure how well it will cope with storing zeroes (nulls) though. regards, tom lane Hmm, this will also screw up textual queries. Not important for this particular case, but not a good general solution. What are the chances of adding such a type for 7.4.2? Shachar P.S. Notice how this message arrives directly as well, despite spam filters. -- Shachar Shemesh Lingnu Open Systems Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] OIDs, CTIDs, updateable cursors and friends
Dave Page wrote: -Original Message- From: Shachar Shemesh [mailto:[EMAIL PROTECTED] Sent: 18 February 2004 13:18 To: Hackers; PostgreSQL OLE DB development Subject: [HACKERS] OIDs, CTIDs, updateable cursors and friends Would adding OID to the rows returned by each Select call, and then doing update blah where oid=xxx when I'm requested to update the row sound like a reasonable stategy, in lieu of updateable cursors? Can anyone suggest a better way? Ignoring potential OID wraparound problems (which we do in pgAdmin) this should work, assuming there is an OID column. I would suggest trying the following methods in sequence: 1) Use the tables primary key. I would, except I'm not sure how many queries I would need in order to find what the primary key is. Also, what happens if the primary key is not a part of the fields returned by the query? 2) Use the OID (and check that only one record will be affected). That may work. Do a query for how many would be affected. Then again, I'm currently not inside a transaction. The plan was not to be inside a transaction unless I needed to. I'm not sure how safe this is to perform many queries. 3) Build a where clause based on all known original values (and check that only one record will be affected). Again - what happens when I'm not inside a transaction? 4) Fail with an appropriate error. 2 3 can potentially affect more than one record, but even Microsoft code runs into that problem from time to time and fails with an appropriate error message. In pgAdmin II we used to ask the user if they wanted to update all matching rows, but of course that is not appropriate in a driver. Regards, Dave. The doc mentions something about making the OID column unique. Would that not cause other problems? What happens if I define the OID field as unique, and I get a wraparound and an attempt to put a new field in with existing value? Would the OID skip to the next unique per table, or would the insert fail? Shachar -- Shachar Shemesh Lingnu Open Systems Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] OIDs, CTIDs, updateable cursors and friends
Dave Page wrote: -Original Message- From: Shachar Shemesh [mailto:[EMAIL PROTECTED] Sent: 18 February 2004 14:10 To: Dave Page Cc: Hackers; PostgreSQL OLE DB development Subject: Re: [HACKERS] OIDs, CTIDs, updateable cursors and friends I would, except I'm not sure how many queries I would need in order to find what the primary key is. Well this is the only safe way to update a specific record. To find the pkey, look for an index on the table in pg_index with indisprimary = true. The indkey column holds an array of pg_attribute.attnum's that are in the index iirc. I'll have a look at that. How would updateable cursors do it? By locking the row? Also, what happens if the primary key is not a part of the fields returned by the query? Add them as you proprosed to do with the OID, or fall back to the next method. ADO etc. normally fail to update rows if the programmer hasn't included a suitable key in the recordset. So, basically, I would not be able to update a table that has no primary key? 2) Use the OID (and check that only one record will be affected). That may work. Do a query for how many would be affected. Then again, I'm currently not inside a transaction. The plan was not to be inside a transaction unless I needed to. I'm not sure how safe this is to perform many queries. Should be perfectly safe. What happens if I check how many would be updated, and get 1 as a result. I then actually do it, but between asking and performing, someone added a second row that matches the criteria? 3) Build a where clause based on all known original values (and check that only one record will be affected). Again - what happens when I'm not inside a transaction? You might find a new row that wasn;t there before but is now, or vice versa. But what if someone else changes some of the known values of my row? The doc mentions something about making the OID column unique. Would that not cause other problems? What happens if I define the OID field as unique, and I get a wraparound and an attempt to put a new field in with existing value? Would the OID skip to the next unique per table, or would the insert fail? It is not the drivers place to mess with peoples schemas, but yes, it could cause an insert to fail following wraparound. Then it's not a good enough solution, even if the driver did have the prorogative to change the table. Regards, Dave. Ok, it seems to me there are several options here. 1. Find out which is the primary key for the table. What happens if the primary key is a multi-row thing? What happens if there is no primary key? 2. If I'm in a transaction, use OID for the insert after checking with a select that I'm only affecting one row. If I'm not in a transaction - perform the update in a generated transaction, and roll it back if there is more than one row affected. I like 1 better, frankly. Dillemas dillemas dillemas. -- Shachar Shemesh Lingnu Open Systems Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Tom lane - your email server is broken
Hi Tom, Sorry for sending this email through a public list. As you can, I think, understand, I am not able to send you mail directly. Whenever I try to send you mail directly, I get a bounce like so: The original message was received at Mon, 26 Jan 2004 02:41:59 +0200 from line102-130.adsl.actcom.co.il [192.117.102.130] - The following addresses had permanent fatal errors - [EMAIL PROTECTED] (reason: 550 5.0.0 Go away, spammer) - Transcript of session follows - ... while talking to sss.pgh.pa.us.: MAIL From:[EMAIL PROTECTED] 550 5.0.0 Go away, spammer 554 5.0.0 Service unavailable Now, I know that certain black holes black list certain addresses. I can even accept that somewhere, sometimes, someone sent spam through some server I'm currently using. As things stand, however, it is impossible for me to do anything about it. For me to be able to do something about it, I need at least the following details (and common sense dictates that they should be in the bounce I got): 1. What IP is black listed? 2. What spam blocking service is black listinging this IP? I would appretiate it if you could help me out with this, or at least forward this request to your mail admin. Many thanks, Shachar -- Shachar Shemesh Lingnu Open Systems Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Getting the results columns before execution
Hi all, From the OLE DB manual: The consumer can also get information about the columns of the rowset by calling IColumnsInfo::GetColumnInfo, which all providers must support, or IColumnsRowset::GetColumnsRowset, which providers may support, on the command. Before calling GetColumnInfo or GetColumnsRowset, the consumer must set the command text and, if the provider supports command preparation, prepare the command object. The problem - it appears there is no requirement to execute the command before asking about the rowset returned. Only setting the actual command and preparing it. This is a problem, as merely preparing a command is not enough to get the returned rows information. I'm wondering whether anyone on this list(s) have an idea how to solve this? I can start the command in a cursor, and not retrieve information until requested. I can also execute the command with where 0 appended to it. Neither solutions seem very practical or good performance wise. Shachar -- Shachar Shemesh Lingnu Open Systems Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Getting the results columns before execution
Tom Lane wrote: Shachar Shemesh [EMAIL PROTECTED] writes: ... This is a problem, as merely preparing a command is not enough to get the returned rows information. Sure it is, if you are using the V3 protocol (new in 7.4). See the Describe message types. regards, tom lane Are those exposed through the libpq interface? If not, is there a way to use the libpq interface for most operations, and only for the rest to use whatever it is you are suggesting I use (couldn't locate it in the docs, yet) Funnily enough, mandating protocol version 3 is not my problem at the moment. I may have to do so for a host of other reasons as well. I guess, if you want to use an earlier version of the database, you will have to make do with the ole db to ODBC interface. Shachar -- Shachar Shemesh Lingnu Open Systems Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] OLE DB driver
Robert Treat wrote: Can you fill us in on your efforts to contact the original project author? Sure thing. Here it is: The project is located at http://gborg.postgresql.org/project/oledb/projdisplay.php. It has released no code, shows no CVS activity, and seems rather empty. There are two tasks listed, without any status attached to them. There was one bug, which suggests that there used to be some code, but it's in submitted state, and I'm not sure what it relates to. The front page says please email me if you want to join. However, I could not locate the email address for mosiu, the administrator. Lastly, I used the form to ask to become a developer of this project (about a month ago). I have received no answer of any kind. As such, I reached the conclusion that this project is terminally dead, and that it's code is unrecoverable. If those efforts have failed and the code is no longer being maintained, we can contact the gborg maintainer about making you the admin for the project. I would love it if you contacted him, but I don't even have the code for the current project. The bug report seems to suggest that such a code exists, but I'm damned if I can find it. I don't think there is an official policy on this, but this seems like a reasonable request as long as your willing to either update the old code or leave it as it's own seperate module within the cvs tree. Like I said, there doesn't appear to be any old code to maintain. I have requested a new project on gborg (pgoledb), but getting the current name is just as good. If the current code is somehow recovered, I'll be more than glad to have a look at it. After all - I don't INSIST on writing from scratch. Robert Treat Shachar -- Shachar Shemesh Lingnu Open Systems Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] OLE DB driver
William ZHANG wrote: Shachar Shemesh [EMAIL PROTECTED] wrote:[EMAIL PROTECTED] Andreas Pflug wrote: I wonder if this could be implemented as a wrapper around libpq. This way, the OLEDB driver would benefit from a proven piece of software. Regards, Andreas That's what I'm doing. I'm not sure why other drivers didn't do that as well (maybe there was no native Windows port of libpq at the time?). AFAIK, rely on libpq is a fast way but not always convenient. Seems like more convinent than hacking the client/server protocol myself. I may come up to a wall later on, and then I may reconsider. OLEDB has many features borrowed from ODBC, and it is a substitute for ODBC. Microsoft is now making ODBC obsolete and advocating OLEDB instead. You'd better read pgodbc's source code, too. I did some of that, and did some ODBC hacking too (very little). The fact that ODBC uses it's own protocol implementation, I think, is a mistake. There are places where some intervention is going to be unavoidable, I'm afraid, but that has nothing to do with what protocol to use for communicating with the back end. If Postgres doesn't support writeable cursors, then it doesn't support writeable cursors. One has to emulate them. I will defenitely use some borrowing from pgsqlODBC (the main reason my OLE DB is LGPL was so I can do that). -- Shachar Shemesh Lingnu Open Systems Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] OLE DB driver
Andreas Pflug wrote: Sounds good. While I probably won't find time helping implementing the driver; I have an OLEDB app running that currently uses MSDASQL/psqlODBC. I'd certainly like testing your driver, and give feedback from that. That would be great. When do you expect first usable versions? That depends. I'm hoping to have a version that implements the basic (read - mandatory) interfaces later this week. A more broad version will take longer. I also wanted to ask the list's opinion about a gborg/sourceforge hosting. The current oledb project on gborg seems dead. Should I open another one? Shachar -- Shachar Shemesh Lingnu Open Systems Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] OLE DB driver
Andreas Pflug wrote: I wonder if this could be implemented as a wrapper around libpq. This way, the OLEDB driver would benefit from a proven piece of software. Regards, Andreas That's what I'm doing. I'm not sure why other drivers didn't do that as well (maybe there was no native Windows port of libpq at the time?). My reason is actually that I'm not a great postgres hacker, and I would rather rely as little as possible on internal structures and things that are likely to change in the future. Shachar -- Shachar Shemesh Lingnu Open Systems Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] OLE DB driver
Hi all, After unsuccessfully trying to join the current OLE DB project on gborg, I'm writing my own. So far I don't have anything too fancy (just trying to get over the initial shock of what OLE DB actually is). It currently does the basic infrastructure, but does not yet actually connect to Postgresql. If anyone else has written code, was contemplating writing code, has access to the gborg project, or is otherwise interested, please let me know. I'm hoping to be able to do some sort of preliminary release in about a week. Shachar -- Shachar Shemesh Lingnu Open Systems Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Why isn't DECLARE CURSOR ... FOR UPDATE supported?
Tom Lane wrote:
Rod Taylor [EMAIL PROTECTED] writes:
On Thu, 2003-12-18 at 10:20, Tom Lane wrote:
Is there any good reason for this restriction?
The help implies you can.
DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
CURSOR [ { WITH | WITHOUT } HOLD ] FOR query
[ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ]
Hmm. Actually that is describing the SQL spec's syntax for DECLARE
CURSOR, in which you can name specific *columns* not tables as being
updatable through the cursor. Now that I think about it, the error
check is probably there to catch anyone who writes FOR UPDATE OF
column expecting to get the SQL spec behavior.
I'm not sure whether anyone is planning to try to converge our notion of
FOR UPDATE with the spec's. If that is going to happen someday, it'd
probably be best not to introduce directly conflicting behavior into
DECLARE CURSOR. Oh well...
regards, tom lane
Do I understand from what you are saying that we are pretty close to
being able to perform write operations on cursors? Can we, in the mean
while, lock entire rows for that purpose?
I'm having a deployment of Postgresql where the application is using MFC
CRecordset. It appears, from superficial inspection, that it uses the
same type of cursor, whether it actually intends to update it or not. I
have also not found any convinent way in MFC to tell it which rows one
intends to update. As such, I suspect it is prepared to update them all
(disclaimer - I have not tested it myself, and may speak utter bullshit
here).
The problem is that, at the moment, the ODBC driver is emulating cursors
by using the OID field, and performing seperate queries per row. This
has two significant problems:
A. One cannot create a read-write cursor for views, as views do not have
an OID field.
B. The performance for fetching 30,000 rows is terrible.
I'm looking for a way to solve these issues (especially the second one).
I may have a solution inside the ODBC driver itself (better cursors
emulation - a performance/memory tradeoff), but I would really prefer a
true solution to the problem.
My question is this - how terrible will it be if we did not lock each
individual column, but instead locked entire rows (as Tom suggested in
the begining of this thread)?
Shachar
--
Shachar Shemesh
Open Source integration consulting
Home page resume - http://www.shemesh.biz/
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [HACKERS] [pgsql-advocacy] Not 7.5, but 8.0 ?
Dave Page wrote: Right, but not having the luxury of time travel (wasn't that removed in Postgres95? ;-) ) we can only go by what the majority think. We won't know if it's actually right unless we try it. We could run a survey saying 'would you use PostgreSQL on win32', but the chances are that the vast majority of potential win32 users would not visit the site to answer that until it became widely know that we do support win32, by which time of course it's all a bit moot. Unless of course, you have other stats that prove that win32 support is uninteresting to most people and potential users? Regards, Dave. I'm sorry if I'm being alow here - is there any problem with running a production server on cygwin's postgresql? Is the cygwin port of lesser quality, or otherwise inferior? I understand that the installation is a bit awkward for cygwin. I somehow don't see that as too much of a problem. As for usage - RedHat guidelines clearly state that OSI approved licensed programs will not be considered by them derived work of the cygwin dll (the one who's GPLness caused the original discussion). This, aside from the question of whether they have any claim on Posix utilities anyhow, or whether a commercial application using PGSQL should be considered derived work of it, mean to me that there is no problem in distributing a commercial app that uses Cygwin PostgreSQL. Shachar -- Shachar Shemesh Open Source integration consultant Home page resume - http://www.shemesh.biz/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [pgsql-advocacy] Not 7.5, but 8.0 ?
Shachar Shemesh wrote: I'm sorry if I'm being alow here alow-slow Just wanted to avoid confusion. -- Shachar Shemesh Open Source integration consultant Home page resume - http://www.shemesh.biz/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] bidirectional cursors on views
Hi all, When I try to open (using MFC) a non-forward only cursor on a view (i.e. - select * from viewname), I get an error of attribute ctid not found. There are some chances that this is an ODBC problem, but my research suggests that the dependancy on ctid is created by the database itself. Can anyone comment about this? Is this problem solveable? Shachar -- Shachar Shemesh Open Source integration consultant Home page resume - http://www.shemesh.biz/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] bidirectional cursors on views
Tom Lane wrote: Shachar Shemesh [EMAIL PROTECTED] writes: When I try to open (using MFC) a non-forward only cursor on a view (i.e. - select * from viewname), I get an error of attribute ctid not found. There are some chances that this is an ODBC problem, but my research suggests that the dependancy on ctid is created by the database itself. Your research is faulty --- it's an ODBC issue. Am I to understand that psql's nativ cursors support bidirectional movements on views? That is good news, actually. Can anyone comment about this? Is this problem solveable? You'd have better luck asking on pgsql-odbc, but my guess is that they depend on ctid as a (crude) form of unique row identification. That was my guess as well. I did ask at pgsql-odbc on Wendsday. When noone answered, I did the open source thing and tried to singstep the code. Thing is - I placed a breakpoint on all occurances of ctid in the ODBC driver's code, and none of those breakpoints were hit when the query that didn't go through was sent. That's the reason I arrived at the conclusion I did. If you can tell me for sure that such cursors are, in fact, supported by pgsql itself, then I'll go back to the odbc sources and, if necessary, implement this (not happy about those prospects - I don't know ODBC worth of manure). regards, tom lane Many thanks, Shachar -- Shachar Shemesh Open Source integration consultant Home page resume - http://www.shemesh.biz/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
