[BUGS]
UPDATE with SUBSELECT and ORDER BY - Hi @ Postgres BUGS... SITUATION: I have a Table id groupid orderby 11 10 21 20 31 30 41 40 52 10 62 20 10 2 30 11 2 40 and a UNIQUE INDEX on columns "groupid,orderby" now i want to update UPDATE orderby SET orderby = orderby +10 WHERE groupid = 1; -> FAILS because of UNIQUE INDEX NOW I WANTED TO UPDATE REVERSE: UPDATE orderby SET orderby = orderby +10 WHERE groupid IN ( SELECT groupid FROM TABLE WHERE group_id = 1 ORDER BY orderby DESC ) ; -> FAILS TOO... SEEMS LIKE THE ORDER BY "DESC" WONT BE USED ?!? This is with Postgres Version 8.0.3 Regards, Claus Pruefer This message was sent using IMP, the Internet Messaging Program. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] Too many function calls in view with LEFT JOIN
Hello! Tom Lane <[EMAIL PROTECTED]> wrote > > Andreas Heiduk <[EMAIL PROTECTED]> writes: > > If a view which calls a function is LEFT JOINed to a table but not all > > result rows are matched by some criteria, then the function is called > > for each row of the view nevertheless. > > > Note that this seems to happen only for left joins, not for a inner join. > > I believe that's because the column is required to go to NULL in an > unjoined row. With a non-strict function, evaluating it after the join > could yield wrong answers. Try making the function strict. First: setting the function to STRICT really cuts down the number of function calls even with the SELECT via view. But as far as I can tell both queries should always return the same results. So I don't understand why the STRICT does not matter in the first query but is necessary in the second one. Especially because the JOIN criterium is not affected by the function call. Oh, and sorry that I forgot the version: "PostgreSQL 8.1.4 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.4 20060507 (prerelease) (Debian 4.0.3-3)" Best regards, Andreas Heiduk __ XXL-Speicher, PC-Virenschutz, Spartarife & mehr: Nur im WEB.DE Club! Jetzt gratis testen! http://freemail.web.de/home/landingpad/?mc=021130 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] error starting service on win2k platform
yups now i got it.. thanx a lot
Re: [BUGS]
[EMAIL PROTECTED] wrote: > and a UNIQUE INDEX on columns "groupid,orderby" > > now i want to update > > UPDATE > orderby SET orderby = orderby +10 > WHERE groupid = 1; > > -> FAILS because of UNIQUE INDEX This is a Known Issue(tm). (A "gotcha", some would say). http://archives.postgresql.org/pgsql-sql/2004-10/msg00146.php Surely you can find better references in the archives, but I'm too lazy. (keywords: "deferrable unique constraint") -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] updating unique columns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 | UPDATE | orderby SET orderby = orderby +10 | WHERE groupid = 1; | -> FAILS because of UNIQUE INDEX One workaround is to do it in two steps, assuming that orderby is > 0 for all rows you are changing: BEGIN; UPDATE mytable SET orderby = -orderby WHERE groupid = 1; UPDATE mytable SET orderby = -orderby+10 WHERE groupid = 1; COMMIT; | UPDATE | orderby SET orderby = orderby +10 | WHERE groupid IN | ( SELECT groupid | FROM TABLE WHERE group_id = 1 | ORDER BY orderby DESC ) No idea what you are trying to do here: try posting the actual SQL used. However, an ORDER BY has no meaning inside of a subselect passed to IN, as IN treats the list as bag of values, and does not care about the internal order. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200606011030 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFEfvpFvJuQZxSWSsgRAjQlAKDTNIpwbSEk0gcQp2pI7LokG+qwWgCgt/b6 /7ZWYDb4gufE4b0zCHyFZgg= =4LQ8 -END PGP SIGNATURE- ---(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: [BUGS] statement stuck when the connection grew up to 45 or more
<[EMAIL PROTECTED]> wrote > > Recently I facing one problem, when the connection for postgresql grow > up > to 45 or more, when I trigger a statement from WebApp > this statement will stuck forever. > I try to kill this transaction and then trigger the same statement > again > but it still the same. > But this time I leave the transaction there and try to kill other > connections that is not in use. > It's weird that after I kill around 5-10 unused connections, the > statement > start to run and finish. > I can hardly believe that's Postgres's problem. Are you sure the query was processing by the server? Try to do: ps -auxw|grep postgres to see if you can see the query was stuck there. Regards, Qingqing ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] Infinite increment of postgre.exe in taskmanager
Hi Newbie when it comes down to postgre. OS: Windows XP SP2 Pro Dutch When I run a DMS (Xinco) that uses postgre (I only use postgre for this), a service is started. Xinco uses a separate limited Xinco User account. After starting the service I see several postgre.exe services running of various sizes. That's fine. After an hour or so I see several postgre.exe services running (up to 200 and still increasing) and it is the postgre.exe 76kb that is in that huge number (even when nobody uses Xinco, it still continous). When I stop the PostgreSQL service then the large kb postgre.exe disappears from the task manager ...but all those 76kb postgre.exe remain in memory. Any ideas? Stefan ---(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
