Re: [HACKERS] Win32 port
Hello Katie, Thursday, November 7, 2002, 7:08:20 PM, you wrote: KW Hi, all. KW I just wanted to give you an update on where my company (PeerDirect) is with KW regards to our native Windows port. KW We are planning on contributing the code for the native port sometime next KW month (in December). We would have liked to contribute it earlier, but our KW work schedules here didn't allow it. KW The state of the port is that it passes all postgres regression tests. It's KW been in BETA since early August with about 27 beta customers. The beta KW customers came from a post made to the cygwin mailing list KW (http://archives.postgresql.org/pgsql-cygwin/2002-08/msg00012.php). The KW BETA has proven successful and several problems were found and fixed. KW At this point you are welcome to run the latest version of this BETA. There KW is no installer, but there is a setenv.bat script to help setup the KW environment. The beta can be downloaded at: KW ftp://209.61.187.152/postgres/postgres_beta4.zip KW This version has its own catalog version which is slightly different than KW 7.2.1, so you'll need to do a pg_dump and pg_restore. If you find any KW issues, please don't mail me directly. Instead, please send emails to KW [EMAIL PROTECTED] Several people monitor this address and the KW issue will get resolved more quickly. KW There is still some cleanup work to do, such as migrating from 7.2.1 to 7.4, KW but my company is committed to contributing this port to the community by KW the end of the year. I have tried this version and it seems to work well on preliminary tests. Bruce, this probably is a good start for the port and I wonder if it worths having all that patching trouble you mentioned, specially when they offered to do it... I think you probably have a lot of work to do on other areas too... To Katie: does it compile with Mingw too ? - Best regards, Steve Howe mailto:howe;carcass.dhs.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Win32 port
Hello Bruce, Wednesday, November 6, 2002, 3:19:35 AM, you wrote: BM I have copies of Peer Direct's (Jan's company) port of PostgreSQL to BM Win32, and SRA's port to Win32, and permission to generate a merged BM patch that can be applied to 7.4. BM Now that 7.3 is almost complete, I am going to start work on that. I BM will post patches that deal with specific portability issues, like BM fork/exec and path separator handling, and once reviewed, apply them to BM the main CVS tree for 7.4. Just wondering, what compiler are they using ? Will it compile using Mingw ? - Best regards, Steve Howe mailto:howe;carcass.dhs.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Win32 port
Hello Bruce, Wednesday, November 6, 2002, 8:33:32 PM, you wrote: BM Steve Howe wrote: Hello Bruce, Wednesday, November 6, 2002, 3:19:35 AM, you wrote: BM I have copies of Peer Direct's (Jan's company) port of PostgreSQL to BM Win32, and SRA's port to Win32, and permission to generate a merged BM patch that can be applied to 7.4. BM Now that 7.3 is almost complete, I am going to start work on that. I BM will post patches that deal with specific portability issues, like BM fork/exec and path separator handling, and once reviewed, apply them to BM the main CVS tree for 7.4. Just wondering, what compiler are they using ? Will it compile using Mingw ? BM I think the two projects are using MS C++, but it would be nice for BM Mingw to work too. Or even (free) Borland C++ compiler, but I would be glad just to see it working with Mingw... It just makes no sense a free project like PostgreSQL to be compiled only with a commercial compiler. - Best regards, Steve Howe mailto:howe;carcass.dhs.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Datetime type dropped from v7.3 ?
Hello all, Just wondering if the datetime type was dropped on purpose from PostgreSQL 7.3 ? This is not an issue of course, I'll be using timestamp, but it's weird having that dropped. --- -- On PostgreSQL 7.2: --- howe=# select version(); version --- PostgreSQL 7.2.1 on i386-portbld-freebsd4.6.2, compiled by GCC 2.95.3 (1 row) howe=# create table a(b datetime); CREATE howe=# \d a Table a Column |Type | Modifiers +-+--- b | timestamp without time zone | --- -- On PostgreSQL 7.3: --- howe=# select version (); version - PostgreSQL 7.3b3 on i386-unknown-freebsd4.4, compiled by GCC 2.95.3 (1 row) howe=# create table a(b datetime); ERROR: Type datetime does not exist - Best regards, Steve Howe mailto:howe;carcass.dhs.org ---(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] Datetime type dropped from v7.3 ?
Hello Tom, Thursday, November 7, 2002, 1:17:00 AM, you wrote: TL Steve Howe [EMAIL PROTECTED] writes: Just wondering if the datetime type was dropped on purpose from PostgreSQL 7.3 ? TL Yes. Ad-hoc name translations in the parser create bogosities with TL respect to schemas --- I forget the details, but it was either drop TL datetime or make it a reserved keyword. Since datetime has been TL deprecated for a couple releases now, the choice seemed clear. Oh. ok. I'm glad it was intended. Thanks :) - Best regards, Steve Howe mailto:howe;carcass.dhs.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Win32 port
Hello Bruce, Thursday, November 7, 2002, 12:56:57 AM, you wrote: BM I have thrown out the idea and some felt that if we could get PITR and BM Win32, that would be enough for a release, even if we could get it done BM in a month or two. BM However, I see your point that releasing too often causes too many BM initdb's. BM What do others want, a regular 4-6 month cycle or a shorter one? The initdb will be needed, no matter the release time is a month or six, right ? There is no point in holding technology that is ready. Those who don't need the release, can wait for the next one and avoid the initdb. Also, as a snapshot, the Win32 version won't get properly tested as it would be as a regular release, specially because most of the developers here work on UNIX. The best field testing would be deploying a regular release to the hundreds of Win32 users that wait for years for the Win32 version... ... just my thoughts, of course. - Best regards, Steve Howe mailto:howe;carcass.dhs.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Cache lookup failed for relation 16905 ??
Hello all, I got a weird behavior testing PostgreSQL 7.3b3. The query below was run in a FreeBSD 4.4 system, on a fresh install and just I just had made an initb. No classes created at all. howe=# select p.oid, n.nspname, pg_get_userbyid(p.proowner), proname from pg_proc as p, pg_namespace as n where pg_table_is_visible(p.oid); ERROR: Cache lookup failed for relation 16905 ERROR: Cache lookup failed for relation 16905 What does that message mean ? there is not even such a relation in pg_class. Thanks. -- Best regards, Steve Howe mailto:howe;carcass.dhs.org ---(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] Cache lookup failed for relation 16905 ??
Hello Tom, Saturday, November 2, 2002, 11:26:56 AM, you wrote: TL Steve Howe [EMAIL PROTECTED] writes: I got a weird behavior testing PostgreSQL 7.3b3. The query below was run in a FreeBSD 4.4 system, on a fresh install and just I just had made an initb. No classes created at all. howe=# select p.oid, n.nspname, pg_get_userbyid(p.proowner), proname from pg_proc as p, pg_namespace as n where pg_table_is_visible(p.oid); ERROR: Cache lookup failed for relation 16905 ERROR: Cache lookup failed for relation 16905 TL I think you want pg_function_is_visible, not pg_table_is_visible. Oh, you are totally right, I got distracted late in the work. I wanted pg_function_is_visible(). TL However this does show that the foo_is_visible functions aren't reacting TL very nicely when given bad input: they all put out a Cache lookup TL failed message, rather than something more easily interpretable. Yes, it confused me, even if the mistake was mine. TL I'm not quite sure what should happen when foo_is_visible is called with TL an OID that is not the OID of any foo object; should it quietly return TL false, or should it raise a no such object error? My opinion is that if should just return false... one can test if the object exists easily, if he/she needs it (WHERE p.oid in (select oid from pg_proc)). However, this situation lead me into another issue. The new conversion functions (utf8_to_big5, iso_to_alt, etc.) appear as listed in every new scheme. Is this correct ? Shouldn't them be listed only in pg_catalog? How can I distinguish user-defined functions from catalog (pre-defined) functions ? - Best regards, Steve Howe mailto:howe;carcass.dhs.org ---(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] Cache lookup failed for relation 16905 ??
Hello Tom, Saturday, November 2, 2002, 5:17:29 PM, you wrote: TL Steve Howe [EMAIL PROTECTED] writes: howe=# select p.oid, n.nspname, pg_get_userbyid(p.proowner), proname from pg_proc as p, pg_namespace as n where pg_table_is_visible(p.oid); However, this situation lead me into another issue. The new conversion functions (utf8_to_big5, iso_to_alt, etc.) appear as listed in every new scheme. Is this correct ? Shouldn't them be listed only in pg_catalog? TL They would be, if you had remembered the join condition ;-) Oh, hehe. It worked, thank you. I remembered to include the condition on the other metadata queries, but forgot this one. Btw I wanted to thank you Tom and Bruce for the HACKERS] 7.3 gotchas for applications and client libraries post, it really helped a lot. And also for solving the issues on the returned info on INSTEAD rule queries... thanks. - Best regards, Steve Howe mailto:howe;carcass.dhs.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Rule updates and PQcmdstatus() issue
Hello Jan, Monday, September 9, 2002, 11:26:20 AM, you wrote: JW Steve Howe wrote: Hello Bruce, Friday, September 6, 2002, 9:52:18 PM, you wrote: BM I am not any happier about it than you are. Your report is good because BM it is the first case where returning the wrong value actually breaks BM software. You may be able to justify adding a fix during beta by saying BM it is a bug fix. Actually I think it must have happened with someone else, but they must have quit using rules or something... Actually I can't ensure security in the system without rules. BM Of course, someone is going to have to generate a patch and champion the BM cause. This stuff doesn't happen by magic. I understand your point. I just was hoping to see more concern about the issue by the developers... but that's been broken for months. Unhappily I can't do it myself because it would take weeks to get familiar with the inners of PostgreSQL... Let's hope someone realize how serious is this and make a fix. JW Seems you at least realized how serious it is. Even if you cannot code At least ?... What do you mean by that ? JW the proper solution, could you please make a complete table of all JW possible situations and the expected returns? With complete I mean JW including all combinations of rules, triggers, deferred constraints and JW the like. Or do you at least see now where in the discussion we got JW stuck? I had seen and the proposal was posted two days ago. JW It doesn't help to cry for a quick hack that fixes your particular JW problem. That only leads to the situation that someday we have a final JW fix that changes the behavior for your case again and then you cry again JW and ask us not to break backwards compatibility. See, I'm not crying. I'm just another user who needs something working. The whole problem was that the PostgreSQL knew the problem existed, had a brief discussion on the subject, and couldn't reach an agreement. That's ok for me, I understand... but releasing versions known to be broken is something I can't understand. - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Rule updates and PQcmdstatus() issue
Hello Jan, Monday, September 9, 2002, 11:26:20 AM, you wrote: JW Seems you at least realized how serious it is. Even if you cannot code JW the proper solution, could you please make a complete table of all JW possible situations and the expected returns? With complete I mean JW including all combinations of rules, triggers, deferred constraints and JW the like. Or do you at least see now where in the discussion we got JW stuck? By the way, I don't think triggers and constraints are in focus here, just as rules other then DO INSTEAD. These should be transparent to the user. I suggest you to read the proposal posted to get aware of the discussion. Thanks. - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Rule updates and PQcmdstatus() issue
Hello Jan, Monday, September 9, 2002, 11:15:47 AM, you wrote: JW Steve Howe wrote: Hello all, PostgreSQL *still* has a bug where PQcmdStatus() won't return the number of rows updated. But that is essential for applications, since without it of course we don't know if the updates/delete/insert commands succeded. Even worst, on interfaces like Delphi/dbExpress the program will return an error message and rollback transaction thinking nothing have been updated. In other words, unusable. This render views useless (I either use view with rules and don't get my program working) and won't allow me to proper use security settings on PostgreSQL... This is a *major* issue in my opinion that appeared on a May thread but I can't see it done on version 7.2.2. Even worst, I can't see nothing on the TODO file. Will this fix finally appear on 7.3 ? Any ways to work around this ? How can I know at least if *something* succeeded, or how many rows (the proper behavior)? JW And of course, in the case you insert into a real table you expect if a JW trigger procedure suppressed your original INSERT, but fired a cascade JW of other triggers by doing a mass UPDATE somewhere else instead, that JW all these caused UPDATEs and whatnot's are summed up and returned JW instead, right? Or what is proper behavior here? What is documented, and what is expected: PQcmdStatus(), PQcmdTuples()and PQoidValue() returning the information they should be. JW So please, proper behavior is not allways what your favorite tool JW expects. And just because you cannot fix your tool doesn't make that JW behavior any more proper. Do you have any word more appropriate ? And just so that you know, I can't fix my tool because I have other job to do (and a lot of that and that job uses PostgreSQL), and unhappily I couldn't join the development team and thus I'm not aware of how it works internally. The reason isn't that I just don't have intellectual capacity. And it looks like *you* overhauled the query rewrite rule system, so what we are talking is something that must have passed through you. So instead of offending me, your proper behavior would be try to help and suggest a solution for the problem, as other developers are doing. Thanks again. - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Proposal: Solving the Return proper effected tuple count from complex commands [return] issue
Hello Peter, Monday, September 9, 2002, 3:41:41 PM, you wrote: PE Steve Howe writes: Here are the proposals for solutioning the Return proper effected tuple count from complex commands [return] issue as seen on TODO. Any comments ?... This is obviously open to voting and discussion. PE We don't have a whole lot of freedom in this; this area is covered by the PE SQL standard. The major premise in the standard's point of view is that PE views are supposed to be transparent. That is, if PE SELECT * FROM my_view WHERE condition; PE return N rows, then a subsequently executed PE UPDATE my_view SET ... WHERE condition; PE returns an update count of N, no matter what happens behind the scenes. I PE don't think this matches Tom Lane's view exactly, but it's a lot closer PE than your proposal. If there was a single statement per rules executed, this would be end of discussion... but as you know there can be possible multiple statements per rules, and the difficulty is what do to in those cases. As far as of now, Tom Lane's proposal seems to be the most accepted, without using a new API. - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Rule updates and PQcmdstatus() issue
Hello Jan, Monday, September 9, 2002, 4:56:04 PM, you wrote: JW Steve Howe wrote: Hello Jan, Monday, September 9, 2002, 11:15:47 AM, you wrote: JW So please, proper behavior is not allways what your favorite tool JW expects. And just because you cannot fix your tool doesn't make that JW behavior any more proper. Do you have any word more appropriate ? [...] And it looks like *you* overhauled the query rewrite rule system, so what we are talking is something that must have passed through you. So instead of offending me, your proper behavior would be try to help and suggest a solution for the problem, as other developers are doing. JW See, and exactly here lies the problem. Indeed, I spent about 3 months JW of my spare time back in 95 or so to fix it, after I spent many more JW months over years to get familiar with the internals. JW Now, instead of even trying to spend some serious amount of time JW yourself, you give some vague hints about the functionality that might JW make your problems disappear, name that a proposal and expect someone JW else to do what you need for free. This is not exactly how open source JW works. As I told you, this would demand weeks and I just don't have time to do it. Other developers offered to make a fix and asked me to do that proposal. And so I did. It's sad that just you don't seem to be trying to help in any way. Other developers had considered the proposal and are actually voting and giving constructive ideas on the subject. JW We should surely keep this on a much more technical level and avoid any JW personal offendings. To do so, please explain to me why you think that JW triggers and constraints are out of focus here? What is the difference JW between a trigger, a rule and an instead rule from a business process JW oriented point of view? I think there is none at all. They are just JW different techniques to do one and the same, implement business logic in JW the database system. Because the affected commands are supposed to give you back information on what your INSERT/UPDATE/DELETE commands, not what is making behind the scenes. And it seems that other people in the thread agree with me, please read thread. Since you are probably very familiar with the rules system, why don't you vote on a proposal too, or just suggest yours. Your opinion is very important. I'm not saying I'm the truth owner; I'm just another developer who needs a feature working again. Thank you. - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Rule updates and PQcmdstatus() issue
Hello Bruce, Monday, September 9, 2002, 11:13:20 PM, you wrote: BM Steve Howe wrote: Because the affected commands are supposed to give you back information on what your INSERT/UPDATE/DELETE commands, not what is making behind the scenes. And it seems that other people in the thread agree with me, please read thread. Since you are probably very familiar with the rules system, why don't you vote on a proposal too, or just suggest yours. Your opinion is very important. I'm not saying I'm the truth owner; I'm just another developer who needs a feature working again. BM Jan actually did vote in the first round which appears in TODO.detail. BM He voted that if the INSTEAD rule had only _one_ statement, return that, BM if not, return nothing. We still need Tom's word and Hiroshi, since they were the most related to the subject, and the other developer's opinion... :) - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(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] Proposal: Solving the Return proper affected tuple
Hello Christopher, Monday, September 9, 2002, 11:36:44 PM, you wrote: CKL Sorry guys - it's killing me! It's 'affected' in the subject line - not CKL 'effected'!!! Sigh :) lol... my bad, English is not my primary language and these things just seem to happen sometimes... I apologize. - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Proposal: Solving the Return proper effected tuple count
Hello Bruce, Monday, September 9, 2002, 12:16:32 AM, you wrote: BM Joe Conway wrote: BM Any suggestion on how to show the tag mutated? Do we want to add more BM tag possibilities? Again, I don't agree with PQcmdStatus() returning a pseudo-keyword, since I would expect a SQL command executed. I prefer Tom's suggestion of returning the same kind of command executed, or the last command as of Proposal #1. I don't think we should add tuple counts from different commands, i.e. adding UPDATE and DELETE counts just yeilds a totally meaningless number. I don't know about that. The number of rows affected is indeed this number. It's just that they were not all affected in the same way. BM Yes, that is true. The problem is that a DELETE returning a value of 10 BM may have deleted only one row and updated another 9 rows. In such BM cases, returning 1 is better. Of course, if there are multiple deletes BM then perhaps the total is better, but then again, there is no way to BM flag this so we have to do one or the other consistently. BM BM The real problem which you outline is that suppose the delete does _no_ BM deletes but only inserts. In my plan, we would return zero while in BM yours you would return the rows updated. You have a good point here, Bruce. And for avoiding it, maybe Tom's suggestion is the best. Unless the new API as of Proposal #3 is introduced. BM In my view, if you return a delete tag, you better only count deletes. Yes, this is Tom's Proposal and it makes more sense when you imagine a case situation. Proposal #1 tried to be more compatible with the behavior of multiple commands execution but that would lead us to bad situations like Bruce exposes here. BM Also, your total affected isn't going to work well with INSERT because BM we could return a non-1 for rows affected and still return an OID, which BM would be quite confusing. I did the total only matching tags because it BM does mesh with the INSERT behavior. Even if this is 100% true, I'm afraid the only way to cover all specific situations is the new API. Let's remember it's easy to implement, and could server to both multiple commands execution *and* this rules situation. I don't think there is any need/desire to add additional API routines to handle multiple return values. Agreed. BM Yep. OK, this counts two points against the new API :) - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Proposal: Solving the Return proper effected tuple count from complex commands [return] issue
Hello Bruce, Monday, September 9, 2002, 12:21:11 AM, you wrote: BM Steve Howe wrote: Hello Bruce, But this *is* the total number of rows affected. There is no current (defined) behavior of rows affected by the same kind of command issued, although I agree it makes some sense. BM Yes, that is a good point, i.e. rows effected. However, see my previous BM email on how this doesn't play with with INSERT. I agree with your point. In fact, since everybody until now seems to agree that the last command behavior isn't consistent, I think Tom's suggestion is the best. BM We don't like to add complexity if we can help it. I understand. If we can reach an agreement on another way, that's ok for me... We still have to hear the other developers about this, but for a while, my votes go to Proposal's #2 (by Tom) and Proposal #3 if enough people consider it important. - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Proposal: Solving the Return proper effected tuple count
Hello Bruce, Monday, September 9, 2002, 12:22:26 AM, you wrote: BM Steve Howe wrote: JC return OID if sum of all replacement INSERTs in the rule inserted JConly one row, else zero I don't agree with this one since it would lead us to a meaningless information... what would be the number retrieved ? Not an OID, nor nothing. BM I don't understand this objection. I misunderstood Joe's statement into thinking we wanted to sum the OIDs for all INSERT commands applied :) Please ignore this. But now that I read it again, I would prefer having at least one OID for the last inserted row. With this info, I would be able to refresh my client dataset to reflect the new inserted rows. I see returning 0 if multiple INSERT commands issued is as weird as returning some OID if multiple INSERT commands issued. But the second options is usable, while the first one is useless... So I would prefer retrieving the last inserted OID. - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposal: Solving the Return proper effected tuple count
Hello Bruce, Monday, September 9, 2002, 12:36:38 AM, you wrote: BM Joe Conway wrote: Sure, but that's why I am in favor of changing the tag. If you did: DELETE FROM fooview WHERE name LIKE 'Joe%'; and got: MUTATED 507324 3 it would mean that 3 tuples in total were affected by all of the substitute operations, only of of them being an INSERT, and the Oid of the lone INSERT was 507324. If instead I got: DELETE 0 I'd be back to having no useful information. Did any rows in fooview match the criteria LIKE 'Joe%'? Did any data in my database get altered? Can't tell from this. BM OK. Do any people have INSTEAD rules where there are not commands BM matching the original query tag? Can anyone think of such a case being BM created? I can think a thousand cases. For instance, one could create an update rule that would delete rows referenced on a second table (to avoid orphan rows). OR a user could make an insert rule that empties a table with DELETE so that only one row can always be assumed in that table... the possibilities are infinite. BM The only one I can think of is UPDATE implemented as separate INSERT and BM DELETE commands. I'm afraid the great imagination of PostgreSQL users has come to all kind of uses and misuses for such a powerful feature :) - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(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] Proposal: Solving the Return proper effected tuple count
Hello Bruce, Monday, September 9, 2002, 12:39:20 AM, you wrote: BM I don't understand this objection. I misunderstood Joe's statement into thinking we wanted to sum the OIDs for all INSERT commands applied :) Please ignore this. But now that I read it again, I would prefer having at least one OID for the last inserted row. With this info, I would be able to refresh my client dataset to reflect the new inserted rows. I see returning 0 if multiple INSERT commands issued is as weird as returning some OID if multiple INSERT commands issued. But the second options is usable, while the first one is useless... So I would prefer retrieving the last inserted OID. BM We would return 0 for oid and an insert count, just like INSERT INTO ... BM SELECT. How is that weird? It's not weird, or as weird as the other proposal which is retrieving the last inserted OID number. If we can return some information for the client, why not doing it ? :-) - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Proposal: Solving the Return proper effected tuple count from complex commands [return] issue
Hello all, Here are the proposals for solutioning the Return proper effected tuple count from complex commands [return] issue as seen on TODO. Any comments ?... This is obviously open to voting and discussion. -- Best regards, Steve Howe mailto:[EMAIL PROTECTED] - Introduction These are three proposals to give a solution on the issue: * Return proper effected tuple count from complex commands [return] ... as seen on TODO http://developer.postgresql.org/todo.php as of 09 Sep 2002. Affect Versions: PostgreSQL v7.2X PostgreSQL pre 7.2 versions has inconsistent behavior as stated below. References -- The main thread discussion is listed in (1): http://momjian.postgresql.org/cgi-bin/pgtodo?return Some previous discussion started on (2): http://archives.postgresql.org/pgsql-general/2002-05/msg00096.php The topic was revisited by Steve Howe in the thread (3): http://archives.postgresql.org/pgsql-hackers/2002-09/msg00429.php Problem Description: PQcmdStatus(), PQcmdTuples() and PQoidValue() do not work properly on rules, most notably updating views. An additional layer of problems can arise if user issues multiple commands per rule, as of what should be the output of those functions in that situation. Specially problematic is PQcmdTuples(), which will return 0, confusing client applications into thinking nothing was updated and even breaking some applications. The pre-version 7.2 behavior is not acceptable as stated by Tom Lane on the threads above. An urgent fix is demanded to allow applications using rules to work properly and allow clients to retrieve proper execution information. Proposal #1 (author: Steve Howe): - As stated in the threads above (from the [References] topic), we have 3 tags to worry about, returned by the following functions: PQcmdStatus() - command status string PQcmdTuples() - number of rows updated PQoidValue() - last inserted OID My proposal consists basically on having the same behavior of when multiple commands per execution string are executed currently (except for PQcmdTuples()) : PQcmdStatus() == Should return the last executed command or the same as the original command (I prefer the second way, but the first is more intuitive on a multiple execution case, as I'll explain below). PQcmdTuples() == should return the sum of modified rows of all commands executed by the rule (DELETE / INSERT / UPDATE). PQoidValue() == should return the value for the last INSERT executed command in the rule (if any). Using this scheme, any SELECT commands executed would not count on PQcmdTuples(), what makes plain sense. The other commands would give a similar response to what we already have when we issue multiple commands per execution string. I would like to quote an issued pointed by Tom Lane, from one of the messages on the thread above: I'm also concerned about having an understandable definition for the OID returned for an INSERT query --- if there are additional INSERTs triggered by rules, does that mean you don't get to see the OID assigned to the single row you tried to insert? In this case, the user has to be aware that if he issued multiple commands, he will get the result for only the last one. This is is the same behavior of multiple commands when you execute: db# insert into MyTable values(1 ,1); insert into MyTable values(2 ,2); INSERT 93345 1 INSERT 93346 1 Of course this could lead to have a PQcmdStatus() return value greater then the number of rows viewable by the rule, but I think that's perfectly understandable if there are multiple commands involved and the client application programmer should be aware of that. PQoidStatus() will return the OID only for the last command, so (again) the proposed behavior is compatible on what already happens when you issue multiple commands. So if the user issues some insert commands but The proposed behavior would be the same for DO and DO INSTEAD rules unless someone points out some flaw. Proposal #2 (author: Tom lane): - Tom Lane's proposal, as posted on http://candle.pha.pa.us/mhonarc/todo.detail/return/msg00012.html, consists basically on the following: PQcmdStatus() == Should always return the same command type original submitted by the client. PQcmdTuples() == If no INSTEAD rule, return same output as for original command, ignoring other commands in the rule.If there is INSTEAD rules, use result of last command in the rewritten series, use result of last command of same type as original command or sum up the results of all the rewritten commands
Re: [HACKERS] Proposal: Solving the Return proper effected tuple count from complex commands [return] issue
Hello Bruce, Sunday, September 8, 2002, 10:52:45 PM, you wrote: BM I liked option #2. I don't think the _last_ query in a rule should have BM any special handling. BM So, to summarize #2, we have: BM if no INSTEAD, BM return value of original command The problem is, this would lead us to the same behavior of Proposal #1 (returning the value for the last command executed), which you didn't like... BM if INSTEAD, BM return tag of original command BM return sum of all affected rows with the same tag BM return OID if all INSERTs in the rule insert only one row, else zero BM This INSERT behavior seems consistent with INSERTs inserting multiple BM rows via INSERT INTO ... SELECT: BM test= create table x (y int); BM inseCREATE TABLE BM test= insert into x select 1; BM INSERT 507324 1 BM^^ BM test= insert into x select 1 union select 2; BM INSERT 0 2 BM^ BM I don't think we should add tuple counts from different commands, i.e. BM adding UPDATE and DELETE counts just yeilds a totally meaningless BM number. But this *is* the total number of rows affected. There is no current (defined) behavior of rows affected by the same kind of command issued, although I agree it makes some sense. BM I don't think there is any need/desire to add additional API routines to BM handle multiple return values. I'm ok with that if we can reach an agreement on how the existing API should work. But as I stated, a new API would be a no-discussion way to solve this, and preferably extending some of the other proposals. BM Can I get some votes on this? We have one user very determined to get a BM fix, and the TODO.detail file has another user who really wants a fix. *Please* let's do it :) Thanks. - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(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] Proposal: Solving the Return proper effected tuple count
Hello Joe, Sunday, September 8, 2002, 11:54:45 PM, you wrote: JC Bruce Momjian wrote: I liked option #2. I don't think the _last_ query in a rule should have any special handling. So, to summarize #2, we have: if no INSTEAD, return value of original command if INSTEAD, return tag of original command return sum of all affected rows with the same tag return OID if all INSERTs in the rule insert only one row, else zero JC How about: JC if no INSTEAD, JC return value of original command JC if INSTEAD, JC return tag MUTATED I see PQcmdStatus() returning a SQL command and not a pseudo-keyword, so I don't agree with this tag. JC return sum of sum of tuple counts of all replacement commands Agreed. JC return OID if sum of all replacement INSERTs in the rule inserted JConly one row, else zero I don't agree with this one since it would lead us to a meaningless information... what would be the number retrieved ? Not an OID, nor nothing. JC I don't know about that. The number of rows affected is indeed this JC number. It's just that they were not all affected in the same way. Agreed too... JC +1 for the version above ;-) Which ? Yours or Tom's ? :) - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Rule updates and PQcmdstatus() issue
Hello Tom, Saturday, September 7, 2002, 5:42:33 PM, you wrote: TL Steve Howe [EMAIL PROTECTED] writes: BM I suggest you read the TODO detail on the item and make a proposal on BM how it _should_ work and if you can get agreement from everyone, you may BM be able to nag someone into doing a patch. I think it should return the number of rows modified in the context of the view, and not exactly that of each of the tables affected. TL That's so vague as to be useless. What is in the context of the view? TL How does that notion help us resolve the uncertainties discussed in the TL TODO thread? I just mean that PQcmdStatus() should not return a value for each changed table but how many rows viewable by the view it could change. Again, I'm not that aware of the inners of PostgreSQL to feel comfortable to do a better suggestion. This was working on some previous build, wasn't it ? What was the previous behavior ? Shouldn't the patch follow that way ? TL The old behavior was quite broken too, just not in a way that affected TL you. We will not be reverting the change that fatally broke it (namely TL altering the order of RULE applications for INSERTs) and so go back TL to the old code isn't a workable answer at all. I didn't mean to revert the code but to make it work like the older version did. I was unaware that it was broken too, but the removal now broke the whole views/rules so I wonder what could be worst... Also, it should have affected thousands of users, not just me. Unless nobody uses views... TL I don't think fixing the code is the hard part; agreeing on what the TL behavior should be in complex cases is the hard part. I understand your point and I'll try to give a proper solution but since I'm not familiar with the PostgreSQL inners, I wonder how good could it be... Thanks :) - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Rule updates and PQcmdstatus() issue
Hello all, PostgreSQL *still* has a bug where PQcmdStatus() won't return the number of rows updated. But that is essential for applications, since without it of course we don't know if the updates/delete/insert commands succeded. Even worst, on interfaces like Delphi/dbExpress the program will return an error message and rollback transaction thinking nothing have been updated. In other words, unusable. This render views useless (I either use view with rules and don't get my program working) and won't allow me to proper use security settings on PostgreSQL... This is a *major* issue in my opinion that appeared on a May thread but I can't see it done on version 7.2.2. Even worst, I can't see nothing on the TODO file. Will this fix finally appear on 7.3 ? Any ways to work around this ? How can I know at least if *something* succeeded, or how many rows (the proper behavior)? Thank you very much. - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(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] Rule updates and PQcmdstatus() issue
Hello Bruce, Friday, September 6, 2002, 3:22:13 PM, you wrote: BM Steve Howe wrote: Hello all, PostgreSQL *still* has a bug where PQcmdStatus() won't return the number of rows updated. But that is essential for applications, since without it of course we don't know if the updates/delete/insert commands succeded. Even worst, on interfaces like Delphi/dbExpress the program will return an error message and rollback transaction thinking nothing have been updated. In other words, unusable. This render views useless (I either use view with rules and don't get my program working) and won't allow me to proper use security settings on PostgreSQL... This is a *major* issue in my opinion that appeared on a May thread but I can't see it done on version 7.2.2. Even worst, I can't see nothing on the TODO file. Will this fix finally appear on 7.3 ? Any ways to work around this ? How can I know at least if *something* succeeded, or how many rows (the proper behavior)? BM I see on TODO: BM * Return proper effected tuple count from complex commands [return] Sorry, I missed it because I check the v7.2.2 TODO. BM and that return link has a discussion of possible fixes. BM Unfortunately, no fix was agreed upon so there is no fix in 7.3. So all the databases that uses rules will still be broken ? I don't believe you guys are so unconcerned about this... BM And, on top of that, I can't even think of a workaround. At best, BM perhaps someone can write you a patch to fix this. Let's hope so... and I disagree about the 'write for me' point; it's for *everyone using rules*. They are useless, currently... and it's broken for months and nothing agreed until know... I just can't believe in it. What do you do when you have to update a view ? - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Rule updates and PQcmdstatus() issue
Hello Bruce, Friday, September 6, 2002, 9:52:18 PM, you wrote: BM I am not any happier about it than you are. Your report is good because BM it is the first case where returning the wrong value actually breaks BM software. You may be able to justify adding a fix during beta by saying BM it is a bug fix. Actually I think it must have happened with someone else, but they must have quit using rules or something... Actually I can't ensure security in the system without rules. BM Of course, someone is going to have to generate a patch and champion the BM cause. This stuff doesn't happen by magic. I understand your point. I just was hoping to see more concern about the issue by the developers... but that's been broken for months. Unhappily I can't do it myself because it would take weeks to get familiar with the inners of PostgreSQL... Let's hope someone realize how serious is this and make a fix. Thanks again... - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Rule updates and PQcmdstatus() issue
Hello Bruce, Friday, September 6, 2002, 10:58:13 PM, you wrote: BM Well, there was a big discussion, and I did bring up the issue in early BM August to see if I could get a resolution to it and was told no BM conclusion could be made. BM I suggest you read the TODO detail on the item and make a proposal on BM how it _should_ work and if you can get agreement from everyone, you may BM be able to nag someone into doing a patch. I think it should return the number of rows modified in the context of the view, and not exactly that of each of the tables affected. And this would not work well with PQcmdStatus() because it returns a single integer entry only. This was working on some previous build, wasn't it ? What was the previous behavior ? Shouldn't the patch follow that way ? - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Roadmap for a Win32 port
Hello Thomas, Wednesday, June 5, 2002, 7:02:33 PM, you wrote: TL ... Good summary. I think we would support both threaded and fork() operation, and users can control which they prefer. For a web backend where many sessions are a single query, people may want to give up the stability of fork() and go with threads, even on Unix. TL I would think that we would build on our strengths of having a fork/exec TL model for separate clients. A threaded model *could* benefit individual TL clients who are doing queries on multiprocessor servers, and I would be TL supportive of efforts to enable that. Just a note - this is also the solution adopted by Interbase/Firebird and it seems interesting. They already had the same problems PostgreSQL has been under today. Those interested in read about Interbase's architeture, please refer to http://community.borland.com/article/0,1410,23217,00.html. Classic is the fork() model, and the SuperServer is the threaded model. - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Roadmap for a Win32 port
Hello Bruce, Wednesday, June 5, 2002, 1:33:44 AM, you wrote: BM INSTALLER BM - BM We clearly need an installer that is zero-hassle for users. We need to BM decide on a direction for this. I suggest Nullsoft install system (http://www.nullsoft.com/free/nsis/). It's real good and very simple to use. I can help on this if you want. BM ENVIRONMENT BM Lots of our code requires a unix shell and utilities. Will we continue BM using cygwin for this? There are other ports ( http://unxutils.sourceforge.net/ ) that won't require Cygwin but they won't provide an environment so complete as Cygwin does. I also would like to empathize that probably a small GUI for controlling the PostgreSQL service/application would be nice. I think about something sitting in the system tray like MSSQL, Oracle, Interbase, etc. does. I could code this in Delphi if you like. I don't have experience in writing GUI apps in C. There is an open source versions of Delphi so it won't be a problem compiling it. Also coming with this, a code for starting the PostgreSQL as a service would be really nice. For those from UNIX world that don't know what a service is, think about it as a daemon for Windows. A service can be automatically started when the machine boots up. - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Catalogs design question
Hello Haller!!! Your question about - pg_proc select t.typname from pg_type t , pg_proc p where p.proname = 'your_stored_procedure' and p.proargtypes[0] = t.oid ; select t.typname from pg_type t , pg_proc p where p.proname = 'your_stored_procedure' and p.proargtypes[1] = t.oid ; ... select t.typname from pg_type t , pg_proc p where p.proname = 'your_stored_procedure' and p.proargtypes[7] = t.oid ; As far as I understand the proargtypes entries 0 means no further parameter. This oidvector type of proargtypes seems to have a start index of 0. As long as there are at maximum 8 parameters allowed, this looks practicable. There is no limit on the number of arguments. An user could create a weird function like this: howe=# CREATE FUNCTION test2(int2, int2, int2, int2, int2, int2, int2, int2, int2, int2, int2, int2, int2) RETURNS int4 AS 'SELECT 1 AS RESULT' LANGUAGE 'sql'; CREATE and it would be allowed... howe=# select proargtypes from pg_proc where proname='test'; proargtypes 21 21 21 21 21 21 21 21 21 21 21 21 21 (1 row) Again, the problem is that I can't predict (nor limit) what users will try to do... Best Regards, Steve Howe ---(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] Catalogs design question
Hello Haller!! Your question about - pg_proc select t.typname from pg_type t , pg_proc p where p.proname = 'your_stored_procedure' and p.proargtypes[0] = t.oid ; select t.typname from pg_type t , pg_proc p where p.proname = 'your_stored_procedure' and p.proargtypes[1] = t.oid ; ... select t.typname from pg_type t , pg_proc p where p.proname = 'your_stored_procedure' and p.proargtypes[7] = t.oid ; As far as I understand the proargtypes entries 0 means no further parameter. This oidvector type of proargtypes seems to have a start index of 0. As long as there are at maximum 8 parameters allowed, this looks practicable. There is such a limit ? I didn't know. This makes your code a working way. I'll look further on this later... and even if it's not a query that I would say it's beautiful, it's a way, thanks :). Your question about - pg_group The pg_group column is more bulky, because the int4[] type does not have an upper limit. So, the only solution I can see is get the number of array elements of the group you want to query select array_dims(grolist) from pg_group where groname = 'your_group'; and then generate automatically a query like select u.usename from pg_user u , pg_group g where g.grolist[1] = u.usesysid and g.groname='your_group' union select u.usename from pg_user u , pg_group g where g.grolist[2] = u.usesysid and g.groname='your_group' union ... select u.usename from pg_user u , pg_group g where g.grolist[n] = u.usesysid and g.groname='your_group' ; This looks very much like another crude hack you've already complained about. Sorry, but I can't help. Yes, it's ugly code. I would rather write a function, but again I can't assume the user has pl/perl or pl/pgsql (or any other). Two more items I do not understand: You said, the procedures to search arrays in contrib/ are slow. Maybe that's true, but usually you do not have thousands of users in a group, don't you. Yes. I would use it if I can. You said, many users cannot compile this contrib code. Yes, and they are not supposed to do so, because it's up to a system admin to do. What do I miss here? Oh, I develop an interface for PostgreSQL called pgExpress(http://www.vitavoom.com) - it's like an ODBC driver or such. I must provide the functionality I described for the driver users; it's not for me. I would of course have compiled and used the contrib code. But the driver must work out-of-the-box, and requiring a recompile (where many times is impossible to users) is not a solution... Right now, I'm hardcoding that relation inside the driver, what's also not what I dreamed about, but I seem to have no other choice. Thanks for the ideas btw :) Best Regards, Steve Howe ---(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] Catalogs design question
I also quote the PotgreSQL user manual (http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/arrays.html): In the contrib/ directory are procedures to search arrays for values. This may help. Thanks for the tip, but in fact I've seen them (and they're listed on the same document I pointed on the original message). These are sequential (slow) searches, and can't be indexed. in resume: nothing but another crude hack :). I could even use it, but I can';t tell my users oh this feature works but you must compile this contrib code inyo your servers. Many users can't do it, and many don't even know how to do it :( Best Regards, Steve Howe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Catalogs design question
Hello Bruce! Yes, we inherited these arrays from Berkeley and haven't had any need to remove them. Are you trying to do things that the other interfaces like ODBC and JDBC don't handle? About the groups: I just want to write a function that will return the users names belonged by a given group. I understand I can load the arrays in memory, then sequentially compare the members from pg_shadow, but doing it goes against the database priciple after all. About the procs: the Borland's dbExpress specification demands a input/output list of parameters for stored procedures, and I'm going to use functions as stored procedures. But I need to make a types list to be able list what are those params. The group array is a hack but the pg_proc array would be hard to replace becauseit acts as part of the unique key used for cache lookups. This design itself bothers me. We have no other option left ? Like arrays being referenced in relations ? That's far from perfect, but at least would solve those issues and others which might appear in other catalogs... Best Regards, Steve Howe ---(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] Catalogs design question
Hello all!! I'm developer of a interface for PostgreSQL for the Borland Kylix and Delphi tools (http://www.vitavoom.com). I've run into the following problems with catalogs: - pg_group: the grolist field is an array. How can I make a query that tell me the usernames of a group ? - pg_proc: the proargtypes field is an array. How can I make a query that will link those types to the pg_types catalog ??? This catalog design seems a very crude hack to make the things working for me. Can't those relations be separated in another table ? Or maybe a function that can search for a value in array, and make a wroking reference for an array element in a relation (something like select typname from pg_type, pg_group where oid in grolist). I also quote the PotgreSQL user manual (http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/arrays.html): Tip: Arrays are not lists; using arrays in the manner described in the previous paragraph is often a sign of database misdesign. The array field should generally be split off into a separate table. Tables can obviously be searched easily. Best Regards, Steve Howe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Retriving users from group ?...
Hello all!!! I need to retrieve all the users from a group, for my Delphi Interface for PostgreSQL. The problem is, I can't accomplish a query that does it, since the users belonging from a group are all stored in an array field (pg_group.groulist). Does anyone have a solution for this ? Using contrib array functions is certainly not a good idea, because it would require ALL users of the interface to compile and install it. I still don't understand anyway why there is not a regular catalog relating users and groups, like: CREATE TABLE pg_groupusers(grosysid integer, usesysid integer); That makes much more sense for me, unless at least the contrib array functions get implemented as builtins, so that we can test user groupship. Can that be added to the TODO least ?... Best Regards, Steve Howe ---(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] Cursor queries fetches
Hello all, I've noted that the MOVE command won't send you an error message if you try to position the cursor AFTER the actual end of the records. Is this ok ??? I think it should return a proper error code and message, but instead, it will return only an empty recordset. The following code will demonstrate what I mean: rollback;begin;declare Test cursor for select typname from pg_type;move 1 in Test;fetch 10 in Test;close Test;end; I think 'move 1' should raise an error... Any comments ? Does Oracle or other DBMSs act like this ? I have another questions, since I can't find answers anywhere on the documentation... 1) can DECLARE be modified to return the number of rows in the resultset, maybe by adding some new parameter ?... Or it would it require to execute the whole query before returning this value ?... 2) How does cursor queries really work: they execute the query, store all rows in memory, and then pass small resultsets retrieved by FETCH commands on the client, or does it produces and retrieves each recorset as each FETCH command is issued ?? Best Regards, Steve Howe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] LIBPQ on Windows and large Queries
Hello all, I was in a trip and just arrived, and will do it real soon. Best Regards, Steve Howe - Original Message - From: Tom Lane [EMAIL PROTECTED] To: Hiroshi Inoue [EMAIL PROTECTED] Cc: Steve Howe [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, July 29, 2001 8:57 PM Subject: Re: [HACKERS] LIBPQ on Windows and large Queries Hiroshi Inoue [EMAIL PROTECTED] writes: Well haven't this problem solved already ? I'm not sure. Steve, have you tried current sources? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] LIBPQ on Windows and large Queries
Hello all, Is anybody trying to solve the 8191 bytes query limit from libpq windows port ??? We've discussed this topic on Large queries - again thread and it seems like nobody got interested on fixing it. All Windows applications that rely on libpq are broken because of this issue (ODBC applications are fine btw). I can also do any kind of testing under Windows (and actually I'll do it anyway). I wonder if this limitation also applies to the unix libpq library ??? Jan, Tom, Bruce - any news on this ? Best Regards, Steve Howe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Large queries - again...
Hello all, I've tried again sending large queries using libpq on Windows environment, without success. I downloaded the PostgreSQL v7.12 sources, compiled libpq.dll using Microsoft's Visual C++ 6.0, and tried sending a large query. The problem is, when the query is 8192 large, a NULL pointer is returned from PQexec(). I have tried using ZDE (http://www.zeoslib.org), which I helped develop, and pgAccess. ZDE is based on the Zeos Database Objects library, which provides full access to PostgreSQL to Borland Delphi and Borland C++ Builder compilers. Could anyone please try this query: ftp://carcass.dhs.org/pub/test.zip on windows (using libpq) and confirm it suceed ? This archive contains a test.sql source, which will create a dumb table with a text filed and then try to insert in it a large data (8192 bytes) on it, and the libpq.dll I just compiled, for who want a fresh libpq (it's virus free, don't worry... ). All my current PostgreSQL driver implementation is depending on this. I'm sure the libpq will fail, unless something very weird is happening in here... :) Other friends have confirmed this behaviour. I tried to look at the libpq sources to find out where's the error, but I think it will take much less time to who develops it... Best Regards, Steve Howe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Large queries - again...
- Original Message - From: Tom Lane [EMAIL PROTECTED] To: Steve Howe [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, July 20, 2001 5:17 PM Subject: Re: [HACKERS] Large queries - again... Steve Howe [EMAIL PROTECTED] writes: It returns Error: pqReadData() -- read() failed: errno=0 No error as expected when a nil pointer is returned. As expected? That's not what I'd expect, especially not for a behavior that's dependent on the size of an *outgoing* message. It is expected, because it's the default message when a PQexec() query returns NULL: pqReadData() will return nothing yet no error is signed. Of course, the really expected would be a sucessfull exec :-) (Thinks for awhile...) You're not using PQsetnonblocking() are you, by any chance? No, I'm not. Asynchronous libpq connections on Windows are still not realiable (althought I read someone submitted a patch recently), so I'm keeping synchronous queries for a while. I'm not also using any non-standard functions; just plain PQconnectdb() and PQexec()... Best Regards, Steve ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Large queries - again...
Steve Howe [EMAIL PROTECTED] writes: (Thinks for awhile...) You're not using PQsetnonblocking() are you, by any chance? No, I'm not. Drat, another perfectly good theory down the drain :-(. Well, we're not going to find out anymore until we discover what the error code actually is --- the errno=0 bogosity isn't helping. As Bruce mentioned, we did just commit a patch that #defines errno as WSAGetLastError() on WIN32, so that you can get at least something useful about socket errors. I'd suggest pulling the current CVS sources (or a nightly snapshot tarball dated after today) and building libpq from that. Then maybe we can learn more. Unhappyly, there are unresolved externals and it won't build... I'll try to fix it. The log follows right below... Best regards, Steve Howe -- Microsoft (R) Program Maintenance Utility Version 6.00.8168.0 Copyright (C) Microsoft Corp 1988-1998. All rights reserved. cd include if not exist config.h copy config.h.win32 config.h cd .. cd interfaces\libpq nmake /f win32.mak Microsoft (R) Program Maintenance Utility Version 6.00.8168.0 Copyright (C) Microsoft Corp 1988-1998. All rights reserved. link.exe @C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\nma01588. Creating library .\Release\libpqdll.lib and object .\Release\libpqdll.exp libpq.lib(fe-exec.obj) : error LNK2001: unresolved external symbol _snprintf libpq.lib(fe-misc.obj) : error LNK2001: unresolved external symbol _snprintf libpq.lib(fe-auth.obj) : error LNK2001: unresolved external symbol _snprintf libpq.lib(dllist.obj) : error LNK2001: unresolved external symbol _elog .\Release\libpq.dll : fatal error LNK1120: 2 unresolved externals NMAKE : fatal error U1077: 'link.exe' : return code '0x460' Stop. NMAKE : fatal error U1077: 'C:\Program Files\Microsoft Visual Studio\VC98\bin\NMAKE.EXE' : return code '0x2' Stop. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Large queries - again...
- Original Message - From: Bruce Momjian [EMAIL PROTECTED] To: Steve Howe [EMAIL PROTECTED] Cc: Tom Lane [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Saturday, July 21, 2001 1:39 AM Subject: Re: [HACKERS] Large queries - again... OK, I just applied a patch to add the final fixes to Win32 libpq. Please try the CVS or later snapshot to see how it works. The patch suggested adding #define snprintf _snprintf to win32.h and I have done that. There was already one there for vsnprintf. I am quite confused about the elog() mention. I don't see where we added a call to elog() in the past day. I only see two mentions of elog in the code, both it dllist.c. They don't use elog() if you define FRONTEND. Please do -DFRONTEND on the compile line. I think this will give you a good library binary. I did it, but that brings other dependency problems (see below). I think it's better to properly fix the elog issue... :-) C:\ttt\srcnmake -f win32.mak Microsoft (R) Program Maintenance Utility Version 6.00.8168.0 Copyright (C) Microsoft Corp 1988-1998. All rights reserved. cd include if not exist config.h copy config.h.win32 config.h cd .. cd interfaces\libpq nmake /f win32.mak Microsoft (R) Program Maintenance Utility Version 6.00.8168.0 Copyright (C) Microsoft Corp 1988-1998. All rights reserved. cl.exe @C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\nma01700. dllist.c ..\..\backend\lib\dllist.c(20) : fatal error C1083: Cannot open include file: 'sysexits.h': No such file or directory Let us know how the new code works. The most recent patch I just applied was tested by a user and it worked well for him. Nice to have this resolved. I can mark this TODO item as done: * -Fix libpq to properly handle socket failures under native MS Win32 [libpq] I want this fixed more then anybody else i the world, believe me :-) Best Regards, Steve Howe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Large queries - again...
Hello Tom, It returns Error: pqReadData() -- read() failed: errno=0 No error as expected when a nil pointer is returned. Best Regards, Steve Howe - Original Message - From: Tom Lane [EMAIL PROTECTED] To: Steve Howe [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, July 20, 2001 2:42 PM Subject: Re: [HACKERS] Large queries - again... Steve Howe [EMAIL PROTECTED] writes: Nope, I'm 100% sure that the libpq.dll used is the one I just compiled. And I never installed an older libpq.dll on this system. Hmph. So what is left in PQerrorMessage() after the failure? regards, tom lane ---(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] Large queries - again...
Hello Tom, Nope, I'm 100% sure that the libpq.dll used is the one I just compiled. And I never installed an older libpq.dll on this system. My application loads specifically the libpq.dll I compiled (I use the full library path on the call to LoadLibrary() call, so there is no way it is an older library. Eduardo Stern from dbExperts (http://www.dbexperts.com.br), a PostgreSQL specialized consulting company, got the same results. The ODBC driver, however, do not suffer from the same problem, once it does not use libpq. Maybe I should write a C sample that proves libpq under windows has this bug ??? Best Regards, Steve Howe - Original Message - From: Tom Lane [EMAIL PROTECTED] To: Steve Howe [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, July 20, 2001 12:35 PM Subject: Re: [HACKERS] Large queries - again... Steve Howe [EMAIL PROTECTED] writes: I downloaded the PostgreSQL v7.12 sources, compiled libpq.dll using Microsoft's Visual C++ 6.0, and tried sending a large query. The problem is, when the query is 8192 large, a NULL pointer is returned from PQexec(). It sure sounds to me like you are invoking an old (6.5 or before) libpq. Perhaps you should check around to see if there are multiple libpq.dll files on your system ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] PQexec() 8191 bytes limit and text fields
Hello all, Writing my interface application, which use the PQexec library, I came across the PQexec() queries 8191 bytes limit. What useful are 4Gb text fields if I have this limit ? I mean, if a user make an update to this field, with a large value (let's say, 4Mb), do I have to call PQexec multiple (more then 500) times, concatenating the strings each time I call it ??? Can't this be better implemented ? This is too slow, and generates much more traffic then I ever wish. This problem also plagues the large objects API, since they're only a wrapper to the built-in large objects API. Does anyone have a better way of doing this ? Best Regards, Steve Howe http://www.vitavoom.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] PQexec() 8191 bytes limit and text fields
Hi... The problem is, I compiled it myself from original PostgreSQL version 7.12 C sources using Microsoft's Visual C++ 6.0. I had to compile it because I add a function to free the handlers returned from PQnotifies(), or I would have a memory leak. The resulting libpq.dll seems ok in everything but this issue... I guess I'll do it again, after checking the sources :) Other people reported me they send large queries with no problems, so I guess it should really be a problem of mine... Best Regards, Steve Howe - Original Message - From: Tom Lane [EMAIL PROTECTED] To: Steve Howe [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, July 18, 2001 1:14 PM Subject: Re: [HACKERS] PQexec() 8191 bytes limit and text fields Steve Howe [EMAIL PROTECTED] writes: Writing my interface application, which use the PQexec library, I came across the PQexec() queries 8191 bytes limit. You must have a very out-of-date library. Time to update. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] This script will crash the connection
Helo all,
Please try this script, it will crash the current connection.
I'm using the 01/18/2001 PostgreSQL v7.1 beta3 snapshot.
-- Script begin -
create table blah(
var_field varchar(8),
n1 integer default 23,
n2 integer,
arr_str varchar[],
m money,
s text
);
create rule blah_update as
on update to blah
do
notify TestEvent;
INSERT INTO blah (var_field, n1, n2, arr_str, m, s) VALUES ('aaa', 1, 2,
NULL, NULL, NULL);
UPDATE blah SET n1=n1+1; -- Won't crash the connection
UPDATE blah SET n1=2 WHERE var_field='aaa' AND n1=1 AND n2=2 AND arr_str IS
NULL AND m IS NULL; -- Will crash the connection
-- Script end -
psql will print :
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!#
Any comments ?
I need this kind of code working for a demo for ZeosDBO users...
Best Regards,
Steve Howe
[HACKERS] Must implement PQnotifyFree()
Hi folks, I'm co-developer of Delphi and Borland C++ Builder Zeos library (http://www.zeos.dn.ua) by Sergey Seroukhov which includes support for PostgreSQL. In fact, I'm dedicated Zeos PostgreSQL developer. We've run into a problem that we can't properly free the PPGnotify handle returned by PQnotifies() - simply because Delphi does NOT include free() routine (it does have it's own memory management routines of course). We could assume MSVC's malloc() is used and import free() from it, like this: procedure free(P: Pointer); cdecl; external 'msvcrt.dll'; // (which is the Delphi/Pascal equivalent of C's free()) - but that would be a crude hack and would probably not work in platforms other then windows (Kylix, Delphi's encarnation on Linux, is almost ready as most of you should know) or libpq.dll compiled with other C compilers then then currently being used. That's why we believe a PQnotifyFree() or similar function should be implemented since not everybody using libpq.dll is also using MSVC compiler. That could also lead to problems in other platforms using compilers with different allocation schemes. Of course it is trivial to implement such a function and this should be no trouble. Could you please consider it ? Of course nobody wants memory corruption in their applications and we don't like having to let those records allocated, but we can't currently do much about it. Thanks. Best Regards, Steve Howe Capella Development Group.
Re: [HACKERS] OID Implicit limit
""Christopher Kings-Lynne"" [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... Reading the documentation, I see that OIDs are unique through the whole database. But since OIDs are int4, does that limit the number of rows I can have in a database to 2^32 = 4 billion ? Yep. Thanks for the answer - although that concerns me a bit. Maybe I could recompile it setting oid to int64 type... If that really concerns you, then the rest of the hackers list I think would be very interested in hearing of a real-world database with more than 4 billion rows/inserts/deletes. Apparently it is somewhat more complicated than just 'recompiling as an int64' to change this. I believe that patches are currently being made to facilitate a future move towards 64bit OIDs, but I am not certain of the status. Chris It won't for sure have 4 billion records *at a time*. But will easyly process (insert/small calculations/summarize/delete) up to 10 or 20 million records per day. But not all records will be deleted; I'll run into key conflicts if the oid sequence generator cross the 2^32 boundary. That is my problem. But sure, that will take some time to happen (215 days for 20 millions rows/day). That means I'll have to be able to do 231,48148148 inserts per second plus the time to delete and aggregate the data. Apparently it is somewhat more complicated than just 'recompiling as an int64' to change this. I believe that patches are currently being made to facilitate a future move towards 64bit OIDs, but I am not certain of the status. Well I hope until there I don't have the key conflicts I mentioned. It will be hard to, anyway - but possible. Best regards, Howe
[HACKERS] OID Implicit limit
Hi folks, Reading the documentation, I see that OIDs are unique through the whole database. But since OIDs are int4, does that limit the number of rows I can have in a database to 2^32 = 4 billion ? Best Regards, Howe
