Skip site navigation (1) Skip section navigation (2)

Re: alter table tablename add column - breaks pl/pgsql function returns tablename

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Palle Girgensohn <girgen(at)pingpong(dot)net>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Amit kapila <amit(dot)kapila(at)huawei(dot)com>, Palle Girgensohn <girgen(at)freebsd(dot)org>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: alter table tablename add column - breaks pl/pgsql function returns tablename
Date: 2012-11-06 00:12:42
Message-ID: 28083.1352160762@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Palle Girgensohn <girgen(at)pingpong(dot)net> writes:
> Ah, sorry. Other sessions get the error immediately as well though. Would input parameters matter, or is it just the return type? I'll see if I can find a test case that breaks permanently, but I'm probably mistaken about that bit then. 

It's not the return value as such that's choking, it's the local
variable.  I believe the issue would appear with any local variable or
parameter of a named composite type.

The general case of this is quite difficult: should we expect that an
ALTER TYPE done (perhaps in some other session) while a function is
running would affect the *current value* of such a local variable?
There's really no practical way to implement that in the current system
structure, and certainly no way to enforce the behavior you get for row
values in regular tables, namely that the ALTER TYPE rolls back if any
row value conversion fails.

However I think we could realistically hope that subsequent function
calls would work with the up-to-date rowtype definition.  My opinion
about how to do that is to stop using the "row" code path in plpgsql for
values of named composite types, and instead treat them as "records";
that is, store a HeapTuple value plus a tuple descriptor (or something
morally equivalent such as a TupleTableSlot) and not break the value up
into a separate plpgsql variable (a/k/a PLpgSQL_datum) per column.  The
fundamental problem here is that doing that bakes the rowtype's column
set into the compiled form of the function.

There was some objection to that in the previous discussion on the
grounds of possible performance loss, but I think that objection is
at best premature; it ignores some salient facts such as
(1) some operations would get faster not slower,
(2) there is scope for performance-improvement efforts,
(3) per the old saying, code can be arbitrarily fast if it doesn't
    have to give the right answer.  Objecting to this fix without
    proposing a more-workable alternative is useless.

			regards, tom lane


In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2012-11-06 00:22:26
Subject: Re: Pg_upgrade speed for many tables
Previous:From: Merlin MoncureDate: 2012-11-05 23:50:22
Subject: Re: What are the advantages of not being able to access multiple databases with one connection?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group