SETVAL in a Window Function

Question

I'm attempting to do an INSERT into multiple tables using the same key field. According to Sequence Manipulation Functions:

Important: Because sequences are non-transactional, changes made by setval are not undone if the transaction rolls back.

The above makes me worried that something else might update the sequence during my transaction.

So here is my current query:

BEGIN;
--[other queries and stuff here]
CREATE TEMPORARY TABLE my_temp_table ON COMMIT DROP AS
SELECT
    (SETVAL('seq_A',(SELECT "last_value" FROM seq_A) + COUNT(*) OVER ()) - (ROW_NUMBER() OVER (ORDER BY some_other_table.field1 DESC))) AS primarykeyid,
    some_other_table.morefields
FROM
    some_other_table;
--[INSERTs into multiple different tables with primarykeyid here]
COMMIT;

Does SETVAL get called once for each row in my_temp_table or does it get called once?

Would it be better to have a separate query prior to creating my_temp_table to "reserve" the values before running? For example:

BEGIN;
--[other queries and stuff here]

CREATE TEMPORARY TABLE my_temp_sequence_value ON COMMIT DROP AS
SELECT
    (SETVAL('seq_A',(SELECT "last_value" FROM seq_A) + COUNT(*)) AS seq_val
FROM
    some_other_table;

CREATE TEMPORARY TABLE my_temp_table ON COMMIT DROP AS
SELECT
    (SELECT seq_val FROM my_temp_sequence_value) - (ROW_NUMBER() OVER (ORDER BY some_other_table.field1 DESC))) AS primarykeyid,
    some_other_table.morefields
FROM
    some_other_table;
--[INSERTs into multiple different tables with primarykeyid here]
COMMIT;

Answer 1

Your usage of setval() is not what one usually expects. It is used for resetting the sequence. Feeding new values to IDs and other stuff is done by nextval(). Its function is described so:

nextval() Advance sequence and return new value

Important to note that this is done so that two concurrent transactions would never get the same number when calling nextval(). Also, it is not really suitable for producing gapless series - the reason is already cited in your question.