using PGSQL error in admin trying to open Product - tags, categories or attributes

[jeromeatneotek]

#0 C:\Domains\myweb\wwwroot\system\db\pgsql.php(291): pg_result_error()
#1 C:\Domains\myweb\wwwroot\storage\model\admin\categorysql.pgsql.php(1983): Vvveb\System\Db\Pgsql->execute()
#2 C:\Domains\myweb\wwwroot\admin\controller\content\tags.php(42): Vvveb\Sql\CategorySQL->getCategoriesAllLanguages()
#3 [internal function]: Vvveb\Controller\Content\Tags->index()
#4 C:\Domains\myweb\wwwroot\system\core\frontcontroller.php(229): call_user_func()
#5 C:\Domains\myweb\wwwroot\system\core\frontcontroller.php(298): Vvveb\System\Core\FrontController::call()
#6 C:\Domains\myweb\wwwroot\system\core\frontcontroller.php(364): Vvveb\System\Core\FrontController::redirect()
#7 C:\Domains\myweb\wwwroot\system\core\startup.php(355): Vvveb\System\Core\FrontController::dispatch()
#8 C:\Domains\myweb\wwwroot\index.php(138): Vvveb\System\Core\start()
#9 C:\Domains\myweb\wwwroot\admin\index.php(41): include('...')
#10 C:\Domains\myweb\wwwroot\public\admin\index.php(26): include('...')
#11 {main}

[givanz]

Thanks for the bug report.

This happens because pgsql uses sequences to generate id's unlike mysql or sqlite which uses the max/last inserted id.

The sample data from menus and categories is inserted with ids while the sequence starts with 1 and this causes primary id conflict.

I added setvalue for sequences to match the last insert id in /install/sql/pgsql/schema/

If you don't want to reinstall you can try this fix

https://wiki.postgresql.org/wiki/Fixing_Sequences

[jeromeatneotek]

Would it be fair to say, that I am likley to experience less issues if I were to use mysql, or specifically mariaDB, because that is what you are developing first?

[givanz]

Testing is done mostly on mysql but now with the latest fixes pgsql is also up to date.

[jeromeatneotek]

The code from the link https://wiki.postgresql.org/wiki/Fixing_Sequences didn't return any records! SO there was no updates to reset the sequence values.
I am an MS SQL developer, what kind of effort do you think it would require from me to port to MS SQL?

[givanz]

It doesn't work for me either probably because sequences were not associated with tables with OWNED BY table.col

The first step is to write the driver /system/db/mssql.php, you need to implement methods like execute, fetch_row, get_all etc https://github.com/givanz/Vvveb/blob/master/system/db/pgsql.php

The second step is to add a new SqlP template /system/sqlp/mssql.php https://github.com/givanz/Vvveb/blob/master/system/sqlp/pgsql.php that provides the code snippets that generates the php code from sql files that calls the mssql driver code.

The third step is to convert the sql code that creates the tables install/sql/mssql https://github.com/givanz/Vvveb/tree/master/install/sql/pgsql

And the last step is to convert the sql queries admin/sql/mssql and app/sql/mssql https://github.com/givanz/Vvveb/tree/master/admin/sql/pgsql

Most sql code is ansi compatible with very small differences between mysql,sqlite and pgsql probably it will work on mssql also with very little changes.