Re: [GENERAL] Materialized views in Oracle

Craig Ringer, 22.09.2011 08:34: - You don't see materialized views without selective updating ("fast refresh") as useful. [I disagree, though I can see how it wouldn't be very useful for the particular use case you're facing.] One thing that is often overlooked and that I find most useful is th

[GENERAL] Is 9.1 considered more stable/robust than 9.0.4 ?

Thanks Danny

Re: [GENERAL] Is 9.1 considered more stable/robust than 9.0.4 ?

http://blog.2ndquadrant.com/en/2011/09/limitations-removed-in-postgre.html On Thu, 2011-09-22 at 02:36 -0500, Abraham, Danny wrote: > Thanks > Danny -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engi

Re: [GENERAL] Windows: where to put pgpass.conf for user "system"

Where should I put pgpass.conf for user "system"? AFAIK it hasn't got an %appdata% directory and the one for all users didn't work. Maybe you can set the environment variable PGPASSWORD temporarily when executing the script? Best Alex -- Sent via pgsql-general mailing list (pgsql-general@pos

Re: [GENERAL] Materialized views in Oracle

Hello, .. > up front, I'm not a database expert by any means and I'm nowhere close > to an Oracle expert, or even an Oracle novice. If Oracle was a > swimming pool, I would have those little floaty duck things on my > arms. Please go through this link http://asktom.oracle.com/pls/asktom/f?p=100:1

Re: [GENERAL] Date time value error in Ms Access using pass through queries

It shows error as 'Date/time value out of range' and gives the actual part of the sql statement which contains date as the details. I have installed both databases at different locations on same drive on Fedora 15 and accessing it from Windows Xp virtual machine. And both databases from 9.0. and 9

Re: [GENERAL] 10GbE / iSCSI storage for postgresql.

Dear Craig , The other end of the iSCSI shall have all the goodies like the raid controller with a WBC with BBU. There can even be multiple raid cards for multiple servers and disksets. I am even planning for NICs having TOE features . The doubt is will it work withing a acceptable performance r

[GENERAL] why VOLATILE attribute is required?

Hi All, I have this function: CREATE FUNCTION mypass(newpass text) returns text EXECUTE 'ALTER USER ' || quote_ident(session_user) || ' PASSWORD ' || quote_literal(newpass); return session_user::text; to varify user passwords before allowing a change. I've put that function in a RULE that s

[GENERAL] limitby without orderby

Hi Gurus, What is difference between select * from items order by random() limit 5; and select * items limit 5; my basic requirement is to get random rows from a table, my where clause will make sure I won't get same rows in repeated execution of above queries. -- Regards

Re: [GENERAL] Random multiple times

Many thanks both, those solutions are great and have gone in my wiki for future ref. Regards Oliver On 21 Sep 2011, at 21:56, Szymon Guz wrote: > > > >> Short answer is: yes. More information you can find > >> here > >> http://simononsoftware.com/problem-with-random-in-postgresql-subselect/ >

Re: [GENERAL] looking for a faster way to do that

hamann.w wrote:Gesendet: Mi 2011-09-21 17:59 > I have one large table (about a million entries) with an indexed column > containing codes > like ABC3561A, ABC3563X, 72-451-823 etc. (lots of order numbers from different > manufacturers) > > When I ask for a specific i

Re: [GENERAL] limitby without orderby

Hi, On 22 September 2011 21:32, Rohan Malhotra wrote: > Hi Gurus, > What is difference between > select * from items order by random() limit 5; > and > select * items limit 5; > my basic requirement is to get random rows from a table, my where clause This one says: give me first five rows which

Re: [GENERAL] looking for a faster way to do that

On 21 September 2011 17:59, wrote: > If I try to get many items on a prefix match > select code where code ~ wantcode > things go very slow. Explain shows a nested loop, so seemingly the table is > rescanned > for every wanted item in the other table. A test run (3000 wanted codes > against

[GENERAL] Speed of lo_unlink vs. DELETE on BYTEA

Hi, everyone. I'm working with someone who has a database application currently running under PostgreSQL 8.3.  Among other things, there is a main table that is referenced by a number of other tables via a foreign key.  One of those tables has a field of type "oid

Re: [GENERAL] why VOLATILE attribute is required?

On Thu, Sep 22, 2011 at 5:28 AM, Rafal Pietrak wrote: > Hi All, > > I have this function: > CREATE FUNCTION mypass(newpass text) returns text > EXECUTE 'ALTER USER ' || quote_ident(session_user) || ' PASSWORD ' || > quote_literal(newpass); return session_user::text; > > to varify user passwor

Re: [GENERAL] Problem dbi_link with postgresql 9.04

Emanuel Araújo wrote: > In one of our applications, we use the dbi_link for communication with a > firebird db, > works very well in version 8.3 we have one of our PostgreSQL server (CentOS > 5.3). > We are doing tests for migration to version 9.4 or 9.1, and the use of tests > dbi_link got the

Re: [GENERAL] why VOLATILE attribute is required?

Rafal Pietrak writes: > but I get: > ERROR: ALTER ROLE is not allowed in a non-volatile function > Why??? Because non-volatile means, among other things, "this function has no side effects". regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgr

Re: [GENERAL] Date time value error in Ms Access using pass through queries

On Thursday, September 22, 2011 2:25:40 am c k wrote: > It shows error as 'Date/time value out of range' and gives the actual part > of the sql statement which contains date as the details. What program threw the error, Access,ODBC or Postgres? > > I have installed both databases at different lo

Re: [GENERAL] why VOLATILE attribute is required?

On Thu, 2011-09-22 at 07:50 -0500, Merlin Moncure wrote: > On Thu, Sep 22, 2011 at 5:28 AM, Rafal Pietrak > wrote: > > if you change the state of the database, including (and especially) > system catalogs, your function is volatile, period. Hmmm. To quote from the online documentation: "VOLATI

Re: [GENERAL] why VOLATILE attribute is required?

Rafal Pietrak wrote: > ERROR: ALTER ROLE is not allowed in a non-volatile function > > Why??? See http://www.postgresql.org/docs/8.3/static/xfunc-volatility.html : A STABLE function cannot modify the database [...] Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@po

Re: [GENERAL] limitby without orderby

Rohan Malhotra wrote: > What is difference between > > select * from items order by random() limit 5; > > and > > select * items limit 5; > > my basic requirement is to get random rows from a table, my where clause will > make sure I won't get > same rows in repeated execution of above queri

[GENERAL] OS X 10.7, psql, and tab completion?

Hi! What's the incantation one needs to recite before compiling Postgres 8.4.x on OS X 10.7 such that psql's tab completion will work? I love my Mac, but Apple really dorked up libedit/readline and I just can't figure out what I'm supposed to do. Any hints will be greatly appreciated! eric --

Re: [GENERAL] why VOLATILE attribute is required?

On 22 September 2011 16:29, Rafal Pietrak wrote: > On Thu, 2011-09-22 at 07:50 -0500, Merlin Moncure wrote: > > On Thu, Sep 22, 2011 at 5:28 AM, Rafal Pietrak > wrote: > > > > if you change the state of the database, including (and especially) > > system catalogs, your function is volatile, peri

[GENERAL] Sending Results From One Function As Input into Another Function

Greetings, I need to send the results (SETOF RECORDS) from one function into another function, to produce another result (SETOF RECORDS). I am not quite sure how to do get this done. The first function filters a large table down a more manageable dataset. I want to send the results of this first f

[GENERAL] limitby without orderby

*From*: Rohan Malhotra select * from items order by random() limit 5; my basic requirement is to get random rows from a table, my where clause will make sure I won't get same rows in repeated execution of above queries. -- Regards To clarify, you are not looking for random then yes? as you

Re: [GENERAL] Materialized views in Oracle

On 09/21/2011 11:34 PM, Mike Christensen wrote: [ much omitted ] > One of the major components I'm working on is this reporting engine > that runs these major huge expensive queries (seriously, some of them > take 2-3 minutes to run) to generate data that gets dumped to an Excel > file for VPs t

Re: [GENERAL] Storing PHP 5.3 sessions into PostgreSQL 8.4

On Wed, Sep 21, 2011 at 10:53 AM, Alexander Farber < alexander.far...@gmail.com> wrote: > Hello, > > I'm using CentOS 6.0 Linux 64 bit with the stock packages: > > # rpm -qa|grep php > php-cli-5.3.2-6.el6_0.1.x86_64 > php-5.3.2-6.el6_0.1.x86_64 > php-xml-5.3.2-6.el6_0.1.x86_64 > php-pgsql-5.3.2-6.

Re: [GENERAL] OS X 10.7, psql, and tab completion?

Eric Ridge writes: > What's the incantation one needs to recite before compiling Postgres > 8.4.x on OS X 10.7 such that psql's tab completion will work? Install GNU readline :-(. Apple managed to improve libedit's tab completion from "sometimes dumps core" in 10.6 to "fails entirely" in 10.7, s

Re: [GENERAL] why VOLATILE attribute is required?

On Thu, 2011-09-22 at 16:57 +0200, Szymon Guz wrote: > > > On 22 September 2011 16:29, Rafal Pietrak > wrote: [--] > > Well. In this caase, I'd like it being optimised away. This is > the > expected result. And the above documentation fragment states >

[GENERAL] Statistics collector failure messages on startup

On PostgreSQL 9.0.1, on Ubuntu 10.04.2, I'm seeing these on startup: 2011-09-22 19:22:15.984 UTC,,,6066,,4e7b8ae7.17b2,1,,2011-09-22 19:22:15 UTC,,0,LOG,08006,"test message did not get through on socket for statistics collector","" 2011-09-22 19:22:15.984 UTC,,,6066,,4e7b8ae7.17b2,2,,201

Re: [GENERAL] Replication between 64/32bit systems?

Folks, >> Sure enough, what I'd really like to do is replicate from a Windows >> (or Linux) 64bit master to a Linux 32bit slave -- that's what I >> currently have easily available. thank you for your replies -- unfortunately, I'm not very content with "it simply won't work" without understandin

Re: [GENERAL] Materialized views in Oracle

On 09/22/2011 01:34 AM, Mike Christensen wrote: If Oracle was a swimming pool, I would have those little floaty duck things on my arms. Yes, it's too bad the license to get Oracle 11g with Floaties is cost prohibitive for most companies. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com

[GENERAL] Quick Date/Time Index Question

Hey, On 9.0.4 I have a database field that stores a timestamp to second+ precision; however, I want to search against it only to day precision. If I leave the field in second precision and try to "WHERE field BETWEEN date0 AND date0" I get no results (OK, fine) but then I cast the field to

Re: [GENERAL] Quick Date/Time Index Question

On Sep 22, 2011, at 2:13 PM, David Johnston wrote: > Hey, > > On 9.0.4 > > I have a database field that stores a timestamp to second+ precision; > however, I want to search against it only to day precision. If I leave the > field in second precision and try to “WHERE field BETWEEN date0 AND

[GENERAL] PostgreSQL to Ingres migration

Folks, A potential large PostgreSQL adopter contacted me in my role on the core team to see if I could forward them any Ingres --> PostgreSQL migration success stories. Does anyone have one? Also, if anyone offers this kind of migration as a professional service, and is experienced at it, please

Re: [GENERAL] limitby without orderby

On Thu, Sep 22, 2011 at 5:32 AM, Rohan Malhotra wrote: > Hi Gurus, > What is difference between > select * from items order by random() limit 5; > and > select * items limit 5; > my basic requirement is to get random rows from a table, my where clause > will make sure I won't get same rows in repe

Re: [GENERAL] Replication between 64/32bit systems?

On 09/22/11 1:35 PM, Hannes Erven wrote: I'm still hoping someone would give me a clue why 32/64 bit platform xlogs are (and always will?) be absolutely incompatible... ??? because the tuple representations are different, plain and simple. you can run a 32bit Postgres on your 64bit platform.

Re: [GENERAL] Replication between 64/32bit systems?

On Thu, Sep 22, 2011 at 2:45 PM, John R Pierce wrote: > On 09/22/11 1:35 PM, Hannes Erven wrote: >> >> I'm still hoping someone would give me a clue why 32/64 bit platform >> xlogs are (and always will?) be absolutely incompatible... ??? > > because the tuple representations are different, plain a

Re: [GENERAL] Quick Date/Time Index Question

"David Johnston" wrote: > I have a database field that stores a timestamp to second+ precision; > however, I want to search against it only to day precision. If I leave the > field in second precision and try to "WHERE field BETWEEN date0 AND date0" I > get no results (OK, fine) but then I cast

Re: [GENERAL] Replication between 64/32bit systems?

John R Pierce writes: > On 09/22/11 1:35 PM, Hannes Erven wrote: >> I'm still hoping someone would give me a clue why 32/64 bit platform >> xlogs are (and always will?) be absolutely incompatible... ??? > because the tuple representations are different, plain and simple. Specifically, the data a

Re: [GENERAL] Quick Date/Time Index Question

"David Johnston" writes: > I have a database field that stores a timestamp to second+ precision; > however, I want to search against it only to day precision. If I leave the > field in second precision and try to "WHERE field BETWEEN date0 AND date0" I > get no results (OK, fine) but then I cast

Re: [GENERAL] Is 9.1 considered more stable/robust than 9.0.4 ?

On Thu, 2011-09-22 at 02:36 -0500, Abraham, Danny wrote: > Thanks > > Danny If you are considering migrating from 9.0.X to 9.1.Y, then perhaps wait for a couple patch releases before going into production. However, I highly recommend that you migrate your development environment now, at least as

Re: [GENERAL] Statistics collector failure messages on startup

Christophe Pettus writes: > On PostgreSQL 9.0.1, on Ubuntu 10.04.2, I'm seeing these on startup: > 2011-09-22 19:22:15.984 UTC,,,6066,,4e7b8ae7.17b2,1,,2011-09-22 19:22:15 > UTC,,0,LOG,08006,"test message did not get through on socket for statistics > collector","" > 2011-09-22 19:22:15.

Re: [GENERAL] Speed of lo_unlink vs. DELETE on BYTEA

Hi again, everyone.  I'm replying to my own posting, to add some information: I decided to do some of my own benchmarking.  And if my benchmarks are at all accurate, then I'm left wondering why people use large objects at all, given their clunky API and their extremely sl

Re: [GENERAL] Quick Date/Time Index Question

Tom Lane wrote: >> I have a database field that stores a timestamp to second+ precision; >> however, I want to search against it only to day precision. If I leave the >> field in second precision and try to "WHERE field BETWEEN date0 AND date0" I >> get no results (OK, fine) but then I cast the

Re: [GENERAL] Quick Date/Time Index Question

On Sep 22, 2011, at 17:13, David Johnston wrote: > Is there some other way to create an index on only the "date" portion of the > field? The cast from timestamptz to date is time zone dependent, which is why it's volatile and can't be used indexes. However, you can do this: test=# create table

[GENERAL] how to install 9.1 on non-graphical server?

Hi, is there a way to install the EnterpriseDB V9.1 release on a server without X-Windows? I've got an OpenSuse 11.4 and the have only V9.0.4 in their repository so I'd need the binary of EnterpriseDB. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to yo

Re: [GENERAL] Replication between 64/32bit systems?

On 09/23/2011 04:35 AM, Hannes Erven wrote: thank you for your replies -- unfortunately, I'm not very content with "it simply won't work" without understanding what the reasons are. ;-) Endianness, OK, but that's the same on i32/64 platforms. So is it just the length of the int& friends data ty

Re: [GENERAL] Replication between 64/32bit systems?

On Thu, Sep 22, 2011 at 5:46 PM, Craig Ringer wrote: > On 09/23/2011 04:35 AM, Hannes Erven wrote: >> >> thank you for your replies -- unfortunately, I'm not very content with >> "it simply won't work" without understanding what the reasons are. ;-) >> >> Endianness, OK, but that's the same on i32

Re: [GENERAL] how to install 9.1 on non-graphical server?

2011/9/22 Andreas : > Hi, > is there a way to install the EnterpriseDB V9.1 release on a server without > X-Windows? > I've got an OpenSuse 11.4 and the have only V9.0.4 in their repository so > I'd need the binary of EnterpriseDB. Have you tried to use a --help on the EDB installer command line?

[GENERAL] PSQLException exception when dropping database and calling has_database_privilege: ERROR: database "test_1" does not exist

Hi Guys, I have an application which will call following SQL code to retrieve roles privileges on all databases in one server instance: SELECT r.rolname, d.datname, has_database_privilege(r.oid, d.oid, 'CONNECT') connectable, has_database_privilege(r.oid, d.oid, 'CREATE') creatable FROM

[GENERAL] about synchronous_standby_names or sync replic

Hi Folks, I wonder if it's possible to have a sync replication only from the the node where the client who update the DB and other node replicated asynchronously ? example : nodeCentral node1 node2 node3 user1 from node3 update db to nodeCentral (master) so nodeCentral replicates synchronously

Re: [GENERAL] about synchronous_standby_names or sync replic

On Thu, Sep 22, 2011 at 2:34 PM, e-blokos wrote: > Hi Folks, > > I wonder if it's possible to have a sync replication only from the the node > where the client > who update the DB and other node replicated asynchronously ? > > example : > nodeCentral node1 node2 node3 > > user1  from node3 update

Re: [GENERAL] how just install psql on windows?

You can install pgadmin, it contain s psql. 2011/9/22, Andreas : > Hi, > > can I just install psql on windows? > Just the client without the server. > There ist the server release as zip-file that includes psql so I at > least hadn't had to actually install a service with a db cluster but > having

Re: [GENERAL] about synchronous_standby_names or sync replic

Thanks for your answer. That's great, so for later other nodes replication it needs an asynchronous replic like pg_stat or even a good old rsync ? Franck - Original Message - From: "Jaime Casanova" To: "e-blokos" Cc: Sent: Thursday, September 22, 2011 11:38 PM Subject: Re: [GENERAL]

Re: [GENERAL] 10GbE / iSCSI storage for postgresql.

On 22/09/2011 5:47 PM, Rajesh Kumar Mallah wrote: Dear Craig , The other end of the iSCSI shall have all the goodies like the raid controller with a WBC with BBU. There can even be multiple raid cards for multiple servers and disksets. I am even planning for NICs having TOE features . The dou