postgres 11 issue?
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
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 registeredsjr_local1db=> insert into user_passwords (name, "timestamp", password) values
('user1', my_timestamp_now(), 'dsfsdfsdf');
INSERT 0 1sjr_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
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 registeredsjr_local1db=> insert into user_passwords (name, "timestamp", password) values
('user1', my_timestamp_now(), 'dsfsdfsdf');
INSERT 0 1sjr_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
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
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
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
Attachments:
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 1testdb=# 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 registeredSame 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
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
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:
Thanks for the report!
regards, tom lane
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