Hey there everyone.

	Sorry for what seems to be a rather strange
thought but, could we change the seperator used to
distinguish 'cross-database' vs 'cross-schema' ?

	For example, i would expect the following
to work:

	CREATE OR REPLACE FUNCTION test_autohist() RETURNS trigger
	AS 'BEGIN
        	INSERT INTO history.test2 VALUES (new.field1,history.test_hist.nextval(), new.field2, new.field3, new.field4, new.creation_id, new.creation_date, new.creation_id, new.creation_date);
            RETURN ;
    	    END;' LANGUAGE 'plpgsql';
 
	CREATE TRIGGER test_autohist_trig
	AFTER INSERT OR UPDATE ON test
	FOR EACH ROW
	EXECUTE PROCEDURE test_autohist();


	However, when i try it, as far as i can tell,
the planner parses it down, finds that the schema isnt
in the current 'search_path' and thus thinks its a cross
database call.

	I understand that it may take the planner a 
while to go through all the available schema's to then
deduce that it isnt infact a schema at all and return
the fact that 'cross-database queries are currently not
implemented', therefore, in the realm of crazy idea, 
would it be possible to change the notation to reference
another db ?

	I was thinking something along the lines of '@',
but i guess any other non-important ascii character would
make sense.

	That way, the planner could decide wether or not
to attempt a schema resolution (history.table1) or database
resolution (live@table4).

	
	Please note, i am not asking for any sort of 
'make cross-database work', merely asking if some sort
of 'clarification' between cross-database and cross-schema
would be beneficial. 

	Ideas ? Comments ? 

	regards
	Stef

Thu, 12 Feb 2004 14:40:58 -0500
Stef <stef@chronozon.artofdns.com>
RFC: Query Planner making a distinction between Cross Database and
	Cross Schema ?
Ummmm.  Postgresql doesn't natively support cross database queries...

On Thu, 12 Feb 2004, Stef wrote:

> Hey there everyone.
> 
> 	Sorry for what seems to be a rather strange
> thought but, could we change the seperator used to
> distinguish 'cross-database' vs 'cross-schema' ?
> 
> 	For example, i would expect the following
> to work:
> 
> 	CREATE OR REPLACE FUNCTION test_autohist() RETURNS trigger
> 	AS 'BEGIN
>         	INSERT INTO history.test2 VALUES (new.field1,history.test_hist.nextval(), new.field2, new.field3, new.field4, new.creation_id, new.creation_date, new.creation_id, new.creation_date);
>             RETURN ;
>     	    END;' LANGUAGE 'plpgsql';
>  
> 	CREATE TRIGGER test_autohist_trig
> 	AFTER INSERT OR UPDATE ON test
> 	FOR EACH ROW
> 	EXECUTE PROCEDURE test_autohist();
> 
> 
> 	However, when i try it, as far as i can tell,
> the planner parses it down, finds that the schema isnt
> in the current 'search_path' and thus thinks its a cross
> database call.
> 
> 	I understand that it may take the planner a 
> while to go through all the available schema's to then
> deduce that it isnt infact a schema at all and return
> the fact that 'cross-database queries are currently not
> implemented', therefore, in the realm of crazy idea, 
> would it be possible to change the notation to reference
> another db ?
> 
> 	I was thinking something along the lines of '@',
> but i guess any other non-important ascii character would
> make sense.
> 
> 	That way, the planner could decide wether or not
> to attempt a schema resolution (history.table1) or database
> resolution (live@table4).
> 
> 	
> 	Please note, i am not asking for any sort of 
> 'make cross-database work', merely asking if some sort
> of 'clarification' between cross-database and cross-schema
> would be beneficial. 
> 
> 	Ideas ? Comments ? 
> 
> 	regards
> 	Stef
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
> 


Thu, 12 Feb 2004 13:42:01 -0700 (MST)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: RFC: Query Planner making a distinction between Cross
> Ummmm.  Postgresql doesn't natively support cross database queries...
> 

I know, but it does schema's, and currently, the same
notation is used to specify schema's as 'cross database'.

So the planner often reports 'cross-database not allowed'
in areas where it should at least report 'cross-schema
support is unavailable for this'

case in point, the example trigger. i would have expected
deliberate schemaname.table during an insert to work, but
instead the parser complains about cross-database.

this is why i am saying that if the parser could identify
schema vs database naming, it would help in clarification
of areas where the parser/planner seems to get 'confused'
about what the object is (schema vs database)

currently:  history.table1   <-- schema
            history2.table1  <-- database

whereas what i am saying is:
            history.table1   <-- schema
            history2@table1  <-- database


readability and consistancy is what i am driving at
here, although it would then be possible for the triggers
to be able to insert/update into schema's that are
specifically named instead of coming back with a 
'cross-database not allowed' (when i am trying
to do cross-schema :)


regards
Stef

Thu, 12 Feb 2004 15:56:30 -0500
Stef <stef@chronozon.artofdns.com>
Re: RFC: Query Planner making a distinction between Cross Database
	and Cross Schema ?
On Thu, 12 Feb 2004, Stef wrote:

> > Ummmm.  Postgresql doesn't natively support cross database queries...
> > 
> 
> I know, but it does schema's, and currently, the same
> notation is used to specify schema's as 'cross database'.
> 
> So the planner often reports 'cross-database not allowed'
> in areas where it should at least report 'cross-schema
> support is unavailable for this'
> 
> case in point, the example trigger. i would have expected
> deliberate schemaname.table during an insert to work, but
> instead the parser complains about cross-database.

I would think just changing the error message to "no schema by the name of 
suchandsuch found" would make it pretty clear.


Thu, 12 Feb 2004 13:59:30 -0700 (MST)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: RFC: Query Planner making a distinction between Cross
> > case in point, the example trigger. i would have expected
> > deliberate schemaname.table during an insert to work, but
> > instead the parser complains about cross-database.
> 
> I would think just changing the error message to "no schema by the name of 
> suchandsuch found" would make it pretty clear.
> 

indeed, the only problem being, that this is a 
-deliberately- called schema and it does exist
jst that its not in the search_path. surely the
pl/pglsql or parser should be able to search the
schemanames if i give it a -deliberate- name and
if it isnt there say 'not found' ?

so, either thats an error (improper parsing/expr
forming) in the pl/pgsql trigger code, or, well,
i dont know. 

thoughts ? comments ? barking mad ?
Stef

Thu, 12 Feb 2004 16:28:08 -0500
Stef <stef@chronozon.artofdns.com>
Re: RFC: Query Planner making a distinction between Cross Database
	and Cross Schema ?
On Thu, 12 Feb 2004, Stef wrote:

> > > case in point, the example trigger. i would have expected
> > > deliberate schemaname.table during an insert to work, but
> > > instead the parser complains about cross-database.
> > 
> > I would think just changing the error message to "no schema by the name of 
> > suchandsuch found" would make it pretty clear.
> > 
> 
> indeed, the only problem being, that this is a 
> -deliberately- called schema and it does exist
> jst that its not in the search_path. surely the
> pl/pglsql or parser should be able to search the
> schemanames if i give it a -deliberate- name and
> if it isnt there say 'not found' ?
> 
> so, either thats an error (improper parsing/expr
> forming) in the pl/pgsql trigger code, or, well,
> i dont know. 
> 
> thoughts ? comments ? barking mad ?

Hmmm.  I would think the first step would be to simply change the cross-db 
queries aren't supported to one of "schema either does not exist or is not 
in the search path".


Thu, 12 Feb 2004 16:59:01 -0700 (MST)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: RFC: Query Planner making a distinction between Cross
Stef <stef@chronozon.artofdns.com> writes:
> 	For example, i would expect the following
> to work:

> 	CREATE OR REPLACE FUNCTION test_autohist() RETURNS trigger
> 	AS 'BEGIN
>         	INSERT INTO history.test2 VALUES (new.field1,history.test_hist.nextval(), new.field2, new.field3, new.field4, new.creation_id, new.creation_date, new.creation_id, new.creation_date);

Why would you expect that to work?

The problem is with this bit:
	history.test_hist.nextval()
which is a cross-database function reference per the standard SQL syntax
for such things.  (If you were in the history database, it wouldn't be
cross-database, but would refer to the nextval() function in the local
test_hist schema.)

I am not sure what you meant here, but I cannot see any need to
introduce a nonstandard syntax to resolve it.

			regards, tom lane

Thu, 12 Feb 2004 19:11:56 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: RFC: Query Planner making a distinction between Cross Database
	and Cross Schema ?
"scott.marlowe" <scott.marlowe@ihs.com> writes:
> Hmmm.  I would think the first step would be to simply change the cross-db 
> queries aren't supported to one of "schema either does not exist or is not 
> in the search path".

AFAICT the issue is that Stef thought it was complaining about a
different name than it actually was complaining about.  I've added
code to CVS tip so that these messages will show the qualified name in
question.  The given example will now produce

ERROR:  cross-database references are not implemented: history.test_hist.nextval
CONTEXT:  PL/pgSQL function "test_autohist" line 2 at SQL statement

which perhaps will be a bit more illuminating.

			regards, tom lane

Thu, 12 Feb 2004 23:42:30 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: RFC: Query Planner making a distinction between Cross 