ALTER TABLE ... REPLACE WITH

Simon Riggs <simon@2ndQuadrant.com> writes:

There are various applications where we want to completely replace the
contents of a table with new/re-calculated data.

It seems fairly obvious to be able to do this like...
1. Prepare new data into "new_table" and build indexes
2. Swap old for new
BEGIN;
DROP TABLE "old_table";
ALTER TABLE "new_table" RENAME to "old_table";
COMMIT;

Why not

BEGIN;
TRUNCATE TABLE;
... load new data ...
COMMIT;

What I propose is to write a function/command to allow this to be
explicitly achievable by the server.

ALTER TABLE "old_table"
REPLACE WITH "new_table";

I don't think the cost/benefit ratio of this is anywhere near as good
as you seem to think (ie, you're both underestimating the work involved
and overstating the benefit). I'm also noticing a lack of specification
as to trigger behavior, foreign keys, etc. The apparent intention to
disregard FKs entirely is particularly distressing,

regards, tom lane

On Tue, 2010-12-14 at 13:54 -0500, Tom Lane wrote:

Simon Riggs <simon@2ndQuadrant.com> writes:

There are various applications where we want to completely replace the
contents of a table with new/re-calculated data.

It seems fairly obvious to be able to do this like...
1. Prepare new data into "new_table" and build indexes
2. Swap old for new
BEGIN;
DROP TABLE "old_table";
ALTER TABLE "new_table" RENAME to "old_table";
COMMIT;

Why not

BEGIN;
TRUNCATE TABLE;
... load new data ...
COMMIT;

The above is atomic, but not fast.

The intention is to produce an atomic swap with as small a lock window
as possible, to allow it to happen in real operational systems.

At the moment we have a choice of fast or atomic. We need both.

(Note that there are 2 utilities that already do this, but the
operations aren't supported in core Postgres).

What I propose is to write a function/command to allow this to be
explicitly achievable by the server.

ALTER TABLE "old_table"
REPLACE WITH "new_table";

I don't think the cost/benefit ratio of this is anywhere near as good
as you seem to think (ie, you're both underestimating the work involved
and overstating the benefit). I'm also noticing a lack of specification
as to trigger behavior, foreign keys, etc. The apparent intention to
disregard FKs entirely is particularly distressing,

No triggers would be fired. All constraints that exist on "old_table"
must also exist on "new_table". As I said, lots of checks required, no
intention to add back doors.

("Disregard FKs" is the other project, not connected other than both are
operations on tables designed to improve manageability of large tables.)

--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services

On Tue, Dec 14, 2010 at 1:54 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

BEGIN;
TRUNCATE TABLE;
... load new data ...
COMMIT;

Because then you have to take an AccessExclusiveLock on the target
table, of course.

If we had some kind of TRUNCATE CONCURRENTLY, I think that'd address a
large portion of the use case for the proposed feature.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 12/14/10 11:07 AM, Robert Haas wrote:

Because then you have to take an AccessExclusiveLock on the target
table, of course.

Well, you have to do that for DROP TABLE as well, and I don't see any
way around doing it for REPLACE WITH.

As for the utility of this command: there is no question that I would
use it. I'm not sure I like the syntax (I'd prefer REPLACE TABLE ____
WITH _____), but that's painting the bike shed. While the command may
appear frivolous and unnecessary syntactical ornamentation to some, I
have to say that doing the "table doesy-doe" which this command
addresses is something I have written scripts for on at least 50% of my
professional clients. It keeps coming up.

In order for REPLACE WITH to be really useful, though, we need a command
cloning at table design with *all* constraints, FKs, keys, and indexes.
Currently, I still don't think we have that ... do we?

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

On 14.12.2010 20:27, Simon Riggs wrote:

There are various applications where we want to completely replace the
contents of a table with new/re-calculated data.

It seems fairly obvious to be able to do this like...
1. Prepare new data into "new_table" and build indexes
2. Swap old for new
BEGIN;
DROP TABLE "old_table";
ALTER TABLE "new_table" RENAME to "old_table";
COMMIT;

Step (2) works, but any people queuing to access the table will see
ERROR: could not open relation with OID xxxxx

Could we make that work without error?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Tue, Dec 14, 2010 at 2:34 PM, Josh Berkus <josh@agliodbs.com> wrote:

On 12/14/10 11:07 AM, Robert Haas wrote:

Because then you have to take an AccessExclusiveLock on the target
table, of course.

Well, you have to do that for DROP TABLE as well, and I don't see any
way around doing it for REPLACE WITH.

Sure, but in Simon's proposal you can load the data FIRST and then
take a lock just long enough to do the swap. That's very different
from needing to hold the lock during the whole data load.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, 2010-12-14 at 21:35 +0200, Heikki Linnakangas wrote:

On 14.12.2010 20:27, Simon Riggs wrote:

There are various applications where we want to completely replace the
contents of a table with new/re-calculated data.

It seems fairly obvious to be able to do this like...
1. Prepare new data into "new_table" and build indexes
2. Swap old for new
BEGIN;
DROP TABLE "old_table";
ALTER TABLE "new_table" RENAME to "old_table";
COMMIT;

Step (2) works, but any people queuing to access the table will see
ERROR: could not open relation with OID xxxxx

Could we make that work without error?

Possibly, and good thinking, but its effectively the same patch, just
syntax free since we still need to do lots of checking to avoid swapping
oranges with lemons.

I prefer explicit syntax because its easier to be certain that you've
got it right.

--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services

On Tue, 2010-12-14 at 11:34 -0800, Josh Berkus wrote:

In order for REPLACE WITH to be really useful, though, we need a
command cloning at table design with *all* constraints, FKs, keys, and
indexes. Currently, I still don't think we have that ... do we?

Being able to vary the indexes when we REPLACE is a good feature.

We only need to check that datatypes and constraints match.

--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services

On Tue, 2010-12-14 at 11:34 -0800, Josh Berkus wrote:

As for the utility of this command: there is no question that I would
use it. I'm not sure I like the syntax (I'd prefer REPLACE TABLE ____
WITH _____), but that's painting the bike shed.

REPLACE TABLE ying WITH yang

is probably easier to implement than hacking at the ALTER TABLE code
mountain.

While the command may
appear frivolous and unnecessary syntactical ornamentation to some, I
have to say that doing the "table doesy-doe" which this command
addresses is something I have written scripts for on at least 50% of
my professional clients. It keeps coming up.

Yeh.

--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services

On 12/14/10 11:43 AM, Simon Riggs wrote:

On Tue, 2010-12-14 at 11:34 -0800, Josh Berkus wrote:

In order for REPLACE WITH to be really useful, though, we need a
command cloning at table design with *all* constraints, FKs, keys, and
indexes. Currently, I still don't think we have that ... do we?

Being able to vary the indexes when we REPLACE is a good feature.

We only need to check that datatypes and constraints match.

No, you're missing my point ... currently we don't have a command which
says "make an identical clone of this table". CREATE TABLE AS allows us
to copy all of the data for the table, but not the full table design.
CREATE TABLE LIKE gives us most of the design (although it still won't
copy FKs) but won't copy the data.

However, for the usual do-si-do case, you need to populate the data
using a query and not clone all the data. What you'd really need is
something like:

CREATE TABLE new_table LIKE old_table ( INCLUDING ALL ) FROM SELECT ...

.. which would create the base tabledef, copy in the data from the
query, and then apply all the constraints, indexes, defaults, etc.

Without some means of doing a clone of the table in a single command,
you've eliminated half the scripting work, but not helped at all with
the other half.

Actually, you know what would be ideal?

REPLACE TABLE old_table WITH SELECT ...

Give it some thought ...

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

On Tue, 2010-12-14 at 16:19 -0800, Josh Berkus wrote:

Without some means of doing a clone of the table in a single command,
you've eliminated half the scripting work, but not helped at all with
the other half.

I'm not trying to eliminate scripting work, I'm trying to minimise the
lock window with a reliable and smooth atomic switcheroo.

Actually, you know what would be ideal?

REPLACE TABLE old_table WITH SELECT ...

Give it some thought ...

I have; the above would hold the lock window open while the SELECT runs
and that is explicitly something we are trying to avoid.

Good creative input though, thank you.

--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services

I have; the above would hold the lock window open while the SELECT runs
and that is explicitly something we are trying to avoid.

Not necessarily. You could copy into a temp table first, and then swap.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

On Tue, 2010-12-14 at 14:36 -0500, Robert Haas wrote:

Well, you have to do that for DROP TABLE as well, and I don't see any
way around doing it for REPLACE WITH.

Sure, but in Simon's proposal you can load the data FIRST and then
take a lock just long enough to do the swap. That's very different
from needing to hold the lock during the whole data load.

Except Simon's original proposal has this line in it:

* "new_table" is TRUNCATEd.

I guess Simon mixed up "new_table" and "old_table", and the one which
should get truncated is the replaced one and not the replacement,
otherwise it doesn't make sense to me.

BTW, I would have also used such a feature on multiple occasions in the
past and expect I would do in the future too.

Cheers,
Csaba.

On Wed, 2010-12-15 at 10:54 +0100, Csaba Nagy wrote:

On Tue, 2010-12-14 at 14:36 -0500, Robert Haas wrote:

Well, you have to do that for DROP TABLE as well, and I don't see any
way around doing it for REPLACE WITH.

Sure, but in Simon's proposal you can load the data FIRST and then
take a lock just long enough to do the swap. That's very different
from needing to hold the lock during the whole data load.

Except Simon's original proposal has this line in it:

* "new_table" is TRUNCATEd.

I guess Simon mixed up "new_table" and "old_table", and the one which
should get truncated is the replaced one and not the replacement,
otherwise it doesn't make sense to me.

What I meant was...

REPLACE TABLE target WITH source;

* target's old rows are discarded
* target's new rows are all of the rows from "source".
* source is then truncated, so ends up empty

Perhaps a more useful definition would be

EXCHANGE TABLE target WITH source;

which just swaps the heap and indexes of each table.
You can then use TRUNCATE if you want to actually destroy data.

I will go with that unless we have other objections.

BTW, I would have also used such a feature on multiple occasions in the
past and expect I would do in the future too.

Cheers,
Csaba.

--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services

On Wed, 2010-12-15 at 10:39 +0000, Simon Riggs wrote:

Perhaps a more useful definition would be

EXCHANGE TABLE target WITH source;

which just swaps the heap and indexes of each table.
You can then use TRUNCATE if you want to actually destroy data.

Yes please, that's exactly what I would have needed in many occasions.

But one problem would be when the replaced table is the _parent_ for a
foreign key relationship. I don't think you can have that constraint
pre-verified on the replacement table and simply replacing the content
could leave the child relations with orphans.

Cheers,
Csaba.

On Wed, Dec 15, 2010 at 5:39 AM, Simon Riggs <simon@2ndquadrant.com> wrote:

Perhaps a more useful definition would be

EXCHANGE TABLE target WITH source;

which just swaps the heap and indexes of each table.
You can then use TRUNCATE if you want to actually destroy data.

I will go with that unless we have other objections.

I still don't see how that's going to work with foreign keys. If
there's a foreign key referencing the old table, there's no way to be
sure that all of those references are still going to be valid with
respect to the new table without a full-table check. And that seems
to defeat the purpose of the feature.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Dec 15, 2010, at 4:39 AM, Simon Riggs wrote:

On Wed, 2010-12-15 at 10:54 +0100, Csaba Nagy wrote:

On Tue, 2010-12-14 at 14:36 -0500, Robert Haas wrote:

Well, you have to do that for DROP TABLE as well, and I don't see any
way around doing it for REPLACE WITH.

Sure, but in Simon's proposal you can load the data FIRST and then
take a lock just long enough to do the swap. That's very different
from needing to hold the lock during the whole data load.

Except Simon's original proposal has this line in it:

* "new_table" is TRUNCATEd.

I guess Simon mixed up "new_table" and "old_table", and the one which
should get truncated is the replaced one and not the replacement,
otherwise it doesn't make sense to me.

What I meant was...

REPLACE TABLE target WITH source;

* target's old rows are discarded
* target's new rows are all of the rows from "source".
* source is then truncated, so ends up empty

Perhaps a more useful definition would be

EXCHANGE TABLE target WITH source;

which just swaps the heap and indexes of each table.
You can then use TRUNCATE if you want to actually destroy data.

Are there any considerations with toast tables and the inline line pointers for toasted tuples?

Regards,

David
--
David Christensen
End Point Corporation
david@endpoint.com

On Wed, 2010-12-15 at 07:43 -0600, David Christensen wrote:

Are there any considerations with toast tables and the inline line pointers for toasted tuples?

Toast tables would be swapped as well. Toast pointers are only
applicable within a relfilenode, so we could not do otherwise.

--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services

On Wed, 2010-12-15 at 12:17 +0100, Csaba Nagy wrote:

But one problem would be when the replaced table is the _parent_ for a
foreign key relationship. I don't think you can have that constraint
pre-verified on the replacement table and simply replacing the content
could leave the child relations with orphans.

Good point.

The only sensible way to handle this is by putting the FK checks into
check pending state (as discussed on a different thread).

We would probably need to disallow FKs with DELETE or UPDATE CASCADE
since it would be difficult to execute those.

--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services