Re: [GENERAL] thank you
Joshua D. Drake wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 18 Dec 2007 19:31:57 -0700 Kevin Kempter [EMAIL PROTECTED] wrote: The point is that I hope you realize just how much you all mean to the community. Kevin I'll second that. Plus thank you for a database system that's a joy to work with. /Kevin (Although not the same Kevin as above) /me takes note of all the Kevins that are handing out hugs... :) Yup .. I'll dish out a dose o' them too.. ;-) -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] thank you
Joshua D. Drake wrote: On Wed, 19 Dec 2007 12:20:32 +0900 Paul Lambert [EMAIL PROTECTED] wrote: I'm just disappointed that I finish up work with my current employer on Friday and where I am going I won't get to work with PG anymore and thus won't have as much opportunity to interact with the PG community. That is until I can convince my new employer to realise the dark side of Microsoft SQL Server. :) 503-667-4564 extension 101... I am available anytime after 9:00am PST Now there's a blatant plug if ever I saw one. That's one in hand for EDB!! :-) (and in case that one gets missed: :-) ) /D ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] thank you
I second that! A BIG THANK YOU for you Postgresql folks out there and since the season is up: Merry Christmas and a Happy New Year! On Tue, 2007-12-18 at 21:26 -0500, Kevin Hunter wrote: Hullo List, This is aimed at everyone in this community who contributes to the Postgres project, but especially at the core folks who continually make this community great through energy, time, money, responses, and what-have-you. I see lots of Thank yous go by for this problem or that conundrum solved, but I don't think I've seen a general thank you for all that all y'all do. (If I've missed them in the blur of emails I get, I do apologize.) Thank you for all that you do. Thank you for the countless times you've saved my butt from my own stupidity. Thank you for the absolute awesome help you've been while tracking down bugs. Thank you for signing the NDA without fuss and searching my DB for a bug that I may have caused. Thank you for answering my frustrated emails at some ungodly hour of the morning (do you sleep?!). Thank you for engaging others in discussion, sometimes beyond the point of annoyance ... your enthusiasm and love for the project is duly noted. Thank you for constantly preaching the right way. Thank you . . . well you get the drift. I'll stop before this turns into some chain-letter type ordeal. The point is that I hope you realize just how much you all mean to the community. Kevin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ -- Pedro Doria Meunier Ips da Olaria Edf. Jardins do Garajau, 4 r/c Y 9125-163 Caniço Madeira Portugal GSM: +351 96 17 20 188 Skype: pdoriam http://www.madeiragps.com signature.asc Description: This is a digitally signed message part
[GENERAL] to realise the dark side of Microsoft SQL Server...?
Please elaborate! (Many a true word spoken in jestg) That is until I can convince my new employer to realise the dark side of Microsoft SQL Server. Dave Page wrote: Joshua D. Drake wrote: On Wed, 19 Dec 2007 12:20:32 +0900 Paul Lambert [EMAIL PROTECTED] wrote: I'm just disappointed that I finish up work with my current employer on Friday and where I am going I won't get to work with PG anymore and thus won't have as much opportunity to interact with the PG community. That is until I can convince my new employer to realise the dark side of Microsoft SQL Server. :) -- Johan van Zyl [EMAIL PROTECTED] http://www.jvz.co.za [EMAIL PROTECTED] http://www.acctual.co.za [EMAIL PROTECTED] http://www.watzon.co.za 079 549 0034 Cell/Mobile 012 543 2919 Huis/Home 086 622 9554 Fax Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! Teach a man how to surf the Internet and he will never bother you again. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] foreign key constraint, planner ignore index.
Andrew Nesheret wrote: And if i'm execute same statement without access to nodes table planer chose to use index fki_nodes!!! explain analyze SELECT 1 FROM ONLY public.sf_ipv4traffic x WHERE 2003 OPERATOR(pg_catalog.=) node FOR SHARE OF x; QUERY PLAN Index Scan using fki_nodes on sf_ipv4traffic x (cost=0.00..9.65 rows=1 width=6) (actual time=0.019..0.019 rows=0 loops=1) Index Cond: (2003 = node) Total runtime: 0.089 ms (3 rows) If you PREPARE then EXECUTE the same query, does it still use the index? The only thing I can think of is that the trigger is planning the query to work for any given value and you have a lot of rows with e.g. node=2004. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] foreign key constraint, planner ignore index.
Richard Huxton wrote:
If you PREPARE then EXECUTE the same query, does it still use the
index?
Done, same result.
--- Code ---
stmt = connection.prepareStatement( explain analyze SELECT 1 FROM
ONLY sf_ipv4traffic x WHERE ? OPERATOR(pg_catalog.=) node FOR SHARE OF x );
stmt.setLong( 1, 2004 );
rs = stmt.executeQuery();
while(rs.next())
{
System.out.println( rs.getString(1) );
}
--
Output:
Index Scan using fki_nodes on sf_ipv4traffic x (cost=0.00..9.65 rows=1
width=6) (actual time=0.063..0.063 rows=0 loops=1)
Index Cond: ($1 = node)
Total runtime: 0.131 ms
Test again with node=165
Seq Scan on sf_ipv4traffic x (cost=0.00..360281.29 rows=15795383
width=6) (actual time=0.041..105833.129 rows=15795376 loops=1)
Filter: ($1 = node)
Total runtime: 414955.170 ms
Planner/optimiser choice different strategies on different node IDS! But
'foreign key constraint checker' always use one strategy.
The only thing I can think of is that the trigger is planning the
query to work for any given value and you have a lot of rows with e.g.
node=2004.
Child table sf_ipv4traffic, contains only ONE value (in node column) and
this node never deleted
ALL 15 millions rows referenced to node 156
inms= select node from sf_ipv4traffic limit 10;
node
--
156
156
156
156
156
156
156
156
156
156
(10 rows)
--
__
WBR, Andrew Nesheret ICQ:10518066
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[GENERAL] Trouble running PostgreSQL server / Server must be started under certain locale.
I'm in the process of getting Subversion and Trac running on my development machine. So far, the installation process has gone fairly smoothly, until I had to install the Subversion bindings for Python. Because I run Ubuntu, and Ubuntu did not have the latest bindings in its repositories, I had to get it from the Debian archives instead. Well, after doing this, the PostgreSQL server is now refusing to start. The following error message is given: [EMAIL PROTECTED]:~$ sudo /etc/init.d/postgresql-8.1 start * Starting PostgreSQL 8.1 database server * Error: The server must be started under the locale en_US.UTF-8 which does not exist any more. Checking my locales tels me... [EMAIL PROTECTED]:~$ locale -a (snip) en_US.utf8 Obviously there's a mismatch there, but I'm still learning Linux, so I don't really know how to fix it. I've tried uninstalling and reinstalling the locales package, but that didn't help. Any help would be appreciated; I have quite a bit of data in my 8.1 cluster (active phpBB, etc.), and while I could upgrade to 8.2 or even the 8.3 beta, I don't know how I would be able to migrate a database if the postmaster process is unable to start. -- Geoff
Re: [GENERAL] foreign key constraint, planner ignore index.
Andrew Nesheret wrote:
Richard Huxton wrote:
If you PREPARE then EXECUTE the same query, does it still use the
index?
Done, same result.
--- Code ---
stmt = connection.prepareStatement( explain analyze SELECT 1 FROM
ONLY sf_ipv4traffic x WHERE ? OPERATOR(pg_catalog.=) node FOR SHARE OF
x );
stmt.setLong( 1, 2004 );
rs = stmt.executeQuery();
while(rs.next())
{
System.out.println( rs.getString(1) );
}
No, this will produce different plans - what happens if you supply each
parameter in turn - the second plan should stay the same as the first.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Trouble running PostgreSQL server / Server must be started under certain locale.
Geoff Ellingwood wrote: I'm in the process of getting Subversion and Trac running on my development machine. So far, the installation process has gone fairly smoothly, until I had to install the Subversion bindings for Python. Because I run Ubuntu, and Ubuntu did not have the latest bindings in its repositories, I had to get it from the Debian archives instead. Well, after doing this, the PostgreSQL server is now refusing to start. If you're not familiar with your system, you really are better off staying with the packages your distribution supports. The following error message is given: [EMAIL PROTECTED]:~$ sudo /etc/init.d/postgresql-8.1 start * Starting PostgreSQL 8.1 database server * Error: The server must be started under the locale en_US.UTF-8 which does not exist any more. Checking my locales tels me... [EMAIL PROTECTED]:~$ locale -a (snip) en_US.utf8 Obviously there's a mismatch there, but I'm still learning Linux, so I don't really know how to fix it. I've tried uninstalling and reinstalling the locales package, but that didn't help. Presumably it's getting the locale details from the wrong repository (Debian unstable, I guess you're using). Did you agree to install a whole bunch of other packages when you were trying to sort out this Python thing? Any help would be appreciated; I have quite a bit of data in my 8.1 cluster (active phpBB, etc.), and while I could upgrade to 8.2 or even the 8.3 beta, I don't know how I would be able to migrate a database if the postmaster process is unable to start. Step 1 is to make sure you have a backup of your database directory. That's *everything* in .../pgsql/data (or wherever). You're going to have to get the database running again and dump it. Step 2 will be to revert the locale packages (at least). I'd actually recommend going back to a completely clean system and staring again unless you're clear what's gone wrong. You'll need to read this and figure out exactly what changes you've made first. http://www.debian.org/doc/manuals/apt-howto/ Sections 3.8/9/10 are probably the most important, but read the lot before doing anything. Best bet for help then is an ubuntu/debian list, but as long as you've got backups you'll be OK. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Initdb problem - catch 22?
Hi people, Right I've setup postgres numerous times without a problem, and this time I'm using all the same stuff but running into a problem where initdb won't run without the postgres server running, and the postgres server will not run without the postgresql.conf etc that initdb creates! To start with I tried the following: #/usr/local/pgsql/bin/createdb SEE --encoding='LATIN1' Which gave me the error : createdb: could not connect to database postgres: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? So okay, I'll try and start the server: #/usr/local/pgsql/bin/postgres -D /data/postgres /data/postgres/psql.log 21 and in psql.log I get the error: postgres cannot access the server configuration file /data/postgres/postgresql.conf: No such file or directory I shout at it I know Initdb should have done it!! Anyone got any idea what's eating my lunch? Maybe I ran something in the wrong order? Thanks Glyn ___ Yahoo! Answers - Got a question? Someone out there knows the answer. Try it now. http://uk.answers.yahoo.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Initdb problem - catch 22?
Hi people, Right I've setup postgres numerous times without a problem, and this time I'm using all the same stuff but running into a problem where initdb won't run without the postgres server running, and the postgres server will not run without the postgresql.conf etc that initdb creates! To start with I tried the following: #/usr/local/pgsql/bin/createdb SEE --encoding='LATIN1' Which gave me the error : createdb: could not connect to database postgres: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? So okay, I'll try and start the server: #/usr/local/pgsql/bin/postgres -D /data/postgres /data/postgres/psql.log 21 and in psql.log I get the error: postgres cannot access the server configuration file /data/postgres/postgresql.conf: No such file or directory I shout at it I know Initdb should have done it!! Anyone got any idea what's eating my lunch? Maybe I ran something in the wrong order? Thanks Glyn ___ Support the World Aids Awareness campaign this month with Yahoo! For Good http://uk.promotions.yahoo.com/forgood/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Initdb problem - catch 22?
Hi people, Right I've setup postgres numerous times without a problem, and this time I'm using all the same stuff but running into a problem where initdb won't run without the postgres server running, and the postgres server will not run without the postgresql.conf etc that initdb creates! To start with I tried the following: #/usr/local/pgsql/bin/createdb SEE --encoding='LATIN1' Which gave me the error : createdb: could not connect to database postgres: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? So okay, I'll try and start the server: #/usr/local/pgsql/bin/postgres -D /data/postgres /data/postgres/psql.log 21 and in psql.log I get the error: postgres cannot access the server configuration file /data/postgres/postgresql.conf: No such file or directory I shout at it I know Initdb should have done it!! Anyone got any idea what's eating my lunch? Maybe I ran something in the wrong order? Thanks Glyn ___ Support the World Aids Awareness campaign this month with Yahoo! For Good http://uk.promotions.yahoo.com/forgood/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Initdb problem - catch 22?
Glyn Astill wrote: Hi people, Right I've setup postgres numerous times without a problem, and this time I'm using all the same stuff but running into a problem where initdb won't run without the postgres server running, and the postgres server will not run without the postgresql.conf etc that initdb creates! To start with I tried the following: #/usr/local/pgsql/bin/createdb SEE --encoding='LATIN1' Keep in mind that createdb is completely different and separate from initdb. My guess is that you're confusing the two. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Password as a command line argument to createuser
Greg Smith [EMAIL PROTECTED] writes: In your typical shell nowadays the echo command is a built-in one--it executes directly rather than calling a separate echo binary, so it won't leak what you tell it onto a command line. That means this line in a script would be simplest way to do this that's not completely insecure: echo create user foo password 'secret' | psql ... And if we haven't given you a headache yet: There's a similar risk even after you've securely sent the command to the database server: it will be transiently exposed in pg_stat_activity, and perhaps permanently logged in the postmaster log. Now the audience that can see either of those things is hopefully smaller than everyone on the machine, but still it's not very nice if you don't want anyone else to know the cleartext of your password. The way to deal with this is to pre-encrypt the password before you send it over to the server. Both the createuser program and psql's \password command do it that way. Unfortunately it looks like they both insist on reading the password from /dev/tty, so if you want to script this, you'd be stuck with making a special-purpose program that didn't. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Getting char * from timestamp in a composite type
Hi,
I need to extract a text string representation of a timestamp from a
user-defined row type; e.g., from the composite type ('1980-01-01
12:00:00', 'Random Comment'), I'd like to extract the C string
1980-01-01 12:00:00 in my user-defined C function.
As I understand it, I should be able to do something like (assuming
time is the attribute name):
bool isNull;
HeapTupleHeader t = DatumGetHeapTupleHeader(row);
Datum var = GetAttributeByName( row, time, isNull );
// Check for null
char * ret = DatumGetCString( DirectFunctionCall1(textout, var ) );
However, the code above (and every other variant I've tried) invariable
segmentation faults the postmaster. so obviously I am doing something
wrong. Can someone explain (or point to an example that works) of how to
do this?
Regards,
Michael A.
begin:vcard
fn:Michael Akinde
n:Akinde;Michael
org:Meteorologisk Institutt, Norge;IT
adr;quoted-printable:;;Gaustadall=C3=A9en 30D;Oslo;;0313;Norge
email;internet:[EMAIL PROTECTED]
tel;work:22963379
tel;cell:45885379
x-mozilla-html:FALSE
url:http://www.met.no
version:2.1
end:vcard
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] thank you
On Dec 19, 2007, at 2:43 AM, Dave Page wrote: Joshua D. Drake wrote: On Wed, 19 Dec 2007 12:20:32 +0900 Paul Lambert [EMAIL PROTECTED] wrote: I'm just disappointed that I finish up work with my current employer on Friday and where I am going I won't get to work with PG anymore and thus won't have as much opportunity to interact with the PG community. That is until I can convince my new employer to realise the dark side of Microsoft SQL Server. :) 503-667-4564 extension 101... I am available anytime after 9:00am PST Now there's a blatant plug if ever I saw one. That's one in hand for EDB!! :-) (and in case that one gets missed: :-) ) /D Hah! :P Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Getting char * from timestamp in a composite type
On Wed, Dec 19, 2007 at 04:40:38PM +0100, Michael Akinde wrote: As I understand it, I should be able to do something like (assuming time is the attribute name): bool isNull; HeapTupleHeader t = DatumGetHeapTupleHeader(row); Datum var = GetAttributeByName( row, time, isNull ); // Check for null char * ret = DatumGetCString( DirectFunctionCall1(textout, var ) ); That's not going to work. textout wants a text datum. Try calling timestamp_out instead... Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Those who make peaceful revolution impossible will make violent revolution inevitable. -- John F Kennedy signature.asc Description: Digital signature
[GENERAL] referential integrity and defaults, DB design or trick
I've something like this: create table i ( iid serial primary key, name varchar(32) ); create table p ( pid serial primary key, iid int references i(iid) on delete cascade, name varchar(32) ); create table c ( bid serial primary key, pid int references p(pid) on delete set null ); insert into i values(default,'i'); -- default proprieties (singularity) insert into p values(-1,null,'default p'); insert into p values(default,1,'this p'); insert into p values(default,1,'that p'); insert into c values(default,null); insert into c values(default,1); insert into c values(default,-1); let's say I'd like to associate c with a name (the propriety) a null c.pid means I still have to assign a propriety or the previously assigned propriety is not anymore available. I'd like to have a way to say take the propriety from i and the above is what I came out with. But that introduces a singularity. Any better design? I don't like to write a schema that needs data inside to have a meaning. If not how can I protect the singularity from accidental delete? Most of the db will be accessed through functions and this is a step. An alternative design could be create table c ( bid serial primary key, usedefault boolean, pid int references p(pid) on delete set null ); where usedefault=true - use default usedefault=false - use i.pid usedefault is null - not yet assigned but then I'll have to find a way to keep usedefault and pid coherent (I can't have usedefault=false and pid=null). And having a trigger doesn't make it look nicer, since if I pass not coherent values I'll have to deal with the error. The first technique doesn't have this problem (just others... but it is simpler). Any good link about DB design and how to deal with similar problems that has some postgres spice? thx and yep pgsql community is great. -- Ivan Sergio Borgonovo http://www.webthatworks.it ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Getting char * from timestamp in a composite type
Martijn van Oosterhout wrote: On Wed, Dec 19, 2007 at 04:40:38PM +0100, Michael Akinde wrote: As I understand it, I should be able to do something like (assuming time is the attribute name): bool isNull; HeapTupleHeader t = DatumGetHeapTupleHeader(row); Datum var = GetAttributeByName( row, time, isNull ); // Check for null char * ret = DatumGetCString( DirectFunctionCall1(textout, var ) ); That's not going to work. textout wants a text datum. Try calling timestamp_out instead.. Sigh... it's always the idiot bugs that are the hardest to see. Thanks a lot. Regards, Michael A. begin:vcard fn:Michael Akinde n:Akinde;Michael org:Meteorologisk Institutt, Norge;IT adr;quoted-printable:;;Gaustadall=C3=A9en 30D;Oslo;;0313;Norge email;internet:[EMAIL PROTECTED] tel;work:22963379 tel;cell:45885379 x-mozilla-html:FALSE url:http://www.met.no version:2.1 end:vcard ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Problem with collector statistic
Dears, Running a query on 2 database-equal, with the same indices, but with slightly different data, I have a very different result of performance. In one the index is used to make the join, in another it is seqscan. The next consultation with explain. Select a.CodPagador,b.Descricao From Frete01 a Left Outer Join Frete02 b On (a.CodPagador = b.CodCliente) explain in database bad: Merge Left Join (cost=46619.72..48113.50 rows=58995 width=39) (actual time=7435.922..8359.063 rows=59161 loops=1) Merge Cond: (outer.codpagador = inner.codcliente) - Sort (cost=24649.80..24797.29 rows=58995 width=11) (actual time=4063.517..4171.374 rows=59161 loops=1) Sort Key: a.codpagador - Seq Scan on frete01 a (cost=0.00..19974.95 rows=58995 width=11) (actual time=9.007..3223.000 rows=59161 loops=1) - Sort (cost=21969.91..22274.37 rows=121784 width=39) (actual time=3372.375..3674.563 rows=169818 loops=1) Sort Key: b.codcliente - Seq Scan on frete02 b (cost=0.00..8127.84 rows=121784 width=39) (actual time=17.330..1608.599 rows=121740 loops=1) Total runtime: 8448.797 ms explain in good dabatase: Limit (cost=0.00..46856.62 rows=55204 width=39) (actual time=11.205..536.761 rows=55204 loops=1) - Nested Loop Left Join (cost=0.00..295237.26 rows=55204 width=39) (actual time=11.201..508.936 rows=55204 loops=1) - Seq Scan on frete01 a (cost=0.00..19974.95 rows=55204 width=11) (actual time=11.057..220.711 rows=55204 loops=1) - Index Scan using frete02_f021 on frete02 b (cost=0.00..4.65 rows=1 width=39) (actual time=0.021..0.024 rows=1 loops=9363) Index Cond: (outer.codpagador = b.codcliente) Total runtime: 551.331 ms Thanks to advance for your´s attention, Marco Aurélio V. da Silva Prodata Inf. e Cad. Ltda. MSN: [EMAIL PROTECTED] Fone: (33) 3322-3082 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Getting char * from timestamp in a composite type
Michael Akinde [EMAIL PROTECTED] writes: bool isNull; HeapTupleHeader t = DatumGetHeapTupleHeader(row); Datum var = GetAttributeByName( row, time, isNull ); // Check for null char * ret = DatumGetCString( DirectFunctionCall1(textout, var ) ); That's not going to work. textout wants a text datum. Try calling timestamp_out instead.. Sigh... it's always the idiot bugs that are the hardest to see. Thanks a lot. The other problem is that you're passing row not t to GetAttributeByName. If you don't have your compiler configured to bleat about type mismatches like that, your days of C programming will be bleak and painful. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Problem with collector statistic
=?iso-8859-1?Q?Marco_Aur=E9lio_V._da_Silva?= [EMAIL PROTECTED] writes: Running a query on 2 database-equal, with the same indices, but with slightly different data, I have a very different result of performance. In one the index is used to make the join, in another it is seqscan. The fact that one explain includes a Limit step, and the other does not, says that you are not really issuing the same query in both cases. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Deploy postgres - upgrade strategy
Hi, I need your kind advice on postgres deployment strategy. My application is developed on 8.2.4 and need to be deployed to Windows server 2003. Clients may /may not have postgres installed. How can I deploy postgres silently on client machine? Specifically: 1. How to determine if postgres is installed on Win2K3, and what's its version? 2. If there is an older version (e.g., 8.1) installed, can I install a new version (8.2.4) without touching the existing installation? 3. If there is an older version of the same version line (e.g., 8.2.3) installed, is it safe for me to upgrade it by running upgrade.bat, believing that it won't break existing application using the old version? 4. If there is a newer version installed (e.g. 8.3), can I still install 8.2.4 as a separate installation without touching the existing installation? 5. If there is a newer version of the same version line (e.g. 8.2.5) installed, shall I simply use it? (same as 3 I guess) If silent installation command can be provided, that will be great. My fresh installation command is like: msiexec /i postgresql-8.2-int.msi /qr INTERNALLAUNCH=1 ADDLOCAL=server,psql,pgadmin,npgsql SERVICEDOMAIN=%COMPUTERNAME% SERVICEPASSWORD=SecretWindowsPassword123 CREATESERVICEUSER=1 SUPERPASSWORD=postgres BASEDIR=C:\Program Files\PostgreSQL DATADIR=e:\PostgreSQL.DATA Thanks a lot! Sean Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs
Re: [GENERAL] Problem with collector statistic
Marco Aurélio V. da Silva [EMAIL PROTECTED] writes: Select a.CodPagador,b.Descricao From Frete01 a Left Outer Join Frete02 b On (a.CodPagador = b.CodCliente) explain in good dabatase: Limit (cost=0.00..46856.62 rows=55204 width=39) (actual time=11.205..536.761 rows=55204 loops=1) That doesn't match the query you describe. Notably there's no LIMIT in the query (or the other plan) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Deploy postgres - upgrade strategy
Sean Z. wrote: Hi, I need your kind advice on postgres deployment strategy. My application is developed on 8.2.4 and need to be deployed to Windows server 2003. Clients may /may not have postgres installed. How can I deploy postgres silently on client machine? Specifically: 1. How to determine if postgres is installed on Win2K3, and what's its version? You can probe the port if you want to catch any kind. If you're only interested in official MSI installs, you can check the registry (there's a key under HKLM\Software\PostgreSQL IIRC) 2. If there is an older version (e.g., 8.1) installed, can I install a new version (8.2.4) without touching the existing installation? Yes. 3. If there is an older version of the same version line (e.g., 8.2.3) installed, is it safe for me to upgrade it by running upgrade.bat, believing that it won't break existing application using the old version? Normally, yes. You might want to check the release notes if there is any incompatibility, but we usually do whatever possible to avoid those between minor releases. 4. If there is a newer version installed (e.g. 8.3), can I still install 8.2.4 as a separate installation without touching the existing installation? Yes. You can always have the different major versions on the same system. You just can't have 1 different *minor* version on the same system if you use the official MSI - if you install it manually, there is no restriction at all. 5. If there is a newer version of the same version line (e.g. 8.2.5) installed, shall I simply use it? (same as 3 I guess) Probably. Depends on if you want your application to be entirelyi independent or not. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] pgpool and slony
Hi, This is a newbie pgpool question. I have 2 servers (master-slave) and Slony is doing the replication. I had installed pgpool for load balance where i had disabled the replication_mode=false and load_balance_mode=true in pgpool.conf pcp_node_info gives this output. -sh-3.00$pcp_node_info 10 localhost 9898 josh postgres 1 172.31.0.67 5432 2 16383.50 -sh-3.00$ pcp_node_info 10 localhost 9898 josh postgres 0 172.31.0.68 5432 2 16383.50 So does this mean that pgpool is working properly with slony-1. Also how do I test to see if the load balancing with pgpool is working properly. What kind of query will help me confirm that it is load balanced ? Also will pgpool automate failover? How can I test that? sorry if my question is silly thanks josh
Re: [GENERAL] Initdb problem - catch 22?
You should run the commands in the following order: initdb pg_ctl -D data_dir -w start # (recommended way of starting the the server) createdb #(optional, since default DB postgres is already there) On Dec 19, 2007 6:17 AM, Glyn Astill [EMAIL PROTECTED] wrote: Hi people, Right I've setup postgres numerous times without a problem, and this time I'm using all the same stuff but running into a problem where initdb won't run without the postgres server running, and the postgres server will not run without the postgresql.conf etc that initdb creates! To start with I tried the following: #/usr/local/pgsql/bin/createdb SEE --encoding='LATIN1' Which gave me the error : createdb: could not connect to database postgres: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? So okay, I'll try and start the server: #/usr/local/pgsql/bin/postgres -D /data/postgres /data/postgres/psql.log 21 and in psql.log I get the error: postgres cannot access the server configuration file /data/postgres/postgresql.conf: No such file or directory I shout at it I know Initdb should have done it!! Anyone got any idea what's eating my lunch? Maybe I ran something in the wrong order? Thanks Glyn ___ Yahoo! Answers - Got a question? Someone out there knows the answer. Try it now. http://uk.answers.yahoo.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com 17° 29' 34.37N, 78° 30' 59.76E - Hyderabad 18° 32' 57.25N, 73° 56' 25.42E - Pune 37° 47' 19.72N, 122° 24' 1.69 W - San Francisco * http://gurjeet.frihost.net Mail sent from my BlackLaptop device
Re: [GENERAL] to realise the dark side of Microsoft SQL Server...?
Johan van Zyl wrote: Please elaborate! (Many a true word spoken in jestg) That is until I can convince my new employer to realise the dark side of Microsoft SQL Server. It's the product of an evil company? Let's see: 1. they are virtually a monopoly 2. they use their monopoly to tie folks to their product upgrade merry-go-round. 3. They purchase companies to destroy competition. 4. They generate FUD rather then innovate. 5. The Novell agreement? 6. They coerce computer makers to not only preload their operating system, but coerce them into promoting it. 7. the OOXML debacle? The list continues, but I suspect you get my point. -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Deploy postgres - upgrade strategy
On Dec 19, 2007 11:12 AM, Sean Z. [EMAIL PROTECTED] wrote: Hi, I need your kind advice on postgres deployment strategy. My application is developed on 8.2.4 and need to be deployed to Windows server 2003. Clients may /may not have postgres installed. How can I deploy postgres silently on client machine? Specifically: 3. If there is an older version of the same version line (e.g., 8.2.3) installed, is it safe for me to upgrade it by running upgrade.bat, believing that it won't break existing application using the old version? Under no circumstances should you simply do it. You should ask the user if it's ok to do it. If I had a machine running a specific version because that's what I'd tested and was deployed in other places, and it got updated by some other app being installed I would not be happy. 4. If there is a newer version installed (e.g. 8.3), can I still install 8.2.4 as a separate installation without touching the existing installation? That may be your best bet if you're looking to use postgresql and your app together as a kind of bolted together application. I'd say that you should ask the user up front if they have another postgresql server (it might not be on the same machine you're installing your app on, btw) they want to use, or if they'd like you to install a postgresql instance just for your application on the local machine. Then make all decisions based on that one question. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] to realise the dark side of Microsoft SQL Server...?
Geoffrey wrote: Johan van Zyl wrote: Please elaborate! (Many a true word spoken in jestg) That is until I can convince my new employer to realise the dark side of Microsoft SQL Server. It's the product of an evil company? Let's see: 1. they are virtually a monopoly 2. they use their monopoly to tie folks to their product upgrade merry-go-round. 3. They purchase companies to destroy competition. 4. They generate FUD rather then innovate. 5. The Novell agreement? 6. They coerce computer makers to not only preload their operating system, but coerce them into promoting it. 7. the OOXML debacle? The list continues, but I suspect you get my point. You are assuming most people care. 1. You have to pay for the the OS. 2. You have to pay for the database. 3. You have to pay for any extra feature. That is where you start. Sincerely, Joshua D. Drake ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] to realise the dark side of Microsoft SQL Server...?
Joshua D. Drake wrote: Geoffrey wrote: Johan van Zyl wrote: Please elaborate! (Many a true word spoken in jestg) That is until I can convince my new employer to realise the dark side of Microsoft SQL Server. It's the product of an evil company? Let's see: 1. they are virtually a monopoly 2. they use their monopoly to tie folks to their product upgrade merry-go-round. 3. They purchase companies to destroy competition. 4. They generate FUD rather then innovate. 5. The Novell agreement? 6. They coerce computer makers to not only preload their operating system, but coerce them into promoting it. 7. the OOXML debacle? The list continues, but I suspect you get my point. You are assuming most people care. No, I'm trying to educate people. I was answering the question of the previous poster. If people begin to understand the issues, they will begin to care. At least some of them. 1. You have to pay for the the OS. 2. You have to pay for the database. 3. You have to pay for any extra feature. That is where you start. Agreed. -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Killing a session in windows
Bruce Momjian wrote: Alvaro Herrera wrote: Bruce Momjian wrote: Alvaro Herrera wrote: Huh, why is it awkward? Alternatively, you can send the signal directly using commandkill/ (or commandpg_ctl kill TERM [process id]/). I think we should mention Windows in there somewhere, because it isn't alternatively on Windows. Actually, this whole change is a bit silly, because the text now says something like: You can send these signals using pg_ctl kill. Alternatively you can use kill (or pg_ctl kill) Wow, yea, I see that now, but it is alone a paragraph above. I updated the text to: The xref linkend=app-pg-ctl program provides a convenient interface for sending these signals to shut down the server. Alternatively, you can send the signal directly using commandkill/ on non-Windows systems. My documentation point is that in administering the service, I cannot drop a database if there is still an active connection. To drop a process in linux appears to be easy using kill, but this does not seem to be the case in windows using taskkill. I would rather see some reference to killing rogue connections using pg_ctl in the Managing Databases chapter, and reference to it in Destroying a Database and DROP DATABASE documentation because this would be where I would first search for a solution when I had the problem. In the pg_ctl documentation, I would recommend explicitly stating that pg_ctl kill -TERM pid can be used to kill individual connections to a database in windows, because taskkill and select pg_cancel_backend() do not seem to always work (for me anyway) in windows. Also HUP and other signals mean nothing to a windows user. In general the documentation, understandably, is geared toward *nix, I do not know what proportion of installations are Windows, but I suspect they are growing at a rapid rate since version 8. Postgres on Windows is a fabulous product, and the migration to the windows platform has been much cleaner than the migration of Mysql, so it would be a shame to lose market share on the basis that the documentation still has sections biased towards *nix. Removal of *nix-isms from the main strand of the documentation and additions of clearly marked build dependant comments where appropriate would make a big difference in uniting the world! So for example, the documentation for pg_ctl would have a description and common options, and then list any linux/bsd/unix/windows differences in section similar to the User Comments sections of the documentation. I enjoyed that! Anyway - Merry Christmas / Eid / Holidays to you all and I'm looking forward to 8.3 under the christmas tree. Howard. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Problem with collector statistic
Sorry, I discovered that the problem appears to be another, doing tests with the same database, this is a consultation limit using the same database and with 9364 records of the problem, and with 9363 not of. Next bad query with bad results: explain analyse Select a.CodPagador,b.Descricao From Frete01 a Left Outer Join Frete02 b On (a.CodPagador = b.CodCliente) limit 9364 Limit (cost=46619.72..46856.82 rows=9364 width=39) (actual time=3442.510..3561.769 rows=9364 loops=1) - Merge Left Join (cost=46619.72..48113.50 rows=58995 width=39) (actual time=3442.505..3535.236 rows=9364 loops=1) Merge Cond: (outer.codpagador = inner.codcliente) - Sort (cost=24649.80..24797.29 rows=58995 width=11) (actual time=1347.896..1364.993 rows=9364 loops=1) Sort Key: a.codpagador - Seq Scan on frete01 a (cost=0.00..19974.95 rows=58995 width=11) (actual time=9.001..558.582 rows=59161 loops=1) - Sort (cost=21969.91..22274.37 rows=121784 width=39) (actual time=2094.581..2115.666 rows=11976 loops=1) Sort Key: b.codcliente - Seq Scan on frete02 b (cost=0.00..8127.84 rows=121784 width=39) (actual time=2.149..383.775 rows=121740 loops=1) Total runtime: 3580.588 ms good query with good results: explain analyse Select a.CodPagador,b.Descricao From Frete01 a Left Outer Join Frete02 b On (a.CodPagador = b.CodCliente) limit 9363 Limit (cost=0.00..46856.62 rows=9363 width=39) (actual time=50.652..1864.420 rows=9363 loops=1) - Nested Loop Left Join (cost=0.00..295237.26 rows=58995 width=39) (actual time=50.647..1836.360 rows=9363 loops=1) - Seq Scan on frete01 a (cost=0.00..19974.95 rows=58995 width=11) (actual time=8.948..215.558 rows=9363 loops=1) - Index Scan using frete02_f021 on frete02 b (cost=0.00..4.65 rows=1 width=39) (actual time=0.163..0.166 rows=1 loops=9363) Index Cond: (outer.codpagador = b.codcliente) Total runtime: 1879.041 ms Thanks for your´s attention. Marco Aurélio V. da Silva Prodata Inf. e Cad. Ltda. MSN: [EMAIL PROTECTED] Fone: (33) 3322-3082 - Original Message - From: Tom Lane [EMAIL PROTECTED] To: Marco Aurélio V. da Silva [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Sent: Wednesday, December 19, 2007 2:09 PM Subject: Re: [GENERAL] Problem with collector statistic =?iso-8859-1?Q?Marco_Aur=E9lio_V._da_Silva?= [EMAIL PROTECTED] writes: Running a query on 2 database-equal, with the same indices, but with slightly different data, I have a very different result of performance. In one the index is used to make the join, in another it is seqscan. The fact that one explain includes a Limit step, and the other does not, says that you are not really issuing the same query in both cases. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Way to avoid expensive Recheck Cond in index lookup?
But it's true that it's possible for a slow expression to make the recheck very expensive. The planner doesn't have a very good understanding of how to tell whether the expression is likely to be slow. The case I ran into is thing like WHERE x = ANY $1::integer[] which become very slow for very large arrays. So I'm sure xpath() could possibly trigger the same case. But the number of matching pages would have to be quite large. And in that case the alternative (regular index scans) is going to suck too. So the actual index function expression is _only_ evaluated in the re-check for some (or all?) matches, if there are more matching pages than can fit into the memory allocated by work_mem? I also seemed to notice that after running a query that did return a large number of results where the query plan did use the text[] index, running the same query, or a similar one, would stop using the index lookup and just do a full table scan. Would that be the optimizer changing plans because of the statistics it gathered when it ran the query initially with the index lookup but found the re-check condition took such a long time to execute? What I was trying to accomplish was to define a text[] index created from the results of an xpath() expression, for the purposes of being able to do fast index lookups using the operator. But I'm finding that even when the index is used, the query is very slow and I was assuming it was coming from the re-check condition, which is defined as that xpath() call. So I'm finding that this approach isn't working out as I had hoped. -- m@ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Problem with collector statistic
On Dec 19, 2007 2:06 PM, Marco Aurélio V. da Silva [EMAIL PROTECTED] wrote: Sorry, I discovered that the problem appears to be another, doing tests with the same database, this is a consultation limit using the same database and with 9364 records of the problem, and with 9363 not of. Sounds like pgsql is switching to a sequential scan a little too quickly for your dataset / server / etc... I'd say look at increasing effective_cache_size and lowering random_page_cost ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] postgres UTC different from perl?
Hi,
when trying:
psql template1 -c select date_part('epoch',current_date at time zone 'UTC');
date_part
1198015200
the result is different from
perl -MDateTime -le 'print DateTime-today(time_zone = UTC)-epoch;'
1198022400
Is there an issue with postgresql?
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] Rich LIKE inheritance
Hi all. Is there a way to inherit from a table with the LIKE construct and also get its indexes along with contraints, DEFAULTs and NOT NULLs? An example: CREATE TABLE story_base ( flag BOOL NOT NULL DEFAULT TRUE, starting TIMESTAMP NOT NULL DEFAULT '-INFINITY', ending TIMESTAMP NOT NULL DEFAULT 'INFINTY' ); CREATE INDEX i_story_base ON story_base( flag,starting,ending ); CREATE TABLE atable ( sometext TEXT, LIKE story_base INCLUDING DEFAULTS ); I'd like atable to also inherit an index like the one defined for story_base. Any hint? -- Reg me, please! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Deploy postgres - upgrade strategy
Yes, bolted together application is something I am thinking about. It will satisfied my application's requirement and it won't affect existing applications. So is it possible to install my postgres server (say 8.2.4) regardless of whether there is a postgres server of any version existing on the same windows box? - Original Message From: Scott Marlowe [EMAIL PROTECTED] To: Sean Z. [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Sent: Wednesday, December 19, 2007 1:04:19 PM Subject: Re: [GENERAL] Deploy postgres - upgrade strategy On Dec 19, 2007 11:12 AM, Sean Z. [EMAIL PROTECTED] wrote: Hi, I need your kind advice on postgres deployment strategy. My application is developed on 8.2.4 and need to be deployed to Windows server 2003. Clients may /may not have postgres installed. How can I deploy postgres silently on client machine? Specifically: 3. If there is an older version of the same version line (e.g., 8.2.3) installed, is it safe for me to upgrade it by running upgrade.bat, believing that it won't break existing application using the old version? Under no circumstances should you simply do it. You should ask the user if it's ok to do it. If I had a machine running a specific version because that's what I'd tested and was deployed in other places, and it got updated by some other app being installed I would not be happy. 4. If there is a newer version installed (e.g. 8.3), can I still install 8.2.4 as a separate installation without touching the existing installation? That may be your best bet if you're looking to use postgresql and your app together as a kind of bolted together application. I'd say that you should ask the user up front if they have another postgresql server (it might not be on the same machine you're installing your app on, btw) they want to use, or if they'd like you to install a postgresql instance just for your application on the local machine. Then make all decisions based on that one question. Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs
Re: [GENERAL] postgres UTC different from perl?
Louis-David Mitterrand wrote:
Hi,
when trying:
psql template1 -c select date_part('epoch',current_date at time zone 'UTC');
date_part
1198015200
the result is different from
perl -MDateTime -le 'print DateTime-today(time_zone = UTC)-epoch;'
1198022400
Is there an issue with postgresql?
Well, there's one of 3 possibilities:
1. PostgreSQL's date/time code is broken.
2. Perl's DateTime module is broken.
3. One of those bits of code doesn't do what you think it does.
I'm not sure that (CURRENT_DATE AT TIME ZONE 'UTC') does what you think
it does. Try setting your timezone to various offsets and exploring.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
[GENERAL] phpPgAdmin 4.2-Beta-1 translation status
Howdy folks,
As the current beta of phppgadmin is also a call for translators, I did a
quick check to see the current status of the phppgadmin translations. We've
had a slew of updates for this release, and with them a large number of new
language strings were introduced. According to some quick hackery, our
status's look like this:
afrikaans.php translation status: 558/875, ~ 63.8%
arabic.php translation status: 519/875, ~ 59.3%
catalan.php translation status: 769/875, ~ 87.9%
chinese-sim.php translation status: 309/875, ~ 35.3%
chinese-tr.php translation status: 516/875, ~ 59%
chinese-utf8-zh_CN.php translation status: 775/875, ~ 88.6%
chinese-utf8-zh_TW.php translation status: 775/875, ~ 88.6%
czech.php translation status: 498/875, ~ 56.9%
danish.php translation status: 532/875, ~ 60.8%
dutch.php translation status: 423/875, ~ 48.3%
french.php translation status: 871/875, ~ 99.5%
german.php translation status: 673/875, ~ 76.9%
hebrew.php translation status: 550/875, ~ 62.9%
hungarian.php translation status: 875/875, ~ 100.0%
italian.php translation status: 654/875, ~ 74.7%
japanese.php translation status: 679/875, ~ 77.6%
mongol.php translation status: 462/875, ~ 52.8%
polish.php translation status: 757/875, ~ 86.5%
portuguese-br.php translation status: 368/875, ~ 42.1%
portuguese-pt.php translation status: 556/875, ~ 63.5%
romanian.php translation status: 774/875, ~ 88.5%
russian.php translation status: 516/875, ~ 59%
slovak.php translation status: 673/875, ~ 76.9%
spanish.php translation status: 656/875, ~ 75%
swedish.php translation status: 495/875, ~ 56.6%
turkish.php translation status: 673/875, ~ 76.9%
ukrainian.php translation status: 515/875, ~ 58.9%
If you're interested in helping out, please don't hesitate to contact me or
the phppgadmin developers at [EMAIL PROTECTED] Thanks
in advance.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Rich LIKE inheritance
Reg Me Please wrote: Hi all. Is there a way to inherit from a table with the LIKE construct and also get its indexes along with contraints, DEFAULTs and NOT NULLs? CREATE TABLE ... LIKE isn't inheritance, it's copying the structure. I believe you can duplicate indexes in 8.3. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Deploy postgres - upgrade strategy
On Dec 19, 2007 1:55 PM, Sean Z. [EMAIL PROTECTED] wrote: Yes, bolted together application is something I am thinking about. It will satisfied my application's requirement and it won't affect existing applications. So is it possible to install my postgres server (say 8.2.4) regardless of whether there is a postgres server of any version existing on the same windows box? Yes it is. Take a look at hyperic, they do that. Note that if you are gonna include a version of postgresql with your application, you should make it an option, as sometimes folks would rather use their one big pgsql server for such things than having yet another odd copy running around. Hyperic makes it very easy to switch from using their pgsql to your own. They install their copy on port 9432 instead of 5432, and all you have to do it edit the jdbc connect line to point at your own pg server and create a db and user to match hyperic's and you're done. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] INDEX on a composite type
Hi all. When trying to create an index on a composite type, I need to enclose the columns in paranthesis. Is this normal? I don't think so as there's no mention in the friendly manual. An example: create type atype as ( atext text, anint int ); create table atable ( info text, compo atype ); create index i_atable_compo on atable( ((compo).atext),((compo).anint) ); With single parenthesis, as suggested by the friendly manual, I get a syntax error close to the . test=# create index i_atable_compo on atable( (compo).atext,(compo).anint ); ERROR: syntax error at or near . LINE 2: (compo).atext,(compo).anint ^ -- Vincenzo Romano Maybe Computers will never become as intelligent as Humans. Surely they won't ever become so stupid. [VR-1989] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Partitioned tables Slony
Goboxe [EMAIL PROTECTED] writes: Thanks Chris. How do I use *.sh *.ik files? The *.sh files are shell scripts that should run with any Bourne shell; the *specific* intent is that they be invoked by the test control script, run_test.sh. The *.ik files are (mostly) bodies of Slonik scripts; they require a preamble that indicates cluster name and node connection information. What you might want to do is to run the partitioning test. You'd do this by making sure you have PGPORT pointing to a postmaster with Slony-I installed, and PGUSER set to a superuser, then, from the test directory, run the command: $ ./run_test.sh testpartition This will run various scripts, using the ones in the testpartition directory to control the test. The script generate_dml.sh shows how any of the special scripts get used. The point of this is that it represents a regression test that verifies that various aspects of replication work in conjunction with table partitioning. You won't be able to take the code directly, but the code does represent an automated test that builds a cluster that involves partitioning, and adds in new partitions on the fly. -- let name=cbbrowne and tld=linuxfinances.info in name ^ @ ^ tld;; http://linuxfinances.info/info/sgml.html The only thing better than TV with the sound off is Radio with the sound off. -- Dave Moon ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] INDEX on a composite type
On Wed, Dec 19, 2007 at 10:07:32PM +0100, GMail wrote: Hi all. When trying to create an index on a composite type, I need to enclose the columns in paranthesis. Is this normal? I don't think so as there's no mention in the friendly manual. I beleive it says somewhere that anything except straight column names need parenthesis. What you have is an expression and thus it needs them. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Those who make peaceful revolution impossible will make violent revolution inevitable. -- John F Kennedy signature.asc Description: Digital signature
Re: [GENERAL] INDEX on a composite type
GMail wrote: When trying to create an index on a composite type, I need to enclose the columns in paranthesis. Is this normal? I don't think so as there's no mention in the friendly manual. I think your problem is similar to one that has been discussed here previously. http://www.mail-archive.com/pgsql-general@postgresql.org/msg75862.html Colin ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Trouble running PostgreSQL server / Server must be started under certain locale.
On Dec 19, 2007 7:40 AM, Richard Huxton [EMAIL PROTECTED] wrote: Geoff Ellingwood wrote: Any help would be appreciated; I have quite a bit of data in my 8.1cluster (active phpBB, etc.), and while I could upgrade to 8.2 or even the 8.3beta, I don't know how I would be able to migrate a database if the postmaster process is unable to start. Step 1 is to make sure you have a backup of your database directory. That's *everything* in .../pgsql/data (or wherever). You're going to have to get the database running again and dump it. Step 2 will be to revert the locale packages (at least). I'd actually recommend going back to a completely clean system and staring again unless you're clear what's gone wrong. You'll need to read this and figure out exactly what changes you've made first. http://www.debian.org/doc/manuals/apt-howto/ Sections 3.8/9/10 are probably the most important, but read the lot before doing anything. Best bet for help then is an ubuntu/debian list, but as long as you've got backups you'll be OK. I think my best bet is going to be to nuke my 8.1 installation from orbit (after making a backup of the cluster directory) and let Ubuntu configure it correctly with whatever locale managed to squeeze itself into my system. When I was fiddling with it last night, I did manage to get a copy of 8.2 up and running, but I knew that I couldn't just copy 8.1's data directory over top of 8.2's. So, what are tips for completely removing 8.1 after I've made my backups? Will apt-get remove -purge be sufficient, or am I going to need to track down individual binaries and config files as well? -- Geoff
Re: [GENERAL] INDEX on a composite type
Il Wednesday 19 December 2007 22:29:25 Colin Wetherbee ha scritto: GMail wrote: When trying to create an index on a composite type, I need to enclose the columns in paranthesis. Is this normal? I don't think so as there's no mention in the friendly manual. I think your problem is similar to one that has been discussed here previously. http://www.mail-archive.com/pgsql-general@postgresql.org/msg75862.html Colin By the way, why not allowing the declaration of an index over a composite type as a whole? (As opposed to a composite type as the list of single component types). -- Vincenzo Romano Maybe Computers will never become as intelligent as Humans. Surely they won't ever become so stupid. [VR-1989] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Postgres 8.3 HOT and non-persistent xids
Hi, Can someone clarify HOT updates for me (and perhaps put more detail into the docs?). Is this statement correct: the HOT technique is used on *any* table so long as no indexed column is affected. create table T (A int, B int); create index TB on T (B); insert into T (A,B) Values (1,2); So if I do an update that is identical to the existing row, nothing changes? update T set A=1, B=2 where A=1; If I change the non-indexed field, A, then HOT applies and no new tuple needed? update T set A=2, B=2 where A=1; If I change the indexed field, B, then HOT doesn't apply and a new tuple is needed? update T set A=2,B=3 where A=2; Is that correct? Actually, what actually happens when you get an update with redundant information, e.g. update T set A=2,B=4 where A=2; The value of A hasn't changed, does postgres still write the value? And for non-persistent transaction ids, the documentation says that this is for read-only transactions. What defines a read-only transaction for this purpose? Does postgres check to see if a SELECT includes e.g. a sequence change via nextval? If I mark the transaction as readonly using the PG JDBC driver, will that be sufficient? Thank you, Mike ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Password as a command line argument to createuser
On Wed, Dec 19, 2007 at 10:38:52AM -0500, Tom Lane wrote: reading the password from /dev/tty, so if you want to script this, you'd be stuck with making a special-purpose program that didn't. But given that passwords are sort of awful in this way anyway, why not use something designed not to have this problem, like Kerberos? Especially now that someone has been doing the work to make Kerberos play nicely in the latest and greatest ways? A ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] pgpool and slony
Josh Harrison [EMAIL PROTECTED] writes: This is a newbie pgpool question. ... sorry if my question is silly It doesn't sound silly, but this isn't the best place to ask it. The slony project has its own mailing list(s) and you'll get better-informed answers there. Not sure if pgpool has its own list. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] postgres UTC different from perl?
Richard Huxton [EMAIL PROTECTED] writes:
I'm not sure that (CURRENT_DATE AT TIME ZONE 'UTC') does what you think
it does. Try setting your timezone to various offsets and exploring.
In fact, I think it's adjusting in exactly the wrong direction.
I get the right number from
regression=# select date_part('epoch', 'today'::timestamp at time zone 'UTC');
date_part
1198022400
(1 row)
and the wrong one from
regression=# select date_part('epoch', 'today'::timestamptz at time zone
'UTC');
date_part
1198058400
(1 row)
and I think the locution with CURRENT_DATE is equivalent to the second
case because timestamptz is the preferred type to promote date to.
regards, tom lane
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] pgpool and slony
Hi, This is a newbie pgpool question. I have 2 servers (master-slave) and Slony is doing the replication. I had installed pgpool for load balance where i had disabled the replication_mode=false and load_balance_mode=true in pgpool.conf pcp_node_info gives this output. -sh-3.00$pcp_node_info 10 localhost 9898 josh postgres 1 172.31.0.67 5432 2 16383.50 -sh-3.00$ pcp_node_info 10 localhost 9898 josh postgres 0 172.31.0.68 5432 2 16383.50 So does this mean that pgpool is working properly with slony-1. You need to trun on master_slave_mode. This will ensure that write queries are sent to only master (remember that Slony's slave does not accept write queries). Also how do I test to see if the load balancing with pgpool is working properly. What kind of query will help me confirm that it is load balanced ? Turn on query logging on PostgreSQL and look into the log file. Also will pgpool automate failover? How can I test that? For Slony slave, it's easy. Stop the slave's postmaster using pg_ctl command. I recommend to use -m f option. For Slony master, it's a little complicated. In this case Slony requires user to manualy promote one of slaves to new master (correct me if I am wrong). While pgpool automaticaly detach the broken master and chose the second PostgreSQL (second means written in the second place in pgpool.conf) and regard it as a new master. So there needs some communication between pgpool and Slony. As of pgpool-II 2.0，a new directive called failover_command is added. User can specify shell command to be executed when a node is detached by pgpool. You can specify following in the directive: # special values: %d = node id # %h = host name # %p = port number # %D = database cluster path # %% = '%' character So you could invoke some Slony management command by using above to sync pgpool and Slony(I'm not familiar with Slony, and hope someone help us in this regard). sorry if my question is silly Not at all. Thanks for testing pgpool. thanks josh ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pgpool and slony
On Wed, 19 Dec 2007, Tom Lane wrote: Not sure if pgpool has its own list. There's a moderately active general user list as part of the set at http://pgfoundry.org/mail/?group_id=155 -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Postgres 8.3 HOT and non-persistent xids
On Dec 20, 2007 3:44 AM, Mike C [EMAIL PROTECTED] wrote: Hi, Can someone clarify HOT updates for me (and perhaps put more detail into the docs?). Is this statement correct: the HOT technique is used on *any* table so long as no indexed column is affected. Its partially correct. HOT is used on system and user tables. No index column change is a necessary but not sufficient condition for HOT update. There must be enough free space in the same block where the old tuple exists. Though we hope that the system will stabilize in terms of availability of free space in the blocks, it might be worthy to leave free space of at least one tuple size by using appropriate fill factor at the table creation time. create table T (A int, B int); create index TB on T (B); insert into T (A,B) Values (1,2); So if I do an update that is identical to the existing row, nothing changes? update T set A=1, B=2 where A=1; HOT update *is not* update-in-place. So every update, HOT or COLD, would generate a new version of the row. The power of HOT comes when the index column is not changed. This allows us to skip index inserts for the new version (thus preventing index bloats). Its also far easier to vacuum the dead HOT tuples without running VACUUM or VACUUM FULL. This gives us the ability to prevent heap bloats. If I change the non-indexed field, A, then HOT applies and no new tuple needed? update T set A=2, B=2 where A=1; HOT applies, but new tuple is needed as described above. If I change the indexed field, B, then HOT doesn't apply and a new tuple is needed? update T set A=2,B=3 where A=2; Right. Actually, what actually happens when you get an update with redundant information, e.g. update T set A=2,B=4 where A=2; The value of A hasn't changed, does postgres still write the value? Yes. Every update generates a new version of the row. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Postgres 8.3 HOT and non-persistent xids
[ Pavan already answered most of this, but ... ] Mike C [EMAIL PROTECTED] writes: And for non-persistent transaction ids, the documentation says that this is for read-only transactions. What defines a read-only transaction for this purpose? A transaction that has not done anything that requires it to sign an update with its transaction ID. Does postgres check to see if a SELECT includes e.g. a sequence change via nextval? IIRC, nextval() doesn't involve marking anything with one's XID, because it is not a rollback-able operation. But you are thinking at quite the wrong level if you suppose that this behavior has anything to do with checking a SELECT for a writing operation. The way it really works is that a backend generates a persistent XID for its current transaction at the instant that some bit of code first demands the transaction's XID. Typically this happens because you insert, update, or delete some tuple, and the XID is needed to set xmin or xmax of the tuple. There are some other cases, but they are covered by definition, because there is no way to get the current XID except to ask that code for it. If I mark the transaction as readonly using the PG JDBC driver, will that be sufficient? If this means what I think it means, it's irrelevant. Materializing a persistent XID is driven off what the transaction *actually* does, not off whether it's declared to be read-only or not. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] foreign key constraint, planner ignore index.
Richard Huxton wrote: Andrew Nesheret wrote: Richard Huxton wrote: If you PREPARE then EXECUTE the same query, does it still use the index? Tested, comments? --- code - --- result --- Result for node #2007 Index Scan using fki_nodes on sf_ipv4traffic x (cost=0.00..9.65 rows=1 width=6) (actual time=0.060..0.060 rows=0 loops=1) Index Cond: ($1 = node) Total runtime: 0.131 ms Result for node #156 Seq Scan on sf_ipv4traffic x (cost=0.00..360281.29 rows=15795383 width=6) (actual time=0.048..108939.142 rows=15795376 loops=1) Filter: ($1 = node) Total runtime: 365989.953 ms UP(subject), we are go away from my problem, in case of integrity check with foreign key. -- __ WBR, Andrew Nesheret ICQ:10518066 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] foreign key constraint, planner ignore index.
Forget to add test source code.
--- code ---
long [] IDs = new long[] {
2007, // no references
156 // all rows in sf_ipv4traffic
referenced to
};
stmt = connection.prepareStatement( explain analyze SELECT 1 FROM
ONLY sf_ipv4traffic x WHERE ? OPERATOR(pg_catalog.=) node FOR SHARE OF x );
for ( long nodeID : IDs )
{
stmt.setLong( 1, nodeID );
rs = stmt.executeQuery();
System.out.println( Result for node # + nodeID );
while ( rs.next() )
System.out.println(+ rs.getString( 1 ) );
}
rs.close();
connection.close();
---
--
__
WBR, Andrew Nesheret ICQ:10518066
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] Way to avoid expensive Recheck Cond in index lookup?
Matt Magoffin [EMAIL PROTECTED] writes: I also seemed to notice that after running a query that did return a large number of results where the query plan did use the text[] index, running the same query, or a similar one, would stop using the index lookup and just do a full table scan. Would that be the optimizer changing plans because of the statistics it gathered when it ran the query initially with the index lookup but found the re-check condition took such a long time to execute? No, there is not any feed-forward from previous query executions to new plans. (There's been discussion of that, but nothing done as yet. Personally I'm worried that it's hard enough to understand what's happening without any such effect.) If you saw the plan changing for apparently no reason, maybe you have autovacuum enabled? A background autovac could update the stored table statistics and thereby cause a plan change. What I was trying to accomplish was to define a text[] index created from the results of an xpath() expression, for the purposes of being able to do fast index lookups using the operator. But I'm finding that even when the index is used, the query is very slow and I was assuming it was coming from the re-check condition, which is defined as that xpath() call. So I'm finding that this approach isn't working out as I had hoped. I'm not sure that anyone's done any performance analysis on xpath as yet. Do you want to try oprofile or gprof or some other tool to see where the time is going? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
