Re: [HACKERS] An idle thought
Surely the VM is already update-friendly. If you update a tuple in a page with the visibility bit set, the bit must be unset or you will get wrong results. I was referring in the context of index only scans to skip visibility checks. I doubt, whether the visibility map feature to skip visibility checks at the heap can be created without any extra cost to updates/inserts. When a data is compressed then there is more contention for the same block and hence would likely affect DMLs. I hope that's what Tom was also referring to, but not in the visibility map context. Gokul.
[HACKERS] [BUG] SECURITY DEFINER on call handler makes daemon crash
It is a bug report in a corner case. When we assign SECURITY DEFINER attribute on plpgsql_call_handler(), it makes server process crashed. postgres=# ALTER FUNCTION plpgsql_call_handler() security definer; ALTER FUNCTION postgres=# CREATE FUNCTION foo(text) RETURNS text AS $$ BEGIN RETURN $1 || '_aaa'; END $$ LANGUAGE plpgsql; CREATE FUNCTION postgres=# select foo('aaa'); 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. ! \q [scenario] 1. PostgreSQL calls fmgr_info_cxt_security() to initialize FmgrInfo of the foo() invocation. 2. This invocation eventually calls fmgr_info_other_lang(), because foo() is implemented with plpgsql. It also calls fmgr_info() to fetch the function pointer of the plpgsql_call_handler(). 3. However, this invocation returns fmgr_security_definer, because it is marked as security definer function. Then, it is copied to FmgrInfo-fn_addr of the foo(). 4. Then, at the first call of fmgr_security_definer(), it also calls fmgr_info_cxt_security() with ignore_security = true, to initialize secondary FmgrInfo. However, its fn_addr is initialized to fmgr_security_definer() because of step (1) - (3). 5. Finally, fmgr_security_definer() calls itself infinity, as long as stack is available. I wonder whether the fmgr_info() is an appropriate choice at fmgr_info_other_lang(), because user intended to call the foo(), not plpgsql_call_handler(), although it actuall calls language call-handler in fact. I think fmgr_info_cxt_security() with ignore_security = true should be used here, instead. Thanks, -- KaiGai Kohei kai...@ak.jp.nec.com pgsql-fix-security-definer.patch Description: application/octect-stream -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
On Thu, 2010-03-18 at 23:27 +0900, Fujii Masao wrote: I agree that this is a bigger problem. Since the standby always starts walreceiver before replaying any WAL files in pg_xlog, walreceiver tries to receive the WAL files following the REDO starting point even if they have already been in pg_xlog. IOW, the same WAL files might be shipped from the primary to the standby many times. This behavior is unsmart, and should be addressed. We might also have written half a file many times. The files in pg_xlog are suspect whereas the files in the archive are not. If we have both we should prefer the archive. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] WIP: preloading of ispell dictionary
Hello I wrote some small patch, that allow preloading of selected ispell dictionary. It solve the problem with slow tsearch initialisation with some language configuration. This patch is most simple - simpler than variant with shared memory and it is usable on Linux platform. I registered some issues about access to different king of memory :(. The local memory is the best, than shared_memory and then virtual memory. Queries with preloaded dictionary are about 20% slower (but still good enough). It depend on platform (and language sure) - I afraid so this module doesn't help on MS Windows. Tested on 64bit Fedora Linux - probably on 32bit these issues will be smaller. I would to add this patch to next commitfest. can somebody test it for different platforms and different languages than Czech? Regards Pavel Stehule preload.diff Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [COMMITTERS] pgsql: Introduce WAL records to log reuse of btree pages, allowing
On Thu, 2010-02-18 at 14:23 +0200, Heikki Linnakangas wrote: Simon Riggs wrote: Introduce WAL records to log reuse of btree pages, allowing conflict resolution during Hot Standby. Page reuse interlock requested by Tom. Analysis and patch by me. There's still a theoretical possibility for this to happen: 1. A page is marked as deleted by VACUUM, setting xact field in the opaque 2. Master crashes. WAL replay replays the XLOG_BTREE_DELETE_PAGE record. It resets the xact field to FrozenTransactionId 3. The page is recycled. This writes a XLOG_BTREE_REUSE_PAGE record with FrozenTransactionId as latestRemovedXid When the standby replays that, it will call ResolveRecoveryConflictWithSnapshot with FrozenTransactionid, not the original xid that was used in the master when the page was deleted. A straightforward way to fix that is to WAL-log the real xid in the XLOG_BTREE_DELETE_PAGE records, instead of resetting it to FrozenTransactionId. Bug accepted, proposal implemented and committed. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Getting to beta1
On Sat, 2010-03-13 at 16:58 +, Simon Riggs wrote: On Fri, 2010-03-12 at 22:28 -0500, Bruce Momjian wrote: Where are we in getting to beta1? I know people are looking to me for 9.0 release notes and I will have them done in about a week, but what about open issues? I don't see many on the main 9.0 open items page: http://wiki.postgresql.org/wiki/PostgreSQL_9.0_Open_Items#Bugs The list has been reduced greatly in the past week. What about HS/SR open items? I have these things on my list * Minor page xid bug fix COMMITTED * btree delete standby-side derivation of xid MOST HACKING DONE, TESTING NOW * review of StartupXLog issue, on open items list, has an effect on HS DONE - no action I expect to be finished with those by Wed, perhaps Thurs. * Start from Shutdown checkpoint Adding the last one will take me through to next week now, but given the list of outstanding SR issues, I figure I have that time. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] explain and PARAM_EXEC
Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: So I guess there are two issues here: (1) somehow I feel like we should be telling the user what expression is being used to initialize $0, $1, etc. when they are PARAM_EXEC parameters; Maybe, but the only reasonable place to put it would be within the (SubPlan N) reference, which is not a place where verboseness would be appreciated, I think. In principle it could look something like (SubPlan N ($0 := b.oid)) but with a few parameters and a bunch of other stuff on the same line that would get out of hand. I'm currently digesting subplan/initplan handling and I really like Robert's idea of showing actual parameters. postgres=# explain select oid::int + 1,(select oid from pg_class a where a.oid = b.relfilenode and a.relnamespace = b.relnamespace) from pg_class b; QUERY PLAN - Seq Scan on pg_class b (cost=0.00..2459.64 rows=296 width=12) SubPlan 1 ($0 := b.relfilenode, $1 := b.relnamespace) - Index Scan using pg_class_oid_index on pg_class a (cost=0.00..8.27 rows=1 width=4) Index Cond: (oid = $0) Filter: (relnamespace = $1) (5 rows) Only changes in ExplainSubPlans, all regression tests passed (which surprized me a bit, no explains with subplanes in expected results?). NB: this is not a patch to HEAD but a local version, so line numbers are off, patch supplied for discussion purposes only. kind regards, Yeb Havinga diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 56d9c5b..454d59b 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -1686,20 +1686,47 @@ static void ExplainSubPlans(List *plans, const char *relationship, ExplainState *es) { ListCell *lst; foreach(lst, plans) { SubPlanState *sps = (SubPlanState *) lfirst(lst); - SubPlan*sp = (SubPlan *) sps-xprstate.expr; + SubPlan *sp = (SubPlan *) sps-xprstate.expr; + StringInfosignature = makeStringInfo(); + int i = 0; + List *context; + bool useprefix; + ListCell *c; + + context = deparse_context_for_plan((Node *)exec_subplan_get_plan(es-pstmt, sp), + NULL, + es-rtable, + es-pstmt-subplans); + useprefix = list_length(es-rtable) 1; + + appendStringInfoString(signature, sp-plan_name); + + foreach(c, sp-args) + { + Node *n = lfirst(c); + appendStringInfo(signature, %s$%d := %s, +(i == 0) ? ( : , , +i, + deparse_expression(n, context, useprefix, true)); + i++; + } + + if (i 0) + appendStringInfoString(signature, )); ExplainNode(exec_subplan_get_plan(es-pstmt, sp), sps-planstate, NULL, - relationship, sp-plan_name, + relationship, + signature-data, es); } } /* * Explain a property, such as sort keys or targets, that takes the form of * a list of unlabeled items. data is a list of C strings. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
Simon Riggs wrote: On Thu, 2010-03-18 at 23:27 +0900, Fujii Masao wrote: I agree that this is a bigger problem. Since the standby always starts walreceiver before replaying any WAL files in pg_xlog, walreceiver tries to receive the WAL files following the REDO starting point even if they have already been in pg_xlog. IOW, the same WAL files might be shipped from the primary to the standby many times. This behavior is unsmart, and should be addressed. We might also have written half a file many times. The files in pg_xlog are suspect whereas the files in the archive are not. If we have both we should prefer the archive. Yep. Here's a patch I've been playing with. The idea is that in standby mode, the server keeps trying to make progress in the recovery by: a) restoring files from archive b) replaying files from pg_xlog c) streaming from master When recovery reaches an invalid WAL record, typically caused by a half-written WAL file, it closes the file and moves to the next source. If an error is found in a file restored from archive or in a portion just streamed from master, however, a PANIC is thrown, because it's not expected to have errors in the archive or in the master. When a file is streamed from master, it's left in pg_xlog, so it's found there after a standby restart, and recovery can progress to the same point as before restart. It also means that you can copy partial WAL files to pg_xlog at any time and have them replayed in a few seconds. The code structure is a bit spaghetti-like, I'm afraid. Any suggestions on how to improve that are welcome.. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com *** a/src/backend/access/transam/xlog.c --- b/src/backend/access/transam/xlog.c *** *** 445,464 static uint32 openLogSeg = 0; static uint32 openLogOff = 0; /* * These variables are used similarly to the ones above, but for reading * the XLOG. Note, however, that readOff generally represents the offset * of the page just read, not the seek position of the FD itself, which * will be just past that page. readLen indicates how much of the current ! * page has been read into readBuf. */ static int readFile = -1; static uint32 readId = 0; static uint32 readSeg = 0; static uint32 readOff = 0; static uint32 readLen = 0; ! /* Is the currently open segment being streamed from primary? */ ! static bool readStreamed = false; /* Buffer for currently read page (XLOG_BLCKSZ bytes) */ static char *readBuf = NULL; --- 445,477 static uint32 openLogOff = 0; /* + * Codes indicating where we got a WAL file from during recovery, or where + * to attempt to get one. + */ + #define XLOG_FROM_ARCHIVE (10) /* Restored using restore_command */ + #define XLOG_FROM_PG_XLOG (11) /* Existing file in pg_xlog */ + #define XLOG_FROM_STREAM (12) /* Streamed from master */ + + /* * These variables are used similarly to the ones above, but for reading * the XLOG. Note, however, that readOff generally represents the offset * of the page just read, not the seek position of the FD itself, which * will be just past that page. readLen indicates how much of the current ! * page has been read into readBuf, and readSource indicates where we got ! * the currently open file from. */ static int readFile = -1; static uint32 readId = 0; static uint32 readSeg = 0; static uint32 readOff = 0; static uint32 readLen = 0; + static int readSource = 0; /* XLOG_FROM_* code */ ! /* ! * Keeps track of which sources we've tried to read the current WAL ! * record from and failed. ! */ ! static int failedSources = 0; /* Buffer for currently read page (XLOG_BLCKSZ bytes) */ static char *readBuf = NULL; *** *** 512,520 static bool InstallXLogFileSegment(uint32 *log, uint32 *seg, char *tmppath, bool find_free, int *max_advance, bool use_lock); static int XLogFileRead(uint32 log, uint32 seg, int emode, TimeLineID tli, ! bool fromArchive, bool notexistOk); static int XLogFileReadAnyTLI(uint32 log, uint32 seg, int emode, ! bool fromArchive); static bool XLogPageRead(XLogRecPtr *RecPtr, int emode, bool fetching_ckpt, bool randAccess); static void XLogFileClose(void); --- 525,533 bool find_free, int *max_advance, bool use_lock); static int XLogFileRead(uint32 log, uint32 seg, int emode, TimeLineID tli, ! int source, bool notexistOk); static int XLogFileReadAnyTLI(uint32 log, uint32 seg, int emode, ! int sources); static bool XLogPageRead(XLogRecPtr *RecPtr, int emode, bool fetching_ckpt, bool randAccess); static void XLogFileClose(void); *** *** 2567,2573 XLogFileOpen(uint32 log, uint32 seg) */ static int XLogFileRead(uint32 log, uint32 seg, int emode, TimeLineID tli, ! bool fromArchive, bool notfoundOk) { char xlogfname[MAXFNAMELEN]; char activitymsg[MAXFNAMELEN + 16];
Re: [HACKERS] [BUG] SECURITY DEFINER on call handler makes daemon crash
KaiGai Kohei kai...@ak.jp.nec.com writes: When we assign SECURITY DEFINER attribute on plpgsql_call_handler(), it makes server process crashed. So don't do that. Whatever possessed you to think that's a sensible idea anyway? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Reset btpo.xact following recovery of btree delete page.
sri...@postgresql.org (Simon Riggs) writes: Log Message: --- Reset btpo.xact following recovery of btree delete page. Add btpo_xact field into WAL record and reset it from there, rather than using FrozenTransactionId which can lead to some corner case bugs. Simon, you can't just whack WAL record contents around without a care. This commit breaks on-disk WAL compatibility and will result in possibly unrecoverable databases as soon as someone applies it; not to mention what will happen if an HS slave is not running the same code as its master. This must be treated as an initdb-forcing, or at least pg_resetxlog-forcing, change. The correct thing to do when committing such a change is to change the WAL-file magic number XLOG_PAGE_MAGIC. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Simon Riggs wrote: We might also have written half a file many times. The files in pg_xlog are suspect whereas the files in the archive are not. If we have both we should prefer the archive. Yep. Really? That will result in a change in the longstanding behavior of ordinary recovery. I'm unconvinced that this is wise. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
Tom Lane wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Simon Riggs wrote: We might also have written half a file many times. The files in pg_xlog are suspect whereas the files in the archive are not. If we have both we should prefer the archive. Yep. Really? That will result in a change in the longstanding behavior of ordinary recovery. Really? Not as far as I can see. Recovery has always tried to restore WAL files from archive first, falling back to the copy in pg_xlog only if restore_command returns failure. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Getting to beta1
Hi all, On Thu, 2010-03-18 at 10:18 -0700, Josh Berkus wrote: Or, let's put it another way: I've made my opinion clear in the past that I think that we ought to ship with a minimal postgresql.conf with maybe 15 items in it. If we are going to continue to ship with postgresql.conf kitchen sick version, however, it should include vacuum_defer_cleanup_age. But considering that these are samples anyway, what is preventing to ship 2 versions, one labeled minimal and one labeled full ? The minimal one should only contain absolutely must-change items, and the full version should contain all. As simple as that. I don't think there's any value in anything in the middle... Cheers, Csaba. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
Heikki Linnakangas escribió: When recovery reaches an invalid WAL record, typically caused by a half-written WAL file, it closes the file and moves to the next source. If an error is found in a file restored from archive or in a portion just streamed from master, however, a PANIC is thrown, because it's not expected to have errors in the archive or in the master. Hmm, I think I've heard that tools like walmgr do incremental copies of the current WAL segment to the archive. Doesn't this change break that? (Maybe I'm confused and it doesn't work that way) -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
Alvaro Herrera wrote: Heikki Linnakangas escribió: When recovery reaches an invalid WAL record, typically caused by a half-written WAL file, it closes the file and moves to the next source. If an error is found in a file restored from archive or in a portion just streamed from master, however, a PANIC is thrown, because it's not expected to have errors in the archive or in the master. Hmm, I think I've heard that tools like walmgr do incremental copies of the current WAL segment to the archive. Doesn't this change break that? Hmm, you could have a restore_command that checks the size before restoring to make it still work. I note that pg_standby does that, but of course you can't use pg_standby with the built-in standby mode. Or maybe we should modify the built-in standby mode to handle partial files coming from restore_command by not throwing an error but recovering to the end of the partial file, and then retrying restore_command again with the same filename until the whole file is recovered (or the missing WAL is received through other means, ie. streaming replication). -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [COMMITTERS] pgsql: Reset btpo.xact following recovery of btree delete page.
On Fri, 2010-03-19 at 08:41 -0400, Tom Lane wrote: sri...@postgresql.org (Simon Riggs) writes: Log Message: --- Reset btpo.xact following recovery of btree delete page. Add btpo_xact field into WAL record and reset it from there, rather than using FrozenTransactionId which can lead to some corner case bugs. Simon, you can't just whack WAL record contents around without a care. This commit breaks on-disk WAL compatibility and will result in possibly unrecoverable databases as soon as someone applies it; not to mention what will happen if an HS slave is not running the same code as its master. This must be treated as an initdb-forcing, or at least pg_resetxlog-forcing, change. OK, understood. The correct thing to do when committing such a change is to change the WAL-file magic number XLOG_PAGE_MAGIC. Will do -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Postgres officially accepted in to 2010 Google Summer of Code program
Howdy folks, I'm very happy to announce that the Postgres project has been selected to participate in this years Google Summer of Code program. Over the next couple weeks we'll be looking to solidify our mentor base; if you work on Postgres and would be willing to mentor a student, please send me a note so we can get you signed up. If you are a student and you're interested in working on Postgres, now is the time to get your proposal together. Student applications will open up on March 29th, so we'd like to have our mentors in place for review, and hopefully had students discussing with the Postgres devs their proposals as much as needed. If anyone has any questions, feel free to email me, or track me down on irc. Handy links for Postgres GSoC: Our ideas page for GSoC: http://www.postgresql.org/developer/summerofcode Our loose attempt at organization: http://wiki.postgresql.org/wiki/GSoC_2010 Our Postgres page on the GSoC site: http://socghop.appspot.com/gsoc/org/show/google/gsoc2010/postgresql Users Guide to GSoC: http://socghop.appspot.com/document/show/gsoc_program/google/gsoc2010/userguide Thanks everyone, I'm looking forward to another interesting year with GSoC, and hoping you'll join in. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Repeating Append operation
Hi all, explain select v from ( select array( select 1 union all select 2) as v from (select 1) ) as s where v is not null; The plan looks like: QUERY PLAN Result (cost=0.08..0.10 rows=1 width=0) One-Time Filter: ($1 IS NOT NULL) InitPlan - Append (cost=0.00..0.04 rows=2 width=0) - Result (cost=0.00..0.01 rows=1 width=0) - Result (cost=0.00..0.01 rows=1 width=0) - Append (cost=0.00..0.04 rows=2 width=0) - Result (cost=0.00..0.01 rows=1 width=0) - Result (cost=0.00..0.01 rows=1 width=0) - Result (cost=0.00..0.01 rows=1 width=0) (10 rows) It seems that that the UNION ALL part of the query will be executed twice. If I remove the WHERE clause the I see only one Append operation. I had a suspicion that its just the display of the plan that showed the same plan twice, but then I noticed that the overall cost of the query also drops making me think that this UNION ALL will actually be executed twice. The plan without the WHERE clause is: QUERY PLAN -- Subquery Scan __unnamed_subquery_0 (cost=0.04..0.06 rows=1 width=0) InitPlan - Append (cost=0.00..0.04 rows=2 width=0) - Result (cost=0.00..0.01 rows=1 width=0) - Result (cost=0.00..0.01 rows=1 width=0) - Result (cost=0.00..0.01 rows=1 width=0) (6 rows) I had seen this with a bigger query on actual tables, and this is just a reproducible test case. In the original query I see 'SubPlan' instead of the 'InitPlan' seen here. Head of plan with WHERE clause: Seq Scan on Person p (cost=0.00..280486580881.10 rows=1373415 width=4) Head of plan without WHERE clause: Seq Scan on Person p (cost=0.00..140594841889.03 rows=1380317 width=4) Is there a way to avoid this double evaluation? Thanks in advance. -- gurjeet.singh @ EnterpriseDB - The Enterprise Postgres Company http://www.enterprisedb.com singh.gurj...@{ gmail | yahoo }.com Twitter/Skype: singh_gurjeet Mail sent from my BlackLaptop device
[HACKERS] Call for translations
In anticipation of the release of PostgreSQL 9.0, it is once again time to update the message translations. We are not very near a string freeze yet, which has traditionally been associated with the first release candidate, but I feel that things are already stable enough to begin this work now. If you want to help, see http://babel.postgresql.org/ for instructions and other information. If there are already active translation teams, please communicate with them first. The mailing list pgtranslation-translat...@pgfoundry.org is available for general discussion and coordination of translation activities. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUG] SECURITY DEFINER on call handler makes daemon crash
On Fri, Mar 19, 2010 at 8:18 AM, Tom Lane t...@sss.pgh.pa.us wrote: KaiGai Kohei kai...@ak.jp.nec.com writes: When we assign SECURITY DEFINER attribute on plpgsql_call_handler(), it makes server process crashed. So don't do that. Whatever possessed you to think that's a sensible idea anyway? It might not be sensible, but the whole server going down as a result doesn't seem very sensible either. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] C libpq frontend library fetchsize
On Thu, Mar 18, 2010 at 1:21 PM, Yeb Havinga yebhavi...@gmail.com wrote: Tom Lane wrote: Yeb Havinga yebhavi...@gmail.com writes: What if the default operation of e.g. php using libpq would be as follows: set some default fetchsize (e.g. 1000 rows), then just issue getrow. In the php pg handling, a function like getnextrow would wait for the first pgresult with 1000 rows. Then if the pgresult is depleted or almost depleted, request the next pgresult automatically. I see a lot of benefits like less memory requirements in libpq, less new users with why is my query so slow before the first row, and almost no concerns. You are blithely ignoring the reasons why libpq doesn't do this. The main one being that it's impossible to cope sanely with queries that fail partway through execution. I'm sorry I forgot to add a reference to your post of http://archives.postgresql.org/pgsql-general/2010-02/msg00956.php which is the only reference to queries failing partway that I know of. But blithely is not a good description of me ignoring it. I though about how queries could fail, but can't think of anything else than e.g. memory exhaustion, and that is just one of the things that is improved this way. Maybe a user defined type with an error on certain data values, but then the same arguing could be: why support UDT? And if a query fails during execution, does that mean that the rows returned until that point are wrong? The described implementation would not cope tremendously well with nonsequential access to the resultset, either. That's why I'm not proposing to replace the current way pgresults are made complete, but just an extra option to enable developers using the libpq libary making the choice themselves. This seems pretty reasonable to me, especially considering that JDBC is apparently already doing it. I suppose there will always be projects that want to reimplement the backend protocol so that they can be pure some-language, but chipping away at the list of other reasons why someone might not want to use libpq still seems like a good idea. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUG] SECURITY DEFINER on call handler makes daemon crash
Robert Haas robertmh...@gmail.com writes: On Fri, Mar 19, 2010 at 8:18 AM, Tom Lane t...@sss.pgh.pa.us wrote: KaiGai Kohei kai...@ak.jp.nec.com writes: When we assign SECURITY DEFINER attribute on plpgsql_call_handler(), it makes server process crashed. So don't do that. Whatever possessed you to think that's a sensible idea anyway? It might not be sensible, but the whole server going down as a result doesn't seem very sensible either. [ shrug... ] If you would like to start enumerating the ways in which you can crash the server with erroneous pg_proc entries for C functions, go for it. It'll keep you out of trouble for a very long time. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUG] SECURITY DEFINER on call handler makes daemon crash
On Fri, Mar 19, 2010 at 8:11 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Fri, Mar 19, 2010 at 8:18 AM, Tom Lane t...@sss.pgh.pa.us wrote: KaiGai Kohei kai...@ak.jp.nec.com writes: When we assign SECURITY DEFINER attribute on plpgsql_call_handler(), it makes server process crashed. So don't do that. Whatever possessed you to think that's a sensible idea anyway? It might not be sensible, but the whole server going down as a result doesn't seem very sensible either. [ shrug... ] If you would like to start enumerating the ways in which you can crash the server with erroneous pg_proc entries for C functions, go for it. It'll keep you out of trouble for a very long time. It's obviously not possible to make this bulletproof in general, but that doesn't mean we should crash just for fun. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUG] SECURITY DEFINER on call handler makes daemon crash
(2010/03/20 11:17), Robert Haas wrote: On Fri, Mar 19, 2010 at 8:11 PM, Tom Lanet...@sss.pgh.pa.us wrote: Robert Haasrobertmh...@gmail.com writes: On Fri, Mar 19, 2010 at 8:18 AM, Tom Lanet...@sss.pgh.pa.us wrote: KaiGai Koheikai...@ak.jp.nec.com writes: When we assign SECURITY DEFINER attribute on plpgsql_call_handler(), it makes server process crashed. So don't do that. Whatever possessed you to think that's a sensible idea anyway? It might not be sensible, but the whole server going down as a result doesn't seem very sensible either. [ shrug... ] If you would like to start enumerating the ways in which you can crash the server with erroneous pg_proc entries for C functions, go for it. It'll keep you out of trouble for a very long time. It's obviously not possible to make this bulletproof in general, but that doesn't mean we should crash just for fun. I'd like to put the question in anotherexpression. Is it an expected behavior that PostgreSQL tries to execute foo() with privileges of the owner of language call handler because of its security definer property? This server crash is just a result. Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUG] SECURITY DEFINER on call handler makes daemon crash
On 3/19/10 5:18 AM, Tom Lane wrote: When we assign SECURITY DEFINER attribute on plpgsql_call_handler(), it makes server process crashed. So don't do that. Whatever possessed you to think that's a sensible idea anyway? PATIENT: Doctor, it hurts when I do this! DOCTOR: So stop doing that. ;-) -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUG] SECURITY DEFINER on call handler makes daemon crash
On Fri, Mar 19, 2010 at 10:29 PM, KaiGai Kohei kai...@kaigai.gr.jp wrote: Is it an expected behavior that PostgreSQL tries to execute foo() with privileges of the owner of language call handler because of its security definer property? This server crash is just a result. I'm inclined to feel (and Tom's response only reinforces this) that the actual behavior isn't critical. I'd be happy with (1) executing foo() with the privileges of the language owner or (2) ignoring the SECURITY DEFINER attribute in this context and executing foo() without changing privileges or (3) throwing an error. We should just do whatever complicates the code the least. Your proposed patch seems good from that point of view, though I'm not clear on whether it's otherwise reasonable or which of the above behaviors it actually implements. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 9.0 release notes done
I have completed the 9.0 release notes: http://developer.postgresql.org/pgdocs/postgres/release-9-0.html I kept the 9.0-alpha release notes in the SGML because people might want to compare them with the release notes I did, and because the introductory text will be needed for the next alpha. Eventually we will want to trim the alpha SGML and perhaps place it in a large comment block for use for 9.1 alphas. Interestingly the 9.0 release notes contain 201 items, while the 8.4 release notes contained 314 items. Of course we will be adding a few more 9.0 items before 9.0 final, but not a lot. The only explanation I can think of is that we were more focused during this release, and there were fewer minor cleanups. The migration issues section, for example, was significantly smaller than in 8.4. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUG] SECURITY DEFINER on call handler makes daemon crash
KaiGai Kohei kai...@kaigai.gr.jp writes: Is it an expected behavior that PostgreSQL tries to execute foo() with privileges of the owner of language call handler because of its security definer property? This server crash is just a result. A language call handler has no function properties of its own --- which is why attaching SECURITY DEFINER to it is both useless and meaningless. The appropriate function properties for any call are those of the user function being called, which the handler is merely a support for. You could argue that we should put call handlers into their own table instead of pg_proc, since they aren't really user-callable functions; that would prevent people from thinking that something like this is sane. However, they share just enough infrastructure with real functions that it didn't seem worth doing it that way. I see no value whatsoever in making the world safe for people to attach SECURITY DEFINER to handlers. It's an incorrect declaration, and superusers need to know better than to declare C functions with incorrect properties. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers