Re: [GENERAL] outdated (bad) information in pg_stat_activity
hubert depesz lubaczewski <[EMAIL PROTECTED]> writes: > of course i can make myself a function which will check if pid exists, but > it would generally be better if there was some way to fix the problem in > postgresql itself. Try updating to PG 8.1 ... 2005-08-09 17:14 tgl * src/backend/postmaster/pgstat.c: Make backends that are reading the pgstats file verify each backend PID against the PGPROC array. Anything in the file that isn't in PGPROC gets rejected as being a stale entry. This should solve complaints about stale entries in pg_stat_activity after a BETERM message has been dropped due to overload. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] outdated (bad) information in pg_stat_activity
On 12/14/05, Chris Browne <[EMAIL PROTECTED]> wrote: The only answer I have been able to come to is that restarting thepostmaster will clear this all up. this is actually not an option for me. of course i can make myself a function which will check if pid exists, but it would generally be better if there was some way to fix the problem in postgresql itself. a series of delete's from some specific place perhaps? depesz
Re: [GENERAL] Timestamp <-> ctime conversion question ...
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > That said, you need to be careful about testing this using psql. I > believe psql is what's converting the timestamp to your local timezone. Certainly not; psql doesn't even know what a timestamp is. If you get different results in a different client interface, it could only be because the interface code fools with the TimeZone (and/or DateStyle) parameter settings. This is not out of the question --- I think JDBC tries to force TimeZone to UTC, for instance. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] is this a bug or I am blind?
Greg Stark <[EMAIL PROTECTED]> writes: > What runtime penalty? It seems likely that strcoll is implemented by the > equivalent of calling strxfrm twice internally anyways. Only by a very incompetent implementor. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] outdated (bad) information in pg_stat_activity
Jim Nasby wrote: > > I too have had issues with "stuck" entries in the pg_stat_activity view -- > > > the real pid is long gone but postgres still thinks the process is there. > > > > It would be real sweet to have a way of cleaning this table with bringing > > > postgres off-line. > > Rather than trying to clean things up by hand, could the stats system > instead periodically check to make sure that all the PIDs it knows about > actually still exist? I think that should be a pretty cheap check to > perform... This would certainly work for me, even as a function that a DBA might call manually. <...> > On a side note, is GlobeXplorer using PostgreSQL? Would they be willing > to let us publicize that fact? Better yet, would they be willing to do a > case study? We are indeed using postgres (and postGIS) for both runtime data access and billing and other misc. data processing requirements. We're be delighted to be publicized as happy users -- the transition from Informix was fairly smooth and performance is solid. Feel free to contact me off-list if you like for more substantive comments. I am gsw @ globexplorer.com Greg W. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] is this a bug or I am blind?
Tom Lane <[EMAIL PROTECTED]> writes: > Greg Stark <[EMAIL PROTECTED]> writes: > > But then I thought of another idea. What if Postgres just used strxfrm() > > instead of strcoll everywhere? Then it ought to never produce inconsistent > > results. At least if strxfrm() doesn't just return randomly varying results > > for the same inputs. > > AFAICS the most that could accomplish is to make failures more obvious; > it wouldn't actually fix anything. I don't think that's worth the > runtime penalty it would incur. What runtime penalty? It seems likely that strcoll is implemented by the equivalent of calling strxfrm twice internally anyways. I don't see how that could produce inconsistent results unless the internal interface has some error handling codepath that returns an error that isn't being checked. In fact calling strxfrm() has the potential to open up some optimizations. Like caching the transformed string for the duration of a single sort operation instead of repeatedly transforming it. > What we *ought* to be doing is trying to figure a way to detect and > disallow inconsistent locale/encoding combinations. We've avoided that > because there seems no general platform-independent way to find out the > encoding expected by a locale. But surely we could manage to make it > work at least on glibc and Windows, which would be a step ahead of doing > nothing. Harumph. When I suggested having a strxfrm() function like the ones three different people have independently developed and posted that would work though slowly on all platforms, used only standard libc functions and perform fine on at least glibc you complained it wasn't portable enough. -- greg ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] outdated (bad) information in pg_stat_activity
On Wed, Dec 14, 2005 at 02:02:02PM -0800, Gregory S. Williamson wrote: > I too have had issues with "stuck" entries in the pg_stat_activity view -- > the real pid is long gone but postgres still thinks the process is there. > > It would be real sweet to have a way of cleaning this table with bringing > postgres off-line. Rather than trying to clean things up by hand, could the stats system instead periodically check to make sure that all the PIDs it knows about actually still exist? I think that should be a pretty cheap check to perform... > Greg Williamson > DBA > GlobeXplorer LLC On a side note, is GlobeXplorer using PostgreSQL? Would they be willing to let us publicize that fact? Better yet, would they be willing to do a case study? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Timestamp <-> ctime conversion question ...
First, I don't think the epoch conversion has anything to do with this,
so it'd be better to focus on simple timestamps.
That said, you need to be careful about testing this using psql. I
believe psql is what's converting the timestamp to your local timezone.
If you test this using a different interface (such as ODBC), you could
well have different results.
I believe converting a timestam without timezone that you know to be in
UTC into text and then appending 'UTC' to it is valid, btw. I would
however make sure you use the same timezone string for both fields; ie:
don't take a timestamp AT TIME ZONE 'UTC' and append '+00'.
On Tue, Dec 13, 2005 at 05:20:41PM +0100, Alex Mayrhofer wrote:
> All,
>
> i'm trying to convert time stamps to "seconds since epoch" and back. My
> original timestamps are given with a time zone (UTC), and i have a
> conversion function to "ctime" which works pretty well:
>
> CREATE OR REPLACE FUNCTION to_ctime (timestamptz) RETURNS integer AS $$
> SELECT date_part('epoch', $1)::integer;
> $$ LANGUAGE SQL;
>
> test=# select to_ctime('1970-01-01T00:00Z');
> to_ctime
> --
> 0
> (1 row)
>
>
> However, i fail at converting ctime values back into timestamps with time
> zone UTC. Inspired from the query on the date/time docs pages, i've tried
> the following approaches:
>
> test=# SELECT (TIMESTAMP WITH TIME ZONE 'epoch'
> + 0 * INTERVAL '1 second') AT TIME ZONE 'UTC';
>
> timezone
> -
> 1970-01-01 00:00:00
> (1 row)
>
> This would yield the right timestamp, but loses the time zone. The nex
> approach:
>
> test=# SELECT (TIMESTAMP WITHOUT TIME ZONE 'epoch'
> + 0 * INTERVAL '1 second') AT TIME ZONE 'UTC';
> timezone
>
> 1970-01-01 01:00:00+01
> (1 row)
>
> yields the right timestamp (from an absolute point of view) as well, but in
> the wrong (my local) timezone. My next approach:
>
> test=# select timezone('UTC', timestamp with time zone 'epoch' + 0 *
> INTERVAL '1 second');
> timezone
> -
> 1970-01-01 00:00:00
> (1 row)
>
> loses the time zone as well. I'm a bit reluctant to use tricks like
> manually appending the "Z" as literal text so that it would "look like" a
> valid UTC time stamp.
>
> I'd appreciate any insight on this - am i simply missing something? I'm
> using PostgreSQL 8.1.0, if that matters.
>
> thanks & cheers
>
> --
> Alex Mayrhofer
> http://nona.net/features/map/
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
>
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] is this a bug or I am blind?
Greg Stark <[EMAIL PROTECTED]> writes: > But then I thought of another idea. What if Postgres just used strxfrm() > instead of strcoll everywhere? Then it ought to never produce inconsistent > results. At least if strxfrm() doesn't just return randomly varying results > for the same inputs. AFAICS the most that could accomplish is to make failures more obvious; it wouldn't actually fix anything. I don't think that's worth the runtime penalty it would incur. What we *ought* to be doing is trying to figure a way to detect and disallow inconsistent locale/encoding combinations. We've avoided that because there seems no general platform-independent way to find out the encoding expected by a locale. But surely we could manage to make it work at least on glibc and Windows, which would be a step ahead of doing nothing. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] is this a bug or I am blind?
Tom Lane <[EMAIL PROTECTED]> writes: > Non-self-consistent comparison results can lead to an index that is > either actually or effectively corrupt (because index searches proceed > down the wrong tree path and thus fail to find items that should be > found). So the observation that only index searches fail is consistent > with this idea. I wondered if there were some simple tests Postgres could do to notice the problem and report it. Checking to make sure strcoll(a,b) = -strcoll(b,a) for example. But then I thought of another idea. What if Postgres just used strxfrm() instead of strcoll everywhere? Then it ought to never produce inconsistent results. At least if strxfrm() doesn't just return randomly varying results for the same inputs. I suspect the worst case in practice is that strxfrm() will return the same data for just about every input string, which would hopefully be noticed by the user. But at least wouldn't cause corrupted indexes. -- greg ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] 7.4.5 vs 8.0 on Debian/sparc
Hi Tom, Am 2005-12-15 11:29:12, schrieb Tom Lane: > Michelle Konzack <[EMAIL PROTECTED]> writes: > > Now if I chante my php5 scripts on my Webserver to point to the 8.0 > > PostgreSQL I get only connect errors. > > What errors exactly? Without details it's impossible to solve this. Some of my queries do not more work. pgsql told me something about malformated... But I have changed in my phpscript only the ocation of the pgsql It must be something with the "SELECT" between php5 and pgsql8.0 > > Please note, that I use "hostssl" only. > > You might have forgotten to set up the SSL key files? No ist is on the right place, and I can connect from Strasbourg with psql/ssl to my Server in Paris Greetings Michelle -- Linux-User #280138 with the Linux Counter, http://counter.li.org/ Michelle Konzack Apt. 917 ICQ #328449886 50, rue de Soultz MSM LinuxMichi 0033/3/8845235667100 Strasbourg/France IRC #Debian (irc.icq.com) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Debug user lib for user define data type
On Wed, Dec 14, 2005 at 11:52:19AM -0800, Ale Raza wrote: > Hi, > > I am writing a user defined data type in C and want to debug the lib > I am creating for this data type. Wondering if somebody knows how to > link and debug this lib or any document which can help me to do this? > Can I debug this lib without compiling the PostgreSQL source code? > > I use prebuilt RPM package to installed PostgreSQL(7.4.6). > Platform: Red Hat Enterprise Linux AS release 3 (Taroon). Kernel 2.4.21-4.EL > on an i686 Hmm, you need the source code you make your user-defined type, and to have it work you need to compile with exactly the same options. The easiest way to do that is to compile both the server and your lib yourself. That said, it can be done, you just use GDB to attach to the backend and break on your functions. But GBD will probably irritate you unless you compile the server for debugging also. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpx3Ouptgczx.pgp Description: PGP signature
Re: [GENERAL] [EMAIL PROTECTED],65$($k!"NY$N1|MMEp;#Ej9F$N?$BJT
Hello Tom, Am 2005-12-15 11:19:03, schrieb Tom Lane: > Michelle Konzack <[EMAIL PROTECTED]> writes: > > Since some days we become SPAMed. > > > How can this happen, if the list is subscriber only? > > Marc accidentally turned off the subscribers-only filter for a few days :-( > It's been fixed, though, and I have not noticed any spam getting through > since then. Have you seen any in the last week? I was some days in Aserbaijan and was coming back today. And no, no singel SPAM found. Thanks Michelle -- Linux-User #280138 with the Linux Counter, http://counter.li.org/ Michelle Konzack Apt. 917 ICQ #328449886 50, rue de Soultz MSM LinuxMichi 0033/3/8845235667100 Strasbourg/France IRC #Debian (irc.icq.com) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] enable SSL on server
** * Do not Cc: me, because I am on THIS list, if I write here * * Keine Cc: am mich, bin auf DIESER Liste wenn ich hier schreibe * ** Hello, are the certificates on teh right place? If not, pgsql will not show any errors about it. I think, this should be done in the default loglevel. I had the same problem some weeks ago. Greetings Michelle Am 2005-12-15 14:03:15, schrieb : >Hello! > >Please tell - how to enable SSL on PostgreSQL 8.1 server. When > setting option "ssl = on" in postgresql.conf - server even doesn't want > to startup. I'm using PostgreSQL 8.1 on Windows 2000, installed from > .msi, downloaded from www.posgtresql.org. > >Best regards, > > Kovalevski Andrei, > [EMAIL PROTECTED] > > > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings - END OF REPLYED MESSAGE - -- Linux-User #280138 with the Linux Counter, http://counter.li.org/ Michelle Konzack Apt. 917 ICQ #328449886 50, rue de Soultz MSM LinuxMichi 0033/3/8845235667100 Strasbourg/France IRC #Debian (irc.icq.com) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] "gmake check" fails with 8.1.1
"Karl O. Pinc" <[EMAIL PROTECTED]> writes: > The problem is that the rpm substitutes in a Makefile that does not > install the language. If you hack it so that plpgsql is installed > (pg_regress --load-languge=plpgsql ...) > then all the tests pass. (Dunno what's with the rpm's Makefile. > It appears to do a lot of something different.) Ah-hah. The rpm makefile evidently hasn't tracked changes in the regular test makefile. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] "gmake check" fails with 8.1.1
Problem solved. On 12/15/2005 09:55:08 AM, Tom Lane wrote: Please note also that this is not a generic breakage. What you need to be asking is what in your particular environment is causing this failure. The problem is that the rpm substitutes in a Makefile that does not install the language. If you hack it so that plpgsql is installed (pg_regress --load-languge=plpgsql ...) then all the tests pass. (Dunno what's with the rpm's Makefile. It appears to do a lot of something different.) Karl <[EMAIL PROTECTED]> Free Software: "You don't pay back, you pay forward." -- Robert A. Heinlein ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Installation trouble - Solved
Dear Steve,
I have had the same problem related SuSE 8.2. A thing that additionally can be wrong is
the LC_CTYPE setting in the environment. Running a SuSE system this can be set in
/etc/sysconfig/language. I am using RC_LANG="de_DE.UTF-8", but other valid
values should be fine, too.
For SuSE 8.2 I am currently using the source rpm files from
ftp://ftp.suse.com/pub/projects/postgresql/postgresql-8.1.1/postgresql-8.1.1-1.src.rpm
and the appended patches for the spec files to build the rpms.
Best regards,
Johannes Weberhofer
--- postgres.811-suse/postgresql.spec 2005-12-14 15:31:48.0 +0100
+++ postgres.811/postgresql.spec2005-12-15 08:48:18.0 +0100
@@ -10,7 +10,7 @@
# norootforbuild
# neededforbuild kerberos-devel-packages openssl openssl-devel pam-devel
readline readline-devel
-# usedforbuildaaa_base acl attr bash bind-utils bison bzip2 coreutils cpio
cpp cvs cyrus-sasl db devs diffutils e2fsprogs file filesystem fillup findutils
flex gawk gdbm-devel glibc glibc-devel glibc-locale gpm grep groff gzip info
insserv kbd less libacl libattr libgcc libstdc++ libxcrypt m4 make man mktemp
modutils ncurses ncurses-devel net-tools netcfg openldap2-client openssl pam
pam-devel pam-modules patch permissions popt ps rcs readline sed sendmail
shadow strace syslogd sysvinit tar texinfo timezone unzip util-linux vim zlib
zlib-devel autoconf automake binutils cracklib e2fsprogs-devel gcc gdbm gettext
heimdal heimdal-devel heimdal-lib libtool openssl-devel perl readline-devel rpm
+# usedforbuildaaa_base acl attr bash bind9-utils bison bzip2 coreutils
cpio cpp cracklib cvs cyrus-sasl2 db devs diffutils e2fsprogs file filesystem
fillup findutils flex gawk gdbm-devel glibc glibc-devel glibc-locale gpm grep
groff gzip info insserv less libacl libattr libgcc libstdc++ libxcrypt m4 make
man mktemp modutils ncurses ncurses-devel net-tools netcfg openldap2-client
openssl pam pam-modules patch permissions popt ps shadow rcs readline sed
strace syslogd sysvinit tar tcpd texinfo timezone unzip util-linux vim zlib
zlib-devel autoconf automake binutils gcc gdbm gettext heimdal-devel
heimdal-lib libtool openssl-devel pam-devel perl readline-devel rpm
Name: postgresql
Summary: PostgreSQL - the Database
@@ -78,7 +78,7 @@
%package libs
Summary: The shared libraries required for any PostgreSQL clients
Group:Productivity/Databases/Clients
-Provides: pg_ifa pg_lib postgresql-lib postgresql-libs = 8.0.1
+Provides: pg_ifa pg_lib postgresql-lib postgresql-libs = %{version}
Obsoletes:pg_ifa pg_lib postgresql-lib
PreReq: sh-utils fileutils
@@ -241,7 +241,16 @@
# Run the regression tests.
#
%ifnarch %arm
-make check || {
+%if %suse_version < 900
+if test -z "`grep '^testuser:' /etc/passwd`" ; then
+useradd testuser
+fi
+chown -R testuser. src/test/
+SUCMD="su testuser -c"
+%else
+SUCMD=""
+%endif
+$SUCMD "make check" || {
cat regression.diffs
exit 1
}
@@ -280,7 +289,10 @@
gcc -shared -o %buildroot%_libdir/postgresql/backup/libreadline.so.4 -lreadline
gcc -shared -o %buildroot%_libdir/postgresql/backup/libssl.so.0.9.6 -lssl
gcc -shared -o %buildroot%_libdir/postgresql/backup/libcrypto.so.0.9.6 -lcrypto
+%if %suse_version < 1000
# This hack is needed because the soname major version was bumped in a
patchlevel release
+gcc -shared -Wl,-soname,libpq.so.3 -o %buildroot%_libdir/libpq.so.3
-L%buildroot%_libdir -lpq
+%endif
cp doc/FAQ doc/KNOWN_BUGS doc/MISSING_FEATURES doc/README* COPYRIGHT \
README HISTORY doc/bug.template %buildroot%_docdir/postgresql
cp -a %SOURCE2 %buildroot%_docdir/postgresql/README.SuSE.de
@@ -462,6 +474,8 @@
%doc %_mandir/man1/pg_config.1*
%changelog -n postgresql
+* Thu Dec 15 2005 - [EMAIL PROTECTED]
+- fixed some incompatibilities with SuSE 8.2 and SuSE 9.0
* Wed Dec 14 2005 - max@suse.de
- New version: 8.1.1
* Fri Jun 17 2005 - max@suse.de
--- postgres.811-suse/postgresql-pl.spec2005-12-14 15:25:17.0
+0100
+++ postgres.811/postgresql-pl.spec 2005-12-15 08:46:43.0 +0100
@@ -1,5 +1,5 @@
#
-# spec file for package postgresql-pl (Version 8.0.3)
+# spec file for package postgresql-pl (Version 8.1.1)
#
# Copyright (c) 2005 SUSE LINUX Products GmbH, Nuernberg, Germany.
# This file and all modifications and additions to the pristine
@@ -11,7 +11,7 @@
# norootforbuild
# neededforbuild kerberos-devel-packages openssl openssl-devel pam-devel
python python-devel tcl tcl-devel
-BuildRequires: aaa_base acl attr bash bind-utils bison bzip2 coreutils cpio
cpp cracklib cvs cyrus-sasl db devs diffutils e2fsprogs file filesystem fillup
findutils flex gawk gdbm-devel gettext-devel glibc glibc-devel glibc-locale gpm
grep groff gzip info insserv klogd less libacl libattr libcom_err libgcc
libnscd libselinux libstdc++ libxcrypt libzio m4 make man mktemp
module-init-tools ncurses ncurses-devel net-tools netcfg openldap2-client
openssl pam pam-modules patch permissions popt procinfo proc
Re: [GENERAL] FW: Advanced search form
Michelle Konzack wrote: Am 2005-12-07 18:16:43, schrieb Richard Huxton: Um - not sure what this has to do with PostgreSQL. You'll probably have more luck at one of the many PHP developer sites. I shouldn't be surprised if there was something available in the PEAR repository too. I do not get the right QUERY for fulltext search in PGSQL Ah - read up on "tsearch2" which provides text-searching. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] is this a bug or I am blind?
UPDATE I was trying to create a demo table, because I cannot send our confidental data. I have found weird result. # drop table common_logins; DROP TABLE $ psql < ../cl.sql SET SET SET SET SET SET CREATE TABLE setval 203650 (1 row) ALTER TABLE CREATE INDEX CREATE INDEX # select * from common_logins where username = 'potyty'; uid | username | password | lastlogin | status | usertype | loginnum -+--+--+---++--+-- (0 rows) # VACUUM FULL analyze; VACUUM # select * from common_logins where username = 'potyty'; uid | username | password | lastlogin | status | usertype | loginnum -+--+--+---++--+-- (0 rows) # select count(1) from common_logins; count 203361 (1 row) # delete from common_logins where uid in (select uid from common_logins where username not ilike 'potyty' limit 10); DELETE 10 mage=# select * from common_logins where username = 'potyty'; uid | username | password | lastlogin | status | usertype | loginnum -+--+--+---++--+-- (0 rows) # VACUUM FULL analyze; VACUUM # select * from common_logins where username = 'potyty'; uid | username | password | lastlogin | status | usertype | loginnum +--+--+++--+-- 155505 | potyty | board| 2004-08-16 17:45:55.723829 | A | S|1 60067 | potyty | board| 2004-07-07 20:22:17.68699 | A | S|3 (2 rows) # delete from common_logins where uid in (select uid from common_logins where username not ilike 'potyty' limit 8); DELETE 8 # VACUUM FULL analyze; VACUUM # select * from common_logins where username = 'potyty'; uid | username | password | lastlogin | status | usertype | loginnum +--+--+++--+-- 174041 | potyty | board| 2005-02-17 00:00:13.706144 | A | S|3 60067 | potyty | board| 2004-07-07 20:22:17.68699 | A | S|3 155505 | potyty | board| 2004-08-16 17:45:55.723829 | A | S|1 (3 rows) The 2 rows part seems to be (for me) a non-locale-related, but serious problem. I have the data file, it is confidental, but I can send it to official pg developers if needed. Mage Tom Lane wrote: Jaime Casanova <[EMAIL PROTECTED]> writes: On 12/15/05, Csaba Nagy <[EMAIL PROTECTED]> wrote: Ok, that explains then the problem... but the index is arguably corrupt in this case, with the important difference that it's not even fixable by reindex... I guess what the OP really wants is a solution to his problem. MAGE was reproducing the problem in a little table that can be send but now tolds me that the problem in the test table disappear when a VACUUM was executed... is this consistent with the idea of locale problem? The VACUUM might have caused the planner not to use the index anymore; check EXPLAIN. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] "gmake check" fails with 8.1.1
On 12/15/2005 09:55:08 AM, Tom Lane wrote: I'm inclined to guess that it's specific to "make check"'s temporary installation. Have you tried "make installcheck" to run against a non-temp installation? 'make installcheck' gets the same errors (and the same regression.diffs file (except for the timestamps).) FWIW, dual Xenon. (Linux sees 4 cpus.) Karl <[EMAIL PROTECTED]> Free Software: "You don't pay back, you pay forward." -- Robert A. Heinlein ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] 7.4.5 vs 8.0 on Debian/sparc
On Thu, 15 Dec 2005, Tom Lane wrote: Michelle Konzack <[EMAIL PROTECTED]> writes: Please note, that I use "hostssl" only. You might have forgotten to set up the SSL key files? I'm having some trouble with a 8.0 on Debian on Intel. My development SPARC version works OK with SSL connections. Anyway, I changed to local conexions on the Intel server, but don't know what really happend. Looks like some problem with the client keys or something like that. I started having those problems after an openssl upgrade. -- 18:02:25 up 4 days, 9:57, 5 users, load average: 1.59, 1.57, 1.62 - Lic. Martín Marqués | SELECT 'mmarques' || Centro de Telemática| '@' || 'unl.edu.ar'; Universidad Nacional| DBA, Programador, del Litoral | Administrador - ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Toolkit for creating editable grid
One thing you might consider is the 'Base' application from openoffice. I'm not sure it's what you want, but it might work... In any case it's likely worth looking at. > > I prefer major (A status) programming language using classification from > > http://www.tiobe.com/tpci.htm ... wandering off topic ... > I'm not sure if I would trust a programming language ranking that is based > on search-engine results. First, a higher rating might indicate that people > have lots of trouble with the language (Because the search for, and post > solution-x for the problems). Well, if they're not using it, they're not going to have problems either. Having used a slew of programming languages, including some fancy, less popular ones myself, I am pretty sure that people run into problems with all of them:-) > Seconds, _any_ programming language comparision > that doesn't say in what _context_ or for which _problems_ a language is > good, and for which it is bad, is worthless IMHO. Anyone who decides solely by looking at stats is kind of silly. That said, though, I think there is some validity to these kinds of things... Here's my attempt at something similar with more data sources: http://www.dedasys.com/articles/language_popularity.html As I say, though...take things with a grain of salt. I did those stats with Tcl, and Hecl, which don't figure highly in the results;-) Saluti, -- David N. Welton - http://www.dedasys.com/davidw/ Linux, Open Source Consulting - http://www.dedasys.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] 7.4.5 vs 8.0 on Debian/sparc
Michelle Konzack <[EMAIL PROTECTED]> writes: > Now if I chante my php5 scripts on my Webserver to point to the 8.0 > PostgreSQL I get only connect errors. What errors exactly? Without details it's impossible to solve this. > Please note, that I use "hostssl" only. You might have forgotten to set up the SSL key files? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] is this a bug or I am blind?
Jaime Casanova <[EMAIL PROTECTED]> writes: > On 12/15/05, Csaba Nagy <[EMAIL PROTECTED]> wrote: >> Ok, that explains then the problem... but the index is arguably corrupt >> in this case, with the important difference that it's not even fixable >> by reindex... >> >> I guess what the OP really wants is a solution to his problem. > MAGE was reproducing the problem in a little table that can be send > but now tolds me that the problem in the test table disappear when a > VACUUM was executed... is this consistent with the idea of locale > problem? The VACUUM might have caused the planner not to use the index anymore; check EXPLAIN. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] 7.4.5 vs 8.0 on Debian/sparc
Hello, I have 3 identicaly Sun/Sparc SMP machines which are working fine with Debian GNU/Linux architecture SPARC. On the first I have Woody 3.0 with PostgreSQL 7.4.5 which is working perfectly. The whole Database is around 160 GByte where the main table is around 90 GByte. Now I have installed on the second Sarge 3.1 with PostgreSQL 8.0 (coming from Etch 4.0) and dumped the whole 7.4.5 into 8.0. Now if I chante my php5 scripts on my Webserver to point to the 8.0 PostgreSQL I get only connect errors. Please note, that I use "hostssl" only. What are the changes from 7.4.5 to 8.0 Please note, that I am switching currentlx to a new appartement and I am Off-Line (No internet connection at home) and can only use my GSM to send sparely messages Thanks and Greetings Michelle -- Linux-User #280138 with the Linux Counter, http://counter.li.org/ # Debian GNU/Linux Consultant # Michelle Konzack Apt. 917 ICQ #328449886 50, rue de Soultz MSM LinuxMichi 0033/3/8845235667100 Strasbourg/France IRC #Debian (irc.icq.com) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] is this a bug or I am blind?
On 12/15/05, Csaba Nagy <[EMAIL PROTECTED]> wrote: > Ok, that explains then the problem... but the index is arguably corrupt > in this case, with the important difference that it's not even fixable > by reindex... > > I guess what the OP really wants is a solution to his problem. MAGE was reproducing the problem in a little table that can be send but now tolds me that the problem in the test table disappear when a VACUUM was executed... is this consistent with the idea of locale problem? -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [EMAIL PROTECTED],65$($k!"NY$N1|MMEp;#Ej9F$N?$BJT
Hello *, Since some days we become SPAMed. How can this happen, if the list is subscriber only? I have this question, because <[EMAIL PROTECTED]> and <[EMAIL PROTECTED]> are two servers where I do not filter SPAM (wast of resources because they are subscriber only) Greetings Michelle Am 2005-12-07 23:15:45, schrieb ?$BNY$N$"$NL<$OAG?M100%: > ?¡?ª?ª?ª?ª?ª?ª?ª?ª?ª?f?l?Š?e?ê?Ÿ?µ?}?K?W???ª?ª?ª 2005/?N?Ì?£?Á?å?? ?ª?ª?¡ -- Linux-User #280138 with the Linux Counter, http://counter.li.org/ # Debian GNU/Linux Consultant # Michelle Konzack Apt. 917 ICQ #328449886 50, rue de Soultz MSM LinuxMichi 0033/3/8845235667100 Strasbourg/France IRC #Debian (irc.icq.com) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] FW: Advanced search form
Am 2005-12-07 18:16:43, schrieb Richard Huxton: > Um - not sure what this has to do with PostgreSQL. You'll probably have > more luck at one of the many PHP developer sites. I shouldn't be > surprised if there was something available in the PEAR repository too. I do not get the right QUERY for fulltext search in PGSQL Greetings Michelle -- Linux-User #280138 with the Linux Counter, http://counter.li.org/ # Debian GNU/Linux Consultant # Michelle Konzack Apt. 917 ICQ #328449886 50, rue de Soultz MSM LinuxMichi 0033/3/8845235667100 Strasbourg/France IRC #Debian (irc.icq.com) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Toolkit for creating editable grid
Andrus wrote: Andrus wrote: I want to create editable grid (client application) for large Postgres table: At startup this grid show first screenful of records and allows to edit them. When user presses page down key, this grid should read next screenful of records and allow to edit them etc. PgADMIN tries to read the whole table. Is there any free toolkit or sample application for this ? Umm - running on what platform? In Microsoft Windows Written in what language? I'm in progress of selecting development platform. pgAdmin3 is written in C++, using the cross-plattform and free GUI-Toolkit wxwindows (www.wxwindows.org I believe). pgAdmin3 includes an editable grid-view (right-click a table, and select "Edit Data"). I believe pgAdmin3 is licensed under a BSD-License, so you might even be able to reuse some code. I prefer major (A status) programming language using classification from http://www.tiobe.com/tpci.htm I'm not sure if I would trust a programming language ranking that is based on search-engine results. First, a higher rating might indicate that people have lots of trouble with the language (Because the search for, and post solution-x for the problems). Seconds, _any_ programming language comparision that doesn't say in what _context_ or for which _problems_ a language is good, and for which it is bad, is worthless IMHO. Including C,C++,Visual Basic, PHP and PL/SQL(!) in the _same_ ranking is just insane. They serve vastly difference purposes (Just imagine a web-application in PL/SQL, a kernel in Visual Basic or a GUI-Applikation in PHP) greetings, Florian Pflug smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] "gmake check" fails with 8.1.1
"Karl O. Pinc" <[EMAIL PROTECTED]> writes: > I don't believe it's just the 'make check' case. Plpgsql is not > in template1 after installation. It's not supposed to be --- at least, not unless you install it there manually. Please note also that this is not a generic breakage. What you need to be asking is what in your particular environment is causing this failure. I'm inclined to guess that it's specific to "make check"'s temporary installation. Have you tried "make installcheck" to run against a non-temp installation? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] "gmake check" fails with 8.1.1
On 12/15/2005 09:45:12 AM, Tom Lane wrote: Devrim GUNDUZ <[EMAIL PROTECTED]> writes: > I reproduced the same... rpath problem? It would be useful to look at the postmaster log to see why it's failing to create the language in the 'make check' case. I don't believe it's just the 'make check' case. Plpgsql is not in template1 after installation. (If I'm understanding what you're saying.) Karl <[EMAIL PROTECTED]> Free Software: "You don't pay back, you pay forward." -- Robert A. Heinlein ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] is this a bug or I am blind?
Mage <[EMAIL PROTECTED]> writes: >> lc_collate | hu_HU >> lc_ctype | hu_HU >> server_encoding| LATIN2 Hm, are those settings actually compatible? You need to check your system documentation to find out what encoding "hu_HU" expects. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] "gmake check" fails with 8.1.1
Devrim GUNDUZ <[EMAIL PROTECTED]> writes: > I reproduced the same... rpath problem? It would be useful to look at the postmaster log to see why it's failing to create the language in the 'make check' case. The other odd thing is, if the createlang step fails, you'd think that pg_regress would complain about it. Is it possible that createlang (thinks it) succeeded, but it connected to the wrong database server or something like that? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] is this a bug or I am blind?
Ok, that explains then the problem... but the index is arguably corrupt in this case, with the important difference that it's not even fixable by reindex... I guess what the OP really wants is a solution to his problem. If the table is not too big, a quick fix would be to just drop the index. Then figure out an encoding+locale combination which can be used to properly host the same sample data which leads to failure here and still satisfies the OP's string sorting and other needs, and then dump reload... or is there other better way to fix things ? I guess a note in the docs about not using the same encoding/locale/(postgres version?)/(OS?) combination as the OP would make sense too ? Cheers, Csaba. On Thu, 2005-12-15 at 16:20, Tom Lane wrote: > Richard Huxton writes: > > Csaba Nagy wrote: > >> Based on the 3rd query of the OP, where the direct comparison results in > >> "true" for all the rows which matched the "like", I would exclude the > >> localisation issues variant... unless = is not equals in all cases ;-) > > > Well spotted Csaba - that _would_ seem to point to the index. > > No, localization issues should be real high on your list. In particular > I wonder whether this is the old bugaboo of using a database encoding > that's incompatible with the postmaster's locale setting. We've seen > that on some platforms strcoll() gets completely confused by this and > returns comparison results that are not even self-consistent. > > Non-self-consistent comparison results can lead to an index that is > either actually or effectively corrupt (because index searches proceed > down the wrong tree path and thus fail to find items that should be > found). So the observation that only index searches fail is consistent > with this idea. > > regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] "gmake check" fails with 8.1.1
Hi, On Thu, 2005-12-15 at 15:24 +, Karl O. Pinc wrote: > === > 5 of 98 tests failed. > === regression.diffs for this is at: http://www.gunduz.org/postgresql/regression.diffs I reproduced the same... -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PL/php, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] is this a bug or I am blind?
Richard Huxton writes: > But "like" without any wildcards should be the same as "=", but it isn't > in the original post. I'm too lazy to go look at the code right now, but I think that the reduction of "x LIKE constant-pattern" to "x = constant-pattern" is part of the LIKE index optimization code, which means it'd only get done in C locale. We're missing a bet there perhaps. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] is this a bug or I am blind?
Richard Huxton writes: > Csaba Nagy wrote: >> Based on the 3rd query of the OP, where the direct comparison results in >> "true" for all the rows which matched the "like", I would exclude the >> localisation issues variant... unless = is not equals in all cases ;-) > Well spotted Csaba - that _would_ seem to point to the index. No, localization issues should be real high on your list. In particular I wonder whether this is the old bugaboo of using a database encoding that's incompatible with the postmaster's locale setting. We've seen that on some platforms strcoll() gets completely confused by this and returns comparison results that are not even self-consistent. Non-self-consistent comparison results can lead to an index that is either actually or effectively corrupt (because index searches proceed down the wrong tree path and thus fail to find items that should be found). So the observation that only index searches fail is consistent with this idea. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] is this a bug or I am blind?
Mage wrote: ./configure --prefix=/usr/local/pgsql --with-python client_encoding| LATIN2 lc_collate | hu_HU lc_ctype | hu_HU lc_messages| en_US lc_monetary| en_US lc_numeric | en_US lc_time| en_US server_encoding| LATIN2 server_version | 8.0.3 I have created a table that can be sent to you to examine the bug. I am actually vacuuming it for further testing. Where can I upload it? Sorry, I cannot host it. Mage ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] "gmake check" fails with 8.1.1
Hi, I don't know what to make of this. I installed the rpms for 8.1.1 on a RH 4 es system and did initdb with LC_TYPE=C and LC_COLLATE=C (and I also tried without changing these locale variables): cd /usr/lib/pgsql/test gmake check gets: rm -rf ./testtablespace mkdir ./testtablespace /bin/sh ./pg_regress --schedule=./parallel_schedule --multibyte=SQL_ASCII (using postmaster on Unix socket, default port) triggers ... FAILED transactions ... FAILED plpgsql ... FAILED copy2... FAILED rangefuncs ... FAILED === 5 of 98 tests failed. === So, then I connect to the template1 db and: template1=# select * from pg_language; lanname | lanispl | lanpltrusted | lanplcallfoid | lanvalidator | lanacl --+-+--+---+--+ internal | f | f| 0 | 2246 | c| f | f| 0 | 2247 | sql | f | t| 0 | 2248 | (3 rows) So then: # su postgres -c 'createlang plpgsql template1' # su postgres -c 'createlang -l template1' Procedural Languages Name | Trusted? -+-- plpgsql | yes And back in psql: select * from pg_language; lanname | lanispl | lanpltrusted | lanplcallfoid | lanvalidator | lanacl --+-+--+---+--+ internal | f | f| 0 | 2246 | c| f | f| 0 | 2247 | sql | f | t| 0 | 2248 | plpgsql | t | t| 38295 |38296 | (4 rows) Restart the server: # /etc/init.d/postgresql restart Stopping postgresql service: [ OK ] Starting postgresql service: [ OK ] And the problem persists. However, I can make a plpgsql function just fine and call it without errors in a test database. (Rebuilding my own rpms from the srpm does not help either.) What's the problem here and do I need to worry about it? Thanks. Karl <[EMAIL PROTECTED]> Free Software: "You don't pay back, you pay forward." -- Robert A. Heinlein ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] is this a bug or I am blind?
Well, then I have the disease. The database is UNICODE: gex_runtime=# select gex_clientname from gex_clients where gex_clientname = 'HomeHappinesses'; gex_clientname -- HomeGain (1 row) gex_runtime=# select gex_clientname from gex_clients where gex_clientname like 'HomeHappinesses'; gex_clientname (0 rows) > > Gregory S. Williamson wrote: > > > > I am puzzled by the lack of a "%" in the LIKE query. When I try this on > > postgres 7.4 and 8.0 I get no rows when I am missing it; including it > > works as expected. > > > > The names have been changed to protect the guilty ;-} but the core of it > > is true -- no "%" means wierdnesses, I think. > > > > gex_runtime=# select gex_clientname from gex_clients where gex_clientname > > like 'Home'; > > gex_clientname > > > > (0 rows) > > > > gex_runtime=# select gex_clientname from gex_clients where gex_clientname > > like 'Home%'; > > gex_clientname > > -- > > HomeHappinesses > > HomeMorgageValues, Inc. > > (2 rows) > > > > Could you try your query again with the wild card ? > > But "like" without any wildcards should be the same as "=", but it isn't > in the original post. > Well then I have the same behavior -- the database is UNICODE: gex_runtime=# select gex_clientname from gex_clients where gex_clientname = 'HomeHappinesses'; gex_clientname -- HomeHappinesses (1 row) gex_runtime=# select gex_clientname from gex_clients where gex_clientname like 'HomeHappinesses'; gex_clientname (0 rows) An "=" is not equivalent to LIKE with no wildcard. I never really thought of this as a bug, but if it is ... ring one up for Mage as a good catch. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] enable SSL on server
=?UTF-8?B?0JDQvdC00YDQtdC5?= <[EMAIL PROTECTED]> writes: > Please tell - how to enable SSL on PostgreSQL 8.1 server. When > setting option "ssl = on" in postgresql.conf - server even doesn't want > to startup. I'm using PostgreSQL 8.1 on Windows 2000, installed from > .msi, downloaded from www.posgtresql.org. Did you set up the key and certificate files required by an SSL server? See the documentation: http://www.postgresql.org/docs/8.1/static/ssl-tcp.html regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] is this a bug or I am blind?
Jaime Casanova wrote: I tried it in two databases (dump and load to another one), so I don't think that we have corrupted indexes. the problem persist after a dump a reload? then there is not because an index corruption... can you send part of the data that reproduces the bug? I can try on a newer version of postgresql on another server. By the way, if this is a bug then it's a serious one. We have it in production environment. Mage what locales do you have? encoding? The problem can be reproduced on pgsql 8.0.3, compiled from source. This is a third machine where the bug persists. I dumped the table and loaded in. I cannot send the table to you because it contains user data and passwords. I will try to create a fake one with the same problem. ./configure --prefix=/usr/local/pgsql --with-python client_encoding| LATIN2 lc_collate | hu_HU lc_ctype | hu_HU lc_messages| en_US lc_monetary| en_US lc_numeric | en_US lc_time| en_US server_encoding| LATIN2 server_version | 8.0.3 Mage ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] is this a bug or I am blind?
Gregory S. Williamson wrote: I am puzzled by the lack of a "%" in the LIKE query. When I try this on postgres 7.4 and 8.0 I get no rows when I am missing it; including it works as expected. The names have been changed to protect the guilty ;-} but the core of it is true -- no "%" means wierdnesses, I think. gex_runtime=# select gex_clientname from gex_clients where gex_clientname like 'Home'; gex_clientname (0 rows) gex_runtime=# select gex_clientname from gex_clients where gex_clientname like 'Home%'; gex_clientname -- HomeHappinesses HomeMorgageValues, Inc. (2 rows) Could you try your query again with the wild card ? But "like" without any wildcards should be the same as "=", but it isn't in the original post. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: 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: [GENERAL] is this a bug or I am blind?
Csaba Nagy wrote: [snip] If that makes a difference then I'd guess you have one of two things: 1. A corrupt index (check the REINDEX command) 2. (perhaps more likely) Some localisation issues. What encoding/locale settings are you using? Based on the 3rd query of the OP, where the direct comparison results in "true" for all the rows which matched the "like", I would exclude the localisation issues variant... unless = is not equals in all cases ;-) Well spotted Csaba - that _would_ seem to point to the index. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] is this a bug or I am blind?
> > I tried it in two databases (dump and load to another one), so I don't > think that we have corrupted indexes. > the problem persist after a dump a reload? then there is not because an index corruption... can you send part of the data that reproduces the bug? > I can try on a newer version of postgresql on another server. > > By the way, if this is a bug then it's a serious one. We have it in > production environment. > > Mage > what locales do you have? encoding? -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] is this a bug or I am blind?
>
> If you look my 3rd query, you will see that there are no spaces, however:
>
> select *, length(username), length('potyty') from common_logins where
username like 'potyty';
> uid | username | password | lastlogin | status |
usertype | loginnum | length | length
> +--+--+++--+--++
> 155505 | potyty | board| 2004-08-16 17:45:55.723829 | A |
S|1 | 6 | 6
> 60067 | potyty | board| 2004-07-07 20:22:17.68699 | A |
S|3 | 6 | 6
> 174041 | potyty | board| 2005-02-17 00:00:13.706144 | A |
S|3 | 6 | 6
> (3 rows)
>
>Mage
I am puzzled by the lack of a "%" in the LIKE query. When I try this on
postgres 7.4 and 8.0 I get no rows when I am missing it; including it works as
expected.
The names have been changed to protect the guilty ;-} but the core of it is
true -- no "%" means wierdnesses, I think.
gex_runtime=# select gex_clientname from gex_clients where gex_clientname like
'Home';
gex_clientname
(0 rows)
gex_runtime=# select gex_clientname from gex_clients where gex_clientname like
'Home%';
gex_clientname
--
HomeHappinesses
HomeMorgageValues, Inc.
(2 rows)
Could you try your query again with the wild card ?
HTH
Greg Williamson
DBA
GlobeXplorer LLC
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Installing the latest 8.1.1 rpms question.
Hi, On Wed, 2005-12-14 at 08:16 -0500, Jerry LeVan wrote: > Oh well, I chickened out and rm'ed the existing rpms and noted which > dependencies were deleted via pencil and paper. > > I then installed the new rpms and reinstalled the previous dependencies. Why didn't you use rpm -Uvh? That would for for 8.1.0-8.1.1 upgrade. Also I'd not use Synaptics for various reasons. We have a document about RPM installation. You may refer to that document next time: http://pgfoundry.org/docman/?group_id=148 egards, -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PL/php, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] is this a bug or I am blind?
Csaba Nagy wrote: [snip] even is that is true i think you need the "comodin characters" (ie: %. _) to make "like" behave different from simple comparisons Not entirely true, if the database was initialized in a different locale than C, then the direct comparison will probably go for an index on username, while "like" will not. Which points to a possible index corruption... which might be interesting for the developers to investigate, but I would guess a reindex will solve the problem for the OP if he has it urgent... I thought that it may be a locale problem, but: - look at my 3rd query - potyty doesn't contain special chars # EXPLAIN ANALYZE select * from common_logins where username = 'potyty'; QUERY PLAN --- Index Scan using common_logins_username_idx on common_logins (cost=0.00..4.30 rows=1 width=47) (actual time=0.056..0.056 rows=0 loops=1) Index Cond: ((username)::text = 'potyty'::text) Total runtime: 0.109 ms (3 rows) online=# EXPLAIN ANALYZE select * from common_logins where username like 'potyty'; QUERY PLAN Seq Scan on common_logins (cost=0.00..63833.88 rows=1 width=47) (actual time=180.333..262.492 rows=3 loops=1) Filter: ((username)::text ~~ 'potyty'::text) Total runtime: 262.551 ms (3 rows) I tried it in two databases (dump and load to another one), so I don't think that we have corrupted indexes. I can try on a newer version of postgresql on another server. By the way, if this is a bug then it's a serious one. We have it in production environment. Mage ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] is this a bug or I am blind?
A. Kretschmer wrote:
select *, length(username), length('potyty') from common_logins where username
like 'potyty';
My guess:
select length(username) from common_logins where username like 'potyty';
is _NOT_ 6, there is a SPACE like 'potyty '.
If you look my 3rd query, you will see that there are no spaces, however:
select *, length(username), length('potyty') from common_logins where
username like 'potyty';
uid | username | password | lastlogin | status |
usertype | loginnum | length | length
+--+--+++--+--++
155505 | potyty | board| 2004-08-16 17:45:55.723829 | A |
S|1 | 6 | 6
60067 | potyty | board| 2004-07-07 20:22:17.68699 | A |
S|3 | 6 | 6
174041 | potyty | board| 2005-02-17 00:00:13.706144 | A |
S|3 | 6 | 6
(3 rows)
Mage
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] is this a bug or I am blind?
[snip] > If that makes a difference then I'd guess you have one of two things: > 1. A corrupt index (check the REINDEX command) > 2. (perhaps more likely) Some localisation issues. > What encoding/locale settings are you using? Based on the 3rd query of the OP, where the direct comparison results in "true" for all the rows which matched the "like", I would exclude the localisation issues variant... unless = is not equals in all cases ;-) Cheers, Csaba. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] is this a bug or I am blind?
On 12/15/05, Csaba Nagy <[EMAIL PROTECTED]> wrote: > [snip] > > even is that is true i think you need the "comodin characters" (ie: %. > > _) to make "like" behave different from simple comparisons > > Not entirely true, if the database was initialized in a different locale > than C, then the direct comparison will probably go for an index on > username, while "like" will not. Which points to a possible index > corruption... which might be interesting for the developers to > investigate, but I would guess a reindex will solve the problem for the > OP if he has it urgent... > > Cheers, > Csaba. > > Mage if it's not urgent maybe you can make a post in -hackers and follow instructions about how to get more info to see what happened here... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] is this a bug or I am blind?
Mage wrote: online=# select * from common_logins where username = 'potyty'; online=# select * from common_logins where username like 'potyty'; It's probably worth seeing whether these have different plans (EXPLAIN ANALYSE...) and if the = is using an index but like isn't. If so, try issuing "set enable_indexscan=false" first and see what happens then. If that makes a difference then I'd guess you have one of two things: 1. A corrupt index (check the REINDEX command) 2. (perhaps more likely) Some localisation issues. What encoding/locale settings are you using? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] is this a bug or I am blind?
[snip] > even is that is true i think you need the "comodin characters" (ie: %. > _) to make "like" behave different from simple comparisons Not entirely true, if the database was initialized in a different locale than C, then the direct comparison will probably go for an index on username, while "like" will not. Which points to a possible index corruption... which might be interesting for the developers to investigate, but I would guess a reindex will solve the problem for the OP if he has it urgent... Cheers, Csaba. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] "incomplete startup packet" on SGI
David Rysdam wrote: David Rysdam wrote: Tom Lane wrote: David Rysdam <[EMAIL PROTECTED]> writes: Just finished building and installing on *Sun* (also "--without-readline", not that I think that could be the issue): Works fine. So it's something to do with the SGI build in particular. More likely it's something to do with weird behavior of the SGI kernel's TCP stack. I did a little googling for "transport endpoint is not connected" without turning up anything obviously related, but that or ENOTCONN is probably what you need to search on. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster It's acting like a race condition or pointer problem. When I add random debug printfs/PQflushs to libpq it sometimes works. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match Not a race condition: No threads Not a memory leak: Electric fence says nothing. And it works when electric fence is running, whereas a binary that uses the same libpq without linking efence does not work. I know nobody is interested in this, but I think I should document the "solution" for anyone who finds this thread in the archives: My theory is that Irix is unable to keep up with how fast the postgresql client is going and that the debug statements/efence stuff are slowing it down enough that Irix can catch up and make sure the socket really is there, connected and working. To that end, I inserted a sleep(1) in fe-connect.c just before the pqPacketSend(...startpacket...) stuff. It's stupid and hacky, but gets me where I need to be and maybe this hint will inspire somebody who knows (and cares) about Irix to find a real fix. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] is this a bug or I am blind?
On 12/15/05, A. Kretschmer <[EMAIL PROTECTED]> wrote:
> am 15.12.2005, um 14:12:23 +0100 mailte Mage folgendes:
> > online=# select * from common_logins where username = 'potyty';
> > uid | username | password | lastlogin | status | usertype | loginnum
> > -+--+--+---++--+--
> > (0 rows)
> >
> > online=# select * from common_logins where username like 'potyty';
> > uid | username | password | lastlogin | status |
> > usertype | loginnum
> > +--+--+++--+--
> > 155505 | potyty | board| 2004-08-16 17:45:55.723829 | A | S
> >|1
> > 60067 | potyty | board| 2004-07-07 20:22:17.68699 | A | S
> >|3
> > 174041 | potyty | board| 2005-02-17 00:00:13.706144 | A | S
> >|3
> > (3 rows)
>
> Try:
>
> select *, length(username), length('potyty') from common_logins where
> username like 'potyty';
>
>
> My guess:
>
> select length(username) from common_logins where username like 'potyty';
>
> is _NOT_ 6, there is a SPACE like 'potyty '.
>
>
even is that is true i think you need the "comodin characters" (ie: %.
_) to make "like" behave different from simple comparisons
> HTH, Andreas
> --
> Andreas Kretschmer(Kontakt: siehe Header)
> Heynitz: 035242/47212, D1: 0160/7141639
> GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
> ===Schollglas Unternehmensgruppe===
>
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] is this a bug or I am blind?
am 15.12.2005, um 14:12:23 +0100 mailte Mage folgendes:
> online=# select * from common_logins where username = 'potyty';
> uid | username | password | lastlogin | status | usertype | loginnum
> -+--+--+---++--+--
> (0 rows)
>
> online=# select * from common_logins where username like 'potyty';
> uid | username | password | lastlogin | status |
> usertype | loginnum
> +--+--+++--+--
> 155505 | potyty | board| 2004-08-16 17:45:55.723829 | A | S
>|1
> 60067 | potyty | board| 2004-07-07 20:22:17.68699 | A | S
>|3
> 174041 | potyty | board| 2005-02-17 00:00:13.706144 | A | S
>|3
> (3 rows)
Try:
select *, length(username), length('potyty') from common_logins where username
like 'potyty';
My guess:
select length(username) from common_logins where username like 'potyty';
is _NOT_ 6, there is a SPACE like 'potyty '.
HTH, Andreas
--
Andreas Kretschmer(Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
===Schollglas Unternehmensgruppe===
---(end of broadcast)---
TIP 1: 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: [GENERAL] enable SSL on server
Андрей wrote: Richard Huxton wrote: Andrei wrote: Hello! Please tell - how to enable SSL on PostgreSQL 8.1 server. When setting option "ssl = on" in postgresql.conf - server even doesn't want to startup. I'm using PostgreSQL 8.1 on Windows 2000, installed from .msi, downloaded from www.posgtresql.org. If the server isn't starting up, it will say why in the logs. What does it say in the logs? No logs appear, just got MessageBox with 'PostgreSQL Tray' - 'Service command failed'. :( It seems unlikely that *nothing* is being logged - PG is usually good at that. Check your logging configuration settings, and see what you get with ssl = false, then try again with ssl = true. If you genuinely have nothing being logged, the failure must be very early in the startup sequence. Try starting it from a command-prompt - you should see an error message then. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] is this a bug or I am blind?
online=# select * from common_logins where username = 'potyty'; uid | username | password | lastlogin | status | usertype | loginnum -+--+--+---++--+-- (0 rows) online=# select * from common_logins where username like 'potyty'; uid | username | password | lastlogin | status | usertype | loginnum +--+--+++--+-- 155505 | potyty | board| 2004-08-16 17:45:55.723829 | A | S|1 60067 | potyty | board| 2004-07-07 20:22:17.68699 | A | S|3 174041 | potyty | board| 2005-02-17 00:00:13.706144 | A | S|3 (3 rows) online=# select username, username = 'potyty' from common_logins where username like 'potyty'; username | ?column? --+-- potyty | t potyty | t potyty | t (3 rows) psql 8.0.3, Debian. Mage ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] enable SSL on server
Richard Huxton wrote: Andrei wrote: Hello! Please tell - how to enable SSL on PostgreSQL 8.1 server. When setting option "ssl = on" in postgresql.conf - server even doesn't want to startup. I'm using PostgreSQL 8.1 on Windows 2000, installed from .msi, downloaded from www.posgtresql.org. If the server isn't starting up, it will say why in the logs. What does it say in the logs? No logs appear, just got MessageBox with 'PostgreSQL Tray' - 'Service command failed'. :( Best Regards, Kovalevski Andrei ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] enable SSL on server
Андрей wrote: Hello! Please tell - how to enable SSL on PostgreSQL 8.1 server. When setting option "ssl = on" in postgresql.conf - server even doesn't want to startup. I'm using PostgreSQL 8.1 on Windows 2000, installed from .msi, downloaded from www.posgtresql.org. If the server isn't starting up, it will say why in the logs. What does it say in the logs? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] enable SSL on server
Hello! Please tell - how to enable SSL on PostgreSQL 8.1 server. When setting option "ssl = on" in postgresql.conf - server even doesn't want to startup. I'm using PostgreSQL 8.1 on Windows 2000, installed from .msi, downloaded from www.posgtresql.org. Best regards, Kovalevski Andrei, [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] copying a database without dumping it
On Donnerstag, Dez 15, 2005, at 12:11 Europe/Berlin, Tino Wildenhain wrote: Marc Brünink schrieb: Hi list, ... Now I'l try a custom dump. Perhaps this will suffice. But I guess it's impossible for a dump to be as fast as a cp. So if a cp would be possible I would favour it. Oh, and did I tell: Everything have to be done tommorow. *sigh* Actually its faster. Custom dump is the way to go because its much more flexible then dumping plaintext. [..] Since the dump only dumps DDL and Data, its much less data then your pg_data directory currently has. Oh yes! It's damn fast! All hail to the postgres crew! Gosh. My dump file is just 1.3 GB big. Impressive. I'm looking forward for the import. Guess I'll get another surprise... I used: pg_dump -f shape.postgresql -F c -o -U postgres -Z 9 -v shape One last thing: Will I have to re-cluster my tables? Thanks Marc ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] copying a database without dumping it
Marc Brünink schrieb: Hi list, ... Now I'l try a custom dump. Perhaps this will suffice. But I guess it's impossible for a dump to be as fast as a cp. So if a cp would be possible I would favour it. Oh, and did I tell: Everything have to be done tommorow. *sigh* Actually its faster. Custom dump is the way to go because its much more flexible then dumping plaintext. I'd not use pg_dumpall but pg_dump for each DB in turn. See also the various compression options or if short on free space try uncompressed custom dump and rar. Since the dump only dumps DDL and Data, its much less data then your pg_data directory currently has. HTH Tino Wildenhain ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] copying a database without dumping it
This link explains lot of useful techniques for backup and restore http://www.postgresql.org/docs/8.1/static/backup.html On 12/15/05, Marc Brünink <[EMAIL PROTECTED]> wrote: > Hi list, > > I know this was asked a lot of times on this mailing list. But actually > no reply satisfied me :-) > However: I've a running postgres database. It's about 6 GB big. Now I > want to copy this database to another host. Clearly pg_dumpall comes to > the mind. But there're 2 problems: > > 1. The new host hasn't got an internet connection. So I've to copy the > data via DVD. Doesn't sound like a problem, eh? But it is! I've to copy > the database to 4 different hosts. So let's calculate: 4 hosts, about > 30 GB of sql statements (plain), therefore about 8 DVDs = lot's of > annoying DVD changes. Which brings me to the next problem. Time. > > 2. All these 4 hosts have to be updated within 2-3 hours. Which is > simply impossible with a plain text export. If I remember correctly the > initial import of the data took about 3-4 hours. On a Sun 240. So I'll > took much longer on a (much slower) i386 system. > > Because of these problems I tought about just cp the data folder. This > didn't work. Is there some sort of evil trick to do so? Is postgres > binary compatible? (Wouldn't be a huge problem if it's not) > > Now I'l try a custom dump. Perhaps this will suffice. But I guess it's > impossible for a dump to be as fast as a cp. So if a cp would be > possible I would favour it. Oh, and did I tell: Everything have to be > done tommorow. *sigh* > > > Many thanks > Marc > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org > -- Regards Pandu ---(end of broadcast)--- TIP 1: 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
[GENERAL] copying a database without dumping it
Hi list, I know this was asked a lot of times on this mailing list. But actually no reply satisfied me :-) However: I've a running postgres database. It's about 6 GB big. Now I want to copy this database to another host. Clearly pg_dumpall comes to the mind. But there're 2 problems: 1. The new host hasn't got an internet connection. So I've to copy the data via DVD. Doesn't sound like a problem, eh? But it is! I've to copy the database to 4 different hosts. So let's calculate: 4 hosts, about 30 GB of sql statements (plain), therefore about 8 DVDs = lot's of annoying DVD changes. Which brings me to the next problem. Time. 2. All these 4 hosts have to be updated within 2-3 hours. Which is simply impossible with a plain text export. If I remember correctly the initial import of the data took about 3-4 hours. On a Sun 240. So I'll took much longer on a (much slower) i386 system. Because of these problems I tought about just cp the data folder. This didn't work. Is there some sort of evil trick to do so? Is postgres binary compatible? (Wouldn't be a huge problem if it's not) Now I'l try a custom dump. Perhaps this will suffice. But I guess it's impossible for a dump to be as fast as a cp. So if a cp would be possible I would favour it. Oh, and did I tell: Everything have to be done tommorow. *sigh* Many thanks Marc ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Question on the use of bracket expressions in Postgres
In article <[EMAIL PROTECTED]>, Michael Glaesemann <[EMAIL PROTECTED]> writes: > On Dec 15, 2005, at 0:29 , Jimmy Rowe wrote: >> select * from catalog where file_name like 'abc%def%.200[2-5]%'; >> The following select keeps returning "(0 rows)". > LIKE doesn't consider [2-5] to be a range, but rather the literal > characters '[2-5]'. If you're looking for regex, take a look at the > POSIX regex operator ~ > http://www.postgresql.org/docs/current/interactive/functions- > matching.html#FUNCTIONS-POSIX-REGEXP > See if something like file_name ~ 'abc.*def.*\.200[2-5]' That's not quite the same because LIKE matching is anchored. Try something like file_name ~ '^abc.*def.*\.200[2-5]$' ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Improving Availability
On 15.12.2005, at 9:10 Uhr, Brendan Duddridge wrote: What about Sequoia? Is that better or worse than pgpool? That looks interesting, I haven't seen it yet. Has anyone experiences to share? cug smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] Improving Availability
Hi Guido / Richard / Scott, What about Sequoia? Is that better or worse than pgpool? Thanks Brendan Duddridge | CTO | 403-277-5591 x24 | [EMAIL PROTECTED] ClickSpace Interactive Inc. Suite L100, 239 - 10th Ave. SE Calgary, AB T2G 0V9 http://www.clickspace.com On Dec 14, 2005, at 9:51 AM, Guido Neitzer wrote: Hi Scott, hi Richard, On 14.12.2005, at 17:30 Uhr, Scott Marlowe wrote: This setup I'm talking about would have pgpool on each db server. If you meant pgpool running on both application servers, that would work fine with slony in the background and pgpool in load balancing mode, or with pgpool doing the replication. Okay, just that I get this right (have to write a business paper on that and they will take me by the word ...): Setup would be: Machine 1: - web server - application server connecting to "localhost --> pgpool" - PostgreSQL installed and accessed only via pgpool - pgpool installed and knowing of machine 1 and machine 2 (replication mode) Machine 2: - web server - application server connecting to "localhost --> pgpool" - PostgreSQL installed and accessed only via pgpool - pgpool installed and knowing of machine 1 and machine 2 (replication mode) If one machine fails, the replication is cut off, and pgpool works with the other machine. Okay so far. The applications only know the connection to the local pgpool, so they are fault tolerant as far as pgpool accepts requests. If one machine fails, the service is not down because as far as all the services on the remaining machines are working properly. To get everything back, we will have to shut down all apps and all databases, sync the db data directories from the working machine to the machine that has failed, start the dbs, start pgpool, start the applications. Everything correct? cug smime.p7s Description: S/MIME cryptographic signature
