El mié, 11-01-2012 a las 10:40 -0500, Tom Lane escribió:
> Gerardo Herzig writes:
> > Hi all. Im working on a 'simple' query with 7, 8 left joins. After the
> > 9nth join or so, explain analyze became to show the plan with many
> > tables being read in sequential
El mié, 11-01-2012 a las 10:40 -0500, Tom Lane escribió:
> Gerardo Herzig writes:
> > Hi all. Im working on a 'simple' query with 7, 8 left joins. After the
> > 9nth join or so, explain analyze became to show the plan with many
> > tables being read in sequential
Hi all. Im working on a 'simple' query with 7, 8 left joins. After the
9nth join or so, explain analyze became to show the plan with many
tables being read in sequential fashion. Of course, this slows down the
query response in a factor on 10.
I can alter the order in wich the join's are appended,
El vie, 04-02-2011 a las 12:17 +0100, Pavel Stehule escribió:
> >>
> >
> > M ok Thanks...So there is no workaround/alternative to this?
> >
>
> yes, (SELECT x.* from func(...) x) instead SELECT (func(...)).*
>
> regards
>
> Pavel Stehule
>
> > Gerardo
> >
> >
>
Great, i will check that. T
El jue, 03-02-2011 a las 20:47 +0100, Pavel Stehule escribió:
> Hello
>
> If you use a record expansion over function's result, then function is
> called once for record's field.
>
> so don't do it on slow functions.
>
> Regards
>
> Pavel
>
>
Hi all, im using a function of my own in a subquery, and when wonderig
about the slowliness of this one, y relalize that:
test=# SELECT (_xxfunction(854,'711H',11,false)).* ;
(4 filas)
--Result DELETED
Duración: 1069,465 ms
glyms=# SELECT * from _xxfunction(854,'711H',11,false) ;
(4 filas)
Durac
Hi all. I dont understand why this is happening. Simple hardcoded
function, looking for an id who doesn't exists:
REATE FUNCTION pp()
returns setof public.pp
as
$$
select * from public.pp where id=99;
$$ language sql;
So there is no id=9 in table public.pp
test=# SELECT * from pp();
id|
Bryan White wrote:
> I was suprised to find out that ORDER BY is case insensitive. Is
> there a way to do a case sensitive ORDER BY clause?
>
> This transcript demonstrates what I am seeing:
>
> $ createdb bryan
> $ psql bryan
> psql (8.4.4)
> Type "help" for help.
>
> bryan=# create table t (f
Jasen Betts wrote:
> On 2010-05-26, Gerardo Herzig wrote:
>> Hi all. Im not being able to cast a record variable into an array.
>>
>> Im writing a trigger, and i would like to store NEW (and OLD) as text[].
>> There is a way to do it in plpgsql? (w/o any contrib modul
Hi all. Im not being able to cast a record variable into an array.
Im writing a trigger, and i would like to store NEW (and OLD) as text[].
There is a way to do it in plpgsql? (w/o any contrib modules)
thanks!
Gerardo
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes
Scott Marlowe wrote:
> On Mon, May 10, 2010 at 12:40 PM, Gerardo Herzig wrote:
>> Hi all. Im triyng to see if i can improve the performance of a query
>> (mainly a 10 table join)
>>
>> 1) Besides of triyng to use indexes, there is some rules of thumb to follow?
>
Hi all. Im triyng to see if i can improve the performance of a query
(mainly a 10 table join)
1) Besides of triyng to use indexes, there is some rules of thumb to follow?
2) Should i try to join the bigger tables last in the query?
3) There is some place for understanding EXPLAIN better?
Thanks
John Dizaro wrote:
> I, nead to capture the IP number from the PC how is running the script
> "update TABLE1 set campo1 = 123 where ...; "
> Can someone help me please?
>
>
the pg_stat_activity view has a column named client_addr and a
current_query column.
That should help.
Gerardo
--
Sent v
Louis-David Mitterrand wrote:
> Hi,
>
> With builtin aggregates is it possible to return the value just before
> max(col)?
>
> Thanks,
>
Mmmm what about
select max(col) from table where col not in (select max(col) from table;
? Looks like a double table reading, but it works.
Gerardo
--
Sent
Uwe Maiwald wrote:
> how to get the name of the sequence that is responsible for setting the
> autoincrement value of a tables primary key column?
>
> i only have the name of the table and need to have an appropiate sql
> statement.
>
>
> i need this to write a program that loops through all tab
Tom Lane wrote:
> Gerardo Herzig writes:
>> Hi all. Im triyng, with no success, to create a user, and set the
>> password with a md5 string.
>
> The correct method for computing the md5'd password includes the
> username. I think it's 'md5' || md5sum(
Hi all. Im triyng, with no success, to create a user, and set the
password with a md5 string.
I did this:
comechingon:~ # echo -n 123 | md5sum
202cb962ac59075b964b07152d234b70 -
Ok, so then
test=# CREATE USER foobar ENCRYPTED PASSWORD
'md5202cb962ac59075b964b07152d234b70'; --I have to add 'md5
Bryce Nesbitt wrote:
> So when restoring a particular DB with pg_restore, I get "WARNING:
> errors ignore on restore". Is there a way to dump a list of those errors?
>
>
> # /usr/local/bin/pg_restore8.3 -d SUP-3067 -h 192.168.1.93 -p 5433 -U
> postgres -L toc_with_parts_commented_out.txt -v pr
Grant Masan wrote:
> Hi all,
>
> I have this kind of query that I need to do, yes my query is giving right
> answers now but it is long and slow. I am now asking you that if
> you have another solution for my query to make that more smarter ! Hope you
> can help me with this !
>
>
> select '0001
Hi All. My poor english doest not allow me to get a better subject.
Here is the thing: I have a function who returns a two column result,
that is is used inside another function, like this
CREATE FUNCTION show_result((some args), OUT shoe varchar, OUT desc
varchar , OUT price numeric)
...
as
$$
se
Tom Lane wrote:
> Gerardo Herzig writes:
>> Obviously im missing something, i cant find any relation between
>> pg_proc.pronamespace and pg_namespace.
>
> You didn't look at pg_namespace.oid, which is a "system column"
> meaning SELECT * doesn't show
i all. Looking for a way to change some functions to 'security
definer'. This is only going to happen in some of the schemas.I found
that pg_catalog.pg_proc have a bool column (prosecdef), which contains
if some function is defined as 'security definer'. So good. It also
contains a column named pro
Hi all. There is a way to simulate the `pipe' in linux so y can use
replace() for replacing 2 different things?
Or i just have to call replace two times?
Thanks!
Gerardo
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.or
Rodrigo E. De León Plicet wrote:
> On Thu, Aug 20, 2009 at 10:17 AM, Gerardo Herzig wrote:
>> Hi all. I have 2 functions , which returns the same columns, but with
>> tottaly different logics. I want to wrap it with a third function, who
>> will receive a boolean argume
Hi all. I have 2 functions , which returns the same columns, but with
tottaly different logics. I want to wrap it with a third function, who
will receive a boolean argument to decide wich sub-function have to use.
I want to use case, so i dont have to use pl/pgsql.
This is a non-working example:
Dave Page wrote:
> On Mon, May 18, 2009 at 1:29 PM, Gerardo Herzig wrote:
>
>> I think this is a good time to propose some kind of CookBook, to
>> preserve this kind of answers.
>
> What, like this one?
>
> http://wiki.postgresql.org/wiki/Snippets
>
> :-)
&
I just saw a beatifull answer from Pavel, as an answer to this question:
"""
I'm just wondering if there's some way to retrieve the hour column as the
> sum of the array values... Just like this:
>
>hour| statistics_date
> +-
> 90
Luigi N. Puleio wrote:
> --- On Fri, 5/8/09, Gerardo Herzig wrote:
>
> From: Gerardo Herzig
> Subject: Re: [SQL] RAISE NOTICE
> To: "Luigi N. Puleio"
> Cc: pgsql-sql@postgresql.org
> Date: Friday, May 8, 2009, 11:34 AM
>
> Luigi N. Puleio wrote:
>>
Luigi N. Puleio wrote:
> Hello everyone...
>
> I have a PL/PgSQL function where I use RAISE NOTICE but this kind of RAISE
> can't be shown on windows with a popup message like the EXCEPTION, indeed it
> goes to log messages list..
> So, is there any way to show a popup message with a custom text
Jyoti Seth wrote:
> Hello,
>
> I have two databases db1 and db2 with the same database structure on
> different systems with no network connection. In the first system with the
> db1 database user updates the master data. At the end of every day, the user
> needs to take the backup of updated data
Tom Lane wrote:
> Gerardo Herzig writes:
>> Hi all. Im failing while trying to obtain some metainfo from
>> information_schema. Acording to the docs, constraint_column_usage and
>> key_column_usage views contains some information about constraints and
>> indexes.
>
Hi all. Im failing while trying to obtain some metainfo from
information_schema. Acording to the docs, constraint_column_usage and
key_column_usage views contains some information about constraints and
indexes.
See:
Table "public.almatnov"
Column| Type
Hi all. I have a situation here: I have to make a 'report' for a table.
I'll try to explain the functionality needed:
Lets say i have a table like this
\d people
name varchar
id_city integer (references city(id_city))
\d city
id_city integer
city_name varchar
id_country (references country (id_cou
A. Kretschmer wrote:
> am Tue, dem 18.11.2008, um 1:09:44 -0800 mailte mahmoud ewiwi folgendes:
>> hi gurus
>> i have a problem in generating serial number with the form mm how
>> can i do that?
>
> test=# create temporary sequence foo;
> CREATE SEQUENCE
> test=# select to_char(current_
Hi all. Inside a plpgsql function, i have a EXECUTE statement, which
sometimes seems to be receiving a null querystring.
Can i check that condition in a BEGIN..EXCEPTION block? Under which
condition?
Or should just check if that string is null?
Thanks!
Gerardo
--
Sent via pgsql-sql mailing li
Tom Lane wrote:
> Richard Huxton <[EMAIL PROTECTED]> writes:
>> SELECT
>> CASE WHEN total >0 THEN total ELSE -1 END AS new_total
>> FROM (
>> SELECT count(*) AS total FROM test WHERE id=$1
>> ) AS raw_total
>
> Actually you could just do
>
> SELECT
> CASE WHEN count(*) >0 THEN count(*) ELSE
Achilleas Mantzios wrote:
> Στις Friday 17 October 2008 15:11:10 ο/η Gerardo Herzig έγραψε:
>> Richard Huxton wrote:
>>> Gerardo Herzig wrote:
>>>> But it does a doble count(*) that i must avoid.
>>>> I cant refer to the 'first' count like
>&
Richard Huxton wrote:
> Gerardo Herzig wrote:
>> But it does a doble count(*) that i must avoid.
>> I cant refer to the 'first' count like
>> select case when (select count(*) from test where id=$1 ) AS total
>> > 0 then total
>> else -1
>&
Hi all. Im triyng to implement this in plain sql.
The only thing i have working is
select case when (select count(*) from test where id=$1 )
> 0 then (select count(*) from test where id=$1)
else -1
end;
But it does a doble count(*) that i must avoid.
I cant refer to the 'first' count
Hi all. Im facing a situation where i have to replicate a table from
database A in databases B,C,F and so on.
The first (and only) thing i have in mind is using triggers with dblink
for comunications with the other DB's.
I dont even like the idea of replicating tables across databases, but it
is
Hi dudes. Im facing a problem with pg_dump,
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: could not identify an
ordering operator for type name
HINT: Use an explicit ordering operator or modify the query.
pg_dump: The command was: SELECT t.tableoid, t.oid, t.relname as
i
Sumaya wrote:
Hi,
I am converting sql code from sql server to postgresql. Data is currently
being retrieved from multiple databases, how do I do this using postgresql. I
have tried using eg. select * from datbasename.tablename but this does not
work. Any ideas?
Thanks,
Sumaya
Are you
Anoop G wrote:
Hi ,
Iam new to plpython,how can I return a recordset from a plpython
function?
Is there is any way give me an example;
plpgsql function
CREATE OR REPLACE FUNCTION function_to_get_all_countries() RETURNS SETOF
RECORD AS $BODY$
DECLARE
r
Anoop G wrote:
Hi ,
Iam new to plpython,how can I return a recordset from a plpython
function?
Is there is any way give me an example;
plpgsql function
CREATE OR REPLACE FUNCTION function_to_get_all_countries() RETURNS SETOF
RECORD AS $BODY$
DECLARE
r
Tom Lane wrote:
Gerardo Herzig <[EMAIL PROTECTED]> writes:
Hi all. Im having a hard time trying to compile the plpython package.
This is the error make gives me:
/usr/lib/python2.5/config/libpython2.5.a(abstract.o): relocation
R_X86_64_32 against `a local symbol' can
Hi all. Im having a hard time trying to compile the plpython package.
This is the error make gives me:
[EMAIL PROTECTED]:/usr/local/src/postgresql-8.2.5/src/pl/plpython> make
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline
-Wdeclaration-after-statement -Wendif-labels -fno-strict-alia
D'Arcy J.M. Cain wrote:
On Fri, 18 Jan 2008 12:16:04 -0300
Gerardo Herzig <[EMAIL PROTECTED]> wrote:
Right. But today, that trigger do some other work, wich includes
writing some files to disk, so there is my problem. Crap, i guess i will
have to review the main logic.
Alvaro Herrera wrote:
Gerardo Herzig escribió:
Right. But today, that trigger do some other work, wich includes writing
some files to disk, so there is my problem. Crap, i guess i will have to
review the main logic.
Probably it's better to move the actual file writing
Filip Rembiałkowski wrote:
2008/1/18, Gerardo Herzig <[EMAIL PROTECTED]>:
Hi all. Im puzzled again. Just thinking:
As im having fun trying to make my own replication system, im stuck in
this situation:
Consider a simple table with a unique index on the `id' field, and a
functi
Hi all. Im puzzled again. Just thinking:
As im having fun trying to make my own replication system, im stuck in
this situation:
Consider a simple table with a unique index on the `id' field, and a
function who will fail, such as
insert into test (id) values (1);
insert into test (id) values (
Hi all. Im working on a "on each statement" update trigger, so NEW and
OLD are NULL.
Suppose a simple query like 'update mytable set id=id+500 where id <
50'...There is a way to obtaining the 'set id=..' and the where clause
in some way?
Thanks!
Gerardo
---(end of broad
Daniel Caune wrote:
Hi,
Is there any way to define a SQL stored function that inserts a row in a
table and returns the serial generated?
CREATE TABLE matchmaking_session
(
session_id bigint NOT NULL DEFAULT
nextval('seq_matchmaking_session_id'),
...
);
CREATE FUNCTION create_matchmaking_ses
Tom Lane wrote:
Alvaro Herrera <[EMAIL PROTECTED]> writes:
Gerardo Herzig escribió:
Yes, the TRUNCATE statement is not sql ansi, maybe is a more low level
thing than i think.
TRUNCATE currently does not fire triggers, but that doesn't mean it's
impossi
Pavel Stehule wrote:
On 08/01/2008, Chris Browne <[EMAIL PROTECTED]> wrote:
[EMAIL PROTECTED] (Gerardo Herzig) writes:
Hi all. Acording to the docs, TRUNCATE will not fire a DELETE trigger
on the table being truncated.
There is a way to capture a TRUNCATE in any way?
I
Hi all. Acording to the docs, TRUNCATE will not fire a DELETE trigger on
the table being truncated.
There is a way to capture a TRUNCATE in any way?
Thanks!
Gerardo
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
C. Bergström wrote:
On Mon, 2008-01-07 at 10:19 -0300, Gerardo Herzig wrote:
Hi all. Im having some fun trying to write my own replication system
using python. I will use the postgres own WAL archiving to write the
files, then my app will read them and do some stuff. As im not a C
Hi all. Im having some fun trying to write my own replication system
using python. I will use the postgres own WAL archiving to write the
files, then my app will read them and do some stuff. As im not a C
programmer, im stuck in the mission of reading the binary files.
I guess im needing to k
Hi all. Im trying to set a Standby server for HA. I have set the program
to copy the WAL files to the standby server, but because im so stupid,
for some stupid reason i delete those files from the standby server, and
now, when i set the recovery_command at the standby server, it tells me that
"
Tom Lane wrote:
Gerardo Herzig <[EMAIL PROTECTED]> writes:
Hi all. Im having some "problems" with a small concurrent plpython function.
Don't even *think* about starting multiple threads inside the Postgres
backend. It's an exc
Hi all. Im having some "problems" with a small concurrent plpython function.
Based on a small example [1] about concurrent programming, there is some
code which works fine under python:
#! /usr/bin/python
import threading, random
import time
def myPrint(str):
print 'searching...', str
t
Alvaro Herrera wrote:
Gerardo Herzig escribió:
Stephen Cook wrote:
I am curious (coming from a MS SQL Server background, I just started
playing with PostgreSQL recently).
What type of situation would warrant a statement-level trigger that can't
access the old and new v
Stephen Cook wrote:
I am curious (coming from a MS SQL Server background, I just started
playing with PostgreSQL recently).
What type of situation would warrant a statement-level trigger that
can't access the old and new values? Without that access, isn't the
only information you get is the
Gregory Stark wrote:
"Gerardo Herzig" <[EMAIL PROTECTED]> writes:
Hi all. Well, im having this problem for the first time.
When executing a VACUUM, i have this msg:
gse_new_version=# VACUUM ;
ERROR: could not access status of transaction 118
DETAIL: could not open file &q
Hi all. Well, im having this problem for the first time.
When executing a VACUUM, i have this msg:
gse_new_version=# VACUUM ;
ERROR: could not access status of transaction 118
DETAIL: could not open file "pg_clog/": No existe el fichero o el
directorio
Ok, wtf, i will create the file. So
Rodrigo De León wrote:
On 10/30/07, Gerardo Herzig <[EMAIL PROTECTED]> wrote:
Wreird enough to me, need some advice plz!
CREATE OR REPLACE FUNCTION READ_WORDS(BIGINT, INT[])
RETURNS VARCHAR
AS
$$
DECLARE
RETURNVALUE VARCHAR;
BEGIN
SELECT ARRAY_TO_STRING(ARRAY(
Hi all. Im having some trouble here that cannot understand.
Consider this function:
CREATE OR REPLACE FUNCTION read_words(bigint, varchar)
returns varchar
as
$$
declare
returnValue varchar ;
BEGIN
select * into returnValue from array_to_string(array(select word
from words where page_id=$1
Patrick De Zlio wrote:
Hi listers,
As a PG administrator, I'm trying to read technical data from pg_class table
to monitor tables and indexes space.
We are running a quite big postgres platform, with multiple databases,
multiples schemes in each database, and hundreds tables.
When I run the a
Check the postgresql.conf in the $prefix/data dir. Also post the exact
error log (which in this case should be some like "address already in
use", because port 5432 is in use by the 8.2.4 version), and also the
exact command you are executing.
Cheers.
Gerardo
I didn't succeed installing the
Loredana Curugiu wrote:
Hi all,
I need to have two different vesions of postgres running in parallel on
different ports. Does anyone knows how to install two different versions
of postgres (7.4.5 and 8.2.4) on the same computer? I am using Linux
operating system.
Any information would greatly
We should have a onliner contest. I love oneliners!!!
oneliner:
select date_trunc('month',now()) + ((8 - extract('dow' from date_trunc
('month',now()))||'days')::text)::interval;
Kristo
On 04.06.2007, at 19:39, Michael Glaesemann wrote:
---(end of broadcast)--
Hi all. Im wondering if there is a way to include in the postgresql logs
the total rows retrieved after a query (im logging the query itself
right now). Something like apache does when it logs the amount of bytes
retrieved after a GET.
Thanks!
Gerardo
---(end of broadc
must appear in GROUP clause or be used in an
aggregate function"
And i want to group by page_id only, because that is what i need to
count. Tips here?
Thanks again man.
Gerardo
am Wed, dem 28.03.2007, um 11:04:29 -0300 mailte Gerardo Herzig folgendes:
Hi dudes, i have the follo
Hi dudes, i have the following question
i have 2 tables with the same format, and i want to know if is there a
possibility of using some sort of count(*) for achieving this result:
select a from table1 where (condition) union select a from table2 where
(condition), count(a) group by a
The idea
Hi dudes. I have my pg log file rotation configured so i have a
psql_`today`.log. The thing is, can i configure postgres so the file
permissions (today is 600) could by 640, so developers could login and
tailf the logfile (without using the postgres superuser, course)?
Thanks!
Gerardo
--
Someday i will ask some question that will makes you think a little
more. You'll see.
I will populate the tables and redo the explain.
Thanks dudes!!
Gerardo
Gerardo Herzig <[EMAIL PROTECTED]> writes:
Watch the last row of the explain command. It makes a sequential scan on the
Hi all. I have this 2 relations
gse=# \d pages
Table "public.pages"
Column| Type| Modifiers
-+---+
id
Hi all, i have a large table with one varchar field, and im triyng to
get information about what index should i use in order to using that
index (if this is possible) for ~ searching, and/or using LIKE searching.
Thanks!
Gerardo
---(end of broadcast)
Cool!! Thanks a lot! I will try it as soon as possible
Gerardo
Gerardo Herzig wrote:
Hi all: What a want to do is something like this:
suppose i have this record
aa--bb--cc
I guess if im able to do some sql/plsql procedure to get something
like it
aa
bb
cc
(3 records, rigth?)
Thanks a
Hi all: What a want to do is something like this:
suppose i have this record
aa--bb--cc
I guess if im able to do some sql/plsql procedure to get something like it
aa
bb
cc
(3 records, rigth?)
Thanks a lot
Gerardo
---(end of broadcast)---
TIP 9: I
79 matches
Mail list logo
