[HACKERS] Deadlock while doing VACUUM??
I've been experimenting with Matthew T. O'Connor's pg_avd (auto vacuum daemon), which is actually quite an interesting piece of software that so far seems to function pretty well. I've ported it to PG 7.2.4, which is the version I'm running on my system, and experimenting with it has uncovered what I can only consider a potentially serious bug in PG. When a heavy INSERT or UPDATE load on a table is occurring (lots of quick INSERTs or UPDATEs within a single transaction), a VACUUM ANALYZE (or just straight VACUUM) has a really good chance (10% or so) of causing either the INSERT/UPDATE or the VACUUM to fail with a deadlock detected error. Usually the INSERT/UPDATE is what fails. I have verified this with manual VACUUM and VACUUM ANALYZE commands issued on the command line while a perl script is generating a heavy load of inserts and/or updates, so this isn't pg_avd's fault. A straight ANALYZE on the table doesn't seem to trigger the problem, while a straight VACUUM does. Statistics collection is enabled -- it's required for pg_avd to run -- but I suspect that this is irrelevant (what does a straight VACUUM do with the statistics tables, if anything?). Even dropping the table and all of its indexes and recreating it does nothing to solve this problem, so I don't have any reason to suspect corruption of the table itself. Corruption elsewhere is a possibility, I suppose, but I haven't noticed any strangeness elsewhere. The only other thing of note is that a REALLY LONG running INSERT ... SELECT (with the source being a temporary table) is running concurrently on a different table in a different database. That, too, is running within a transaction, but I don't have reason to believe that it's having an effect. When it completes I'll do more testing to determine if it really did have an effect. I've searched the archives for references to this and have found nothing relevant (all references I found are to older implementations in which VACUUM locks the table). Have I managed to uncover something new here? I don't have 7.3.x (or later) installed so I can't test this myself on that or CVS tip, but I may have to if nobody else has the time. Anyone have any suggestions on what to do about this? This problem pretty much kills pg_avd's usefulness, at least on 7.2.x. :-( I'll be happy to supply the Perl script I'm using to do the inserts, if that'll help people track this down... -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] pgtypeslib/timestamp problem
I found it, well not really that is. It seems the problem is triggered by some gcc optimization. I'm using gcc 3.2.3. If I specify -O2 it does not work correctly, without optimization it does. Now the big question is which optimization is causing trouble. It certainly is not -ffast-math as I do include the corresponding error message which is not triggered. Unfortunately I cannot install the complete 7.4 CVS version right now to see if the timestamp code in the backend compiles correctly. So here's the question comojng out of this all, anyone out here with an idea why the timestamp Wed Jul 12 17:34:29 2000 becomes Wed Jul 12 4649:34:26.02 2000 when putting it into timestamp format and back out. The function is almost identical to timestamp_in resp. timestamp_out. If these two functions and their helpers are compield without -O2 the output is correct. Michael -- Michael Meskes Email: [EMAIL PROTECTED] ICQ: 179140304 Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(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] Deadlock while doing VACUUM??
Kevin Brown [EMAIL PROTECTED] writes: When a heavy INSERT or UPDATE load on a table is occurring (lots of quick INSERTs or UPDATEs within a single transaction), a VACUUM ANALYZE (or just straight VACUUM) has a really good chance (10% or so) of causing either the INSERT/UPDATE or the VACUUM to fail with a deadlock detected error. I was unable to replicate this in CVS tip, using pgbench -c 10 -t 1000 as the load generator. 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] pgtypeslib/timestamp problem
On Thu, Mar 27, 2003 at 09:07:48AM -0500, Tom Lane wrote: FWIW, I get the correct answer in CVS tip on Red Hat Linux 8.0, which is using gcc 3.2 (and -O2, per defaults in our makefiles): Thanks Tom. I just wonder what's different in my makefile. It just does: include $(top_builddir)/src/Makefile.global override CPPFLAGS := -O1 -g -I$(top_srcdir)/src/interfaces/ecpg/include -I$(top_srcdir)/src/include/utils $(CPPFLAGS) with -O1 just inserted of course. Tom, I take it you have released gcc 3.2 right? It seems my ione is based on a CVS prerelease: gcc -v Reading specs from /usr/lib/gcc-lib/i386-linux/3.2.3/specs Configured with: ../src/configure -v --enable-languages=c,c++,java,f77,proto,pascal,objc,ada --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-gxx-include-dir=/usr/include/c++/3.2 --enable-shared --with-system-zlib --enable-nls --without-included-gettext --enable-__cxa_atexit --enable-clocale=gnu --enable-java-gc=boehm --enable-objc-gc i386-linux Thread model: posix gcc version 3.2.3 20030316 (Debian prerelease) Michael -- Michael Meskes Email: [EMAIL PROTECTED] ICQ: 179140304 Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(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] inquiry
Jinqiang Han [EMAIL PROTECTED] writes: hi,Bruce Momjian I think you are wrong. multiple query in a string, such as SELECT;SELECT will invoke pg_execute_query_string twice, It won't generate two parsetree. Try it in something other than psql. psql splits such a string into multiple submissions to the backend --- but other interfaces don't. 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] pgtypeslib/timestamp problem
Michael Meskes [EMAIL PROTECTED] writes: Tom, I take it you have released gcc 3.2 right? It seems my ione is based on a CVS prerelease: It's whatever came with RH 8.0 ... $ gcc -v Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --host=i386-redhat-linux --with-system-zlib --enable-__cxa_atexit Thread model: posix gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Autoheader plan
Tom Lane writes: Maybe I'm just a Luddite, but I've never understood what autoheader buys us that's worth the trouble of conforming to its restrictions. We wouldn't have to edit the config.h file by hand whenever some editing of configure.in occurs. That's all, but I'm not aware of any real restrictions that that would impose. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Autoheader plan
Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane writes: Maybe I'm just a Luddite, but I've never understood what autoheader buys us that's worth the trouble of conforming to its restrictions. We wouldn't have to edit the config.h file by hand whenever some editing of configure.in occurs. That's all, but I'm not aware of any real restrictions that that would impose. Mph. Well, there is some advantage in separating the manually-settable config options from the automatically set ones (don't have to worry about whether to edit config.h.in or config.h), so your plan is probably a good idea in any case. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] BUG: Vacuum Analyze - datumGetSize: Invalid typLen 0
Neil Conway [EMAIL PROTECTED] writes: On Wed, 2003-03-26 at 20:42, Thomas T. Thai wrote: ERROR: datumGetSize: Invalid typLen 0 Works for me: Me too. Could be a 64-bit issue? Or a bug in the compiler Thomas is using? Please get a stack trace from the point of the error and post that. Tom, since I am unable to send direct email to you because the larger 63.226.* ip class is blocked by five-ten-sg.com. I only have a small 63.226.186.152/29 block under that, but I get affected too. --- [EMAIL PROTECTED]: Connected to 192.204.191.242 but sender was rejected. Remote host said: 550 5.7.1 Probable spam from 63.226.186.153 refused - see http://www.five-ten-sg.com/blackhole.php?63.226.186.153 --- If you would like, I can create an account and you can test it out? Do you have a diff email address we could communicate through? The error: ERROR: datumGetSize: Invalid typLen 0 doesn't cause a crash on 7.4-snapshot. Is there a way to trace the problem to see what it's doing? Here is my GCC version: # gcc -v Using builtin specs. gcc version 2.95.3 20010315 (release) (NetBSD nb3) -- Thomas ---(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] Deadlock while doing VACUUM??
Even dropping the table and all of its indexes and recreating it does nothing to solve this problem, so I don't have any reason to suspect corruption of the table itself. Corruption elsewhere is a possibility, I suppose, but I haven't noticed any strangeness elsewhere. This sounds very similiar to problems I was having under the thread: Re: [HACKERS] BUG: Vacuum Analyze - datumGetSize: Invalid typLen 0 Dropping the table, indexes, and recreating it didn't help me either. I had to drop the database! What kind of platform are you on? 64-bit? I've noticed that 7.4-snapshot fixed a huge amount of bugs, but the vacuum analyze bug is still there. -- Thomas ---(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] Deadlock while doing VACUUM??
Tom Lane wrote: Kevin Brown [EMAIL PROTECTED] writes: When a heavy INSERT or UPDATE load on a table is occurring (lots of quick INSERTs or UPDATEs within a single transaction), a VACUUM ANALYZE (or just straight VACUUM) has a really good chance (10% or so) of causing either the INSERT/UPDATE or the VACUUM to fail with a deadlock detected error. I was unable to replicate this in CVS tip, using pgbench -c 10 -t 1000 as the load generator. I guess I should mention that I was doing VACUUM on the individual tables, not the entire database. I have no idea if that makes any difference. I'll see about compiling and installing CVS tip for testing. I'll also see about doing some testing on 7.3.2. Can anyone here independently verify what I've found on 7.2.4? You'll probably have to run a process that does continuous INSERTs on a table, and another that does continuous VACUUMs on the same table at the same time. -- Kevin Brown [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])
[HACKERS] compile failure in HEAD
I seem to be getting this: gmake[3]: Entering directory `/home/chriskl/pgsql-temp/src/backend/parser' bison -y -d gram.y gram.y:4260.4-4307.10: type clash (`boolean' `keyword') on default action gram.y:4307.11: parse error, unexpected :, expecting ; or | gmake[3]: *** [parse.h] Error 1 Chris ---(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] updateable cursors visibility
Bruce Momjian wrote: Peter Eisentraut wrote: Bruce Momjian writes: One idea is to require FOR UPDATE on the cursor --- while that prevents other transactions from changing the cursor, it doesn't deal with the current transaction modifying the table outside the cursor. That would only keep existing rows from being deleted but not new rows from being added. One idea is to have UPDATE/DELETE WHERE CURRENT OF behave like UPDATE/DELETE do now when they find a row that is locked by another transaction --- they wait to see if the transaction commits or aborts, then if committed they follow the tid to the newly updated row, check the WHERE clause to see if it still is satisfied, then perform the update. (Is this correct?) Surely it would have to do something like that, but that's a matter of the transaction isolation, not the sensitivity. It doesn't do anything to address the potential problems I mentioned. Well, a unique constraint on the row would see your other INSERT. I don't see how making an INSERT visible in the cursor would help us, and I don't see how we would implement that except by rerunning the query for each fetch, which seems like a bad idea. I don't understand what you two are discussing. What's is SENSITIVE, INSENSITIVE or ASESNSITIVE ? regards, Hiroshi Inoue http://www.geocities.jp/inocchichichi/psqlodbc/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] updateable cursors visibility
Hiroshi Inoue wrote: Bruce Momjian wrote: Peter Eisentraut wrote: Bruce Momjian writes: One idea is to require FOR UPDATE on the cursor --- while that prevents other transactions from changing the cursor, it doesn't deal with the current transaction modifying the table outside the cursor. That would only keep existing rows from being deleted but not new rows from being added. One idea is to have UPDATE/DELETE WHERE CURRENT OF behave like UPDATE/DELETE do now when they find a row that is locked by another transaction --- they wait to see if the transaction commits or aborts, then if committed they follow the tid to the newly updated row, check the WHERE clause to see if it still is satisfied, then perform the update. (Is this correct?) Surely it would have to do something like that, but that's a matter of the transaction isolation, not the sensitivity. It doesn't do anything to address the potential problems I mentioned. Well, a unique constraint on the row would see your other INSERT. I don't see how making an INSERT visible in the cursor would help us, and I don't see how we would implement that except by rerunning the query for each fetch, which seems like a bad idea. I don't understand what you two are discussing. What's is SENSITIVE, INSENSITIVE or ASESNSITIVE ? In SQL99 standard, I see: - If the cursor is insensitive, then significant changes are not visible. - If the cursor is sensitive, then significant changes are visible. - If the cursor is asensitive, then the visibility of significant changes is implementation-dependent. So, I think we have two issues --- what does the cursor see, and what does the UPDATE see. I think we have to have the cursor remain INSENSITIVE, because we don't at fetch time whether WHERE CURRENT OF is going to be used. One nice thing is that while the standard says you can't specify INSENSITIVE for a WHERE CURRENT OF cursor, we can say it is ASENSITIVE and that will match our behavior. (We just need a boolean to make sure if they do specify INSENSTIVIVE that WHERE CURRENT OF throws an error.) Then, when we do the UPDATE, the UPDATE is SENSITIVE in that it sees the most recent version of the tuple, assuming the newest tuple still matches the WHERE clause of the cursor. The UPDATE also has to do contraint checking using current visibility. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] updateable cursors visibility
Hiroshi Inoue, But still can't explain this: SENSITIVE = not READ_ONLY It's in the ODBC Spec. Bruce Momjian wrote: Sorry, no idea. Peter's idea is that FOR UPDATE requires SENSITIVE, so INSENSITIVE has to be READONLY because the update has to see other changes to be accurate. I think clearly SENSITIVE/READONLY should be possible, so: READONLY/SENSITIVE possible READONLY/INSENSITIVEpossible FOR UPDATE/SENSITIVEpossible FOR UPDATE/INSENSITIVE not possible READONLY can be either way, while FOR UPDATE requires SENSITIVE. SENSITIVE doesn't mean *not INSENESITIVE*. INSENSITIVE doesn't mean *not SENSITIVE*. regards, Hiroshi Inoue http://www.geocities.jp/inocchichichi/psqlodbc/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) Regards! Han [EMAIL PROTECTED] 2003-03-28 ---(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] updateable cursors visibility
Bruce Momjian wrote: Hiroshi Inoue wrote: Bruce Momjian wrote: Peter Eisentraut wrote: Bruce Momjian writes: I don't understand what you two are discussing. What's is SENSITIVE, INSENSITIVE or ASESNSITIVE ? In SQL99 standard, I see: - If the cursor is insensitive, then significant changes are not visible. - If the cursor is sensitive, then significant changes are visible. - If the cursor is asensitive, then the visibility of significant changes is implementation-dependent. While a cursor is open, another application inserted a row which satisfies the condition to be contained in the cursor and committed. Then If the cursor is SENSITIVE, must it see the row ? If the cursor is INSENSITIVE, it mustn't see the row ? regards, Hiroshi Inoue http://www.geocities.jp/inocchichichi/psqlodbc/ ---(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] updateable cursors visibility
Bruce Momjian wrote: Sorry, no idea. Peter's idea is that FOR UPDATE requires SENSITIVE, so INSENSITIVE has to be READONLY because the update has to see other changes to be accurate. I think clearly SENSITIVE/READONLY should be possible, so: READONLY/SENSITIVE possible READONLY/INSENSITIVEpossible FOR UPDATE/SENSITIVEpossible FOR UPDATE/INSENSITIVE not possible READONLY can be either way, while FOR UPDATE requires SENSITIVE. SENSITIVE doesn't mean *not INSENESITIVE*. INSENSITIVE doesn't mean *not SENSITIVE*. regards, Hiroshi Inoue http://www.geocities.jp/inocchichichi/psqlodbc/ ---(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] [GENERAL] Solution to UPDATE...INSERT problem
AFAIK the except select won't see other inserts in uncommitted transactions. If those transactions are committed you will end up with the same problem. You can try it yourself, by manually doing two separate transactions in psql. Yeah, I see that now. You either have to lock the whole table, or lock at the application layer. Some time back I suggested a lock on arbitrary string feature for postgresql for this and various other purposes, but that feature probably wouldn't scale in terms of management (it requires 100% cooperation amongst all apps/clients involved). There's no select * from table where pkey=x for insert; which would block on uncommitted inserts/updates of pkey=x and other selects for insert/update. How about user locks? Isn't there something in contrib/ for that??? I could do a userlock on the primary key, whether it existed or not? Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
