Re: [GENERAL] vacuum analyze hurts performance
Forgot to mention I'm running (PostgreSQL) 8.2.9 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] vacuum analyze hurts performance
Starting with a database where analyze has never been run I get worse
performance after running it -- is there something I'm missing?
Hopefully the log below shows it clearly:
test= EXPLAIN ANALYZE
SELECT COUNT(DISTINCT agent_agent.id)
FROM agent_agent INNER JOIN auth_user ON
(agent_agent.user_id = auth_user.id)
WHERE ((UPPER(auth_user.email::text) LIKE UPPER('%john%')
OR UPPER(agent_agent.email_leads::text) LIKE UPPER('%john%')
OR UPPER(auth_user.first_name::text) LIKE UPPER('%john%')
OR UPPER(auth_user.last_name::text) LIKE UPPER('%john%')
OR UPPER(agent_agent.phone_number::text) LIKE UPPER('%john
%')
OR UPPER(agent_agent.personal_statement::text) LIKE
UPPER('%john%') ));
QUERY
PLAN
--
Aggregate (cost=8156.28..8156.29 rows=1 width=4) (actual
time=340.557..340.557 rows=1 loops=1)
- Nested Loop (cost=0.00..8155.36 rows=369 width=4) (actual
time=1.326..340.346 rows=144 loops=1)
Join Filter: ((upper((auth_user.email)::text) ~~ '%JOHN
%'::text) OR (upper((agent_agent.email_leads)::text) ~~ '%JOHN
%'::text) OR (upper((auth_user.first_name)::text) ~~ '%JOHN%'::text)
OR (upper((auth_user.last_name)::text) ~~ '%JOHN%'::text) OR
(upper((agent_agent.phone_number)::text) ~~ '%JOHN%'::text) OR
(upper(agent_agent.personal_statement) ~~ '%JOHN%'::text))
- Seq Scan on agent_agent (cost=0.00..457.44 rows=7844
width=326) (actual time=0.043..8.852 rows=7844 loops=1)
- Index Scan using auth_user_pkey on auth_user
(cost=0.00..0.94 rows=1 width=328) (actual time=0.013..0.014 rows=1
loops=7844)
Index Cond: (agent_agent.user_id = auth_user.id)
Total runtime: 340.730 ms
(7 rows)
test= vacuum analyze agent_agent;
VACUUM
test= vacuum analyze auth_user;
VACUUM
test= EXPLAIN ANALYZE
SELECT COUNT(DISTINCT agent_agent.id)
FROM agent_agent INNER JOIN auth_user ON
(agent_agent.user_id = auth_user.id)
WHERE ((UPPER(auth_user.email::text) LIKE UPPER('%john%')
OR UPPER(agent_agent.email_leads::text) LIKE UPPER('%john%')
OR UPPER(auth_user.first_name::text) LIKE UPPER('%john%')
OR UPPER(auth_user.last_name::text) LIKE UPPER('%john%')
OR UPPER(agent_agent.phone_number::text) LIKE UPPER('%john
%')
OR UPPER(agent_agent.personal_statement::text) LIKE
UPPER('%john%') ));
QUERY
PLAN
--
Aggregate (cost=4539.73..4539.74 rows=1 width=4) (actual
time=448.742..448.742 rows=1 loops=1)
- Hash Join (cost=877.49..4538.80 rows=369 width=4) (actual
time=28.144..448.426 rows=144 loops=1)
Hash Cond: (auth_user.id = agent_agent.user_id)
Join Filter: ((upper((auth_user.email)::text) ~~ '%JOHN
%'::text) OR (upper((agent_agent.email_leads)::text) ~~ '%JOHN
%'::text) OR (upper((auth_user.first_name)::text) ~~ '%JOHN%'::text)
OR (upper((auth_user.last_name)::text) ~~ '%JOHN%'::text) OR
(upper((agent_agent.phone_number)::text) ~~ '%JOHN%'::text) OR
(upper(agent_agent.personal_statement) ~~ '%JOHN%'::text))
- Seq Scan on auth_user (cost=0.00..1733.37 rows=54837
width=48) (actual time=0.007..35.345 rows=54837 loops=1)
- Hash (cost=457.44..457.44 rows=7844 width=307) (actual
time=26.044..26.044 rows=7844 loops=1)
- Seq Scan on agent_agent (cost=0.00..457.44
rows=7844 width=307) (actual time=0.024..11.615 rows=7844 loops=1)
Total runtime: 449.260 ms
(8 rows)
--
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] plpgsql returning resultset
Roberts, Jon wrote: PostgreSQL has table partitioning in it so you don't have to dynamically figure out which table to get the data from. I know, but the super table can't handle the number of partition tables I need, 10K-100K tables. Whenever I do a query on the super table, it just aborts. regards thomas -- 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 optimisator deoptimise queries sometime...
postgresql version 8.3: I found issue when optimisator had tried rollup subrequest (without attempt compare final cost with direct plan) and finished with bad plan. The simplest test is below: Preparing data: testdb=# INSERT INTO table2 select (random()*99+1)::integer from generate_series(1,10); testdb=# drop table table2; DROP TABLE testdb=# drop table table1; DROP TABLE testdb=# CREATE TABLE table1 (id serial primary key); CREATE TABLE testdb=# INSERT INTO table1 select generate_series(1,50); INSERT 0 50 testdb=# ANALYZE table1; ANALYZE testdb=# CREATE TABLE table2 (fk integer not null references table1(id)); CREATE TABLE testdb=# INSERT INTO table2 select (random()*49+1)::integer from generate_series(1,5); INSERT 0 5 testdb=# ANALYZE table2; ANALYZE Now lets try execute next query: SELECT table1.id,(select count(*) from table2 where table2.fk=table1.id) from table1 where (select count(*) from table2 where table2.fk=table1.id)1000 testdb=# EXPLAIN ANALYZE SELECT table1.id,(select count(*) from table2 where table2.fk=table1.id) from table1 where (select count(*) from table2 where table2.fk=table1.id)1000; QUERY PLAN Seq Scan on table1 (cost=0.00..56918.96 rows=17 width=4) (actual time=52.576..1450.798 rows=33 loops=1) Filter: ((subplan) 1000) SubPlan - Aggregate (cost=849.50..849.51 rows=1 width=0) (actual time=17.459..17.460 rows=1 loops=50) - Seq Scan on table2 (cost=0.00..847.00 rows=1000 width=0) (actual time=0.029..16.022 rows=1000 loops=50) Filter: (fk = $0) - Aggregate (cost=849.50..849.51 rows=1 width=0) (actual time=17.484..17.486 rows=1 loops=33) - Seq Scan on table2 (cost=0.00..847.00 rows=1000 width=0) (actual time=0.029..16.002 rows=1037 loops=33) Filter: (fk = $0) Total runtime: 1453.577 ms (10 rows) oops... grouping query executing twice per row... but this is ok i think. Lets try more optimal query form (eliminate twice calling count(*) query...): testdb=# EXPLAIN ANALYZE select * from (SELECT table1.id,(select count(*) from table2 where table2.fk=table1.id) as count from table1) as t1 where count1000; QUERY PLAN Seq Scan on table1 (cost=0.00..56918.96 rows=17 width=4) (actual time=53.473..1525.859 rows=33 loops=1) Filter: ((subplan) 1000) SubPlan - Aggregate (cost=849.50..849.51 rows=1 width=0) (actual time=18.445..18.446 rows=1 loops=50) - Seq Scan on table2 (cost=0.00..847.00 rows=1000 width=0) (actual time=0.033..17.001 rows=1000 loops=50) Filter: (fk = $0) - Aggregate (cost=849.50..849.51 rows=1 width=0) (actual time=18.262..18.263 rows=1 loops=33) - Seq Scan on table2 (cost=0.00..847.00 rows=1000 width=0) (actual time=0.033..16.777 rows=1037 loops=33) Filter: (fk = $0) Total runtime: 1526.027 ms (10 rows) Hey... i dont asked rollup subrequest... and calculate subplan twice again per row... Workaround ofcource easy (add offset 0 to subquery) (And lead to lower estimated cost!): testdb=# EXPLAIN ANALYZE select * from (SELECT table1.id,(select count(*) from table2 where table2.fk=table1.id) as count from table1 offset 0) as t1 where count1000; QUERY PLAN Subquery Scan t1 (cost=0.00..42477.75 rows=17 width=12) (actual time=35.393..908.265 rows=33 loops=1) Filter: (t1.count 1000) - Limit (cost=0.00..42477.12 rows=50 width=4) (actual time=16.925..908.092 rows=50 loops=1) - Seq Scan on table1 (cost=0.00..42477.12 rows=50 width=4) (actual time=16.921..907.950 rows=50 loops=1) SubPlan - Aggregate (cost=849.50..849.51 rows=1 width=0) (actual time=18.148..18.149 rows=1 loops=50) - Seq Scan on table2 (cost=0.00..847.00 rows=1000 width=0) (actual time=0.032..16.719 rows=1000 loops=50) Filter: (fk = $0) Total runtime: 908.421 ms (9 rows) So i think it is bug if planner rollup internal query without even try compare cost with direct plan. Writing such not easy to understand workarounds as 'offset 0' i think bad style. == Even worse situation when internal subrequest can produce volatile results: testdb=# EXPLAIN ANALYZE select * from (SELECT table1.id,(select
Re: [GENERAL] Oracle and Postgresql
On Wed, Sep 3, 2008 at 5:56 AM, Robert Treat
[EMAIL PROTECTED]wrote:
On Tuesday 02 September 2008 17:21:12 Asko Oja wrote:
On Tue, Sep 2, 2008 at 2:09 AM, Michael Nolan [EMAIL PROTECTED] wrote:
Oracle handles connecting to multiple databases (even on
multiple/remote
computers) fairly seamlessly, PG does not (yet.)
Stuff we do with plProxy on PostgreSQL is in some respects more advanced
than anything Oracle has to offer :) We have hundreds of databases in
quite
complex network of remote calls and replication.
Yes, but it is also far more complex to install, configure, and use,
compared
to something simple like oracle's dblink, which comes pre-installed, is
simple to set-up, and has a much more straight-forward syntax for use in
day
to day query work.
We are working on these matters and hopefully get some of them solved in 8.4
:)
Configure and use part is NO more complex than Oracle and has several use
cases for which neither of dblinks is suitable.
Or are you claiming that calling functions is not straight forward and
seamless in PostgreSQL.
But yes getting plProxy into the database might be the hurdle for many
potential users.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
Re: [GENERAL] immutable functions and enumerate type casts in indexes
Martijn van Oosterhout ha scritto:
On Tue, Sep 02, 2008 at 10:53:03AM +0200, Edoardo Panfili wrote:
But i have a little question about parameters of enum_out.
Datum enum_out(PG_FUNCTION_ARGS);
this is a part of my function
---
Datum esterna_nome2(PG_FUNCTION_ARGS){
int label;
label = enum_out(fcinfo);
sprintf(debug,false enum_out: \%s\ ,unrolled);
elog(LOG, debug);
---
but it works only because my enum parameter is the first (and using
fcinfo is a little obscure).
Look in the fmgr.h header for functions like DirectFunctionCall1 and
various other ways of calling functions.
Now it works! thank you to Martin and Tom.
this is a code fragment:
--
#include utils/builtins.h
PG_FUNCTION_INFO_V1(esterna_nome);
Datum esterna_nome(PG_FUNCTION_ARGS){
Datum datumIbrido = PG_GETARG_DATUM(0);
Datum labelDatum;
char *label;
labelDatum = DirectFunctionCall1(enum_out,datumIbrido);
label = (char *) DatumGetPointer(labelDatum);
if(strcmp(label,(label_constant))==0){
...
}
...
}
--
I don't know why but I need #include utils/builtins.h
The line label = (char *) DatumGetPointer(labelDatum); is essential to
use the information in strcmp() if I use directly labelDatum it does not
works (but it works inside a sprintf(buffer,%s,labelDatum)).
thank you again
Edoardo
--
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] immutable functions and enumerate type casts in indexes
Edoardo Panfili [EMAIL PROTECTED] writes: labelDatum = DirectFunctionCall1(enum_out,datumIbrido); label = (char *) DatumGetPointer(labelDatum); Just FYI, preferred style for the second line would be label = DatumGetCString(labelDatum); Nearly all standard data types have DatumGetFoo and FooGetDatum macros to hide the conversion details (even if it's only a cast). 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] vacuum analyze hurts performance
aderose [EMAIL PROTECTED] writes: Starting with a database where analyze has never been run I get worse performance after running it -- is there something I'm missing? Well, not basing such a sweeping statement on a single query example would be a good start ;-). This particular plan might have got a little worse but I'm sure some further investigation would show other cases that got better. If you want to nudge it back towards the indexscan plan, reducing random_page_cost a bit would probably do the trick. But that's definitely not a parameter you want to adjust on the basis of only one 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
Re: [GENERAL] immutable functions and enumerate type casts in indexes
Tom Lane ha scritto:
Edoardo Panfili [EMAIL PROTECTED] writes:
labelDatum = DirectFunctionCall1(enum_out,datumIbrido);
label = (char *) DatumGetPointer(labelDatum);
Just FYI, preferred style for the second line would be
label = DatumGetCString(labelDatum);
Nearly all standard data types have DatumGetFoo and FooGetDatum
macros to hide the conversion details (even if it's only a cast).
the clean version:
--
#include utils/builtins.h
PG_FUNCTION_INFO_V1(esterna_nome);
Datum esterna_nome(PG_FUNCTION_ARGS){
Datum datumIbrido = PG_GETARG_DATUM(0);
Datum labelDatum;
char *label;
labelDatum = DirectFunctionCall1(enum_out,datumIbrido);
label = DatumGetCString(labelDatum);
if(strcmp(label,(label_constant))==0){
...
}
...
}
--
thank you again!
Edoardo
--
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] Oracle and Postgresql
I don't know if Oracle changed recently, but the last few times I used it, it was incredibly annoying having to put everything in a subquery to get a LIMIT-type operation to work AFTER the sort, so that you could use their ROWNUM. For example, to get the first 50 rows of a SELECT result. Their ROWNUM worked BEFORE the ORDER BY, so to get the 1st 50 rows, you had to put the query in a subselect, and say SELECT ... where ROWNUM = 50. I love OFFSET ... LIMIT in PostgreSQL! I do a lot of web applications, and it is incredibly handy to page output with. Susan Tiered Data Protection Made Simple http://www.overlandstorage.com/
[GENERAL] hash partitioning
Hi folks, I'm wondering why the postgres planner is not capable of determining the correct partition for a simple select for the following partitioning scheme, in which I'd like to automatically divide rows into four sub-tables, ie, a simple form of hash partitioning. Any ideas why this doesn't work, or a work around to make it work? I would have expected the query plan below to only query the test_1 table. Regards David CREATE TABLE test ( id int not null primary key ); CREATE TABLE test_0 ( CHECK ( id % 4 = 0) ) INHERITS (test); CREATE TABLE test_1 ( CHECK ( id % 4 = 1) ) INHERITS (test); CREATE TABLE test_2 ( CHECK ( id % 4 = 2) ) INHERITS (test); CREATE TABLE test_3 ( CHECK ( id % 4 = 3) ) INHERITS (test); CREATE RULE test_0 AS ON INSERT TO test WHERE ( id % 4 = 0 ) DO INSTEAD INSERT INTO test_0 VALUES ( NEW.id ); CREATE RULE test_1 AS ON INSERT TO test WHERE ( id % 4 = 1 ) DO INSTEAD INSERT INTO test_1 VALUES ( NEW.id ); CREATE RULE test_2 AS ON INSERT TO test WHERE ( id % 4 = 2 ) DO INSTEAD INSERT INTO test_2 VALUES ( NEW.id ); CREATE RULE test_3 AS ON INSERT TO test WHERE ( id % 4 = 3 ) DO INSTEAD INSERT INTO test_3 VALUES ( NEW.id ); insert into test values(1); explain analyse select * from test; Result (cost=0.00..170.00 rows=12000 width=4) (actual time=0.027..0.042 rows=1 loops=1) - Append (cost=0.00..170.00 rows=12000 width=4) (actual time=0.020..0.032 rows=1 loops=1) - Seq Scan on test (cost=0.00..34.00 rows=2400 width=4) (actual time=0.002..0.002 rows=0 loops=1) - Seq Scan on test_0 test (cost=0.00..34.00 rows=2400 width=4) (actual time=0.001..0.001 rows=0 loops=1) - Seq Scan on test_1 test (cost=0.00..34.00 rows=2400 width=4) (actual time=0.007..0.009 rows=1 loops=1) - Seq Scan on test_2 test (cost=0.00..34.00 rows=2400 width=4) (actual time=0.001..0.001 rows=0 loops=1) - Seq Scan on test_3 test (cost=0.00..34.00 rows=2400 width=4) (actual time=0.001..0.001 rows=0 loops=1) Total runtime: 0.115 ms
[GENERAL] Case sensitive full text searching
Hi all:
Is there any way to create a case sensitive full text index? My target
is to make case sensitive full text searches but I don't know how.
I could create a configurario for full text searching:
CREATE TEXT SEARCH CONFIGURATION public.myconfiguration (PARSER =
pg_catalog.default);
CREATE TEXT SEARCH DICTIONARY public.my_dict (
TEMPLATE = pg_catalog.simple,
STOPWORDS = my_stops
);
ALTER TEXT SEARCH CONFIGURATION myconfiguration
ALTER MAPPING FOR asciiword, asciihword, hword_asciipart,
word, hword, hword_part
WITH my_dict;
And then create the full text index:
CREATE INDEX textindexsensible ON documento
USING gin(to_tsvector('myconfiguration',texto));
But default behaviour of Simple Dictionary returns the lower-cased form
of the word if it isn't in stopwords list. Is there any way to change
this behaviour?
Thanks in advance,
Mario Barcala
P.S. I am using PostgreSQL 8.3
--
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] Oracle and Postgresql
On Wed, Sep 3, 2008 at 9:36 AM, [EMAIL PROTECTED] wrote: I don't know if Oracle changed recently, but the last few times I used it, it was incredibly annoying having to put everything in a subquery to get a LIMIT-type operation to work AFTER the sort, so that you could use their ROWNUM. For example, to get the first 50 rows of a SELECT result. Their ROWNUM worked BEFORE the ORDER BY, so to get the 1st 50 rows, you had to put the query in a subselect, and say SELECT ... where ROWNUM = 50. I love OFFSET ... LIMIT in PostgreSQL! I do a lot of web applications, and it is incredibly handy to page output with. Yeah, those kinds of things drove me a little crazy. I think PostgreSQL's equivalent parsing peculiarity is the need to put certain things in an extra set of parens for creating an index. But really that's pretty minor compared to rownum versus order by firing order. One of the things an Oracle DBA coworker of mine found really bothersome at first in pgsql was that suddenly on large queries that he'd never needed an order by on Oracle, he was getting randomly ordered reporting data. He was flabbergasted that something would use an aggregation method that wouldn't result in an ordered set. He was sure it would be a loser with an order by but it was very very fast on large numbers of rows. I think that if you're really used to one database you'll find a new one bothersome in some ways, and a pleasure to use in others. Once I had installed rlwrapper on sqlplus I was ok. It wasn't in the same league as psql, which is the greatest command line sql monitor ever created, but having line editing and history made sqlplus usable. It was cool to hit !alter sess and get my previous default schema statement. All the Oracle DBAs around me were working in guis and they'd walk by and think I in psql, I'd ask them to look at something, like a query, and it would take them about 10 seconds to realize I was working on oracle from the command line. If I could ask for one thing from Oracle it would be that they borrow the basic operational characteristics of psql and stuff it into sqlplus or whatever they use. Having the \ commands and expanded versions in sqlplus would have been awesome. -- 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] hash partitioning
On Wed, Sep 3, 2008 at 10:24 AM, David West [EMAIL PROTECTED] wrote: Hi folks, I'm wondering why the postgres planner is not capable of determining the correct partition for a simple select for the following partitioning scheme, in which I'd like to automatically divide rows into four sub-tables, ie, a simple form of hash partitioning. Have you got constraint_exclusion turned on? -- 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] Case sensitive full text searching
On Wed, 3 Sep 2008, Fco. Mario Barcala Rodr?guez wrote:
Hi all:
Is there any way to create a case sensitive full text index? My target
is to make case sensitive full text searches but I don't know how.
I could create a configurario for full text searching:
CREATE TEXT SEARCH CONFIGURATION public.myconfiguration (PARSER =
pg_catalog.default);
CREATE TEXT SEARCH DICTIONARY public.my_dict (
TEMPLATE = pg_catalog.simple,
STOPWORDS = my_stops
);
ALTER TEXT SEARCH CONFIGURATION myconfiguration
ALTER MAPPING FOR asciiword, asciihword, hword_asciipart,
word, hword, hword_part
WITH my_dict;
And then create the full text index:
CREATE INDEX textindexsensible ON documento
USING gin(to_tsvector('myconfiguration',texto));
But default behaviour of Simple Dictionary returns the lower-cased form
of the word if it isn't in stopwords list. Is there any way to change
this behaviour?
If you don't need any dictionary, why do you use simple dictionary ?
Instead, you can write dummy dictionary, which just checks for stopwords.
Another option, is write simple wrapper around simple dictionary, which
returns array (lexem_from_simple_dic, original_lexem). Then you can
have two different configurations - one for case sensitive search
(uses this wrapper dictionary for query) and another one - for more broader
search.
Looks like we can take this into account in our future work on improving
flexibility of text search.
Oleg
Thanks in advance,
Mario Barcala
P.S. I am using PostgreSQL 8.3
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
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 query not using index: why?
I am looking for records with duplicate keys, so I am running this query: SELECT fileid, COUNT(*) FROM file GROUP BY fileid HAVING COUNT(*)1 The table has an index on fileid (non-unique index) so I am surprised that postgres is doing a table scan. This database is 15GB, and there are a number of fairly large string columns in the table. I am very surprised that scanning the index is not faster than scanning the table. Any thoughts on that? Is scanning the table faster than scanning the index? Is there a reason that it needs anything other than the index? -- 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] hash partitioning
David West [EMAIL PROTECTED] writes: I'm wondering why the postgres planner is not capable of determining the correct partition for a simple select for the following partitioning scheme, The planner doesn't know anything about the behavior of %. Heed the fine manual's advice: Keep the partitioning constraints simple, else the planner may not be able to prove that partitions don't need to be visited. Use simple equality conditions for list partitioning, or simple range tests for range partitioning, as illustrated in the preceding examples. A good rule of thumb is that partitioning constraints should contain only comparisons of the partitioning column(s) to constants using B-tree-indexable operators. 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] hash partitioning
When I attended the PostgreSQL East conference, someone presented a way of doing this that they used for http://www.mailermailer.com/ and they did this: SET constraint_exclusion = on; EXPLAIN SELECT * FROM test WHERE id = 7 AND id % 4 = 3 Their business layer then generated the AND id % 4 = 3 part of the SQL. :( Does anyone know if Oracle or any other database can handle this? Does this work with stored procs? Ex, suppose a stored procedure like this: get_from_test(id int, id_mod_4 int) SELECT id FROM test WHERE id = $1 and id % 4 = $2; Would the optimizer know the correct table to use in that case? David West wrote: Hi folks, I'm wondering why the postgres planner is not capable of determining the correct partition for a simple select for the following partitioning scheme, in which I'd like to automatically divide rows into four sub-tables, ie, a simple form of hash partitioning. Any ideas why this doesn't work, or a work around to make it work? I would have expected the query plan below to only query the test_1 table. Regards David CREATE TABLE test ( id int not null primary key ); CREATE TABLE test_0 ( CHECK ( id % 4 = 0) ) INHERITS (test); CREATE TABLE test_1 ( CHECK ( id % 4 = 1) ) INHERITS (test); CREATE TABLE test_2 ( CHECK ( id % 4 = 2) ) INHERITS (test); CREATE TABLE test_3 ( CHECK ( id % 4 = 3) ) INHERITS (test); CREATE RULE test_0 AS ON INSERT TO test WHERE ( id % 4 = 0 ) DO INSTEAD INSERT INTO test_0 VALUES ( NEW.id ); CREATE RULE test_1 AS ON INSERT TO test WHERE ( id % 4 = 1 ) DO INSTEAD INSERT INTO test_1 VALUES ( NEW.id ); CREATE RULE test_2 AS ON INSERT TO test WHERE ( id % 4 = 2 ) DO INSTEAD INSERT INTO test_2 VALUES ( NEW.id ); CREATE RULE test_3 AS ON INSERT TO test WHERE ( id % 4 = 3 ) DO INSTEAD INSERT INTO test_3 VALUES ( NEW.id ); insert into test values(1); explain analyse select * from test; Result (cost=0.00..170.00 rows=12000 width=4) (actual time=0.027..0.042 rows=1 loops=1) - Append (cost=0.00..170.00 rows=12000 width=4) (actual time=0.020..0.032 rows=1 loops=1) - Seq Scan on test (cost=0.00..34.00 rows=2400 width=4) (actual time=0.002..0.002 rows=0 loops=1) - Seq Scan on test_0 test (cost=0.00..34.00 rows=2400 width=4) (actual time=0.001..0.001 rows=0 loops=1) - Seq Scan on test_1 test (cost=0.00..34.00 rows=2400 width=4) (actual time=0.007..0.009 rows=1 loops=1) - Seq Scan on test_2 test (cost=0.00..34.00 rows=2400 width=4) (actual time=0.001..0.001 rows=0 loops=1) - Seq Scan on test_3 test (cost=0.00..34.00 rows=2400 width=4) (actual time=0.001..0.001 rows=0 loops=1) Total runtime: 0.115 ms
Re: [GENERAL] Simple query not using index: why?
-- Original message -- From: William Garrison [EMAIL PROTECTED] I am looking for records with duplicate keys, so I am running this query: SELECT fileid, COUNT(*) FROM file GROUP BY fileid HAVING COUNT(*)1 The table has an index on fileid (non-unique index) so I am surprised that postgres is doing a table scan. This database is 15GB, and there are a number of fairly large string columns in the table. I am very surprised that scanning the index is not faster than scanning the table. Any thoughts on that? Is scanning the table faster than scanning the index? Is there a reason that it needs anything other than the index? I may be missing something, but it would have to scan the entire table to get all the occurrences of each fileid in order to do the count(*). -- Adrian Klaver [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Testers: 8.4 snapshot RPMs are available
Hi, If you are a Fedora-9 or RHEL/CentOS 5 user and want to test new features of PostgreSQL 8.4 and help development team, you may use the packages that I have just released, based on today's CVS snapshot. I am planning to push new packages each weekend during commitfest. Please note that these packages are not built using the official tarball. I built it with the help of some PostgreSQL hackers. These are not stable packages. Use them at your own risk. They are *not* production ready. As usual, the RPMs are available at http://yum.pgsqlrpms.org . Please install repository RPMs from here first: http://yum.pgsqlrpms.org/reporpms/repoview/letter_p.group.html Remove existing RPMs after taking your backups, and then install 8.4 packages. Current set is labeled as 8.4devel_03092008-1PGDG . You may want to install -debuginfo RPM, which may help PostgreSQL developers to get some data in case you find a bug. Source RPMs are also available: http://yum.pgsqlrpms.org/srpms/8.4 (and they are installable using yum). Please report any packaging related errors to me. If you find any PostgreSQL 8.4 bugs, please post them to [EMAIL PROTECTED] or fill this form: http://www.postgresql.org/support/submitbug Regards, -- Devrim GÜNDÜZ, RHCE devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org signature.asc Description: This is a digitally signed message part
[GENERAL] offtopic, about subject prefix
Hello, I'm new to this mailing list, and I have a couple of questions: Is it really necessary to add the [GENERAL] prefix? Are messages without this prefix likely to be ignored by automatic filters or something like that? Thanks in advance.
Re: [GENERAL] offtopic, about subject prefix
Fernando Moreno wrote: Hello, I'm new to this mailing list, and I have a couple of questions: Is it really necessary to add the [GENERAL] prefix? The prefix is added by the mailing list software. It's there so that people subscribed to multiple pgsql-* lists can easily distinguish them. There's no need to include it in your messages. b -- 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] hash partitioning
On Wed, Sep 3, 2008 at 10:36 AM, William Garrison [EMAIL PROTECTED] wrote: When I attended the PostgreSQL East conference, someone presented a way of doing this that they used for http://www.mailermailer.com/ and they did this: SET constraint_exclusion = on; EXPLAIN SELECT * FROM test WHERE id = 7 AND id % 4 = 3 Their business layer then generated the AND id % 4 = 3 part of the SQL. :( Does anyone know if Oracle or any other database can handle this? Oracle has support for hash partitioning like so: CREATE TABLE sales_hash (salesman_id NUMBER(5), salesman_name VARCHAR2(30), sales_amount NUMBER(10), week_no NUMBER(2)) PARTITION BY HASH(salesman_id) PARTITIONS 4 STORE IN (data1, data2, data3, data4); There is no need to specify which partition to search or reference any hash function in queries, it's all magic. David West wrote: Hi folks, I'm wondering why the postgres planner is not capable of determining the correct partition for a simple select for the following partitioning scheme, in which I'd like to automatically divide rows into four sub-tables, ie, a simple form of hash partitioning. Any ideas why this doesn't work, or a work around to make it work? I would have expected the query plan below to only query the test_1 table. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SELECT INTO returns incorrect values
Bill, Did you try it like this: parent_id = 0 category_name = '' select category, parent_category_id from note.category where category_id = 477 into category_name, parent_id; raise notice 'curr cat, name, parent id: % % ', category_name, parent_id; I have found in the past that it's a good idea to initialize your vars before you use them in PL/pgsql. Also as a FYI, you don't need to upper case all your text in a function (I know you have to do that in Firebird), just use standard case with normal capitalization because PostgreSQL will lowercase everything you send to the server that is not in quotes. It's a lot easier to read without the uppercase. Later, Tony Caduto AM Software Design Home of Lightning Admin for PostgreSQL http://www.amsoftwaredesign.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] Simple query not using index: why?
Can't it just scan the index to get that? I assumed the index had links to every fileid in the table. In my over-simplified imagination, the table looks like this: ctid|fileid|column|column|column|column ctid|fileid|column|column|column|column ctid|fileid|column|column|column|column ctid|fileid|column|column|column|column etc. While the index looks like fileid|ctid fileid|ctid fileid|ctid fileid|ctid ... So I expected scanning the index was faster, and still had everything it needed to do the count. Or perhaps it was because I said COUNT(*) so it needs to look at the other columns in the table? I really just wanted the number of hits not the number of records with distinct values or anything like that. My understanding was that COUNT(*) did that, and didn't really look at the columns themselves. Adrian Klaver wrote: -- Original message -- From: William Garrison [EMAIL PROTECTED] I am looking for records with duplicate keys, so I am running this query: SELECT fileid, COUNT(*) FROM file GROUP BY fileid HAVING COUNT(*)1 The table has an index on fileid (non-unique index) so I am surprised that postgres is doing a table scan. This database is 15GB, and there are a number of fairly large string columns in the table. I am very surprised that scanning the index is not faster than scanning the table. Any thoughts on that? Is scanning the table faster than scanning the index? Is there a reason that it needs anything other than the index? I may be missing something, but it would have to scan the entire table to get all the occurrences of each fileid in order to do the count(*). -- Adrian Klaver [EMAIL PROTECTED]
Re: [GENERAL] Simple query not using index: why?
On Wed, 03 Sep 2008 15:55:17 -0400 William Garrison [EMAIL PROTECTED] wrote: So I expected scanning the index was faster, and still had everything it needed to do the count. Or perhaps it was because I said COUNT(*) so it needs to look at the other columns in the table? I really just wanted the number of hits not the number of records with distinct values or anything like that. My understanding was that COUNT(*) did that, and didn't really look at the columns themselves. We do not have visibility information in the index, so we have to scan the pages to see what tuples are live or dead (and thus count them). Sincerely, Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- 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] Oracle and Postgresql
David Fetter wrote: What they want to have is a huge entity they can blame when everything goes wrong. They're not interested in the actual response times or even in the much more important time-to-fix because once they've blamed Oracle, they know the responsibility is no longer on their shoulders. That is only a perceived sense of risk avoidance, if you read the EULA etc that ship with Oracle, MS SQL server etc, they are not responsible for anything that may happen to your data. Sure management could blame them, but that's about it. They would get the same amount of satisfaction from blaming the FOSS community. No matter what management says any blame rests squarely on their shoulders and the people they have entrusted to create their corp projects/products when something goes wrong. -- 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] Oracle and Postgresql
On Wed, Sep 3, 2008 at 2:07 PM, Tony Caduto [EMAIL PROTECTED] wrote: David Fetter wrote: What they want to have is a huge entity they can blame when everything goes wrong. They're not interested in the actual response times or even in the much more important time-to-fix because once they've blamed Oracle, they know the responsibility is no longer on their shoulders. That is only a perceived sense of risk avoidance, if you read the EULA etc that ship with Oracle, MS SQL server etc, they are not responsible for anything that may happen to your data. Sure management could blame them, but that's about it. They would get the same amount of satisfaction from blaming the FOSS community. No matter what management says any blame rests squarely on their shoulders and the people they have entrusted to create their corp projects/products when something goes wrong. I had a boss (great one, really) who was getting a lot of crap from upstairs about our use of open source, and that was one of the PHB's arguments above him. When he parroted the thing about having someone to blame, I asked which would let him sleep better at night, having someone to blame, or a system that didn't break? I made it clear that all he'd get was someone to blame, and nothing else. At that time I'd tested several commercial components and found them wanting compared to open source. One component was LDAP, and at the time, OpenLDAP was a solid, fast, lightweight choice, while everything else was a hog. The recommended memory for a linux OpenLDAP server was something like 256M, while the memory for the big name vendor was something like 1 Gig minimum, with recommendations for 2Gig or more. I think he repeated what I'd said to him to upper management, and we went ahead and installed OpenLDAP, and it was still running complaint free the day I left years later. -- 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] Conflict between MVCC and manual locking
On Sep 2, 2008, at 4:16 AM, jose lawrence wrote: HI, I want to get more information whether MVCC conflicts with manual locking ? Have you read the chapter on MVCC and locks? http://www.postgresql.org/docs/8.2/interactive/mvcc.html Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability (415) 963-4410 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgres does not start, gives no error
HI, I am facing this peciliar problem.I am using postgres 7.2.2 installed on solaris. It has been running very well since all the time, until somebody tried to stop it. Using the command Now it does not start. On giving the start command its says: /home/data/www/pg7/bin/pg_ctl: 5432: not found postmaster successfully started But when i check the status, it says postmaster is not running. Also, if I try to stop it, i get the expected error: pg_ctl: cannot find /home/data/www/pg7/data/postmaster.pid Is postmaster running? I have realised that when I give the start command, even though the msg is successfully started, the postmaster.pid file is not created.. Am i missing something.? Any help will be appreciated.
[GENERAL] Problem with trigger function
Hi,
I wrote the trigger function below and when trying to execute it, I
get the following error:
15:00:42 [CREATE - 0 row(s), 0.000 secs] [Error Code: 0, SQL
State: 42601] ERROR: syntax error at or near INSERT
I am using DBVisualizer's SQL Commander window, not the create
funciton option. I know the SQL is correct - I can execute it just
by itself. The problem is somewhere in the
syntax for a postgres trigger function. Any input on what the problem
might be would be just fabulous!
Many thanks,
Mira
CREATE OR REPLACE FUNCTION audit_sequence_update() RETURNS trigger AS '
BEGIN
IF tg_op = ''INSERT'' THEN
INSERT INTO audit_sequence_update(operation, day,
owner_uid, sequence)
VALUES
('INSERT', NEW.day, NEW.owner_uid, NEW.sequence);
ELSE
INSERT INTO audit_sequence_update(operation, day,
owner_uid, sequence)
SELECT
'UPDATE', day, owner_uid, sequence
FROM sequence where isdid=NEW.isdid;
END IF;
RETURN NEW;
END
' LANGUAGE plpgsql;
--
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 does not start, gives no error
On 03/09/2008 20:11, Akhtar Yasmin-B05532 wrote: I am facing this peciliar problem.I am using postgres 7.2.2 installed on solaris. I know this isn't helpful to your particular problem, but 7.2.* is VERY old - in fact, I don't think it's even maintained any more. Also, the command you used to start PG didn't come though in your email - can you re-post it please? Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] 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] Postgres does not start, gives no error
Hi, I am using the following command to start the database: pg_ctl start -p 5432 -D /home/data/www/pg7/data Yes I know, 7.2 is a very old version, but that's what we have to work with for now.. :-( -Original Message- From: Raymond O'Donnell [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 03, 2008 2:54 PM To: Akhtar Yasmin-B05532 Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Postgres does not start, gives no error On 03/09/2008 20:11, Akhtar Yasmin-B05532 wrote: I am facing this peciliar problem.I am using postgres 7.2.2 installed on solaris. I know this isn't helpful to your particular problem, but 7.2.* is VERY old - in fact, I don't think it's even maintained any more. Also, the command you used to start PG didn't come though in your email - can you re-post it please? Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] 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] Problem with trigger function
On Wed, 3 Sep 2008, Mira Dimitrijevic wrote:
Hi,
I wrote the trigger function below and when trying to execute it, I
get the following error:
15:00:42 [CREATE - 0 row(s), 0.000 secs] [Error Code: 0, SQL
State: 42601] ERROR: syntax error at or near INSERT
I am using DBVisualizer's SQL Commander window, not the create
funciton option. I know the SQL is correct - I can execute it just
by itself. The problem is somewhere in the
syntax for a postgres trigger function. Any input on what the problem
might be would be just fabulous!
It looks like you're not always quoting properly if the below is an exact
quote. For example you double the single quotes in the IF tg_op =
''INSERT'' but don't double them in the VALUES ('INSERT').
You might find it easier to use dollar quoting for the function body
(using $$ instead of ' to wrap the body) if you're using a version
that supports it which will mean you shouldn't need to double any of the
quotes inside the function.
CREATE OR REPLACE FUNCTION audit_sequence_update() RETURNS trigger AS '
BEGIN
IF tg_op = ''INSERT'' THEN
INSERT INTO audit_sequence_update(operation, day,
owner_uid, sequence)
VALUES
('INSERT', NEW.day, NEW.owner_uid, NEW.sequence);
ELSE
INSERT INTO audit_sequence_update(operation, day,
owner_uid, sequence)
SELECT
'UPDATE', day, owner_uid, sequence
FROM sequence where isdid=NEW.isdid;
END IF;
RETURN NEW;
END
' LANGUAGE plpgsql;
--
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 does not start, gives no error
On Wed, 3 Sep 2008 14:58:45 -0700 Akhtar Yasmin-B05532 [EMAIL PROTECTED] wrote: Hi, I am using the following command to start the database: pg_ctl start -p 5432 -D /home/data/www/pg7/data Yes I know, 7.2 is a very old version, but that's what we have to work with for now.. So what does your logging say? Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- 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 does not start, gives no error
On Wed, 3 Sep 2008 15:19:25 -0700 Akhtar Yasmin-B05532 [EMAIL PROTECTED] wrote: It says, pg_ctl: 5432: not found postmaster successfully started pg_ctl doesn't take a -p argument for the port. -p is reserved for the path to the postmaster. Try: pg_ctl -D /home/data/www/pg7/data start Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- 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 does not start, gives no error
Even the command pg_ctl -D /home/data/www/pg7/data start Does not start the server, I mean the message I get on running the command, is: postmaster successfully started But the status still shows, pg_ctl: postmaster or postgres is not running Also when I try to access psql, it gives me the following error.. psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? -Original Message- From: Joshua Drake [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 03, 2008 3:25 PM To: Akhtar Yasmin-B05532 Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Postgres does not start, gives no error On Wed, 3 Sep 2008 15:19:25 -0700 Akhtar Yasmin-B05532 [EMAIL PROTECTED] wrote: It says, pg_ctl: 5432: not found postmaster successfully started pg_ctl doesn't take a -p argument for the port. -p is reserved for the path to the postmaster. Try: pg_ctl -D /home/data/www/pg7/data start Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- 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 does not start, gives no error
It says, pg_ctl: 5432: not found postmaster successfully started But when I check the status, by using the command pg_ctl status, Its says, pg_ctl: postmaster or postgres is not running Any idea when the postmaster.pid file is created?, because after giving the start command I cannot see the .pid file in the directory it is supposed to be. Logically, I am assuming that, when the start command is given, pg_ctl start, the postmaster.pid file gets created. But this does not happen in my case, is there a way to debug, whether any script has gotten corrupted? -Original Message- From: Joshua Drake [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 03, 2008 3:13 PM To: Akhtar Yasmin-B05532 Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Postgres does not start, gives no error On Wed, 3 Sep 2008 14:58:45 -0700 Akhtar Yasmin-B05532 [EMAIL PROTECTED] wrote: Hi, I am using the following command to start the database: pg_ctl start -p 5432 -D /home/data/www/pg7/data Yes I know, 7.2 is a very old version, but that's what we have to work with for now.. So what does your logging say? Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- 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 does not start, gives no error
On Wed, 3 Sep 2008 15:34:26 -0700 Akhtar Yasmin-B05532 [EMAIL PROTECTED] wrote: Even the command pg_ctl -D /home/data/www/pg7/data start Does not start the server, I mean the message I get on running the command, is: postmaster successfully started O.k. what does the log say? Joshua D. Drake But the status still shows, pg_ctl: postmaster or postgres is not running Also when I try to access psql, it gives me the following error.. psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? -Original Message- From: Joshua Drake [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 03, 2008 3:25 PM To: Akhtar Yasmin-B05532 Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Postgres does not start, gives no error On Wed, 3 Sep 2008 15:19:25 -0700 Akhtar Yasmin-B05532 [EMAIL PROTECTED] wrote: It says, pg_ctl: 5432: not found postmaster successfully started pg_ctl doesn't take a -p argument for the port. -p is reserved for the path to the postmaster. Try: pg_ctl -D /home/data/www/pg7/data start Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- 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 does not start, gives no error
The log says, postmaster successfully started But I don't think its started as, while connecting to db, I get the following error in the logs as, psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? -Original Message- From: Joshua Drake [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 03, 2008 3:45 PM To: Akhtar Yasmin-B05532 Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Postgres does not start, gives no error On Wed, 3 Sep 2008 15:34:26 -0700 Akhtar Yasmin-B05532 [EMAIL PROTECTED] wrote: Even the command pg_ctl -D /home/data/www/pg7/data start Does not start the server, I mean the message I get on running the command, is: postmaster successfully started O.k. what does the log say? Joshua D. Drake But the status still shows, pg_ctl: postmaster or postgres is not running Also when I try to access psql, it gives me the following error.. psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? -Original Message- From: Joshua Drake [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 03, 2008 3:25 PM To: Akhtar Yasmin-B05532 Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Postgres does not start, gives no error On Wed, 3 Sep 2008 15:19:25 -0700 Akhtar Yasmin-B05532 [EMAIL PROTECTED] wrote: It says, pg_ctl: 5432: not found postmaster successfully started pg_ctl doesn't take a -p argument for the port. -p is reserved for the path to the postmaster. Try: pg_ctl -D /home/data/www/pg7/data start Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Compiling C Trigger function
Hello, I am attempting to compile a C trigger function for use with Postgresql. The function uses SPI and I am comiling in linux using gcc. The compiler is finding all the correct headers but complains that it can't find the SPI functions. I am compiling with -lpq . Do I need to compile with additional library flags? If so, what libraries do I need to link with? Thanks Peter -- 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 does not start, gives no error
Akhtar Yasmin-B05532 [EMAIL PROTECTED] writes: The log says, postmaster successfully started That's not the postmaster's log, that's just pg_ctl saying it managed to find and exec the postmaster. I speculate that you are sending the log to /dev/null which is why you cannot find out anything about what's wrong. You might try invoking the postmaster manually: postmaster -D whateveritwas which should let the error message come out on your terminal. regards, tom lane PS: please don't top post. -- 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 does not start, gives no error
Hi, Thanks for the prompt reply, I tried postmaster -D /home/data/www/pg7/data, but the error message still doesn't appear. Nothing really happens after this command. Is there a way I can find where the errors are logging..? Thanks n regards -Original Message- You might try invoking the postmaster manually: postmaster -D whateveritwas which should let the error message come out on your terminal. regards, tom lane PS: please don't top post. -- 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 does not start, gives no error
Akhtar Yasmin-B05532 [EMAIL PROTECTED] writes: I tried postmaster -D /home/data/www/pg7/data, but the error message still doesn't appear. Maybe you have it configured to log to syslog? Look in postgresql.conf. 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] Oracle and Postgresql
Oh, as I was writing a CUBE query today and realized that I forgot to mention this. And unlike most gripes, like MERGE INTO or CTE's which are really convenience things, this is key piece of functionality that you just can't reproduce in Postgres. That said, there's not the same sense of community when it comes to Oracle. And how many of you have ever asked a question and had it answered by the Oracle equivalent of 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
