search

jfischoff / postgresql-queue

A PostgreSQL backed queue
BSD 3-Clause "New" or "Revised" License
16 stars 3 forks source link

Migrate fails when trying to create second job queue #19

Open niteria opened 4 years ago

niteria commented 4 years ago

Hello,

Thank you for the library, I needed to create something similar myself, so it saved me some time.

Here's the reproduction steps, running on a fresh database:

*SpotCheck> conn <- connectPostgreSQL "dbname='test' user='niteria'"
*SpotCheck> Queue.migrate "queue1" conn
NOTICE:  trigger "payloads_modified" for relation "payloads" does not exist, skipping
*SpotCheck> Queue.migrate "queue2" conn
*** Exception: SqlError {sqlState = "42704", sqlExecStatus = FatalError, sqlErrorMsg = "type \"state_t\" does not exist", sqlErrorDetail = "", sqlErrorHint = ""}

I'm running a version from Hackage, postgresql-simple-queue-1.0.1 to be precise.

I'm not an expert on PostgreSQL, so I googled a bit and found this: https://stackoverflow.com/a/56647928

It seems to suggest that the types are scoped to the schema and this part of the migration statement SELECT 1 FROM pg_type WHERE typname = 'state_t' ignores the scope. The correct query seems to be SELECT 1 FROM pg_type typ INNER JOIN pg_namespace nsp ON nsp.oid = typ.typnamespace WHERE nsp.nspname = current_schema() AND typ.typname = 'state_t' as per the StackOverflow answer.

niteria commented 4 years ago

I copied over the module Database.PostgreSQL.Simple.Queue.Migrate to my project and applied the fix and it worked.

I'd send a pull request, but master seems to be substantially different from Hackage.

jfischoff commented 4 years ago

Thanks for the bug report. I am working a new postgres based queue using hasql instead of postgresql-simple but I will try to get a maintenance release out on this at some point.