Thread: missed features and unhappy changes when pg 7.1->7.2

missed features and unhappy changes when pg 7.1->7.2

From

Yury Bokhoncovich

Date:

20 September 2002, 06:06:50

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.

Re: missed features and unhappy changes when pg 7.1->7.2

From

Jeff Davis

Date:

20 September 2002, 06:26:34

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.

Re: missed features and unhappy changes when pg 7.1->7.2

From

Yury Bokhoncovich

Date:

20 September 2002, 09:10:56

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.

Re: missed features and unhappy changes when pg 7.1->7.2

From

Jeff Davis

Date:

20 September 2002, 09:48:25

> 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

Re: missed features and unhappy changes when pg 7.1->7.2

From

Tom Lane

Date:

20 September 2002, 13:37:58

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

Re: missed features and unhappy changes when pg 7.1->7.2

From

Stephan Szabo

Date:

20 September 2002, 14:00:31

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.