Jan Wieck wrote:
I think this is a larger argument than the one that was being discussed
above. Given a dump of objects I own, can I restore them without requiring
the fk check to be done if I alter table add constraint a foreign key? If
the answer to that is no, then the option can be put
Well, with the CREATE CONSTRAINT TRIGGER you _can_, but we already have
a consensus that we don't _want_ that. Probably we should declare it
deprecated and remove it in 7.5. And the option currently under
discussion is exactly what will cause ALTER TABLE to let you, but IMHO
that _should_ be
Christopher Kings-Lynne wrote:
Well, with the CREATE CONSTRAINT TRIGGER you _can_, but we already have
a consensus that we don't _want_ that. Probably we should declare it
deprecated and remove it in 7.5. And the option currently under
discussion is exactly what will cause ALTER TABLE to
Bruce Momjian wrote:
Christopher Kings-Lynne wrote:
Well, with the CREATE CONSTRAINT TRIGGER you _can_, but we already have
a consensus that we don't _want_ that. Probably we should declare it
deprecated and remove it in 7.5. And the option currently under
discussion is exactly what will cause
Stephan Szabo wrote:
On Tue, 30 Sep 2003, Bruce Momjian wrote:
Stephan Szabo wrote:
If we go that direction, why don't we just make a GUC variable to
disable constraint checking. Is that what this will do, or is it more
limited. I know it breaks referential integrity, but we
Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] writes:
How many folks are going to remember to do this? Why make it hard for
them? Someone is going to forget too easily. Why is this restore
taking so long? Oh, I forgot that switch. Or they put it in a login
file and forget it is
Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] writes:
I assume it would be only setable by the super-user.
That might be a good restriction too (on top of my speculation about not
allowing it in postgresql.conf). Only allow it to be SET per-session,
We don't have a way to make something
Bruce Momjian wrote:
Fact is, folks are doing it anyway by modifying pg_class. I know one
guy who did it in a transaction so he was the only one to see the
triggers disabled! The PostgreSQL cookbook page has an example too.
People are always asking how to do this. Why not just make it setable
Bruce Momjian [EMAIL PROTECTED] writes:
Tom Lane wrote:
Since there's no performance difference at pg_dump time, I can't see any
advantage to freezing your decision then.
I understand, and if everyone used pg_restore, then adding a flag to
pg_restore to do this would make sense. However,
On Tue, 30 Sep 2003, Jan Wieck wrote:
Stephan Szabo wrote:
On Tue, 30 Sep 2003, Tom Lane wrote:
I see where Stephan is coming from, but in my mind disabling consistency
checks ought to be a feature reserved to the DBA (ie superuser), who
presumably has some clue about the tradeoffs
Bruce Momjian [EMAIL PROTECTED] writes:
Tom Lane wrote:
That might be a good restriction too (on top of my speculation about not
allowing it in postgresql.conf). Only allow it to be SET per-session,
We don't have a way to make something unsetable in postgresql.conf right
now, do we?
Yeah,
Stephan Szabo wrote:
On Tue, 30 Sep 2003, Jan Wieck wrote:
Stephan Szabo wrote:
On Tue, 30 Sep 2003, Tom Lane wrote:
I see where Stephan is coming from, but in my mind disabling consistency
checks ought to be a feature reserved to the DBA (ie superuser), who
presumably has some clue about
On Tue, 30 Sep 2003, Tom Lane wrote:
I see where Stephan is coming from, but in my mind disabling consistency
checks ought to be a feature reserved to the DBA (ie superuser), who
presumably has some clue about the tradeoffs involved. I don't think
ordinary users should be able to do it. If
Stephan Szabo [EMAIL PROTECTED] writes:
As a side note, in the partial implementation I'd already done, I noticed
a potential problem if the person doing the alter table didn't have read
permissions on the pktable. I'd written it to bail and do the slow check
in that case (well actually in
On Tue, 30 Sep 2003, Tom Lane wrote:
Stephan Szabo [EMAIL PROTECTED] writes:
As a side note, in the partial implementation I'd already done, I noticed
a potential problem if the person doing the alter table didn't have read
permissions on the pktable. I'd written it to bail and do the
Bruce Momjian [EMAIL PROTECTED] writes:
Stephan Szabo wrote:
The problem I have with a super-user only solution is that it doesn't
solve the problem for restores in general.
OK. Let's explore that. What does ownership mean?
It does not normally mean the ability to bypass consistency
Stephan Szabo wrote:
On Tue, 30 Sep 2003, Tom Lane wrote:
I see where Stephan is coming from, but in my mind disabling consistency
checks ought to be a feature reserved to the DBA (ie superuser), who
presumably has some clue about the tradeoffs involved. I don't think
ordinary users should be
Christopher Kings-Lynne wrote:
You could just as easily argue that the lack of integrity testing at
data load time was equally a bug.
I think we need someway of telling postgres to suppress a foreign key
check.
The main problem is that the foreign key column is often not indexed.
So a db
So a db designer made a bloody mistake.
The problem is there's no easy way to find out what's missing.
I'd really like EXPLAIN to display all subsequent triggered queries
also, to see the full scans caused by missing indexes.
I'd sure second that!
Chris
---(end of
Andreas Pflug kirjutas E, 29.09.2003 kell 12:04:
Christopher Kings-Lynne wrote:
You could just as easily argue that the lack of integrity testing at
data load time was equally a bug.
I think we need someway of telling postgres to suppress a foreign key
check.
The main problem is
So a db designer made a bloody mistake.
The problem is there's no easy way to find out what's missing.
I'd really like EXPLAIN to display all subsequent triggered queries
also, to see the full scans caused by missing indexes.
It could probably be doable for EXPLAIN ANALYZE (by actually tracing
On Monday 29 September 2003 15:58, Christopher Kings-Lynne wrote:
So a db designer made a bloody mistake.
The problem is there's no easy way to find out what's missing.
I'd really like EXPLAIN to display all subsequent triggered queries
also, to see the full scans caused by missing indexes.
On Mon, 29 Sep 2003, Christopher Kings-Lynne wrote:
So a db designer made a bloody mistake.
The problem is there's no easy way to find out what's missing.
I'd really like EXPLAIN to display all subsequent triggered queries
also, to see the full scans caused by missing indexes.
I'd
Shridhar Daithankar kirjutas E, 29.09.2003 kell 13:34:
On Monday 29 September 2003 15:58, Christopher Kings-Lynne wrote:
So a db designer made a bloody mistake.
The problem is there's no easy way to find out what's missing.
I'd really like EXPLAIN to display all subsequent triggered queries
Nigel J. Andrews wrote:
On Mon, 29 Sep 2003, Christopher Kings-Lynne wrote:
So a db designer made a bloody mistake.
The problem is there's no easy way to find out what's missing.
I'd really like EXPLAIN to display all subsequent triggered queries
also, to see the full scans caused by missing
So a db designer made a bloody mistake.
Not necessarily. If I'm never going to update or delete from the parent table
the index would be useless. I find very few of my foreign key relationships
actually need indexes on the child table. I usually only have the unique index
on the parent
On Sun, 28 Sep 2003, Tom Lane wrote:
Stephan Szabo [EMAIL PROTECTED] writes:
On Sat, 27 Sep 2003, Tom Lane wrote:
I thought of what seems to be a better design for the check query: use
a LEFT JOIN and check for NULL in the righthand joined column.
Hmm, my initial testing showed that it
On Mon, 29 Sep 2003, Stephan Szabo wrote:
When I lowered random_page_cost to 1, I got an indexscan on fktable, but
that hadn't seemed to finish after about 2 hours (as opposed to about
30-35 minutes for the not exists and about 30 min - 1 1/2 hours for the
Small correction, I'd meant to type
Stephan Szabo [EMAIL PROTECTED] writes:
As an update, so far I still am getting better results with NOT EXISTS
than the left join.
Hm. Don't suppose you were using EXPLAIN ANALYZE so we could see what's
happening? This is clearly a planner failure, although I'm unsure if we
can expect the
On Mon, 29 Sep 2003, Tom Lane wrote:
Stephan Szabo [EMAIL PROTECTED] writes:
As an update, so far I still am getting better results with NOT EXISTS
than the left join.
Hm. Don't suppose you were using EXPLAIN ANALYZE so we could see what's
happening? This is clearly a planner failure,
Greg Stark wrote:
So a db designer made a bloody mistake.
Not necessarily. If I'm never going to update or delete from the parent table
the index would be useless. I find very few of my foreign key relationships
actually need indexes on the child table. I usually only have the unique
Stephan Szabo [EMAIL PROTECTED] writes:
Hm. Don't suppose you were using EXPLAIN ANALYZE so we could see what's
happening? This is clearly a planner failure, although I'm unsure if we
can expect the planner to get the right answer with no pg_statistic entries.
The left join one seems to
On Mon, 29 Sep 2003, Tom Lane wrote:
Stephan Szabo [EMAIL PROTECTED] writes:
Hm. Don't suppose you were using EXPLAIN ANALYZE so we could see what's
happening? This is clearly a planner failure, although I'm unsure if we
can expect the planner to get the right answer with no pg_statistic
(I think my previous attempt got aborted by a lost connection, so a
message like this may arrive twice)
On Mon, 29 Sep 2003, Tom Lane wrote:
Stephan Szabo [EMAIL PROTECTED] writes:
Hm. Don't suppose you were using EXPLAIN ANALYZE so we could see what's
happening? This is clearly a
Tom Lane wrote:
Christopher Kings-Lynne [EMAIL PROTECTED] writes:
I think we need someway of telling postgres to suppress a foreign key check.
Well, the subtext argument here is do we fix it by providing a way to
suppress the check, or do we fix it by making the check fast enough to
be tolerable?
Jan Wieck [EMAIL PROTECTED] writes:
I think I can accept it to be the choice of the DBA what to do. Pg_dump
has that kind of options already, one can choose between COPY and INSERT
for example. Why not adding the choice of dumping FKeys as ALTER TABLE
or CREATE CONSTRAINT TRIGGER?
We don't
Tom Lane wrote:
Jan Wieck [EMAIL PROTECTED] writes:
I think I can accept it to be the choice of the DBA what to do. Pg_dump
has that kind of options already, one can choose between COPY and INSERT
for example. Why not adding the choice of dumping FKeys as ALTER TABLE
or CREATE CONSTRAINT
Correct me if I am wrong but I remember postgresql throwing error that foreign
key field was not unique in foreign table. Obviously it can not detect that
without an index. Either primary key or unique constraint would need an
index.
What am I missing here?
IOW, how do I exactly create
Jan Wieck wrote:
Tom Lane wrote:
Jan Wieck [EMAIL PROTECTED] writes:
I think I can accept it to be the choice of the DBA what to do. Pg_dump
has that kind of options already, one can choose between COPY and INSERT
for example. Why not adding the choice of dumping FKeys as ALTER
Bruce Momjian [EMAIL PROTECTED] writes:
Jan Wieck wrote:
Tom Lane wrote:
if we were to go that route would be a boolean GUC variable that simply
prevents ALTER TABLE ADD FOREIGN KEY from doing the validity checks.
Okay too. And this would be simple and safe enough to add it at the time
On Tue, 30 Sep 2003, Bruce Momjian wrote:
Jan Wieck wrote:
Tom Lane wrote:
Jan Wieck [EMAIL PROTECTED] writes:
I think I can accept it to be the choice of the DBA what to do. Pg_dump
has that kind of options already, one can choose between COPY and INSERT
for example. Why not
Tom Lane wrote:
If we go that direction, why don't we just make a GUC variable to
disable constraint checking.
You mean in general, even for plain old insert/update/delete changes?
Yipes. What happened to ACID compliance?
What I actually expected to ensue was a discussion about how we
Stephan Szabo wrote:
If we go that direction, why don't we just make a GUC variable to
disable constraint checking. Is that what this will do, or is it more
limited. I know it breaks referential integrity, but we have had many
folks as for it, it is on the TODO list, and there are tons
Bruce Momjian [EMAIL PROTECTED] writes:
How many folks are going to remember to do this? Why make it hard for
them? Someone is going to forget too easily. Why is this restore
taking so long? Oh, I forgot that switch. Or they put it in a login
file and forget it is set. Seems safer for it
Bruce Momjian [EMAIL PROTECTED] writes:
I assume it would be only setable by the super-user.
That might be a good restriction too (on top of my speculation about not
allowing it in postgresql.conf). Only allow it to be SET per-session,
and only by a superuser.
regards,
On Tue, 30 Sep 2003, Bruce Momjian wrote:
Stephan Szabo wrote:
If we go that direction, why don't we just make a GUC variable to
disable constraint checking. Is that what this will do, or is it more
limited. I know it breaks referential integrity, but we have had many
folks as for
Stephan Szabo [EMAIL PROTECTED] writes:
On Sat, 27 Sep 2003, Tom Lane wrote:
I thought of what seems to be a better design for the check query: use
a LEFT JOIN and check for NULL in the righthand joined column.
Hmm, my initial testing showed that it really was a little slower
than a more
On Sun, 28 Sep 2003, Bruce Momjian wrote:
Stephan Szabo wrote:
Hmm, my initial testing showed that it really was a little slower
than a more complicated one with NOT EXISTS so I'd abandoned it. How does
it fare for you compared to:
select f1, f2 from fk where not exists (select 1 from
Bruce Momjian [EMAIL PROTECTED] writes:
Tom Lane wrote:
Well, we haven't even *got* a proposed patch yet, but yeah we should
tread carefully.
OK. What releases had this slow restore problem?
We introduced it in 7.3 --- before that, FKs were simply dumped as
create trigger commands, and
Bruce Momjian [EMAIL PROTECTED] writes:
Let's have multiple people eyeball the patch and give it an OK and we
can add it for 7.4 if people want it.
Well, we haven't even *got* a proposed patch yet, but yeah we should
tread carefully. I do think it'd be okay to apply a patch if we can
come up
Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] writes:
Let's have multiple people eyeball the patch and give it an OK and we
can add it for 7.4 if people want it.
Well, we haven't even *got* a proposed patch yet, but yeah we should
tread carefully. I do think it'd be okay to apply a
Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] writes:
Tom Lane wrote:
Well, we haven't even *got* a proposed patch yet, but yeah we should
tread carefully.
OK. What releases had this slow restore problem?
We introduced it in 7.3 --- before that, FKs were simply dumped as
create
Stephan Szabo wrote:
Hmm, my initial testing showed that it really was a little slower
than a more complicated one with NOT EXISTS so I'd abandoned it. How does
it fare for you compared to:
select f1, f2 from fk where not exists (select 1 from pk where pk.f1=fk.f1
and pk.f2=pk.f2) where fk.f1
Tom Lane wrote:
I've actually got code (that no longer cleanly applies, but...) that uses
the single query version with NOT EXISTS (which could be easily changed to
either of the other forms) and was planning to put it together for a patch
when 7.5 devel started because I figured it wasn't
On Sun, 28 Sep 2003, Tom Lane wrote:
Stephan Szabo [EMAIL PROTECTED] writes:
On Sat, 27 Sep 2003, Tom Lane wrote:
I thought of what seems to be a better design for the check query: use
a LEFT JOIN and check for NULL in the righthand joined column.
Hmm, my initial testing showed that it
On Sat, 27 Sep 2003, Tom Lane wrote:
[ continuing a discussion from mid-August ]
Stephan Szabo [EMAIL PROTECTED] writes:
I assume what you have in mind is to replace
validateForeignKeyConstraint() with something that does a join of the
two tables via an SPI command.
It'd probably be:
You could just as easily argue that the lack of integrity testing at
data load time was equally a bug.
I think we need someway of telling postgres to suppress a foreign key check.
The main problem is that the foreign key column is often not indexed.
Chris
Bruce Momjian wrote:
Tom Lane wrote:
Christopher Kings-Lynne wrote:
You could just as easily argue that the lack of integrity testing at
data load time was equally a bug.
I think we need someway of telling postgres to suppress a foreign key check.
The main problem is that the foreign key column is often not indexed.
As I
Christopher Kings-Lynne [EMAIL PROTECTED] writes:
I think we need someway of telling postgres to suppress a foreign key check.
Well, the subtext argument here is do we fix it by providing a way to
suppress the check, or do we fix it by making the check fast enough to
be tolerable?
I think the
I think the advantages of choice (b) are obvious --- it doesn't allow
bogus data to be loaded accidentally, and it doesn't create a problem
with loading existing 7.3 dump files that don't know how to suppress the
check.
OK, I didn't realise there was a (b). I volunteer to do speed tests on
data
[ continuing a discussion from mid-August ]
Stephan Szabo [EMAIL PROTECTED] writes:
I assume what you have in mind is to replace
validateForeignKeyConstraint() with something that does a join of the
two tables via an SPI command.
It'd probably be:
MATCH unspecified:
SELECT keycolumns FROM
Stephan Szabo [EMAIL PROTECTED] writes:
On Fri, 15 Aug 2003, Christopher Kings-Lynne wrote:
I can also attest to the horrendously long time it takes to restore the ADD
FOREIGN KEY section...
That really needs to be rewritten to do a single check over the table
rather than running the
On Fri, 15 Aug 2003, Tom Lane wrote:
Stephan Szabo [EMAIL PROTECTED] writes:
On Fri, 15 Aug 2003, Christopher Kings-Lynne wrote:
I can also attest to the horrendously long time it takes to restore the ADD
FOREIGN KEY section...
That really needs to be rewritten to do a single check
63 matches
Mail list logo
