[HACKERS] sqlstate 22P06 is a warning in an error's clothing
The server logs WARNING: nonstandard use of \\ in a string literal at character 44, but the message comes with sqlstate code 22P06, which is in an error class (Data exception). So my application thinks it's an error, and is unhappy. Since the sqlstate code is the only useful machine-readable field in the error message, it is rather unfortunate for it to not reflect the actual failure status. Of course, I can special-case code 22P06 in my code and treat it as a warning (which is what I'll have to do anyway, for 8.2 compatibility), but I think: - the warning should be assigned a different code in the 01 class. - The 22P06 code should be retired, i.e. not reassigned to a real error in future (because then any bug workaround similar to mine would break). Thoughts? -- ams ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] minimal update
On Nov 8, 2007, at 10:46 , Andrew Dunstan wrote: Tom Lane wrote: Michael Glaesemann [EMAIL PROTECTED] writes: What would be the disadvantages of always doing this, i.e., just making this part of the normal update path in the backend? (1) cycles wasted to no purpose in the vast majority of cases. (2) visibly inconsistent behavior for apps that pay attention to ctid/xmin/etc. (3) visibly inconsistent behavior for apps that have AFTER triggers. There's enough other overhead in issuing an update (network, parsing/planning/etc) that a sanely coded application should try to avoid issuing no-op updates anyway. The proposed trigger is just a band-aid IMHO. I think having it as an optional trigger is a reasonable compromise. Right. I never proposed making this the default behaviour, for all these good reasons. The point about making the app try to avoid no-op updates is that this can impose some quite considerable code complexity on the app, especially where the number of updated fields is large. It's fragile and error-prone. A simple switch that can turn a trigger on or off will be nicer. Syntax support for that might be even nicer, but there appears to be some resistance to that, so I can easily settle for the trigger. This confirms what I thought. Thanks. Michael Glaesemann grzm seespotcode net ---(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] autovacuum_freeze_max_age
Hi Tom, At 02:51 10/11/2007, Tom Lane wrote: Uh, no, it's called only when we've advanced datfrozenxid, which is a pretty uncommon event. So I think the real hole in the proposal is that there would be a long and not-very-predictable delay between changing the parameter and having it really take effect. Well, I believe in a situation where you need to change its value, it would happen quite soon. If there were a big use-case for changing this setting on the fly then I'd be more worried about it, but I don't see one really ... Just found out that the default value had one less 0 than I thought and that it was the reason autovacuum was vacuuming 20 GB+ insert-only tables every 3 days or so on a box of ours... And that it needs an outage (or a switchover to the slave, actually) to change the value, when it does not seem obvious why. It's really just a matter of being able to change any setting that can be at runtime rather than having to restart. A few of them can be pretty annoying (e.g. max_connections and such), but are understandable given what's required to make it possible to change them, but if there's no real reason... :-) Jacques. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [BUGS] Nasty tsvector can make dumps unrestorable
Bruce Momjian [EMAIL PROTECTED] writes: However, I am still unclear if the dump code is correct because I don't see the backslash preserved in \\'' cases, just cases: test= INSERT INTO Foo(bar) VALUES (E'\\''x'); You're just confused. That produces a word whose contents are the two characters 'x, so either '\'x' or '''x' would be legitimate output. However, I'd prefer to see Teodor fix this, because it needs to be back-patched too, and I'm not entirely sure if there are other consequences. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] data access automatic filter
[EMAIL PROTECTED] [EMAIL PROTECTED] writes: I'm evaluating the option of write a PG-extension which allow the application to apply some per-connection filters. This filters work at table level and remove records which aren't allowed from a select result-set. It sounds a whole lot like you're trying to re-invent this: http://pgfoundry.org/projects/veil/ regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Free Space Map thoughts
Just a small thought. If the file decision is preferred, then it might be worth considering to create a file per tablespace. Thanks, Gokul. On Nov 9, 2007 7:15 PM, Simon Riggs [EMAIL PROTECTED] wrote: On Fri, 2007-11-09 at 13:27 +, Heikki Linnakangas wrote: Alvaro Herrera wrote: One idea is to have the first FSM page be movable, and create it by extending the table when as soon as it's first needed (this would be the first vacuum that needs to record free space on the table). The page number used is recorded in the relcache entry (and pg_class). Further FSM pages use a fixed position. If the table grows beyond the first fixed position before creating the first FSM page, reserve that one for the first FSM page and record that. It wouldn't need to be movable. We could just allocate the first FSM page when the table grows bigger than say 10 pages. The first FSM page would always be at block 11, and it could store the free space information for pages 0-10 as well. I'm not particularly worried about the bloat on small tables, though. If a table that used to take 8k bytes now takes 16k, who cares. You wouldn't need to load the FSM pages to shared buffers unless the FSM is actually used. I'm more worried about the shared memory space we would waste if we have FSM blocks for very small tables. Now we use very few bytes per block, which is memory efficient for lots of small tables and bad with lots of large tables. Putting the FSM in blocks might change that the other way around. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] sqlstate 22P06 is a warning in an error's clothing
Abhijit Menon-Sen [EMAIL PROTECTED] writes: The server logs WARNING: nonstandard use of \\ in a string literal at character 44, but the message comes with sqlstate code 22P06, which is in an error class (Data exception). So my application thinks it's an error, and is unhappy. If you are trying to tell errors from warnings, why are you not looking first at PQresultStatus (or equivalent in other client APIs)? I think reassigning the message to another sqlstate will break more clients than it fixes. It's not the only problematic case either, eg ereport(NOTICE, (errcode(ERRCODE_NAME_TOO_LONG), errmsg(identifier \%s\ will be truncated to \%.*s\, ident, len, ident))); regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [SQL] functions are returns columns
Gregory Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: Not quite --- it's just returns setof record. I did test my example before posting it: postgres=# postgres=# CREATE or replace FUNCTION getfoo (IN int, OUT int, OUT int) returns setof record(int,int)AS $$ SELECT 1,2 union all select 2,3; $$ LANGUAGE SQL; Interesting --- if you try it in anything older than 8.3, it will fail. What is happening here is that the (int,int) is being taken as a typmod (per Teodor's work to allow typmods for all data types), and apparently in this path we never check to see if it's a *valid* typmod. Now typmods are always discarded from function argument and result types, but it seems like we'd better validate that they're legal for the datatype anyway. Otherwise there will be confusion of just this sort. Comments, objections? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Win32 shared memory speed
I've seen several comments about shared memory under Windows being slow, but I haven't had much luck finding info in the archives. What are the details of this? How was it determined and is there a straightforward test/benchmark? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] 8.2.3: Server crashes on Windows using Eclipse/Junit
On 10/26/07, I wrote: On 10/26/07, Magnus Hagander [EMAIL PROTECTED] wrote: Can you try the attached patch? See how many backends you can get up to. This patch changes from using a single thread for each backend started to using the builtin threadpool functionality. It also replaces the pid/handle arrays with an i/o completion port. The net result is also, imho, much more readable code :-) The patch looks good; I'm not set up to build yet, but I should be able to test it sometime in the next week. Sorry about the long delay; I retested with the 8.3-beta2 installer, still Win2003 SP2 32bit. I stopped the test at 824 connections because I was about to run out of memory (1.25GB RAM + 3.75GB swap), but postmaster VM space usage was only 191MB. As for desktop heap, only 65KB of the service heap was allocated, or about 80 bytes per connection. No danger of hitting limits in the kernel memory pools either. Available RAM seems like a pretty reasonable limit to me ;) ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Refactor parse_type.c's lookup API?
In this thread: http://archives.postgresql.org/pgsql-sql/2007-11/msg00038.php we saw that there is considerable new potential for user confusion created by the 8.3 extensions to allow any type name to be decorated with typmod parameters (which can be either constants or identifiers). I see no problem if the typmod data is actually checked, but there are various code paths that don't care about the typmod and thus don't bother to check. The problematic places are those that call LookupTypeName, typenameType, or typenameTypeId, but don't bother to call typenameTypeMod. While we could go around and fix just the buggy spots, I am thinking that will leave us open to more errors of omission in the future. I am inclined to refactor the API so that it's not possible to make this mistake, by merging typenameTypeMod into the other entry points. I'd add a parameter int32 *typmod to each of them, which could be passed as NULL by callers that don't care about typmod, but the validation would still be carried out. I'm also a bit inclined to change LookupTypeName to return a Type struct (ie, a syscache tuple) the way typenameType does, because it looks to me like all callers either do or should look up the syscache entry anyway --- in some cases only indirectly by calling get_typisdefined, but still there are going to be multiple syscache fetches there if we don't change the return value. Comments? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Segmentation fault using digest from pg_crypto
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: If you don't re-run a contrib module's sql script then you don't get any new functions that may have been added to the module. So I think the real issue here is that we ought to deprecate the idea of skipping that step, period. The good news is that the scripts have CREATE OR REPLACE function so it would clean up everything to match the library you just installed. Hmm ... now that you mention it: some of them do, some of them don't. And some of them wrap the whole script in BEGIN/COMMIT, which means that a conflict against existing definitions would prevent any new definitions from being added. Seems like maybe we need an explicit project policy that contrib scripts should be able to overwrite an existing older installation properly; maybe with some error reports, but not failing outright. If so, someone will have to do the legwork of really making that happen ... any volunteers? I have cleaned up the contrib install/uninstall to be more consistent, used CREATE OR REPLACE function consisently, and removed transaction blocks. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Segmentation fault using digest from pg_crypto
Bruce Momjian wrote: Tom Lane wrote: Marko Kreen [EMAIL PROTECTED] writes: On 11/10/07, Bruce Momjian [EMAIL PROTECTED] wrote: OK, first question. How would someone have the 8.0 signatures in 8.2? Don't they reinstall pgcrypto for every major release? Why reinstall if the functions come from dump? At least in skype we stumbled on the problem. But if the reinstall is widespread practice, then indeed it may not be worth bothering. If you don't re-run a contrib module's sql script then you don't get any new functions that may have been added to the module. So I think the real issue here is that we ought to deprecate the idea of skipping that step, period. When did we ever promote such a skipping idea? Seems we need to add some documentation for this. Once we get the /contrib docs moved to SGML I can add something. Documentation added. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Segmentation fault using digest from pg_crypto
Bruce Momjian [EMAIL PROTECTED] writes: I have cleaned up the contrib install/uninstall to be more consistent, used CREATE OR REPLACE function consisently, and removed transaction blocks. ... and broken the buildfarm ... regards, tom lane ---(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] Segmentation fault using digest from pg_crypto
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I have cleaned up the contrib install/uninstall to be more consistent, used CREATE OR REPLACE function consisently, and removed transaction blocks. ... and broken the buildfarm ... OK, fixed, thanks. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
