Is there a way to add a foreign key constraint without having to wait for it
to check the consistency of all existing records? If a database is being
reloaded (pg_dumpall then load), it really shouldn't be necessary to check
the referential integrity - or at least I should be able to stipulate tha
Wes <[EMAIL PROTECTED]> writes:
> My database reload is currently taking about 6 hours to load the data, 42
> hours to reindex, and about another 40 hours or so to check the foreign key
> constraints (about 1.2 billion rows).
What PG version is this, and what have you got maintenance_work_mem set
Wes wrote:
Is there a way to add a foreign key constraint without having to wait for it
to check the consistency of all existing records? If a database is being
reloaded (pg_dumpall then load), it really shouldn't be necessary to check
the referential integrity - or at least I should be able to
Wes wrote:
You could create the fk-constraints _first_, then disable them, load
the data, reindex, and reenable them afterwards.
pg_dump/pg_restore can enable and disable fk-constraints before restoring
the data, I believe. It does so by tweaking the system catalogs.
Are referring to '--disabl
Florian,
Are you certain:
"You can only create an FK if the fields you are referencing in the
foreign table form a PK there. And creating a PK implicitly creates an
index, which you can't drop without dropping the PK :-("
I'm not sure I am convinced the necessity of a foreign key, "need
louis gonzales wrote:
Florian,
Are you certain:
"You can only create an FK if the fields you are referencing in the
foreign table form a PK there. And creating a PK implicitly creates an
index, which you can't drop without dropping the PK :-("
Arg.. Should have written "unique index" instea
Florian,
I understand where you're coming from. Indexes are always unique and
all RDBMS systems use them to 'uniquely' identify a row from the the
perspective of internal software management. Index != PrimaryKey, so
every table created, despite any Primary/Foreign key contraints put on
them,
Florian,
So if you:
create table test (
id varchar(2) primary key,
age int );
create table test2 (
id varchar(2) primary key,
age2 int );
alter table test2 add foreign key (id) references test (id);
\d test2
you'll see that attribute "id" from test2, now has both a primary key
constraint an
louis gonzales wrote:
Florian,
I understand where you're coming from. Indexes are always unique and
all RDBMS systems use them to 'uniquely' identify a row from the the
perspective of internal software management.
Surely there are non-unique indices - meaning indices for which there
are more
On Tue, Jun 20, 2006 at 00:49:21 -0400,
louis gonzales <[EMAIL PROTECTED]> wrote:
> Florian,
> I understand where you're coming from. Indexes are always unique and
> all RDBMS systems use them to 'uniquely' identify a row from the the
> perspective of internal software management. Index != Pr
Wes wrote:
On 6/20/06 5:07 AM, "Florian G. Pflug" <[EMAIL PROTECTED]> wrote:
My suggestion was to create the fk _before_ loading the data, and disable it
similarly to what "--disable-triggers" doest. It turned out, however, that a
FK always depends on a unique index (be it a primary key, or not
On 6/18/2006 11:41 PM, Wes wrote:
Is there a way to add a foreign key constraint without having to wait for it
to check the consistency of all existing records? If a database is being
reloaded (pg_dumpall then load), it really shouldn't be necessary to check
the referential integrity - or at le
12 matches
Mail list logo