current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them, it only takes a minute:
up vote 0 down vote favorite

I've been writing my own PHP model system that offers basic ORM and DBAL support as merely a learning lesson. It allowed me to write things I've never done before and use databases like SQLite and PostgreSQL that I've never used.

Model package: https://github.com/titon/Model PgSQL package: https://github.com/titon/Model.PostgreSQL

The problem I am having is unit testing the PgSQL driver (I have MySQL and SQLite working). Any time a unit test is run, I receive the following error:

PDOException : SQLSTATE[55000]: Object not in prerequisite state: 7 ERROR:  currval of sequence "users_id_seq" is not yet defined in this session

I know what this problem is, but nothing I seem to do fixes it. It doesn't even reach my unit tests, it fails on the fixture setup portion. Here are the steps taken during each unit test:

  1. Driver is initialized and connected during setUp()
  2. Unit test method is called
  3. Fixtures are loaded
    1. CREATE TABLE statement is called
    2. CREATE INDEX statements are called
    3. INSERT records are called
  4. Unit test is ran
  5. tearDown() is called which unloads fixtures (DROP TABLE and INDEX) and disconnects the driver

The error occurs during step 3.3 while inserting the fixture records. It inserts the 1st record (out of 10) and fails trying to fetch the last insert ID (the sequence error above). When I check PGAdmin, the tables and the sequences DO exist, so I am confused.

  • Are the sequences not fully created yet by the time the inserts are being called?
  • Does my connection session die too early?
  • How can I solve this?

I'm very new to PgSQL so any help would be appreciated. For the record, the code is using PHP PDO and calls lastInsertId('table_column_seq') to retrieve the last ID.

Here's the fixture setUp SQL if you are interested.

CREATE TABLE IF NOT EXISTS "users" (
    "id" serial NOT NULL,
    "country_id" integer NULL DEFAULT NULL,
    "username" varchar(255) NULL,
    "password" varchar(255) NULL,
    "email" varchar(255) NULL,
    "firstName" varchar(255) NULL,
    "lastName" varchar(255) NULL,
    "age" smallint NULL,
    "created" timestamp NULL DEFAULT NULL,
    "modified" timestamp NULL DEFAULT NULL,
    UNIQUE ("username")
);
CREATE INDEX "country_id" ON "users" ("country_id");
INSERT INTO "users" ("id", "country_id", "username", "firstName", "lastName", "password", "email", "age", "created") VALUES ('1', 1, 'miles', 'Miles', 'Johnson', '1Z5895jf72yL77h', '[email protected]', 25, '1988-02-26 21:22:34');
# Fails right after this insert
share|improve this question
    
If you're going to use a sequence (a SERIAL data type), why are you passing a literal ID in the INSERT statement? And why are you passing that literal as a string instead of as an integer? Why is there no primary key? Why is username unique, but nullable? – Mike Sherrill 'Cat Recall' Jul 13 '13 at 21:30
    
The fixtures are basically simple data with random schemas and types littered around, some of which don't make sense in a literal prod setup. The fixtures are also shared between MySQL, PgSQL, SQLite and MongoDB, so there are some quirks getting everything working correctly. Also the statement outputs aren't an exact match since the PDO statements can't be seen with their bound values. These are simply auto generated. – Miles Johnson Jul 14 '13 at 10:09

1 Answer 1

up vote 2 down vote accepted

Don't guess, know. Turn statement logging on and see what it does.

Then you'll probably see that the lastInserId() call is fetching the last id from the sequence on id. There isn't one because you've provided your own value. Stop doing that and it should work.

share|improve this answer
    
Ahh such a simple oversight. The fixtures are used for MySQL, SQLite, PgSQL and MongoDB, so the IDs are hard-coded. How would you suggest writing the unit tests then if I need to connect relational records? – Miles Johnson Jul 14 '13 at 3:01
    
If you're not generating IDs in the database, why fetch last-insert-id at all? I can't quite see how that would make sense. You can of course return the IDs via a RETURNING clause on the INSERT if you really need to know what value you just provided to the database. – Richard Huxton Jul 14 '13 at 14:51
    
Well I insert the fixture records which are hardcoded, but a lot of unit tests insert more rows or delete, so it was easier to hardcode the IDs. But assuming I remove the IDs they all should insert in the correct order still. – Miles Johnson Jul 14 '13 at 20:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.