Re: unlooged tables

> -----Original Message-----
> From: Sergey Konoplev [mailto:gray(dot)ru(at)gmail(dot)com]
> Sent: Thursday, December 06, 2012 4:52 PM
> To: Igor Neyman
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] unlooged tables
>
> Hi,
>
> On Thu, Dec 6, 2012 at 7:08 AM, Igor Neyman <ineyman(at)perceptron(dot)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