While reviewing Fabien Coelho's patch for emitting warnings for slow
foreign-key checks, it occurred to me that we aren't covering all bases.
The patch as committed makes sure that there is a usable index on the
referenced table, but it does not look for one on the referencing table.
Failure to provide such an index will lead to slow DELETEs on the
referenced table.  And that's a mistake plenty of people make, even
without bringing datatype incompatibilities into it.

I am tempted to add some more code that issues a WARNING about slow
deletes if there's no matching index on the referencing table, or
if that index has type-compatibility problems.  (It turns out that
this is not necessarily the same check as whether the PK index has
compatibility problems.)

The main problem with doing this is that in the common case of doing
	CREATE TABLE foo (f1 int REFERENCES bar);
there will normally not be any matching index available yet.  Unless
you want a UNIQUE index, which you often wouldn't, there isn't any
way to make the required index during CREATE TABLE; you have to add
it later.  So I'm worried that adding such a warning would create
useless noise during CREATE TABLE.

A possible compromise is to issue warnings only during ALTER TABLE ADD
CONSTRAINT.  I'm not sure how useful that would really be though.

Comments, ideas?

			regards, tom lane

Sat, 13 Mar 2004 18:12:36 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Further thoughts about warning for costly FK checks
Tom Lane wrote:
> While reviewing Fabien Coelho's patch for emitting warnings for slow
> foreign-key checks, it occurred to me that we aren't covering all bases.
> The patch as committed makes sure that there is a usable index on the
> referenced table, but it does not look for one on the referencing table.
> Failure to provide such an index will lead to slow DELETEs on the
> referenced table.  And that's a mistake plenty of people make, even
> without bringing datatype incompatibilities into it.
> 
> I am tempted to add some more code that issues a WARNING about slow
> deletes if there's no matching index on the referencing table, or
> if that index has type-compatibility problems.  (It turns out that
> this is not necessarily the same check as whether the PK index has
> compatibility problems.)
> 
> The main problem with doing this is that in the common case of doing
> 	CREATE TABLE foo (f1 int REFERENCES bar);
> there will normally not be any matching index available yet.  Unless
> you want a UNIQUE index, which you often wouldn't, there isn't any
> way to make the required index during CREATE TABLE; you have to add
> it later.  So I'm worried that adding such a warning would create
> useless noise during CREATE TABLE.
> 
> A possible compromise is to issue warnings only during ALTER TABLE ADD
> CONSTRAINT.  I'm not sure how useful that would really be though.

Yes, I was worried about this too, and mentioned it in relation to the
pg_statistic bucket size discussion we had.

Agreed, there seems to be no good way to emit the warning during table
creation.

Isn't the ALTER TABLE ADD CONSTRAINT used by pg_dump?

Looking at what we have, we know every table will get some inserts, and
we know every insert will have to check the primary key.  What we don't
know is if there will be any modifications or deletes to the primary
key.  Call me crazy, but maybe we have to throw a message for primary
key lookups on foreign key tables without indexes.  I hate to throw a
message on update/delete rather than create table, but I don't see
another way.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Sat, 13 Mar 2004 18:42:24 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Further thoughts about warning for costly FK checks
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Looking at what we have, we know every table will get some inserts, and
> we know every insert will have to check the primary key.  What we don't
> know is if there will be any modifications or deletes to the primary
> key.

Yeah.  It's possible that the user has deliberately omitted the index
because he knows that PK deletions will be nonexistent, or at least so
infrequent that it's not worth maintaining an index on the FK column.
So there are use-cases for it, it's just not the normal case.  (This
is also the reason not to automatically create the supporting FK index
during ADD CONSTRAINT, which is an idea I'd toyed with briefly.)

> Call me crazy, but maybe we have to throw a message for primary
> key lookups on foreign key tables without indexes.  I hate to throw a
> message on update/delete rather than create table, but I don't see
> another way.

I don't think that will fly.  It's too noisy/repetitive, and it's
complaining at the wrong time (in production rather than when you're
setting up the DB schema).  Imagine how annoying it would be to get
such warnings if you were a user without the privileges needed to create
the requested index.

			regards, tom lane

Sat, 13 Mar 2004 19:16:15 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Further thoughts about warning for costly FK checks 
On Sunday 14 March 2004 00:16, Tom Lane wrote:

> > Call me crazy, but maybe we have to throw a message for primary
> > key lookups on foreign key tables without indexes.  I hate to throw a
> > message on update/delete rather than create table, but I don't see
> > another way.
>
> I don't think that will fly.  It's too noisy/repetitive, and it's
> complaining at the wrong time (in production rather than when you're
> setting up the DB schema).  Imagine how annoying it would be to get
> such warnings if you were a user without the privileges needed to create
> the requested index.

Maybe there should be a separate "pg_advisor" tool that you can run against a 
database and which looks for "oddities". Something pluggable so people can 
add tests simply.

-- 
  Richard Huxton
  Archonet Ltd

Sun, 14 Mar 2004 11:30:27 +0000
Richard Huxton <dev@archonet.com>
Re: Further thoughts about warning for costly FK checks
Fabien COELHO <fabien.coelho@ensmp.fr> writes:
>> Maybe there should be a separate "pg_advisor" tool that you can run against a
>> database and which looks for "oddities". Something pluggable so people can
>> add tests simply.

> Or maybe some new "ADVICE" or "ODDITY" level next to "DEBUG NOTICE WARNING
> ERROR PANIC...", that would be hidden by default and triggered by an
> option?

But that doesn't really solve the problem, which is that there are
conditions that it's difficult to test for on-the-fly while a schema
is being constructed.  A pg_advisor tool would look at the completed
schema and would not have that problem.

			regards, tom lane

Mon, 15 Mar 2004 09:39:37 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Further thoughts about warning for costly FK checks 

> > Or maybe some new "ADVICE" or "ODDITY" level next to "DEBUG NOTICE
> > WARNING ERROR PANIC...", that would be hidden by default and triggered
> > by an option?
>
> But that doesn't really solve the problem, which is that there are
> conditions that it's difficult to test for on-the-fly while a schema is
> being constructed.

Ok, I was off my shoes again, sorry.

> A pg_advisor tool would look at the completed schema and would not have
> that problem.

Ok.

As a more interesting contribution to the issue, I would suggest that such
checks could be triggered by "ANALYSE".

1/ it is the first thing to be done on performance problems.

2/ it is not done too often.

3/ checking this stuff seems to belong to the analyse phase.
   warnings about costly foreign key checks could also be repeated
   on these occasions.

Also, because of the opposition by some DBA, these checks could be disable
by some options, but I would suggest the option to be on by default.

Have a nice day,

-- 
Fabien Coelho - coelho@cri.ensmp.fr

Wed, 17 Mar 2004 10:19:57 +0100 (CET)
Fabien COELHO <coelho@cri.ensmp.fr>
Re: Further thoughts about warning for costly FK checks 
Fabien COELHO wrote:
> 
> > > Or maybe some new "ADVICE" or "ODDITY" level next to "DEBUG NOTICE
> > > WARNING ERROR PANIC...", that would be hidden by default and triggered
> > > by an option?
> >
> > But that doesn't really solve the problem, which is that there are
> > conditions that it's difficult to test for on-the-fly while a schema is
> > being constructed.
> 
> Ok, I was off my shoes again, sorry.
> 
> > A pg_advisor tool would look at the completed schema and would not have
> > that problem.
> 
> Ok.
> 
> As a more interesting contribution to the issue, I would suggest that such
> checks could be triggered by "ANALYSE".
> 
> 1/ it is the first thing to be done on performance problems.
> 
> 2/ it is not done too often.
> 
> 3/ checking this stuff seems to belong to the analyse phase.
>    warnings about costly foreign key checks could also be repeated
>    on these occasions.
> 
> Also, because of the opposition by some DBA, these checks could be disable
> by some options, but I would suggest the option to be on by default.

I was thinking of a GUC variable called PERFORMANCE_HINTS, which would
throw a message if a lookup from the primary to the foreign key didn't
have an index.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 17 Mar 2004 12:15:41 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Further thoughts about warning for costly FK checks

> > Also, because of the opposition by some DBA, these checks could be disable
> > by some options, but I would suggest the option to be on by default.
>
> I was thinking of a GUC variable called PERFORMANCE_HINTS, which would
> throw a message if a lookup from the primary to the foreign key didn't
> have an index.

As I've looked in the code abouts these things to present some patches,
there are different concepts :

 - message levels as DEBUG, NOTICE, WARNING, ERROR...

 - additional message fields as HINT, CONTEXT...

I think that this is not related to level or fields. so
"performance_hints" looks misleading to me. I would take a
"performance_advices" or "performance_checks" as these are not used yet,
and the levels may be notice/warning... and the hint field is not
necessarily used.

Well, this is just to talk;-)

-- 
Fabien Coelho - coelho@cri.ensmp.fr

Wed, 17 Mar 2004 18:29:23 +0100 (CET)
Fabien COELHO <coelho@cri.ensmp.fr>
Re: Further thoughts about warning for costly FK checks
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I was thinking of a GUC variable called PERFORMANCE_HINTS, which would
> throw a message if a lookup from the primary to the foreign key didn't
> have an index.

I like the pg_advisor idea a lot better.

In the first place, a lot of these sorts of checks don't have any clean
place to insert as a test made in-passing in regular operation.  I can't
think of a reasonable place to do the above, for example --- the only
way to do it at all would be to have the RI trigger code look at the
plan it gets back to see if it's an indexscan, which is very nonmodular,
and besides which the RI trigger couldn't really tell *why* the plan
wasn't an indexscan; it might not be for lack of an available index.

In the second place, you don't really want notices about bad schema
design popping out during regular operation --- they are at best noise
from the point of view of the applications using the database.  What you
want is something you can point at an existing database and ask for
advice.

In the third place, if we try to solve the problem by embedding checks
here and there in the backend, we'll limit ourselves to checks that can
be made with minimal impact on backend performance and complexity.  And
we'll be limiting the number of people who can contribute, because
writing backend code is hard.  An external tool would be a lot more
approachable IMHO.  The original suggestion for pg_advisor mentioned
pluggable tests, which seems like the right kind of approach to me.

BTW, something that just occurred to me now: EXPLAIN is currently really
designed only for SELECTs.  It would make sense to upgrade it for
INSERT/UPDATE/DELETE to list the triggers that will get fired.  While
we'd have to treat user triggers as black boxes, I think it would also
be possible to "look inside" RI triggers and display the plans of the
queries that will get invoked.  Not sure about the long-term usefulness
of that, because Stephan keeps threatening to rewrite the RI
implementation to not use normal queries ... but if it can be done
without too much pain it'd be worth doing.

			regards, tom lane

Wed, 17 Mar 2004 12:36:35 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Further thoughts about warning for costly FK checks 

OK with me.

---------------------------------------------------------------------------

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I was thinking of a GUC variable called PERFORMANCE_HINTS, which would
> > throw a message if a lookup from the primary to the foreign key didn't
> > have an index.
> 
> I like the pg_advisor idea a lot better.
> 
> In the first place, a lot of these sorts of checks don't have any clean
> place to insert as a test made in-passing in regular operation.  I can't
> think of a reasonable place to do the above, for example --- the only
> way to do it at all would be to have the RI trigger code look at the
> plan it gets back to see if it's an indexscan, which is very nonmodular,
> and besides which the RI trigger couldn't really tell *why* the plan
> wasn't an indexscan; it might not be for lack of an available index.
> 
> In the second place, you don't really want notices about bad schema
> design popping out during regular operation --- they are at best noise
> from the point of view of the applications using the database.  What you
> want is something you can point at an existing database and ask for
> advice.
> 
> In the third place, if we try to solve the problem by embedding checks
> here and there in the backend, we'll limit ourselves to checks that can
> be made with minimal impact on backend performance and complexity.  And
> we'll be limiting the number of people who can contribute, because
> writing backend code is hard.  An external tool would be a lot more
> approachable IMHO.  The original suggestion for pg_advisor mentioned
> pluggable tests, which seems like the right kind of approach to me.
> 
> BTW, something that just occurred to me now: EXPLAIN is currently really
> designed only for SELECTs.  It would make sense to upgrade it for
> INSERT/UPDATE/DELETE to list the triggers that will get fired.  While
> we'd have to treat user triggers as black boxes, I think it would also
> be possible to "look inside" RI triggers and display the plans of the
> queries that will get invoked.  Not sure about the long-term usefulness
> of that, because Stephan keeps threatening to rewrite the RI
> implementation to not use normal queries ... but if it can be done
> without too much pain it'd be worth doing.
> 
> 			regards, tom lane
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 17 Mar 2004 12:58:34 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Further thoughts about warning for costly FK checks
Tom Lane <tgl@sss.pgh.pa.us> writes:

> BTW, something that just occurred to me now: EXPLAIN is currently really
> designed only for SELECTs.  It would make sense to upgrade it for
> INSERT/UPDATE/DELETE to list the triggers that will get fired.  While
> we'd have to treat user triggers as black boxes, I think it would also
> be possible to "look inside" RI triggers and display the plans of the
> queries that will get invoked.  

It seems EXPLAIN ANALYZE at least ought to be able to show the complete plan
for even user triggers.

-- 
greg


17 Mar 2004 14:45:21 -0500
Greg Stark <gsstark@mit.edu>
Re: Further thoughts about warning for costly FK checks
On Wednesday 17 March 2004 17:36, Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I was thinking of a GUC variable called PERFORMANCE_HINTS, which would
> > throw a message if a lookup from the primary to the foreign key didn't
> > have an index.
>
> I like the pg_advisor idea a lot better.
>
> In the first place, a lot of these sorts of checks don't have any clean
> place to insert as a test made in-passing in regular operation.  
[snip]
> In the second place, you don't really want notices about bad schema
> design popping out during regular operation
[snip]
> In the third place, if we try to solve the problem by embedding checks
> here and there in the backend, we'll limit ourselves
[snip]

Fourthly - re-checking the entire schema when you have made changes to a 
system is a good idea.

Fifthly - this is the sort of thing that goes into the "new features" list and 
advocacy can talk about. People can write articles on it, all sorts.

Just my tuppence-worth.

-- 
  Richard Huxton
  Archonet Ltd

Wed, 17 Mar 2004 20:37:28 +0000
Richard Huxton <dev@archonet.com>
Re: Further thoughts about warning for costly FK checks

Dear Tom,

> I like the pg_advisor idea a lot better.
>
> [...]
>
> In the third place, if we try to solve the problem by embedding checks
> here and there in the backend, we'll limit ourselves to checks that can
> be made with minimal impact on backend performance and complexity.  And
> we'll be limiting the number of people who can contribute, because
> writing backend code is hard.  An external tool would be a lot more
> approachable IMHO.  The original suggestion for pg_advisor mentioned
> pluggable tests, which seems like the right kind of approach to me.

There is something I cannot visualise about the idea being discussed.

If the tool is "separate" from postgresql as first suggested, it would
mean that it should have its own interface? It would be a new command?
What about GUI such as pgadmin3 of pgaccess?

Or separate only mean that it is a "separate" function of the backend that
can be triggered by calling existing functions such as "EXPLAIN" or
"ANALYZE" or new ones such as "CHECK" or "ADVICE" or whatever.

The second idea seems more reasonnable to me, because it avoids having a
separate interface. So it means that it would be a new "module" of the
backend. It would also be possible to have both worlds, that is:

psql> CHECK DATABASE comics; or ANALYZE comics;...
WARNING: costly this, missing that

AND

shell> pg_advisor -h sablons -U calvin -d comics
WARNING: ....

If it is mainly in the backend, because pg_advisor will be easy to
implement then;-) If all the logic is outside in a pg_advisor program,
then you won't have psql/pgaccess/pgadmin3... views.

Also, I'm not sure that checks would be that easy to implement outside of
the backend. I think real the argument is not to have the stuff performed
"on the fly", but the backend seems the proper place anyway.

Anyway, from my "teacher" point a view, I think it would be great to have
some place to put warnings or advices, so even if it is outside, it is
better than nowhere;-)

Have a nice day,

-- 
Fabien Coelho - coelho@cri.ensmp.fr

Wed, 17 Mar 2004 22:09:57 +0100 (CET)
Fabien COELHO <coelho@cri.ensmp.fr>
Re: Further thoughts about warning for costly FK checks 
Fabien COELHO <coelho@cri.ensmp.fr> writes:
> There is something I cannot visualise about the idea being discussed.

What I'm imagining is a separate program that you run, and it connects
to the backend and grabs schema data much like pg_dump does.  (In fact
the pg_dump code might possibly be useful as a starting point, at least
for the data-acquisition part of it.)  Then it prints out some kind of
report.

This would of course only be able to address problems that are apparent
from static inspection of the schema.  A further extension would be to
give it a set of sample queries (perhaps grabbed from the postmaster log
output) to see if it can spot anything wrong with the queries --- here,
lack of applicable indexes would be an example of something that can
only be checked when looking at a particular query.

> If the tool is "separate" from postgresql as first suggested, it would
> mean that it should have its own interface? It would be a new command?
> What about GUI such as pgadmin3 of pgaccess?

If you want a GUI, it could be a GUI, though I'd be worried about the
portability price paid to have one.  Or are you concerned about whether
a GUI could invoke it?  I don't see why not --- the GUIs don't
reimplement pg_dump, do they?

> Or separate only mean that it is a "separate" function of the backend that
> can be triggered by calling existing functions such as "EXPLAIN" or
> "ANALYZE" or new ones such as "CHECK" or "ADVICE" or whatever.

That still leaves us in the situation where only people who are capable
of doing backend programming can help.  I hope that a standalone program
would be more understandable and could attract developers who wouldn't
touch the backend.

Also, you'd still have to invent an interface for it --- and the
interface would be constrained by the limits of the FE/BE protocol.
It would have to look like a SQL command that returns a query result,
or possibly NOTICE messages, both of which are pretty confining.

			regards, tom lane

Wed, 17 Mar 2004 16:23:26 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Further thoughts about warning for costly FK checks 

Dear Tom,

On Wed, 17 Mar 2004, Tom Lane wrote:
> If you want a GUI, it could be a GUI,

I do not want a GUI, I'm not a GUI guy;-) I was just wondering how GUI
could be adapted to deal with the tool if it is outside.

> though I'd be worried about the portability price paid to have one.  Or
> are you concerned about whether a GUI could invoke it?  I don't see why
> not --- the GUIs don't reimplement pg_dump, do they?

Yes, but pg_dump is more like a blackbox, the interface does not need
to look at the generated output and interpret it, or in a very simple
way to check whether it failed.

> > Or separate only mean that it is a "separate" function of the backend that
> > can be triggered by calling existing functions such as "EXPLAIN" or
> > "ANALYZE" or new ones such as "CHECK" or "ADVICE" or whatever.
>
> That still leaves us in the situation where only people who are capable
> of doing backend programming can help.  I hope that a standalone program
> would be more understandable and could attract developers who wouldn't
> touch the backend.

Mmm. The tool would need support functions that should already exist
in the backend, so they will be re-developed or somehow replicated.

Moreover I'm among the ones asking for advices, and I'm not that afraid of
the backend, as maybe I should be;-)

Also, I would like to get the advices simply from psql, thus an added
command (ADVISE) or even ANALYZE would be just fine.

> Also, you'd still have to invent an interface for it --- and the
> interface would be constrained by the limits of the FE/BE protocol.
> It would have to look like a SQL command that returns a query result,
> or possibly NOTICE messages, both of which are pretty confining.

I think that such tool would generate "WARNING, NOTICE", HINT, CONTEXT
just as the be does at the time, so I don't think that it is that
confining. Also, some new fields could be added to improve reports,
if they are really necessary, but I'm not even that sure that any is
needed.

Well, anyway if there is some place to put advices, that would be a good
think, even if I'm not convinced about the design;-)

Have a nice day,

-- 
Fabien Coelho - coelho@cri.ensmp.fr

Thu, 18 Mar 2004 11:18:47 +0100 (CET)
Fabien COELHO <coelho@cri.ensmp.fr>
Re: Further thoughts about warning for costly FK checks 
On Thursday 18 March 2004 10:18, Fabien COELHO wrote:
> On Wed, 17 Mar 2004, Tom Lane wrote:

> > though I'd be worried about the portability price paid to have one.  Or
> > are you concerned about whether a GUI could invoke it?  I don't see why
> > not --- the GUIs don't reimplement pg_dump, do they?

Actually Tom, I think they do (where they have an export facility). How would 
you run pg_dump on a remote machine? (well, without building an RPC 
mechanism)

> Yes, but pg_dump is more like a blackbox, the interface does not need
> to look at the generated output and interpret it, or in a very simple
> way to check whether it failed.
>
> > > Or separate only mean that it is a "separate" function of the backend
> > > that can be triggered by calling existing functions such as "EXPLAIN"
> > > or "ANALYZE" or new ones such as "CHECK" or "ADVICE" or whatever.
> >
> > That still leaves us in the situation where only people who are capable
> > of doing backend programming can help.  I hope that a standalone program
> > would be more understandable and could attract developers who wouldn't
> > touch the backend.

Well - let's look at what info we might need for the ultimate pg_advisor:
 1. schema details
 2. stats info
 3. query stats (correlated with existing stats, so we know what is causing 
table-scans)
 4. query plans
That's the same information as I need to make decisions, so it must be 
sufficient. We already have 1+2, in the system tables/information_schema and 
stats schema. The others could be generated if required.

We want two main features:
 1. Add new tests without writing C
 2. Allow a number of clients (pg_advisor/psql?/pgadmin/phppgadmin/RHdbadmin 
etc) to run the tests/process results.

So - have something like:
  Core => test domains => tests
Core is responsible for running the right tests (based on user parameters)
Test domains provide one or more views/tablefuncs that individual tests use to 
query against.
Tests consist of a query, an assertion, message and keywords to match against
Results are returned as SQL - client formats it how they like.

Anyone can add tests by inserting rows into pg_advisor_tests (or whatever). 
Most test-domains can be built using raw SQL/plpgsql (don't want to say all - 
haven't thought it through yet).

So - a simple test might be defined as:
INSERT INTO pg_advisor_tests 
(pat_id, pat_title, 
pat_description, 
pat_query, 
pat_msg)
VALUES ('NAMES0001','Mixed-case column names',
'You appear to be using mixed-case column-names. See ADVISOR-HINT #32 for why 
you need to be careful',
'SELECT schema_name,table_name,column_name FROM colname_test_domain_view
WHERE lower(column_name) <> column_name'
'Mixed-case column: %.%.%'
);

> I think that such tool would generate "WARNING, NOTICE", HINT, CONTEXT
> just as the be does at the time, so I don't think that it is that
> confining. Also, some new fields could be added to improve reports,
> if they are really necessary, but I'm not even that sure that any is
> needed.

Different levels of message sounds sensible to me, though I'm not sure what to 
call them.

-- 
  Richard Huxton
  Archonet Ltd

Thu, 18 Mar 2004 16:49:55 +0000
Richard Huxton <dev@archonet.com>
Re: Further thoughts about warning for costly FK checks
On Thu, 18 Mar 2004, Richard Huxton wrote:

> On Thursday 18 March 2004 10:18, Fabien COELHO wrote:
> > On Wed, 17 Mar 2004, Tom Lane wrote:
> 
> > > though I'd be worried about the portability price paid to have one.  Or
> > > are you concerned about whether a GUI could invoke it?  I don't see why
> > > not --- the GUIs don't reimplement pg_dump, do they?
> 
> Actually Tom, I think they do (where they have an export facility). How would 
> you run pg_dump on a remote machine? (well, without building an RPC 
> mechanism)

Can't the GUI just do "pg_dump -h remotehost ..."? Or do you mean 
something else? I think the phpPgAdmin developers recommend using pg_dump 
exactly because they don't want to reinvent that formidable wheel.

Jon

Thu, 18 Mar 2004 17:05:12 +0000 (UTC)
Jon Jensen <jon@endpoint.com>
Re: Further thoughts about warning for costly FK checks
Richard Huxton <dev@archonet.com> writes:
> How would you run pg_dump on a remote machine?

Trivially.  It's a client.

			regards, tom lane

Thu, 18 Mar 2004 12:51:03 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Further thoughts about warning for costly FK checks 
On Thursday 18 March 2004 17:51, Tom Lane wrote:
> Richard Huxton <dev@archonet.com> writes:
> > How would you run pg_dump on a remote machine?
>
> Trivially.  It's a client.

Eh? I'm assuming we're talking at cross purposes here. *I* can run it 
trivially - ssh in and run it over there, or run it on my linux box here and 
tunnel the connection through. PGadmin etc. can't rely on pg_dump existing 
(not yet - once the windows port is ready though...) and it can't run it 
remotely.

-- 
  Richard Huxton
  Archonet Ltd

Thu, 18 Mar 2004 18:32:08 +0000
Richard Huxton <dev@archonet.com>
Re: Further thoughts about warning for costly FK checks

Dear Tom,

I thought about it... how to solve the contradiction:
 - backend vs external tool?
 - new interface? new command? unix? windows?
 - compatibility with old/existing interfaces?
 - plugins, any one can contribute?
 - don't bother DBA's
 - communicate about it?


My 2 pence idea of the day (for free):


CREATE SCHEMA pg_advisor;

CREATE VIEW pg_advisor.table_without_primary_keys
AS SELECT ... FROM pg_catalog...;
COMMENT ON VIEW pg_advisor.table_without_primary_keys
IS 'hey man, it is considered better to have a primary key on tables...';

CREATE VIEW pg_advisor.costly_ri_checks
AS ...

CREATE VIEW pg_advisor.summary AS
SELECT 'missing primary key declarations', COUNT(*)
FROM pg_advisor.table_without_primary_keys
UNION
SELECT 'costly referencial integrity checks...', COUNT(*)
FROM pg_advisor.costly_ri_checks
UNION
... ;


Then:
 - it is in the backend, somehow;-)
 - easy plugin: CREATE VIEW ...;;-)
 - anyone fluent in SQL and in pg_catalog can contribute!
 - ALL existing interfaces are already compatible;-)
   I can use psql;-);-)
 - no one has to look at the views if he does not want to.
 - you can communicate about it in the new release...
 - well, we're in a relationnal database, so let us stay there;-)

Good night,

-- 
Fabien Coelho - coelho@cri.ensmp.fr

Thu, 18 Mar 2004 23:55:20 +0100 (CET)
Fabien COELHO <coelho@cri.ensmp.fr>
Re: Further thoughts about warning for costly FK checks 
>>>though I'd be worried about the portability price paid to have one.  Or
>>>are you concerned about whether a GUI could invoke it?  I don't see why
>>>not --- the GUIs don't reimplement pg_dump, do they?
> 
> Actually Tom, I think they do (where they have an export facility). How would 
> you run pg_dump on a remote machine? (well, without building an RPC 
> mechanism)

In phpPgAdmin 2.x, such a re-implementation did exist.  When we did the 
3.2 rewrite, I wrote another one just for dumping tables.  Then I had 
the much better idea of just allowing the person to specify the location 
of pg_dump on their server and now we stream raw pg_dump output back to 
the client browser.

Chris



Fri, 19 Mar 2004 09:29:28 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Further thoughts about warning for costly FK checks