Обсуждение: 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 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
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
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
Вложения
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
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: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=24c5c711f497c995ad7b560aedd41b4d0f0f77f4 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