On Wed, 2005-06-01 at 16:34 +0800, Christopher Kings-Lynne wrote:
> > There are some other arguments in favour of a LOAD command.... Alon?
>
> We already have LOAD, so you'll have to choose something else

Its annoying, I grant you. :-)

LOAD 'library' would still need to be the default.

LOAD LIBRARY 'library' would be the new recommended usage.

LOAD DATA... would be the new command... with most other options hanging
off of that. There's no problem with that, since that is then the same
as Oracle syntax for the load utility.

Best Regards, Simon Riggs



---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

On Wed, 2005-06-01 at 04:44 -0400, Greg Stark wrote:
> Greg Stark <gsstark@MIT.EDU> writes:
>
> > For CREATE TABLE AS in the non-PITR case you don't really need to WAL log the
> > records at all. If it fails in the middle you just drop the table. When it
> > completes you do a checkpoint before acknowledging the COMMIT.
> >
> > I think this is already done for CREATE INDEX/REINDEX, also only in the
> > non-PITR case.
>
> Sorry to followup to my own message, but it occurs to me that COPY could be
> made to automatically do this for the case of an empty destination table too.
>
> I'm not sure if it should automatically check for an empty table or if there
> should be an option for the user to indicate he wants COPY to replace the
> current contents entirely. The latter might actually be more useful. .
>
> But either way, you just WAL log a record indicating that the table should be
> entirely empty. Then you fill it up without logging anything. Do a checkpoint
> and then WAL log that the COPY is finished. If any failure occurs replay
> leaves it empty.
>
> Again this sadly only works in the non-PITR case.

Yes, all of the above could work.

It would use essentially the same functionality that Manfred suggested
for handling truncated tables. Ignore the first LOAD DATA started
message until recovery completes, then truncate table if the LOAD DATA
complete message was not logged in wal.

Best Regards, Simon Riggs


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On K, 2005-06-01 at 00:01 +0100, Simon Riggs wrote:
> Recent test results have shown a substantial performance improvement
> (+25%) if WAL logging is disabled for large COPY statements. This is to
> be expected, though has a price attached: losing the ability to crash
> recover data loaded in this manner.

Not only recover the DB itself but also having a hot standby (and
hopefully a read-only replica some time in the future).

> There are two parts to this proposal. First, when and whether to do this
> at all. Second, syntax and invocation.

I think this should be a decision done when creating a table, just like
TEMP tables. So you always know if a certain table is or is not
safe/replicated/recoverable.

This has also the advantage of requiring no changes to actual COPY and
INSERT commands.

--
Hannu Krosing <hannu@skype.net>


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Simon Riggs wrote:
> On Wed, 2005-06-01 at 04:44 -0400, Greg Stark wrote:
> > Greg Stark <gsstark@MIT.EDU> writes:
> >
> > > For CREATE TABLE AS in the non-PITR case you don't really need to WAL log the
> > > records at all. If it fails in the middle you just drop the table. When it
> > > completes you do a checkpoint before acknowledging the COMMIT.
> > >
> > > I think this is already done for CREATE INDEX/REINDEX, also only in the
> > > non-PITR case.
> >
> > Sorry to followup to my own message, but it occurs to me that COPY could be
> > made to automatically do this for the case of an empty destination table too.
> >
> > I'm not sure if it should automatically check for an empty table or if there
> > should be an option for the user to indicate he wants COPY to replace the
> > current contents entirely. The latter might actually be more useful. .
> >
> > But either way, you just WAL log a record indicating that the table should be
> > entirely empty. Then you fill it up without logging anything. Do a checkpoint
> > and then WAL log that the COPY is finished. If any failure occurs replay
> > leaves it empty.
> >
> > Again this sadly only works in the non-PITR case.
>
> Yes, all of the above could work.
>
> It would use essentially the same functionality that Manfred suggested
> for handling truncated tables. Ignore the first LOAD DATA started
> message until recovery completes, then truncate table if the LOAD DATA
> complete message was not logged in wal.

Well, why not just add this functionality to COPY rather than create a
new command? One optimization is to write the dirty shared buffers to
the kernel then fsync that relation, rather than do a checkpoint.

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Greg Stark <gsstark@mit.edu> writes:
> Sorry to followup to my own message, but it occurs to me that COPY could be
> made to automatically do this for the case of an empty destination table too.

Not unless you are proposing to change COPY to acquire a lock strong
enough to lock out other writers to the table for the duration ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Simon Riggs <simon@2ndquadrant.com> writes:
> On Wed, 2005-06-01 at 16:34 +0800, Christopher Kings-Lynne wrote:
> There are some other arguments in favour of a LOAD command.... Alon?
>>
>> We already have LOAD, so you'll have to choose something else

> Its annoying, I grant you. :-)

> LOAD 'library' would still need to be the default.

> LOAD LIBRARY 'library' would be the new recommended usage.

> LOAD DATA... would be the new command... with most other options hanging
> off of that. There's no problem with that, since that is then the same
> as Oracle syntax for the load utility.

Uh, what's wrong with adding an option to COPY? Not like it hasn't got
a ton of 'em already. The Oracle-compatibility angle doesn't interest
me at all, mainly because I find it highly improbable that we'd be exactly
compatible anyway.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

On 01 Jun 2005 04:44:24 -0400, Greg Stark wrote:
> Greg Stark writes:
>>
>> For CREATE TABLE AS in the non-PITR case you don't really need to WAL log the
>> records at all. If it fails in the middle you just drop the table. When it
>> completes you do a checkpoint before acknowledging the COMMIT.
>>
>> I think this is already done for CREATE INDEX/REINDEX, also only in the
>> non-PITR case.

Checkpoint or fsync?


> Sorry to followup to my own message, but it occurs to me that COPY could be
> made to automatically do this for the case of an empty destination table too.

Why only on an empty table? What is the problem with bypassing WAL on
any table as long as all files of that table are fsync'ed before
commit?


> Again this sadly only works in the non-PITR case.

Apart from that problem of course

Jochem

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Tom Lane wrote:
> Greg Stark <gsstark@mit.edu> writes:
> > Sorry to followup to my own message, but it occurs to me that COPY could be
> > made to automatically do this for the case of an empty destination table too.
>
> Not unless you are proposing to change COPY to acquire a lock strong
> enough to lock out other writers to the table for the duration ...

Well, if the table is initally empty, what harm is there in locking the
table? How many people query the table while it is being loaded, and
because the transaction isn't committed, the table is empty to everyone
else anyway.

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Jochem van Dieten wrote:
> On 01 Jun 2005 04:44:24 -0400, Greg Stark wrote:
> > Greg Stark writes:
> >>
> >> For CREATE TABLE AS in the non-PITR case you don't really need to WAL log the
> >> records at all. If it fails in the middle you just drop the table. When it
> >> completes you do a checkpoint before acknowledging the COMMIT.
> >>
> >> I think this is already done for CREATE INDEX/REINDEX, also only in the
> >> non-PITR case.
>
> Checkpoint or fsync?
>
>
> > Sorry to followup to my own message, but it occurs to me that COPY could be
> > made to automatically do this for the case of an empty destination table too.
>
> Why only on an empty table? What is the problem with bypassing WAL on
> any table as long as all files of that table are fsync'ed before
> commit?

Because adding rows to a table might modify existing pages, and if the
COPY fails, you have to restore those pages to a consistent state, and
make sure they are recovered for partial page writes, which we can't do
without WAL. With an initially empty table, you can just throw away the
file system file.

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> Not unless you are proposing to change COPY to acquire a lock strong
>> enough to lock out other writers to the table for the duration ...

> Well, if the table is initally empty, what harm is there in locking the
> table?

You cannot *know* whether it is empty unless you lock the table before
you look. So your argument is circular.

I think this only makes sense as an explicit option to COPY, one of the
effects of which would be to take a stronger lock than COPY normally does.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly