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 git@github.com: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