Re: [HACKERS] rc tarball built with older flex version?
On Monday 22 June 2009 00:17:06 Marc G. Fournier wrote: On Thu, 18 Jun 2009, Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: I noticed that the rc1 tarball includes scanner files that are built with an older flex version that generates warnings with our default compilation flags. Since I have been running with -Werror by default for a great while now, this caught my attention while testing the tarball. Are we tracking this or are we just using whatever was installed on the host that created the snapshot? It's whatever is installed on svr1, but we don't change that often, and I'm particularly not inclined to change it post-RC. We don't recommend that people use -Werror to build, so I think we should just write this off as not a bug. I'm a bit confused here though ... I haven't changed flex on that VPS recently ... in fact, its dated Sep 15, 2007 ... so the builds have been using the same flex for a long while now ... Peter, is this a recently change you've noticed, or something that has been like that for while now? Well, I rarely test the actual release source tarball, so it might have been like that forever. -- 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] rc tarball built with older flex version?
On Mon, 22 Jun 2009, Peter Eisentraut wrote: Well, I rarely test the actual release source tarball, so it might have been like that forever. 'k ... I swore I haven't changed anything over there in awhile, so was most confused as to where this sudden error came from ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email . scra...@hub.org MSN . scra...@hub.org Yahoo . yscrappy Skype: hub.orgICQ . 7615664 -- 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] 8.4 open item: copy performance regression?
Hi Tom, How much concern is there for the contention for use cases where the WAL can't be bypassed? Thanks, Alan On Sun, Jun 21, 2009 at 10:00 AM, Tom Lane t...@sss.pgh.pa.us wrote: Stefan Kaltenbrunner ste...@kaltenbrunner.cc writes: The following copying 3M rows(each) into a seperate table of the same database. Is this with WAL, or bypassing WAL? Given what we've already seen, a lot of contention for WALInsertLock wouldn't surprise me much here. It should be possible to bypass that though. regards, tom lane
Re: [HACKERS] Missing Docs for MOVE direction?
On Monday 22 June 2009 03:15:57 David E. Wheeler wrote: On Jun 21, 2009, at 5:07 PM, David E. Wheeler wrote: I was just looking at the documentation for cursors command, and noticed that the MOVE command's direction argument doesn't seem to have documentation for its possible values. http://www.postgresql.org/docs/8.4/static/sql-move.html I assume that they should be FORWARD and BACKWARD, as the former is used in the example, but in truth I have no idea. Also, what's the difference between the FROM and IN clauses? Or are they they same thing? Bah! I now see that these expressions are the same as for FETCH. Something like this would help, I think: *** move.sgml.~1.31.~ Thu Nov 20 20:39:34 2008 --- move.sgml Sun Jun 21 17:15:44 2009 *** *** 40,46 /para para !Refer to xref linkend=sql-fetch endterm=sql-fetch-title for details on syntax and usage. /para --- 40,47 /para para !The parameters for commandMOVE/command are identical to those for !commandFETCH/command; refer to xref linkend=sql-fetch endterm=sql-fetch-title for details on syntax and usage. /para Thanks, David done -- 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] Synch Rep: communication between backends and walsender
Hi, On Sat, Jun 20, 2009 at 6:05 PM, Markus Wannermar...@bluegap.ch wrote: Reading the past messages on this topic, I realized that this problem so far only affects HPUX. I fear the proposed UDP/semaphores approach might have a similar gotcha on at least one of the supported platforms, too. Limits of open file descriptors come to mind, for example. Or kernel packet filtering rules, as mentioned in pgstat.c. You're right. The UDP approach at least would cause another dissatisfaction, as you illustrated. If I understand correctly, even Postgres itself suffers from that problem on HPUX (even though the consequences aren't dramatic, as pointed out by Tom). Plus we are not completely save from syscalls returning EINTR due to SA_RESTART not being set for SIGALRM. So, does it really make sense to take care of this issue as part of the sync rep patch? The perfect solution seems to be to remove SA_RESTART and handle EINTR in an appropriate way after every syscalls. But, this is very tough job and has much influence on whole source code, so I don't think that this should be done as part of synch rep. On the other hand, I think that a semaphore should be used instead of a signal at least for backends to wait for walsender. This would turn around the situation on HPUX in some degree. In this case, the remaining problem is that walsender cannot immediately wake up. But, since walsender wakes up periodically and that period can be tweaked (by new GUC wal_sender_delay), the overhead on HPUX might not be actually so big. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- 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] 8.4 open item: copy performance regression?
Tom Lane wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: I was going to say that since we flush the WAL every 16MB anyway (at every XLOG file switch), you shouldn't see any benefit with larger ring buffers, since to fill 16MB of data you're not going to write more than 16MB WAL. I'm not convinced that WAL segment boundaries are particularly relevant to this. The unit of flushing is an 8K page, not a segment. We fsync() the old WAL segment every time we switch to a new WAL segment. That's what I meant by flush. If the walwriter is keeping up, it will fsync() the WAL more often, but 16MB is the maximum distance between fsync()s. -- 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] 8.4 open item: copy performance regression?
On Mon, 2009-06-22 at 10:52 +0300, Heikki Linnakangas wrote: Tom Lane wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: I was going to say that since we flush the WAL every 16MB anyway (at every XLOG file switch), you shouldn't see any benefit with larger ring buffers, since to fill 16MB of data you're not going to write more than 16MB WAL. I'm not convinced that WAL segment boundaries are particularly relevant to this. The unit of flushing is an 8K page, not a segment. We fsync() the old WAL segment every time we switch to a new WAL segment. That's what I meant by flush. If the walwriter is keeping up, it will fsync() the WAL more often, but 16MB is the maximum distance between fsync()s. Yes, but the fsync is performed by the process that writes the WAL, not necessarily by the process that inserts the WAL. In perfect balance, an inserter-of-WAL could insert an infinite amount of WAL and never need to fsync the WAL. So the question is are we in balance between WALWriter and COPY? -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] security checks for largeobjects?
On Mon, Jun 22, 2009 at 02:00:10PM +0900, KaiGai Kohei wrote: KaiGai Kohei wrote: The todo list says, as follows: * Binary Data o Add security checks for large objects http://wiki.postgresql.org/wiki/Todo#Binary_Data Is anyone working on? or interested in? OK, it seems to me nobody is working on the todo item. I'll also submit this feature on the v8.5 development cycle. This seems related to SQL/MED's file handling portion. Perhaps the TODO should be merged into the SQL/MED one. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] 8.4 open item: copy performance regression?
Alan Li a...@truviso.com writes: How much concern is there for the contention for use cases where the WAL can't be bypassed? If you mean is something going to be done about it in 8.4, the answer is no. This is a pre-existing issue that there is no simple fix for. 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] security checks for largeobjects?
On Monday 22 June 2009 16:24:44 David Fetter wrote: On Mon, Jun 22, 2009 at 02:00:10PM +0900, KaiGai Kohei wrote: KaiGai Kohei wrote: The todo list says, as follows: * Binary Data o Add security checks for large objects http://wiki.postgresql.org/wiki/Todo#Binary_Data Is anyone working on? or interested in? OK, it seems to me nobody is working on the todo item. I'll also submit this feature on the v8.5 development cycle. This seems related to SQL/MED's file handling portion. Perhaps the TODO should be merged into the SQL/MED one. MED is management of external data, whereas the large objects are internal, no? -- 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] 8.4 open item: copy performance regression?
Tom Lane wrote: Alan Li a...@truviso.com writes: How much concern is there for the contention for use cases where the WAL can't be bypassed? If you mean is something going to be done about it in 8.4, the answer is no. This is a pre-existing issue that there is no simple fix for. I thought he was asking if we intend to provide for WAL bypass on a table by table basis in future. cheers andrew -- 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] 8.4 open item: copy performance regression?
Andrew Dunstan and...@dunslane.net writes: Tom Lane wrote: Alan Li a...@truviso.com writes: How much concern is there for the contention for use cases where the WAL can't be bypassed? If you mean is something going to be done about it in 8.4, the answer is no. This is a pre-existing issue that there is no simple fix for. I thought he was asking if we intend to provide for WAL bypass on a table by table basis in future. I thought he was asking for a solution to the problem of WALInsertLock contention. In any case, we have WAL bypass on a table by table basis now, don't we? 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] 8.4 open item: copy performance regression?
Tom Lane wrote: I thought he was asking for a solution to the problem of WALInsertLock contention. In any case, we have WAL bypass on a table by table basis now, don't we? If we do I'm ignorant of it ;-) How do we say Never WAL this table? cheers andrew -- 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] 8.4 open item: copy performance regression?
Andrew Dunstan and...@dunslane.net writes: Tom Lane wrote: I thought he was asking for a solution to the problem of WALInsertLock contention. In any case, we have WAL bypass on a table by table basis now, don't we? If we do I'm ignorant of it ;-) How do we say Never WAL this table? Make it a temporary table. The more useful case for data load is create or truncate it in the same transaction, of course. 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] 8.4 open item: copy performance regression?
* Andrew Dunstan and...@dunslane.net [090622 10:47]: If we do I'm ignorant of it ;-) How do we say Never WAL this table? CREATE TEMPORARY TABLE ... a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] 8.4 open item: copy performance regression?
* Tom Lane (t...@sss.pgh.pa.us) wrote: The more useful case for data load is create or truncate it in the same transaction, of course. Unfortunately, WAL bypass also requires not being in archive mode with no way to turn that off w/o a server restart, aiui. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] 8.4 open item: copy performance regression?
Stephen Frost sfr...@snowman.net writes: * Tom Lane (t...@sss.pgh.pa.us) wrote: The more useful case for data load is create or truncate it in the same transaction, of course. Unfortunately, WAL bypass also requires not being in archive mode with no way to turn that off w/o a server restart, aiui. Well, if you're trying to archive then you certainly wouldn't want WAL off, so I'm failing to see where this thread is going ... 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] 8.4 open item: copy performance regression?
* Tom Lane (t...@sss.pgh.pa.us) wrote: Stephen Frost sfr...@snowman.net writes: Unfortunately, WAL bypass also requires not being in archive mode with no way to turn that off w/o a server restart, aiui. Well, if you're trying to archive then you certainly wouldn't want WAL off, so I'm failing to see where this thread is going ... I disagree. I'd love to be able to say please bypass WAL logging for this bulk load because I know that I'll pick up the data during my next full dump and I can reload it from original if I get disrupted before then. This is especially true when you're doing bulk loads of static or reference data from another data source. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] security checks for largeobjects?
On Mon, Jun 22, 2009 at 05:18:51PM +0300, Peter Eisentraut wrote: On Monday 22 June 2009 16:24:44 David Fetter wrote: On Mon, Jun 22, 2009 at 02:00:10PM +0900, KaiGai Kohei wrote: KaiGai Kohei wrote: The todo list says, as follows: * Binary Data o Add security checks for large objects http://wiki.postgresql.org/wiki/Todo#Binary_Data Is anyone working on? or interested in? OK, it seems to me nobody is working on the todo item. I'll also submit this feature on the v8.5 development cycle. This seems related to SQL/MED's file handling portion. Perhaps the TODO should be merged into the SQL/MED one. MED is management of external data, whereas the large objects are internal, no? It depends on your definition. The lo interface is pretty much to objects on the file system directly. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] 8.4 open item: copy performance regression?
On Mon, 2009-06-22 at 11:14 -0400, Tom Lane wrote: Stephen Frost sfr...@snowman.net writes: * Tom Lane (t...@sss.pgh.pa.us) wrote: The more useful case for data load is create or truncate it in the same transaction, of course. Unfortunately, WAL bypass also requires not being in archive mode with no way to turn that off w/o a server restart, aiui. Well, if you're trying to archive then you certainly wouldn't want WAL off, so I'm failing to see where this thread is going ... I was thinking it might be beneficial to be able to defer writing WAL until COPY is complete, so heap_sync would either fsync the whole heap file or copy the whole file to WAL. That would avoid writing WAL piecemeal because we could just backup the whole block, plus we wouldn't write anything at all if the COPY failed. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] 8.4 open item: copy performance regression?
Simon Riggs si...@2ndquadrant.com writes: I was thinking it might be beneficial to be able to defer writing WAL until COPY is complete, so heap_sync would either fsync the whole heap file or copy the whole file to WAL. What about indexes? 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] 8.4 open item: copy performance regression?
On Mon, 2009-06-22 at 11:24 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: I was thinking it might be beneficial to be able to defer writing WAL until COPY is complete, so heap_sync would either fsync the whole heap file or copy the whole file to WAL. What about indexes? I was thinking we could do exactly as stated for the cases that would be WAL-bypass now, but need to write WAL because XLogArchivingActive(). i.e. improve the exact case we are measuring here. Yes, it is more complex than that for loading to existing tables. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] security checks for largeobjects?
David Fetter da...@fetter.org writes: On Mon, Jun 22, 2009 at 05:18:51PM +0300, Peter Eisentraut wrote: MED is management of external data, whereas the large objects are internal, no? It depends on your definition. The lo interface is pretty much to objects on the file system directly. LO's are transaction-controlled, and they're not (readily) accessible from outside the database. Seems rather completely different from regular filesystem files. (In any case, there wasn't anything I liked about SQL/MED's ideas about external files, so I'm not in favor of modeling LO management after that.) 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] 8.4 open item: copy performance regression?
Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: Tom Lane wrote: I thought he was asking for a solution to the problem of WALInsertLock contention. In any case, we have WAL bypass on a table by table basis now, don't we? If we do I'm ignorant of it ;-) How do we say Never WAL this table? Make it a temporary table. That doesn't help if you need the data visible in multiple sessions. But we're digressing from the original topic. Sorry. cheers andrew -- 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] Synch Rep: communication between backends and walsender
Hi, Fujii Masao wrote: The perfect solution seems to be to remove SA_RESTART and handle EINTR in an appropriate way after every syscalls. But, this is very tough job and has much influence on whole source code, so I don't think that this should be done as part of synch rep. Especially note Tom's valid concerns about third party code. OTOH checking against error codes should be common practice. And the failure can then clearly be located in the third party module, and not in Postgres. On the other hand, I think that a semaphore should be used instead of a signal at least for backends to wait for walsender. This would turn around the situation on HPUX in some degree. In this case, the remaining problem is that walsender cannot immediately wake up. But, since walsender wakes up periodically and that period can be tweaked (by new GUC wal_sender_delay), the overhead on HPUX might not be actually so big. Hm.. does the walsender really wake up periodically? IIRC the misbehavior discovered on HPUX is that select() gets restarted upon signaled with SA_RESTART - with its full timeout, so that a steady rate of signals would lock the walsender process within select() completely. Or what else do you use for the walsender to wake up periodically? However, my point is that I think you don't have to solve this problem. It should rather be taken care of by core. We can then pick up whatever solution is decided on. Just my 2c. Regards Markus Wanner -- 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] 8.4 open item: copy performance regression?
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Tom Lane wrote: I'm not convinced that WAL segment boundaries are particularly relevant to this. The unit of flushing is an 8K page, not a segment. We fsync() the old WAL segment every time we switch to a new WAL segment. That's what I meant by flush. If the walwriter is keeping up, it will fsync() the WAL more often, but 16MB is the maximum distance between fsync()s. I'm still not convinced --- to my mind the issue is not whether fsyncs happen but whether the COPY process has to wait for 'em, and I don't think that segment boundaries directly affect that. I'd still be interested to see similar measurements done with different wal_buffer settings. However, in the interests of getting this resolved in time for 8.4.0, I propose that we just settle on 16MB as the bulkwrite ring buffer size. There doesn't seem to be any evidence that a larger size will make for a significant improvement, and we shouldn't allow COPY to trash a bigger fraction of the arena than it really has to. 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] security checks for largeobjects?
On Mon, Jun 22, 2009 at 11:31:45AM -0400, Tom Lane wrote: David Fetter da...@fetter.org writes: On Mon, Jun 22, 2009 at 05:18:51PM +0300, Peter Eisentraut wrote: MED is management of external data, whereas the large objects are internal, no? It depends on your definition. The lo interface is pretty much to objects on the file system directly. LO's are transaction-controlled, and they're not (readily) accessible from outside the database. Seems rather completely different from regular filesystem files. Not according to SQL/MED. (In any case, there wasn't anything I liked about SQL/MED's ideas about external files, so I'm not in favor of modeling LO management after that.) Good point ;) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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: [BUGS] BUG #4862: different results in to_date() between 8.3.7 8.4.RC1
2009/6/23 Tom Lane t...@sss.pgh.pa.us: Brendan Jurd dire...@gmail.com writes: I should be able to get the same results by snipping an extra strspace_len() characters in the new code path in from_char_parse_int_len(). This ought to be a one-line fix that doesn't clobber the good parts of my work so far. I'll run some tests and then post a patch within the hour. Does that work for you? +1 [cross-posting to -hackers] Here's a one-line patch to fix a regression in the new from_char code I introduced into 8.4. Versions = 8.3 skipped over any whitespace immediately preceding any integer field, and this behaviour was lost in HEAD when my from_char patch was committed back in September '08 [1]. Fortunately, since the code has been refactored, this now only needs to be repaired in one place =) With thanks to Jeremy Ford for sending in his bug report, and an abject mea culpa. Cheers, BJ [1] http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=6f09ab2c24491a217f8b88012aababf3b723b902 from_char-skip-whitespace.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
Re: [HACKERS] 8.4 open item: copy performance regression?
Le 22 juin 2009 à 17:24, Tom Lane t...@sss.pgh.pa.us a écrit : Simon Riggs si...@2ndquadrant.com writes: I was thinking it might be beneficial to be able to defer writing WAL until COPY is complete, so heap_sync would either fsync the whole heap file or copy the whole file to WAL. What about indexes? Skip this optimisation if there are any. It's already Common practise to create them only after copy succeeded when possible for better bulk loading perfs. Then there's also the copy + Insert ... Select ... technique. -- dim -- 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] [BUGS] BUG #4862: different results in to_date() between 8.3.7 8.4.RC1
Brendan Jurd dire...@gmail.com writes: Here's a one-line patch to fix a regression in the new from_char code I introduced into 8.4. Versions = 8.3 skipped over any whitespace immediately preceding any integer field, and this behaviour was lost in HEAD when my from_char patch was committed back in September '08 [1]. Applied along with some regression test additions. Thanks for the quick response. 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] 8.4 open item: copy performance regression?
Tom Lane t...@sss.pgh.pa.us wrote: I wonder though whether the wal_buffers setting interacts with the ring size. Has everyone who's tested this used the same 16MB wal_buffers setting as in Alan's original scenario? I had been using his postgresql.conf file, then added autovacuum = off. When I tried with setting the ring size to 16MB, I accidentally left off the step to copy the postgresql.conf file, and got better performance. I alternated between the postgresql.conf file from earlier tests and the default file left there by the initdb, and got this: 8.4rc1 with 16MB ring, default postgresql.conf 0m23.223s 0m23.489s 0m23.921s 8.4rc1 with 16MB ring, Alan's postgresql.conf 0m28.678s 0m26.171s 0m27.513s default postgresql.conf (comments stripped) max_connections = 100 shared_buffers = 32MB datestyle = 'iso, mdy' lc_messages = 'C' lc_monetary = 'C' lc_numeric = 'C' lc_time = 'C' default_text_search_config = 'pg_catalog.english' Alan's postgresql.conf (comments stripped) shared_buffers = 256MB wal_buffers = 16MB checkpoint_segments = 100 autovacuum = off I'm not going to claim I know why, but I thought I should report it. Oh, and the 8.3.7 numbers and pre-patch numbers were averaging the same under the day-time load as the replication sync mode. So, with the ring size at 16MB this load is faster under 8.4 than 8.3. -Kevin -- 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] 8.4 open item: copy performance regression?
Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: I wonder though whether the wal_buffers setting interacts with the ring size. Has everyone who's tested this used the same 16MB wal_buffers setting as in Alan's original scenario? I had been using his postgresql.conf file, then added autovacuum = off. When I tried with setting the ring size to 16MB, I accidentally left off the step to copy the postgresql.conf file, and got better performance. Huh, that's bizarre. I can see that increasing shared_buffers should make no difference in this test case (we're not using them all anyway). But why should increasing wal_buffers make it slower? I forget the walwriter's control algorithm at the moment ... maybe it works harder when wal buffers are full? BTW, I committed the change to use 16MB; that will be in RC2. 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] 8.4 open item: copy performance regression?
On Mon, 22 Jun 2009, Kevin Grittner wrote: When I tried with setting the ring size to 16MB, I accidentally left off the step to copy the postgresql.conf file, and got better performance. Do you have happen to have a build with assertions turned on? That is one common cause of performance going down via increased shared_buffers that people tend to run into during beta. You can check via psql with show debug_assertions; -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- 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] 8.4 open item: copy performance regression?
Greg Smith gsm...@gregsmith.com wrote: Do you have happen to have a build with assertions turned on? Nope. I showed my ./configure options upthread, but can confirm with pg_config: BINDIR = /usr/local/pgsql-8.4rc1/bin DOCDIR = /usr/local/pgsql-8.4rc1/share/doc HTMLDIR = /usr/local/pgsql-8.4rc1/share/doc INCLUDEDIR = /usr/local/pgsql-8.4rc1/include PKGINCLUDEDIR = /usr/local/pgsql-8.4rc1/include INCLUDEDIR-SERVER = /usr/local/pgsql-8.4rc1/include/server LIBDIR = /usr/local/pgsql-8.4rc1/lib PKGLIBDIR = /usr/local/pgsql-8.4rc1/lib LOCALEDIR = /usr/local/pgsql-8.4rc1/share/locale MANDIR = /usr/local/pgsql-8.4rc1/share/man SHAREDIR = /usr/local/pgsql-8.4rc1/share SYSCONFDIR = /usr/local/pgsql-8.4rc1/etc PGXS = /usr/local/pgsql-8.4rc1/lib/pgxs/src/makefiles/pgxs.mk CONFIGURE = '--prefix=/usr/local/pgsql-8.4rc1' '--enable-integer-datetimes' '--enable-debug' '--disable-nls' '--with-libxml' CC = gcc CPPFLAGS = -D_GNU_SOURCE -I/usr/include/libxml2 CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -g CFLAGS_SL = -fpic LDFLAGS = -Wl,--as-needed -Wl,-rpath,'/usr/local/pgsql-8.4rc1/lib' LDFLAGS_SL = LIBS = -lpgport -lxml2 -lz -lreadline -lcrypt -ldl -lm VERSION = PostgreSQL 8.4rc1 You can check via psql with show debug_assertions; OK, we'll do it your way. :-) kgri...@athena:~/postgresql-8.4rc1 psql postgres psql (8.4rc1) Type help for help. postgres=# show debug_assertions; debug_assertions -- off (1 row) -Kevin -- 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] 8.4 open item: copy performance regression?
Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: A 25-30% performance regression in our main bulk loading mechanism should at least be explained before the release... I think a performance regression of that magnitude merits holding up a release to resolve. Note that in a follow-up post showing the slow 8.4 copying on Linux, the copy was 11.7% to 19.3% *faster* on 8.4 when the WAL writing was suppressed: http://archives.postgresql.org/pgsql-performance/2009-06/msg00219.php Extracting from that post: # I can reproduce that on Linux(CentoS 5.3/x86_64, Nehalem Xeon E5530) # on 8.4 I get: # # 3m59/4m01/3m56s runtime and a profile of # # samples %symbol name # 636302 19.6577 XLogInsert # 415510 12.8366 CopyReadLine # on 8.3.7 I get 2m58s,2m54s,2m55s # # and a profile of: # # samples %symbol name # 460966 16.2924 XLogInsert # 307386 10.8643 CopyReadLine # If I do the same test utilizing WAL bypass the picture changes: # # 8.3 runtimes:2m16,2min14s,2min22s # 8.4 runtime: 2m1s,2m,1m59s Is there a reason to believe that the XLogInsert part of this *only* affects bulk loads? -Kevin -- 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] 8.4 open item: copy performance regression?
Tom Lane t...@sss.pgh.pa.us wrote: Huh, that's bizarre. I can see that increasing shared_buffers should make no difference in this test case (we're not using them all anyway). But why should increasing wal_buffers make it slower? I forget the walwriter's control algorithm at the moment ... maybe it works harder when wal buffers are full? I created a postgresql.conf file with the options from the default file, and then tried that by itself again, and with each of three other options: none 0m24.540s 0m24.630s 0m23.778s checkpoint_segments = 100 0m30.251s 0m29.474s 0m26.604s wal_buffers = 16MB 0m24.487s 0m23.939s 0m23.557s shared_buffers = 256MB 0m25.885s 0m25.654s 0m24.025s So the big hit seems to come from boosting checkpoint_segments, although boosting shared_buffers seems to cause a slight slowdown. Boosting wal_buffers seemed to help a little. Both of these last two, though, are within the noise, so low confidence on those without a lot more tests. The checkpoint_segments seems dramatic enough to be real. I wonder if the test is short enough that it never got around to re-using any of them, so it was doing extra writes for the initial creation during the test? -Kevin -- 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] 8.4 open item: copy performance regression?
I wrote: Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: A 25-30% performance regression in our main bulk loading mechanism should at least be explained before the release... I think a performance regression of that magnitude merits holding up a release to resolve. Wow. That sure took a long time to come through. I posted that days ago -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Changed error message for blocks by prepared transactions
Hello,
the small attached patch changes the error message for a blocked
database in case there are prepared transactions. The original message
accessed by other users is misleading.
Example:
- snip -
postgres=# begin;
BEGIN
postgres=# prepare transaction 'abc';
PREPARE TRANSACTION
postgres=# \c template1
psql (8.4rc1)
Sie sind jetzt verbunden mit der Datenbank »template1«.
template1=# alter database postgres rename to test;
ERROR: database postgres is being blocked by prepared transactions
DETAIL: There are 1 prepared transaction(s) using the database.
- snip -
Translation still pending, how to add new messages to the .po files?
Kind regards
--
Andreas 'ads' Scherbaum
German PostgreSQL User Group
European PostgreSQL User Group - Board of Directors
Volunteer Regional Contact, Germany - PostgreSQL Project
PGDay.eu 2009 in Paris, Nov. 6/7, http://www.pgday.eu/
Index: src/backend/commands/dbcommands.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/dbcommands.c,v
retrieving revision 1.225
diff -u -3 -p -r1.225 dbcommands.c
--- src/backend/commands/dbcommands.c 11 Jun 2009 14:48:55 - 1.225
+++ src/backend/commands/dbcommands.c 22 Jun 2009 21:50:23 -
@@ -501,11 +501,24 @@ createdb(const CreatedbStmt *stmt)
* throw one.
*/
if (CountOtherDBBackends(src_dboid, notherbackends, npreparedxacts))
- ereport(ERROR,
-(errcode(ERRCODE_OBJECT_IN_USE),
- errmsg(source database \%s\ is being accessed by other users,
- dbtemplate),
- errdetail_busy_db(notherbackends, npreparedxacts)));
+ {
+ if (npreparedxacts 0)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_IN_USE),
+errmsg(source database \%s\ is being blocked by prepared transactions,
+ dbtemplate),
+ errdetail_busy_db(notherbackends, npreparedxacts)));
+ }
+ else
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_IN_USE),
+errmsg(source database \%s\ is being accessed by other users,
+ dbtemplate),
+ errdetail_busy_db(notherbackends, npreparedxacts)));
+ }
+ }
/*
* Select an OID for the new database, checking that it doesn't have a
@@ -799,11 +812,24 @@ dropdb(const char *dbname, bool missing_
* As in CREATE DATABASE, check this after other error conditions.
*/
if (CountOtherDBBackends(db_id, notherbackends, npreparedxacts))
- ereport(ERROR,
-(errcode(ERRCODE_OBJECT_IN_USE),
- errmsg(database \%s\ is being accessed by other users,
- dbname),
- errdetail_busy_db(notherbackends, npreparedxacts)));
+ {
+ if (npreparedxacts 0)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_IN_USE),
+ errmsg(database \%s\ is being blocked by prepared transactions,
+ dbname),
+ errdetail_busy_db(notherbackends, npreparedxacts)));
+ }
+ else
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_IN_USE),
+ errmsg(database \%s\ is being accessed by other users,
+ dbname),
+ errdetail_busy_db(notherbackends, npreparedxacts)));
+ }
+ }
/*
* Remove the database's tuple from pg_database.
@@ -940,11 +966,24 @@ RenameDatabase(const char *oldname, cons
* As in CREATE DATABASE, check this after other error conditions.
*/
if (CountOtherDBBackends(db_id, notherbackends, npreparedxacts))
- ereport(ERROR,
-(errcode(ERRCODE_OBJECT_IN_USE),
- errmsg(database \%s\ is being accessed by other users,
- oldname),
- errdetail_busy_db(notherbackends, npreparedxacts)));
+ {
+ if (npreparedxacts 0)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_IN_USE),
+ errmsg(database \%s\ is being blocked by prepared transactions,
+ oldname),
+ errdetail_busy_db(notherbackends, npreparedxacts)));
+ }
+ else
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_IN_USE),
+ errmsg(database \%s\ is being accessed by other users,
+ oldname),
+ errdetail_busy_db(notherbackends, npreparedxacts)));
+ }
+ }
/* rename */
newtup = SearchSysCacheCopy(DATABASEOID,
@@ -1077,11 +1116,24 @@ movedb(const char *dbname, const char *t
* As in CREATE DATABASE, check this after other error conditions.
*/
if (CountOtherDBBackends(db_id, notherbackends, npreparedxacts))
- ereport(ERROR,
-(errcode(ERRCODE_OBJECT_IN_USE),
- errmsg(database \%s\ is being accessed by other users,
- dbname),
- errdetail_busy_db(notherbackends, npreparedxacts)));
+ {
+ if (npreparedxacts 0)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_IN_USE),
+ errmsg(database \%s\ is being blocked by prepared transactions,
+ dbname),
+ errdetail_busy_db(notherbackends, npreparedxacts)));
+ }
+ else
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_IN_USE),
+ errmsg(database \%s\ is being accessed by other users,
+ dbname),
+ errdetail_busy_db(notherbackends, npreparedxacts)));
+ }
+ }
/*
* Get old and new database paths
--
Sent via
Re: [HACKERS] 8.4 open item: copy performance regression?
Kevin Grittner kevin.gritt...@wicourts.gov writes: The checkpoint_segments seems dramatic enough to be real. I wonder if the test is short enough that it never got around to re-using any of them, so it was doing extra writes for the initial creation during the test? That's exactly what I was about to suggest. Are you starting each run from a fresh initdb? If so, try running the load long enough that the number of WAL files stabilizes (should happen at 2x checkpoint_segments) and then start the test measurement. 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] Changed error message for blocks by prepared transactions
Andreas 'ads' Scherbaum adsm...@wars-nicht.de writes: the small attached patch changes the error message for a blocked database in case there are prepared transactions. Isn't this duplicative of the errdetail_busy_db code? And anyway I do not see a reason not to consider prepared transactions as other users. 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] 8.4 open item: copy performance regression?
On Mon, Jun 22, 2009 at 7:16 AM, Tom Lane t...@sss.pgh.pa.us wrote: Alan Li a...@truviso.com writes: How much concern is there for the contention for use cases where the WAL can't be bypassed? If you mean is something going to be done about it in 8.4, the answer is no. This is a pre-existing issue that there is no simple fix for. regards, tom lane No no, I am certainly not implying anything for the 8.4 timeframe. Moving forward, I imagine this being more of a problem for data warehouse applications, where bulk inserts occur on existing fact tables. In this case, the WAL cannot be bypassed (unless the bulk insert occurs on a newly created partition). And since COPY is cpu-bound, it would perhaps be advantageous to do parallel COPY's on the same table on multi-core systems, which won't work with WAL bypassing either. Thanks, Alan
Re: [HACKERS] security checks for largeobjects?
David Fetter wrote: On Mon, Jun 22, 2009 at 11:31:45AM -0400, Tom Lane wrote: David Fetter da...@fetter.org writes: On Mon, Jun 22, 2009 at 05:18:51PM +0300, Peter Eisentraut wrote: MED is management of external data, whereas the large objects are internal, no? It depends on your definition. The lo interface is pretty much to objects on the file system directly. LO's are transaction-controlled, and they're not (readily) accessible from outside the database. Seems rather completely different from regular filesystem files. Not according to SQL/MED. (In any case, there wasn't anything I liked about SQL/MED's ideas about external files, so I'm not in favor of modeling LO management after that.) Good point ;) Cheers, David. I would like to develop the feature independent from SQL/MED. -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.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] building without perl
I'm a bit confused as to what we're trying to support. While I was poking around tonight, I notice that we have both ./src/backend/utils/Gen_fmgrtab.sh and ./src/backend/utils/Gen_fmgrtab.pl, ostensibly because: # The reason for implementing this functionality twice is that we don't # require people to have perl to build from a tarball, but on the other # hand Windows can't deal with shell scripts. However, you definitely can't build from a git (or, presumably, CVS) checkout without perl, and in fact the error message leaves a good deal to be desired. Configure succeeds, but then: [...lots of output omitted...] make -C ../../../../src/port all make[5]: Entering directory `/home/rhaas/pgsql-git/src/port' make[5]: Nothing to be done for `all'. make[5]: Leaving directory `/home/rhaas/pgsql-git/src/port' ./parse.pl . ../../../../src/backend/parser/gram.y preproc.y /bin/sh: : command not found make[4]: *** [preproc.y] Error 127 make[4]: *** Deleting file `preproc.y' make[4]: Leaving directory `/home/rhaas/pgsql-git/src/interfaces/ecpg/preproc' make[3]: *** [all] Error 2 make[3]: Leaving directory `/home/rhaas/pgsql-git/src/interfaces/ecpg' make[2]: *** [all] Error 2 make[2]: Leaving directory `/home/rhaas/pgsql-git/src/interfaces' make[1]: *** [all] Error 2 make[1]: Leaving directory `/home/rhaas/pgsql-git/src' make: *** [all] Error 2 I've seen previous references to perl being a build requirement as well, so I guess I'm a bit confused. Is there some tarball that is built with just enough extra stuff in it to avoid the perl dependency? And if so, how confident are we that we're solving a real problem here as opposed to a hypothetical one? Admittedly, I tend to use machines running relatively recent operating system versions, but I think the last time that I ran into a machine that didn't have perl on it was not more recently than 1994 (I'm guessing someone is now going to tell me what a sheltered life I lead...). ...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] building without perl
Robert Haas robertmh...@gmail.com writes: I'm a bit confused as to what we're trying to support. You are not supposed to need perl to build from a release tarball (except on Windows which doesn't have a decent make or shell, so we depend on perl instead). You do need it to build from a CVS checkout. Same story as bison/flex. make -C ../../../../src/port all make[5]: Entering directory `/home/rhaas/pgsql-git/src/port' make[5]: Nothing to be done for `all'. make[5]: Leaving directory `/home/rhaas/pgsql-git/src/port' ./parse.pl . ../../../../src/backend/parser/gram.y preproc.y /bin/sh: : command not found make[4]: *** [preproc.y] Error 127 make[4]: *** Deleting file `preproc.y' Hmm, that's a new dependency on perl, and it wasn't properly guarded ... will fix. I think the last time that I ran into a machine that didn't have perl on it was not more recently than 1994 (I'm guessing someone is now going to tell me what a sheltered life I lead...). Yup. 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] building without perl
On Mon, Jun 22, 2009 at 10:12 PM, Tom Lanet...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I'm a bit confused as to what we're trying to support. You are not supposed to need perl to build from a release tarball (except on Windows which doesn't have a decent make or shell, so we depend on perl instead). You do need it to build from a CVS checkout. Same story as bison/flex. FWIW, README.CVS mentions bison and flex but not perl. So, is our policy that it's OK to use perl to generate things, as long as they are things that are architecture-independent and configuration-independent therefore could be pregenerated for the release tarball? If so, it seems we might be able to punt Gen_fmgrtab.sh and just keep Gen_fmgrtab.pl... make -C ../../../../src/port all make[5]: Entering directory `/home/rhaas/pgsql-git/src/port' make[5]: Nothing to be done for `all'. make[5]: Leaving directory `/home/rhaas/pgsql-git/src/port' ./parse.pl . ../../../../src/backend/parser/gram.y preproc.y /bin/sh: : command not found make[4]: *** [preproc.y] Error 127 make[4]: *** Deleting file `preproc.y' Hmm, that's a new dependency on perl, and it wasn't properly guarded ... will fix. I think the last time that I ran into a machine that didn't have perl on it was not more recently than 1994 (I'm guessing someone is now going to tell me what a sheltered life I lead...). Yup. I suppose this was intended to be funny, but I'd be curious as to the details. ...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] building without perl
Robert Haas robertmh...@gmail.com writes: So, is our policy that it's OK to use perl to generate things, as long as they are things that are architecture-independent and configuration-independent therefore could be pregenerated for the release tarball? If so, it seems we might be able to punt Gen_fmgrtab.sh and just keep Gen_fmgrtab.pl... Yeah, that was discussed when Gen_fmgrtab.pl was written, but we kept the old method around out of sheer conservatism (not to mention that the early versions of Gen_fmgrtab.pl weren't very good). I think the last time that I ran into a machine that didn't have perl on it was not more recently than 1994 (I'm guessing someone is now going to tell me what a sheltered life I lead...). Yup. I suppose this was intended to be funny, but I'd be curious as to the details. Actually, further investigation discloses that someone broke the no-Perl defense in psql's makefile back in 8.0, and we hadn't noticed till now. So it certainly seems to be the case that nobody associated with the Postgres project works in a Perl-free environment. Nonetheless, the makefiles are supposed to behave sanely if Perl is missing. 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] building without perl
Tom Lane wrote: Actually, further investigation discloses that someone broke the no-Perl defense in psql's makefile back in 8.0, and we hadn't noticed till now. So it certainly seems to be the case that nobody associated with the Postgres project works in a Perl-free environment. Nonetheless, the makefiles are supposed to behave sanely if Perl is missing. Don't we ship sql_help.h in the tarball? I thought that's all the perl was used for in psql. cheers andrew -- 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] building without perl
Andrew Dunstan and...@dunslane.net writes: Tom Lane wrote: Actually, further investigation discloses that someone broke the no-Perl defense in psql's makefile back in 8.0, and we hadn't noticed till now. Don't we ship sql_help.h in the tarball? I thought that's all the perl was used for in psql. Yeah, but the makefile protected that with ifdef PERL, which stopped failing as intended when someone forced some quotes into the value of PERL. I'm about to commit something that hopefully will be a bit more robust. 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] security checks for largeobjects?
On Tue, Jun 23, 2009 at 10:38:59AM +0900, KaiGai Kohei wrote: David Fetter wrote: On Mon, Jun 22, 2009 at 11:31:45AM -0400, Tom Lane wrote: David Fetter da...@fetter.org writes: On Mon, Jun 22, 2009 at 05:18:51PM +0300, Peter Eisentraut wrote: MED is management of external data, whereas the large objects are internal, no? It depends on your definition. The lo interface is pretty much to objects on the file system directly. LO's are transaction-controlled, and they're not (readily) accessible from outside the database. Seems rather completely different from regular filesystem files. Not according to SQL/MED. (In any case, there wasn't anything I liked about SQL/MED's ideas about external files, so I'm not in favor of modeling LO management after that.) Good point ;) I would like to develop the feature independent from SQL/MED. If, as I suspect, SQL/MED does something that would collide with your feature, you're about to let yourself in for even more pain, as we tend to go with standard features over ones that would be unique to PostgreSQL, given the choice. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] security checks for largeobjects?
KaiGai Kohei wrote: KaiGai Kohei wrote: The todo list says, as follows: * Binary Data o Add security checks for large objects http://wiki.postgresql.org/wiki/Todo#Binary_Data Is anyone working on? or interested in? OK, it seems to me nobody is working on the todo item. I'll also submit this feature on the v8.5 development cycle. At first, I would have a discussion about its interfaces and functionalities. Here are a few issues. * What permissions should be checked? It is quite natural to check 'read' and 'write' permission for largeobjects. In addition, we may need to consider how 'create' and 'unlink' permission should be handled. When we create a database objects under the certain schema, it checks ACL_CREATE privilege on the parent schema object. But, now largeobjects are not corresponding to any certain schemas. When we drop a database objects, it checks ownership of the target objects. But, now largeobjects does not have its owner identifier within pg_largeobject. My preference is to add a namespace and a owner id for each largeobjects and checks create permissions for the schema object, and unlink permission based on its ownership. * What interface is preferable? We have two options here. The one is an enhancement of current GRANT/REVOKE statement, such as: GRANT READ,WRITE ON LARGE OBJECT 1234 TO kaigai; The other option is a few new largeobject functions to set up permissions on largeobjects, such as: SELECT lo_grant(1234, 'kaigai', 'read,write'); My preference is the later approach because the first one consumes two new permission bits, although here is no fundamental differences to SELECT and UPDATE. Thanks, any comments please. From the viewpoint of the implementation, we have two approaches. Someone needs to have various kind of attributes corresponding to a certain largeobject, such as owner-id, namespace-id and acls. We call them metadata. 1. Metadata within pg_largeobject Now, a largeobject consists of multiple page-frames, stored in pg_largeobject system catalog. This approach adds a few new fields to store the metadata on the pg_largeobject system catalog, and copies the metadata to all the page-frames. It will minimize the changes in largeobject subsystem, but data structure is not smart in my sense. 2. Metadata within separated system catalog. This approach adds a new system catalog to manage the metadata of the largeobjects. It will contain the owner-id, namespace-id and acls, and all the page-frames within pg_largeobject will indicate a tuple within the new system catalog. It will provide natural data structure, but scale of the changes may be larger than the first approach. My preference is the later one. It reduces the pain to manage consistency of the metadata between page-frames. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.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] building without perl
Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: Tom Lane wrote: Actually, further investigation discloses that someone broke the no-Perl defense in psql's makefile back in 8.0, and we hadn't noticed till now. Don't we ship sql_help.h in the tarball? I thought that's all the perl was used for in psql. Yeah, but the makefile protected that with ifdef PERL, which stopped failing as intended when someone forced some quotes into the value of PERL. I'm about to commit something that hopefully will be a bit more robust. Oh. Oops! cheers andrew -- 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] security checks for largeobjects?
David Fetter wrote: On Tue, Jun 23, 2009 at 10:38:59AM +0900, KaiGai Kohei wrote: David Fetter wrote: On Mon, Jun 22, 2009 at 11:31:45AM -0400, Tom Lane wrote: David Fetter da...@fetter.org writes: On Mon, Jun 22, 2009 at 05:18:51PM +0300, Peter Eisentraut wrote: MED is management of external data, whereas the large objects are internal, no? It depends on your definition. The lo interface is pretty much to objects on the file system directly. LO's are transaction-controlled, and they're not (readily) accessible from outside the database. Seems rather completely different from regular filesystem files. Not according to SQL/MED. (In any case, there wasn't anything I liked about SQL/MED's ideas about external files, so I'm not in favor of modeling LO management after that.) Good point ;) I would like to develop the feature independent from SQL/MED. If, as I suspect, SQL/MED does something that would collide with your feature, you're about to let yourself in for even more pain, as we tend to go with standard features over ones that would be unique to PostgreSQL, given the choice. Since the largeobject is originally a unique feature in PostgreSQL, I think it can be considered independently from the standard feature. However, we have no fixed security design here. If you can provide more preferable security design, could you suggest us? I never disagree to improve the features. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
