Re: [GENERAL] Collapsing multiple subqueries into one
Thanks Royce. I put together another query using a WITH statement that's also working: WITH v AS ( SELECT item_id, type, direction, array_agg(user_id) as user_ids FROM votes WHERE root_id = 5305 GROUP BY type, direction, item_id ORDER BY type, direction, item_id ) SELECT *, (SELECT user_ids from v where item_id = i.id AND type = 0 AND direction = 1) as upvoters, (SELECT user_ids from v where item_id = i.id AND type = 0 AND direction = -1) as downvoters, (SELECT user_ids from v where item_id = i.id AND type = 1) as favoriters FROM items i WHERE root_id = 5305 ORDER BY id It feels more sensible to me, but it's slightly slower than my initial attempt (15 ms vs. 13 ms, when running as a prepared statement to avoid any query parsing overhead, and averaging the time over several thousand queries). I'm not sure why...? On Tue, Aug 23, 2011 at 8:14 PM, Royce Ausburn wrote: > This might help you: > > http://www.postgresql.org/docs/8.4/static/queries-with.html > > On 24/08/2011, at 9:54 AM, Chris Hanks wrote: > >> I have two tables: >> >> CREATE TABLE items >> ( >> root_id integer NOT NULL, >> id serial NOT NULL, >> -- Other fields... >> >> CONSTRAINT items_pkey PRIMARY KEY (root_id, id) >> ) >> >> CREATE TABLE votes >> ( >> root_id integer NOT NULL, >> item_id integer NOT NULL, >> user_id integer NOT NULL, >> type smallint NOT NULL, >> direction smallint, >> >> CONSTRAINT votes_pkey PRIMARY KEY (root_id, item_id, user_id, type), >> CONSTRAINT votes_root_id_fkey FOREIGN KEY (root_id, item_id) >> REFERENCES items (root_id, id) MATCH SIMPLE >> ON UPDATE CASCADE ON DELETE CASCADE, >> -- Other constraints... >> ) >> >> I'm trying to, in a single query, pull out all items of a particular >> root_id along with a few arrays of user_ids of the users who voted in >> particular ways. The following query does what I need: >> >> SELECT *, >> ARRAY(SELECT user_id from votes where root_id = i.root_id AND >> item_id = i.id AND type = 0 AND direction = 1) as upvoters, >> ARRAY(SELECT user_id from votes where root_id = i.root_id AND >> item_id = i.id AND type = 0 AND direction = -1) as downvoters, >> ARRAY(SELECT user_id from votes where root_id = i.root_id AND >> item_id = i.id AND type = 1) as favoriters >> FROM items i >> WHERE root_id = 1 >> ORDER BY id >> >> The problem is that I'm using three subqueries to get the information >> I need when it seems like I should be able to do the same in one. I >> thought that Postgres (I'm using 8.4) might be smart enough to >> collapse them all into a single query for me, but looking at the >> explain output in pgAdmin it looks like that's not happening - it's >> running multiple primary key lookups on the votes table instead. I >> feel like I could rework this query to be more efficient, but I'm not >> sure how. >> >> Any pointers? >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] JDBC Connection Errors
On Tue, 2011-08-23 at 19:38 -0400, Dave Cramer wrote: > The only difference JDBC has over psql is that it has to connect via > tcpip. Not sure about pgadmin. > pgAdmin is like psql on this. It can use TCP connections, and socket connections. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.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] postgresql server crash on windows 7 when using plpython
Yes, Now I have removed python 2.7. Restarted the postgresql. When I call the above mentioned function, now server doesn't crash. It is running, but I get the error 'No connection to the server'. I am using PgAdmin to work with Postgresql. I tried to execute a simple function to show python version or path, but still I am getting the same error. I tried to execute other plpython function but still the same problem. I can use other plsql and sql functions correctly. When checked the log file it have entries like this: 2011-08-24 11:19:57 IST LOG: database system was interrupted; last known up at 2011-08-10 22:25:38 IST 2011-08-24 11:19:57 IST LOG: database system was not properly shut down; automatic recovery in progress 2011-08-24 11:19:57 IST FATAL: the database system is starting up 2011-08-24 11:19:57 IST LOG: consistent recovery state reached at 0/424E9800 2011-08-24 11:19:57 IST LOG: redo starts at 0/424E9800 2011-08-24 11:19:57 IST LOG: record with zero length at 0/424EFC70 2011-08-24 11:19:57 IST LOG: redo done at 0/424EFC30 2011-08-24 11:19:57 IST LOG: last completed transaction was at log time 2011-08-10 22:27:35.06+05:30 2011-08-24 11:19:57 IST LOG: database system is ready to accept connections 2011-08-24 11:19:58 IST LOG: autovacuum launcher started ImportError: No module named site ImportError: No module named site ImportError: No module named site ImportError: No module named site ImportError: No module named site ImportError: No module named site ImportError: No module named site This last line is added every time I call any plpython function. Here is the simple plpython function. CREATE OR REPLACE FUNCTION software.pyver() RETURNS text AS $BODY$ import sys #return sys.version return sys.path $BODY$ LANGUAGE plpythonu VOLATILE COST 100; What is the problem? Chaitany Kulkarni On Mon, Aug 22, 2011 at 8:08 PM, Adrian Klaver wrote: > On Monday, August 22, 2011 7:24:30 am c k wrote: > > No i didn't removed any thing. Only I have installed python 2.6. And then > > tried to create plpythonu. It got created, but when tries to execute > > already mentioned function server crashes. > > > > I didn't have any clue. > > My guess is this is the clue: > > "First I installed python 2.7 and then added python installation > path to windows PATH variable. ..." > > It is very possible you have a version cross reference going on. In other > words > pl/pythonu is being compiled against one version of Python, but run against > another. If it is possible I would remove the Python 2.7 installation or at > least the references to it and then recompile pl/pythonu. > > > > > > Thanks and regards. > > > > Chaitany Kulkarni > > > > -- > Adrian Klaver > adrian.kla...@gmail.com >
Re: [GENERAL] documentation suggestion
Bruce Momjian wrote: Rob Sargent wrote: Apologies if this is the wrong forum. If there has been a suggestion to get the "Up" hyperlink placed also at/near the top of the page, please add my vote. Else could this be considered as a feature request? I have talked to Peter Eisentraut on several occasions during the past nine months requesting these changes. Peter, can you make this change or give me a hint on how I can do it? Is the build system of the web-site also downloadable? I could possibly take a peek. (In a couple of weeks, that is. Vaction.) -- 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 on SSD
Hi, On 12 August 2011 14:57, Greg Smith wrote: >> I'm about to evaluate this SSD card: FusionIO ioDrive Duo [1]. The >> main reason for this experiment is to see if SSD can significantly >> improve query performance The result is that FusionIO will help to our queries which was expected. Most of the long running queries return data between 5 and 30 sec range which is very good. The rest of the queries is super fast but aggregates queries need sometimes several minutes. Anyway, the overal performance is satisfactory and the rest could be fixed by redesigning aggregates (keep/rollover top N counts not everything) >> Database size is around ~1.4TB. Main tables occupied around 1/3 >> (450GB, ~220mil rows) and aggregated data occupied 2/3 (900GB). All >> indexes are on separate table space (~550GB) The redesign mentioned above can reduce aggregated data size to size between 1/3 and 1/4 of the current size (and speed up queries). I've tried to change several settings (work_mem, shared_buffers, random/seq/... costs) but I wasn't able to get better benchmark results. Our schema is very simple and query execution plan is reasonable. >> checkpoint_segments | 48 >> maintenance_work_mem | 256MB >> shared_buffers | 9GB >> wal_buffers | 50MB >> work_mem | 256MB >> > > checkpoint_segments should be higher, at least 64 and probably 128 to 256. > shared_buffers should be lower (at most 8GB, maybe even less). > maintenance_work_mem should be 1 to 2GB on a server with 80GB of RAM. > There's no proven benefit to increasing wal_buffers over 16MB. I think this is the part which I have to look at... Thanks, -- Ondrej Ivanic (ondrej.iva...@gmail.com) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] documentation suggestion
Rob Sargent wrote: > Apologies if this is the wrong forum. > > If there has been a suggestion to get the "Up" hyperlink placed also > at/near the top of the page, please add my vote. Else could this be > considered as a feature request? I have talked to Peter Eisentraut on several occasions during the past nine months requesting these changes. Peter, can you make this change or give me a hint on how I can do it? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Collapsing multiple subqueries into one
This might help you: http://www.postgresql.org/docs/8.4/static/queries-with.html On 24/08/2011, at 9:54 AM, Chris Hanks wrote: > I have two tables: > > CREATE TABLE items > ( > root_id integer NOT NULL, > id serial NOT NULL, > -- Other fields... > > CONSTRAINT items_pkey PRIMARY KEY (root_id, id) > ) > > CREATE TABLE votes > ( > root_id integer NOT NULL, > item_id integer NOT NULL, > user_id integer NOT NULL, > type smallint NOT NULL, > direction smallint, > > CONSTRAINT votes_pkey PRIMARY KEY (root_id, item_id, user_id, type), > CONSTRAINT votes_root_id_fkey FOREIGN KEY (root_id, item_id) > REFERENCES items (root_id, id) MATCH SIMPLE > ON UPDATE CASCADE ON DELETE CASCADE, > -- Other constraints... > ) > > I'm trying to, in a single query, pull out all items of a particular > root_id along with a few arrays of user_ids of the users who voted in > particular ways. The following query does what I need: > > SELECT *, > ARRAY(SELECT user_id from votes where root_id = i.root_id AND > item_id = i.id AND type = 0 AND direction = 1) as upvoters, > ARRAY(SELECT user_id from votes where root_id = i.root_id AND > item_id = i.id AND type = 0 AND direction = -1) as downvoters, > ARRAY(SELECT user_id from votes where root_id = i.root_id AND > item_id = i.id AND type = 1) as favoriters > FROM items i > WHERE root_id = 1 > ORDER BY id > > The problem is that I'm using three subqueries to get the information > I need when it seems like I should be able to do the same in one. I > thought that Postgres (I'm using 8.4) might be smart enough to > collapse them all into a single query for me, but looking at the > explain output in pgAdmin it looks like that's not happening - it's > running multiple primary key lookups on the votes table instead. I > feel like I could rework this query to be more efficient, but I'm not > sure how. > > Any pointers? > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] JDBC Connection Errors
Everything is remote. I thought of the IPv6 thing, but that seems unlikely - all connections are coming from the same system. Still, we'll ask them and try to get some more details about things like that. --- === Samuel Nelson Consistent State www.consistentstate.com 303-955-0509 === On Tue, Aug 23, 2011 at 5:46 PM, Adrian Klaver wrote: > On Tuesday, August 23, 2011 3:47:33 pm Sam Nelson wrote: >> Hi list, >> >> A client is hitting an issue with JDBC: >> org.postgresql.util.PSQLException: Connection refused. Check that the >> hostname and port are correct and that the postmaster is accepting >> TCP/IP connections. >> >> -pg_hba.conf is set to trust 0.0.0.0/0 (IPv4 only) >> -listen_addresses is * >> -I can find no evidence of iptables running on the server. >> -PGAdmin connects just fine. >> -psql connects just fine. > > Are PgAdmin and psql local and the Java client remote? > Is the Java client connecting using IPv6 ? > >> -I can find no errors in the log file from that day for the user that >> the client is trying to log in as. >> >> We're working on getting access to more details about how they're >> trying to connect, but in the mean time, does anyone know if JDBC has >> any issues connecting that psql and PGAdmin wouldn't have? Is it >> possible that JDBC is somehow susceptible to connection issues that >> JDBC and psql are not? >> --- >> === >> Samuel Nelson >> Consistent State >> www.consistentstate.com >> 303-955-0509 >> === > > -- > Adrian Klaver > adrian.kla...@gmail.com > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] JDBC Connection Errors
On Tuesday, August 23, 2011 6:58:13 pm Sam Nelson wrote: > Everything is remote. I thought of the IPv6 thing, but that seems > unlikely - all connections are coming from the same system. The easy way to test is to throw in an IPv6 rule that matches the IPv4 rule into pg_hba.conf. > > Still, we'll ask them and try to get some more details about things like > that. --- -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Wal archiving and streaming replication
On Tue, Aug 23, 2011 at 10:49 PM, Ray Stell wrote: > On Tue, Aug 23, 2011 at 08:30:55PM +0530, Raghavendra wrote: > > > > Is it a best practice to keep cluster in Archive_mode = on and setup > > streaming replication or just leave archive_mode=off? > > Depends. The reason for creating WAL is in case they are needed for > recovery. > In the event that the stby host goes down, if bringing it back into service > exceeds wal_keep_segments on the primary then you would need to rebuild > the standby. This is a local decision and should be made based on business > rules. > Thanks Ray. --Raghav
[GENERAL] Collapsing multiple subqueries into one
I have two tables: CREATE TABLE items ( root_id integer NOT NULL, id serial NOT NULL, -- Other fields... CONSTRAINT items_pkey PRIMARY KEY (root_id, id) ) CREATE TABLE votes ( root_id integer NOT NULL, item_id integer NOT NULL, user_id integer NOT NULL, type smallint NOT NULL, direction smallint, CONSTRAINT votes_pkey PRIMARY KEY (root_id, item_id, user_id, type), CONSTRAINT votes_root_id_fkey FOREIGN KEY (root_id, item_id) REFERENCES items (root_id, id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE, -- Other constraints... ) I'm trying to, in a single query, pull out all items of a particular root_id along with a few arrays of user_ids of the users who voted in particular ways. The following query does what I need: SELECT *, ARRAY(SELECT user_id from votes where root_id = i.root_id AND item_id = i.id AND type = 0 AND direction = 1) as upvoters, ARRAY(SELECT user_id from votes where root_id = i.root_id AND item_id = i.id AND type = 0 AND direction = -1) as downvoters, ARRAY(SELECT user_id from votes where root_id = i.root_id AND item_id = i.id AND type = 1) as favoriters FROM items i WHERE root_id = 1 ORDER BY id The problem is that I'm using three subqueries to get the information I need when it seems like I should be able to do the same in one. I thought that Postgres (I'm using 8.4) might be smart enough to collapse them all into a single query for me, but looking at the explain output in pgAdmin it looks like that's not happening - it's running multiple primary key lookups on the votes table instead. I feel like I could rework this query to be more efficient, but I'm not sure how. Any pointers? -- 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] JDBC Connection Errors
On Tuesday, August 23, 2011 3:47:33 pm Sam Nelson wrote: > Hi list, > > A client is hitting an issue with JDBC: > org.postgresql.util.PSQLException: Connection refused. Check that the > hostname and port are correct and that the postmaster is accepting > TCP/IP connections. > > -pg_hba.conf is set to trust 0.0.0.0/0 (IPv4 only) > -listen_addresses is * > -I can find no evidence of iptables running on the server. > -PGAdmin connects just fine. > -psql connects just fine. Are PgAdmin and psql local and the Java client remote? Is the Java client connecting using IPv6 ? > -I can find no errors in the log file from that day for the user that > the client is trying to log in as. > > We're working on getting access to more details about how they're > trying to connect, but in the mean time, does anyone know if JDBC has > any issues connecting that psql and PGAdmin wouldn't have? Is it > possible that JDBC is somehow susceptible to connection issues that > JDBC and psql are not? > --- > === > Samuel Nelson > Consistent State > www.consistentstate.com > 303-955-0509 > === -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] JDBC Connection Errors
The only difference JDBC has over psql is that it has to connect via tcpip. Not sure about pgadmin. Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Tue, Aug 23, 2011 at 6:47 PM, Sam Nelson wrote: > Hi list, > > A client is hitting an issue with JDBC: > org.postgresql.util.PSQLException: Connection refused. Check that the > hostname and port are correct and that the postmaster is accepting > TCP/IP connections. > > -pg_hba.conf is set to trust 0.0.0.0/0 (IPv4 only) > -listen_addresses is * > -I can find no evidence of iptables running on the server. > -PGAdmin connects just fine. > -psql connects just fine. > -I can find no errors in the log file from that day for the user that > the client is trying to log in as. > > We're working on getting access to more details about how they're > trying to connect, but in the mean time, does anyone know if JDBC has > any issues connecting that psql and PGAdmin wouldn't have? Is it > possible that JDBC is somehow susceptible to connection issues that > JDBC and psql are not? > --- > === > Samuel Nelson > Consistent State > www.consistentstate.com > 303-955-0509 > === > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] View "Caching" - Is this Known and Expected Behavior?
-Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Tuesday, August 23, 2011 5:51 PM To: David Johnston Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] View "Caching" - Is this Known and Expected Behavior? "David Johnston" writes: > I am wondering whether the behavior I am observing is expected. No, it isn't. Please provide a concrete test case. regards, tom lane - OK, I found the true cause of the behavior; schemas. CREATE OR REPLACE only looks at the first schema listed for a match; if one is not present it stops searching and instead immediately chooses the CREATE option. The following script results in two "testview" VIEWS; one in "test1" and one in "public". With the specification of "OR REPLACE" it would make sense that the entire search_path would be searched for an object to replace BEFORE a new object is created in the first schema listed. Since changing this behavior is likely to be difficult having a "REPLACE OBJECT" command would make sense - one that errors if a matching object cannot be located. In my situation my "CREATE OR REPLACE" made me a second VIEW which my direct call used but the original VIEW was still being used by the two dependent views. That said, the dependent VIEWS refer to the source view using an unqualified name - so in theory they should have ended up using the newly created VIEW as well. Especially since "CREATE OR REPLACE" is often used interactively it would be somewhat rational to emit a NOTICE indicating which option (CREATE | REPLACE) was picked; and the resultant schema where the replacement was performed. Regardless, depending on which option "search all schemas, replace if not found OR search first listed schema, add if not present" is chosen the relevant documentation sections should probably make it clear how the system determines if "...a view of the same name already exists". My $0.03 David J. CREATE SCHEMA test1; BEGIN; SET LOCAL search_path = test1; CREATE VIEW testview AS SELECT 1 AS resultcol; ; COMMIT; BEGIN; SET LOCAL search_path = public, test1; CREATE OR REPLACE VIEW testview AS SELECT 2 AS resultcol; ; COMMIT; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] documentation suggestion
Apologies if this is the wrong forum. If there has been a suggestion to get the "Up" hyperlink placed also at/near the top of the page, please add my vote. Else could this be considered as a feature request? rjs -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] JDBC Connection Errors
Hi list, A client is hitting an issue with JDBC: org.postgresql.util.PSQLException: Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections. -pg_hba.conf is set to trust 0.0.0.0/0 (IPv4 only) -listen_addresses is * -I can find no evidence of iptables running on the server. -PGAdmin connects just fine. -psql connects just fine. -I can find no errors in the log file from that day for the user that the client is trying to log in as. We're working on getting access to more details about how they're trying to connect, but in the mean time, does anyone know if JDBC has any issues connecting that psql and PGAdmin wouldn't have? Is it possible that JDBC is somehow susceptible to connection issues that JDBC and psql are not? --- === Samuel Nelson Consistent State www.consistentstate.com 303-955-0509 === -- 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] View "Caching" - Is this Known and Expected Behavior?
On Tue, Aug 23, 2011 at 4:36 PM, David Johnston wrote: > Hey All, > > I am wondering whether the behavior I am observing is expected. The rough > scenario I have setup goes as follows (I can likely put together a test > script if that is warranted): > > version > > PostgreSQL 9.0.3 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real > (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit > > Initially: > > VIEW inner := SELECT * FROM complex WHERE filter; > > VIEW outer := SELECT * FROM inner JOIN other; > > > > Now, I discover that the “filter” in the “inner” VIEW is wrong (had > hard-coded a month/year combination during testing) and so I modified the > WHERE clause of the “inner” VIEW. I do this using CREATE OR REPLACE VIEW > inner […] > > > > Now, I can (SELECT * FROM inner) and I get the expected results. However, > if I (SELECT * FROM outer) the query (including the explain), shows me > original “inner” plan and I thus get – in this case – no results (since the > hard-coded date does not match my live data). > > > > Since I did not change the signature of the VIEW the CREATE OR REPLACE > worked as expected. > > > > I have pretty good feel for how/why this is happening (though a precise > explanation is welcomed), and obviously I will need to recreate the > dependent VIEWs, but I am curious whether any efforts have/are being taken > to avoid this issue in the future. One thing that's very important to understand about views in postgres is that they are *mostly* like SQL macros. The view definition is simply injected into the outer query so that if you see this problem using dependent views, you should also see it in a fully expanded query since that is what postgres does under the hood. Note this is not necessarily true for other databases (and when it isn't, using views tends to suck). I say mostly, because there are a few changes postgres makes when parsing and storing the SQL behind views for later use. For example, "select * from foo" is expanded to "select foo.a, foo.b ... from foo" etc. A simple \d+ on the view should give you the sql as postgres sees it post storage. 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] View "Caching" - Is this Known and Expected Behavior?
"David Johnston" writes: > I am wondering whether the behavior I am observing is expected. No, it isn't. Please provide a concrete test case. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] View "Caching" - Is this Known and Expected Behavior?
Hey All, I am wondering whether the behavior I am observing is expected. The rough scenario I have setup goes as follows (I can likely put together a test script if that is warranted): version PostgreSQL 9.0.3 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit Initially: VIEW inner := SELECT * FROM complex WHERE filter; VIEW outer := SELECT * FROM inner JOIN other; Now, I discover that the "filter" in the "inner" VIEW is wrong (had hard-coded a month/year combination during testing) and so I modified the WHERE clause of the "inner" VIEW. I do this using CREATE OR REPLACE VIEW inner [.] Now, I can (SELECT * FROM inner) and I get the expected results. However, if I (SELECT * FROM outer) the query (including the explain), shows me original "inner" plan and I thus get - in this case - no results (since the hard-coded date does not match my live data). Since I did not change the signature of the VIEW the CREATE OR REPLACE worked as expected. I have pretty good feel for how/why this is happening (though a precise explanation is welcomed), and obviously I will need to recreate the dependent VIEWs, but I am curious whether any efforts have/are being taken to avoid this issue in the future. Thank you for your attention in this matter, David J.
Re: [GENERAL] Wal archiving and streaming replication
On Tue, Aug 23, 2011 at 08:30:55PM +0530, Raghavendra wrote: > > Is it a best practice to keep cluster in Archive_mode = on and setup > streaming replication or just leave archive_mode=off? Depends. The reason for creating WAL is in case they are needed for recovery. In the event that the stby host goes down, if bringing it back into service exceeds wal_keep_segments on the primary then you would need to rebuild the standby. This is a local decision and should be made based on business rules. -- 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] Executing more than one function.
Il giorno mar, 23/08/2011 alle 16.30 +0100, f vf ha scritto: [...] > it takes more time than if I execute one function at the time and sum > the execution times of each one: > > > BEGIN; > SELECT functionX(); > COMMIT; > You should probably accout a time for the COMMIT operation. In one case you commit only once, while in other case you commit three times. Bye, Giuseppe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Executing more than one function.
Hello, I have a set of plsql functions that I want to execute. All these functions perform queries and insert data in the database and I need to execute them in order. What I have noticed is that if I execute this script BEGIN; SELECT function1(); COMMIT; BEGIN; SELECT function2(); COMMIT; BEGIN; SELECT function3(); COMMIT; it takes more time than if I execute one function at the time and sum the execution times of each one: BEGIN; SELECT functionX(); COMMIT; In some cases if there are several millions of tuples in the database running all the rules in the same script takes 10 times more than running one at the time. Can anyone explain why this happens and how can I prevent it? Thanks for the help. Filipe
Re: [GENERAL] Wal archiving and streaming replication
On Tue, Aug 23, 2011 at 7:17 PM, Ray Stell wrote: > On Tue, Aug 23, 2011 at 06:23:58AM -0700, Adrian Klaver wrote: > > On Tuesday, August 23, 2011 4:14:15 am Ray Stell wrote: > > > On Tue, Aug 23, 2011 at 02:01:10AM -0700, alexondi wrote: > > > > Hi! > > > > Do I need setup wal archiving (archiving_mode = on) setup when I use > > > > streaming replication? > > > > > > yes > > > > http://www.postgresql.org/docs/current/interactive/high-availability.html > > > > Actually no. Streaming will work without archiving. For a quick > introduction > > see: > > > > > http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial#5_Minutes_to_Simple_Replication > > right, you don't need to. I find it to be a best practice, which is > different. > > Question: Is it a best practice to keep cluster in Archive_mode = on and setup streaming replication or just leave archive_mode=off? --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] COPY FROM (query) in plpgsql
Le mardi 23 août 2011 à 11:29 +0200, Marc Mamin a écrit : > Hello, > > there seems to be no way to use COPY this way, so I guess this is a > feature request... > > this may also help users who tried using COPY FROM STDIN in plpgsql. > > > I have a query with a lot of string manipulation that returns data as > single strings, e.g.: > > > 'a,12,ght,45,1.2' > 'b,13,ght,45,1.1' > 'a,14,ght,45,1.5' > > > in order to save this result into a table, I still need to quote the > string value and use EXECUTE: > If you can write the data to disk, this gets you rid of the quote problem : COPY query TO 'file.csv'; COPY foo FROM 'file.csv' WITH CSV DELIMITER ','; -- Vincent Veyron http://marica.fr/ Logiciel de gestion des sinistres et des contentieux pour le service juridique -- 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] GRANT privileges strange behavior
On Tue, 2011-08-23 at 06:43 -0700, igivanoff wrote: > Hi, > > I have the following situation: > > postgres=# create database foo with encoding = 'UTF8'; > postgres=# \c foo > foo=# CREATE SCHEMA sc; > foo=# ALTER DATABASE foo SET search_path=sc, pg_catalog; > foo=# CREATE ROLE usr LOGIN PASSWORD 'usr' NOINHERIT CREATEDB VALID UNTIL > 'infinity'; > foo=# grant all privileges on all sequences in schema sc to usr; > foo=# CREATE SEQUENCE foo_seq INCREMENT BY 1; > foo=# select relname, relacl from pg_class where relkind = 'S'; > relname | relacl > -+ > foo_seq | > (1 row) > > i.e. even thought I request to have all privileges for all sequences in the > schema, I don't for any new ones. > > This is not a problem for a fresh PostgreSQL database where I can set the > grant at the end of the schema deployment. But if I want to add any new > sequence to the database I need to explicitly add the grant to each new > sequence. > > Is this expected behavior? Sure, GRANT only works on existing objects. > If so is there a work around to my problem so I can get all privileges to my > user for any new sequence in the schema? > ALTER DEFAULT PRIVILEGES (http://www.postgresql.org/docs/9.0/interactive/sql-alterdefaultprivileges.html). -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.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] GRANT privileges strange behavior
Hi, I have the following situation: postgres=# create database foo with encoding = 'UTF8'; postgres=# \c foo foo=# CREATE SCHEMA sc; foo=# ALTER DATABASE foo SET search_path=sc, pg_catalog; foo=# CREATE ROLE usr LOGIN PASSWORD 'usr' NOINHERIT CREATEDB VALID UNTIL 'infinity'; foo=# grant all privileges on all sequences in schema sc to usr; foo=# CREATE SEQUENCE foo_seq INCREMENT BY 1; foo=# select relname, relacl from pg_class where relkind = 'S'; relname | relacl -+ foo_seq | (1 row) i.e. even thought I request to have all privileges for all sequences in the schema, I don't for any new ones. This is not a problem for a fresh PostgreSQL database where I can set the grant at the end of the schema deployment. But if I want to add any new sequence to the database I need to explicitly add the grant to each new sequence. Is this expected behavior? If so is there a work around to my problem so I can get all privileges to my user for any new sequence in the schema? Thanks, - Ivo -- View this message in context: http://postgresql.1045698.n5.nabble.com/GRANT-privileges-strange-behavior-tp4726831p4726831.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] Wal archiving and streaming replication
On Tue, Aug 23, 2011 at 06:23:58AM -0700, Adrian Klaver wrote: > On Tuesday, August 23, 2011 4:14:15 am Ray Stell wrote: > > On Tue, Aug 23, 2011 at 02:01:10AM -0700, alexondi wrote: > > > Hi! > > > Do I need setup wal archiving (archiving_mode = on) setup when I use > > > streaming replication? > > > > yes > > http://www.postgresql.org/docs/current/interactive/high-availability.html > > Actually no. Streaming will work without archiving. For a quick introduction > see: > > http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial#5_Minutes_to_Simple_Replication right, you don't need to. I find it to be a best practice, which is different. -- 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] Wal archiving and streaming replication
On Tuesday, August 23, 2011 4:14:15 am Ray Stell wrote: > On Tue, Aug 23, 2011 at 02:01:10AM -0700, alexondi wrote: > > Hi! > > Do I need setup wal archiving (archiving_mode = on) setup when I use > > streaming replication? > > yes > http://www.postgresql.org/docs/current/interactive/high-availability.html Actually no. Streaming will work without archiving. For a quick introduction see: http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial#5_Minutes_to_Simple_Replication -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Wal archiving and streaming replication
On Tue, Aug 23, 2011 at 02:01:10AM -0700, alexondi wrote: > Hi! > Do I need setup wal archiving (archiving_mode = on) setup when I use > streaming replication? yes http://www.postgresql.org/docs/current/interactive/high-availability.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] COPY FROM (query) in plpgsql
Hello,
there seems to be no way to use COPY this way, so I guess this is a
feature request...
this may also help users who tried using COPY FROM STDIN in plpgsql.
I have a query with a lot of string manipulation that returns data as
single strings, e.g.:
'a,12,ght,45,1.2'
'b,13,ght,45,1.1'
'a,14,ght,45,1.5'
in order to save this result into a table, I still need to quote the
string value and use EXECUTE:
EXECUTE 'insert into foo values (''a'',12,''ght'',45,1.2)';
EXECUTE 'insert into foo values (''b'',13,''ght'',45,1.1)';
EXECUTE 'insert into foo values (''a'',14,''ght'',45,1.5)';
(or
EXECUTE 'insert into foo VALUES
(''a'',12,''ght'',45,1.2),
(''b'',13,''ght'',45,1.1),
(''a'',14,''ght'',45,1.5)
';
)
I guess this could become faster with such a syntax:
COPY foo FROM
(
SELECT 'a,12,ght,45,1.2'
UNION ALL
SELECT 'b,13,ght,45,1.1'
UNION ALL
SELECT 'a,14,ght,45,1.5'
) WITH CSV;
best regards,
Marc Mamin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Wal archiving and streaming replication
Hi! Do I need setup wal archiving (archiving_mode = on) setup when I use streaming replication? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Wal-archiving-and-streaming-replication-tp4726040p4726040.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] Getting value of bind variables
On Tue, Aug 23, 2011 at 09:07:20AM +0530, Jayadevan M wrote: > I guess so. But when I tried the same query on psql by replacing ($4) with > a value like '20110404', the query works OK. The value of $4 is being > passed from a java application. So does this mean I have to change the > data type in java code? For clarity, when you put the value '20110404' in a query, that is not a varchar. Rather it's type is coerced to whatever is most appropriate from the surrounding SQL. To generate the same error you need to put '20110404'::varchar in place of the parameter. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > He who writes carelessly confesses thereby at the very outset that he does > not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] SSL certificates issue
Il giorno lun, 22/08/2011 alle 09.37 -0400, Tom Lane ha scritto: > Asia writes: > > Now the issue is then when using libpq it was enough to have only root > > certificate in server's root.crt and it worked fine. > > But when I tried using the same with JDBC it turned out that I need to put > > whole chain (2 certs) of Intermediate CA 1 in server's root.crt. [...] > In the JDBC case you'd need to put all those certs into the client's > keystore, which I'm afraid I don't know the details of doing. Possibly > somebody on pgsql-jdbc could help you with that. you should import CA certificate in your JRE ca certstore with commands: cd $JAVA_HOME/jre/lib/security keytool -import -trustcacerts -alias $YOURCAALIAS \ -file $YOURCACERTFILE -keystore cacerts I usually store in client and server certificates the whole chain from primary CA. Bye, Giuseppe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
