Comments (25)
The PR didn't work (see Travis logs). I think two things are needed. First, your mod ought to be tested against PG 13. Did you run any of the tests locally, in particular the extensions tests? Anyway we need to change .travis.yml to add that version (we should probably remove versions 9.4 and 9.5 as they're no longer supported). And then we need to investigate the errors and fix them. They all appear to be the same error in test_create_lang_extension
in tests/dbobject/test_extension.py
.
from pyrseas.
from pyrseas.
I just got really excited about using Pyrseas but the dbtoyaml function erred immediately and I am pretty sure it is this same bug. Its hitting issues with my extensions, which I have several. I dropped one, and it just errors at the next. So yes, I think this is still a bug.
I am probably not suited to help with the python, but I can certainly help with generating pg_* queries to get the right info as per the above? If I can help in that way, give me some specs to work towards.
from pyrseas.
@alexitheodore Could you please first show the dbtoyaml
error/stack trace? Also, it would help if you could create a simple test case. You can look at pyrseas/tests/dbobject/test_extension.py
to see what we're already testing. Later I'll see if I can run those tests on PG 13.
from pyrseas.
@jmafc Sure, here are two different ones:
First error (seemingly from pg_cron extension)
Traceback (most recent call last):
File "/usr/local/bin/dbtoyaml", line 11, in <module>
sys.exit(main())
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbtoyaml.py", line 49, in main
dbmap = db.to_map()
File "/usr/local/lib/python2.7/dist-packages/pyrseas/database.py", line 488, in to_map
dbmap.update(self.db.schemas.to_map(self.db, opts))
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbobject/schema.py", line 357, in to_map
schemas.update(self[sch].to_map(db, self, opts))
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbobject/schema.py", line 104, in to_map
schobjs.append((obj, obj.to_map(db, dbschemas, opts)))
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbobject/table.py", line 556, in to_map
dct['triggers'].update(self.triggers[k.name].to_map(db))
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbobject/trigger.py", line 156, in to_map
dct = super(Trigger, self).to_map(db)
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbobject/__init__.py", line 361, in to_map
deps -= self.get_implied_deps(db)
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbobject/trigger.py", line 239, in get_implied_deps
deps.add(db.functions[fschema, fname, self.arguments or ''])
KeyError: (u'cron', 'job_cache_invalidate', '')
Then I tried dropping that and running again. So the second error looks like it is to do with JSONB - hard to tell if that is extension related or not since it is native...
Traceback (most recent call last):
File "/usr/local/bin/dbtoyaml", line 11, in <module>
sys.exit(main())
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbtoyaml.py", line 49, in main
dbmap = db.to_map()
File "/usr/local/lib/python2.7/dist-packages/pyrseas/database.py", line 488, in to_map
dbmap.update(self.db.schemas.to_map(self.db, opts))
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbobject/schema.py", line 357, in to_map
schemas.update(self[sch].to_map(db, self, opts))
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbobject/schema.py", line 140, in to_map
schobjs.append((obj, obj.to_map(db, no_owner, no_privs)))
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbobject/function.py", line 569, in to_map
dct = super(Aggregate, self).to_map(db, no_owner, no_privs)
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbobject/__init__.py", line 361, in to_map
deps -= self.get_implied_deps(db)
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbobject/function.py", line 666, in get_implied_deps
deps.add(db.functions[sch, fnc, args])
KeyError: (u'global', 'json_agg_statef', u'jsonb, jsonb')
As for pyrseas/tests/dbobject/test_extension.py
- I looked at it, but I don't quite follow Python well enough to create a test case, sorry... I'd be happy to provide SQL tests though?
from pyrseas.
Yes, I wasn't expecting you to create a test in test_extension.py
, only to get an idea of what we're already testing. You can simply describe the steps of your test case, e.g., createdb testdb
, CREATE EXTENSION xyz
, etc. Preferably, the extension used in the test should be one generally available such as the pg_trgm
we're using.
from pyrseas.
@jmafc Ok, that I can do.
So, for pg_cron, I did the most remedial test first: fresh cluster/database and adding the pg_cron extension.
Steps to recreate:
- create a fresh cluster
- Make sure pg_cron is available locally. Or if it is easier for you, just use AWS RDS, since it is loaded natively.
- Add configuration to postgresql.conf:
shared_preload_libraries= 'pg_cron'
cron.database_name='postgres'
- Make sure the config changes are loaded (i.e. restart server)
CREATE EXTENSION pg_cron;
Then you will get the error:
Traceback (most recent call last):
File "/usr/local/bin/dbtoyaml", line 11, in <module>
sys.exit(main())
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbtoyaml.py", line 49, in main
dbmap = db.to_map()
File "/usr/local/lib/python2.7/dist-packages/pyrseas/database.py", line 488, in to_map
dbmap.update(self.db.schemas.to_map(self.db, opts))
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbobject/schema.py", line 357, in to_map
schemas.update(self[sch].to_map(db, self, opts))
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbobject/schema.py", line 104, in to_map
schobjs.append((obj, obj.to_map(db, dbschemas, opts)))
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbobject/table.py", line 556, in to_map
dct['triggers'].update(self.triggers[k.name].to_map(db))
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbobject/trigger.py", line 156, in to_map
dct = super(Trigger, self).to_map(db)
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbobject/__init__.py", line 361, in to_map
deps -= self.get_implied_deps(db)
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbobject/trigger.py", line 239, in get_implied_deps
deps.add(db.functions[fschema, fname, self.arguments or ''])
KeyError: (u'cron', 'job_cache_invalidate', '')
For what it is worth, job_cache_invalidate
is a C language function. I would assume that if the source code is unavailable, it is safe to just skip or put a blank for that.
from pyrseas.
And here is another test, this time starting from a totally clean slate:
CREATE OR REPLACE FUNCTION json_agg_statef (IN json_cur_in JSONB, IN json_next_in JSONB, OUT json_cur_out JSONB) AS
$$
BEGIN
json_cur_out := json_cur_in & json_next_in;
END;
$$
LANGUAGE PLPGSQL
IMMUTABLE
PARALLEL SAFE
;
This doesn't cause any errors.
postgres@ip-172-33-2-75:~/adt_tmp$ dbtoyaml -p 5555 postgres
extension plpgsql:
description: PL/pgSQL procedural language
owner: postgres
schema: pg_catalog
version: '1.0'
schema public:
description: standard public schema
function json_agg_statef(json_cur_in jsonb, json_next_in jsonb, OUT json_cur_out jsonb):
language: plpgsql
owner: postgres
returns: jsonb
source: "\nBEGIN\n json_cur_out := json_cur_in & json_next_in;\nEND;\n"
volatility: immutable
owner: postgres
privileges:
- PUBLIC:
- all
- postgres:
- all
But when I create the corresponding aggregate function, then the error happens:
CREATE AGGREGATE json_agg_test (JSONB)
(
SFUNC = json_agg_statef,
STYPE = JSONB
)
;
Traceback (most recent call last):
File "/usr/local/bin/dbtoyaml", line 11, in <module>
sys.exit(main())
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbtoyaml.py", line 49, in main
dbmap = db.to_map()
File "/usr/local/lib/python2.7/dist-packages/pyrseas/database.py", line 488, in to_map
dbmap.update(self.db.schemas.to_map(self.db, opts))
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbobject/schema.py", line 357, in to_map
schemas.update(self[sch].to_map(db, self, opts))
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbobject/schema.py", line 140, in to_map
schobjs.append((obj, obj.to_map(db, no_owner, no_privs)))
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbobject/function.py", line 569, in to_map
dct = super(Aggregate, self).to_map(db, no_owner, no_privs)
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbobject/__init__.py", line 361, in to_map
deps -= self.get_implied_deps(db)
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbobject/function.py", line 666, in get_implied_deps
deps.add(db.functions[sch, fnc, args])
KeyError: (u'public', 'json_agg_statef', u'jsonb, jsonb')
My guess is that the aggregate function is being regarded as a normal function/procedure - which it isn't.
from pyrseas.
One last thing: please confirm that you're using the 0.9.1 version of Pyrseas. Josh Curtis (the OP) submitted a PR but it was only committed to master
(unfortunately, the PR doesn't fix the bug, but it causes a different behavior).
from pyrseas.
Confirmed.
postgres@ip-172-33-2-75:~/adt_tmp$ dbtoyaml --version
dbtoyaml 0.9.1
from pyrseas.
I've been out of the game for too long it seems. Travis-ci.org doesn't work anymore and travis-ci.com wants me to sign up for a plan and provide a bunch of info, instead of just an email. It also refuses for some strange reason to map perseas/pyrseas
as a valid repository.
Anyway, I've made a minor change to test_extensions.py
so that it can run against PG 13.
However, more importantly, the problem that you're seeing is not related to issue #226, which is a problem with the dropped pg_pltempate
catalog (which we fetch in yamltodb
), but your problem is in dbtoyaml
. I'll dig a little further tomorrow and let you know.
from pyrseas.
For the record, once I installed plperl-13
and plpython3-13
, all five test_map*
tests in test_extension.py
passed using both Python 2.7 and 3.7.
from pyrseas.
If you have access to a database running under PG earlier than 13, then it may be helpful if you ran the test against that. If you don't have access, that's OK. I'll have to download pg_cron and I'll install it on both PG 11 and 13.
from pyrseas.
FYI, I tried to install postgresql-11-cron and postgresql-13-cron from the PGDG repositories onto Debian butster, and both failed as "not found". I'm going to try the GH local build instructions.
from pyrseas.
@alexitheodore I was able to make pg_cron locally (after installing postgresql-server-{11,13}-dev, which are not mentioned as requirements). The instructions mention /usr/pgsql-12/bin
which I'm not sure where it comes from. I tried make --dry-run install
and it would try to install lots of stuff all over the place, in particular in /usr/lib/postgresl/13
which I would prefer not to do. IMHO it should it should install under /usr/local/ so it can be removed easily. I'm going to see if there's a way around that. Also I would like to install under PG11 first, because I suspect this is not a PG 13 problem, but rather something unique to pg_cron (dbtoyaml
works fine on PG 13 with pg_trgm
).
from pyrseas.
I don't have pg11 handy to test anything with.
I can only add that installation was easy for me on EC2 Ubuntu via:
sudo apt -y install postgresql-13 postgresql-client-13
sudo apt-get -y install postgresql-13-cron
Not sure if that helps - but I completely understand if you don't want to spend all day and muddy your system just to get pg_cron loaded. I also agree, its probably more unique to pg_cron than pg13.
Does pyrseas currently support C language functions?
from pyrseas.
I had forgotten to do an apt update
. Once I did that, I was able to install pg_cron
(postgresql-11-cron) and try it out. I get the same dbtoyaml
error (using Python 3, instead of 2.7).
It is trying to map the trigger cron_job_cache_invalidate
on the table cron.job
, but it cannot find the procedure cron.job_cache_invalidate
(which does exist). This is related to #209 and the general issue that we retrieve objects that are "owned" by extensions and that therefore should be excluded the map output, but we're not always able to detect that. I think we should open a separate issue for this.
If you'd like to help, you can take a look at the build_dependency_graph
queries in pyrseas/database.py
(lines 232-298). @dvarrazzo can tell you all about them (sorry Daniele :-). I also think that in the past our "standard" solution has been to add a clause such as seen in dbobject/function.py
lines 182-4, i.e., don't load functions that depend on extensions (deptype = 'e'
). That's why we're not loading cron.job_cache_invalidate
. Perhaps the solution is to add a similar clause to the dbobject/trigger.py
(starting at line 94).
The simple answer to your last question is yes, but it may depend on what you think "support" means.
from pyrseas.
If you change the WHERE
clause of the query in dbobject/trigger.py
to the following (basically adding the AND ... NOT IN (...)
:
WHERE NOT tgisinternal
AND (nspname != 'pg_catalog' AND nspname != 'information_schema')
AND t.tgfoid NOT IN (
SELECT objid FROM pg_depend WHERE deptype = 'e'
AND classid = 'pg_proc'::regclass)
then dbtoyaml
completes without error, at least on my system with PG 11. However, it does list the schema cron
with its tables job
and job_run_details
and those probably shouldn't be there either. I would suggest that you try the above change on your system, trying both your first and second test cases.
from pyrseas.
@alexitheodore Did you get a chance to test my suggestion above?
from pyrseas.
@jmafc Sorry not yet - almost got around to it last night. I will soon, just got swamped.
from pyrseas.
Opened new issue (#236) to deal with dbtoyaml
problem reported 2-Nov-21.
from pyrseas.
As posted in #236, the updated SQL fixes the pg_cron bug, though the second error persists:
from pyrseas.
The discussion from 2 to 13 Nov 2021 is not relevant to the basic issue of yamltodb
failing against PG 13. However, as discussed on 7-8 Apr, the PR submitted by @joshcurtis does not resolve the issue. It is not enough to query the pg_available_extensions
and pg_language
because that only gives the languages that are installed on the target.
If, for example (as tested in tests/dbobject/test_extension.py test_create_lang_extension
), we have to create/install a new language, e.g., plperl, to later create a new function in that language, the input YAML file has to tells not only that extension plperl
is needed, but also that language plperl
has to be present, so that when we're connecting the new function we'll find the language.
The dropped pg_pltemplate
catalog was a surrogate for having dbtoyaml
outputting language xxx
information to the YAML file(s). Since that is no longer available in PG 13, we can either reconstruct the pg_pltemplate
information, i.e., replace the ExtensionDict.from_map()
langtempls
argument by a hard-coded list of strings (output of SELECT tmplname FROM pg_pltemplate
against PG 12 or earlier), or modify dbtoyaml
to output the language info (and verify that yamltodb
processes it correctly).
For now, I'll probably submit a hard-coded solution as a temporay measure.
from pyrseas.
Hey! Just wanted to share that I am still getting the original error mentioned in this post.
...
psycopg2.errors.UndefinedTable: relation "pg_pltemplate" does not exist
LINE 1: SELECT tmplname FROM pg_pltemplate
I'm on PostgreSQL 14.2
, and yamltodb 0.9.1
. I am going to try and downgrade my PostgesSQL to 12 (to get over this issue), but wanted to share in case this is helpful. Let me know if anyone needs more info from me on this error.
Turned out pip install Pyrseas
was installing a version of Pyrseas without the latest commits. Specifically, the commit was missing. I basically used the instructions to download (using git
) and build Pyrseas from source, and that seems to have fixed that error.
from pyrseas.
@teejays pip
will not install commit 4c1a12e because it was only committed to the master branch but PyPI only has releases up to 0.9.1 (from 2020-4-14). To have pip
install the former, we'd have to do a new maintenance release. Sorry.
from pyrseas.
Related Issues (20)
- ALTER SEQUENCE OWNER comes before CREATE TABLE HOT 3
- SSL Connection to remote database HOT 3
- datacopy CSV with null trailing columns not importing with -u HOT 7
- CREATE EXTENSION statements in the change script HOT 5
- [Contribution] Docker support HOT 2
- Sequences processing doesn't skip schemas defined with '-N' option. HOT 3
- dbtoyaml fails when dealing with extension-owned triggers/functions HOT 7
- Multiline Strings (source: |2) Output Unstable HOT 5
- Enable Github Discussions HOT 2
- Fails to Install: Error: pg_config executable not found HOT 5
- Error Using dbtoyaml With Supabase Schema HOT 67
- dbtoyaml Fails When PG Password Has Spaces (Improper Escaping) HOT 1
- Convert .travis.yml to GH Actions
- Fold pgdbconn back into Pyrseas HOT 1
- Key error when converting aggregate to YAML HOT 4
- plv8 to extension and fix reserved words in the primary key HOT 5
- Add REFERENCING tables in trigger HOT 3
- Support columns GENERATED AS (x) STORED HOT 2
- Support --revert for tables HOT 4
- the yamltodb tool is not placing the names of schemas, tables and columns in double quotes HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from pyrseas.