Geek asks and answers

Postgresql thread security for temporary tables

This syntax that I use to create a temporary table:

create temp table tmpTable (id bigint not null, primary key (id)) on commit drop;

I know this means that at the end of each transaction this table will be deleted. My question is, if two or more threads in the same session create and paste values ​​into a temporary table, will each one receive its own instance or temporary instance common to the session? If he shared, is there a way to make it local in the stream?

Thanks Netta p>

+5
source share
1 answer

. , , ( ).

:

CREATE TEMP TABLE tmptbl IF NOT EXISTS ...

CREATE TABLE .

"" ( ), . SEQUENCE SQL - , plpgsql DO ( , ).

:

CREATE SEQUENCE myseq;

:

DO $$
BEGIN
EXECUTE 'CREATE TABLE tmp' || nextval('myseq')  ||'(id int)';
END;
$$

:

SELECT 'tmp' || currval('myseq');

plpgsql .

SQL- , , SQL . , , plpgsql.

temp thread_id . , . thread_id ( ).

:

CREATE SEQUENCE myseq;

:

CREATE TEMP TABLE tmptbl(thread_id int, col1 int) IF NOT EXISTS;
my_id :=  nextval('myseq'); -- in plpgsql
-- else find another way to assign unique id per thread

SQL:

INSERT INTO tmptbl(thread_id, col1) VALUES
(my_id, 2), (my_id, 3), (my_id, 4);

SELECT * FROM tmptbl WHERE thread_id = my_id;
+8

More articles:


All Articles