System administration functions about relation size ignore changes in the table structure

Поиск
Список
Период
Сортировка
От Erki Eessaar
Тема System administration functions about relation size ignore changes in the table structure
Дата
Msg-id AM9PR01MB82688DEDD3B7D0D85A58BDE3FED1A@AM9PR01MB8268.eurprd01.prod.exchangelabs.com
обсуждение исходный текст
Ответы Re: System administration functions about relation size ignore changes in the table structure
Список pgsql-bugs
Дерево обсуждения
Hello

According to the example at the end of the letter the functions pg_total_relation_size,  pg_table_size and pg_indexes_size seem to ignore adding or removing columns from a table.

I first noticed it in case of stored generated columns but the same applies to regular columns as well.

I tested it in PostgreSQL 16.

Best regards
Erki Eessaar
*******************************
CREATE TABLE Invoice (invoice_code INTEGER NOT NULL,
client_id INTEGER NOT NULL,
invoice_date DATE NOT NULL,
invoice_year SMALLINT GENERATED ALWAYS AS (extract(year FROM invoice_date)) STORED NOT NULL,
invoice_month SMALLINT GENERATED ALWAYS AS (extract(month FROM invoice_date)) STORED NOT NULL,
CONSTRAINT pk_invoice PRIMARY KEY (invoice_code));

/*I generate 15000 rows.*/
INSERT INTO Invoice (invoice_code, client_id, invoice_date)
SELECT generator AS invoice_code,
floor(random() * (1_000 - 1 + 1) + 1)::int AS client_id,
'2015-01-01'::date + floor(random() * (current_date - '2015-01-01' + 1) + 1)::int AS invoice_date
FROM generate_series(1, 15_000) AS generator;

SELECT pg_total_relation_size('invoice'::regclass) AS reslt;
--Result: 1056768

SELECT pg_table_size('invoice'::regclass)+pg_indexes_size('invoice'::regclass) AS reslt;
--Result: 1056768

ALTER TABLE Invoice DROP invoice_year;
ALTER TABLE Invoice DROP invoice_month;
VACUUM ANALYZE;

SELECT pg_total_relation_size('invoice'::regclass) AS reslt;
--Result: 1056768

SELECT pg_table_size('invoice'::regclass)+pg_indexes_size('invoice'::regclass) AS reslt;
--Result: 1056768

I removed stored(!) generated columns but the size does not change.

DROP TABLE Invoice;

CREATE TABLE Invoice (invoice_code INTEGER NOT NULL,
client_id INTEGER NOT NULL,
invoice_date DATE NOT NULL);

INSERT INTO Invoice (invoice_code, client_id, invoice_date)
SELECT generator AS invoice_code,
floor(random() * (1_000 - 1 + 1) + 1)::int AS client_id,
'2015-01-01'::date + floor(random() * (current_date - '2015-01-01' + 1) + 1)::int AS invoice_date
FROM generate_series(1, 15_000) AS generator;

SELECT pg_total_relation_size('invoice'::regclass) AS reslt;
--Result: 704512

SELECT pg_table_size('invoice'::regclass)+pg_indexes_size('invoice'::regclass) AS reslt;
--Result: 704512

ALTER TABLE Invoice ADD COLUMN invoice_year SMALLINT GENERATED ALWAYS AS (extract(year FROM invoice_date)) STORED NOT NULL;
ALTER TABLE Invoice ADD COLUMN invoice_month SMALLINT GENERATED ALWAYS AS (extract(month FROM invoice_date)) STORED NOT NULL;

SELECT pg_total_relation_size('invoice'::regclass) AS reslt;
--Result: 704512

SELECT pg_table_size('invoice'::regclass)+pg_indexes_size('invoice'::regclass) AS reslt;
--Result: 704512

I added stored(!) generated columns but the size does not change.

ALTER TABLE Invoice DROP COLUMN invoice_month;

ALTER TABLE Invoice DROP COLUMN invoice_year;

ALTER TABLE Invoice DROP COLUMN invoice_date;

VACUUM ANALYZE;

SELECT pg_total_relation_size('invoice'::regclass) AS reslt;
--Result: 704512

SELECT pg_table_size('invoice'::regclass)+pg_indexes_size('invoice'::regclass) AS reslt;
--Result: 704512

The size is still the same.

В списке pgsql-bugs по дате отправления: