Por que o banco de dados não lê dados recém-inseridos quando uma única consulta está fazendo tanto gravação quanto leitura?

No PostgreSQL, o comportamento que você está observando é devido à sequência em que as instruções são executadas na consulta. Quando você executa:

SELECT * FROM insert_and_return(10), A AS y;

Execução da função : A função insert_and_return(10) é chamada primeiro, inserindo o valor 10 na tabela A.
Varredura da tabela A : Após executar a função, o PostgreSQL tenta recuperar registros de A (com o alias de y).

No entanto, o isolamento de transação do PostgreSQL impede que a ação de inserção da função seja imediatamente visível para a consulta. Isso ocorre porque o PostgreSQL efetivamente cria o plano de execução para ambas as partes da consulta antes que quaisquer linhas reais sejam inseridas. A linha inserida não está disponível para a mesma consulta devido às regras de visibilidade no PostgreSQL, que separam as modificações de dados das leituras de dados em uma única instrução.

DROP TABLE IF EXISTS a;
CREATE TABLE a (id int);

CREATE OR REPLACE FUNCTION insert_and_return_read(val int)
    RETURNS TABLE (inserted_id int, existing_id int) AS $$
BEGIN
    RETURN QUERY
    WITH ins AS (
        INSERT INTO a (id) VALUES (val) RETURNING id
    )
    SELECT ins.id AS inserted_id, a.id AS existing_id
    FROM ins
    LEFT JOIN a ON TRUE;  -- This will show the newly inserted value alongside any existing values in 'a'
END;
$$ LANGUAGE plpgsql;

-- Call the function
SELECT * FROM insert_and_return_read(10);

Eu esperava insert_and_returninserir uma nova linha quando FROM insert_and_return(10),Afaz um cross-join, antes de SELECTvê-lo e retorná-lo no resultado.
Real: Conjunto de resultados em branco

A função insere a linha e também a retorna para sua consulta. Ela é então unida cruzadamente com um conjunto vazio que a consulta vê na tabela a, o que anula tudo. EXPLAIN ANALYZE VERBOSEpode mostrar que a consulta intermediária produz rows=1e é apenas a final que acaba vazia porque aquela linha foi unida a zero linhas:
_{demo em db<>fiddle}

explain analyze verbose SELECT * FROM insert_and_return(10),A AS y;

Não tenho certeza do que e por que você está tentando fazer aqui, mas se você está tentando ver todas as linhas que inseriu, não apenas o valor fornecido para uma coluna, parece que você só deseja

insert into a values(10)returning *;

Se você estiver tentando ver o estado final de toda a tabela logo após a inserção, você está procurando por union:

table a
union all
select insert_and_return(10);

O tablecomando é apenas uma forma abreviada de a select*fromno PostgreSQL. É a única forma razoável de envolver a tabela aincondicionalmente - caso contrário, se houver algo naquela tabela, uma ,junção cruzada de vírgula atingiria você com quantas linhas você acabou de inserir, vezes quantas você tinha antes, tudo pareado e listado com todo o resto, esse número de vezes.

Se você estiver tentando comparar a carga útil recebida com o que efetivamente acaba sendo escrito na tabela após disparar todos triggeros s (e rules , com algumas ressalvas), é exatamente isso que returningmostra:

Se houver gatilhos ( Capítulo 37 ) na tabela de destino, os dados disponíveis RETURNINGsão a linha conforme modificada pelos gatilhos. Portanto, inspecionar colunas computadas por gatilhos é outro caso de uso comum para RETURNING.

Isso significa que se os valores forem processados ​​de alguma forma, returningnão mostrará o que entrou inicialmente, apenas o que acabou na tabela, então returning *perde de vista a entrada bruta. Para vê-la, você pode repeti-la como uma constante:

insert into a values('your_input_value')returning 'your_input_value',*;

Exemplo na demonstração :

create function trg1_f()returns trigger as $f$
begin
   NEW.id = NEW.id + 1; --incoming payload gets increased here
   return NEW;
end $f$ language plpgsql;

create trigger trg1 before insert on a--happens before the write
for each row execute function trg1_f();

insert into a values(10)returning 10 as payload,*;--payload added as constant

Pergunta: Por que não vejo os dados? Tenho que SELECT novamente para vê-los.
Acho que os SELECTs estão sempre vendo 1 linha a menos do que há.
Espero entender a ordem dos eventos sob o capô, se os dados já estão inseridos, mas de alguma forma ocultos do SELECT, ou não foram inseridos no momento em que o SELECT é executado.

Não há realmente nenhuma sequência determinística dessas operações. A varredura de função e a varredura de tabela podem ou não ser executadas em alguma ordem ou outra, ou em paralelo ambas ao mesmo tempo - o planejador/otimizador é livre para reorganizá-las como achar melhor.

Você pode usar subqueries e lateralCTEs para definir algum tipo de fluxo dentro de uma única consulta multi-instrução, mas esse fluxo não pode envolver o estado da tabela, ele não pode escrever nela, depois dela, e depois novamente nela. Por causa do MVCC , todas as instruções na sua consulta veem exatamente o mesmo instantâneo dessa tabela durante todo o tempo de vida da consulta.

Perguntas relacionadas
Esta pergunta tem algo a ver com níveis de isolamento ou é irrelevante porque os níveis de isolamento só se aplicam a múltiplas transações?

Isso é exatamente correto. O isolamento de transação não tem nada a ver com declarações de uma única consulta.

Por que os dados podem ser gerados dinamicamente com CASE ou padrões semelhantes, SELECT price, price * 0.9mas isso não funciona aqui?

Funciona aqui, você gerou dados na hora: você especificou uma constante 10e ela foi escrita na tabela e retornada dela. Só que você decidiu fazer cross joinisso com um conjunto vazio, então você obteve um conjunto vazio. Outra ilustração:

select price, price*0.9 
from (values(1)) as v(price)
cross join (select 2 limit 0) as unrelated_empty_set(col1);

cross joinNão importa se o conjunto vazio com o qual você está relacionado está relacionado ou não.

Tentei criar separação com CTE, mas vejo o mesmo comportamento em que só vejo dados ao executar a consulta inteira abaixo uma 2ª vez. Isso pode ser explicado pelos mesmos motivos acima, ou o CTE tem suas próprias ressalvas?

WITH inserted_data AS (
   INSERT INTO a (id) VALUES (10)
   RETURNING id
)
SELECT * FROM inserted_data,A;

Não importa. O cross join Aresultado da antiga sintaxe implícita de junção por vírgula ,Aapaga seus resultados de inserted_dataporque você está unindo cruzadamente com um conjunto vazio: durante todo o tempo de vida desta consulta, o instantâneo de Anão reflete nenhuma alteração que a consulta pretende aplicar a ele como consequência.

Se você realmente quer tudo lado a lado com tudo, troque isso por uma junção qualificada :

WITH inserted_data AS (
    INSERT INTO a (id) VALUES (10)
    RETURNING id
)
SELECT * 
FROM inserted_data
FULL JOIN a USING(id);

Isso lhe dá tudo o que você acabou de inserir à esquerda, pareado com nulos à direita se for completamente novo ou com qualquer ids correspondente que você já tinha na tabela aanterior, então lista todo o resto ada direita. Diferentemente do cross join, ele só multiplica os recém-chegados que encontram mais de uma idcorrespondência na tabela de destino.

Você também pode listar tudo junto, uma vez, usando o unionmencionado anteriormente:

WITH inserted_data AS (
    INSERT INTO a (id) VALUES (10)
    RETURNING id
)
SELECT*FROM a
UNION ALL
SELECT*FROM inserted_data;