[HACKERS] Documentation on new features?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I know it's very early, but I think this is going to be important if you want people (like me) who want to help test. First off, the only reference to nested transaction I could find in the documentation is a note that PostgreSQL does not have nested transactions. It most certainly does - I tried it and it works. Should I submit documentation changes to the sgml files for nested transcations? It will most likely be wrong, but maybe enough will be right that those who know better won't have to spend much time. - -- Jonathan Gardner [EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFA+Uscqp6r/MVGlwwRAsQSAJ9CX/WhMuH5fmKU1YL8IYz3PvfGsQCghSuc 2cdV2JDb78zomxLDGAjzPqQ= =y0lN -END PGP SIGNATURE- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Documentation on new features?
Jonathan M. Gardner wrote: Should I submit documentation changes to the sgml files for nested transcations? It will most likely be wrong, but maybe enough will be right that those who know better won't have to spend much time. It would certainly be appreciated if you could work on this. Please contact the author of the nested transaction feature to get the technical details. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] Documentation on new features?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Saturday 17 July 2004 9:55 am, Peter Eisentraut wrote: Jonathan M. Gardner wrote: Should I submit documentation changes to the sgml files for nested transcations? It will most likely be wrong, but maybe enough will be right that those who know better won't have to spend much time. It would certainly be appreciated if you could work on this. Please contact the author of the nested transaction feature to get the technical details. I posted a patch on the SQL reference pages to the patches list. I didn't touch the tutorial or the MVCC page, and I don't know enough to do anything in the internals section. - -- Jonathan Gardner [EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFA+Xobqp6r/MVGlwwRAkmsAJ4u6xhaj5RQFFaK5QXtBwDE7aKZjwCgkr/G grJErCtlWYalhqOrNr5Pty8= =DrKL -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Fun with nested transactions in PL/pgSQL
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Check out this gem. = CREATE TABLE t (i int); = CREATE OR REPLACE FUNCTION test() RETURNS VOID LANGUAGE 'plpgsql' AS ' BEGIN INSERT INTO t VALUES (1); EXECUTE ''BEGIN''; DELETE FROM t; EXECUTE ''ROLLBACK''; RETURN; END '; = SELECT test(); server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. In the log: LOG: server process (PID 23748) was terminated by signal 11 LOG: terminating any other active server processes LOG: background writer process (PID 23740) exited with exit code 1 FATAL: the database system is in recovery mode LOG: all server processes terminated; reinitializing LOG: database system was interrupted at 2004-07-17 12:18:09 PDT LOG: checkpoint record is at 0/B116FC LOG: redo record is at 0/B116FC; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 565; next OID: 25419 LOG: database system was not properly shut down; automatic recovery in progress LOG: record with zero length at 0/B1173C LOG: redo is not required LOG: database system is ready - -- Jonathan Gardner [EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFA+Xv7qp6r/MVGlwwRAm4ZAKCJe11K5vYNtSAbS/VbqBMM6G+YcACguref 8T4f1oQ7gjaNak5s6WhIdwU= =eEU+ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Why we really need timelines *now* in PITR
If we do not add timeline numbers to WAL file names, we will be forced to destroy information during recovery. Consider the following scenario: 1. You have a WAL directory containing, say, WAL segments 0010 to 0020 (for the purposes of this example I won't bother typing out realistic 16-digit filenames, but just use 4-digit names). 2. You discover that your junior DBA messed up badly and you need to revert to yesterday evening's state. Let's say the chosen recovery end time is in the middle of file 0014. 3. You run the recovery process. At its end, the WAL end pointer will be 0014 and some offset. If we simply run forward from this situation, then we will be overwriting existing WAL records in the existing files 0014-0020. This is bad from the point of view of not wanting to discard information (what if we decide we should have recovered to a later time??), but there is an even more serious reason for not doing that. Suppose we suffer a crash sometime after recovery. On restart, the system will start replaying the logs, and *there will be nothing to keep it from replaying all the way to the end of file 0020*. (The files will contain proper, in-sequence page headers, so the tests that normally detect recycled log segments won't think there is anything wrong.) This will leave you with a thoroughly corrupt database. One way to solve this would be to physically discard 0015-0020 as soon as we decide we're stopping short of the end of WAL. I think that is unacceptable on don't-throw-away-information grounds. I think it would be far better to invent the timeline concept. Then, our old WAL files would be named say 0001.0010 through 0001.0020, and we would start logging into 0002.0014 after recovery. A slightly tricky point is that we have to sew together the end of one timeline and the start of the next --- for instance, we only want the front part of 0001.0014, not the back part, to be part of the new timeline. Patrick Macdonald told me about a pretty baroque scheme that DB2 uses for this, but I think it would be simplest if we just copied the appropriate amount of data from 0001.0014 into 0002.0014 and then ran forward from there. Copying a max of 16MB of data doesn't sound very onerous. During WAL replay or recovery, there would be a notion of the target timeline that you are trying to recover to a point within. The rule for selecting which WAL segment file to read is use the one with largest timeline number less than or equal to the target, and never less than the timeline number you used for the previous segment. So for example if we realized we'd chosen the wrong recovery target time, we could backpedal and redo the same recovery process with target timeline 0001, ignoring any WAL segments that had been archived with timeline 0002. Alternatively, if we were simply doing crash recovery in timeline 0002, we could stop at (say) segment 0002.0018, and we'd know that we should ignore 0001.0019 because it is not in our timeline. 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] Fun with nested transactions in PL/pgSQL
On Sat, Jul 17, 2004 at 12:20:27PM -0700, Jonathan M. Gardner wrote: = CREATE TABLE t (i int); = CREATE OR REPLACE FUNCTION test() RETURNS VOID LANGUAGE 'plpgsql' AS ' BEGIN INSERT INTO t VALUES (1); EXECUTE ''BEGIN''; DELETE FROM t; EXECUTE ''ROLLBACK''; RETURN; END '; = SELECT test(); server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. I described this behavior not less than two weeks ago, including why it happens. Try begin; select test(); commit. The submitted savepoint patch prevents this from happening, and some other misbehaviors as well. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) I personally became interested in Linux while I was dating an English major who wouldn't know an operating system if it walked up and bit him. (Val Henson) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] NT + deadlock intended behaviour ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I'm doing some experiments with NT, I din't expect this behaviuor: create table test ( a integer ); insert into test values (3); insert into test values (4); insert into test values (5); insert into test values (6); SESSION 1;SESSION 2; begin; begin; update test set a = 300 where a = 3; update test set a = 40 where a = 4; ~ begin; update test set a = 400 where a = 4; BLOCKED ~ update test set a = 30 where a = 3; ~ DEAD LOCK DETECTED ~ commit; UNBLOCKED-- !?!?! ~ here I'm able to do another commit why SESSION 1 was unblocked ? If I repeat again but I do an abort: SESSION 1;SESSION 2; begin; begin; update test set a = 300 where a = 3; update test set a = 40 where a = 4; ~ begin; update test set a = 400 where a = 4; BLOCKED ~ update test set a = 30 where a = 3; ~ DEAD LOCK DETECTED ~ abort; STILL BLOCKED Why that commit unblock the SESSION 1? Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFA+bD+7UpzwH2SGd4RAq0VAJ9rZQ3aJmsJM6WSlLqIERJzDDS9iQCeL5rT rF7PkCaJ59PWNQw4CK6uvug= =Rb3s -END PGP SIGNATURE- ---(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
[HACKERS] NT and aborted transaction
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, still experimenting NT: kalman=# begin; BEGIN kalman=# select * from a; ERROR: relation a does not exist kalman=# begin; BEGIN kalman=# select * from test; ERROR: current transaction is aborted, commands ignored until end of transaction block I think the second begin shall fail instead of open an already invalid transaction. Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFA+bEz7UpzwH2SGd4RAujVAJ4wBx64eSKwokcBUl/l3sJJV9LelwCcDf/Z K/kv375ERUxwvY8tGUmhmYA= =K8OT -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] unused variable
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I'm compiling the last postgres CVS version and I get: vacuum.c: In function `repair_frag': vacuum.c:1528: warning: unused variable `myXID' Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFA+bCj7UpzwH2SGd4RAtyKAJsH6V22SijBN3VD/HJ/sWoLO8Ta7wCcD1wt U2VsEehBuK64q4/kjsHA4g4= =TsL8 -END PGP SIGNATURE- ---(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] plperl (7.5)
elein wrote: The new plperl returns sets by having the function return an array. This requires that the entire array be built before anything is returned. It seems to me that that does not scale very well. The technique of RETURN NEXT; scales much better. Indeed. For example, you maybe selecting rows, doing a little formating and adding some information and returning the rows as you process them. It the table you are selecting is very large, you still have to hold the results in memory to return them all at once. Am I misunderstanding something or can someone explain the reasoning? The reasoning behind the current set of new features is simple - what has been provided is what we were able to get done before feature freeze. It is not by any means all that is intended by the plperlng project. The rest will have to wait for another release. In an ideal implementation both techniques would be possible since returning the array is kind of cool ;-) Quite so. I think we would have to provide a callback procedure to add a resultset member, and we could easily have it set a flag so that if it had been used in the function call we would use that method of accumulating resultset members, otherwise we would use the current all-at-once method. But before we embark on any such exercise, we need to have a good discussion of future features and APIs. My intention was to start that discussion after we put the 7.5 stuff to bed. Please feel free to join the plperlng-devel mailing list. cheers andrew ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Vacuum Cost Documentation?
Related to autovacuum work, I was looking into the new vacuum delay functionality. I might be missing something, but I can't find anything on it in the developer docs. Is that right? Also, in the default postgresql.conf there is: #vacuum_cost_naptime = 50 # 0-1000 milliseconds however psql reports this value as 0. I assume this is just postgresql.conf is just wrong. Can we fix this so it's consistent? Thanks, Matthew O'Connor ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Vacuum Cost Documentation?
Matthew T. O'Connor wrote: Related to autovacuum work, I was looking into the new vacuum delay functionality. I might be missing something, but I can't find anything on it in the developer docs. Is that right? Also, in the default postgresql.conf there is: #vacuum_cost_naptime = 50 # 0-1000 milliseconds however psql reports this value as 0. I assume this is just postgresql.conf is just wrong. Can we fix this so it's consistent? I am not really fond of the term 'naptime'. Delay? Sleep? -- 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 7: don't forget to increase your free space map settings
Re: [HACKERS] NT + deadlock intended behaviour ?
On Sun, Jul 18, 2004 at 01:06:39AM +0200, Gaetano Mendola wrote: I'm doing some experiments with NT, I din't expect this behaviuor: First of all, let me point that the behavior on deadlock has been agreed to change. Instead of only aborting the innermost transaction, it will abort the whole transaction tree. The reason is simple. Consider this case: create table foo (a int); insert into test values (1); insert into test values (2); begin; update foo set a=20 where a=1; begin; update foo set a=21 where a=2; begin; update foo set a=22 where a=2; LOCKED begin; update foo set a=23 where a=1; DEADLOCK DETECTED If I abort only the innermost transaction on session 2, the application writer can have a retry loop on it, so it will issue the begin again and the same update. Since session 1 is still locked, session 2 will see a deadlock again. The user could cope with detecting a deadlock condition and do something else, but frankly I don't think we can leave this as is. SESSION 1;SESSION 2; begin; begin; update test set a = 300 where a = 3; update test set a = 40 where a = 4; ~ begin; update test set a = 400 where a = 4; BLOCKED ~ update test set a = 30 where a = 3; ~ DEAD LOCK DETECTED ~ commit; UNBLOCKED-- !?!?! ~ here I'm able to do another commit why SESSION 1 was unblocked? Because when you COMMIT a subtransaction that was in aborted state, the parent is aborted too. So when you COMMIT you are not really committing, you are aborting. That gives session 1 green light to continue, because session 2 has released all locks. If I repeat again but I do an abort: SESSION 1;SESSION 2; begin; begin; update test set a = 300 where a = 3; update test set a = 40 where a = 4; ~ begin; update test set a = 400 where a = 4; BLOCKED ~ update test set a = 30 where a = 3; ~ DEAD LOCK DETECTED ~ abort; STILL BLOCKED This is what you expected, wasn't it? When you ABORTed the subtransaction, the parent did not abort, so it held it locks. So session 1 does not have the lock it needs. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Now I have my system running, not a byte was off the shelf; It rarely breaks and when it does I fix the code myself. It's stable, clean and elegant, and lightning fast as well, And it doesn't cost a nickel, so Bill Gates can go to hell. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] NT and aborted transaction
On Sun, Jul 18, 2004 at 01:07:32AM +0200, Gaetano Mendola wrote: kalman=# begin; BEGIN kalman=# select * from a; ERROR: relation a does not exist kalman=# begin; BEGIN kalman=# select * from test; ERROR: current transaction is aborted, commands ignored until end of transaction block I think the second begin shall fail instead of open an already invalid transaction. We are not gonna use begin for starting a subtransaction, so this is a nonissue. Please apply the savepoints patch and try again. If you have current CVS tip you'll have for me to post a new patch, in the next hour or so, because the code has drifted. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Ellos andaban todos desnudos como su madre los parió, y también las mujeres, aunque no vi más que una, harto moza, y todos los que yo vi eran todos mancebos, que ninguno vi de edad de más de XXX años (Cristóbal Colón) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] NT + deadlock intended behaviour ?
Alvaro Herrera [EMAIL PROTECTED] writes: First of all, let me point that the behavior on deadlock has been agreed to change. Instead of only aborting the innermost transaction, it will abort the whole transaction tree. Who agreed to that? Your example is entirely unconvincing --- deadlock is very far from being the only failure that will recur indefinitely, if an app writer is so foolish as to code an indefinite retry loop. Any simple illegal-data-value error will act the same. I do not think declaring by fiat that certain types of errors abort the whole tree is acceptable from the user end or reasonable from the implementation end. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] NT + deadlock intended behaviour ?
On Sun, Jul 18, 2004 at 01:16:17AM -0400, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: First of all, let me point that the behavior on deadlock has been agreed to change. Instead of only aborting the innermost transaction, it will abort the whole transaction tree. Who agreed to that? Huh? I showed this example to Bruce on IRC several days ago, while you were away -- he said (or at least I understood) that he talked to you and you agreed to this behavior. Maybe I was confused about what he said. This is a small change from the implementation POV anyway (two lines patch). -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) El número de instalaciones de UNIX se ha elevado a 10, y se espera que este número aumente (UPM, 1972) ---(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] NT + deadlock intended behaviour ?
Gaetano Mendola [EMAIL PROTECTED] writes: why SESSION 1 was unblocked ? ... Why that commit unblock the SESSION 1? IMHO session 1 should have been unblocked in both cases as soon as session 2's subtransaction failed. We have always made a practice of releasing a transaction's locks immediately upon failure. The reason it does not seem to act that way is that Alvaro's taken a shortcut in WaitForTransaction: subtransactions do not take out a separate transaction lock and so WaitForTransaction has to wait for the top-level transaction. Your sub-COMMIT fails the outer transaction (because the inner one is failed) and so the top transaction lock releases at that point. In the sub-ABORT case the outer transaction stays good and continues to hold its lock. I've already suggested that this shortcut is no good, and now I'm pretty sure of it ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] PITR COPY Failure (was Point in Time Recovery)
I decided to produce a nice simple example, so that anyone could hopefully replicate what I am seeing. The scenario is the same as before (the 11 steps), but the CREATE TABLE and COPY step has been reduced to: CREATE TABLE test0 (filler VARCHAR(120)); COPY test0 FROM '/data0/dump/test0.dat' USING DELIMITERS ','; Now the file 'test0.dat' consists of (128293) identical lines, each of 109 'a' charactors (plus end of line) A script to run the whole business can be found here : http://homepages.paradise.net.nz/markir/download/pitr-bug.tar.gz (It will need a bit of editing for things like location of Pg, PGDATA, and you will need to make your own data file) The main points of interest are: - anything =128392 rows in test0.dat results in 1 archived log, and the recovery succeeds - anything =128393 rows in test0.dat results in 2 or more archived logs, and recovery fails on the second log (and gives the zero length redo at 0/1E0 message). Let me know if I can do any more legwork on this (I am considering re-compiling with WAL_DEBUG now that example is simpler) regards Mark Simon Riggs wrote: On Thu, 2004-07-15 at 10:47, Mark Kirkwood wrote: I tried what I thought was a straightforward scenario, and seem to have broken it :-( Here is the little tale 1) initdb 2) set archive_mode and archive_dest in postgresql.conf 3) startup 4) create database called 'test' 5) connect to 'test' and type 'checkpoint' 6) backup PGDATA using 'tar -zcvf' 7) create tables in 'test' and add data using COPY (exactly 2 logs worth) 8) shutdown and remove PGDATA 9) recover using 'tar -zxvf' 10) copy recovery.conf into PGDATA 11) startup This is what I get : LOG: database system was interrupted at 2004-07-15 21:24:04 NZST LOG: recovery command file found... LOG: restore_program = cp %s/%s %s LOG: recovery_target_inclusive = true LOG: recovery_debug_log = true LOG: starting archive recovery LOG: restored log file from archive LOG: checkpoint record is at 0/A48054 LOG: redo record is at 0/A48054; undo record is at 0/0; shutdown FALSE LOG: next transaction ID: 496; next OID: 25419 LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 0/A48094 LOG: restored log file 0001 from archive LOG: record with zero length at 0/1E0 LOG: redo done at 0/130 LOG: restored log file 0001 from archive LOG: restored log file 0001 from archive PANIC: concurrent transaction log activity while database system is shutting down LOG: startup process (PID 13492) was terminated by signal 6 LOG: aborting startup due to startup process failure The concurrent access is a bit of a puzzle, as this is my home machine (i.e. I am *sure* noone else is connected!) I can see what is wrong now, but you'll have to help me on details your end... The log shows that xlog 1 was restored from archive. It contains a zero length record, which indicates that it isn't yet full (or thats what the existing recovery code assumes it means). Which also indicates that it should never have been archived in the first place, and should not therefore be a candidate for a restore from archive. The double message restored log file can only occur after you've retrieved a partially full file from archive - which as I say, shouldn't be there. Other messages are essentially spurious in those circumstances. Either: - somehow the files have been mixed up in the archive directory, which is possible if the filing discipline is not strict - various ways, unfortunately I would guess this to be the most likely, somehow - the file that has been restored has been damaged in some way - the archiver has archived a file too early (very unlikely, IMHO - thats the most robust bit of the code) - some aspect of the code has written a zero length record to WAL (which is supposed to not be possible, but we musn't discount an error in recent committed work) - there may also be an effect going on with checkpoints that I don't understand...spurious checkpoint warning messages have already been observed and reported, Best regards, Simon Riggs ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
