Re: [HACKERS] alpha4 bundled -- please verify
Peter Eisentraut wrote: Alpha4 has been bundled and is available at http://developer.postgresql.org/~petere/alpha/ Please check that it is sane. Since I'll be away for the next few days, someone has to take it from here: write announcement, move tarballs, send announcement. I can pass make check OK MacOS 10.4.11 PPC - gcc 4.0.1 -- Shane Ambler pgSQL (at) Sheeky (dot) Biz -- 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 t...@sss.pgh.pa.us writes: It's really not much different from a function call with subplans as functions. The PARAM_EXEC stuff looks just like 1950's era non-reentrant function parameter passing mechanisms, back before anybody had thought of recursive functions and they passed a function's parameters in fixed storage locations. It's okay for this because subplan trees are never recursive ... hand waving alert How much does this stuff is dependent on the current state of the backend? If not that much, maybe the planner could consider costs of having another backend started to handle the subplan. We'd need a tuplestore or some other place to exchange results (a unix style pipe maybe, but we do support windows as a platform), and a special executor mode for running a subplan, maybe more than once. Oh, and a way to share the same snapshot in more than one backend too, but that's being worked on I think. Ok that's a far stretch from the question at hand, but would that be a plausible approach to have parallel queries in PostgreSQL ? Regards, -- 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] explain and PARAM_EXEC
On Sat, Feb 20, 2010 at 4:33 AM, Tom Lane t...@sss.pgh.pa.us wrote: It's really not much different from a function call with subplans as functions. Perhaps it would be clearer to display the (Subplan 1) in a function call style format like Subplan1(b.oid) -- greg -- 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
On Fri, Feb 19, 2010 at 11:58 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Fri, Feb 19, 2010 at 11:33 PM, Tom Lane t...@sss.pgh.pa.us wrote: We can also fetch that tuple's relfilenode and pass it to the subplan, which we do by setting the $0 Param value before invoking the subplan. Are the same tuples in scope when evaluating the expression that sets $0 as were in scope when evaluating ((b.oid)::integer + 1)? Yes, exactly the same. The parameter-value expression is just like any other scalar expression that could appear where the SubPlan reference is. It doesn't know anything about the subplan, really. OK, thanks for the explanation. That makes more sense now. I've got to go study this some more before I ask my next question... ...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] explain and PARAM_EXEC
On Sat, Feb 20, 2010 at 6:57 AM, Dimitri Fontaine dfonta...@hi-media.com wrote: Tom Lane t...@sss.pgh.pa.us writes: It's really not much different from a function call with subplans as functions. The PARAM_EXEC stuff looks just like 1950's era non-reentrant function parameter passing mechanisms, back before anybody had thought of recursive functions and they passed a function's parameters in fixed storage locations. It's okay for this because subplan trees are never recursive ... hand waving alert How much does this stuff is dependent on the current state of the backend? A whole lot. Ok that's a far stretch from the question at hand, but would that be a plausible approach to have parallel queries in PostgreSQL ? This is really a topic for another thread, but at 100,000 feet it seems to me that the hardest question is - how will you decide which operations to parallelize in the first place? Actually making it happen is really hard, too, of course, but even to get that that point you have to have some model for what types of operations it makes sense to parallelize and how you're going to decide when it's a win. ...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] explain and PARAM_EXEC
On Sat, Feb 20, 2010 at 7:53 AM, Greg Stark gsst...@mit.edu wrote:
On Sat, Feb 20, 2010 at 4:33 AM, Tom Lane t...@sss.pgh.pa.us wrote:
It's really not much different from a function call with subplans as
functions.
Perhaps it would be clearer to display the (Subplan 1) in a function
call style format like Subplan1(b.oid)
I thought about that, too... maybe for 9.1 we should consider it. It
might be nice to add some sort of glyph to make the user less likely
to think that Subplan1 is in fact a function. Subplan1(b.oid)?
${Subplan1}(b.oid)? I dunno.
...Robert
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
parallelizing subplan execution (was: [HACKERS] explain and PARAM_EXEC)
Robert Haas robertmh...@gmail.com writes: On Sat, Feb 20, 2010 at 6:57 AM, Dimitri Fontaine dfonta...@hi-media.com wrote: How much does this stuff is dependent on the current state of the backend? A whole lot. Bad news. Ok that's a far stretch from the question at hand, but would that be a plausible approach to have parallel queries in PostgreSQL ? This is really a topic for another thread, but at 100,000 feet it seems to me that the hardest question is - how will you decide which operations to parallelize in the first place? Actually making it happen is really hard, too, of course, but even to get that that point you have to have some model for what types of operations it makes sense to parallelize and how you're going to decide when it's a win. My naive thoughts would be to add some cost parameters. The fact to fork() another backend first, then model for each supported subplan (we will want to add more, or maybe have a special rendez-vous-materialise node) some idea of the data exchange cost. Now the planner would as usual try to find the less costly plan, and will be able to compare plans with and without distributing the work. Overly naive ? Regards, -- 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] explain and PARAM_EXEC
Robert Haas robertmh...@gmail.com writes: On Sat, Feb 20, 2010 at 6:57 AM, Dimitri Fontaine Ok that's a far stretch from the question at hand, but would that be a plausible approach to have parallel queries in PostgreSQL ? This is really a topic for another thread, but at 100,000 feet it seems to me that the hardest question is - how will you decide which operations to parallelize in the first place? Actually making it happen is really hard, too, of course, but even to get that that point you have to have some model for what types of operations it makes sense to parallelize and how you're going to decide when it's a win. Indeed, and if I were setting out to parallelize queries in PG (which I am not), subplans would be the last thing I would think about. You could put an enormous amount of work in there and have nothing much to show for it, because the construct doesn't even arise in many queries. Even where the user wrote something that looks like a subplan, the planner will do its best to get rid of it by turning it into a join. So if you want to parallelize queries, start someplace else. The past discussions of this have revolved around splitting the node tree of an ordinary query plan into separately executable parts. Maybe a subplan could be one of the cut points for such an approach, but if it's the only one or even the main one, you're wasting your 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] explain and PARAM_EXEC
Robert Haas robertmh...@gmail.com writes:
On Sat, Feb 20, 2010 at 7:53 AM, Greg Stark gsst...@mit.edu wrote:
Perhaps it would be clearer to display the (Subplan 1) in a function
call style format like Subplan1(b.oid)
I thought about that, too... maybe for 9.1 we should consider it. It
might be nice to add some sort of glyph to make the user less likely
to think that Subplan1 is in fact a function. Subplan1(b.oid)?
${Subplan1}(b.oid)? I dunno.
You really can't escape the need to identify which $N symbol is
associated with which parameter value. As soon as you've got more than
one subplan in a query, that becomes a nontrivial thing for a user to
guess.
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] auto_explain causes regression failures
Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: With the following settings custom_variable_classes = 'auto_explain' auto_explain.log_min_duration = 0 auto_explain.log_format = 'xml' auto_explain.log_analyze = on auto_explain.log_verbose = on shared_preload_libraries = 'auto_explain' I am getting regression failures on the rowtypes, transactions and arrays tests. This seems to be fixed now in HEAD. Somebody should try 8.4 as well. Done. All is good now. Thanks. 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] explain and PARAM_EXEC
On Fri, Feb 19, 2010 at 10:22 PM, Tom Lane t...@sss.pgh.pa.us 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)) what if we put fully qualified names every time we use a reference from a subplan? something like: QUERY PLAN Seq Scan on pg_catalog.pg_class b (cost=0.00..2250.22 rows=271 width=4) Output: (SubPlan 1) SubPlan 1 - Index Scan using pg_class_oid_index on pg_catalog.pg_class a (cost=0.00..8.27 rows=1 width=4) Output: a.oid Index Cond: (a.oid = subplan1.$0) -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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: Forbid setval() during recovery.
hei...@postgresql.org (Heikki Linnakangas) writes: Forbid setval() during recovery. This prevents the PANIC reported by Erik Rijkers. Patch by Andres Freund. ISTM this is the wrong fix. The real bug is that setval() doesn't check XactReadOnly. Now XactReadOnly is only a soft read only mode, but I don't see an argument for either setval() or nextval() to be allowed when that's set. Any objections to changing 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] explain and PARAM_EXEC
Tom Lane t...@sss.pgh.pa.us writes: Indeed, and if I were setting out to parallelize queries in PG (which I am not), subplans would be the last thing I would think about. You could put an enormous amount of work in there and have nothing much to show for it, because the construct doesn't even arise in many queries. Even where the user wrote something that looks like a subplan, the planner will do its best to get rid of it by turning it into a join. I guess that's because subplans do cost more than their alternative. The idea was to provide a parallel implementation of them, so they get some benefits, then compare better to plain join'ing. But I can see that's an entirely wrong approach, and I'm happy to know that and glad I asked, thanks :) So if you want to parallelize queries, start someplace else. The past discussions of this have revolved around splitting the node tree of an ordinary query plan into separately executable parts. Maybe a subplan could be one of the cut points for such an approach, but if it's the only one or even the main one, you're wasting your time. Unless you arrange for the planner to have good (new) reasons to prefer using subplans, or provide subplan based joins ? Ok, once you've done that, maybe you're back to the main problem and just changed its name. Regards, -- 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] [COMMITTERS] pgsql: Forbid setval() during recovery.
On Saturday 20 February 2010 17:34:50 Tom Lane wrote: hei...@postgresql.org (Heikki Linnakangas) writes: Forbid setval() during recovery. This prevents the PANIC reported by Erik Rijkers. Patch by Andres Freund. ISTM this is the wrong fix. The real bug is that setval() doesn't check XactReadOnly. Now XactReadOnly is only a soft read only mode, but I don't see an argument for either setval() or nextval() to be allowed when that's set. Any objections to changing that? Not from my side - sounds more sensible... Andres -- 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
Dimitri Fontaine wrote: Tom Lane t...@sss.pgh.pa.us writes: Well, that isn't really going to help us in terms of what to do for 9.0. But the possibility that something like this might happen in future is one thing that makes me hesitant about extending CREATE LANGUAGE right now --- the more bells and whistles we put on it, the harder it will be to have a clean upgrade to an EXTENSION facility. Agreed, but we could still evolve the command with keeping an eye on the future. As of now I intend to implement what's on this page: http://wiki.postgresql.org/wiki/ExtensionPackaging So maybe a quick glance then some early design approval would make it possible to change the CREATE LANGUAGE in an EXTENSION compatible way. One thing that strikes me about your proposal is that INSTALL EXTENSION doesn't sound like a CREATE OR REPLACE operation. It sounds like a CREATE IF NOT EXISTS operation, because there simply is not a guarantee that what gets installed is exactly what the user expected --- in particular, for pg_dump, it isn't guaranteeing that the new version's extension is exactly like what was in the old database. And that's not a bad thing, in this context; it's more or less the Whole Point. In fact it's not either one or the other, because the CREATE EXTENSION is providing the meta data, which includes an optional upgrade function. So if you INSTALL EXTENSION over an existing one, and meantime you've been installing the new version (file system install, PGAN or distro packaged or source level install; then the new CREATE EXTENSION which should be given in the foo.sql for the foo EXTENSION), in this case it's an upgrade, and what INSTALL EXTENSION is meant to do is run the upgrade function with as arguments current and new version numbers. It's when the EXTENSION is not providing this upgrade function that the behavior is more CREATE OR REPLACE, because it'd then run the installation script all over again. In case you provided an upgrade function, we're yet to see how to provide facilities to the extensions authors in order to easily address the columns of their data type and the indexes from their operator classes, etc. This discussion is sounding very design-ish, which makes me think we should just leave things unchanged for 9.0 and have external regression test designers work around this problem in their Makefiles, as Alvaro suggested. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- 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] Recent vendor SSL renegotiation patches break PostgreSQL
Tom Lane wrote: Chris Campbell chris_campb...@mac.com writes: Is there a way to detect when the SSL library has renegotiation disabled? Probably not. The current set of emergency security patches would certainly not have exposed any new API that would help us tell this :-( If said patches were done properly they'd have also turned an application-level renegotiation request into a no-op, instead of breaking apps by making it fail --- but apparently they were not done properly. Is there anything remaining to do on this issue? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
Bruce Momjian br...@momjian.us writes: This discussion is sounding very design-ish, which makes me think we should just leave things unchanged for 9.0 and have external regression test designers work around this problem in their Makefiles, as Alvaro suggested. I would have said that some time ago, except that I think we have a must fix issue here: isn't pg_upgrade broken for any database containing plpgsql? A decent solution for that probably will allow something to fall out for the regression test problem too. 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] Recent vendor SSL renegotiation patches break PostgreSQL
Bruce Momjian br...@momjian.us writes: Tom Lane wrote: Chris Campbell chris_campb...@mac.com writes: Is there a way to detect when the SSL library has renegotiation disabled? Probably not. The current set of emergency security patches would certainly not have exposed any new API that would help us tell this :-( If said patches were done properly they'd have also turned an application-level renegotiation request into a no-op, instead of breaking apps by making it fail --- but apparently they were not done properly. Is there anything remaining to do on this issue? I'm not sure. My impression is that by the time we had anything in the field, there will be real fixes for the SSL renegotiation problem. So all we'd be accomplishing is to weaken security for people who have those fixes, to cater to people who are using copies of openssl they'd obtained in the past couple of months and then not updated to latest. However, if anyone thinks that the SSL problem isn't going to get fixed promptly, maybe it needs more consideration. 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
Tom Lane wrote:
Bruce Momjian br...@momjian.us writes:
This discussion is sounding very design-ish, which makes me think we
should just leave things unchanged for 9.0 and have external regression
test designers work around this problem in their Makefiles, as Alvaro
suggested.
I would have said that some time ago, except that I think we have a
must fix issue here: isn't pg_upgrade broken for any database
containing plpgsql? A decent solution for that probably will allow
something to fall out for the regression test problem too.
Uh, well, I added this to pg_dump.c for 9.0:
else if (g_fout-remoteVersion = 80300)
{
/* pg_language has a lanowner column */
/* pg_language has a lanowner column */
appendPQExpBuffer(query, SELECT tableoid, oid,
lanname, lanpltrusted, lanplcallfoid,
lanvalidator, lanacl,
(%s lanowner) AS lanowner
FROM pg_language
WHERE lanispl%s
ORDER BY oid,
username_subquery,
binary_upgrade ? \nAND lanname != 'plpgsql' : );
---
meaning it will not dump plpsql when doing a binary upgrade.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do
+ If your life is a hard drive, Christ can be your backup. +
--
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] PGXS: REGRESS_OPTS=--load-language=plpgsql
On Feb 20, 2010, at 9:49 AM, Tom Lane wrote: This discussion is sounding very design-ish, which makes me think we should just leave things unchanged for 9.0 and have external regression test designers work around this problem in their Makefiles, as Alvaro suggested. I would have said that some time ago, except that I think we have a must fix issue here: isn't pg_upgrade broken for any database containing plpgsql? A decent solution for that probably will allow something to fall out for the regression test problem too. There is also the must fix issue with pg_regress. Thanks, David -- 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
David E. Wheeler wrote: On Feb 20, 2010, at 9:49 AM, Tom Lane wrote: This discussion is sounding very design-ish, which makes me think we should just leave things unchanged for 9.0 and have external regression test designers work around this problem in their Makefiles, as Alvaro suggested. I would have said that some time ago, except that I think we have a must fix issue here: isn't pg_upgrade broken for any database containing plpgsql? A decent solution for that probably will allow something to fall out for the regression test problem too. There is also the must fix issue with pg_regress. Why? My pg_regress runs just fine. If you are talking about 3rd party modules, I suggested conditional Makefile rules. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
On Feb 20, 2010, at 11:03 AM, Bruce Momjian wrote: There is also the must fix issue with pg_regress. Why? My pg_regress runs just fine. If you are talking about 3rd party modules, I suggested conditional Makefile rules. Because you can either make the simple change to pg_regress that David Fetter sent yesterday and have things continue to work, or you can break a slew of third-party module test suites (and possibly modules, as well) and make a lot of other people do a lot more work, not to mention email -hackers and ask WTF happened because they may well not know. I think that not changing pg_regress is more work for third-party module maintainers *and* more work for the Pg community when those maintainers come asking what happened and for advice on how to fix it. Best, David -- 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
David E. Wheeler wrote: On Feb 20, 2010, at 11:03 AM, Bruce Momjian wrote: There is also the must fix issue with pg_regress. Why? My pg_regress runs just fine. If you are talking about 3rd party modules, I suggested conditional Makefile rules. Because you can either make the simple change to pg_regress that David Fetter sent yesterday and have things continue to work, or you can break a slew of third-party module test suites (and possibly modules, as well) and make a lot of other people do a lot more work, not to mention email -hackers and ask WTF happened because they may well not know. I think that not changing pg_regress is more work for third-party module maintainers *and* more work for the Pg community when those maintainers come asking what happened and for advice on how to fix it. Well, I was asking why you labeled it must fix rather than should fix. I am fine with the pg_regress.c change. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
Bruce Momjian br...@momjian.us writes: Tom Lane wrote: I would have said that some time ago, except that I think we have a must fix issue here: isn't pg_upgrade broken for any database containing plpgsql? A decent solution for that probably will allow something to fall out for the regression test problem too. Uh, well, I added this to pg_dump.c for 9.0: That's a crock that needs to go away ASAP. 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Tom Lane wrote: I would have said that some time ago, except that I think we have a must fix issue here: isn't pg_upgrade broken for any database containing plpgsql? A decent solution for that probably will allow something to fall out for the regression test problem too. Uh, well, I added this to pg_dump.c for 9.0: That's a crock that needs to go away ASAP. Well, it works, so you are going to need to explain it. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- 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] Using views for row-level access control is leaky
Simon Riggs wrote: On Fri, 2009-10-23 at 10:04 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: On Fri, 2009-10-23 at 19:38 +0900, KaiGai Kohei wrote: Sorry, what is happen if function is marked as plan security? I was suggesting an intelligent default by which we could determine function marking implicitly, if it was not explicitly stated on the CREATE FUNCTION. The thought that's been in the back of my mind is that you could solve 99% of the performance problem if you trusted all builtin functions and nothing else. This avoids the question of who gets to mark functions as trustable. That is a very good default. My experience is that those 1% of cases are responsible for 99% of wasted time, so the ability to specify things for user functions is critical. If we make user extensibility second rate we will force solutions to be second rate also. (e.g. where would PostGIS be without type-specific analyze functions?). Added to TODO: Prevent low-cost functions from seeing unauthorized view rows -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] scheduler in core
Hi, I'm trying to figure out how difficult is this What we need: - a shared catalog - an API for filling the catalog - a scheduler daemon - pg_dump support A shared catalog - Why shared? obviously because we don't want to scan all database's pg_job every time the daemon wake up. Maybe something like: pg_job ( oid-- use the oid as pk jobname jobdatoid -- job database oid jobowner -- for permission's checking jobstarttime -- year to minute jobfrequency -- an interval? jobnexttime or joblasttime jobtype -- if we are going to allow plain sql or executable/shell job types jobexecute or jobscript ) comments about the catalog? An API for filling the catalog - do we want a CREATE JOB SQL synatx? FWIW, Oracle uses functions to create/remove jobs. An scheduler daemon I think we can use 8.3's autovacuum daemon as a reference for this... AFAIK, it's a child of postmaster that sleep for $naptime and then looks for something to do (it also looks in a catalog) and the send a worker to do it that's what we need to do but... for the $naptime i think we can autoconfigure it, when we execute a job look for the next job in queue and sleep until we are going to reach the time to execute it i don't think we need a max_worker parameter, it should launch as many workers as it needs pg_dump support -- dump every entry of the pg_job catalog as a CREATE JOB SQL statement or a create_job() function depending on what we decided ideas? comments? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] scheduler in core
On Sat, Feb 20, 2010 at 9:33 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: Hi, I'm trying to figure out how difficult is this Why not just use pgAgent? It's far more flexible than the design you've suggested, and already exists. -- Dave Page EnterpriseDB UK: 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] scheduler in core
On Sat, Feb 20, 2010 at 4:33 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: Hi, I'm trying to figure out how difficult is this What we need: - a shared catalog - an API for filling the catalog - a scheduler daemon - pg_dump support A shared catalog - Why shared? obviously because we don't want to scan all database's pg_job every time the daemon wake up. Maybe something like: pg_job ( oid -- use the oid as pk jobname jobdatoid -- job database oid jobowner -- for permission's checking jobstarttime -- year to minute jobfrequency -- an interval? jobnexttime or joblasttime jobtype -- if we are going to allow plain sql or executable/shell job types jobexecute or jobscript ) comments about the catalog? An API for filling the catalog - do we want a CREATE JOB SQL synatx? FWIW, Oracle uses functions to create/remove jobs. An scheduler daemon I think we can use 8.3's autovacuum daemon as a reference for this... AFAIK, it's a child of postmaster that sleep for $naptime and then looks for something to do (it also looks in a catalog) and the send a worker to do it that's what we need to do but... for the $naptime i think we can autoconfigure it, when we execute a job look for the next job in queue and sleep until we are going to reach the time to execute it i don't think we need a max_worker parameter, it should launch as many workers as it needs pg_dump support -- dump every entry of the pg_job catalog as a CREATE JOB SQL statement or a create_job() function depending on what we decided ideas? comments? IMNSHO, an 'in core' scheduler would be useful. however, I think before you tackle a scheduler, we need proper stored procedures. Our existing functions don't cut it because you can manage the transaction state yourself. merlin -- 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
On Sat, Feb 20, 2010 at 2:51 PM, Bruce Momjian br...@momjian.us wrote: David E. Wheeler wrote: On Feb 20, 2010, at 11:03 AM, Bruce Momjian wrote: There is also the must fix issue with pg_regress. Why? My pg_regress runs just fine. If you are talking about 3rd party modules, I suggested conditional Makefile rules. Because you can either make the simple change to pg_regress that David Fetter sent yesterday and have things continue to work, or you can break a slew of third-party module test suites (and possibly modules, as well) and make a lot of other people do a lot more work, not to mention email -hackers and ask WTF happened because they may well not know. I think that not changing pg_regress is more work for third-party module maintainers *and* more work for the Pg community when those maintainers come asking what happened and for advice on how to fix it. Well, I was asking why you labeled it must fix rather than should fix. I am fine with the pg_regress.c change. Yeah, if it makes life easier for other people, I say we go for it. ...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] scheduler in core
Dave Page dp...@pgadmin.org writes: Why not just use pgAgent? It's far more flexible than the design you've suggested, and already exists. What would it take to have it included in core, so that it's not a separate install to do? I'd love to have some support for running my maintenance pl functions directly from the database. I mean without installing, running and monitoring another (set of) process. Main advantage over cron or another scheduler being that it'd be part of my transactional backups, of course. Use cases, in case it's needed already, include creating new partitions, materializing views at known intervals, more general maintenance like vacuum and clusters operations, some reporting that could be done in the database itself, etc. Regards, -- 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] scheduler in core
pg_job ( oid -- use the oid as pk jobname jobdatoid -- job database oid jobowner -- for permission's checking jobstarttime -- year to minute jobfrequency -- an interval? jobnexttime or joblasttime jobtype -- if we are going to allow plain sql or executable/shell job types jobexecute or jobscript ) comments about the catalog? + success_action +failure_action An API for filling the catalog - do we want a CREATE JOB SQL synatx? FWIW, Oracle uses functions to create/remove jobs. An scheduler daemon I think we can use 8.3's autovacuum daemon as a reference for this... AFAIK, it's a child of postmaster that sleep for $naptime and then looks for something to do (it also looks in a catalog) and the send a worker to do it that's what we need to do but... for the $naptime i think we can autoconfigure it, when we execute a job look for the next job in queue and sleep until we are going to reach the time to execute it i don't think we need a max_worker parameter, it should launch as many workers as it needs pg_dump support -- dump every entry of the pg_job catalog as a CREATE JOB SQL statement or a create_job() function depending on what we decided ideas? comments? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
Robert Haas robertmh...@gmail.com writes: On Sat, Feb 20, 2010 at 2:51 PM, Bruce Momjian br...@momjian.us wrote: Well, I was asking why you labeled it must fix rather than should fix. I am fine with the pg_regress.c change. Yeah, if it makes life easier for other people, I say we go for it. I don't think that the way to fix this is to have an ugly kluge in pg_dump and another ugly kluge in pg_regress (and no doubt ugly kluges elsewhere by the time all the dust settles). 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
On Sat, Feb 20, 2010 at 5:53 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Sat, Feb 20, 2010 at 2:51 PM, Bruce Momjian br...@momjian.us wrote: Well, I was asking why you labeled it must fix rather than should fix. I am fine with the pg_regress.c change. Yeah, if it makes life easier for other people, I say we go for it. I don't think that the way to fix this is to have an ugly kluge in pg_dump and another ugly kluge in pg_regress (and no doubt ugly kluges elsewhere by the time all the dust settles). IMO, the non-ugly kludges are (1) implement CREATE OR REPLACE LANGUAGE and (2) revert the original patch. Do you want to do one of those (which?) or do you have another 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
Robert Haas robertmh...@gmail.com writes: On Sat, Feb 20, 2010 at 5:53 PM, Tom Lane t...@sss.pgh.pa.us wrote: I don't think that the way to fix this is to have an ugly kluge in pg_dump and another ugly kluge in pg_regress (and no doubt ugly kluges elsewhere by the time all the dust settles). IMO, the non-ugly kludges are (1) implement CREATE OR REPLACE LANGUAGE and (2) revert the original patch. Do you want to do one of those (which?) or do you have another idea? Well, I'm willing to implement CREATE OR REPLACE LANGUAGE if people are agreed that that's a reasonable fix. I'm slightly worried about the restore-could-change-ownership issue, but I think that's much less likely to cause problems than embedding special cases for plpgsql in a pile of places that we'll never find again. 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
Robert Haas wrote: On Sat, Feb 20, 2010 at 5:53 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Sat, Feb 20, 2010 at 2:51 PM, Bruce Momjian br...@momjian.us wrote: Well, I was asking why you labeled it must fix rather than should fix. ?I am fine with the pg_regress.c change. Yeah, if it makes life easier for other people, I say we go for it. I don't think that the way to fix this is to have an ugly kluge in pg_dump and another ugly kluge in pg_regress (and no doubt ugly kluges elsewhere by the time all the dust settles). IMO, the non-ugly kludges are (1) implement CREATE OR REPLACE LANGUAGE and (2) revert the original patch. Do you want to do one of those (which?) or do you have another idea? For #2, if you mean the pg_dump.c plpgsql hack for pg_migrator, that is not an option unless you want to break pg_migrator for 9.0. If you implement #1, why would you have pg_dump issue CREATE OR REPLACE LANGUAGE? We don't do the OR REPLACE part for any other object I can think of, so why would pg_dump do it for languages by default? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
Robert Haas wrote: IMO, the non-ugly kludges are (1) implement CREATE OR REPLACE LANGUAGE and (2) revert the original patch. Do you want to do one of those (which?) or do you have another idea? I thought we seemed to be converging on some agreement on CREATE OR REPLACE LANGUAGE. If not, let me add my vote for that. I also think we need to state explicitly that module authors can not expect build files to be version ignorant and always work. Even if we do something that handles this particular issue, that is likely to be a happy coincidence rather than something that can be expected all the time. People need to expect to have to do version-dependent things. 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Sat, Feb 20, 2010 at 5:53 PM, Tom Lane t...@sss.pgh.pa.us wrote: I don't think that the way to fix this is to have an ugly kluge in pg_dump and another ugly kluge in pg_regress (and no doubt ugly kluges elsewhere by the time all the dust settles). IMO, the non-ugly kludges are (1) implement CREATE OR REPLACE LANGUAGE and (2) revert the original patch. Do you want to do one of those (which?) or do you have another idea? Well, I'm willing to implement CREATE OR REPLACE LANGUAGE if people are agreed that that's a reasonable fix. I'm slightly worried about the restore-could-change-ownership issue, but I think that's much less likely to cause problems than embedding special cases for plpgsql in a pile of places that we'll never find again. All binary upgrade code is clearly marked as binary_upgrade (in fact you complained about my marking them more clearly in tqual.c), so I don't think we are going to lose it. I have answered the other questions by replying to Robert Haas. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- 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] scheduler in core
On Sat, Feb 20, 2010 at 10:03 PM, Dimitri Fontaine dfonta...@hi-media.com wrote: What would it take to have it included in core, so that it's not a separate install to do? I'd love to have some support for running my maintenance pl functions directly from the database. I mean without installing, running and monitoring another (set of) process. It'll always be another (set of) processes even if it's in core. All it means to be in core is that it will be harder to make modifications and you'll be tied to the Postgres release cycle. Main advantage over cron or another scheduler being that it'd be part of my transactional backups, of course. All you need for that is to store the schedule in a database table. This has nothing to do with where the scheduler code lives. -- greg -- 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] scheduler in core
Dimitri Fontaine dfonta...@hi-media.com writes: Dave Page dp...@pgadmin.org writes: Why not just use pgAgent? It's far more flexible than the design you've suggested, and already exists. What would it take to have it included in core, I don't think this really makes sense. There's basically no argument for having it in core other than I'm too lazy to install a separate package. Unlike the case for autovacuum, there isn't anything an in-core implementation could do that an external one doesn't do as well or better. So I'm not eager to take on additional maintenance burden for such a thing. 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
On Feb 20, 2010, at 5:16 PM, Bruce Momjian wrote: Robert Haas wrote: On Sat, Feb 20, 2010 at 5:53 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Sat, Feb 20, 2010 at 2:51 PM, Bruce Momjian br...@momjian.us wrote: Well, I was asking why you labeled it must fix rather than should fix. ?I am fine with the pg_regress.c change. Yeah, if it makes life easier for other people, I say we go for it. I don't think that the way to fix this is to have an ugly kluge in pg_dump and another ugly kluge in pg_regress (and no doubt ugly kluges elsewhere by the time all the dust settles). IMO, the non-ugly kludges are (1) implement CREATE OR REPLACE LANGUAGE and (2) revert the original patch. Do you want to do one of those (which?) or do you have another idea? For #2, if you mean the pg_dump.c plpgsql hack for pg_migrator, that is not an option unless you want to break pg_migrator for 9.0. If you implement #1, why would you have pg_dump issue CREATE OR REPLACE LANGUAGE? We don't do the OR REPLACE part for any other object I can think of, so why would pg_dump do it for languages by default? In what cases would one be able to meaningfully REPLACE a language, other than to not break when encountering an already installed language? i.e., in which cases would this not invalidate functions already written if you were changing from trusted to untrusted status or a different call handler, etc. If there is not a meaningful case for the OR REPLACE, and it is just a syntactic loophole to allow the errorless recreation of an existing language and if the parameters for the CREATE LANGUAGE call indicate identical final state, why aren't we free change change the semantics of CREATE LANGUAGE to just issue a NOTIFY instead of an error in that case, and only complain if there are differences in the call handler, trusted status, etc? I am including a preliminary patch to implement this behavior in the pg_pltemplate case; since we are already using the defaults from that entry and ignoring any explicitly provided ones in the command, this seems to be a safe assumption. Presumably you could do the same in the other case, if you verified that the existing pg_language tuple had the same relevant fields (i.e., notify without error). This would have the benefit of allowing CREATE LANGUAGE langname for those languages with pg_pltemplate entries (specifically plpgsql, but any with the same parameters) and would mean that we could use dumps from pre 9.0 in 9.0 without breaking, appears to fix --single, the pg_regress case, etc. Thoughts on the approach? Regards, David -- David Christensen End Point Corporation da...@endpoint.com skip-create-lang-dupe.patch 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
On Feb 20, 2010, at 15:03, Tom Lane t...@sss.pgh.pa.us wrote: Well, I'm willing to implement CREATE OR REPLACE LANGUAGE if people are agreed that that's a reasonable fix. I'm slightly worried about the restore-could-change-ownership issue, but I think that's much less likely to cause problems than embedding special cases for plpgsql in a pile of places that we'll never find again. Just throwing this out there: would a syntax such as CREATE OF NOT EXISTS, a complement to DROP IF EXISTS, avoid the permissions issue? And if so, would that be a syntax we'd want to accept in general? Could the be a CREATE IF NOT EXISTS TABLE? Best, David -- 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
On Feb 20, 2010, at 15:18, Andrew Dunstan and...@dunslane.net wrote: I also think we need to state explicitly that module authors can not expect build files to be version ignorant and always work. Even if we do something that handles this particular issue, that is likely to be a happy coincidence rather than something that can be expected all the time. People need to expect to have to do version-dependent things. Sure, but where it cab easily be avoided it should be. Most Makefiles work as-is back to 8.0 and earlier AFAICT. Best, David -- 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] scheduler in core
Tom, I believe that in core may be installed by default in case of the pgAgent or similar solution... Many big companies does not allow the developers to configure and install components we need to request everthing in 10 copies of forms... By making it in core or installed by default means that we have more chance that the db scheduler would be widely accepted... And more important... we would not have to check its availability on the setup and provide an alternate scheduler if the database scheduler is off... I believe that a database scheduler would allow me to drop 20 thousand lines of java code in my server... 2010/2/20 Tom Lane t...@sss.pgh.pa.us Dimitri Fontaine dfonta...@hi-media.com writes: Dave Page dp...@pgadmin.org writes: Why not just use pgAgent? It's far more flexible than the design you've suggested, and already exists. What would it take to have it included in core, I don't think this really makes sense. There's basically no argument for having it in core other than I'm too lazy to install a separate package. Unlike the case for autovacuum, there isn't anything an in-core implementation could do that an external one doesn't do as well or better. So I'm not eager to take on additional maintenance burden for such a thing. 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 -- Lucas
Re: [HACKERS] PGXS: REGRESS_OPTS=--load-language=plpgsql
David E. Wheeler da...@kineticode.com writes: Just throwing this out there: would a syntax such as CREATE OF NOT EXISTS, a complement to DROP IF EXISTS, avoid the permissions issue? No, it'd just move it to a different place: now you risk breaking the restored state rather than pre-existing state. And if so, would that be a syntax we'd want to accept in general? Could the be a CREATE IF NOT EXISTS TABLE? *Please* go read some of the linked older discussions before you propose that. I don't want to rehash it yet again. 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] alpha4 bundled -- please verify
All, Problem with the alpha: initdb --version initdb (PostgreSQL) 8.5devel psql --version psql (PostgreSQL) 8.5devel contains support for command-line editing Otherwise, builds great on OSX 10.5. --Josh Berkus -- 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] scheduler in core
On Sat, Feb 20, 2010 at 4:37 PM, Dave Page dp...@pgadmin.org wrote: On Sat, Feb 20, 2010 at 9:33 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: Hi, I'm trying to figure out how difficult is this Why not just use pgAgent? It's far more flexible than the design you've suggested, and already exists. - it's not that easy if you don't have pgadmin - i need to backup postgres database to backup the schedules - the use pgagent here is not very extended but the few a know have tried desisted because they said: not always executed the jobs... i don't have any real evidence of that and probably what happens was that the pgagent daemon wasn't working (error prone), but being it started by the postmaster get rid of that problem... The first one could be rid out with a set of functions in pgagent and clear docs... i can live with the other two at some degree... but getting rid of the third one should be nice :) -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] alpha4 bundled -- please verify
On 2/20/10 3:58 PM, Josh Berkus wrote: All, Problem with the alpha: initdb --version initdb (PostgreSQL) 8.5devel psql --version psql (PostgreSQL) 8.5devel contains support for command-line editing Never mind. My build mistake. Reads 9.0 the way it should. --Josh -- 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] scheduler in core
On Sun, Feb 21, 2010 at 12:03 AM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Sat, Feb 20, 2010 at 4:37 PM, Dave Page dp...@pgadmin.org wrote: On Sat, Feb 20, 2010 at 9:33 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: Hi, I'm trying to figure out how difficult is this Why not just use pgAgent? It's far more flexible than the design you've suggested, and already exists. - it's not that easy if you don't have pgadmin That's easily changed. EDB's Advanced Server emulates Oracles DBMS_JOB interface with it for example. - i need to backup postgres database to backup the schedules Only if you put the control schema in that database. If you don't want to do that, stick it somewhere else. With your proposed scheme, you'd probably have to use pg_dumpall --backup-globals (or whatever it's called) - the use pgagent here is not very extended but the few a know have tried desisted because they said: not always executed the jobs... i don't have any real evidence of that and probably what happens was that the pgagent daemon wasn't working (error prone), but being it started by the postmaster get rid of that problem... Noone has ever reported such a bug that I'm aware of. -- Dave Page EnterpriseDB UK: 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] scheduler in core
On Sat, Feb 20, 2010 at 11:55 PM, Lucas luca...@gmail.com wrote: I believe that a database scheduler would allow me to drop 20 thousand lines of java code in my server... How does that work? If you don't have a scheduler in the database, or pgAgent, why aren't you using cron or Windows task scheduler, neither of which would require 20K lines of Java code. -- Dave Page EnterpriseDB UK: 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
On Feb 20, 2010, at 3:57 PM, Tom Lane wrote: And if so, would that be a syntax we'd want to accept in general? Could the be a CREATE IF NOT EXISTS TABLE? *Please* go read some of the linked older discussions before you propose that. I don't want to rehash it yet again. :-) Was just a thought. David -- 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] scheduler in core
On Sat, Feb 20, 2010 at 7:32 PM, Dave Page dp...@pgadmin.org wrote: On Sun, Feb 21, 2010 at 12:03 AM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Sat, Feb 20, 2010 at 4:37 PM, Dave Page dp...@pgadmin.org wrote: On Sat, Feb 20, 2010 at 9:33 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: Hi, I'm trying to figure out how difficult is this Why not just use pgAgent? It's far more flexible than the design you've suggested, and already exists. - it's not that easy if you don't have pgadmin That's easily changed. EDB's Advanced Server emulates Oracles DBMS_JOB interface with it for example. maybe i can work on that, then -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] scheduler in core
On Sat, Feb 20, 2010 at 10:03 PM, Dimitri Fontaine dfonta...@hi-media.com wrote: Dave Page dp...@pgadmin.org writes: Why not just use pgAgent? It's far more flexible than the design you've suggested, and already exists. What would it take to have it included in core, so that it's not a separate install to do? I'd love to have some support for running my maintenance pl functions directly from the database. I mean without installing, running and monitoring another (set of) process. It's currently written in C++/pl/pgsql and uses wxWidgets, none of which couldn't be changed with a little work. Having it in core will almost certainly result in reduced functionality though - there are use cases in which you may have multiple agents running against one control database, or executing jobs on remote databases for example. We originally wrote the code such that it might be easily included in core in the future, but every time this topic comes up in -hackers, there are a significant number of people who don't think a scheduler should be tied to the core code so we stopped assuming it ever would be. -- Dave Page EnterpriseDB UK: 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] scheduler in core
Lucas wrote: Tom, I believe that in core may be installed by default in case of the pgAgent or similar solution... Many big companies does not allow the developers to configure and install components we need to request everthing in 10 copies of forms... By making it in core or installed by default means that we have more chance that the db scheduler would be widely accepted... This reasoning just doesn't fly in the PostgreSQL world. PostgreSQL is designed to be extensible, not a monolithic product. We're not going to change that because some companies have insane corporate policies. The answer, as Jefferson said in another context, is to inform their ignorance. That isn't to say that there isn't a case for an in core scheduler, but this at least isn't a good reason for it. 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] scheduler in core
On Sun, Feb 21, 2010 at 12:38 AM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Sat, Feb 20, 2010 at 7:32 PM, Dave Page dp...@pgadmin.org wrote: On Sun, Feb 21, 2010 at 12:03 AM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Sat, Feb 20, 2010 at 4:37 PM, Dave Page dp...@pgadmin.org wrote: On Sat, Feb 20, 2010 at 9:33 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: Hi, I'm trying to figure out how difficult is this Why not just use pgAgent? It's far more flexible than the design you've suggested, and already exists. - it's not that easy if you don't have pgadmin That's easily changed. EDB's Advanced Server emulates Oracles DBMS_JOB interface with it for example. maybe i can work on that, then I'd love to add a management API to pgAgent if you'd like to work on it. -- Dave Page EnterpriseDB UK: 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
David Christensen da...@endpoint.com writes: On Feb 20, 2010, at 5:16 PM, Bruce Momjian wrote: If you implement #1, why would you have pg_dump issue CREATE OR REPLACE LANGUAGE? We don't do the OR REPLACE part for any other object I can think of, so why would pg_dump do it for languages by default? In what cases would one be able to meaningfully REPLACE a language, other than to not break when encountering an already installed language? I'm getting the distinct impression that Bruce didn't read yesterday's portion of this thread... The proposal that I had in mind was to have pg_dump use OR REPLACE only when emitting a parameterless CREATE LANGUAGE. This would guarantee that both the desired new language definition and any pre-existing one that it could replace would be exactly like the pg_pltemplate entry for the language. The only risk is that the restore would force the language's ownership and permissions to be what they'd been in the source database, which might possibly not be desirable. Then again it might be desirable; it's really hard to decide that without a specific scenario in mind. 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] scheduler in core
On Sat, 2010-02-20 at 18:19 -0500, Tom Lane wrote: Dimitri Fontaine dfonta...@hi-media.com writes: Dave Page dp...@pgadmin.org writes: Why not just use pgAgent? It's far more flexible than the design you've suggested, and already exists. What would it take to have it included in core, I don't think this really makes sense. There's basically no argument for having it in core other than I'm too lazy to install a separate package. Unlike the case for autovacuum, there isn't anything an in-core implementation could do that an external one doesn't do as well or better. So I'm not eager to take on additional maintenance burden for such a thing. There is zero technical reason for this to be in core. That doesn't mean it isn't a really good idea. It would be nice to have a comprehensive job scheduling solution that allows me to continue abstract away from external solutions and operating system dependencies. Joshua D. Drake regards, tom lane -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir. -- 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
Tom Lane wrote: David Christensen da...@endpoint.com writes: On Feb 20, 2010, at 5:16 PM, Bruce Momjian wrote: If you implement #1, why would you have pg_dump issue CREATE OR REPLACE LANGUAGE? We don't do the OR REPLACE part for any other object I can think of, so why would pg_dump do it for languages by default? In what cases would one be able to meaningfully REPLACE a language, other than to not break when encountering an already installed language? I'm getting the distinct impression that Bruce didn't read yesterday's portion of this thread... The proposal that I had in mind was to have pg_dump use OR REPLACE only when emitting a parameterless CREATE LANGUAGE. This would guarantee that both the desired new language definition and any pre-existing one that it could replace would be exactly like the pg_pltemplate entry for the language. The only risk is that the restore would force the language's ownership and permissions to be what they'd been in the source database, which might possibly not be desirable. Then again it might be desirable; it's really hard to decide that without a specific scenario in mind. Yea, I did read it, but I was just unclear how adding OR REPLACE wasn't just moving the hack to another location, and because there was so much discussion about OR REPLACE, it felt like we were designing the feature, which is something we don't want to be doing now. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- 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] scheduler in core
On Feb 20, 2010, at 8:06 PM, Joshua D. Drake j...@commandprompt.com wrote: There is zero technical reason for this to be in core. That doesn't mean it isn't a really good idea. It would be nice to have a comprehensive job scheduling solution that allows me to continue abstract away from external solutions and operating system dependencies. Well put. That pretty much sums up my feelings on this perfectly. ...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] scheduler in core
Ah! wxWidgets... Yes, i knew there was something i didn't like about pgAgent. So is not as simple as installing it 2010/2/20, Dave Page dp...@pgadmin.org: On Sat, Feb 20, 2010 at 10:03 PM, Dimitri Fontaine dfonta...@hi-media.com wrote: Dave Page dp...@pgadmin.org writes: Why not just use pgAgent? It's far more flexible than the design you've suggested, and already exists. What would it take to have it included in core, so that it's not a separate install to do? I'd love to have some support for running my maintenance pl functions directly from the database. I mean without installing, running and monitoring another (set of) process. It's currently written in C++/pl/pgsql and uses wxWidgets, none of which couldn't be changed with a little work. Having it in core will almost certainly result in reduced functionality though - there are use cases in which you may have multiple agents running against one control database, or executing jobs on remote databases for example. We originally wrote the code such that it might be easily included in core in the future, but every time this topic comes up in -hackers, there are a significant number of people who don't think a scheduler should be tied to the core code so we stopped assuming it ever would be. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Enviado desde mi dispositivo móvil Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: parallelizing subplan execution (was: [HACKERS] explain and PARAM_EXEC)
On Sat, Feb 20, 2010 at 8:31 AM, Dimitri Fontaine dfonta...@hi-media.com wrote: This is really a topic for another thread, but at 100,000 feet it seems to me that the hardest question is - how will you decide which operations to parallelize in the first place? Actually making it happen is really hard, too, of course, but even to get that that point you have to have some model for what types of operations it makes sense to parallelize and how you're going to decide when it's a win. My naive thoughts would be to add some cost parameters. The fact to fork() another backend first, then model for each supported subplan (we will want to add more, or maybe have a special rendez-vous-materialise node) some idea of the data exchange cost. Now the planner would as usual try to find the less costly plan, and will be able to compare plans with and without distributing the work. Overly naive ? Probably. For one thing, you can't use fork(), because it won't work on Windows. It seems to me that you need to start by thinking about what kinds of queries could be usefully parallelized. What I think you're proposing here, modulo large amounts of hand-waving, is that we should basically find a branch of the query tree, cut it off, and make that branch the responsibility of a subprocess. What kinds of things would be sensible to hand off in this way? Well, you'd want to find nodes that are not likely to be repeatedly re-executed with different parameters, like subplans or inner-indexscans, because otherwise you'll get pipeline stalls handing the new parameters back and forth. And you want to find nodes that are expensive for the same reason. So maybe this would work for something like a merge join on top of two sorts - one backend could perform each sort, and then whichever one was the child would stream the tuples to the parent for the final merge. Of course, this assumes the I/O subsystem can keep up, which is not a given - if both tables are fed by the same, single spindle, it might be worse than if you just did the sorts consecutively. This approach might also benefit queries that are very CPU-intensive, on a multi-core system with spare cycles. Suppose you have a big tall stack of hash joins, each with a small inner rel. The child process does about half the joins and then pipelines the results into the parent, which does the other half and returns the results. But there's at least one other totally different way of thinking about this problem, which is that you might want two processes to cooperate in executing the SAME query node - imagine, for example, a big sequential scan with an expensive but highly selective filter condition, or an enormous sort. You have all the same problems of figuring out when it's actually going to help, of course, but the details will likely be quite different. I'm not really sure which one of these would be more useful in practice - or maybe there are even other strategies. What does $COMPETITOR do? I'm also ignoring the difficulties of getting hold of a second backend in the right state - same database, same snapshot, etc. It seems to me unlikely that there are a substantial number of real-world applications for which this will not work very well if we have to actually start a new backend every time we want to parallelize a query. IOW, we're going to need, well, a connection pool in core. *ducks, runs for cover* ...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] PGXS: REGRESS_OPTS=--load-language=plpgsql
On Sat, Feb 20, 2010 at 6:42 PM, David Christensen da...@endpoint.com wrote: In what cases would one be able to meaningfully REPLACE a language, other than to not break when encountering an already installed language? i.e., in which cases would this not invalidate functions already written if you were changing from trusted to untrusted status or a different call handler, etc. At the risk of being smart, who cares and why is this our problem? This question has been asked before, and I can't really figure out what is behind the asking of it. It is as if someone imagines that you would install plperl and then come along later and change the handlers to, say, the plpython handlers, and then complain that your functions were all broken. But that is obviously stupid, and no one would do it (or if they did, we would laugh at them). I think the most likely use of CREATE OR REPLACE FUNCTION is to avoid an error when creating a language that might already exist. But it doesn't seem like the only possible use. Perhaps you've done an in-place upgrade to version 9.0 and you'd like to add an inline handler, for example. If there is not a meaningful case for the OR REPLACE, and it is just a syntactic loophole to allow the errorless recreation of an existing language and if the parameters for the CREATE LANGUAGE call indicate identical final state, why aren't we free change change the semantics of CREATE LANGUAGE to just issue a NOTIFY instead of an error in that case, and only complain if there are differences in the call handler, trusted status, etc? I guess we could do that, but it's inconsistent with the way we handle other object types, so it doesn't seem as clean to me. ...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] PGXS: REGRESS_OPTS=--load-language=plpgsql
Robert Haas robertmh...@gmail.com writes: I think the most likely use of CREATE OR REPLACE [LANGUAGE] is to avoid an error when creating a language that might already exist. But it doesn't seem like the only possible use. Perhaps you've done an in-place upgrade to version 9.0 and you'd like to add an inline handler, for example. Given the existing semantics of CREATE LANGUAGE, nothing at all would happen when replacing a language that has a pg_pltemplate entry, because that overrides all the command's options. However, I think CORL has potential use for developers working on a non-core language implementation: they could use it to add an inline handler, for example, without losing the function definitions they already have loaded. Admittedly that's a pretty thin use-case. However, I intensely dislike the line of thought that says let's take shortcuts because nobody is going to use this except for one specific use-case. There is a very clear set of behaviors that CORL ought to have given the precedents of our other COR commands. If we don't make it do things that way then we are going to surprise users, and we are also going to paint ourselves into a corner because we won't be able to fix it later without creating compatibility gotchas. 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
On Feb 20, 2010, at 10:56 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I think the most likely use of CREATE OR REPLACE [LANGUAGE] is to avoid an error when creating a language that might already exist. But it doesn't seem like the only possible use. Perhaps you've done an in-place upgrade to version 9.0 and you'd like to add an inline handler, for example. Given the existing semantics of CREATE LANGUAGE, nothing at all would happen when replacing a language that has a pg_pltemplate entry, because that overrides all the command's options. However, I think CORL has potential use for developers working on a non-core language implementation: they could use it to add an inline handler, for example, without losing the function definitions they already have loaded. Admittedly that's a pretty thin use-case. However, I intensely dislike the line of thought that says let's take shortcuts because nobody is going to use this except for one specific use-case. There is a very clear set of behaviors that CORL ought to have given the precedents of our other COR commands. If we don't make it do things that way then we are going to surprise users, and we are also going to paint ourselves into a corner because we won't be able to fix it later without creating compatibility gotchas. Exactly. I agree completely. ...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] parallelizing subplan execution
Robert Haas wrote: It seems to me that you need to start by thinking about what kinds of queries could be usefully parallelized. What I think you're proposing here, modulo large amounts of hand-waving, is that we should basically find a branch of the query tree, cut it off, and make that branch the responsibility of a subprocess. What kinds of things would be sensible to hand off in this way? Well, you'd want to find nodes that are not likely to be repeatedly re-executed with different parameters, like subplans or inner-indexscans, because otherwise you'll get pipeline stalls handing the new parameters back and forth. And you want to find nodes that are expensive for the same reason. So maybe this would work for something like a merge join on top of two sorts - one backend could perform each sort, and then whichever one was the child would stream the tuples to the parent for the final merge. Of course, this assumes the I/O subsystem can keep up, which is not a given - if both tables are fed by the same, single spindle, it might be worse than if you just did the sorts consecutively. This approach might also benefit queries that are very CPU-intensive, on a multi-core system with spare cycles. Suppose you have a big tall stack of hash joins, each with a small inner rel. The child process does about half the joins and then pipelines the results into the parent, which does the other half and returns the results. But there's at least one other totally different way of thinking about this problem, which is that you might want two processes to cooperate in executing the SAME query node - imagine, for example, a big sequential scan with an expensive but highly selective filter condition, or an enormous sort. You have all the same problems of figuring out when it's actually going to help, of course, but the details will likely be quite different. I'm not really sure which one of these would be more useful in practice - or maybe there are even other strategies. What does $COMPETITOR do? I'm also ignoring the difficulties of getting hold of a second backend in the right state - same database, same snapshot, etc. It seems to me unlikely that there are a substantial number of real-world applications for which this will not work very well if we have to actually start a new backend every time we want to parallelize a query. IOW, we're going to need, well, a connection pool in core. *ducks, runs for cover* One thing that might work quite well is slicing up by partition (properly implemented partitioning would go along with this nicely too...) regards Mark -- 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] scheduler in core
2010/2/21 Andrew Dunstan and...@dunslane.net: Lucas wrote: Tom, I believe that in core may be installed by default in case of the pgAgent or similar solution... Many big companies does not allow the developers to configure and install components we need to request everthing in 10 copies of forms... By making it in core or installed by default means that we have more chance that the db scheduler would be widely accepted... This reasoning just doesn't fly in the PostgreSQL world. PostgreSQL is designed to be extensible, not a monolithic product. We're not going to change that because some companies have insane corporate policies. The answer, as Jefferson said in another context, is to inform their ignorance. That isn't to say that there isn't a case for an in core scheduler, but this at least isn't a good reason for it. What I remember - this is exactly same discus like was about replication thre years ago fiirst strategy - we doesn't need it in core next we was last with replacation Regards Pavel Stehule 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
