Thread: postgres 11 issue?

postgres 11 issue?

From

Steve Rogerson

Date:

06 June 2019, 14:02:49

I've just updated my laptop to pg11 and I'm getting a problem. I'm  trying to
keeps the details confidential, so somewhat vague I'm afraid.

sjr_local1db=> select count(*) from user_passwords ;
ERROR:  record type has not been registered

sjr_local1db=> insert into user_passwords (name, "timestamp", password) values
('user1', my_timestamp_now(), 'dsfsdfsdf');
INSERT 0 1

sjr_local1db=> select count(*) from user_passwords ;

 count
-------
    27
(1 row)

The timestamp column is a "row" consisting of a timestamp and the timezone
(essentially),  the my_timestamp_now  is function that returns a "timestamp"
for now. 

This all works fine for pg >= 9 and pg <11.


Steve

Re: postgres 11 issue?

From

Adrian Klaver

Date:

06 June 2019, 16:35:09

On 6/6/19 4:02 AM, Steve Rogerson wrote:
> I've just updated my laptop to pg11 and I'm getting a problem. I'm  trying to
> keeps the details confidential, so somewhat vague I'm afraid.
> 
> sjr_local1db=> select count(*) from user_passwords ;
> ERROR:  record type has not been registered
> 
> sjr_local1db=> insert into user_passwords (name, "timestamp", password) values
> ('user1', my_timestamp_now(), 'dsfsdfsdf');
> INSERT 0 1
> 
> sjr_local1db=> select count(*) from user_passwords ;
> 
>   count
> -------
>      27
> (1 row)
> 
> The timestamp column is a "row" consisting of a timestamp and the timezone
> (essentially),  the my_timestamp_now  is function that returns a "timestamp"
> for now.
> 
> This all works fine for pg >= 9 and pg <11.

More information is needed:

1) Table definition

2) Exactly how "timestamp" is defined.

3) my_timestamp_now definition.

> 
> 
> Steve
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: postgres 11 issue?

From

Steve Rogerson

Date:

06 June 2019, 18:12:21

On 06/06/2019 14:35, Adrian Klaver wrote:
> On 6/6/19 4:02 AM, Steve Rogerson wrote:
>> I've just updated my laptop to pg11 and I'm getting a problem. I'm  trying to
>> keeps the details confidential, so somewhat vague I'm afraid.
>>
>> sjr_local1db=> select count(*) from user_passwords ;
>> ERROR:  record type has not been registered
>>
>> sjr_local1db=> insert into user_passwords (name, "timestamp", password) values
>> ('user1', my_timestamp_now(), 'dsfsdfsdf');
>> INSERT 0 1
>>
>> sjr_local1db=> select count(*) from user_passwords ;
>>
>>   count
>> -------
>>      27
>> (1 row)
>>
>> The timestamp column is a "row" consisting of a timestamp and the timezone
>> (essentially),  the my_timestamp_now  is function that returns a "timestamp"
>> for now.
>>
>> This all works fine for pg >= 9 and pg <11.
>
> More information is needed:
>
> 1) Table definition
>
> 2) Exactly how "timestamp" is defined.
>
> 3) my_timestamp_now definition.


Ok - on it's way - in the mean time - what does "record type has not been
registered" mean?


Steve

Re: postgres 11 issue?

From

Alvaro Herrera

Date:

06 June 2019, 19:06:10

On 2019-Jun-06, Steve Rogerson wrote:

> On 06/06/2019 14:35, Adrian Klaver wrote:
> > On 6/6/19 4:02 AM, Steve Rogerson wrote:
> >> I've just updated my laptop to pg11 and I'm getting a problem. I'm  trying to
> >> keeps the details confidential, so somewhat vague I'm afraid.
> >>
> >> sjr_local1db=> select count(*) from user_passwords ;
> >> ERROR:  record type has not been registered


> >> The timestamp column is a "row" consisting of a timestamp and the timezone
> >> (essentially),  the my_timestamp_now  is function that returns a "timestamp"
> >> for now.

> Ok - on it's way - in the mean time - what does "record type has not been
> registered" mean?

It seems a bug to me. Can you share an anonymized/simplified definition
of that table that reproduces the problem?

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: postgres 11 issue?

From

Adrian Klaver

Date:

07 June 2019, 01:37:58

On 6/6/19 8:12 AM, Steve Rogerson wrote:
> On 06/06/2019 14:35, Adrian Klaver wrote:

> 
> Ok - on it's way - in the mean time - what does "record type has not been
> registered" mean?

Difficult to be definitive without knowing what "timestamp" actually is. 
A vague answer is that whatever you defined "timestamp" as is not being 
found.

> 
> 
> Steve
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: postgres 11 issue?

From

Steve Rogerson

Date:

03 July 2019, 19:42:28

> It seems a bug to me. Can you share an anonymized/simplified definition
> of that table that reproduces the problem?
>
Ok See attached sql set up. I guess you need to to a createdb first.

so :

testdb=# \i db.sql
DROP TRIGGER
DROP TABLE
...

testdb=# select * from user_passwords ;
 name | timestamp | password
------+-----------+----------
(0 rows)

testdb=# insert into users (name, password) values  ('fred', 'sdfsdf');
INSERT 0 1

testdb=# select * from user_passwords ;
 name |                  timestamp                  | password
------+---------------------------------------------+----------
 fred | ("2019-07-03 16:37:07.124207",01:00:00,BST) | sdfsdf
(1 row)


testdb=# \q
steve@work-hp pg_prob$ psql testdb
psql (11.3)
Type "help" for help.

testdb=# select * from user_passwords ;
ERROR:  record type has not been registered

Same thing (record type has not been registered/) in a different session that
previously showed user_passwords to be empty in the "normal" way. before the
insert.


It's taken me a while to get this to get it to go wrong and I don't think that
all the complexity is needed, but this at least is consistent.


Steve

Attachment

db.sql

Re: postgres 11 issue?

From

Adrian Klaver

Date:

03 July 2019, 22:32:00

On 7/3/19 9:42 AM, Steve Rogerson wrote:
> 
>> It seems a bug to me. Can you share an anonymized/simplified definition
>> of that table that reproduces the problem?
>>
> Ok See attached sql set up. I guess you need to to a createdb first.
> 
> so :
> 
> testdb=# \i db.sql
> DROP TRIGGER
> DROP TABLE
> ...
> 
> testdb=# select * from user_passwords ;
>   name | timestamp | password
> ------+-----------+----------
> (0 rows)
> 
> testdb=# insert into users (name, password) values  ('fred', 'sdfsdf');
> INSERT 0 1
> 
> testdb=# select * from user_passwords ;
>   name |                  timestamp                  | password
> ------+---------------------------------------------+----------
>   fred | ("2019-07-03 16:37:07.124207",01:00:00,BST) | sdfsdf
> (1 row)
> 
> 
> testdb=# \q
> steve@work-hp pg_prob$ psql testdb
> psql (11.3)
> Type "help" for help.
> 
> testdb=# select * from user_passwords ;
> ERROR:  record type has not been registered
> 
> Same thing (record type has not been registered/) in a different session that
> previously showed user_passwords to be empty in the "normal" way. before the
> insert.
> 
> 
> It's taken me a while to get this to get it to go wrong and I don't think that
> all the complexity is needed, but this at least is consistent.

Hmm:

test=> select * from user_passwords ;
ERROR:  record type has not been registered

test=> select my_from_local(now());
                 my_from_local
----------------------------------------------
  ("2019-07-03 19:29:34.587891",-07:00:00,PDT)
(1 row)

test=> select * from user_passwords ;
  name |                  timestamp                   | password
------+----------------------------------------------+----------
  fred | ("2019-07-03 19:28:07.082895",-07:00:00,PDT) | sdfsdf
(1 row)

Now to figure out why?

> 
> 
> Steve
> 
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: postgres 11 issue?

From

Tom Lane

Date:

03 July 2019, 22:36:56

Steve Rogerson <steve.pg@yewtc.demon.co.uk> writes:
>> It seems a bug to me. Can you share an anonymized/simplified definition
>> of that table that reproduces the problem?

> Ok See attached sql set up. I guess you need to to a createdb first.

It looks like what's happening is that the result of my_from_local()
is being stored into the table as an anonymous record value rather
than a value of type my_timestamp.  (The originating session can figure
out what the anonymous record type is, but no other session can.)
That should be fixed, but in the meantime you could dodge the problem by
declaring my_from_local()'s v_result variable as my_timestamp not record.

            regards, tom lane

Re: postgres 11 issue?

From

Tom Lane

Date:

04 July 2019, 01:18:32

I wrote:
> It looks like what's happening is that the result of my_from_local()
> is being stored into the table as an anonymous record value rather
> than a value of type my_timestamp.  (The originating session can figure
> out what the anonymous record type is, but no other session can.)

I pushed a fix for this:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=24c5c711f497c995ad7b560aedd41b4d0f0f77f4

Thanks for the report!

            regards, tom lane

Re: postgres 11 issue?

From

Steve Rogerson

Date:

05 July 2019, 14:18:56

On 03/07/2019 20:36, Tom Lane wrote:
>
> It looks like what's happening is that the result of my_from_local()
> is being stored into the table as an anonymous record value rather
> than a value of type my_timestamp.  (The originating session can figure
> out what the anonymous record type is, but no other session can.)
> That should be fixed, but in the meantime you could dodge the problem by
> declaring my_from_local()'s v_result variable as my_timestamp not record.
>
Thanks for the quick response. The "workaround" works, though it would seem
best practice in any case.

Steve