Craig Ringer wrote:
On 19/01/2010 1:13 AM, Vincenzo Romano wrote:
Another case, Tom, could be when the file is updated from a non-DB
application and you need to synchronize
the data with other DB applications ...
How can that work without a transactional file system, though? If the
external
In response to Yan Cheng Cheok :
> As you can see, their select statement is the same. Except stored
> procedure is having additional 'QUERY'. Does that make the speed
> difference?
No. The problem is, the planner don't know the actual parameters.
Therefore the planner picked out a wrong plan (se
I have a stored procedure, which perform single RETURN QUERY SELECT..., by
taking in 2 function parameters.
It takes around 7 seconds to complete the operation.
When I perform outside stored procedure, with exact same SELECT statement, it
only takes 0.5 seconds, with same result being returned.
Elian Laura wrote:
Hello,
I´m an undergraduate in Peru in systems engineering.
I need to know about how does postgres work with the definition of
data type in run time.
I downloaded de source code of postgres, but es very complex, at least
I would like to know in which part of the code is the r
Hello,
I´m an undergraduate in Peru in systems engineering.
I need to know about how does postgres work with the definition of data type
in run time.
I downloaded de source code of postgres, but es very complex, at least I
would like to know in which part of the code is the recognition of a data
th
Thanks. I solved my problem using :
RETURN QUERY SELECT ...
Thanks and Regards
Yan Cheng CHEOK
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hey all,
I need help on constructing a query with arrays that matches the arrays up
as foriegn keys to another table. For example, say that I have two tables,
owners and pets.
Owner Table
owner_id | pet_ids
1 |{1,2,3}
2 |{2,3}
Pet Table
pet_ids | Pet Type
1
On Jan 19, 2010, at 2:57 PM, Tom Lane wrote:
> Ben Chobot writes:
>> Shouldn't the source change to "config file" after the reset?
>
> Not within the same session. ALTER DATABASE and ALTER USER settings
> are only examined at session startup.
OK, things are working as expected now. Thanks!
-
Ben Chobot writes:
> Shouldn't the source change to "config file" after the reset?
Not within the same session. ALTER DATABASE and ALTER USER settings
are only examined at session startup.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgres
On Jan 19, 2010, at 2:32 PM, Tom Lane wrote:
> You might try looking at the pg_settings row for the variable to see
> what it says the source is.
That's interesting:
foo# select source from pg_settings where name='log_min_duration_statement';
source
--
database
foo# alter database
Ben Chobot writes:
> On Jan 19, 2010, at 1:44 PM, Devrim GÜNDÜZ wrote:
>> Also, a distro (Gentoo) has 2 conf files which overrides some settings
>> in postgresql.conf -- you may suffer from something similar.
> Not to my knowledge. I'm on debian, and I'm editing the file listed in
> config_file.
On Jan 19, 2010, at 1:44 PM, Devrim GÜNDÜZ wrote:
> What do you get after reloading server and running
>
> psql -c "SHOW log_min_duration_statement"
The same value that was there before the reload.
> Maybe there are more than one log_min_duration_statement in
> postgresql.conf?
Nope. (Or, mor
On 1/19/2010 3:39 PM, Andy Colson wrote:
On 1/19/2010 3:23 PM, Kynn Jones wrote:
I have a Perl CGI script (using DBD::Pg) that interfaces with a
server-side Pg database. I'm looking for general
guidelines/tools/strategies that will help me guard against SQL
injection attacks.
Any pointers/sugge
On Tue, 2010-01-19 at 13:27 -0800, Ben Chobot wrote:
>
> I'm having difficulties with one of my 8.1.19 installs.
> log_min_duration_statement is currently set to 500. I would like to
> change it to 0. Changing it in the config file and sending a HUP to
> the postmaster has no effect.
What do you
On 1/19/2010 3:23 PM, Kynn Jones wrote:
I have a Perl CGI script (using DBD::Pg) that interfaces with a
server-side Pg database. I'm looking for general
guidelines/tools/strategies that will help me guard against SQL
injection attacks.
Any pointers/suggestions would be much appreciated.
~K
I'm having difficulties with one of my 8.1.19 installs.
log_min_duration_statement is currently set to 500. I would like to change it
to 0. Changing it in the config file and sending a HUP to the postmaster has no
effect. (Changing other values in the same works correctly.) I can set
log_min_du
I have a Perl CGI script (using DBD::Pg) that interfaces with a server-side
Pg database. I'm looking for general guidelines/tools/strategies that will
help me guard against SQL injection attacks.
Any pointers/suggestions would be much appreciated.
~K
On Tue, Jan 19, 2010 at 12:06 AM, Johan Nel wrote:
> Terry wrote:
>>
>> Hello,
>>
>> Sorry for the poor subject. Not sure how to describe what I need
>> here. I have an application that logs to a single table in pgsql.
>> In order for me to get into our log management, I need to dump it out
>> t
Dean Rasheed writes:
> 2010/1/19 Philippe Lang :
>> That works just fine, but is there maybe a way of creating a slighly
>> more "generic" index? If I change the ">" with a "<" in the query, index
>> cannot of course be used. According to documentation, answer seems to be
>> "no"...
> You could c
2010/1/19 Philippe Lang :
> That works just fine, but is there maybe a way of creating a slighly
> more "generic" index? If I change the ">" with a "<" in the query, index
> cannot of course be used. According to documentation, answer seems to be
> "no"...
>
You could create an index on the differ
pgsql-general-ow...@postgresql.org wrote:
> On Tue, Jan 19, 2010 at 07:40:00AM +0100, Philippe Lang wrote:
>> I'm trying to figure out how to use an index on an immutable function
>> call in order to speed up queries.
> [..]
>> Unfortunately, Postgreql does not use the index at all.
>
> Yup, an in
pgsql-general-ow...@postgresql.org wrote:
> Hello Philippe,
>
> if you always select data1 > this_is_a_long_transformation(data2) you
> could use the following index:
>
>
> create index long_transformation_index_2 on indexed_table ( ( data1 >
> this_is_a_long_transformation(data2) ) );
>
>
>
Hi,
We use Oracle's external tables to process the files (so no need to look
for the other alternatives you mentioned). We do not have access to the
data providers' network to access the files. That is why we process them
after we receive the files.
If there is a database implementation of 'exte
On 19 Jan 2010, at 12:16, Jayadevan M wrote:
> Yes. We get quite a few files as 'feeds' from external systems. Once the
> files are in our network, we know that no changes will happen to those files.
> We access them using Oracle external tables and process them (the data, after
> some processi
On Tue, Jan 19, 2010 at 07:40:00AM +0100, Philippe Lang wrote:
> I'm trying to figure out how to use an index on an immutable function
> call in order to speed up queries.
[..]
> Unfortunately, Postgreql does not use the index at all.
Yup, an index isn't going to be very useful in what you're doin
On Tue, Jan 19, 2010 at 11:57 AM, Alban Hertroys
> Strange. I noticed that the number of records you get from each method
> differs somewhat, are you recreating the database each time?
>
> With the combined index, or just an index on each column; if you disable
> seqscans (set enable_seqscan to f
Yes. We get quite a few files as 'feeds' from external systems. Once the
files are in our network, we know that no changes will happen to those
files. We access them using Oracle external tables and process them (the
data, after some processing, end up in other real tables). If external
tables
On Tue, Jan 19, 2010 at 4:41 AM, Craig Ringer
wrote:
> How can that work without a transactional file system, though? If the
> external process writes to the file while you're half-way through reading
> it, what's the database to do? In general, how do external tables cope with
> the fact that the
> I have tried with a combined index:
>
> create index long_transformation_index on indexed_table (data1,
> this_is_a_long_transformation(data2));
>
> Unfortunately, it does not work:
>
> ---
> Seq Scan on indexed_table (cost=0.00..26791.00 rows=3 width=12)
> (ac
Hello Philippe,
if you always select data1 > this_is_a_long_transformation(data2) you could
use the following index:
create index long_transformation_index_2 on indexed_table ( ( data1 >
this_is_a_long_transformation(data2) ) );
Index Scan using long_transformation_index_2 on indexed_table
(
pgsql-general-ow...@postgresql.org wrote:
> In response to Philippe Lang :
>>
>> My goal is to make query...
>>
>> select * from indexed_table WHERE data1 >
>> this_is_a_long_transformation(data2);
>>
>> ... as fast as
>>
>> select * from indexed_table WHERE data1 > data2;
>>
>> ... with t
pgsql-general-ow...@postgresql.org wrote:
> In response to Philippe Lang :
>>
>> My goal is to make query...
>>
>> select * from indexed_table WHERE data1 >
>> this_is_a_long_transformation(data2);
>>
>> ... as fast as
>>
>> select * from indexed_table WHERE data1 > data2;
>>
>> ... with t
Alban Hertroys wrote:
>> Hi,
>>
>> It does!
>>
>> With your index alone:
>>
>> ---
>> Bitmap Heap Scan on indexed_table (cost=815.17..10106.08 rows=3
>> width=12) (actual time=7.796..236.722 rows=50116 loops=1) Recheck
>> Cond: (data1 > this_is_a_long_transformation
In response to Philippe Lang :
>
> My goal is to make query...
>
> select * from indexed_table WHERE data1 >
> this_is_a_long_transformation(data2);
>
> ... as fast as
>
> select * from indexed_table WHERE data1 > data2;
>
> ... with the help of the index "long_transformation_index".
>
A
> Hi,
>
> It does!
>
> With your index alone:
>
> ---
> Bitmap Heap Scan on indexed_table (cost=815.17..10106.08 rows=3
> width=12) (actual time=7.796..236.722 rows=50116 loops=1)
> Recheck Cond: (data1 > this_is_a_long_transformation(data2))
> -> Bitmap Index Sca
On 19 Jan 2010, at 10:38, Philippe Lang wrote:
>> What I notice off-hand is that you don't appear to have an index on
>> data1, so Postgres doesn't know for which rows that is >
>> some_immutable_function(data2).
>
> I tried adding an index on data1:
>
> create index long_transformation1_index
pgsql-general-ow...@postgresql.org wrote:
> On Tue, Jan 19, 2010 at 10:22 AM, Alban Hertroys
> wrote:
>>
>> On 19 Jan 2010, at 8:26, A. Kretschmer wrote:
>>> The planner doesn't know the actual parameter for the function, so
>>> he picked out the wrong plan. You can force the planner to
>>> re-p
pgsql-general-ow...@postgresql.org wrote:
> Did you analyse the table?
Hi,
Yes, I did.
> Can you show us an explain analyse?
Here is the explain with my initial index:
create index long_transformation2_index on indexed_table
(this_is_a_long_transformation(data2));
---
In response to Alban Hertroys :
>
> On 19 Jan 2010, at 8:26, A. Kretschmer wrote:
> > The planner doesn't know the actual parameter for the function, so he
> > picked out the wrong plan. You can force the planner to re-planning with
> > a dynamical statement within the function and EXECUTE that.
>
On Tue, Jan 19, 2010 at 10:22 AM, Alban Hertroys
wrote:
>
> On 19 Jan 2010, at 8:26, A. Kretschmer wrote:
>> The planner doesn't know the actual parameter for the function, so he
>> picked out the wrong plan. You can force the planner to re-planning with
>> a dynamical statement within the functio
On 19 Jan 2010, at 8:26, A. Kretschmer wrote:
> The planner doesn't know the actual parameter for the function, so he
> picked out the wrong plan. You can force the planner to re-planning with
> a dynamical statement within the function and EXECUTE that.
There are no queries in his function, I t
On 19 Jan 2010, at 7:40, Philippe Lang wrote:
> Hi,
>
> I'm trying to figure out how to use an index on an immutable function
> call in order to speed up queries.
>
> I came up with this small test:
>
>
> --create database foo;
>
> --drop table indexed_
Ivan Sergio Borgonovo writes:
> I'd appreciate any pointer that will quickly put me on the right
> track.
I'd guess you begin here:
http://wiki.postgresql.org/wiki/Developer_FAQ
--
dim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription
43 matches
Mail list logo
