Re: [GENERAL] explain analyse and nested loop joins
Thanks, It does look like an incorrect prediction. Looking again, I think it's the row estimate for the join that's out - the planner estimates one row returned, in which case a nested join would probably make sense, whereas in fact there are 23. However it's a generated (user created) query, so I think what I might do is get the application to detect this case from the query plan where there is a slow query and automatically test turning off nested joins. I'll just have to keep an eye on it to see if it becomes unnecessary in future PG versions. Regards Oliver www.agilebase.co.uk On 6 Nov 2011, at 04:17, Pavel Stehule wrote: > Hello > > Propably there are a dependency between following columns - and then a > prediction is not correct. > > Try to move one less selective to OUTER SELECT > > SELECT * FROM (SELECT your query OFFSET 0) x WHERE x.invoiced = false > > Regards > > Pavel Stehule > > 2011/11/5 Oliver Kohll - Mailing Lists : >> b2deliveryorders.complete = false AND b2deliveryorders.invoiced = false -- 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 references in the Middle East
On Sun, Nov 6, 2011 at 11:04 PM, Jan Geluk (Collis) wrote: > Dear all, > > For our customer in Dubai, we are looking for references of PostgreSQL > implementations in the Middle East, preferably in the GCC countries, > preferably in the United Arab Emirates (Dubai / Abu Dhabi). > > Please help me in this matter. > > Thanks in advance! some quick googling turned out this company in the UAE that does postgres hosting...maybe you might find some good contacts there: http://www.uaehosting.org/ this guy looks like a good contact: http://www.kannayath.com/ merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] function doesn't see change in search_path
I have a behaviour similar to this
http://archives.postgresql.org/pgsql-bugs/2007-09/msg00017.php
create language plpgsql;
create schema test1;
create schema test2;
create table test1.a(a varchar(3) unique);
create table test2.a(a varchar(3) unique);
create or replace function test_insert() returns void as
$$
begin
raise notice 'path %', current_schemas(true);
insert into a values('a');
end;
$$ language plpgsql volatile;
set search_path to 'test1', 'public';
select * from test_insert();
NOTICE: path {pg_catalog,test1,public}
test_insert
-
(1 row)
set search_path to 'test2', 'public';
select * from test_insert();
NOTICE: path {pg_catalog,test2,public}
ERROR: duplicate key value violates unique constraint "a_a_key"
CONTEXT: SQL statement "insert into a values('a')"
PL/pgSQL function "test_insert" line 3 at SQL statement
PostgreSQL 8.3.14
what's going on?
--
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] Custom Contraint Violation Errors
On Thu, Nov 3, 2011 at 11:56 PM, Michael Musenbrock wrote: > But I have not > found any information if this is possible to create a trigger on a > constraint violation, and if yes, how could that be done? You want to use the special type of "CONSTRAINT" trigger. http://www.postgresql.org/docs/9.1/interactive/sql-createtrigger.html Notice that constraint triggers require the developer of the trigger to RAISE EXCEPTION when the constraint is violated. http://www.postgresql.org/docs/9.1/interactive/plpgsql-errors-and-messages.html The syntax for RAISE EXCEPTION allow the developer to specify any desired message. -- Regards, Richard Broersma Jr. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Why fetch a row is more slow than a 'selec * from'
Hello I've been looking for ways to optimize a query. I have a table with 120,000 records. When searched on: select * from big_table takes to run: 3 min. I wanted to use cursors and the query with big_table, it taked 11 minutes. It caught my attention on a small_table (100 records) because the time, with cursors, were reduced by half. The EXPLAIN ANALYZE: indicates that a search is sequential, but has a primary key Will have some advice to optimize the response time of the visit? there is nothing to do? :( El contenido de este correo electrónico y sus archivos adjuntos son privados y confidenciales y va dirigido exclusivamente a su destinatario. No se autoriza la utilización, retransmisión, diseminación, o cualquier otro uso de esta información por un receptor o entidades distintas al destinatario. Si recibe este correo sin ser el destinatario se le solicita eliminarlo y hacerlo del conocimiento del emisor. La empresa no se hace responsable de transmisiones o comunicaciones no autorizadas o emitidas por personas ajenas a sus colaboradores utilizando éste medio electrónico. The content of this email and its attached files are private and confidential and intended exclusively for the use of the individual or entity to which they are addressed. The retransmission, dissemination, or any other use of this information other than by the intended recipient is prohibited. If you have received this email in error please delete it and notify the sender. The company cannot be held liable for unauthorized electronic transmissions or communications, nor for those emitted by non-company individuals and entities. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why fetch a row is more slow than a 'selec * from'
Hello cursors are optimized for fast return of first row and there are no expect to return complete result. This can be a problem try to set cursor_tuple_fraction to 1 Regards Pavel Stehule 2011/11/7 Ing.Edmundo.Robles.Lopez : > Hello > I've been looking for ways to optimize a query. > > I have a table with 120,000 records. When searched on: > > select * from big_table > > takes to run: 3 min. > > I wanted to use cursors and the query with big_table, it taked 11 minutes. > It caught my attention on a small_table (100 records) because the time, > with cursors, were reduced by half. > > > The EXPLAIN ANALYZE: indicates that a search is sequential, but has a > primary key > > > > Will have some advice to optimize the response time of the visit? there is > nothing to do? :( > El contenido de este correo electrónico y sus archivos adjuntos son privados > y confidenciales y va dirigido exclusivamente a su destinatario. No se > autoriza la utilización, retransmisión, diseminación, o cualquier otro uso > de esta información por un receptor o entidades distintas al destinatario. > Si recibe este correo sin ser el destinatario se le solicita eliminarlo y > hacerlo del conocimiento del emisor. La empresa no se hace responsable de > transmisiones o comunicaciones no autorizadas o emitidas por personas ajenas > a sus colaboradores utilizando éste medio electrónico. > > The content of this email and its attached files are private and > confidential and intended exclusively for the use of the individual or > entity to which they are addressed. The retransmission, dissemination, or > any other use of this information other than by the intended recipient is > prohibited. If you have received this email in error please delete it and > notify the sender. The company cannot be held liable for unauthorized > electronic transmissions or communications, nor for those emitted by > non-company individuals and entities. > > -- > 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] function doesn't see change in search_path
2011/11/7 Ivan Sergio Borgonovo : > On Mon, 7 Nov 2011 17:55:11 +0100 > Pavel Stehule wrote: > >> Hello >> >> this is know bug/feature based on caching plans > > What puzzled me is I'm operating in a similar way in a different > system and I'm not experiencing the same problem. > > Do different users have different caches? depend on usage - cache is per session > What about different sessions? if you don't change a search_path inside session, then all will works well > >> There is workaround - you can put a copy of test_insert function to >> every schema - no to "public" schema. > > That's pretty ugly. I'll take the chance to refactor everything and > learn. yes, this is workaround - it's not nice > > Where can i learn about creation and invalidation of plans in > postgres documentation? > > BTW it looks to me you just answered to my address and not to the > list. If it was by mistake feel free to repost everything to the > list for other people's reference. > > 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] installation problems on OSX Lion
Hello, I had the same issue and could solve it the following way. open a terminal, cd /usr/lib sudo mv libpq.5.dylib libpq.5.dylib-ORG sudo ln -s libpq.5.3.dylib libpq.5.dylib Cheers, Stéphane -- View this message in context: http://postgresql.1045698.n5.nabble.com/installation-problems-on-OSX-Lion-tp4627419p4969019.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] Is *.projects.postgresql.org offline?
Hello everyone, Is the pages hosted at http://projects.postgresql.org/ offline? I can't access any of them. Tks in advance.
[GENERAL] function within a function/rollbacks/exception handling
I have a function, call it 'functionMain'. And I have several tables that each have trigger functions. Each trigger function needs to call 'functionMain' (with different parameters). table A => trigger function A ==> functionMain table B => trigger function B ==> functionMain table C => trigger function C ==> functionMain 'functionMain' returns VOID (runs an insert statement). and has an exception/raise exception block. An insert transaction for table A is launched (insertA), trigger function A is called, 'functionMain' is called and 'functionMain' fails. Hence, trigger function A needs to rollback. Questions: a) I am assuming that the trigger functions should use 'PERFORM functionMain()'? b) if 'functionMain' fails, then 'funtionMain' automatically performs an implicit rollback, correct? c) if 'functionMain' fails, should the trigger function also contain an exception handler or will the rollback from 'functionMain' cascade up to the original transaction (insertA)? Thanks. Lori -- 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] Is *.projects.postgresql.org offline?
On Mon, 2011-11-07 at 17:10 -0200, Leonardo Carneiro wrote: > > Is the pages hosted at http://projects.postgresql.org/ offline? I > can't access any of them. http://archives.postgresql.org/pgsql-www/2011-11/msg00016.php -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [GENERAL] function within a function/rollbacks/exception handling
Hello 2011/11/7 Lori Corbani : > > I have a function, call it 'functionMain'. And I have several tables that > each have trigger functions. Each trigger function needs to call > 'functionMain' (with different parameters). > > table A => trigger function A ==> functionMain > table B => trigger function B ==> functionMain > table C => trigger function C ==> functionMain > > 'functionMain' returns VOID (runs an insert statement). and has an > exception/raise exception block. > > An insert transaction for table A is launched (insertA), trigger function A > is called, > 'functionMain' is called and 'functionMain' fails. Hence, trigger function A > needs to rollback. > > Questions: > > a) I am assuming that the trigger functions should use 'PERFORM > functionMain()'? > > b) if 'functionMain' fails, then 'funtionMain' automatically performs an > implicit rollback, correct? > > c) if 'functionMain' fails, should the trigger function also contain an > exception handler > or will the rollback from 'functionMain' cascade up to the original > transaction (insertA)? PostgreSQL function does not do rollback by self. You should to do it manually after exception. When functionMain fails, then trigger fails too. If trigger contains a exception handler, then it start a subtransaction on begin block execution and rollback this subtransaction when exception is raised. Regards Pavel Stehule > > Thanks. > Lori > > -- > 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
[GENERAL] Www emulator
Hi. Is any application, which works as www server on client's side, and loads pages from postgresql database? (free or commercial) -- pasman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Behavior of negative OFFSET
I've been using a query on Postgres 8.4 with a negative OFFSET, which works fine: SELECT DISTINCT s.* FROM s WHERE ... ORDER BY s.bday ASC, s.name ASC LIMIT 15 OFFSET -15 When I run the same query on Postgres 9.1, I get an error: ERROR: OFFSET must not be negative Question: 1. Was this changed in a version of Postgres? Is this configurable? 2. How do I get the original behavior of negative offsets? -- 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] Behavior of negative OFFSET
On Mon, Nov 7, 2011 at 3:47 PM, Robert James wrote: > I've been using a query on Postgres 8.4 with a negative OFFSET, which > works fine: > > SELECT DISTINCT s.* FROM s WHERE ... ORDER BY s.bday ASC, s.name > ASC LIMIT 15 OFFSET -15 > > When I run the same query on Postgres 9.1, I get an error: > ERROR: OFFSET must not be negative > > > Question: > 1. Was this changed in a version of Postgres? Is this configurable? > 2. How do I get the original behavior of negative offsets? the original behavior was undefined. to kinda sorta get it, create function oldoffset(int) returns int as $$ select case when $1 < 0 then 0 else $1 end; $$ language sql immutable; select v from generate_series(1,15) v limit 15 offset oldoffset(-15); merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_restore: [custom archiver] unexpected end of file on Postgres 9.1.1
I am trying to restore a dump created with pg_dump, both source and destination are Postgres 9.1.1 albeit different machines (source is Linux, destination is OS X). $ pg_restore -U postgres -Fc -d batch_api_production 200708_batch_api_production.dump.sql pg_restore: [custom archiver] unexpected end of file pg_restore does seem like its doing something, as it doesnt error out until 10-12 seconds in. I can dump the TOC just fine with pg_restore -l, which I've collected here: https://gist.github.com/951e417e7098fdf987d4 If I access the DB it appears that all the tables and sequences exist, but none of the data or indexes & constraints. Any help would be appreciated. /Cody Caughlan
Re: [GENERAL] function doesn't see change in search_path
On Mon, 7 Nov 2011 19:07:29 +0100 Pavel Stehule wrote: > 2011/11/7 Ivan Sergio Borgonovo : > > On Mon, 7 Nov 2011 17:55:11 +0100 > > Pavel Stehule wrote: > > > >> Hello > >> > >> this is know bug/feature based on caching plans > > > > What puzzled me is I'm operating in a similar way in a different > > system and I'm not experiencing the same problem. > > > > Do different users have different caches? > > depend on usage - cache is per session OK. It is clear it is "per session". Up to my knowledge users can't be changed inside the same session. What are you referring to with "depend on usage". Is there any other thing that can influence cached plans? Right now I just need a workaround and calling the function in different sessions seems cleaner than writing a function for each schema especially since I can use psql \connect. It seems that cache management happens completely behind the scenes and there are no way to control it other than playing tricks as sql := 'select * from ' | sometable |... execute sql; I didn't find anything on cache other than what's written here http://developer.postgresql.org/pgdocs/postgres/plpgsql-implementation.html 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] Www emulator
On 07/11/2011 20:13, pasman pasmański wrote: > Hi. > > Is any application, which works as www server on client's side, and > loads pages from postgresql database? (free or commercial) > Many. Depends on what you want to do care to be a bit more specific? Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Simple question on SELECT
Does simple SELECT query like select * from tableX (without FOR UPDATE) opens an implicit transaction when issued? Thanks, -- Edson Carlos Ericksson Richter SimKorp Informática Ltda Fone: (51) 3366-7964 Celular: (51)9318-9766/(51) 8585-0796
Re: [GENERAL] Custom Constraint Violation Errors
Am 2011-11-07 16:06, schrieb Richard Broersma: > On Thu, Nov 3, 2011 at 11:56 PM, Michael Musenbrock wrote: >> But I have not >> found any information if this is possible to create a trigger on a >> constraint violation, and if yes, how could that be done? > You want to use the special type of "CONSTRAINT" trigger. > > http://www.postgresql.org/docs/9.1/interactive/sql-createtrigger.html Ah, thank you very much, I was already reading about the constraint trigger, but I was intentionally looking for a trigger firing on violating of an already existing constraint. So am I got that right, that If I want to have eg custom error messages for a foreign key violation, I need to replace all fkeys by trigger functions?! Thanks in advance, Regards, Michael -- 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] Simple question on SELECT
On 11/07/11 2:41 PM, Edson Richter wrote: Does simple SELECT query like select * from tableX (without FOR UPDATE) opens an implicit transaction when issued? opens and closes.if you don't bracket with BEGIN; COMMIT|ROLLBACK;then each statement is a transaction of and by itself -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] Custom Constraint Violation Errors
On Mon, Nov 7, 2011 at 2:47 PM, Michael Musenbrock wrote: > was intentionally looking for a trigger firing on violating of an > already existing constraint. > > So am I got that right, that If I want to have eg custom error messages > for a foreign > key violation, I need to replace all fkeys by trigger functions?! I see. The short answer is, while it is possible to custom make your own constraint triggers that emulate foreign keys, don't do it. This would be a maintenance nightmare. I'd be more maintainable to catch these errors in your client application. Here you would reword these error messages according the business rules of your client application. If you insist that the server rewrite your error messages, then you'll need to create stored functions that will preform the desired operations; catch any possible errors; and then throw the altered error messages. But remember that catching errors and throwing them will have a performance penalty. http://www.postgresql.org/docs/9.1/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING -- Regards, Richard Broersma Jr. -- 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] function within a function/rollbacks/exception handling
On 07/11/11 19:18, Lori Corbani wrote: I have a function, call it 'functionMain'. And I have several tables that each have trigger functions. Each trigger function needs to call 'functionMain' (with different parameters). table A => trigger function A ==> functionMain table B => trigger function B ==> functionMain table C => trigger function C ==> functionMain 'functionMain' returns VOID (runs an insert statement). and has an exception/raise exception block. An insert transaction for table A is launched (insertA), trigger function A is called, 'functionMain' is called and 'functionMain' fails. Hence, trigger function A needs to rollback. Questions: a) I am assuming that the trigger functions should use 'PERFORM functionMain()'? If you don't want the result, yes. b) if 'functionMain' fails, then 'funtionMain' automatically performs an implicit rollback, correct? c) if 'functionMain' fails, should the trigger function also contain an exception handler or will the rollback from 'functionMain' cascade up to the original transaction (insertA)? Unless you catch the exception, it will roll back the whole transaction, so "yes" to b + c. If it helps to visualise what happens, exceptions are actually implemented using savepoints in plpgsql. -- Richard Huxton Archonet Ltd -- 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] function doesn't see change in search_path
On 07/11/11 14:43, Ivan Sergio Borgonovo wrote: create or replace function test_insert() returns void as [snip] $$ language plpgsql volatile; set search_path to 'test1', 'public'; set search_path to 'test2', 'public'; [snip unexpected behaviour] I now try to add a SET search_path to the bottom of all my plpgsql functions. It can get very confusing otherwise, as you've just demonstrated. -- Richard Huxton Archonet Ltd -- 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] Simple question on SELECT
Em 07-11-2011 20:54, John R Pierce escreveu: On 11/07/11 2:41 PM, Edson Richter wrote: Does simple SELECT query like select * from tableX (without FOR UPDATE) opens an implicit transaction when issued? opens and closes.if you don't bracket with BEGIN; COMMIT|ROLLBACK;then each statement is a transaction of and by itself Thanks for the fast answer. Is there any way to avoid that? I mean, in MS SQL Server, I do have "with no_lock" (that produces dirty reads)? Or the way to go is the transaction isolation level? Regards, Edson. -- 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] Simple question on SELECT
On Mon, Nov 7, 2011 at 3:30 PM, Edson Richter wrote: > Thanks for the fast answer. Is there any way to avoid that? I mean, in MS > SQL Server, I do have "with no_lock" (that produces dirty reads)? > Or the way to go is the transaction isolation level? The lowest level of Isolation supported by PostgreSQL is READ COMMITTED which is the default transaction isolation level: http://www.postgresql.org/docs/9.1/interactive/sql-set-transaction.html -- Regards, Richard Broersma Jr. -- 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] Simple question on SELECT
On 11/07/11 3:30 PM, Edson Richter wrote: Thanks for the fast answer. Is there any way to avoid that? I mean, in MS SQL Server, I do have "with no_lock" (that produces dirty reads)? Or the way to go is the transaction isolation level? there's no actual overhead in a single statement read transaction like this. Postgres uses a novel approach to MVCC. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] Www emulator
2011/11/7 Raymond O'Donnell : > On 07/11/2011 20:13, pasman pasmański wrote: >> Hi. >> >> Is any application, which works as www server on client's side, and >> loads pages from postgresql database? (free or commercial) >> > > Many. Depends on what you want to do care to be a bit more specific? Wikimedia is a good front end for content management on top of pgsql. It's pretty easy to install to boot. But yeah, we need to know more of what it is OP is trying to do. -- 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] Behavior of negative OFFSET
On 11/7/11, Merlin Moncure wrote: > On Mon, Nov 7, 2011 at 3:47 PM, Robert James wrote: >> I've been using a query on Postgres 8.4 with a negative OFFSET, which >> works fine: >> >> SELECT DISTINCT s.* FROM s WHERE ... ORDER BY s.bday ASC, s.name >> ASC LIMIT 15 OFFSET -15 >> > > the original behavior was undefined. What do it do in reality? I'm debugging a legacy app which used it. > to kinda sorta get it, > create function oldoffset(int) returns int as > $$ > select case when $1 < 0 then 0 else $1 end; > $$ language sql immutable; > > select v from generate_series(1,15) v limit 15 offset oldoffset(-15); > That sounds like if OFFSET was negative, it would be simply ignored. Is that correct? When was the behavior of OFFSET changed? Also: Is there any reference in the docs to this? I wasn't able to find this. -- 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] pg_restore: [custom archiver] unexpected end of file on Postgres 9.1.1
On Monday, November 07, 2011 11:27:05 am Cody Caughlan wrote: > I am trying to restore a dump created with pg_dump, both source and > destination are Postgres 9.1.1 albeit different machines (source is Linux, > destination is OS X). > > $ pg_restore -U postgres -Fc -d batch_api_production > 200708_batch_api_production.dump.sql > pg_restore: [custom archiver] unexpected end of file > > pg_restore does seem like its doing something, as it doesnt error out until > 10-12 seconds in. > > I can dump the TOC just fine with pg_restore -l, which I've collected here: > > https://gist.github.com/951e417e7098fdf987d4 > > If I access the DB it appears that all the tables and sequences exist, but > none of the data or indexes & constraints. > > Any help would be appreciated. What do the database logs show when you do the restore? > > /Cody Caughlan -- 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
[GENERAL]
Hi,friends. Can you tell me the difference between the "dpkg -b directory packagename" and "dpkg-buildpackage -rfakeroot".Do these two commands both have the function of build a debian package?
[GENERAL] Postgres vs other Postgres based MPP implementations
Hi, I have simple question (I think which is not easy to answer): why Postgres is so slow comparing to other Postgres based MPP products (even on the same box in single node configuration)? I'm mot talking about multi node setup; all benchmarks were done on single box (CentOS 5.5, 16 cores, 80GB ram, fast SAS local disks) and Postgres is slower by order of magnitude (10 or sometimes 100 times slower). Secondly, I've run simple selects and aggregations on vertically partitioned star schema and I haven't used features like columnar tables or replicated dimension tables. I believe that my Postgres configuration is close to optimal one. Another interesting experiment was to parallelise query by hand: select sum(count) from T where org_id = ... and date_in_tz >= '2011-08-01' and date_in_tz < '2011-11-01' The query above was revritten as series of queries like this: create table t00 as select sum(count) from T where (... previous where ...) and date_in_tz >= hist[1] < date_in_tz >= hist[2] create table t01 as select sum(count) from T where (... previous where ...) and date_in_tz >= hist[2] < date_in_tz >= hist[3]... create table t08 as select sum(count) from T where (... previous where ...) and date_in_tz >= hist[8] < date_in_tz >= hist[9] create table t09 as select sum(count) from T where (... previous where ...) and date_in_tz >= hist[9] < date_in_tz >= hist[10] "hist" is similar to pg_stat.histogram_bounds (A list of values that divide the column's values into groups of approximately equal population). The idea is to slice T table by primary key (date_in_tz) into 10 groups (or N groups) with similar row count and execute those CTAS queries thru multiple connections. Final query just sums: select sum(count) from ( select count from t00 union all ... union all select count from t09 ) as x All above were faster than single query at the begging. Disk activity was lower and spanned over longer timespan for original query comparing to sliced query scenario. Maybe there is no need for quite complex query rewrite and queries can be speed up by async parallel reads... -- 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] Behavior of negative OFFSET
Robert James writes: > On 11/7/11, Merlin Moncure wrote: >> On Mon, Nov 7, 2011 at 3:47 PM, Robert James wrote: >>> I've been using a query on Postgres 8.4 with a negative OFFSET, which >>> works fine: >>> SELECT DISTINCT s.* FROM s WHERE ... ORDER BY s.bday ASC, s.name >>> ASC LIMIT 15 OFFSET -15 >> the original behavior was undefined. > What do it do in reality? I'm debugging a legacy app which used it. It used to treat negative offsets/limits as zero. http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=bfce56eea45b1369b7bb2150a150d1ac109f5073 > Also: Is there any reference in the docs to this? I wasn't able to find this. The 8.4 release notes mention * Disallow negative LIMIT or OFFSET values, rather than treating them as zero (Simon) I'm pretty sure this changed in 8.4, not since then. 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] Custom Constraint Violation Errors
On 11/08/2011 07:06 AM, Richard Broersma wrote: I'd be more maintainable to catch these errors in your client application. Here you would reword these error messages according the business rules of your client application. +1 It's not hard to create: CONSTRAINT some_constraint_name FOREIGN KEY col REFERENCES blah(id) ... then in the app, match "some_constraint_name" and map it to a suitable error. That's what I do and it works very well for all constraint types, not just foreign key constraints. -- 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]
On 11/08/2011 09:18 AM, daflmx wrote: Hi,friends. Can you tell me the difference between the "dpkg -b directory packagename" and "dpkg-buildpackage -rfakeroot".Do these two commands both have the function of build a debian package? First: is this an assignment or something? We've had an unusual flood of questions about building Debian packages for PostgreSQL, which often means someone's set coursework. IIRC "dpkg -b" is just a wrapper around other tools. In the end the work is done by debian/control and debian/rules anyway. Neither does anything useful without a suitable debian/ directory with debian/control file etc in the target directory. They don't just magically turn any old source folder into a binary Debian package. If you want to learn about building Debian packages, don't ask here, read the Debian guide on packaging: http://www.debian.org/doc/manuals/maint-guide/ http://www.debian.org/doc/manuals/maint-guide/first.en.html#workflow http://www.debian.org/doc/manuals/maint-guide/build.en.html -- 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 vs other Postgres based MPP implementations
On 11/08/2011 09:28 AM, Ondrej Ivanič wrote: I have simple question (I think which is not easy to answer): why Postgres is so slow comparing to other Postgres based MPP products Which one(s) are you referring to? In what kind of workloads? Are you talking about Greenplum or similar? Another interesting experiment was to parallelise query by hand: select sum(count) from T where org_id = ... and date_in_tz>= '2011-08-01' and date_in_tz< '2011-11-01' Pg isn't very good at parallelism within a single query. It handles lots of small queries concurrently fairly well, but isn't as good at using all the resources of a box on one big query because it can only use one CPU per query and has only a very limited ability to do concurrent I/O on a single query too. That said, you should be tuning effective_io_concurrency to match your storage; if you're not, then you aren't getting the benefit of the concurrent I/O that PostgreSQL *is* capable of. You'll also need to have tweaked your shared_buffers, work_mem etc appropriately for your query workload. Since Pg needs some kind of pooling and admission control to perform very well in OLTP, it's only highly performant without addons in the middle of the range - medium numbers of medium-complexity queries. For huge numbers of simple queries it needs a pooler, and for small numbers of hugely complex queries it won't perform all that well without something to try to parallelise the queries outside Pg. All above were faster than single query at the begging. I'm not at all surprised by that. PostgreSQL couldn't use the full resources of your system when it was expressed as just one query. -- 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] FATAL: the database system is starting up
On Tue, Nov 1, 2011 at 5:43 AM, patrick keshishian wrote: > On Wed, Oct 26, 2011 at 5:46 PM, Scott Marlowe > wrote: >> On Wed, Oct 26, 2011 at 6:09 PM, patrick keshishian >> wrote: >>> On Wed, Oct 26, 2011 at 4:49 PM, William E. Moreno A. >>> wrote: Solution: http://archives.postgresql.org/pgsql-general/2007-12/msg01339.php Solution: Message-id: <476d6de1.4050...@latnet.lv> Problem: FATAL: the database system is starting up Solved: change postgresql_flags in /etc/rc.conf to: postgresql_flags="-s -m fast" or postgresql_flags="-s -m smart" >>> >>> interesting enough, got bit by this recently. Our set up does not have >>> a "-w" option. During upgrades we "cycle" our PostgreSQL daemon. We >>> had to change the "pg_ctl stop" command to "-mimmediate" because >>> during upgrades we would find that someone had an abandoned psql shell >>> running for days and that would halt our upgrade script. >>> >>> Adding "-m immediate" for shutdown seemed like a logical choice to get >>> around this sort of a "procedural" issue(s). >> >> didn't -m fast work? > > I forget now why "-m fast" wasn't used. I tried to dig up anything I > had in my notes and did a set of new experiments (hence the late > reply), but didn't come up with much. > > About the only difference with '-m fast' and '-m immediate' seems to > be the following log entry: > > database system was > not properly shut down; automatic recovery in progress > > For now, I've changed the script to use '-m fast' and removed the > sleep. Will find out after some moderate use of it whether it was a > wise decision or not :-) (just for the archives ...) So, even though 'stop -m fast' does in fact work nicer than "-m immediate", the sleep(3) is required after a "pg_ctl -D $PGDATA start" command, in our scripts. Excuse gmail line-wraps: # /etc/init.d/postgres stop Stopping PostgreSQL Database (smart)... OK # date ; /etc/init.d/postgres start ; date ; i=0 ; while : ; do psql -lU postgres 2>/dev/null && date && break ; i=$((i+1)) ; printf "PG9 connect try #%d\n" $i ; done Mon Nov 7 20:58:06 PST 2011 Starting PostgreSQL Database... OK Mon Nov 7 20:58:06 PST 2011 PG9 connect try #1 PG9 connect try #2 PG9 connect try #3 PG9 connect try #4 PG9 connect try #5 PG9 connect try #6 PG9 connect try #7 PG9 connect try #8 PG9 connect try #9 PG9 connect try #10 PG9 connect try #11 PG9 connect try #12 PG9 connect try #13 PG9 connect try #14 PG9 connect try #15 PG9 connect try #16 PG9 connect try #17 PG9 connect try #18 PG9 connect try #19 PG9 connect try #20 PG9 connect try #21 PG9 connect try #22 PG9 connect try #23 PG9 connect try #24 PG9 connect try #25 PG9 connect try #26 PG9 connect try #27 PG9 connect try #28 PG9 connect try #29 PG9 connect try #30 PG9 connect try #31 PG9 connect try #32 PG9 connect try #33 PG9 connect try #34 PG9 connect try #35 PG9 connect try #36 PG9 connect try #37 PG9 connect try #38 PG9 connect try #39 PG9 connect try #40 PG9 connect try #41 PG9 connect try #42 PG9 connect try #43 PG9 connect try #44 PG9 connect try #45 PG9 connect try #46 PG9 connect try #47 PG9 connect try #48 PG9 connect try #49 PG9 connect try #50 PG9 connect try #51 PG9 connect try #52 PG9 connect try #53 PG9 connect try #54 PG9 connect try #55 List of databases Name| Owner | Encoding | Collation |Ctype| Access privileges ---+--+--+-+-+--- foo | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres x1| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | (5 rows) Mon Nov 7 20:58:07 PST 2011 # /etc/init.d/postgres stop Stopping PostgreSQL Database (smart)... OK # date ; /etc/init.d/postgres start ; date ; i=0 ; while : ; do psql -lU postgres 2>/dev/null && date && break ; i=$((i+1)) ; printf "PG9 connect try #%d\n" $i ; done Mon Nov 7 20:58:49 PST 2011 Starting PostgreSQL Database... OK Mon Nov 7 20:58:49 PST 2011 PG9 connect try #1 PG9 connect try #2 PG9 connect try #3 PG9 connect try #4 PG9 connect try #5 PG9 connect try #6 PG9 connect try #7 PG9 connect try #8 PG9 connect try #9 PG9 connect try #10 PG9 connect try #11 PG9 connect try #12 PG9 connect try #13 PG9 connect try #14 PG9 connect try #15 PG9 connect try #16 PG9 connect try #17 PG9 connect try #18 PG9 connect try #19 PG9 connect try #20 PG9 connect try #21 PG9 connect try #22 PG9 connect try #23 PG9 connect try #24 PG9 connect try #25 PG9 connect try #26 PG9 connect try #27 PG9 connect try #28 PG9 connect try #29 PG9 connect try #30 PG9 co
Re: [GENERAL] Www emulator
Thanks for reply. I have simple www server. Today postgres and www server are on single computer. Plan is to reduce cpu consumption by client application which translate requests from internet explorer directly to postgres. 2011/11/8, Scott Marlowe : > 2011/11/7 Raymond O'Donnell : >> On 07/11/2011 20:13, pasman pasmański wrote: >>> Hi. >>> >>> Is any application, which works as www server on client's side, and >>> loads pages from postgresql database? (free or commercial) >>> >> >> Many. Depends on what you want to do care to be a bit more specific? > > Wikimedia is a good front end for content management on top of pgsql. > It's pretty easy to install to boot. But yeah, we need to know more > of what it is OP is trying to do. > -- pasman -- 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 vs other Postgres based MPP implementations
Hi, On 8 November 2011 16:58, Craig Ringer wrote: > Which one(s) are you referring to? In what kind of workloads? > > Are you talking about Greenplum or similar? Yes, mainly Geenplum and nCluster (AsterData). I haven't played with gridSQL and pgpool-II's parallel query mode too much. Queries are simple aggregations/drill downs/roll ups/... -- mostly heavy read workloads but OLTP performance is required (like run query over 100m+ dataset in 15 sec) > Pg isn't very good at parallelism within a single query. It handles lots of > small queries concurrently fairly well, but isn't as good at using all the > resources of a box on one big query because it can only use one CPU per > query and has only a very limited ability to do concurrent I/O on a single > query too. Usually CPU is not bottleneck but I it was when I put Pustgres on FusionIO. The problem is that PG spreads reads too much . iostat reports very low drive utilisation and very low queue size. > That said, you should be tuning effective_io_concurrency to match your > storage; if you're not, then you aren't getting the benefit of the > concurrent I/O that PostgreSQL *is* capable of. You'll also need to have > tweaked your shared_buffers, work_mem etc appropriately for your query > workload. I've played with effective_io_concurrency (went thru entire range: 1, 2, 5, 10, 20, 50, 100, 200, 500, 1000) but nothing improved. Is there a way to get PG backed IO stats using stock CentOS (5.7) kernel and tools? (I can't change my env easily) > queries it won't perform all that well without something to try to > parallelise the queries outside Pg. yeah, I have one moster query which needs half a day to finish but it finishes in less than two hours on the same hw if is executed in parallel... > I'm not at all surprised by that. PostgreSQL couldn't use the full resources > of your system when it was expressed as just one query. This is very interesting area to work in but my lack of C/C++ and PG internals puts me out of the game :) -- 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] Www emulator
On 11/07/11 10:25 PM, pasman pasmański wrote: Thanks for reply. I have simple www server. Today postgres and www server are on single computer. Plan is to reduce cpu consumption by client application which translate requests from internet explorer directly to postgres. that 'client application' would in fact be a web server.http requests are nothing like SQL transactions. typically, your web server implements an application which uses the database to store the results of whatever it is the web application generates (sales orders, forum pages, etc). -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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 vs other Postgres based MPP implementations
On 11/07/11 10:49 PM, Ondrej Ivanič wrote: mostly heavy read workloads but OLTP performance is required (like run query over 100m+ dataset in 15 sec) that isn't OLTP, its OLAP. Online Analytic Processing rather than Online Transaction Processing large complex reporting queries that have to aggregate many rows is classic OLAP. OLTP transactions tend to refer to a few rows at a time of a bunch of tables, and update a few rows of a various tables, and you execute many of them per second, often for a large number of concurrent clients. classic example is a web store system (adding/modifying items in a shopping cart, then purchasing the items in the cart). -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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 vs other Postgres based MPP implementations
On 11/08/2011 02:49 PM, Ondrej Ivanič wrote: Usually CPU is not bottleneck but I it was when I put Pustgres on FusionIO. The problem is that PG spreads reads too much . iostat reports very low drive utilisation and very low queue size. "Spreads reads too much" ? Are you saying there's too much random I/O? Is it possible it'd benefit from a column store? When you're using Greenplum are you using "Polymorphic Data Storage" column storage "WITH (orientation=column)" ? Or is the performance different just in better utilisation of the hardware under Greenplum? I've played with effective_io_concurrency (went thru entire range: 1, 2, 5, 10, 20, 50, 100, 200, 500, 1000) but nothing improved. Hm, ok. Scratch that then. Is there a way to get PG backed IO stats using stock CentOS (5.7) kernel and tools? (I can't change my env easily) Dunno; check postgresql high performance (book), the manual, etc. Useful tools are the pg_stat_ tables, "vmstat", "iostat", "iotop", etc. I'm not at all surprised by that. PostgreSQL couldn't use the full resources of your system when it was expressed as just one query. This is very interesting area to work in but my lack of C/C++ and PG internals puts me out of the game :) That's a cop-out! I say that as someone who _used_ to have no C knowledge, but learned it to get things done on code I use. That said, Pg's codebase isn't exactly trivial :S and trying to get involved in major re-engineering like parallelisation isn't going to be practical when you're just getting started. -- 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
