Tom Lane írta:
> I wrote:
>
>> I see another problem with this patch: the code added to
>> ATExecDropColumn is a crude hack. It doesn't work anyway since this is
>> not the only possible way for columns to be dropped (another one that
>> comes to mind immediately is DROP TYPE ... CASCADE). The only correct
>> way to handle things is to let the dependency mechanism do it.
>>

I will try that.

> Actually, the whole question of dependencies for generated columns
> probably needs some thought. What should happen if a function or
> operator used in a GENERATED expression gets dropped? The result
> for a normal column's default expression is that the default expression
> goes away, but the column is still there. I suspect we don't want
> that for a GENERATED column --- it would then be effectively a plain
> column.
>

No, I would want the DROP FUNCTION to be cancelled if used in
a GENERATED, but a DROP ... CASCADE would drop it, too.
So, DEPENDENCY_NORMAL will keep the referencing object
but DEPENDENCY_AUTO would drop it too if the referenced
object is dropped?

> Along the same lines, is ALTER COLUMN DROP DEFAULT a legal operation
> on a generated column? What about just replacing the expression with
> ALTER COLUMN SET DEFAULT?
>

Neither of these options are legal for GENERATED columns,
AFAIK I prohibited them already.

> Before you get too excited about making generated columns disappear
> automatically in all these cases, consider that dropping a column
> is not something to be done lightly --- it might contain irreplaceable
> data.
>

The standard says that the GENERATED column should be
dropped silently if either of the referenced columns is dropped.
I haven't seen anything about the expression, though.

> On second thought maybe the right approach is just to allow the default
> expression to be dropped the same as it would be for an ordinary column,
> and to make sure that if a GENERATED column doesn't (currently) have a
> default, it is treated the same as an ordinary column.
>
> This leads to the further thought that maybe GENERATED is not a property
> of a column at all, but of its default (ie, it should be stored in
> pg_attrdef not pg_attribute, which would certainly make the patch less
> messy). AFAICS plain GENERATED merely indicates that the default
> expression can depend on other columns, which is certainly a property
> of the default --- you could imagine ALTER COLUMN SET DEFAULT GENERATED
> AS ... to make a formerly plain column into a GENERATED one. I'm not
> entirely sure about ALWAYS though.
>

The standard says somewhere that GENERATED columns
can only be added to and dropped from a table.

My observation is: I deleted my hack from ATExecDropColumn()
and now I cannot drop the referenced column without CASCADE.
The comment in StoreAttrDefault() says the objects in the expression
will have dependencies registered. I guess "objects" also means functions?
This way, if I do explicit recordDependencyOn(, DEPENDENCY_AUTO)
on referenced columns then the standard requirement will
be satisfied, i.e. dropping columns will drop GENERATED columns
silently that reference the said column but . Am I right? Or how about using
recordDependencyOnSingleRelExpr(... , DEP_NORMAL, DEP_AUTO ) ?

> regards, tom lane
>
>


--
----------------------------------
Zoltán Böszörményi
Cybertec Geschwinde & Schönig GmbH
http://www.postgresql.at/


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Zoltan Boszormenyi <zb@cybertec.at> writes:
>> Before you get too excited about making generated columns disappear
>> automatically in all these cases, consider that dropping a column
>> is not something to be done lightly --- it might contain irreplaceable
>> data.

> The standard says that the GENERATED column should be
> dropped silently if either of the referenced columns is dropped.

[ itch... ] I think a pretty good case could be made for ignoring that
provision, on the grounds that it's a foot-gun, and that it's not very
important to follow the standard slavishly on this point because it's
hard to conceive of any application actually relying on that behavior.

You could probably implement the auto-drop behavior with some combination
of (a) AUTO rather than NORMAL dependencies from the default expression
to the stuff it depends on and (b) INTERNAL rather than AUTO dependency
from the default expression to its column. But I really question
whether this is a good idea.

> The standard says somewhere that GENERATED columns
> can only be added to and dropped from a table.

This argument carries no weight at all --- there is plenty of stuff in
PG that is an extension of the capabilities listed in the spec.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Tom Lane írta:
> Zoltan Boszormenyi <zb@cybertec.at> writes:
>
>>> Before you get too excited about making generated columns disappear
>>> automatically in all these cases, consider that dropping a column
>>> is not something to be done lightly --- it might contain irreplaceable
>>> data.
>>>
>
>
>> The standard says that the GENERATED column should be
>> dropped silently if either of the referenced columns is dropped.
>>
>
> [ itch... ] I think a pretty good case could be made for ignoring that
> provision, on the grounds that it's a foot-gun, and that it's not very
> important to follow the standard slavishly on this point because it's
> hard to conceive of any application actually relying on that behavior.
>
> You could probably implement the auto-drop behavior with some combination
> of (a) AUTO rather than NORMAL dependencies from the default expression
> to the stuff it depends on and (b) INTERNAL rather than AUTO dependency
> from the default expression to its column. But I really question
> whether this is a good idea.
>

So, all dependency should be NORMAL to require
manual CASCADE to avoid accidental data loss.

I have two questions about the dependency system.

1. Is there a built-in defense to avoid circular dependencies?

2. If I register dependencies between column, is there a way
to retrieve all table/column type dependencies for a depender column?

What I would like to achieve is to lift the limit that
a GENERATED column cannot reference another one.
Only self-referencing should be disallowed.

>> The standard says somewhere that GENERATED columns
>> can only be added to and dropped from a table.
>>
>
> This argument carries no weight at all --- there is plenty of stuff in
> PG that is an extension of the capabilities listed in the spec.
>

Point taken. So, just like with SET / DROP IDENTITY,
I should implement SET GENERATED ALWAYS
and DROP GENERATED.

> regards, tom lane
>
>


--
----------------------------------
Zoltán Böszörményi
Cybertec Geschwinde & Schönig GmbH
http://www.postgresql.at/


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Zoltan Boszormenyi <zb@cybertec.at> writes:
> I have two questions about the dependency system.

> 1. Is there a built-in defense to avoid circular dependencies?

It doesn't have a problem with them, if that's what you mean.

> 2. If I register dependencies between column, is there a way
> to retrieve all table/column type dependencies for a depender column?

You can scan pg_depend.

> What I would like to achieve is to lift the limit that
> a GENERATED column cannot reference another one.

I would counsel not doing that, mainly because then you will have to
solve an evaluation-order problem at runtime.

> Point taken. So, just like with SET / DROP IDENTITY,
> I should implement SET GENERATED ALWAYS
> and DROP GENERATED.

If you think of it as a property of the default expression, then DROP
DEFAULT covers both cases, you don't need DROP GENERATED...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Tom Lane írta:
> Zoltan Boszormenyi <zb@cybertec.at> writes:
>
>> I have two questions about the dependency system.
>>
>
>
>> 1. Is there a built-in defense to avoid circular dependencies?
>>
>
> It doesn't have a problem with them, if that's what you mean.
>
>
>> 2. If I register dependencies between column, is there a way
>> to retrieve all table/column type dependencies for a depender column?
>>
>
> You can scan pg_depend.
>
>
>> What I would like to achieve is to lift the limit that
>> a GENERATED column cannot reference another one.
>>
>
> I would counsel not doing that, mainly because then you will have to
> solve an evaluation-order problem at runtime.
>

OK.

>> Point taken. So, just like with SET / DROP IDENTITY,
>> I should implement SET GENERATED ALWAYS
>> and DROP GENERATED.
>>
>
> If you think of it as a property of the default expression, then DROP
> DEFAULT covers both cases, you don't need DROP GENERATED...
>

So, I should allow DROP DEFAULT, implement
SET DEFAULT GENERATED ALWAYS AS
and modify the catalog so the GENERATED property
is part of pg_attrdef. What about IDENTITY?
Should it also be part of pg_attrdef? There are two ways
to implement it: have or don't have a notion of it.
The latter would treat GENERATED BY DEFAULT AS IDENTITY
the same as SERIAL.

> regards, tom lane
>

--
----------------------------------
Zoltán Böszörményi
Cybertec Geschwinde & Schönig GmbH
http://www.postgresql.at/


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Zoltan Boszormenyi <zb@cybertec.at> writes:
> So, I should allow DROP DEFAULT, implement
> SET DEFAULT GENERATED ALWAYS AS
> and modify the catalog so the GENERATED property
> is part of pg_attrdef.

Sounds good.

> What about IDENTITY?
> Should it also be part of pg_attrdef? There are two ways
> to implement it: have or don't have a notion of it.
> The latter would treat GENERATED BY DEFAULT AS IDENTITY
> the same as SERIAL.

Is there any good reason to distinguish the two?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Tom Lane írta:
> Zoltan Boszormenyi <zb@cybertec.at> writes:
>
>> So, I should allow DROP DEFAULT, implement
>> SET DEFAULT GENERATED ALWAYS AS
>> and modify the catalog so the GENERATED property
>> is part of pg_attrdef.
>>
>
> Sounds good.
>
>
>> What about IDENTITY?
>> Should it also be part of pg_attrdef? There are two ways
>> to implement it: have or don't have a notion of it.
>> The latter would treat GENERATED BY DEFAULT AS IDENTITY
>> the same as SERIAL.
>>
>
> Is there any good reason to distinguish the two?
>

Yes. Plain SERIALs can be updated with given values
whereas IDENTITY columns cannot. And there is the
difference between GENERATED and GENERATED IDENTITY:
GENERATED columns can updated with DEFAULT
values, IDENTITY columns cannot. I strictly have to
distinguish IDENTITY from both GENERATED and
plain SERIALs.

> regards, tom lane
>

--
----------------------------------
Zoltán Böszörményi
Cybertec Geschwinde & Schönig GmbH
http://www.postgresql.at/


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Zoltan Boszormenyi <zb@cybertec.at> writes:
> Tom Lane írta:
>>> The latter would treat GENERATED BY DEFAULT AS IDENTITY
>>> the same as SERIAL.

>> Is there any good reason to distinguish the two?

> Yes. Plain SERIALs can be updated with given values
> whereas IDENTITY columns cannot.

Really? How is pg_dump going to deal with that?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Tom Lane írta:
> Zoltan Boszormenyi <zb@cybertec.at> writes:
>
>> Tom Lane írta:
>>
>>>> The latter would treat GENERATED BY DEFAULT AS IDENTITY
>>>> the same as SERIAL.
>>>>
>
>
>>> Is there any good reason to distinguish the two?
>>>
>
>
>> Yes. Plain SERIALs can be updated with given values
>> whereas IDENTITY columns cannot.
>>
>
> Really? How is pg_dump going to deal with that?
>
> regards, tom lane
>

It emits ALTER TABLE ... SET GENERATED AS IDENTITY
after ALTER SEQUENCE OWNED BY and if any of the
columns is IDENTITY or GENERATED then it emits
COPY OVERRIDING SYSTEM VALUE in my patch already.

--
----------------------------------
Zoltán Böszörményi
Cybertec Geschwinde & Schönig GmbH
http://www.postgresql.at/


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Zoltan Boszormenyi <zb@cybertec.at> writes:
> Tom Lane írta:
>> Zoltan Boszormenyi <zb@cybertec.at> writes:
>>> Yes. Plain SERIALs can be updated with given values
>>> whereas IDENTITY columns cannot.
>>
>> Really? How is pg_dump going to deal with that?

> It emits ALTER TABLE ... SET GENERATED AS IDENTITY
> after ALTER SEQUENCE OWNED BY and if any of the
> columns is IDENTITY or GENERATED then it emits
> COPY OVERRIDING SYSTEM VALUE in my patch already.

And you fail to see the irony in that? You might as well just
admit that it's OK to update an identity column.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly