Re: [GENERAL] Wrong string length from unicode database in Borland's app
TL> Alex Guryanow <[EMAIL PROTECTED]> writes: >> When pg-server is version 7.1.3 windows app works fine, but when >> pg-server is version 7.4.6 or 8.0beta4 under certain conditions the >> app receives strings with wrong lengths. TL> Are both servers set up with the same database encoding? I think the answer is yes. For both servers the command "initdb" was executed only with parameter DATADIR. At the same time the locale is set up to "ru_RU.cp1251". But by creating the database I specify parameter "-E UNICODE" and "psql -l" shows that the database is in UNICODE encoding. One time I have forgotten to specify '-E UNICODE' by executing createdb and windows app worked fine with 7.4.6 TL> (Is the 7.1 TL> server even compiled to support non-ASCII encodings?) Here is the fragment of config.status from 7.1.3 source directory ./configure --prefix=/db/pgsql-713 --enable-locale --enable-multibyte --with-perl >> But by executing the query >> select volume, trim(number) from issue where mag_id = 25403; >> the datagrid component (that displays query's results) contains in >> second column values with length of 32769. TL> If you try the same query in plain psql, what do you get? I get all ok. For example, the query select volume, length( trim( number ) ) from issue where mag_id = 25403; shows in second column values from 5 to 7 TL> What is in TL> the wrong-length value, exactly? 'N 1-2' The appropriate "volume" column contains 'Evf. 120' where 'E' is 'E with ascent' (I don't know how to write them in this letter). pg_dump writes the following sequence of bytes (in hex-format) for this value: C3 89 76 66 2E 20 31 32 30 and 'N 1-2' is 4E 20 31 2D 32 Best regards, Alex TL> regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Wrong string length from unicode database in Borland's app
Hi, I have windows app written in Borland C++ Builder 5.0. Using ODBC driver windows app connects to database on linux server. Database is created with UNICODE encoding. When pg-server is version 7.1.3 windows app works fine, but when pg-server is version 7.4.6 or 8.0beta4 under certain conditions the app receives strings with wrong lengths. For example: the database contains table "issue" with the following structure: Column | Type | Modifiers ---+--+--- id| integer | not null ... mag_id| integer | year | character(36)| volume| character(78)| number| character(300) | user_name | character(20)| default getpgusername() mod_date | timestamp with time zone | default now() Only the column "volume" can contain unicode symbols. When the the windows application executes the following query: select year, trim(number) from issue where mag_id = 25403; all works fine. But by executing the query select volume, trim(number) from issue where mag_id = 25403; the datagrid component (that displays query's results) contains in second column values with length of 32769. Those big values are only in rows where appropriate "volume" field contains unicode symbols. Actually all values in column "number" are 5-7 symbols long. By executing the query select year, number from issue where mag_id = 25403; all works fine. I have checked several versions of ODBC-driver: 07.03.0200, 07.05.0001, 08.00.0002 - the effects are the same: all works fine with postgres-server 7.1.3 and works bad with 7.4.6 or 8.0beta4 Changing of driver settings (as described on http://gborg.postgresql.org/project/psqlodbc/faq/faq.php in "Borland Apps" section) does not help too. My setup is: Parse Statements - checked Unknown Sizes - Longest Text as LongVarChar - checked Unknowns as LongVarChar - unchecked Without those settings the windows app works even worse. Please help me to solve this problem. May be I'm doing something wrong? May be my windows is set up incorrectly? (I'm using Win2K SP4) Or is this a ODBC-driver's bug? Or server's? Or may be it's a Borlands BDE error? Has somebody used windows app written in borland to connect to unicode table under postgresql > 7.1.3 ? Best regards, Alex ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Solaris 8 compilation errors
JG> I don't see Solaris 8 on the list of supported platforms, I have compiled postgres 7.0.3 on SPARC/Solrais 8. But it was compiled w/o tcl support. There is no much time since installation, but yet all works fine. JG> but Solaris 7 is JG> there, so I had hoped 8 would work pretty much the same. Has anyone else run JG> into this problem? JG> -john
Re[6]: [GENERAL] SPI_getvalue problem
Monday, January 29, 2001, 9:49:32 AM Tom wrote: TL> Alex Guryanow <[EMAIL PROTECTED]> writes: >> DEBUG: tupdesc is NULL TL> Hm. Well, I can assure you that rd_att will *never* be null in a valid TL> relation cache entry. So there is something wrong with either TL> CurrentTriggerData, the relation pointer, or your function's TL> interpretation of the structures. TL> A thought that comes to mind here is that perhaps your function was TL> compiled against the wrong set of header files, causing it to think TL> that rd_att is at a different offset in the relation struct than what TL> the backend thinks. Do you have more than one version of Postgres TL> installed on the Solaris machine, Yes, but they are 7.0.2 and 7.0.3 TL> and if so where are you picking up TL> the header files while building the library? Here is my Makefile for trigger install: test4.so cp -f test4.so /home/gav/pgsql/lib/trigger.so test4.so: test4.o ld -G -Bdynamic -o test4.so test4.o /usr/local/lib/libicuuc.so test4.o: test4.c g++ -c -o test4.o -I /home/gav/pgsql/include -I /home/gav/postgresql-7.0.3/src/include test4.c /home/gav/postgresql-7.0.3 is catalog with sources /home/gav/pgsql is target for installation (./configure --prefix=/home/gav/pgsql ...) By the way. To compile using g++ I have added two lines to src/include/nodes/parsenodes.h: #ifndef PARSENODES_H #define PARSENODES_H + #define typename gav_typename + #define class gav_class #include "nodes/primnodes.h" because without them g++ encounters some errors. Possible this is the cause? Best regards, Alex
Re[4]: [GENERAL] SPI_getvalue problem
Monday, January 29, 2001, 9:33:06 AM Tom wrote: TL> Alex Guryanow <[EMAIL PROTECTED]> writes: >> You have right. One of the values passed to SPI_getvalue is NULL. This is second >parameter >> (tupdesc). But why on Linux it is not NULL, and on Solaris is? >> Here is part of my trigger-code: >> rel = CurrentTriggerData->tg_relation; >> trigtuple = CurrentTriggerData->tg_trigtuple; >> newtuple = CurrentTriggerData->tg_newtuple; >> tupdesc = rel->rd_att; >> elog(DEBUG, "before 1 SPI"); >> elog(DEBUG, "triggered for relation %s", >SPI_getrelname(CurrentTriggerData->tg_relation) ); >> id = atoi( SPI_getvalue( trigtuple, tupdesc, 1 ) ); >> elog( DEBUG, "before 1.5 SPI" );// !!! this isn't called in Solaris TL> Are you sure that rel->rd_att is null? That seems extremely improbable. I have added the followind code after tupdesc = rel->rd_att; // (see above) if( tupdesc == NULL || trigtuple == NULL ){ elog( DEBUG, "tupdesc OR trigtuple == NULL" ); if( tupdesc == NULL ) elog( DEBUG, "tupdesc is NULL" ); if( trigtuple == NULL ) elog( DEBUG, "trigtuple is NULL" ); } else elog( DEBUG, "tupdesc && trigtuple ARE NOT NULL" ); And here is what I see in my postmaster.log: DEBUG: tupdesc OR trigtuple == NULL DEBUG: tupdesc is NULL DEBUG: before 1 SPI DEBUG: triggered for relation magazine Server process (pid 925) exited with status 139 at Mon Jan 29 08:59:23 2001 Terminating any active server processes... Server processes were terminated at Mon Jan 29 08:59:23 2001 Reinitializing shared memory and semaphores On Linux instead first two lines log contains DEBUG: tupdesc && trigtuple ARE NOT NULL Best regards, Alex
Re[2]: [GENERAL] SPI_getvalue problem
Sunday, January 28, 2001, 8:24:51 PM Tom wrote: TL> Alex Guryanow <[EMAIL PROTECTED]> writes: >> I have the following problem: the backend crashes on Solaris while >> executing the function SPI_getvalue. >> At the same time on Linux this trigger works fine. >> Is this a bug or my misconfuguration? TL> Sounds like a bug to me, but you haven't demonstrated that the bug is in TL> SPI_getvalue and not in your own code. The first thing I'd wonder about TL> is if your trigger function is checking for NULL value before calling TL> SPI_getvalue (or at least before trying to do anything useful with the TL> result). You have right. One of the values passed to SPI_getvalue is NULL. This is second parameter (tupdesc). But why on Linux it is not NULL, and on Solaris is? Here is part of my trigger-code: rel = CurrentTriggerData->tg_relation; trigtuple = CurrentTriggerData->tg_trigtuple; newtuple = CurrentTriggerData->tg_newtuple; tupdesc = rel->rd_att; elog(DEBUG, "before 1 SPI"); elog(DEBUG, "triggered for relation %s", SPI_getrelname(CurrentTriggerData->tg_relation) ); id = atoi( SPI_getvalue( trigtuple, tupdesc, 1 ) ); elog( DEBUG, "before 1.5 SPI" );// !!! this isn't called in Solaris Best regards, Alex
[GENERAL] SPI_getvalue problem
Hi, I have the following problem: the backend crashes on Solaris while executing the function SPI_getvalue. I have small database on Intel/Linux that works fine. Now I need to transfer them to Sparc/Solaris. This database contains a small trigger written in C++. After installing postgresql-7.0.3 on Solaris and compiling the trigger function by inserting a new row in table I receive the following: pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. connection to server was lost And in postgres log I receive: DEBUG: start update_prednet_words function DEBUG: before 1 SPI DEBUG: triggered for relation magazine Server process (pid 1397) exited with status 139 at Sun Jan 28 17:23:48 2001 Terminating any active server processes... Server processes were terminated at Sun Jan 28 17:23:48 2001 Reinitializing shared memory and semaphores DEBUG: Data Base System is starting up at Sun Jan 28 17:23:48 2001 DEBUG: Data Base System was interrupted being in production at Sun Jan 28 17:23:30 2001 DEBUG: Data Base System is in production state at Sun Jan 28 17:23:48 2001 First 3 lines are from my C-function, after logging the string "triggered for relation ..." it calls SPI_getvalue. And the backend crashes. And this happens on Solaris 2.6 and Solaris 2.8 for both postgresql-7.0.2 and 7.0.3. At the same time on Linux this trigger works fine. Is this a bug or my misconfuguration? My setup is Linux: RedHat 6.2, Intel Pentium II-400, 128 Mb Solris: SunOS 5.8 Generic_108528-02 sun4u sparc SUNW,Ultra-60, SunOS 5.6 Generic_105181-19 sun4u sparc SUNW,Ultra-1 postgres is configured in the following way: ./configure --enable-locale --enable-multibyte=UNICODE Best regards, Alex Guryanow
[GENERAL] SPI_modifytuple for varchar
Hi, my query is about using the function SPI_modifytuple for a varchar field. I have (for example) a table with a field my_field of type varchar(100). And I need to create a trigger that adds the phrase "hello, world" to any value, that user inserts (or updates) in field my_field. Using SPI_getvalue I can retrive the value that user tries to insert, I can concatenate this value with my phrase "hello world" using standard function strcat, but I do not understand what arguments should I pass to SPI_modifytuple function. Say my_value is the pointer to the concatenated string: char *my_value; If I use the function SPI_modifytuple( rel, newtuple, 1, attnums, my_value, NULL ) then my backend crashes. Can anybody say what I'm doing wrong or give me an example of modification of varchar value using SPI_* interface? Best regards, Alex
[GENERAL] sorting in UNICODE table
Hi, I'm use postgresql-7.0.2. It's compiled with unicode support (./configure --enable-multibyte=UNICODE ...) I have a table which contains both latin and non-latin letters. All they are in UTF-8 encoding. When I try to sort the rows ( SELECT * FROM my_table ORDER BY sort_field ) I receive strange error: the rows that begin with ascii symbols are ordered while all other - are not! Why is this happen? Regards, Alex
[GENERAL] using INTERSECT and UNION in IN clause
Hi, postgresql 7.0.2. Why by executing the following query select * from magazine where id in ( select mag_id from dict where word = 'akademie' intersect select mag_id from dict where word = 'der' intersect select mag_id from dict where word = 'klasse' ) I receive the following error: ERROR: parse error at or near 'intersect' while the query select mag_id from dict where word = 'akademie' intersect select mag_id from dict where word = 'der' intersect select mag_id from dict where word = 'klasse' ) is executed successfully. Is it possible to use INTERSECT and UNION keywords in subqueries? Regards, Alex
[GENERAL] problem with plpgsql function
Hi, I have a table "isbn" (postgres-7.0.2) with two columns: book_id int4 isbn varchar(20) I want to create a function that joins all isbn for specified book_id. The code of this function is: CREATE FUNCTION all_isbn2( int4 ) RETURNS varchar AS 'DECLARE i isbn%ROWTYPE; res varchar; BEGIN res := ""; FOR i IN SELECT * FROM isbn WHERE isbn.book_id = $1 LOOP res := res || i.isbn; END LOOP; RETURN res; END;' LANGUAGE 'plpgsql'; But when i execute the query book=> select all_isbn2(1); I receive the following error: ERROR: Attribute '' not found What I'm doing wrong? Regards, Alex
[GENERAL] pqReadData() error
Hi, during execution of block of commands like BEGIN INSERT INTO... INSERT INTO... . INSERT INTO... COMMIT sometimes receive the error pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. 3 last lines in postmaster's log are: ERROR: Cannot insert a duplicate key into a unique index ERROR: Cannot insert a duplicate key into a unique index FATAL 1: my bits moved right off the end of the world! Between BEGIN-COMMIT the program (written in C) makes 100 to 5 INSERTs. In the database I have two tables, say t1 and t2. Table t1 has an unique index and two first lines from the error-messages above are when the program tryis to make INSERT duplicate rows. But the error pqReadData() I recieve when try to insert into the second table t2. It hasn't the unique index. My configurations is: postgresql 6.5.3 , RedHat 6.1 on Intel-based server, RAM 256 Mb, swap 256 Mb, HDD 2x9Gb. Best regards, Alex
[GENERAL] uninterruptible sleep
Hi, I'm use postgresql 6.5.3 on RedHat 6.1. To monitor the perfomance of the system I use top. When I run VACUUM ANALYZE in psql monitor top shows sometimes the state of postmaster as D. "man top" says that D in STAT column is "uninterruptible sleep". Is this normal? Best regards, Alex
[GENERAL] inserting timestamp values
Hi, I have troubles inserting the timestamp values. I use postgresql 6.5.3 on RedHat 6.1 (kernel 2.2.13). I do the following sample==> create table t1 (d timestamp); CREATE sample==> create index t1_d on t1 using btree (d datetime_ops); CREATE sample==> insert into t1 (d) values ('1999-01-01 14:00:00+03'); INSERT 4668876 1 sample==> insert into t1 (d) values ('1999-01-01 14:00:00+03'); pqReadData() -- backend closed the channel unexpectedly. This possible means the backend terminated abnormally before or while processing the request. We have lost the connection to the backend, so further processing is impossible. Terminating. Can anybody help me to solve this problem ? Best regards, Alex P.S. Before this experiment I have the database in postgresql 6.5.3 too. One of tables in this database contains the timestamp field. I used the pg_dump utility to backup this database and received the flat file where all values of this field are in the format '-mm-dd HH:MM:SS+04'. When I try to restore the database using this backup-file: psql mydata < I receive the error as above.
[GENERAL] NOTICE: (transaction aborted): queries ignored until END
Hi, I'm use Postgres-6.5.1 on Linux. My database contains two tables and two indices. I wrote simple program on C that - begins transaction: BEGIN WORK - inserts many (10-500) rows in the first table - commits the transaction: COMMIT - begins transaction: BEGIN WORK - inserts many (1-50) rows in the second table - commits the transaction: COMMIT During execution of this program I receive very often the following message: NOTICE: (transaction aborted): queries ignored until END that repeated many times. What means this message? And why this happens? Best regards, Alex