On Fri, Dec 7, 2012 at 6:29 AM, Igor Neyman <ineyman@xxxxxxxxxxxxxx> wrote: >> -----Original Message----- >> From: Sergey Konoplev [mailto:gray.ru@xxxxxxxxx] >> Sent: Thursday, December 06, 2012 4:52 PM >> To: Igor Neyman >> Cc: pgsql-general@xxxxxxxxxxxxxx >> Subject: Re: unlooged tables >> >> Hi, >> >> On Thu, Dec 6, 2012 at 7:08 AM, Igor Neyman <ineyman@xxxxxxxxxxxxxx> >> 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@xxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
