Re: retroactive pg10 relnotes: sequence changes

[Andres Freund]

On 2018-08-28 19:02:06 +0200, Magnus Hagander wrote:
> Looks reasonable to me. And I definitely think we should do it -- people
> will be upgrading to 10 for years to come, so claiming it's too late is
> definitely not correct.

Please make sure to backpatch it to all branches carrying v10 release
notes...

Greetings,

Andres Freund

Re: retroactive pg10 relnotes: sequence changes

[Jonathan S. Katz]

On Aug 28, 2018, at 1:09 PM, Jonathan S. Katz  wrote:On Aug 28, 2018, at 1:02 PM, Magnus Hagander  wrote:On Tue, Aug 28, 2018 at 6:34 PM, Alvaro Herrera  wrote:HelloA customer of ours was taken by surprise by a change in Postgres 10 on atrial upgrade from 9.6.  They were using sequences from SERIAL columns alittle unorthodoxly, and their stuff stopped working: essentially, theyhacked the default _expression_ so that it'd automatically use negativenumbers when the sequence reached INT_MAX.  Since pg10 changed sequencesto stop emitting values at that point, it raised an error rather thanemit the negative numbers.(In 9.6 and prior, the sequence would emit values past INT_MAX; it wasthe column that raised the error.  In pg10 things were changed so thatit is now the sequence that raises the error.)My proposal now is to document this issue in the Postgres 10 releasenotes.  "It's a little late for that!" I hear you say, but keep this inmind: many users have *not* yet upgraded to 10, and they'll keep doingit for years to come still.  So I disagree that now is too late.  Wefailed to warn people that already upgraded, but we're still on time toalert people yet to upgrade.I attach both the patch and a screenshot to show how minor the visualeffect of the change is.(If people hate this, another option is to make it a separate bulletpoint.)Looks reasonable to me. And I definitely think we should do it -- people will be upgrading to 10 for years to come, so claiming it's too late is definitely not correct. +1.I have attached patch where I suggested some alternate wording andremove the parenthetical comment, as I don’t believe that should bean aside.Per off-list discussion from Bruce, re-attaching the patch. Apparentlyit was only available in HTML mimepart. Hopefully this gets it intothe archives.Jonathan

sequences-10-v2.patch
Description: Binary data


signature.asc
Description: Message signed with OpenPGP

Re: retroactive pg10 relnotes: sequence changes

[Jonathan S. Katz]

On Aug 28, 2018, at 1:02 PM, Magnus Hagander  wrote:On Tue, Aug 28, 2018 at 6:34 PM, Alvaro Herrera  wrote:HelloA customer of ours was taken by surprise by a change in Postgres 10 on atrial upgrade from 9.6.  They were using sequences from SERIAL columns alittle unorthodoxly, and their stuff stopped working: essentially, theyhacked the default _expression_ so that it'd automatically use negativenumbers when the sequence reached INT_MAX.  Since pg10 changed sequencesto stop emitting values at that point, it raised an error rather thanemit the negative numbers.(In 9.6 and prior, the sequence would emit values past INT_MAX; it wasthe column that raised the error.  In pg10 things were changed so thatit is now the sequence that raises the error.)My proposal now is to document this issue in the Postgres 10 releasenotes.  "It's a little late for that!" I hear you say, but keep this inmind: many users have *not* yet upgraded to 10, and they'll keep doingit for years to come still.  So I disagree that now is too late.  Wefailed to warn people that already upgraded, but we're still on time toalert people yet to upgrade.I attach both the patch and a screenshot to show how minor the visualeffect of the change is.(If people hate this, another option is to make it a separate bulletpoint.)Looks reasonable to me. And I definitely think we should do it -- people will be upgrading to 10 for years to come, so claiming it's too late is definitely not correct. +1.I have attached patch where I suggested some alternate wording andremove the parenthetical comment, as I don’t believe that should bean aside.Jonathan

sequences-10.patch
Description: Binary data


signature.asc
Description: Message signed with OpenPGP

Re: retroactive pg10 relnotes: sequence changes

[Magnus Hagander]

On Tue, Aug 28, 2018 at 6:34 PM, Alvaro Herrera 
wrote:

> Hello
>
> A customer of ours was taken by surprise by a change in Postgres 10 on a
> trial upgrade from 9.6.  They were using sequences from SERIAL columns a
> little unorthodoxly, and their stuff stopped working: essentially, they
> hacked the default expression so that it'd automatically use negative
> numbers when the sequence reached INT_MAX.  Since pg10 changed sequences
> to stop emitting values at that point, it raised an error rather than
> emit the negative numbers.
>
> (In 9.6 and prior, the sequence would emit values past INT_MAX; it was
> the column that raised the error.  In pg10 things were changed so that
> it is now the sequence that raises the error.)
>
> My proposal now is to document this issue in the Postgres 10 release
> notes.  "It's a little late for that!" I hear you say, but keep this in
> mind: many users have *not* yet upgraded to 10, and they'll keep doing
> it for years to come still.  So I disagree that now is too late.  We
> failed to warn people that already upgraded, but we're still on time to
> alert people yet to upgrade.
>
> I attach both the patch and a screenshot to show how minor the visual
> effect of the change is.
>
> (If people hate this, another option is to make it a separate bullet
> point.)
>

Looks reasonable to me. And I definitely think we should do it -- people
will be upgrading to 10 for years to come, so claiming it's too late is
definitely not correct.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

retroactive pg10 relnotes: sequence changes

[Alvaro Herrera]

Hello

A customer of ours was taken by surprise by a change in Postgres 10 on a
trial upgrade from 9.6.  They were using sequences from SERIAL columns a
little unorthodoxly, and their stuff stopped working: essentially, they
hacked the default expression so that it'd automatically use negative
numbers when the sequence reached INT_MAX.  Since pg10 changed sequences
to stop emitting values at that point, it raised an error rather than
emit the negative numbers.

(In 9.6 and prior, the sequence would emit values past INT_MAX; it was
the column that raised the error.  In pg10 things were changed so that
it is now the sequence that raises the error.)

My proposal now is to document this issue in the Postgres 10 release
notes.  "It's a little late for that!" I hear you say, but keep this in
mind: many users have *not* yet upgraded to 10, and they'll keep doing
it for years to come still.  So I disagree that now is too late.  We
failed to warn people that already upgraded, but we're still on time to
alert people yet to upgrade.

I attach both the patch and a screenshot to show how minor the visual
effect of the change is.

(If people hate this, another option is to make it a separate bullet
point.)

-- 
Álvaro Herrera
diff --git a/doc/src/sgml/release-10.sgml b/doc/src/sgml/release-10.sgml
index 968ed866c2..f1dc816278 100644
--- a/doc/src/sgml/release-10.sgml
+++ b/doc/src/sgml/release-10.sgml
@@ -4717,6 +4717,13 @@ Branch: REL_10_STABLE [5159626af] 2017-11-03 14:14:16 
-0400
  
 
  
+  Also, sequences created for SERIAL columns now
+  generate positive 32-bit wide values rather than 64-bit, as they
+  used to.  (This has no visible effect if the values are just stored in
+  the column.)
+ 
+
+ 
   The output of psql's \d 
command for a
   sequence has been redesigned, too.
  

Re: Limitation of prepared statement name

[Tom Lane]

Flavio Henrique Araque Gurgel  writes:
> It seems that naming prepared statements is limited to 63 characters as per
> https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

Yeah, just like every other SQL identifier in Postgres...

> The dangerous situation is that PostgreSQL seems to ignore rigthmost
> overflowed characters silently.

It's hardly silent:

regression=# prepare 
foo1234567890123456789012345678901234567890123456789012345678901234567890 as 
select 1;
NOTICE:  identifier 
"foo1234567890123456789012345678901234567890123456789012345678901234567890" 
will be truncated to 
"foo123456789012345678901234567890123456789012345678901234567890"
PREPARE

> Wouldn't it be good to include that information on the prepared statement
> doc page?

You could as well argue that every single page that describes creating
any sort of object ought to repeat this information.  I doubt that people
would find that to be an improvement.

regards, tom lane

Re: Error when following a documentation

[David G. Johnston]

On Tuesday, August 28, 2018, PG Doc comments form 
wrote:

>
> postgres=# DECLARE
> postgres-# key integer;
> ERROR:  syntax error at or near "integer"
> LINE 2: key integer;
> ^
>

You are writing pl/psql language code at the SQL input prompt.  That won't
work.  You have to either use the SQL DO command or write a function.

David J.

Error when following a documentation

[PG Doc comments form]

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/10/static/plpgsql-cursors.html
Description:

As specified On page
https://www.postgresql.org/docs/10/static/plpgsql-cursors.html#PLPGSQL-CURSOR-OPENING
i am trying to run below commands 
-
DECLARE
key integer;
curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key;
BEGIN
key := 42;
OPEN curs4;

but i am getting below errors, please help

postgres=# 
postgres=# SELECT VERSION();
 version


-

 PostgreSQL 10.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7
20120313 (Red Hat 4.
4.7-18), 64-bit
(1 row)

postgres=# DECLARE
postgres-# key integer;
ERROR:  syntax error at or near "integer"
LINE 2: key integer;
^
postgres=# curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key;
ERROR:  syntax error at or near "curs4"
LINE 1: curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key;
^
postgres=# BEGIN
postgres-# key := 42;
ERROR:  syntax error at or near "key"
LINE 2: key := 42;
^
postgres=# OPEN curs4;
ERROR:  syntax error at or near "OPEN"
LINE 1: OPEN curs4;
^
postgres=# 

Limitation of prepared statement name

[Flavio Henrique Araque Gurgel]

Hello all

I just found a limitation in prepared statements names that I didn't know
before and can lead applications to be difficult to debug.

It seems that naming prepared statements is limited to 63 characters as per
https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

However, the documentation for PREPARE says nothing about it
https://www.postgresql.org/docs/10/static/sql-prepare.html

The dangerous situation is that PostgreSQL seems to ignore rigthmost
overflowed characters silently. If we try to prepare another statement with
the same 63 leftmost characters as the first one, we get an error of
duplicate prepared statement name.

Wouldn't it be good to include that information on the prepared statement
doc page? Maybe I should notify this as a general bug so we treat this at
the code level.

Best,
Flavio Gurgel