TagsPlugin with Trac 1.0 and PostgreSQL

[Peter Suter]

Hi,
I'm trying to use the TagsPlugin [1] with Trac 1.0 and a PostgreSQL
database, but I always get "environment needs upgrade".
"trac-admin tracenv upgrade" fails with "ProgrammingError: ERROR:
Relation »tags« already exists".

The table "tags" does exist.
Everything works when SQLite is used instead of PostgreSQL. (I just
migrated the database.)
Everything works with PostgreSQL when the tagsplugin is disabled.

Looking at the logs below and at the TagsPlugin source [2] I checked
manually and rollback_is_safe is False. So after the query from the
"wiki_namespace" table that should fail, rollback() is not called. So
PostgreSQL ignores the query from the "tags" table that should
succeed.
I think this is caused by [3], which seems to be related to th:#9521
[4] and #10451 [5].

Does that mean TagsPlugin is broken for PostgreSQL at the moment? Or
am I missing something?

Thanks,
Peter

[1] http://trac-hacks.org/wiki/TagsPlugin
[2] http://trac-hacks.org/browser/tagsplugin/trunk/tractags/model.py
[3] http://trac-hacks.org/changeset/11041
[4] http://trac-hacks.org/ticket/9521
[5] http://trac.edgewall.org/ticket/10451

== Versions
* Trac: 1.0dev-r11152
* TagsPlugin: 0.7dev-r11105
* psycopg2: 2.4.5
* PostgreSQL: 9.0

== Logs
* Trac logs the following during the upgrade attempt:
Trac[env] INFO: -------------------------------- environment startup
[Trac 1.0dev-r11152] --------------------------------
Trac[model] ERROR: DatabaseError: ERROR: Current transaction is
aborted, commands ignored until end of transaction block
Trac[env] WARNING: Component <tractags.model.TagModelProvider object
at 0x0307B470> requires environment upgrade
Trac[model] ERROR: DatabaseError: ERROR: Current transaction is
aborted, commands ignored until end of transaction block
Trac[env] INFO: tractags.model.TagModelProvider upgrading...
Trac[model] ERROR: DatabaseError: ERROR: Relation »tags« already exists
Trac[console] ERROR: Exception in trac-admin command
ProgrammingError: ERROR: Relation »tags« already exists
ProgrammingError: ERROR: Relation »tags« already exists

* While PostgreSQL logs:
ERROR: Relation »wiki_namespace« does not exist at character 22
QUERY: SELECT COUNT(*) FROM wiki_namespace
ERROR: Current transaction is aborted, commands ignored until end of
transaction block
QUERY: SELECT COUNT(*) FROM tags
ERROR: Relation »wiki_namespace« does not exist at character 22
QUERY: SELECT COUNT(*) FROM wiki_namespace
ERROR: Current transaction is aborted, commands ignored until end of
transaction block
QUERY: SELECT COUNT(*) FROM tags
ERROR: Relation »tags« already exists
QUERY: CREATE TABLE "tags" (
"tagspace" text,
"name" text,
"tag" text,
CONSTRAINT "tags_pk" PRIMARY KEY ("tagspace","name","tag")
)

[Eduard-Cristian Stefan]

On Wed, Jul 25, 2012 at 10:18 AM, Peter Suter <pets...@gmail.com> wrote:
> Hi,
> I'm trying to use the TagsPlugin [1] with Trac 1.0 and a PostgreSQL
> database, but I always get "environment needs upgrade".
> "trac-admin tracenv upgrade" fails with "ProgrammingError: ERROR:
> Relation »tags« already exists".

Can you please test the attached patch? It works for me with the current trunk
of tags plugin, but I can't test the old data migration.

Have a nice day,
Eduard

[Peter Suter]

On Wed, Jul 25, 2012 at 11:28 AM, Eduard-Cristian Stefan
<alexan...@gmail.com> wrote:
> Can you please test the attached patch? It works for me with the current trunk
> of tags plugin, but I can't test the old data migration.

Thanks.
Same here. With this patch I can get it to start and it works. I have
no old data to migrate so I haven't tested that either.

I'm still not sure if this is a bug in the plugin or in Trac or in
both. In http://trac.edgewall.org/ticket/10451#comment:1 the idea was
mentioned that postgresql backend might need automatic rollback on
error. A workaround was added to the TagsPlugin and the idea was
dropped / changed because of that. But it still doesn't work...

Thanks,
Peter

[Remy Blank]

Peter Suter wrote:
> I'm still not sure if this is a bug in the plugin or in Trac or in
> both. In http://trac.edgewall.org/ticket/10451#comment:1 the idea was
> mentioned that postgresql backend might need automatic rollback on
> error. A workaround was added to the TagsPlugin and the idea was
> dropped / changed because of that. But it still doesn't work...

TagsPlugin is one of the only plugins that checks if its DB schema
exists by selecting on a table and catching the exception if it doesn't.
As you noticed, different DB backends react differently in that case.
Moreover, this strategy interacts badly with nested transaction blocks
(as does an explicit db.rollback() by the plugin), because it messes up
the whole transaction, not only the innermost one. So we want to
discourage this practice.

The standard way of checking that the DB schema for a plugin exists is
by adding a row to the `system` table, using the schema version as the
value, and checking for the existence (and value, in the case of
upgrades) of that row. I would suggest that the TagsPlugin switches to
that strategy.

But this still leaves the transition from the current state. It may work
to open a nested transaction block in the plugin's
environment_needs_upgrade() for the potentially failing query, and to
call db.rollback() in case of failure. Now that each
environment_needs_upgrade() is called within a separate block, it
shouldn't have any effect on other plugins, and it should be reasonably
safe. Same strategy for upgrade_environment().

-- Remy

[Steffen Hoffmann]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Obviously the workaround doesn't cope with with non-graceful handling of
failing transactions due to bad SELECT statements.

Thanks for the patch. May I carry it over to the appropriate ticket
against TagsPlugin [1] and use it to patch current trunk, please?

I have no test setup to check the actual upgrade with PostgreSQL too, so
I'd be grateful, if someone could follow-up with his/her experience,
preferably reporting to the ticket. Thanks for taking care.

Sincerely,

Steffen Hoffmann

[1] http://trac-hacks.org/ticket/9521
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAlAQc4wACgkQ31DJeiZFuHck5QCfY7spn7EiAzntcRpPFJ9ifI43
+E4AoNX/CDzMvyaYVFRSn8GKDJa89pHN
=XMSw
-----END PGP SIGNATURE-----

[Eduard-Cristian Stefan]

On 2012-07-26 01:30, Steffen Hoffmann wrote:
> Thanks for the patch. May I carry it over to the appropriate ticket
> against TagsPlugin [1] and use it to patch current trunk, please?

Please do.

Do you know if a conversion from SVN to Mercurial
is planned in the near future?

[RjOllos]

On Wednesday, July 25, 2012 8:44:14 PM UTC-7, Eduard-Cristian Stefan wrote:

Do you know if a conversion from SVN to Mercurial
is planned in the near future?

For trac-hacks?  There is a request for this that you can follow (1).

I don't expect it to happen in the near future. Besides the work in makingthe transition, an issue that comes up when considering moving away from SVN is, Hg or Git? I doubt we'll get any community agreement on a single VCS for trac-hacks. I suppose it would be possible with Trac 1.0 and later to allow users to choose from SVN, Hg or Git, but having support for all 3 would be a big project.

(1) https://trac-hacks.org/ticket/9831

[Eduard-Cristian Stefan]

On Fri, Jul 27, 2012 at 2:04 AM, RjOllos <ry...@physiosonics.com> wrote:
> On Wednesday, July 25, 2012 8:44:14 PM UTC-7, Eduard-Cristian Stefan wrote:
>> Do you know if a conversion from SVN to Mercurial
>> is planned in the near future?
> For trac-hacks? There is a request for this that you can follow (1).

It's mine :D

> I don't expect it to happen in the near future. Besides the work in
> makingthe transition, an issue that comes up when considering moving away
> from SVN is, Hg or Git? I doubt we'll get any community agreement on a
> single VCS for trac-hacks. I suppose it would be possible with Trac 1.0 and
> later to allow users to choose from SVN, Hg or Git, but having support for
> all 3 would be a big project.

I'm only using Trac because it's written in Python, so it's easy to
fix it (Windows user, btw). For the same reason (Windows) I have
switched from Git to Mercurial, so I'm not impartial. But if it's
something that I can help with regarding to Git/Mercurial transition,
let me know.

[Eduard-Cristian Stefan]

You can find a mirror of http://trac-hacks.org/svn/tagsplugin/trunk/
(starting from version 0.6) here:

https://bitbucket.org/alexandrul/trac-tags-plugin

The patch attached in my previous mail was also applied
(model: fix _need_migration for PostgreSQL).

[Eduard-Cristian Stefan]

On 2012-07-26 01:30, Steffen Hoffmann wrote:

> Thanks for the patch. May I carry it over to the appropriate ticket
> against TagsPlugin [1] and use it to patch current trunk, please?
>
> I have no test setup to check the actual upgrade with PostgreSQL too, so
> I'd be grateful, if someone could follow-up with his/her experience,
> preferably reporting to the ticket. Thanks for taking care.

Could you please apply the patch on the trunk? I can't find it
in the revision log.