error-free disabling of individual child partition tables

On Mon, May 22, 2006 at 02:25:09PM -0400, April Lorenzen wrote:

It comes up repeatedly that I need to load fresh data into a new
table, build indexes and vacuum - then drop the old table and
substitute the new.

Why are you vacuuming a brand new table? Just run analyze.

Postgresql partioning makes this all almost possible: the master
automatically SELECTs from all the child tables - so it automatically
recoginize the new ones, and no loss when the old ones are dropped.

How is that any better than:

BEGIN;
-- Don't do the drop right now, because it might take awhile
ALTER TABLE tablename RENAME TO delete_tablename;
ALTER TABLE temporary_tablename RENAME TO tablename;
COMMIT;
DROP delete_tablename;

Just one thing would make it near perfect: if I could keep the master
from being able to SELECT from the new child table while I'm COPYing
data into the new child, building the index and vacuuming it - without
sending an error back to the user who is querying the master table.

I tried a CHECK constraint, a RULE - SELECT rule can't INSTEAD DO
NOTHING - #postgresql channel people say there's nothing that can do
this... revoking privs or changing the owner of the new child results
in an error back to the user who queries the master table. Tried
creating a dummy table with no records and making a rule for the child
to INSTEAD select from the dummy table but that was not allowed
because it would turn my non-empty child table into a view.

Thank you,

- April

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

--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Jim C. Nasby wrote:

BEGIN;
-- Don't do the drop right now, because it might take awhile
ALTER TABLE tablename RENAME TO delete_tablename;
ALTER TABLE temporary_tablename RENAME TO tablename;
COMMIT;
DROP delete_tablename;

What if there are dependencies? Might be better to have a view, which
everything depends on, and change the view definition from one table to
another back and forth.

cheers

andrew

On Mon, May 22, 2006 at 03:28:39PM -0400, Andrew Dunstan wrote:

Jim C. Nasby wrote:

BEGIN;
-- Don't do the drop right now, because it might take awhile
ALTER TABLE tablename RENAME TO delete_tablename;
ALTER TABLE temporary_tablename RENAME TO tablename;
COMMIT;
DROP delete_tablename;

What if there are dependencies? Might be better to have a view, which
everything depends on, and change the view definition from one table to
another back and forth.

What dependencies would there be that a view would solve? You can't
define RI on a view AFAIK...
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Jim C. Nasby wrote:

On Mon, May 22, 2006 at 03:28:39PM -0400, Andrew Dunstan wrote:

Jim C. Nasby wrote:

BEGIN;
-- Don't do the drop right now, because it might take awhile
ALTER TABLE tablename RENAME TO delete_tablename;
ALTER TABLE temporary_tablename RENAME TO tablename;
COMMIT;
DROP delete_tablename;

What if there are dependencies? Might be better to have a view, which
everything depends on, and change the view definition from one table to
another back and forth.

What dependencies would there be that a view would solve? You can't
define RI on a view AFAIK...

functions, rules, other views ... RI is not the only source of dependencies.

you would do this:

< load table_a >
create or replace view v as select * from table_a;
drop if exists table table_b;

next time around swap table_a and table_b, or you could write some
plpgsql or plperl to do it nicely for you.

cheers

andrew

On Mon, 2006-05-22 at 14:25 -0400, April Lorenzen wrote:

Postgresql partioning makes this all almost possible: the master
automatically SELECTs from all the child tables - so it automatically
recoginize the new ones, and no loss when the old ones are dropped.

Just one thing would make it near perfect: if I could keep the master
from being able to SELECT from the new child table while I'm COPYing
data into the new child, building the index and vacuuming it - without
sending an error back to the user who is querying the master table.

There is not currently any way to make an existing table become a child
table of another table. I propose a TODO item to allow this:

ALTER TABLE childN INHERITS ( parent1, ... );

This would only succeed if all of the columns that would have been
inherited from all parent tables already exist with the same name and
datatype, nullability and default values. Additional columns would be
allowed in the child table.

This would also allow you to use CREATE TABLE AS SELECT and then move
that table underneath a parent.

We don't need a disinherit do we?

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

Simon Riggs wrote:

There is not currently any way to make an existing table become a child
table of another table. I propose a TODO item to allow this:

ALTER TABLE childN INHERITS ( parent1, ... );

This would only succeed if all of the columns that would have been
inherited from all parent tables already exist with the same name and
datatype, nullability and default values. Additional columns would be
allowed in the child table.

This would also allow you to use CREATE TABLE AS SELECT and then move
that table underneath a parent.

We don't need a disinherit do we?

O, yes, I think we do. I can imagine that the ability to swap a table
out of a set without deleting it could be very useful (e.g. you might
move it in as a child of an archive table). These two would add markedly
to the usefulness of inheritance as a partitioning mechanism.

cheers

andrew

table of another table. I propose a TODO item to allow this:

ALTER TABLE childN INHERITS ( parent1, ... );

We don't need a disinherit do we?

I propose: ALTER TABLE childN INHERITS ();
Thus I also think, that the list should be complete, and is not an
addition
to existing inheritance.

O, yes, I think we do. I can imagine that the ability to swap a table

Agreed. Simon, were you testing how many ppl read to the end :-)

Andreas

On Tue, 2006-05-23 at 09:49 +0200, Zeugswetter Andreas DCP SD wrote:

table of another table. I propose a TODO item to allow this:

ALTER TABLE childN INHERITS ( parent1, ... );

We don't need a disinherit do we?

I propose: ALTER TABLE childN INHERITS ();
Thus I also think, that the list should be complete, and is not an
addition
to existing inheritance.

Sounds good; an absolute rather than a relative approach. Avoids new
keywords.

Implementation is simpler too:
- check that we have all required merged attributes (if any)
- remove any inheritance that isn't on the list

If the table is already INHERITS (x) and we specify INHERITS (x) then
its a no-op that returns success.

O, yes, I think we do. I can imagine that the ability to swap a table

Agreed. Simon, were you testing how many ppl read to the end :-)

Just seeing who cared. DROP TABLE removes inheritance too, in a Keyser
Soze manner.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

Ühel kenal päeval, T, 2006-05-23 kell 10:51, kirjutas Simon Riggs:

On Tue, 2006-05-23 at 09:49 +0200, Zeugswetter Andreas DCP SD wrote:

table of another table. I propose a TODO item to allow this:

ALTER TABLE childN INHERITS ( parent1, ... );

We don't need a disinherit do we?

I propose: ALTER TABLE childN INHERITS ();
Thus I also think, that the list should be complete, and is not an
addition
to existing inheritance.

Sounds good; an absolute rather than a relative approach. Avoids new
keywords.

And also allows you move a partition from live to archive table in one
command. Brilliant :)

Implementation is simpler too:
- check that we have all required merged attributes (if any)
- remove any inheritance that isn't on the list

If the table is already INHERITS (x) and we specify INHERITS (x) then
its a no-op that returns success.

O, yes, I think we do. I can imagine that the ability to swap a table

Agreed. Simon, were you testing how many ppl read to the end :-)

Just seeing who cared. DROP TABLE removes inheritance too, in a Keyser
Soze manner.

Just fyi - I care too .

--
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com

"Zeugswetter Andreas DCP SD" <ZeugswetterA@spardat.at> writes:

We don't need a disinherit do we?

I propose: ALTER TABLE childN INHERITS ();
Thus I also think, that the list should be complete, and is not an
addition to existing inheritance.

Don't like that at all: it seems far too error-prone.

regards, tom lane

Ühel kenal päeval, T, 2006-05-23 kell 09:37, kirjutas Tom Lane:

"Zeugswetter Andreas DCP SD" <ZeugswetterA@spardat.at> writes:

We don't need a disinherit do we?

I propose: ALTER TABLE childN INHERITS ();
Thus I also think, that the list should be complete, and is not an
addition to existing inheritance.

Don't like that at all: it seems far too error-prone.

In what way ?

Do you mean that it will be easy for the user to make errors, od do yuo
think that it will be hard to implement in a robust way ?

In the first case, I'd propose following syntax

ALTER TABLE childN ALTER INHERITS DROP (parent);
ALTER TABLE childN ALTER INHERITS ADD (parent);

With this syntax reparenting would need an explicit transaction and two
"ALTER TABLE ... ALTER INHERITS ..." commands, but it is (arguably)
harder to make mistakes.

--
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com

Hannu Krosing <hannu@skype.net> writes:

Ühel kenal päeval, T, 2006-05-23 kell 09:37, kirjutas Tom Lane:

I propose: ALTER TABLE childN INHERITS ();
Thus I also think, that the list should be complete, and is not an
addition to existing inheritance.

Don't like that at all: it seems far too error-prone.

In what way ?

It seems like it'd be awfully easy to unintentionally disinherit a child
table from a parent.

In a situation where you're only using single inheritance, it hardly
matters of course, but for multiple inheritance it just seems like a
way to shoot yourself in the foot. ISTM it'd be safer to have an
explicit disinherit-from-this-parent operation.

In the first case, I'd propose following syntax

ALTER TABLE childN ALTER INHERITS DROP (parent);
ALTER TABLE childN ALTER INHERITS ADD (parent);

I could live with that. Do we need the parens?

regards, tom lane

On Tue, 2006-05-23 at 09:37 -0400, Tom Lane wrote:

"Zeugswetter Andreas DCP SD" <ZeugswetterA@spardat.at> writes:

We don't need a disinherit do we?

I propose: ALTER TABLE childN INHERITS ();
Thus I also think, that the list should be complete, and is not an
addition to existing inheritance.

Don't like that at all: it seems far too error-prone.

What types of error do you think its prone to?

Can you say what you would prefer?

As ever, not that worried about syntax, but I would like to get
agreement on a specific way forward now we're discussing this.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

ALTER TABLE childN ALTER INHERITS DROP (parent);
ALTER TABLE childN ALTER INHERITS ADD (parent);

Wouldn't it be possible to allow the ADD/DROP to happen in the same
statement, like:

ALTER TABLE childN ALTER INHERITS DROP crt_parent ADD new_parent;

or:

ALTER TABLE childN ALTER INHERITS DROP crt_parent, ALTER INHERITS ADD
new_parent;

That would still make it one statement, but more explicit. And it would
eliminate the need for parenthesis (I assume they were needed for
supplying more than 1 table to inherit/disinherit).

Cheers,
Csaba.

On Tue, 2006-05-23 at 16:29 +0200, Csaba Nagy wrote:

ALTER TABLE childN ALTER INHERITS DROP (parent);
ALTER TABLE childN ALTER INHERITS ADD (parent);

Wouldn't it be possible to allow the ADD/DROP to happen in the same
statement, like:

ALTER TABLE childN ALTER INHERITS DROP crt_parent ADD new_parent;

or:

ALTER TABLE childN ALTER INHERITS DROP crt_parent, ALTER INHERITS ADD
new_parent;

That would still make it one statement, but more explicit. And it would
eliminate the need for parenthesis (I assume they were needed for
supplying more than 1 table to inherit/disinherit).

Sounds good.

Do we need the ALTER keyword? That isn't used anywhere apart from
manipulating columns. i.e.

ALTER TABLE childN INHERITS DROP old_parent;
ALTER TABLE childN INHERITS ADD new_parent;

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

Ühel kenal päeval, T, 2006-05-23 kell 15:59, kirjutas Simon Riggs:

On Tue, 2006-05-23 at 16:29 +0200, Csaba Nagy wrote:

ALTER TABLE childN ALTER INHERITS DROP (parent);
ALTER TABLE childN ALTER INHERITS ADD (parent);

Wouldn't it be possible to allow the ADD/DROP to happen in the same
statement, like:

ALTER TABLE childN ALTER INHERITS DROP crt_parent ADD new_parent;

or:

ALTER TABLE childN ALTER INHERITS DROP crt_parent, ALTER INHERITS ADD
new_parent;

That would still make it one statement, but more explicit. And it would
eliminate the need for parenthesis (I assume they were needed for
supplying more than 1 table to inherit/disinherit).

Sounds good.

Do we need the ALTER keyword?

Probably not.

That isn't used anywhere apart from
manipulating columns. i.e.

ALTER TABLE childN INHERITS DROP old_parent;
ALTER TABLE childN INHERITS ADD new_parent;

For me "DROP INHERITS oldtable" sounds better than "INHERITS DROP
oldtable" , but it may be just me :)

--
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com

NOTICE: This communication contains privileged or other confidential
information. If you have received it in error, please advise the sender
by reply email and immediately delete the message and any attachments
without copying or disclosing the contents.

On Tue, 2006-05-23 at 18:19 +0300, Hannu Krosing wrote:

For me "DROP INHERITS oldtable" sounds better than "INHERITS DROP
oldtable" , but it may be just me :)

Agreed, so proposal is now

ALTER TABLE childN DROP INHERITS old_parent;
ALTER TABLE childN ADD INHERITS new_parent;

Going once; going twice...

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

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

Do we need the ALTER keyword? That isn't used anywhere apart from
manipulating columns. i.e.

ALTER TABLE childN INHERITS DROP old_parent;
ALTER TABLE childN INHERITS ADD new_parent;

At that point it seems like it'd read more naturally the other way
round:

ALTER TABLE childN DROP INHERITS old_parent;
ALTER TABLE childN ADD INHERITS new_parent;

although I'm not sure if this would create a parser conflict against
ADD/DROP COLUMN.

regards, tom lane

On Tue, 2006-05-23 at 11:31 -0400, Tom Lane wrote:

At that point it seems like it'd read more naturally the other way
round:

ALTER TABLE childN DROP INHERITS old_parent;
ALTER TABLE childN ADD INHERITS new_parent;

although I'm not sure if this would create a parser conflict against
ADD/DROP COLUMN.

Behaviour would be:
- If you DROP INHERITS this simply removes the link to the parent. All
existing columns, constraints etc are retained. You can DROP inheritance
on a table that is itself a parent; its children are unaffected.
- If you ADD INHERITS this will fail if it would do the equivalent of
possibly multiple ADD COLUMNs. You can ADD inheritance onto a table that
is itself a parent; its children are unaffected.
- The table data is not scanned at all for either ADD or DROP INHERITS
- You cannot ADD INHERITS if the table being added as parent is already
one of the inheritance set of the target table (i.e. no loops)
- ADD/DROP are opposites; you can use the other one to undo an action
taken in haste, error etc
- Once DROP INHERITS has committed no changes are propagated down from
parent to former child.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com