PostgreSQL 9.6: view based on sequence

2018-01-24 12:52 GMT+01:00 Enrico Pirozzi <e.pirozzi@nbsgroup.it>:

Hi, I was doing a test on a 9.6 and I've seen a strange thing

if I run

create sequence test_sequence;
create view v_test_sequence as select * from test_sequence;

PostgreSQL creates a view based on the sequence and the strange thing is
that, among the various fields that are created in the view,
there is a field named sequence_name with datatype name.

I've seen in the docs
https://www.postgresql.org/docs/9.6/static/datatype-character.html
and the datatype name should be reserved only for structures internal of PG.

Does anyone help me?

Enrico

--
Enrico Pirozzi
e.pirozzi@nbsgroup.it

tmp1=# create sequence x;
CREATE SEQUENCE
Time: 69,857 ms
tmp1=# select * from x;
last_value | log_cnt | is_called
------------+---------+-----------
1 | 0 | f
(1 row)

Maybe what you want to do is

create view v_test_sequence as select nextval( 'test_sequence' );

Ciao.

--
Vincenzo Romano - NotOrAnd.IT
Information Technologies
--
NON QVIETIS MARIBVS NAVTA PERITVS

tmp1=# create sequence x;
CREATE SEQUENCE
Time: 69,857 ms
tmp1=# select * from x;
last_value | log_cnt | is_called
------------+---------+-----------
1 | 0 | f
(1 row)

Maybe what you want to do is

create view v_test_sequence as select nextval( 'test_sequence' );

Ciao.

Yes but this appens on a 10.x version :)

Ciao
--

Enrico Pirozzi
e.pirozzi@nbsgroup.it

tmp1=# create sequence x;

CREATE SEQUENCE
Time: 69,857 ms
tmp1=# select * from x;
last_value | log_cnt | is_called
------------+---------+-----------
1 | 0 | f
(1 row)

Maybe what you want to do is

create view v_test_sequence as select nextval( 'test_sequence' );

Ciao.

Yes but this happens on a 10.x version on a 9.6 version things are
differents

Ciao
Enrico

--
Enrico Pirozzi
e.pirozzi@nbsgroup.it

2018-01-24 13:02 GMT+01:00 Enrico Pirozzi <e.pirozzi@nbsgroup.it>:

tmp1=# create sequence x;
CREATE SEQUENCE
Time: 69,857 ms
tmp1=# select * from x;
last_value | log_cnt | is_called
------------+---------+-----------
1 | 0 | f
(1 row)

Maybe what you want to do is

create view v_test_sequence as select nextval( 'test_sequence' );

Ciao.

Yes but this appens on a 10.x version :)

Ciao
--

Enrico Pirozzi
e.pirozzi@nbsgroup.it

I haven't any v9 any more in my deployments. Sorry.

What is the objective of that view?
If it's to hide the nextval() function, then the solution can be
similar to mine.
If it's to access the sequence metainfo, then maybe it'd be better to
explore the pg_catalog schema.

--
Vincenzo Romano - NotOrAnd.IT
Information Technologies
--
NON QVIETIS MARIBVS NAVTA PERITVS

it's just to know why postgresql does not return a warning.

Il 24/01/2018 13:06, Vincenzo Romano ha scritto:

I haven't any v9 any more in my deployments. Sorry.

What is the objective of that view?
If it's to hide the nextval() function, then the solution can be
similar to mine.
If it's to access the sequence metainfo, then maybe it'd be better to
explore the pg_catalog schema.

--
Enrico Pirozzi
e.pirozzi@nbsgroup.it

2018-01-24 13:10 GMT+01:00 Enrico Pirozzi <e.pirozzi@nbsgroup.it>:

it's just to know why postgresql does not return a warning.

Il 24/01/2018 13:06, Vincenzo Romano ha scritto:

I haven't any v9 any more in my deployments. Sorry.

What is the objective of that view?
If it's to hide the nextval() function, then the solution can be
similar to mine.
If it's to access the sequence metainfo, then maybe it'd be better to
explore the pg_catalog schema.

--
Enrico Pirozzi
e.pirozzi@nbsgroup.it

Those queries are all legal. No warning is due.

--
Vincenzo Romano - NotOrAnd.IT
Information Technologies
--
NON QVIETIS MARIBVS NAVTA PERITVS

Yes the query is legal,but if you use pg_upgrade to upgrade from 9.6 to
10.x , pg_upgrade will fail .

Enrico

Il 24/01/2018 13:11, Vincenzo Romano ha scritto:

Those queries are all legal. No warning is due.

--
Enrico Pirozzi
e.pirozzi@nbsgroup.it

2018-01-24 14:52 GMT+01:00 Enrico Pirozzi <e.pirozzi@nbsgroup.it>:

Yes the query is legal,but if you use pg_upgrade to upgrade from 9.6 to 10.x
, pg_upgrade will fail .

Enrico

Il 24/01/2018 13:11, Vincenzo Romano ha scritto:

Those queries are all legal. No warning is due.

--
Enrico Pirozzi
e.pirozzi@nbsgroup.it

It is all documented here:
https://www.postgresql.org/docs/10/static/release-10.html#idm46428658049888

"...selecting from a sequence relation now returns only the three
fields named above."

And there's no way to keep portability from 9.x to 10.x

--
Vincenzo Romano - NotOrAnd.IT
Information Technologies
--
NON QVIETIS MARIBVS NAVTA PERITVS

It is all documented here:
https://www.postgresql.org/docs/10/static/release-10.html#idm46428658049888

"...selecting from a sequence relation now returns only the three
fields named above."

And there's no way to keep portability from 9.x to 10.x

Thanks ;)

Enrico

--
Enrico Pirozzi
e.pirozzi@nbsgroup.it

On 1/24/18 06:52, Enrico Pirozzi wrote:

PostgreSQL creates a view based on the sequence and the strange thing
is that, among the various fields that are created in the view,
there is a field named sequence_name with datatype name.

I've seen in the docs
https://www.postgresql.org/docs/9.6/static/datatype-character.html
and the datatype name should be reserved only for structures internal of PG.

There is nothing from with user code using the data type "name". It's
probably just not the best choice. But if you create a view over a
system table, then you'll get whatever types the system table uses.
There is nothing from with that.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

I found this db, it's not mine

Thanks ;)

Enrico

Il 25/01/2018 18:07, Peter Eisentraut ha scritto:

There is nothing from with user code using the data type "name". It's
probably just not the best choice. But if you create a view over a
system table, then you'll get whatever types the system table uses.
There is nothing from with that.

--
Enrico Pirozzi
e.pirozzi@nbsgroup.it