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
