Setting Up PostgreSQL on Linux
Russell Bateman
17 November 2011
last update:
|
|
Setting Up PostgreSQL on Linux
Russell Bateman |
I show this basically in the form of a log of exactly what I've done. That way, you can compare what you're seeing with what I saw and overcome any problems you face along the way, assuming they're the same or similar to mine. (Otherwise, mon ami, I'm afraid Google will have to be your friend.)
root@tuonela:/# apt-get install postgresql Reading package lists... Done Building dependency tree Reading state information... Done The following packages were automatically installed and are no longer required: libgcj-bc linux-headers-2.6.38-8 libgcj11 linux-headers-2.6.38-10 linux-headers-2.6.38-11 linux-headers-2.6.38-10-generic-pae linux-headers-2.6.38-8-generic-pae libgcj-common gcj-4.5-jre-lib linux-headers-2.6.38-11-generic-pae ant-gcj gcj-4.5-base Use 'apt-get autoremove' to remove them. The following extra packages will be installed: libpq5 postgresql-8.4 postgresql-client-8.4 postgresql-client-common postgresql-common Suggested packages: oidentd ident-server postgresql-doc-8.4 The following NEW packages will be installed: libpq5 postgresql postgresql-8.4 postgresql-client-8.4 postgresql-client-common postgresql-common 0 upgraded, 6 newly installed, 0 to remove and 31 not upgraded. Need to get 4,849 kB of archives. After this operation, 13.1 MB of additional disk space will be used. Do you want to continue [Y/n]? Y Get:1 http://us.archive.ubuntu.com/ubuntu/ natty-updates/main libpq5 i386 8.4.9-0ubuntu0.11.04 [83.5 kB] Get:2 http://us.archive.ubuntu.com/ubuntu/ natty/main postgresql-client-common all 114 [25.6 kB] Get:3 http://us.archive.ubuntu.com/ubuntu/ natty-updates/main postgresql-client-8.4 i386 8.4.9-0ubuntu0.11.04 [778 kB] Get:4 http://us.archive.ubuntu.com/ubuntu/ natty/main postgresql-common all 114 [89.7 kB] Get:5 http://us.archive.ubuntu.com/ubuntu/ natty-updates/main postgresql-8.4 i386 8.4.9-0ubuntu0.11.04 [3,853 kB] Get:6 http://us.archive.ubuntu.com/ubuntu/ natty-updates/main postgresql all 8.4.9-0ubuntu0.11.04 [18.9 kB] Fetched 4,849 kB in 12s (404 kB/s) Preconfiguring packages ... Selecting previously deselected package libpq5. (Reading database ... 135160 files and directories currently installed.) Unpacking libpq5 (from .../libpq5_8.4.9-0ubuntu0.11.04_i386.deb) ... Selecting previously deselected package postgresql-client-common. Unpacking postgresql-client-common (from .../postgresql-client-common_114_all.deb) ... Selecting previously deselected package postgresql-client-8.4. Unpacking postgresql-client-8.4 (from .../postgresql-client-8.4_8.4.9-0ubuntu0.11.04_i386.deb) ... Selecting previously deselected package postgresql-common. Unpacking postgresql-common (from .../postgresql-common_114_all.deb) ... Selecting previously deselected package postgresql-8.4. Unpacking postgresql-8.4 (from .../postgresql-8.4_8.4.9-0ubuntu0.11.04_i386.deb) ... Selecting previously deselected package postgresql. Unpacking postgresql (from .../postgresql_8.4.9-0ubuntu0.11.04_all.deb) ... Processing triggers for man-db ... Processing triggers for ureadahead ... ureadahead will be reprofiled on next reboot Setting up libpq5 (8.4.9-0ubuntu0.11.04) ... Setting up postgresql-client-common (114) ... Setting up postgresql-client-8.4 (8.4.9-0ubuntu0.11.04) ... update-alternatives: using /usr/share/postgresql/8.4/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode. Setting up postgresql-common (114) ... Adding user postgres to group ssl-cert Building PostgreSQL dictionaries from installed myspell/hunspell packages... Setting up postgresql-8.4 (8.4.9-0ubuntu0.11.04) ... Creating new cluster (configuration: /etc/postgresql/8.4/main, data: /var/lib/postgresql/8.4/main)... Moving configuration file /var/lib/postgresql/8.4/main/postgresql.conf to /etc/postgresql/8.4/main... Moving configuration file /var/lib/postgresql/8.4/main/pg_hba.conf to /etc/postgresql/8.4/main... Moving configuration file /var/lib/postgresql/8.4/main/pg_ident.conf to /etc/postgresql/8.4/main... Configuring postgresql.conf to use port 5432... update-alternatives: using /usr/share/postgresql/8.4/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode. * Starting PostgreSQL 8.4 database server [ OK ] Setting up postgresql (8.4.9-0ubuntu0.11.04) ... Processing triggers for libc-bin ... ldconfig deferred processing now taking place
The preceeding sets up a user, postgres. Here's what I did next: you're supposed to log in as user postgres to complete the installation. First, assign this user a password.
root@tuonela:/# passwd postgres Enter new UNIX password: test123 Retype new UNIX password: test123 passwd: password updated successfully root@tuonela:/# su - postgres postgres@tuonela:~> initdb --pgdata=/var/lib/pgsql/data initdb: command not found
Cool. What you're supposed to do doesn't actually work right off. This is
because you've got to set up PATH for the postgres user (use
.bashrc) and also some variables in .bash_profile. Be sure to
log out, then in again so the new PATH variable takes effect. Do
su postgres instead of su - postgres or it won't
happen. (Note that the postgres user home directory is actually at
/var/lib/postgresql.)
alias ll='ls -al' export PATH="/usr/lib/postgresql/8.4/bin:/usr/local/sbin:/usr/bin:/bin"
export PGLIB=/usr/lib/pgsql export PGDATA=/var/lib/pgsql/data
Et maintenant, messieurs, revenons-en à nos moutons... (Er, let's get back to it.)
root@tuonela:/# su postgres postgres@tuonela:/$ echo $PATH /usr/lib/postgresql/8.4/bin:/usr/local/sbin:/usr/bin:/bin postgres@tuonela:~> initdb --pgdata=/var/lib/pgsql/data The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale en_US.UTF-8. The default database encoding has accordingly been set to UTF8. The default text search configuration will be set to "english". creating directory /var/lib/pgsql/data ... initdb: could not create directory "/var/lib/pgsql": Permission denied
Just a note... initdb is an important utility in PostgreSQL. It allows you to specify, for instance, the default character set.
I had to create /var/lib/psgsql and fix up its ownership before continuing.
root@tuonela:/# mkdir /var/lib/pgsql root@tuonela:/# chown postgres /var/lib/pgsql root@tuonela:/# ^own^grp chgrp postgres /var/lib/pgsql root@tuonela:/# ll -d /var/lib/pgsql drwxr-xr-x 2 postgres postgres 4096 2011-11-17 08:58 /var/lib/pgsql
Encore les dames ! (And now, ladies...)
root@tuonela:/# su postgres postgres@tuonela:~> initdb --pgdata=/var/lib/pgsql/data The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale en_US.UTF-8. The default database encoding has accordingly been set to UTF8. The default text search configuration will be set to "english". creating directory /var/lib/pgsql/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 28MB creating configuration files ... ok creating template1 database in /var/lib/pgsql/data/base/1 ... ok initializing pg_authid ... ok initializing dependencies ... ok creating system views ... ok loading system objects' descriptions ... ok creating conversions ... ok creating dictionaries ... ok setting privileges on built-in objects ... ok creating information schema ... ok vacuuming database template1 ... ok copying template1 to template0 ... ok copying template1 to postgres ... ok WARNING: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the -A option the next time you run initdb. Success. You can now start the database server using: postgres -D /var/lib/pgsql/data or pg_ctl -D /var/lib/pgsql/data -l logfile start postgres@tuonela:/$ ^D postgres@tuonela:/# /etc/init.d/postgresql restart* * Restarting PostgreSQL 8.4 database server
Succès ! (Note: you have to be postgres to bounce the database manager service.
(* Depending on the version, this may be /etc/init.d/postgresql-8.4 or other.)
Still as user postgres, you can issue commands to create a database, then launch a command-line console (terminal) to play with it. You can also create a new database inside the terminal after starting it.
postgres@tuonela:/$ createdb fun postgres@tuonela:/$ psql fun Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit
Note: you can create databases with hyphens in their name, but to delete such a name, you must double-quote it.
postgres@tuonela:/$ createdb fun-stuff postgres@tuonela:/$ psql . . . (later) . . . postgres=# DROP DATABASE "fun-stuff"; DROP DATABASE
It seems the command-line tool likes commands preceded by the backslash character. Some important ones, like how to quit the terminal, were given at start-up. Others include those in a list below, but first, a warning...
Sometimes you get bizarre errors from statements that look perfectly correct.
acme-# SELECT * FROM account; ERROR: syntax error at or near "SELECT" LINE 2: SELECT * FROM account; ^
These are usually a subtle thing. psql doesn't give you an error when you fail to terminate a line with a semicolon (likely thinking you're going to add more). Instead, it changes its prompt subtlely to a minus sign instead of an equal sign, whereas mysql indents with a caret (>) as a continuation character. Once you get back to the right level, your statement might then work:
acme=# SELECT * FROM account; id | fullname | email ----+----------+------------------- 1 | Jack | [email protected] 2 | Jill | [email protected] 3 | | [email protected] 4 | Jill | [email protected] 5 | слон | [email protected] 6 | 森鷗外 | [email protected] (6 rows)
Here is the short command-line summary:
| \c fun | —connect to a specific database (MySQL: use) | ||
| \l | —list databases (MySQL: show databases) | ||
| \d | —list tables, sequences, etc. in current database (MySQL: show tables) | ||
| \d <table-name> | —describe a table's or sequence's schema (MySQL: desc) | ||
| \du | —list users created in PostgreSQL | ||
| SELECT * FROM <table-name> | —(SQL commands are as you'd expect—Ben, voyons !) | ||
| CREATE USER russ WITH PASSWORD 'test123'; | —create a user | ||
| DROP USER levi | —remove a user | ||
| GRANT ALL PRIVILEGES ON <table-name> TO russ | —allow user to work in a table | ||
| CREATEDB <database> | —create a database | ||
| DROP DATABASE <database> | —remove a database | ||
| CREATE TABLE mytable( id SERIAL, ... ); | —sequence to create an auto-incrementing id column * | ||
| DROP SEQUENCE <sequence-name> | —remove an unused sequence after removing a table * |
* This wiggle, added to PostgreSQL version 6.4, replaces the NOT NULL AUTO_INCREMENT UNIQUE directive in MySQL:
CREATE TABLE mytable( id INTEGER NOT NULL AUTO_INCREMENT UNIQUE, ... );
After creating id SERIAL in mytable, PostgreSQL yields:
NOTICE: CREATE TABLE will create implicit sequence "mytable_id_seq" for serial column "mytable.id" CREATE TABLE
...and that creates a SEQUENCE just as if (how to do SERIAL the old, long way):
CREATE SEQUENCE mytable_id_seq; CREATE TABLE mytable( id INTEGER NOT NULL UNIQUE, ... ); ALTER TABLE mytable ALTER COLUMN id SET DEFAULT NEXTVAL( 'mytable_id_seq' );
Therefore, you'll want to remove the sequence after removing the table that consumes it. Here's what you see when you describe a database, sequences listed along with tables:
mydatabase=# \d List of relations Schema | Name | Type | Owner --------+----------------+----------+---------- public | mytable | table | postgres public | mytable_id_seq | sequence | postgres (5 rows)
...and comments.
| alter user russ createdb; | This fixes up a user so he or she may create a database—important when automatically creating a database using ant and/or via Jenkins, etc. | ||
| If you need to be able to remove a database, then re-create it, say from ant, etc., you must own it (in PostgreSQL). So don't go creating your database using the postgres user on your development host. | |||
| When doing something that's going to take more than a screenful of output, the terminal goes into a sort of less mode. At the end, the only key that will get you out (short of killing your process) is 'q'—just what you'd use to Linux less. | |||
| (Uh, take with a grain of salt what I've just said. In the end, I struggled unable to create a database as russ and so removed such statements from my creation script. I'm still creating tables, however, so I'll come back and fix what I say here if I ever get around to it.) | |||
A picture being worth a thousand words, here's a short session as I gain PostgreSQL experience.
postgres@russ-elite-book:/$ psql acme psql (8.4.9) Type "help" for help. acme=# \l List of databases Name | Owner | Encoding | Collation | Ctype | Access privileges ------------+----------+----------+------------+------------+----------------------- acme | postgres | UTF8 | en_US.utf8 | en_US.utf8 | postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 | template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres : postgres=CTc/postgres template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres : postgres=CTc/postgres (4 rows) acme=# \c acme psql (8.4.9) You are now connected to database "acme". acme=# \d No relations found. acme=# create user russ with password 'test123'; CREATE ROLE acme=# create user levi with password 'test123'; CREATE ROLE acme=# drop user levi DROP ROLE acme=# create user russ with password 'test123'; ERROR: role "russ" already exists acme=# \c acme psql (8.4.9) You are now connected to database "acme". acme=# create table table1 ( id integer not null primary key, name varchar(255) ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "table1_pkey" for table "table1" CREATE TABLE acme=# drop table table1; DROP TABLE acme=# create table table1 ( id integer not null unique, name varchar(255) ); NOTICE: CREATE TABLE / UNIQUE will create implicit index "table1_id_key" for table "table1" CREATE TABLE acme=# \d table1 Table "public.table1" Column | Type | Modifiers --------+------------------------+----------- id | integer | not null name | character varying(255) | Indexes: "table1_id_key" UNIQUE, btree (id) acme=# create sequence table1_id_seq; CREATE SEQUENCE acme=# alter table table1 alter column id set default nextval( 'table1_id_seq' ); ALTER TABLE acme=# create table mytable( id serial, name varchar(255) ); NOTICE: CREATE TABLE will create implicit sequence "mytable_id_seq" for serial column "mytable.id" CREATE TABLE acme=# drop table table1; DROP TABLE acme=# \d List of relations Schema | Name | Type | Owner --------+----------------+----------+---------- public | mytable | table | postgres public | mytable_id_seq | sequence | postgres (4 rows) acme=# \d mytable; Table "public.mytable" Column | Type | Modifiers --------+------------------------+------------------------------------------------------ id | integer | not null default nextval('mytable_id_seq'::regclass) name | character varying(255) | acme=# \du* List of roles Role name | Attributes | Member of ---------------+-------------+----------- brent | | {} brooks | | {} cruisecontrol | | {} jenkins | | {} kyle | | {} levi | | {} postgres | Superuser | {} : Create role : Create DB russ | | {} sean | | {} suresh | | {} thomas | | {} tomcat6 | | {} acme=# grant all privileges on mytable to russ; GRANT acme=# flush privileges; ERROR: syntax error at or near "flush" LINE 1: flush privileges; ^ acme=# insert into mytable ( name ) values ( 'russ' ); INSERT 0 1 acme=# select * from mytable; id | name ----+------ 1 | russ (1 row) acme=# insert into mytable ( name ) values ( 'levi' ); INSERT 0 1 acme=# insert into mytable ( name ) values ( 'kyle' ); INSERT 0 1 acme=# insert into mytable ( name ) values ( 'thomas' ); INSERT 0 1 acme=# insert into mytable ( name ) values ( 'suresh' ); INSERT 0 1 acme=# insert into mytable ( name ) values ( 'sean' ); INSERT 0 1 acme=# select * from mytable; id | name ----+-------- 1 | russ 2 | levi 3 | kyle 4 | thomas 5 | suresh 6 | sean (6 rows)
* what \du actually does:
SELECT u.usename AS "User name", u.usesysid AS "User ID", CASE WHEN u.usesuper AND u.usecreatedb THEN CAST('superuser, create database' AS pg_catalog.text) WHEN u.usesuper THEN CAST('superuser' AS pg_catalog.text) WHEN u.usecreatedb THEN CAST('create database' AS pg_catalog.text) ELSE CAST('' AS pg_catalog.text) END AS "Attributes" FROM pg_catalog.pg_user u ORDER BY 1;
My article on SQuirreL, which shows how to connect to MySQL and Oracle, is enough to get SQuirreL running with PostgreSQL. Nevertheless, the details, for the database used to illustrate PostgreSQL above, were:
URL: jdbc:postgresql://localhost[:5432]/acme JDBC driver: postgresql-9.1-901.jdbc4.jar Driver class: org.postgresql.Driver
I was once obliged to switch from using MySQL to PostgreSQL for a project.
Using Hibernate with a PostgreSQL database isn't as straightforward as using it with MySQL. Sure, there's the Hibernate configuration stuff that you expect:
com.mysql.jdbc.Driver ...org.hibernate.dialect.MySQLDialect ...
becomes...
org.postgresql.Driver ...org.hibernate.dialect.PostgreSQLDialect ...
But, there is the matter of hibernate_sequence, something that must be created in your initial database script if you're using table OIDs with sequences (which is automatic in MySQL). The need arises when you employ the @GeneratedValue annotation in your Java bean code:
@XmlRootElement
@Entity
@Table( name = "fun" )
public class Fun implements Serializable
{
private Long id;
...
@Id
@Column(name = "id")
@GeneratedValue( strategy = GenerationType.AUTO )
public Long getId() { return this.id; }
public void setId( Long id ) { this.id = id; }
...
Here's the SQL code creating the sequence needed by Hibernate's PostgreSQL dialect as well as the table. Even if you use PostgreSQL's modern SERIAL type, you must do this.
CREATE TABLE fun
(
id INT NOT NULL, --(or "id SERIAL NOT NULL" it is the same)
...
PRIMARY KEY (id)
);
CREATE SEQUENCE hibernate_sequence
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 5;
What happens that demonstrates this necessity is that your "reads" will work fine:
@Implementor( FunManager.class )
public class FunManager
{
private static Logger log = Logger.getLogger( FunManager.class );
private static Session session = HibernateUtil.getSession();
public List< Fun > read()
{
try
{
Criteria criteria = session.createCriteria( Fun.class );
criteria.isReadOnly();
log.info( "Read in list of all fun..." );
return criteria.list();
}
catch( HibernateException e )
{
session.getTransaction().rollback();
}
return null;
}
...
}
But, your "writes" will not if you don't have that sequence defined.
@Implementor( FunManager.class )
public class FunManager
{
private static Logger log = Logger.getLogger( FunManager.class );
private static Session session = HibernateUtil.getSession();
...
public Fun create( Fun fun ) throws HibernateException
{
Transaction transaction = null;
try
{
transaction = session.beginTransaction();
session.save( fun );
transaction.commit();
session.flush();
log.info( "Created new fun" + fun.getId() );
}
catch( HibernateException he )
{
transaction.rollback();
throw he;
}
return fun;
}
...
}
Instead, what will happen is:
org.hibernate.exception.SQLGrammarException: could not get next sequence value
In the Eclipse debugger, the HibernateException details appear:
Name | Value -------------------+---------------------------------- ↓ e | SQLGrammarException → cause | PSQLException → detailMessage | "could not get next sequence value" → sql | "select nextval('hibernate_sequence')"
For MySql, HyperSQL, etc. there are increment fields that automatically handle this problem. But in Postgres or Oracle, older databases, you must use the sequence construct. In modern PostgreSQL, this is handled by the SERIAL type, however, for some reason, this does not benefit its Hibernate dialect implementation.
Alternatively (to hibernate_sequence), you can specify a private table sequence name. Why would you want to do this? Do this if you have several tables that use auto-incrementing OIDs and do not wish unique OIDs across all of them, particularly in a production environment where you must not run out of them. (This said, I happen to know that the magnitude of an Oracle sequence is 1E+27 or 1,000,000,000,000,000,000,000,000,000 discrete values, which is a very huge number. I haven't looked into the magnitude of PostgreSQL's sequence.)
Incidentally, the Hibernate reference documentation seems to recommend annotating methods instead of variables—a practice I've followed here.
@XmlRootElement
@Entity
@Table( name = "fun" )
public class Fun implements Serializable
{
private Long id;
...
@Id
@GeneratedValue( strategy=GenerationType.AUTO, generator="fun_seq_gen" )
@SequenceGenerator( name="fun_seq_gen", sequenceName="fun_seq" )
public Long getId() { return this.id; }
public void setId( Long id ) { this.id = id; }
...
}
In order for the above to work, you must create the sequence, probably via a database-loading script:
DROP SEQUENCE IF EXISTS fun_seq; CREATE SEQUENCE fun_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 5;
Later, I described its schema just for fun:
acme=> \d fun_seq Sequence "public.fun_seq" Column | Type | Value ---------------+---------+--------------------- sequence_name | name | fun_seq last_value | bigint | 5 start_value | bigint | 1 increment_by | bigint | 1 max_value | bigint | 9223372036854775807 min_value | bigint | 1 cache_value | bigint | 5 log_cnt | bigint | 32 is_cycled | boolean | f is_called | boolean | t
Note that you can specify the starting value for OID generation if you care using initialValue in the @SequenceGenerator annotation. See (javax.persistence) Annotation SequenceGenerator.
There is another annotation, @GeneratedValue(strategy=GenerationType.IDENTITY) that also generates an automatic value during commit for every new object (i.e.: "row" in SQL parlance).
The difference between this and GenerationType.AUTO is that the sequence is managed independently for each object class instead of for all object classes. I did not choose to experiment with this method. I'm guessing it doesn't solve the need to create separately named sequences explicitly in the case of PostgreSQL and Oracle.
First, a caveat...
Much of the trouble I had with Hibernate on PostgreSQL ended up being due to having pre-populated tables. I didn't recognize this until late in the game. Then I added the section below entitled, "Somthing very odd". I have chosen not to rewrite the intervening sections in case they prove to be useful observations anyway. Sorry about this.
While not necessarily things whose analog cannot happen with MySQL, here are some notes I made along the way to getting a MySQL-to-PostgreSQL switch-over completed. I was updating a database via a ReST service using JSON. Some of what's happening is due to JSON-to-object translation, though sometimes not a problem for the original MySQL implementation.
Consider this improved table:
CREATE TABLE fun
(
id INT NOT NULL,
astring VARCHAR( 16 ) NOT NULL,
aboolean BOOLEAN NOT NULL DEFAULT FALSE,
adate DATE NOT NULL,
atime TIMESTAMP NULL,
PRIMARY KEY (id)
);
--what happened when I put a 0 in for id:
{
"id":0,
"astring":"Another string",
"aboolean":"true",
"adate":"1945-08-08",
"atime":"2012-02-09 12:34:56"
}
Name | Value -------------------+---------------------------------- ↓ e | ConstraintViolationException → cause | BatchUpdateException → detailMessage | "Could not execute JDBC batch update" → sql | "insert into fun(adate,astring,atime,aboolean,id) values(?,?,?,?,?)"
Then, it worked when I put in a 1 (which already existed). And the next time, too. It's just about what's a valid value (and not whether the value already exists or not) as the name of the exception implies.
acme=> select * from fun; id | astring | aboolean | adate | atime ----+------------------+----------+------------+--------------------- 1 | This is a string | t | 2011-08-08 | 2011-02-09 12:34:56 2 | Another string | f | 1945-08-08 | 3 | Another string | f | 1945-08-08 | (3 rows)
The reason that atime doesn't get populated from the JSON is probably based in the inability of the JSON-to-Fun (object) translation going on prior to reaching the Fun service (in FunWS.java).
This is caused by passing data in one or more columns that doesn't fit, such as a longer string than specified. Consider this table:
CREATE TABLE fun
(
id INT NOT NULL,
anint INT,
astring VARCHAR( 16 ) NOT NULL,
aboolean BOOLEAN NOT NULL DEFAULT FALSE,
adate DATE NOT NULL,
atime TIMESTAMP NULL,
PRIMARY KEY (id)
);
--what happened when I exceeded 16 characters for astring:
{
"id":1,
"anint":2,
"astring":"Another string",
"aboolean":"true",
"adate":"1945-08-08",
"atime":"2012-02-09 12:34:56"
}
Here's the exception in the Eclipse debugger.
Name | Value ---------------------+---------------------------------- ↓ e | DataException → cause | BatchUpdateException → detailMessage | "Could not execute JDBC batch update" → sql | "insert into fun(adate,anint,astring,atime,aboolean,id) values(?,?,?,?,?,?)" | ↓ fun | Fun serialVersionUID | -4308822924595470061 aboolean | false → adate | Date → anint | 2 astring | "Another string that is simply too long" atime | null → id | Integer
What happens if you send a bogus payload (that doesn't match the desired schema):
Name | Value ------------------+---------------------------------- ↓ e | SQLGrammarException → cause | PSQLException → detailMessage | "Could not execute JDBC batch update" → sql | "insert into fun(adate,anint,astring,atime,aboolean,id) values(?,?,?,?,?,?)"Something's still wrong
What happens if you send what you think is a good payload, but it seems to be unspecifically rejected by PostgreSQL (or Hibernate). (Actually, this doesn't happen with Fun, but with a much more complex table.
Name | Value ------------------+---------------------------------- ↓ e | SQLGrammarException → cause | BatchUpdateException → detailMessage | "Could not execute JDBC batch update" → sql | "insert into fun(adate,anint,astring,atime,aboolean,id) values(?,?,?,?,?,?)"In this case, the problem was narrowed down to a String member being passed as an Integer in the JSON. The definition of the object was changed to accommodate Integer for that column which was already INT in the SQL definition.
At this point, the major stumbling blocks to this port from MySQL to PostgreSQL have been removed.
But, what I really think is happening when I test this stuff is...
Something very odd
It turns out that if I pre-populate a table with a few rows, when I first begin attempting to commit new rows, in the case of my ReST server this is done from a remote browser or application, I will get:
Name | Value ------------------+---------------------------------- ↓ e | ConstraintViolationException → cause | BatchUpdateException → detailMessage | "Could not execute JDBC batch update" → sql | "insert into fun(adate,anint,astring,atime,aboolean,id) values(?,?,?,?,?,?)" ...exactly the first n times I attempt the commit:... transaction = session.beginTransaction(); session.save( fun ); transaction.commit(); <--------- fails here session.flush(); ......where n corresponds to the number of rows by which I pre-populate the database table. Go figure...
Well, I conjecture that this is because I'm not starting out hibernate_sequence at n + 1. This is inconvenient, but it is not a problem in production mode since you'd not necessarily pre-populate or, at least not pre-populate tables that would be added to. (Then again, you just might very well do this as you might have prepopulated tables containing more or less static information, but that you could add to, n'est-ce pas ?). Knowing that this is happening is half the battle.
In the end, a colleague discovered that it's the use of GeneratedValue( strategy=GenerationType.AUTO ) that doesn't work with PostgreSQL. Instead, substitute GeneratedValue( strategy=GenerationType.SEQUENCE ) in the code.
Later, I found that this wouldn't work for me anyway in a real-world assignment, probably for those tables I filled with static or test data. I had to add instead a piece of duct tape to correct the sequence number to be ready for the next value:
DROP SEQUENCE IF EXISTS language_sequence CASCADE; CREATE SEQUENCE language_sequence INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1; DROP TABLE IF EXISTS language; CREATE TABLE language ( id INTEGER NOT NULL PRIMARY KEY DEFAULT NEXTVAL( 'language_sequence' ); name VARCHAR(32) NOT NULL, code VARCHAR(2) NOT NULL ); START TRANSACTION; INSERT INTO language( id, name, code ) VALUES( 1, 'Abkhazian', 'ab' ); ... INSERT INTO language( id, name, code ) VALUES( 170, 'Yiddish', 'ji' ); COMMIT; SELECT SETVAL( 'language_sequence', ( SELECT MAX( id ) FROM language ) + 1 );The last statement above sets the next sequence number to 171, ready to add another language (like Klingon or Wookie). Of course, in Language.java, I put in all the annotations for id that I noted earlier in these appendices (@SequenceGenerator in particular).
Last minute observations...
...to fold into the above someday. In the definition below,
CREATE SEQUENCE hibernate_sequence INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 5;...CACHE 5 is billed as causing 5 sequence numbers to be cached for faster assignment. In fact, whether or not this is faster, it results in auto-incremented OIDs getting bumped by 5.