Thread: NOLOGGING option, or ?

NOLOGGING option, or ?

From
Simon Riggs
Date:
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.

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

Why?

Performance.

The performance gain has a price and so should only be enabled if
requested explicitly by the user. It is up to the user whether they
accept this price, since in many useful cases it is a small price
against a huge saving.

The price is that if a crash occurs, then any table that was not empty
to begin with would not be in a transactionally consistent state
following crash recovery. It may have data in it, but it would be up to
the user to determine whether that was satisfactory or not. It could be
possible to sense what to do in this situation automatically, by putting
the table into a needs-recovery type state... I don't propose to handle
this *at this stage*.

Syntax and invocation:

Previously I had discussed adding a NOLOGGING option onto both COPY and
CREATE TABLE AS SELECT that would bypass the creation of wal logging
data. That is still a possibility, but would require manual code changes
to much of the SQL submitted.

Now, I would like to discuss adding an enable_logging USERSET GUC, that
would apply *only* to COPY and CREATE TABLE AS SELECT. The default of
this would be false.

How can we gain this performance benefit for those willing to accept the
restrictions imposed?

Your comments are sought and are most welcome.

Best Regards, Simon Riggs



Re: NOLOGGING option, or ?

From
Tom Lane
Date:
Simon Riggs <simon@2ndquadrant.com> writes:
> Recent test results have shown a substantial performance improvement
> (+25%) if WAL logging is disabled for large COPY statements.

How much of that is left after we fix the 64-bit-CRC issue?

> Now, I would like to discuss adding an enable_logging USERSET GUC,

[ fear and loathing ... ]

I don't like the idea of a GUC at all, and USERSET is right out.
I think it would have to be system-wide (cf fsync) to be even
implementable let alone somewhat predictable.  Even if it could
be done per-backend with reasonable semantics, random users should
not get to make that decision --- it should be the DBA's call,
which means it needs at least SUSET permissions.

BTW, I'm sure you are the last one who needs to be reminded that
any such thing breaks PITR completely.  Which is surely sufficient
reason not to let it be USERSET.
        regards, tom lane


Re: NOLOGGING option, or ?

From
Alvaro Herrera
Date:
On Tue, May 31, 2005 at 10:47:30PM -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > Recent test results have shown a substantial performance improvement
> > (+25%) if WAL logging is disabled for large COPY statements.

> BTW, I'm sure you are the last one who needs to be reminded that
> any such thing breaks PITR completely.  Which is surely sufficient
> reason not to let it be USERSET.

This doesn't work for COPY, but maybe for CREATE TABLE AS we could log
the fact that the command was executed, so the replayer could execute
the same command again.

Of course, this handwaving doesn't explain how the system in recovery
mode would be able to execute a full query to reconstruct the table, and
also it doesn't say a lot about the extra complexity at the source level
to implement this option.

For people loading big files into the database, maybe we could think
about a command to let a file be loaded directly as initial table
content.  So all that we'd need is a program to write the file, which
could be done externally (The filewriter would have to have access to
the catalog and input functions for the involved types, though I think
for simple types it would be straighforward ... we could write frozen
tuples to avoid TransactionId problems.)

-- 
Alvaro Herrera (<alvherre[a]surnet.cl>)
www.google.com: interfaz de línea de comando para la web.


Re: NOLOGGING option, or ?

From
Neil Conway
Date:
On Wed, 2005-06-01 at 00:40 -0400, Alvaro Herrera wrote:
> This doesn't work for COPY, but maybe for CREATE TABLE AS we could log
> the fact that the command was executed, so the replayer could execute
> the same command again.
> 
> Of course, this handwaving doesn't explain how the system in recovery
> mode would be able to execute a full query to reconstruct the table

There's also the typical problem with this kind of approach: how do you
handle non-deterministic queries? (e.g. "CREATE TABLE ... AS SELECT
random(), gettimeofday(), some_func(t1.x) FROM t1 LIMIT 5")

-Neil




Re: NOLOGGING option, or ?

From
Hans-Jürgen Schönig
Date:
Personally I don't think that it is a good idea to do that.
People will tend to corrupt their systems because they want speed 
(sometimes without thinking about the consequences).

I can only think of one scenario where nologging would actually make 
sense: Many people use session tables to keep track of user level 
information on a website. corrupting a session table (usually not very 
large) would not cause a lot of problems.

Doing it for COPY would be fatal. I can tell you from experience that 
80% of all users will use that if the manual says that PostgreSQL will 
beform better this way. This is a key feature to make people think that 
PostgreSQL is reliable.
Best regards,
    Hans


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.
> 
> There are two parts to this proposal. First, when and whether to do this
> at all. Second, syntax and invocation.
> 
> Why?
> 
> Performance.
> 
> The performance gain has a price and so should only be enabled if
> requested explicitly by the user. It is up to the user whether they
> accept this price, since in many useful cases it is a small price
> against a huge saving.
> 
> The price is that if a crash occurs, then any table that was not empty
> to begin with would not be in a transactionally consistent state
> following crash recovery. It may have data in it, but it would be up to
> the user to determine whether that was satisfactory or not. It could be
> possible to sense what to do in this situation automatically, by putting
> the table into a needs-recovery type state... I don't propose to handle
> this *at this stage*.
> 
> Syntax and invocation:
> 
> Previously I had discussed adding a NOLOGGING option onto both COPY and
> CREATE TABLE AS SELECT that would bypass the creation of wal logging
> data. That is still a possibility, but would require manual code changes
> to much of the SQL submitted.
> 
> Now, I would like to discuss adding an enable_logging USERSET GUC, that
> would apply *only* to COPY and CREATE TABLE AS SELECT. The default of
> this would be false.
> 
> How can we gain this performance benefit for those willing to accept the
> restrictions imposed?
> 
> Your comments are sought and are most welcome.
> 
> Best Regards, Simon Riggs
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend


-- 
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/664/393 39 74
www.cybertec.at, www.postgresql.at



Re: NOLOGGING option, or ?

From
Simon Riggs
Date:
On Tue, 2005-05-31 at 22:47 -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > Recent test results have shown a substantial performance improvement
> > (+25%) if WAL logging is disabled for large COPY statements.
> 
> How much of that is left after we fix the 64-bit-CRC issue?

Well, I don't know. The I/O is the main thing I'm trying to avoid.

> > Now, I would like to discuss adding an enable_logging USERSET GUC,
> 
> [ fear and loathing ... ]

OK. I needed to say the idea, to make sure we had considered it. I now
pronounce it dead and buried.

> BTW, I'm sure you are the last one who needs to be reminded that
> any such thing breaks PITR completely.  Which is surely sufficient
> reason not to let it be USERSET.

You're right, thank you. But I do need to be reminded to say "this would
only apply when archiving is not enabled" just as the other two existing
WAL-avoidance optimisations do.

In answer to the other points raised, the main use case for this
optimisation is to improve data load performance into an empty table.
This is a very timeconsuming stage on a big warehouse and needs
performance optimization. I agree with Hans-Jurgen that this is a
dangerous option for use on general COPY commands, since these can be
used on empty and already populated tables. I seek a way to improve the
main use case though without leaving any danger in other situations.

I have two suggested approaches:
1. Introduce a new LOAD command that only works on empty tables.
Following a crash, the table is dropped and the user accepts that the
action-on-recovery is to reload the table. (Though in PITR mode, the
data would be logged).

2. Introduce NOT LOGGED INITIALLY mode, as DB2 has done. The first COPY
into an empty table would avoid WAL logging, if the user invokes that
option on the specific COPY command.

There are some other arguments in favour of a LOAD command.... Alon?

Best Regards, Simon Riggs



Re: NOLOGGING option, or ?

From
Greg Stark
Date:
Neil Conway <neilc@samurai.com> writes:

> On Wed, 2005-06-01 at 00:40 -0400, Alvaro Herrera wrote:
> > This doesn't work for COPY, but maybe for CREATE TABLE AS we could log
> > the fact that the command was executed, so the replayer could execute
> > the same command again.
> > 
> > Of course, this handwaving doesn't explain how the system in recovery
> > mode would be able to execute a full query to reconstruct the table
> 
> There's also the typical problem with this kind of approach: how do you
> handle non-deterministic queries? (e.g. "CREATE TABLE ... AS SELECT
> random(), gettimeofday(), some_func(t1.x) FROM t1 LIMIT 5")

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.

-- 
greg



Re: NOLOGGING option, or ?

From
Christopher Kings-Lynne
Date:
> There are some other arguments in favour of a LOAD command.... Alon?

We already have LOAD, so you'll have to choose something else :)

Chris



Re: NOLOGGING option, or ?

From
Greg Stark
Date:
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.

-- 
greg



Re: NOLOGGING option, or ?

From
Hannu Krosing
Date:
On K, 2005-06-01 at 09:16 +0100, Simon Riggs wrote:
> On Tue, 2005-05-31 at 22:47 -0400, Tom Lane wrote:
> > Simon Riggs <simon@2ndquadrant.com> writes:
> > > Recent test results have shown a substantial performance improvement
> > > (+25%) if WAL logging is disabled for large COPY statements.
> > 
> > How much of that is left after we fix the 64-bit-CRC issue?
> 
> Well, I don't know. The I/O is the main thing I'm trying to avoid.

While avoiding IO is a good thing in general, WAL IO traffic can at
least easily made parallel to other IO by allocating own disk for WAL.

> > > Now, I would like to discuss adding an enable_logging USERSET GUC,
> > 
> > [ fear and loathing ... ]
> 
> OK. I needed to say the idea, to make sure we had considered it. I now
> pronounce it dead and buried.
> 
> > BTW, I'm sure you are the last one who needs to be reminded that
> > any such thing breaks PITR completely.  

I don't think we do any WAlling of TEMP tables, so it may be easy to
extend this to any table with 'NO_WAL' bit set.

That would create kind of 'extended temp table' - unsafe but fast ;)

-- 
Hannu Krosing <hannu@skype.net>



Re: NOLOGGING option, or ?

From
Simon Riggs
Date:
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




Re: NOLOGGING option, or ?

From
Simon Riggs
Date:
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



Re: NOLOGGING option, or ?

From
Hannu Krosing
Date:
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>



Re: NOLOGGING option, or ?

From
Bruce Momjian
Date:
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,
Pennsylvania19073
 


Re: NOLOGGING option, or ?

From
Tom Lane
Date:
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


Re: NOLOGGING option, or ?

From
Tom Lane
Date:
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


Re: NOLOGGING option, or ?

From
Jochem van Dieten
Date:
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


Re: NOLOGGING option, or ?

From
Bruce Momjian
Date:
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,
Pennsylvania19073
 


Re: NOLOGGING option, or ?

From
Bruce Momjian
Date:
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,
Pennsylvania19073
 


Re: NOLOGGING option, or ?

From
Tom Lane
Date:
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


Re: NOLOGGING option, or ?

From
Dawid Kuroczko
Date:
On 6/1/05, Hans-Jürgen Schönig <postgres@cybertec.at> wrote:
> Personally I don't think that it is a good idea to do that.
> People will tend to corrupt their systems because they want speed
> (sometimes without thinking about the consequences).
>
> I can only think of one scenario where nologging would actually make
> sense: Many people use session tables to keep track of user level
> information on a website. corrupting a session table (usually not very
> large) would not cause a lot of problems.

Well, from what I know, TEMPORARY tables are not WAL-logged, since
they won't exist after "restart" (since none of the current sessions
would exist).  The problem with TEMPORARY tables is that they are not
globally visible.

I think it would be neat to be able to create "server-life-time" tables.
I.e. table which is initially empty, can be used by all users, but is
guaranteed to be empty (truncated) upon server restart.  A place
to store global temporary variables.  A table type to put on a RAM-disk.

Potential uses?  Imagine a site which has a busy 'session' table and
a relatively 'static' other tables.  Most of WALs would consist on
'useless' updates to session table.  And recovery using WAL files
would take longer (as PostgreSQL would have to dump and restore
whole a lot of session data).  Having a "global temporary" table
would be helpful in such a situation.  And theoretically it wouldn't
need to "spill to disk" at all, provided it was small enough.
  Regards,     Dawid


Re: NOLOGGING option, or ?

From
Simon Riggs
Date:
On Wed, 2005-06-01 at 10:18 -0400, Tom Lane wrote:
> 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.

Locking the table is OK. We're loading it for the first time, so we are
expecting to be the only users at this time. 

Here's a more fully worked out plan for wal/concurrency:

1. Lock table, scan until we find our first live row (not deleted,
insert not aborted), in which case throw an error. If no error, then ok
to proceed to next phase. 

2. If there are any dead row versions we truncate the file, since we do
not wish to see those rows ever again. No existing transaction can have
a lock on them, since we hold it, so its ok for them to go. We record
that we have done this in WAL with a short message to say that that we
are loading an empty table, whether or not archiving is not enabled.

3. We load the table, with all TransactionIds set as FROZEN. We do not
generate WAL for each row unless archiving is enabled.

4. Optionally, we set a flag on the table showing the whole table is
frozen. Anybody writing to this table subsequently will spoil this flag.
If the flag is set, all forms of VACUUM will return success immediately
without performing a scan (since it is already in a perfect VACUUM FULL
and VACUUM FREEZE state).

If the server crashes, we replay WAL. If we see a load start message, we
truncate the relation and note that a load has started. If there is WAL
data for the tuples, we replay it. If WAL replay ends without the load
transaction having successfully committed, then we truncate the table.

The above applies to both LOAD/COPY whatever-yer-call-it and in modified
form for CREATE TABLE AS SELECT. For CTAS, no scan is required in (1),
and no truncate is required in (2), otherwise the same.

I'm expecting Alon Goldshuv to join this discussion soon to explain some
other ideas, new developments and discuss the potential for a new
command, so lets wait for him...

Best Regards, Simon Riggs




Re: NOLOGGING option, or ?

From
Tom Lane
Date:
Hannu Krosing <hannu@skype.net> writes:
> 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.

That doesn't seem right to me; the scenario I envision is that you are
willing to do the initial data loading over again (since you presumably
still have the source data available).  But once you've got it loaded
you want full protection.

Perhaps it could work to use an ALTER TABLE command to flip the state.
But I'm not really seeing the point compared to treating it as a COPY
option.  I do not believe that anyone needs this to work on individual
INSERT commands --- if you are after max speed, why aren't you using
COPY?  And treating it as an ALTER property opens the possibility of
forgetting to ALTER the table back to normal behavior, which would be
a foot-gun of large caliber indeed :-(
        regards, tom lane


Re: NOLOGGING option, or ?

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Jochem van Dieten wrote:
>> 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.

You have also got to think about the effects on the table's indexes ...
        regards, tom lane


Re: NOLOGGING option, or ?

From
Tom Lane
Date:
Simon Riggs <simon@2ndquadrant.com> writes:
> 4. Optionally, we set a flag on the table showing the whole table is
> frozen. Anybody writing to this table subsequently will spoil this flag.
> If the flag is set, all forms of VACUUM will return success immediately
> without performing a scan (since it is already in a perfect VACUUM FULL
> and VACUUM FREEZE state).

This bit strikes me as dangerous and not related to the original
proposal.  I don't care for the load-already-frozen-data part at all,
either.  That's not just giving up WAL protection, that's an outright
MVCC semantics violation, in return for which we get ... not much.
Certainly not any speedup in the LOAD itself.
        regards, tom lane


Re: NOLOGGING option, or ?

From
Jochem van Dieten
Date:
On 6/1/05, Bruce Momjian wrote:
> Jochem van Dieten wrote:
>>
>> 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.

Thank you for the explanation, but I am afraid I still don't get it.

COPY can either fail and do a normal rollback, in which case there is
no problem because the xid never made it to the xlog. So I take it you
are talking about a hard crash (pull the plug) somewhere during the
actual writing to disk. In that case you have updated several pages
and overwritten the free space with new tuples. But you have not
overwritten live tuples, so why would you need to restore them? I
mean, didn't PostgreSQL < 7.1 work without a WAL at all?

Jochem


Re: NOLOGGING option, or ?

From
Simon Riggs
Date:
On Wed, 2005-06-01 at 11:31 -0400, Tom Lane wrote:
> Hannu Krosing <hannu@skype.net> writes:
> > 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.
> 
> That doesn't seem right to me; the scenario I envision is that you are
> willing to do the initial data loading over again (since you presumably
> still have the source data available).  But once you've got it loaded
> you want full protection.

Yes, thats the scenario. 

Believe me, I prefer less code, but I think general feeling now is that
we must provide a data safe solution to the performance challenge.

> Perhaps it could work to use an ALTER TABLE command to flip the state.
> But I'm not really seeing the point compared to treating it as a COPY
> option.  I do not believe that anyone needs this to work on individual
> INSERT commands --- if you are after max speed, why aren't you using
> COPY?  And treating it as an ALTER property opens the possibility of
> forgetting to ALTER the table back to normal behavior, which would be
> a foot-gun of large caliber indeed :-(

Oh no, not the foot gun again. I surrender.

Best Regards, Simon Riggs



Re: NOLOGGING option, or ?

From
Alvaro Herrera
Date:
On Wed, Jun 01, 2005 at 06:00:28PM +0100, Simon Riggs wrote:
> On Wed, 2005-06-01 at 11:31 -0400, Tom Lane wrote:

> > Perhaps it could work to use an ALTER TABLE command to flip the state.
> > But I'm not really seeing the point compared to treating it as a COPY
> > option.  I do not believe that anyone needs this to work on individual
> > INSERT commands --- if you are after max speed, why aren't you using
> > COPY?  And treating it as an ALTER property opens the possibility of
> > forgetting to ALTER the table back to normal behavior, which would be
> > a foot-gun of large caliber indeed :-(
> 
> Oh no, not the foot gun again. I surrender.

Sorry, what are you surrendering from/for/of/to?  I think the proposal
is doing a reasonable headways.  So far we have

- it is an option to COPY and CREATE TABLE AS, not GUC, not ALTER TABLE
- it'd only work on empty tables
- it'd only work it PITR is off
- it needs to follow MVCC semantics, i.e. the tuples need to be written with the correct Xids
- after the command is done, an fsync is applied to the table file(s)

Another point that needs thought is what to do if the table has any
indexes.  Are operations on said indexes logged or not?  Maybe we should
just say that indexes are verbotten and the user needs to create them
afterwards.

-- 
Alvaro Herrera (<alvherre[a]surnet.cl>)
"Las cosas son buenas o malas segun las hace nuestra opinión" (Lisias)


Re: NOLOGGING option, or ?

From
Tom Lane
Date:
Alvaro Herrera <alvherre@surnet.cl> writes:
> - it is an option to COPY and CREATE TABLE AS, not GUC, not ALTER TABLE

AFAICS it could just happen automatically for CREATE TABLE AS; there's
no need for an option there, any more than there is for CREATE INDEX.

The only reason it needs to be an explicitly specified option for COPY
is that it would require taking a sole-writer lock on the table, which
COPY does not now do (and I believe I've heard of people using parallel
COPYs to load a table faster, so changing the lock type wouldn't be
transparent for everyone).

> Another point that needs thought is what to do if the table has any
> indexes.  Are operations on said indexes logged or not?  Maybe we should
> just say that indexes are verbotten and the user needs to create them
> afterwards.

That seems pretty reasonable to me.  Again, that's what you'd do anyway
if you are after the fastest possible load time, so why should we work
much harder to support an inefficient approach?

Thinking about it, maybe the user-visible option should be defined thus:
LOCK    Causes COPY FROM to acquire Exclusive lock on the target    table, rather than RowExclusive lock as it normally
does.   This ensures that no other process is modifying the table    while the COPY proceeds.  In some cases this can
allow   significantly faster operation.
 

and then the checks on PITR mode, no indexes, and empty starting table
could be internal implementation details rather than part of the
user-visible spec (ie, we just fall through and do it normally if any of
those conditions don't hold).  I like this a little better because there
might be application-level reasons to want exclusive lock, independently
of implementation details.

Also: AFAICS the starting table need not be empty, if we arrange for all
inserts done by the COPY to be done into freshly-appended blocks.  The
initial WAL entry could note the current table length, and instead of
"truncate to 0 length" the recovery action is "truncate to noted
length".  So really the constraints are just "no PITR" and "no indexes".
        regards, tom lane


Re: NOLOGGING option, or ?

From
"Alon Goldshuv"
Date:
I have been working on improving the COPY command performance and as a
result also came up with other thoughts that may possibly be better off
implemented in a new command (i.e LOAD DATA) rather than adding them to the
existing COPY.

The improvements I made were in the COPY parsing logic - changing a
char-by-char parsing to a multi char buffered fast parsing, while using a
bytebuffer which is faster than StringInfoData and minimizing the number of
loads into the buffer. The data conversion and insertion parts of COPY I
left untouched. As a result the parsing performance increased by about 550%,
and the overall COPY performance increased by:

Around 40% for 15 column (mixed types) table.
Around 90% for 1 column table.

(the difference betweeen the two is caused by data conversion overhead).

I will post the patch and more numbers to the list later today with more
details. I'll just comment now that it is only available for delimited ASCII
input data when client and server encodings are the same. CSV and encoding
conversions may be added later, this is merely to show that data could be
loaded much faster.

Here are some things that make me think a new LOAD command is a good idea:

1) There seem to be a possibility that many COPY modifications/improvements
may be problematic to incorporate in the current postgres COPY code. Further
more, it may be desired to keep the COPY command as is and also have a way
to run an improved COPY command for purposes of backwards compatibility.

2) A modified command syntax for introducing a direct single row error
handling. By direct I mean - a row that if rejected from within the COPY
command context does not throw an error and rollsback the whole transaction.
Instead the error is caught and recorded elsewhere, maybe in some error
table, with some more information that can later on be retrieved. The
following rows continue to be processed. This way there is barely any error
handling overhead. Having a recursive row isolation into smaller batches is
extremely expensive for non-small data sets. It's not an option for serious
users.

3) maybe have an option to indicate the EOL (end of line) format in the
command syntax. Current COPY code detects the line-end according to the
first data line, this is problematic is 1st data line is mal formatted, and
also this doesn't allow having any CR's for example in the data file when
EOL is only a linefeed. That causes extra data errors in COPY processing. A
CR is a valid data character. Specifying the line end in command syntax will
save all this badness.
4) Data integrity and escaping improvements. My patch changes now treats all
characters as data (unless it's an escaped delim or EOL) and therefore data
integrity is preserved (take for example the following valid data field
"file:\new\bang" that after COPY into the database, querying for it from
psql will result is wrong data -- backslashes are gone, there are 2 data
lines, and a bell will ring for \b!) However, some people that already got
used to the postgres COPY escaping way may want to keep it. They could do so
by still using the old COPY.

5) allow an ERRORLIMIT to allow control of aborting a load after a certain
number of errors (and a pre-requisite for this is point number 2 above).

6) allow LIMIT and OFFSET, for files with header rows for example (could be
done in COPY too).

7) Allow the blocks to be directly written to the table, rather than via
the buffer cache.

8) Allow a bulk index insertion operation at the end of the LOAD step, if
the data has been loaded in sorted order. Use something like the SORTED
INDEXES statement on Oracle sql*loader to specify the sort order of the
incoming data, so that the index build step can bypass another external
sort before loading directly into the index.

9) allow for Simon's WAL bypass.

I have surely missed some problems that hide behind the idea, but these
points make me believe that LOAD DATA is a good idea.

Alon.




On 5/31/05 7:47 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

> Simon Riggs <simon@2ndquadrant.com> writes:
>> Recent test results have shown a substantial performance improvement
>> (+25%) if WAL logging is disabled for large COPY statements.
> 
> How much of that is left after we fix the 64-bit-CRC issue?
> 
>> Now, I would like to discuss adding an enable_logging USERSET GUC,
> 
> [ fear and loathing ... ]
> 
> I don't like the idea of a GUC at all, and USERSET is right out.
> I think it would have to be system-wide (cf fsync) to be even
> implementable let alone somewhat predictable.  Even if it could
> be done per-backend with reasonable semantics, random users should
> not get to make that decision --- it should be the DBA's call,
> which means it needs at least SUSET permissions.
> 
> BTW, I'm sure you are the last one who needs to be reminded that
> any such thing breaks PITR completely.  Which is surely sufficient
> reason not to let it be USERSET.
> 
> regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
> 




Re: NOLOGGING option, or ?

From
Simon Riggs
Date:
On Wed, 2005-06-01 at 11:55 -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > 4. Optionally, we set a flag on the table showing the whole table is
> > frozen. Anybody writing to this table subsequently will spoil this flag.
> > If the flag is set, all forms of VACUUM will return success immediately
> > without performing a scan (since it is already in a perfect VACUUM FULL
> > and VACUUM FREEZE state).
> 
> This bit strikes me as dangerous and not related to the original
> proposal.  I don't care for the load-already-frozen-data part at all,
> either.  That's not just giving up WAL protection, that's an outright
> MVCC semantics violation, in return for which we get ... not much.
> Certainly not any speedup in the LOAD itself.

I agree it *sounds* dangerous, but is it? If so, how? I will immediately
withdraw any idea that proves dangerous.

We're holding the table lock and will continue to do so until end of
transaction. No transaction with an earlier id will ever see the data we
load because of the lock. Later transactions will see the data only when
we commit. At that stage, all they care about is that the data is
visible...it doesn't matter that we have frigged it to look like its
been there a real long time.

We're not giving up WAL protection. The PITR case is covered. Non-PITR
cases have exactly the same transactional consistency across as crash as
they do now. Nothing is lost through this proposal.

Doing those two additional actions gives us the ability to avoid:
1. avoid writing the whole table twice because of
SetBufferCommitInfoNeedsSave
2. avoid pointless VACUUMs of very large tables
3. load the table direct to read-only media, or copy it to read-only
media at some point in the future without needing to do a VACUUM FREEZE

All of that means we write the table once, rather than 3 times over the
lifetime of the table. And we only need scan it for user SQL, not for
wrap-around avoiding VACUUMs.

I know you have a solution to the second one in mind. I would not argue
against the needs-vacuuming bitmap idea when that comes back around.

Best Regards, Simon Riggs



Re: NOLOGGING option, or ?

From
Alvaro Herrera
Date:
On Wed, Jun 01, 2005 at 06:55:46PM +0100, Simon Riggs wrote:
> On Wed, 2005-06-01 at 11:55 -0400, Tom Lane wrote:
> > Simon Riggs <simon@2ndquadrant.com> writes:
> > > 4. Optionally, we set a flag on the table showing the whole table is
> > > frozen. Anybody writing to this table subsequently will spoil this flag.
> > > If the flag is set, all forms of VACUUM will return success immediately
> > > without performing a scan (since it is already in a perfect VACUUM FULL
> > > and VACUUM FREEZE state).
> > 
> > This bit strikes me as dangerous and not related to the original
> > proposal.  I don't care for the load-already-frozen-data part at all,
> > either.  That's not just giving up WAL protection, that's an outright
> > MVCC semantics violation, in return for which we get ... not much.
> > Certainly not any speedup in the LOAD itself.
> 
> I agree it *sounds* dangerous, but is it? If so, how? I will immediately
> withdraw any idea that proves dangerous.
> 
> We're holding the table lock and will continue to do so until end of
> transaction. No transaction with an earlier id will ever see the data we
> load because of the lock.

Suppose you load half the tuples and the plug is pulled.  After
recovery, you have half-load of tuples that are visible to everyone.
This is a no-no.  Plus, what is the benefit of having the tuples in
frozen state to start with?  If this is a data warehouse application,
I'd expect the table to be dropped or truncated rather before the
billion-transactions barrier comes to pass.

-- 
Alvaro Herrera (<alvherre[a]surnet.cl>)
"I dream about dreams about dreams", sang the nightingale
under the pale moon (Sandman)


Re: NOLOGGING option, or ?

From
Tom Lane
Date:
Simon Riggs <simon@2ndquadrant.com> writes:
> If the server crashes, we replay WAL. If we see a load start message, we
> truncate the relation and note that a load has started. If there is WAL
> data for the tuples, we replay it. If WAL replay ends without the load
> transaction having successfully committed, then we truncate the table.

On further thought, this seems both risky and unnecessary.

The reason it's risky is this scenario:
* Backend 1 makes a LOAD-start WAL entry.
* Backend 1 loads some data, extending the table beyond its  former end.
* Backend 1 errors out without committing its transaction.
* Backend 2 inserts some data into the no-longer-locked table.  It uses free space in one of the added pages, or maybe
even adds new pages of its own.
 
* Backend 2 commits.
* System crashes, and we have to replay the above actions.

In this scenario you cannot truncate at the end of replay without losing
backend 2's committed data.

You can think of various ways to avoid this risk (for instance, maybe
*any* WAL-logged operation on the table should cause the pending
TRUNCATE to be discarded) but they all seem expensive and/or still
somewhat unsafe.

The reason it's unnecessary is what's the point?  All you're doing by not
truncating is leaving some uncommitted tuples in the table.  It's not
the job of WAL recovery to get rid of such things; that's VACUUM's job.

So what I'm thinking is we need no special WAL entries for this.  What
we need is just an operating mode of COPY in which it doesn't WAL-log
its inserts, but instead fsyncs before completion, much like index build
does.  For safety it must do all its inserts into freshly-added pages;
this is not to ensure truncatability, because we aren't going to do that
anyway, but to ensure that we don't have unlogged operations changing
pages that might contain committed tuples. (That would pose a risk of
losing committed data to incomplete writes in case of system crash
partway through.  The same reason is why we need exclusive lock: else
we might end up with pages containing a mix of logged and unlogged
tuples.)  Also there can be no indexes, since we don't want index
entries pointing to unlogged tuples.  And PITR can't be enabled.
Otherwise no problem.
        regards, tom lane


Re: NOLOGGING option, or ?

From
Tom Lane
Date:
Alvaro Herrera <alvherre@surnet.cl> writes:
> On Wed, Jun 01, 2005 at 06:55:46PM +0100, Simon Riggs wrote:
>> We're holding the table lock and will continue to do so until end of
>> transaction. No transaction with an earlier id will ever see the data we
>> load because of the lock.

> Suppose you load half the tuples and the plug is pulled.  After
> recovery, you have half-load of tuples that are visible to everyone.
> This is a no-no.

Simon is expecting that the loaded tuples are guaranteed to be erased
(by table truncation) during recovery.  As I just noted I'm unconvinced
of the safety of doing truncations during recovery, so I'd prefer not
to depend on that.

The scenario I was thinking of was different: you load pre-frozen
tuples, commit, and thereby release the table lock.  Now the tuples
are visible to transactions that started before you did; that's what
violates MVCC.
        regards, tom lane


Re: NOLOGGING option, or ?

From
Simon Riggs
Date:
On Wed, 2005-06-01 at 14:24 -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre@surnet.cl> writes:
> > On Wed, Jun 01, 2005 at 06:55:46PM +0100, Simon Riggs wrote:
> >> We're holding the table lock and will continue to do so until end of
> >> transaction. No transaction with an earlier id will ever see the data we
> >> load because of the lock.
> 
> > Suppose you load half the tuples and the plug is pulled.  After
> > recovery, you have half-load of tuples that are visible to everyone.
> > This is a no-no.
> 
> Simon is expecting that the loaded tuples are guaranteed to be erased
> (by table truncation) during recovery.  As I just noted I'm unconvinced
> of the safety of doing truncations during recovery, so I'd prefer not
> to depend on that.
> 
> The scenario I was thinking of was different: you load pre-frozen
> tuples, commit, and thereby release the table lock.  Now the tuples
> are visible to transactions that started before you did; that's what
> violates MVCC.

Agreed. MVCC violation. OK, back to the drawing board.

Best Regards, Simon Riggs



Re: NOLOGGING option, or ?

From
Simon Riggs
Date:
On Wed, 2005-06-01 at 14:14 -0400, Tom Lane wrote:
> So what I'm thinking is we need no special WAL entries for this.  What
> we need is just an operating mode of COPY in which it doesn't WAL-log
> its inserts, but instead fsyncs before completion, much like index build
> does.  For safety it must do all its inserts into freshly-added pages;
> this is not to ensure truncatability, because we aren't going to do that
> anyway, but to ensure that we don't have unlogged operations changing
> pages that might contain committed tuples. (That would pose a risk of
> losing committed data to incomplete writes in case of system crash
> partway through.  The same reason is why we need exclusive lock: else
> we might end up with pages containing a mix of logged and unlogged
> tuples.)  Also there can be no indexes, since we don't want index
> entries pointing to unlogged tuples.  And PITR can't be enabled.
> Otherwise no problem.

What you describe above is a coherent set of features that provide most
of the benefits I sought, plus some others. We also don't mess with WAL,
which is grand thing. We gain the ability to load into tables with rows
already in them.

I don't agree with all of your other points, but given time schedules, I
think that we win with the above, so forget the rest.

The main COPY/LOAD DATA discussion is on another thread of this from
Alon, who has some interesting ideas and some really cool performance
results to share.

Best Regards, Simon Riggs



Re: NOLOGGING option, or ?

From
Bruce Momjian
Date:
Jochem van Dieten wrote:
> On 6/1/05, Bruce Momjian wrote:
> > Jochem van Dieten wrote:
> >>
> >> 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.
> 
> Thank you for the explanation, but I am afraid I still don't get it.
> 
> COPY can either fail and do a normal rollback, in which case there is
> no problem because the xid never made it to the xlog. So I take it you
> are talking about a hard crash (pull the plug) somewhere during the
> actual writing to disk. In that case you have updated several pages
> and overwritten the free space with new tuples. But you have not
> overwritten live tuples, so why would you need to restore them? I
> mean, didn't PostgreSQL < 7.1 work without a WAL at all?

What if you are adding rows to an existing page --- in that case you are
writing a page that also contained valid tuples before the COPY.

--  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,
Pennsylvania19073
 


Re: NOLOGGING option, or ?

From
Bruce Momjian
Date:
Alon Goldshuv wrote:
> 9) allow for Simon's WAL bypass.
> 
> I have surely missed some problems that hide behind the idea, but these
> points make me believe that LOAD DATA is a good idea.

The community is unlikely to add a new LOAD DATA command that does
_almost_ everything COPY does.  We are much more likely to incrementally
improve COPY.

The problem with a new command is that it becomes unclear when you
should use COPY and when LOAD DATA, and it confuses users, and has
maintenance overhead.  If Bizgres wants a new command name, go for it,
but it is unlikely that the community release is going to go in that
direction, unless there is a fundamental agreement that COPY is broken
and needs a major revamp, and I have heard no talk of that.

--  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,
Pennsylvania19073
 


Re: NOLOGGING option, or ?

From
Bruce Momjian
Date:
Tom Lane wrote:
> 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.

One idea would be to look at the table file size first.  If it has zero
blocks, lock the table and if it still has zero blocks, do the no-WAL
copy.

I hate to add a flag to a command when we can automatically handle it
ourselves.

Now, you mentioned the idea of doing the optimization in tables that
already have data, and if we do that, we would need a flag because the
lock is stronger than what we have now.

What we could do is to do no-WAL automatically for empty tables (like
when a database is first loaded), and use the flag for cases where the
tables is not zero pages.  The fact is that database loads are a prefect
case for this optimization and old dumps are not going to have that flag
anyway, and automatic is better if we can do it.

--  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,
Pennsylvania19073
 


Re: NOLOGGING option, or ?

From
"Luke Lonergan"
Date:
Bruce,

> The problem with a new command is that it becomes unclear when you
> should use COPY and when LOAD DATA, and it confuses users, and has
> maintenance overhead.  If Bizgres wants a new command name, go for it,
> but it is unlikely that the community release is going to go in that
> direction, unless there is a fundamental agreement that COPY is broken
> and needs a major revamp, and I have heard no talk of that.

The question of whether COPY should be improved or whether the changes
should take the form of a new command is separate from the question of
whether the performance of the load path in PostgreSQL needs improvement.

The 90% performance increase (from 12 MB/s to 21 MB/s) that Alon reported
comes from replacing the parsing logic within COPY.  I believe that the
parsing logic in COPY is fundamentally broken from a performance
perspective, and may be broken from a functionality perspective WRT embedded
backslashes.

One of the reasons to consider a LOAD DATA command is that we can isolate
the need for performance improvements and special syntax from the concerns
of preserving the legacy behavior of COPY for use as the primary mechanism
for DUMP and RESTORE.

- Luke 




Re: NOLOGGING option, or ?

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> One idea would be to look at the table file size first.  If it has zero
> blocks, lock the table and if it still has zero blocks, do the no-WAL
> copy.

I think that's a bad idea.  It would make the behavior unpredictable
--- sometimes a COPY will take an exclusive lock, and other times not;
and the reason why is at a lower semantic level than the user is
supposed to know about.

Before you say "this is not important", consider the nontrivial risk
that the stronger lock will cause a deadlock failure.  I don't think
that it's acceptable for lock strength to be unpredictable.
        regards, tom lane


Re: NOLOGGING option, or ?

From
Tom Lane
Date:
"Luke Lonergan" <llonergan@greenplum.com> writes:
> One of the reasons to consider a LOAD DATA command is that we can isolate
> the need for performance improvements and special syntax from the concerns
> of preserving the legacy behavior of COPY for use as the primary mechanism
> for DUMP and RESTORE.

... and instead, define some new behavior that will soon be considered
broken legacy code itself?

There isn't any demand for changing the semantics of COPY, as far as
I've noticed.  If we can make it faster with the same semantics that's
great, but I'm not in favor of inventing an alternate that does almost
the same thing but (eg) breaks backslash handling in the name of speed.
        regards, tom lane


Re: NOLOGGING option, or ?

From
Simon Riggs
Date:
On Wed, 2005-06-01 at 10:35 -0700, Alon Goldshuv wrote:
> I have been working on improving the COPY command performance 

> Around 40% for 15 column (mixed types) table.
> Around 90% for 1 column table.

Thats very cool. 

> 2) A modified command syntax for introducing a direct single row error
> handling. By direct I mean - a row that if rejected from within the COPY
> command context does not throw an error and rollsback the whole transaction.
> Instead the error is caught and recorded elsewhere, maybe in some error
> table, with some more information that can later on be retrieved. The
> following rows continue to be processed. This way there is barely any error
> handling overhead. Having a recursive row isolation into smaller batches is
> extremely expensive for non-small data sets. It's not an option for serious
> users.

Can we call this the ERRORTABLE clause?

> 5) allow an ERRORLIMIT to allow control of aborting a load after a certain
> number of errors (and a pre-requisite for this is point number 2 above).

The default for which would be ERRORLIMIT 0 to give backwards
compatibility.

2) and 5) seem critical for combined usability & performance with real
world data.

I'm not clear from all of those options whether we still need a LOAD
command, based upon other issues/comments raised on this thread.

However, there are some other arguments for why it might be a good idea
to have a LOAD DATA command separate from COPY. Certainly long term
features would be easier to add with two commands. Trying to maintain
backwards compatibility just because we use COPY seems like an uphill
struggle and is going to mean we have to handle sensible new additions
as options so we don't break existing applications. The most important
one is the lock type held. 

[Oracle compatibility isn't one of them, even if it did provide the
command name.]

But things will be clearer when we see the patch.

Best Regards, Simon Riggs




Re: NOLOGGING option, or ?

From
Simon Riggs
Date:
On Wed, 2005-06-01 at 18:32 -0400, Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > One idea would be to look at the table file size first.  If it has zero
> > blocks, lock the table and if it still has zero blocks, do the no-WAL
> > copy.
> 
> I think that's a bad idea.  It would make the behavior unpredictable
> --- sometimes a COPY will take an exclusive lock, and other times not;
> and the reason why is at a lower semantic level than the user is
> supposed to know about.
> 
> Before you say "this is not important", consider the nontrivial risk
> that the stronger lock will cause a deadlock failure.  I don't think
> that it's acceptable for lock strength to be unpredictable.

While I agree with Bruce's sentiment, the locking is an issue with
COPY. 

It is particularly important to be able to plan the concurrency within
an application. That is why DB2's interesting ability to perform lock
upgrades in a relatively unpredictable manner is not good.

For CREATE TABLE AS SELECT, I think we could do this Bruce's way and
win, since we would take the same grade of lock in both cases.

Best Regards, Simon Riggs



Re: NOLOGGING option, or ?

From
Bruce Momjian
Date:
Luke Lonergan wrote:
> Bruce,
> 
> > The problem with a new command is that it becomes unclear when you
> > should use COPY and when LOAD DATA, and it confuses users, and has
> > maintenance overhead.  If Bizgres wants a new command name, go for it,
> > but it is unlikely that the community release is going to go in that
> > direction, unless there is a fundamental agreement that COPY is broken
> > and needs a major revamp, and I have heard no talk of that.
> 
> The question of whether COPY should be improved or whether the changes
> should take the form of a new command is separate from the question of
> whether the performance of the load path in PostgreSQL needs improvement.
> 
> The 90% performance increase (from 12 MB/s to 21 MB/s) that Alon reported
> comes from replacing the parsing logic within COPY.  I believe that the
> parsing logic in COPY is fundamentally broken from a performance
> perspective, and may be broken from a functionality perspective WRT embedded
> backslashes.

COPY works as designed.  The idea that some guy we have never heard of
is going to appear and rewrite COPY's processing and tell us that the
existing code is actually broken seems pretty arrogant to me.  If it is
broken (meaning doesn't work as designed), please show us facts rather
than conjecture.

Oh, and the "Our COPY improvements are so fundamental that they deserve
a new command name" also has a similar flavor.

(Please explain how you handle literal delimiters and nulls with no
escape processing.)

> One of the reasons to consider a LOAD DATA command is that we can isolate
> the need for performance improvements and special syntax from the concerns
> of preserving the legacy behavior of COPY for use as the primary mechanism
> for DUMP and RESTORE.

This seems like a case where GreenPlum's priorities and the community's
priorities might not match.  There is much more work required on your
part if you are going to convince the community it needs a new data
loading command, and starting out with the assumption in emails that it
is going to be a newly named command isn't the best approach.  That is
my fundamental point.

--  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,
Pennsylvania19073
 


Re: NOLOGGING option, or ?

From
Bruce Momjian
Date:
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > One idea would be to look at the table file size first.  If it has zero
> > blocks, lock the table and if it still has zero blocks, do the no-WAL
> > copy.
> 
> I think that's a bad idea.  It would make the behavior unpredictable
> --- sometimes a COPY will take an exclusive lock, and other times not;
> and the reason why is at a lower semantic level than the user is
> supposed to know about.
> 
> Before you say "this is not important", consider the nontrivial risk
> that the stronger lock will cause a deadlock failure.  I don't think
> that it's acceptable for lock strength to be unpredictable.

Yea, but you are only doing the lock if the table is zero pages. 
Doesn't that help?  Maybe not.

I do like the LOCK keyword if we have to use one to enable this
functionality, but I am suspecting people will want this functionality
in pg_dump output.  How do we do that?  Just make it the default for
pg_dump output?

--  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,
Pennsylvania19073
 


Re: NOLOGGING option, or ?

From
Bruce Momjian
Date:
Simon Riggs wrote:
> I'm not clear from all of those options whether we still need a LOAD
> command, based upon other issues/comments raised on this thread.
> 
> However, there are some other arguments for why it might be a good idea
> to have a LOAD DATA command separate from COPY. Certainly long term
> features would be easier to add with two commands. Trying to maintain
> backwards compatibility just because we use COPY seems like an uphill
> struggle and is going to mean we have to handle sensible new additions
> as options so we don't break existing applications. The most important
> one is the lock type held. 

Well, we have had a pretty much unmodified COPY format since like the
Berkeley days (I added \N and \.).  Please tell us exactly what you want
do to that requires a format change, and we can talk about it, but
showing up with no proof and expecting a new command is the _wrong_
approach.  It actually reminds me of the "our company developed it so it
must be great" approach, which doesn't work well in the community.

--  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,
Pennsylvania19073
 


Re: NOLOGGING option, or ?

From
Joe Conway
Date:
Simon Riggs wrote:
> On Wed, 2005-06-01 at 10:35 -0700, Alon Goldshuv wrote:
>>2) A modified command syntax for introducing a direct single row error
>>handling. By direct I mean - a row that if rejected from within the COPY

>>5) allow an ERRORLIMIT to allow control of aborting a load after a certain
>>number of errors (and a pre-requisite for this is point number 2 above).

> 2) and 5) seem critical for combined usability & performance with real
> world data.

I'll second that! This would be a huge win for one of my real world 
applications, whether implemented as a new command, or as added 
capability on top of COPY. The other performance enhancements would 
certainly be nice to have also, but in my experience not nearly as 
important as these two.

Joe


Re: NOLOGGING option, or ?

From
"Alon Goldshuv"
Date:
Bruce,

The patch is not there to show that something is "broken" is it there to
show how things could be done in another way, which may or may not be more
desireable.

> COPY works as designed.  The idea that some guy we have never heard of
> is going to appear and rewrite COPY's processing and tell us that the
> existing code is actually broken seems pretty arrogant to me.  If it is
> broken (meaning doesn't work as designed), please show us facts rather
> than conjecture.

I am sure that the code works as designed. In my previous email I was
referring to the fact that using COPY with a data field that happens to have
backslashes in it, and then querying this data field you will get different
results. For example do COPY of a field "c:\\one\ten\nine" and query for it.
The result will be much different. This is a problem with clickstream data
for example. That's all. It's very possible that there are reasons that I
missed for why things are the way they are.

> (Please explain how you handle literal delimiters and nulls with no
> escape processing.)
Escape processing is done only for these 2 cases.

Thx,
Alon.





Re: NOLOGGING option, or ?

From
"Luke Lonergan"
Date:
Tom,
> 
> ... and instead, define some new behavior that will soon be considered
> broken legacy code itself?

I'll not argue further about whether to have a separate LOAD command.
That's not as important as fixing the performance issues in the data load
path in PostgreSQL to me.

However, I find it compelling that the underlying problem confronting high
performance data loading in the engine is the slow parse code in psql/copy,
etc., and not the WAL or other issues.  Next on the list are huge gains from
removing things like repetitive calls to strlen(TZ) in the attribute
conversion code. 

> There isn't any demand for changing the semantics of COPY, as far as
> I've noticed.  If we can make it faster with the same semantics that's
> great, but I'm not in favor of inventing an alternate that does almost
> the same thing but (eg) breaks backslash handling in the name of speed.

A 540% increase in parsing speed while performing substantially the same
logic is what the patch accomplishes.  The patch is now available on
pgsql-patches.  In this case the speed did not come from breaking backslash
handling, but rather from using faster processing for escape processing and
delimiter parsing.  We *could* recreate identical escape semantics to COPY
at nearly the same speed, but frankly we're puzzled as to why character
sequences with "\" automatically imply escape processing.

We've found that there are many cases where more sophisticated escape
processing options are necessary, including multi-byte delimiters and escape
sequences, and the default use of "\" for escaping breaks many data load
cases.

- Luke




Re: NOLOGGING option, or ?

From
Bruce Momjian
Date:
Alon Goldshuv wrote:
> Bruce,
> 
> The patch is not there to show that something is "broken" is it there to
> show how things could be done in another way, which may or may not be more
> desireable.

Sure, we are always looking for ways to improve things.

> > COPY works as designed.  The idea that some guy we have never heard of
> > is going to appear and rewrite COPY's processing and tell us that the
> > existing code is actually broken seems pretty arrogant to me.  If it is
> > broken (meaning doesn't work as designed), please show us facts rather
> > than conjecture.
> 
> I am sure that the code works as designed. In my previous email I was
> referring to the fact that using COPY with a data field that happens to have
> backslashes in it, and then querying this data field you will get different
> results. For example do COPY of a field "c:\\one\ten\nine" and query for it.
> The result will be much different. This is a problem with clickstream data
> for example. That's all. It's very possible that there are reasons that I
> missed for why things are the way they are.

Yep, you have to double backslahses coming in as data so we can use
backslash for marking null, delimiters, etc.  I see no way around that,
and no one since Berkeley has come up with one either.  If you have an
idea, we would _love_ to hear it.

> > (Please explain how you handle literal delimiters and nulls with no
> > escape processing.)
> Escape processing is done only for these 2 cases.

OK, how is that done?  How about for newlines in the data?  

Right now I think that is the only escapes we do. We support more but
basically the only required ones are delimiter and nulls.  The others
are around just for convenience.

--  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,
Pennsylvania19073
 


Re: NOLOGGING option, or ?

From
Bruce Momjian
Date:
Luke Lonergan wrote:
> Tom,
> > 
> > ... and instead, define some new behavior that will soon be considered
> > broken legacy code itself?
> 
> I'll not argue further about whether to have a separate LOAD command.
> That's not as important as fixing the performance issues in the data load
> path in PostgreSQL to me.
> 
> However, I find it compelling that the underlying problem confronting high
> performance data loading in the engine is the slow parse code in psql/copy,
> etc., and not the WAL or other issues.  Next on the list are huge gains from
> removing things like repetitive calls to strlen(TZ) in the attribute
> conversion code. 

Yep, we would _love_ those improvements.

> > There isn't any demand for changing the semantics of COPY, as far as
> > I've noticed.  If we can make it faster with the same semantics that's
> > great, but I'm not in favor of inventing an alternate that does almost
> > the same thing but (eg) breaks backslash handling in the name of speed.
> 
> A 540% increase in parsing speed while performing substantially the same
> logic is what the patch accomplishes.  The patch is now available on
> pgsql-patches.  In this case the speed did not come from breaking backslash
> handling, but rather from using faster processing for escape processing and
> delimiter parsing.  We *could* recreate identical escape semantics to COPY
> at nearly the same speed, but frankly we're puzzled as to why character
> sequences with "\" automatically imply escape processing.

I am confused why you are confused.  :-)

> We've found that there are many cases where more sophisticated escape
> processing options are necessary, including multi-byte delimiters and escape
> sequences, and the default use of "\" for escaping breaks many data load
> cases.

Uh, how do you do the escapes if you don't double the escape character
on input so you can distinguish a literal escape from one use to mark
special data like a literal delimiter or a null?

--  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,
Pennsylvania19073
 


Re: NOLOGGING option, or ?

From
"Luke Lonergan"
Date:
Bruce,

> Yep, you have to double backslahses coming in as data so we can use
> backslash for marking null, delimiters, etc.  I see no way around that,
> and no one since Berkeley has come up with one either.  If you have an
> idea, we would _love_ to hear it.

Apropos to the previous thread on escape processing, IMEO it needs to be
switchable (e.g. No default of "\" as the escape character, escape
processing not done by default).  Otherwise lots of data loading scenarios
(e.g. virtually all weblog sources) break in nasty ways.

The remedy to date has been to perform extended preprocessing of the data to
"pre-escape process" the data so that it will pass through the default
escape processing of the PostgreSQL copy processing.

I propose an extended syntax to COPY with a change in semantics to remove
the default of "WITH ESCAPE '\'".

- Luke




Re: NOLOGGING option, or ?

From
"Luke Lonergan"
Date:
> 
> Yep, we would _love_ those improvements.
> 

Coming soon, probably from the guy you've never heard of :-)

> 
> I am confused why you are confused.  :-)
> 
> Uh, how do you do the escapes if you don't double the escape character
> on input so you can distinguish a literal escape from one use to mark
> special data like a literal delimiter or a null?

Escape processing would proceed as before, but the semantics would change to
allow the use of different characters as the escape character, in addition
to the special characters for delimiter and newline.  Also, escape
processing would be "false" as the default, so that the only special
characters by default would be the newline and delimiter characters.

Also of importance is the specification of newline and delimiter as
arbitrary double byte or 8-bit characters.

- Luke 




Re: NOLOGGING option, or ?

From
Oliver Jowett
Date:
Luke Lonergan wrote:

> I propose an extended syntax to COPY with a change in semantics to remove
> the default of "WITH ESCAPE '\'".

Er, doesn't this break existing database dumps?

-O


Re: NOLOGGING option, or ?

From
Bruce Momjian
Date:
Luke Lonergan wrote:
> 
> > 
> > Yep, we would _love_ those improvements.
> > 
> 
> Coming soon, probably from the guy you've never heard of :-)

LOL

> > 
> > I am confused why you are confused.  :-)
> > 
> > Uh, how do you do the escapes if you don't double the escape character
> > on input so you can distinguish a literal escape from one use to mark
> > special data like a literal delimiter or a null?
> 
> Escape processing would proceed as before, but the semantics would change to
> allow the use of different characters as the escape character, in addition
> to the special characters for delimiter and newline.  Also, escape
> processing would be "false" as the default, so that the only special
> characters by default would be the newline and delimiter characters.
> 
> Also of importance is the specification of newline and delimiter as
> arbitrary double byte or 8-bit characters.

I am still confused how you have reliable, never-break semantics without
special escaping.

How do you distinguis an escape-delimiter used to escape a delimiter in
the data from a literal escape-delimiter in the data being loaded --- it
seems impossible to do.

The idea of allowing a different escape character is interesting,
however, and certainly possible.  Right now we allow ESCAPE to be
changed only in CSV mode, but I suppose it is possible to allow it to be
changed in non-CSV mode as well.

Or are you saying there would be no escape at all.  If you make '@' the
escape, you can't just say @n is a newline because you need to make '@'
output as '@@' so you can distinguish @-n from a newline.

--  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,
Pennsylvania19073
 


Re: NOLOGGING option, or ?

From
Bruce Momjian
Date:
Oliver Jowett wrote:
> Luke Lonergan wrote:
> 
> > I propose an extended syntax to COPY with a change in semantics to remove
> > the default of "WITH ESCAPE '\'".
> 
> Er, doesn't this break existing database dumps?

Right, we will not change the default.

--  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,
Pennsylvania19073
 


Re: NOLOGGING option, or ?

From
"Luke Lonergan"
Date:
Oliver,

On 6/1/05 7:13 PM, "Oliver Jowett" <oliver@opencloud.com> wrote:

> Luke Lonergan wrote:
> 
>> I propose an extended syntax to COPY with a change in semantics to remove
>> the default of "WITH ESCAPE '\'".
> 
> Er, doesn't this break existing database dumps?

Yes, one of the previously stated reasons to create another command for
loading data.

Another possible approach is to keep the default, but allow the escape
processing to be turned off.

- Luke




Re: NOLOGGING option, or ?

From
"Luke Lonergan"
Date:
> How do you distinguis an escape-delimiter used to escape a delimiter in
> the data from a literal escape-delimiter in the data being loaded --- it
> seems impossible to do.

The usual approach is fine - a pair of escapes is equivalent to a literal
escape.
> Or are you saying there would be no escape at all.  If you make '@' the
> escape, you can't just say @n is a newline because you need to make '@'
> output as '@@' so you can distinguish @-n from a newline.

Yup - which is why when we've implemented this set of semantics in the past
we've allowed for the use of arbitrary 8-bit escape characters.

Binary characters (128-255) will appear fairly regularly in web log data,
and as Alon pointed out previously, many text fields include backslashes
(particularly Windows friendly ones).  More flexibility in string handling
is needed.

Luke 




Re: NOLOGGING option, or ?

From
Steve Atkins
Date:
On Wed, Jun 01, 2005 at 07:35:33PM -0700, Luke Lonergan wrote:
> >> I propose an extended syntax to COPY with a change in semantics to remove
> >> the default of "WITH ESCAPE '\'".
> > 
> > Er, doesn't this break existing database dumps?
> 
> Yes, one of the previously stated reasons to create another command for
> loading data.
> 
> Another possible approach is to keep the default, but allow the escape
> processing to be turned off.

I've been following this thread, and I'm a little confused. Could you
possibly clarify what you mean, by providing a couple of lines of
input as it would be formatted with escape processing turned off - 
containing a text field with an embedded newline and tab and a null field.

Cheers, Steve


Re: NOLOGGING option, or ?

From
Alvaro Herrera
Date:
On Wed, Jun 01, 2005 at 10:35:30AM -0700, Alon Goldshuv wrote:

> 2) A modified command syntax for introducing a direct single row error
> handling. By direct I mean - a row that if rejected from within the COPY
> command context does not throw an error and rollsback the whole transaction.
> Instead the error is caught and recorded elsewhere, maybe in some error
> table, with some more information that can later on be retrieved. The
> following rows continue to be processed. This way there is barely any error
> handling overhead.

Is there any idea on exactly how would this be done?  Do you plan on
using savepoints to implement it?  I fail to see how is this "barely any
overhead".  Savepoints are not that expensive but they are not free either.
(No, I haven't measured it.)

-- 
Alvaro Herrera (<alvherre[a]surnet.cl>)
"Oh, great altar of passive entertainment, bestow upon me thy discordant images
at such speed as to render linear thought impossible" (Calvin a la TV)


Re: NOLOGGING option, or ?

From
"Luke Lonergan"
Date:
Steve,

> I've been following this thread, and I'm a little confused. Could you
> possibly clarify what you mean, by providing a couple of lines of
> input as it would be formatted with escape processing turned off -
> containing a text field with an embedded newline and tab and a null field.

Using an extended command syntax for a "copy-like" command named LOAD:

LOAD [schema.]tablename [(column1,column2,...)]    FROM {'filename' | STDIN}    [ [WITH]         [DELIMITER [AS]
'delimiter']       [NULL [AS] 'null string']        [ESCAPE [AS] Œescape¹] ] ; 

If you intend to support embedded newlines (0x0a) in your character data
without escapes, you will need to choose an alternative newline character
for formatting the data. An example that uses '0xaa' as the newline might
be:

Control statement:
LOAD webform (formdata) FROM /home/sample/sample.txt WITH DELIMITER Œ|¹ NULL
Œ¹ NEWLINE '0xaa';

Sample with 2 identical rows (with binary representations depicted between
<>):
Blahblah<0xaa>blahblah<0x09>blahblah<0x00>blahblah<0xaa>Blahblah<0xaa>blahbl
ah<0x09>blahblah<0x00>blahblah<0xaa>

- Luke




Re: NOLOGGING option, or ?

From
"Alon Goldshuv"
Date:
yeah, this is all a bit confusing, but I *hope* I can clarify things here as I think I got a better understanding now.

before that, let me just clarify that the performance improvements in the patch have nothing to do with the escaping
mechanizm.Escapes could change. The performance gain in due to a buffered processing with minimal line/attribute buffer
loads.

I think that the basic issue is that there are some database users that would like to take their data and put it into
thedatabase without pre-processing it - regardless if there are any backslashes in it or 0x0D (CR's) etc... these are
theusers I am targeting in my patch as these are the users I ran into in the field. The only responsibility of these
usersis to explicitly escape any delimiter or 0x0A (LF) characters that they intend to have as DATA. that's all.  

On the other hand there are users that would like to pre-process their data with C-escape sequences (or alternatevly,
usersthat already have their data escaped) - this is what the postgres COPY targets these days. 

2 different ways to do it... none of them is right or wrong.

Examples:

users that my patch targets may have a data row as such (delim = '|', EOL = [LF]):
   c:\one\two|d:\ten\nine[LF]

using the way i do escaping in my patch those 2 fields of data will end up in the DB as
Field 1: c:\one\two
Field 2: d:\ten\nine

which is what the user would want. If they wanted to have a pipe char in the second field they could escape it as such:
d:\ten\ninehere is a pipe \| [LF] and no error will occur, and result will be: 

Field 2: d:\ten\nine here is a pipe |

If you try to insert that first data line above using the existing COPY command you will get an undesired result:

Field 1: c:one   wo
Field 2: d:            ine


Now, the other way around, users that do intend for their data to have escape sequences in it may have a line like
this:
   that's a \t tab| and this is a \nline feed [LF]

and will get the desired result of:

Field 1: that's a       tab
Field 2: and this is a         line feed

while using my code they will get undesired results:
Field 1: that's a \t tab
Field 2: and this is a \nline feed


so, basically it really depends on the target audience...

Bruce, does that sounds right to you?

Alon.





-----Original Message-----
From: pgsql-hackers-owner@postgresql.org on behalf of Steve Atkins
Sent: Wed 6/1/2005 10:47 PM
To: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] NOLOGGING option, or ?
On Wed, Jun 01, 2005 at 07:35:33PM -0700, Luke Lonergan wrote:
> >> I propose an extended syntax to COPY with a change in semantics to remove
> >> the default of "WITH ESCAPE '\'".
> >
> > Er, doesn't this break existing database dumps?
>
> Yes, one of the previously stated reasons to create another command for
> loading data.
>
> Another possible approach is to keep the default, but allow the escape
> processing to be turned off.

I've been following this thread, and I'm a little confused. Could you
possibly clarify what you mean, by providing a couple of lines of
input as it would be formatted with escape processing turned off -
containing a text field with an embedded newline and tab and a null field.

Cheers, Steve

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





Re: NOLOGGING option, or ?

From
"Alon Goldshuv"
Date:
>I've been following this thread, and I'm a little confused. Could you
possibly clarify what you mean, by providing a couple of lines of
input as it would be formatted with escape processing turned off -
containing a text field with an embedded newline and tab and a null field.


yeah, this is all a bit confusing, but I *hope* I can clarify things here as I think I got a better understanding now.

before that, let me just clarify that the performance improvements in the patch have nothing to do with the escaping
mechanizm.Escapes could change. The performance gain in due to a buffered processing with minimal line/attribute buffer
loads.

I think that the basic issue is that there are some database users that would like to take their data and put it into
thedatabase without pre-processing it - regardless if there are any backslashes in it or 0x0D (CR's) etc... these are
theusers I am targeting in my patch as these are the users I ran into in the field. The only responsibility of these
usersis to explicitly escape any delimiter or 0x0A (LF) characters that they intend to have as DATA. that's all.  

On the other hand there are users that would like to pre-process their data with C-escape sequences (or alternatevly,
usersthat already have their data escaped) - this is what the postgres COPY targets these days. 

2 different ways to do it... none of them is right or wrong.

Examples:

users that my patch targets may have a data row as such (delim = '|', EOL = [LF]):
   c:\one\two|d:\ten\nine[LF]

using the way i do escaping in my patch those 2 fields of data will end up in the DB as
Field 1: c:\one\two
Field 2: d:\ten\nine

which is what the user would want. If they wanted to have a pipe char in the second field they could escape it as such:
d:\ten\ninehere is a pipe \| [LF] and no error will occur, and result will be: 

Field 2: d:\ten\nine here is a pipe |

If you try to insert that first data line above using the existing COPY command you will get an undesired result:

Field 1: c:one   wo
Field 2: d:            ine


Now, the other way around, users that do intend for their data to have escape sequences in it may have a line like
this:
   that's a \t tab| and this is a \nline feed [LF]

and will get the desired result of:

Field 1: that's a       tab
Field 2: and this is a         line feed

while using my code they will get undesired results:
Field 1: that's a \t tab
Field 2: and this is a \nline feed


so, basically it really depends on the target audience...

Bruce, does that sounds right to you?
Alon.





-----Original Message-----
From: pgsql-hackers-owner@postgresql.org on behalf of Steve Atkins
Sent: Wed 6/1/2005 10:47 PM
To: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] NOLOGGING option, or ?
On Wed, Jun 01, 2005 at 07:35:33PM -0700, Luke Lonergan wrote:
> >> I propose an extended syntax to COPY with a change in semantics to remove
> >> the default of "WITH ESCAPE '\'".
> >
> > Er, doesn't this break existing database dumps?
>
> Yes, one of the previously stated reasons to create another command for
> loading data.
>
> Another possible approach is to keep the default, but allow the escape
> processing to be turned off.

I've been following this thread, and I'm a little confused. Could you
possibly clarify what you mean, by providing a couple of lines of
input as it would be formatted with escape processing turned off -
containing a text field with an embedded newline and tab and a null field.

Cheers, Steve

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






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





Re: NOLOGGING option, or ?

From
"Luke Lonergan"
Date:
Steve,

Oops.  Example below should have read differently:

> Sample with 2 identical rows (with binary representations depicted between
> <>):
> Blahblah<0xaa>blahblah<0x09>blahblah<0x00>blahblah<0xaa>Blahblah<0xaa>blahbl
> ah<0x09>blahblah<0x00>blahblah<0xaa>

Blahblah<0x0a>blahblah<0x09>blahblah<0x00>blahblah<0xaa>Blahblah<0x0a>blahbl
ah<0x09>blahblah<0x00>blahblah<0xaa>

This would result in the load of two records each of which would look like
this (when printed on a typical terminal):

Blahblah
blahblah    blahblah

Luke




Re: NOLOGGING option, or ?

From
Alvaro Herrera
Date:
On Thu, Jun 02, 2005 at 12:30:01AM -0400, Alon Goldshuv wrote:

> before that, let me just clarify that the performance improvements in
> the patch have nothing to do with the escaping mechanizm. Escapes
> could change. The performance gain in due to a buffered processing
> with minimal line/attribute buffer loads.

May I suggest you present them as separate issues and separate patches?
This way, a patch with the performance improvements is very likely to
get merged; simultaneously we can discuss changes to the escaping
mechanism until everyone agrees (or not) and then produce a patch as
appropiate.

-- 
Alvaro Herrera (<alvherre[a]surnet.cl>)
Este mail se entrega garantizadamente 100% libre de sarcasmo.


Re: NOLOGGING option, or ?

From
Oliver Jowett
Date:
Alon Goldshuv wrote:

> I think that the basic issue is that there are some database users that would like to take their data and put it into
thedatabase without pre-processing it [...]
 
> The only responsibility of these users is to explicitly escape any delimiter or 0x0A (LF) characters that they intend
tohave as DATA. that's all. 
 

Haven't you just replaced one preprocessing step with another, then?

-O


Re: NOLOGGING option, or ?

From
Hannu Krosing
Date:
On K, 2005-06-01 at 18:05 -0400, Bruce Momjian wrote:

> What we could do is to do no-WAL automatically for empty tables (like
> when a database is first loaded),

You forget that some databases use WAL for PITR / replication and doing
it automatically there would surely mess up their replica.

How is index creation handeled if it is not logged in WAL ? - is it not automatically WAL'ed ? - Must one recreate
indexesafter PITR or failover ?
 

>  and use the flag for cases where the
> tables is not zero pages.  The fact is that database loads are a prefect
> case for this optimization and old dumps are not going to have that flag
> anyway, and automatic is better if we can do it.

-- 
Hannu Krosing <hannu@tm.ee>


Re: NOLOGGING option, or ?

From
Hannu Krosing
Date:
On K, 2005-06-01 at 11:31 -0400, Tom Lane wrote:
> Hannu Krosing <hannu@skype.net> writes:
> > 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.
> 
> That doesn't seem right to me; the scenario I envision is that you are
> willing to do the initial data loading over again (since you presumably
> still have the source data available).  But once you've got it loaded
> you want full protection.

What I mean, was that as it can't be safely replicated using log-
shipping, It should be visible as such.

> Perhaps it could work to use an ALTER TABLE command to flip the state.

No. It would be the same as flipping a TEMP table to an ordinary table,
which we don't support, and IMHO for a good reason

> But I'm not really seeing the point compared to treating it as a COPY
> option.  

The point is having a separate (sub)type of storage - non-WAL/non-
replicated table and its indexes.

> I do not believe that anyone needs this to work on individual
> INSERT commands --- if you are after max speed, why aren't you using
> COPY?  And treating it as an ALTER property opens the possibility of
> forgetting to ALTER the table back to normal behavior, which would be
> a foot-gun of large caliber indeed :-(

That's what I'm trying to avoid - If it is obvious, that the whole table
is quasi-stable (in PITR/log-shipping sense) it is more clearly a user
choice what kinds of data can be stored there. Same as TEMP tables
again.

-- 
Hannu Krosing <hannu@tm.ee>


Re: NOLOGGING option, or ?

From
"Zeugswetter Andreas DAZ SD"
Date:
> Escape processing would proceed as before, but the semantics would change to
> allow the use of different characters as the escape character, in addition
> to the special characters for delimiter and newline.

If you mean syntax to specify escape and delimiter (and newline ?),
that is a great addition, that imho all would like.

>  Also, escape
> processing would be "false" as the default, so that the only special
> characters by default would be the newline and delimiter characters.

I don't see how that would be any advantage ? What is so wrong about having syntax to
choose no escape processing, like "escape ''" ?

Andreas


Re: NOLOGGING option, or ?

From
"Luke Lonergan"
Date:
Oliver,

> Haven't you just replaced one preprocessing step with another, then?

Generally not.  The most common problem with the current choice of escape
character is that there are *lots* of data load scenarios with backslash in
the text strings.  The extra preprocessing to escape them is unnecessary on
other databases and, in effect, causes the load to be even slower because
you have to prepare the data ahead of time.

Also, note that this patch can also do escape processing and the net result
will still be 5+ times faster than what is there.

In the data warehousing industry, data conversion and manipulation is
normally kept distinct from data loading.  Conversion is done by tools
called ETL (Extract Transform Load) and the database will have a very fast
path for direct loading of the resulting data.  PostgreSQL is definitely a
strange database right now in that there is a default filter applied to the
data on load.

It's even more strange because the load path is so slow, and now that we've
found that the slowness is there mostly because of non-optimized parsing and
attribute conversion routines.  The question of how to do escape processing
is a separate one, but is wrapped up in the question of whether to introduce
a new loading routine or whether to optimize the old one.

- Luke




Re: NOLOGGING option, or ?

From
"Luke Lonergan"
Date:
Andreas,

>> Escape processing would proceed as before, but the semantics would change to
>> allow the use of different characters as the escape character, in addition
>> to the special characters for delimiter and newline.
> 
> If you mean syntax to specify escape and delimiter (and newline ?),
> that is a great addition, that imho all would like.

Cool.  We've found it invaluable with customers doing web data analysis.
> I don't see how that would be any advantage ? What is so wrong about having
> syntax to 
> choose no escape processing, like "escape ''" ?

I agree.

- Luke




Re: NOLOGGING option, or ?

From
Steve Atkins
Date:
On Thu, Jun 02, 2005 at 07:33:13AM -0700, Luke Lonergan wrote:
> Oliver,
> 
> > Haven't you just replaced one preprocessing step with another, then?
> 
> Generally not.  The most common problem with the current choice of escape
> character is that there are *lots* of data load scenarios with backslash in
> the text strings. 

I can only think of one where it's common. Windows filenames. But if
you're going to support arbitrary data in a load then whatever escape
character you choose will appear sometimes.

> The extra preprocessing to escape them is unnecessary on
> other databases and, in effect, causes the load to be even slower because
> you have to prepare the data ahead of time.

> Also, note that this patch can also do escape processing and the net result
> will still be 5+ times faster than what is there.

I strongly suspect that a patch to improve performance without changing
behaviour would be accepted with no questions asked.

One that allowed specifying the field and record delimiters and the
escape character and null symbol might require more discussion about
an appropriate syntax at the very least. So you may want to separate
the two.

> In the data warehousing industry, data conversion and manipulation is
> normally kept distinct from data loading.  Conversion is done by tools
> called ETL (Extract Transform Load) and the database will have a very fast
> path for direct loading of the resulting data.  PostgreSQL is definitely a
> strange database right now in that there is a default filter applied to the
> data on load.

> It's even more strange because the load path is so slow, and now that we've
> found that the slowness is there mostly because of non-optimized parsing and
> attribute conversion routines.  The question of how to do escape processing
> is a separate one, but is wrapped up in the question of whether to introduce
> a new loading routine or whether to optimize the old one.

There are already two loader routines. One of them is text-based and is
designed for easy generation of data load format using simple text
manipulation tools by using delimiters. It also allows (unlike your
suggestion) for loading of arbitrary data from a text file.

Because it allows for arbitrary data and uses delimiters to separate
fields it has to use an escaping mechanism.

If you want to be able to load arbitrary data and not have to handle
escape characters there's are two obvious ways to do it.

The first is that used by MIME and suggested by you. That is to use a
separator that you believe will not appear in the data. That can be
done by using a long multicharacter separator containing random
characters and assuming that sequence won't appear, it can be done by
parsing the input data twice, looking for strings that don't appear
for use as delimiters or it can take advantage of knowledge about
what characters can and can not appear in the input data. (I can't
imagine any case involving data-mining of web logs where the last
is likely to be relevant).

The other is to use length+data format for each tuple, avoiding all
issues of escapes in the data and allowing arbitrary data to be
represented. That's how the binary load format PG supports works, I
believe. If you're really concerned about speed of load that may be a
better format for your front-end to generate, perhaps?

Cheers, Steve


Re: NOLOGGING option, or ?

From
"Luke Lonergan"
Date:
Steve,

> I can only think of one where it's common. Windows filenames.

Nearly all weblog data then.

> But if
> you're going to support arbitrary data in a load then whatever escape
> character you choose will appear sometimes.

If we allow an 8-bit character set in the "text" file, then yes, any
delimiter you choose has the potential to appear in your input data.  In
practice, with *mostly* 7-bit ASCII characters and even with international
8-bit text encodings, you can choose a delimiter and newline that work well.
Exceptions are handled by the forthcoming single row error handling patch.

> I strongly suspect that a patch to improve performance without changing
> behaviour would be accepted with no questions asked.

Understood - not sure it's the best thing for support of the users yet.
We've found a large number of issues from customers with the unmodified
behavior. 
> There are already two loader routines. One of them is text-based and is
> designed for easy generation of data load format using simple text
> manipulation tools by using delimiters. It also allows (unlike your
> suggestion) for loading of arbitrary data from a text file.

Not to distract, but try loading a binary null into a text field.  The
assumption of null terminated strings penetrates deep into the codebase.
The existing system does not allow for loading arbitrary data from a text
file.

Our suggestion allows for escapes, but requires the ability to specify
alternate characters or none.
> Because it allows for arbitrary data and uses delimiters to separate
> fields it has to use an escaping mechanism.
> 
> If you want to be able to load arbitrary data and not have to handle
> escape characters there's are two obvious ways to do it.

Let's dispense with the notion that we're suggesting no escapes (see above).

Binary with a bookends format is a fine idea and would be my personal
preference if it were fast, which it isn't.  Customers in the web log
analysis and other data warehousing fields prefer "mostly 7-bit" ascii text
input, which we're trying to support with this change.

- Luke




Re: NOLOGGING option, or ?

From
"Alon Goldshuv"
Date:
>> 2) A modified command syntax for introducing a direct single row error
>> handling. By direct I mean - a row that if rejected from within the COPY
>> command context does not throw an error and rollsback the whole transaction.
>> Instead the error is caught and recorded elsewhere, maybe in some error
>> table, with some more information that can later on be retrieved. The
>> following rows continue to be processed. This way there is barely any error
>> handling overhead.
> 
> Is there any idea on exactly how would this be done?  Do you plan on
> using savepoints to implement it?  I fail to see how is this "barely any
> overhead".  Savepoints are not that expensive but they are not free either.
> (No, I haven't measured it.)

Good question, I am not entirely sure if this is possible yet, as I didn't
think it through entirely yet.

I guess data errors could be divided into 2 main categories: mal-formed data
where error is detected even before forming a tuple, and the other is errors
that are caused by some constraint violation, that is, after the tuple is
formed and inserted.

From what I hear and experience the big majority of errors are of the first
type. In that case the error could be caught, the data line + line number +
error description could be inserted into an ERROR table (all TEXT fields),
and then COPY can skip forming a tuple, and move to parsing the next. In
this process there is barely any overhead.

The more difficult part obviously is handling the second error type, which I
haven't looked at yet deeply. Hopefully it is not impossible to do while
keeping transaction integrity (Any ideas anyone?). The overhead for this one
will probably be larger, but again, we expect those to happen less (in most
cases at least). Nevertheless, it is surely much faster than recursively
narrowing down batch sizes.


Alon.




Re: NOLOGGING option, or ?

From
Greg Stark
Date:
"Luke Lonergan" <llonergan@greenplum.com> writes:

> In the data warehousing industry, data conversion and manipulation is
> normally kept distinct from data loading.  

It's a bit strange to call this conversion or manipulation. One way or another
you have to escape whatever your delimiters are. How would you propose loading
strings that contain newlines?

The ETL transformations you're talking about are a different beast entirely.
You're talking about things like canonicalizing case or looking up foreign key
ids to replace strings and such.

Simply parsing the file format properly isn't part of that game. Otherwise
where do you stop? You could take this to a silly extreme and just say
postgres should just load each line as a record with single text field and let
"tools" deal with actually parsing. Or better yet, load the whole thing as a
single big blob.

Personally I would prefer to make prepared inserts as efficient as COPY and
deprecate COPY. Then we could have an entirely client-side tool that handled
as many formats as people want to implement without complicating the server.
Things like various vintages of Excel, fixed column files, etc should all be
handled as plugins for such a tool.

That would have the side benefit of allowing people to do other batch jobs
efficiently. Pipelining parameters to hundreds of executions of a prepared
query in the network. 

Actually it seems like there's no particular reason the NOLOGGING option Tom
described (where it only inserts on new pages, doesn't have any special WAL
entries, just fsyncs at the end instead of WAL logging) can't work with
arbitrary inserts. Somehow some state has to be preserved remembering which
pages the nologging inserts have created and hold locks on.

-- 
greg



Re: NOLOGGING option, or ?

From
Bruce Momjian
Date:
Alon Goldshuv wrote:
> >I've been following this thread, and I'm a little confused. Could you
> possibly clarify what you mean, by providing a couple of lines of input
> as it would be formatted with escape processing turned off - containing
> a text field with an embedded newline and tab and a null field.
> 
> 
> yeah, this is all a bit confusing, but I *hope* I can clarify things
> here as I think I got a better understanding now.

The basic problem with this thread is that it started with _conclusions_
(we need a LOAD DATA command, escapes are broken), and not with
statements of fact (we need another way of specifying escapes, we have
performance improvements).

Any discussion that starts with conclusions instead of facts is bound to
have this problem, and it often happens when a group discusses among
themselves, outside the community, and appears with the conclusions,
thinking they are helping us by not going into the details.  As you can
see, lack of facts actually hampers the discussion.

What has me particularly concerned is someone saying that loading C:\TMP
must be broken, and not understanding that the doubling of escapes is a
major requirement to have data loaded reliably.  Now, you can argue that
a different escape should be possible, or that some other escape syntax
could be used, but the existing mechanism is clearly 100% reliable when
used properly and not broken.

A quick email asking why C:\TMP doesn't load in properly would have
yielded a much clearer conversation about why escaping is required in
our current system, and the other options that should be explored. 
Saying escapes are broken and here is the fix really didn't get very
far.

I recommend you just start a new thread, with a new topic, and head in
the _facts_ direction.

-- 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,
Pennsylvania19073
 


Re: NOLOGGING option, or ?

From
"Alon Goldshuv"
Date:
Bruce,

Point taken. 

> Now, you can argue that
> a different escape should be possible, or that some other escape syntax
> could be used, but the existing mechanism is clearly 100% reliable when
> used properly and not broken.

I think that having an option for another escape syntax (such as using
ESCAPE AS clause in delimited format (non-csv) COPY) with a default of '\\'
is a good compromise that will allow users to escape their data (like COPY
currently is), or by specifying another escape character allow all of their
backslashes to be treated as data.

I'll start a new discussion about it on a new thread soon.

Thx,
Alon. 




Re: NOLOGGING option, or ?

From
Bruce Momjian
Date:
Alon Goldshuv wrote:
> Bruce,
> 
> Point taken. 
> 
> > Now, you can argue that
> > a different escape should be possible, or that some other escape syntax
> > could be used, but the existing mechanism is clearly 100% reliable when
> > used properly and not broken.
> 
> I think that having an option for another escape syntax (such as using
> ESCAPE AS clause in delimited format (non-csv) COPY) with a default of '\\'
> is a good compromise that will allow users to escape their data (like COPY
> currently is), or by specifying another escape character allow all of their
> backslashes to be treated as data.
> 
> I'll start a new discussion about it on a new thread soon.

Yep, great.  Right now we only support single-byte escapes (and
delimiters), so there should be a discussion if multiple byte values are
useful too.  Also, there was discussion of what the default should be. 
I don't think there is any agreement to change the existing defaults. 
You might argue for a new mode that sets certain defaults to be easier
to load, but I am afraid of a new format that isn't 100% reliable,
because it assumes that some sequence of bytes will never appear in the
data.

Anyway, these are ideas you can consider when making a proposal.

--  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,
Pennsylvania19073
 


Re: NOLOGGING option, or ?

From
"Luke Lonergan"
Date:
Bruce,

Is there a good source of multi-byte copy data test cases?  What is
currently done to test the trans-coding support? (where client and server
encodings are different)

I notice that the regression data in the CVS version of postgres does not
seem to include cases other than the ASCII data, is there another source of
data/cases we're missing?

Also - Alon's looking into this, but it would appear that the presumption on
EOL for two-byte encodings is 0x0a+0xNN, where 0x0a is followed by any byte.
Similar for other current control characters (escape, delimiter).  Is there
a definition of format and semantics for COPY with 2-byte encodings we
should look at?

I've looked at the code and the docs like sql-copy.html and the question is
relevant because of the following case: if newline were defined as 0x0a+0x00 as opposed to 0x0a+0xNN where N is
arbitrary, we could parse using 16-bit logic.however if newline were defined as 0x0a+0xNN, we must use byte-wise
parsing

TIA

- Luke




Re: NOLOGGING option, or ?

From
Bruce Momjian
Date:
Luke Lonergan wrote:
> Bruce,
> 
> Is there a good source of multi-byte copy data test cases?  What is
> currently done to test the trans-coding support? (where client and server
> encodings are different)
> 
> I notice that the regression data in the CVS version of postgres does not
> seem to include cases other than the ASCII data, is there another source of
> data/cases we're missing?
> 
> Also - Alon's looking into this, but it would appear that the presumption on
> EOL for two-byte encodings is 0x0a+0xNN, where 0x0a is followed by any byte.
> Similar for other current control characters (escape, delimiter).  Is there
> a definition of format and semantics for COPY with 2-byte encodings we
> should look at?
> 
> I've looked at the code and the docs like sql-copy.html and the question is
> relevant because of the following case:
>   if newline were defined as 0x0a+0x00 as opposed to 0x0a+0xNN where N is
> arbitrary, we could parse using 16-bit logic.
>  however
>   if newline were defined as 0x0a+0xNN, we must use byte-wise parsing

We have two and three-byte encodings, so 16-bit seems like it wouldn't
work.  I am not aware of any specs except the C code itself.

--  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,
Pennsylvania19073
 


Re: NOLOGGING option, or ?

From
"Luke Lonergan"
Date:
Bruce,
> We have two and three-byte encodings, so 16-bit seems like it wouldn't
> work.  I am not aware of any specs except the C code itself.

Ok - no problem.

How about test data and cases?  I see the SQL encoding examples used in
src/test/regress/sql for testing encoding in SQL, but are there regressions
for QA/test of multi-byte encoding support?  If not, that's OK, but it would
save us time if some were already written.

WRT the COPY command, I'd like to have regressions that test the input of
matched client/server encodings with different (standardized) multi-byte
control characters.

The current code seems to allow for an arbitrary second byte in control
characters, so if we made a statement about control character support, I
think it would be  <ctl-byte><any-byte>...mblen...<any-byte>

is allowed for specification of control characters (newline, delimiter).
Luke




Re: NOLOGGING option, or ?

From
Bruce Momjian
Date:
Luke Lonergan wrote:
> Bruce,
>  
> > We have two and three-byte encodings, so 16-bit seems like it wouldn't
> > work.  I am not aware of any specs except the C code itself.
> 
> Ok - no problem.
> 
> How about test data and cases?  I see the SQL encoding examples used in
> src/test/regress/sql for testing encoding in SQL, but are there regressions
> for QA/test of multi-byte encoding support?  If not, that's OK, but it would
> save us time if some were already written.

No, I don't think so, but the good news is that the existing code has
always worked flawlessly.

> WRT the COPY command, I'd like to have regressions that test the input of
> matched client/server encodings with different (standardized) multi-byte
> control characters.

Makes sense, but how do we know what encodings the client supports?  We
would need some tests for that.

> The current code seems to allow for an arbitrary second byte in control
> characters, so if we made a statement about control character support, I
> think it would be
>    <ctl-byte><any-byte>...mblen...<any-byte>
> 
> is allowed for specification of control characters (newline, delimiter).

I have no idea what you are talking about.  Again, give me facts about
what we currently don't do and what you want to do.

--  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,
Pennsylvania19073
 


Re: NOLOGGING option, or ?

From
"Luke Lonergan"
Date:
Bruce,

> 
> I have no idea what you are talking about.  Again, give me facts about
> what we currently don't do and what you want to do.

Currently:
- No statement of multi-byte control character format
- No tests to define or prove "works flawlessly" or identify when something
breaks the current operational state

Desired:
- Clear statement of multi-byte control character format
- Tests that define what "works flawlessly" means

- Luke