Re: [GENERAL] Postgres: Installing as a service
CM J wrote: Hi , I have extracted postgres from postgres-noinstaller.zip file. How do i install postgres as a service from cmd line ? Search: http://msdn.microsoft.com/ You can use the `net' command to start/stop services. I don't recall the command(s) used to install them, if any, but you'll be able to find the info on MSDN. Are there are any binaries provided by postgres to install it as a service ? No. Why not use the MSI installer's silent mode? -- Craig Ringer -- 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] Postgres: Installing as a service
Craig Ringer wrote: CM J wrote: Hi , I have extracted postgres from postgres-noinstaller.zip file. How do i install postgres as a service from cmd line ? Search: http://msdn.microsoft.com/ You can use the `net' command to start/stop services. I don't recall the command(s) used to install them, if any, but you'll be able to find the info on MSDN. sc is the command line tool for installing/configuring services. you'll need to create a service account with NET USER, then configure the pgsql service to run as that user. the standard postgresql windows installer configures it to look like... C:\sc queryex pgsql-8.3 SERVICE_NAME: pgsql-8.3 TYPE : 10 WIN32_OWN_PROCESS STATE : 4 RUNNING (STOPPABLE,PAUSABLE,ACCEPTS_SHUTDOWN) WIN32_EXIT_CODE: 0 (0x0) SERVICE_EXIT_CODE : 0 (0x0) CHECKPOINT : 0x0 WAIT_HINT : 0x0 PID: 1256 FLAGS : C:\sc qc pgsql-8.3 [SC] GetServiceConfig SUCCESS SERVICE_NAME: pgsql-8.3 TYPE : 10 WIN32_OWN_PROCESS START_TYPE : 2 AUTO_START ERROR_CONTROL : 1 NORMAL BINARY_PATH_NAME : D:\postgres\8.3\bin\pg_ctl.exe runservice -w -N pgsql-8.3 -D D:\postgres\8.3\data\ LOAD_ORDER_GROUP : TAG: 0 DISPLAY_NAME : PostgreSQL Database Server 8.3 DEPENDENCIES : SERVICE_START_NAME : .\postgres so the commands to create tthis would be something like... C:\net user postgres 3gesIjita%9 /add C:\sc create pgsql-8.3 binpath= D:\postgres\8.3\bin\pg_ctl.exe runservice -w -N pgsql-8.3 -D D:\postgres\8.3\data\ type= own start= auto displayname= PostgreSQL Database Server 8.3 obj= .\postgres password= 3gesIjita%9 C:\sc start pgsql-8.3 and pray. note I intentionally installed postgresql to a directory with no spaces in its name to dodge issues with nested quotes. 3gesIjita%9 is just a random password I concocted. note, those sc commands have a funny syntax, the = MUST have no space before it and MUST have a space after it. -- 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] Postgres: Installing as a service
CM J wrote on 13.04.2009 07:48: Hi , I have extracted postgres from postgres-noinstaller.zip file. How do i install postgres as a service from cmd line ? Are there are any binaries provided by postgres to install it as a service ? Please note that i am aware that msi installer automatically does all this. I want to know how to acheive the same from the postgres db extracted from the zip file. Thanks. Use pg_ctl register (after you have created the postgres windows user as described by John) http://www.postgresql.org/docs/8.3/static/app-pg-ctl.html -- 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] Postgres: Installing as a service
Hi, The following cmd can be used for installating postgres as a service: pg_ctl.exe register -N servicename -U user -P password -D datadir and pgctl.exe unregister -N servicename can be used to uninstall. Thanks. On Mon, Apr 13, 2009 at 12:15 PM, Craig Ringer cr...@postnewspapers.com.auwrote: CM J wrote: Hi , I have extracted postgres from postgres-noinstaller.zip file. How do i install postgres as a service from cmd line ? Search: http://msdn.microsoft.com/ You can use the `net' command to start/stop services. I don't recall the command(s) used to install them, if any, but you'll be able to find the info on MSDN. Are there are any binaries provided by postgres to install it as a service ? No. Why not use the MSI installer's silent mode? -- Craig Ringer
[GENERAL] pgSql authentication problem with openLdap
We have installed postgresql 8.1.11 in CentOS, also installed openLdap 2.3.27. I have tried a lot to authenticate the user against ldap but couldn't succeed. Can anyone pl help me out? I have added the following entry in pg_hba.conf host all all 202.18.10.0/24 ldap://202.18.10.1:389/dc=abc,dc=net;;ou=people My ldap server ip is 202.18.10.1 domain is abc.net all users are under ou=People,dc=abc,dc=net Though I have found few discussions about the same issues and bugs, but couldn't fix my problem. -- View this message in context: http://www.nabble.com/pgSql-authentication-problem-with-openLdap-tp23021809p23021809.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
[GENERAL] Really wierd PGAdmin/Windows Explorer bug?
Greetings! I just ran across one of the strangest pieces I have ever seen in a piece of software. I was connected remotely to a test machine set up at a customer's site. The machine is running Windows XP and PostgreSQL 8.1. PGAdmin has a connection to the customer's production database, which resides on another machine. The database has a simple 3-column table that I thought has 22 million records, until last week when I found that a backup of the table showed that it actually has no records. A member of this list introduced me to the concept of inherited tables. There are 2 rules on the feedback table. One says that if the charge number is less than 7000, then data should be inserted into the feedback_backup table and the other says that if if the charge number is greater than 7000, data should be inserted into the feedback_active table. The feedback_active table's definition includes INHERIT (feedback). The feedback_backup table does not have an INHERIT clause. I was curious about how much data was in each of the other two tables. I opened an SQL window and issued select count(charge) from feedback_backup. In a separate window I ussied select count(charge) from feedback_active. The two queries were running simultaneously. I already knew that select count(*) from feedback takes about twenty minutes. I minimized my Remote Desktop window and moved on to other things. Half an hour later I returned. Neither query had finished, and the query timer in both windows had not been updated. Task Manager showed me that every PGAdmin task was not responding. Using Task Manager, I killed PGAdmin. At the time I first issued the count queries, a Windows Explorer window was up. It was showing the contents of a folder that had 4 filed: backup_feedback.bat, backup_in_pieces..bat, restore_pieces.bat and restore_feedback.bat. After I killed PGAdmin, the names of the files were shown to be: backup_feedback.bat Ln 1 Col 31 46250 ms 46360 ms With some idea of showing you what happened, I pressed Alt-PrtSc to get a screenshot of the strange file names. On my machine, I started MS Word and pasted the contents of the clipboard into my document. The file names were correct! I tried using Ctrl-PrtSc on the remote machine, but the same thing happened when I pasted the image into my Word document: the file names were correct. I closed the Windows Explorer window and reopened it. And the incorrect file names reappeared! Can anybody explain this? Should this be brought to anyone else's attention, and if so, whose? RobR
Re: [GENERAL] pgSql authentication problem with openLdap
Sorry, forget to mention the log message of pgsql: invalid entry in the file pg_hba.conf at line 82, token ldap://202.. fatal: missing of erroneous pg_hba.conf file -- View this message in context: http://www.nabble.com/pgSql-authentication-problem-with-openLdap-tp23021809p23021943.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] pgSql authentication problem with openLdap
On 13/04/2009 13:49, sandiphw wrote: invalid entry in the file pg_hba.conf at line 82, token ldap://202.. fatal: missing of erroneous pg_hba.conf file Have you got double-quotes around the entry in ph_hba.conf? According to the docs, it's a good idea to do that. http://www.postgresql.org/docs/8.3/static/auth-methods.html#AUTH-LDAP Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] Minimizing Recovery Time (wal replication)
On Sun, Apr 12, 2009 at 5:52 AM, Simon Riggs si...@2ndquadrant.com wrote: The database is performing too frequent restartpoints. This has been optimised in PostgreSQL 8.4 by the addition of the bgwriter running during recovery. This will mean that your hot spare will not pause while waiting for restartpoint to complete, so will be significantly faster. 8.4 is already looking like it's going to be a great upgrade for us, this would be another win. Thanks, Bryan -- 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] Can't get the debugger going
[Redirecting to the list] On 13/04/2009 16:33, gaybana2...@hotmail.com wrote: Thank you for your reply. I only have pgadmin3 installed. where do I run these commands from. what do I need to install to rund these command line commands? What platform are you on? Also, what exactly are you trying to do? I looked in the docs for how to use the pg_dumpall commands...don't know where to run these commands. You just run them at the system command line... if you're on Windows, go to Start - Run and type 'cmd' to bring up a command prompt. Of course, you need to have these utilities installed in the first place - how you do this depends on your platform. I also don't have template1 dbase. I think pgAdmin hides it by default. To see it (and a lot of other stuff), go to File - Options - Display, and check Show system objects in the treeview; then refresh the Databases node of the treeview by clicking on it and pressing F5. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] Can't get the debugger going
On 13/04/2009 17:04, Raymond O'Donnell wrote: [Redirecting to the list] Wrong list - sorry. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] Really wierd PGAdmin/Windows Explorer bug?
Radcon Entec wrote: I was connected remotely [snip] Can anybody explain this? It's much more likely to be an issue with your remote software. At a guess, you were using RDP (Remote Desktop) to control the remote host? It tries to be clever with the way it sends data, but it *can* get confused when things go wrong. -- Craig Ringer -- 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] In memory Database for postgres
2009/4/12 John R Pierce pie...@hogranch.com: aravind chandu wrote: I created in-memory database but the problem is all the data will be accessed from main memory .when ever the system is restarted the entire data that is in the tables will lost.Is there any way to dump all the data in to local hard disk before restarting the system or any similar method to save the data in to a permanent storage. memory is volatile, disk is persistent. if you want persistent databases, I recommend storing them on disk. ubuntu=# create table test_ram (i integer, name text) tablespace ram_space; CREATE TABLE ubuntu=# create temp table test_ram_temp (i integer, name text) tablespace ram_space; CREATE TABLE ubuntu=# create temp table test_disk_temp (i integer, name text); CREATE TABLE ubuntu=# create table test_disk (i integer, name text); CREATE TABLE ubuntu=# explain analyze insert into test_ram values (generate_series(1,100),random()::text); QUERY PLAN - Result (cost=0.00..0.02 rows=1 width=0) (actual time=0.019..9354.014 rows=100 loops=1) Total runtime: 22836.532 ms (2 rows) ubuntu=# explain analyze insert into test_ram_temp values (generate_series(1,100),random()::text); QUERY PLAN - Result (cost=0.00..0.02 rows=1 width=0) (actual time=0.025..7507.349 rows=100 loops=1) Total runtime: 12773.371 ms (2 rows) ubuntu=# explain analyze insert into test_disk values (generate_series(1,100),random()::text); QUERY PLAN - Result (cost=0.00..0.02 rows=1 width=0) (actual time=0.025..7948.205 rows=100 loops=1) Total runtime: 16902.042 ms (2 rows) ubuntu=# explain analyze insert into test_disk_temp values (generate_series(1,100),random()::text); QUERY PLAN - Result (cost=0.00..0.02 rows=1 width=0) (actual time=0.018..8135.287 rows=100 loops=1) Total runtime: 13716.049 ms (2 rows) So, let's see in a brief: standard table on ram: 22836.532 standard table on disk: 16902.042 temp table on ram: 12773.371 temp table on disk: 13716.049 -- Emanuel Calvo Franco Sumate al ARPUG ! (www.postgres-arg.org - www.arpug.com.ar) ArPUG / AOSUG Member Postgresql Support Admin -- 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] Number Conversion Function
On Mon, Apr 13, 2009 at 2:40 PM, Tino Wildenhain t...@wildenhain.de wrote: justin wrote: Tom Lane wrote: Tino Wildenhain t...@living-examples.com writes: I would not recommend to do this within the database. Thats typical a job for your presentation layer. ... but having said that, I think the money datatype has a function for this. Whether that's of any use to you I dunno; money is pretty restrictive about what it can handle. regards, tom lane I disagree the database is the wrong place, there are cases it makes sense. Which cases would that be? What if you had several different 'presentation layers' interfacing with the database? Say, a internal web app, some reporting programs used by sales, dept, the old delphi program that no one ever bothered to rewrite, etc. Do you really want to implement the formatting function in all those places? as a general rule, the more i do in the database, the easier things are for me in the long run. merlin -- 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] Number Conversion Function
justin wrote: Tom Lane wrote: Tino Wildenhain t...@living-examples.com writes: I would not recommend to do this within the database. Thats typical a job for your presentation layer. ... but having said that, I think the money datatype has a function for this. Whether that's of any use to you I dunno; money is pretty restrictive about what it can handle. regards, tom lane I disagree the database is the wrong place, there are cases it makes sense. Which cases would that be? Regards Tino -- 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] Number Conversion Function
Tino Wildenhain wrote: justin wrote: I disagree the database is the wrong place, there are cases it makes sense. Which cases would that be? Regards Tino Report engines that don't have this ability . I use for check writing. To come think, I don't know of UI framework or report engine that has this ability. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 'no pg_hba.conf entry for host [local], user postgres, database postgres'...
When I try to run % psql as the postgres user, I get the error psql: FATAL: no pg_hba.conf entry for host [local], user postgres, database postgres, SSL off I don't understand this error, since the pg_hba.conf file includes the line: local all all ident sameuser What could explain this behavior? Incidentally, is there a general way to get positive confirmation that a particular pg_hba.conf has been re-read upon bouncing the server with kill -HUP PID ? TIA! kynn
[GENERAL] In memory Database for postgres
Hello, Thanks for your reply,but what I am actually looking for is database should be an in-memory database and at the same i want to store that data into disk so that data won't be lost when the system restarts or in case of power failure. Can you guys tell me the procedure how to do this? your help will he greatly appreciated. Thanks, Avin. From: Emanuel Calvo Franco postgres@gmail.com To: John R Pierce pie...@hogranch.com Cc: postgresql Forums pgsql-general@postgresql.org Sent: Monday, April 13, 2009 11:54:45 AM Subject: Re: [GENERAL] In memory Database for postgres 2009/4/12 John R Pierce pie...@hogranch.com: aravind chandu wrote: I created in-memory database but the problem is all the data will be accessed from main memory .when ever the system is restarted the entire data that is in the tables will lost.Is there any way to dump all the data in to local hard disk before restarting the system or any similar method to save the data in to a permanent storage. memory is volatile, disk is persistent. if you want persistent databases, I recommend storing them on disk. ubuntu=# create table test_ram (i integer, name text) tablespace ram_space; CREATE TABLE ubuntu=# create temp table test_ram_temp (i integer, name text) tablespace ram_space; CREATE TABLE ubuntu=# create temp table test_disk_temp (i integer, name text); CREATE TABLE ubuntu=# create table test_disk (i integer, name text); CREATE TABLE ubuntu=# explain analyze insert into test_ram values (generate_series(1,100),random()::text); QUERY PLAN - Result (cost=0.00..0.02 rows=1 width=0) (actual time=0.019..9354.014 rows=100 loops=1) Total runtime: 22836.532 ms (2 rows) ubuntu=# explain analyze insert into test_ram_temp values (generate_series(1,100),random()::text); QUERY PLAN - Result (cost=0.00..0.02 rows=1 width=0) (actual time=0.025..7507.349 rows=100 loops=1) Total runtime: 12773.371 ms (2 rows) ubuntu=# explain analyze insert into test_disk values (generate_series(1,100),random()::text); QUERY PLAN - Result (cost=0.00..0.02 rows=1 width=0) (actual time=0.025..7948.205 rows=100 loops=1) Total runtime: 16902.042 ms (2 rows) ubuntu=# explain analyze insert into test_disk_temp values (generate_series(1,100),random()::text); QUERY PLAN - Result (cost=0.00..0.02 rows=1 width=0) (actual time=0.018..8135.287 rows=100 loops=1) Total runtime: 13716.049 ms (2 rows) So, let's see in a brief: standard table on ram: 22836.532 standard table on disk: 16902.042 temp table on ram: 12773.371 temp table on disk: 13716.049 -- Emanuel Calvo Franco Sumate al ARPUG ! (www.postgres-arg.org - www.arpug.com.ar) ArPUG / AOSUG Member Postgresql Support Admin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL in the cloud now up on media.postgresql.org
Greetings, The video from the April 8, 2009 SFPUG meeting, PostgreSQL in the Cloud, is now available for viewing or download from the media.postgresql.org server: http://media.postgresql.org/sfpug/sfpug-cloud-20090408.mov Thanks to Josh Berkus for his organizational talents, and Dirk Jagdmann for running the live streaming during the event. -- Xof -- 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] In memory Database for postgres
2009/4/13 aravind chandu avin_frie...@yahoo.com: Hello, Thanks for your reply,but what I am actually looking for is database should be an in-memory database and at the same i want to store that data into disk so that data won't be lost when the system restarts or in case of power failure. Can you guys tell me the procedure how to do this? your help will he greatly appreciated. Thanks, Avin. Oh, well... there is a way but is not recomended. So you can make the tables on memory and with a trigger update on disk... you can call this inverted materialized views (because in general you update the views on memory but not in the disk). But there is a problem... if you update on memory and a shutdown occurs and the trigger didn't start... you lost this record :( I must say that you That's because i prefer have a database in disk and have updatable views in memory :) -- Emanuel Calvo Franco Sumate al ARPUG ! (www.postgres-arg.org - www.arpug.com.ar) ArPUG / AOSUG Member Postgresql Support Admin -- 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] In memory Database for postgres
On Mon, Apr 13, 2009 at 2:06 PM, aravind chandu avin_frie...@yahoo.com wrote: Hello, Thanks for your reply,but what I am actually looking for is database should be an in-memory database and at the same i want to store that data into disk so that data won't be lost when the system restarts or in case of power failure. Can you guys tell me the procedure how to do this? your help will he greatly appreciated. But that's just the point people have been making. A small enough db will be cached completely in memory, and the only time you'll have to access the disks is the first read, and during writes, which can be made to happen mostly after the fact and not influence the rest of the db. You're trying to reinvent a wheel to solve a non-existent problem. -- 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] In memory Database for postgres
On Mon, 2009-04-13 at 17:36 -0300, Emanuel Calvo Franco wrote: Thanks for your reply,but what I am actually looking for is database should be an in-memory database and at the same i want to store that data into disk so that data won't be lost when the system restarts or in case of power failure. Can you guys tell me the procedure how to do this? your help will he greatly appreciated. If you want the writes to be preserved across shutdown, the writes must go to disk. If that's too expensive, and you are only concerned about preserving the writes after a clean shutdown, you can turn off fsync (but then your data will be corrupt after a crash). The reads will usually come from memory anyway (as long as you have enough memory), even if the tables are stored on disk. So what's wrong with just using normal tables? So you can make the tables on memory and with a trigger update on disk... you can call this inverted materialized views (because in general you update the views on memory but not in the disk). How does that help? Don't you have the same number of disk writes that way? But there is a problem... if you update on memory and a shutdown occurs and the trigger didn't start... you lost this record :( I must say that you Triggers are transactional. Either they all fire, and all the updates happen, or none do. Aravind, Scott asked the most important question: what problem are you trying to solve? Regards, Jeff Davis -- 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] 'no pg_hba.conf entry for host [local], user postgres, database postgres'...
On Mon, Apr 13, 2009 at 2:01 PM, Kynn Jones kyn...@gmail.com wrote: When I try to run % psql as the postgres user, I get the error psql: FATAL: no pg_hba.conf entry for host [local], user postgres, database postgres, SSL off I don't understand this error, since the pg_hba.conf file includes the line: local all all ident sameuser What could explain this behavior? What do all the lines look like together? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] BUMP: Using foreign key constraint to eliminate unnecessary joins in view
A last try on this question. In the absence of any response, I'll assume
the postgresql query optimizer simply doesn't take foreign keys into
account in this way.
Original Message
Subject: Using foreign key constraint to eliminate unnecessary joins in
view
Date: Sun, 29 Mar 2009 14:00:53 -0400
From: Paul Wehr sf_psql_gene...@informationsoftworks.com
To: pgsql-general@postgresql.org
I could be wrong, but I would swear I was once able to set up my foreign
key constraints so that the optimizer would not bother looking up rows in
tables that did not contribute to the result set. This is useful because I
can set up a single view that joins all the related tables together, but
can still get the performance of a view that only includes the tables I'm
interested in at the time.
Here is a simple example of what I mean:
-- begin example
create table test1 (
a serial not null,
b text,
primary key (a)
);
create table test2 (
c text,
d integer,
constraint test2_fk foreign key (d) references test1(a) not deferrable
;
insert into test1 (b) values ('test1');
insert into test1 (b) values ('test2');
insert into test2 values ('hey',1);
insert into test2 values ('stuff',1);
insert into test2 values ('thing',2);
explain
select c
from test2, test1
where test1.a = test2.d
--- end example
Explain plan from postgresql 8.3.7:
Hash Join (cost=37.67..76.89 rows=1230 width=32)
Hash Cond: (test2.d = test1.a)
- Seq Scan on test2 (cost=0.00..22.30 rows=1230 width=36)
- Hash (cost=22.30..22.30 rows=1230 width=4)
- Seq Scan on test1 (cost=0.00..22.30 rows=1230 width=4)
I am looking for a way to get the plan to show only a seq scan of test2,
since test1 does not contribute any columns, and we know from the
not-deferrable-not-null-primary-key that there will always be exactly one
match.
Am I just missing something obvious, or does postgresql (currently) not do
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] PostgreSQL in the cloud now up on media.postgresql.org
On Mon, 13 Apr 2009 13:11:23 -0700 Christophe x...@thebuild.com wrote: http://media.postgresql.org/sfpug/sfpug-cloud-20090408.mov Thanks to Josh Berkus for his organizational talents, and Dirk Jagdmann for running the live streaming during the event. wget says it is a 1Gb file. Is there anything smaller? thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] BUMP: Using foreign key constraint to eliminate unnecessary joins in view
On Mon, Apr 13, 2009 at 1:36 PM, Paul Wehr
sf_psql_gene...@informationsoftworks.com wrote:
A last try on this question. In the absence of any response, I'll assume
the postgresql query optimizer simply doesn't take foreign keys into
account in this way.
Original Message
Subject: Using foreign key constraint to eliminate unnecessary joins in
view
Date: Sun, 29 Mar 2009 14:00:53 -0400
From: Paul Wehr sf_psql_gene...@informationsoftworks.com
To: pgsql-general@postgresql.org
I could be wrong, but I would swear I was once able to set up my foreign
key constraints so that the optimizer would not bother looking up rows in
tables that did not contribute to the result set. This is useful because I
can set up a single view that joins all the related tables together, but
can still get the performance of a view that only includes the tables I'm
interested in at the time.
Here is a simple example of what I mean:
-- begin example
create table test1 (
a serial not null,
b text,
primary key (a)
);
create table test2 (
c text,
d integer,
constraint test2_fk foreign key (d) references test1(a) not deferrable
;
insert into test1 (b) values ('test1');
insert into test1 (b) values ('test2');
insert into test2 values ('hey',1);
insert into test2 values ('stuff',1);
insert into test2 values ('thing',2);
explain
select c
from test2, test1
where test1.a = test2.d
--- end example
Explain plan from postgresql 8.3.7:
Hash Join (cost=37.67..76.89 rows=1230 width=32)
Hash Cond: (test2.d = test1.a)
- Seq Scan on test2 (cost=0.00..22.30 rows=1230 width=36)
- Hash (cost=22.30..22.30 rows=1230 width=4)
- Seq Scan on test1 (cost=0.00..22.30 rows=1230 width=4)
I am looking for a way to get the plan to show only a seq scan of test2,
since test1 does not contribute any columns, and we know from the
not-deferrable-not-null-primary-key that there will always be exactly one
match.
Am I just missing something obvious, or does postgresql (currently) not do
that?
Have you tried an index on test1.c?
--
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] PostgreSQL in the cloud now up on media.postgresql.org
On Mon, Apr 13, 2009 at 2:59 PM, Ivan Sergio Borgonovo m...@webthatworks.it wrote: On Mon, 13 Apr 2009 13:11:23 -0700 Christophe x...@thebuild.com wrote: http://media.postgresql.org/sfpug/sfpug-cloud-20090408.mov Thanks to Josh Berkus for his organizational talents, and Dirk Jagdmann for running the live streaming during the event. wget says it is a 1Gb file. Is there anything smaller? thanks If you'd like I can dl and rencode it quite a bit smaller I'm sure. It'll take several hours to dl, process, then upload, but that's mostly machine time, right? :) -- 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] PostgreSQL in the cloud now up on media.postgresql.org
Scott Marlowe wrote: ... http://media.postgresql.org/sfpug/sfpug-cloud-20090408.mov Thanks to Josh Berkus for his organizational talents, and Dirk Jagdmann for running the live streaming during the event. wget says it is a 1Gb file. Is there anything smaller? If you'd like I can dl and rencode it quite a bit smaller I'm sure. It'll take several hours to dl, process, then upload, but that's mostly machine time, right? :) The people doing the actual work will correct me if I'm wrong, but I believe it will be up on vimeo.com fairly soon as last month's is. 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] PostgreSQL in the cloud now up on media.postgresql.org
On Mon, 13 Apr 2009 14:36:49 -0700 Steve Crawford scrawf...@pinpointresearch.com wrote: Scott Marlowe wrote: http://media.postgresql.org/sfpug/sfpug-cloud-20090408.mov Thanks to Josh Berkus for his organizational talents, and Dirk Jagdmann for running the live streaming during the event. wget says it is a 1Gb file. Is there anything smaller? If you'd like I can dl and rencode it quite a bit smaller I'm sure. It'll take several hours to dl, process, then upload, but that's mostly machine time, right? :) I was just thinking about a public official place for everybody. I'm going to sleep so I'm not going to miss the bandwidth required for downloading the full stuff ;) but still then it will have to find it's way back to a public place. The people doing the actual work will correct me if I'm wrong, but I believe it will be up on vimeo.com fairly soon as last month's is. Can vimeo movie be downloaded? thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] PostgreSQL in the cloud now up on media.postgresql.org
On Apr 13, 2009, at 1:59 PM, Ivan Sergio Borgonovo wrote: wget says it is a 1Gb file. Is there anything smaller? Since I have the master right here, I'll be happy to reencode it to a smaller size (250mb is probably the reasonable lower limit before the video quality reaches the point that we might as well just post the audio :) ). -- 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] PostgreSQL in the cloud now up on media.postgresql.org
On Mon, 2009-04-13 at 23:48 +0200, Ivan Sergio Borgonovo wrote: was just thinking about a public official place for everybody. I'm going to sleep so I'm not going to miss the bandwidth required for downloading the full stuff ;) but still then it will have to find it's way back to a public place. The people doing the actual work will correct me if I'm wrong, but I believe it will be up on vimeo.com fairly soon as last month's is. Can vimeo movie be downloaded? Yes Vimeo can download and I will have it up soon. Joshua D Drkae thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] PostgreSQL in the cloud now up on media.postgresql.org
On Apr 13, 2009, at 2:54 PM, Joshua D. Drake wrote: Yes Vimeo can download and I will have it up soon. Thank you! My work here is done. :) -- 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] PostgreSQL in the cloud now up on media.postgresql.org
On Mon, 2009-04-13 at 14:44 -0700, Christophe wrote: On Apr 13, 2009, at 1:59 PM, Ivan Sergio Borgonovo wrote: wget says it is a 1Gb file. Is there anything smaller? Since I have the master right here, I'll be happy to reencode it to a smaller size (250mb is probably the reasonable lower limit before the video quality reaches the point that we might as well just post the audio :) ). We really don't want anything that is less than about 700MB. After that it is just youtube crap. At least with 700MB we get SVCD quality. Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] BUMP: Using foreign key constraint to eliminate unnecessary joins in view
On Mon, Apr 13, 2009 at 2:36 PM, Paul Wehr sf_psql_gene...@informationsoftworks.com wrote: I am looking for a way to get the plan to show only a seq scan of test2, since test1 does not contribute any columns, and we know from the not-deferrable-not-null-primary-key that there will always be exactly one match. Am I just missing something obvious, or does postgresql (currently) not do that? postgres will look at all tables involved, i think there is work to make it smart enough to make what you describe but that's in the future... -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] BUMP: Using foreign key constraint to eliminate unnecessary joins in view
Paul Wehr sf_psql_gene...@informationsoftworks.com writes: A last try on this question. In the absence of any response, I'll assume the postgresql query optimizer simply doesn't take foreign keys into account in this way. It does not, and never has, and yes there's a TODO item about that. 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] In memory Database for postgres
If you need to run some database really fast, try to put only all your indexes onto ram disk. Look here... http://www.linux.com/feature/142658 They use SSD to store indexes (not data) for postgresql. A think the same conclusions should apply for ram disk too. And in wrost case (power off for RAM disk or wear out for SSD) you need only a reindex to build your indexes again Scott Marlowe napsal(a): On Mon, Apr 13, 2009 at 2:06 PM, aravind chandu avin_frie...@yahoo.com wrote: Hello, Thanks for your reply,but what I am actually looking for is database should be an in-memory database and at the same i want to store that data into disk so that data won't be lost when the system restarts or in case of power failure. Can you guys tell me the procedure how to do this? your help will he greatly appreciated. But that's just the point people have been making. A small enough db will be cached completely in memory, and the only time you'll have to access the disks is the first read, and during writes, which can be made to happen mostly after the fact and not influence the rest of the db. You're trying to reinvent a wheel to solve a non-existent problem. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Wrap around vacuum
Please copy el...@varlena.com on responses. I am way behind on my general mail. autovacuum does special stuff when there is an approaching transaction wrap around. We have a lot of transactions and wrap around as much as once a week. We'd like to invoke vacuum to do the special stuff explicitly so that we can do it during lower traffic times. Like weekends rather than weekdays. How can we do that? elein el...@varlena.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] Wrap around vacuum
elein wrote: Hi Elein, autovacuum does special stuff when there is an approaching transaction wrap around. We have a lot of transactions and wrap around as much as once a week. We'd like to invoke vacuum to do the special stuff explicitly so that we can do it during lower traffic times. Like weekends rather than weekdays. What release are we talking about? 8.1, 8.2 and 8.3 are all different in this regard. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
