Re: [HACKERS] Automatic function replanning
Christopher Browne wrote: > > Not unless you do something that would cause the planner to make > > different choices next time. (Such as changing the ANALYZE statistics, > > perhaps.) The TODO item is OK as stated, it's just talking about > > mechanism and not the things that might trigger the mechanism. > > A mechanism might be to bump up the stats stored for pg_autovacuum, > which would encourage a table to be re-ANALYZEd. > > That may not immediately change ANALYZE statistics, but it's > something... > > Even if there is NO feedback mechanism on statistics, if we know the > plan was pretty bad, it is surely at least *a* feedback to invalidate > the plan. Something has to cause the replan to be better, and that is one possibility. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Log of CREATE USER statement
Peter Eisentraut wrote: Letting createuser.c hash the password would be the biggest win. Both of these things are now done, and I have sent notices to pgadmin and phppgadmin asking for equivalent adjustments. Um, didn't receive a notice. I'm a little uncomfortable using an arbitrary md5 algorithm to encrypt the password before sending it to the backend. How about supplying an official pg encryption routine through libpq? Regards, Andreas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [pgadmin-hackers] Client-side password encryption
Andreas Pflug wrote: > Dave Page wrote: > > So did you just rip it from there into psql? I don't see it in the > > list of libpq exports so if thats not the case, on Windows at least > > we'll need to change the api, and possibly the dll name as well to > > avoid any compatibility issues. > > And a prototype in libpq-fe.h wouldn't hurt either... And a macro, to > enable distinguishing md5-enabled libpq versions from older versions. So it appears that pg_md5_encrypt is not officially exported from libpq. Does anyone see a problem with adding it to the export list and the header file? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Automatic function replanning
On Saturday 2005-12-17 16:28, Lukas Smith wrote: > Bruce Momjian wrote: > > * Flush cached query plans when the dependent objects change, > > when the cardinality of parameters changes dramatically, or > > when new ANALYZE statistics are available > > Wouldn't it also make sense to flush a cached query plan when after > execution it is determined that one or more assumptions that the cached > query plan was based on was found to be off? Like the query plan was > based on the assumption that a particular table would only return a hand > full of rows, but in reality it returned a few thousand. > > regards, > Lukas > Proposed rewrite * Mark query plan for flush (opportunistic replan) when: ** dependent objects change, ** cardinality of parameters changes sufficiently (per planner parameter) ** when new ANALYZE statistics are available and per planner parameter differ sufficiently from prior statistics. * Mark plan as "tried" when parameters of returned set out of statistical control, create alternate plan hill-climbing to statical control. ** Too many/too few rows relative to plan expectations *** Auto-sample for better statistics? ** History of plan shows throughput time for result set varies excessively (need more execution stability, possibly at expense of median optimality). > ---(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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Recovery from multi trouble
Hi I am testing the durability of PostgreSQL. When a part of log was lost due to the trouble(power supply trouble etc.), I tested about the recovery of PostgreSQL. The loss of log was simulated by deleting the latest xlog file. postmaster startup, it only output log message "No such file or directory" while recovering database. Here, the client can be connected with the database.I tried to access table by "psql", but could not access it or all query was lost. When xlog was lost or destroyed, I think that postmaster should output PANIC message, and interrupt startup process. (At least, I think that log level should be "Error".) How do you think? My operations are as follows. PostgreSQL Version: 8.1.1 OS: Fedora Core 4 * create database and table > createdb testdb CREATE DATABASE > pgbench -d testdb -i * database access by using pgbench > pgbench testdb -t 1 * kill postgresql processes before end of pgbench execution (simulate trouble) > ps -ef | grep post postgres 18901 1 0 03:10 pts/2 00:00:00 /home/postgres/Work/pg811/bin/postmaster postgres 18903 18901 0 03:10 pts/2 00:00:00 postgres: writer process postgres 18904 18901 0 03:10 pts/2 00:00:00 postgres: stats buffer process postgres 18905 18904 0 03:10 pts/2 00:00:00 postgres: stats collector process postgres 18947 18901 59 03:11 pts/2 00:00:01 postgres: postgres testdb [local] UPDATE postgres 18949 18918 0 03:11 pts/3 00:00:00 grep post > kill -9 18901; kill -9 18947 * latest xlog remove (sumilate xlog trouble) > cd $PGDATA/pg_xlog; ls 0001 00010001 archive_status/ > mv 00010001 /tmp/. * postmaster start(recovery start) > pg_ctl -l pg811_1.log start * check tables of pgbench > psql testdb testdb=# SELECT * from history ; tid | bid | aid | delta | mtime | filler -+-+-+---+---+ (0 rows) testdb=# SELECT * from accounts ; ERROR: xlog flush request 0/1200F88 is not satisfied flushed only to 0/160 CONTEXT: writing block 12 of relation 1663/16384/2619 * contnt of postmaster log file, pg811_1.log --- LOG: database system was interrupted at 2005-12-14 03:10:51 JST LOG: checkpoint record is at 0/33AC48 LOG: redo record is at 0/33AC48; undo record is at 0/0; shutdown FALSE LOG: next transaction ID: 567; next OID: 24576 LOG: next MultiXactId: 1; next MultiXactOffset: 0 LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 0/33AC8C LOG: could not open file "pg_xlog/00010001" (log file 0, segment 1): no such file or direcotry LOG: redo done at 0/5C LOG: database system is ready LOG: transaction ID wrap limit is 1073742415, limited by database "testdb" ERROR: xlog flush request 0/1200F88 is not satisfied --- flushed only to 0/160 CONTEXT: writing block 12 of relation 1663/16384/2619 ERROR: xlog flush request 0/11FDE40 is not satisfied --- flushed only to 0/160 CONTEXT: writing block 4 of relation 1663/16384/2619 ERROR: xlog flush request 0/1205938 is not satisfied --- flushed only to 0/160 CONTEXT: writing block 1 of relation 1663/16384/2696 ERROR: xlog flush request 0/11F0DB4 is not satisfied --- flushed only to 0/160 CONTEXT: writing block 9 of relation 1663/16384/2619 ERROR: xlog flush request 0/11F0290 is not satisfied --- flushed only to 0/160 CONTEXT: writing block 1 of relation 1663/16384/2619 ERROR: xlog flush request 0/1206B80 is not satisfied --- flushed only to 0/160 CONTEXT: writing block 2 of relation 1663/16384/2696 ERROR: xlog flush request 0/1200F88 is not satisfied --- flushed only to 0/160 CONTEXT: writing block 12 of relation 1663/16384/2619 WARNING: could not write block 12 of 1663/16384/2619 DETAIL: Multiple failures --- write error may be permanent. ERROR: xlog flush request 0/11FDE40 is not satisfied --- flushed only to 0/160 CONTEXT: writing block 4 of relation 1663/16384/2619 WARNING: could not write block 4 of 1663/16384/2619 DETAIL: Multiple failures --- write error may be permanent. ERROR: xlog flush request 0/12D69A0 is not satisfied --- flushed only to 0/160 CONTEXT: writing block 0 of relation 1663/16384/16391 ERROR: xlog flush request 0/134F6D4 is not satisfied --- flushed only to 0/160 CONTEXT: writing block 1 of relation 1663/16384/16391 ERROR: xlog flush request 0/139AF10 is not satisfied --- flushed only to 0/160 CONTEXT: writing block 2 of relation 1663/16384/16391 ERROR: xlog flush request 0/13CC814 is not satisfied --- flushed only to 0/160 CONTEXT: writing block 3 of relation 1663/16384/16391 LOG: received smart shutdown request LOG: shutting down PANIC: xlog flush request 0/1205938 is not satisfied --- flushed only to 0/160 CONTEXT: writing block 1 of relation 1663/16384/2696 LOG: background writer process (PID 18969) was terminated by signal 6 LOG: terminating any other active server processes LOG: all server processes termin
Re: [HACKERS] [pgadmin-hackers] Client-side password encryption
So it appears that pg_md5_encrypt is not officially exported from libpq. Does anyone see a problem with adding it to the export list and the header file? Is it different to normal md5? How is this helpful to the phpPgAdmin project? Chris ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Recovery from multi trouble
OKADA Satoshi <[EMAIL PROTECTED]> writes: > The loss of log was simulated by deleting the latest xlog file. What does that have to do with reality? Postgres is very careful not to use an xlog file until it's been fully metadata-synced. You might as well complain that PG doesn't recover after "rm -rf /" ... regards, tom lane ---(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] [pgadmin-hackers] Client-side password encryption
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: >> So it appears that pg_md5_encrypt is not officially exported from libpq. >> Does anyone see a problem with adding it to the export list and the >> header file? > Is it different to normal md5? How is this helpful to the phpPgAdmin > project? It would be better to export an API that is (a) less random (why one input null-terminated and the other not?) and (b) less tightly tied to MD5 --- the fact that the caller knows how long the result must be is the main problem here. Something like char *pg_gen_encrypted_passwd(const char *passwd, const char *user) with malloc'd result (or NULL on failure) seems more future-proof. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
