Hello,
Im running VP on my PC and the Postgres DB on a different local server. I have been able to generate the database before but now i keep running into errors.
Generating the database seems to crash the postgres server.
VP gives me the following:
Connecting Database…
Export to database failed…
org.postgresql.util.PSQLException: This connection has been closed.Generate database finish…
In my postgres log file i can see that an error occured but its not clear to me why i occurs:
2018-10-11 17:48:28.739 CEST [24982] LOG: server process (PID 30680) was terminated by signal 9: Killed
2018-10-11 17:48:28.739 CEST [24982] DETAIL: Failed process was running: SELECT NULL::text AS PKTABLE_CAT, pkn.nspname AS PKTABLE_SCHEM, pkc.relname AS PKTABLE_NAME, pka.attname AS PKCOLUMN_NAME, NULL::text AS FKTABLE_CAT, fkn.nspname AS FKTABLE_SCHEM, fkc.relname AS FKTABLE_NAME, fka.attname AS FKCOLUMN_NAME, pos.n AS KEY_SEQ, CASE con.confupdtype WHEN ‘c’ THEN 0 WHEN ‘n’ THEN 2 WHEN ‘d’ THEN 4 WHEN ‘r’ THEN 1 WHEN ‘p’ THEN 1 WHEN ‘a’ THEN 3 ELSE NULL END AS UPDATE_RULE, CASE con.confdeltype WHEN ‘c’ THEN 0 WHEN ‘n’ THEN 2 WHEN ‘d’ THEN 4 WHEN ‘r’ THEN 1 WHEN ‘p’ THEN 1 WHEN ‘a’ THEN 3 ELSE NULL END AS DELETE_RULE, con.conname AS FK_NAME, pkic.relname AS PK_NAME, CASE WHEN con.condeferrable AND con.condeferred THEN 5 WHEN con.condeferrable THEN 6 ELSE 7 END AS DEFERRABILITY FROM pg_catalog.pg_namespace pkn, pg_catalog.pg_class pkc, pg_catalog.pg_attribute pka, pg_catalog.pg_namespace fkn, pg_catalog.pg_class fkc, pg_catalog.pg_attribute fka, pg_catalog.pg_constraint con, pg_catalog.generate_series(1, 32) pos(n), pg_catalog.pg_class pkic WHERE pkn.oid = pkc.relnamespace
2018-10-11 17:48:28.739 CEST [24982] LOG: terminating any other active server processes
2018-10-11 17:48:28.740 CEST [20936] WARNING: terminating connection because of crash of another server process
2018-10-11 17:48:28.740 CEST [20936] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2018-10-11 17:48:28.740 CEST [20936] HINT: In a moment you should be able to reconnect to the database and repeat your command.
Does anyone have any experience with this?
The only other topic i could find mentioned it fixed itself, but that doesnt really help all that much
VP 15.0 (build 20180901)
Postgres 9.6 (or PostgreSQL 9.6.9 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit if you want it a bit more specific )
I’ve got to admit to some ignorance, what do you mean by “what kind of database” ? (just a ERD diagram to DB->Generate? )
Well, that’s what you get when fans roam a forum Still, it’s only step one.
If possible you might want to consider upgrading to 15.1 which has been released a few weeks ago. I’m not saying that this will fix the issue, but since it is the latest version it also contains all updates (and most optional bug fixes).
Still, for what’s it worth I tried a few connections using 15.1 with a PostgreSQL 9.5 database (running on FreeBSD) but can’t reproduce anything out of the ordinary. Crashing the entire server is definitely not normal, even if bad SQL would be generated then that should definitely not crash an entire server, at most it triggers some errors.
Could you share the ERD diagram or database you’re trying to set up? Or maybe just the SQL code which VP generated?
That might allow us to try and reproduce the issue, if possible of course.
Well, that file confirmed it (had a suspicion when I saw your username): always fun to meet a fellow Dutchie online!
So first things first: always be careful with passwords. Your database connection details get stored inside the project file and although I can’t “just” access the raw data it is a risk to keep in mind. Please note: you’re not in any direct danger or something, but could be if you use the same passwords on all site(s) you visit. Well, if it happens to correspond with the one you used for the database. I’m pretty sure you already know this, but I always mention these things just in case.
I went over the project and I think I found the cause of your problem. Your database was called v1.0 and that’s an invalid name, see also this website (PostgreSQL documentation). The colon is a reserved character and used as separator for table, column and scheme names. So if you use that as database name you’re in for some problems.
Fix that and you should be good:
In case you’re wondering why VP didn’t pick this up: it supports dozens of different database types and not every server invalidates a colon within the name. It’s pretty bizarre though that this crashed your server, and I am a little puzzled that the psql connector didn’t pick that up (VP uses open source database connectors to, well, connect. In this case the psql JDBC driver).
The name is quite dutch
That also explains why you were so quick to respond, i usually have to wait for the USA to wake up.
The password is unique and the database only locally accessable so it shouldnt be a problem, i figured it would be exported but meh
I renamed the database but the problem isnt solved unfortunately.
Ive also created a new database to test, i can create the database without problems, but when i try to update it i get the same errors, even on a clean database.
Ayups. Keep in mind though that Visual Paradigm is a Chinese company, so they’re long awake when we get up
Quite bizarre. I’ll be honest with you: I don’t think that Visual Paradigm is causing this but that you’re experiencing a symptom of something else which is wrong somewhere. Because like I said before: a database server doesn’t “just” fully crash, not even when using a bad database name or malformed SQL (or worse).
There is a way to rule a few things out though, are you familiar with pgAdmin? It’s a very useful GUI for PostgreSQL administration and it allows you to administer a database remotely.
My suggestion would be to use VP to generate the SQL for you and then use pgAdmin to actually feed it to the database server. Like so:
This is what you’d use to actually generate the SQL code, after you select this option you’ll get a screen which first pane (‘DDL’) shows the SQL it’s about to use. Copy that and paste it into pgAdmin:
Keep in mind to first use the Drop sequence (2nd section) and then the Create sequence (at the top) unless you’re creating the tables in a blank database of course.
The DROP and CREATE dont seem to cause any problems, just the ALTER part, when i run the Generate SQL from rmb->utilities and click the generate button under Alter (3rd button) i get the same error in the database log.
Somehow the “check to see the differences” function causes the error (or so it seems).
db rights shouldnt be a problem
I have been using pgadmin to add newly added tables, so no problems from pgadmin.
Thanks for the willingness to help (and sorry for being a pain)
I tested your project on Postgres 9.6.10 but cannot reproduce your problem.
I tried generate SQL and execute, modify ERD, generate under Alter and execute the alter statement without problems.
As ShelLuser said, any SQL shouldn’t cause Postgres server terminate. It seems there are problems with your Postgres installation…
Can you try on other Postgres server? Or update / reinstall your Postgres?
If you have docker, you can test with offical docker images: https://hub.docker.com/_/postgres/
Still, it’s only step one.
