[GENERAL] set-level update fails with unique constraint violation
Hello, this fails with duplicate key value: CREATE TABLE x ( i INT NOT NULL UNIQUE ); INSERT INTO x (i) VALUES (1), (2), (3); UPDATE x SET i = i + 1; are there any plans to make this work? -- Roman Neuhauser -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem with index in OR'd expression
Ragnar wrote: Reguardless of the issue whether pl/pgsql could be expected to optimize this case, I find it difficult to imagine a scenario where this kind of coding makes sense. I understand that in some cases on would like to do this with a *variable* to simplify logic, but what possible gain can be had from doing this with a constant, other that obfuscation? Well, in one way it's a variable, but in another a constant. It's a variable in the context of general PG usage... e.g., my application code may call the function with whatever parameters a user chooses, leaving some parameters null and others not. Within the context of the function (after calling), these variables are constant and I'm attempting to use my OR syntax as shorthand to avoid having to use a dynamic statement *only* because of this situation. As I've mentioned, this approach seems to work with MSSQL 6.5+, which I assume we consider as a valid competitor to PG... if this didn't work anywhere else, I probably wouldn't even have brought it up. I'll re-iterate another question I attempted to pose which was: what have other PG application developers done in this situation? Is it most common to just use dynamic statements? Thanks for your response. jl ---(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: [GENERAL] Problem with index in OR'd expression
Tom Lane wrote: [EMAIL PROTECTED] writes: I would submit that in that situation, it would be reasonable for a user to expect my suggested syntax to still use the indicated indexes. The only thing that will make that work is if indexed_col IS NULL were an indexable condition, which it isn't because the PG index API only supports indexed_col operator something as an indexable condition (IS NULL is not an operator, and even if it were, there's no something on its righthand side). Fixing this has been on the radar screen for awhile, but it's not done, largely for lack of agreement about a reasonably clean way to change that API. Sorry to keep this issue alive even longer, Tom, but I think I may've been unclear with my example. I was referring to the situation where one has this in a WHERE clause: ((vConstant IS NULL) OR (Table.IndexedCol = vConstant)) where vConstant is a *constant* parameter in a pl/pgsql function. In the latest versions (8.1 *or* 8.2), would you expect this to successfully use the index on Table.IndexedCol and not have PG be confused (into a sequential scan) by the (vConstant IS NULL) expression? As I indicated, I'm currently running 8.0.x, and am wondering whether it would be worth the effort to upgrade to 8.1 or 8.2 (Gentoo doesn't yet have PG at 8.2, and I'm a bit lazy with installing things outside of Portage) to solve this issue or whether I should just enable a workaround for now and keep an eye on future releases for a better solution to this problem. Thanks again, John ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Problem with index in OR'd expression
[EMAIL PROTECTED] wrote: Tom Lane wrote: you're still gonna lose because those are variables not constants ... Well, that *is* what I'm hoping to do. I understand how (0 IS NULL) is different from (variable IS NULL), but isn't it reasonable to expect that PG could evaluate that expression only once (knowing that the variable couldn't change during the course of the query execution) and then treat that expression as constant? Also, I should mention that in my case, the variables are actually constants either defined in the function param list (and thus non-modifiable within the function body) or defined with: DECLARE vC1 CONSTANT varchar(10) := 'blah' -type syntax. I would submit that in that situation, it would be reasonable for a user to expect my suggested syntax to still use the indicated indexes. Do you agree? It it possible that something other than picking up the 'IS NULL' boolean as constant has changed between 8.0.x 8.2 that might make this work? Thanks, jl ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Problem with index in OR'd expression
Tom Lane wrote: Well, you could update --- 8.2 contains code to recognize that the IS NULL expression is constant, but prior releases do not. That's excellent to hear -- I'd missed that in my perusing of the changelogs between 8.0.x and 8.2. That does give me one more reason to upgrade. It appears I did not mention what version I was running -- sorry for that, though you guessed it was 8.2. It's actually 8.0.x. However, if you're hoping to do this: ((vC1 IS NULL) OR (C1 = vC1)) AND ((vC2 IS NULL) OR (C2 = vC2)) ... you're still gonna lose because those are variables not constants ... Well, that *is* what I'm hoping to do. I understand how (0 IS NULL) is different from (variable IS NULL), but isn't it reasonable to expect that PG could evaluate that expression only once (knowing that the variable couldn't change during the course of the query execution) and then treat that expression as constant? I appreciate that you're saying that it won't work even in 8.2, but what I'm getting at is would it be possible to add it in the future? As I mentioned, I'm pretty sure that that must be what MSSQL (6.5, 7, 2000 and 2005 [all of which I've had some experience with]) seem to be doing. Now failing all of this, does any one have a better idea for what I'm trying to do? A simple syntax for optionally including WHERE criteria depending on the null-ness of variables (w/o having to go to dynamic execution)? Thanks for your reply Tom. jl ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Problem with index in OR'd expression
Hello, I've been using PostgreSQL for a few years and mostly love it. Aside from a few (perceived, anyway) annoying limitations in PL/PGSQL (which I almost exclusively am using for db interaction), I'm very satisfied with it. I ran across this problem several months back and decided to blow it off back then, but now I'm really wanting to understand why it's happening, if there's a work around, if I'm doing something wrong, and if it's just a shortcoming / bug if the developers are aware of it and might implement a fix some day. I've already done a fair amount of testing (a total of hours, I'm sure) and Googling around on it. I also read this FAQ on indexes: http://www.postgresql.org/docs/faqs.FAQ.html#item4.6 I think my issue is not explained by any of the resources I reviewed. To simplify my actual problem, I conducted a very simple test. First I defined a table: create table t ( c1 int primary key, c2 int ) with the single index (primary key). Then I filled the table with 100,000 rows using a quick pl/pgsql function (c1 = 1, 2, 3... 100,000). Then I ran these two tests (with EXPLAIN in pgAdmin): select * from t where c1 = 75000; select * from t where ((0 is null) OR (c1 = 75000)); The first one properly uses the index on c1, the second does not. Obviously, a human looking at the second one would realize it's essentially identical to the first and properly evaluate the (0 is null) part once (since it's immutable) and then ignore it for the rest of the searching. Now, I'm sure some of you might ask why the hell are you doing that in the first place? I have a good reason. I write a lot of pl/pgsql functions that are search functions with a list of *optional* parameters. I don't know ahead of time whether a user will include on or not. In MSSQL, what I'm able to do (with no obvious index problems that I've seen) is add those all to the WHERE clause like this: ((vC1 IS NULL) OR (C1 = vC1)) AND ((vC2 IS NULL) OR (C2 = vC2)) ... (here vC1 and vC2 represent variables passed into the pl/pgsql function). So my question were basically asked at the beginning of this post: is there another way to get the optimizer to understand what I'm trying to do here? Is this a known problem? Is it working as preferred and unlikely to change any time soon? Is there some setting I can hit somewhere to make it work like I want? The only solution that I've come up with so far is making all of my statements that otherwise wouldn't have to be dynamic and then only including criteria for ones that I really need (based on the tested nullness of the variables), but I find that highly annoying and have run into other problems as a result as well. I'd appreciate any suggestions you might have to help resolve this. Thank, John Lawler ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] pgpool log errors
I've started using pgpool and while everything appears to be working, I've been getting a lot of the following errors in my logs: ERROR: pid 14761: pool_read: EOF encountered This seems to be simple enough - the client/user probably just canceled the request and isn't anything to be concerned about? I get about a dozen of these per hour on a system that has about 25,000 connections/day. ERROR: pid 14761: ProcessFrontendResponse: failed to read kind I have no idea what this one means. I'm concerned because I get a lot of these Between one and two dozen per hour. I've googled. I've searched the postgres mailing list archives. Asked around in IRC. Can't really find anything to explain it. I would greatly appreciate clarification from anyone with insight to this. Regards, ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
