Does your test user have permissions to create databases ?

What is the outcome if you try to create this database with the command line tools using the Postgres role you have setup for the test user ?

Sorry, ignore my comment. In bug #609583 you show that you can create the db with the testuser.

Does the gnucash.trace file and/or postgres server log have more information?

Created attachment 156148 [details]
gnucash.trace

Created attachment 156149 [details]
Tail of postgresql.log

Sorry it's taken me awhile to get back. I re-ran, as you requested, using the svn trunk version of several days ago. I've just posted gnucash.trace and the tail of /var/log/postgresql. Looks like a character-encoding issue ...

Yes, it does. And in this case, you're going to have to modify your postgres template to use UTF8, which is the only encoding Gnucash will support. It's much too hard to support everything else.

(In reply to comment #7)
> Yes, it does. And in this case, you're going to have to modify your postgres
> template to use UTF8, which is the only encoding Gnucash will support. It's
> much too hard to support everything else.

But I think it ought to support the default postgres encoding and I don't believe I did anything special regarding character-encoding when I created the postgres db last February. So it would appear that the default, at least at the time, was not UTF8. Some time has passed and I can't vouch for the details of what I did when I first submitted the bug report, except for what I documented then. I'll repeat the experiment with 2.3.16 and the version of postgres I have installed now and will report back.

AFAICT, the default encoding *is* UTF8. 
If you run the psql \l command it will tell you what is the encoding of each database -- and templates are databases. It appears from your error message that you somehow got the basic template to have a US_ASCII encoding.

You are not correct -- the default encoding is not UTF8.

Relevant excerpt from postgres' initdb man page:
[begin quote]
-E encoding

--encoding=encoding
              Selects  the  encoding of the template database. This will also be the default encoding of any data-base you create later, unless you override it there. The default is  derived  from  the  locale,  or SQL_ASCII if that does not work. The character sets supported by the PostgreSQL server are described in in the documentation.
[end quote]

I don't know what Arch did about the locale (but suspect that it was the same as Slackware; see below) and I am no longer using it (Arch's rolling release nonsense will periodically blow up your system because they can't test the whole system when someone gets a bright idea, and you get to mess with a rescue cd and chroot to try to piece things back together). But you can see from the man page that ending up with SQL_ASCII is a distinct possibility. I am now running Slackware and have been since shortly after I submitted the original report. So let's look at what happens with Slackware and Postgres 8.4.2.

In /usr/share/doc/postgresql-8.4.2/README.SBo, you are instructed to

     su postgres -c "initdb -D /var/lib/pgsql/data"

This is very much the same as you will find with other Linux distributions (and with OpenBSD) and I reiterate more strongly: I am POSITIVE that I did nothing explicit about character encoding, no -E option, when I did the initdb with Arch last February (the first time I looked at the initdb man page was to prepare this comment; I was not even aware of the -E option prior to that). 

So without -E, you get the default encoding, whatever that turns out to be.

In this case:
[begin quote]
dca@sergei:~$ su postgres -c "initdb -D /var/lib/pgsql/data"
Password:
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 C.
The default database encoding has accordingly been set to SQL_ASCII.
The default text search configuration will be set to "english".
.....
[end quote]

So with Slackware, and I'm quite sure with Arch and OpenBSD, you end up with SQL_ASCII if you've done nothing special about your locale (which you can't with OpenBSD; they don't have locale support) and do not explicitly specify an encoding.

If gnucash supports only UTF8, then the gnucash documentation will need to provide very explicit instructions to the potential postgres user about how to set the locale (the default won't work, as we've just seen) and/or do the initdb and/or the createdb to end up with a database for gnucash with the supported encoding.

The postgresql log (Attachment 156149 [details] from comment 5) suggests that we should use template0 to create the new database instead of the default template1.

This could be changed easily in the code such that the SQL command executed will be:
CREATE DATABASE gnucash WITH TEMPLATE 'template0' ENCODING 'UTF8'

This seems the way to solve this properly. Though I don't know if we would be missing out on some important "installation local" objects that may be in template1 [1]. I don't really know what is meant with installation local objects. In any case, if the installation local objects are important, the user can always create the database manually via the postgresql own mechanisms and feed that db to gnucash. If gnucash is to create the database, we only have to make sure it has everything we need, which is, basically an empty database in utf-8 encoding.


[1] See http://www.postgresql.org/docs/8.1/static/sql-createdatabase.html which mentions the use of template0 to prevent installation local objects to be included.

Geert -- I think you are on the right track. I had forgotten that gnucash creates the database, if I ever knew it (when I was talking about fixing the documentation, I had it in my head for some reason that the user creates the database) and I also didn't review the history of this bug as carefully as you did. Good catch, as they say these days.

Here's an excerpt from Chapter 21 of the Postgres User Manual:
[begin quote]
Another common reason for copying template0 instead of template1 is that new encoding and locale settings can be specified when copying template0, whereas a copy of template1 must use the same settings it does. This is because template1 might contain encoding-specific or locale-specific data, while template0 is known not to.
[end quote]

So gnucash's attempts to create a UTF8-encoded database using template1 in a framework initialized with SQL_ASCII aren't going to work. From what is written above, it looks like your proposed fix is likely to work.

It's worth a try.
Don, please apply the following patch to a current svn checkout (r19822 or later), compile, and see if it creates the database:

Index: src/backend/dbi/gnc-backend-dbi.c
===================================================================
--- src/backend/dbi/gnc-backend-dbi.c	(revision 19822)
+++ src/backend/dbi/gnc-backend-dbi.c	(working copy)
@@ -976,7 +976,7 @@
                 qof_backend_set_error( qbe, ERR_BACKEND_SERVER_ERR );
                 goto exit;
             }
-            dresult = dbi_conn_queryf( be->conn, "CREATE DATABASE %s WITH ENCODING 'UTF8'", dbnamelc );
+            dresult = dbi_conn_queryf( be->conn, "CREATE DATABASE %s WITH ENCODING 'UTF8' TEMPLATE = 'template0'", dbnamelc );
             if ( dresult == NULL )
             {
                 PERR( "Unable to create database '%s'\n", dbname );

(If you'd rather, you could edit that one line in whatever source you've got rather than pulling in svn; just be carefull about the dbnamelc argument; it was dbname until 19822.)

(In reply to comment #13)
> It's worth a try.
> Don, please apply the following patch to a current svn checkout (r19822 or
> later), compile, and see if it creates the database:
> 

I committed this yesterday in r19824 and ran some basic tests on it sometime a little bit before you added your comment here. I wanted to inform the bug of this but my PC crashed (for an unrelated reason). I was too tired to restart. So I add my comment now.

It have tested this patch doesn't break GnuCash. I can create a new db just fine still with the change.

But my template1 has a default encoding of UTF-8, so I can't test if the change actually fixed the problem of creating a new DB in a different encoding from template1.

@Don: if you want to test, you can checkout r19824 or later and see if that solves your problem.

OK, I managed to test this by explicitly creating a new cluster with --locale=C. As one can see for psql \l:
postgres=# \l
                              List of databases
   Name    |  Owner   | Encoding  | Collation | Ctype |   Access privileges   
-----------+----------+-----------+-----------+-------+-----------------------
 gnucash   | john     | SQL_ASCII | C         | C     | 
 postgres  | postgres | SQL_ASCII | C         | C     | 
 sampledb  | john     | UTF8      | C         | C     | 
 template0 | postgres | SQL_ASCII | C         | C     | =c/postgres
                                                      : postgres=CTc/postgres
 template1 | postgres | SQL_ASCII | C         | C     | =c/postgres
                                                      : postgres=CTc/postgres
(5 rows)

It works.

A couple of things:

1. John: in comment 15, you show a gnucash database with SQL_ASCII encoding. I thought the whole purpose of this exercise was to get gnucash to respond to an initial 'save as' by creating a gnucash database with UTF8 encoding. So I'm confused by your "it works" and what you are showing us. Perhaps I'm missing something and you could clarify with further explanation.

2. After tripping over my own feet as discussed on gnucash-dev, I got this morning's svn head (r19826, so it should have Geert's fix) built, installed and running. But when I attempt a 'save as', selecting postgres and putting in the appropriate username and password, I still get the error message "The server at URL postgres://dca@localhost/gnucash experienced an error or encountered bad or corrupt data". However, if I create the gnucash database myself from psql using template0 and requesting UTF8 encoding, I can then do the 'save as' from gnucash (same userid and password). I should note that while gnucash is inserting the data into the database (a LOT of it in my case) it does not update the screen and the mouse disappears when moved into the 'save as' window, which remains on the screen while the data is being copied. I think the UI behavior needs work (progress bar?). Perhaps no one has tested this with a large amount of data? My gnucash file is over 20 MB uncompressed. So I think there's work remaining here in both the db creation and UI areas.

Ah, but it's the "sampledb" database that's interesting here: That's the one I created (using gnucash) against template 0 with UTF8 encoding.

There's no requirement to take the "gnucash" default name for the database. Indeed if you have multiple books, you'll need a separate database for each. The gnucash one in this set happens to be one I'd created from the command line while I was trying to figure out an error I'd made in r19822.

That error is what's tripping you up, too. You need r19832, which I checked in this afternoon and which fixes the problem.

The progress bar is a good idea, but not a necessity for the upcoming release. After all, it's presumably a one-shot: You'll do the save as once, and then use your Postgres database forever after. If you like, file a new bug, marked "enhancement", with that suggestion.

(In reply to comment #17)
> Ah, but it's the "sampledb" database that's interesting here: That's the one I
> created (using gnucash) against template 0 with UTF8 encoding.

Ok. It would have been good to have told us that, especially since there was another db called 'gnucash'. Though the UTF8 encoding of 'sampledb' was a clue to the puzzle you posed.

> 
> There's no requirement to take the "gnucash" default name for the database.
> Indeed if you have multiple books, you'll need a separate database for each.
> The gnucash one in this set happens to be one I'd created from the command line
> while I was trying to figure out an error I'd made in r19822.

I understand, now that you explain it.

> 
> That error is what's tripping you up, too. You need r19832, which I checked in
> this afternoon and which fixes the problem.

Yes. I did an svn update a little bit ago and have r19833 running. The 'save as' now works.

> 
> The progress bar is a good idea, but not a necessity for the upcoming release.

I suppose. But I would suggest that the behavior be noted in the release notes, because the UI does not give any indication that the system has paid attention to your click of the 'save as' button. My first reaction was that it was hung. Then I looked at the disk light on the machine and saw that there was activity. Then I ran 'top' and could see that gnucash and postgres were busy doing something. Copying 20 MB worth of gnucash xml into postgres takes long enough to make all that possible.

> After all, it's presumably a one-shot: You'll do the save as once, and then use
> your Postgres database forever after.

True, unless the user misinterprets what's happening because the system appears to be ignoring her, kills gnucash in the middle of building the database, and complains that gnucash's db backend isn't working. That's why I think there should be something in the release notes describing this, with a promise to improve the UI in a subsequent release (you'll still get complaints from people who don't read carefully, as I was guilty of yesterday, but they will be fewer and at least you can then refer them to the release notes). I also note that there is a progress bar when loading from the database (which doesn't work when loading from postgres, by the way -- I'll file a bug report), so it makes sense to have one on saving.

 If you like, file a new bug, marked
> "enhancement", with that suggestion.

GnuCash bug tracking has moved to a new Bugzilla host. This bug has been copied to https://bugs.gnucash.org/show_bug.cgi?id=609581. Please update any external references or bookmarks.