On 08/06/2012 01:38 PM, Rajiv Kasera wrote:
Hi,
I am planning a Postgres migration from 8.4 to 9.1 to be able to
leverage the replication features available in the 9.1 version. I
would like to understand the following things in this regard:
1.Any good documentation which should help in this
On 08/03/2012 05:14 PM, robertha...@o2.pl wrote:
> It is read-only table so every integer column have an index.
First tip: Define the table without the indexes. INSERT your data, and
only after it is inserted create your indexes.
Similarly, if you're making huge changes to the table you shoul
Hi,
I am planning a Postgres migration from 8.4 to 9.1 to be able to leverage
the replication features available in the 9.1 version. I would like to
understand the following things in this regard:
1. Any good documentation which should help in this upgrade.
2. To be able to rep
Hello
Let say we have a table with 6 million records. There are 16 integer columns
and few text column. It is read-only table so every integer column have an
index. Every record is around 50-60 bytes.
The table name is "Item"
The server is: 12 GB RAM, 1,5 TB SATA, 4 CORES. All server for postgres
On Tue, Aug 7, 2012 at 5:07 PM, Stefan Keller wrote:
> Hi Craig
>
> Clever proposal!
> I slightly tried to adapt it to the hstore involved.
> Now I'm having a weird problem that PG says that "relation 'p' does not
> exist".
> Why does PG recognize table b in the subquery but not table p?
> Any id
On Tue, Aug 7, 2012 at 5:29 PM, Craig Ringer wrote:
> On 08/08/2012 04:15 AM, Merlin Moncure wrote:
>>
>> IF current_user = 'bulk_writer' THEN
>>return new;
>> END IF;
>>
>
> ... or re-create the trigger with a `WHEN` clause (only available in newer
> Pg versions, see CREATE TRIGGER) that exc
On 08/08/2012 04:15 AM, Merlin Moncure wrote:
IF current_user = 'bulk_writer' THEN
return new;
END IF;
... or re-create the trigger with a `WHEN` clause (only available in
newer Pg versions, see CREATE TRIGGER) that excludes the migrated
customer ID. You'd have to do it in a new tx to avoid
Hi Craig
Clever proposal!
I slightly tried to adapt it to the hstore involved.
Now I'm having a weird problem that PG says that "relation 'p' does not exist".
Why does PG recognize table b in the subquery but not table p?
Any ideas?
-- Stefan
SELECT b.way AS building_geometry
FROM
(SELECT way
On Tue, Aug 7, 2012 at 3:22 PM, Jeff Janes wrote:
> On Tue, Aug 7, 2012 at 2:39 PM, Craig James wrote:
>> On Tue, Aug 7, 2012 at 1:45 PM, Jeff Janes wrote:
>>> On Tue, Aug 7, 2012 at 1:15 PM, Merlin Moncure wrote:
IF current_user = 'bulk_writer' THEN
return new;
END IF;
>>
On Tue, Aug 7, 2012 at 2:39 PM, Craig James wrote:
> On Tue, Aug 7, 2012 at 1:45 PM, Jeff Janes wrote:
>> On Tue, Aug 7, 2012 at 1:15 PM, Merlin Moncure wrote:
>>>
>>> IF current_user = 'bulk_writer' THEN
>>> return new;
>>> END IF;
>>>
>>
>> I don't know Craig's case, but often the most expe
On Tue, Aug 7, 2012 at 4:39 PM, Craig James wrote:
> On Tue, Aug 7, 2012 at 1:45 PM, Jeff Janes wrote:
>> On Tue, Aug 7, 2012 at 1:15 PM, Merlin Moncure wrote:
>>> On Tue, Aug 7, 2012 at 1:48 PM, Craig James wrote:
I found this discussion from 2005 that says you can drop and restore a
On Tue, Aug 7, 2012 at 2:39 PM, Craig James wrote:
>
> Obviously this is a very expensive trigger, but one that we can drop
> in a very specific circumstance. But we NEVER want to drop it for
> everyone. It seems like a very reasonable use-case to me.
>
>
Sounds like you should try doing the wo
On Tue, Aug 7, 2012 at 1:45 PM, Jeff Janes wrote:
> On Tue, Aug 7, 2012 at 1:15 PM, Merlin Moncure wrote:
>> On Tue, Aug 7, 2012 at 1:48 PM, Craig James wrote:
>>> I found this discussion from 2005 that says you can drop and restore a
>>> trigger inside a transaction, but that doing so locks the
On Tue, Aug 7, 2012 at 3:45 PM, Jeff Janes wrote:
>> absolutely -- the database needs to guard against other writers to the
>> table doing inserts in the meantime.
>
> But why must it? Why can't other writers simply obey the trigger,
> since its removal has not yet been committed? You could have
On Tue, Aug 7, 2012 at 1:15 PM, Merlin Moncure wrote:
> On Tue, Aug 7, 2012 at 1:48 PM, Craig James wrote:
>> I found this discussion from 2005 that says you can drop and restore a
>> trigger inside a transaction, but that doing so locks the whole table:
>>
>> http://archives.postgresql.org/pgsql
On Tue, Aug 7, 2012 at 2:15 PM, Merlin Moncure wrote:
> On Tue, Aug 7, 2012 at 1:48 PM, Craig James wrote:
>> I found this discussion from 2005 that says you can drop and restore a
>> trigger inside a transaction, but that doing so locks the whole table:
>>
>> http://archives.postgresql.org/pgsql
On Tue, Aug 7, 2012 at 1:48 PM, Craig James wrote:
> I found this discussion from 2005 that says you can drop and restore a
> trigger inside a transaction, but that doing so locks the whole table:
>
> http://archives.postgresql.org/pgsql-general/2005-01/msg01347.php
>> From: Jeff Davis
>>
>> It go
I found this discussion from 2005 that says you can drop and restore a
trigger inside a transaction, but that doing so locks the whole table:
http://archives.postgresql.org/pgsql-general/2005-01/msg01347.php
> From: Jeff Davis
>
> It got me curious enough that I tested it, and apparently droping a
On Tue, Aug 7, 2012 at 9:06 AM, Ioannis Anagnostopoulos
wrote:
> On 07/08/2012 17:00, Jeff Janes wrote:
>>
>> What happens if you set "enable_seqscan=off" and run the query with
>> the very large list? (This is an experiment, not a recommendation for
>> production use)
>>
>>
>> Cheers,
>>
>> Jeff
On 07/08/2012 17:00, Jeff Janes wrote:
On Mon, Aug 6, 2012 at 8:08 AM, Ioannis Anagnostopoulos
wrote:
Hi, my query is very simple:
select
msg_id,
msg_type,
ship_pos_messages.pos_georef1,
ship_pos_messages.pos_georef2,
ship_pos_me
On Mon, Aug 6, 2012 at 8:08 AM, Ioannis Anagnostopoulos
wrote:
> Hi, my query is very simple:
>
> select
> msg_id,
> msg_type,
> ship_pos_messages.pos_georef1,
> ship_pos_messages.pos_georef2,
> ship_pos_messages.pos_georef3,
>
On Tue, Aug 7, 2012 at 5:01 AM, Stefan Keller wrote:
> Hi
>
> I have an interesting query to be optimized related to this one [1].
>
> The query definition is: Select all buildings that have more than 1
> pharmacies and more than 1 schools within a radius of 1000m.
>
> The problem is that I think
On 7 Srpen 2012, 14:22, Stefan Keller wrote:
> Your proposal lacks the requirement that it's the same building from
> where pharmacies and schools are reachable.
> But I think about.
I don't know the dataset so I've expected the osm_id to identify the
building - then the intersect should work as A
Your proposal lacks the requirement that it's the same building from
where pharmacies and schools are reachable.
But I think about.
Yours, S.
2012/8/7 Tomas Vondra :
> On 7 Srpen 2012, 14:01, Stefan Keller wrote:
>> Hi
>>
>> I have an interesting query to be optimized related to this one [1].
>>
Offhand I'd have thought that ANALYZE would gather stats on the
date_trunc expression (because it is indexed) and then you should get
something reasonably accurate for a comparison to a constant.
"Reasonably accurate" meaning "not off by two orders of magnitude".
Practically all of your runtime is
On 7 Srpen 2012, 14:01, Stefan Keller wrote:
> Hi
>
> I have an interesting query to be optimized related to this one [1].
>
> The query definition is: Select all buildings that have more than 1
> pharmacies and more than 1 schools within a radius of 1000m.
>
> The problem is that I think that this
Hi
I have an interesting query to be optimized related to this one [1].
The query definition is: Select all buildings that have more than 1
pharmacies and more than 1 schools within a radius of 1000m.
The problem is that I think that this query is inherently O(n^2). In
fact the solution I propos
27 matches
Mail list logo
