Pipenv using old create.sql

[retr0h]

Expected Behavior

PQ.create() is idempotent

Actual Behavior

Subsequent calls to PQ.create() fails.

Steps to Reproduce the Problem

1. pip install pipenv
2. mkdir pq-pipenv && cd pq-pipenv
3. pipenv --python 3.7
4. pipenv install pq psycopg2-binary
5. pipenv run pip freeze
6. Create the following file test.py

from psycopg2 import connect
from pq import PQ

conn = connect('postgres://xx:xx@localhost:5432/xx')
pq = PQ(conn)
pq.create()

7. Execute the script more than once pipenv run python test.py.

[jodewey:~/pq-pipenv] % pipenv run python test.py
[jodewey:~/pq-pipenv] % pipenv run python test.py
Traceback (most recent call last):
  File "test.py", line 6, in <module>
    pq.create()
  File "/Users/jodewey/.local/share/virtualenvs/pq-pipenv-EeMTMI3R/lib/python3.7/site-packages/pq/__init__.py", line 63, in create
    cursor.execute(sql, {'name': Literal(queue.table)})
psycopg2.errors.DuplicateTable: relation "queue" already exists
CONTEXT:  SQL statement "CREATE TABLE queue (
  id          bigserial    PRIMARY KEY,
  enqueued_at timestamptz  NOT NULL DEFAULT current_timestamp,
  dequeued_at timestamptz,
  expected_at timestamptz,
  schedule_at timestamptz,
  q_name      text         NOT NULL CHECK (length(q_name) > 0),
  data        json         NOT NULL
)"
PL/pgSQL function inline_code_block line 3 at SQL statement

After further investigation the create.sql file in the virtualenv is old. It does not contain the version from 1.8.1.

[jodewey:~/pq-pipenv] % cat /Users/jodewey/.local/share/virtualenvs/pq-pipenv-EeMTMI3R/lib/python3.7/site-packages/pq/create.sql
do $$ begin

CREATE TABLE %(name)s (
  id          bigserial    PRIMARY KEY,
  enqueued_at timestamptz  NOT NULL DEFAULT current_timestamp,
  dequeued_at timestamptz,
  expected_at timestamptz,
  schedule_at timestamptz,
  q_name      text         NOT NULL CHECK (length(q_name) > 0),
  data        json         NOT NULL
);

end $$ language plpgsql;

create index priority_idx_%(name)s on %(name)s
    (schedule_at nulls first, expected_at nulls first, q_name)
    where dequeued_at is null
          and q_name = '%(name)s';

create index priority_idx_no_%(name)s on %(name)s
    (schedule_at nulls first, expected_at nulls first, q_name)
    where dequeued_at is null
          and q_name != '%(name)s';

drop function if exists pq_notify() cascade;

create function pq_notify() returns trigger as $$ begin
  perform pg_notify(new.q_name, '');
  return null;
end $$ language plpgsql;

create trigger pq_insert
after insert on %(name)s
for each row
execute procedure pq_notify();

Specifications

• Version:

[jodewey:~/pq-pipenv] 1 % pipenv run pip freeze
pq==1.8.1
psycopg2-binary==2.8.5

• Python version:

[jodewey:~/pq-pipenv] % python --version
Python 3.7.6

[retr0h]

Actually, this looks to be a problem with pip install in general.

[jodewey:~] % mkdir foo
[jodewey:~] % cd foo
[jodewey:~/foo] % virtualenv --no-site-packages .venv
Using base prefix '/Users/jodewey/.pyenv/versions/3.7.6'
New python executable in /Users/jodewey/foo/.venv/bin/python3.7
Also creating executable in /Users/jodewey/foo/.venv/bin/python
Installing setuptools, pip, wheel...
done.
[jodewey:~/foo] % source .venv/bin/activate
[jodewey:~/foo] % pip install pq
Processing /Users/jodewey/Library/Caches/pip/wheels/bb/9a/57/391012c0c104963cc3c6575f90af34d951eb5b123179bfe9c9/pq-1.8.1-py3-none-any.whl
Installing collected packages: pq
Successfully installed pq-1.8.1
[jodewey:~/foo] % find . -name "create.sql"
./.venv/lib/python3.7/site-packages/pq/create.sql
[jodewey:~/foo] % cat ./.venv/lib/python3.7/site-packages/pq/create.sql
do $$ begin

CREATE TABLE %(name)s (
  id          bigserial    PRIMARY KEY,
  enqueued_at timestamptz  NOT NULL DEFAULT current_timestamp,
  dequeued_at timestamptz,
  expected_at timestamptz,
  schedule_at timestamptz,
  q_name      text         NOT NULL CHECK (length(q_name) > 0),
  data        json         NOT NULL
);

end $$ language plpgsql;

create index priority_idx_%(name)s on %(name)s
    (schedule_at nulls first, expected_at nulls first, q_name)
    where dequeued_at is null
          and q_name = '%(name)s';

create index priority_idx_no_%(name)s on %(name)s
    (schedule_at nulls first, expected_at nulls first, q_name)
    where dequeued_at is null
          and q_name != '%(name)s';

drop function if exists pq_notify() cascade;

create function pq_notify() returns trigger as $$ begin
  perform pg_notify(new.q_name, '');
  return null;
end $$ language plpgsql;

create trigger pq_insert
after insert on %(name)s
for each row
execute procedure pq_notify();

[stas]

@retr0h, could you please help us understand what exactly is the issue here?

The error you're getting looks pretty straight-forward:

psycopg2.errors.DuplicateTable: relation "queue" already exists

I recently had to set up pq with django and there's a simple way to work around this: https://github.com/occrp/id-backend/commit/0e0b5f9421996eb4b2f93aaf5f84511baf2031b8#diff-1066dd485a7c75c339cc3bd577b45328

[retr0h]

@stas The issue is the create.sql file in the pypi package 1.8.1, does not contain the same contents in the pq repo's master branch.

For some reason when the author tags the repo and creates a release, the create.sql is not being updated.

create.sql:

• 1.8.1.
• master

The file in master branch has conditions in the sql create table if not exists %(name)s, where this schema file would be safe to run multiple times.

The error you're getting looks pretty straight-forward:

psycopg2.errors.DuplicateTable: relation "queue" already exists

I recently had to set up pq with django and there's a simple way to work around this: occrp/id-backend@0e0b5f9#diff-1066dd485a7c75c339cc3bd577b45328

You are working around the very problem I am discussing in this issue.

[stas]

@malthe do you think you can release a new version, because it looks like the tag v1.8.1 is not including the latest changes in the SQL file the @retr0h is mentioning.

@retr0h, the tag v1.8.1 actually does not include the changes, so I think we should just release the master under v1.8.2 and it should solve the issue: https://github.com/malthe/pq/blob/1.8.1/pq/create.sql

Thanks for being patient with us :bow: :see_no_evil:

[malthe]

I have released 1.8.2. Sorry for the wait!