Thread: unlooged tables
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
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
> -----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 likethis). b.t.w. there will be no other active connections, so there is no risk of needing to add/update/delete records in the tablewhile changing to "unlogged". Regards, Igor Neyman
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 likethis). 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 tablewhile 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
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 likethis). > > b.t.w. there will be no other active connections, so there is no risk of needing to add/update/delete records in the tablewhile 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