RFC: Query Planner making a distinction between Cross Database and Cross Schema ?
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
Ummmm. Postgresql doesn't natively support cross database queries...
On Thu, 12 Feb 2004, Stef wrote:
Show quoted text
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
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
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.
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
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".
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
"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