PostgreSQL using sequences issue

[morrigan]

Hi,
this is the issue I am facing when running migrations on production. Any tips? Using Phalcon DevTools (2.0.13).

Error:

CREATE TABLE "public"."images" (
  "id" BIGSERIAL DEFAULT "nextval('images_id_seq'::regclass)" NOT NULL,
  "base64" TEXT
);

ERROR: SQLSTATE[42601]: Syntax error: 7 ERROR:
multiple default values specified for column "id" of table "images"

Generated migration file:

<?php

use Phalcon\Db\Column;
use Phalcon\Db\Index;
use Phalcon\Db\Reference;
use Phalcon\Mvc\Model\Migration;

/**
 * Class ImagesMigration_101
 */
class ImagesMigration_101 extends Migration
{
    /**
     * Define the table structure
     *
     * @return void
     */
    public function morph()
    {
        $this->morphTable('images', array(
                'columns' => array(
                    new Column(
                        'id',
                        array(
                            'type' => Column::TYPE_BIGINTEGER,
                            'default' => "nextval('images_id_seq'::regclass)",
                            'notNull' => true,
                            'autoIncrement' => true,
                            'first' => true
                        )
                    ),
                    new Column(
                        'base64',
                        array(
                            'type' => Column::TYPE_TEXT,
                            'size' => 1,
                            'after' => 'id'
                        )
                    )
                ),
                'indexes' => array(
                    new Index('pk_image_id', array('id'), null)
                ),
            )
        );
    }

    /**
     * Run the migrations
     *
     * @return void
     */
    public function up()
    {

    }

    /**
     * Reverse the migrations
     *
     * @return void
     */
    public function down()
    {

    }

}

[sergeyklay]

Hi @morrigan,

Thanks for contributing!
It seems this is Phalcon issue. Correct SQL is:

CREATE TABLE "public"."images" (
  -- NOTE:
  -- Without > DEFAULT "nextval('images_id_seq'::regclass)" NOT NULL
  "id" BIGSERIAL,
  "base64" TEXT
);

Or:

CREATE TABLE "public"."images" (
  -- NOTE:
  -- Without > DEFAULT "nextval('images_id_seq'::regclass)"
  "id" BIGSERIAL NOT NULL,
  "base64" TEXT
);

as workaround try to comment

'default' => "nextval('images_id_seq'::regclass)",

new Column(
    'id',
    array(
        'type' => Column::TYPE_BIGINTEGER,
        // 'default' => "nextval('images_id_seq'::regclass)",
        'notNull' => true,
        'autoIncrement' => true,
        'first' => true
    )
)

[morrigan]

@sergeyklay Yeah, I don't get the error when default propery is removed and seems like auto incrementation still works :)

[sergeyklay]

@morrigan I'll fix this ASAP

[sergeyklay]

@morrigan
I've fixed Phalcon issue in the Phalcon 3.0.x branch. So now migration must be created in the right way.

Could you please check the Phalcon 3.0.x branch?

git clone [email protected]:phalcon/cphalcon.git
cd cphalcon
git checkout 3.0.x

zephir fullclean
zephir build

[sergeyklay]

I've added hotfix in Devtools 3.0.x branch #864