Re: [HACKERS] Closing out CommitFest 2009-11
Thanks Greg - nice job! :-) On Sat, Dec 19, 2009 at 7:30 AM, Greg Smith g...@2ndquadrant.com wrote: CommitFest 2009-11 is now closed, having committed 27 patches in 33 days. For comparison sake, 2009-09 committed 20 patches in 29 days, 2009-07 37 patches in 34 days, and 2008-09 29 patches in 30 days. The much bigger 2008-11 involved 58 patches going on for months, the bulk of it committed 28 patches in 36 days. Seems pretty consistent at this point: at the average patch contribution size seen over the last year, about one of those gets committed per day once we enter a CommitFest. I didn't bother accounting for things that were committed outside of the official dates, so it's actually a bit worse than that, but that gives a rough idea that's easy to remember. Also, just based on the last three CFs, 42% of patches are either returned with feedback or rejected (with quite a bit more CF to CF variation). The working estimation figure I'd suggest is that once a CF reaches 50 incoming patches it's unlikely that will finish in a month. CommitFest 2010-01, the last one for 8.5, begins on January 15th, 2010. I'll be out of commission with projects by then, so unless Robert wants to reprise his role as CF manager we may need to get someone else involved to do it. Between the CF application and how proactive everyone involved is at this point (almost all authors, reviewers, and committers do the bulk of the state changes and link to messages in the archives for you), the job of running things does keep getting easier. And the guidlines for how to be the CF manager are pretty nailed down now--you could just execute on a pretty mechanical plan and expect to make useful progress. It's still a lot of time though. I've never had an appreciation for exactly how many messages flow through this list like I do now, after a month of needing to read and pay attention to every single one of them. For those of you still furiously working on a patch with that deadline, if you have a large patch and it's not already been reviewed in a previous CommitFest, I wouldn't give you good odds of it being even looked at during that one. There doesn't seem to be any official warning of this where people will likely notice it, but this topic has been discussed on the list here. Large patches submitted just before the deadline for a release have not fared very well historically. Recognizing that, there's really no tolerance for chasing after them (at the expense of postponing the beta) left for this release. Just figured I'd pass along that warning before somebody discovers it the hard way, by working madly to finish their submission up only to see it get kicked to the next version anyway. -- Greg Smith 2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] Closing out CommitFest 2009-11
On Dec 19, 2009, at 4:07 AM, Dave Page dp...@pgadmin.org wrote: Thanks Greg - nice job! :-) +1! ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] alpha3 release schedule?
Do people want more time to play with hot standby? Otherwise alpha3 should go out on Monday or Tuesday. -- 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] Distinguish view and table problem
W dniu 19 grudnia 2009 03:20 użytkownik suzhiyang suzhiy...@gmail.comnapisał: Sorry, I've not describe my problem precisely. I mean that I want to get relkind or something from a systable by the programm but not by sql. I don't understand how you can get data from table without using SQL. (maybe I'm just too sql) That is, if I execute such sql by exec_simple_query(select xxx from pg_xxx), how could I get the result of it in the programm? Are you programming in C? If so, use the API provided by PostgreSQL, http://www.postgresql.org/docs/current/static/libpq.html PS. suzhiyang, please use Reply All when talking on this list.
Re: [HACKERS] Re: [COMMITTERS] pgsql: Allow read only connections during recovery, known as Hot
Le 19 déc. 2009 à 03:01, Robert Haas a écrit : On Fri, Dec 18, 2009 at 8:32 PM, Simon Riggs sri...@postgresql.org wrote: Log Message: --- Allow read only connections during recovery, known as Hot Standby. Congratulations! And, may I be the first to say - woo hoo! +1! -- 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] alpha3 release schedule?
Do people want more time to play with hot standby? Otherwise alpha3 should go out on Monday or Tuesday. Well, I want to know whether the problem I refered to in http://archives.postgresql.org/pgsql-hackers/2009-12/msg01641.php is must-fix or not. This problem is a corollary of the deadlock problem. This is less catstrophic but more likely to happen. If you leave this problem, for example, any long-running transactions, holding any cursors in whatever tables, have a possibility of freezing whole recovery work in HotStandby node until the transaction commit. regards, -- Hiroyuki YAMADA Kokolink Corporation yam...@kokolink.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Allow read only connections during recovery, known as Hot
On Sat, Dec 19, 2009 at 8:44 AM, Dimitri Fontaine dfonta...@hi-media.com wrote: Le 19 déc. 2009 à 03:01, Robert Haas a écrit : On Fri, Dec 18, 2009 at 8:32 PM, Simon Riggs sri...@postgresql.org wrote: Log Message: --- Allow read only connections during recovery, known as Hot Standby. Congratulations! And, may I be the first to say - woo hoo! +1! +1 i will start to play with HS in windows -- 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] Distinguish view and table problem
2009/12/19 Filip Rembiałkowski plk.zu...@gmail.com: W dniu 19 grudnia 2009 03:20 użytkownik suzhiyang suzhiy...@gmail.com napisał: Sorry, I've not describe my problem precisely. I mean that I want to get relkind or something from a systable by the programm but not by sql. I don't understand how you can get data from table without using SQL. (maybe I'm just too sql) i think he is hacking postgres's source code to make the TODO: allow recompilation of views (he send an email about that in another thread)... i think this is somewhat necesary to read: http://wiki.postgresql.org/wiki/Developer_FAQ#Technical_Questions and of course look at other files that acces that kind of info, for example look at AlterTableNamespace() funtion in src/backend/commands/tablecmds.c to find out for a complete example to identify tables and views -- 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] COPY IN as SELECT target
Tom Lane wrote: I think that there are two likely possibilities for the result format: * Raw data after just the de-escaping and column separation steps. Array of text is probably the right thing here, at least for a text COPY (doesn't seem to cover the binary case though). * The data converted to some specified row type. RETURNING type-expression is probably not good since it looks more like the second case than the first --- and in fact it could be outright ambiguous, what if your data actually is one column that is a text array? If we're willing to assume these are the *only* possibilities then we could use COPY FROM ... for the first and COPY RETURNING type-list FROM ... for the second. I'm a bit uncomfortable with that assumption though; it seems likely that we'll want to shoehorn in some more alternatives later. (Like, what about the binary case?) Yeah. I think we need an explicit marker. The first of these cases is the one I'm particularly interested in. I think you could actually get the second from the first with a little more work anyway, but the raw input as an array lets me get the things I can't easily get another way. I think we're going to need some marker such as parentheses to distinguish the second case. In that case, RETURNING text[] could be the first case and RETURNING (text[]) could be the second, but maybe that's a bit too subtle. How about RETURNING TYPE (type_list) for the second case? 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] alpha3 release schedule?
On Sat, Dec 19, 2009 at 7:20 AM, Peter Eisentraut pete...@gmx.net wrote: Do people want more time to play with hot standby? Otherwise alpha3 should go out on Monday or Tuesday. I think we should try to wrap it promptly. It's true that Hot Standby almost certainly has bugs and/or annoying limitations, as one would expect with a feature of this magnitude, but I think we'll get a better idea what they are and which ones are the most important by getting something out there for people to test. AIUI, the reason why Simon has been busting ass to get this committed is precisely so that it could go into alpha3 and get more testing, and speaking in my capacity as a guy who is anal about the schedule, I couldn't be happier about that! Postponing alpha3 would seem to defeat the purpose of all that hard work. ...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] no lo_import(text, oid) document
Tatsuo Ishii is...@postgresql.org writes: BTW, why don't lo_creat, lo_create, lo_unlink, lo_import and lo_export server side function's document appear in the Chapter 9. Functions and Operators section? Because large objects have their very own chapter. Shall I add them? No, I don't think we should document them all twice. 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] alpha3 release schedule?
Hiroyuki Yamada yam...@kokolink.net writes: Well, I want to know whether the problem I refered to in http://archives.postgresql.org/pgsql-hackers/2009-12/msg01641.php is must-fix or not. This problem is a corollary of the deadlock problem. This is less catstrophic but more likely to happen. If you leave this problem, for example, any long-running transactions, holding any cursors in whatever tables, have a possibility of freezing whole recovery work in HotStandby node until the transaction commit. Seems like something we should fix ASAP, but I do not see why it need hold up an alpha release. Alpha releases are expected to have bugs, and this one doesn't look like it would stop people from finding other bugs. 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] alpha3 release schedule?
Tom Lane wrote: Hiroyuki Yamada yam...@kokolink.net writes: Well, I want to know whether the problem I refered to in http://archives.postgresql.org/pgsql-hackers/2009-12/msg01641.php is must-fix or not. This problem is a corollary of the deadlock problem. This is less catstrophic but more likely to happen. If you leave this problem, for example, any long-running transactions, holding any cursors in whatever tables, have a possibility of freezing whole recovery work in HotStandby node until the transaction commit. Seems like something we should fix ASAP, but I do not see why it need hold up an alpha release. Alpha releases are expected to have bugs, and this one doesn't look like it would stop people from finding other bugs. yeah afaik alpha tarballs are a forma of a checkpoint at the end of a commitfest to get people a reasonable testing target. Every feature (not only HS) deserves getting serious testing so I vote for getting alpha3 out as soon as possible. Stefan -- 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] Backup history file should be replicated in Streaming Replication?
Robert Haas wrote: I think (as I did/do with Hot Standby) that the most important thing here is to get to a point where we have a reasonably good feature that is of some use, and commit it. It will probably have some annoying limitations; we can remove those later. I have a feel that what we have right now is going to be non-robust in the face of network breaks, but that is a problem that can be fixed by a future patch. Improving robustness in all the situations where you'd like things to be better for replication is a never ending job. As I understand it, a major issue with this patch right now is how it links to the client libpq. That's the sort of problem that can make this uncomittable. As long as the fundamentals are good, it's important not to get lost in optimizing the end UI here if it's at the expense of getting something you can deploy at all in the process. If Streaming Replication ships with a working core but a horribly complicated setup/failover mechanism, that's infinitely better than not shipping at all because resources were diverted toward making things more robust or easier to setup instead. Also, the pool of authors who can work on tweaking the smaller details here is larger than those capable of working on the fundamental streaming replication code. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] alpha3 release schedule?
On Sat, 2009-12-19 at 18:12 +0100, Stefan Kaltenbrunner wrote: Seems like something we should fix ASAP, but I do not see why it need hold up an alpha release. Alpha releases are expected to have bugs, and this one doesn't look like it would stop people from finding other bugs. yeah afaik alpha tarballs are a forma of a checkpoint at the end of a commitfest to get people a reasonable testing target. Every feature (not only HS) deserves getting serious testing so I vote for getting alpha3 out as soon as possible. +1 for both. -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [HACKERS] alpha3 release schedule?
Hiroyuki Yamada yam...@kokolink.net writes: Well, I want to know whether the problem I refered to in http://archives.postgresql.org/pgsql-hackers/2009-12/msg01641.php is must-fix or not. This problem is a corollary of the deadlock problem. This is less catstrophic but more likely to happen. If you leave this problem, for example, any long-running transactions, holding any cursors in whatever tables, have a possibility of freezing whole recovery work in HotStandby node until the transaction commit. Seems like something we should fix ASAP, but I do not see why it need hold up an alpha release. Alpha releases are expected to have bugs, and this one doesn't look like it would stop people from finding other bugs. At the beginning of this commit fest, Heikki said in http://archives.postgresql.org/pgsql-hackers/2009-11/msg00914.php Of course there should be several phases! We've *already* punted a lot of stuff from this first increment we're currently working on. The criteria for getting this first phase committed is: could we release with no further changes? And other patches seem to be checked with similar criteria, as long as I read mails in this list. So I wanted to know whether the problem is must-fix, and if it is, why the criteria has been changed during the commit fest. Anyway, thanks for answering my question. regards, -- Hiroyuki YAMADA Kokolink Corporation yam...@kokolink.net -- 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] LATERAL
2009/10/20 Andrew Gierth and...@tao11.riddles.org.uk: Right now, the only way pg can plan this is to do a hashjoin or mergejoin of the _entire content of big1 and big2_ and join the result against small (again in a hashjoin or mergejoin plan). This becomes excessively slow compared to the ideal plan: nested loop seqscan on small nested loop indexscan on big1 where id=small.id indexscan on big2 where id=small.id (or big1.id which is equiv) (The same argument applies if small is not actually small but has restriction clauses) I have a similar issue on my mind, but is this the same as the topic? SELECT ... FROM small INNER JOIN (SELECT ... FROM large GROUP BY large.id) agged ON small.id = agged.id WHERE small.id IN (bla bla bla) The ideal plan is SeqScan on small with filtering sub query aggregate on large by small.id but the actual plan is full aggregate on large since the planner doesn't push down outer qual to aggregate node. The output will discard almost all of agged's output. Regards, -- Hitoshi Harada -- 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] LATERAL
On Sat, Dec 19, 2009 at 12:49 PM, Hitoshi Harada umi.tan...@gmail.com wrote: 2009/10/20 Andrew Gierth and...@tao11.riddles.org.uk: Right now, the only way pg can plan this is to do a hashjoin or mergejoin of the _entire content of big1 and big2_ and join the result against small (again in a hashjoin or mergejoin plan). This becomes excessively slow compared to the ideal plan: nested loop seqscan on small nested loop indexscan on big1 where id=small.id indexscan on big2 where id=small.id (or big1.id which is equiv) (The same argument applies if small is not actually small but has restriction clauses) I have a similar issue on my mind, but is this the same as the topic? SELECT ... FROM small INNER JOIN (SELECT ... FROM large GROUP BY large.id) agged ON small.id = agged.id WHERE small.id IN (bla bla bla) The ideal plan is SeqScan on small with filtering sub query aggregate on large by small.id but the actual plan is full aggregate on large since the planner doesn't push down outer qual to aggregate node. The output will discard almost all of agged's output. I just tried this and it works for me. create table foo (id serial, name varchar, primary key (id)); create table bar (id serial, foo_id integer references foo (id), name varchar, primary key (id)); insert into foo (name) select random()::varchar from generate_series(1,1000); insert into bar (foo_id, name) select (g%10)+1, random()::varchar from generate_series(1,1) g; explain select * from foo inner join (select foo_id, sum(1) from bar group by 1) x on foo.id = x.foo_id where x.foo_id = 1; ...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] LATERAL
2009/12/20 Robert Haas robertmh...@gmail.com: On Sat, Dec 19, 2009 at 12:49 PM, Hitoshi Harada umi.tan...@gmail.com wrote: 2009/10/20 Andrew Gierth and...@tao11.riddles.org.uk: Right now, the only way pg can plan this is to do a hashjoin or mergejoin of the _entire content of big1 and big2_ and join the result against small (again in a hashjoin or mergejoin plan). This becomes excessively slow compared to the ideal plan: nested loop seqscan on small nested loop indexscan on big1 where id=small.id indexscan on big2 where id=small.id (or big1.id which is equiv) (The same argument applies if small is not actually small but has restriction clauses) I have a similar issue on my mind, but is this the same as the topic? SELECT ... FROM small INNER JOIN (SELECT ... FROM large GROUP BY large.id) agged ON small.id = agged.id WHERE small.id IN (bla bla bla) The ideal plan is SeqScan on small with filtering sub query aggregate on large by small.id but the actual plan is full aggregate on large since the planner doesn't push down outer qual to aggregate node. The output will discard almost all of agged's output. I just tried this and it works for me. create table foo (id serial, name varchar, primary key (id)); create table bar (id serial, foo_id integer references foo (id), name varchar, primary key (id)); insert into foo (name) select random()::varchar from generate_series(1,1000); insert into bar (foo_id, name) select (g%10)+1, random()::varchar from generate_series(1,1) g; explain select * from foo inner join (select foo_id, sum(1) from bar group by 1) x on foo.id = x.foo_id where x.foo_id = 1; ...Robert Ah your example works for me, too. My issue is: explain select * from foo inner join (select foo_id, sum(1) from bar group by 1) x on foo.id = x.foo_id where foo.id = 1; where foo.id = 1 (not where x.foo_id = 1). And I now figured out it's another problem. Regards, -- Hitoshi Harada -- 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] LATERAL
On Thu, Dec 17, 2009 at 10:13 PM, Robert Haas robertmh...@gmail.com wrote:
Another question I have - while generalizing the inner-indexscan
machinery is an interesting join optimization technique, I'm thinking
that it actually has very little to do with LATERAL. Is there any
reason to suppose that one or the other needs to be done first?
And the winner is... yes. Or at least, I think so. One of the major
reasons why people want LATERAL() is for SRFs, but currently, even if
you beat the code into allowing a SRF with an outer reference, the
planner can easily be persuaded to run the SRF on the outer side of a
join with the dependency as the inner side, which ain't gonna work.
(Even you jigger the query so that the planner gets them on the
correct sides of the join, the executor fails, but that's a different
problem.)
The idea Tom came up with back in October is to allow paths to be
tagged with a set of rels to which they must in the future be joined
in order for the path to be allowable. The point of that exercise was
to generalize the current inner-indexscan machinery so that we can
create that type of plan in match_unsorted_outer() even when the inner
side is a joinrel. But, it strikes me that what we need to allow a
function scan with an outer reference is remarkably similar - the
function scan can only be used as the inner side of a nestloop with a
certain set of rels on the outer side.
On the other hand, it's not exactly the same, either. In the case of
a construct like A LJ (B IJ C), partial-index scan paths for B and C
will require a subsequent nest-join to A to become fully valid, but
there will also be other paths that don't. But for something like A,
LATERAL (some_srf(A.x)), the ONLY path for the rel defined by
some_srf(A.x) has a future-join requirement of {A}. It's not clear to
me whether there's anything useful that can be done with this
knowledge.
Incidentally, the reason why the executor chokes trying to execute a
SRF with an outer reference is because ExecEvalVar() craps out trying
to dereference a null TupleTableSlot. If I'm understanding this
correctly, that, in turn, happens because the variable that we're
trying to deference is marked as neither INNER nor OUTER, so it's
assumed to be from a scan, but there's no scan node. Going even
further from my area of actually understanding what's going on, I
think this needs to be fixed by adjusting setrefs.c. Allowing
LATERAL(), or for that matter the generalized inner-index scan stuff,
will I think mean that set_inner_join_references() will need to handle
a lot more cases than it current does. I don't understand this code
well enough to begin to speculate as to what should happen here.
...Robert
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: About Allow VIEW/RULE recompilation when the underlying tables change
I was dealing with a customer recently who very much wanted this behavior, during discussions with them I wrote up a little something describing how different database vendors treat views and alter statements. ... Part of the issue here is that the SQL Standard does a very poor job of expressing what correct behavior is of VIEWS when the underlying table is altered, as a result nearly every major database vendor has different behavior. the customer would be having similar (but slightly different) problems if they moved from almost any database to almost any other database. Oracle: Treats all views as the text used to define them and allows for the possibility of invalid views Terradata: Expands and fully qualifies the text used to define the views, but still treats them as text, and allows for the possibility of invalid views. DB2: Treats views as logical and does not allow for the possibility of invalid views. It tries to allow ALTER statements but only under limited circumstances. Postgres: Treats views as logical and does not allow for the possibility of invalid views. It tries to allow ALTER statements but only under limited circumstances (not the same circumstances as DB2). Microsoft: Supports two different kinds of views. These different approaches allow for different sorts of DDL operations to succeed and can leave views in different levels of usability. ALTER TABLE example RENAME TO example_old; - In oracle and Terradata views over example are now invalid. - In Postgres and DB2 views over example continue to work even though the table has a different name. DROP TABLE example; - In oracle and Terradata views over example are now invalid. - In Postgres and DB2 the DROP fails unless CASCADE is specified. ALTER TABLE example SET SCHEMA new_schema; - In Oracle the views become invalid unless the new schema is in the search path - In Terradata the views become invalid - In Postgres and DB2 the views still refer to the original table. ALTER TABLE example ADD COLUMN new int; - In Oracle views may return the new column - In Terradata, Postgres, and DB2 the new column does not show up in existing views. ALTER TABLE example DROP COLUMN old; - In Oracle views may return fewer columns and/or become invalid - In Terradata views that reference the stated column will become invalid (even when the view was simply SELECT *). - In Postgres and DB2 the ALTER statement will fail if the view references the specified column. ALTER TABLE example RENAME COLUMN old TO new; - In Oracle views will return different columns and/or become invalid, dependent views may become invalid. - In Terradata views referencing the stated column become invalid. - In Postgres, DB2 existing views will automatically update with the change. ALTER TABLE example ALTER COLUMN old TYPE text; - In Oracle and Terradata views may update automatically, or may become invalid. - In DB2 views will try to rewrite themselves and may or may not fail depending on contents - In Postgres the ALTER statement will fail if the view references the specified column. Note that in the above NO database will always be able to keep views in sync with alterations to the underlying tables, this is because there is not a single well defined answer to how that update should occur. For every single database vendor certain types of update operations will require manual user intervention to go through the entire dependent view tree and manually fix the views under some circumstances. The question is only /which/ circumstances. I maintain that the approaches that inform the user that they have met that condition via ALTER statement failures (Postgres/DB2/Microsoft Bound Views) have certain advantages over databases that allow the update but may silently leave views in an usable state (Oracle, Terradata), in that at least the user Knows when they have to re-examine their views. There might be some slight inaccuracies above since I was going off documentation and extrapolation of the described behavior, but the general points still hold. ... As far as I can tell there are three approaches that could be taken to help address this problem: 1) DB2 like approach - try to perform rewrites where able, but if the rewrite fails then the alter operation fails. Would allow simple edits such as ALTER TYPE that are only changes in typmod, or if done more ambitiously would allow numbers to be changed to other numbers. But as Robert says this quickly approaches the territory of black magic. 2) Microsoft like approach - create a new kind of view that is just stored as the view text and can become invalid. The people who want this type of view can use it combined with all the headaches associated with this type of view. 3) We extend things in a way that just makes dropping and recreating views more convenient. E.G. Some syntax for drop all dependents would be helpful to make schema
Re: [HACKERS] alpha3 release schedule?
Hiroyuki Yamada wrote: Hiroyuki Yamada yam...@kokolink.net writes: Well, I want to know whether the problem I refered to in http://archives.postgresql.org/pgsql-hackers/2009-12/msg01641.php is must-fix or not. This problem is a corollary of the deadlock problem. This is less catstrophic but more likely to happen. If you leave this problem, for example, any long-running transactions, holding any cursors in whatever tables, have a possibility of freezing whole recovery work in HotStandby node until the transaction commit. Seems like something we should fix ASAP, but I do not see why it need hold up an alpha release. Alpha releases are expected to have bugs, and this one doesn't look like it would stop people from finding other bugs. At the beginning of this commit fest, Heikki said in http://archives.postgresql.org/pgsql-hackers/2009-11/msg00914.php Of course there should be several phases! We've *already* punted a lot of stuff from this first increment we're currently working on. The criteria for getting this first phase committed is: could we release with no further changes? And other patches seem to be checked with similar criteria, as long as I read mails in this list. So I wanted to know whether the problem is must-fix, and if it is, why the criteria has been changed during the commit fest. Well, that was the criteria I used to decide whether to commit or not. Not everyone agreed to begin with, and the reason I used that criteria was a selfish one: I didn't want to be forced to fix loose ends after the commitfest myself. The big reason for that was that I didn't know how much time I would have for that. I have no complaints about Simon's commit. Knowing that I'm not on the hook to close the loose ends, I'm very happy that it's finally in. (That doesn't mean that I'll stop paying attention to this patch; I will do as much as I have time to.) Regarding the bugs you found, I put them on the TODO list at https://wiki.postgresql.org/wiki/Hot_Standby_TODO, under the must-fix category. I think they need to be fixed before final release, but there's no need to delay the alpha release for them. -- 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] Aggregate ORDER BY patch
On 2009-12-15 23:10 +0200, Tom Lane wrote: Andrew Gierthand...@tao11.riddles.org.uk writes: Notice that there are cases where agg(distinct x order by x) is nondeterministic while agg(distinct x order by x,y) is deterministic. Well, I think what you're really describing is a case where you're using the wrong sort opclass. If the aggregate can distinguish two values of x, and the sort operator can't, use another sort operator that can. If we really wanted to take the above seriously, my opinion is that we ought to introduce DISTINCT ON in aggregates. However, at that point you lose the argument of standard syntax, so it's not real clear why you shouldn't just fall back on select agg(x) from (select distinct on (x) x ... order by x,y) FWIW, in my opinion the idea behind this patch is to not fall back on hacks like that. This patch already goes beyond the standard and having this seems like a useful feature in some cases. Although the DISTINCT ON syntax would have a bit more resemblance on the existing syntax, I'd still like to see agg(distinct x order by x,y). Just my $0.02. Regards, Marko Tiikkaja -- 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] alpha3 release schedule?
Well, that was the criteria I used to decide whether to commit or not. Not everyone agreed to begin with, and the reason I used that criteria was a selfish one: I didn't want to be forced to fix loose ends after the commitfest myself. The big reason for that was that I didn't know how much time I would have for that. I have no complaints about Simon's commit. Knowing that I'm not on the hook to close the loose ends, I'm very happy that it's finally in. (That doesn't mean that I'll stop paying attention to this patch; I will do as much as I have time to.) Regarding the bugs you found, I put them on the TODO list at https://wiki.postgresql.org/wiki/Hot_Standby_TODO, under the must-fix category. I think they need to be fixed before final release, but there's no need to delay the alpha release for them. I never think it's selfish. But I see. Thanks for your kind reply. regards, -- Hiroyuki YAMADA Kokolink Corporation yam...@kokolink.net -- 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] Aggregate ORDER BY patch
Marko Tiikkaja marko.tiikk...@cs.helsinki.fi writes: On 2009-12-15 23:10 +0200, Tom Lane wrote: If we really wanted to take the above seriously, my opinion is that we ought to introduce DISTINCT ON in aggregates. FWIW, in my opinion the idea behind this patch is to not fall back on hacks like that. This patch already goes beyond the standard and having this seems like a useful feature in some cases. Although the DISTINCT ON syntax would have a bit more resemblance on the existing syntax, I'd still like to see agg(distinct x order by x,y). I remain entirely unconvinced. If DISTINCT + ORDER BY work differently inside aggregates than at query level, we're going to forever be explaining the difference, fielding bug reports, etc. Even documenting the difference would be a serious PITA considering how subtle it is (AFAICS Andrew's submitted doc patch failed to address the point). I'm not against the idea of introducing DISTINCT ON here, though I think perhaps we ought to wait for a release or so and see if there's really any field demand for it. 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] LATERAL
Robert Haas robertmh...@gmail.com writes: Incidentally, the reason why the executor chokes trying to execute a SRF with an outer reference is because ExecEvalVar() craps out trying to dereference a null TupleTableSlot. If I'm understanding this correctly, that, in turn, happens because the variable that we're trying to deference is marked as neither INNER nor OUTER, so it's assumed to be from a scan, but there's no scan node. Going even further from my area of actually understanding what's going on, I think this needs to be fixed by adjusting setrefs.c. Well, no: we can't handle such references as OUTER vars because the OUTER slot is likely to be in use already in the sub-join. It would be even messier if you wanted several references to different outer relations. I believe the correct approach is probably to treat values that need to be propagated into the inner side as executor parameters. This could replace the existing, rather crocky, management of values passed into a nestloop inner indexscan. The mechanisms that deal with forcing rescans of subplans affected by a changed parameter value would be very helpful here 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
[HACKERS] creating index names automatically?
Could we create an option to create index names automatically, so you'd only have to write CREATE INDEX ON foo (a); which would pick a name like foo_a_idx. We already do this in a number of places such as constraint names and sequences without much trouble. In most cases you don't really need to give an index a smart name since the purpose is obvious. Comments? -- 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] creating index names automatically?
2009/12/20 Peter Eisentraut pete...@gmx.net: Could we create an option to create index names automatically, so you'd only have to write CREATE INDEX ON foo (a); Yes, please. Cheers, BJ -- 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] Time to run initdb is mostly figure-out-the-timezone work
On Fri, Dec 18, 2009 at 10:57, Tom Lane t...@sss.pgh.pa.us wrote: Obviously there's something there for the kernel guys to fix, but even with a non-borked kernel it's an expensive thing to do. Any thoughts on back patching this? While its not a bug per-say, it seems reasonably low-risk. I for one would love a 2-4x initdb speedup in the back branches :) Granted now I know I can just set TZ... -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] dump order of sequence options
A very minor point, but I found when reading dumps it makes more sense that in the CREATE SEQUENCE command MINVALUE comes before MAXVALUE. Objections to this patch? diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 9748379..8776e27 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -11254,16 +11254,16 @@ dumpSequence(Archive *fout, TableInfo *tbinfo) appendPQExpBuffer(query, INCREMENT BY %s\n, incby); - if (maxv) - appendPQExpBuffer(query, MAXVALUE %s\n, maxv); - else - appendPQExpBuffer(query, NO MAXVALUE\n); - if (minv) appendPQExpBuffer(query, MINVALUE %s\n, minv); else appendPQExpBuffer(query, NO MINVALUE\n); + if (maxv) + appendPQExpBuffer(query, MAXVALUE %s\n, maxv); + else + appendPQExpBuffer(query, NO MAXVALUE\n); + appendPQExpBuffer(query, CACHE %s%s, cache, (cycled ? \nCYCLE : )); -- 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] creating index names automatically?
Peter Eisentraut pete...@gmx.net writes: Could we create an option to create index names automatically, so you'd only have to write CREATE INDEX ON foo (a); which would pick a name like foo_a_idx. We already do this in a number of places such as constraint names and sequences without much trouble. In most cases you don't really need to give an index a smart name since the purpose is obvious. In the cases where that's sensible, you can use constraint syntax, no? I really doubt that it's that easy to pick a sensible name for an index on an expression, for example. 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] creating index names automatically?
In response to Peter Eisentraut : Could we create an option to create index names automatically, so you'd only have to write CREATE INDEX ON foo (a); which would pick a name like foo_a_idx. We already do this in a number of places such as constraint names and sequences without much trouble. In most cases you don't really need to give an index a smart name since the purpose is obvious. Comments? +1, as an additional option, only if no index-name specified. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] LATERAL
On Sat, Dec 19, 2009 at 3:01 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Incidentally, the reason why the executor chokes trying to execute a SRF with an outer reference is because ExecEvalVar() craps out trying to dereference a null TupleTableSlot. If I'm understanding this correctly, that, in turn, happens because the variable that we're trying to deference is marked as neither INNER nor OUTER, so it's assumed to be from a scan, but there's no scan node. Going even further from my area of actually understanding what's going on, I think this needs to be fixed by adjusting setrefs.c. Well, no: we can't handle such references as OUTER vars because the OUTER slot is likely to be in use already in the sub-join. It would be even messier if you wanted several references to different outer relations. Oh. Yeah. I believe the correct approach is probably to treat values that need to be propagated into the inner side as executor parameters. This could replace the existing, rather crocky, management of values passed into a nestloop inner indexscan. The mechanisms that deal with forcing rescans of subplans affected by a changed parameter value would be very helpful here too. What is the best place to look for the existing, rather crocky code? I have to admit that the whole mechanism by which paths get transformed into plans and handed off to the executor is still rather opaque 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] LATERAL
Robert Haas robertmh...@gmail.com writes: On Sat, Dec 19, 2009 at 3:01 PM, Tom Lane t...@sss.pgh.pa.us wrote: I believe the correct approach is probably to treat values that need to be propagated into the inner side as executor parameters. This could replace the existing, rather crocky, management of values passed into a nestloop inner indexscan. What is the best place to look for the existing, rather crocky code? Follow the second argument of ExecReScan from nodeNestloop to nodeIndexscan. 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] creating index names automatically?
I wrote: In the cases where that's sensible, you can use constraint syntax, no? I really doubt that it's that easy to pick a sensible name for an index on an expression, for example. Although, having said that, I realize we just opened that can of worms with the exclusion-constraint patch: regression=# create table foo (f1 text, exclude (lower(f1) with =)); NOTICE: CREATE TABLE / EXCLUDE will create implicit index foo_exclusion for table foo CREATE TABLE The above behavior seems to need improvement already. 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
[HACKERS] Re: About Allow VIEW/RULE recompilation when the underlying tables change
On Sat, Dec 19, 2009 at 1:56 PM, Caleb Welton cwel...@greenplum.com wrote: I maintain that the approaches that inform the user that they have met that condition via ALTER statement failures (Postgres/DB2/Microsoft Bound Views) have certain advantages over databases that allow the update but may silently leave views in an usable state (Oracle, Terradata), in that at least the user Knows when they have to re-examine their views. Agreed. As far as I can tell there are three approaches that could be taken to help address this problem: 1) DB2 like approach - try to perform rewrites where able, but if the rewrite fails then the alter operation fails. Would allow simple edits such as ALTER TYPE that are only changes in typmod, or if done more ambitiously would allow numbers to be changed to other numbers. But as Robert says this quickly approaches the territory of black magic. And it can easily lead to silent breakage - e.g. if you change an integer column to text, the view's attempt to coerce the text back to integer will continue working as long as that coercion is valid for all the data the view examines, but you have to think the user had a reason for changing the type... 2) Microsoft like approach - create a new kind of view that is just stored as the view text and can become invalid. The people who want this type of view can use it combined with all the headaches associated with this type of view. This could be emulated fairly easily. Just create a table with all your view definitions in it and write a function that drops and recreates them all. Call it whenever you change anything. 3) We extend things in a way that just makes dropping and recreating views more convenient. E.G. Some syntax for drop all dependents would be helpful to make schema changes easier. How is that different from CASCADE? ...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] no lo_import(text, oid) document
Tatsuo Ishii is...@postgresql.org writes: BTW, why don't lo_creat, lo_create, lo_unlink, lo_import and lo_export server side function's document appear in the Chapter 9. Functions and Operators section? Because large objects have their very own chapter. Problem is, the chapter is under Client Interfaces which make confuse users. I think it would be better to move under The SQL Language. -- Tatsuo Ishii SRA OSS, Inc. Japan -- 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] Distinguish view and table problem
W dniu 19 grudnia 2009 16:19 użytkownik Jaime Casanova jcasa...@systemguards.com.ec napisał: i think he is hacking postgres's source code to make the TODO: allow recompilation of views (he send an email about that in another thread)... oh. I didn't realise, that such seemingly simple question can relate to such hard task. even Oracle and other big players do not have an ideal solution for this... good luck suzhiyang! -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/
Re: [HACKERS] Removing pg_migrator limitations
Tom Lane wrote:
Bruce Momjian br...@momjian.us writes:
... The idea I had was to create a global structure:
struct pg_migrator_oids {
Oid pg_type;
Oid pg_type_array;
...
}
This would initialize to zero as a global structure, and only
pg_migrator server-side functions set it.
I would prefer *not* to do that, as that makes the list of settable oids
far more public than I would like; also you are totally dependent on
pg_migrator and the backend to be in sync about the definition of that
struct, which is going to be problematic in alpha releases in
particular, since PG_VERSION isn't going to distinguish them.
What I had in mind was more like
static Oid next_pg_class_oid = InvalidOid;
void
set_next_pg_class_oid(Oid oid)
{
next_pg_class_oid = oid;
}
Good point about requiring a link to a symbol; a structure offset would
not link to anything and would silently fail.
Does exporting a function buy us anything vs. exporting a variable?
in each module that needs to be able to accept a next-oid setting,
and then the pg_migrator loadable module would expose SQL-callable
wrappers for these functions. That way, any inconsistency shows up as
a link error: function needed not present.
I will work on a patch to accomplish this, and have pg_migrator link in
the .so only if the new server is = 8.5, which allows a single
pg_migrator binary to work for migration to 8.4 and 8.5.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ 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] Small typos in Hot Standby docs
Here's a patch: diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml index 6750db8..1276c39 100644 *** a/doc/src/sgml/backup.sgml --- b/doc/src/sgml/backup.sgml *** if (!triggered) *** 2018,2024 itemizedlist listitem para !Data Definition Language (DML) - INSERT, UPDATE, DELETE, COPY FROM, TRUNCATE. Note that there are no allowed actions that result in a trigger being executed during recovery. /para --- 2018,2024 itemizedlist listitem para !Data Manipulation Language (DML) - INSERT, UPDATE, DELETE, COPY FROM, TRUNCATE. Note that there are no allowed actions that result in a trigger being executed during recovery. /para *** if (!triggered) *** 2164,2170 listitem para Dropping tablespaces on the primary while standby queries are using ! those tablespace for temporary work files (work_mem overflow) /para /listitem listitem --- 2164,2170 listitem para Dropping tablespaces on the primary while standby queries are using ! those tablespaces for temporary work files (work_mem overflow) /para /listitem listitem -- 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: [HACKERS] Distinguish view and table problem
. This task is just a homework for me, but the TA may not deep into this problem and give me such difficult task. That simple idea was very ugly by all appearances. I'm a freshman to postgres, sorry for that bad idea. Now I've give up this problem, complaint to TA and try to solve another easier one to complete my work. These days I've learned a lot from your discussion and source code.:-) Thank you for your help! 2009-12-20 suzhiyang 发件人： Filip_Rembiałkowski 发送时间： 2009-12-20 08:33:31 收件人： Jaime Casanova 抄送： suzhiyang; Pgsql Hackers 主题： Re: [HACKERS] Distinguish view and table problem W dniu 19 grudnia 2009 16:19 użytkownik Jaime Casanova jcasa...@systemguards.com.ec napisał: i think he is hacking postgres's source code to make the TODO: allow recompilation of views (he send an email about that in another thread)... oh. I didn't realise, that such seemingly simple question can relate to such hard task. even Oracle and other big players do not have an ideal solution for this... good luck suzhiyang! -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/
Re: [HACKERS] Removing pg_migrator limitations
Tom Lane wrote: Bruce Momjian wrote: Seems I need some help here. I'm willing to work on this --- it doesn't look particularly fun but we really need it. You don't know fun until you have tried to stack hack upon hack and still create a reliable migration system. :-( -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + 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] Removing pg_migrator limitations
On Sat, Dec 19, 2009 at 10:46 PM, Bruce Momjian br...@momjian.us wrote: Tom Lane wrote: Bruce Momjian wrote: Seems I need some help here. I'm willing to work on this --- it doesn't look particularly fun but we really need it. You don't know fun until you have tried to stack hack upon hack and still create a reliable migration system. :-( They say that people who love sausage and respect the law should never watch either one being made, and I have to say I'm coming to feel that way about in-place upgrade, too. ...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] Removing pg_migrator limitations
Robert Haas wrote: On Sat, Dec 19, 2009 at 10:46 PM, Bruce Momjian br...@momjian.us wrote: Tom Lane wrote: Bruce Momjian wrote: Seems I need some help here. I'm willing to work on this --- it doesn't look particularly fun but we really need it. You don't know fun until you have tried to stack hack upon hack and still create a reliable migration system. ?:-( They say that people who love sausage and respect the law should never watch either one being made, and I have to say I'm coming to feel that way about in-place upgrade, too. Agreed ... There is nothing to see here --- move along. ;-) LOL -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + 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] LATERAL
On Sat, Dec 19, 2009 at 4:46 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Sat, Dec 19, 2009 at 3:01 PM, Tom Lane t...@sss.pgh.pa.us wrote: I believe the correct approach is probably to treat values that need to be propagated into the inner side as executor parameters. This could replace the existing, rather crocky, management of values passed into a nestloop inner indexscan. What is the best place to look for the existing, rather crocky code? Follow the second argument of ExecReScan from nodeNestloop to nodeIndexscan. Yeah, this is grotty. It appears that the comment introducing ExecReScan() is somewhat incorrect. It asserts that exprCtxt is used only -- 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] LATERAL
On Sat, Dec 19, 2009 at 11:01 PM, Robert Haas robertmh...@gmail.com wrote: On Sat, Dec 19, 2009 at 4:46 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Sat, Dec 19, 2009 at 3:01 PM, Tom Lane t...@sss.pgh.pa.us wrote: I believe the correct approach is probably to treat values that need to be propagated into the inner side as executor parameters. This could replace the existing, rather crocky, management of values passed into a nestloop inner indexscan. What is the best place to look for the existing, rather crocky code? Follow the second argument of ExecReScan from nodeNestloop to nodeIndexscan. Yeah, this is grotty. It appears that the comment introducing ExecReScan() is somewhat incorrect. It asserts that exprCtxt is used only Sigh. ...is used only for index scans. However, it's actually also used for bitmap scans (both heap and index) and TID scans. Also, there appears to be an effort by nodes that don't use exprCtxt directly to propagate down through the node tree, which doesn't seem to make much sense if this is only intended to be used on the inner side of a nestloop. ...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] LATERAL
Robert Haas robertmh...@gmail.com writes: Yeah, this is grotty. It appears that the comment introducing ExecReScan() is somewhat incorrect. It asserts that exprCtxt is used only Sigh. ...is used only for index scans. However, it's actually also used for bitmap scans (both heap and index) and TID scans. Yeah, the comment was probably correct when written. Also, there appears to be an effort by nodes that don't use exprCtxt directly to propagate down through the node tree, which doesn't seem to make much sense if this is only intended to be used on the inner side of a nestloop. That's just dead code, which is a good thing because the coverage is pretty incomplete. 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
[HACKERS] Additional SPI functions
In the event that my plpython3 patch does not make it, it seems prudent to try and get a *much* smaller patch in to allow the PL to easily exist out of core. I added a couple SPI functions in order to support the database access functionality in plpython3u. Also, a getelevel() function for conditionally including context information due to error trapping awkwardness: extern int SPI_execute_statements(const char *src); Execute multiple statements. Intended, primarily, for executing one or more DDL or DML statements. In contrast with the other execution functions, the RPT loop plans and executes the statement before planning and executing the next in order to allow subsequent statements to see the effects of all the formers. The read only argument is omitted as it should only be used in read-write cases(you can't read anything out of it). extern SPIPlanPtr SPI_prepare_statement( const char *src, int cursorOptions, SPIParamCallback pcb, void *pcb_arg, TupleDesc *resultDesc); Prepare a *single* statement and call the SPIParamCallback with the parameter information allowing the caller to store the information and supply constant parameters based on the identified parameter types, if need be. Also, if it returns rows, return the TupleDesc via *resultDesc. typedef void (*SPIParamCallback)( void *cb_data, const char *commandTag, int nargs, Oid *typoids, Datum **param_values, char **param_nulls); Not at all in love with the callback, but it seemed desirable over using an intermediate structure that would require some additional management. Certainly, docs and tests will be necessary for this, but I'm sending it out now with the hopes of getting some feedback before sweating those tasks. The patch is attached for easy reference. Any help would, of course, be greatly appreciated. cheers spi.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] Additional SPI functions
James William Pye li...@jwp.name writes: extern int SPI_execute_statements(const char *src); Execute multiple statements. Intended, primarily, for executing one or more DDL or DML statements. In contrast with the other execution functions, the RPT loop plans and executes the statement before planning and executing the next in order to allow subsequent statements to see the effects of all the formers. The read only argument is omitted as it should only be used in read-write cases(you can't read anything out of it). This seems just about entirely useless. Why not code a loop around one of the existing SPI execution functions? extern SPIPlanPtr SPI_prepare_statement( const char *src, int cursorOptions, SPIParamCallback pcb, void *pcb_arg, TupleDesc *resultDesc); Prepare a *single* statement and call the SPIParamCallback with the parameter information allowing the caller to store the information and supply constant parameters based on the identified parameter types, if need be. Also, if it returns rows, return the TupleDesc via *resultDesc. This looks like it's most likely redundant with the stuff I added recently for the plpgsql parser rewrite. Please see if you can use that instead. 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
