Add column if not exists (CINE)
Hello,
I've been reading the earlier threads at:
http://archives.postgresql.org/pgsql-hackers/2009-05/thrd7.php#00252
http://archives.postgresql.org/pgsql-hackers/2005-10/thrd4.php#00632
and I'm not sure I have anything that substantially new to add but:
1. I can't see there's an unambiguity about what the syntax would do. It is IF NOT EXISTS, not IF NOT LIKE. Anyone who shoots themselves in the foot by calling a CINE and thinking that a preexisting differently defined column is magically converted deserves it. Either it should act exactly like the non-CINE command, or do nothing at all as if the statement wasn't there.
2. The use case is pretty clear to me - flexible scripts that'll bring all earlier database versions to the latest schema. I've been experimenting in 9.0 alpha with calling DROP CONSTRAINT IF EXISTS then ADD CONSTRAINT with named constants for a CINE effect. which as a side effect will correct any updated constraints too - and it works great. Unfortunately DROP COLUMN IF EXISTS then ADD COLUMN has the side effect of deleting all the data, so that's hardly usable.
I saw some indications that this might be a minority opinion, well I would like to cast a vote FOR this functionality. The workarounds are ugly, the solution simple and while I agree it's possible to misuse it, my opinion is that you shouldn't become a surgeon if you can't handle a scalpel. In this case I get the feeling I'm reading instructions on how to do surgery with a butter knife because we don't dare hand out anything sharper.
Regards,
Kjell Rune Skaaraas
On Tue, Apr 27, 2010 at 6:45 PM, Kjell Rune Skaaraas <kjella79@yahoo.no> wrote:
Hello,
I've been reading the earlier threads at:
http://archives.postgresql.org/pgsql-hackers/2009-05/thrd7.php#00252
http://archives.postgresql.org/pgsql-hackers/2005-10/thrd4.php#00632
and I'm not sure I have anything that substantially new to add but:1. I can't see there's an unambiguity about what the syntax would do. It is IF NOT EXISTS, not IF NOT LIKE. Anyone who shoots themselves in the foot by calling a CINE and thinking that a preexisting differently defined column is magically converted deserves it. Either it should act exactly like the non-CINE command, or do nothing at all as if the statement wasn't there.
2. The use case is pretty clear to me - flexible scripts that'll bring all earlier database versions to the latest schema. I've been experimenting in 9.0 alpha with calling DROP CONSTRAINT IF EXISTS then ADD CONSTRAINT with named constants for a CINE effect. which as a side effect will correct any updated constraints too - and it works great. Unfortunately DROP COLUMN IF EXISTS then ADD COLUMN has the side effect of deleting all the data, so that's hardly usable.
I saw some indications that this might be a minority opinion, well I would like to cast a vote FOR this functionality. The workarounds are ugly, the solution simple and while I agree it's possible to misuse it, my opinion is that you shouldn't become a surgeon if you can't handle a scalpel. In this case I get the feeling I'm reading instructions on how to do surgery with a butter knife because we don't dare hand out anything sharper.
I've already said my piece on this, but I couldn't agree more. Well
said, and your use case is exactly the one I want it for.
...Robert
Kjell Rune Skaaraas <kjella79@yahoo.no> wrote:
I've been reading the earlier threads at:
http://archives.postgresql.org/pgsql-hackers/2009-05/thrd7.php#00252
http://archives.postgresql.org/pgsql-hackers/2005-10/thrd4.php#00632
and I'm not sure I have anything that substantially new to add but:I saw some indications that this might be a minority opinion,
well I would like to cast a vote FOR this functionality.
+1 for CINE, just because MySQL supports it.
But before developing, we need to decide how to handle an added object
that has the same name but has different definitions.
Also, developers should consider not only ADD COLUMN but also other
CREATE or ADD commands. The patch will be large, including documentation
adjustments in many places -- it would be hard work.
Regards,
---
Takahiro Itagaki
NTT Open Source Software Center
Takahiro Itagaki wrote:
Kjell Rune Skaaraas <kjella79@yahoo.no> wrote:
I've been reading the earlier threads at:
http://archives.postgresql.org/pgsql-hackers/2009-05/thrd7.php#00252
http://archives.postgresql.org/pgsql-hackers/2005-10/thrd4.php#00632
and I'm not sure I have anything that substantially new to add but:I saw some indications that this might be a minority opinion,
well I would like to cast a vote FOR this functionality.+1 for CINE, just because MySQL supports it.
MySQL compatibility has never been our aim. We should adopt ideas from
other projects because they are good, not just because they are there.
That doesn't mean I don't think this is a good idea.
But before developing, we need to decide how to handle an added object
that has the same name but has different definitions.
The OP explicitly stated that in his opinion nothing should be done in
such cases. That's a defensible position, in the case of objects such as
tables that must be unique by name (e.g. tables). But what would we do
about objects where the name could be overloaded? Since we would
presumably want to do this for all (or almost all) of our CREATE/ADD
commands, we'd need a policy on those.
Also, developers should consider not only ADD COLUMN but also other
CREATE or ADD commands. The patch will be large, including documentation
adjustments in many places -- it would be hard work.
I can speak with some experience on this at least. :-) I don't see that
it would be a heck of a lot bigger than the DROP IF EXISTS cases, which
after the first few had been done were not hard, merely tedious to do :-)
cheers
andrew
Andrew Dunstan <andrew@dunslane.net> writes:
Takahiro Itagaki wrote:
But before developing, we need to decide how to handle an added object
that has the same name but has different definitions.
The OP explicitly stated that in his opinion nothing should be done in
such cases. That's a defensible position, in the case of objects such as
tables that must be unique by name (e.g. tables). But what would we do
about objects where the name could be overloaded?
Even if it's defensible, the consensus position so far has been that
it's a bad design. Every time we've looked at this, we have concluded
that CREATE OR REPLACE semantics are considerably safer to use, because
there is no question what the state of the object is afterwards. That
argument is just as valid for a column as for anything larger.
AFAICS, the only excuse CINE has for living is that (people think)
it would take less work to implement.
regards, tom lane
On Tue, Apr 27, 2010 at 08:18:13PM -0400, Robert Haas wrote:
On Tue, Apr 27, 2010 at 6:45 PM, Kjell Rune Skaaraas <kjella79@yahoo.no> wrote:
[snip]
I saw some indications that this might be a minority opinion, well I would like to cast a vote FOR this functionality. The workarounds are ugly, the solution simple and while I agree it's possible to misuse it, my opinion is that you shouldn't become a surgeon if you can't handle a scalpel. In this case I get the feeling I'm reading instructions on how to do surgery with a butter knife because we don't dare hand out anything sharper.
I've already said my piece on this, but I couldn't agree more. Well
said, and your use case is exactly the one I want it for.
+1 (Scribbles down the phrase "instructions on how to do surgery with a
butter knife because we don't dare hand out anything sharper" for future
repurposing)
Ross
--
Ross Reedstrom, Ph.D. reedstrm@rice.edu
Systems Engineer & Admin, Research Scientist phone: 713-348-6166
The Connexions Project http://cnx.org fax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE
On Wed, Apr 28, 2010 at 9:58 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Andrew Dunstan <andrew@dunslane.net> writes:
Takahiro Itagaki wrote:
But before developing, we need to decide how to handle an added object
that has the same name but has different definitions.The OP explicitly stated that in his opinion nothing should be done in
such cases. That's a defensible position, in the case of objects such as
tables that must be unique by name (e.g. tables). But what would we do
about objects where the name could be overloaded?Even if it's defensible, the consensus position so far has been that
it's a bad design. Every time we've looked at this, we have concluded
that CREATE OR REPLACE semantics are considerably safer to use, because
there is no question what the state of the object is afterwards. That
argument is just as valid for a column as for anything larger.AFAICS, the only excuse CINE has for living is that (people think)
it would take less work to implement.
I don't believe you are fairly stating the consensus from previous
discussion and I believe that you are actually in the minority on this
one. I agree that we probably don't need to support this for object
types for which CREATE OR REPLACE is available or can be made
available, but that isn't feasible for all object types - tables and
columns being the obvious examples.
...Robert
Robert Haas <robertmhaas@gmail.com> writes:
I don't believe you are fairly stating the consensus from previous
discussion and I believe that you are actually in the minority on this
one. I agree that we probably don't need to support this for object
types for which CREATE OR REPLACE is available or can be made
available, but that isn't feasible for all object types - tables and
columns being the obvious examples.
What's obvious about it? In particular, I should think that ADD OR
REPLACE COLUMN would usefully be defined as "ADD if no such column,
else ALTER COLUMN as necessary to match this spec". Dropping the
ALTER part of that has no benefit except to lazy implementors; it
certainly is not more useful to users if they can't be sure of the
column properties after issuing the command.
regards, tom lane
Tom Lane wrote:
Robert Haas <robertmhaas@gmail.com> writes:
I don't believe you are fairly stating the consensus from previous
discussion and I believe that you are actually in the minority on this
one. I agree that we probably don't need to support this for object
types for which CREATE OR REPLACE is available or can be made
available, but that isn't feasible for all object types - tables and
columns being the obvious examples.What's obvious about it? In particular, I should think that ADD OR
REPLACE COLUMN would usefully be defined as "ADD if no such column,
else ALTER COLUMN as necessary to match this spec". Dropping the
ALTER part of that has no benefit except to lazy implementors; it
certainly is not more useful to users if they can't be sure of the
column properties after issuing the command.
Right. A trivial implementation of CREATE OR REPLACE is to create the
object if it doesn't exist, do nothing if it exists already and is
identical to the new definition, and throw an error if it's not
identical. That covers the same use case as CREATE IF NOT EXISTS, but
you know what the state is after a successful execution, is easy to
implement, and is in line with the existing CREATE OR REPLACE commands.
And can be extended in the future to alter the existing object instead
of throwing an error.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
On Wed, Apr 28, 2010 at 11:20 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
I don't believe you are fairly stating the consensus from previous
discussion and I believe that you are actually in the minority on this
one. I agree that we probably don't need to support this for object
types for which CREATE OR REPLACE is available or can be made
available, but that isn't feasible for all object types - tables and
columns being the obvious examples.What's obvious about it? In particular, I should think that ADD OR
REPLACE COLUMN would usefully be defined as "ADD if no such column,
else ALTER COLUMN as necessary to match this spec". Dropping the
ALTER part of that has no benefit except to lazy implementors; it
certainly is not more useful to users if they can't be sure of the
column properties after issuing the command.
Actually, that's a good idea. But how will you handle tables?
...Robert
Robert Haas wrote:
On Wed, Apr 28, 2010 at 11:20 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
I don't believe you are fairly stating the consensus from previous
discussion and I believe that you are actually in the minority on this
one. I agree that we probably don't need to support this for object
types for which CREATE OR REPLACE is available or can be made
available, but that isn't feasible for all object types - tables and
columns being the obvious examples.What's obvious about it? In particular, I should think that ADD OR
REPLACE COLUMN would usefully be defined as "ADD if no such column,
else ALTER COLUMN as necessary to match this spec". Dropping the
ALTER part of that has no benefit except to lazy implementors; it
certainly is not more useful to users if they can't be sure of the
column properties after issuing the command.Actually, that's a good idea. But how will you handle tables?
What do you mean?
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Robert Haas wrote:
On Wed, Apr 28, 2010 at 11:20 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
I don't believe you are fairly stating the consensus from previous
discussion and I believe that you are actually in the minority on this
one. I agree that we probably don't need to support this for object
types for which CREATE OR REPLACE is available or can be made
available, but that isn't feasible for all object types - tables and
columns being the obvious examples.What's obvious about it? In particular, I should think that ADD OR
REPLACE COLUMN would usefully be defined as "ADD if no such column,
else ALTER COLUMN as necessary to match this spec". Dropping the
ALTER part of that has no benefit except to lazy implementors; it
certainly is not more useful to users if they can't be sure of the
column properties after issuing the command.Actually, that's a good idea. But how will you handle tables?
I think I Iike Heikki's suggestion better, to error out if the object
exists but the properties differ. At least I'd like an option for that.
cheers
andrew
Robert Haas <robertmhaas@gmail.com> writes:
Actually, that's a good idea. But how will you handle tables?
Well, tables are a special case, mainly because it's not clear how to
avoid accidentally throwing away data. (In particular if some column in
the existing table isn't there in the new definition. It's a bit scary
to just drop the column, IMO.) I don't see that that argument applies
to doing an automatic ALTER COLUMN, though, especially since the only
column type alterations that will go through without a USING clause are
reasonably straightforward.
regards, tom lane
On Wed, Apr 28, 2010 at 12:07 PM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
Robert Haas wrote:
On Wed, Apr 28, 2010 at 11:20 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
I don't believe you are fairly stating the consensus from previous
discussion and I believe that you are actually in the minority on this
one. I agree that we probably don't need to support this for object
types for which CREATE OR REPLACE is available or can be made
available, but that isn't feasible for all object types - tables and
columns being the obvious examples.What's obvious about it? In particular, I should think that ADD OR
REPLACE COLUMN would usefully be defined as "ADD if no such column,
else ALTER COLUMN as necessary to match this spec". Dropping the
ALTER part of that has no benefit except to lazy implementors; it
certainly is not more useful to users if they can't be sure of the
column properties after issuing the command.Actually, that's a good idea. But how will you handle tables?
What do you mean?
Well, how would you define CREATE OR REPLACE TABLE? I think that
doesn't make much sense, which is why I think CREATE IF NOT EXISTS is
a reasonable approach.
...Robert
--- Den ons 2010-04-28 skrev Tom Lane <tgl@sss.pgh.pa.us>:Fra: Tom Lane <tgl@sss.pgh.pa.us>
Emne: Re: [HACKERS] Add column if not exists (CINE)
Til: "Robert Haas" <robertmhaas@gmail.com>
Kopi: "Andrew Dunstan" <andrew@dunslane.net>, "Takahiro Itagaki" <itagaki.takahiro@oss.ntt.co.jp>, "Kjell Rune Skaaraas" <kjella79@yahoo.no>, pgsql-hackers@postgresql.org
Dato: Onsdag 28. april 2010 17.20
Robert Haas <robertmhaas@gmail.com>
writes:I don't believe you are fairly stating the consensus
from previous
discussion and I believe that you are actually in the
minority on this
one. I agree that we probably don't need to
support this for object
types for which CREATE OR REPLACE is available or can
be made
available, but that isn't feasible for all object
types - tables and
columns being the obvious examples.
What's obvious about it? In particular, I should
think that ADD OR
REPLACE COLUMN would usefully be defined as "ADD if no such
column,
else ALTER COLUMN as necessary to match this spec".
Dropping the
ALTER part of that has no benefit except to lazy
implementors; it
certainly is not more useful to users if they can't be sure
of the
column properties after issuing the command.
To me this construct seems horribly ambigious. Imagine I did a ALTER TABLE foo ADD COLUMN bar INTEGER NOT NULL UNIQUE DEFAULT 10, then an ALTER TABLE foo ADD OR REPLACE COLUMN bar BIGINT. Would I get a BIGINT NOT NULL UNIQUE DEFAULT 10 or a plain BIGINT? Either way I think one group will be disappointed because it either trashes all your other setup *or* forces you to call DROP NOT NULL, DROP DEFAULT etc. when you don't want it.
There's a reason why there's no ALTER TABLE foo SET COLUMN bar [definition]" and instead many statements. Remember it has to deal with all these possible column constraints in ADD COLUMN:
where column_constraint is:
[ CONSTRAINT constraint_name ]
{ NOT NULL |
NULL |
UNIQUE index_parameters |
PRIMARY KEY index_parameters |
CHECK ( expression ) |
REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
What about something like CHECK? Would you assume it's a complete set of CHECKs and drop the rest? Or just keep creating new CHECKs every time it is run? Dupe checking?
CINE has none of these problems, either the column didn't exist before so there's nothing to worry about or the command does nothing. True, you may have a borked column but not if you follow a simple design pattern of never recasting a column type but rather add a new, migrate your data and update your queries. And for the exceptions to that rule, you can add a ALTER COLUMN SET DATA TYPE (or any of the other ALTERs) after the CINE in your scipt. If the CINE triggered all is the latest version, if not the detailed ALTERs will change any column that needs changing. Clean and simple.
Regards,
Kjell Rune
Robert Haas <robertmhaas@gmail.com> writes:
Well, how would you define CREATE OR REPLACE TABLE? I think that
doesn't make much sense, which is why I think CREATE IF NOT EXISTS is
a reasonable approach.
<hand waving time>
The behavior I'd like to have would be to allow me to give a SELECT
query to run for replacing what is there if there's something. If the
query can not be run on the existing data set, error out of course.
So you know the state for sure after the command, but it depends on your
query being correct. And you can (de)normalize existing data using joins.
The REPLACE keyword would here mean that there's a CTAS going under the
hood, then we add the constraints and indexes and triggers etc. That
would mean being able to express those entities changes too, but it
seems important.
Well, that may be not precise enough as a spec, but at least that's food
for though I hope.
Regards,
--
dim
On Wed, Apr 28, 2010 at 1:40 PM, Dimitri Fontaine
<dfontaine@hi-media.com> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
Well, how would you define CREATE OR REPLACE TABLE? I think that
doesn't make much sense, which is why I think CREATE IF NOT EXISTS is
a reasonable approach.<hand waving time>
The behavior I'd like to have would be to allow me to give a SELECT
query to run for replacing what is there if there's something. If the
query can not be run on the existing data set, error out of course.So you know the state for sure after the command, but it depends on your
query being correct. And you can (de)normalize existing data using joins.The REPLACE keyword would here mean that there's a CTAS going under the
hood, then we add the constraints and indexes and triggers etc. That
would mean being able to express those entities changes too, but it
seems important.Well, that may be not precise enough as a spec, but at least that's food
for though I hope.
This type of hand-waving convinces me more than ever that we should
just implement CINE, and it should just C if it doesn't already E.
This is what has been requested multiple times, by multiple people,
including various people who don't normally poke their head into
-hackers. I think the resistance to a straightforward implementation
with easy-to-understand behavior is completely unjustifiable. It's
completely unobvious to me that all of the above will work at all and,
if it did, whether it would actually solve the problems that I care
about, like being able to write schema-upgrade scripts that would work
in a simple and predictable fashion.
...Robert
Robert Haas wrote:
On Wed, Apr 28, 2010 at 12:07 PM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:Robert Haas wrote:
On Wed, Apr 28, 2010 at 11:20 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
I don't believe you are fairly stating the consensus from previous
discussion and I believe that you are actually in the minority on this
one. I agree that we probably don't need to support this for object
types for which CREATE OR REPLACE is available or can be made
available, but that isn't feasible for all object types - tables and
columns being the obvious examples.What's obvious about it? In particular, I should think that ADD OR
REPLACE COLUMN would usefully be defined as "ADD if no such column,
else ALTER COLUMN as necessary to match this spec". Dropping the
ALTER part of that has no benefit except to lazy implementors; it
certainly is not more useful to users if they can't be sure of the
column properties after issuing the command.Actually, that's a good idea. But how will you handle tables?
What do you mean?
Well, how would you define CREATE OR REPLACE TABLE?
It the table doesn't exist, create it. If it exists with the same name
and same columns and constraints and all, do nothing. Otherwise throw an
error.
Maybe it should also check that the existing table is empty.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
Robert Haas wrote:
Well, how would you define CREATE OR REPLACE TABLE?
It the table doesn't exist, create it. If it exists with the same name
and same columns and constraints and all, do nothing. Otherwise throw an
error.
Maybe it should also check that the existing table is empty.
The last bit doesn't seem to make sense. If you want an empty table,
you can do DROP IF EXISTS and then CREATE. ISTM that the use-cases
where you don't want to do that are cases where you don't want to lose
existing data.
For either CINE or COR, there are a number of issues that are being
hand-waved away here: is it OK to change ownership and/or permissions?
What about foreign key constraints relating this table to others?
For that matter it's not real clear that indexes, check constraints,
etc should be allowed to survive. If they are allowed to survive then
CINE TABLE is just the tip of the iceberg: to do anything useful you'd
also need CINE for ADD CONSTRAINT, CREATE INDEX, ADD FOREIGN KEY, etc.
And the more of those you add, the more problematic it gets to allow
existing objects that don't quite match what the command says.
Any of these commands are headache-y for something as complicated
as a table. I'm not at all impressed by the argument that mysql
does it, because they are *notorious* for being willing to ship
half-baked solutions.
regards, tom lane
On Wed, Apr 28, 2010 at 2:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
Robert Haas wrote:
Well, how would you define CREATE OR REPLACE TABLE?
It the table doesn't exist, create it. If it exists with the same name
and same columns and constraints and all, do nothing. Otherwise throw an
error.Maybe it should also check that the existing table is empty.
The last bit doesn't seem to make sense. If you want an empty table,
you can do DROP IF EXISTS and then CREATE. ISTM that the use-cases
where you don't want to do that are cases where you don't want to lose
existing data.
Right.
For either CINE or COR, there are a number of issues that are being
hand-waved away here: is it OK to change ownership and/or permissions?
What about foreign key constraints relating this table to others?
For that matter it's not real clear that indexes, check constraints,
etc should be allowed to survive. If they are allowed to survive then
CINE TABLE is just the tip of the iceberg: to do anything useful you'd
also need CINE for ADD CONSTRAINT, CREATE INDEX, ADD FOREIGN KEY, etc.
And the more of those you add, the more problematic it gets to allow
existing objects that don't quite match what the command says.Any of these commands are headache-y for something as complicated
as a table. I'm not at all impressed by the argument that mysql
does it, because they are *notorious* for being willing to ship
half-baked solutions.
We can artificially make this problem as complicated as we wish, but
the people who are asking for this feature (including me) will, I
believe, be quite happy with a solution that throws, say, a NOTICE
instead of an ERROR when the object already exists, and then returns
without doing anything further. There are very few, if any,
definitional issues here, except by people who are brainstorming crazy
alternative behaviors whose actual usefulness I very much doubt.
CREATE OR REPLACE is indeed much more complicated. In fact, for
tables, I maintain that you'll need to link with -ldwim to make it
work properly.
...Robert
Robert Haas <robertmhaas@gmail.com> writes:
We can artificially make this problem as complicated as we wish, but
the people who are asking for this feature (including me) will, I
believe, be quite happy with a solution that throws, say, a NOTICE
instead of an ERROR when the object already exists, and then returns
without doing anything further. There are very few, if any,
definitional issues here, except by people who are brainstorming crazy
alternative behaviors whose actual usefulness I very much doubt.
CREATE OR REPLACE is indeed much more complicated. In fact, for
tables, I maintain that you'll need to link with -ldwim to make it
work properly.
This may in fact be an appropriate way to handle the case for tables,
given the complexity of their definitions. However, the original
point of the thread was about what to do for columns. I still say
that COR rather than CINE semantics would be appropriate for columns.
regards, tom lane
robertmhaas@gmail.com (Robert Haas) writes:
On Wed, Apr 28, 2010 at 1:40 PM, Dimitri Fontaine
<dfontaine@hi-media.com> wrote:Robert Haas <robertmhaas@gmail.com> writes:
Well, how would you define CREATE OR REPLACE TABLE? �I think that
doesn't make much sense, which is why I think CREATE IF NOT EXISTS is
a reasonable approach.<hand waving time>
The behavior I'd like to have would be to allow me to give a SELECT
query to run for replacing what is there if there's something. If the
query can not be run on the existing data set, error out of course.So you know the state for sure after the command, but it depends on your
query being correct. And you can (de)normalize existing data using joins.The REPLACE keyword would here mean that there's a CTAS going under the
hood, then we add the constraints and indexes and triggers etc. That
would mean being able to express those entities changes too, but it
seems important.Well, that may be not precise enough as a spec, but at least that's food
for though I hope.This type of hand-waving convinces me more than ever that we should
just implement CINE, and it should just C if it doesn't already E.
This is what has been requested multiple times, by multiple people,
including various people who don't normally poke their head into
-hackers. I think the resistance to a straightforward implementation
with easy-to-understand behavior is completely unjustifiable. It's
completely unobvious to me that all of the above will work at all and,
if it did, whether it would actually solve the problems that I care
about, like being able to write schema-upgrade scripts that would work
in a simple and predictable fashion.
I tend to agree with you here.
While yes, "CINE is a simplification of COR (CREATE OR REPLACE)", I'm
not at all sure that it's reasonable to hope for the latter, in that it
elides potentially grave problems that aren't reasonable to expect
solved.
Notably, the "and what if a substantial data transformation is needed to
accomplish this?"
CINE doesn't propose to try to do that transformation, which seems like
the right choice to me.
When I put my "we've got things replicating using Slony-I" hat on, CINE
looks pretty preferable to me. It's unambitious - but it is certainly
NOT doing a bunch of magic behind your back so as to make it tougher to
predict what might happen in a trigger-replicated environment.
In any case, CINE seems pretty useful to me. I'm prepared to listen to
persuasion, but thus far, it looks like a "+1 from me."
An alternative that seems likable is "COR, raising an exception if
there's a type mismatch." Where there's certainly room to debate how
much of a difference represents a "mismatch."
--
"cbbrowne","@","gmail.com"
http://linuxfinances.info/info/wp.html
Predestination was doomed from the start.
Tom Lane wrote:
Robert Haas <robertmhaas@gmail.com> writes:
We can artificially make this problem as complicated as we wish, but
the people who are asking for this feature (including me) will, I
believe, be quite happy with a solution that throws, say, a NOTICE
instead of an ERROR when the object already exists, and then returns
without doing anything further. There are very few, if any,
definitional issues here, except by people who are brainstorming crazy
alternative behaviors whose actual usefulness I very much doubt.CREATE OR REPLACE is indeed much more complicated. In fact, for
tables, I maintain that you'll need to link with -ldwim to make it
work properly.This may in fact be an appropriate way to handle the case for tables,
given the complexity of their definitions. However, the original
point of the thread was about what to do for columns. I still say
that COR rather than CINE semantics would be appropriate for columns.
I have added this TODO item:
Allow CREATE TABLE to optionally create a table if it does not already
exist, without throwing an error
The fact that tables contain data makes this more complex than other
CREATE OR REPLACE operations.
* http://archives.postgresql.org/pgsql-hackers/2010-04/msg01300.php
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
--- Den fre 2010-04-30 skrev Bruce Momjian <bruce@momjian.us>:Tom Lane wrote:
Robert Haas <robertmhaas@gmail.com>
writes:
We can artificially make this problem as
complicated as we wish, but
the people who are asking for this feature
(including me) will, I
believe, be quite happy with a solution that
throws, say, a NOTICE
instead of an ERROR when the object already
exists, and then returns
without doing anything further. There are
very few, if any,
definitional issues here, except by people who
are brainstorming crazy
alternative behaviors whose actual usefulness I
very much doubt.
CREATE OR REPLACE is indeed much more
complicated. In fact, for
tables, I maintain that you'll need to link with
-ldwim to make it
work properly.
This may in fact be an appropriate way to handle the
case for tables,
given the complexity of their definitions.
However, the original
point of the thread was about what to do for
columns. I still say
that COR rather than CINE semantics would be
appropriate for columns.
I have added this TODO item:
Allow CREATE TABLE to optionally create
a table if it does not already
exist, without throwing an error
The fact that tables
contain data makes this more complex than other
CREATE OR REPLACE operations.
If you could write a COR for columns, then a COR for tables would be
90% done by calling COR on each column and drop/keep the rest (COR
WITH/WITHOUT DROP?). You'd have to deal with table constraints but you
also have to handle column constraints to do COR on columns.
In other words, pretty much all the hard bits I seem to hear people agree
on exist still apply to the single column. COR for columns was suggested
already back in the same thread in 2005:
http://archives.postgresql.org/pgsql-hackers/2005-10/msg00644.php
We're already talking 2010, what I fear is that COR is that shiny goal
far, far out there but if I come back in 2015 there'll still be neither
COR or CINE. I really can't understand how CINE can be equally hard to
implement as COR, since CINE is simply to NOT throw an error.
Regards,
Kjell Rune
Import Notes
Resolved by subject fallback
On Sat, May 1, 2010 at 2:26 PM, Kjell Rune Skaaraas <kjella79@yahoo.no> wrote:
In other words, pretty much all the hard bits I seem to hear people agree
on exist still apply to the single column. COR for columns was suggested
already back in the same thread in 2005:http://archives.postgresql.org/pgsql-hackers/2005-10/msg00644.php
We're already talking 2010, what I fear is that COR is that shiny goal
far, far out there but if I come back in 2015 there'll still be neither
COR or CINE. I really can't understand how CINE can be equally hard to
implement as COR, since CINE is simply to NOT throw an error.
It's not hard to implement at all; this whole discussion - and all the
ones that have preceded it - are based on the theory that people who
are asking for CINE are too stupid to see that CINE isn't really going
to fix their problem. That comes in several flavors, including:
- 90% of the things you would want to do in real life are way more
complicated anyway, so CINE by itself is no use,
- COR is really what you want but since you're too lazy to implement
that you want CINE instead,
- MySQL has it so it must be a bad idea (even though that was part of
our justification for adopting DIE), and/or
- most people agree that we shouldn't implement CINE.
I am usually very supportive of our community's decision-making
process, but in this case I think we're just being extraordinarily
stubborn, and frankly insulting the intelligence of our end-users,
among whom I number myself.
Q. I ran CREATE IF NOT EXISTS and it didn't magically make my table
match the column list I gave!
A1. Duh.
A2. Did you notice the part where it said:
NOTICE: table "dwiw" already exists, skipping
...and if yes, what did you think the word "skipping" (which also
appears in the similar notice we give for DROP IF EXISTS) meant?
...Robert
On Wed, Apr 28, 2010 at 9:15 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
CREATE OR REPLACE is indeed much more complicated. In fact, for
tables, I maintain that you'll need to link with -ldwim to make it
work properly.This may in fact be an appropriate way to handle the case for tables,
given the complexity of their definitions.
Patch attached.
...Robert
Attachments:
create_table_if_not_exists.patchapplication/octet-stream; name=create_table_if_not_exists.patchDownload
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 240ab93..7b77cb5 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable class="PARAMETER">table_name</replaceable> ( [
+CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE [ IF NOT EXISTS ] <replaceable class="PARAMETER">table_name</replaceable> ( [
{ <replaceable class="PARAMETER">column_name</replaceable> <replaceable class="PARAMETER">data_type</replaceable> [ DEFAULT <replaceable>default_expr</replaceable> ] [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
| <replaceable>table_constraint</replaceable>
| LIKE <replaceable>parent_table</replaceable> [ <replaceable>like_option</replaceable> ... ] }
@@ -32,7 +32,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable class="PAR
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ]
-CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable class="PARAMETER">table_name</replaceable>
+CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] [ IF NOT EXISTS ] TABLE <replaceable class="PARAMETER">table_name</replaceable>
OF <replaceable class="PARAMETER">type_name</replaceable> [ (
{ <replaceable class="PARAMETER">column_name</replaceable> WITH OPTIONS [ DEFAULT <replaceable>default_expr</replaceable> ] [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
| <replaceable>table_constraint</replaceable> }
@@ -164,6 +164,18 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable class="PAR
</varlistentry>
<varlistentry>
+ <term><literal>IF NOT EXISTS</></term>
+ <listitem>
+ <para>
+ Do not throw an error if a relation with the same name already exists.
+ A notice is issued in this case. Note that there is no guarantee that
+ the existing relation is anything like the one that would have been
+ created.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="PARAMETER">table_name</replaceable></term>
<listitem>
<para>
diff --git a/src/backend/bootstrap/bootparse.y b/src/backend/bootstrap/bootparse.y
index 387d43e..1da8fb6 100644
--- a/src/backend/bootstrap/bootparse.y
+++ b/src/backend/bootstrap/bootparse.y
@@ -245,7 +245,8 @@ Boot_CreateStmt:
ONCOMMIT_NOOP,
(Datum) 0,
false,
- true);
+ true,
+ false);
elog(DEBUG4, "relation created with oid %u", id);
}
do_end();
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index d848ef0..20d78a2 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -903,11 +903,13 @@ heap_create_with_catalog(const char *relname,
OnCommitAction oncommit,
Datum reloptions,
bool use_user_acl,
- bool allow_system_table_mods)
+ bool allow_system_table_mods,
+ bool if_not_exists)
{
Relation pg_class_desc;
Relation new_rel_desc;
Acl *relacl;
+ Oid existing_relid;
Oid old_type_oid;
Oid new_type_oid;
Oid new_array_oid = InvalidOid;
@@ -921,10 +923,27 @@ heap_create_with_catalog(const char *relname,
CheckAttributeNamesTypes(tupdesc, relkind, allow_system_table_mods);
- if (get_relname_relid(relname, relnamespace))
+ /*
+ * If the relation already exists, it's an error, unless the user specifies
+ * "IF NOT EXISTS". In that case, we just print a notice and do nothing
+ * further.
+ */
+ existing_relid = get_relname_relid(relname, relnamespace);
+ if (existing_relid != InvalidOid)
+ {
+ if (if_not_exists)
+ {
+ ereport(NOTICE,
+ (errcode(ERRCODE_DUPLICATE_TABLE),
+ errmsg("relation \"%s\" already exists, skipping",
+ relname)));
+ heap_close(pg_class_desc, RowExclusiveLock);
+ return InvalidOid;
+ }
ereport(ERROR,
(errcode(ERRCODE_DUPLICATE_TABLE),
errmsg("relation \"%s\" already exists", relname)));
+ }
/*
* Since we are going to create a rowtype as well, also check for
diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c
index 435dfdd..52eef7c 100644
--- a/src/backend/catalog/toasting.c
+++ b/src/backend/catalog/toasting.c
@@ -223,7 +223,9 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid, Datum reloptio
ONCOMMIT_NOOP,
reloptions,
false,
- true);
+ true,
+ false);
+ Assert(toast_relid != InvalidOid);
/* make the toast relation visible, else index creation will fail */
CommandCounterIncrement();
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index ccb4599..516dbd2 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -687,7 +687,9 @@ make_new_heap(Oid OIDOldHeap, Oid NewTableSpace)
ONCOMMIT_NOOP,
reloptions,
false,
- true);
+ true,
+ false);
+ Assert(OIDNewHeap != InvalidOid);
ReleaseSysCache(tuple);
diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index f52e1d8..66b67dd 100644
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -203,8 +203,10 @@ DefineSequence(CreateSeqStmt *seq)
stmt->options = list_make1(defWithOids(false));
stmt->oncommit = ONCOMMIT_NOOP;
stmt->tablespacename = NULL;
+ stmt->if_not_exists = false;
seqoid = DefineRelation(stmt, RELKIND_SEQUENCE);
+ Assert(seqoid != InvalidOid);
rel = heap_open(seqoid, AccessExclusiveLock);
tupDesc = RelationGetDescr(rel);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 9b5ce65..39d76f0 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -546,8 +546,18 @@ DefineRelation(CreateStmt *stmt, char relkind)
stmt->oncommit,
reloptions,
true,
- allowSystemTableMods);
+ allowSystemTableMods,
+ stmt->if_not_exists);
+ /*
+ * If heap_create_with_catalog returns InvalidOid, it means that the user
+ * specified "IF NOT EXISTS" and the relation already exists. In that
+ * case we do nothing further.
+ */
+ if (relationId == InvalidOid)
+ return InvalidOid;
+
+ /* Store inheritance information for new rel. */
StoreCatalogInheritance(relationId, inheritOids);
/*
diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c
index 1e14dca..fec73c6 100644
--- a/src/backend/commands/typecmds.c
+++ b/src/backend/commands/typecmds.c
@@ -1506,6 +1506,7 @@ DefineCompositeType(const RangeVar *typevar, List *coldeflist)
CreateStmt *createStmt = makeNode(CreateStmt);
Oid old_type_oid;
Oid typeNamespace;
+ Oid relid;
if (coldeflist == NIL)
ereport(ERROR,
@@ -1523,6 +1524,7 @@ DefineCompositeType(const RangeVar *typevar, List *coldeflist)
createStmt->options = list_make1(defWithOids(false));
createStmt->oncommit = ONCOMMIT_NOOP;
createStmt->tablespacename = NULL;
+ createStmt->if_not_exists = false;
/*
* Check for collision with an existing type name. If there is one and
@@ -1546,7 +1548,9 @@ DefineCompositeType(const RangeVar *typevar, List *coldeflist)
/*
* Finally create the relation. This also creates the type.
*/
- return DefineRelation(createStmt, RELKIND_COMPOSITE_TYPE);
+ relid = DefineRelation(createStmt, RELKIND_COMPOSITE_TYPE);
+ Assert(relid != InvalidOid);
+ return relid;
}
/*
diff --git a/src/backend/commands/view.c b/src/backend/commands/view.c
index d7a06bc..67c90a2 100644
--- a/src/backend/commands/view.c
+++ b/src/backend/commands/view.c
@@ -222,6 +222,8 @@ DefineVirtualRelation(const RangeVar *relation, List *tlist, bool replace)
}
else
{
+ Oid relid;
+
/*
* now set the parameters for keys/inheritance etc. All of these are
* uninteresting for views...
@@ -233,13 +235,16 @@ DefineVirtualRelation(const RangeVar *relation, List *tlist, bool replace)
createStmt->options = list_make1(defWithOids(false));
createStmt->oncommit = ONCOMMIT_NOOP;
createStmt->tablespacename = NULL;
+ createStmt->if_not_exists = false;
/*
* finally create the relation (this will error out if there's an
* existing view, so we don't need more code to complain if "replace"
* is false).
*/
- return DefineRelation(createStmt, RELKIND_VIEW);
+ relid = DefineRelation(createStmt, RELKIND_VIEW);
+ Assert(relid != InvalidOid);
+ return relid;
}
}
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index d299310..c28cf37 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -2174,7 +2174,9 @@ OpenIntoRel(QueryDesc *queryDesc)
into->onCommit,
reloptions,
true,
- allowSystemTableMods);
+ allowSystemTableMods,
+ false);
+ Assert(intoRelationId != InvalidOid);
FreeTupleDesc(tupdesc);
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index e770e89..e8ed79d 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2518,6 +2518,7 @@ _copyCreateStmt(CreateStmt *from)
COPY_NODE_FIELD(options);
COPY_SCALAR_FIELD(oncommit);
COPY_STRING_FIELD(tablespacename);
+ COPY_SCALAR_FIELD(if_not_exists);
return newnode;
}
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 5d83727..70b3c62 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1104,6 +1104,7 @@ _equalCreateStmt(CreateStmt *a, CreateStmt *b)
COMPARE_NODE_FIELD(options);
COMPARE_SCALAR_FIELD(oncommit);
COMPARE_STRING_FIELD(tablespacename);
+ COMPARE_SCALAR_FIELD(if_not_exists);
return true;
}
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index e7dae4b..e745201 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -1782,6 +1782,7 @@ _outCreateStmt(StringInfo str, CreateStmt *node)
WRITE_NODE_FIELD(options);
WRITE_ENUM_FIELD(oncommit, OnCommitAction);
WRITE_STRING_FIELD(tablespacename);
+ WRITE_BOOL_FIELD(if_not_exists);
}
static void
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index b793c4d..69248af 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -2212,6 +2212,23 @@ CreateStmt: CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
n->options = $9;
n->oncommit = $10;
n->tablespacename = $11;
+ n->if_not_exists = false;
+ $$ = (Node *)n;
+ }
+ | CREATE OptTemp TABLE IF_P NOT EXISTS qualified_name '('
+ OptTableElementList ')' OptInherit OptWith OnCommitOption
+ OptTableSpace
+ {
+ CreateStmt *n = makeNode(CreateStmt);
+ $7->istemp = $2;
+ n->relation = $7;
+ n->tableElts = $9;
+ n->inhRelations = $11;
+ n->constraints = NIL;
+ n->options = $12;
+ n->oncommit = $13;
+ n->tablespacename = $14;
+ n->if_not_exists = true;
$$ = (Node *)n;
}
| CREATE OptTemp TABLE qualified_name OF any_name
@@ -2227,6 +2244,22 @@ CreateStmt: CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
n->options = $8;
n->oncommit = $9;
n->tablespacename = $10;
+ n->if_not_exists = false;
+ $$ = (Node *)n;
+ }
+ | CREATE OptTemp TABLE IF_P NOT EXISTS qualified_name OF any_name
+ OptTypedTableElementList OptWith OnCommitOption OptTableSpace
+ {
+ CreateStmt *n = makeNode(CreateStmt);
+ n->relation = $7;
+ n->tableElts = $10;
+ n->ofTypename = makeTypeNameFromNameList($9);
+ n->ofTypename->location = @9;
+ n->constraints = NIL;
+ n->options = $11;
+ n->oncommit = $12;
+ n->tablespacename = $13;
+ n->if_not_exists = true;
$$ = (Node *)n;
}
;
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 8960246..1815539 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -513,6 +513,13 @@ standard_ProcessUtility(Node *parsetree,
RELKIND_RELATION);
/*
+ * If "IF NOT EXISTS" was specified and the relation
+ * already exists, do nothing further.
+ */
+ if (relOid == InvalidOid)
+ continue;
+
+ /*
* Let AlterTableCreateToastTable decide if this one
* needs a secondary relation too.
*/
diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h
index 8292273..07db4a3 100644
--- a/src/include/catalog/heap.h
+++ b/src/include/catalog/heap.h
@@ -61,7 +61,8 @@ extern Oid heap_create_with_catalog(const char *relname,
OnCommitAction oncommit,
Datum reloptions,
bool use_user_acl,
- bool allow_system_table_mods);
+ bool allow_system_table_mods,
+ bool if_not_exists);
extern void heap_drop_with_catalog(Oid relid);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index b591073..fec8d3c 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1375,6 +1375,7 @@ typedef struct CreateStmt
List *options; /* options from WITH clause */
OnCommitAction oncommit; /* what do we do at COMMIT? */
char *tablespacename; /* table space to use, or NULL */
+ bool if_not_exists; /* just do nothing if it already exists? */
} CreateStmt;
/* ----------
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index daecabb..6f65885 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -196,7 +196,11 @@ CREATE TABLE array_index_op_test (
i int4[],
t text[]
);
-CREATE TABLE test_tsvector(
+CREATE TABLE IF NOT EXISTS test_tsvector(
t text,
a tsvector
);
+CREATE TABLE IF NOT EXISTS test_tsvector(
+ t text
+);
+NOTICE: relation "test_tsvector" already exists, skipping
diff --git a/src/test/regress/expected/typed_table.out b/src/test/regress/expected/typed_table.out
index e92cdf6..c314f72 100644
--- a/src/test/regress/expected/typed_table.out
+++ b/src/test/regress/expected/typed_table.out
@@ -2,6 +2,8 @@ CREATE TABLE ttable1 OF nothing;
ERROR: type "nothing" does not exist
CREATE TYPE person_type AS (id int, name text);
CREATE TABLE persons OF person_type;
+CREATE TABLE IF NOT EXISTS persons OF person_type;
+NOTICE: relation "persons" already exists, skipping
SELECT * FROM persons;
id | name
----+------
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index e43371e..f491e8c 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -232,8 +232,11 @@ CREATE TABLE array_index_op_test (
t text[]
);
-CREATE TABLE test_tsvector(
+CREATE TABLE IF NOT EXISTS test_tsvector(
t text,
a tsvector
);
+CREATE TABLE IF NOT EXISTS test_tsvector(
+ t text
+);
diff --git a/src/test/regress/sql/typed_table.sql b/src/test/regress/sql/typed_table.sql
index 4e81f1d..4eb92d7 100644
--- a/src/test/regress/sql/typed_table.sql
+++ b/src/test/regress/sql/typed_table.sql
@@ -2,6 +2,7 @@ CREATE TABLE ttable1 OF nothing;
CREATE TYPE person_type AS (id int, name text);
CREATE TABLE persons OF person_type;
+CREATE TABLE IF NOT EXISTS persons OF person_type;
SELECT * FROM persons;
\d persons
--On 1. Mai 2010 23:09:23 -0400 Robert Haas <robertmhaas@gmail.com> wrote:
On Wed, Apr 28, 2010 at 9:15 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
CREATE OR REPLACE is indeed much more complicated. In fact, for
tables, I maintain that you'll need to link with -ldwim to make it
work properly.This may in fact be an appropriate way to handle the case for tables,
given the complexity of their definitions.Patch attached.
I had an initial look at Robert's patch. Patch applies cleanly,
documentation and regression tests included, everything works as expected.
When looking at the functionality there's one thing that strikes me a
little:
bernd@localhost:bernd #*= CREATE TABLE IF NOT EXISTS foo(id int);
ERROR: duplicate key value violates unique constraint
"pg_type_typname_nsp_index"
DETAIL: Key (typname, typnamespace)=(foo, 2200) already exists.
This is what you get from concurrent CINE commands. The typname thingie
might be confusing by unexperienced users, but i think its hard to do
anything about it ?
--
Thanks
Bernd
On Wed, Jul 21, 2010 at 2:53 PM, Bernd Helmle <mailings@oopsware.de> wrote:
--On 1. Mai 2010 23:09:23 -0400 Robert Haas <robertmhaas@gmail.com> wrote:
On Wed, Apr 28, 2010 at 9:15 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
CREATE OR REPLACE is indeed much more complicated. In fact, for
tables, I maintain that you'll need to link with -ldwim to make it
work properly.This may in fact be an appropriate way to handle the case for tables,
given the complexity of their definitions.Patch attached.
I had an initial look at Robert's patch. Patch applies cleanly,
documentation and regression tests included, everything works as expected.
When looking at the functionality there's one thing that strikes me a
little:bernd@localhost:bernd #*= CREATE TABLE IF NOT EXISTS foo(id int);
ERROR: duplicate key value violates unique constraint
"pg_type_typname_nsp_index"
DETAIL: Key (typname, typnamespace)=(foo, 2200) already exists.This is what you get from concurrent CINE commands. The typname thingie
might be confusing by unexperienced users, but i think its hard to do
anything about it ?
I get the same error message from concurrent CREATE TABLE commands
even without CINE...
S1:
rhaas=# begin;
BEGIN
rhaas=# create table foo (id int);
CREATE TABLE
S2:
rhaas=# begin;
BEGIN
rhaas=# create table foo (id int);
<blocks>
S1:
rhaas=# commit;
COMMIT
S2:
ERROR: duplicate key value violates unique constraint
"pg_type_typname_nsp_index"
DETAIL: Key (typname, typnamespace)=(foo, 2200) already exists.
I agree it would be nice to fix this. I'm not sure how hard it is. I
don't think it's the job of this patch. :-)
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
On Wed, 2010-04-28 at 21:15 -0400, Tom Lane wrote:
I still say
that COR rather than CINE semantics would be appropriate for columns.
Viewed from a locking perspective, I would disagree.
COR semantics force a table rewrite, in certain cases. That makes it
hard to predict externally how long the command will run for.
As a DBA, I would want a command that executes without rewrite (if
appropriate) or does nothing.
Predictable behaviour is the most important concern.
That isn't necessarily an argument in favour of CINE, which seems
slightly less clear about what we might expect from that, in my reading
at least.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services
Hello,
At least from a performance point of view CINE should never cause a table rewrite, it should either execute as a plain CREATE or as "nothing". I don't mind if the CINE fails if the column already exists but with a different definition, so maybe it could be worded differently to make it clearer what you get? How about something like:
"ALTER TABLE foo ADD OR MATCH COLUMN bar INTEGER"
a) doesn't exist => create
b) exists and matches => nothing
c) exists and doesn't match => error
if COR semantics should ever be implmented they could be
"ALTER TABLE foo ADD OR REPLACE COLUMN bar INTEGER"
a) doesn't exist => create
b) exists and matches => nothing
c) exists and doesn't match => replace
However, I don't want it to fail unless there's an explicit conflict, because I tend to modify the columns later:
"ALTER TABLE foo ADD COLUMN bar INTEGER"
"ALTER TABLE foo ALTER COLUMN bar SET DEFAULT 0"
"ALTER TABLE foo ALTER COLUMN bar SET NOT NULL"
"ALTER TABLE foo ADD OR MATCH COLUMN bar INTEGER" <-- succeed or fail?
Personally, I'm only interested to match on TYPE so possibly:
"ALTER TABLE foo ADD OR MATCH TYPE COLUMN bar INTEGER" <-- succeed
"ALTER TABLE foo ADD OR MATCH [ALL] COLUMN bar INTEGER" <-- fail
To be honest, I think this becomes more complicated than a CINE, but as I felt that got a rather lukewarm reception maybe this sounds better. The syntax leaves it open for COR later, and the matching code should be useful to determine if the COR actually needs to do a REPLACE. Opinions?
Regards,
Kjell Rune
--- Den tor 2010-07-22 skrev Simon Riggs <simon@2ndQuadrant.com>:Show quoted text
Fra: Simon Riggs <simon@2ndQuadrant.com>
Emne: Re: [HACKERS] Add column if not exists (CINE)
Til: "Tom Lane" <tgl@sss.pgh.pa.us>
Kopi: "Robert Haas" <robertmhaas@gmail.com>, "Heikki Linnakangas" <heikki.linnakangas@enterprisedb.com>, "Andrew Dunstan" <andrew@dunslane.net>, "Takahiro Itagaki" <itagaki.takahiro@oss.ntt.co.jp>, "Kjell Rune Skaaraas" <kjella79@yahoo.no>, pgsql-hackers@postgresql.org
Dato: Torsdag 22. juli 2010 02.43
On Wed, 2010-04-28 at 21:15 -0400,
Tom Lane wrote:I still say
that COR rather than CINE semantics would beappropriate for columns.
Viewed from a locking perspective, I would disagree.
COR semantics force a table rewrite, in certain cases. That
makes it
hard to predict externally how long the command will run
for.As a DBA, I would want a command that executes without
rewrite (if
appropriate) or does nothing.Predictable behaviour is the most important concern.
That isn't necessarily an argument in favour of CINE, which
seems
slightly less clear about what we might expect from that,
in my reading
at least.--
Simon Riggs
www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and
Services
--On 21. Juli 2010 17:16:13 -0400 Robert Haas <robertmhaas@gmail.com> wrote:
I get the same error message from concurrent CREATE TABLE commands
even without CINE...S1:
rhaas=# begin;
BEGIN
rhaas=# create table foo (id int);
CREATE TABLES2:
rhaas=# begin;
BEGIN
rhaas=# create table foo (id int);
<blocks>S1:
rhaas=# commit;
COMMITS2:
ERROR: duplicate key value violates unique constraint
"pg_type_typname_nsp_index"
DETAIL: Key (typname, typnamespace)=(foo, 2200) already exists.
Funny, never realized that before, but you're right.
I agree it would be nice to fix this. I'm not sure how hard it is. I
don't think it's the job of this patch. :-)
Yes, i agree. I would like to mark this patch "Ready for Committer", if
that's okay for you (since you are a committer you might want to commit it
yourself). Given that there's still some discussion in progress, i'm not
sure about it, however. The patch itself looks fine to me and I'm traveling
this weekend, so i don't want to hold it off as long as necessary.
--
Thanks
Bernd
Import Notes
Resolved by subject fallback
On Fri, Jul 23, 2010 at 2:46 AM, Bernd Helmle <mailings@oopsware.de> wrote:
--On 21. Juli 2010 17:16:13 -0400 Robert Haas <robertmhaas@gmail.com> wrote:
I get the same error message from concurrent CREATE TABLE commands
even without CINE...S1:
rhaas=# begin;
BEGIN
rhaas=# create table foo (id int);
CREATE TABLES2:
rhaas=# begin;
BEGIN
rhaas=# create table foo (id int);
<blocks>S1:
rhaas=# commit;
COMMITS2:
ERROR: duplicate key value violates unique constraint
"pg_type_typname_nsp_index"
DETAIL: Key (typname, typnamespace)=(foo, 2200) already exists.Funny, never realized that before, but you're right.
I agree it would be nice to fix this. I'm not sure how hard it is. I
don't think it's the job of this patch. :-)Yes, i agree. I would like to mark this patch "Ready for Committer", if
that's okay for you (since you are a committer you might want to commit it
yourself). Given that there's still some discussion in progress, i'm not
sure about it, however. The patch itself looks fine to me and I'm traveling
this weekend, so i don't want to hold it off as long as necessary.
As far as I can see, the other emails were regarding adding columns,
whereas this patch is about creating tables. So I think it's OK...
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
On Fri, Jul 23, 2010 at 2:46 AM, Bernd Helmle <mailings@oopsware.de> wrote:
Yes, i agree. I would like to mark this patch "Ready for Committer", if
that's okay for you (since you are a committer you might want to commit it
yourself).
I see that it is so marked, so, committed, with a minor correction to
my original docs.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
On Thu, Jul 22, 2010 at 4:34 PM, Kjell Rune Skaaraas <kjella79@yahoo.no> wrote:
At least from a performance point of view CINE should never cause a table rewrite, it should either execute as a plain CREATE or as "nothing". I don't mind if the CINE fails if the column already exists but with a different definition, so maybe it could be worded differently to make it clearer what you get?
That's what I want, too. The people saying we should implement COR
for columns seem to be, by and large, people who have never wished for
this feature and have no particular use case for either one. I have
stated my use case in the past, but it has been dismissed as stupid or
contrived. I can live with the possibility that I'm dumb, but, for
the record, I'm not making this up.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company