I am using PostgreSQL with two tables, called records and flags, that are partitioned exactly the same -- by range. I want to truncate old partitions in both. We use pg_partman, and they are created as so (toy example):

CREATE TABLE logging.records (
    id BIGSERIAL,
    record VARCHAR,
    PRIMARY KEY (id)
) PARTITION BY RANGE (id);
CREATE TABLE logging.records_template (LIKE logging.records);
ALTER TABLE logging.records_template ADD PRIMARY KEY (id);
SELECT logging.create_parent(
    p_parent_table := 'logging.records'
    , p_control := 'id'
    , p_interval := '10'
    , p_premake := '20'
    , p_default_table := false
    , p_template_table := 'logging.records_template');


CREATE TABLE logging.flags(
    id BIGSERIAL PRIMARY KEY,
    record_id BIGSERIAL,
    archived BOOLEAN,
    FOREIGN KEY (record_id) REFERENCES logging.records(id) ON DELETE CASCADE
) PARTITION BY RANGE (id);
SELECT logging.create_parent(
    p_parent_table := 'logging.flags'
    , p_control := 'id'
    , p_interval := '10'
    , p_premake := '20'
    , p_default_table := false);

If I:

TRUNCATE logging.records_p0;

I get the error: > ERROR: cannot truncate a table referenced by a foreign key constraint If I use CASCADE, then it truncates all partitioned flags, which I do not want. If I:

TRUNCATE logging.flags_p0;

it works. But of course that leaves logging.records full of stuff. If I:

ALTER TABLE logging.records DETACH PARTITION logging.records_p0 CONCURRENTLY;

I get the error: > ERROR: removing partition "records_p0" violates foreign key constraint "flags_record_id_fkey2" If I:

ALTER TABLE logging.flags DETACH PARTITION logging.flags_p0 CONCURRENTLY;
ALTER TABLE logging.records DETACH PARTITION logging.records_p0 CONCURRENTLY;
TRUNCATE logging.flags_p0;
TRUNCATE logging.records_p0;

it works. However, at this point I may as well just drop the tables instead of truncating them since they won't be used again. But I have the deadlock issue with detaching, because while this is all going on, there is heavy activity with other partitions getting inserts and updates. In my ideal world I could just truncate with cascade and it would just wipe out the partition in records and the corresponding one in flags and not have to detach. Is there another way? Thank you

I have multiple temporary tables stored in the

schema for an SSIS pipeline that runs daily. The pipeline extracts data from multiple tables and stores it in the temp tables, which the data of the temp tables is later used to store in a different database. My question is about the performance of the creation and deletion of the temp table, I want to know which approach is more optimal. Which of the following approaches is the better option? And what are the disadvantages and advantages of each one? 1-

temp table if it doesn't already exist -->

temp table from previous data before inserting new data -->

the new data into temp table. 2-

temp table if it exists --> use the statement

INTO

to insert data directly without creating a temp table in a separate statement. edit: the tables in the tempdb are created like this

TABLE tempdb..Table1

My database full backup file size is 760 GB and differential would be around 20 GB but the database size is around 2TB. As it is a production database, so to apply only a minimum downtime, i want to run DBCC SHRINKFILE with TRUNCATEONLY before restoring differential backup. Please advise if i can run the command DBCC SHRINKFILE with TRUNCATEONLY after full backup restored but before differential backup restoration.

On Microsoft SQL Server 2019, there is a lot of long temp tables TRUNCATE operations with time over 5..10 seconds. Easy query like "TRUNCATE TABLE #tt22" can take over 15 seconds. It's an intermittent problem, sometimes it disappears for a day after restarting the server . What could be the reason? UPD: According to the latest performance measurements, long TRUNCATE operations are observed not only in temporary tables, but also in physical ones.

DROP and TRUNCATE both are DDL commands and both are autocommited.Then why we can recover DROP table by using FLASHBACK(unless it is purge) and why we can not recover TRUNCATED data(don't consider backup).

Can I create event that would first TRUNCATE table and then make INSERT? I know how to TRUNCATE and INSERT in separate events but need to do it immediately one after the other. I just don't know the syntax for events. I can do it in myPHPadmin TRUNCATE tbl1; INSERT INTO tbl1 SELECT .... I know I could you PHP to run those two queries. I prefer event. Is it possible to do it in single event or at least to call those two event in other event. BTW, are MySQL events ok to use or PHP file with cron would be better? Thank you

Is it possible to disable TRUNCATE operation on the entire PostgreSQL database, or on a specific table?

We have 2 tables of size 186G and 63G that are no longer in use and needs to be truncated from the database. I cannot directly run the truncate table statement on the master DB as it has 14 replicas dependent on it and it would cause a lag on the replicas as we have binlog row format set. I tried deleting some records from the table but it is causing a lag on the replicas. The master server is a busy server and receives read/write requests frequently. Is there any way to perform this task without any impact/with minimum lag on the replicas.

I have run truncate table but the Heidi SQL is still showing table occupying space. Database: MYSQL 5.6.26 I have truncated the con_consumer table but heidi is still showing it taking space. Even other tools like Toad shows it taking up space. Mysql Engine:Inno DB

Why does the TRUNCATE TABLE statement hang sometimes? What are the reasons for this type of issue? I am migrating from MySQL to MariaDB. This problem doesn't happen with MySQL, only with MariaDB. The hanging statement is simply: TRUNCATE TABLE sampledb.datatable; What can cause this to happen, and how could I fix it? Another one observation is if the table have some data, may be one or two rows, then the truncate query works successfully. Else the table have a lot of data, query becomes hang.

I currently have a program that inserts into a database by creating temp tables, filling the tables, then merging that data into the main tables. Then dropping the tables and doing it all again. I'm wondering what the speed difference is if instead of drop and create, if I just truncate.

I’ve just started with a web host which supports PostgreSQL. When setting up a database user, I have the choice of the following privileges: ■ ALTER ■ CREATE ■ DELETE ■ EXECUTE ■ DROP ■ EVENT ■ INDEX ■ INSERT SELECT ■ TRIGGER ■ UPDATE That doesn’t include TRUNCATE, though it does include more drastic privileges. I plan to include the DELETE,EXECUTE,INSERT,SELECT,UPDATE privileges, but what’s needed to permit TRUNCATE without giving too much away?

Maybe I am missing something here:

CREATE TABLE public.example_table (
	id integer UNIQUE
);

CREATE TABLE public.foreign_table (
	id integer,
	example_table_id integer,
	CONSTRAINT fk_example_table_id
    FOREIGN KEY (example_table_id)
    REFERENCES public.example_table (id)
    ON DELETE SET NULL
);

INSERT INTO public.example_table (id) VALUES
    (1);

INSERT INTO public.foreign_table (id, example_table_id) VALUES
    (1, 1),
	(2, null);

If I run TRUNCATE CASCADE, both tables are wiped which is not what I expected would happen.

TRUNCATE example_table CASCADE;

SELECT COUNT(*) FROM public.foreign_table;

0

What I would expect to happen would be that foreign_table would alter to:

(1, null)
(2, null)

Am I not understanding what SET NULL is supposed to accomplish? Is there a way to use TRUNCATE CASCADE without having it delete from the other table? I use Laravel where I can call Model::truncate(); and it will automatically truncate the table and reset my indexes, I was hoping I could call this on the example_table and have it reset all the rows in foreign_table to null instead of just deleting the whole table. Thanks for your help.

I have a requirement where we need to drop historical partitions which are not dropped . Requirement is dropping multiple partitions in one step instead of dropping one by one. like ALTER TABLE sales DROP PARTITION sales_q1_2008, sales_q2_2008, sales_q3_2008, sales_q4_2008 update global indexes; instead of ALTER TABLE sales DROP PARTITION sales_q1_2008 update global indexes; ALTER TABLE sales DROP PARTITION sales_q2_2008 update global indexes; ALTER TABLE sales DROP PARTITION sales_q3_2008 update global indexes; and so on... app team has this code:- CREATE OR REPLACE PROCEDURE Test_user.DROP_PARTITIONS (p_table_name in varchar2, p_partition_key in number) is pragma autonomous_transaction; begin for cur in ( select partition_name from user_tab_partitions where table_name = p_table_name and substr(partition_name,1,8) <= 'P_'||trim(to_char(p_partition_key,'000000')) order by partition_position desc ) loop -- wait for processes which might have a lock on the partition to drop (max 10min) execute immediate 'lock table '||p_table_name||' partition ('||cur.partition_name||') in exclusive mode wait 600'; execute immediate 'alter table '||p_table_name||' drop partition '||cur.partition_name || ' UPDATE GLOBAL INDEXES'; end loop; end; / But I guess this is doing serial drop for each partitions thereby creating many alter statements instead of 1 step command where all the necessary partitions are dropped. How can we adapt this ?

I've got Oracle 12c data warehouse database on oracle Linux 7.3, and there's job that truncating one table. Table has size of 5GB And always truncate takes about hour with waiting for "db file async i/o submit". Filesystemio and disk_asynch_io are set to asynch already. What can I do here? Thanks in advance.

I'm trying to create a job in SSIS to truncate a table, read from a data source and rewrite the table, so only 2 steps (1 Truncate and 2 Select + Insert). I need to guarantee a Truncate's Rollback in the event that the insert has errors. I tried with the sequence container and the TransactionOption but SSIS doesn't want to cooperate. PS I can't do too many operations because I have millions of records and I need rapid performance.

I'm running MySQL5.5 with Master/Slave replication (1 master, 2 slaves). I have a process that runs once a week and truncate a specific table. The table is not large and only has a few thousands records. For some reason, the TRUNCATE TABLE command takes really long time to execute (both on the master and on the slave). It takes about 400K ms to execute!! When it runs on the slave, it causes it to lag from the Master. After the TRUNCATE TABLE finishes, everything is back to normal. I know that one of the slaves didn't receive any reads while performing the TRUNCATE TABLE since its a dedicated slave and the process that reads from that slave was down. Also, on this slave, it took the same amount of time to execute. Here is the table structure: http://pastebin.com/qEQB4juR Any thoughts on how I can speed up the TRUNCATE TABLE ?

In my process I have 2 tables. > target table, load table. The target table holds the current data, and a lot of queries are fired against it. When new data coming we use a FULL LOAD and the goal is to switch the current data that in the target table with the new data. The new data exists in the load table. In goal to do that we are using > Exchange partition Those are the steps: 1. create new load table with only 1 partition -all_rows (values less than maxvalue). 2. insert all the data from source to the load table by insert statement. 3. exchange partition of the all_rows (=all the new data) with the target table. That way, we have the most new data, and there is no space that data is not available. The problem start when I want to drop my load table after the exchange. If someone send a query to the target table he can get exception: ORA-08103: Object no longer exists. Even the drop was only on the load_table. Same when load table truncate. * What does that mean? * Those two tables will be together forever? * Every truncate or drop I need to be afraid on the second one? * Why they are connected at all? I'm using oracle 11g. My actions: CREATE TABLE tbl_ld partition by range (col1) (partition all_rows values less than (maxvalue) AS SELECT * FROM tbl_tgt WHERE 1=2 ; insert into tbl_ld select * from tbl_temp; alter table tbl_ld exchange partition all_rows with table tbl_tgt; -- drop or truncate drop table tbl_ld; truncate table tbl_ld;

I have only 2GB left, so I need to remove this history table. This table now is empty but the database disk space not released. And the database file is 320GB.

In the system I work on there are a lot of stored procedures and SQL scripts that make use of temporary tables. After using these tables it's good practice to drop them. Many of my colleagues (almost all of whom are much more experienced than I am) typically do this: TRUNCATE TABLE #mytemp DROP TABLE #mytemp I typically use a single DROP TABLE in my scripts. Is there any good reason for doing a TRUNCATE immediately before a DROP?