On Sat, Oct 10, 2015 at 10:00 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> On 10/09/2015 08:30 PM, Victor Blomqvist wrote: > >> Note that these errors most of the time only happens very briefly at the >> same time as the ALTER is run. When I did some experiments today the >> server in total had around 3k req/s with maybe 0.1% of them touching the >> table being updated, and the error then happens maybe 1-10% of the times >> I try this operation. If I do the operation on a table with more load >> the error will happen more frequently. >> > > Out of curiosity more then any else, what happens if you ADD a column > instead of DROP a column in the experiment? > The same behaviour. (Actually its more annoying than when it happens with DROPs since we do ADDs much more often) > > >> Also, someone suggested me to try and recreate the functions returning >> the table as well inside a transaction, but that did not change anything: >> BEGIN; >> ALTER TABLE... >> CREATE OR UPDATE FUNCTION ... >> END; >> >> Thanks for your help so far! >> /Victor >> >> On Fri, Oct 9, 2015 at 10:49 PM, Adrian Klaver >> <adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>> wrote: >> >> On 10/09/2015 07:31 AM, Albe Laurenz wrote: >> >> Adrian Klaver wrote: >> >> For the reason why this is happening see: >> >> >> http://www.postgresql.org/docs/9.4/interactive/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING >> >> >> Yes, but the ALTER TABLE causes the plan to be recreated >> the next time. >> >> >> But does it? From the link above: >> >> "Because PL/pgSQL saves prepared statements and sometimes >> execution >> plans in this way, SQL commands that appear directly in a >> PL/pgSQL >> function must refer to the same tables and columns on every >> execution; >> that is, you cannot use a parameter as the name of a table >> or column in >> an SQL command. To get around this restriction, you can >> construct >> dynamic commands using the PL/pgSQL EXECUTE statement — at >> the price of >> performing new parse analysis and constructing a new >> execution plan on >> every execution." >> >> I see '*' as a parameter. Or to put it another way '*' is >> not referring >> to the same thing on each execution when you change the >> table definition >> under the function. Now if I can only get the brain to wake >> up I could >> find the post where Tom Lane explained this more coherently >> then I can:) >> >> >> 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. >> >> >> I stand corrected. I also tried on Postgres 9.3.7, which is a close >> as I could get to OP's 9.3.5 and it worked. Will have to rethink my >> assumptions. >> >> >> >> Yours, >> Laurenz Albe >> >> >> >> -- >> Adrian Klaver >> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> >> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
