Re: [BUGS] 10 concurrent clients / pure insert SQL scripts (each 100000 lines)
Hi Tom! Thank you for your reply. > > Is this bug fixed in 7.2.3? > > Try it and see. > I tried it on another box (RedHat Linux 8.0, much faster and newer than the OpenBSD box) and it took 2000 seconds (10 times faster than the 7.1.3 on the slower OpenBSD box). Btw: MySQL on the slower box is able to insert 50 times faster (I assume this is because of worse reliability). Btw: PostgreSQL 7.1.3 was 4 times slower (160 seconds) with a single client than MySQL with a single client. This factor increased appr. by the factor 12 (from 4 to 50) when I used 10 concurrent clients. Concluding I still have the impression that the semaphore handling on i386 RedHat Linux and i386 OpenBSD in combination with PostgreSQL leads to bad performance. > FWIW, on HPUX with current CVS tip I could not measure any difference in > performance between a single client issuing INSERTs like yours and ten > clients doing so: I got essentially the same number of insertions/sec > either way. > That sounds good... :-)) But I cannot afford HP UX. :-)) Due to the nature of our applications performance is not so important for us. That means that I do not consider this bug to be severe. Bye Arne ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] Issues with german 'Umlaute'
Tom Lane wrote: > > Nicolaus Erichsen <[EMAIL PROTECTED]> writes: > > I recently found a problem with sorting german 'Umlaute' . > > Sounds like you did not set the right locale when creating > the database. > You need to be careful to run initdb with LANG (or LC_ALL or at least > LC_COLLATE) set to what you want, probably "de_DE". > > > All this happens with a database created with encoding ='latin1'. > > Encoding is not the issue, locale is. Then what about having German, English, Italian and French words in the same database? Shall we create four databases and place each language in a separate one? Iavor -- Iavor Raytchev very small technologies (a company of CEE Solutions) www.verysmall.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] VACUUM ANALYZE differs under 7.2.3 from 7.2.1
The "VACUUM ANALYZE tablename" command does not have the same effect on table metadata under 7.2.1 and 7.2.3. In particular, I've noted that pg_class.reltuples is not updated by vacuuming after a delete. Here is a sequence of SQL commands to demonstrate the difference. Under 7.2.1, the resulting last three displays of the pg_class.reltuples value will be zero (0), while under 7.2.3 it will be 10. create table foo (a char(1)); copy foo from stdin; a a a a a a a a a a \. \x \t select count(*) from foo; select reltuples from pg_class where relname = 'foo'; vacuum foo; select reltuples from pg_class where relname = 'foo'; vacuum analyze foo; select reltuples from pg_class where relname = 'foo'; analyze foo; select reltuples from pg_class where relname = 'foo'; delete from foo; select reltuples from pg_class where relname = 'foo'; select count(*) from foo; select reltuples from pg_class where relname = 'foo'; vacuum foo; select reltuples from pg_class where relname = 'foo'; vacuum analyze foo; select reltuples from pg_class where relname = 'foo'; analyze foo; select reltuples from pg_class where relname = 'foo'; drop table foo; ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[BUGS] error creating 'plpgsql' language
$ createlang --pglib=/usr/local/pgsql/lib plpgsql template1 ERROR: Load of file /usr/local/pgsql/lib/plpgsql.so failed: ld.so.1: /usr/local/pgsql/bin/postmaster: errore fatale:libgcc_s.so.1: impossibile aprire: File o directory non trovati createlang: language installation failed $ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] select fails to return certain rows from table
I am selecting rows from a table using "select * from gla where glacode = $glresult[0]" $glresult[0] comes from a previous query result. For the first three rows in the table I get the error "Warning: Unable to jump to row 0 on PostgreSQL result index 4 in /home/httpd/infocus/html/iris/ben_glrep.php on line 65The other rows in the table are found ok. The glacode field is a varchar The first three values of glacode are "010", "020", "050" The rest of the values of glacode all begin with something other than "0" e.g. "110", "115", "120" etc. So it appears that the leading "0" is a problem. I have also tried this in phpPgAdmin and I get the same results without the error message. Your help is gratefully appreciated _Andy Dunlopmailto:[EMAIL PROTECTED] Office: +27 (21) 975 2440Cell: +27 (82) 770 8749_
[BUGS] Technical Question
Is postgreSQL ODBC compliant? Will it operate with MS Windows 2000 or NT? Your assistance with this questions would be greatly appreciated. Cheers, Michael ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] socket problem under BeOS
Hallo! I have a problem with BeOS and PostgreSQL7.2.1 with BONE. The follow error is comming. $ createdb db01 psql: could not create socket: Address family not supported by protocol family createdb: database creation failed Please help me. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] VACUUM ANALYZE differs under 7.2.3 from 7.2.1
Jeff Boes <[EMAIL PROTECTED]> writes: > The "VACUUM ANALYZE tablename" command does not have the same effect on > table metadata under 7.2.1 and 7.2.3. In particular, I've noted that > pg_class.reltuples is not updated by vacuuming after a delete. Hard to believe ... I see no changes in the 7.2.* CVS logs that might cause such a problem. > Here is a sequence of SQL commands to demonstrate the difference. Under > 7.2.1, the resulting last three displays of the pg_class.reltuples value > will be zero (0), while under 7.2.3 it will be 10. Here I get zeroes, as you expect, with 7.2.3. Some problem with your installation perhaps? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[BUGS] Bug #806: create function failing... ERROR: stat failed on file '
Lynn Tilby ([EMAIL PROTECTED]) reports a bug with a severity of 1
The lower the number the more severe it is.
Short Description
create function failing... ERROR: stat failed on file '
Long Description
DEBUG: ProcessUtility: create function dys_btwn_dates ( date , date ) returns int
as '
BEGIN
RETURN $1 - $2;
END;
' language 'c'
ERROR: stat failed on file '
BEGIN
RETURN $1 - $2;
END;
': No such file or directory
Sample Code
!! CPG SOURCE CODE !!
/* SET TABSTOP = 2 */
/* days between dates an attempt at using the 'date' data type in postgres
to try and determine the actual days between calendar dates.
*/
#include
EXEC SQL INCLUDE sqlca; /* include the sql debugging stuff... */
/* !! POSTGRES SQL DEBUGGING STRUCTURE FIELDS /
#define DEBUG_SQL printf("sqlcaid = %s\n", sqlca.sqlcaid); printf("sqlabc = %f\n",
sqlca.sqlabc); printf("sqlcode = %f\n", sqlca.sqlcode); printf("sqlca.sqlerrm.sqlerrml
= %d\n", sqlca.sqlerrm.sqlerrml); printf("sqlca.sqlerrm.sqlerrmc = %s\n",
sqlca.sqlerrm.sqlerrmc); printf("sqlerrp = %s\n", sqlca.sqlerrp); printf("sqlerrd =
%f\n", sqlca.sqlerrd); printf("sqlext = %s\n", sqlca.sqlext);
int main()
{
EXEC SQL BEGIN DECLARE SECTION; /* declare the variables used by sql */
char t_date1[] = {'1','9','9','9','-','0','3','-','0','1','\0'};
char t_date2[] = {'1','9','9','9','-','0','1','-','0','1','\0'};
int date_rtrn = 20;
chart_in_date[12];
EXEC SQL END DECLARE SECTION; /* declare the variables used by sql */
EXEC SQL CONNECT TO pma;
EXEC SQL WHENEVER sqlerror sqlprint; /* turn on verbose sql error ckg */
printf("after whenever\n");
EXEC SQL CREATE FUNCTION dys_btwn_dates(date, date) RETURNS int AS '
BEGIN
RETURN $1 - $2;
END;
' LANGUAGE 'c';
DEBUG_SQL
printf("after create\n");
EXEC SQL DECLARE date_crsr CURSOR
FOR SELECT dta_date
FROM stck_dta_tbl
WHERE dta_date < '1999-02-04'
AND :date_rtrn >= dys_btwn_dates(:t_date1, dta_date);
printf("after cusrsor\n");
EXEC SQL OPEN date_crsr;
printf("after open cusrsor\n");
EXEC SQL FETCH FORWARD NEXT IN date_crsr INTO :t_in_date;
printf("after fetch\n");
DEBUG_SQL
printf("days = %d\n",t_in_date);
}
/*
DECLARE
date1 ALIAS FOR $1;
date2 ALIAS FOR $2;
*/
!! CPG PRE PROCESSOR OUTPUT !!
/* Processed by ecpg (2.8.0) */
/* These three include files are added by the preprocessor */
#include
#include
#include
#line 1 "dys_btwn_dates.cpg"
/* SET TABSTOP = 2 */
/* days between dates an attempt at using the 'date' data type in postgres
to try and determine the actual days between calendar dates.
*/
#include
#line 1 "/usr/local/pgsql/include/sqlca.h"
#ifndef POSTGRES_SQLCA_H
#define POSTGRES_SQLCA_H
#ifndef DLLIMPORT
#ifdef __CYGWIN__
#define DLLIMPORT __declspec (dllimport)
#else
#define DLLIMPORT
#endif /* __CYGWIN__ */
#endif /* DLLIMPORT */
#define SQLERRMC_LEN70
#ifdef __cplusplus
extern "C"
{
#endif
struct sqlca
{
charsqlcaid[8];
longsqlabc;
longsqlcode;
struct
{
int sqlerrml;
charsqlerrmc[SQLERRMC_LEN];
} sqlerrm;
charsqlerrp[8];
longsqlerrd[6];
/* Element 0: empty */
/* 1: OID of processed tuple if applicable */
/* 2: number of rows processed */
/* after an INSERT, UPDATE or */
/* DELETE statement */
/* 3: empty */
/* 4: empty */
/* 5: empty */
charsqlwarn[8];
/* Element 0: set to 'W' if at least one other is 'W' */
/* 1: if 'W' at least one character string */
/* value was truncated when it was */
/* stored into a host variable. */
/*
* 2: if 'W' a (hopefully) non-fatal notice occured
*//* 3: empty */
/* 4: empty */
/* 5: empty */
/* 6: empty
Re: [BUGS] Bug #806: create function failing... ERROR: stat failed
On Mon, 28 Oct 2002 [EMAIL PROTECTED] wrote: > Lynn Tilby ([EMAIL PROTECTED]) reports a bug with a severity of 1 > The lower the number the more severe it is. > > Short Description > create function failing... ERROR: stat failed on file ' > > Long Description > DEBUG: ProcessUtility: create function dys_btwn_dates ( date , date ) returns int > as ' >BEGIN > RETURN $1 - $2; >END; > ' language 'c' > ERROR: stat failed on file ' >BEGIN > RETURN $1 - $2; >END; > ': No such file or directory Errm, did you really mean language 'c'? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[BUGS] PAM Authentication Bug
I found that PAM authentication in 7.2.3 doesn't appear to work
properly. It appeared to me that the server wasn't waiting for
the PAM conversation to complete before it rejected the request,
so I did a little rewrite on the auth.c file to sort of force
things to prompt for a password and then shove it into PAM.
The one thing I see still is that it appears that you still have
to create user accounts in the database for things to work. This
is kind of disappointing as I'd like to not have to repeat creating
users in PAM if I've already got them defined, say in an LDAP
database somewhere, but at least I have PAM authentication working
with passwords.
Attached is the patch if the developers want to look at it, clean
it up, and stick it into the next version.
-jth
*** src/backend/libpq/auth.cMon Feb 25 15:07:33 2002
--- auth.c Mon Oct 28 20:34:06 2002
***
*** 44,65
char *pg_krb_server_keyfile;
#ifdef USE_PAM
! #include
!
! #define PGSQL_PAM_SERVICE "postgresql"/* Service name passed to PAM */
!
! static intCheckPAMAuth(Port *port, char *user, char *password);
! static int pam_passwd_conv_proc(int num_msg, const struct pam_message ** msg,
!struct pam_response ** resp, void
*appdata_ptr);
!
! static struct pam_conv pam_passw_conv = {
! &pam_passwd_conv_proc,
! NULL
! };
!
! static char *pam_passwd = NULL; /* Workaround for Solaris 2.6 brokenness */
! static Port *pam_port_cludge; /* Workaround for passing "Port *port"
!* into
pam_passwd_conv_proc */
#endif /* USE_PAM */
#ifdef KRB4
--- 44,58
char *pg_krb_server_keyfile;
#ifdef USE_PAM
!#include
!
!/* Constants */
!#define PGSQL_PAM_SERVICE "postgresql" /* Service name passed to PAM */
!
!/* PAM functions */
!static int doPAMAuth(Port *port, char *user, char *password);
!static int doPAMConversation(int num_msg, const struct pam_message **msg,
! struct pam_response **resp, void *appdata_ptr);
#endif /* USE_PAM */
#ifdef KRB4
***
*** 583,590
#ifdef USE_PAM
case uaPAM:
! pam_port_cludge = port;
! status = CheckPAMAuth(port, port->user, "");
break;
#endif /* USE_PAM */
--- 576,583
#ifdef USE_PAM
case uaPAM:
! sendAuthRequest(port, AUTH_REQ_PASSWORD);
! status = recv_and_check_password_packet(port);
break;
#endif /* USE_PAM */
***
*** 625,823
#ifdef USE_PAM
/*
! * PAM conversation function
*/
!
! static int
! pam_passwd_conv_proc(int num_msg, const struct pam_message ** msg, struct
pam_response ** resp, void *appdata_ptr)
{
! StringInfoData buf;
! int32 len;
!
! if (num_msg != 1 || msg[0]->msg_style != PAM_PROMPT_ECHO_OFF)
! {
! switch (msg[0]->msg_style)
! {
! case PAM_ERROR_MSG:
! snprintf(PQerrormsg, PQERRORMSG_LENGTH,
!"pam_passwd_conv_proc: Error from
underlying PAM layer: '%s'\n", msg[0]->msg);
! fputs(PQerrormsg, stderr);
! pqdebug("%s", PQerrormsg);
! return PAM_CONV_ERR;
! default:
! snprintf(PQerrormsg, PQERRORMSG_LENGTH,
!"pam_passwd_conv_proc: Unexpected PAM
conversation %d/'%s'\n",
!msg[0]->msg_style, msg[0]->msg);
! fputs(PQerrormsg, stderr);
! pqdebug("%s", PQerrormsg);
! return PAM_CONV_ERR;
! }
! }
!
! if (!appdata_ptr)
! {
! /*
!* Workaround for Solaris 2.6 where the PAM library is broken and
!* does not pass appdata_ptr to the conversation routine
!*/
! appdata_ptr = pam_passwd;
! }
!
! /*
!* Password wasn't passed to PAM the first time around - let's go ask
!* the client to send a password, which we then stuff into PAM.
!*/
! if (strlen(appdata_ptr) == 0)
! {
! sendAuthRequest(pam_port_cludge, AUTH_REQ_PASSWORD);
! if (pq_eof() == EOF || pq_getint(&len, 4) == EOF)
! {
! return PAM_CONV_ERR;/* client didn't want to send password
*/
! }
!
! initStringInfo(&buf);
! pq_getstr(&buf);
! if (DebugLvl > 5)
! fprintf(stderr, "received PAM packet with len=%d, pw=%s\n",
!
Re: [BUGS] Technical Question
Michael Conn wrote: > Is postgreSQL ODBC compliant? Will it operate with MS Windows 2000 or NT? > > Your assistance with this questions would be greatly appreciated. Not sure how compliant we are, but we do communicate with clients running MS software just fine. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] database caches
Hi, I have problem in PG performance - all database caches is allocated to one table, which is much greater than all other tables together, but requests to this huge table much rarely, than requests with other tables. How can I adjust which tables should be cached more (cache rate), and which less, if this possible?? Or which tables not to cache completely. Thanks a lot in advice. --- best regards, Ruslan A Dautkhanov [EMAIL PROTECTED] smime.p7s Description: S/MIME Cryptographic Signature
Re: [BUGS] database caches
Ruslan A Dautkhanov <[EMAIL PROTECTED]> writes: > I have problem in PG performance - all database caches is allocated > to one table, which is much greater than all other tables together, but > requests to this huge table much rarely, than requests with > other tables. What makes you think that all the cache is allocated for this one specific table? Also, caching is done on a page-by-page basis, not per-relation. > How can I adjust which tables should be cached more (cache rate), and > which less, if this possible?? Or which tables not to cache > completely. The buffer manager decides what to cache by itself; there are a few knobs you can tweak (shared_buffers, for example), but for the most part the buffer manager is expected to do its job. In your specific example, it doesn't sound like there's a need to manually tweak the buffer manager: the large but infrequently accessed table will likely not have very many buffers kept in memory. Cheers, Neil -- Neil Conway <[EMAIL PROTECTED]> || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
