In PostgreSQL it's possible to override built-in functions by moving <code class="notr

<a class="user-mention notranslate" data-hovercard-type="user" data-hovercard-url="/us

The code that needs to be fixed is: <div c

Hi <a class="user-mention notranslate" data-hovercard-type="user" data-hovercard-url="

sql: cannot override built-in functions using search_path about cockroach HOT 4 OPEN

michae2 commented on June 26, 2024

sql: cannot override built-in functions using search_path

from cockroach.

Comments (4)

rafiss commented on June 26, 2024 1

@Vidyap23 sure, feel free to submit a PR. Review our contribution guidelines here: https://cockroachlabs.atlassian.net/wiki/spaces/CRDB/pages/73072641/What+is+a+Good+CockroachDB+PR

I recommend fixing this by modifying the MergeWith function so that it accepts a SearchPath parameter.

cockroach/pkg/sql/sem/tree/function_definition.go

Lines 236 to 238 in 41cb974

 func (fd *ResolvedFunctionDefinition) MergeWith( 

 another *ResolvedFunctionDefinition, 

 ) (*ResolvedFunctionDefinition, error) {

When it combines the two lists, it should make sure they are ordered by the order the schemas appear when using SearchPath.Iter().

To test this, you can use the example that @michae2 wrote above. You can add that test to pkg/sql/logictest/testdata/logic_test/udf.

from cockroach.

rafiss commented on June 26, 2024

I haven't looked into what the code is doing right now, but the thing we want it to use when resolving the name is (sessiondata.SearchPath).Iter():

cockroach/pkg/sql/sessiondata/search_path.go

Lines 133 to 159 in d4957b3

 // Iter returns an iterator through the search path. We must include the 

 // implicit pg_catalog and temporary schema at the beginning of the search path, 

 // unless they have been explicitly set later by the user. 

 // We also include pg_extension in the path, as this normally be used in place 

 // of the public schema. This should be read before "public" is read. 

 // "The system catalog schema, pg_catalog, is always searched, whether it is 

 // mentioned in the path or not. If it is mentioned in the path then it will be 

 // searched in the specified order. If pg_catalog is not in the path then it 

 // will be searched before searching any of the path items." 

 // "Likewise, the current session's temporary-table schema, pg_temp_nnn, is 

 // always searched if it exists. It can be explicitly listed in the path by 

 // using the alias pg_temp. If it is not listed in the path then it is searched 

 // first (even before pg_catalog)." 

 // - https://www.postgresql.org/docs/9.1/static/runtime-config-client.html 

 func (s SearchPath) Iter() SearchPathIter { 

 implicitPgTempSchema := !s.containsPgTempSchema && s.tempSchemaName != "" 

 sp := SearchPathIter{ 

 paths: s.paths, 

 implicitPgCatalog: !s.containsPgCatalog, 

 implicitPgExtension: !s.containsPgExtension, 

 implicitPgTempSchema: implicitPgTempSchema, 

 tempSchemaName: s.tempSchemaName, 

 userSchemaName: s.userSchemaName, 

 duplicatedIndexes: s.duplicatedIndexes, 

 } 

 return sp 

 }

from cockroach.

rafiss commented on June 26, 2024

The code that needs to be fixed is:

cockroach/pkg/sql/schema_resolver.go

Lines 455 to 467 in 45771c6

 // Get builtin and udf functions if there is any match. 

 builtinDef, err := tree.GetBuiltinFuncDefinition(fn, path) 

 if err != nil { 

 return nil, err 

 } 

 udfDef, err := maybeLookUpUDF(ctx, sr, path, fn) 

 if err != nil { 

 return nil, err 

 } 

 switch { 

 case builtinDef != nil && udfDef != nil: 

 return builtinDef.MergeWith(udfDef)

as well as possibly other implementations of ResolveFunction. I think the MergeWith function should be updated so that it orders the overloads by search path.

from cockroach.

Vidyap23 commented on June 26, 2024

Hi @rafiss I would like to work on this issue, if no one has picked it up yet

from cockroach.

sql: cannot override built-in functions using search_path about cockroach HOT 4 OPEN

Comments (4)

Related Issues (20)

Recommend Projects

React

Vue.js

Typescript

TensorFlow

Django

Laravel

D3

Recommend Topics

javascript

web

server

Machine learning

Visualization

Game

Recommend Org

Facebook

Microsoft

Google

Alibaba

D3

Tencent

	func (fd *ResolvedFunctionDefinition) MergeWith(
	another *ResolvedFunctionDefinition,
	) (*ResolvedFunctionDefinition, error) {

	// Iter returns an iterator through the search path. We must include the
	// implicit pg_catalog and temporary schema at the beginning of the search path,
	// unless they have been explicitly set later by the user.
	// We also include pg_extension in the path, as this normally be used in place
	// of the public schema. This should be read before "public" is read.
	// "The system catalog schema, pg_catalog, is always searched, whether it is
	// mentioned in the path or not. If it is mentioned in the path then it will be
	// searched in the specified order. If pg_catalog is not in the path then it
	// will be searched before searching any of the path items."
	// "Likewise, the current session's temporary-table schema, pg_temp_nnn, is
	// always searched if it exists. It can be explicitly listed in the path by
	// using the alias pg_temp. If it is not listed in the path then it is searched
	// first (even before pg_catalog)."
	// - https://www.postgresql.org/docs/9.1/static/runtime-config-client.html
	func (s SearchPath) Iter() SearchPathIter {
	implicitPgTempSchema := !s.containsPgTempSchema && s.tempSchemaName != ""
	sp := SearchPathIter{
	paths: s.paths,
	implicitPgCatalog: !s.containsPgCatalog,
	implicitPgExtension: !s.containsPgExtension,
	implicitPgTempSchema: implicitPgTempSchema,
	tempSchemaName: s.tempSchemaName,
	userSchemaName: s.userSchemaName,
	duplicatedIndexes: s.duplicatedIndexes,
	}
	return sp
	}

	// Get builtin and udf functions if there is any match.
	builtinDef, err := tree.GetBuiltinFuncDefinition(fn, path)
	if err != nil {
	return nil, err
	}
	udfDef, err := maybeLookUpUDF(ctx, sr, path, fn)
	if err != nil {
	return nil, err
	}

	switch {
	case builtinDef != nil && udfDef != nil:
	return builtinDef.MergeWith(udfDef)