unlooged tables

Started by Igor Neymanover 13 years ago5 messagesgeneral

ineyman@perceptron.com

over 13 years ago

Hello,

Is there any way to change "regular" table to "unlogged" without dropping said table and recreating it as unlogged?
Didn't find the answer in the docs. Looks like "alter table ..." does not support "unlogged.

TIA,
Igor Neyman

Sergey Konoplev

gray.ru@gmail.com

over 13 years ago

In reply to: Igor Neyman (#1)

Re: unlooged tables

Hi,

On Thu, Dec 6, 2012 at 7:08 AM, Igor Neyman <ineyman@perceptron.com> wrote:

Is there any way to change “regular” table to “unlogged” without dropping
said table and recreating it as unlogged?

AFAIK it is impossible currently.

The best way to do such transformation that comes to my mind is:

CREATE TABLE table1 (
id bigserial PRIMARY KEY,
data text
);

INSERT INTO table1 (data)
SELECT 'bla' || i::text
FROM generate_series(1, 10) AS i;

SELECT * FROM table1;

CREATE UNLOGGED TABLE tmp (LIKE table1 INCLUDING ALL);
ALTER TABLE table1 INHERIT tmp;

BEGIN;
ALTER TABLE table1 RENAME TO table1_old;
ALTER TABLE tmp RENAME TO table1;
END;

So new rows will be inserted into the new unlogged table and old rows
will be available from the old one.

INSERT INTO table1 (data)
SELECT 'bla' || i::text
FROM generate_series(11, 15) AS i;

UPDATE table1 SET data = 'mla' || i::text WHERE i <= 5;

SELECT * FROM table1;

And then all we need is move the data to the new table and finish with
the old one.

ALTER SEQUENCE table1_id_seq OWNED BY table1.id;

BEGIN;
INSERT INTO table1 SELECT * FROM table1_old;
DROP TABLE table1_old CASCADE;
END;

SELECT * FROM table1;

Correct me if I misunderstand something, please.

Didn’t find the answer in the docs. Looks like “alter table …” does not
support “unlogged.

TIA,

Igor Neyman

--
Sergey Konoplev
Database and Software Architect
http://www.linkedin.com/in/grayhemp

Phones:
USA +1 415 867 9984
Russia, Moscow +7 901 903 0499
Russia, Krasnodar +7 988 888 1979

Skype: gray-hemp
Jabber: gray.ru@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Igor Neyman

ineyman@perceptron.com

over 13 years ago

In reply to: Sergey Konoplev (#2)

Re: unlooged tables

-----Original Message-----
From: Sergey Konoplev [mailto:gray.ru@gmail.com]
Sent: Thursday, December 06, 2012 4:52 PM
To: Igor Neyman
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] unlooged tables

Hi,

On Thu, Dec 6, 2012 at 7:08 AM, Igor Neyman <ineyman@perceptron.com>
wrote:

Is there any way to change "regular" table to "unlogged" without
dropping said table and recreating it as unlogged?

AFAIK it is impossible currently.

The best way to do such transformation that comes to my mind is:

CREATE TABLE table1 (
id bigserial PRIMARY KEY,
data text
);

INSERT INTO table1 (data)
SELECT 'bla' || i::text
FROM generate_series(1, 10) AS i;

SELECT * FROM table1;

CREATE UNLOGGED TABLE tmp (LIKE table1 INCLUDING ALL); ALTER TABLE
table1 INHERIT tmp;

BEGIN;
ALTER TABLE table1 RENAME TO table1_old; ALTER TABLE tmp RENAME TO
table1; END;

So new rows will be inserted into the new unlogged table and old rows
will be available from the old one.

INSERT INTO table1 (data)
SELECT 'bla' || i::text
FROM generate_series(11, 15) AS i;

UPDATE table1 SET data = 'mla' || i::text WHERE i <= 5;

SELECT * FROM table1;

And then all we need is move the data to the new table and finish with
the old one.

ALTER SEQUENCE table1_id_seq OWNED BY table1.id;

BEGIN;
INSERT INTO table1 SELECT * FROM table1_old; DROP TABLE table1_old
CASCADE; END;

SELECT * FROM table1;

Correct me if I misunderstand something, please.

Didn't find the answer in the docs. Looks like "alter table ..." does
not support "unlogged.

TIA,

Igor Neyman

--
Sergey Konoplev
Database and Software Architect
http://www.linkedin.com/in/grayhemp

I was hoping, may be hacking pg_catalog, like setting pg_class.relpersistence to 'u' will do the trick (or something like this).

b.t.w. there will be no other active connections, so there is no risk of needing to add/update/delete records in the table while changing to "unlogged".

Regards,
Igor Neyman

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Sergey Konoplev

gray.ru@gmail.com

over 13 years ago

In reply to: Igor Neyman (#3)

Re: unlooged tables

On Fri, Dec 7, 2012 at 6:29 AM, Igor Neyman <ineyman@perceptron.com> wrote:

-----Original Message-----
From: Sergey Konoplev [mailto:gray.ru@gmail.com]
Sent: Thursday, December 06, 2012 4:52 PM
To: Igor Neyman
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] unlooged tables

Hi,

On Thu, Dec 6, 2012 at 7:08 AM, Igor Neyman <ineyman@perceptron.com>
wrote:

Is there any way to change "regular" table to "unlogged" without
dropping said table and recreating it as unlogged?

AFAIK it is impossible currently.

The best way to do such transformation that comes to my mind is:

CREATE TABLE table1 (
id bigserial PRIMARY KEY,
data text
);

INSERT INTO table1 (data)
SELECT 'bla' || i::text
FROM generate_series(1, 10) AS i;

SELECT * FROM table1;

CREATE UNLOGGED TABLE tmp (LIKE table1 INCLUDING ALL); ALTER TABLE
table1 INHERIT tmp;

BEGIN;
ALTER TABLE table1 RENAME TO table1_old; ALTER TABLE tmp RENAME TO
table1; END;

So new rows will be inserted into the new unlogged table and old rows
will be available from the old one.

INSERT INTO table1 (data)
SELECT 'bla' || i::text
FROM generate_series(11, 15) AS i;

UPDATE table1 SET data = 'mla' || i::text WHERE i <= 5;

SELECT * FROM table1;

And then all we need is move the data to the new table and finish with
the old one.

ALTER SEQUENCE table1_id_seq OWNED BY table1.id;

BEGIN;
INSERT INTO table1 SELECT * FROM table1_old; DROP TABLE table1_old
CASCADE; END;

SELECT * FROM table1;

Correct me if I misunderstand something, please.

Didn't find the answer in the docs. Looks like "alter table ..." does
not support "unlogged.

TIA,

Igor Neyman

--
Sergey Konoplev
Database and Software Architect
http://www.linkedin.com/in/grayhemp

I was hoping, may be hacking pg_catalog, like setting pg_class.relpersistence to 'u' will do the trick (or something like this).

I am not sure all this hacks are worth doing. There is no guaranty
that they will work in the future versions even if they work in the
current one.

b.t.w. there will be no other active connections, so there is no risk of needing to add/update/delete records in the table while changing to "unlogged".

Then things are much more simple.

Regards,
Igor Neyman

--
Sergey Konoplev
Database and Software Architect
http://www.linkedin.com/in/grayhemp

Phones:
USA +1 415 867 9984
Russia, Moscow +7 901 903 0499
Russia, Krasnodar +7 988 888 1979

Skype: gray-hemp
Jabber: gray.ru@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Andres Freund

andres@anarazel.de

over 13 years ago

In reply to: Igor Neyman (#3)

Re: unlooged tables

Hi,

On 2012-12-07 14:29:26 +0000, Igor Neyman wrote:

I was hoping, may be hacking pg_catalog, like setting pg_class.relpersistence to 'u' will do the trick (or something like this).

b.t.w. there will be no other active connections, so there is no risk of needing to add/update/delete records in the table while changing to "unlogged".

Nope, thats not enough, won't create the init fork, so your next restart
will probably fail.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general