Re: [GENERAL] Very weird problem of "order by" in postgresql

Still the same problem…… What I did is: 1. set locale to "zh_cn.UTF8" 2. reboot 3. use "locale" command to see if locale is set. 4. delete everything under /lib/var/pgsql/data to make sure the database will be completely re-initialed 5. restart postgresql I check pgstartup.log and I am sure the

Re: [GENERAL] [HACKERS] New shapshot RPMs (Sep 7 2008) are ready for testing

Hi, On Sun, 2008-09-07 at 13:39 -0400, Andrew Dunstan wrote: > The point I was making is that for 8.4, unless you specifically > configure with --disable-integer-datetimes, it is enabled by default on > any platform that can support it. We committed that change on 30 March > here: http://archi

Re: [GENERAL] Very weird problem of "order by" in postgresql

"Peter Cai" <[EMAIL PROTECTED]> writes: > The only difference is that one OS is centos and the other is ubuntu It's hardly impossible that those two contain different locale definitions. Have you tried comparing the results of sort(1) under the same locale settings? regar

Re: [GENERAL] recover in single-user backend fails

Hey Guys, Thanks for your input. Meanwhile we have recovered from a backup. Lesson of all this, upgrade asap to postgres 8.2? Thanks, WS. -Original Message- From: Greg Stark [mailto:[EMAIL PROTECTED] On Behalf Of Gregory Stark Sent: zondag 7 september 2008 11:50 To: Tom Lane Cc: Wouter

Re: [GENERAL] Very weird problem of "order by" in postgresql

Here is my locale settings: LANG=zh_CN.UTF-8 LC_CTYPE="zh_CN.UTF-8" LC_NUMERIC="zh_CN.UTF-8" LC_TIME="zh_CN.UTF-8" LC_COLLATE="zh_CN.UTF-8" LC_MONETARY="zh_CN.UTF-8" LC_MESSAGES="zh_CN.UTF-8" LC_PAPER="zh_CN.UTF-8" LC_NAME="zh_CN.UTF-8" LC_ADDRESS="zh_CN.UTF-8" LC_TELEPHONE="zh_CN.UTF-8" LC_MEASUR

[GENERAL] ERROR: cache lookup failed for relation

Hello list, I have contributed the mysql2pgsql script to the community at http://pgfoundry.org/projects/mysql2pgsql/ I am wondering why I pg_class can see it, but " AND pg_catalog.pg_table_is_visible(c.oid)" (as in \d korean_english) queries are returning false. My database has been acting

[GENERAL] 8.3 on FreeBSD 6.3, sudden performance degradations

Hello, We experience sudden performance degradations on a PostgreSQL server used as a backend for pretty big web application. It's 16 GB RAM dedicated PostgreSQL 8.3.3 server with 2 x Quad Core Xeon 2.33 GHz running 6.3-PRERELEASE FreeBSD. postgresql.conf tweaked to match current configuration si

Re: [GENERAL] 64-bit Compile Failure on Solaris 10 with OpenSSL

On Mon, September 8, 2008 12:06 am, Tom Lane wrote: > "Randal T. Rioux" <[EMAIL PROTECTED]> writes: >> bash-3.00# ldd /usr/local/ssl/lib/libssl.so > ... >> libgcc_s.so.1 => (file not found) > > Smoke, meet gun ... > >> Now why would libssl.so not be linked to libgcc_s.so.1? Why woul

Re: [ADMIN] [GENERAL] secure connections

2008/9/7 Filip Rembiałkowski <[EMAIL PROTECTED]>: > 2008/9/7 c k <[EMAIL PROTECTED]>: >> Hello, >> I have a question regarding secure connections between pg clients and pg >> server. As per manual for 8.3 we can use openssl for this purpose. does odbc >> driver supports it and how? Is there any oth

Re: [GENERAL] recover in single-user backend fails

On Mon, Sep 8, 2008 at 4:34 AM, Wouter Sergeyssels <[EMAIL PROTECTED]> wrote: > Hey Guys, > > Thanks for your input. Meanwhile we have recovered from a backup. > > Lesson of all this, upgrade asap to postgres 8.2? Close, but I'd change that to 8.3.3 myself. -- Sent via pgsql-general mailing list

[GENERAL] Suspected bug: outer WHERE reordered before inner WHERE -> input syntax ERROR

This fails with ERROR: invalid input syntax for integer: "JOHN": select * from ( select * from (VALUES ('100'), ('JOHN')) as A (n) where trim(trim(n), '0123456789') = '' ) as B where n::integer <> -1; But without the nested trim, this works just fine (although it would not suffice for my pur

Re: [GENERAL] Suspected bug: outer WHERE reordered before inner WHERE -> input syntax ERROR

John Keith Hohm wrote: > select * from ( > select * from (VALUES ('100'), ('JOHN')) as A (n) > where trim(trim(n), '0123456789') = '' > ) as B where n::integer <> -1; > I'm also interested in responses of the form "why not just do X?". Why not just add an "OFFSET 0" in the subselect so that

[GENERAL] Automated Backup On Windows

FYI on the witting a batch script to run on Windows 2003 R2 server with no install of postgresql or pgadmin III This will create a new file every time the backup is run with the Name then the date followed with the extension .backup I would have added it to the postgresqldocs.org webstie but

[GENERAL] How to check what is current postgres version

Hi, Is there any way to check the exact current version without issuing 'psql' command? Say, 8.2.1 or 8.2.9? Thanks, Nina

Re: [GENERAL] Suspected bug: outer WHERE reordered before inner WHERE -> input syntax ERROR

John Keith Hohm <[EMAIL PROTECTED]> writes: > This fails with ERROR: invalid input syntax for integer: "JOHN": > select * from ( > select * from (VALUES ('100'), ('JOHN')) as A (n) > where trim(trim(n), '0123456789') = '' > ) as B where n::integer <> -1; This isn't a bug: the optimizer is ent

Re: [GENERAL] How to check what is current postgres version

select version() Markova, Nina wrote: Hi, Is there any way to check the exact current version without issuing 'psql' command? Say, 8.2.1 or 8.2.9? Thanks, Nina

Re: [GENERAL] How to check what is current postgres version

select version(); ??? (Note does NOT require psql, just a connection you can issue sql over.) Or were you looking for a way from the command line? /usr/lib/postgresql/8.3/bin/postgres --version works for me on centos 5.2 On Mon, Sep 8, 2008 at 11:40 AM, Markova, Nina <[EMAIL PROTECTED]> wrote:

Re: [GENERAL] How to check what is current postgres version

Thanks From: justin [mailto:[EMAIL PROTECTED] Sent: September 8, 2008 13:54 To: Markova, Nina Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] How to check what is current postgres version select version() Markova, Nina wrote: Hi, I

[GENERAL] Postgres in a solaris zone - patch 125077-02 needed

Hi, I am working on installing postgres ina zone - zoneadm install command finished with errors : Error: /usr/lib/libkrb5.so.1 not found. Please install the patch for 6381288 (we should expose the krb5 api). The patch needed is 125077-02 for SPARC and 125078-02 for x86 systems. pkgadd: ERROR: che

Re: [GENERAL] Suspected bug: outer WHERE reordered before inner WHERE -> input syntax ERROR

On Mon, Sep 08, 2008 at 01:53:03PM -0400, Tom Lane wrote: > John Keith Hohm <[EMAIL PROTECTED]> writes: > > This fails with ERROR: invalid input syntax for integer: "JOHN": > > select * from ( > > select * from (VALUES ('100'), ('JOHN')) as A (n) > > where trim(trim(n), '0123456789') = '' > >

Re: [GENERAL] Suspected bug: outer WHERE reordered before inner WHERE -> input syntax ERROR

On Mon, 08 Sep 2008 13:53:03 -0400 Tom Lane <[EMAIL PROTECTED]> wrote: > This isn't a bug: the optimizer is entitled to rearrange WHERE clauses > any way it pleases. If you want an optimization fence between the > inner and outer SELECTS, add OFFSET 0 (or LIMIT ALL if you like). Thanks for telli

[GENERAL] Postgres 8.3.1 RowExclusiveLock With JDBC XA

I am having a problem where row exclusive locks are causing applications and vacuums to hang. I shutdown all applications, but the locks remain. I stop and start postgres, but the locks are still there. A pid is not listed. This problem started showing up after switching to the XA datasource wit

Re: [GENERAL] Suspected bug: outer WHERE reordered before inner WHERE -> input syntax ERROR

Sam Mason <[EMAIL PROTECTED]> writes: > I'd interpret John's note as pointing out that SQL doesn't distinguish > between type declarations and type casting. I think he wants A.n to > be of type TEXT, would like to temporarily treat it as INTEGER in one > sub-expression. PG incorrectly propagates

Re: [GENERAL] Suspected bug: outer WHERE reordered before inner WHERE -> input syntax ERROR

John Keith Hohm <[EMAIL PROTECTED]> writes: > Thanks for telling me about LIMIT ALL being an optimization fence. > I believe you that it is not a bug, but I admin I cannot find any > documentation of that entitlement. It's over here: http://www.postgresql.org/docs/8.3/static/sql-expressions.html#S

[GENERAL] Install Postgres on a SAN volume?

We are using PostgreSQL 8.2.9 on Windows, and we are setting up some new machines. We used to install PostgreSQL on C: and then we put the tablespaces onto our SAN drive (Z:). When we tried to mount the snapshots of the SAN we learned that they were useless since we only had the tablespaces,

Re: [GENERAL] Install Postgres on a SAN volume?

On Mon, 8 Sep 2008, William Garrison wrote: 2) We could install PostgreSQL onto the C: drive and then configure the data folder to be on the SAN volume (Z:) Do that. You really don't want to get into the situation where you can't run anything related to the PostgreSQL service just because th

Re: [GENERAL] Install Postgres on a SAN volume?

On Mon, Sep 8, 2008 at 6:18 PM, William Garrison <[EMAIL PROTECTED]> wrote: > 2) We could install PostgreSQL onto the C: drive and then configure the data > folder to be > on the SAN volume (Z:) You want this. If you're going to take snapshots, you need all the data files AND the transaction log

Re: [GENERAL] Install Postgres on a SAN volume?

Thanks. I notice that the link you provided says: "Per best practices, my postgres data directory, xlogs and WAL archives are on different filesystems (ZFS of course). " Why is this a best practice? Is there a reference for that? Greg Smith wrote: On Mon, 8 Sep 2008, William Garrison wrote:

[GENERAL] OS X library path issues for libpq (ver 8.3)

I'm new to using PostgreSQL on mac OS X. I used a binary installer for PostgreSQL 8.3 on mac OS X 10.5, which installs into /Library/PostgreSQL/[version]/ I'm building a lot of software that links to libpq and most of the builds fail or the run-time fails, because it cannot find the PostgreS

Re: [GENERAL] Very weird problem of "order by" in postgresql

Oh, you are right! Although the 'locale' command show that the 2 has the same locale settings. The sort result of the same file is different. Ubuntu is right, centos is wrong…… Maybe I should as this question on some Linux mail lists or forums. Thanks a lot! On Mon, Sep 8, 2008 at 8:34 PM, Tom

Re: [GENERAL] ERROR: cache lookup failed for relation

List and (most importantly) somebody with the same problem, I finally got access to my table by performing these random steps: 1) /usr/local/pg8.1/bin/pg_resetxlog -x 0x500 -l 0x1,0x0,0xC9 mod [ read the manpage and figure out the args ] 2) following the steps here: http://people.planetpo

Re: [GENERAL] Postgres 8.3.1 RowExclusiveLock With JDBC XA

"Urciolo, Kevin" <[EMAIL PROTECTED]> writes: > I am having a problem where row exclusive locks are causing applications > and vacuums to hang. I shutdown all applications, but the locks remain. > I stop and start postgres, but the locks are still there. A pid is not > listed. This problem starte

Re: [GENERAL] A challenge for the SQL gurus out there...

On Sun, Sep 7, 2008 at 6:09 AM, Gregory Stark <[EMAIL PROTECTED]> wrote: > "Uwe C. Schroeder" <[EMAIL PROTECTED]> writes: > >> I want to get a list looking like >> >> forum idthread_id post_id >> 1 6 443 >> 2 9 123 >> 3 3 557 > ... >> It all

Re: [GENERAL] ERROR: cache lookup failed for relation

joe speigle <[EMAIL PROTECTED]> writes: > List and (most importantly) somebody with the same problem, > I finally got access to my table by performing these random steps: > ... > happily, there is no visible bad news even on running "\set verbosity verbose" Dump, initdb, reload. Do NOT assume tha

Re: [GENERAL] A challenge for the SQL gurus out there...

On Mon, Sep 8, 2008 at 9:49 PM, Merlin Moncure <[EMAIL PROTECTED]> wrote: > On Sun, Sep 7, 2008 at 6:09 AM, Gregory Stark <[EMAIL PROTECTED]> wrote: >> "Uwe C. Schroeder" <[EMAIL PROTECTED]> writes: >> >>> I want to get a list looking like >>> >>> forum idthread_id post_id >>> 1 6

[GENERAL] weird query runtime results...

hello pg'lers, we are facing a puzzle, and was wondering if you could advise? db has been just vacuumed: table as: CREATE TABLE bigdb.t_rrddata ( f_insrt_dt timestamptz, f_timestamp int4 NOT NULL, f_site_a text NOT NULL, f_site_b text NOT NULL, f_probe text NOT NULL, f_ipv varchar N

Re: [GENERAL] Install Postgres on a SAN volume?

On Mon, 8 Sep 2008, William Garrison wrote: I notice that the link you provided says: "Per best practices, my postgres data directory, xlogs and WAL archives are on different filesystems (ZFS of course). " Why is this a best practice? Is there a reference for that? Those all have different

Re: [GENERAL] Automated Backup On Windows

On Mon, 8 Sep 2008, justin wrote: I would have added it to the postgresqldocs.org webstie but can't create an account for my self.Tried following theses instructions but never could get it to work That page has been moved

[GENERAL] Server installation problem using freebsd ports

Hi, This might be a very small problem but I am stuck. When I try to install postgresql server 8.3.3 on freebsd using ports, I am getting - "postgresql-server-8.3.3 cannot install: unknown PostgreSQL version: 83" I was able to install client. This has something to do with pgsql_ver. What's