NAME
TABLE ADD KEY - Add primary key for use by Slony-I for a table with no
suitable key
SYNOPSIS
TABLE ADD KEY (options);
DESCRIPTION
In the Slony-I replication system, every replicated table is required
to have at least one UNIQUE constraint whose columns are declared NOT
NULL. Any primary key satisfies this requirement.
As a last resort, in versions of Slony-I prior to 2.0, this command can
be used to add such an attribute to a table that does not have a
primary key. Since this modification can have unwanted side effects, it
is strongly recommended that users add a unique and not null attribute
by other means.
If you intend to use Slony-I version 2.0, you must arrange for a more
proper primary key. Slony-I will not provide one for you, and if you
have cases of keys created via TABLE ADD KEY, you cannot expect Slony-I
to function properly.
NODE ID = ival
Node ID of the set origin where the table will be added as a set
member. (See SLONIK SET ADD TABLE(7).)
FULLY QUALIFIED NAME = ’string’
The full name of the table consisting of the schema and table
name as the SQL expression quote_ident(nspname) || ’.’ ||
quote_ident(relname) would return it.
Note
There is a limitation at present; you can create a PostgreSQL
table with no columns, as with create table real_short (); .
Slony-I will refuse to handle such a table. This isn’t presently
regarded as a serious limitation, as we can’t see there being
terribly much interest in replicating tables that contain no
application data.
Caution
TABLE ADD KEY should not be used if you can possibly avoid it.
It is emphatically not a Best Practice [“ Slony-I Best Practices
” [not available as a man page]].
The absence of a proper primary key should be a big red flag
that the database schema is broken. The right way to repair this
is to introduce a proper primary key, not to have Slony-I ‘fake’
one up.
It is not supported in log shipping [“Log Shipping - Slony-I
with Files” [not available as a man page]], and we do not intend
to add support.
This uses “schemadoctableaddkey( text )” [not available as a man page].
EXAMPLE
TABLE ADD KEY ( NODE ID = 1,
FULLY QUALIFIED NAME = ’public.history’ );
LOCKING BEHAVIOUR
On the origin node, this will take out an exclusive lock on the table
being modified for as long as it takes to:
· Alter the table, adding the column;
· Alter each row in the table, attaching the sequence value;
· Adding the new unique index to the table.
On subscriber nodes, these alterations take place on the table when it
is empty, and do not add any particular additional burden to
subscription time where the table will be locked on the subscriber
node.
If the table is large and frequently updated, by your applications,
this will impose a not-insignificant application outage for the
duration of the time it takes to modify the table on the origin node.
That is why it is recommended that this command should not be used if
you can possibly avoid it.
VERSION INFORMATION
This command was introduced in Slony-I 1.0
Warning
This command is no longer supported as of Slony-I version 2.0.
In version 2, the various ‘catalogue breakages’ done in
PostgreSQL versions prior to 8.3 are being eliminated so that
schema dumps may be taken from any node. That leaves the
‘kludgy’ columns created via TABLE ADD KEY as the only thing
that prevents SLONIK UNINSTALL NODE(7) from being comprised of
the SQL statement drop schema _ClusterName cascade;.
12 May 2010 SLONIK TABLE ADD KEY(7)