# Test TOAST behavior in PL/pgSQL procedures with transaction control.
#
# We need to ensure that values stored in PL/pgSQL variables are free
# of external TOAST references, because those could disappear after a
# transaction is committed (leading to errors "missing chunk number
# ... for toast value ...").  The tests here do this by running VACUUM
# in a second session.  Advisory locks are used to have the VACUUM
# kick in at the right time.  The different "assign" steps test
# different code paths for variable assignments in PL/pgSQL.

setup
{
    CREATE TABLE test1 (a int, b text);
    ALTER TABLE test1 ALTER COLUMN b SET STORAGE EXTERNAL;
    INSERT INTO test1 VALUES (1, repeat('foo', 2000));
    CREATE TYPE test2 AS (a bigint, b text);
}

teardown
{
    DROP TABLE test1;
    DROP TYPE test2;
}

session "s1"

setup
{
    SELECT pg_advisory_unlock_all();
}

# assign_simple_var()
step "assign1"
{
do $$
  declare
    x text;
  begin
    select test1.b into x from test1;
    delete from test1;
    commit;
    perform pg_advisory_lock(1);
    raise notice 'x = %', x;
  end;
$$;
}

# assign_simple_var()
step "assign2"
{
do $$
  declare
    x text;
  begin
    x := (select test1.b from test1);
    delete from test1;
    commit;
    perform pg_advisory_lock(1);
    raise notice 'x = %', x;
  end;
$$;
}

# expanded_record_set_field()
step "assign3"
{
do $$
  declare
    r record;
  begin
    select * into r from test1;
    r.b := (select test1.b from test1);
    delete from test1;
    commit;
    perform pg_advisory_lock(1);
    raise notice 'r = %', r;
  end;
$$;
}

# expanded_record_set_fields()
step "assign4"
{
do $$
  declare
    r test2;
  begin
    select * into r from test1;
    delete from test1;
    commit;
    perform pg_advisory_lock(1);
    raise notice 'r = %', r;
  end;
$$;
}

# expanded_record_set_tuple()
step "assign5"
{
do $$
  declare
    r record;
  begin
    for r in select test1.b from test1 loop
      null;
    end loop;
    delete from test1;
    commit;
    perform pg_advisory_lock(1);
    raise notice 'r = %', r;
  end;
$$;
}

session "s2"
setup
{
    SELECT pg_advisory_unlock_all();
}
step "lock"
{
    SELECT pg_advisory_lock(1);
}
step "vacuum"
{
    VACUUM test1;
}
step "unlock"
{
    SELECT pg_advisory_unlock(1);
}

permutation "lock" "assign1" "vacuum" "unlock"
permutation "lock" "assign2" "vacuum" "unlock"
permutation "lock" "assign3" "vacuum" "unlock"
permutation "lock" "assign4" "vacuum" "unlock"
permutation "lock" "assign5" "vacuum" "unlock"