Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?

ash,

* ash (ash@commandprompt.com) wrote:

This came up recently on general list (and I've just hit the same issue today):
/messages/by-id/CAB7nPqTLmMn1LTb5WE0v0dO57iP0U73yKwzbZytAXDF1CAWLZg@mail.gmail.com

Why couldn't postgres re-create the dependent views automatically? I
mean it knows which views depend on the altered column and keeps the
view definition, no?

Might be pretty complicated in the end..

Would a patch likely to be accepted? How hard do you feel this might be
to implement? Any caveat that defeats the purpose of such feature?

It'd need to be explicitly requested, eg a 'CASCADE' option.

Thanks,

Stephen

On Mon, May 26, 2014 at 06:25:09PM +0400, ash wrote:

Hi Hackers,

This came up recently on general list (and I've just hit the same issue today):
/messages/by-id/CAB7nPqTLmMn1LTb5WE0v0dO57iP0U73yKwzbZytAXDF1CAWLZg@mail.gmail.com

Why couldn't postgres re-create the dependent views automatically? I
mean it knows which views depend on the altered column and keeps the
view definition, no?

Also worth considering: functions which take any part of the view as a
parameter.

Not, IMHO, worth considering, are functions like this:

CREATE OR REPLACE FUNCTION make_task_impossible_for_alex()
RETURNS int8
LANGUAGE plpgsql
AS $$
DECLARE
foo text[] := ARRAY['list','of','views','here'];
BEGIN
EXECUTE 'SELECT COUNT(*) FROM %', foo[floor(random()*array_upper(foo,1))];
END;
$$;

That counts pretty strictly as pilot error, not least because it makes
things like you want to write not just hard, but impossible.

Would a patch likely to be accepted? How hard do you feel this might be
to implement?

In the general case, impossible. In most sane cases, mostly a matter
of chasing down dependencies, which is harder than it first appears,
as anyone who's worked on that part of pg_dump can tell you.

Any caveat

Locking. Given that, you'd want this behavior only with CASCADE, per
Stephen's response.

that defeats the purpose of such feature?

Probably not. I'd certainly like to have the feature :)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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

David Fetter <david@fetter.org> writes:

On Mon, May 26, 2014 at 06:25:09PM +0400, ash wrote:

Hi Hackers,

This came up recently on general list (and I've just hit the same issue today):
/messages/by-id/CAB7nPqTLmMn1LTb5WE0v0dO57iP0U73yKwzbZytAXDF1CAWLZg@mail.gmail.com

Why couldn't postgres re-create the dependent views automatically? I
mean it knows which views depend on the altered column and keeps the
view definition, no?

Also worth considering: functions which take any part of the view as a
parameter.

Sorry, I don't get it: do you suggest we should re-create dependent
functions too?

I don't think that's feasible, but there is certainly a use case for
silently re-defining the views together with alteration of the joined
table.

--
Alex

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

On Tue, May 27, 2014 at 12:37:32AM +0400, ash wrote:

David Fetter <david@fetter.org> writes:

On Mon, May 26, 2014 at 06:25:09PM +0400, ash wrote:

Hi Hackers,

This came up recently on general list (and I've just hit the same
issue today):
/messages/by-id/CAB7nPqTLmMn1LTb5WE0v0dO57iP0U73yKwzbZytAXDF1CAWLZg@mail.gmail.com

Why couldn't postgres re-create the dependent views
automatically? I mean it knows which views depend on the altered
column and keeps the view definition, no?

Also worth considering: functions which take any part of the view
as a parameter.

Sorry, I don't get it: do you suggest we should re-create dependent
functions too?

I'd throw an error in cases where such functions had an obvious and
deterministic dependency on the views, ideally having gone through all
such functions first and enumerated them in the error message.

I don't think that's feasible, but there is certainly a use case for
silently re-defining the views together with alteration of the joined
table.

Indeed.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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

David Fetter <david@fetter.org> writes:

Also worth considering: functions which take any part of the view
as a parameter.

Sorry, I don't get it: do you suggest we should re-create dependent
functions too?

I'd throw an error in cases where such functions had an obvious and
deterministic dependency on the views, ideally having gone through all
such functions first and enumerated them in the error message.

Then it would also make sense to start with checking function dependency
on the tables themselves, not only the joining views:

psql=> CREATE TABLE t(id INT);
CREATE TABLE
psql=> CREATE FUNCTION func1() RETURNS SETOF INT AS $$ SELECT id FROM t; $$ LANGUAGE SQL;
CREATE FUNCTION
psql=> ALTER TABLE t ALTER COLUMN id TYPE BIGINT;
ALTER TABLE
-- Would complain on func1 right away

psql=> SELECT func1();
ERROR: return type mismatch in function declared to return integer
DETAIL: Actual return type is bigint.
CONTEXT: SQL function "func1" during startup

psql=> CREATE FUNCTION func2() RETURNS SETOF INT AS $$ SELECT id FROM t; $$ LANGUAGE SQL;
ERROR: return type mismatch in function declared to return integer
DETAIL: Actual return type is bigint.
CONTEXT: SQL function "func2"

--
Alex

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

On Mon, May 26, 2014 at 10:39 AM, Stephen Frost <sfrost@snowman.net> wrote:

* ash (ash@commandprompt.com) wrote:

This came up recently on general list (and I've just hit the same issue today):
/messages/by-id/CAB7nPqTLmMn1LTb5WE0v0dO57iP0U73yKwzbZytAXDF1CAWLZg@mail.gmail.com

Why couldn't postgres re-create the dependent views automatically? I
mean it knows which views depend on the altered column and keeps the
view definition, no?

Might be pretty complicated in the end..

Would a patch likely to be accepted? How hard do you feel this might be
to implement? Any caveat that defeats the purpose of such feature?

It'd need to be explicitly requested, eg a 'CASCADE' option.

Why? Would any sane person NOT want this behavior?

I think the question here is whether there's any way to make this work
at all, not whether we'd want it if we could get it. Consider:

CREATE OR REPLACE VIEW v AS SELECT a + 0 FROM t;

If we drop the view, change the column type of t.a, and re-execute the
view, + might resolve to a different operator than before (or no
operator, at all). Furthermore, the operator to which it resolves
will depend on the search path at the time the CREATE OR REPLACE VIEW
command is executed.

Now, consider the situation in which we want to achieve the same
result without having to drop and recreate v. When the column type of
t.a is changed, we can use the dependencies to figure out that v might
be impacted. We can trace through the rewrite rule to find out where
column t.a is referenced. And ... then what? All we know about t.a
is that we're applying some operator to it, which is specified by OID.
The rewrite rule doesn't tell us the actual *name* by which the
operator was referenced in the original view text, nor does it tell us
the search path that was in effect at that time. If it did, we could
pick the same operator for + that would have been used had t.a been of
the new type originally, but as it is, we can't.

Now maybe there are options other than trying to reproduce what the
original CREATE OR REPLACE statement would have done against the new
type. For example, we could look through views that depend on t.a and
rewrite each reference to that column to t.a::oldtype. This might
lead to odd results with multiple nested casts and generally funny
behavior if the column is re-typed multiple times; but maybe there's
some way to fix that. Also, it might not really be the semantics you
want if you were hoping the type update would truly cascade. But it
might still be better than a sharp stick in the eye, which is kinda
what we offer today.

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

Alexander Shulgin wrote

Hi Hackers,

This came up recently on general list (and I've just hit the same issue
today):
http://www.postgresql.org/message-id/

CAB7nPqTLmMn1LTb5WE0v0dO57iP0U73yKwzbZytAXDF1CAWLZg@.gmail

Why couldn't postgres re-create the dependent views automatically? I
mean it knows which views depend on the altered column and keeps the
view definition, no?

Would a patch likely to be accepted? How hard do you feel this might be
to implement? Any caveat that defeats the purpose of such feature?

Thanks.

Would it be possible to handle the specific case of varchar(n) to
varchar/text by just ignoring the error?

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Re-create-dependent-views-on-ALTER-TABLE-ALTER-COLUMN-TYPE-tp5804972p5805191.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

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

Robert Haas <robertmhaas@gmail.com> writes:

It'd need to be explicitly requested, eg a 'CASCADE' option.

Why? Would any sane person NOT want this behavior?

I think the question here is whether there's any way to make this work
at all, not whether we'd want it if we could get it. Consider:

CREATE OR REPLACE VIEW v AS SELECT a + 0 FROM t;

If we drop the view, change the column type of t.a, and re-execute the
view, + might resolve to a different operator than before (or no
operator, at all). Furthermore, the operator to which it resolves
will depend on the search path at the time the CREATE OR REPLACE VIEW
command is executed.

Now, consider the situation in which we want to achieve the same
result without having to drop and recreate v. When the column type of
t.a is changed, we can use the dependencies to figure out that v might
be impacted. We can trace through the rewrite rule to find out where
column t.a is referenced. And ... then what? All we know about t.a
is that we're applying some operator to it, which is specified by OID.
The rewrite rule doesn't tell us the actual *name* by which the
operator was referenced in the original view text, nor does it tell us
the search path that was in effect at that time. If it did, we could
pick the same operator for + that would have been used had t.a been of
the new type originally, but as it is, we can't.

This could be a showstopper indeed. We can look up view def in pg_views
view, but it doesn't include any schema references unless they were
explicit in the CREATE VIEW statement.

On the other hand, pg_dump *can* work around this: if you dump a view
that has been defined when a specific search_path was in effect, you'll
get correct definition in the schema dump.

So why can't we try to learn from pg_dump?

Now maybe there are options other than trying to reproduce what the
original CREATE OR REPLACE statement would have done against the new
type. For example, we could look through views that depend on t.a and
rewrite each reference to that column to t.a::oldtype. This might
lead to odd results with multiple nested casts and generally funny
behavior if the column is re-typed multiple times; but maybe there's
some way to fix that. Also, it might not really be the semantics you
want if you were hoping the type update would truly cascade. But it
might still be better than a sharp stick in the eye, which is kinda
what we offer today.

No, casting back to oldtype totally defeats the purpose, at least for my
usecase.

--
Alex

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

David G Johnston <david.g.johnston@gmail.com> writes:

Would it be possible to handle the specific case of varchar(n) to
varchar/text by just ignoring the error?

Simply for the reference, my case is INT to BIGINT.

--
Alex

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

On Tue, May 27, 2014 at 11:20 PM, ash <ash@commandprompt.com> wrote:

Now, consider the situation in which we want to achieve the same
result without having to drop and recreate v. When the column type of
t.a is changed, we can use the dependencies to figure out that v might
be impacted. We can trace through the rewrite rule to find out where
column t.a is referenced. And ... then what? All we know about t.a
is that we're applying some operator to it, which is specified by OID.
The rewrite rule doesn't tell us the actual *name* by which the
operator was referenced in the original view text, nor does it tell us
the search path that was in effect at that time. If it did, we could
pick the same operator for + that would have been used had t.a been of
the new type originally, but as it is, we can't.

This could be a showstopper indeed. We can look up view def in pg_views
view, but it doesn't include any schema references unless they were
explicit in the CREATE VIEW statement.

On the other hand, pg_dump *can* work around this: if you dump a view
that has been defined when a specific search_path was in effect, you'll
get correct definition in the schema dump.

So why can't we try to learn from pg_dump?

Well, pg_dump is trying to do something different than what you're
trying to do here. pg_dump wants to make sure that the view, when fed
back into psql, creates the same view that exists now, regardless of
whether that's what the user created originally. For example, if a
view is created referring to table foo, and table foo is later renamed
to bar, then pg_dump wants to (and does) dump a statement referring to
bar, not foo - even if there's a *new* table called foo against which
the view could have been defined. Similarly, pg_dump will
schema-qualify functions and operators, or not, based on whether
that's necessary to reference the exact same operators that were
selected when the original CREATE VIEW command was run, regardless of
whether the original references were schema-qualified. None of that
involves answering hypothetical questions; but what you want to do
does, and that I think is the problem in a nutshell.

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

* Robert Haas (robertmhaas@gmail.com) wrote:

On Mon, May 26, 2014 at 10:39 AM, Stephen Frost <sfrost@snowman.net> wrote:

It'd need to be explicitly requested, eg a 'CASCADE' option.

Why? Would any sane person NOT want this behavior?

[...]

Now maybe there are options other than trying to reproduce what the
original CREATE OR REPLACE statement would have done against the new
type. For example, we could look through views that depend on t.a and
rewrite each reference to that column to t.a::oldtype. This might
lead to odd results with multiple nested casts and generally funny
behavior if the column is re-typed multiple times; but maybe there's
some way to fix that.

This.

Also, it might not really be the semantics you
want if you were hoping the type update would truly cascade. But it
might still be better than a sharp stick in the eye, which is kinda
what we offer today.

I hadn't even considered the idea that we would go through and try to
change everything which referenced that view to now be the new type- but
in that case, I'd want to know that there were other changes which were
happening beyond the single view which I was updating. Perhaps a NOTICE
would be enough, but it doesn't feel correct to me. Also consider
MatViews which would need to be rewritten for the new type, or pl/pgsql
functions which we couldn't possibly fix entirely (we're going to change
the variable's type definition because it selects out a column from this
view?) and so they'd just break instead.

Thanks,

Stephen

Robert Haas <robertmhaas@gmail.com> writes:

Well, pg_dump is trying to do something different than what you're
trying to do here. pg_dump wants to make sure that the view, when fed
back into psql, creates the same view that exists now, regardless of
whether that's what the user created originally. For example, if a
view is created referring to table foo, and table foo is later renamed
to bar, then pg_dump wants to (and does) dump a statement referring to
bar, not foo - even if there's a *new* table called foo against which
the view could have been defined. Similarly, pg_dump will
schema-qualify functions and operators, or not, based on whether
that's necessary to reference the exact same operators that were
selected when the original CREATE VIEW command was run, regardless of
whether the original references were schema-qualified.

Sorry, I don't see how any of the above is a problem in my use case.
Should a table has been renamed, naturally we want to re-create the view
referring to the *old* table, but under its *new name*. The same goes
with schema-qualifying objects.

None of that involves answering hypothetical questions; but what you
want to do does, and that I think is the problem in a nutshell.

In a nutshell I'd like PostgreSQL to just re-parse the *current* view
definition. Should that throw an error, user intervention will be
required anyway, but most of the time it should just work.

--
Alex

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

Stephen Frost <sfrost@snowman.net> writes:

I hadn't even considered the idea that we would go through and try to
change everything which referenced that view to now be the new type- but
in that case, I'd want to know that there were other changes which were
happening beyond the single view which I was updating. Perhaps a NOTICE
would be enough, but it doesn't feel correct to me.

Also consider MatViews which would need to be rewritten for the new
type

That might be costly but not impossible. A user would need to do that
anyway, though manually.

or pl/pgsql functions which we couldn't possibly fix entirely
(we're going to change the variable's type definition because it
selects out a column from this view?) and so they'd just break
instead.

I'm not suggesting that we try to *fix* functions, but if we can detect
function breakage by re-parsing them it would be nice to alert the user
of any problems at the instant of running ALTER TABLE statement, not
when the user tries to actually run the function (see my mail upthread
for sample scenario.)

--
Alex

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

* ash (ash@commandprompt.com) wrote:

Stephen Frost <sfrost@snowman.net> writes:

I hadn't even considered the idea that we would go through and try to
change everything which referenced that view to now be the new type- but
in that case, I'd want to know that there were other changes which were
happening beyond the single view which I was updating. Perhaps a NOTICE
would be enough, but it doesn't feel correct to me.

Also consider MatViews which would need to be rewritten for the new
type

That might be costly but not impossible. A user would need to do that
anyway, though manually.

I was pointing out why it should need to be explicitly requested, but
all-in-all, I don't really see this proposal going anywhere. It's a
neat idea, and if there's a sensible way to do it, then the user should
have to explicitly request it, imv.

or pl/pgsql functions which we couldn't possibly fix entirely
(we're going to change the variable's type definition because it
selects out a column from this view?) and so they'd just break
instead.

I'm not suggesting that we try to *fix* functions, but if we can detect
function breakage by re-parsing them it would be nice to alert the user
of any problems at the instant of running ALTER TABLE statement, not
when the user tries to actually run the function (see my mail upthread
for sample scenario.)

We're not going to re-parse every function in the system, like, ever. I
might be willing to buy off on "too bad" in those cases (it's not like
we're going and fixing them today for ALTER TABLE .. TYPE cases anyway)
but the point is that cascading the change to a column's type through
all of its dependencies is likely to cause breakage somewhere in even
modestly complex systems and we shouldn't just start doing that
automatically.

Thanks,

Stephen

Stephen Frost <sfrost@snowman.net> writes:

* ash (ash@commandprompt.com) wrote:

Stephen Frost <sfrost@snowman.net> writes:

Also consider MatViews which would need to be rewritten for the new
type

That might be costly but not impossible. A user would need to do that
anyway, though manually.

I was pointing out why it should need to be explicitly requested, but
all-in-all, I don't really see this proposal going anywhere. It's a
neat idea, and if there's a sensible way to do it, then the user should
have to explicitly request it, imv.

Ah, understood.

or pl/pgsql functions which we couldn't possibly fix entirely
(we're going to change the variable's type definition because it
selects out a column from this view?) and so they'd just break
instead.

I'm not suggesting that we try to *fix* functions, but if we can detect
function breakage by re-parsing them it would be nice to alert the user
of any problems at the instant of running ALTER TABLE statement, not
when the user tries to actually run the function (see my mail upthread
for sample scenario.)

We're not going to re-parse every function in the system, like, ever.

Well, only every *affected* function, which might be pretty minimal in
the usual case.

I might be willing to buy off on "too bad" in those cases (it's not
like we're going and fixing them today for ALTER TABLE .. TYPE cases
anyway) but the point is that cascading the change to a column's type
through all of its dependencies is likely to cause breakage somewhere
in even modestly complex systems and we shouldn't just start doing
that automatically.

What I am suggesting is that we try to detect such breakage at the time
the user runs ALTER TABLE (issuing NOTICE or ERROR at user discretion.)
If changing column type of a table breaks some functions down the way,
the user will hit it anyway, but better know it soon than when he wants
to *run* that function.

--
Alex

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

* ash (ash@commandprompt.com) wrote:

What I am suggesting is that we try to detect such breakage at the time
the user runs ALTER TABLE (issuing NOTICE or ERROR at user discretion.)
If changing column type of a table breaks some functions down the way,
the user will hit it anyway, but better know it soon than when he wants
to *run* that function.

Sure, if we had the information about what would break then we could
tell the user about it. We don't and that's not likely to ever
change...

Thanks,

Stephen

ash <ash@commandprompt.com> writes:

Stephen Frost <sfrost@snowman.net> writes:

We're not going to re-parse every function in the system, like, ever.

Well, only every *affected* function, which might be pretty minimal in
the usual case.

We don't store dependency information for function bodies, so there's
no way to do this except by reparsing everything in sight.

A larger issue with the idea is that a function might fail reparsing
for reasons having nothing to do with the proposed ALTER TABLE.
For instance, it's not at all unusual for functions to contain references
to tables that don't normally exist, but are created when the function is
to be called (or maybe even by the function itself). Because of this
problem, "reparsing", in the sense of detecting semantic rather than
purely syntactic problems in a function body, is something that we don't
actually do *at all*, ever, except when the function is actually executed.
(This is part of the reason why there's no dependency info.)
Pavel Stehule has made some efforts towards improving that situation
for plpgsql functions:
https://commitfest.postgresql.org/action/patch_view?id=884
but that patch remains pretty controversial and may never get committed.
Even if it does get in, it wouldn't move the goalposts for any other PL.

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

Tom Lane <tgl@sss.pgh.pa.us> writes:

We don't store dependency information for function bodies, so there's
no way to do this except by reparsing everything in sight.

A larger issue with the idea is that a function might fail reparsing
for reasons having nothing to do with the proposed ALTER TABLE.
For instance, it's not at all unusual for functions to contain references
to tables that don't normally exist, but are created when the function is
to be called (or maybe even by the function itself). Because of this
problem, "reparsing", in the sense of detecting semantic rather than
purely syntactic problems in a function body, is something that we don't
actually do *at all*, ever, except when the function is actually executed.
(This is part of the reason why there's no dependency info.)
Pavel Stehule has made some efforts towards improving that situation
for plpgsql functions:
https://commitfest.postgresql.org/action/patch_view?id=884
but that patch remains pretty controversial and may never get committed.
Even if it does get in, it wouldn't move the goalposts for any other PL.

OK, forget functions, I now realize it's not feasible to consider.

Can we get back to re-defining views at least?

--
Alex

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

ash wrote:

Tom Lane <tgl@sss.pgh.pa.us> writes:

We don't store dependency information for function bodies, so there's
no way to do this except by reparsing everything in sight.

OK, forget functions, I now realize it's not feasible to consider.

Can we get back to re-defining views at least?

Hi Alex,

I think it's reasonable to try and fix the problems for views (and other
objects -- there are other things that can depend on table definitions;
composite types come to mind) and ignore functions bodies, since you can
already get into trouble by using ALTER TABLE today and it's known to be
an unsolvable problem.

Now -- do we need to do anything about tables used as return types or
argument types for functions?

alvherre=# create table qux (a int, b text);
CREATE TABLE
alvherre=# create or replace function test_qux(a qux) returns void language plpgsql as $$ begin raise notice 'the qux we got is %', $1; end; $$;
CREATE FUNCTION
alvherre=# insert into qux values (1, 'one');
INSERT 0 1
alvherre=# select * from qux, test_qux(qux.*);
NOTICE: the qux we got is (1,one)
a | b | test_qux
---+-----+----------
1 | one |
(1 fila)

alvherre=# alter table qux add column c timestamptz;
ALTER TABLE
alvherre=# update qux set c = now();
UPDATE 1
alvherre=# select * from qux, test_qux(qux.*);
NOTICE: the qux we got is (1,one,)
a | b | c | test_qux
---+-----+-------------------------------+----------
1 | one | 2014-05-28 12:08:28.210895-04 |
(1 fila)

Notice how the NOTICE has a final comma, meaning the tuple descriptor is
aware that there is a third column -- but the value in the table is not
null per the UPDATE, so the fact that there's nothing after the comma
means this is not being handled correctly. If I close the session and
start a fresh one, the result is saner:

alvherre=# select * from qux, test_qux(qux.*);
NOTICE: the qux we got is (1,one,"2014-05-28 12:08:28.210895-04")
a | b | c | test_qux
---+-----+-------------------------------+----------
1 | one | 2014-05-28 12:08:28.210895-04 |
(1 fila)

Maybe we're missing a function cache invalidation or something like
that.

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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