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
Hello
On Fri, 21 Dec 2012 08:46:14 -0200
Alex Pires de Camargo acama...@gmail.com 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
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
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:
On Fri, Dec 21, 2012 at 10:45 AM, Christian Hammers c...@lathspell.de 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
On Fri, Dec 21, 2012 at 4:31 AM, Florian Weimer fwei...@redhat.com 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
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
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
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]
-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 following
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:
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;
On Fri, Dec 21, 2012 at 8:55 AM, jg j...@rilk.com 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 Sat, Dec 22, 2012 at 2:25 AM, David Johnston pol...@yahoo.com 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
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 write
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)) );
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
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)
-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 AM, jg
On Sat, Dec 22, 2012 at 2:40 AM, jg j...@rilk.com 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
-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 this
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
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
-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
Denis Papathanasiou denis.papathanas...@banrai.com 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
On Sat, Dec 22, 2012 at 2:57 AM, jg j...@rilk.com 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
David Johnston pol...@yahoo.com 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
-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
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
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 by
-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 pol...@yahoo.com writes:
The first case is:
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 plan
time
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 pol...@yahoo.com 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
ERR ORR rd0...@gmail.com 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
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
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
On Sat, Dec 22, 2012 at 3:53 AM, David Johnston pol...@yahoo.com 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
On 12/21/2012 02:22 PM, Chris Angelico wrote:
On Sat, Dec 22, 2012 at 3:53 AM, David Johnston pol...@yahoo.com 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
Bryan Lee Nuse n...@uga.edu 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
On Sat, Dec 22, 2012 at 9:31 AM, Adrian Klaver adrian.kla...@gmail.com 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
发自我的 iPad
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
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
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
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
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
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:
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
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
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
On Thu, Dec 20, 2012 at 12:59 AM, Ivan Pašić ivanpasic...@gmail.com 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
On Mon, Dec 17, 2012 at 2:38 PM, Kirk Wythers wythe...@umn.edu 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
On Thu, Dec 20, 2012 at 4:03 PM, Stephen Touset
stephen.tou...@onelogin.com 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
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?
55 matches
Mail list logo
