Thread: PostgreSQL 9.6: view based on sequence
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
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