Thread: How to make silently truncate for char type
I upgraded postgresql 7.1 to 7.2 and found that many
sql cannot execute because version 7.2 does not
silently truncate the string for char(n) type during
insert a new row.
e.g. insert t (field1) value ('abcdef');
where field1 is char(5) but I insert the string
has 6 characters.
How can I solve the problem except downgrade the
Postgresql?
Looking forward to your reply!!!
=====
Yours sincerely,
Leung Chun Man, Terence
Mobile: (852) 9273 9176
Homepage: http://tcmleung.uhome.net ,
http://www.dynamicdrive.com/dynamicindex4/filter/index.htm
__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
Actually, I consider it normal behavior of a database to have an error if
the
data value length is greater than the defined length. If it needs to be
truncated, you do
the truncate before you insert into the table. This is the way it works on
enterprise databases like Oracle. Maybe it was a bug in 7.1 that
automatically truncated for you. I don't know for sure as I only have 7.2
installed.
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Terence Leung
Sent: Sunday, December 08, 2002 7:02 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] How to make silently truncate for char type
I upgraded postgresql 7.1 to 7.2 and found that many
sql cannot execute because version 7.2 does not
silently truncate the string for char(n) type during
insert a new row.
e.g. insert t (field1) value ('abcdef');
where field1 is char(5) but I insert the string
has 6 characters.
How can I solve the problem except downgrade the
Postgresql?
Looking forward to your reply!!!
=====
Yours sincerely,
Leung Chun Man, Terence
Mobile: (852) 9273 9176
Homepage: http://tcmleung.uhome.net ,
http://www.dynamicdrive.com/dynamicindex4/filter/index.htm
__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Creating a rule/trigger (before insert) with the appropriate substr
function would, I imagine, do the trick.
Regards,
Aasmund
* Terence Leung <tcmleung@yahoo.com> wrote:
> I upgraded postgresql 7.1 to 7.2 and found that many
> sql cannot execute because version 7.2 does not
> silently truncate the string for char(n) type during
> insert a new row.
> e.g. insert t (field1) value ('abcdef');
> where field1 is char(5) but I insert the string
> has 6 characters.
>
> How can I solve the problem except downgrade the
> Postgresql?
>
> Looking forward to your reply!!!
>
> =====
> Yours sincerely,
> Leung Chun Man, Terence
>
> Mobile: (852) 9273 9176
> Homepage: http://tcmleung.uhome.net ,
> http://www.dynamicdrive.com/dynamicindex4/filter/index.htm
>
> __________________________________________________
> Do you Yahoo!?
> Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
> http://mailplus.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
Aasmund Midttun Godal
aasmund@godal.com - http://www.godal.com/
+47 40 45 20 46 - Elgesetergt. 26B, N-7030 Trondheim
Is it possible, via a shared library that contains functions used by postgres, to detect when the library is first loaded and when it is unloaded? Also, each postgres process loads its own copy of a library that contains functions. Is it possible to make a single library instance shared across all postgres processes? I suspect the answer is no b/c I assume the postmaster exec()'s postgres, rather than forking, but it doesn't hurt to ask. thanks in advance! eric
First, let me say that table functions are really cool!
Let's say I've defined a table function named: myTableFunc() (written
in C), and that I do this:
select a, b, c
from myTableFunc() as foo (a integer, b varchar(255), c text)
where a = 2;
Is it possible for me to get the FROM, WHERE, and AS ... clauses from
within my table function code? I'd like to do this so my table
function can be a little smarter about what it does and what it returns.
Also, in psql, if I press ^C while a table function is executing, the
function doesn't actually stop, but psql does say "Cancel request
sent". What processes this request? How do I tell postgres that I
want to be notified too?
thanks again!
eric
Eric B.Ridge wrote: > Let's say I've defined a table function named: myTableFunc() (written in > C), and that I do this: > > select a, b, c > from myTableFunc() as foo (a integer, b varchar(255), c text) > where a = 2; > > Is it possible for me to get the FROM, WHERE, and AS ... clauses from > within my table function code? I'd like to do this so my table function > can be a little smarter about what it does and what it returns. You can get the information related to the "as foo (a integer, b varchar(255), c text)" aka column definition clause. It is passed in to the function. See contrib/tablefunc/tablefunc.c:connectby_text(). The ReturnSetInfo structure handed to the function includes a copy of the tupdesc formed by the parser (expectedDesc). This capability was added so late in the 7.3 developemnt cycle that I don't think it is documented anywhere other than the source code and some posts Tom Lane made to the list (although I'll have to look -- he may have snuck it in somewhere). As far as the FROM and WHERE clauses, I don't think there is any information available to the function. Not sure it would be practical or even possible to change that -- maybe someone else will comment on this. Joe
On Monday, December 9, 2002, at 01:01 AM, Joe Conway wrote: > Eric B.Ridge wrote: >> Let's say I've defined a table function named: myTableFunc() (written >> in C), and that I do this: >> select a, b, c >> from myTableFunc() as foo (a integer, b varchar(255), c text) >> where a = 2; >> Is it possible for me to get the FROM, WHERE, and AS ... clauses from >> within my table function code? I'd like to do this so my table >> function can be a little smarter about what it does and what it >> returns. > > You can get the information related to the "as foo (a integer, b > varchar(255), c text)" aka column definition clause. It is passed in > to the function. See contrib/tablefunc/tablefunc.c:connectby_text(). > The ReturnSetInfo structure handed to the function includes a copy of > the tupdesc formed by the parser (expectedDesc). great! I was snooping through tablefunc.c, but didn't really know what I was looking for. Thanks! <snip> > As far as the FROM and WHERE clauses, I don't think there is any > information available to the function. Not sure it would be practical > or even possible to change that -- maybe someone else will comment on > this. hmm. eric
Eric B.Ridge <ebr@tcdi.com> writes: > Is it possible, via a shared library that contains functions used by > postgres, to detect when the library is first loaded and when it is > unloaded? You could have a 'static' variable in the library, that all its functions check first thing, and set to 1 after checking. That would be a little clumsy, but would work. As for unloading, I don't think you can detect it other than running a function on process exit with an atexit() handler. > Also, each postgres process loads its own copy of a library that > contains functions. Is it possible to make a single library instance > shared across all postgres processes? I suspect the answer is no b/c > I assume the postmaster exec()'s postgres, rather than forking, but it > doesn't hurt to ask. Shared libraries are not shared memory. Each process has its own copy of the library data area; though the text (library code) is usually shared, it's read-only. If you want chared memory then use the SysV or POSIX shm API. -Doug
On 9 Dec 2002 at 8:42, Doug McNaught wrote: > Eric B.Ridge <ebr@tcdi.com> writes: > > > Is it possible, via a shared library that contains functions used by > > postgres, to detect when the library is first loaded and when it is > > unloaded? > > You could have a 'static' variable in the library, that all its > functions check first thing, and set to 1 after checking. That would > be a little clumsy, but would work. I do not know the postgresql code, but I believe, postgresql must be doing a dlopen internally. So if you define _init and _fini, they would called while loading and unloading the library. At least it is that way on linux. Check with man page of dlopen on the platform you are running.. HTH Bye Shridhar -- Great American Axiom: Some is good, more is better, too much is just right.
"Eric B.Ridge" <ebr@tcdi.com> writes:
> Is it possible for me to get the FROM, WHERE, and AS ... clauses from
> within my table function code?
AS: yes, as Joe already mentioned.
FROM/WHERE: No, and I think it would be quite inappropriate for the
function's behavior to depend on any such info. To work correctly,
you'd need to encode virtually a complete understanding of SQL into your
function --- consider cases like outer joins or grouping that intervene
between your function call and the FROM/WHERE clauses. And those are
just the simpler cases.
> Also, in psql, if I press ^C while a table function is executing, the
> function doesn't actually stop, but psql does say "Cancel request
> sent". What processes this request? How do I tell postgres that I
> want to be notified too?
You can throw a
CHECK_FOR_INTERRUPTS();
into your main loop whereever it seems safe to be killed by an
interrupt.
regards, tom lane
Joe Conway <mail@joeconway.com> writes:
> This capability was added so late in the 7.3 developemnt cycle that I don't
> think it is documented anywhere other than the source code and some posts Tom
> Lane made to the list (although I'll have to look -- he may have snuck it in
> somewhere).
src/backend/utils/fmgr/README has
: If the function is being called as a table function (ie, it appears in a
: FROM item), then the expected tuple descriptor is passed in ReturnSetInfo;
: in other contexts the expectedDesc field will be NULL. The function need
: not pay attention to expectedDesc, but it may be useful in special cases.
Someday that README should be rewritten and merged into the SGML
programmer's guide. Right now, it's essential reading for backend
function authors.
regards, tom lane
> FROM/WHERE: No, and I think it would be quite inappropriate for the > function's behavior to depend on any such info. To work correctly, > you'd need to encode virtually a complete understanding of SQL into > your very true. I could probably make a case for this being useful in highly specialized functions. But in general, I agree, not a good idea. I've been working on a dblink clone that speaks JDBC via JNI, and am just trying to figure out what postgres will (and won't) let me do. > You can throw a > CHECK_FOR_INTERRUPTS(); > into your main loop whereever it seems safe to be killed by an > interrupt. cool! Is this documented somewhere? Did I overlook it? thanks! eric
"Eric B. Ridge" <ebr@tcdi.com> writes:
>> You can throw a
>> CHECK_FOR_INTERRUPTS();
>> into your main loop whereever it seems safe to be killed by an
>> interrupt.
> cool! Is this documented somewhere?
Uh, only in src/include/miscadmin.h I'm afraid :=(
regards, tom lane
Tom Lane wrote: > src/backend/utils/fmgr/README has > > : If the function is being called as a table function (ie, it appears in a > : FROM item), then the expected tuple descriptor is passed in ReturnSetInfo; > : in other contexts the expectedDesc field will be NULL. The function need > : not pay attention to expectedDesc, but it may be useful in special cases. > > Someday that README should be rewritten and merged into the SGML > programmer's guide. Right now, it's essential reading for backend > function authors. I remember that now. I think I even indicated that I would do the merging into the programmers guide -- I guess I better get busy :-) Joe