create db PostgreSQL - create newId() function not allowed in Amazon AWS cloud

Hello,

my name is Patrick. I recently discovered your platform and feel pretty impressed about it.

I did a little research project that i would like to host on amazon aws. In my first steps, i created a amazon postgres rds instance. when running my local cuba app against this db, i get an error while creating the database:

Executing SQL script: /home/patrick/examples/cuba/test/modules/core/build/db/10-cuba/init/postgres/create-db.sql
Failed to execute: --------------------------------------------------------------------------------------------------------------
create or replace function newid()
returns uuid
as '$libdir/uuid-ossp', 'uuid_generate_v1'
volatile strict language c because: ERROR: permission denied for language c
:app-core:createDb FAILED
FAILURE: Build failed with an exception.

When googleing for this error, i encountered this stack overflow question, that is quite similar: http://dba.stackexchange.com/questions/78768/postgresql-9-3-amazon-rds-gives-error-while-using-uuid-ossp-extension-for-genera

It seems, that the function that you guys defined loads a c extension, right? im not 100% sure, but it seems, that thins kind of a thing if not allowed in amazon rds.

Nevertheless, the extension ‘uuid-ossp’ is avaliable at the server:


sql> SELECT * FROM pg_available_extensions WHERE name = 'uuid-ossp'
[2015-07-20 16:11:56] 1 row(s) retrieved starting from 1 in 112ms (232ms total)

Im not really a db expert, but would it be possible to “use” the already installed postgres extension?

Another question would be, if a have already tried to install cuba in a cloudy enviornment. Are there any restrictions that would not work out of the box with a PaaS cloud? If i get the db running, i would first go the IaaS track of EC2 before trying another layer of abstraction with something like “amazon elastic beanstalk”.

Have a nice day!

Hi Patrick,

Thank you for pointing out the problem.

Actually, the newid() function is auxiliary and is used sometimes only in SQL scripts to help writing inserts for entities with UUID identifiers.

Anyway, the problem should be fixed. Perhaps you are creating the database by executing scripts on the server startup as explained here (correct me if I’m wrong). Then edit the tomcat\webapps\app-core\WEB-INF\db\10-cuba\init\postgres\create-db.sql file and replace

create or replace function newid()
returns uuid
as '$libdir/uuid-ossp', 'uuid_generate_v1'
volatile strict language c^

with the following:

create extension "uuid-ossp"^

create or replace function newid() returns uuid
as 'select uuid_generate_v1();'
language sql^

Then re-create the database (to ensure it is empty), and restart Tomcat.

So the solution is to load an existing extension - as you suggested. We’ll fix the scripts in the next platform version (5.6).

Are there any restrictions that would not work out of the box with a PaaS cloud?

CUBA-based applications work on Amazon EC2 and Microsoft Azure without any problems. As for “higher level” PaaS, we have no experience yet. What I can say for certain, the application needs full access to one “home” directory of the file system. You can follow this guide to create WAR files and home content, then try to deploy them to your PaaS.

Please share your thoughts and experience - this is very interesting!

ok, so this worked quite well - thank you.
With that sql script, the db migration on amazon rds worked (with the cuba studio based version of the tomcat).
Probably it might be a little off topic, but what i’m trying to do now, is to create a docker image with the tomcat & war inside of it. I’m using docker and not directly trying the PaaS road, because as reading the docs a little further, i noticed that installing openoffice is required when using the report engine with pdf (which i probably want). So this seems to be a show stopper in a Platform like “elastic beanstalk”.
Nevertheless:
After going through the guide you mentioned another problem occurs:
After doing a “./gradlew createWarDistr” that created the war directory under “build”. I renamed the folder “${app.home}” to “cuba_home”. When coping the content in my docker image like this (the Dockerfile):

FROM tomcat  
ADD tomcat-users.xml /usr/local/tomcat/conf/  
ADD app.war /usr/local/tomcat/webapps/  
ADD app-core.war /usr/local/tomcat/webapps/  
ADD cuba_home /opt/cuba_home  
ENV app.home /opt/cuba_home  
ADD context.xml /usr/local/tomcat/conf/  
ADD [url=https://jdbc.postgresql.org/download/postgresql-9.3-1101.jdbc41.jar]https://jdbc.postgresql.org/download/postgresql-9.3-1101.jdbc41.jar[/url] /usr/local/tomcat/lib/postgresql.jar  




On Tomcat startup it says, a FileNotFoundException occurs on persistence.xml (see attached file). When i look at the cuba_home directory, there is only the “db” directory, but not the “app-core” directory that seems to be required. After comparing it to “build/tomcat/work” it seems that exaclty this directory (with the persistence.xml in it) is missing, since under work it is avaliable. When copying this directory manually to the cuba_home dir, tomcat can start correctly.
Does anyone have a glue, why the dir is missing? by the way, when looking into the “app.war” under “web-inf/lib/app-global-0.1.SNAPSHOT/” there is a persistence.xml.
Thanks for your help.

tomcat-log.txt (4.8K)

Hi Patrick,

The persistence.xml file in the app_home/app-core/work directory is created dynamically at the server startup. It differs from the persistence.xml in your project sources and JAR in that it contains all entities from all based projects plus your project entities. This is the persistence configuration actually used by ORM. In your deployment, the server is probably cannot write this file for some reason, and consequently ORM cannot find it later. Please look at the logs, maybe there are earlier exceptions besides that you post here. Look also for the message “Creating file …” from PersistenceConfigProcessor class (see com.haulmont.cuba.core.sys.persistence.PersistenceConfigProcessor#create).

In general, all contents of app_home//work directories is created dynamically, and you see it in the standard Tomcat deployment because you have run it. The directories layout is slightly different on standard Tomcat: work/, but their usage is the same.

If you have further questions, please start a separate topic. We are very interested in your deployment experiments, so don’t hesitate to contact us.

hi,
the problem probably has been a different one. I think the Dollar sign has been the problem. Probably there was a problem with Docker here, because it seems to be parsed by docker at build time as well.
The way i got around it, is just to remove the dynamic part of it all together. In the “build.gradle” i replaced “${app.home}” with a predefined absolute path like “/opt/cuba_home”. This in general would be a potential problem, but in case of docker, when the docker image (with the OS within it) becomes the main deployment artifact, this folder is just an implementation detail of the binary - so it works for me pretty well in this scenario.
Bye

1 Like