errors on functions on branches r0.7 and master

[dvarrazzo]

master and 0.7 maint branches are broken on any function created with plpythonu.

That's caused by 3b99487 which added a filter:

AND lanname NOT IN (SELECT tmplname FROM pg_pltemplate)

causing plpythonu to be skipped.

The following database is enough to trigger the error:

create language plpythonu;
create function nothing() returns int as 'return 1' language plpythonu;

Meta-problem: when I've found the error I wanted to add a test for it so I started digging inside the test suite. What I haven't discovered to date:

• how do I run just a test module? "python setup.py test -m ... doesn't seem to do nothing
• where should I add the above test?
• is there any test that use a real database?

The answers above are probably generic and worth a post to the ML.

[dvarrazzo]

Added a test in a branch. That was mostly to recall how to make tests here.

By the way I've answered some of my above questions by looking at my own commits a few years ago :)

[jmafc]

I chose to make the approach in Database._link_refs symmetrical to the one used previously in Database.diff_map rather than mess with other catalog queries.

[dvarrazzo]

As explained in 692ef0c comment, this bug is not closed, please re-open it and assign it to dbtoyaml.

[dvarrazzo]

This is a test suite patch showing the problem. The first test fails. The second pass but is there to check regressions.

diff --git a/tests/dbobject/test_function.py b/tests/dbobject/test_function.py
index 3f068f8..bfef88d 100644
--- a/tests/dbobject/test_function.py
+++ b/tests/dbobject/test_function.py
@@ -131,8 +131,20 @@ class FunctionToMapTestCase(DatabaseToMapTestCase):
             self.to_map(["CREATE OR REPLACE LANGUAGE plpythonu"])
         except psycopg2.OperationalError as e:
             self.skipTest("plpython installation failed: %s" % e)
-        self.to_map(["CREATE FUNCTION test103() RETURNS int AS "
+        m = self.to_map(["CREATE FUNCTION test103() RETURNS int AS "
                      "'return 1' LANGUAGE plpythonu"])
+        self.to_map(["DROP LANGUAGE plpythonu CASCADE"])
+        assert 'language plpythonu' in m
+
+    def test_bug_103_extension(self):
+        "Test a function created with language other than plpgsql or plperl"
+        try:
+            self.to_map(["CREATE EXTENSION plpythonu"])
+        except psycopg2.OperationalError as e:
+            self.skipTest("plpython installation failed: %s" % e)
+        m = self.to_map(["CREATE FUNCTION test103() RETURNS int AS "
+                     "'return 1' LANGUAGE plpythonu"])
+        assert 'extension plpythonu' in m


 class FunctionToSqlTestCase(InputMapToSqlTestCase):

[jmafc]

@dvarrazzo Hello Daniele, I'm getting a failure on test test_map_language_bug_103 againg PG13 because the catalog pg_pltemplate has been removed (see also #226). The error is on the DROP LANGUAGE plpython3u CASCADE command and says:

DependentObjectsStillExist: cannot drop language plpython3u because extension plpython3u requires it
HINT:  You can drop extension plpython3u instead.

The PG 13 docs say "The form of CREATE LANGUAGE that does not supply any handler function is obsolete. For backwards compatibility with old dump files, it is interpreted as CREATE EXTENSION." So in essence, the test function is no longer about language creation but about extensions.
An easy way out for now would be to skip the test if on PG >= 13, but I would appreciate feedback before doing that.

[dvarrazzo]

Hello @jmafc

from the sound of it it seems that the test is not valid on PG>=13 indeed and I would skip it.

I thought that before PG13 languages couldn't be into extensions, but that's not the case, I see the usual stanza

              AND l.oid NOT IN (
                  SELECT objid FROM pg_depend WHERE deptype = 'e'
                               AND classid = 'pg_language'::regclass)

in langauge.py, meaning that we did consider the relation between extensions and languages. I understand now the concept of a language without an extension has disappeared, maybe?

For a more comprehensive solution of several problems that I remember Pyrseas had, I think the program should stop special-casing the objects living in extensions. Right now all objects queries skip objects belonging to an extension using a condition similar to the above sample, but this leaves holes when you try to rebuild puzzles. I think all those objects should be included and be part of the dependencies tracking. Only on output, you can consider if they are part of an extension and so don't try to create/delete them but rather manage just the extension. ISTM that handling cases like this would just derive naturally from the new behaviour (a function would depend on the language, but the statement run would be a CREATE EXTENSION).