On 21/10/10 05:22, Joshua J. Kugler wrote:
> On Wednesday 20 October 2010, John R Pierce elucidated thus:
>> On 10/20/10 3:46 AM, Georgi Ivanov wrote:
>>> Hi,
>>> Is there any specific reason one should/should not run postgre on
>>> virtual machine for production ?
>>> Is there any special tuning f
Tim Uckun, 21.10.2010 07:05:
No, it isn't. This is a three-way join between consolidated_urls, cu,
and tu --- the fact that cu is the same underlying table as
cu is an alias for consolidated_urls. tu is an alias for trending_urls.
There are only two tables in the query.
Yes, but consolidate
Hi,
I am using Slony-I 2.0.4 with PostgreSQL 8.4 in windows XP. I have
replicated the data from a master (say A ) to a slave machine(say B).
And when the Master machine A fails, the slave machine B, acts as Master
machine and the node corresponding to master is dropped using
DROP
On Thu, Oct 21, 2010 at 12:47 AM, Tom Lane wrote:
> Josh Kupershmidt writes:
>> pg_temp is being implicitly included in the default search path when
>> looking for tables, but not for functions. Is there a reason for this
>> difference?
>
> Yes. They used to be the same, but awhile back we decid
One more follow up.
Did a vacuum full on both tables and a re-index on both tables.
Changed the wal_buffers to 16MB (increased the kernel param as a
result) as per
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
I also turned off fsync but still no joy.
--
Sent via pgsql-general
"Neil D'Souza" writes:
> Thank you for the quick reply. The example I constructed was
> specifically for this post. I modified the function as below and it
> works fine now. It would be great if the point you mentioned was a
> note in the PGSQL Documentation (or did I miss it).
As of 9.0, plpgsql
>
> No, it isn't. This is a three-way join between consolidated_urls, cu,
> and tu --- the fact that cu is the same underlying table as
cu is an alias for consolidated_urls. tu is an alias for trending_urls.
There are only two tables in the query.
> consolidated_urls doesn't change that. And t
Hello
2010/10/21 Neil D'Souza :
>>
>> You have same plpgsql identifiers as sql identifiers, and because
>> plpgsql identifiers has higher priority, your query is broken. For
>> simple functions like this don't use a plpgsql language - use sql
>> language instead.
>>
>
> Thank you for the quick rep
>
> You have same plpgsql identifiers as sql identifiers, and because
> plpgsql identifiers has higher priority, your query is broken. For
> simple functions like this don't use a plpgsql language - use sql
> language instead.
>
Thank you for the quick reply. The example I constructed was
specific
Josh Kupershmidt writes:
> pg_temp is being implicitly included in the default search path when
> looking for tables, but not for functions. Is there a reason for this
> difference?
Yes. They used to be the same, but awhile back we decided it was a
security hole to look for functions or operator
On Wed, Oct 20, 2010 at 4:53 PM, Rich Shepard wrote:
> For reasons I do not understand, the Slackware start-up file for postgres
> (/etc/rc.d/rc.postgresql) fails to work properly after I reboot the system.
> (Reboots normally occur only after a kernel upgrade or with a hardware
> failure that cr
On Wed, Oct 20, 2010 at 10:31 PM, dan.m.harris
wrote:
> LOG: entering standby mode
> WARNING: WAL was generated with wal_level=minimal, data may be missing
> HINT: This happens if you temporarily set wal_level=minimal without taking a
> new base backup.
Did you set wal_level to archive or hot_sta
Josh Kupershmidt wrote:
On Wed, Oct 20, 2010 at 6:22 PM, David Kerr wrote:
I know I've seen posts on how to do this, but i can't seem to find them.
I've got a data set
A, B
A, C
A, D
[...]
and so on
and i'd like to be able to wite a query that would result in
1,A,B
2,A,C
3,A,D
[...]
PG ve
Tim Uckun writes:
> Here is the actual query.
> update consolidated_urls
> set screenshot_file_name = tu.screenshot_file_name,
>screenshot_content_type = tu.screenshot_content_type,
>screenshot_file_size = tu.screenshot_file_size,
>screenshot_status = tu.screenshot_status
> from co
Hello
2010/10/21 Neil D'Souza :
> consider the following sql statements:
>
> create table food(
> food_code serial unique,
> food_category varchar(20),
> food_name varchar(20)
> );
>
> insert into food (food_category, food_name) values ('fruit', 'tomato');
> insert into food (foo
On 10/20/2010 6:53 PM, Rich Shepard wrote:
For reasons I do not understand, the Slackware start-up file for postgres
(/etc/rc.d/rc.postgresql) fails to work properly after I reboot the system.
(Reboots normally occur only after a kernel upgrade or with a hardware
failure that crashes the system
consider the following sql statements:
create table food(
food_code serial unique,
food_category varchar(20),
food_name varchar(20)
);
insert into food (food_category, food_name) values ('fruit', 'tomato');
insert into food (food_category, food_name) values ('fruit', 'banana');
in
On Thu, Oct 21, 2010 at 5:05 PM, Tom Lane wrote:
> Tim Uckun writes:
>> I have a very simple update query.
>
>> update cu
>> set screenshot_file_name = tu.screenshot_file_name,
>> screenshot_content_type = tu.screenshot_content_type,
>> screenshot_file_size = tu.screenshot_file_size,
>>
On 21 October 2010 16:50, Tom Lane wrote:
> could be reduced to just:
>
> else
> su postgres -c 'postgres -D /var/lib/pgsql/data &'
> exit 0
> fi
> I'm not sure about your comment that manual start attempts fail with
>
Tim Uckun writes:
> I have a very simple update query.
> update cu
> set screenshot_file_name = tu.screenshot_file_name,
> screenshot_content_type = tu.screenshot_content_type,
> screenshot_file_size = tu.screenshot_file_size,
> screenshot_status = tu.screenshot_status
> from cu
>
To follow up...
I did a full vacuum analyze on both tables and re-ran the query. Same
story. I ended the query after eight minutes.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Rich Shepard writes:
>The entire script is attached. It's only 2588 bytes.
Personally, I'd drop all the machinations with checking the pidfile or
removing old socket files. The postmaster is fully capable of doing
those things for itself, and is much less likely to do them mistakenly
than th
>
> 1) Increase checkpoint_segments (>64, increases beyond that can be helpful
> but they eventually level out)
Changed it back to 64 from 256
> 2) Increase shared_buffers (~25% of RAM is normal)
Changed it to one gig (25% of my RAM) obviously this involved changing
the shmmax and shmall setting
Hi all,
I notice slightly different handling of the implicit search_path for
temporary tables and temporary functions. Consider:
(with a default search path):
# SHOW search_path;
search_path
"$user",public
(1 row)
BEGIN;
CREATE TABLE pg_temp.bar();
CREATE FUNCTION pg_t
Tim Uckun wrote:
BTW I have read many of those links and have adjusted some values but
honestly there are so many buttons to push and knobs to dial that it's
hard to know what will fix what.
Generally update/delete tuning goes like this:
1) Increase checkpoint_segments (>64, increases beyon
On Thu, Oct 21, 2010 at 3:37 PM, Gary Chambers wrote:
>> Where is the FAST button for postgres updates? What parameter do I
>> have to set in order to update 6000 records in under an hour?
>
> Which version of Postgres?
8.4
>Have you investigated more than just two
> performance tuning paramet
On 10/20/2010 05:43 PM, DM wrote:
> Composite Index question:
>
> I have composite index on 3 columns on a table, by mistake the composite
> index was created twice on the table.
>
> Will there any performance issues on this table because of the 2 same
> composite indexes?
>
> Thanks
> Deepak
> Where is the FAST button for postgres updates? What parameter do I
> have to set in order to update 6000 records in under an hour?
Which version of Postgres? Have you investigated more than just two
performance tuning parameters? Does your MS Access version of the
query run any faster?
http:/
On Wed, Oct 20, 2010 at 6:22 PM, David Kerr wrote:
> I know I've seen posts on how to do this, but i can't seem to find them.
>
> I've got a data set
>
> A, B
> A, C
> A, D
> [...]
>
> and so on
>
> and i'd like to be able to wite a query that would result in
>
> 1,A,B
> 2,A,C
> 3,A,D
> [...]
>
>
Gabi Julien writes:
> In my case, I do not know how big my cache will be.
That makes it awfully hard to use shared memory.
> If shared memory turns out too difficult to use, I could create
> separate caches for each postgresql processes.
That's what I'd recommend. A big advantage of private ca
DM writes:
> I know there is no benfit of having duplicate indexes.
> Inorder for me to make change on production it requires lot of approvals and
> stuff.
> I wnat to know if there is any major performance drawback for having
> duplicate composite index,
Of course there is: it doubles the index
I have a very simple update query.
update cu
set screenshot_file_name = tu.screenshot_file_name,
screenshot_content_type = tu.screenshot_content_type,
screenshot_file_size = tu.screenshot_file_size,
screenshot_status = tu.screenshot_status
from cu
inner join tu on tu.cu_id = cu.id
Question on Index scan:
--->
test=# \d test_seqindex1
Table "public.test_seqindex1"
Column | Type | Modifiers
+---+---
sid| character varying(13) | not null
nam
Sorry, I cannot prove or disprove any penalty. I don't currently have
access to any postgres db of any serious magnitude.
On 10/20/2010 06:23 PM, DM wrote:
> I know there is no benfit of having duplicate indexes.
> Inorder for me to make change on production it requires lot of approvals
> and stu
create temp table dup_test (nm1 varchar(2),nm2 varchar(3));
insert into dup_test values ('A','A'),('A','B'),('A','C'),('B','A'),('B',
'B'),('B','C');
CREATE SEQUENCE
dup_test_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
alter table dup_test add column dup_id
I know there is no benfit of having duplicate indexes.
Inorder for me to make change on production it requires lot of approvals and
stuff.
I wnat to know if there is any major performance drawback for having
duplicate composite index, so that i can push hard for the change. Let me
know.
thanks fo
Are you sure you cache needs to grow endlessly? Otherwise you could use
RequestAddinShmemSpace and manage you're map within that space, perhaps
"overwriting" chunks on an LRU basis or a rollover. i.e. Grab it all and
do your own management within that single block of shmem.
Caches are best for thi
If you can think of one benefit from having the redundant index then by
all means keep it. It certainly eludes me. Seems to me, removing an
un-necessary index on a huge table can only be a good thing.
On 10/20/2010 06:02 PM, DM wrote:
> Its a huge table in production, i dont want to take any ris
Hi,
Here is my problem: I have a postgresql C function that looks like this:
Datum filter(PG_FUNCTION_ARGS);
It takes identifiers and queries a bunch of tables and ends up returning true
or false. So far nothing difficult except that we want better performance. The
function was already optimiz
Its a huge table in production, i dont want to take any risk.
I can simulate and test this but i was to checking to see If any one knows
off hand about this.
I can simulate it but
On Wed, Oct 20, 2010 at 4:57 PM, Rob Sargent wrote:
> Hm. Run some queries; drop the second version of the index
Hm. Run some queries; drop the second version of the index definition;
re-run the same queries; report to the group. The redundant index isn't
helping, that much is certain.
On 10/20/2010 05:43 PM, DM wrote:
> Composite Index question:
>
> I have composite index on 3 columns on a table, by mista
Composite Index question:
I have composite index on 3 columns on a table, by mistake the composite
index was created twice on the table.
Will there any performance issues on this table because of the 2 same
composite indexes?
Thanks
Deepak
On Thu, 21 Oct 2010, Andrej wrote:
Please do - provide the section, I mean.
Andrej,
The entire script is attached. It's only 2588 bytes.
Also, when there is no postmaster.pid or .s.PGSQL.5432 (and its lock file)
are these recreated automagically when postgres is properly loaded, or do I
On 20 October 2010 23:52, Dennis Gearon wrote:
> Regarding the previously discussed triggers on tables to keep track of count:
> http://www.varlena.com/GeneralBits/120.php
> http://www.varlena.com/GeneralBits/49.php
>
> CREATE OR REPLACE FUNCTION count_rows()
> RETURNS TRIGGER AS
> '
> BEGIN
>
A.M. wrote:
>
> On Oct 7, 2010, at 5:34 AM, Devrim G?ND?Z wrote:
>
> > On Thu, 2010-10-07 at 12:23 +0300, Peter Eisentraut wrote:
> >> This is really something that psycopg2 should work out for you. I
> >> suggest you take up a discussion on this on their mailing list.
> >
> > ...which is down o
On 21 October 2010 11:53, Rich Shepard wrote:
> If someone would be kind enough to point out what I'm doing incorrectly
> (e.g., removing /tmp/.s.PGSQL.5432 and postmaster.pid when the startup
> process complains they're not right) I'll save this information for the next
> time. I can also provid
Regarding the previously discussed triggers on tables to keep track of count:
http://www.varlena.com/GeneralBits/120.php
http://www.varlena.com/GeneralBits/49.php
CREATE OR REPLACE FUNCTION count_rows()
RETURNS TRIGGER AS
'
BEGIN
IF TG_OP = ''INSERT'' THEN
UPDATE rowcount
On Wed, Oct 20, 2010 at 03:47:19PM -0700, DM wrote:
- select generate_series(1,(select count(*) from tax)), country from tax;
-
- you should use braces around the sub select.
-
- Thanks
- Deepak
Table "public.test"
Column | Type | Modifiers
+--+
While I'm re-learning how to properly start postgres after a reboot, I'd
like recommendations on upgrading my current 8.3.3 to a newer version. Since
I'm the only one currently using the system (but I'll be using CMS Made
Simple for my revised web site and that works only with the 8.x releases),
For reasons I do not understand, the Slackware start-up file for postgres
(/etc/rc.d/rc.postgresql) fails to work properly after I reboot the system.
(Reboots normally occur only after a kernel upgrade or with a hardware
failure that crashes the system.)
Trying to restart the system manually
On Wed, Oct 20, 2010 at 03:47:19PM -0700, DM wrote:
- select generate_series(1,(select count(*) from tax)), country from tax;
-
- you should use braces around the sub select.
-
- Thanks
- Deepak
-
Ah, great, thanks!
Dave
- On Wed, Oct 20, 2010 at 3:30 PM, David Kerr wrote:
-
- > On Wed, O
select generate_series(1,(select count(*) from tax)), country from tax;
you should use braces around the sub select.
Thanks
Deepak
On Wed, Oct 20, 2010 at 3:30 PM, David Kerr wrote:
> On Wed, Oct 20, 2010 at 11:28:18PM +0100, Raymond O'Donnell wrote:
> - On 20/10/2010 23:22, David Kerr wrote:
On Wed, Oct 20, 2010 at 11:28:18PM +0100, Raymond O'Donnell wrote:
- On 20/10/2010 23:22, David Kerr wrote:
- >I know I've seen posts on how to do this, but i can't seem to find them.
- >
- >I've got a data set
- >
- >A, B
- >A, C
- >A, D
- >[...]
- >
- >and so on
- >
- >and i'd like to be able to
On 20/10/2010 23:22, David Kerr wrote:
I know I've seen posts on how to do this, but i can't seem to find them.
I've got a data set
A, B
A, C
A, D
[...]
and so on
and i'd like to be able to wite a query that would result in
1,A,B
2,A,C
3,A,D
[...]
PG version is 8.3.
Any ideas?
You probab
I know I've seen posts on how to do this, but i can't seem to find them.
I've got a data set
A, B
A, C
A, D
[...]
and so on
and i'd like to be able to wite a query that would result in
1,A,B
2,A,C
3,A,D
[...]
PG version is 8.3.
Any ideas?
Thanks
Dave
--
Sent via pgsql-general mailing lis
On Wednesday 20 October 2010, John R Pierce elucidated thus:
> On 10/20/10 3:46 AM, Georgi Ivanov wrote:
> > Hi,
> > Is there any specific reason one should/should not run postgre on
> > virtual machine for production ?
> > Is there any special tuning for virtual environment ?
> > Client of mine is
Andrus Moor wrote:
> > Either (1) get a non-lobotomized SSL library
> I'm using latest official Postgres 9.0 server and pgAdmin client.
> Does one of them contain bug in SSL??
Uh, we don't ship SSL in the server. We ship code that _uses_ ssl, so I
would look at your operating system to see what v
On 10/20/10 3:46 AM, Georgi Ivanov wrote:
Hi,
Is there any specific reason one should/should not run postgre on
virtual machine for production ?
Is there any special tuning for virtual environment ?
Client of mine is running 8.4 on virtual machine (VMWare ) so I'm
wandering if there is anythi
On Wed, 2010-10-20 at 16:10 -0400, Michael Glaesemann wrote:
> Upgrade if you can. Otherwise you can do two updates when rearranging
> paragraphs. The first updates them to an "invalid" range which isn't used in
> "correct" data and so won't conflict with other values, and the second moves
> the
On Oct 20, 2010, at 15:58 , Jamie Kahgee wrote:
> I have a table of paragraphs for pages that are in a specific order (1st,
> 2nd, 3rd, etc...).
> I tried using a unique constraint on the page/pos columns, but was running
> into constraint errors when I did an update to move positions - in a
>
I have a table of paragraphs for pages that are in a specific order (1st,
2nd, 3rd, etc...).
demo=# \d paragraphs
Table "toolbox.paragraphs"
Column| Type |Modifiers
-+-+---
Gotcha.
Apologies for the digression, off your exact topic but consistent with the
subject :-)
I'm interested in both, PL/R & representational graphics from an analytical
perspective, doing more than just retrieving raw or accumulated data with SQL.
& also from the (mathemetical) graphic persp
Thom Brown wrote:
> Hi all,
>
> I might not be understanding this correctly, but does Postgres, when
> VACUUM ANALYZE-ing a table, perform separate scans for each index?
> And if so, is this necessary? Can't it update indexes parallel? This
> would be particularly useful when rebuilding all inde
On 20/10/2010 03:01, JY Wang wrote:
Hi
Please count me out of the list of your subscription.
Hi there,
This is a mailing list, so you unsubscribe yourself - instructions are
at the bottom of every email sent from the list server.
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie
On Wed, Oct 20, 2010 at 9:32 AM, Alexander B wrote:
> I use PostGre 8.3 on Ubuntu.
>
> I realized last Friday that my /opt hard disk was full. The disk was full
> about 2-4 hours of moderate activity. The disk ran out of space for reasons
> outside of postgres
> I'm really at a loss of what to do
2010/10/20 Merlin Moncure :
> In recent versions of postgres (I think 8.4+?) you can add columns to
> the view via create/replace (not drop of course). This greatly
> reduces the practical annoyances of dropping view dependencies, at
> least for me...
Ok, We're still on 8.3 here, and management re
On Wed, Oct 20, 2010 at 4:50 PM, Tom Lane wrote:
> =?UTF-8?Q?Viktor_Bojovi=C4=87?= writes:
> > while creating an index on billion records table i have canceled creation
> > because index took me ~70GB of space.
> > When I looked into disk space i saw that space is still occupied , and
> the
> >
=?UTF-8?Q?Viktor_Bojovi=C4=87?= writes:
> while creating an index on billion records table i have canceled creation
> because index took me ~70GB of space.
> When I looked into disk space i saw that space is still occupied , and the
> index doesn't exist.
hmm ... was your version of "cancel" spel
Hi,
while creating an index on billion records table i have canceled creation
because index took me ~70GB of space.
When I looked into disk space i saw that space is still occupied , and the
index doesn't exist.
I have started full vacuuming but it still runs after three days, so i
wanted to ask i
2010/10/19 Grzegorz Jaśkiewicz :
> On Tue, Oct 19, 2010 at 3:12 PM, Tom Lane wrote:
>> Ravi Katkar writes:
>>> Is there any feature to drop the view with out cascading the dependents.
>>
>> No. But why don't you use CREATE OR REPLACE VIEW?
>>
> only caveat is, it won't work if he adds/removes an
Hi all
I'm trying to stop the service that controls a warm standby server, but
issuing a 'net stop '
command fails to stop the service, however the service appears as
stopped on the Services Snap-In.
Checking the process tree, I can see that only the pg_ctl.exe process
was removed
from the roo
2010/10/20 Alexander B :
> I use PostGre 8.3 on Ubuntu.
>
> I realized last Friday that my /opt hard disk was full. The disk was full
> about 2-4 hours of moderate activity. The disk ran out of space for reasons
> outside of postgres
> I'm really at a loss of what to do. Does anyone have suggesti
One more note. I added extra disk space, so I cleared that part up.
/dev/sdg1103210940 5766776 92201356 6% /opt
From: Alexander B
To: pgsql-general@postgresql.org
Sent: Wed, October 20, 2010 8:32:22 AM
Subject: [GENERAL] recovery from out of
I use PostGre 8.3 on Ubuntu.
I realized last Friday that my /opt hard disk was full. The disk was full about
2-4 hours of moderate activity. The disk ran out of space for reasons outside
of postgres
I'm really at a loss of what to do. Does anyone have suggestions for what my
next step shoul
Hi all,
I'm having a problem getting streaming replication to work between two
servers running Solaris 10 (both x86 platforms).
Both servers have PG 9.0 installed at /postgres/postgres/9.0_pgdg/, with a
database initialised at /postgres/postgres/9.0_pgdg/data/.
I have followed the instructions
2010/10/20 Georgi Ivanov :
> Hi,
> Is there any specific reason one should/should not run postgre on virtual
> machine for production ?
> Is there any special tuning for virtual environment ?
> Client of mine is running 8.4 on virtual machine (VMWare ) so I'm wandering
> if there is anything speci
Hi,
Is there any specific reason one should/should not run postgre on virtual
machine for production ?
Is there any special tuning for virtual environment ?
Client of mine is running 8.4 on virtual machine (VMWare ) so I'm wandering
if there is anything special I can tune to speed up a bit.
Also i
Start with it http://www.postgresql.org/docs/8.4/interactive/admin.html
On 20 October 2010 11:14, sameer malve wrote:
> Hi sir,
>
> Can you please guide me for installation of postgres server-8.4.
>
>
> Thanks & Regards,
> Sameer M. Malve
>
--
Sergey Konoplev
Blog: http://gray-hemp.blogsp
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of sameer malve
Sent: Wednesday, October 20, 2010 12:14 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Installation of postgres server-8.4
Hi sir,
Can you please guide me for installation o
Hi sir,
Can you please guide me for installation of postgres server-8.4.
Thanks & Regards,
Sameer M. Malve
80 matches
Mail list logo
