Thread: alter + preserving dependencies
I have a client who is looking for a way to be able to alter objects without having to recreate (say, from a dump) all the objects in a possibly large dependency tree rooted at the object. Of course, if the alteration invalidates the dependency, than this operation should fail, but adding a column to a view or table (which is our most common case) should arguably be a benign operation that shouldn't fail because of dependencies. I couldn't see anything in the TODO list that seemed appropriate, in a quick scan. Thoughts? cheers andrew
Andrew Dunstan wrote: > > I have a client who is looking for a way to be able to alter objects > without having to recreate (say, from a dump) all the objects in a > possibly large dependency tree rooted at the object. Of course, if the > alteration invalidates the dependency, than this operation should fail, > but adding a column to a view or table (which is our most common case) > should arguably be a benign operation that shouldn't fail because of > dependencies. > > I couldn't see anything in the TODO list that seemed appropriate, in a > quick scan. I don't follow you. I can currently add a column, without breaking either foriegn keys or inheritance. What's the problem? --Josh
Josh Berkus wrote: > Andrew Dunstan wrote: >> >> I have a client who is looking for a way to be able to alter objects >> without having to recreate (say, from a dump) all the objects in a >> possibly large dependency tree rooted at the object. Of course, if >> the alteration invalidates the dependency, than this operation should >> fail, but adding a column to a view or table (which is our most >> common case) should arguably be a benign operation that shouldn't >> fail because of dependencies. >> >> I couldn't see anything in the TODO list that seemed appropriate, in >> a quick scan. > > I don't follow you. I can currently add a column, without breaking > either foriegn keys or inheritance. What's the problem? > > not for a view at least. cheers andrew
Andrew Dunstan <andrew@dunslane.net> writes:
> Josh Berkus wrote:
>> I don't follow you. I can currently add a column, without breaking
>> either foriegn keys or inheritance. What's the problem?
> not for a view at least.
Yeah, the restrictions on replacing a view definition date from before
we had any fancy ALTER TABLE stuff. They could probably use a re-visit.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, Le 6 mai 08 à 19:44, Tom Lane a écrit : > Andrew Dunstan <andrew@dunslane.net> writes: >> Josh Berkus wrote: >>> I don't follow you. I can currently add a column, without breaking >>> either foriegn keys or inheritance. What's the problem? > >> not for a view at least. > > Yeah, the restrictions on replacing a view definition date from before > we had any fancy ALTER TABLE stuff. They could probably use a re- > visit. Could we add some other VIEWs features while revisiting? We had some question on #postgresqlfr about updatable views and the rewrite rule system, which I could chat about with Jan Wieck on #postgresql too. The problem we had was related to DEFAULT versus NULL handling from an insert or update RULE and how to avoid having an absent column rewritten as NULL instead of DEFAULT. Could we consider ALTER VIEW ALTER COLUMN ... SET DEFAULT ...;? Bonus question: why is the rewriter unable to distinguish whether NULL comes from the user or comes from the column was not part of the user query? Regards, - -- dim -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (Darwin) iEYEARECAAYFAkghQRIACgkQlBXRlnbh1bnWAQCeNF4RJwWLcGmmPPE8eUGn3/Yi 6NEAn1YOQ3Bz7L+tD01rQqqeyNt7djwA =MXD8 -----END PGP SIGNATURE-----
Dimitri Fontaine <dim@hi-media.com> writes:
> Could we consider ALTER VIEW ALTER COLUMN ... SET DEFAULT ...;?
We could if we hadn't already done it five or so years ago.
Or am I missing what you need here?
> Bonus question: why is the rewriter unable to distinguish whether NULL
> comes from the user or comes from the column was not part of the user
> query?
Not following this either ...
regards, tom lane
Le 7 mai 08 à 07:52, Tom Lane a écrit :
> Dimitri Fontaine <dim@hi-media.com> writes:
>> Could we consider ALTER VIEW ALTER COLUMN ... SET DEFAULT ...;?
>
> We could if we hadn't already done it five or so years ago.
> Or am I missing what you need here?
My 8.3.1 installation psql \h only gives me:
Syntax:
ALTER VIEW name RENAME TO newname
Ok, I've been quite bad at explaining the case, let's retry.
CREATE TABLE t(id serial, name text not null, a_count not null default
0, option);
CREATE VIEW v AS SELECT * FROM t;
Now we add a on INSERT RULE such as INSERT INTO v will insert to t.
And we want the RULE to be able to use the a_count DEFAULT when user
didn't give a_count column in its INSERT order: INSERT INTO v(name) VALUES ('foo');
The RULE kicks in and rewrite the INSERT to target t, and a_count not
being given explicitely any value will get rewritten to NULL, not to
DEFAULT, and the INSERT subsequently fails.
In order to be able to avoid this, AFAIK, you need to poke into
catalogs to retrieve the DEFAULT value, at the RULE definition level...
>> Bonus question: why is the rewriter unable to distinguish whether
>> NULL
>> comes from the user or comes from the column was not part of the user
>> query?
>
> Not following this either ...
...and the RULE definition has no chance to figure out by itself if
the user omit to give any value for our not null column, or
explicitely gave a NULL, in which case we'd better not rewrite it to
DEFAULT...
Hope I made the case clear by now, thx for your interest,
--
dim
Le mercredi 07 mai 2008, Dimitri Fontaine a écrit : > Ok, I've been quite bad at explaining the case, let's retry. Thanks a lot to the OP on #postgresqlfr (nickname renchap), who is providing attached test case, where you'll see how we hacked our way into information_schema to have the insert rule insert DEFAULT instead of NULL. Of course the OP loses here the option to error out on NULL input, but the application is being ported from MySQL so he's not losing any feature here. Regards, -- dim
Attachment
Dimitri Fontaine wrote: > Le 7 mai 08 à 07:52, Tom Lane a écrit : > >> Dimitri Fontaine <dim@hi-media.com> writes: >>> Could we consider ALTER VIEW ALTER COLUMN ... SET DEFAULT ...;? >> >> We could if we hadn't already done it five or so years ago. >> Or am I missing what you need here? > > My 8.3.1 installation psql \h only gives me: > Syntax: > ALTER VIEW name RENAME TO newname Ah, you use ALTER TABLE: ALTER TABLE my_view ALTER COLUMN view_column DEFAULT expr; -- Richard Huxton Archonet Ltd
Dimitri Fontaine <dim@hi-media.com> writes:
> My 8.3.1 installation psql \h only gives me:
> Syntax:
> ALTER VIEW name RENAME TO newname
You're not the first person to think that ALTER VIEW covers everything
that can be done to a view.
I'm starting to think that we should just make ALTER VIEW be an alias
for ALTER TABLE (rather than a separate node type as now), and then list
in the ALTER VIEW reference page all of the ALTER TABLE variants that
will work on views.
regards, tom lane
>>> Tom Lane <tgl@sss.pgh.pa.us> wrote: > Dimitri Fontaine <dim@hi-media.com> writes: >> My 8.3.1 installation psql \h only gives me: >> Syntax: >> ALTER VIEW name RENAME TO newname > > You're not the first person to think that ALTER VIEW covers everything > that can be done to a view. > > I'm starting to think that we should just make ALTER VIEW be an alias > for ALTER TABLE (rather than a separate node type as now), and then list > in the ALTER VIEW reference page all of the ALTER TABLE variants that > will work on views. From a DBA perspective, it reduces the risk of error if ALTER TABLE only affects tables, not views, and ALTER VIEW only affects views, not tables. -Kevin
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Le 7 mai 08 à 16:26, Tom Lane a écrit : > I'm starting to think that we should just make ALTER VIEW be an alias > for ALTER TABLE (rather than a separate node type as now), and then > list > in the ALTER VIEW reference page all of the ALTER TABLE variants that > will work on views. Quite obviously, +1. - -- dim -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (Darwin) iEYEARECAAYFAkgh7MMACgkQlBXRlnbh1bnhzACeM9JSHFd/yBjIO+fFLz+SAFC6 eCgAn05/5y1E7eA/qz27ZsBY5+vxHvsn =zroi -----END PGP SIGNATURE-----