App doesn't work with a PostgreSQL backend

[colwin]

I am trying to use your app with a PostgreSQL backend. From the looks of the problem PostgreSQL doesn't support the REPLACE sql command. Here is a log of the unit test run

No fixtures found.
.FEFEE
======================================================================
ERROR: test_get_n_tickets (ticketing.tests.tests.TicketingTest)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/opt/race/share/sw/os/Linux_2.6_libc2.5_i686/python/lib/python2.7/site-packages/django_ticketing-0.6.2-py2.7.egg/ticketing/tests/tests.py", line 27, in test_get_n_tickets
    t0 = get_ticket()
  File "/opt/race/share/sw/os/Linux_2.6_libc2.5_i686/python/lib/python2.7/site-packages/django_ticketing-0.6.2-py2.7.egg/ticketing/models.py", line 85, in get_ticket
    ticket = ticketing_models[sequence].objects.create()
  File "/opt/race/share/sw/os/Linux_2.6_libc2.5_i686/python/lib/python2.7/site-packages/django_ticketing-0.6.2-py2.7.egg/ticketing/managers.py", line 7, in create
    id = self.get_ticket()
  File "/opt/race/share/sw/os/Linux_2.6_libc2.5_i686/python/lib/python2.7/site-packages/django_ticketing-0.6.2-py2.7.egg/ticketing/managers.py", line 23, in get_ticket
    result = cursor.execute(sql, [self.model.STUB_DEFAULT])
  File "/opt/race/share/sw/os/Linux_2.6_libc2.5_i686/python/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py", line 44, in execute
    return self.cursor.execute(query, args)
DatabaseError: syntax error at or near "REPLACE" at character 1


======================================================================
ERROR: test_get_ticket (ticketing.tests.tests.TicketingTest)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/opt/race/share/sw/os/Linux_2.6_libc2.5_i686/python/lib/python2.7/site-packages/django_ticketing-0.6.2-py2.7.egg/ticketing/tests/tests.py", line 11, in test_get_ticket
    t = get_ticket()
  File "/opt/race/share/sw/os/Linux_2.6_libc2.5_i686/python/lib/python2.7/site-packages/django_ticketing-0.6.2-py2.7.egg/ticketing/models.py", line 85, in get_ticket
    ticket = ticketing_models[sequence].objects.create()
  File "/opt/race/share/sw/os/Linux_2.6_libc2.5_i686/python/lib/python2.7/site-packages/django_ticketing-0.6.2-py2.7.egg/ticketing/managers.py", line 7, in create
    id = self.get_ticket()
  File "/opt/race/share/sw/os/Linux_2.6_libc2.5_i686/python/lib/python2.7/site-packages/django_ticketing-0.6.2-py2.7.egg/ticketing/managers.py", line 23, in get_ticket
    result = cursor.execute(sql, [self.model.STUB_DEFAULT])
  File "/opt/race/share/sw/os/Linux_2.6_libc2.5_i686/python/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py", line 44, in execute
    return self.cursor.execute(query, args)
DatabaseError: syntax error at or near "REPLACE" at character 1

Are you planning to support PostgreSQL as a backend? The documentation for your app says that it works on all backends that Django supports.

Version Information
PostgreSQL version
        PostgreSQL 8.1.18 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46)
Django Version
      1.2.5

[streeter]

Huh. I'm not sure why the postgres backend doesn't work. It should be creating standard SQL, though I guess postgres doesn't like it. The SQL should look like:

REPLACE INTO `ticketing_default` ('stub') VALUES (1)

I'll try to get postgres running somewhere and see if I can't get it to work with it.

[colwin]

I don't think that PostgreSQL has the replace sql command. I AK using

sent via my HTC Legend On 27-May-2011 7:17 PM, "streeter" < [email protected]> wrote:

[colwin]

Dam fat fingers and small keys. Hit send prematurely.

As I was saying I don't think that PostgeSQL has the replace command. I am using version 8.1

-Colwin

sent via my HTC Legend On 27-May-2011 7:17 PM, "streeter" < [email protected]> wrote:

Huh. I'm not sure why the postgres backend doesn't work. It should be creating standard SQL, though I guess postgres doesn't like it. The SQL should look like:

REPLACE INTO `ticketing_default` ('stub') VALUES (1)

I'll try to get postgres running somewhere and see if I can't get it to work with it.

Reply to this email directly or view it on GitHub: https://github.com/streeter/django-ticketing/issues/2#comment_1249125

[streeter]

Bummer. Looks like the only way to convert a MySQL REPLACE INTO statement is with a stored procedure. I'm not sure if adding a stored procedure is the way I want to go here. If you want to implement it, feel free to make a pull request and I'll add it.

Here are some more resources:

• http://stackoverflow.com/questions/3057598/replace-into-equivalent-for-postgresql-and-then-autoincrementing-an-int
• http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql

[streeter]

This requires Postgres to implement SQL MERGE. This was slated for Postgres 9.4, but missed the cut unfortunately.