Re: [HACKERS] ALTER TABLE ... IF EXISTS feature?
Yes, thanks, those are reasonable goals. --- Daniel Farina wrote: On Wed, Nov 24, 2010 at 7:21 PM, Bruce Momjian br...@momjian.us wrote: What are we adding a pl/pgsql dependency for? ?What is the benefit that will warrant requiring people who disable plpgsql to enable it for restores? There are two use cases I want to cover: 1) It should be possible to restore a dump made with --clean on an empty database without error, so it can be run in a transaction and the error code can be usefully monitored. 2) It should be possible a database be dumped and restored by a non-superuser, again, cleanly, as per 1. It was easy enough to change all the DROP ... statements to DROP ... IF EXISTS, but the ALTER statements have no equivalent, and thus the only way for a dump created with --clean to run without error is to ensure that all table and domain constraints exist prior to restore. The obvious mechanisms that have come to mind in this thread are: * An IF EXISTS variant of ALTER, at least for TABLE and DOMAIN (although it may be strange to only support it on a couple of types) * Use of anonymous-DO code blocks (the prototype uses this, and this depends on plpgsql) * Bizarre things I can imagine doing that involve creative queries that, as a side effect, might drop objects that I have not mentioned because I thought they were too gross to be given serious consideration. But it might be plpgsql-less, which would be nice. Note that in the case where one wants to dump/restore as a non-superuser that one may not be in a position to conveniently do a (DROP|CREATE) DATABASE statement to work around the problem. -- fdr -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] ALTER TABLE ... IF EXISTS feature?
Robert Haas wrote: With respect to the syntax itself, I have mixed feelings. On the one hand, I'm a big fan of CREATE IF NOT EXISTS and DROP IF EXISTS precisely because I believe they handle many common cases that people want in real life without much hullabaloo. But, there's clearly some limit to what can reasonably be done this way. At some point, what you really want is some kind of meta-language where you can write things like: IF EXISTS TABLE t1 THEN ALTER TABLE t1 DROP CONSTRAINT IF EXISTS t1_constr; END IF; FYI, I have felt this way for a while. IF EXISTS seemed like something that should never have been added as an inline SQL command option; it just crept in, and kept growing. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] ALTER TABLE ... IF EXISTS feature?
On Wed, Nov 24, 2010 at 4:30 PM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: With respect to the syntax itself, I have mixed feelings. On the one hand, I'm a big fan of CREATE IF NOT EXISTS and DROP IF EXISTS precisely because I believe they handle many common cases that people want in real life without much hullabaloo. But, there's clearly some limit to what can reasonably be done this way. At some point, what you really want is some kind of meta-language where you can write things like: IF EXISTS TABLE t1 THEN ALTER TABLE t1 DROP CONSTRAINT IF EXISTS t1_constr; END IF; FYI, I have felt this way for a while. IF EXISTS seemed like something that should never have been added as an inline SQL command option; it just crept in, and kept growing. Okay, that being the case: would it make sense to have pg_dump emit DO blocks? I have a feeling this might draw fire, but I don't see any reason why the mechanism would not work to more or less equivalent effect. Certainly making dumps harder to use for those who insist on disabling PL/PGSQL is probably a negative side effect, if one can identify this hypothetical class of person. fdr -- 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] ALTER TABLE ... IF EXISTS feature?
Daniel Farina wrote: On Wed, Nov 24, 2010 at 4:30 PM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: With respect to the syntax itself, I have mixed feelings. ?On the one hand, I'm a big fan of CREATE IF NOT EXISTS and DROP IF EXISTS precisely because I believe they handle many common cases that people want in real life without much hullabaloo. ?But, there's clearly some limit to what can reasonably be done this way. ?At some point, what you really want is some kind of meta-language where you can write things like: IF EXISTS TABLE t1 THEN ? ?ALTER TABLE t1 DROP CONSTRAINT IF EXISTS t1_constr; END IF; FYI, I have felt this way for a while. ?IF EXISTS seemed like something that should never have been added as an inline SQL command option; it just crept in, and kept growing. Okay, that being the case: would it make sense to have pg_dump emit DO blocks? I have a feeling this might draw fire, but I don't see any reason why the mechanism would not work to more or less equivalent effect. Certainly making dumps harder to use for those who insist on disabling PL/PGSQL is probably a negative side effect, if one can identify this hypothetical class of person. Not being able to recover a dump is serious problem for a user. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] ALTER TABLE ... IF EXISTS feature?
On Wed, Nov 24, 2010 at 7:03 PM, Bruce Momjian br...@momjian.us wrote: Daniel Farina wrote: On Wed, Nov 24, 2010 at 4:30 PM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: With respect to the syntax itself, I have mixed feelings. ?On the one hand, I'm a big fan of CREATE IF NOT EXISTS and DROP IF EXISTS precisely because I believe they handle many common cases that people want in real life without much hullabaloo. ?But, there's clearly some limit to what can reasonably be done this way. ?At some point, what you really want is some kind of meta-language where you can write things like: IF EXISTS TABLE t1 THEN ? ?ALTER TABLE t1 DROP CONSTRAINT IF EXISTS t1_constr; END IF; FYI, I have felt this way for a while. ?IF EXISTS seemed like something that should never have been added as an inline SQL command option; it just crept in, and kept growing. Okay, that being the case: would it make sense to have pg_dump emit DO blocks? I have a feeling this might draw fire, but I don't see any reason why the mechanism would not work to more or less equivalent effect. Certainly making dumps harder to use for those who insist on disabling PL/PGSQL is probably a negative side effect, if one can identify this hypothetical class of person. Not being able to recover a dump is serious problem for a user. Even if it only involves enabling PLPGSQL to do the restore? Also take into consideration that plpgsql is enabled by default. A user would have to change the template database (which, in general, can cause restores to fail in at least a few other ways) or drop the procedural language explicitly to make that mechanism not work with a fresh and normal-looking createdb. -- fdr -- 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] ALTER TABLE ... IF EXISTS feature?
Daniel Farina wrote: On Wed, Nov 24, 2010 at 7:03 PM, Bruce Momjian br...@momjian.us wrote: Daniel Farina wrote: On Wed, Nov 24, 2010 at 4:30 PM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: With respect to the syntax itself, I have mixed feelings. ?On the one hand, I'm a big fan of CREATE IF NOT EXISTS and DROP IF EXISTS precisely because I believe they handle many common cases that people want in real life without much hullabaloo. ?But, there's clearly some limit to what can reasonably be done this way. ?At some point, what you really want is some kind of meta-language where you can write things like: IF EXISTS TABLE t1 THEN ? ?ALTER TABLE t1 DROP CONSTRAINT IF EXISTS t1_constr; END IF; FYI, I have felt this way for a while. ?IF EXISTS seemed like something that should never have been added as an inline SQL command option; it just crept in, and kept growing. Okay, that being the case: would it make sense to have pg_dump emit DO blocks? I have a feeling this might draw fire, but I don't see any reason why the mechanism would not work to more or less equivalent effect. Certainly making dumps harder to use for those who insist on disabling PL/PGSQL is probably a negative side effect, if one can identify this hypothetical class of person. Not being able to recover a dump is serious problem for a user. Even if it only involves enabling PLPGSQL to do the restore? Also take into consideration that plpgsql is enabled by default. A user would have to change the template database (which, in general, can cause restores to fail in at least a few other ways) or drop the procedural language explicitly to make that mechanism not work with a fresh and normal-looking createdb. What are we adding a pl/pgsql dependency for? What is the benefit that will warrant requiring people who disable plpgsql to enable it for restores? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] ALTER TABLE ... IF EXISTS feature?
On Wed, Nov 24, 2010 at 7:21 PM, Bruce Momjian br...@momjian.us wrote: What are we adding a pl/pgsql dependency for? What is the benefit that will warrant requiring people who disable plpgsql to enable it for restores? There are two use cases I want to cover: 1) It should be possible to restore a dump made with --clean on an empty database without error, so it can be run in a transaction and the error code can be usefully monitored. 2) It should be possible a database be dumped and restored by a non-superuser, again, cleanly, as per 1. It was easy enough to change all the DROP ... statements to DROP ... IF EXISTS, but the ALTER statements have no equivalent, and thus the only way for a dump created with --clean to run without error is to ensure that all table and domain constraints exist prior to restore. The obvious mechanisms that have come to mind in this thread are: * An IF EXISTS variant of ALTER, at least for TABLE and DOMAIN (although it may be strange to only support it on a couple of types) * Use of anonymous-DO code blocks (the prototype uses this, and this depends on plpgsql) * Bizarre things I can imagine doing that involve creative queries that, as a side effect, might drop objects that I have not mentioned because I thought they were too gross to be given serious consideration. But it might be plpgsql-less, which would be nice. Note that in the case where one wants to dump/restore as a non-superuser that one may not be in a position to conveniently do a (DROP|CREATE) DATABASE statement to work around the problem. -- fdr -- 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] ALTER TABLE ... IF EXISTS feature?
On Sat, Nov 6, 2010 at 6:09 PM, Robert Haas robertmh...@gmail.com wrote: Since we now have PL/pgsql by default, we could possibly fix pg_dump --clean by emitting a DO block, although the syntax for checking existence of a table is none too pretty, and it would make pg_dump --clean rely for correctness on plpgsql being installed, which might be none too desirable. It would actually be sort of spiffy to be able to have some of the PL/pgsql control constructs available in straight SQL, but I'm not expecting that to happen any time in the forseeable future. I have hacked up a little set of work-in-progress pg_dump patches as a proof of concept. It uses the PGSQL and DO mechanics, thanks for making me aware of the latter... https://github.com/fdr/postgres/tree/pg_dump-if-exists I will be giving it a try on some more schemas in the near future. On my small, plain but not-entirely-a-toy schema it works great. For some reason I cannot seem to get it to exercise the ALTER DOMAIN check, even though I created a domain with a check constraint...hmm, I'll have to stare at what can cause it to exercise that later. fdr -- 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] ALTER TABLE ... IF EXISTS feature?
On Fri, Nov 5, 2010 at 7:49 PM, Daniel Farina drfar...@acm.org wrote: On Fri, Nov 5, 2010 at 4:20 PM, Robert Haas robertmh...@gmail.com wrote: Can you give us a self-contained example of the problem you're talking about? Sure. Consider the following: CREATE TABLE t1 ( id integer PRIMARY KEY ); CREATE TABLE t2 ( id integer PRIMARY KEY, fk integer ); ALTER TABLE ONLY t2 ADD CONSTRAINT t2_constr FOREIGN KEY (fk) REFERENCES t1(id); Try something like this: createdb foo psql -1f this_ddl.sql foo pg_dump --clean foo cleaning_backup.sql # db wipe dropdb foo createdb foo psql -1f cleaning_backup.sql foo The last command will return non-zero and abort the xact early on, because of the following stanza in pg_dump --clean's output: ALTER TABLE ONLY public.t2 DROP CONSTRAINT t2_constr; ALTER TABLE ONLY public.t2 DROP CONSTRAINT t2_pkey; ALTER TABLE ONLY public.t1 DROP CONSTRAINT t1_pkey; DROP TABLE public.t2; DROP TABLE public.t1; Since there's no public.t1/t2, it's not possible to ALTER them. I'm not entirely sure why the DROPs CONSTRAINT on pkeys are being done, as they only introduce an internal (or is it auto?) style self-dependency. It is more obvious why foreign keys are dropped, which is to break up the dependencies so that tables can be dropped without CASCADE. If we're going to try to fix this, we probably ought to try to make sure that we are fixing it fairly completely. How confident are you that this is the only problem? With respect to the syntax itself, I have mixed feelings. On the one hand, I'm a big fan of CREATE IF NOT EXISTS and DROP IF EXISTS precisely because I believe they handle many common cases that people want in real life without much hullabaloo. But, there's clearly some limit to what can reasonably be done this way. At some point, what you really want is some kind of meta-language where you can write things like: IF EXISTS TABLE t1 THEN ALTER TABLE t1 DROP CONSTRAINT IF EXISTS t1_constr; END IF; ...or possibly something much more complicated, like checking whether a table foo has a column called bar and if so doing nothing but if not but a column called baz exists then renaming it to bar and otherwise adding a column called bar. Since we now have PL/pgsql by default, we could possibly fix pg_dump --clean by emitting a DO block, although the syntax for checking existence of a table is none too pretty, and it would make pg_dump --clean rely for correctness on plpgsql being installed, which might be none too desirable. It would actually be sort of spiffy to be able to have some of the PL/pgsql control constructs available in straight SQL, but I'm not expecting that to happen any time in the forseeable future. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] ALTER TABLE ... IF EXISTS feature?
On Sat, Nov 6, 2010 at 6:09 PM, Robert Haas robertmh...@gmail.com wrote: If we're going to try to fix this, we probably ought to try to make sure that we are fixing it fairly completely. How confident are you that this is the only problem? I haven't tried to isolate problems on really complicated schemas yet, but I can tell you what I did do: I went through pg_dump and tried to find as many parts of the code that added dropStmt to DumpableObject nodes as possible (in the dumpFoo family of functions). ALTER seemed like the only interesting bit so far. But we can give the mechanic a try with, say, a PLPGSQL hack for a little while to see if it basically gets the job done in sort-of real life for a while. I think that's not a bad ideaI'll see what I can do about that. (Of course, continue to share your suggestions and revelations, I'd appreciate it) fdr -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] ALTER TABLE ... IF EXISTS feature?
Hello List, Is there any reason why Postgres should not support an ALTER TABLE tablename [IF EXISTS] feature? (And similar for other ALTER OBJECTTYPE) For example, a hypothetical statement that attempts to drop a constraint in a *completely* optional manner would look like the following: ALTER TABLE IF EXISTS foo DROP CONSTRAINT bar IF EXISTS; If you think this is already a reasonable idea, you can stop reading now and express approval at the general idea. If you are curious as to why *I* encountered it, continue reading. It would be very useful if pg_dump --clean produced output that could be run on an empty database without errors so that it is easier to monitor the process exit status of pg_restore to check for problems as well as enabling the use with one-transaction restore. The former is a vital feature for organizations that manage the creation and monitoring of many backups. In addition, these semantics are pretty familiar: they are not dissimilar from make clean. As-is, you are very likely to hit errors upon restoring a dump with --clean in most schemas. The degree of most here includes schemas that use any form of foreign key as a subset, so this is a rather large set. I encountered this problem through first hoping that meeting this requirement was entirely possible by changing pg_dump alone. The first hurdle was: DROP TABLE foo; A straightforward pg_dump change can change this to: DROP TABLE foo IF EXISTS; Things then got tricky when dealing with the constraints on domains and tables. In order to clean tables, for example, pg_dump will reverse its topological sort on the dependencies and emit drop statements for the dumpable objects individually. This ensures that within the scope of objects to be dumped that all dependent objects (the constraints, in this case) are dropped first, then finally the table. Thus, one will see the following prior to dropping the table: ALTER TABLE foo DROP CONSTRAINT bar; While one can easily fix part of a problem by emitting the following instead: ALTER TABLE foo DROP CONSTRAINT bar IF EXISTS; One is still stuck on not being able to not-error when there's a missing foo table. And so there is an impasse that requires some grammar and DDL machinery changes on the server side. fdr -- 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] ALTER TABLE ... IF EXISTS feature?
Daniel Farina drfar...@acm.org writes: Is there any reason why Postgres should not support an ALTER TABLE tablename [IF EXISTS] feature? I think you've phrased the question backwards. Why *should* we support that large increment of complexity? The use-cases seem pretty few and far between. 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] ALTER TABLE ... IF EXISTS feature?
On Nov 5, 2010, at 10:49 AM, Tom Lane t...@sss.pgh.pa.us wrote: Daniel Farina drfar...@acm.org writes: Is there any reason why Postgres should not support an ALTER TABLE tablename [IF EXISTS] feature? I think you've phrased the question backwards. Why *should* we support that large increment of complexity? The use-cases seem pretty few and far between. Obviously we have different definitions of a large increment in complexity. ...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] ALTER TABLE ... IF EXISTS feature?
Robert Haas robertmh...@gmail.com writes: On Nov 5, 2010, at 10:49 AM, Tom Lane t...@sss.pgh.pa.us wrote: I think you've phrased the question backwards. Why *should* we support that large increment of complexity? The use-cases seem pretty few and far between. Obviously we have different definitions of a large increment in complexity. Well, (1) the proposal affects a large number of commands (2) in some cases the correct behavior isn't obvious (I note Daniel's example had *two* IF EXISTS options in one command...) (3) it raises the bar of expectation for every future ALTER command That spells large maintenance burden to me, even if any one command would be relatively simple to fix. And we haven't even reached the question of whether pg_dump could use these things usefully; I suspect that the bottom-line issue there might be something else entirely. 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] ALTER TABLE ... IF EXISTS feature?
On Fri, Nov 5, 2010 at 8:44 AM, Tom Lane t...@sss.pgh.pa.us wrote: That spells large maintenance burden to me, even if any one command would be relatively simple to fix. And we haven't even reached the question of whether pg_dump could use these things usefully; I suspect that the bottom-line issue there might be something else entirely. Well, why not? I was in the middle of writing it. But I'm open to other approaches, the real motivating issue could be stated simply as: pg_dump --clean should produce output that can be run on empty, full or partially-full databases in a transaction without a non-zero error code ALTER IF EXISTS just struck me as one such mechanism that is familiar looking, at least. Also take note that I suffered from some syntactic dyslexia above, IF EXISTS doesn't appear to ever occur after the identifier, so the example would be: ALTER TABLE IF EXISTS foo DROP CONSTRAINT IF EXISTS bar; To me this reads fine, although I agree that it could be annoying to main this optional-ness on a large series of commands. One might be able to imagine a special backend function to do this missing-identifier guarding, that may shorten the whole thing up a bit...any suggestions, in that line of thinking or others? fdr -- 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] ALTER TABLE ... IF EXISTS feature?
Daniel Farina drfar...@acm.org writes: Well, why not? I was in the middle of writing it. But I'm open to other approaches, the real motivating issue could be stated simply as: pg_dump --clean should produce output that can be run on empty, full or partially-full databases in a transaction without a non-zero error code [ shrug... ] I'm less than convinced that that's an important goal. If you don't know what it is you're overwriting, maybe you should stop and think what you're doing, instead of expecting pg_dump to silently stomp on portions of whatever you already have. What you're proposing would maybe be useful for overwriting a database that contains portions of what is in the source database, but what's the use of that? You could just as well dropdb and start fresh. The interesting case here is where the target db has *more* objects than are in the dump, and I really doubt that there is any self-consistent behavior that pg_dump can automatically provide for such cases. It can't drop objects it doesn't know about, and it also has no hope of ensuring that their relationships to the replaced objects remain consistent. Silently wiping out FKs, for instance, seems like a pretty bad idea. So, basically, I've never seen any fully credible use case for pg_dump --clean, and this proposal does nothing to fix that. I don't see the argument for putting a large amount of work down that rathole. Especially not if the only benefit you get out of it is not looking at error messages. In most cases you'd *better* study those error messages, to see what manual cleanup you're going to need to undertake. 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] ALTER TABLE ... IF EXISTS feature?
On Fri, Nov 5, 2010 at 10:04 AM, Tom Lane t...@sss.pgh.pa.us wrote: What you're proposing would maybe be useful for overwriting a database that contains portions of what is in the source database, but what's the use of that? You could just as well dropdb and start fresh. The interesting case here is where the target db has *more* objects than are in the dump, and I really doubt that there is any self-consistent behavior that pg_dump can automatically provide for such cases. It can't drop objects it doesn't know about, and it also has no hope of ensuring that their relationships to the replaced objects remain consistent. Silently wiping out FKs, for instance, seems like a pretty bad idea. I am somewhat sympathetic to this argument, except for one thing: pg_dump --clean will successfully and silently wipe out a foreign key right now, should it exist, especially considering the default mode is not to run in a single transaction; a bunch of errors will be spit out to the log (except in the case where it will, as you say, silently wipe out a FK), but it'll probably be too late to do anything. It only complains (and then fails to act anyway, as there is no object to act upon) when no such object exists. One way to ensure it doesn't delete a foreign key that makes less sense is to ensure that all dependents are to-be-dumped DependentObjects in pg_dump. Pattern-matching/partial pg_dumps have never been sound in the general case anyway. In most cases you'd *better* study those error messages, to see what manual cleanup you're going to need to undertake. Of what possible use to me is a transaction-aborting error message and state emitted by an ALTER that is then succeeded by a series of DROP (TABLE|DOMAIN) statements that encompass the dependents anyway? I think an error message saying couldn't drop this table because other objects that were not named by the cleaning-dump depend on it falls into the category of interesting error message that should abort the transaction. An error condition/xact abort complaining about a missing constraint for tables that are about to have DROP statements execute for both of them is considerably less useful. So, basically, I've never seen any fully credible use case for pg_dump --clean Then why is it not on the road to deprecation, or at least given a strong warning to not use it in the --help message (or is it)? As-is I think it may only exist to misguide people. fdr -- 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] ALTER TABLE ... IF EXISTS feature?
Daniel Farina drfar...@acm.org writes: I am somewhat sympathetic to this argument, except for one thing: pg_dump --clean will successfully and silently wipe out a foreign key right now, should it exist, No, it will not, because we don't use CASCADE in the drop commands. The case I'm thinking about is where we are trying to --clean the PK table, and the referencing table is one we don't know about. The other way around, the FK constraint will disappear, but that's arguably less problematic. 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] ALTER TABLE ... IF EXISTS feature?
On Fri, Nov 5, 2010 at 11:04 AM, Tom Lane t...@sss.pgh.pa.us wrote: Daniel Farina drfar...@acm.org writes: I am somewhat sympathetic to this argument, except for one thing: pg_dump --clean will successfully and silently wipe out a foreign key right now, should it exist, No, it will not, because we don't use CASCADE in the drop commands. I know it does not use CASCADE, but if I understand it correctly, foreign keys are dropped between tables, and then the tables are dropped. (effectively a manual cascade) In not-single-transaction mode, this can cause a foreign key to get dropped, but then the DROP TABLE could subsequently fail, leaving two tables that were once connected no longer connected. I could be wrong about this, but I think this is the case. If that is not the case, please correct me before continuing to read... If the referencing table is not scoped for dumping by pg_dump, then wouldn't the transaction *correctly* (or perhaps I should say desirably) abort upon attempting to drop the PK? Right now pg_dump --clean is falling down in very common cases where constraints are being dropped prior to all the related objects being dropped. I will also make an argument that, provided --clean is regarded as a feature to have at all, that ensuring it can be used productively in a well-formed case in single-transaction mode is going to prevent a lot of mistakes. As-is the user is compelled -- should they opt to use clean -- to not use single-transaction mode for restores because it's practically guaranteed to fail all the time should they use foreign key constraints anywhere, unless they are certain that all objects in the database being restored into exist with the same relationships, for sure. Right now to get it to do anything useful it *must* run in multi-transaction mode so constraint drops can fail in their own transaction, and that means that you will be left with a database with some subset of constraints remaining (only discernable by the log). I could be wrong, but I think the change I'm suggesting is very safe, and quite potentially safer because users will be more inclined to restore in a transaction. fdr -- 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] ALTER TABLE ... IF EXISTS feature?
Daniel Farina drfar...@acm.org writes: On Fri, Nov 5, 2010 at 11:04 AM, Tom Lane t...@sss.pgh.pa.us wrote: Daniel Farina drfar...@acm.org writes: pg_dump --clean will successfully and silently wipe out a foreign key right now, should it exist, No, it will not, because we don't use CASCADE in the drop commands. I know it does not use CASCADE, but if I understand it correctly, foreign keys are dropped between tables, and then the tables are dropped. (effectively a manual cascade) You're missing the point. The scenario I'm concerned about is: source database contained table foo target database contains table foo, and table bar, and bar has an FK reference to foo The FK constraint is not known to pg_dump, and therefore there will not be a DROP CONSTRAINT for it in the dump. So the DROP on the referenced table will fail. The only way we could make it succeed would be to use CASCADE, which we don't do specifically because this sort of thing seems quite unsafe. 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] ALTER TABLE ... IF EXISTS feature?
On Fri, Nov 5, 2010 at 1:31 PM, Tom Lane t...@sss.pgh.pa.us wrote: Daniel Farina drfar...@acm.org writes: On Fri, Nov 5, 2010 at 11:04 AM, Tom Lane t...@sss.pgh.pa.us wrote: Daniel Farina drfar...@acm.org writes: pg_dump --clean will successfully and silently wipe out a foreign key right now, should it exist, No, it will not, because we don't use CASCADE in the drop commands. I know it does not use CASCADE, but if I understand it correctly, foreign keys are dropped between tables, and then the tables are dropped. (effectively a manual cascade) You're missing the point. The scenario I'm concerned about is: source database contained table foo target database contains table foo, and table bar, and bar has an FK reference to foo I think that's intended and okay to fail, and would continue to fail post-patch, if I understand what I am doing correctly (always suspect). The only condition where this should be emitted is when all the dependent objects are going to be dropped anyway. fdr -- 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] ALTER TABLE ... IF EXISTS feature?
On Fri, Nov 5, 2010 at 4:48 PM, Daniel Farina drfar...@acm.org wrote: On Fri, Nov 5, 2010 at 1:31 PM, Tom Lane t...@sss.pgh.pa.us wrote: Daniel Farina drfar...@acm.org writes: On Fri, Nov 5, 2010 at 11:04 AM, Tom Lane t...@sss.pgh.pa.us wrote: Daniel Farina drfar...@acm.org writes: pg_dump --clean will successfully and silently wipe out a foreign key right now, should it exist, No, it will not, because we don't use CASCADE in the drop commands. I know it does not use CASCADE, but if I understand it correctly, foreign keys are dropped between tables, and then the tables are dropped. (effectively a manual cascade) You're missing the point. The scenario I'm concerned about is: source database contained table foo target database contains table foo, and table bar, and bar has an FK reference to foo I think that's intended and okay to fail, and would continue to fail post-patch, if I understand what I am doing correctly (always suspect). The only condition where this should be emitted is when all the dependent objects are going to be dropped anyway. Dan, Can you give us a self-contained example of the problem you're talking about? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] ALTER TABLE ... IF EXISTS feature?
On Fri, Nov 5, 2010 at 4:20 PM, Robert Haas robertmh...@gmail.com wrote: Can you give us a self-contained example of the problem you're talking about? Sure. Consider the following: CREATE TABLE t1 ( id integer PRIMARY KEY ); CREATE TABLE t2 ( id integer PRIMARY KEY, fk integer ); ALTER TABLE ONLY t2 ADD CONSTRAINT t2_constr FOREIGN KEY (fk) REFERENCES t1(id); Try something like this: createdb foo psql -1f this_ddl.sql foo pg_dump --clean foo cleaning_backup.sql # db wipe dropdb foo createdb foo psql -1f cleaning_backup.sql foo The last command will return non-zero and abort the xact early on, because of the following stanza in pg_dump --clean's output: ALTER TABLE ONLY public.t2 DROP CONSTRAINT t2_constr; ALTER TABLE ONLY public.t2 DROP CONSTRAINT t2_pkey; ALTER TABLE ONLY public.t1 DROP CONSTRAINT t1_pkey; DROP TABLE public.t2; DROP TABLE public.t1; Since there's no public.t1/t2, it's not possible to ALTER them. I'm not entirely sure why the DROPs CONSTRAINT on pkeys are being done, as they only introduce an internal (or is it auto?) style self-dependency. It is more obvious why foreign keys are dropped, which is to break up the dependencies so that tables can be dropped without CASCADE. fdr -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
