On 12/19/2012 5:44 AM, grell wrote:
Firstly I will tell you what I'm trying to do:
I have a database. I have a table Person. Person has location and this
location need to be updated f.e. every 5 min or with higher frequently.
Should I have some special tool, table or whatever to keep this data? I
On Thu, Dec 20, 2012 at 4:03 PM, Stephen Touset
wrote:
> I'm having difficulty locating the source of a problem our webapp has been
> running into. Multiple Google searches have failed me, so I'm hoping someone
> here can help troubleshoot.
>
> When some clients (psql, the webapp) connect to our
On Mon, Dec 17, 2012 at 2:38 PM, Kirk Wythers wrote:
>
> I seem to be dealing with a data type issue when I try and import data
> into a new and empty database.
>
> Error Message: ERROR: date/time field value out of range:
> "1332471600:00:00"
> LINE 1: ...tc_avg25") values ('2012-03-22 21:00:00_
On Thu, Dec 20, 2012 at 12:59 AM, Ivan Pašić wrote:
> Hi, I installed postgresql 8.4 and I set port number in installation as
> 5432. I also installed PostGIS and everything was working well. But now I
> wanted to create new server with port number 54321 but I can't do it because
> it gives me err
I seem to be dealing with a data type issue when I try and import data into a
new and empty database.
Error Message: ERROR: date/time field value out of range: "1332471600:00:00"
LINE 1: ...tc_avg25") values ('2012-03-22 21:00:00_B4WARM_A','133247160…
Here are the first two rows in the files I
Hi, I installed postgresql 8.4 and I set port number in installation as
5432. I also installed PostGIS and everything was working well. But now I
wanted to create new server with port number 54321 but I can't do it
because it gives me error ''Server doesn't listen''. I was reading about
that in the
I'm having difficulty locating the source of a problem our webapp has been
running into. Multiple Google searches have failed me, so I'm hoping someone
here can help troubleshoot.
When some clients (psql, the webapp) connect to our production database, they
become stuck in an aborted transactio
Wed, 19 Dec 2012 00:03:43 -0800 (PST)
Received: by 10.49.127.198 with SMTP id ni6mr757036qeb.23.1355904223562; Wed,
19 Dec 2012 00:03:43 -0800 (PST)
X-Newsgroups: pgsql.general
Date: Wed, 19 Dec 2012 00:03:43 -0800 (PST)
Complaints-To: groups-ab...@google.com
Injection-Info: glegroupsg2000
Hi Tatsuo Ishii,
I am very beginner at PostgreSQL. I am trying to create fail-over cluster
server (with replication and online recover) using pg-pool 3.1.
1. which prerequisite are needed for preparing failover server using
pgpool at linux environment (RHEL 6.1).
2. I am not able to conf
Hi,
Firstly I will tell you what I'm trying to do:
I have a database. I have a table Person. Person has location and this
location need to be updated f.e. every 5 min or with higher frequently.
Should I have some special tool, table or whatever to keep this data? I want
to have history of location
Hi,
In a projet, I have an heavy fonction that double the time of the query.
I was surprised because the function was IMMUTABLE but no cache happens.
So I wrote a small test.
test.sql
---
\timing on
CREATE OR REPLACE FUNCTION dum(a int)
RETURNS int
LANGUAGE SQ
I'm having difficulty locating the source of a problem our webapp has been
running into. Multiple Google searches have failed me, so I'm hoping someone
here can help troubleshoot.
When some clients (psql, the webapp) connect to our production database, they
become stuck in an aborted transactio
Hello,
I have the following table and indices defined:
CREATE TABLE ticket
(
wid bigint NOT NULL DEFAULT nextval('tickets_id_seq'::regclass),
eid bigint,
created timestamp with time zone NOT NULL DEFAULT now(),
status integer NOT NULL DEFAULT 0,
argsxml text,
moduleid character varyin
发自我的 iPad
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Sat, Dec 22, 2012 at 9:31 AM, Adrian Klaver wrote:
> On 12/21/2012 02:22 PM, Chris Angelico wrote:
>> As I understand it, there are three keywords: VOLATILE, STRICT, and
>> IMMUTABLE. Putting one of those keywords into the declaration flags
>> the function accordingly; if none is given, VOLATIL
Bryan Lee Nuse writes:
> My question is, then, how is it that the query embodied in "view_1" below
> executes fine, but cannot seem to be restored? Is this telling me my query
> is dumb? If so, any advice on how to easily derive "view_1" from "tab_1" and
> "tab_2" below, without baffling pg_r
On 12/21/2012 02:22 PM, Chris Angelico wrote:
On Sat, Dec 22, 2012 at 3:53 AM, David Johnston wrote:
Chris Angelico wrote, and David dropped the citation (oops!):
By the way, why do you declare your functions as "STRICT IMMUTABLE"
and "STRICT VOLATILE"?
Is this a question about the layout of
On Sat, Dec 22, 2012 at 3:53 AM, David Johnston wrote:
> Chris Angelico wrote, and David dropped the citation (oops!):
>> By the way, why do you declare your functions as "STRICT IMMUTABLE"
>> and "STRICT VOLATILE"?
>
> Is this a question about the layout of the commands spatially?
As I understan
Greetings, list,
I have a VIEW I use to combine both acquired and missing field observations.
Because of they way these data will be used, missing values can't be NULL. So
I make them some outrageous integer value, like -999. I put the full VIEW
together using UNION.
As shown in the test ca
HI all -
I wrote a blog post on how my site takes advantage of Postgres ENUM
types with NHibernate and Castle ActiveRecord. Thought I'd share:
http://blog.kitchenpc.com/2012/12/21/using-enum-types-with-postgresql-and-castle-activerecord/
Mike
PS - Lemme know if any of the Postgres stuff isn't
ERR ORR writes:
> 1) What is the difference between *varchar_pattern_ops* and *
> varchar_text_ops*?
None whatsoever --- they're both there just so that people can write an
opclass name that matches the declared type of their table column.
> 2) Is there any pre-definded operator class for indexe
"David Johnston" writes:
>> Indeed, COALESCE will not execute the second sub-select at runtime, but
>> that doesn't particularly matter here. What matters is that "ps3(2)"
>> qualifies to be pre-evaluated (folded to a constant) at plan time.
> Understood (I'm guessing there is no "global" cache
> Something to consider: Since you've told Postgres that your function is
> immutable, it might be remembering the result from the first execution and
> using it in the second. Try restarting the server between the EXPLAIN and
the
> test.
>
Now that you've said this as well (hopefully you are not
David Johnston wrote:
> Understood (I'm guessing there is no "global" cache but simply the
> plan-level cache that gets populated each time?)
>
> However, in the following example the ps3(2) expression should also qualify
> for this "folding" and thus the RAISE NOTICE should also appear during pl
> -Original Message-
> From: Tom Lane [mailto:t...@sss.pgh.pa.us]
> Sent: Friday, December 21, 2012 11:16 AM
> To: David Johnston
> Cc: 'Adrian Klaver'; 'jg'; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Coalesce bug ?
>
> "David Johnston" writes:
> > The first case is:
>
> > SE
David Johnston wrote:
> I thought that in order to call the Coalesce function the system
> would have to know the value of all parameters. There is no lazy
> instantiation in SQL.
Tom already addressed the main question, but I want to clarify this
point. COALESCE is not a function; it is defined
Hi,
I actually have several questions on indexes and operator classes:
1) What is the difference between *varchar_pattern_ops* and *
varchar_text_ops*?
They both appear to work similarly and in both cases the operands are cast
to
type TEXT when I look at the explain of the query.
2) Is there any
> -Original Message-
> From: Adrian Klaver [mailto:adrian.kla...@gmail.com]
> Sent: Friday, December 21, 2012 10:57 AM
> To: David Johnston
> Cc: 'jg'; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Coalesce bug ?
>
> On 12/21/2012 07:49 AM, David Johnston wrote:
> >> -Original
"David Johnston" writes:
> The first case is:
> SELECT COALESCE( (SELECT), (SELECT) ); I am not sure exactly what the
> parentheses surrounding the scalar-sub-SELECTs do (turn them into anonymously
> typed rows?) but if the first scalar-sub-select results in a non-null result
> then the secon
On Sat, Dec 22, 2012 at 2:57 AM, jg wrote:
> Hi,
>
> Interesting idea.
> With VOLATILE, the bug disappears.
> With IMMUTABLE, the EXPLAIN and the execution does not match
> That is a bug. Even if the behavior has to be different in VOLATILE and
> IMMUTABLE, the EXPLAIN and the execution MUST
Denis Papathanasiou writes:
> I did realize that ANY() must be a right-hand operator, but what I
> didn't understand (and admittedly still don't understand) is why regex
> operations that are normally right-side work from the left.
All regex operators in Postgres have the pattern on the right.
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Denis Papathanasiou
> Sent: Friday, December 21, 2012 10:43 AM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Using POSIX Regular Expressions on xml type
Hi,
Interesting idea.
With VOLATILE, the bug disappears.
With IMMUTABLE, the EXPLAIN and the execution does not match
That is a bug. Even if the behavior has to be different in VOLATILE and
IMMUTABLE, the EXPLAIN and the execution MUST be coherent.
JG
[postgres@]test=# create or replace fun
On 12/21/2012 07:49 AM, David Johnston wrote:
-Original Message-
In the first case the nested parentheses mean the SELECT statements are run
first and the COALESCE is run on the return results.
The first case is:
SELECT COALESCE( (SELECT), (SELECT) ); I am not sure exactly what th
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of jg
> Sent: Friday, December 21, 2012 10:40 AM
> To: Chris Angelico
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Coalesce bug ?
>
> Hi,
>
> Please test
On Sat, Dec 22, 2012 at 2:40 AM, jg wrote:
> Thank you for the documentation link, but it does not help me.
The documentation link states that a function with side effects *must*
to be declared VOLATILE (or if you prefer, *not* declared STRICT or
IMMUTABLE). Emitting warnings is a side effect; yo
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Adrian Klaver
> Sent: Friday, December 21, 2012 10:27 AM
> To: jg
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Coalesce bug ?
>
> On 12/21/2012 07:03 A
Hi,
Test done on PostgreSQL 9.2.1
pgb=# create or replace function ps3(a int) returns int as $$ BEGIN
pgb$# RAISE WARNING 'Call ps3(%)=%',$1,$1; RETURN $1::int;
pgb$# END; $$ LANGUAGE plpgsql STRICT IMMUTABLE;
CREATE FUNCTION
pgb=# SELECT ps3(1);
WARNING: Call ps3(1)=1
ps3
-
1
(1 row)
On 12/21/2012 10:35 AM, David Johnston wrote:
If you look at the four examples which follow the posix match table in the
docs (http://www.postgresql.org/docs/9.1/static/functions-
matching.html#FUNCTIONS-POSIX-TABLE),
some of them work from the left side, e.g.:
'abc' ~ '(b|d)' true
In my orig
Hi,
Please test this script on a PostgreSQL 9.1.6,
create or replace function ps3(a int) returns int as $$ BEGIN
RAISE WARNING 'Call ps3(%)=%',$1,$1; RETURN $1::int;
END; $$ LANGUAGE plpgsql STRICT IMMUTABLE;
SELECT ps3(1);
SELECT ps3(2);
select coalesce( (select ps3(1)), (SELECT ps3(2)) );
expla
>
> If you look at the four examples which follow the posix match table in the
> docs (http://www.postgresql.org/docs/9.1/static/functions-
> matching.html#FUNCTIONS-POSIX-TABLE),
> some of them work from the left side, e.g.:
>
> 'abc' ~ '(b|d)' true
>
> In my original example, I found I could
On Sat, Dec 22, 2012 at 2:25 AM, David Johnston wrote:
> You have defined the function as "IMMUTABLE". The system is allowed to cache
> the results of a given call (i.e. "ps3(2)") and return the value without
> actually executing the function ("never executed"). Your second example
> returns
On Fri, Dec 21, 2012 at 8:55 AM, jg wrote:
> Hi,
>
> In a projet, I have an heavy fonction that double the time of the query.
> I was surprised because the function was IMMUTABLE but no cache happens.
> So I wrote a small test.
>
> test.sql
> ---
> \timing on
>
On 12/21/2012 07:03 AM, jg wrote:
Hi,
In PostgreSQL 9.2, I have the following behavior, and I found it strange.
ps3 is executed or "never executed" ? !!!
JG
[postgres@]test=# create or replace function ps3(a int) returns int as $$ BEGIN
RAISE WARNING 'Call ps3(%)=%',$1,$1; RETURN $1::int;
END
Hi,
> In PostgreSQL 9.2, I have the following behavior, and I found it strange.
Sorry the test was with 9.1.6
# psql -V
psql (PostgreSQL) 9.1.6
JG
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pg
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of jg
> Sent: Friday, December 21, 2012 10:04 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Coalesce bug ?
>
> Hi,
>
> In PostgreSQL 9.2, I have the follow
Hi David,
On 12/20/2012 08:48 PM, David Johnston wrote:
-Original Message-
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
ow...@postgresql.org] On Behalf Of Denis Papathanasiou
Sent: Thursday, December 20, 2012 7:56 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL]
Hi,
In PostgreSQL 9.2, I have the following behavior, and I found it strange.
ps3 is executed or "never executed" ? !!!
JG
[postgres@]test=# create or replace function ps3(a int) returns int as $$ BEGIN
RAISE WARNING 'Call ps3(%)=%',$1,$1; RETURN $1::int;
END; $$ LANGUAGE plpgsql STRICT IMMUTAB
Hi,
In a projet, I have an heavy fonction that double the time of the query.
I was surprised because the function was IMMUTABLE but no cache happens.
So I wrote a small test.
test.sql
---
\timing on
CREATE OR REPLACE FUNCTION dum(a int)
RETURNS int
LANGUAGE SQ
On Fri, Dec 21, 2012 at 4:31 AM, Florian Weimer wrote:
> I would like to pipeline INSERT statements. The idea is to avoid waiting
> for server round trips if the INSERT has no RETURNING clause and runs in a
> transaction. In my case, the failure of an individual INSERT is not
> particularly inte
On Fri, Dec 21, 2012 at 10:45 AM, Christian Hammers wrote:
> Hallo
>
> A function that is used as part of an index has at least to be declared
> immutable:
>
> devel_np=# CREATE OR REPLACE FUNCTION f() RETURNS int AS $$ BEGIN return
> (random()*100)::int; END; $$ VOLATILE LANGUAGE plpgsql;
> CR
Hallo
A function that is used as part of an index has at least to be declared
immutable:
devel_np=# CREATE OR REPLACE FUNCTION f() RETURNS int AS $$ BEGIN return
(random()*100)::int; END; $$ VOLATILE LANGUAGE plpgsql;
CREATE FUNCTION
devel_np=# CREATE INDEX ON t (f(), i);
ERROR: functions
Thanks a lot!
>From documentation:
"
IMMUTABLE indicates that the function cannot modify the database and always
returns the same result when given the same argument values; that is, it
does not do database lookups or otherwise use information not directly
present in its argument list. If this op
Hello
On Fri, 21 Dec 2012 08:46:14 -0200
Alex Pires de Camargo wrote:
> Is it possible?
>
>Thanks!
Why not?
devel_np=# CREATE TABLE t (i int);
CREATE TABLE
devel_np=# CREATE INDEX ON t (length(i::text), i);
CREATE INDEX
bye,
-christian-
--
Sent via pgsql-general mailing list (p
I would like to pipeline INSERT statements. The idea is to avoid
waiting for server round trips if the INSERT has no RETURNING clause and
runs in a transaction. In my case, the failure of an individual INSERT
is not particularly interesting (it's a "can't happen" scenario, more or
less). I b
55 matches
Mail list logo
