Обсуждение: missed features and unhappy changes when pg 7.1->7.2
Hello! Maybe I missed something but what are reasons to change behaviour in 7.2 in comparison with 7.1? I mean that early PG just truncated the rest (tail) of extra long string (i.e. where len(string)>char(this attr) but insert the head. Now inserting fails. Is it tunable somewhere in GUC so I could revert to old behaviour? And yet, what is the Right Way to deal with timestamp? Now I'm using Thomas's recipe \"timestamp\" but it loooks wierd and bad. -- WBR, Yury Bokhoncovich, Senior System Administrator, NOC of F1 Group. Phone: +7 (3832) 106228, ext.140, E-mail: byg@center-f1.ru. Unix is like a wigwam -- no Gates, no Windows, and an Apache inside.
A fair number of people were a little bugged about the change from silent truncation to throwing an error, including me. I wasn't so much bothered by the change, but rather that it wasn't in the migration notes. Unfortunatly it turned up a few lazy programmer mistakes amongst my colleagues and I. No big problem for me though, I changed the atttypmod attribute of the pg_attribute system catalog for the problem attributes, thereby putting off my work a little longer ;) For the date thing, you can do: SELECT datetime '01/01/01 01:01:01'; or SELECT datetime('01/01/01 01:01:01'); or SELECT timestamp '01/01/01 01:01:01'; /* the last one returns a timezonetz type though */ I suppose it depends on your needs, but I think those will work mostly the same. Regards, Jeff On Friday 20 September 2002 12:08 am, Yury Bokhoncovich wrote: > Hello! > > Maybe I missed something but what are reasons to change behaviour in 7.2 > in comparison with 7.1? > I mean that early PG just truncated the rest (tail) of extra long string > (i.e. where len(string)>char(this attr) but insert the head. Now inserting > fails. Is it tunable somewhere in GUC so I could revert to old behaviour? > > And yet, what is the Right Way to deal with timestamp? > Now I'm using Thomas's recipe \"timestamp\" but it loooks wierd and bad.
Hello! On Fri, 20 Sep 2002, Jeff Davis wrote: > > A fair number of people were a little bugged about the change from silent > truncation to throwing an error, including me. > > I wasn't so much bothered by the change, but rather that it wasn't in the > migration notes. Unfortunatly it turned up a few lazy programmer mistakes > amongst my colleagues and I. No big problem for me though, I changed the Yep. Quik alter fixed all. But it costs me a lot nervos.:( Luckly I had spare way to store info in the file. So, do you know where to dig?8) > atttypmod attribute of the pg_attribute system catalog for the problem > attributes, thereby putting off my work a little longer ;) > > For the date thing, you can do: > SELECT datetime '01/01/01 01:01:01'; > or SELECT datetime('01/01/01 01:01:01'); > or SELECT timestamp '01/01/01 01:01:01'; > /* the last one returns a timezonetz type though */ Hm...I meant commands like this: "UPDATE ${acct_table1} SET AcctStopTime='%S', AcctSessionTime=\"interval\"(\"timestamp\"('%S') - AcctStartTime), AcctTerminateCause='%{Acct-Terminate-Cause}', AcctStopDelay = %{Acct-Delay-Time} WHERE AcctSessionTime=NULL AND AcctStopTime=NULL AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStartTime <= '%S'; Looks weird for me now but it works. pure interval and timestamp gave errors for a known reason. > > (i.e. where len(string)>char(this attr) but insert the head. Now inserting > > fails. Is it tunable somewhere in GUC so I could revert to old behaviour? > > > > And yet, what is the Right Way to deal with timestamp? > > Now I'm using Thomas's recipe \"timestamp\" but it loooks wierd and bad. -- WBR, Yury Bokhoncovich, Senior System Administrator, NOC of F1 Group. Phone: +7 (3832) 106228, ext.140, E-mail: byg@center-f1.ru. Unix is like a wigwam -- no Gates, no Windows, and an Apache inside.
> Yep. Quik alter fixed all. But it costs me a lot nervos.:( Luckly I had > spare way to store info in the file. So, do you know where to dig?8) Sorry, I don't understand your question. > > > atttypmod attribute of the pg_attribute system catalog for the problem > > attributes, thereby putting off my work a little longer ;) > > > > For the date thing, you can do: > > SELECT datetime '01/01/01 01:01:01'; > > or SELECT datetime('01/01/01 01:01:01'); > > or SELECT timestamp '01/01/01 01:01:01'; > > /* the last one returns a timezonetz type though */ > > Hm...I meant commands like this: > > "UPDATE ${acct_table1} SET AcctStopTime='%S', > AcctSessionTime=\"interval\"(\"timestamp\"('%S') - AcctStartTime), > AcctTerminateCause='%{Acct-Terminate-Cause}', AcctStopDelay = > %{Acct-Delay-Time} WHERE AcctSessionTime=NULL AND AcctStopTime=NULL AND > NASIPAddress= '%{NAS-IP-Address}' AND AcctStartTime <= '%S'; > The things I wrote above should work in the update statement as well. timestamp is now a reserved word so you need to quote it to call the function, but you can still use the casting version of it, or those other things I mentioned up there. If you still have problems with the query let me know. Try, for example, replacing "timestamp" with datetime in the query you showed me. Regards, Jeff Davis
Jeff Davis <list-pgsql-general@empires.org> writes: > A fair number of people were a little bugged about the change from silent > truncation to throwing an error, including me. BTW, after further study of the SQL spec we concluded that we still didn't have it quite right. 7.3 will throw an error only during implicit coercion to char(N) or varchar(N); an explicit coercion will silently truncate. For example: update foo set varchar4col = '12345'; -- throws error update foo set varchar4col = '12345'::varchar(4); -- stores '1234' Dunno if this will help either of you at this point; you've probably already changed your apps to not need it. >> And yet, what is the Right Way to deal with timestamp? > Try, for example, replacing "timestamp" with datetime in the query you > showed me. Datetime is an obsolete alias for timestamptz ... it will go away in 7.3, so I'd recommend not using it now ... regards, tom lane
On Fri, 20 Sep 2002, Yury Bokhoncovich wrote: > Maybe I missed something but what are reasons to change behaviour in 7.2 > in comparison with 7.1? > I mean that early PG just truncated the rest (tail) of extra long string > (i.e. where len(string)>char(this attr) but insert the head. Now inserting > fails. Is it tunable somewhere in GUC so I could revert to old behaviour? Not currently. This was a push to become more complient with sql's desired handling for strings like that. > And yet, what is the Right Way to deal with timestamp? > Now I'm using Thomas's recipe \"timestamp\" but it loooks wierd and bad. For most of the uses of timestamp(), you probably should now be using either a timestamp literal (timestamp '...') or a cast (cast(... as timestamp). The reason this changed was again an sql compatibility issue, timestamp(n) in sql terms is a type with a particular precision.