All Questions
Tagged with temporary-tables postgresql
35 questions
0
votes
0
answers
32
views
Temporary relation files not removed after sessions ends - Single PostgreSQL instance PostgreSQL 13
We are facing default temporary tablespace disk space full issue due to lots of below temporary relations files created but not removed after ending session
1.1G t756_1536213694.409
1.1G ...
- 143
1
vote
1
answer
596
views
Will Postgres reclaim space when a temporary table is dropped?
I'm using a large number of TEMP TABLES and want to make sure I'm not leaking storage.
I create and use the table in a transaction with CREATE TEMP TABLE mytable ... ON COMMIT DROP.
I'm having trouble ...
- 1,655
0
votes
1
answer
74
views
Compose a complex query in Postgresql
I have a huge table like following:
CREATE TABLE public.huge_table (
sampl_day date NOT NULL,
tick_time timestamp(6) with time zone NOT NULL,
crit_feat integer NOT NULL,
---
--- ...
- 359
1
vote
1
answer
2k
views
How does PostgreSQL resolve pg_temp?
pg_temp is an alias for current session's temporary-table schema.
Likewise, the current session's temporary-table schema, pg_temp_nnn, is always searched if it exists. It can be explicitly listed in ...
- 111
3
votes
2
answers
2k
views
UPDATE FROM with a large table is slow and uses Seq Scans
I have a large table (ultimately maybe a billion rows but currently ~26 million) for which I want to set a flag on the highest PK for a given grouping, in a one-off batch.
I chose to create a ...
- 1,655
0
votes
1
answer
3k
views
How to create multiple temp tables using records from a CTE that I need to call multiple times in Postgres plpgsql Procedure?
I am already using a CTE expression within a plpgsql Procedure to grab some Foreign Keys from (1) specific table, we can call it master_table. I created a brand new table, we can call this table ...
- 121
0
votes
2
answers
3k
views
How to avoid creating and dropping temp tables in functions?
I have been creating some functions lately that run into the "out of shared memory" warning in postgres. I have tried to increase the max_locks_per_transaction, but it still occurs.
As far ...
2
votes
1
answer
1k
views
Unable to save a stored procedure which contains a temporary table creation and update query
I am unable to save my procedure in Postgres. The procedure contains a temporary table creation and update query.
Here is my query:
CREATE OR REPLACE PROCEDURE account_report_v2()
LANGUAGE SQL
AS $$
...
- 121
1
vote
1
answer
4k
views
Using WITH together with CREATE TEMPORARY TABLE AS SELECT ... in Postgres?
I would like to create a temporary table using a select statement that uses temporary views specified in a WITH statement (not the WITH statement of CREATE TEMPORARY TABLE).
e.g. something along the ...
- 113
2
votes
1
answer
621
views
Postgres long running query not blocking table operations
We have a 3rd party service that imports data into a local postgres instance, that we then query/copy into our own view-data system. These queries can yield millions of rows, so the import of them to ...
- 187
0
votes
1
answer
482
views
Aurora postgres grant permission to pg_attribute
In the process of migration from local postgres version to and RDS aurora postgres version. I'm using temporal tables and do for that reason have temporal tables that are inhirited from the origin. ...
0
votes
1
answer
849
views
Use Common Table Expression (CTE) to reduce locks (max_locks_per_transaction)
I am getting
"ERROR: out of shared memory"
presumably because my plpgsql function drops/creates a lot of temporary tables.
Searching for a solution to the issue, I found this answer that says:
...
- 3,692
2
votes
1
answer
2k
views
Why can't temporary tables reference regular (non-temp) tables in PostgreSQL?
Why can't temporary tables references non-temp tables in PostgreSQL? When I run the following
CREATE TABLE foo ( a int PRIMARY KEY );
CREATE TEMP TABLE bar ( a int REFERENCES foo );
I get,
ERROR:...
- 64.7k
6
votes
1
answer
2k
views
Safe to use the same temporary table name in parallel transactons?
Using postgreSQL 10 or 11, would it be safe creating temporary tables with the same name, each carrying different data? I do not intend to share the data between any sessions/connection/transactions. ...
- 267
7
votes
1
answer
9k
views
Where is `pg_temp` documented in the PG manual?
I have tried various ways to search for pg_temp in the manual, but could not find it. Is it documented in the manual?
- 389
2
votes
1
answer
3k
views
How to check if a PostgreSQL table is temporary?
How can I write a query to check if a PostgreSQL (10.x) table is a temporary table?
I looked at the pg_class system table, but couldn't find any column about whether the relation is temporary.
...
- 3,692
8
votes
2
answers
21k
views
Postgresql: when temporary tables are deleted in postgresql
I am inserting data into temp table and its working fine. below is the psedo sql code for the same
with cte as(
)
select *
into temp_table
from cte
with this approach data is inserted very fast ...
- 463
0
votes
1
answer
59
views
can my SQL Server application code using temporary tables be used for Postgres?
I have a large application that connects to SQL Server and makes extensive use of temporary tables using the #table syntax. I would like to convert this to connect to postgres, which does not accept ...
- 1
0
votes
1
answer
1k
views
Postgresql using LEFT JOIN and RIGHT JOIN at the same time
I have two temporary tables that I have created using tampa, tampb clauses in PostgreSQL. I want to be able to get result when data from either side is missing (company1 or company2) but not both.
I ...
- 141
3
votes
1
answer
8k
views
Make a temporary table visible for a function inside a function (or a subfunction)
I have two functions in PL/pgSQL:
The function 1 calls function 2, and function 2 needs to process a temporary table created on function 1, in another words, this temporary table needs to be global ...
- 1,591
2
votes
3
answers
3k
views
Can one have Temporary Rows?
I understand that Temporary Tables in Postgres and MS SQL Server persist until the end of the session. When the user’s session ends, any temp tables created by that user are dropped automatically. The ...
- 11k
1
vote
2
answers
332
views
How do I rewrite my query to use less temporary space?
I'm using Postgres 9.5. I want to identify (and then delete) rows from my table that are "duplicates" in the sense that they contain three fields that are the same, namely
my_object_id
name
...
- 733
4
votes
2
answers
21k
views
In Postgres, how do I adjust the "pgsql_tmp" setting?
I"m using Postgres 9.5.4 on Ubuntu 14.04. I have my Postgres data on a separate disk partiion, which is getting pretty full.
myuser@myproject:~$ df -h /mnt/volume-nyc1-01/
Filesystem Size Used Avail ...
- 733
14
votes
1
answer
30k
views
Index usage on a temporary table
I have two rather simple queries. The first query
UPDATE mp_physical SET periodic_number = '' WHERE periodic_number is NULL;
and it's plan
duration: 0.125 ms plan:
Query Text: UPDATE ...
- 253
0
votes
1
answer
447
views
Postgres: extracting multiple bit-columns from single 'flags' column after join with 2nd table
I have a query similar to this below. As you can see, there are multiple parts which are very similar.
SELECT
id,
aaaa,
bbbb,
tags
,(
SELECT COUNT(*) > 0
...
- 109
4
votes
1
answer
2k
views
Temp postgres table ignores "excluding constraints"
In PostgreSQL 9.5.
I'd like to create a temporary copy of my table without its primary key. Postgres has a feature { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | ...
0
votes
1
answer
2k
views
TEMP table alternatives for pl/pgsql
I am trying to emulate MERGE behavior with pl/pgsql:
-- Generate the data from funtion
CREATE TEMP TABLE temp_x (id int, id2 int, data text, created_at timestamp, updated_at timestamp) ON COMMIT ...
- 327
6
votes
1
answer
11k
views
Postgres 'could not write to hash-join temporary file: No space left on device'
I was getting this error a while ago and I was curious about two things -
1- How do the internals of postgres work that cause this message to occur in the first place? What is the hash-join in ...
- 183
1
vote
1
answer
735
views
Join external data in SELECT query
I have few (~20) names of products corresponding to an integer id that needs to be joined on products while exporting products data (using COPY (<query>) TO). The burden is that these names do ...
- 113
1
vote
0
answers
1k
views
CREATE TEMPORARY TABLE, temp_buffers and Postgres performance
I have a bunch of queries that use temporary tables, and most of them are working really good, but from time to time they take an usual amount of time: 3-5 seconds.
My temp_buffers is set to the ...
- 11
6
votes
3
answers
30k
views
Copying CSV file to temp table with dynamic number of columns?
I am wondering if there is a way to copy a csv file into a temp table where the number of columns is unknown in the csv file. The DB software I'm using is PgAdmin III. I figured out that if I do know ...
- 161
3
votes
2
answers
386
views
Is copy/rename a good way to improve temp table performance?
An application I am working with contains this sequence of queries at the end of constructing a large temp table; I believe it is an attempt to improve performance by pruning dead tuples -
create ...
- 135
1
vote
1
answer
2k
views
Table variable not Recognized in stored procedure
The following stored procedure for a postgresql database attempts to insert into a temporary table 'result' of type result_load_status ,which is a table in database, and return this temporary table. ...
- 33
6
votes
2
answers
3k
views
Is this temp table behaviour documented?
Query Language (SQL) and PL/pgSQL functions treat temp tables differently:
begin;
create table foo(id) as values (1);
select * from foo;
/*
id
----
1
*/
savepoint s;
create function f() returns ...
- 40.2k
5
votes
1
answer
1k
views
What are all the reasons/possibilities that may fail to release disk space occupied by TEMPORARY table?
We're using PostgreSQL v8.2.3. Ours is a web-based application and we're using pgpool-II v 2.0.1 purely for connection pooling (we
don't use other features of pgpool like Replication, Load Balancing, ...
- 987