On Fri, Sep 02, 2005 at 04:02:08PM -0400, Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> THe only fundamental disadvantage that COPY labors under is having to
> >> write WAL records. It might be interesting to do something similar to
> >> the recent hacks for CREATE TABLE AS, so that a COPY into a table just
> >> created in the current transaction would skip writing WAL and instead
> >> fsync the table at the end.
>
> > Added to TODO:
> > o Allow COPY into an empty table to skip WAL logging
>
> It has to be a *new* table, not an *empty* table. If it's already
> visible to other xacts then somebody else could insert into it in
> parallel with you, because COPY doesn't take an exclusive lock.

What about the indexes? Logging one of the inserters and not the other
is certain to corrupt the whole thing. (Logging index insertion but not
the heap itself is silly, but perhaps an easy way out is to disable the
feature for tables with indexes.)

> Contrariwise, it doesn't really matter (I think) if there are WAL-logged
> records already in the table and COPY is adding more that aren't logged.

Only if the page is locked in a fashion that the bulk loader can't
insert tuples into a page that the other transaction is using. (Not
sure if this can happen in reality.) Else we risk both inserting a
tuple in the same page, and on recovery finding out that somebody else
used the tuple slot.

--
Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com
"Los románticos son seres que mueren de deseos de vida"

---------------------------(end of broadcast)---------------------------
TIP 1: 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

Tom, Alvaro,

> > It has to be a *new* table, not an *empty* table. If it's already
> > visible to other xacts then somebody else could insert into it in
> > parallel with you, because COPY doesn't take an exclusive lock.

There's still major gains to be had, for ETL, in being able to disable
logging on new tables/partitions. *particularly* partitions.

> Contrariwise, it doesn't really matter (I think) if there are WAL-logged
> records already in the table and COPY is adding more that aren't logged.
> (You might have to force COPY to start adding the rows on freshly added
> pages ... hmm ... all of a sudden I think we had this discussion
> already? I for sure remember the fresh-pages trick from some other
> thread.)

Yes, and that's what shot the proposal down before. But I don't think we
devoted sufficient discussion to the "new table" case.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

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

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> On Fri, Sep 02, 2005 at 04:02:08PM -0400, Tom Lane wrote:
>> It has to be a *new* table, not an *empty* table. If it's already
>> visible to other xacts then somebody else could insert into it in
>> parallel with you, because COPY doesn't take an exclusive lock.

> What about the indexes? Logging one of the inserters and not the other
> is certain to corrupt the whole thing.

Good point, but that fits in just fine with the restriction to
just-created tables.

>> Contrariwise, it doesn't really matter (I think) if there are WAL-logged
>> records already in the table and COPY is adding more that aren't logged.

> Only if the page is locked in a fashion that the bulk loader can't
> insert tuples into a page that the other transaction is using.

What other transaction? The point I was making is that
BEGIN;
CREATE TABLE ...
INSERT ...
COPY ...
is still optimizable. There isn't going to be anyone competing with
the COPY while it runs.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: 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

On Fri, Sep 02, 2005 at 04:27:59PM -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:

> >> Contrariwise, it doesn't really matter (I think) if there are WAL-logged
> >> records already in the table and COPY is adding more that aren't logged.
>
> > Only if the page is locked in a fashion that the bulk loader can't
> > insert tuples into a page that the other transaction is using.
>
> What other transaction? The point I was making is that
> BEGIN;
> CREATE TABLE ...
> INSERT ...
> COPY ...
> is still optimizable. There isn't going to be anyone competing with
> the COPY while it runs.

Sure. I was thinking that you were looking for a mechanism to relax the
other restriction.

--
Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com
Dios hizo a Adán, pero fue Eva quien lo hizo hombre.

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

On Fri, Sep 02, 2005 at 01:30:58PM -0700, Josh Berkus wrote:

> > Contrariwise, it doesn't really matter (I think) if there are WAL-logged
> > records already in the table and COPY is adding more that aren't logged.
> > (You might have to force COPY to start adding the rows on freshly added
> > pages ... hmm ... all of a sudden I think we had this discussion
> > already? I for sure remember the fresh-pages trick from some other
> > thread.)
>
> Yes, and that's what shot the proposal down before. But I don't think we
> devoted sufficient discussion to the "new table" case.

If we are going to have real partitioning sometime soon, I don't think
the restriction is a problem. You may have to load a whole partition
again, which may be faster than using logged COPY to an already-filled
partition. The point is, it's not the whole table, just a partition.

--
Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com
"Acepta los honores y aplausos y perderás tu libertad"

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

Tom Lane wrote:
> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> > On Fri, Sep 02, 2005 at 04:02:08PM -0400, Tom Lane wrote:
> >> It has to be a *new* table, not an *empty* table. If it's already
> >> visible to other xacts then somebody else could insert into it in
> >> parallel with you, because COPY doesn't take an exclusive lock.
>
> > What about the indexes? Logging one of the inserters and not the other
> > is certain to corrupt the whole thing.
>
> Good point, but that fits in just fine with the restriction to
> just-created tables.

Seem the newly created table could have an index, but we would skip
logging on that too and create a zero-length file on crash restore.

> >> Contrariwise, it doesn't really matter (I think) if there are WAL-logged
> >> records already in the table and COPY is adding more that aren't logged.
>
> > Only if the page is locked in a fashion that the bulk loader can't
> > insert tuples into a page that the other transaction is using.
>
> What other transaction? The point I was making is that
> BEGIN;
> CREATE TABLE ...
> INSERT ...
> COPY ...
> is still optimizable. There isn't going to be anyone competing with
> the COPY while it runs.

Updated TODO:

o Allow COPY on a newly-created table to skip WAL logging

On crash recovery, the table involved in the COPY would
have its heap and index files truncated. One issue is
that no other backend should be able to add to the table
at the same time, which is something that is currently
allowed.

--
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 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Updated TODO:

> o Allow COPY on a newly-created table to skip WAL logging

> On crash recovery, the table involved in the COPY would
> have its heap and index files truncated. One issue is
> that no other backend should be able to add to the table
> at the same time, which is something that is currently
> allowed.

This is simply wrong. (1) a table created in the current transaction
isn't visible to anyone else, (2) the correct rollback state is for
it not to be there, rather than be there and empty.

regards, tom lane

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

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Updated TODO:
>
> > o Allow COPY on a newly-created table to skip WAL logging
>
> > On crash recovery, the table involved in the COPY would
> > have its heap and index files truncated. One issue is
> > that no other backend should be able to add to the table
> > at the same time, which is something that is currently
> > allowed.
>
> This is simply wrong. (1) a table created in the current transaction
> isn't visible to anyone else, (2) the correct rollback state is for
> it not to be there, rather than be there and empty.

New text:

o Allow COPY on a newly-created table to skip WAL logging

On crash recovery, the table involved in the COPY would
removed or have its heap and index files truncated. One
issue is that no other backend should be able to add to
the table at the same time, which is something that is
currently allowed.

I think we can lock a zero-length table and do this optimization.

--
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 5: don't forget to increase your free space map settings

On Fri, Sep 02, 2005 at 05:18:09PM -0400, Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Updated TODO:
>
> > o Allow COPY on a newly-created table to skip WAL logging
>
> > On crash recovery, the table involved in the COPY would
> > have its heap and index files truncated. One issue is
> > that no other backend should be able to add to the table
> > at the same time, which is something that is currently
> > allowed.
>
> This is simply wrong. (1) a table created in the current transaction
> isn't visible to anyone else, (2) the correct rollback state is for
> it not to be there, rather than be there and empty.

As a related note:

I remember somebody mentioned some time ago that if you create a table
and then crash before ending the transaction, the tuple in pg_class is
no longer valid, but the file remains. I think this will be a much
worse problem if we allow a table that's being COPY'ed to remain after a
crash.

--
Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com
Oh, oh, las chicas galacianas, lo harán por las perlas,
¡Y las de Arrakis por el agua! Pero si buscas damas
Que se consuman como llamas, ¡Prueba una hija de Caladan! (Gurney Halleck)

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> I remember somebody mentioned some time ago that if you create a table
> and then crash before ending the transaction, the tuple in pg_class is
> no longer valid, but the file remains.

Right --- it will be removed on transaction rollback, but not if the
backend crashes first. There was a patch submitted earlier this year to
try to clean out such files, but it got rejected (as too messy IIRC).
I think we still have a TODO item about it.

regards, tom lane

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