On 10/12/2015 07:53 PM, Victor Blomqvist wrote:
Do you have some advice how to design my functions to work around this
problem?
If I understand your conversation correct the problem is returning the
rowtype users from the function. If so, I can think of two workarounds
(both quite inconvenient and complex):
1. Use RETURNS TABLE(...) together with not selecting * in the functions.
2. Use RETURNS <custom type> also without select * in the functions.
Might want to investigate the record return type:
http://www.postgresql.org/docs/9.4/interactive/plpgsql-overview.html#PLPGSQL-ARGS-RESULTS
40.1.2. Supported Argument and Result Data Types
"It is also possible to declare a PL/pgSQL function as returning record,
which means that the result is a row type whose columns are determined
by specification in the calling query, as discussed in Section 7.2.1.4."
The section that explains difference between declared type record and
returned type record:
http://www.postgresql.org/docs/9.4/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-RECORDS
How to use a returned record in query:
http://www.postgresql.org/docs/9.4/interactive/queries-table-expressions.html#QUERIES-TABLEFUNCTIONS
See bottom of section.
Basically all the above leaves it up to the calling query to 'shape' the
output. Not sure if that will work for you.
What do other people do in this situation? For our system the lowest
load is in the late night, 04 - 06, which might have sufficiently low
load to avoid the issue, but I would much prefer to run schema changes
when there are people in the office.
/Victor
On Mon, Oct 12, 2015 at 10:15 PM, Adrian Klaver
<adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> wrote:
On 10/12/2015 06:53 AM, Tom Lane wrote:
Andres Freund <andres@xxxxxxxxxxx <mailto:andres@xxxxxxxxxxx>>
writes:
On 2015-10-09 14:32:44 +0800, Victor Blomqvist wrote:
CREATE FUNCTION select_users(id_ integer) RETURNS SETOF
users AS
$$
BEGIN
RETURN QUERY SELECT * FROM users WHERE id = id_;
END;
$$ LANGUAGE plpgsql;
My guess is that the problem here is that table level
locking prevents
modification of the "users" type when the table is used, but
there's no
locking preventing the columns to be dropped while the
function is
used. So what happens is that 1) the function is parsed &
planned 2)
DROP COLUMN is executed 3) the contained statement is
executed 4) a
mismatch between the contained statement and the function
definition is
detected.
The query plan as such does get refreshed, I believe. The
problem is that
plpgsql has no provision for the definition of a named composite
type to
change after a function's been parsed. This applies to
variables of named
composite types for sure, and based on this example I think it
must apply
to the function result type as well, though I'm too lazy to go
check the
code right now.
That makes sense. The problem is that I cannot square that with
Albe's example, which I tested also:
"
Session 1:
test=> CREATE TABLE users (id integer PRIMARY KEY, name varchar NOT
NULL, to_be_removed integer NOT NULL);
CREATE TABLE
test=> CREATE FUNCTION select_users(id_ integer) RETURNS SETOF users AS
$$BEGIN RETURN QUERY SELECT * FROM users WHERE id = id_;
END;$$ LANGUAGE plpgsql;
CREATE FUNCTION
Session 2:
test=> SELECT id, name FROM select_users(18);
id | name
----+------
(0 rows)
Ok, now the plan is cached.
Now in Session 1:
test=> ALTER TABLE users DROP COLUMN to_be_removed;
ALTER TABLE
Session2:
test=> SELECT id, name FROM select_users(18);
id | name
----+------
(0 rows)
No error. This is 9.4.4.
"
We have had past discussions about fixing this. I believe it would
require getting rid of use of plpgsql's "row" infrastructure for
named
composites, at least in most cases, and going over to the "record"
infrastructure instead. In the past the conversations have
stalled as
soon as somebody complained that that would probably make some
operations
slower. I don't entirely understand that objection, since (a)
some other
operations would probably get faster, and (b) performance does
not trump
correctness. But that's where the discussion stands at the moment.
regards, tom lane
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
