Re: [HACKERS] [COMMITTERS] pgsql: Automatic view update rules Bernd Helmle
On Fri, Jan 23, 2009 at 7:17 PM, Tom Lane wrote: > > I think that we probably want the rules to show up automatically during > an upgrade from an older version, but it does not follow that they > should come back after being intentionally removed from an 8.4 > installation. > [...] > > We could imagine attaching a "no auto rules please" property to views > (hm, perhaps this is an application for reloptions for a view). > +1 for reloptions (the other way i think is to invent new syntax and i think the reloptions are exactly to avoid that) -- 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: Automatic view update rules Bernd Helmle
Robert Haas wrote: > On the other hand, it's easy to draw a line from the lax criteria for > resubmitting patches to the length of this CommitFest. It now appears > that this CommitFest will be something like 3.5 months long and that > the next one will not occur before May. That means we're essentially > closed to new patches for six months, which is a really long time. To > put it another way, for every week the core team spends reworking the > existing patches, it will be another week before someone can get > feedback on any new patches submitted now. At some point that becomes > a bad trade-off for the project as a whole. Judging when exactly > you've hit that point is difficult, but I'm starting to think we may > have entered the zone of diminishing returns. Well, also consider 8.3 was released in February, so we had 9 months of development before the last commit-fest started. Also, one thing we have always known is that many of the complex patches bottle up on the last commit-fest because we kind of realize they are not going to get much easier to deal with. As long as we are mostly busy reviewing and committing stuff, and not bottled up on 1-2 patches, I think the time in commit fest is being well spent. -- Bruce Momjian http://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] [COMMITTERS] pgsql: Automatic view update rules Bernd Helmle
> You're suggestion doesn't help with the problem that (like Joshua already > mentioned) core developers are too busy with reviewing stuff during the > CommitFest. Because of this it's really hard to get the necessary time of > somebody who is able to evaluate the architecture of a new feature and (more > important) its side-effects on the whole system. Especially the last > CommitFest before Feature Freeze is becoming heavily busted with many many > interesting patches, because people want to have their WIP reviewed at least > for the upcoming release (like me). I don't see how postponing patches would > make it better? Hmm, well, I'm only talking about postponing patches that aren't ready to be committed, and then only if their authors don't respond to feedback in a timely fashion. I agree that it would be nice if the core developers had more time to provide more feedback to more people, but that seems like an unrelated problem, and in any event I don't know how to solve it, other than by hoping that we'll eventually have more core developers. On the other hand, it's easy to draw a line from the lax criteria for resubmitting patches to the length of this CommitFest. It now appears that this CommitFest will be something like 3.5 months long and that the next one will not occur before May. That means we're essentially closed to new patches for six months, which is a really long time. To put it another way, for every week the core team spends reworking the existing patches, it will be another week before someone can get feedback on any new patches submitted now. At some point that becomes a bad trade-off for the project as a whole. Judging when exactly you've hit that point is difficult, but I'm starting to think we may have entered the zone of diminishing returns. ...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] More FOR UPDATE/FOR SHARE problems
Jeff Davis writes: > On Sat, 2009-01-24 at 19:45 +, Greg Stark wrote: >> There already is quite an extensive discussion of how FOR UPDATE >> behaves including these kinds of violations. > > Not in the documentation, that I can see. And I think it's important > that it be there for the reasons I mentioned. > > Can you refer me to the dicussion that you're talking about? I don't > remember any discussion that points out that FOR UPDATE/FOR SHARE is > broken in the simple case of a simple WHERE clause. http://www.postgresql.org/docs/8.3/static/transaction-iso.html#XACT-READ-COMMITTED Because of the above rule, it is possible for an updating command to see an inconsistent snapshot: it can see the effects of concurrent updating commands that affected the same rows it is trying to update, but it does not see effects of those commands on other rows in the database. This behavior makes Read Committed mode unsuitable for commands that involve complex search conditions. However, it is just right for simpler cases. For example, consider updating bank balances with transactions like ... -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! -- 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] More FOR UPDATE/FOR SHARE problems
Jeff Davis writes: > There you see a snapshot of the table that never existed. Either the > snapshot was taken before the UPDATE, in which case i=3 should be > included, or it was taken after the UPDATE, in which case i=4 should be > included. So atomicity is broken for WHERE. This assertion is based on a misunderstanding of what FOR UPDATE in read-committed mode is defined to do. It is supposed to give you the latest available rows. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Automatic view update rules Bernd Helmle
Bernd Helmle writes: > What i'm missing is some notion about CHECK OPTION. We > surely want to support that in way. Feel free to insist on that, if you want to make dead certain that updatable views don't make it into 8.4 ;-) My recollection of the discussion two years ago is that we concluded that WITH CHECK OPTION is simply not implementable using a rule-based infrastructure, because of the multiple-evaluation problem. Perhaps it could be done with some kind of extension to the constraint-checking logic, but I freely admit I don't see how to do it in any detail. That seems like something to tackle later on. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Automatic view update rules Bernd Helmle
Gregory Stark writes: > Tom Lane writes: >> ON UPDATE DO INSTEAD SUBSTITUTE base_table [ (base_column_name, ...) ] >> [ WHERE ... ] >> >> ON UPDATE DO INSTEAD >> UPDATE base_table SET base_col_1 = new.derived_col_1, base_col_2 ... >> WHERE CURRENT OF VIEW; > What would happen with these if the view is defined with "SELECT *" and I add > a new column or drop columns from the table? Nothing, just as happens now, because the * got expanded to a set column list by the parser before the view ever got defined. > This same machinery isn't present in the normal executor is it? I mean, if I > can update a view then ISTM I should be able to update a view written inline > in the query like: > UPDATE (select * from a where x=1) set y=2 That is not a view; the primary reason why not being that there are no applicable rules. 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] SE-PostgreSQL Updated Revision (r1460)
On Fri, Jan 23, 2009 at 12:30 AM, KaiGai Kohei wrote: > The patch set of SE-PostgreSQL and related stuff were updated (r1460). > > [1/5] > http://sepgsql.googlecode.com/files/sepostgresql-sepgsql-8.4devel-3-r1460.patch > [2/5] > http://sepgsql.googlecode.com/files/sepostgresql-utils-8.4devel-3-r1460.patch > [3/5] > http://sepgsql.googlecode.com/files/sepostgresql-policy-8.4devel-3-r1460.patch > [4/5] > http://sepgsql.googlecode.com/files/sepostgresql-docs-8.4devel-3-r1460.patch > [5/5] > http://sepgsql.googlecode.com/files/sepostgresql-tests-8.4devel-3-r1460.patch KaiGai - I read through your docs patch tonight and did some copy editing. Please see the attached patches, which I hope you will find helpful. I have attached my suggested changes both as a patch against v1460 (sepostgresql-docs-rmh-vs-1460.gz) and also as patch against CVS HEAD (sepostgresql-docs-rmh-vs-cvs-head), since I am not sure which is easier for you. I have a couple of general comments about the documentation: 1. The docs as written are very Red Hat-centric, even to the point of making reference to specific versions of Red Hat RPMs. I think that the community will find this unacceptable, as Red Hat is certainly not the only SELinux-enabled distribution and I presume that we want to support all of them to an equal degree. 2. Some of the information that is documented here properly belongs in other sections of the documentation. For example, the information about GUCs clearly belongs somewhere in the section on server configuration where all of the other GUCs are documented, not in a separate sections about SE-PostgreSQL. I suspect that all of the information about row-level ACLs should be ripped out of security.sgml and inserted into an appropriate portion of the "Database Roles and Privileges" chapter, leaving this file to talk just about SE-PostgreSQL. 3. It seems to me that the analogy between SQL DAC and Unix user/group DAC is mentioned far too many times, and there are other cases where information is repeated as well. I think it might help to reorganize the document a bit so that you introduce concepts in the right order. For example, the section that defines MAC and DAC is a ways down in the document, but you use those terms a whole bunch of times before defining them. I'm not 100% sure that we even want to be defining MAC and DAC in our documentation, since those are general industry terms that are not PostgreSQL-specific. But if we are going to define them then we should try to do so in the clearest way possible. Overall, I would say there is a fair amount of work left to be done to get this documentation up to par, but it's a good start and I hope that the attached patches and suggestions will be helpful. ...Robert sepostgresql-docs-rmh-vs-1460.patch.gz Description: GNU Zip compressed data sepostgresql-docs-rmh-vs-cvs-head.patch.gz Description: GNU Zip compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] mingw check hung
Something happened about 80 hours ago that caused my mingw buildfarm member (gcc 3.4.2 on Win XP Pro SP2) to hang at the check stage. It looks like it's hung in initdb. I wonder if it could be this commit: Log Message: --- Make win32 builds always do SetEnvironmentVariable() when doing putenv(). Also, if linked against other versions than the default MSVCRT library (for example the MSVC build which links against MSVCRT80), also update the cache in the default MSVCRT at the same time. I note that the change is not apparently limited to MSVC builds. The MSVC animal that runs on the same machine appears unaffected. I see one other mingw buildfarm member that is having problems that started a few days ago (yak) and another that looks like it is a few hours overdue to report, so it might also be hung (vaquita). 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] More FOR UPDATE/FOR SHARE problems
On Sat, 2009-01-24 at 19:45 +, Greg Stark wrote: > There already is quite an extensive discussion of how FOR UPDATE > behaves including these kinds of violations. Not in the documentation, that I can see. And I think it's important that it be there for the reasons I mentioned. Can you refer me to the dicussion that you're talking about? I don't remember any discussion that points out that FOR UPDATE/FOR SHARE is broken in the simple case of a simple WHERE clause. > What you propose is interesting though. It would have been impossible > before subtransactions but it's doable now. Still the performance > might be unusable for complex queries. It's basically generalizing the > logic a serializable transaction would take to a read committed command. It might be effective for queries that are highly selective on large tables. Still has strange deadlock possibilities, but I think that's the case already. Regards, Jeff Davis -- 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] Hot Standby (v9d)
Simon Riggs wrote: On Sat, 2009-01-24 at 11:20 +, Simon Riggs wrote: On Sat, 2009-01-24 at 17:24 +1300, Mark Kirkwood wrote: version 9g - please use this for testing now I'm doing some test runs with this now. I notice an old flatfiles related bug has reappeared: I'm seeing an off-by-one error on xmax, in some cases. That then causes the flat file update to not pick up correct info, even though it executed in other ways as intended. If you run two create databases and then test only the first, it appears to have worked as intended. These bugs are result of recent refactoring and it will take a few days to shake some of them out. We've had more than 20 already so we're beating them back, but we're not done yet. I was at a loss to explain how this could have slipped through our tests. It appears that the error was corrected following each checkpoint as a result of ProcArrayUpdateRunningXacts(). Our tests were performed after a short delay, which typically would be greater than the deliberately short setting of checkpoint_timeout/archive_timeout and so by the time we looked the error was gone and masked the problem. We're setting checkpoint_timeout to 30 mins now to avoid the delay... That makes sense - I had archive_timeout set at 5 minutes, and I would have checked before 5 minutes were up. Cheers 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] Time to finalize patches for 8.4 beta
Grzegorz Jaskiewicz wrote: > > On 2009-01-24, at 15:33, Bruce Momjian wrote: > > > > The PostgreSQL community is considering including security > > enhancements > > in Postgres 8.4, e.g. row-level permissions and SE-Linux security. > > However, to evaluate the patch and its usefulness, we need security > > experts who want to use this capability or have used it in other > > databases. > > With that sort of features, usually what happens in other projects, is > that despite their evaluation during beta period - they are explicitly > marked and considered as 'beta', even when product reaches release > status. > Maybe PostgresSql should follow that tactic too. After all, security > stuff needs to be tested and exposed for sometime before considered > rock solid, and trustworthy. > Which of course doesn't mean, that it shouldn't start in beta's. Once we include the code in an official release, it is much harder to make changes so I would like to get to 8.4 final at least _thinking_ it is ready, rather than trying to adjust it after the final release. -- Bruce Momjian http://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] [COMMITTERS] pgsql: Automatic view update rules Bernd Helmle
--On Samstag, Januar 24, 2009 14:17:58 -0500 Tom Lane wrote: ON UPDATE DO INSTEAD UPDATE base_table SET base_col_1 = new.derived_col_1, base_col_2 ... WHERE CURRENT OF VIEW; and the rewriter would interpret this appropriately. You'd end up with essentially the same results as with the other syntax, but there is more flexibility here to omit columns, store results computed from columns, etc. I like this idea more than Plan A or B, since it's much closer to the current rule syntax. What i'm missing is some notion about CHECK OPTION. We surely want to support that in way. -- Thanks Bernd -- 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: Automatic view update rules Bernd Helmle
Tom Lane writes: > ON UPDATE DO INSTEAD SUBSTITUTE base_table [ (base_column_name, ...) ] > [ WHERE ... ] > > ON UPDATE DO INSTEAD > UPDATE base_table SET base_col_1 = new.derived_col_1, base_col_2 ... > WHERE CURRENT OF VIEW; What would happen with these if the view is defined with "SELECT *" and I add a new column or drop columns from the table? It seems like the former with the optional list of columns would magically apply to the new columns which would make it behave differently from the normal select rule. Or would you expand an ommitted column list like we do with "select *" In any case the fact that the latter allows you to extend things with computed values seems pretty attractive. We could always allow shortcuts like "SET * WHERE CURRENT OF VIEW" analogous to "SELECT *" for manually created views. We could also allow the rhs of the expressions to be skipped so you could do UPDATE base_table SET col1, col2, col, base_col = new.derived_col - 1 WHERE CURRENT OF VIEW This same machinery isn't present in the normal executor is it? I mean, if I can update a view then ISTM I should be able to update a view written inline in the query like: UPDATE (select * from a where x=1) set y=2 just like I can with SELECTs. This does incidentally work in Oracle and is its way of doing what we do with UPDATE...FROM. It's the only way AFAIK to get merge join update plans out of it. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] UnixWare 7.1.4 (and OpenServer) sigwait issue
Continuing http://archives.postgresql.org/pgsql-hackers/2009-01/msg01762.php Bruce Momjian wrote: > > Well, this helps explain why were are getting these problems reports > only now. How many hacks do you have that we don't support, aside from > the threading one for HPUX? > Compiling threaded libpq on UnixWare fails on sigwait. I quickly setup a test for unixware 7.1.4. The only issue I found was with sigwait. Although unixware does have a 2arg POSIX.1c version, it requires setting _XOPEN_SOURCE and _POSIX_C_SOURCE in such a manner that breaks other things: (http://uw714doc.sco.com/en/man/html.3pthread/sigwait.3pthread.html). My solution was: 1. added the below to configure.in line 1620, same section as PGAC_FUNC_POSIX_SIGNALS checks. AC_MSG_CHECKING(for POSIX.1c sigwait) AC_TRY_COMPILE([#include ], [sigwait((const sigset_t *)NULL, (int *)NULL)], [AC_DEFINE(HAVE_POSIX1C_SIGWAIT, 1, [Define if the system includes a POSIX.1c sigwait]) AC_MSG_RESULT(yes)], [AC_MSG_RESULT(no)]) 2. Remove the sigwait prototype declaration from src/test/thread/thread_test.c. I do not know why it is forcing the 2 arg version of sigwait? Any insight would be helpful. Maybe it is for good reason. 3. Update fe-secure.c line 1337, the only place that uses sigwait, to the below: #ifdef HAVE_POSIX1C_SIGWAIT sigwait(&sigpipe_sigset, &signo); #else signo = sigwait(&sigpipe_sigset); #endif If these changes seemare acceptable, I will provide a formal patch. BTW, openserver has the same issue so this kills two birds with one stone. Not sure if openserver has other issues yet, test scheduled for Monday. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Hot standby, dropping a tablespace
When replaying a DROP TABLE SPACE, you first try to remove the directory, and if that fails, you assume that it's because it's in use as a temp tablespace in a read-only transaction. You then call ResolveRecoveryConflictWithVirtualXIDs to kill such transactions, and try removing the directory again. But ResolveRecoveryConflictWithVirtualXIDs doesn't wait for the target transaction to die anymore (or at least it shouldn't, as we discussed earlier), so that doesn't work AFAICS. One quick work around would be to simply not respect temp_tablespace during recovery... -- 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] Pluggable Indexes
On Sat, 2009-01-24 at 13:51 +, Simon Riggs wrote: > On Sat, 2009-01-24 at 09:57 +, Simon Riggs wrote: > > > I agree we need an external module and I learned that lesson from the > > earier API proposal you mentioned. The supplied WAL filter plugin was/is > > a valid use for this and, as discussed, is the only practical way of > > doing WAL filtering. As I said, am happy to make a few mods to make that > > more acceptable. > > I can change the contrib plugin to show how to exclude DROP DATABASE and > DROP TABLESPACE records, which is a common recovery scenario. > > I'll produce the table filter plugin and release it to pgfoundry. We > currently have everything we need to make that work, AFAICS. On reflection, I'm not going to do those things. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] More FOR UPDATE/FOR SHARE problems
There already is quite an extensive discussion of how FOR UPDATE behaves including these kinds of violations. What you propose is interesting though. It would have been impossible before subtransactions but it's doable now. Still the performance might be unusable for complex queries. It's basically generalizing the logic a serializable transaction would take to a read committed command. -- Greg On 24 Jan 2009, at 18:50, Jeff Davis wrote: This post is a follow-up of an off-list discussion with Nathan Boley. All references to FOR UPDATE apply to FOR SHARE as well. create table a(i int, j int); insert into a values(1, 10); insert into a values(2, 10); insert into a values(3, 10); insert into a values(4, 20); insert into a values(5, 20); insert into a values(6, 20); Session 1: BEGIN; UPDATE a SET j = (j - 10) WHERE i = 3 OR i = 4; Session 2: SELECT * FROM a WHERE j = 10 FOR UPDATE; -- blocks Session 1: COMMIT; Session 2 (results): i | j ---+ 1 | 10 2 | 10 (2 rows) There you see a snapshot of the table that never existed. Either the snapshot was taken before the UPDATE, in which case i=3 should be included, or it was taken after the UPDATE, in which case i=4 should be included. So atomicity is broken for WHERE. So, FOR UPDATE produces known incorrect results for: * WHERE * ORDER BY: http://archives.postgresql.org/pgsql-bugs/2009-01/msg00017.php * LIMIT * SAVEPOINT/ROLLBACK TO And I expect we'll find more, as well. It's not simply that FOR UPDATE works strangely in a couple isolated edge cases, as the docs imply. It works contrary to the basic assumptions that people familiar with PostgreSQL rely on. Furthermore, the people using FOR UPDATE are likely to be the people who care about these edge cases. I think that FOR UPDATE deserves a jarring disclaimer in the docs if we maintain the current behavior. Something along the lines of "this does not follow normal transactional semantics and will produce incorrect results". Existing users may find current FOR UPDATE behavior useful to avoid full-table locks, but they should be properly warned. If there is a fix, the only thing that I can imagine working (aside from a full table lock) would be to iteratively acquire new snapshots and re-run the query until no concurrent transaction interferes. Regards, Jeff Davis -- 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] [COMMITTERS] pgsql: Automatic view update rules Bernd Helmle
I wrote: > ... It seems to me that the rule engine > has probably got all the infrastructure needed to convert the query the > way we'd like, we just don't have a suitable API to tell it to do that. I have in mind a couple of quite different approaches to this, and wanted to solicit some feedback about which direction to pursue. The idea I'd originally had was something along the lines of ON UPDATE DO INSTEAD SUBSTITUTE base_table [ (base_column_name, ...) ] where the intended transformation is that you take the update command on the view as-written, substitute base_table for the view name and appropriate base_column_names for each view column name, and presto you have your update command for the base table. The list of column names would be there to let you specify the correspondence between base columns and view columns. One thing this is lacking is anything corresponding to the view's WHERE clause to ensure that the update is restricted to rows that are visible through the view. We could just have the rewriter copy over the view's WHERE clause, or we could insist that the clause be repeated in the rule, ie ON UPDATE DO INSTEAD SUBSTITUTE base_table [ (base_column_name, ...) ] [ WHERE ... ] That would be pretty tedious to write or maintain by hand, but in simple cases the automatic rewriter should do it for you. (Note: I'm focusing on UPDATE here because that's the hardest case. DELETE is easier because there's no new column values to compute, and INSERT is easy because there's no need to worry about matching to an existing view row.) Plan B was to not have any of this syntax exposed at all, but just have the rewriter try to do it automatically when no update rule exists for a view. I think the main argument in favor of exposing syntax would be if the syntax allows you to do things above and beyond the cases that we're willing to take care of automatically. Some examples of that would be ignoring attempted updates on derived columns of a view, or reversing invertible functions in the view. (A trivial example of that: if the view exposes "base_col + 1", you could allow updates that subtract one from the value the user tries to store.) The above syntax doesn't work very well for doing such things, though. I came up with a Plan C, which is to keep mostly the current syntax for update rules but invent some notation that says "apply the update to the view's underlying row". There's an obvious candidate for existing syntax to abuse for this purpose: WHERE CURRENT OF. So we'd write something like ON UPDATE DO INSTEAD UPDATE base_table SET base_col_1 = new.derived_col_1, base_col_2 ... WHERE CURRENT OF VIEW; and the rewriter would interpret this appropriately. You'd end up with essentially the same results as with the other syntax, but there is more flexibility here to omit columns, store results computed from columns, etc. This is a bit ugly because of the potential conflict with regular "WHERE CURRENT OF cursor", but I find it hard to see a use-case for that in a rule, since cursors are so much shorter-lived than rules. Anyway you could avoid the conflict by not naming your cursor "view". A bigger objection is that the semantics would be just a little bit different from regular WHERE CURRENT OF cursor, because our implementation of that is effectively a ctid match; and as I explained before, that's not what we want for an updatable view. Does anyone find any of these examples particularly attractive or horrific? Got any better ideas? 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] More FOR UPDATE/FOR SHARE problems
This post is a follow-up of an off-list discussion with Nathan Boley. All references to FOR UPDATE apply to FOR SHARE as well. create table a(i int, j int); insert into a values(1, 10); insert into a values(2, 10); insert into a values(3, 10); insert into a values(4, 20); insert into a values(5, 20); insert into a values(6, 20); Session 1: BEGIN; UPDATE a SET j = (j - 10) WHERE i = 3 OR i = 4; Session 2: SELECT * FROM a WHERE j = 10 FOR UPDATE; -- blocks Session 1: COMMIT; Session 2 (results): i | j ---+ 1 | 10 2 | 10 (2 rows) There you see a snapshot of the table that never existed. Either the snapshot was taken before the UPDATE, in which case i=3 should be included, or it was taken after the UPDATE, in which case i=4 should be included. So atomicity is broken for WHERE. So, FOR UPDATE produces known incorrect results for: * WHERE * ORDER BY: http://archives.postgresql.org/pgsql-bugs/2009-01/msg00017.php * LIMIT * SAVEPOINT/ROLLBACK TO And I expect we'll find more, as well. It's not simply that FOR UPDATE works strangely in a couple isolated edge cases, as the docs imply. It works contrary to the basic assumptions that people familiar with PostgreSQL rely on. Furthermore, the people using FOR UPDATE are likely to be the people who care about these edge cases. I think that FOR UPDATE deserves a jarring disclaimer in the docs if we maintain the current behavior. Something along the lines of "this does not follow normal transactional semantics and will produce incorrect results". Existing users may find current FOR UPDATE behavior useful to avoid full-table locks, but they should be properly warned. If there is a fix, the only thing that I can imagine working (aside from a full table lock) would be to iteratively acquire new snapshots and re-run the query until no concurrent transaction interferes. Regards, Jeff Davis -- 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: Automatic view update rules Bernd Helmle
--On 23. Januar 2009 21:18:19 -0500 Robert Haas wrote: In the future, I think we should have an expectation that resubmits within the same CommitFest should happen within a week, and that if no revision is forthcoming within two weeks the patch is declared dead (and the submitter can add it to the next CommitFest when they resubmit). Don't think I'm picking on you, either: there was quite a bit of it this CommitFest, and it's bad, because: yes, i'd be happier if i could have spent more time on it, being more responsive, especially in late December. But it wasn't possible, my fault :( You're suggestion doesn't help with the problem that (like Joshua already mentioned) core developers are too busy with reviewing stuff during the CommitFest. Because of this it's really hard to get the necessary time of somebody who is able to evaluate the architecture of a new feature and (more important) its side-effects on the whole system. Especially the last CommitFest before Feature Freeze is becoming heavily busted with many many interesting patches, because people want to have their WIP reviewed at least for the upcoming release (like me). I don't see how postponing patches would make it better? The lesson i've learned is to post more, post often and not waiting until the last CommitFest begins. Bernd -- 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 finalize patches for 8.4 beta
Bruce Momjian wrote: KaiGai Kohei wrote: Bruce Momjian wrote: Now that we are two months into the final commit fest, it is time to finalize all the open patches so we can target a February beta. The two major outstanding patches are: o SE-PostgreSQL: The author has done an outstanding job of reworking the patch so the burden is now on the community. We have to decide if we want to add this amount of new code to have both SQL row permissions and SE-Linux support. o Recovery, Replication, Hot Standby: We need a _final_ version of any patches that are targeted for 8.4. There is so much activity in this area I am unclear what is ready for 8.4. I think the remaining patches can be addressed pretty easily but we need final versions from any authors who are still adjusting them. Let's see what we can get done in the next two weeks and reevaluate. From the recent pgsql-hackers, it seems me people implicitly considers the last commit-fest is forcibly called off at end of the January. No, we still have many people working on patches and the commit-fest is going to extend into February. All right, I'll prepare the discussion on the list. I know a few SELinux developers good for database also, so I would forward the message to them. They are surely experts in security field. Thanks, I am about to post to the 'announce' list saying discussion on this patch will begin on Wednesday, at 12:00 GMT. The text is below: --- The PostgreSQL community is considering including security enhancements in Postgres 8.4, e.g. row-level permissions and SE-Linux security. However, to evaluate the patch and its usefulness, we need security experts who want to use this capability or have used it in other databases. The most recent version of the patch is mentioned here: http://archives.postgresql.org/pgsql-hackers/2009-01/msg01680.php Particularly interesting is the documentation patch: http://sepgsql.googlecode.com/files/sepostgresql-docs-8.4devel-3-r1460.patch Also, http://wiki.postgresql.org/wiki/SEPostgreSQL If you know someone who is interested in these features or can help in discussing them, please have them subscribe to pgsql-hackers here: http://www.postgresql.org/community/lists/subscribe Email discussion about this topic will start on Wednesday, 12:00 GMT, and will include the subject text "SE-PostgreSQL". -- KaiGai Kohei -- 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 finalize patches for 8.4 beta
On 2009-01-24, at 15:33, Bruce Momjian wrote: The PostgreSQL community is considering including security enhancements in Postgres 8.4, e.g. row-level permissions and SE-Linux security. However, to evaluate the patch and its usefulness, we need security experts who want to use this capability or have used it in other databases. With that sort of features, usually what happens in other projects, is that despite their evaluation during beta period - they are explicitly marked and considered as 'beta', even when product reaches release status. Maybe PostgresSql should follow that tactic too. After all, security stuff needs to be tested and exposed for sometime before considered rock solid, and trustworthy. Which of course doesn't mean, that it shouldn't start in beta's. -- 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 finalize patches for 8.4 beta
KaiGai Kohei wrote: > Bruce Momjian wrote: > > Now that we are two months into the final commit fest, it is time to > > finalize all the open patches so we can target a February beta. > > > > The two major outstanding patches are: > > > > o SE-PostgreSQL: The author has done an outstanding job of > > reworking the patch so the burden is now on the community. We have to > > decide if we want to add this amount of new code to have both SQL row > > permissions and SE-Linux support. > > > > o Recovery, Replication, Hot Standby: We need a _final_ version > > of any patches that are targeted for 8.4. There is so much activity in > > this area I am unclear what is ready for 8.4. > > > > I think the remaining patches can be addressed pretty easily but we need > > final versions from any authors who are still adjusting them. > > > > Let's see what we can get done in the next two weeks and reevaluate. > > From the recent pgsql-hackers, it seems me people implicitly considers > the last commit-fest is forcibly called off at end of the January. No, we still have many people working on patches and the commit-fest is going to extend into February. I am about to post to the 'announce' list saying discussion on this patch will begin on Wednesday, at 12:00 GMT. The text is below: --- The PostgreSQL community is considering including security enhancements in Postgres 8.4, e.g. row-level permissions and SE-Linux security. However, to evaluate the patch and its usefulness, we need security experts who want to use this capability or have used it in other databases. The most recent version of the patch is mentioned here: http://archives.postgresql.org/pgsql-hackers/2009-01/msg01680.php Particularly interesting is the documentation patch: http://sepgsql.googlecode.com/files/sepostgresql-docs-8.4devel-3-r1460.patch If you know someone who is interested in these features or can help in discussing them, please have them subscribe to pgsql-hackers here: http://www.postgresql.org/community/lists/subscribe Email discussion about this topic will start on Wednesday, 12:00 GMT, and will include the subject text "SE-PostgreSQL". -- Bruce Momjian http://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] duplicated tables
Teodor Sigaev escreveu: >> That doesn't look like duplicated tables so much as duplicated >> pg_namespace rows --- try doing \d's query by hand and adding display >> of pg_class.ctid and pg_namespace.ctid. However, if that theory is >> correct then the next question is what you were doing to pg_namespace... > > The bug was dereferencing uninitialized pointer, and postgres dumps core > immediately. And patch does nothing with namespace. > Could you post a backtrace or a test case? Maybe it is worth adding an assert or test at some point in the code. -- Euler Taveira de Oliveira http://www.timbira.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] Pluggable Indexes
On Sat, 2009-01-24 at 09:57 +, Simon Riggs wrote: > I agree we need an external module and I learned that lesson from the > earier API proposal you mentioned. The supplied WAL filter plugin was/is > a valid use for this and, as discussed, is the only practical way of > doing WAL filtering. As I said, am happy to make a few mods to make that > more acceptable. I can change the contrib plugin to show how to exclude DROP DATABASE and DROP TABLESPACE records, which is a common recovery scenario. I'll produce the table filter plugin and release it to pgfoundry. We currently have everything we need to make that work, AFAICS. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby (v9d)
On Sat, 2009-01-24 at 11:20 +, Simon Riggs wrote: > On Sat, 2009-01-24 at 17:24 +1300, Mark Kirkwood wrote: > > > > version 9g - please use this for testing now > > > I'm doing some test runs with this now. I notice an old flatfiles > > related bug has reappeared: > > I'm seeing an off-by-one error on xmax, in some cases. That then causes > the flat file update to not pick up correct info, even though it > executed in other ways as intended. If you run two create databases and > then test only the first, it appears to have worked as intended. > > These bugs are result of recent refactoring and it will take a few days > to shake some of them out. We've had more than 20 already so we're > beating them back, but we're not done yet. I was at a loss to explain how this could have slipped through our tests. It appears that the error was corrected following each checkpoint as a result of ProcArrayUpdateRunningXacts(). Our tests were performed after a short delay, which typically would be greater than the deliberately short setting of checkpoint_timeout/archive_timeout and so by the time we looked the error was gone and masked the problem. We're setting checkpoint_timeout to 30 mins now to avoid the delay... -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Time to finalize patches for 8.4 beta
Bruce Momjian wrote: Now that we are two months into the final commit fest, it is time to finalize all the open patches so we can target a February beta. The two major outstanding patches are: o SE-PostgreSQL: The author has done an outstanding job of reworking the patch so the burden is now on the community. We have to decide if we want to add this amount of new code to have both SQL row permissions and SE-Linux support. o Recovery, Replication, Hot Standby: We need a _final_ version of any patches that are targeted for 8.4. There is so much activity in this area I am unclear what is ready for 8.4. I think the remaining patches can be addressed pretty easily but we need final versions from any authors who are still adjusting them. Let's see what we can get done in the next two weeks and reevaluate. From the recent pgsql-hackers, it seems me people implicitly considers the last commit-fest is forcibly called off at end of the January. I believe we can have an opportunity to review our proposed patches at least, because Tom commented as follows two months ago. http://archives.postgresql.org/message-id/25086.1227240...@sss.pgh.pa.us Yes, I can understand the planned schedule to release v8.4 beta is now coming and we cannot wait for them forever. However, IIRC, SE-PostgreSQL patches have been neglected for a month (except for comments from Alvaro Herrera) since I released a revised version which supports simultaneous DAC and MAC at Dec.17. I'm afraid of SE-PostgreSQL rejected for v8.4 without serious reviews due to lack of time, if nothing is done. So, I had to review my patches by myseld for the recent weeks and rework some items which will be commented later. As I said a few times, I can work to upstream it with my highest priority (my employer also allows it for v8.4), but it is impossible by myself only. We need all your help! I'm sorry, if you felt above my concern uncomfortable. Thanks, -- KaiGai Kohei -- 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] [PATCH] reloptions - RELOPT_KIND_ALL
Tom Lane píše v pá 23. 01. 2009 v 10:19 -0500: > Zdenek Kotala writes: > > Alvaro Herrera píše v pá 23. 01. 2009 v 11:04 -0300: > >> Do you have an example use case for this? > > > I use it in my space reservation patch. I going to send it soon. > > Haven't we been over that ground already? Maybe I overlooked something, but IIRC that we discussed only TOAST chunks which is different problem. > A user-settable reloption > is not a reasonable solution to a space-reservation problem. The > potential for errors of commission and omission is too great. Hmm, yeah it could be dangerous, but on other side new columns in pg_class doesn't protect superuser to set incorrect values. I guess that put constrains on pg_class are not good idea and wrong values could cause server crash (when reservedspace will be greater then BLCKSZ). What about reloptions which can be set only by superuser? Or any other idea? Zdenek -- 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] foreign_data test fails with non-C locale
Andrew Dunstan píše v pá 23. 01. 2009 v 23:57 -0500: > > Zdenek Kotala wrote: > > Andrew Dunstan píše v pá 09. 01. 2009 v 12:16 -0500: > > > > > >> Sure, we can easily have buildfarm's initdb step set any locale (and > >> encoding, for that matter) we like. That's a simple change. > >> > > > > Will be possible to set more locales and run tests without recompilation > > on all of them? For example I have installed all Solaris'es locales on > > my animal, but currently it means that I need perform whole cycle for > > each locale. > > > > I'm working on this. Yes, you will be able to specify a list of locales > to check. For each locale the following tests will be run: > installcheck, pl-installcheck, and contrib-installcheck. thanks > However, our tests are still a bit short of working across locales. Yes, they are. Peter cleaned up some of them, but there are still open issues. And MacOS has broken locale which is different problem. > PL-check gives the diff below on PLTCL tests under en_US locale. I guess > the simplest answer is to add an alternative result file. Yes, I thought about add locale suffix for alternative result file, but it could be useless overhead. But some tests can be modified. For example select * from T_pkey1 order by key1 using @<, key2; can be rewritten as select * from T_pkey1 order by key1 using @<, key2::name; Zdenek -- 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] Hot Standby (v9d)
On Sat, 2009-01-24 at 17:24 +1300, Mark Kirkwood wrote: > > version 9g - please use this for testing now > I'm doing some test runs with this now. I notice an old flatfiles > related bug has reappeared: I'm seeing an off-by-one error on xmax, in some cases. That then causes the flat file update to not pick up correct info, even though it executed in other ways as intended. If you run two create databases and then test only the first, it appears to have worked as intended. These bugs are result of recent refactoring and it will take a few days to shake some of them out. We've had more than 20 already so we're beating them back, but we're not done yet. Thanks for the report, will publish this and other fixes on Monday. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] duplicated tables
That doesn't look like duplicated tables so much as duplicated pg_namespace rows --- try doing \d's query by hand and adding display of pg_class.ctid and pg_namespace.ctid. However, if that theory is correct then the next question is what you were doing to pg_namespace... The bug was dereferencing uninitialized pointer, and postgres dumps core immediately. And patch does nothing with namespace. -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ -- 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] Pluggable Indexes
On Fri, 2009-01-23 at 16:49 -0500, Tom Lane wrote: > Simon Riggs writes: > > On Fri, 2009-01-23 at 10:33 -0500, Tom Lane wrote: > >> Right, the WAL-record-processing API is not really at issue, since it's > >> been proven internally to the core code. My concern is with the other > >> part, namely exactly how are we going to identify and install additional > >> rmgrs. > > > The patch is just > > * a hook in StartupXLOG to allow loading arbitrary code into Startup > > * some slight redefinition of RmgrTable to allow arbitrary code to add > > or modify the contents of that table of functions. (Being able to modify > > the table is an not necessary for index extensions, but is for other > > uses). > > * some safeguards people requested > > Well, that really seems to just prove my point. You've defined a hook > and not thought carefully about how people will use it. This was originally proposed on 19 August and a patch submitted to the September commit fest. http://archives.postgresql.org/pgsql-hackers/2008-08/msg00794.php After about 30 emails of technical rebuttal we have a list of possible uses that can't be done sensibly any other way. * WAL filtering * Recovery when we have buggy index AMs, yet without losing data * Pluggable indexes * Extracting user data from WAL records (very challenging though) Those uses require the ability to both add to *and* modify all of the RmgrTable entries. If this was just for pluggable indexes then the API probably would look a little different, but it's not. The simplicity of the hook proposal says nothing about the careful thought behind it, it just relates to the wide variety of beneficial uses. At any point there we might have hit serious problems with the patch, but we didn't. I've done my best to cover the objections raised with code or suggested control mechanisms, so I'm not expecting anyone to agree with my first musings. > The main thing > that I can see right now that we'd need is some way to determine who > gets which rmgr index. (Maybe community assignment of numbers --- > similar to what we've defined for pg_statistic kind codes --- is fine, http://archives.postgresql.org/pgsql-hackers/2008-08/msg00916.php > or maybe it isn't; in any case we need an answer for that before this > hook can be considered usable.) Furthermore, maybe that's not the only > problem. I'd feel a lot better about this if the hook patch were done > in parallel with development of actual WAL support in an actual external ... I agree we need an external module and I learned that lesson from the earier API proposal you mentioned. The supplied WAL filter plugin was/is a valid use for this and, as discussed, is the only practical way of doing WAL filtering. As I said, am happy to make a few mods to make that more acceptable. I've deferred on this patch sometimes because of my other work, but also because I sensed there might be some feeling that people thought this was a threat to the project from some commercial usurpation (e.g. like InnoDB). I asked to be contacted off-list if that was the case but nobody has, so I have assumed this to be a decision based on technical merit alone. After considering all that has been said I feel this idea has merit. Yes, we need more and better plugins and this patch is the seed for those. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Column-Level Privileges
On Thu, Jan 22, 2009 at 07:03:45PM -0500, Tom Lane wrote:
> BTW, something else I'd meant to bring up for discussion is whether
> anyone likes the formatting of column privileges in \dp:
>
> regression=# create table foo(bar int, baz int);
> CREATE TABLE
> regression=# grant select on foo to joe;
> GRANT
> regression=# grant insert(bar), update(baz) on foo to joe;
> GRANT
> regression=# \dp foo
> Access privileges
> Schema | Name | Type | Access privileges | Column access privileges
> +--+---+---+--
> public | foo | table | postgres=arwdDxt/postgres | bar:
>: joe=r/postgres: joe=a/postgres
>: baz:
>: joe=w/postgres
> (1 row)
>
> (The colons after the column names are something I added on my own
> authority to Stephen's original.)
>
> This seems a bit ASCII-art-ish to me; it certainly wouldn't be
> readily parsable by programs. Now that's not really the design goal
> for \d output, and I don't have a better suggestion offhand, but
> still... anyone got a better idea?
Apart from enclosing braces and commas in between, it looks like JSON.
Maybe adding those in would help :)
regression=# \dp foo
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
+--+---+---+--
public | foo | table | postgres=arwdDxt/postgres | {
: joe=r/postgres: bar:
: joe=a/postgres,
: baz:
: joe=w/postgres
: }
(1 row)
Cheers,
David.
--
David Fetter http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
