Обсуждение: Odd behavior with 'currval'

I have a body of code using JDBC to work with a PostgreSQL 9.6 database. 
All tables use 'SERIAL' or 'BIGSERIAL' types to generate ids.  All are 
working correctly in terms of using the next value as a default. 
However, reading back the most recently applied (currval) value is failing 
for one table, where it always returns '0'. Note that the table data shows 
the expected value when queried by SELECT!  It is only the currval() 
function that is wrong.  I am properly guarding for SQL exceptions and 
none are being thrown.

The code being used in the failing case is not the slightest bit different 
from the working cases in terms of structure and transaction control - 
only the SQL, column count, etc. is different (but correctly formed and in 
all other ways functional).

I'm not sure where to start debugging this.  Can anyone give me even a 
working theory to explain how returning a bogus value is possible?  When I 
look at the sequences in pgAdmin, they are as expected in terms of 
ownership, etc.  And, again, the table IS getting the correct value.

Thanks much for any ideas!

-- 

On 02/08/2018 08:09 AM, Steven Hirsch wrote:
> I have a body of code using JDBC to work with a PostgreSQL 9.6 database. 
> All tables use 'SERIAL' or 'BIGSERIAL' types to generate ids.  All are 
> working correctly in terms of using the next value as a default. 
> However, reading back the most recently applied (currval) value is 
> failing for one table, where it always returns '0'. Note that the table 
> data shows the expected value when queried by SELECT!  It is only the 
> currval() function that is wrong.  I am properly guarding for SQL 
> exceptions and none are being thrown.
> 
> The code being used in the failing case is not the slightest bit 
> different from the working cases in terms of structure and transaction 
> control - only the SQL, column count, etc. is different (but correctly 
> formed and in all other ways functional).

Can we see the table schema and the currval code?

Is there anything in the Postgres logs that might help?

> 
> I'm not sure where to start debugging this.  Can anyone give me even a 
> working theory to explain how returning a bogus value is possible?  When 
> I look at the sequences in pgAdmin, they are as expected in terms of 
> ownership, etc.  And, again, the table IS getting the correct value.
> 
> Thanks much for any ideas!
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, Feb 8, 2018 at 5:09 PM, Steven Hirsch <snhirsch@gmail.com> wrote:
> I have a body of code using JDBC to work with a PostgreSQL 9.6 database. All
......
> The code being used in the failing case is not the slightest bit different
> from the working cases in terms of structure and transaction control - only
> the SQL, column count, etc. is different (but correctly formed and in all
> other ways functional).

Something must be different. As requested by others, try posting the
SQL code chunks, more eyeballs make bugs shallower ( it's happened
several times to me, make a typo, go over it for half an hour, grab a
colleague, she immediately points to it )

> I'm not sure where to start debugging this.  Can anyone give me even a
> working theory to explain how returning a bogus value is possible?  When I
> look at the sequences in pgAdmin, they are as expected in terms of
> ownership, etc.  And, again, the table IS getting the correct value.

It shouldn't be, this I why several perople are requesting to see the
relevant code. Experience says lots of this fails are pilot error.

As an aside, with recent postgres versions you can normally use the
returning construct to grab autogenerated id. I.e., instead of "insert
blah-blah-blah, select currval(), whatever else" you can many times do
"insert balh-blah-blah returning auto_gen_column, whatever else". I've
used it a lot, and normally leads to shorter/easier/faster code.

Francisco Olarte.

On Thu, 8 Feb 2018, Francisco Olarte wrote:

> Something must be different. As requested by others, try posting the
> SQL code chunks, more eyeballs make bugs shallower ( it's happened
> several times to me, make a typo, go over it for half an hour, grab a
> colleague, she immediately points to it )

Fair enough.  Here is the DDL:

CREATE TABLE udm_asset_type_definition (
   def_id BIGSERIAL NOT NULL,
   def_name VARCHAR(32) NOT NULL,
   PRIMARY KEY (def_id)
);

When I look at the column definition, I see:

nextval('udm_asset_type_definition_def_id_seq'::regclass)

When I look at the catalog, I can see a sequence:

udm_asset_type_definition_def_id_seq

That appears identical to the column default definition and it has the 
expected 'last_value'.

Here's the odd part: If I issue

SELECT pg_get_serial_sequence('udm_asset_type_definition','def_id')

I get back NULL (doesn't matter if I qualify with schema - everything is 
in a schema called 'main' and that is first on the search path).  All 
other sequences in the database (created exactly the same way, through 
definition as 'BIGSERIAL' type) are properly found.

On a hunch, I tried 'SELECT currval(NULL)' to see if it returned '0', but 
that too returns NULL.  So, where is the '0' coming from when I do:

SELECT currval( pg_get_serial_sequence('udm_asset_type_definition','def_id'))

? I've already established that the inner expression evaluates to NULL!

> It shouldn't be, this I why several perople are requesting to see the
> relevant code. Experience says lots of this fails are pilot error.
>
> As an aside, with recent postgres versions you can normally use the
> returning construct to grab autogenerated id. I.e., instead of "insert
> blah-blah-blah, select currval(), whatever else" you can many times do
> "insert balh-blah-blah returning auto_gen_column, whatever else". I've
> used it a lot, and normally leads to shorter/easier/faster code.

Yes, I changed the code to 'INSERT .. RETURNING ..' and that works 
correctly.  But, again, not necessary for any of the other tables.

This problem is not a transient fluke - I can reproduce it in two 
different databases on different servers that were created with the same 
DDL.

-- 

On 02/08/2018 09:58 AM, Steven Hirsch wrote:
> On Thu, 8 Feb 2018, Francisco Olarte wrote:
> 
>> Something must be different. As requested by others, try posting the
>> SQL code chunks, more eyeballs make bugs shallower ( it's happened
>> several times to me, make a typo, go over it for half an hour, grab a
>> colleague, she immediately points to it )
> 
> Fair enough.  Here is the DDL:
> 
> CREATE TABLE udm_asset_type_definition (
>    def_id BIGSERIAL NOT NULL,
>    def_name VARCHAR(32) NOT NULL,
>    PRIMARY KEY (def_id)
> );
> 
> When I look at the column definition, I see:
> 
> nextval('udm_asset_type_definition_def_id_seq'::regclass)
> 
> When I look at the catalog, I can see a sequence:
> 
> udm_asset_type_definition_def_id_seq
> 
> That appears identical to the column default definition and it has the 
> expected 'last_value'.
> 
> Here's the odd part: If I issue
> 
> SELECT pg_get_serial_sequence('udm_asset_type_definition','def_id')

What if you do?:

SELECT * FROM udm_asset_type_definition_def_id_seq;

SELECT currval('udm_asset_type_definition_id_seq');


Also what happens if you do:

pg_dump -d db_name -U some_user -s  -t udm_asset_type_definition > out.sql

> 
> I get back NULL (doesn't matter if I qualify with schema - everything is 
> in a schema called 'main' and that is first on the search path).  All 
> other sequences in the database (created exactly the same way, through 
> definition as 'BIGSERIAL' type) are properly found.
> 
> On a hunch, I tried 'SELECT currval(NULL)' to see if it returned '0', 
> but that too returns NULL.  So, where is the '0' coming from when I do:
> 
> SELECT currval( 
> pg_get_serial_sequence('udm_asset_type_definition','def_id'))
> 
> ? I've already established that the inner expression evaluates to NULL!
> 
>> It shouldn't be, this I why several perople are requesting to see the
>> relevant code. Experience says lots of this fails are pilot error.
>>
>> As an aside, with recent postgres versions you can normally use the
>> returning construct to grab autogenerated id. I.e., instead of "insert
>> blah-blah-blah, select currval(), whatever else" you can many times do
>> "insert balh-blah-blah returning auto_gen_column, whatever else". I've
>> used it a lot, and normally leads to shorter/easier/faster code.
> 
> Yes, I changed the code to 'INSERT .. RETURNING ..' and that works 
> correctly.  But, again, not necessary for any of the other tables.
> 
> This problem is not a transient fluke - I can reproduce it in two 
> different databases on different servers that were created with the same 
> DDL.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, 8 Feb 2018, Adrian Klaver wrote:

> What if you do?:
>
> SELECT * FROM udm_asset_type_definition_def_id_seq;

I get:

udm_asset_type_definition_def_id_seq    21    1    1    9223372036854775807    1    1    32    false    true


> SELECT currval('udm_asset_type_definition_id_seq');

I get:

[Code: , SQL State: 42P01]  ERROR: relation 
"udm_asset_type_definition_id_seq" does not exist
   Position: 16

> Also what happens if you do:
>
> pg_dump -d db_name -U some_user -s  -t udm_asset_type_definition > out.sql

This:

--
-- Name: udm_asset_type_definition; Type: TABLE; Schema: main; Owner: 
asset_registry_admins
--

CREATE TABLE udm_asset_type_definition (
     def_id bigint DEFAULT nextval('udm_asset_type_definition_def_id_seq'::regclass) NOT NULL,
     def_name character varying(32) NOT NULL,
);


ALTER TABLE udm_asset_type_definition OWNER TO asset_registry_admins;

--
-- Name: udm_asset_type_definition udm_asset_type_definition_def_name_key; 
Type: CONSTRAINT; Schema: main; Owner: asset_registry_admins
--

ALTER TABLE ONLY udm_asset_type_definition
     ADD CONSTRAINT udm_asset_type_definition_def_name_key UNIQUE 
(def_name);

--
-- Name: udm_asset_type_definition; Type: ACL; Schema: main; Owner: 
asset_registry_admins
--

GRANT SELECT ON TABLE udm_asset_type_definition TO asset_registry_readers;
GRANT ALL ON TABLE udm_asset_type_definition TO asset_registry_writers;




-- 

On 02/08/2018 10:20 AM, Steven Hirsch wrote:
> On Thu, 8 Feb 2018, Adrian Klaver wrote:
> 
>> What if you do?:
>>
>> SELECT * FROM udm_asset_type_definition_def_id_seq;
> 
> I get:
> 
> udm_asset_type_definition_def_id_seq    21    1    1    
> 9223372036854775807    1    1    32    false    true
> 
> 
>> SELECT currval('udm_asset_type_definition_id_seq');

Arrgh my mistake, the above should have been

SELECT currval('udm_asset_type_definition_def_id_seq');

> 
> I get:
> 
> [Code: , SQL State: 42P01]  ERROR: relation 
> "udm_asset_type_definition_id_seq" does not exist
>    Position: 16
> 
>> Also what happens if you do:
>>
>> pg_dump -d db_name -U some_user -s  -t udm_asset_type_definition > 
>> out.sql
> 
> This:
> 
> -- 
> -- Name: udm_asset_type_definition; Type: TABLE; Schema: main; Owner: 
> asset_registry_admins
> -- 
> 
> CREATE TABLE udm_asset_type_definition (
>      def_id bigint DEFAULT 
> nextval('udm_asset_type_definition_def_id_seq'::regclass) NOT NULL,
>      def_name character varying(32) NOT NULL,
> );
> 
> 
> ALTER TABLE udm_asset_type_definition OWNER TO asset_registry_admins;
> 
> -- 
> -- Name: udm_asset_type_definition 
> udm_asset_type_definition_def_name_key; Type: CONSTRAINT; Schema: main; 
> Owner: asset_registry_admins
> -- 
> 
> ALTER TABLE ONLY udm_asset_type_definition
>      ADD CONSTRAINT udm_asset_type_definition_def_name_key UNIQUE 
> (def_name);
> 
> -- 
> -- Name: udm_asset_type_definition; Type: ACL; Schema: main; Owner: 
> asset_registry_admins
> -- 
> 
> GRANT SELECT ON TABLE udm_asset_type_definition TO asset_registry_readers;
> GRANT ALL ON TABLE udm_asset_type_definition TO asset_registry_writers;
> 
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 02/08/2018 10:20 AM, Steven Hirsch wrote:
> On Thu, 8 Feb 2018, Adrian Klaver wrote:
> 
>> What if you do?:
>>
>> SELECT * FROM udm_asset_type_definition_def_id_seq;
> 
> I get:
> 
> udm_asset_type_definition_def_id_seq    21    1    1    
> 9223372036854775807    1    1    32    false    true

What does the below show?:

\d udm_asset_type_definition_def_id_seq

> 
> 
>> SELECT currval('udm_asset_type_definition_id_seq');
> 
> I get:
> 
> [Code: , SQL State: 42P01]  ERROR: relation 
> "udm_asset_type_definition_id_seq" does not exist
>    Position: 16
> 
>> Also what happens if you do:
>>
>> pg_dump -d db_name -U some_user -s  -t udm_asset_type_definition > 
>> out.sql
> 
> This:
> 
> -- 
> -- Name: udm_asset_type_definition; Type: TABLE; Schema: main; Owner: 
> asset_registry_admins
> -- 
> 
> CREATE TABLE udm_asset_type_definition (
>      def_id bigint DEFAULT 
> nextval('udm_asset_type_definition_def_id_seq'::regclass) NOT NULL,
>      def_name character varying(32) NOT NULL,
> );
> 
> 
> ALTER TABLE udm_asset_type_definition OWNER TO asset_registry_admins;
> 
> -- 
> -- Name: udm_asset_type_definition 
> udm_asset_type_definition_def_name_key; Type: CONSTRAINT; Schema: main; 
> Owner: asset_registry_admins
> -- 
> 
> ALTER TABLE ONLY udm_asset_type_definition
>      ADD CONSTRAINT udm_asset_type_definition_def_name_key UNIQUE 
> (def_name);
> 
> -- 
> -- Name: udm_asset_type_definition; Type: ACL; Schema: main; Owner: 
> asset_registry_admins
> -- 
> 
> GRANT SELECT ON TABLE udm_asset_type_definition TO asset_registry_readers;
> GRANT ALL ON TABLE udm_asset_type_definition TO asset_registry_writers;
> 
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Steven:

On Thu, Feb 8, 2018 at 6:58 PM, Steven Hirsch <snhirsch@gmail.com> wrote:
> On Thu, 8 Feb 2018, Francisco Olarte wrote:
>> Something must be different. As requested by others, try posting the
>> SQL code chunks, more eyeballs make bugs shallower ( it's happened
>> several times to me, make a typo, go over it for half an hour, grab a
>> colleague, she immediately points to it )
> Fair enough.  Here is the DDL:

... nice and fast response, good explanation. Although it doesn't seem
to be ( due to the formatting ) the actual JDBC code chunks. I meant
that ones for the more eyeballs thing because you may have an actual
typo in them. Given you have taken a nice effort I suppose you have
already triple checked them, so you do not have something like

stmt.execute("select currval('uMD_asset_type_definition......

or a more devious one.

.....
> SELECT pg_get_serial_sequence('udm_asset_type_definition','def_id')
> I get back NULL (doesn't matter if I qualify with schema - everything is in
> a schema called 'main' and that is first on the search path).  All other
> sequences in the database (created exactly the same way, through definition
> as 'BIGSERIAL' type) are properly found.

That is certainly strange. Seems like the sequence wasn't created by
serial code.

I would try to look carefully at the dumps and insure the columns /
schemas are all correct, that nothing has fallen  into the public
schema instead of the min one you told us you used. By I do not
remember the exact commands, it should not be too difficult.

Try comparing the definitions of this sequence and a working one in
the catalogs, and the definition of the associated columns, to see if
any field varies, I would suspect something like this.



> Yes, I changed the code to 'INSERT .. RETURNING ..' and that works
> correctly.  But, again, not necessary for any of the other tables.

I pointed it not as a necesity, but as a normally better way to grab
autogenerated id's, as this is a useful way to get the values inserted
by defaults without having to know what expression is in the default,
and even when the expression is non-repeateable, something like
"default generate_random_uuid()", or when you insert more than one
row.

The problem with the sequence must be solved, even if I convince you
my way is better and you stop using it. I would never be in peace
knowing I have a malfunctioning sequence in the database.

Francisco Olarte.

On Thu, 8 Feb 2018, Melvin Davidson wrote:

> I believe your problem is in your usage.
> In order for currval(regclass) to work, you must first do a
> SELECT nextval(regclass) in your _current transaction_!
> 
> https://www.postgresql.org/docs/9.6/static/functions-sequence.html

I AM doing that.  It is returning zero.  The code is identical to that 
used in all other tables in the schema - all of them work.  This one does 
not - unless I use the INSERT .. RETURNING ..  approach.

I suspect the weirdness with the sequence not being found is part of the 
underlying problem.


-- 

On 02/08/2018 10:45 AM, Steven Hirsch wrote:
> On Thu, 8 Feb 2018, Melvin Davidson wrote:
> 
>> I believe your problem is in your usage.
>> In order for currval(regclass) to work, you must first do a
>> SELECT nextval(regclass) in your _current transaction_!
>>
>> https://www.postgresql.org/docs/9.6/static/functions-sequence.html
> 
> I AM doing that.  It is returning zero.  The code is identical to that 
> used in all other tables in the schema - all of them work.  This one 
> does not - unless I use the INSERT .. RETURNING ..  approach.
> 
> I suspect the weirdness with the sequence not being found is part of the 
> underlying problem.

I suspect that also, hence my previous suggestion:

\d udm_asset_type_definition_def_id_seq

This should show whether the sequence is 'owned' by the table.column.

> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, 8 Feb 2018, Adrian Klaver wrote:

>>> SELECT currval('udm_asset_type_definition_id_seq');
>
> Arrgh my mistake, the above should have been
>
> SELECT currval('udm_asset_type_definition_def_id_seq');

[Code: , SQL State: 55000]  ERROR: currval of sequence 
"udm_asset_type_definition_def_id_seq" is not yet defined in this session

Which is what I'd expect at the psql command line.

In real-life, I am calling from JDBC in a single session - in fact, from 
inside a single transaction.

I will post the JDBC code as soon as I can cut it down to the pertinent 
area.

Also, since you request it:

[hirschs@ui24-lin ~]$ psql -d asset_registry -U hirschs
psql (9.6.5)
Type "help" for help.

asset_registry=# \d udm_asset_type_definition_def_id_seq
       Sequence "main.udm_asset_type_definition_def_id_seq"
     Column     |  Type   |                Value
---------------+---------+--------------------------------------
  sequence_name | name    | udm_asset_type_definition_def_id_seq
  last_value    | bigint  | 21
  start_value   | bigint  | 1
  increment_by  | bigint  | 1
  max_value     | bigint  | 9223372036854775807
  min_value     | bigint  | 1
  cache_value   | bigint  | 1
  log_cnt       | bigint  | 32
  is_cycled     | boolean | f
  is_called     | boolean | t

To me, the greater mystery is why 'pg_get_serial_sequence' is unable to 
find that sequence when invoked by table + column.


-- 

On 02/08/2018 10:54 AM, Steven Hirsch wrote:
> On Thu, 8 Feb 2018, Adrian Klaver wrote:
> 
>>>> SELECT currval('udm_asset_type_definition_id_seq');
>>
>> Arrgh my mistake, the above should have been
>>
>> SELECT currval('udm_asset_type_definition_def_id_seq');
> 
> [Code: , SQL State: 55000]  ERROR: currval of sequence 
> "udm_asset_type_definition_def_id_seq" is not yet defined in this session
> 
> Which is what I'd expect at the psql command line.
> 
> In real-life, I am calling from JDBC in a single session - in fact, from 
> inside a single transaction.
> 
> I will post the JDBC code as soon as I can cut it down to the pertinent 
> area.
> 
> Also, since you request it:
> 
> [hirschs@ui24-lin ~]$ psql -d asset_registry -U hirschs
> psql (9.6.5)
> Type "help" for help.
> 
> asset_registry=# \d udm_asset_type_definition_def_id_seq
>        Sequence "main.udm_asset_type_definition_def_id_seq"
>      Column     |  Type   |                Value
> ---------------+---------+--------------------------------------
>   sequence_name | name    | udm_asset_type_definition_def_id_seq
>   last_value    | bigint  | 21
>   start_value   | bigint  | 1
>   increment_by  | bigint  | 1
>   max_value     | bigint  | 9223372036854775807
>   min_value     | bigint  | 1
>   cache_value   | bigint  | 1
>   log_cnt       | bigint  | 32
>   is_cycled     | boolean | f
>   is_called     | boolean | t

For comparison, from one of my databases:

hplc=> \d student_attendance_attendance_id_seq
      Sequence "public.student_attendance_attendance_id_seq"
     Column     |  Type   |                Value
---------------+---------+--------------------------------------
  sequence_name | name    | student_attendance_attendance_id_seq
  last_value    | bigint  | 39590
  start_value   | bigint  | 1
  increment_by  | bigint  | 1
  max_value     | bigint  | 9223372036854775807
  min_value     | bigint  | 1
  cache_value   | bigint  | 1
  log_cnt       | bigint  | 0
  is_cycled     | boolean | f
  is_called     | boolean | t
Owned by: public.student_attendance.attendance_id


> 
> To me, the greater mystery is why 'pg_get_serial_sequence' is unable to 
> find that sequence when invoked by table + column.

Assuming you showed the complete output I am not seeing the Owned by: 
for your sequence. I would do the \d on one of your sequences that 
'works', I am guessing you will see Owned by: .

To correct see:

https://www.postgresql.org/docs/10/static/sql-altersequence.html

"OWNED BY table_name.column_name
OWNED BY NONE

     The OWNED BY option causes the sequence to be associated with a 
specific table column, such that if that column (or its whole table) is 
dropped, the sequence will be automatically dropped as well. If 
specified, this association replaces any previously specified 
association for the sequence. The specified table must have the same 
owner and be in the same schema as the sequence. Specifying OWNED BY 
NONE removes any existing association, making the sequence “free-standing”.
"

> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, 8 Feb 2018, Adrian Klaver wrote:

> hplc=> \d student_attendance_attendance_id_seq
>     Sequence "public.student_attendance_attendance_id_seq"
>    Column     |  Type   |                Value
> ---------------+---------+--------------------------------------
> sequence_name | name    | student_attendance_attendance_id_seq
> last_value    | bigint  | 39590
> start_value   | bigint  | 1
> increment_by  | bigint  | 1
> max_value     | bigint  | 9223372036854775807
> min_value     | bigint  | 1
> cache_value   | bigint  | 1
> log_cnt       | bigint  | 0
> is_cycled     | boolean | f
> is_called     | boolean | t
> Owned by: public.student_attendance.attendance_id


> Assuming you showed the complete output I am not seeing the Owned by: for 
> your sequence. I would do the \d on one of your sequences that 'works', I am 
> guessing you will see Owned by: .
>
> To correct see:
>
> https://www.postgresql.org/docs/10/static/sql-altersequence.html
>
> "OWNED BY table_name.column_name
> OWNED BY NONE
>
>    The OWNED BY option causes the sequence to be associated with a specific 
> table column, such that if that column (or its whole table) is dropped, the 
> sequence will be automatically dropped as well. If specified, this 
> association replaces any previously specified association for the sequence. 
> The specified table must have the same owner and be in the same schema as the 
> sequence. Specifying OWNED BY NONE removes any existing association, making 
> the sequence “free-standing”.

Agggh.  That's it!  I'll fix the ownership.

So, a few questions:

1. How on earth did this happen? I do not recall doing any manual fiddling 
with either database - they were (as far as I know) built from the same 
DDL. We may never have an answer for this.  Being human, who knows what I 
may or may not have done 4 months ago...

But,

2. Why is the currval() function being so blasted dumb?  If 
'pg_get_serial_sequence' cannot resolve the sequence, it returns NULL. 
As such, shouldn't the outer currval() also be returning NULL?  I cannot 
imagine a rationale for the current behavior.

THANKS to everyone who chimed in on this.  I was beginning to think I was 
losing my marbles.

--

On 02/08/2018 11:12 AM, Steven Hirsch wrote:
> On Thu, 8 Feb 2018, Adrian Klaver wrote:
> 
>> hplc=> \d student_attendance_attendance_id_seq
>>     Sequence "public.student_attendance_attendance_id_seq"
>>    Column     |  Type   |                Value
>> ---------------+---------+--------------------------------------
>> sequence_name | name    | student_attendance_attendance_id_seq
>> last_value    | bigint  | 39590
>> start_value   | bigint  | 1
>> increment_by  | bigint  | 1
>> max_value     | bigint  | 9223372036854775807
>> min_value     | bigint  | 1
>> cache_value   | bigint  | 1
>> log_cnt       | bigint  | 0
>> is_cycled     | boolean | f
>> is_called     | boolean | t
>> Owned by: public.student_attendance.attendance_id
> 
> 
>> Assuming you showed the complete output I am not seeing the Owned by: 
>> for your sequence. I would do the \d on one of your sequences that 
>> 'works', I am guessing you will see Owned by: .
>>
>> To correct see:
>>
>> https://www.postgresql.org/docs/10/static/sql-altersequence.html
>>
>> "OWNED BY table_name.column_name
>> OWNED BY NONE
>>
>>    The OWNED BY option causes the sequence to be associated with a 
>> specific table column, such that if that column (or its whole table) 
>> is dropped, the sequence will be automatically dropped as well. If 
>> specified, this association replaces any previously specified 
>> association for the sequence. The specified table must have the same 
>> owner and be in the same schema as the sequence. Specifying OWNED BY 
>> NONE removes any existing association, making the sequence 
>> “free-standing”.
> 
> Agggh.  That's it!  I'll fix the ownership.
> 
> So, a few questions:
> 
> 1. How on earth did this happen? I do not recall doing any manual 
> fiddling with either database - they were (as far as I know) built from 
> the same DDL. We may never have an answer for this.  Being human, who 
> knows what I may or may not have done 4 months ago...

https://www.postgresql.org/docs/10/static/datatype-numeric.html#DATATYPE-SERIAL

"The data types smallserial, serial and bigserial are not true types, 
but merely a notational convenience for creating unique identifier 
columns (similar to the AUTO_INCREMENT property supported by some other 
databases). In the current implementation, specifying:

CREATE TABLE tablename (
     colname SERIAL
);

is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
     colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
"

So the only way I know this can happen is:

ALTER SEQUENCE seq_name OWNED BY NONE;

> 
> But,
> 
> 2. Why is the currval() function being so blasted dumb?  If 
> 'pg_get_serial_sequence' cannot resolve the sequence, it returns NULL. 
> As such, shouldn't the outer currval() also be returning NULL?  I cannot 
> imagine a rationale for the current behavior.

Someone else will need to comment on this as I have no idea.

> 
> THANKS to everyone who chimed in on this.  I was beginning to think I 
> was losing my marbles.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, 8 Feb 2018, David G. Johnston wrote:

> On Thu, Feb 8, 2018 at 10:58 AM, Steven Hirsch <snhirsch@gmail.com> wrote:
>       On a hunch, I tried 'SELECT currval(NULL)' to see if it returned '0', but that too returns NULL. 
>       So, where is the '0' coming from when I do:
>
>       SELECT currval( pg_get_serial_sequence('udm_asset_type_definition','def_id'))
>
>       ? I've already established that the inner expression evaluates to NULL!
> 
> 
> ​This is indeed unusual...to be specific here pg_get_serial_sequence returns null in lieu of an error for
> being unable to locate the indicated sequence.  currval is returning null because it is defined "STRICT" and
> so given a null input it will always return null.  currval itself, when provided a non-null input, is going
> to error or provide a number (which should never be zero...).

> I'm wondering whether someone didn't like the fact that currval errors and instead wrote a overriding
> function that instead returns zero?

Do you mean "someone" on the PostgreSQL development team - or "someone" at 
my end?  I can assure you there are no overriding functions in either 
of my databases.  I just double-checked this.  The only 'currval' 
procedure is the one defined at installation (in public).

Looks like I may have encountered a legitimate bug?

--

On Thu, 8 Feb 2018, David G. Johnston wrote:

> On Thu, Feb 8, 2018 at 12:54 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
>              The only 'currval' procedure is the one defined at installation (in public).
> 
> 
> ​So, the installed version of currval would be defined in "pg_catalog", not "public" ...

??

All I can tell you is that when I connect from dbVisualizer and open the 
twisty under 'main.procedures' I see 100+ functions that are intrinsic to 
pgsql - currval() included.  I have almost no experience writing pgsql 
procs and absolutely never installed anything that would override the base 
function.



--

On Thu, 8 Feb 2018, David G. Johnston wrote:

new_db=# \dfS+ currval

List of functions
    Schema   |  Name   | Result data type | Argument data types |  Type  | 
Volatility | Parallel |  Owner   | Security | Access privileges | Language 
| Source code |      Des
cription

------------+---------+------------------+---------------------+--------+------------+----------+----------+----------+-------------------+----------+-------------+---------
---------------
  pg_catalog | currval | bigint           | regclass            | normal | 
volatile   | unsafe   | postgres | invoker  |                   | internal 
| currval_oid | sequence
  current value
(1 row)

> I'll agree this would be highly unusual but I so would this being a 
> bug.  And the oddity with the lost sequence ownership...

So, I believe I have gotten to the bottom of the issue.  Your suggestion 
to stay within psql was the secret.  Not too surprisingly, when I run:

--- code ----

\pset null '(null)'

CREATE TABLE bugtest (
   id BIGSERIAL NOT NULL,
   name VARCHAR(32) NOT NULL,
   PRIMARY KEY (id)
);

INSERT INTO bugtest(name)
VALUES ('one');

SELECT currval( pg_get_serial_sequence('bugtest','id'));

INSERT INTO bugtest(name)
VALUES ('two');

SELECT currval( pg_get_serial_sequence('bugtest','id'));

ALTER SEQUENCE bugtest_id_seq
OWNED BY NONE;

INSERT INTO bugtest(name)
VALUES ('three');

SELECT currval( pg_get_serial_sequence('bugtest','id'));

--- end code ---

I see:

--- output ---

Null display is "(null)".
CREATE TABLE
INSERT 0 1
  currval
---------
        1
(1 row)

INSERT 0 1
  currval
---------
        2
(1 row)

ALTER SEQUENCE
INSERT 0 1
  currval
---------
   (null)  <---- !!!!
(1 row)

-- end output ---

The culprit is in the JDBC domain, NOT PostgreSQL!  According to the 
documentation I found, the ResultSet 'getLong()' method returns a value of 
zero when it sees NULL as an input. Why the JDBC libs don't treat this as 
an invalid numeric conversion is beyond me.

Once again, thanks to everyone who took their time to help me out on this 
issue.  It's a great reminder of the high-quality community that surrounds 
PostgreSQL.



--

On Thu, Feb 8, 2018 at 8:12 PM, Steven Hirsch <snhirsch@gmail.com> wrote:
.....

> 2. Why is the currval() function being so blasted dumb?  If
> 'pg_get_serial_sequence' cannot resolve the sequence, it returns NULL. As
> such, shouldn't the outer currval() also be returning NULL?  I cannot
> imagine a rationale for the current behavior.

Are you sure it does ?  http://sqlfiddle.com/#!17/9eecb/9696 shows it
returning null. ( as expected, if it is defined strict as it should
and someone has already pointed it ).

Are you sure you are not using pgAdmin or a similar thing which
displays null as 0 in a numeric field?



Francisco Olarte.

Steven:

On Thu, Feb 8, 2018 at 10:52 PM, Steven Hirsch <snhirsch@gmail.com> wrote:

This may sound a bit harsh but:

> The culprit is in the JDBC domain, NOT PostgreSQL!  According to the
> documentation I found, the ResultSet 'getLong()' method returns a value of
> zero when it sees NULL as an input. Why the JDBC libs don't treat this as an
> invalid numeric conversion is beyond me.

No, that's not a JDBC problem, that's pilot error.

JDBC has behaved like these for ever, and you have had people chasing
ghosts ( not a biggie, we are used to this ) because, instead of
providing the real chunks you were running you kept saying "when I
select currval() I get 0" and similar things. If you had said "when I
do rs.getLong(col) I get 0", or pasted a relevant chunk of code, lots
of people here would have pointed that getLong returns a primitive
java long, so rs.getObject() is needed if you may get null. JDBC has
been doing this forever, and they probably do it because in many cases
this behaviour is useful.

This also raises suspicions about your sequence ownership problems, as
you may have being ( with your best intention ) pasting your code to
reproduce the problems instead of the real scripts from the database.

In general, when you run a code chunk for problem reporting, try to do
it with psql ( which everybody has and knows how it works ) and paste
the code / result directly, without much editing. This aids a lot. In
fact, if you do it you may have noticed your text in psql returned an
empty column in currval(null), which, knowgin it is int, means it was
null ( in a string you cannot easily distinguish empty from nulls in a
simple select ).

Anyway, you report was quite nice and showed you put quite a bit of
work in it, treat these as tips to make problem reporting / solving
easier, not just as criticism.

Francisco Olarte.

On Fri, 9 Feb 2018, Francisco Olarte wrote:

> This may sound a bit harsh but:
>
>> The culprit is in the JDBC domain, NOT PostgreSQL!  According to the
>> documentation I found, the ResultSet 'getLong()' method returns a value of
>> zero when it sees NULL as an input. Why the JDBC libs don't treat this as an
>> invalid numeric conversion is beyond me.

> No, that's not a JDBC problem, that's pilot error.

Of course it is, but that doesn't change the fact that the behavior of 
JDBC is not helpful at all.  It also doesn't change the fact that I had a 
very real and very confusing "disconnect" between the sequence and the 
table.  That may well have been pilot error on my part, but was extremely 
subtle and unusual in its impact - as witnessed by the number of rounds we 
went before David hit the issue with ownership.

> JDBC has behaved like these for ever, and you have had people chasing
> ghosts ( not a biggie, we are used to this ) because, instead of
> providing the real chunks you were running you kept saying "when I
> select currval() I get 0" and similar things. If you had said "when I
> do rs.getLong(col) I get 0", or pasted a relevant chunk of code, lots
> of people here would have pointed that getLong returns a primitive
> java long, so rs.getObject() is needed if you may get null. JDBC has
> been doing this forever, and they probably do it because in many cases
> this behaviour is useful.

Please note that I _did_ post the JDBC code after being requested to do 
so.  It showed clearly where I was using rs.getLong() to retrieve the id. 
No one commented on that as being a factor.

Given Java's usual strictness about converting null to numerics, I do not 
find it useful.  I find it completely broken and orthogonal to the spirit 
of a type-safe language. In my opinion, this:

long i = rs.getLong(n);

should throw a type exception if NULL is fetched and this:

Long i = rs.getLong(n);

should give 'i' the value of 'null'.  Getting 0 as an answer simply makes 
no sense at all to me.

But, now that I've been burned by it, I'll certainly never make the same 
mistake again.  I'll make other mistakes :-).

> This also raises suspicions about your sequence ownership problems, as
> you may have being ( with your best intention ) pasting your code to
> reproduce the problems instead of the real scripts from the database.

Not following this statement.  After being requested to do so, I did post 
the actual JDBC code.  Did that not show up in the message thread? 
Nothing was done to edit down or elide anything.

> In general, when you run a code chunk for problem reporting, try to do
> it with psql ( which everybody has and knows how it works ) and paste
> the code / result directly, without much editing. This aids a lot. In
> fact, if you do it you may have noticed your text in psql returned an
> empty column in currval(null), which, knowgin it is int, means it was
> null ( in a string you cannot easily distinguish empty from nulls in a
> simple select ).

Of course I noticed it!  That's how I finally spotted the issue.  If I was 
a bit more on the ball, I'd have used psql from the start.  So, now that I 
understand the importance of doing so (and the brain-dead behavior of 
JDBC) I will be sure to do so in the future.

> Anyway, you report was quite nice and showed you put quite a bit of
> work in it, treat these as tips to make problem reporting / solving
> easier, not just as criticism.

I do not take your tone as harsh.  I'm willing to take responsibility 
where I created my own problems.  I have learned that I must reduce 
trouble reports to issues that can be reproduced in psql.  It makes sense 
and I'll do so in the future.



-- 

On 02/09/2018 08:58 AM, Steven Hirsch wrote:
> On Fri, 9 Feb 2018, Francisco Olarte wrote:
> 
>> This may sound a bit harsh but:
>>
>>> The culprit is in the JDBC domain, NOT PostgreSQL!  According to the
>>> documentation I found, the ResultSet 'getLong()' method returns a 
>>> value of
>>> zero when it sees NULL as an input. Why the JDBC libs don't treat 
>>> this as an
>>> invalid numeric conversion is beyond me.
> 
>> No, that's not a JDBC problem, that's pilot error.
> 
> Of course it is, but that doesn't change the fact that the behavior of 
> JDBC is not helpful at all.  It also doesn't change the fact that I had 
> a very real and very confusing "disconnect" between the sequence and the 
> table.  That may well have been pilot error on my part, but was 
> extremely subtle and unusual in its impact - as witnessed by the number 
> of rounds we went before David hit the issue with ownership.
> 
>> JDBC has behaved like these for ever, and you have had people chasing
>> ghosts ( not a biggie, we are used to this ) because, instead of
>> providing the real chunks you were running you kept saying "when I
>> select currval() I get 0" and similar things. If you had said "when I
>> do rs.getLong(col) I get 0", or pasted a relevant chunk of code, lots
>> of people here would have pointed that getLong returns a primitive
>> java long, so rs.getObject() is needed if you may get null. JDBC has
>> been doing this forever, and they probably do it because in many cases
>> this behaviour is useful.
> 
> Please note that I _did_ post the JDBC code after being requested to do 
> so.  It showed clearly where I was using rs.getLong() to retrieve the 
> id. No one commented on that as being a factor.
> 
> Given Java's usual strictness about converting null to numerics, I do 
> not find it useful.  I find it completely broken and orthogonal to the 
> spirit of a type-safe language. In my opinion, this:
> 
> long i = rs.getLong(n);
> 
> should throw a type exception if NULL is fetched and this:
> 
> Long i = rs.getLong(n);
> 
> should give 'i' the value of 'null'.  Getting 0 as an answer simply 
> makes no sense at all to me.
> 
> But, now that I've been burned by it, I'll certainly never make the same 
> mistake again.  I'll make other mistakes :-).
> 
>> This also raises suspicions about your sequence ownership problems, as
>> you may have being ( with your best intention ) pasting your code to
>> reproduce the problems instead of the real scripts from the database.
> 
> Not following this statement.  After being requested to do so, I did 
> post the actual JDBC code.  Did that not show up in the message thread? 
> Nothing was done to edit down or elide anything.

I remember seeing it, so I went back to look at the message. Turns out 
you sent it to me only. Unfortunately I am not a Java programmer so I 
did not catch the error. For the record:

"
Here is the JDBC code:

             try {
                 conn.setAutoCommit(false);
                 PreparedStatement sth = null;
                 ResultSet rs = null;

                 // Insert new
                 sth = conn.prepareStatement(addAssetType);
                 sth.setString(1, name);
                 sth.execute();

                 sth = conn.prepareStatement(lastTypeId);
                 rs = sth.executeQuery();
                 if (rs.next()) {
                     long id = rs.getLong(1);
                     result.put("id", id);
                     result.put("name", name);
                 }
                 else {
                     throw new 
WebApplicationException(buildError(BAD_REQUEST, "Lookup of most recent 
sequence failed"));
                 }
                 conn.commit();
             }
             catch (SQLException e) {
                 conn.rollback();
                 throw new 
WebApplicationException(buildError(BAD_REQUEST, e.getMessage()));
             }


Where:

     private final static String addAssetType =
             "INSERT INTO udm_asset_type_definition (def_name) "
             + "VALUES (?)";

     private final static String lastTypeId = "SELECT currval( 
pg_get_serial_sequence('udm_asset_type_definition', 'def_id'))";

Code with this exact same structure (but different SQL, obviously) is 
working just fine with five other tables using sequences.  The above 
code snippet always produces '0' for the id.  If I use the 'INSERT .. 
RETURNING..' approach, it gives the right answer.
"

> 
>> In general, when you run a code chunk for problem reporting, try to do
>> it with psql ( which everybody has and knows how it works ) and paste
>> the code / result directly, without much editing. This aids a lot. In
>> fact, if you do it you may have noticed your text in psql returned an
>> empty column in currval(null), which, knowgin it is int, means it was
>> null ( in a string you cannot easily distinguish empty from nulls in a
>> simple select ).
> 
> Of course I noticed it!  That's how I finally spotted the issue.  If I 
> was a bit more on the ball, I'd have used psql from the start.  So, now 
> that I understand the importance of doing so (and the brain-dead 
> behavior of JDBC) I will be sure to do so in the future.
> 
>> Anyway, you report was quite nice and showed you put quite a bit of
>> work in it, treat these as tips to make problem reporting / solving
>> easier, not just as criticism.
> 
> I do not take your tone as harsh.  I'm willing to take responsibility 
> where I created my own problems.  I have learned that I must reduce 
> trouble reports to issues that can be reproduced in psql.  It makes 
> sense and I'll do so in the future.
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Fri, 9 Feb 2018, Adrian Klaver wrote:

> I remember seeing it, so I went back to look at the message. Turns out you 
> sent it to me only. Unfortunately I am not a Java programmer so I did not 
> catch the error. For the record:

Ah, blast...  Apologies to everyone concerned.  I need to understand why 
my mail reader (Alpine on Linux) insists on defaulting to the original 
poster's e-mail address when I hit 'Reply' rather than the group list. 
It's not doing that with any other mail list I subscribe to.

I was trying to be careful and changed the address manually for all 
messages except this one.

Thanks, Adrian!  I was sure that I sent it - just not to the right place 
as it turns out.

> "
> Here is the JDBC code:
>
>            try {
>                conn.setAutoCommit(false);
>                PreparedStatement sth = null;
>                ResultSet rs = null;
>
>                // Insert new
>                sth = conn.prepareStatement(addAssetType);
>                sth.setString(1, name);
>                sth.execute();
>
>                sth = conn.prepareStatement(lastTypeId);
>                rs = sth.executeQuery();
>                if (rs.next()) {
>                    long id = rs.getLong(1);
>                    result.put("id", id);
>                    result.put("name", name);
>                }
>                else {
>                    throw new WebApplicationException(buildError(BAD_REQUEST, 
> "Lookup of most recent sequence failed"));
>                }
>                conn.commit();
>            }
>            catch (SQLException e) {
>                conn.rollback();
>                throw new WebApplicationException(buildError(BAD_REQUEST, 
> e.getMessage()));
>            }
>
>
> Where:
>
>    private final static String addAssetType =
>            "INSERT INTO udm_asset_type_definition (def_name) "
>            + "VALUES (?)";
>
>    private final static String lastTypeId = "SELECT currval( 
> pg_get_serial_sequence('udm_asset_type_definition', 'def_id'))";
>
> Code with this exact same structure (but different SQL, obviously) is working 
> just fine with five other tables using sequences.  The above code snippet 
> always produces '0' for the id.  If I use the 'INSERT .. RETURNING..' 
> approach, it gives the right answer.
> "

-- 

Steven:


On Fri, Feb 9, 2018 at 5:58 PM, Steven Hirsch <snhirsch@gmail.com> wrote:
> On Fri, 9 Feb 2018, Francisco Olarte wrote:
>> This may sound a bit harsh but:
>>> The culprit is in the JDBC domain, NOT PostgreSQL!  According to the
>>> documentation I found, the ResultSet 'getLong()' method returns a value
>>> of
>>> zero when it sees NULL as an input. Why the JDBC libs don't treat this as
>>> an
>>> invalid numeric conversion is beyond me.
>
>> No, that's not a JDBC problem, that's pilot error.

> Of course it is, but that doesn't change the fact that the behavior of JDBC
> is not helpful at all.

That's an opinion, perfectly valid but just that.

For me a long returning function which has to cope with the DB nulls
has two options. Return a default value, and 0 is the one I would in
this case without doubt, or throw an exception ( NullPointer,
SQLexception, checked or not ). IMO the second one is much less
helpful, given a null value on a nullable column is not an exceptional
condition, and there are plenty of methods to do it right, i.e., test
for null first read the value then, or use (Long)getObject.

>  It also doesn't change the fact that I had a very
> real and very confusing "disconnect" between the sequence and the table.
> That may well have been pilot error on my part, but was extremely subtle and
> unusual in its impact - as witnessed by the number of rounds we went before
> David hit the issue with ownership.

Bear in mind the disconnect is easier to see in the catalogs, or on a
schema dump. It's extremely difficult to debug without having access
to them. And the 0 issue does not help, as it sends people on a wild
goose chase thinking something is borked there.

Given the sequence was unowned someone had to 1.- create the sequence
and table in two steps ( maybe from an edited schema dump ) or 2.-
alter it to not owned. Those are DML, those are schema definitions,
and we didn't have them.


>> JDBC has behaved like these for ever, and you have had people chasing
>> ghosts ( not a biggie, we are used to this ) because, instead of
>> providing the real chunks you were running you kept saying "when I
>> select currval() I get 0" and similar things. If you had said "when I
>> do rs.getLong(col) I get 0", or pasted a relevant chunk of code, lots
>> of people here would have pointed that getLong returns a primitive
>> java long, so rs.getObject() is needed if you may get null. JDBC has
>> been doing this forever, and they probably do it because in many cases
>> this behaviour is useful.
> Please note that I _did_ post the JDBC code after being requested to do so.
> It showed clearly where I was using rs.getLong() to retrieve the id. No one
> commented on that as being a factor.

My fault them, I failed to see it, and I'm unable to see it in my
archives, and google is not finding it so ..... aha, next message in
the thread, you did not sent it to everyone. Happens a lot.

> Given Java's usual strictness about converting null to numerics, I do not
> find it useful.  I find it completely broken and orthogonal to the spirit of
> a type-safe language. In my opinion, this:
>
> long i = rs.getLong(n);
> should throw a type exception if NULL is fetched and this:
> Long i = rs.getLong(n);
> should give 'i' the value of 'null'.  Getting 0 as an answer simply makes no
> sense at all to me.

You are, IMO, completely wrong. Java can not override on an inferred
return value type, not even on a explicit one.

So, getLong returning a long will never cast to (Long) null.

Your second behaviour is trivial, is just (Long)(rs.getObject(n)), and
I use it a lot.

If you (trivially) derive and wrap or make the driver use jour
resultSet and you put a public Long getLongX(int n) { return
(Long)getObject(n) } in it you have your method. You just cannot call
it getLong(n) ( no overrideing with different result type ).

There is probably a good reason to make it return primitives. Remember
JDBC dates from when java had no autoboxing as was really slow.

and throwing an exception is, IMO, as I wrote above, much less helpful.

> But, now that I've been burned by it, I'll certainly never make the same
> mistake again.  I'll make other mistakes :-).

We all learn this way.


>> This also raises suspicions about your sequence ownership problems, as
>> you may have being ( with your best intention ) pasting your code to
>> reproduce the problems instead of the real scripts from the database.
> Not following this statement.  After being requested to do so, I did post
> the actual JDBC code.  Did that not show up in the message thread? Nothing
> was done to edit down or elide anything.

Commented above, common error, you did reply instead of reply to all.


>> In general, when you run a code chunk for problem reporting, try to do
>> it with psql ( which everybody has and knows how it works ) and paste
>> the code / result directly, without much editing. This aids a lot. In
>> fact, if you do it you may have noticed your text in psql returned an
>> empty column in currval(null), which, knowgin it is int, means it was
>> null ( in a string you cannot easily distinguish empty from nulls in a
>> simple select ).
> Of course I noticed it!  That's how I finally spotted the issue.  If I was a
> bit more on the ball, I'd have used psql from the start.  So, now that I
> understand the importance of doing so (and the brain-dead behavior of JDBC)
> I will be sure to do so in the future.

Putting java silliness aside, the problem is that retrying a chunk of
JDBC code for testing normally needs writing a minimum program around
it and is really tedious, while replyaing psql lines is just a cut &
paste issue.

>> Anyway, you report was quite nice and showed you put quite a bit of
>> work in it, treat these as tips to make problem reporting / solving
>> easier, not just as criticism.
> I do not take your tone as harsh.  I'm willing to take responsibility where
> I created my own problems.  I have learned that I must reduce trouble
> reports to issues that can be reproduced in psql.  It makes sense and I'll
> do so in the future.

Not limit to it, but split them. psql is like the standard, is a very
simple program well understood by most people and is what we normally
use to try to reproduce and  investigate problems. And normally, when
a tool does not match psql it tends to be a problem in the tool.

If you ask "I get 0 for currval(null) in jdbc but null in psql" it's
very easy to point to getLong vs getLong+wasNull or getObject issues.

Francisco Olarte.

Steven:

On Fri, Feb 9, 2018 at 6:33 PM, Steven Hirsch <snhirsch@gmail.com> wrote:
> Ah, blast...  Apologies to everyone concerned.  I need to understand why my
> mail reader (Alpine on Linux) insists on defaulting to the original poster's
> e-mail address when I hit 'Reply' rather than the group list. It's not doing
> that with any other mail list I subscribe to.

It's probably because this list headers are "old school", without
those newflangled thingied.

It defaults to the sender for mine, and for nearly everyone else, too.
We are just used to hit reply-all.

> I was trying to be careful and changed the address manually for all messages
> except this one.

Do not bother with that. The style in this list is to just reply to
all, so the people that are participating in the thread can get a copy
of the messages in both their inbox and the list folder when
filtering. In fact I told gmail to skip inbox for the list, as I read
it fully normally, but if I hadn't do that and there was a traffic
spike it would be easier for me to first answer the threads Iwas
involved in and then reading the rest. In fact traffic is not that
high, and I do reply to the messages which show "me" in the gmail list
first, then the rest.

Francisco Olarte.

On 02/09/2018 09:43 AM, Francisco Olarte wrote:
> Steven:
> 
> On Fri, Feb 9, 2018 at 6:33 PM, Steven Hirsch <snhirsch@gmail.com> wrote:
>> Ah, blast...  Apologies to everyone concerned.  I need to understand why my
>> mail reader (Alpine on Linux) insists on defaulting to the original poster's
>> e-mail address when I hit 'Reply' rather than the group list. It's not doing
>> that with any other mail list I subscribe to.
> 
> It's probably because this list headers are "old school", without
> those newflangled thingied.

I believe with the change to PGLister that changed:

https://wiki.postgresql.org/wiki/PGLister_Announce

"PGLister will use standard, industry-recognized headers to identify 
mailing list messages. The non-standard header "X-Mailing-List" will no 
longer be included. Any users whose filters are based on this 
non-standard header will need to adjust their filters. "

> 
> It defaults to the sender for mine, and for nearly everyone else, too.
> We are just used to hit reply-all.
> 
>> I was trying to be careful and changed the address manually for all messages
>> except this one.
> 
> Do not bother with that. The style in this list is to just reply to
> all, so the people that are participating in the thread can get a copy
> of the messages in both their inbox and the list folder when
> filtering. In fact I told gmail to skip inbox for the list, as I read
> it fully normally, but if I hadn't do that and there was a traffic
> spike it would be easier for me to first answer the threads Iwas
> involved in and then reading the rest. In fact traffic is not that
> high, and I do reply to the messages which show "me" in the gmail list
> first, then the rest.
> 
> Francisco Olarte.
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Adrian:

On Fri, Feb 9, 2018 at 7:17 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> On 02/09/2018 09:43 AM, Francisco Olarte wrote:
....
>> It's probably because this list headers are "old school", without
>> those newflangled thingied.
> I believe with the change to PGLister that changed:
>
> https://wiki.postgresql.org/wiki/PGLister_Announce
>
> "PGLister will use standard, industry-recognized headers to identify mailing
> list messages. The non-standard header "X-Mailing-List" will no longer be
> included. Any users whose filters are based on this non-standard header will
> need to adjust their filters. "

Yep, my reference to mailing list headers is unfortunate. Thae fact is
it seems messages in the list do not have the reply-to to the lists as
many other lists seem to use, no magic list headers involved at all.

I.e., in another list I have From=poster, to=list, reply-to=list, in
your message I have from=poster, to=me&steve, cc=list, no reply-to

For what I know of muas, what you put in to/cc only does make a
difference in reply-all, but witout reply-to normal reply defaults to
from, so pg sends it to the OP, the others to the list. Reply-to
inclusion might be better for a list, but I found the pg style is a
bit confussing ( still ) but makes for nicer identification of people
in message lists.

Francisco Olarte.

On Fri, 9 Feb 2018, Francisco Olarte wrote:

>> I was trying to be careful and changed the address manually for all messages
>> except this one.
>
> Do not bother with that. The style in this list is to just reply to
> all, so the people that are participating in the thread can get a copy
> of the messages in both their inbox and the list folder when
> filtering. In fact I told gmail to skip inbox for the list, as I read
> it fully normally, but if I hadn't do that and there was a traffic
> spike it would be easier for me to first answer the threads Iwas
> involved in and then reading the rest. In fact traffic is not that
> high, and I do reply to the messages which show "me" in the gmail list
> first, then the rest.

Heh.  There is no winning in this situation.  I have been hauled over hot 
coals in other mailing lists for replying to 'All' :-).

--