Re: [HACKERS] [GENERAL] Cascades Failing
On Wed, 17 Aug 2005, Stephan Szabo wrote: > > On Tue, 16 Aug 2005, Stephan Szabo wrote: > > > On Tue, 16 Aug 2005, Tom Lane wrote: > > > > > I think this would take some generalization of afterTriggerInvokeEvents, > > > which now might or might not find the target rel in the EState it's > > > passed, but otherwise it doesn't seem too invasive. Thoughts? > > > > That doesn't seem too bad really, looking at afterTriggerInvokeEvents it > > doesn't look like it'd be that much work to change it to handle that case. > > I can put a patch together to see what it looks like. > > I did some work on this, and I'm getting a couple of other failures from > other parts of the foreign key regression test (specifically an error > that is no longer erroring in a multi-column on update set default). I'm > going to need to look more closely to see if I can figure out why. I think I see at least part of what's going on. It looks to me that events are being added, but not fired because they weren't marked. The event sequence seems to be: after trigger begin query add events for the actual statement after trigger end query fire trigger add events for the triggered statement finish trigger skip event added for triggered statement because it's not marked. Is the correct answer to continue marking and running the triggers until there are no immediate triggers left to run for this case? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Windows + IP6 progress
Andrew Dunstan <[EMAIL PROTECTED]> writes: > Chuck McDevitt wrote: >> I think it's because it's __stdcall, and the name gets mangled to >> include the number of parameters. > Aha! now it makes sense. How do we get around that in the configure tests? I thought it might be something like that ... but the question remains: how/why is getaddrinfo different from all the other library routines we probe for? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] SQL/XML extension
Dear Josh and Andrew, Thanks for the prompt replies. For now it's just a paper. It was Rob and Pim's mission to find out if the SQL /XML standard can be implemented using the postgresql extension mechanism. Building it into the parser was no option. Best, Djoerd. On Thu, 18 Aug 2005, Andrew Dunstan wrote: > > IIRC, Peter Eisentraut noted a while ago that implementing the SQL/XML > functions properly would require building them into the postgresql > parser as special cases. That of course would mean we wouldn't be using > the extension mechanism, and is something we should normally shy away > from, but I think it could be contemplated for something that is in the > standard. > > The paper does not seem to have addressed the issue of how this could be > done other than bu using the extension mechanism - that seems a bit of a > pity, although maybe that's exactly the topic they were set. > > cheers > > andrew > > Josh Berkus wrote: > > >Paul, Rob, > > > >I just read with some interest your paper on XML queries with PostgreSQL. > >I'm particularly puzzled by some of your conclusions, and thought you might > >want to discuss them with the PGSQL-Hackers mailing list. > > > >Particulary: > >Functions should be able to have a variable amount of arguments. > > > >I find this conclusion odd, because function overloading (that is, the idea > >that a function is defined by the combination of its name and the number and > >type of arguments) is now enshrined in the SQL2003 standard. Of course, > >I wouldn't be at all surprised to find out that the SQL committee had broken > >their own standard. ;-) > > > >Re-defining AS would, as you notice, break many things. However, you could > >easily get around this through quoting. While that would not be exactly > >adherent to the standard, it's easier that re-writing the parser. > > > >In some ways, it seems to me that SQL/XML might be better defined as a > >separate interface to the database; that is, it's own "shell" which is > >incompatible with SQL (since the committee seems to have deliberately made it > >incompatible). > > > >Thoughts? > > > > > > > ---(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] Windows + IP6 progress
Chuck McDevitt wrote: I think it's because it's __stdcall, and the name gets mangled to include the number of parameters. Aha! now it makes sense. How do we get around that in the configure tests? cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Windows + IP6 progress
I think it's because it's __stdcall, and the name gets mangled to include the number of parameters. > -Original Message- > From: Andrew Dunstan [mailto:[EMAIL PROTECTED] > Sent: Thursday, August 18, 2005 4:44 PM > To: Chuck McDevitt > Cc: Tom Lane; PostgreSQL-development > Subject: Re: [HACKERS] Windows + IP6 progress > > > The mingw header has pretty much this with WINSOCK_API_LINKAGE IN OUT > and FAR dissolved away. > > The standard test complains about it being an unresolved reference when > it is declared as "char getaddrinfo (); ". If we remove that and instead > include the header the test passes. I have no idea why that should be > the case for this function and not for others. > > cheers > > andrew > > > Chuck McDevitt wrote: > > >The definition in WS2tcpip.h > > > >WINSOCK_API_LINKAGE > >int > >WSAAPI > >getaddrinfo( > >IN const char FAR * nodename, > >IN const char FAR * servname, > >IN const struct addrinfo FAR * hints, > >OUT struct addrinfo FAR * FAR * res > >); > > > > > >(IN, FAR, and OUT are #defined to empty string). > > > >WINSOCK_API_LINKAGE is __declspec(dllimport) > >WSAAPI is __stdcall > > > >So, nothing magic with #defines of the name getaddrinfo. > > > > > > > > > >>-Original Message- > >>From: [EMAIL PROTECTED] [mailto:pgsql-hackers- > >>[EMAIL PROTECTED] On Behalf Of Tom Lane > >>Sent: Thursday, August 18, 2005 3:47 PM > >>To: Andrew Dunstan > >>Cc: PostgreSQL-development > >>Subject: Re: [HACKERS] Windows + IP6 progress > >> > >>Andrew Dunstan <[EMAIL PROTECTED]> writes: > >> > >> > >>>. what do we do about the getaddrinfo test? I'm almost inclined not > >>> > >>> > >to > > > > > >>>do it on windows, and assume that if we have ws2_32.dll we have it. > >>> > >>> > >>There's something mighty fishy about that. AC_REPLACE_FUNCS works on > >>Windows for the other cases it's used for (no?), so what's different > >>about getaddrinfo? Perhaps Microsoft has #define'd that name as > >>something else, or some equally ugly crock? It'd be useful to look > >> > >> > >into > > > > > >>their header files and see exactly how and where getaddrinfo is > >>declared. > >> > >>regards, tom lane > >> > >>---(end of > >> > >> > >broadcast)--- > > > > > >>TIP 3: Have you checked our extensive FAQ? > >> > >> http://www.postgresql.org/docs/faq > >> > >> > > > > > > > > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Windows + IP6 progress
The mingw header has pretty much this with WINSOCK_API_LINKAGE IN OUT and FAR dissolved away. The standard test complains about it being an unresolved reference when it is declared as "char getaddrinfo (); ". If we remove that and instead include the header the test passes. I have no idea why that should be the case for this function and not for others. cheers andrew Chuck McDevitt wrote: The definition in WS2tcpip.h WINSOCK_API_LINKAGE int WSAAPI getaddrinfo( IN const char FAR * nodename, IN const char FAR * servname, IN const struct addrinfo FAR * hints, OUT struct addrinfo FAR * FAR * res ); (IN, FAR, and OUT are #defined to empty string). WINSOCK_API_LINKAGE is __declspec(dllimport) WSAAPI is __stdcall So, nothing magic with #defines of the name getaddrinfo. -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-hackers- [EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Thursday, August 18, 2005 3:47 PM To: Andrew Dunstan Cc: PostgreSQL-development Subject: Re: [HACKERS] Windows + IP6 progress Andrew Dunstan <[EMAIL PROTECTED]> writes: . what do we do about the getaddrinfo test? I'm almost inclined not to do it on windows, and assume that if we have ws2_32.dll we have it. There's something mighty fishy about that. AC_REPLACE_FUNCS works on Windows for the other cases it's used for (no?), so what's different about getaddrinfo? Perhaps Microsoft has #define'd that name as something else, or some equally ugly crock? It'd be useful to look into their header files and see exactly how and where getaddrinfo is declared. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Windows + IP6 progress
IPv6 exists in a "production quality" state only in XP sp1, XP sp2, and Windows 2003. There was an optional prototype stack for 2000, but not production quality and not installed by default. XP non-service-pack had IPv6, but not production-quality. One thing you could do is dynamically load getaddrinfo from ws2_32.dll at run time. If the DLL doesn't exist, or getaddrinfo isn't in that ws2_32.dll, you could then fail gracefully (somehow). You call something like: hWs2_32 = LoadLibraryA("ws2_32.dll"); if (hWs2_32) { Getaddrinfoptr = GetProcAddress(hWs2_32, "getaddrinfo"); } The GetProcAddress will return null if getaddrinfo doesn't exist, and a pointer to the routine if it does. > -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-hackers- > [EMAIL PROTECTED] On Behalf Of Andrew Dunstan > Sent: Thursday, August 18, 2005 3:17 PM > To: PostgreSQL-development > Subject: [HACKERS] Windows + IP6 progress > > > I have just managed to get pg server and client (cvs tip) talking IPv6 > on Windows. :-) > > 1. Building > - added in library in configure.in: > AC_CHECK_LIB(ws2_32, main) > - faked out getaddrinfo test in resulting configure and force answer > to "yes" > - added these lines to src/include/port/win32/sys/socket.h: > #include > #define gai_strerrorA(err) "undetermined getaddrinfo error" > > After installation and initdb, I edited postgresql.conf to set > listen_addresses to '127.0.0.1, ::1' just to make sure what we were > getting. > > 2. Running without IPv6 driver installed. > The build works, although it complains about IPv6 addresses. But I > could run it with IPv4 addresses quite happily - the IPv6 addresses just > fail, but they don't stop us running. > > 3. Running with IPv6 driver installed >Now the build does not complain about IPv6 addresses (either in > pg_hba.conf or postgresql.conf) >And this command works: psql -h ::1 -l > > > So the remaining questions are: > . what do we do about the getaddrinfo test? I'm almost inclined not to > do it on windows, and assume that if we have ws2_32.dll we have it. > . what to do about the gai_strerror mess (import our own but leave out > our own getaddrinfo?) > . make sure that this doesn't break less modern Windows platforms than > mine (XP Pro SP1). How ubiquitous is ws2_32.dll? > > I have asked a few people to test this build. I don't want to publish > its location openly, but if anyone wants to help they can drop me an > email. Alternatively, some kind person could provide a site on a nice > fat pipe for an 18Mb download. > > > cheers > > andrew > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] t_ctid chains
Paul Tillotson <[EMAIL PROTECTED]> writes: > For some time, I have wondered: what does postgres use "t_ctid chains" > for? It seems like it is useful to find the "newer" version of a > tuple. However, wouldn't that eventually get found anyway? A > sequential scan scans the whole table, and so it will find the new > tuple. Since indexes contain all tuples, so will an index scan. The problem is not that the table reader wouldn't "find" the tuple. The problem is that he'd disregard it as too new for his snapshot. The essential point of the EvalPlanQual mechanism is to identify tuples that we should consider visible for modification even though the MVCC rules say no. Basically, the normal search mechanisms will find a prior state of the row (whichever state was committed when we took our snapshot) and then we have to "chain up" to the latest state by following the t_ctid links. There's some discussion of this in the manual under http://developer.postgresql.org/docs/postgres/transaction-iso.html#XACT-READ-COMMITTED regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Windows + IP6 progress
The definition in WS2tcpip.h WINSOCK_API_LINKAGE int WSAAPI getaddrinfo( IN const char FAR * nodename, IN const char FAR * servname, IN const struct addrinfo FAR * hints, OUT struct addrinfo FAR * FAR * res ); (IN, FAR, and OUT are #defined to empty string). WINSOCK_API_LINKAGE is __declspec(dllimport) WSAAPI is __stdcall So, nothing magic with #defines of the name getaddrinfo. > -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-hackers- > [EMAIL PROTECTED] On Behalf Of Tom Lane > Sent: Thursday, August 18, 2005 3:47 PM > To: Andrew Dunstan > Cc: PostgreSQL-development > Subject: Re: [HACKERS] Windows + IP6 progress > > Andrew Dunstan <[EMAIL PROTECTED]> writes: > > . what do we do about the getaddrinfo test? I'm almost inclined not to > > do it on windows, and assume that if we have ws2_32.dll we have it. > > There's something mighty fishy about that. AC_REPLACE_FUNCS works on > Windows for the other cases it's used for (no?), so what's different > about getaddrinfo? Perhaps Microsoft has #define'd that name as > something else, or some equally ugly crock? It'd be useful to look into > their header files and see exactly how and where getaddrinfo is > declared. > > regards, tom lane > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq ---(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] Found: some pretty ugly VACUUM bugs
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Interesting failure mode. While reading it I was suddenly struck by the > thought that overwriting storage managers may somehow be more resistent > to these kind of failures. This may well be true, because there is > never need for a VACUUM process which would fail to correctly determine > whether a tuple is truly dead or not; but in the end, concurrent > processes have to follow t_ctid chains anyway. Yeah. I think the Oracle style has got about exactly the same issues if they try to reuse space in the rollback segment. > I also considered whether the correct test was xmin=xmax, or rather a > transaction-tree test was needed. Then I realized that it's not > possible for a transaction to create a tuple chain crossing a > subtransaction boundary. So the xmin=xmax test is correct. Actually, I thought of a counterexample: consider a tuple updated twice in the same xact: XMINXMAXt_ctid T1 X0 X1 -> T2 T2 X1 X1 -> T3 T3 X1 - -> T3 (self) If we remove T2 we'll be unable to chain from T1 to T3, which would definitely be wrong. So I'm now thinking that the special case in HeapTupleSatisfiesVacuum has to go, too. >> This is going to require a number of changes since there are several >> places that follow t_ctid chains. > I wonder whether this should be refactored so all of them use a single > piece of code. Most of the places end up feeding into EvalPlanQual, but passing down the original tuple's XMAX to there will require changing the APIs of heap_update, heap_delete, and heap_lock_tuple (sigh). regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] t_ctid chains
(Prompted by nearby thread about VACUUM FULL bugs, but not having anything to do with that properly speaking.) Hackers, For some time, I have wondered: what does postgres use "t_ctid chains" for? It seems like it is useful to find the "newer" version of a tuple. However, wouldn't that eventually get found anyway? A sequential scan scans the whole table, and so it will find the new tuple. Since indexes contain all tuples, so will an index scan. I infer that the there must be some sort of optimization to make it worth (a) using extra space in the disk pages and (b) causing the extra complexity such as the bugs mentioned in VACUUM FULL. So: what are the t_ctid chains good for? If this is too long or too elementary to type, can someone point me to the source code that uses t_ctid chains? Regards, Paul Tillotson ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Windows + IP6 progress
Andrew Dunstan <[EMAIL PROTECTED]> writes: > . what do we do about the getaddrinfo test? I'm almost inclined not to > do it on windows, and assume that if we have ws2_32.dll we have it. There's something mighty fishy about that. AC_REPLACE_FUNCS works on Windows for the other cases it's used for (no?), so what's different about getaddrinfo? Perhaps Microsoft has #define'd that name as something else, or some equally ugly crock? It'd be useful to look into their header files and see exactly how and where getaddrinfo is declared. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Found: some pretty ugly VACUUM bugs
On Thu, Aug 18, 2005 at 03:48:55PM -0400, Tom Lane wrote: > The only solution I can see (short of abandoning lazy VACUUM) is that > we have to make the code that follows t_ctid chains more wary. That > code is already aware (at least in the places I looked at) that a t_ctid > link might lead to an empty slot, but if there is a tuple in the slot > it just assumes that that is really a descendant version of the tuple > pointing to it. That won't do. But I believe it would work if we also > test that the XMIN of the tuple in the slot equals the XMAX of the > referencing tuple. If they are unequal, we can conclude that the > original child tuple is dead and has been removed, so there is no > current version of the referencing tuple. Interesting failure mode. While reading it I was suddenly struck by the thought that overwriting storage managers may somehow be more resistent to these kind of failures. This may well be true, because there is never need for a VACUUM process which would fail to correctly determine whether a tuple is truly dead or not; but in the end, concurrent processes have to follow t_ctid chains anyway. I also considered whether the correct test was xmin=xmax, or rather a transaction-tree test was needed. Then I realized that it's not possible for a transaction to create a tuple chain crossing a subtransaction boundary. So the xmin=xmax test is correct. I assume you will make a note on this somewhere, just in case we forget later. > This is going to require a number of changes since there are several > places that follow t_ctid chains. I wonder whether this should be refactored so all of them use a single piece of code. -- Alvaro Herrera () "Cómo ponemos nuestros dedos en la arcilla del otro. Eso es la amistad; jugar al alfarero y ver qué formas se pueden sacar del otro" (C. Halloway en La Feria de las Tinieblas, R. Bradbury) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Windows + IP6 progress
I have just managed to get pg server and client (cvs tip) talking IPv6 on Windows. :-) 1. Building - added in library in configure.in: AC_CHECK_LIB(ws2_32, main) - faked out getaddrinfo test in resulting configure and force answer to "yes" - added these lines to src/include/port/win32/sys/socket.h: #include #define gai_strerrorA(err) "undetermined getaddrinfo error" After installation and initdb, I edited postgresql.conf to set listen_addresses to '127.0.0.1, ::1' just to make sure what we were getting. 2. Running without IPv6 driver installed. The build works, although it complains about IPv6 addresses. But I could run it with IPv4 addresses quite happily - the IPv6 addresses just fail, but they don't stop us running. 3. Running with IPv6 driver installed Now the build does not complain about IPv6 addresses (either in pg_hba.conf or postgresql.conf) And this command works: psql -h ::1 -l So the remaining questions are: . what do we do about the getaddrinfo test? I'm almost inclined not to do it on windows, and assume that if we have ws2_32.dll we have it. . what to do about the gai_strerror mess (import our own but leave out our own getaddrinfo?) . make sure that this doesn't break less modern Windows platforms than mine (XP Pro SP1). How ubiquitous is ws2_32.dll? I have asked a few people to test this build. I don't want to publish its location openly, but if anyone wants to help they can drop me an email. Alternatively, some kind person could provide a site on a nice fat pipe for an 18Mb download. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Found: some pretty ugly VACUUM bugs
I believe I've traced down the cause of the Assert trap in VACUUM FULL that Teodor reported here: http://archives.postgresql.org/pgsql-hackers/2005-06/msg01278.php The case that VACUUM is tripping up on is one in which some concurrent transaction (call it X1) updates and then later deletes a row. By the time VACUUM starts, X1 is committed, but it's still within the OldestXmin horizon, so there are open transactions that should not see its effects. We have two tuples in the table, call 'em T1 and T2, representing the original and updated states of the row: XMINXMAXt_ctid T1 X0 X1 points to T2 T2 X1 X1 points to T2 (ie, itself) (X0 is whichever transaction originally inserted T1; it's old enough to not be interesting. The reason T1 must point to T2 is that a READ COMMITTED transaction that decides to update T1 must be able to find T2 instead.) The reason this configuration is troublesome is that HeapTupleSatisfiesVacuum has this (premature?) optimization in it: if (TransactionIdEquals(HeapTupleHeaderGetXmin(tuple), HeapTupleHeaderGetXmax(tuple))) { /* * inserter also deleted it, so it was never visible to anyone * else */ return HEAPTUPLE_DEAD; } This code causes VACUUM to delete T2, even though T1 is still pointing at it --- and because T1's XMAX is past the OldestXmin horizon, T1 will not be deleted. The Assert that Teodor saw arose from the following specific series of events (which can only occur if T2 is on a lower- numbered page than T1): * T2 is removed and its slot is marked free. * repair_frag moves some unrelated tuple into T2's slot. * repair_frag visits T1, decides it has to move a tuple chain, and moves the new occupant of T2's slot (which is already wrong anyway). * when update_hint_bits scans T2's page, it finds the wrong number of MOVED_IN tuples because the tuple that was moved into T2's slot is now MOVED_OFF. This triggers the Assert. However this is merely the least interesting symptom of the problem. If the state with T1 pointing to a tuple that is actually unrelated is allowed to persist, then after the VACUUM is done someone else could find T1 and then update the new occupant of T2's slot under the mistaken assumption that it's the descendant of T1. My first instinct for a quick fix was to delete the above-quoted section of HeapTupleSatisfiesVacuum. This would ensure that we never remove a tuple that some other tuple might still be pointing at, unless we are going to remove the referencing tuple as well. But this only fixes the problem for VACUUM FULL, which exclusive-locks the whole table. With lazy VACUUM, concurrent transactions can see the intermediate state with T2 gone and T1 not. Thus they could write some new tuple into T2's slot and then make the mistaken update before VACUUM gets a chance to remove T1. The only solution I can see (short of abandoning lazy VACUUM) is that we have to make the code that follows t_ctid chains more wary. That code is already aware (at least in the places I looked at) that a t_ctid link might lead to an empty slot, but if there is a tuple in the slot it just assumes that that is really a descendant version of the tuple pointing to it. That won't do. But I believe it would work if we also test that the XMIN of the tuple in the slot equals the XMAX of the referencing tuple. If they are unequal, we can conclude that the original child tuple is dead and has been removed, so there is no current version of the referencing tuple. It is clearly true that if the XMIN and XMAX are different, the putative child tuple isn't really the child. I believe this test is sufficient, because VACUUM never removes rows generated by still-in-progress transactions, and so it is not possible for a single transaction to store different tuples into the same slot over its lifetime. This is going to require a number of changes since there are several places that follow t_ctid chains. With the change in place, though, I think it's OK to leave the xmin=xmax optimization in place in HeapTupleSatisfiesVacuum. Comments? Anyone see any flaws in the reasoning? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] problem with coalesce
Oleg, > ERROR: column "query.query" must appear in the GROUP BY clause or be > used in an aggregate function Apparently the parser thinks you have an aggregate function in there. Do you? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] problem with coalesce
Hi there, I don't understand why this select doesn't works: regression=# select coalesce( tp_rewrite_substitute(ARRAY[query, test.target, test.sample]), query) from test,to_tsquery('new2 & york2 & hotel') as query where query @ test.target; ERROR: column "query.query" must appear in the GROUP BY clause or be used in an aggregate function as a workaround I could explicitly specify query, but this doesn't looks nice regression=# select coalesce( tp_rewrite_substitute(ARRAY[query, test.target, test.sample]), to_tsquery('new2 & york2 & hotel')) from test,to_tsquery('new2 & york2 & hotel') as query where query @ test.target; Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Need help on SpikeSource Testing Contest
Folks, Those of you who went to OSCON may have heard that SpikeSource is having a contest to increase testing code coverage of popular OSS projects. The contest involves prizes of up to $2500 for test writers. Given that we have people in the community who have been thinking about testing, and just need a little encouragement, I'd really like PostgreSQL to participate (and so would SpikeSource). There's two things I'd like to have help with in this regard: 1) I need the committed help of one or more pgsql-committers who can evaluate stuff submitted to our regression test suite. The contest doesn't coincide with our development cycle (it's 9/15 to 12/30), so we may need to evaluate a new test at a time when we can't actually commit it to HEAD. Andrew? Neil? Bruce? 2) Do we have a coding standards document anywhere? There's a lot of stuff in the Developer FAQ but it doesn't quite lay out at "this is what your code has to have to be accepted". I'd swear I've seen something of the sort ... -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] SQL/XML extension
IIRC, Peter Eisentraut noted a while ago that implementing the SQL/XML functions properly would require building them into the postgresql parser as special cases. That of course would mean we wouldn't be using the extension mechanism, and is something we should normally shy away from, but I think it could be contemplated for something that is in the standard. The paper does not seem to have addressed the issue of how this could be done other than bu using the extension mechanism - that seems a bit of a pity, although maybe that's exactly the topic they were set. cheers andrew Josh Berkus wrote: Paul, Rob, I just read with some interest your paper on XML queries with PostgreSQL. I'm particularly puzzled by some of your conclusions, and thought you might want to discuss them with the PGSQL-Hackers mailing list. Particulary: Functions should be able to have a variable amount of arguments. I find this conclusion odd, because function overloading (that is, the idea that a function is defined by the combination of its name and the number and type of arguments) is now enshrined in the SQL2003 standard. Of course, I wouldn't be at all surprised to find out that the SQL committee had broken their own standard. ;-) Re-defining AS would, as you notice, break many things. However, you could easily get around this through quoting. While that would not be exactly adherent to the standard, it's easier that re-writing the parser. In some ways, it seems to me that SQL/XML might be better defined as a separate interface to the database; that is, it's own "shell" which is incompatible with SQL (since the committee seems to have deliberately made it incompatible). Thoughts? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] SQL/XML extension
I'm thinking about GiST approach http://www.cs.arizona.edu/xiss/ Oleg On Thu, 18 Aug 2005, Djoerd Hiemstra wrote: Dear PostreSQL hackers, For the interested, here: http://www.cs.utwente.nl/~hiemstra/courses/reports/sqlxml.pdf you find a little report discussing possibilities to implement the SQL/XML standard using the PostgreSQL extension mechanism. The report is written by Master students following the course "XML & Databases" at U Twente. Comments are welcome. (I am not on the developers list, so please reply to my personal email as well) Best regards, Djoerd. Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(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] SQL/XML extension
Paul, Rob, I just read with some interest your paper on XML queries with PostgreSQL. I'm particularly puzzled by some of your conclusions, and thought you might want to discuss them with the PGSQL-Hackers mailing list. Particulary: Functions should be able to have a variable amount of arguments. I find this conclusion odd, because function overloading (that is, the idea that a function is defined by the combination of its name and the number and type of arguments) is now enshrined in the SQL2003 standard. Of course, I wouldn't be at all surprised to find out that the SQL committee had broken their own standard. ;-) Re-defining AS would, as you notice, break many things. However, you could easily get around this through quoting. While that would not be exactly adherent to the standard, it's easier that re-writing the parser. In some ways, it seems to me that SQL/XML might be better defined as a separate interface to the database; that is, it's own "shell" which is incompatible with SQL (since the committee seems to have deliberately made it incompatible). Thoughts? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] SQL/XML extension
Djoerd, > http://www.cs.utwente.nl/~hiemstra/courses/reports/sqlxml.pdf > you find a little report discussing possibilities to implement the > SQL/XML standard using the PostgreSQL extension mechanism. The report is > written by Master students following the course "XML & Databases" at U > Twente. Comments are welcome. (I am not on the developers list, so > please reply to my personal email as well) Cool, thanks! Is this something that somebody is actually working on, or just the paper? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] transactions not working properly ?
Ali Baba wrote: Hi Michael, i want to support explicit commit/rollback support in pl/pgsql instead of using autocommit feature. The fine manual is your friend: http://www.postgresql.org/docs/8.0/static/transaction-iso.html http://www.postgresql.org/docs/8.0/static/tutorial-transactions.html Sincerely, Joshua D. Drake my requirement is to know how transactions work in postgres generally and how to support transaction managment in pl/pgsql thanks for your help. -- Asif Ali. --- Michael Fuhr <[EMAIL PROTECTED]> wrote: [This question would probably be more appropriate in pgsql-general than in pgsql-hackers.] On Wed, Aug 17, 2005 at 05:53:14AM -0700, Ali Baba wrote: can any one describe how the transaction are being handled in postgres. I think you're talking about how PL/pgSQL exception handlers work with transactions. See the documentation: http://www.postgresql.org/docs/8.0/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING function given below should actually insert the desire values in test table but it do not save them. A complete test case would make it easier help. All we see in the example is the start of a transaction and the creation of a function -- we don't see how you're actually using it nor what output (e.g., error messages) it produces. begin x := 1; insert into test values (210,20); x := x/0; RETURN 0; exception when others then raise info 'error generated '; commit; RETURN 0; end; The "Trapping Errors" documentation states: When an error is caught by an EXCEPTION clause, the local variables of the PL/pgSQL function remain as they were when the error occurred, but all changes to persistent database state within the block are rolled back. Since the divide-by-zero error is in the same block as the INSERT, the INSERT is rolled back. Also, you can't issue COMMIT inside a function -- see the "Structure of PL/pgSQL" documentation: http://www.postgresql.org/docs/8.0/static/plpgsql-structure.html Functions and trigger procedures are always executed within a transaction established by an outer query they cannot start or commit that transaction, since there would be no context for them to execute in. However, a block containing an EXCEPTION clause effectively forms a subtransaction that can be rolled back without affecting the outer transaction. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] transactions not working properly ?
Hi Michael, i want to support explicit commit/rollback support in pl/pgsql instead of using autocommit feature. my requirement is to know how transactions work in postgres generally and how to support transaction managment in pl/pgsql thanks for your help. -- Asif Ali. > --- Michael Fuhr <[EMAIL PROTECTED]> wrote: > > > [This question would probably be more appropriate > in > > pgsql-general > > than in pgsql-hackers.] > > > > On Wed, Aug 17, 2005 at 05:53:14AM -0700, Ali Baba > > wrote: > > > can any one describe how the transaction are > being > > > handled in postgres. > > > > I think you're talking about how PL/pgSQL > exception > > handlers work > > with transactions. See the documentation: > > > > > http://www.postgresql.org/docs/8.0/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING > > > > > function given below should actually insert the > > desire > > > values in test table but it do not save them. > > > > A complete test case would make it easier help. > All > > we see in the > > example is the start of a transaction and the > > creation of a function -- > > we don't see how you're actually using it nor what > > output (e.g., error > > messages) it produces. > > > > > begin > > > x := 1; > > > insert into test values (210,20); > > > x := x/0; > > > > > > RETURN 0; > > > > > > exception > > > when others then > > > raise info 'error generated '; > > > commit; > > > RETURN 0; > > > end; > > > > The "Trapping Errors" documentation states: > > > > When an error is caught by an EXCEPTION clause, > > the local variables > > of the PL/pgSQL function remain as they were > when > > the error occurred, > > but all changes to persistent database state > > within the block are > > rolled back. > > > > Since the divide-by-zero error is in the same > block > > as the INSERT, > > the INSERT is rolled back. Also, you can't issue > > COMMIT inside a > > function -- see the "Structure of PL/pgSQL" > > documentation: > > > > > http://www.postgresql.org/docs/8.0/static/plpgsql-structure.html > > > > Functions and trigger procedures are always > > executed within a > > transaction established by an outer query they > > cannot start or > > commit that transaction, since there would be no > > context for them > > to execute in. However, a block containing an > > EXCEPTION clause > > effectively forms a subtransaction that can be > > rolled back without > > affecting the outer transaction. > > > > -- > > Michael Fuhr > > > > ---(end of > > broadcast)--- > > TIP 4: Have you searched our list archives? > > > >http://archives.postgresql.org > > > > > __ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam > protection around > http://mail.yahoo.com > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] SQL/XML extension
Dear PostreSQL hackers, For the interested, here: http://www.cs.utwente.nl/~hiemstra/courses/reports/sqlxml.pdf you find a little report discussing possibilities to implement the SQL/XML standard using the PostgreSQL extension mechanism. The report is written by Master students following the course "XML & Databases" at U Twente. Comments are welcome. (I am not on the developers list, so please reply to my personal email as well) Best regards, Djoerd. -- Djoerd Hiemstra University of Twente Department of Computer Science PO Box 217, 7500 AE Enschede, The Netherlands URL: www.cs.utwente.nl/~hiemstra Tel: +31 53 4892335 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] [HACKERS] bitmap scan issues 8.1 devel
> "Merlin Moncure" <[EMAIL PROTECTED]> writes: > > Doing some testing on upcoming 8.1 devel and am having serious issues > > with new bitmap index scan feature. It is easy to work around (just > > disable it) but IMO the planner is using it when a regular index scan > > should be strongly favored. > > I think blaming the bitmap code is the wrong response. What I see in > your example is that the planner doesn't know what the LIMIT value is, > and accordingly is favoring a plan that isn't going to get blown out of > the water if the LIMIT is large. I'd suggest not parameterizing the > LIMIT. You nailed it...I hard coded the limit and everything was cool. In fact, the same problem contributes to the fact that I've had to run seqscan=false on all my production systems. It seemed the planner would randomly seqscan the table...now I know why. > (But hmm ... I wonder if we could use estimate_expression_value for > LIMIT items, instead of handling only simple Consts as the code does > now?) I absolutely support this :) In normal usage, the supplied limit is quite small, say 100 or less. Anyways, planner issues aside, parameterizing the limit is an elegant way to read records off a table when you don't know how many you are going to read in advance...I make heavy use of it :(. Merlin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] SHM_LOCK under Linux ... do we use this?
""Marc G. Fournier"" <[EMAIL PROTECTED]> writes > > I've done a grep through the code, to see if its something that we do use, and > it doesn't seem to come back with anything ... I believe its considered > common knowledge that 'swapping' for a database is evil, so am wondering > if there is some way that we can make use of this to help reduce/eliminate > that from happening? > There are some similar flags in other OS could help us to keep the memory resident in. However, it is not always a net win if we force it. This is because other parts of the system (like fork a process) may need memory, so the OS will pick up the "coldest" memory to be swapped. If we have already use our memory intensively, I don't think we will be swapped. On the contrary, if we force OS not to swap some memory, we may get other penalties like our processes have to be sarcrificed. Regards, Qingqing ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster