Thread: syntax error with v12

syntax error with v12

From
Ivan Kabaivanov
Date:
Hi, everyone.

First time poster here.

This snippet (from ovirt-engine-dwh) used to work fine with postgresql-11.x, but emits a syntax error with postgresql-12.0:

CREATE TABLE history_configuration
(
var_name VARCHAR(50) NOT NULL,
var_value VARCHAR(255),
var_datetime TIMESTAMP WITH TIME ZONE,
CONSTRAINT PK_history_configuration PRIMARY KEY(var_name)
) WITH OIDS;

psql:/usr/share/ovirt-engine-dwh/dbscripts/create_tables.sql:7: ERROR:  syntax error at or near "OIDS"
LINE 7: ) WITH OIDS;

Is what I provided sufficient to troubleshoot the issue?  Let me know what else must be necessary for me to add.

Thanks,
IvanK.

Re: syntax error with v12

From
Josef Šimánek
Date:
Hello, I think this change is mentioned in release notes https://www.postgresql.org/docs/release/12.0/.

čt 10. 10. 2019 v 14:51 odesílatel Ivan Kabaivanov <ivan.kabaivanov@gmail.com> napsal:
Hi, everyone.

First time poster here.

This snippet (from ovirt-engine-dwh) used to work fine with postgresql-11.x, but emits a syntax error with postgresql-12.0:

CREATE TABLE history_configuration
(
var_name VARCHAR(50) NOT NULL,
var_value VARCHAR(255),
var_datetime TIMESTAMP WITH TIME ZONE,
CONSTRAINT PK_history_configuration PRIMARY KEY(var_name)
) WITH OIDS;

psql:/usr/share/ovirt-engine-dwh/dbscripts/create_tables.sql:7: ERROR:  syntax error at or near "OIDS"
LINE 7: ) WITH OIDS;

Is what I provided sufficient to troubleshoot the issue?  Let me know what else must be necessary for me to add.

Thanks,
IvanK.

Re: syntax error with v12

From
Félix GERZAGUET
Date:
Hi,

On Thu, Oct 10, 2019 at 2:51 PM Ivan Kabaivanov <ivan.kabaivanov@gmail.com> wrote:
psql:/usr/share/ovirt-engine-dwh/dbscripts/create_tables.sql:7: ERROR:  syntax error at or near "OIDS"
LINE 7: ) WITH OIDS;

Indeed, OID were removed from 12 release as described in:


You must adjust the statement.

--
Félix

Re: syntax error with v12

From
Ivan Kabaivanov
Date:
Thanks!

ivanK.

On Thu, Oct 10, 2019 at 3:56 PM Félix GERZAGUET <felix.gerzaguet@gmail.com> wrote:
Hi,

On Thu, Oct 10, 2019 at 2:51 PM Ivan Kabaivanov <ivan.kabaivanov@gmail.com> wrote:
psql:/usr/share/ovirt-engine-dwh/dbscripts/create_tables.sql:7: ERROR:  syntax error at or near "OIDS"
LINE 7: ) WITH OIDS;

Indeed, OID were removed from 12 release as described in:


You must adjust the statement.

--
Félix

Re: syntax error with v12

From
Adrian Klaver
Date:
On 10/10/19 5:51 AM, Ivan Kabaivanov wrote:
> Hi, everyone.
> 
> First time poster here.
> 
> This snippet (from ovirt-engine-dwh) used to work fine with 
> postgresql-11.x, but emits a syntax error with postgresql-12.0:
> 
> CREATETABLEhistory_configuration
> (
> var_name VARCHAR(50) NOTNULL,
> var_value VARCHAR(255),
> var_datetime TIMESTAMPWITHTIME ZONE,
> CONSTRAINT PK_history_configuration PRIMARY KEY(var_name)
> ) WITHOIDS;
> 
> psql:/usr/share/ovirt-engine-dwh/dbscripts/create_tables.sql:7: ERROR: 
>   syntax error at or near "OIDS"
> LINE 7: ) WITH OIDS;
> 
> Is what I provided sufficient to troubleshoot the issue?  Let me know 
> what else must be necessary for me to add.

https://www.postgresql.org/docs/12/release-12.html#id-1.11.6.5.3

"

Remove the special behavior of oid columns (Andres Freund, John Naylor)

Previously, a normally-invisible oid column could be specified during 
table creation using WITH OIDS; that ability has been removed. Columns 
can still be explicitly declared as type oid. Operations on tables that 
have columns created using WITH OIDS will need adjustment.

The system catalogs that previously had hidden oid columns now have 
ordinary oid columns. Hence, SELECT * will now output those columns, 
whereas previously they would be displayed only if selected explicitly.
"

> 
> Thanks,
> IvanK.


-- 
Adrian Klaver
adrian.klaver@aklaver.com