Greetings, * tsunakawa.ta...@fujitsu.com (tsunakawa.ta...@fujitsu.com) wrote: > * ALTER TABLE SET UNLOGGED/LOGGED without data copy > Good: > - Does not require server restart (if this feature can be used in all > wal_level settings). > > Bad: > - The user has to maintain and modify some scripts to use ALTER TABLE when > adding or removing the tables/partitions to load data into. For example, if > the data loading job specifies a partitioned table, he may forget to add > ALTER TABLE for new partitions, resulting in slow data loading.
I'm not sure that I see this as really being much of an issue. Perhaps there are some things we can do, as I mentioned before, to make it easier for users to have tables be created as unlogged from the start, or to be able to ALTER TABLE a bunch of tables at once (using all in tablespace, or maybe having an ALTER TABLE on a partitioned table cascade to the partitions), but overall the risk here seems very low- clearly whatever processing is running to load the data into a particular table knows what the table is and adding an ALTER TABLE into it would be trivial. Specifically, for a partitioned table, I would think the load would go something like: CREATE UNLOGGED TABLE ... load all of the data ALTER TABLE ... SET LOGGED ALTER TABLE ... ATTACH PARTITION If the data load could all be done in a single transaction then you wouldn't even need to create the table as UNLOGGED or issue the SET LOGGED, with wal_level=minimal, you just need to create the table in the same transaction that you do the data load in. > * wal_level = none > Good: > - Easy to use. The user does not have to be aware of what tables are loaded. > This can come in handy when migrating from an older version or another DBMS, > building test databases, and consolidating databases. A GUC that allowed users to set a default for newly created tables to be unlogged would also address this. > Bad: > - Requires server restart. Introducing yet another wal_level strikes me as a very large step, one that the arguments presented here for why it'd be worth it don't come anywhere near justifying that step. > I expect both features will be able to meet our customer's needs. The worst > scenario (I don't want to imagine!) is that neither feature fails to be > committed. So, let us continue both features. I'll join Horiguchi-san's new > thread, and please help us here too. (I'll catch up with the recent > discussion in this thread and reply.) While you're certainly welcome to spend your time where you wish to, if, as you say, making these changes to how tables can be switched from unlogged to logged with wal_level=minimal meets this use-case then that strikes me as definitely the right approach and removes any justification for adding another wal_level. > > Couldn't we have something like the following? > > > > ALTER TABLE table1, table2, table3 SET UNLOGGED; > > > > That is, multiple target object specification in ALTER TABLE sttatement. > > Likewise, can't we do ALTER TABLE SET UNLOGGED/LOGGED against a partitioned > table? Currently, the statement succeeds but none of the partitioned table > nor its partitions is set unlogged (pg_class.relpersistence remains 'p'). Is > this intended? If it's a bug, I'm willing to fix it so that it reports an > eror. Of course, it's good to make all partitions unlogged at once. I agree that this doesn't seem quite right and considering the way other commands work like CREATE INDEX, I would think that doing such an ALTER TABLE would recurse to the individual partitions (skipping over any which are already set to the persistance desired..). Thanks, Stephen
signature.asc
Description: PGP signature
