#45 Trouble switching to PostgreSQL

[Aseem]

Hi Chris,

To summarize the changes I made:

1) included 'postgresql-8.3-603.jdbc4.jar' in the build/jars directory

2) created a database zocalo under ownership of user and pwd 'zocalo'

3) zocalo.conf now has:
database.url.file: zocalo
database.url.base: jdbc:postgresql://localhost/

4) hibernate.properties now has:
hibernate.dialect org.hibernate.dialect.PostgreSQLDialect
hibernate.connection.driver_class org.postgresql.Driver
hibernate.connection.username zocalo
hibernate.connection.password zocalo

(Uncommented the above, and commented the two uncommented lines (dialect and driver_class) under the HypersonicSQL section)

-With just the above changes, running zocalo.sh gave a whole bunch of generic errors none of which provided any conclusive information.

5) I'm sure the properties would be read off the properties file, I added the following under the 'initializeSessionFactory' method in HibernateUtil.java anyway:

cfg.setProperty("hibernate.connection.username", "zocalo"); cfg.setProperty("hibernate.connection.password", "zocalo");
cfg.setProperty("dialect", "org.hibernate.dialect.PostgreSQLDialect"); cfg.setProperty("hibernate.connection.driver_class", "org.postgresql.Driver");

6) Also in HibernateUtil.java, I changed 'return hsqlFileUrl(dbFilePath)' in method connectionUrl to 'return pgUrl(dbFilePath)', where the new method is as below:

static public String pgUrl(String dbFilePath) {
return "jdbc:postgresql://localhost/zocalo";
}

------------------------------
I'm guessing startDB is when using hsql, so I'm not running that any more.
Now, on running zocalo.sh it does not give any errors, but does so on trying to create an account.

The problem seems to be that no relations are being created in the database...since it threw a bunch of exceptions but the following stood out:

'Caused by: org.postgresql.util.PSQLException: ERROR: relation "users" does not exist'

How should I go about resolving this? (is there a section that initializes/creates relations when they do not exist?)

Thanks.

Aseem

[Aseem]

Within AllMarkets.java I can see another constructor in addition to the one which I believe is being called.

// called from tests
public AllMarkets(String dbFilePath, boolean create) {
String connectionURL = HibernateUtil.connectionUrl(dbFilePath, create);
String mode = create ? HibernateUtil.SCHEMA_CREATE : HibernateUtil.SCHEMA_UPDATE;
configure(connectionURL, mode);
}

I'm guessing this would do the job creating the relations within the database? (What does 'tests' refer to?)

Thanks again.

Aseem

[Chris Hibbert]

> How should I go about resolving this? (is there a section that
> initializes/creates relations when they do not exist?)

There isn't any explicit code in Zocalo that creates the relations or tables. It happens invisibly in Hibernate. I don't even know what invokes it. I'm pretty sure that connecting to the db with the hibernate .hbm.xml files included (which are collected in hibernate.jar) is what causes it, but I don't know what invokes it during execution.

The second version of the AllMarkets constructor you found is called from the junit tests. These are in the tests hierarchy, and compiled into zocalo-test.jar. That's not likely to be what you're looking for.

Can you email me the complete log of error messages? I may see something you didn't notice. The error message 'Caused by: org.postgresql.util.PSQLException: ERROR: relation "users" does
not exist' is informative, but it doesn't tell me what wasn't invoked to create the database.

[Chris Hibbert]

I downloaded the postgresql.jar file learned a little bit. Firstly, notice that startDB (as shipped) invokes java twice. The first time it's starting up an hsql server. You seem to have figured out how to run postgres as a server, but I haven't been successful there. The second time startDB runs Hibernate's SchemaExport tool to initialize the database. If you have the parameters set correctly in hibernate.properties and zocalo.conf, the invocation in startDB should initialize the db. You might change the db startup line to start up postgresql instead, or comment it out in order to re-use the call to SchemaExport.

I'm still reading the postgresql docs (http://jdbc.postgresql.org/documentation/) tying to figure out how I get a server running.

[Aseem]

Hi Chris,

I think I figured out where the problem was. According to (), Postgresql doesn't really support the 'identity' generator being used in the xml mapping files, while mysql does...which is why using a mysql database seemed to work for me.

Also,

1) Is it an easy change to restrict the claims bought/sold by users in binary markets with a market maker to be integer values and not fractional claims?

2) Also, how do I go about creating a binary market without a market maker?

Thanks.

Aseem

[Aseem]

I knew I was forgetting something...Is it possible to perhaps start users off with a certain number of holdings when a binary market is created?

Thanks.

Aseem

[Chris Hibbert]

Oh, good. Thanks for pointing out that mysql works better than postgresql. That my be useful to know.

To your question 1, there's a switch to restrict trading to whole shares only, but it's only checked in Book markets. The way the market maker calculates prices, it would be odd to restrict trading to whole shares. The market maker changes the price continuously as incremental shares are sold. It would probably make more sense to implement a different market maker that used discrete prices and quantities if that's the effect you want.

2) Creating a binary market without a market maker is simple: on the market creation page, fill in a market name, don't fill in the market maker endownment, and make sure the radio button for binary markets is selected.

3) In the experiment configuration, there are parameters to give people initial holdings for each round of an experiment. But there's nothing set up for that in the Prediction Market configuration. But it shouldn't be necessary. Both YES and NO coupons are created from scratch as a result of trading, so traders can both buy and sell when they first start up.

I have thought about supporting the style of trading that the Iowa Markets use, in which people start by buying a basket of all possible outcomes, (both YES and NO) and then sell the outcomes they don't want. It wouldn't be hard to represent that style in Zocalo, but it would require a careful walkthrough of all the trading code, since it currently assumes that users' holdings should be reduced by selling matching sets when possible.

The other thing you might mean is the style of trading in which there's only one kind of good, and you can only buy it if someone has some on hand to sell. That style (which I call "unary" assets to distinguish it from "binary" or "multi-outcome" assets) is available in the experiment configuration. It wouldn't be hard to support it in the prediction markets. I haven't worked on that because I think it reduces liquidity unnecessarily compared to "binary" markets.