COPY and Volatile default expressions
COPY cannot be optimised correctly if we have before triggers or
volatile default expressions.
The multi-insert code detects those cases and falls back to the single
row mechanism in those cases.
There a common class of volatile functions that wouldn't cause
problems: any volatile function that doesn't touch the table being
loaded and still works correctly when called with alternately ordered
data.
I claim this is a common class, since sequence next_val functions and
uuid generators meet that criteria and most common forms of auditing
trigger, as well as any other form of data-reformatting trigger. Since
this is a common case, it seems worth optimising.
What I'd like to do is to invent a new form of labelling that allows
us to understand that COPY can still be optimised. I'm thinking to add
a new function label, something like one of
* IDEMPOTENT
* ORDER INDEPENDENT
* BATCHABLE
* NON SELF REFERENCING
* GO FASTER DAMMIT
etc
I'm sure many people will have a much more exact description and a
better name than I do.
This becomes more important when we think about parallelising SQL,
since essentially the same problem exists with parallel SQL calling
volatile functions. Oracle handles that by having a pragma to allow a
function to be declared as parallel safe.
I was also thinking that the best way to do this would be to invent a
new flexible function labelling scheme, so use something like hstore
to store a list of function attributes. Something that would mean we
don't have to invent new keywords every time we have a new function
label.
Suggestions please.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Apr 15, 2013 at 03:00:34PM +0100, Simon Riggs wrote:
COPY cannot be optimised correctly if we have before triggers or
volatile default expressions.The multi-insert code detects those cases and falls back to the
single row mechanism in those cases.There a common class of volatile functions that wouldn't cause
problems: any volatile function that doesn't touch the table being
loaded and still works correctly when called with alternately
ordered data.
"Doesn't touch already-existing rows?" Makes a lot of sense :)
I claim this is a common class, since sequence next_val functions and
uuid generators meet that criteria and most common forms of auditing
trigger, as well as any other form of data-reformatting trigger. Since
this is a common case, it seems worth optimising.
Do you have numbers on this, or ways to gather same? In other words,
how do we know what resources (time, CPU cycles, disk seeks, etc.) are
being consumed here?
What I'd like to do is to invent a new form of labelling that allows
us to understand that COPY can still be optimised. I'm thinking to add
a new function label, something like one of
* IDEMPOTENT
* ORDER INDEPENDENT
* BATCHABLE
* NON SELF REFERENCING
* GO FASTER DAMMIT
etcI'm sure many people will have a much more exact description and a
better name than I do.This becomes more important when we think about parallelising SQL,
since essentially the same problem exists with parallel SQL calling
volatile functions. Oracle handles that by having a pragma to allow a
function to be declared as parallel safe.
What happens when you misinform Oracle about this? Does it attempt to
check? More importantly, what *should* happen?
I was also thinking that the best way to do this would be to invent a
new flexible function labelling scheme, so use something like hstore
to store a list of function attributes. Something that would mean we
don't have to invent new keywords every time we have a new function
label.Suggestions please.
JSON's in core. How about using that?
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 15.04.2013 17:00, Simon Riggs wrote:
COPY cannot be optimised correctly if we have before triggers or
volatile default expressions.The multi-insert code detects those cases and falls back to the single
row mechanism in those cases.There a common class of volatile functions that wouldn't cause
problems: any volatile function that doesn't touch the table being
loaded and still works correctly when called with alternately ordered
data.I claim this is a common class, since sequence next_val functions and
uuid generators meet that criteria and most common forms of auditing
trigger, as well as any other form of data-reformatting trigger. Since
this is a common case, it seems worth optimising.What I'd like to do is to invent a new form of labelling that allows
us to understand that COPY can still be optimised.
It would be even nicer to detect at runtime, when a default expression
or before trigger tries to access the same table. When that happens, we
could immediately flush all the tuples buffered that far to disk, so
that they are visible to the expression, and then proceed with it.
- Heikki
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 15 April 2013 16:24, David Fetter <david@fetter.org> wrote:
I claim this is a common class, since sequence next_val functions and
uuid generators meet that criteria and most common forms of auditing
trigger, as well as any other form of data-reformatting trigger. Since
this is a common case, it seems worth optimising.Do you have numbers on this, or ways to gather same? In other words,
how do we know what resources (time, CPU cycles, disk seeks, etc.) are
being consumed here?
The multi-insert optimisation for COPY is already there and works well
enough to have been committed.
All we have to do to allow it to be used is to persuade COPY that come
kinds of volatile function need not prevent the optimisation. So once
we have a mechanism for appropriately labelling a function, it will be
a one-line change in copy.c to enable the optimisation.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Simon Riggs <simon@2ndQuadrant.com> writes:
COPY cannot be optimised correctly if we have before triggers or
volatile default expressions.
The multi-insert code detects those cases and falls back to the single
row mechanism in those cases.
There a common class of volatile functions that wouldn't cause
problems: any volatile function that doesn't touch the table being
loaded and still works correctly when called with alternately ordered
data.
I claim this is a common class, since sequence next_val functions and
uuid generators meet that criteria and most common forms of auditing
trigger, as well as any other form of data-reformatting trigger.
I don't believe that it's a good idea to consider nextval() to be
reorderable, so I'm not convinced by your argument here.
What I'd like to do is to invent a new form of labelling that allows
us to understand that COPY can still be optimised.
And I don't want to invent impossible-to-verify function attributes with
such a tiny use-case as this.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 15 April 2013 16:41, Heikki Linnakangas <hlinnakangas@vmware.com> wrote:
What I'd like to do is to invent a new form of labelling that allows
us to understand that COPY can still be optimised.It would be even nicer to detect at runtime, when a default expression or
before trigger tries to access the same table. When that happens, we could
immediately flush all the tuples buffered that far to disk, so that they are
visible to the expression, and then proceed with it.
Maybe, but do we really want an extra test every time we access a
table? And if we did that, how would we pass control back to the COPY
command to allow it flush the buffer before continuing with the
function?
How would we cope with times when a subtle problem makes a function
unusable, even though it passes automatic detection?
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Simon Riggs <simon@2ndQuadrant.com> writes:
On 15 April 2013 16:24, David Fetter <david@fetter.org> wrote:
Do you have numbers on this, or ways to gather same? In other words,
how do we know what resources (time, CPU cycles, disk seeks, etc.) are
being consumed here?
The multi-insert optimisation for COPY is already there and works well
enough to have been committed.
You seem to not have answered the question. Exactly what sort of
performance gain might be possible, bearing in mind that anything that
invokes a trigger (for instance) is unlikely to be amazingly fast
anyway?
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Apr 15, 2013 at 11:49:42AM -0400, Tom Lane wrote:
Simon Riggs <simon@2ndQuadrant.com> writes:
COPY cannot be optimised correctly if we have before triggers or
volatile default expressions.The multi-insert code detects those cases and falls back to the single
row mechanism in those cases.There a common class of volatile functions that wouldn't cause
problems: any volatile function that doesn't touch the table being
loaded and still works correctly when called with alternately ordered
data.I claim this is a common class, since sequence next_val functions and
uuid generators meet that criteria and most common forms of auditing
trigger, as well as any other form of data-reformatting trigger.I don't believe that it's a good idea to consider nextval() to be
reorderable, so I'm not convinced by your argument here.
We tell people very clearly in the docs and elsewhere that nextval()
guarantees uniqueness and very specifically not ordering, so with
greatest respect, I agree with Simon on its reorderability.
What I'd like to do is to invent a new form of labelling that
allows us to understand that COPY can still be optimised.And I don't want to invent impossible-to-verify function attributes
with such a tiny use-case as this.
Are you referring to the Halting Problem?
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 15 April 2013 16:55, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Simon Riggs <simon@2ndQuadrant.com> writes:
On 15 April 2013 16:24, David Fetter <david@fetter.org> wrote:
Do you have numbers on this, or ways to gather same? In other words,
how do we know what resources (time, CPU cycles, disk seeks, etc.) are
being consumed here?The multi-insert optimisation for COPY is already there and works well
enough to have been committed.You seem to not have answered the question. Exactly what sort of
performance gain might be possible, bearing in mind that anything that
invokes a trigger (for instance) is unlikely to be amazingly fast
anyway?
Forgive me, I assumed the list would be familiar with the optimization
and so be excited by the need for this.
I will implement as a kluge, test and report the results.
Loading data into a table with a SERIAL or UUID column is the main use
case, so I'll measure that.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Apr 15, 2013 at 05:04:16PM +0100, Simon Riggs wrote:
On 15 April 2013 16:55, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Simon Riggs <simon@2ndQuadrant.com> writes:
On 15 April 2013 16:24, David Fetter <david@fetter.org> wrote:
Do you have numbers on this, or ways to gather same? In other
words, how do we know what resources (time, CPU cycles, disk
seeks, etc.) are being consumed here?The multi-insert optimisation for COPY is already there and works
well enough to have been committed.You seem to not have answered the question. Exactly what sort of
performance gain might be possible, bearing in mind that anything
that invokes a trigger (for instance) is unlikely to be amazingly
fast anyway?Forgive me, I assumed the list would be familiar with the
optimization and so be excited by the need for this.I will implement as a kluge, test and report the results.
Loading data into a table with a SERIAL or UUID column is the main
use case, so I'll measure that.
The former is common enough a use case to optimize specifically,
should the numbers come out right. Do you suppose that an in-core
UUID generator would help the latter make more sense as a part of the
same use case?
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 15 April 2013 17:08, David Fetter <david@fetter.org> wrote:
Loading data into a table with a SERIAL or UUID column is the main
use case, so I'll measure that.The former is common enough a use case to optimize specifically,
should the numbers come out right. Do you suppose that an in-core
UUID generator would help the latter make more sense as a part of the
same use case?
Only if some form of labelling becomes an issue, but I'm ever hopeful.
Let's wait for the test results now.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 04/15/2013 06:04 PM, Simon Riggs wrote:
On 15 April 2013 16:55, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Simon Riggs <simon@2ndQuadrant.com> writes:
On 15 April 2013 16:24, David Fetter <david@fetter.org> wrote:
Do you have numbers on this, or ways to gather same? In other words,
how do we know what resources (time, CPU cycles, disk seeks, etc.) are
being consumed here?The multi-insert optimisation for COPY is already there and works well
enough to have been committed.You seem to not have answered the question. Exactly what sort of
performance gain might be possible, bearing in mind that anything that
invokes a trigger (for instance) is unlikely to be amazingly fast
anyway?Forgive me, I assumed the list would be familiar with the optimization
and so be excited by the need for this.I will implement as a kluge, test and report the results.
Would just declaring nextval() to be a stable function be a good test ?
Hannu
Loading data into a table with a SERIAL or UUID column is the main use
case, so I'll measure that.--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
David Fetter <david@fetter.org> writes:
On Mon, Apr 15, 2013 at 05:04:16PM +0100, Simon Riggs wrote:
Loading data into a table with a SERIAL or UUID column is the main
use case, so I'll measure that.
The former is common enough a use case to optimize specifically,
should the numbers come out right.
Yeah. TBH I would rather see a special-case hack in the COPY code to
accept nextval() than expose anything as dirty and special-purpose as
this proposed flag to users. But in any case, I don't believe that
adequate evidence has been offered to show that we should do anything
at all here.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 15 April 2013 17:04, Simon Riggs <simon@2ndquadrant.com> wrote:
I will implement as a kluge, test and report the results.
Test is COPY 1 million rows on a table with 2 columns, both bigint.
Verified no checkpoints triggered during load.
No other work active on database, tests condicted on laptop
Autovacuum disabled.
Results from multiple runs, outliers excluded, rough averages
HEAD
COPY, with sequence ~5500ms
COPY, with sequence, cached ~5000ms
COPY, no sequence ~1600ms
PATCH to allow sequences to use multi-insert optimisation (1 line change)
COPY, with sequence ~1850ms
COPY, with sequence, cached ~1750ms
COPY, no sequence ~1600ms
This shows that
* cacheing the sequence gives a useful improvement currently
* use of multi-insert optimisaton is very important
Proposals
* set CACHE 100 on automatically created SERIAL sequences
* allow some way to use multi-insert optimisation when default expr is
next_val on a sequence
Tests performed without indexes since this is another area of known
performance issues that I hope to cover later. Zero indexes is not
real, but we're trying to measure the effect and benefit of an
isolated change, so in this case it is appropriate.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Apr 15, 2013 at 06:30:55PM +0100, Simon Riggs wrote:
On 15 April 2013 17:04, Simon Riggs <simon@2ndquadrant.com> wrote:
I will implement as a kluge, test and report the results.
Test is COPY 1 million rows on a table with 2 columns, both bigint.
Verified no checkpoints triggered during load.
No other work active on database, tests condicted on laptop
Autovacuum disabled.
Results from multiple runs, outliers excluded, rough averagesHEAD
COPY, with sequence ~5500ms
COPY, with sequence, cached ~5000ms
COPY, no sequence ~1600msPATCH to allow sequences to use multi-insert optimisation (1 line change)
COPY, with sequence ~1850ms
COPY, with sequence, cached ~1750ms
COPY, no sequence ~1600msThis shows that
* cacheing the sequence gives a useful improvement currently
* use of multi-insert optimisaton is very importantProposals
* set CACHE 100 on automatically created SERIAL sequences
* allow some way to use multi-insert optimisation when default expr is
next_val on a sequenceTests performed without indexes since this is another area of known
performance issues that I hope to cover later. Zero indexes is not
real, but we're trying to measure the effect and benefit of an
isolated change, so in this case it is appropriate.
The difference between HEAD and patch in the "COPY, with sequence"
case is pretty remarkable. What's the patch?
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 15 April 2013 18:41, David Fetter <david@fetter.org> wrote:
The difference between HEAD and patch in the "COPY, with sequence"
case is pretty remarkable. What's the patch?
Attached.
This is usable only for this test. It is not anywhere remotely close
to being applied.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Attachments:
copy_test_kluge.v1.patchapplication/octet-stream; name=copy_test_kluge.v1.patchDownload+2-2
On Mon, Apr 15, 2013 at 07:04:55PM +0100, Simon Riggs wrote:
On 15 April 2013 18:41, David Fetter <david@fetter.org> wrote:
The difference between HEAD and patch in the "COPY, with sequence"
case is pretty remarkable. What's the patch?Attached.
Thanks! :)
This is usable only for this test. It is not anywhere remotely close
to being applied.
Of course.
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Apr 15, 2013 at 11:49 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I claim this is a common class, since sequence next_val functions and
uuid generators meet that criteria and most common forms of auditing
trigger, as well as any other form of data-reformatting trigger.I don't believe that it's a good idea to consider nextval() to be
reorderable, so I'm not convinced by your argument here.
Why not?
I admit that I can't convince myself that it's safe. But I can't
think of a concrete example where it breaks anything, either.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Robert Haas <robertmhaas@gmail.com> writes:
On Mon, Apr 15, 2013 at 11:49 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I don't believe that it's a good idea to consider nextval() to be
reorderable, so I'm not convinced by your argument here.
Why not?
I admit that I can't convince myself that it's safe. But I can't
think of a concrete example where it breaks anything, either.
I think plenty of people would be upset if row serial numbers assigned
with nextval() were not assigned in the order of the incoming rows.
The argument that you can get gaps in the sequence in some corner cases
(none of which apply within a single COPY operation, btw) doesn't
entitle us to violate the POLA to that extent.
After looking again at the code Simon is concerned about, though,
whether we are willing to allow volatile function calls to be reordered
has approximately nothing to do with this COPY optimization. Rather,
the thing that makes it safe is that nextval() isn't going to look at
the COPY target table, and thus whether or not the previous rows have
been physically inserted isn't important. The same goes for the UUID
example. So I think he's done himself a disservice by talking about
reordering and bringing up the question of parallel queries. What we
ought to be thinking about is how we can be certain that a function call
isn't going to look at the uncommitted table rows; no more and no less.
In this context, I think we could do a lot worse than to special-case
nextval(), because it's hard to see a really principled function
attribute definition that would admit it here. It does look at, and
even modify, uncommitted database state. We know it's safe because a
sequence relation couldn't be the target of COPY ... but that reasoning
doesn't fit nicely into anything I think we'd want to expose to users.
OTOH, the notion that a UUID generator doesn't touch *any* database
state seems like it might be worth treating as a general function
property: it's simple to understand and applies to a lot of other
volatile functions such as random() and clock_timestamp().
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Apr 15, 2013 at 3:21 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
OTOH, the notion that a UUID generator doesn't touch *any* database
state seems like it might be worth treating as a general function
property: it's simple to understand and applies to a lot of other
volatile functions such as random() and clock_timestamp().
Something like the NO SQL access indication mandated by sql standard?
/messages/by-id/1267473390.7837.9.camel@vanquo.pezone.net
--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
Phone: +593 4 5107566 Cell: +593 987171157
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
