SEO

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

-----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)

iEYEARECAAYFAkghQRIACgkQlBXRlnbh1bnWAQCeNF4RJwWLcG mmPPE8eUGn3/Yi
6NEAn1YOQ3Bz7L+tD01rQqqeyNt7djwA
=MXD8
-----END PGP SIGNATURE-----

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQBIIXDllBXRlnbh1bkRAp/aAJ0aqpt5Hb6gBLe+P1OzpEbAfgZQOwCeJ4cM
7Ux//9j0Vr2EhEkBKDScPy0=
=EVf8
-----END PGP SIGNATURE-----

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers