Re: [GENERAL] isn't "insert into where not exists" atomic?

[Alban Hertroys]

On 3 Feb 2011, at 2:17, Mage wrote:

> The trigger looks like:
> 
> create or replace function trf_chat_room_users_insert() returns trigger as $$
> begin
>if NEW.active_at is null then
>insert into chat_room_users (user_id, chat_room_id, active_at) 
> (select NEW.user_id, NEW.chat_room_id, now() where not exists (select 1 from 
> chat_room_users where user_id = NEW.user_id and chat_room_id = 
> NEW.chat_room_id));
>if not found then
>update chat_room_users set active_at = now() where 
> user_id = NEW.user_id and chat_room_id = NEW.chat_room_id;
>end if;
>return null;
>end if;
>return NEW;
> end;
> $$ language plpgsql;


Your trigger is the wrong way around. Insert doesn't set found, but update does.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4d4a559711736475013765!



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] upgrade

[Scott Marlowe]

On Wed, Feb 2, 2011 at 12:08 PM, William Bruton  wrote:
> How do I know which version to upgrade to from 8.1.4?

you should first update to 8.1.latest so you've got all the bug fixes
available.  It's pretty close to painless, and unless the release
notes between 8.1.5 and 8.1.latest say you need to do anything out of
the ordinary, it's just a software update, no admin work to do.

Upgrading to a later version I'd go for 8.2 or 9.0.  8.3 removed a lot
of explicit casts that some (broken really) programs need to work
right.  So going to 8.3 or beyond you're making a commitment to
hunting down such code and fixing it.

9.0 is WORLDS ahead of 8.2 though, and well worth the effort, if
needed, to move to.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] upgrade

[William Bruton]

How do I know which version to upgrade to from 8.1.4?

 

Regards,

 

William Bruton

 

Data Retrieval Corporation

13231 Champion Forest Dr Suite 401

Houston Tx 77069

Tel: 281 444-5398

Fax: 281 444-5397

24 Hrs: 832 752-0074

http://www.spidr.com/

  d...@spidr.com

 

DOWNHOLE RESULTS WITHOUT DOWNHOLE GAUGES!

 

[GENERAL] set theory question

[matty jones]

I am looking for a good book on the math and/or theory behind relational
databases and associated topics..  I am looking some works on set theory,
algebra, or any other books/papers on the mechanics that databases are built
on.  I found one book online,
http://web.cecs.pdx.edu/~maier/TheoryBook/TRD.html, The Theory of Relational
Databases by David Maier and that is what got me interested in the
foundations of database design and theory.

Thanks,

Matt

Re: [GENERAL] Why does my DB size differ between Production and DR? (Postgres 8.4)

[Scott Marlowe]

On Wed, Feb 2, 2011 at 8:49 PM, Peter Geoghegan
 wrote:
> On 2 February 2011 05:41, Scott Marlowe  wrote:
>
>>> I wouldn't increase index fill factor as an optimisation, unless you
>>> had the unusual situation of having very static data in the table.
>>
>> That makes no sense whatsoever.  You decrease fill factor (not
>> increase btw) so there will be some space for future updates.  If he's
>> getting bloat it may well help quite a bit to have a lower than 100%
>> fill factor.
>
> As I said, it depends on the profile of the data. Heavily or randomly
> updated tables will benefit from reducing *index* fillfactor - it will
> reduce index fragmentation. OTOH, indexes for static data can have
> their fillfactors increased to 100% from the default of 90% without
> consequence.
>

Certainly.  I was talking table fill factor at the time, so that's why
I wasn't sure what you meant.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PQfinish blocking on non-existent IP address ...

[Mad]

Hmm ... It would appear that is it actually WSACleanup() that is taking
forever. I Added a WSAStartup() and a WSACleanup(), and it hung for awhile
on WSACleanup() instead of PQfinish() :)

-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Mad
Sent: Wednesday, February 02, 2011 6:03 PM
To: pgsql-general@postgresql.org
Cc: mad...@schif.org
Subject: [GENERAL] PQfinish blocking on non-existent IP address ...

/**

How do I stop PQfinish from blocking?
(try any LAN IP address that doesn't exist on your LAN.)

I compiled it with both VC and MinGW, same result.

(change C:\Program Files (x86) to C:\Program Files for 32bit Windows OS)

Command Line Compile in VC 32bit:
cl x.cpp -I"C:\Program Files (x86)\PostgreSQL\8.4\include" -link "C:\Program
Files (x86)\PostgreSQL\8.4\lib\libpq.lib"

Command Line Compile in MinGW 32bit:
g++ x.cpp -o"x.exe" -I"C:\Program Files (x86)\PostgreSQL\8.4\include"
-L"C:\Program Files (x86)\PostgreSQL\8.4\lib" -llibpq
 
***/

#include 
#include 
#include 

int main(int na,char** sa){
printf("Connecting ...\n");
PGconn* lpcn = PQconnectStart("dbname=postgres
host=192.168.250.60");
printf("Connected\n");
printf("Calling PQfinish\n");
PQfinish(lpcn);
printf("PQfinished\n");
return 0;
};



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Why does my DB size differ between Production and DR? (Postgres 8.4)

[Peter Geoghegan]

On 2 February 2011 05:41, Scott Marlowe  wrote:

>> I wouldn't increase index fill factor as an optimisation, unless you
>> had the unusual situation of having very static data in the table.
>
> That makes no sense whatsoever.  You decrease fill factor (not
> increase btw) so there will be some space for future updates.  If he's
> getting bloat it may well help quite a bit to have a lower than 100%
> fill factor.

As I said, it depends on the profile of the data. Heavily or randomly
updated tables will benefit from reducing *index* fillfactor - it will
reduce index fragmentation. OTOH, indexes for static data can have
their fillfactors increased to 100% from the default of 90% without
consequence.

-- 
Regards,
Peter Geoghegan

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Why does my DB size differ between Production and DR? (Postgres 8.4)

[Scott Marlowe]

On Wed, Feb 2, 2011 at 10:45 AM, Chris Browne  wrote:
> peter.geoghega...@gmail.com (Peter Geoghegan) writes:
>> I'm not sure why you'd advocate CLUSTER as a way to reclaim disk space.
>
> Because it works pretty well; it reorganizes the table on the basis of
> the order indicated by one index, and simultaneously:
>  a) Shortens the table, removing all dead space;
>  b) Regenerates all indices, so they too have no dead space.

It's important at this point to set fill factor before the cluster if
something besides the default 100% makes sense.  any randomly updated
table full of small records will usually benefit from a fill fact even
as high as 95% which is very little "wasted" space for a gain in HOT
updates starting in 8.3.  HOT saved our bacon at work.  They really
lowered the requirements for disk access / index update a LOT.  I wish
I'd have saved the pg_stat_index from 8.1 versus 8.3.   And IO
numbers.  Our load dropped by a power of ten more or less.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Why does my DB size differ between Production and DR? (Postgres 8.4)

[Aleksey Tsalolikhin]

Thank you for the discussion.

I'm on Postgres 8.4, and the hardware between Slony master and slave
is identical,
as is the autovacuum config.

We do have transactions that fail to commit, transactions that roll back.

I'm glad to have some idea of the cause of the difference in table size
between Slony Master and Slave.

If disk usage on the Master goes over 75% before my upgrade money
is approved, I will try Bill Moran's suggestion of doing a practice vacuum
run on the Slave, and then we'll take a maintenance window to VACUUM,
VACUUM FULL, REINDEX; or CLUSTER on the master.

THANK YOU!  This is a super-helpful list.  I really appreciate the positive
energy in the PostgreSQL community.   I'm looking forward to helping out
at the PostgresSQL booth at the So Cal Linux Expo later this month.

Thank you!
Aleksey

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] isn't "insert into where not exists" atomic?

[Mage]

Hello,

I just received an error message:

  PGError: ERROR:  duplicate key value violates unique constraint 
"chu_user_id_chat_room_id"
DETAIL:  Key (user_id, chat_room_id)=(8, 2) already exists.
CONTEXT:  SQL statement "insert into chat_room_users (user_id, chat_room_id, 
active_at) (select NEW.user_id, NEW.chat_room_id, now() where not exists (select 1 from 
chat_room_users where user_id = NEW.user_id and chat_room_id = NEW.chat_room_id))"
PL/pgSQL function "trf_chat_room_users_insert" line 3 at SQL statement
: INSERT INTO "chat_room_users" ("user_id", "chat_room_id", "active_at") VALUES 
(8, 2, NULL)


The important line is:
insert into chat_room_users (user_id, chat_room_id, active_at) (select 
NEW.user_id, NEW.chat_room_id, now() where not exists (select 1 from 
chat_room_users where user_id = NEW.user_id and chat_room_id = 
NEW.chat_room_id))


I always thought this is atomic and can not fail. Was I wrong?

If it isn't then I have to rewrite my triggers. Do I have to use "lock 
table" instead of the above to avoid errors in parallel inserts?


The trigger looks like:

create or replace function trf_chat_room_users_insert() returns trigger 
as $$

begin
if NEW.active_at is null then
insert into chat_room_users (user_id, chat_room_id, 
active_at) (select NEW.user_id, NEW.chat_room_id, now() where not exists 
(select 1 from chat_room_users where user_id = NEW.user_id and 
chat_room_id = NEW.chat_room_id));

if not found then
update chat_room_users set active_at = now() 
where user_id = NEW.user_id and chat_room_id = NEW.chat_room_id;

end if;
return null;
end if;
return NEW;
end;
$$ language plpgsql;

And it meant to be "insert or update".

Mage


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] PQfinish blocking on non-existent IP address ...

[Mad]

/**

How do I stop PQfinish from blocking?
(try any LAN IP address that doesn't exist on your LAN.)

I compiled it with both VC and MinGW, same result.

(change C:\Program Files (x86) to C:\Program Files for 32bit Windows OS)

Command Line Compile in VC 32bit:
cl x.cpp -I"C:\Program Files (x86)\PostgreSQL\8.4\include" -link "C:\Program
Files (x86)\PostgreSQL\8.4\lib\libpq.lib"

Command Line Compile in MinGW 32bit:
g++ x.cpp -o"x.exe" -I"C:\Program Files (x86)\PostgreSQL\8.4\include"
-L"C:\Program Files (x86)\PostgreSQL\8.4\lib" -llibpq
 
***/

#include 
#include 
#include 

int main(int na,char** sa){
printf("Connecting ...\n");
PGconn* lpcn = PQconnectStart("dbname=postgres
host=192.168.250.60");
printf("Connected\n");
printf("Calling PQfinish\n");
PQfinish(lpcn);
printf("PQfinished\n");
return 0;
};



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Why does a normally fast query run so slow when the table is in a partition?

[Steve Crawford]

On 02/02/2011 01:35 PM, Bill Thoen wrote:

Steve Crawford wrote:

On 02/02/2011 12:17 PM, Bill Thoen wrote:
I've got a large  (and growing) database set up as a partitioned 
database

What is the setting of contstraint_exclusion?
http://www.postgresql.org/docs/9.0/static/runtime-config-query.html#GUC-CONSTRAINT-EXCLUSION 



Cheers,
Steve

It's set to 'Partition'

That sounds good. Out of curiosity, what happens if you use an explicit 
cast?:

...where 'co'::char(2)...

I've seen lots of cases where the planner doesn't use indexes when the 
data-type differs sometimes even subtly. Might be the same for 
constraint exclusion.


Cheers,
Steve


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Why does a normally fast query run so slow when the table is in a partition?

[Steve Crawford]

On 02/02/2011 12:17 PM, Bill Thoen wrote:
I've got a large  (and growing) database set up as a partitioned 
database

What is the setting of contstraint_exclusion?
http://www.postgresql.org/docs/9.0/static/runtime-config-query.html#GUC-CONSTRAINT-EXCLUSION

Cheers,
Steve

[GENERAL] effective_io_concurrency

[Yves Weißig]

Hi pgsql-general group,

I was wondering if there is more information about this switch in the
configuration. Does it really work? Where in the source code can I
follow how it works? "sgmgr.c" seems to be an entry point, but where
exactly is it used?

Greets, Yves

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] redirecting query statement and output to a marked up file, using psql

[Bosco Rama]

Wim Bertels wrote:
> 
> --user2
> SET SESSION AUTHORIZATION user2;
> \pset format latex
> \echo ECHO queries
> \o report/test_user2.tex
> \i structure/test_user2.sql
> "
> 
> This doenst seem to work,
> as the ECHO queries output isnt written to the file (test_user2.tex)

Actions are performed as they are encountered so put the \echo *after* the
\o, like this:

   SET SESSION AUTHORIZATION user2;
   \pset format latex
   \o report/test_user2.tex
   \echo ECHO queries
   \i structure/test_user2.sql

HTH,
Bosco.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Database Design Question

[Martijn van Oosterhout]

On Wed, Feb 02, 2011 at 11:44:51AM -0800, John R Pierce wrote:
> On 02/02/11 11:24 AM, Joshua D. Drake wrote:
>> Forget separate databases. Use separate users with schemas.
>
> for canned applications like mediawiki and phpbb?   not sure they  
> support that.
>

If they use different users you can easily do it by setting the default
search path per user.

ALTER USER phpbb SET search_path='phpbbschema';

As long as the apps don't play with the search path themselves it
should be fine.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patriotism is when love of your own people comes first; nationalism,
> when hate for people other than your own comes first. 
>   - Charles de Gaulle


signature.asc
Description: Digital signature

[GENERAL] SCALE: seeking booth attendees

[Richard Broersma]

On February 26th and 27th (Saturday and Sunday), PostgreSQL is assigned an
exhibitor booth.  Between the hours of 10:00 am and 6:00 pm, we need booth
coverage.

The call is going out for booth attendees.  This will be an excellent
opportunity to meet PostgreSQL community leaders as well as community
locals!

If your interested, please reply with an email "off-list."

-- 
Regards,
Richard Broersma Jr.


P.S.

Also if your in town on Friday, be sure to check out PgDay on Friday
February 25th.
https://sites.google.com/site/pgdayla/

Re: [GENERAL] How best to load modules?

[Steve White]

Hi,

Here is the best cludge so far.  To load the module 'tablefunc' from the
contrib/ directory, process the output of the 'pg_config' program with unix
commands.  The 'pg_config' program is often distributed in a package
separate from  postgresql.

\set tablefunc `pg_config|grep SHAREDIR|sed "s/SHAREDIR = 
\(.*\)/\1\/contrib\/tablefunc.sql/g"`
\i :tablefunc

This isn't very robust, but at least it allows me to load and unload stuff
from a single sql script on two different distros.

Cheers!


On 28.01.11, Steve White wrote:
> Hello, all!
> 
> What are best practices regarding the loading of postgresql modules, say
> from the contrib/ directory; specifically, with regard to portability?
> 
> I would like to distribute an SQL script which loads a module, and works
> with as little further fiddling as possible.
> 
> known options
> =
> 
> Within a session, or in a script, one can use 
>   \i 
> But within a script this has the weakness that the file path varies from
> one system distribution to another.
> 
> One can start psql with 
>   psql ... -f 
> but that's a measure taken outside the script, to done either with session,
> or else be done by a further measure such as a shell script.
> 
> Ideally, the location of the default modules directory (or installation
> directory) should be available within a session in some variable or from
> some function call.
> 
> There are some pre-defined variables, listed in a session by
>   show all;
> but I don't see anything like a directory path there.
> Maybe a built-in function returning this directory?  Searched to no avail:
>   http://www.postgresql.org/docs/8.2/interactive/functions.html
> 
> There has been talk about a bigger solution on
>   http://wiki.postgresql.org/wiki/Module_Manager
> but little seems to have happened there in some years.
> 
> An environment variable
>   $libdir, 
> is mentioned
>   http://www.postgresql.org/docs/8.2/static/runtime-config-client.html
> but this seems not to be present within a session.
> It seems to be expanded within the LANGUAGE C environment, for instance in
> tablefunc.sql
>  -
> CREATE OR REPLACE FUNCTION crosstab2(text)
> RETURNS setof tablefunc_crosstab_2
> AS '$libdir/tablefunc','crosstab'
> LANGUAGE C STABLE STRICT;
>  -
> 
> Thanks!
> 
> -- 
> | -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -
> | Steve White +49(331)7499-202
> | E-ScienceZi. 27  Villa Turbulenz 
> | -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -
> | Astrophysikalisches Institut Potsdam (AIP)
> | An der Sternwarte 16, D-14482 Potsdam
> |
> | Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz
> |
> | Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026
> | -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -

-- 
| -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -
| Steve White +49(331)7499-202
| E-ScienceZi. 27  Villa Turbulenz 
| -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -
| Astrophysikalisches Institut Potsdam (AIP)
| An der Sternwarte 16, D-14482 Potsdam
|
| Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz
|
| Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026
| -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Database Design Question

[Chris Browne]

carlos.menn...@gmail.com (Carlos Mennens) writes:
> I was sitting down thinking the other day about when is it good to
> generate a new database or just use an existing one. For example, lets
> say my company name is called 'databasedummy.org' and I have a
> database called 'dbdummy'. Now I need PostgreSQL to manage several
> applications for my company:
>
> - webmail
> - software
> - mediawiki
> - phpbb forum
>
> Now what I've been doing is just creating multiple tables in the
> 'dbdummy' database but each table is owned by different users
> depending on their role. Is this bad? Should I be creating new
> databases for each application above rather than one single company
> database?
>
> Just trying to understand good DBA design practice. This is obviously
> a very general question but any feedback on what good or bad issues
> would come from me dumping all my tables for applications in one
> database or spread out across multiple databases on PostgreSQL.
>
> Thank you!

I think it's likely that these would properly have separate databases,
as...

 - There isn't *that* much data that is likely to be shared between
   these applications, so it probably doesn't add a lot of value to
   force them together.

 - If you integrate the databases together, then any maintenance on "the
   database" represents an outage for *ALL* those systems, whereas if
   they're separate, there's at least the possibility of outages being
   independent.

You'll have to think about the expected kinds of failure cases to
determine in which direction to go.
-- 
(format nil "~S@~S" "cbbrowne" "acm.org")
http://www3.sympatico.ca/cbbrowne/rdbms.html
Make sure your code does nothing gracefully.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Why does a normally fast query run so slow when the table is in a partition?

[Bill Thoen]

I've got a large  (and growing) database set up as a partitioned 
database. The partitions are physically broken out by state plus a 
unique id for each. There's roughly 20 million records in the whole 
thing just now. My question is, why does a simple query supplying both 
parts of the index key work nearly instantly as expected when I submit 
it to the appropriate partition table directly, but the same query when 
sent to the master table takes nearly 3/4 of a minute to return one 
record? Actually, running the queries with 'Explain analyze verbose'  
tells me what it chose, so I know it's slopw because it chose to do a 
sequential scan on the master table but what I'd like to know is why 
does it take so long to go through the master table looking for the 
partition for 'co'? According to the log, if I read it correctly, it 
took nearly 40 seconds just to scan through the 19 partition tables 
before it found the colorado partition. Can soeone tell me ho wot speed 
up that step?


Also (this might be relevant) I accidentally got 15 million records into 
the master table earlier, but those have all been deleted and I've run 
VACUUM ANALYZE on  the master table since then.


The following shows the details and the environment.  I'm using 
PostgreSQL 8.4.5 and running on CentOS 5.5


This is the master table. It has no records or indexes as per the PG manual.
fsa=# \d clu
Table "vfm.clu"
  Column| Type  | Modifiers
-+---+---
ogc_fid | bigint| not null
geom| geometry  |
comments| character(80) |
statecd | character(2)  |
countycd| character(3)  |
tractnbr| character(7)  |
farmnbr | character(7)  |
clunbr  | numeric(7,0)  |
acres   | numeric(8,2)  |
fsa_acres   | numeric(8,2)  |
heltypecd   | character(1)  |
cluclscd| numeric(2,0)  |
cluid   | character(36) |
admnstate   | character(2)  |
admncounty  | character(3)  |
source_disc | character(2)  | not null

This is one of the partition tables. It has the same structure, although 
the key fields are not in the same order as the master table. It is also 
indexed on source_disc + ogc_fid (and spatially as well). Its constraint 
is that only records with 'co' in the source_disk attribute can be added 
or found here.

fsa=# \d clu_co
  Table "vfm.clu_co"
  Column| Type  | Modifiers
-+---+---
geom| geometry  |
comments| character(80) |
statecd | character(2)  |
countycd| character(3)  |
tractnbr| character(7)  |
farmnbr | character(7)  |
clunbr  | numeric(7,0)  |
acres   | numeric(8,2)  |
fsa_acres   | numeric(8,2)  |
heltypecd   | character(1)  |
cluclscd| numeric(2,0)  |
cluid   | character(36) |
admnstate   | character(2)  |
admncounty  | character(3)  |
ogc_fid | bigint| not null
source_disc | character(2)  | not null
Indexes:
   "clu_co_pkey" PRIMARY KEY, btree (source_disc, ogc_fid)
   "clu_co_geom" gist (geom)
Check constraints:
   "cd_id" CHECK (source_disc = 'co'::bpchar)
Inherits: clu


Here's the query that executes quickly in the partition table. Notice 
that it's using the index for a fast lookup.


fsa=# explain analyze verbose select :flds from clu_co where 
source_disc='co' and ogc_fid = 116337;
QUERY 
PLAN  
 
--

---
Index Scan using clu_co_pkey on clu_co  (cost=0.00..8.31 rows=1 
width=48) (actual time=0.079..0.086 rows=

1 loops=1)
  Output: source_disc, ogc_fid, statecd, countycd, tractnbr, farmnbr, 
clunbr, acres

  Index Cond: ((source_disc = 'co'::bpchar) AND (ogc_fid = 116337))
Total runtime: 0.177 ms
(4 rows)


fsa=# select :flds from clu where source_disc='co' and ogc_fid = 116337;
source_disc | ogc_fid | statecd | countycd | tractnbr | farmnbr | 
clunbr |  acres 
-+-+-+--+--+-++-
co  |  116337 | 08  | 043  | 533  | 065 |  
9 | 4677.79

(1 row)



The same query when sent through the master table. Notice it's using a 
sequential scan. But why does this operation take 38 seconds? How do I 
speed that up?


fsa=# explain analyze verbose select :flds from clu where 
source_disc='co' and ogc_fid = 116337;
QUERY PLAN
 
--
Result  (cost=0.00..1098364.31 rows=2 width=52) (actual 
time=38367.332..38367.355 rows=1 loops=1)
  Output: vfm.clu.source_disc, vfm.clu.ogc_fid, vfm.clu.statecd, 
vfm.clu.countycd,

   vfm.clu.tractnbr, vfm.clu.farmnbr, vfm.clu.clunbr, vfm.clu.acres
  ->  Append  (cost=0.00..1098364.31 

Re: [GENERAL] Changing SHMMAX

[Vick Khera]

On Wed, Feb 2, 2011 at 10:31 AM, Torsten Zühlsdorff
 wrote:
>> kernel.shmall = 90
>> kernel.shmmax = 90
>>
>> into /etc/sysctl.conf.  Run "sysctl -p" to activate them.  However,
>> this is a bit distribution-specific.
>
> If you're using FreeBSD you even have to restart the machine.
>

No, you do not.  kern.ipc.shmall and shmmax are run-time tunables in FreeBSD.

The only postgres related settings you need a reboot to adjust are
kern.ipc.semmni and semmns.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Database Design Question

[Gary Chambers]

Forget separate databases. Use separate users with schemas.

for canned applications like mediawiki and phpbb?   not sure they support
that.


Mediawiki does -- I'm doing just that.  It's been liberating learning how
PostgreSQL deals with schemas (and applying that knowledge).

-- Gary Chambers

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Database Design Question

[John R Pierce]

On 02/02/11 11:24 AM, Joshua D. Drake wrote:

Forget separate databases. Use separate users with schemas.


for canned applications like mediawiki and phpbb?   not sure they 
support that.






--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Streaming Rep 101 questions

[Magnus Hagander]

On Wed, Feb 2, 2011 at 16:52, Gauthier, Dave  wrote:
> Hi:
>
>
> I'm going to be experimenting with streaming replication using v9.0.1.  Here
> are a few questions I have at the onset...

You should use 9.0.3


> 1) Is it possible to replicate one database out of many that exist in the DB
> instance?  Or do you have to replicate them all?

No, you must do all.


> 2) Is replication transaction based?  That is to say, does the act of
> replicating a transaction wait until the commit on the server side?

Yes, it's transaction based, in the sense that it's transactionally
safe. No, you don't have to wait until commit on the slave side (which
I assume you mean). THat's sync rep, which is hopefully going to be an
option in 9.1.


> 3) I will be replicating to a DB server 2 timezones away initially, but
> perhaps from the US-> India at some future point.  Is the PG replication
> solution meant to do something like this (given sufficient bandwidth of
> course)?

Should work fine. As long as the connection is reasonably reliable
(doesn't go up and down every minute), a bit of latency shouldn't be a
problem.


> 4) The slave DB instance will also be v9.0.1 on linux, but it wil be a
> virtual machine.  Do you see any problem with that?

None other than potential performance issues if it's too slow.


> 5) Is there a step-by-step "how to" document for this?

http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Database Design Question

[Joshua D. Drake]

On Wed, 2011-02-02 at 11:08 -0800, John R Pierce wrote:
> On 02/02/11 10:32 AM, Carlos Mennens wrote:

> I would create a seperate database for each thing that has nothing to do 
> with the other things.I doubt mediawiki and phpbb will ever share 
> any data, they are totally different applications, each is a self 
> contained world.  ditto your webmail.   the other item there, 
> 'software', well, I have no idea what that means specifically.

Forget separate databases. Use separate users with schemas.

JD

> 
> 

-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Database Design Question

[David Johnston]

The main concern to consider is whether there are any shared relationships
that the different projects all have (e.g., common logon users).  Since you
cannot query across different databases if there is shared information then
a single database would be preferred.  I think the concept you want to
consider further is "Schemas".  You can get the same kind of separation that
you would want with multiple databases with the possibility to have a
"global" schema that holds data common to multiple projects.

Also, I would suggest managing permissions by "group" roles and strictly
assigning "user/logon" roles to those group roles.

If, from an application standpoint, the structure does not matter then
consider the maintenance aspects of such a design.  The advantage of
multiple databases is that you can easily put each database onto its own
machine and individual applications can be brought offline without bringing
down all the applications.  Your admin tool will also have references to
each of the separate databases instead of a single database with multiple
schemas.  If you end up using maintenance functions and/or views they will
probably need to be installed and configured in each database.  At the same
time it becomes easier to look at the maintenance logs when each application
is independent (of course this depends on the tool and how schemas are
handled).

Dave


-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce
Sent: Wednesday, February 02, 2011 2:09 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Database Design Question

On 02/02/11 10:32 AM, Carlos Mennens wrote:
> I was sitting down thinking the other day about when is it good to 
> generate a new database or just use an existing one. For example, lets 
> say my company name is called 'databasedummy.org' and I have a 
> database called 'dbdummy'. Now I need PostgreSQL to manage several 
> applications for my company:
>
> - webmail
> - software
> - mediawiki
> - phpbb forum
>
> Now what I've been doing is just creating multiple tables in the 
> 'dbdummy' database but each table is owned by different users 
> depending on their role. Is this bad? Should I be creating new 
> databases for each application above rather than one single company 
> database?
>
> Just trying to understand good DBA design practice. This is obviously 
> a very general question but any feedback on what good or bad issues 
> would come from me dumping all my tables for applications in one 
> database or spread out across multiple databases on PostgreSQL.

I would create a seperate database for each thing that has nothing to do 
with the other things.I doubt mediawiki and phpbb will ever share 
any data, they are totally different applications, each is a self 
contained world.  ditto your webmail.   the other item there, 
'software', well, I have no idea what that means specifically.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Query plan optimization: sorting vs. partitioning

[Sergey Zaharchenko]

2011/2/2, Tom Lane :

>> I see the database doesn't understand that there are no entries in the
>> main table, so it has to assume the Append data is not ordered. Is
>> there a way to avoid sorting?
>
> No.  In existing releases there is no plan type that can produce
> presorted output from an append relation (ie, an inheritance tree).
> 9.1 will be able to do that, but it wasn't exactly a small fix:
> http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=11cad29c91524aac1d0b61e0ea0357398ab79bf8

OK, I hope I'll be able to come up with a stored procedure to query
the tables directly, then. Thanks!

-- 
DoubleF

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Database Design Question

[John R Pierce]

On 02/02/11 10:32 AM, Carlos Mennens wrote:

I was sitting down thinking the other day about when is it good to
generate a new database or just use an existing one. For example, lets
say my company name is called 'databasedummy.org' and I have a
database called 'dbdummy'. Now I need PostgreSQL to manage several
applications for my company:

- webmail
- software
- mediawiki
- phpbb forum

Now what I've been doing is just creating multiple tables in the
'dbdummy' database but each table is owned by different users
depending on their role. Is this bad? Should I be creating new
databases for each application above rather than one single company
database?

Just trying to understand good DBA design practice. This is obviously
a very general question but any feedback on what good or bad issues
would come from me dumping all my tables for applications in one
database or spread out across multiple databases on PostgreSQL.


I would create a seperate database for each thing that has nothing to do 
with the other things.I doubt mediawiki and phpbb will ever share 
any data, they are totally different applications, each is a self 
contained world.  ditto your webmail.   the other item there, 
'software', well, I have no idea what that means specifically.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Why "copy ... from stdio" does not return immediately when reading invalid data?

[John R Pierce]

On 02/02/11 10:20 AM, Nicolas Grilly wrote:
Is the copy protocol (aka PQputCopyData and PQputCopyEnd) designed to 
send gigabytes of data with just one "copy ... from stdio" query, and 
is there a way to be notified of a potential error before calling 
PQputCopyEnd? Or do I have to send my data in small chunks (for 
example batch of 1 rows), issue a PQputCopyEnd, check for errors, 
and continue with the next chunk?


I would batch the data, maybe 1000 lines or even 100 lines at a time if 
these errors are at all frequent.  put the errored batches in an 
exception list or something so you can sort them out later.




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Database Design Question

[Carlos Mennens]

I was sitting down thinking the other day about when is it good to
generate a new database or just use an existing one. For example, lets
say my company name is called 'databasedummy.org' and I have a
database called 'dbdummy'. Now I need PostgreSQL to manage several
applications for my company:

- webmail
- software
- mediawiki
- phpbb forum

Now what I've been doing is just creating multiple tables in the
'dbdummy' database but each table is owned by different users
depending on their role. Is this bad? Should I be creating new
databases for each application above rather than one single company
database?

Just trying to understand good DBA design practice. This is obviously
a very general question but any feedback on what good or bad issues
would come from me dumping all my tables for applications in one
database or spread out across multiple databases on PostgreSQL.

Thank you!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Why does my DB size differ between Production and DR? (Postgres 8.4)

[Chris Browne]

peter.geoghega...@gmail.com (Peter Geoghegan) writes:
> On 1 February 2011 03:52, Scott Marlowe  wrote:
>> You can reclaim that space by doing a cluster or vacuum full on the
>> subject table.
>
> Yes, but this is a fairly bad idea, particularly prior to PG 9.0 . 9.0
> has a new vacuum full implementation that makes it not so bad - it
> just rewrites the entire table.
>
> VACUUM FULL will take exclusive locks on tables being vacuumed. It
> also causes index bloat. You should be very careful about using it on
> a production system.
>
> I'm not sure why you'd advocate CLUSTER as a way to reclaim disk space.

Because it works pretty well; it reorganizes the table on the basis of
the order indicated by one index, and simultaneously:
 a) Shortens the table, removing all dead space;
 b) Regenerates all indices, so they too have no dead space.

Traditional VACUUM FULL tends to worsen the dead space problem on
indices, so adds the "insult to injury" problem that after running
VACUUM FULL, you might need to reindex, and that aftermath is nearly as
expensive as CLUSTER.

CLUSTER is likely to be quicker than VACUUM FULL, and it gives nice,
squeaky-tight indexes.

The new form of VACUUM FULL in 9.0 changes things, but it wasn't obvious
that the original poster was on 9.0.

> I wouldn't increase index fill factor as an optimisation, unless you
> had the unusual situation of having very static data in the table.

-- 
output = reverse("gro.mca" "@" "enworbbc")
http://linuxfinances.info/info/wp.html
"The world needs more people like us and fewer like them."  -- Unknown

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Why does my DB size differ between Production and DR? (Postgres 8.4)

[Chris Browne]

atsaloli.t...@gmail.com (Aleksey Tsalolikhin) writes:
> Situation:  Disk usage on production server root filesystem is at 68%
> utilization (80 GB used), on DR is at 51% (56 GB used).   We use
> SlonyII-1.2.x to keep the DR up to date.  I would like to account for
> the 24 GB difference.

It's more than likely a result of transactions failing on the origin,
leaving dead space around, where replication doesn't bother trying to do
any work for the "failed stuff," with the consequence that there's no
corresponding "clutter" on the replica.

I'm talking here about cases of failures that are expected.

Look to what activities you have that tend to lead to tranactions that
ROLLBACK.  Slony-I makes no attempt to replicate activity that is
terminated by ROLLBACK (explicit or implicit), so all that activity
won't be processed on replicas.

For instance, in our applications, operating domain registries,
intentionally failed database transactions occur heavily *common*
whenever customers are 'fighting' over domain names - one and only one
customer can win the name, while all others lose, and each losing
request leaves a certain amount of mess in its wake.  Common patterns of
this sort include transactions that fail because:

 - Customer has insufficient funds on account to pay for the transaction

 - Inventory request fails because there are insufficient items in stock

 - Attempt to insert a second instance of an object that is required to
   be unique

 - Rejection of partially processed transaction due to violation of some
   business policy (which is mighty open-ended!)

It's likely, as well, that there is some set of tables that you are not
vacuuming heavily enough.  Probably a table or three needs to have
CLUSTER run on it to bring them down to size, and you may need to fiddle
with autovacuum parameters to vacuum more frequently.
-- 
(reverse (concatenate 'string "moc.liamg" "@" "enworbbc"))
http://linuxdatabases.info/info/lisp.html
"Microsoft has world class quality control" -- Arthur Norman

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Why "copy ... from stdio" does not return immediately when reading invalid data?

[Nicolas Grilly]

Hello,

I am importing gigabytes of data into PostgreSQL, and I don't want to wait
10 minutes just to discover an error in the 10th line of my input file.

I tried the command "\copy ... from stdio" in psql and it looks like psql
has to read the entire input before returning a potential error, even if the
invalid value is in one of the first rows.

Is it a limitation of PostgreSQL protocol, of the library lipq, or of the
tool psql?

Is the copy protocol (aka PQputCopyData and PQputCopyEnd) designed to send
gigabytes of data with just one "copy ... from stdio" query, and is there a
way to be notified of a potential error before calling PQputCopyEnd? Or do I
have to send my data in small chunks (for example batch of 1
rows), issue a PQputCopyEnd, check for errors, and continue with the next
chunk?

Thanks for your help and advice.

Regards,

Nicolas Grilly

Re: [GENERAL] Streaming Rep 101 questions

[Adrian Klaver]

On 02/02/2011 07:52 AM, Gauthier, Dave wrote:

Hi:

I'm going to be experimenting with streaming replication using v9.0.1.
Here are a few questions I have at the onset...

1) Is it possible to replicate one database out of many that exist in
the DB instance? Or do you have to replicate them all?


It replicates the cluster.



2) Is replication transaction based? That is to say, does the act of
replicating a transaction wait until the commit on the server side?


It is shipping WAL records, so when they are recorded they ship, 
assuming streaming replication.




3) I will be replicating to a DB server 2 timezones away initially, but
perhaps from the US-> India at some future point. Is the PG replication
solution meant to do something like this (given sufficient bandwidth of
course)?


Not sure.



4) The slave DB instance will also be v9.0.1 on linux, but it wil be a
virtual machine. Do you see any problem with that?


FYI 9.0.3 was just released. I have tried it on an EC2 instance as a 
test and did not see any problems.




5) Is there a step-by-step "how to" document for this?


http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial



Thanks in Advance !




--
Adrian Klaver
adrian.kla...@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Streaming Rep 101 questions

[Ray Stell]

On Wed, Feb 02, 2011 at 08:52:02AM -0700, Gauthier, Dave wrote:
> I'm going to be experimenting with streaming replication using v9.0.1.  Here 
> are a few questions I have at the onset...

why not 9.0.2?


> 5) Is there a step-by-step "how to" document for this?

http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] "could not accept SSPI security context"

[Francisco Figueiredo Jr.]

Thank you very much for your patch!

I'm going to review and apply it.

As soon as it is done, I'll let you know.


On Wed, Feb 2, 2011 at 12:52, Ahmed  wrote:
>
> The issue has been addressed and patch has been submitted. Refer to Npgsql
> mailing thread
> http://lists.pgfoundry.org/pipermail/npgsql-devel/2011-February/001116.html
> http://lists.pgfoundry.org/pipermail/npgsql-devel/2011-February/001116.html
> .
>
> --
> View this message in context: 
> http://postgresql.1045698.n5.nabble.com/could-not-accept-SSPI-security-context-tp3275102p3367884.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
Regards,

Francisco Figueiredo Jr.
Npgsql Lead Developer
http://www.npgsql.org
http://fxjr.blogspot.com
http://twitter.com/franciscojunior

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Streaming Rep 101 questions

[Gauthier, Dave]

Hi:

I'm going to be experimenting with streaming replication using v9.0.1.  Here 
are a few questions I have at the onset...
1) Is it possible to replicate one database out of many that exist in the DB 
instance?  Or do you have to replicate them all?
2) Is replication transaction based?  That is to say, does the act of 
replicating a transaction wait until the commit on the server side?
3) I will be replicating to a DB server 2 timezones away initially, but perhaps 
from the US-> India at some future point.  Is the PG replication solution meant 
to do something like this (given sufficient bandwidth of course)?
4) The slave DB instance will also be v9.0.1 on linux, but it wil be a virtual 
machine.  Do you see any problem with that?
5) Is there a step-by-step "how to" document for this?

Thanks in Advance !

Re: [GENERAL] Changing SHMMAX

[Torsten Zühlsdorff]

Florian Weimer schrieb:


Please guide  me how to change it  permanently and what is the correct
value for it.
I am going for 8GB .


Usually, you can put these lines

kernel.shmall = 90
kernel.shmmax = 90

into /etc/sysctl.conf.  Run "sysctl -p" to activate them.  However,
this is a bit distribution-specific.


If you're using FreeBSD you even have to restart the machine.

Greetings,
Torsten
--
http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 
verschiedenen Datenbanksystemen abstrahiert,
Queries von Applikationen trennt und automatisch die Query-Ergebnisse 
auswerten kann.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Hot-Standby and sequences

[Wouter D'Haeseleer]

OKay Tom,

Thanks for this clarification

Re: [GENERAL] Hot-Standby and sequences

[Tom Lane]

"Wouter D'Haeseleer"  writes:
> I have a question about sequences in combination with streaming
> replication.
> It seems something strange is happening with sequences which are
> streamed to the slave.

> When updating the sequence the last_value on the slave shifts with 32
> and halts at this value until the master increased the value also with
> 32.
> Is this normal behavior ?

Yes, this is expected because of the hacks that are used to minimize
the number of WAL records emitted during nextval() operations.  The
slave is seeing the state that would prevail on the master, too, if the
master were to crash and restart.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] redirecting query statement and output to a marked up file, using psql

[Wim Bertels]

Hallo,

goal:
to write the input and query results to different files in 1 script

context example:
1 sql script demo.sql
"
--init messaging
\set client_min_messages warning
\set log_error_verbosity terse

--user1 
SET SESSION AUTHORIZATION user1;
\pset format html
\o report/test_user1.html
\i structure/test_user1.sql

--user2
SET SESSION AUTHORIZATION user2;
\pset format latex
\echo ECHO queries
\o report/test_user2.tex
\i structure/test_user2.sql
"

This doenst seem to work,
as the ECHO queries output isnt written to the file (test_user2.tex)

The only work around i found so far,
is using redirection.
starting for example
> psql -L out.txt  # only txt markup
or
> psql > out.txt 
But then if have to postprocess the out.txt file, dividing it into
several different files, and the format/markup doenst work so nice any
more.

Suggestions?

tnx,
Wim


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] "could not accept SSPI security context"

[Ahmed]

The issue has been addressed and patch has been submitted. Refer to Npgsql
mailing thread 
http://lists.pgfoundry.org/pipermail/npgsql-devel/2011-February/001116.html
http://lists.pgfoundry.org/pipermail/npgsql-devel/2011-February/001116.html
.

-- 
View this message in context: 
http://postgresql.1045698.n5.nabble.com/could-not-accept-SSPI-security-context-tp3275102p3367884.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Query plan optimization: sorting vs. partitioning

[Tom Lane]

Sergey Zaharchenko  writes:
> I need to select some data in the time order. When I query a separate
> smaller table, the index is used an no sorting is needed. However,
> when I query the main table, it occurs:
> ...
>   ->  Sort ...
>  Sort Key: ...
>  Sort Method: ...
>  ->  Result ...
>->  Append ...
>  ->  Seq Scan on states
>Filter: ...
>  ->  Seq Scan on states_20101206
>Filter: ...
> ...

> I see the database doesn't understand that there are no entries in the
> main table, so it has to assume the Append data is not ordered. Is
> there a way to avoid sorting?

No.  In existing releases there is no plan type that can produce
presorted output from an append relation (ie, an inheritance tree).
9.1 will be able to do that, but it wasn't exactly a small fix:
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=11cad29c91524aac1d0b61e0ea0357398ab79bf8

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Importing/Appending to Existing Table

[David Johnston]

You also don't have to import the source files directly into the live table.
Instead you could create a "staging" table that has no constraints where you
can import everything, do some review and updates, then merge that table
over to the live one.  Depending on how many files you are dealing with and
whether you want or need to pre-combine them a more forgiving staging table
may come in quite handy.  Especially consider the fact that you likely will
not know the maximum length used for various "text" fields you can make the
staging table fields varchar(255) (or even text) and then do some length
checks before importing the data into the accurately sized live table.

Dave


-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Rich Shepard
Sent: Wednesday, February 02, 2011 9:30 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Importing/Appending to Existing Table

On Wed, 2 Feb 2011, Greg Williamson wrote:

> It will not overwrite any existing data; if the table has constraints 
> that will prevent duplicates then the entire load will fail if any 
> item fails (it is a single transaction).
>
> If the table doesn't have such constraints then duplicate data in the 
> copy file will result in duplicate rows.

Greg,

   These data originate in highly undisciplined Excel spreadsheets over the
past 10+ years. They need a lot of preprossing in emacs and python scripts
I've written but there are no primary keys or other constraints until all
the data are entered then moved to the actual application tables. I will not
be surprised to discover duplicate data because of data-entry errors, but
there should not be any duplicates in these permit compliance monitoring
results.

Thanks,

Rich

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Importing/Appending to Existing Table

[Rich Shepard]

On Wed, 2 Feb 2011, Greg Williamson wrote:


It will not overwrite any existing data; if the table has constraints that
will prevent duplicates then the entire load will fail if any item fails
(it is a single transaction).

If the table doesn't have such constraints then duplicate data in the copy file
will result in duplicate rows.


Greg,

  These data originate in highly undisciplined Excel spreadsheets over the
past 10+ years. They need a lot of preprossing in emacs and python scripts
I've written but there are no primary keys or other constraints until all
the data are entered then moved to the actual application tables. I will not
be surprised to discover duplicate data because of data-entry errors, but
there should not be any duplicates in these permit compliance monitoring
results.

Thanks,

Rich

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Importing/Appending to Existing Table

[Rich Shepard]

On Wed, 2 Feb 2011, Sergey Konoplev wrote:


No it wont overwrite, it will append rows.


  Thank you, Sergey.

Rich

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Hot-Standby and sequences

[Wouter D'Haeseleer]

Hi All,

I have a question about sequences in combination with streaming
replication.
It seems something strange is happening with sequences which are
streamed to the slave.

When updating the sequence the last_value on the slave shifts with 32
and halts at this value until the master increased the value also with
32.
Is this normal behavior ?

Please see the example below:

Using Setval
--

# psql -t -h master -c "select setval('foo', 10)"
  10

# psql -t -h master  -c "select last_value from foo"
  10

# psql -t -h slave  -c "select last_value from foo"
  10

Using Nextval
--

# psql -t -h master -c "select nextval('foo')"
   11

# psql -t -h master  -c "select last_value from foo"
  11

# psql -t -h slave -c "select last_value from foo"
 43


-- 
Wouter D'Haeseleer
Linux System Engineer

VASCO Data Security
Dellingstraat 28b
2800 Mechelen
Belgium

phone: +32 15 50 44 00
email: w...@vasco.com 
web: www.vasco.com

[GENERAL] Query plan optimization: sorting vs. partitioning

[Sergey Zaharchenko]

Hello list,

I have a large time-indexed table (states) partitioned into several
tables based on the date. The smaller tables are clustered by their
time indices.The main table is empty.

I need to select some data in the time order. When I query a separate
smaller table, the index is used an no sorting is needed. However,
when I query the main table, it occurs:
...
  ->  Sort ...
 Sort Key: ...
 Sort Method: ...
 ->  Result ...
   ->  Append ...
 ->  Seq Scan on states
   Filter: ...
 ->  Seq Scan on states_20101206
   Filter: ...
...

I see the database doesn't understand that there are no entries in the
main table, so it has to assume the Append data is not ordered. Is
there a way to avoid sorting?

Please CC me as I'm not on the list. Thanks in advance,

-- 
DoubleF

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Why does my DB size differ between Production and DR? (Postgres 8.4)

[Bill Moran]

In response to Aleksey Tsalolikhin :

> On Mon, Jan 31, 2011 at 7:52 PM, Scott Marlowe  
> wrote:
> > On Mon, Jan 31, 2011 at 5:54 PM, Aleksey Tsalolikhin
> >  wrote:
> >> Situation:  Disk usage on production server root filesystem is at 68%
> >> utilization (80 GB used), on DR is at 51% (56 GB used).   We use
> >> SlonyII-1.2.x to keep the DR up to date.  I would like to account for
> >> the 24 GB difference.
> >
> > This is likely free space in your database.  Some of it is completely
> > normal and actually improves performance.  Too much and your db is
> > bloated and things starting taking too long.
> 
> Thanks, Scott!
> 
> Bucardo's "check_postgres.pl --action bloat" complains about one table,
> 1 GB wasted.  So the other tables must be OK.
> 
> So what about my DR, which doesn't have this same 20+ GB of "free space".
> Will it acquire it once it goes into production?  Will performance be impacted
> as it acquires the free space?  Should I even be concerned about the
> difference in disk usage or is it normal and expected?

Difference in free space from master to slaves is typical.  Transactions
run on the slaves differently than on the master.  For example, if you
rollback transactions on the master, that can bloat tables, but those
activities are never communicated to the slaves because the rollback
doesn't alter any data.

It's also possible that you have different autovacuum configs on the two
different machines (have you checked) or that the hardware isn't the
same, thus one is able to vacuum more successfully than the other,
or that simply the fates have caused vacuum to start at times that it
gets more done on one server than the other.

Do not be afraid of vacuum full.  It's not that it's an evil command or
should never be used, etc.  It's just something that has consequences
that you need to be aware of, such as:
*) It can take a long time
*) It locks tables while it works on them, thus it blocks other processes
   from accessing those tables
*) It can cause index bloat

However, there are mitigating factors:
*) You can tell it which tables to vacuum, thus you can vacuum full one
   table at a time to recduce the overall impact
*) It can be interrupted, so if it's taking longer than you're able to
   wait, you can cancel it.
*) You can use the REINDEX command to clean up index bloat.

Based on personal experience, and the fact that you have a slony slave to
work with, I recommend the following:

1) On the Slony slave, do the following, timing each step so you have an
   estimate of how long they will take on the master
1a) VACUUM the table.  This is non-locking and will do some preliminary
work so that VACUUM FULL takes less time.
1b) VACUUM FULL just that table.  Slony will be unable to replicate to
the table while the FULL is running, but that's OK, it will catch
up after it's done and the master won't be interrupted.
1c) REINDEX just that table.  This will have no effect on the master.
2) Now that you have time estimates for all those steps, add the times
   for 1b and 1c together.  This is an estimate of how long the master
   database will be interrupted while you do maintenance (step 1a does
   not interrupt other work going on).  Schedule downtime for about 2x
   that time, just in case things run a little longer.
3) Run steps 1a - 1c on the master.  Start 1a before your maintenance
   window starts, with enough time that it should be finished before
   your maintenance window.

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Privileges for read-only tables with sequence and foreign keys

[gvim]

To allow read-only access, ie. SELECT, to a user on a table which has a SERIAL 
column as well as foreign key references do I need to GRANT the user anything 
other than SELECT on the table and its corresponding sequence? As I understand, 
USAGE is only required to modify the table or use the currval function while 
REFERENCES is only required to create a foreign key, not to make use of it.

gvim

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Changing SHMMAX

[Florian Weimer]

* Adarsh Sharma:

> Please guide  me how to change it  permanently and what is the correct
> value for it.
> I am going for 8GB .

Usually, you can put these lines

kernel.shmall = 90
kernel.shmmax = 90

into /etc/sysctl.conf.  Run "sysctl -p" to activate them.  However,
this is a bit distribution-specific.

-- 
Florian Weimer
BFK edv-consulting GmbH   http://www.bfk.de/
Kriegsstraße 100  tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Changing SHMMAX

[Adarsh Sharma]

Dear all,

Today i try to perform some performance tuning for Postgresql database.

I want to change my shared memory permanently.
I have 16 GB RAM.

Please guide  me how to change it  permanently and what is the correct  
value for it.

I am going for 8GB .

Thanks & Regards
Adarsh Sharma



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Select for update with offset interferes with concurrent transactions

[Yngve N. Pettersen (Developer Opera Software ASA)]

Hello David,


On Wed, 02 Feb 2011 01:36:15 +0100, David Johnston   
wrote:


If random sampling is desirable would the following construct limit  
locking

only to the sampled rows?

SELECT id
FROM tasktable
WHERE id IN (SELECT random_id_sample())
FOR UPDATE

The "random_id_sample" would supply a configurable group of IDs off of
tasktable which the FOR UPDATE would then lock

I guess the issue remains that "random_id_sample()" would still end up
blocking if any of the rows it wants to return are already locked.


My immediate guess is that this would work, and I might explore it once I  
get my new fullscale test-db up and running



I too am using this basic protocol of maintaining state info within the
database and sending every query against it.  As I ponder this more it
really seems as if moving some of this logic into the application layer
would possibly make more sense in Yngve's situation (or at least  
something
to consider).  Continue to use the database as a persistence mechanism  
but

code the "dispatching" of tasks in the application layer and then as each
task is dispatched you simply do an "UPDATE table SET state = 'dispatch'
WHERE id = 'ID'" and a similar UPDATE when the task is returned  
completed.
This somewhat presumes you still only ever hand off one task at a time.   
If
you are indeed handing off tasks in batches then it would make sense to  
have
a "batch" table and operate at the batch level instead of individual  
tasks -

assigning tasks to a given batch via some standard mechanism.


If I read you correctly that is what my system does (dispatch = started,  
marked by the node that is to do the task).


The reason I am allocating tasks in batches is that there are so many  
processes involved that if they pick one at a time they would block each  
other. With the block allocation they only need to fetch the tasks once,  
meaning that there are not as many requests to the queue at a time, on  
average.



Either way if you truly want true parallel processing then you need to
create the parallel paths that can operate without clobbering each other  
and
thus each path needs to have its own pool of tasks since as soon as you  
have


That is what the offset part of the query was supposed to achieve.

At the moment I have worked around the problem by breaking the task list  
into 2000 subgroups, and each process picks one at random. That limits the  
number of processes that get in each others way, and the measured speed is  
now 4-5 times what I saw on Monday, and back in the old range of  
performance. However, it is a hack I had hoped to avoid (and I might get  
rid of it with the above suggestion)


a shared resource the only true way to make sure it is only allocated  
once

is to serialize access to it.  An alternative method would be to allow
multiple dispatches but have a "write-once" method that is called and  
sets
an immutable handler_id and then when the processing begins only the  
handler
with the matching id would be able allow to perform the actual  
processing.


This requires the handlers to have a unique ID, which my system has not  
needed so far.


I say the above with certainty but at the moment I am using and fairly  
happy

with my limited serialization - especially since I have specific
sub-properties that I can use to limit how many records are locked AND  
also
because the locking time is very short (I cap around 20 or so active  
tasks
to dispatch - and only infrequently at that) so my experience and  
insight to

high-demand situations is limited.

Dave


-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: Tuesday, February 01, 2011 12:18 PM
To: Yngve Nysaeter Pettersen
Cc: pgsql-general@postgresql.org
Subject: Re: Select for update with offset interferes with concurrent
transactions

"Yngve Nysaeter Pettersen"  writes:

To avoid having the processes trample each other's queries (the first
attempt was to select the first matching entries of the table, which
caused one to block all other transactions), one of the steps I took
was to select a set of idle rows at a random offset into the table
from the project, mark them for update, then update each record's state  
as

started.


   SELECT record_id FROM queue WHERE project_id = my_project AND state
= idle LIMIT n OFFSET i FOR UPDATE



At present "n" is 100-150, "i" is a random value in the range 0-1.



There is, intentionally, no ordering specified, since that would just
slow down the query, and is not necessary.


This seems like a pretty bad design.  There are recognized ways to solve
this problem with more predictability and much less chance of different
processes blocking each other.  In particular, this query seems be based  
on

some untenable assumptions about the physical row order being stable.


What I've discovered when using Postgres 9.0 is that the processes are
now blocking every other query into this table,


In 9.0, LIMIT/OFFSET processing is done after FOR U

Re: [GENERAL] Installation Issue of PostgresPlus-9.0

[Sachin Srivastava]

Install termcap (http://ftp.gnu.org/gnu/termcap/)

Add --enable-install-termcap configure option while running configure.


On Feb 2, 2011, at 3:29 PM, Adarsh Sharma wrote:

> I add one more thing after researching i find that this a bug and the 
> solution is to install libtermcap library solves this problem.
> 
> I followed the below steps :
> sudo apt-get install gcc
> sudo apt-get install libgcc1
> sudo apt-get install g++
> sudo apt-get install cpp
> sudo apt-get install ncurses-base
> sudo apt-get install ncurses-bin
> sudo apt-get install ncurses-term
> sudo apt-get install libncurses5
> 
> (and most importantly)
> sudo apt-get install libncurses5-dev
> 
> 
> But still I face the same issue.
> 
> Any comments.
> 
> 
> 
> 
> 
> Adarsh Sharma wrote:
>> Dear all,
>> 
>> I am trying to install postgresplus on a Ubuntu10.4 based system.
>> I  got a problem after installation when i issued the below command :
>> 
>> root@S10-MySQL:/opt/PostgresPlus/9.0SS# bin/psql -Upostgres
>> Password for user postgres:
>> psql (9.0.2)
>> Type "help" for help.
>> 
>> Cannot read termcap database;
>> using dumb terminal settings.
>> Aborted
>> root@S10-MySQL:/opt/PostgresPlus/9.0SS# ls -ls
>> 
>> I followed the same steps as in PostgrePlus-8.4 but don't know how it occurs.
>> Please help me to find the reason.
>> My startup.log says :
>> 
>> 2011-02-02 09:02:17 GMT LOG:  could not recognize system time zone
>> 2011-02-02 09:02:17 GMT DETAIL:  The PostgreSQL time zone will be set to 
>> "Etc/GMT-5".
>> 2011-02-02 09:02:17 GMT HINT:  You can specify the correct timezone in 
>> postgresql.conf.
>> 2011-02-02 09:05:47 GMT LOG:  could not recognize system time zone
>> 2011-02-02 09:05:47 GMT DETAIL:  The PostgreSQL time zone will be set to 
>> "Etc/GMT-5".
>> 2011-02-02 09:05:47 GMT HINT:  You can specify the correct timezone in 
>> postgresql.conf.
>> 
>> 
>> 
>> 
>> 
>> Thanks & Regards
>> Adarsh Sharma
>> 
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

--
Regards,
Sachin Srivastava
EnterpriseDB, the Enterprise PostgreSQL company.

[GENERAL] Hot-Standby and sequences

[Wouter D'Haeseleer]

Hi All,

I have a question about sequences in combination with streaming
replication.
It seems something strange is happening with sequences which are
streamed to the slave.

When updating the sequence the last_value on the slave shifts with 32
and halts at this value until the master increased the value also with
32.
Is this normal behavior ?

Please see the example below:

Using Setval
--

# psql -t -h master -c "select setval('foo', 10)"
  10

# psql -t -h master  -c "select last_value from foo"
  10

# psql -t -h slave  -c "select last_value from foo"
  10

Using Nextval
--

# psql -t -h master -c "select nextval('foo')"
   11

# psql -t -h master  -c "select last_value from foo"
  11

# psql -t -h slave -c "select last_value from foo"
 43


-- 
Wouter D'Haeseleer
Linux System Engineer

VASCO Data Security
Dellingstraat 28b
2800 Mechelen
Belgium

phone: +32 15 50 44 00
email: w...@vasco.com 
web: www.vasco.com

Re: [GENERAL] Importing/Appending to Existing Table

[Greg Williamson]

Rich --
> 
> 

>   I have an existing table with 15,492 rows and want to add additional rows
> from a .csv file. If I use 'COPY  from  with delimiter
> as ":" csv quote as "'" ' will this overwrite existing rows in the table or
> append rows?

It will not overwrite any existing data; if the table has constraints that will 
prevent duplicates then the entire load will fail if any item fails (it is a 
single transaction).

If the table doesn't have such constraints then duplicate data in the copy file 
will result in duplicate rows.

HTH,

Greg Williamson


  

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Installation Issue of PostgresPlus-9.0

[Adarsh Sharma]

I add one more thing after researching i find that this a bug and the 
solution is to install libtermcap library solves this problem.


I followed the below steps :
sudo apt-get install gcc
sudo apt-get install libgcc1
sudo apt-get install g++
sudo apt-get install cpp
sudo apt-get install ncurses-base
sudo apt-get install ncurses-bin
sudo apt-get install ncurses-term
sudo apt-get install libncurses5

(and most importantly)
sudo apt-get install libncurses5-dev


But still I face the same issue.

Any comments.





Adarsh Sharma wrote:

Dear all,

I am trying to install postgresplus on a Ubuntu10.4 based system.
I  got a problem after installation when i issued the below command :

root@S10-MySQL:/opt/PostgresPlus/9.0SS# bin/psql -Upostgres
Password for user postgres:
psql (9.0.2)
Type "help" for help.

Cannot read termcap database;
using dumb terminal settings.
Aborted
root@S10-MySQL:/opt/PostgresPlus/9.0SS# ls -ls

I followed the same steps as in PostgrePlus-8.4 but don't know how it 
occurs.

Please help me to find the reason.
My startup.log says :

2011-02-02 09:02:17 GMT LOG:  could not recognize system time zone
2011-02-02 09:02:17 GMT DETAIL:  The PostgreSQL time zone will be set 
to "Etc/GMT-5".
2011-02-02 09:02:17 GMT HINT:  You can specify the correct timezone in 
postgresql.conf.

2011-02-02 09:05:47 GMT LOG:  could not recognize system time zone
2011-02-02 09:05:47 GMT DETAIL:  The PostgreSQL time zone will be set 
to "Etc/GMT-5".
2011-02-02 09:05:47 GMT HINT:  You can specify the correct timezone in 
postgresql.conf.






Thanks & Regards
Adarsh Sharma




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Problem with encode () and hmac() in pgcrypto

[Marko Kreen]

On Wed, Feb 2, 2011 at 1:19 AM, hlcborg  wrote:
>> These two operations are not equivalent.
>
> But...
> Can I have this operation done in the Stored Procedure inside the Database?
> Plain SHA1, which is signed with RSA signature. and in the end encoded to
> base64?
>
> I was looking in the pgcrypto functions, and I haven´t found any that I
> could use I think... Maybe I am not looking for the rigth one.
> I need to use Plain SHA1 signed with a RSA signature and encoded into base64
> due to a new law related to digital signature in invoices in my country.

pgcrypto does not do signing, sorry.

But you can do it with PL/Python or PL/Perl and their wrapper libraries
around OpenSSL (or any other crypto library).

-- 
marko

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Installation Issue of PostgresPlus-9.0

[Adarsh Sharma]

Dear all,

I am trying to install postgresplus on a Ubuntu10.4 based system.
I  got a problem after installation when i issued the below command :

root@S10-MySQL:/opt/PostgresPlus/9.0SS# bin/psql -Upostgres
Password for user postgres:
psql (9.0.2)
Type "help" for help.

Cannot read termcap database;
using dumb terminal settings.
Aborted
root@S10-MySQL:/opt/PostgresPlus/9.0SS# ls -ls

I followed the same steps as in PostgrePlus-8.4 but don't know how it 
occurs.

Please help me to find the reason.
My startup.log says :

2011-02-02 09:02:17 GMT LOG:  could not recognize system time zone
2011-02-02 09:02:17 GMT DETAIL:  The PostgreSQL time zone will be set to 
"Etc/GMT-5".
2011-02-02 09:02:17 GMT HINT:  You can specify the correct timezone in 
postgresql.conf.

2011-02-02 09:05:47 GMT LOG:  could not recognize system time zone
2011-02-02 09:05:47 GMT DETAIL:  The PostgreSQL time zone will be set to 
"Etc/GMT-5".
2011-02-02 09:05:47 GMT HINT:  You can specify the correct timezone in 
postgresql.conf.






Thanks & Regards
Adarsh Sharma

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general