Re: [HACKERS] WORM and Read Only Tables (v0.1)

2007-12-19 Thread Hannu Krosing
Ühel kenal päeval, E, 2007-12-17 kell 09:20, kirjutas Simon Riggs: On Sat, 2007-12-15 at 13:32 +0100, Albert Cervera i Areny wrote: Read-Only Tables Postgres supports the concept of freezing tuples, so they can live forever within the database without needing further

Re: [HACKERS] WORM and Read Only Tables (v0.1)

2007-12-17 Thread Simon Riggs
On Sat, 2007-12-15 at 13:32 +0100, Albert Cervera i Areny wrote: Read-Only Tables Postgres supports the concept of freezing tuples, so they can live forever within the database without needing further writes. Currently there is no command that will guarantee that a table

Re: [HACKERS] WORM and Read Only Tables (v0.1)

2007-12-15 Thread Albert Cervera i Areny
Read-Only Tables Postgres supports the concept of freezing tuples, so they can live forever within the database without needing further writes. Currently there is no command that will guarantee that a table has been completely frozen. This makes it difficult to reliably

Re: [HACKERS] WORM and Read Only Tables (v0.1)

2007-12-12 Thread Josh Berkus
Simon, Use Case: VLDB with tons of (now) read only data, some not. Data needs to be accessible, but data itself is rarely touched, allowing storage costs to be minimised via a storage hierarchy of progressively cheaper storage. There's actually 2 cases to optimize for: 1)

Re: [HACKERS] WORM and Read Only Tables (v0.1)

2007-12-12 Thread Simon Riggs
On Tue, 2007-12-11 at 20:30 -0800, Josh Berkus wrote: Simon, Use Case: VLDB with tons of (now) read only data, some not. Data needs to be accessible, but data itself is rarely touched, allowing storage costs to be minimised via a storage hierarchy of progressively cheaper storage.

Re: [HACKERS] WORM and Read Only Tables (v0.1)

2007-12-12 Thread Zeugswetter Andreas ADI SD
There are a number of nasty limitations for partitions currently (not the least of which is that real uniqueness guarantees are impractical), Just to add an other opinion to this statement, because it imho sounds overly pessimistic: Uniqueness is currently perfectly practical, when the

Re: [HACKERS] WORM and Read Only Tables (v0.1)

2007-12-12 Thread Zeugswetter Andreas ADI SD
Getting partitioning/read-only right will allow 70+TB of that to be on tape or similar, which with compression can be reduced to maybe 20TB? I don't want to promise any particular compression ratio, but it will make a substantial difference, as I'm sure you realise. Wouldn't one very

Re: [HACKERS] WORM and Read Only Tables (v0.1)

2007-12-12 Thread Andrew Sullivan
On Wed, Dec 12, 2007 at 12:14:43PM +0100, Zeugswetter Andreas ADI SD wrote: Uniqueness is currently perfectly practical, when the unique index contains the column[s] that is/are used in a non overlapping partitioning scheme. Well, yes, assuming you have no bugs. Part of the reason I want the

Re: [HACKERS] WORM and Read Only Tables (v0.1)

2007-12-12 Thread Andrew Sullivan
On Wed, Dec 12, 2007 at 12:58:11PM +0100, Zeugswetter Andreas ADI SD wrote: Wouldn't one very substantial requirement of such storage be to have it independent of db version, or even db product? Keeping old hardware and software around can be quite expensive. This was one of the explicit

Re: [HACKERS] WORM and Read Only Tables (v0.1)

2007-12-12 Thread Simon Riggs
On Wed, 2007-12-12 at 11:22 -0500, Andrew Sullivan wrote: On Wed, Dec 12, 2007 at 12:14:43PM +0100, Zeugswetter Andreas ADI SD wrote: Uniqueness is currently perfectly practical, when the unique index contains the column[s] that is/are used in a non overlapping partitioning scheme. Well,

Re: [HACKERS] WORM and Read Only Tables (v0.1)

2007-12-12 Thread Simon Riggs
On Wed, 2007-12-12 at 10:48 -0800, Josh Berkus wrote: Andrew, The requirement was, anyway, that we be able to read old versions of archived rows. IIRC there was an implementation choice, whether we would _never_ allow such rows to be SET READ WRITE or whether they'd be immediately

Re: [HACKERS] WORM and Read Only Tables (v0.1)

2007-12-12 Thread Josh Berkus
Andrew, The requirement was, anyway, that we be able to read old versions of archived rows. IIRC there was an implementation choice, whether we would _never_ allow such rows to be SET READ WRITE or whether they'd be immediately upgraded to the present format on SET READ WRITE. Well, in

Re: [HACKERS] WORM and Read Only Tables (v0.1)

2007-12-12 Thread Joshua D. Drake
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 12 Dec 2007 19:07:57 + Simon Riggs [EMAIL PROTECTED] wrote: I seem to be the only one saying global indexes are bad, so if people that want them can do the math and honestly say they want them, then I will listen. global indexes are

Re: [HACKERS] WORM and Read Only Tables (v0.1)

2007-12-12 Thread Josh Berkus
Simon, Who was working on it? Zdenec and Dhanaraj from Sun, and someone from EDB (I'm not sure who, maybe Korry?). Unfortunately, both companies have shifted staff around and we need to re-start work. Of course, if hackers other than those from EDB Sun want to attack the problem, the more

Re: [HACKERS] WORM and Read Only Tables (v0.1)

2007-12-12 Thread Andrew Sullivan
On Wed, Dec 12, 2007 at 07:07:57PM +, Simon Riggs wrote: Enforcing uniqueness with a global index has a number of disadvantages. This is why I was trying to talk about constraints rather than global indexes. Just because we happen to implement them that way today does not mean that such

[HACKERS] WORM and Read Only Tables (v0.1)

2007-12-11 Thread Simon Riggs
Many applications have the need to archive data after it has been through the initial flurry of reads and updates that follows its original insertion. Currently there is no specific feature support to meet this requirement, so I propose to add this for 8.4. Use Case: VLDB with tons of (now) read

Re: [HACKERS] WORM and Read Only Tables (v0.1)

2007-12-11 Thread Gregory Stark
Simon Riggs [EMAIL PROTECTED] writes: So... VACUUM FREEZE table SET READ ONLY; would be my first thought, but I'm guessing everybody will press me towards supporting the more obvious ALTER TABLE table SET READ ONLY; This command will place a ShareLock (only) on the table, preventing

Re: [HACKERS] WORM and Read Only Tables (v0.1)

2007-12-11 Thread Csaba Nagy
On Tue, 2007-12-11 at 11:12 +, Simon Riggs wrote: Features - Read Only Tables - Compressed Tablespaces I wonder if instead of read-only tables wouldn't it be better to have some kind of automatic partitioning which permits to have different chunks of the table data in different

Re: [HACKERS] WORM and Read Only Tables (v0.1)

2007-12-11 Thread Csaba Nagy
On Tue, 2007-12-11 at 13:44 +0100, Csaba Nagy wrote: Another advantage I guess would be that active data would more likely stay in cache, as updated records would stay together and not spread over the inactive. And I forgot to mention that vacuum could mostly skip the archive part, and only

Re: [HACKERS] WORM and Read Only Tables (v0.1)

2007-12-11 Thread Hannu Krosing
Ühel kenal päeval, T, 2007-12-11 kell 13:44, kirjutas Csaba Nagy: On Tue, 2007-12-11 at 11:12 +, Simon Riggs wrote: Features - Read Only Tables - Compressed Tablespaces I wonder if instead of read-only tables wouldn't it be better to have some kind of automatic partitioning which

Re: [HACKERS] WORM and Read Only Tables (v0.1)

2007-12-11 Thread Csaba Nagy
On Tue, 2007-12-11 at 14:58 +0200, Hannu Krosing wrote: Ühel kenal päeval, T, 2007-12-11 kell 13:44, kirjutas Csaba Nagy: Then put the active chunk on a high performance file system and the archive tablespace on a compressed/slow/cheap file system and you're done. Allow even the archive

Re: [HACKERS] WORM and Read Only Tables (v0.1)

2007-12-11 Thread Trevor Talbot
On 12/11/07, Simon Riggs [EMAIL PROTECTED] wrote: Compressed Tablespaces Using a streaming library like zlib, it will be easy to read/write data files into a still-usable form but with much reduced size. Access to a compressed table only makes sense as a SeqScan. That would be handled by

Re: [HACKERS] WORM and Read Only Tables (v0.1)

2007-12-11 Thread Peter Childs
On 11/12/2007, Csaba Nagy [EMAIL PROTECTED] wrote: On Tue, 2007-12-11 at 14:58 +0200, Hannu Krosing wrote: Ühel kenal päeval, T, 2007-12-11 kell 13:44, kirjutas Csaba Nagy: Then put the active chunk on a high performance file system and the archive tablespace on a compressed/slow/cheap

Re: [HACKERS] WORM and Read Only Tables (v0.1)

2007-12-11 Thread Peter Childs
On 11/12/2007, Simon Riggs [EMAIL PROTECTED] wrote: Attach -- Writing tables on one system and then moving that data to other systems is fairly common. If we supported read-only tables then you might consider how you would publish new versions to people. For now, we just want to

Re: [HACKERS] WORM and Read Only Tables (v0.1)

2007-12-11 Thread Simon Riggs
On Tue, 2007-12-11 at 11:49 +, Gregory Stark wrote: Simon Riggs [EMAIL PROTECTED] writes: So... VACUUM FREEZE table SET READ ONLY; would be my first thought, but I'm guessing everybody will press me towards supporting the more obvious ALTER TABLE table SET READ ONLY; This

Re: [HACKERS] WORM and Read Only Tables (v0.1)

2007-12-11 Thread Tom Lane
Simon Riggs [EMAIL PROTECTED] writes: This command will place a ShareLock (only) on the table, preventing anybody from writing to the table while we freeze it. The ShareLock is incompatible with any transaction that has written to the table, so when we acquire the lock all writers to the table

Re: [HACKERS] WORM and Read Only Tables (v0.1)

2007-12-11 Thread Simon Riggs
On Tue, 2007-12-11 at 10:19 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: This command will place a ShareLock (only) on the table, preventing anybody from writing to the table while we freeze it. The ShareLock is incompatible with any transaction that has written to the

Re: [HACKERS] WORM and Read Only Tables (v0.1)

2007-12-11 Thread Simon Riggs
On Tue, 2007-12-11 at 13:44 +0100, Csaba Nagy wrote: On Tue, 2007-12-11 at 11:12 +, Simon Riggs wrote: Features - Read Only Tables - Compressed Tablespaces I wonder if instead of read-only tables wouldn't it be better to have some kind of automatic partitioning That's definitely

Re: [HACKERS] WORM and Read Only Tables (v0.1)

2007-12-11 Thread Andrew Sullivan
On Tue, Dec 11, 2007 at 11:12:46AM +, Simon Riggs wrote: Read-Only Tables In the past when this topic came up, there was some discussion of doing this at a level somewhere below the table horizon. There are a number of nasty limitations for partitions currently (not the

Re: [HACKERS] WORM and Read Only Tables (v0.1)

2007-12-11 Thread Simon Riggs
On Tue, 2007-12-11 at 14:25 -0500, Andrew Sullivan wrote: On Tue, Dec 11, 2007 at 11:12:46AM +, Simon Riggs wrote: Read-Only Tables In the past when this topic came up, there was some discussion of doing this at a level somewhere below the table horizon. There are