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 wil
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 ALT
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 r
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
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 abl
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
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
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 i
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.
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
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?
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
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. How
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
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 so
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 forg
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 integr
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
> > > f
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
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
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
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
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
>
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
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 F
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 foreig
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 TR
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
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
(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
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 p
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 seem
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 f
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 i
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 f
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, 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 ty
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 sh
> > > 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 tab
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
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 trigg
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.
>
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
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
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
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 broad
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 desi
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
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 t
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
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:
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.
>
> > I
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 sh
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
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 f
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 dump
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 ap
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 u
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,
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
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
[ 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 keyco
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 sin
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
64 matches
Mail list logo
