[GENERAL] a performence question
Hi, Maybe someone on this list actually have already tried this: I'm planning to make a partitioned database. From Postgres documentation I can see, that there are basically two methods to route INSERTS into partitioned table: one. is a TRIGGER other. is a RULE My Table will have over 1000 partitions. Some not so big, but significant number of them will be of multimillion rows. Partitioning will be done using a single column, on equality meaning: CREATE TABLE mainlog (sel int, tm timestamp, info text,...); CREATE TABLE mainlog_p1 (CHECK (sel=1)) INHERITS (mainlog); CREATE TABLE mainlog_p2 (CHECK (sel=2)) INHERITS (mainlog); ...etc. If I route INSERT with a TRIGGER, the function would look like: CREATE TRIGGER...AS $$ DECLARE x RECORD; BEGIN SELECT id INTO x FROM current_route; NEW.sel := x.id; IF NEW.sel = 1 THEN INSERT INTO mainlog_p1 VALUES (NEW.*); ELSE IF NEW.sel = 2 THEN INSERT INTO mainlog_p2 VALUES (NEW.*); END IF; RETURN NULL; $$; If I route INSETS with a RULE, I'd have something like 1000 rules hooked up to MAINLOG, all looking like: CREATE RULE ON INSERT ... WHERE EXISTS(SELECT 1 FROM current_route WHERE id = 1) DO INSTEAD INSERT INTO mainlog_p1 VALUES SELECT x.id,new.tm... FROM (SELECT id FROM current_route) x; ... and similar RULES for cases WHERE id = 2, etc. My question is, where should I expect better performance on those INSERTS). I would prefer a set of RULES (as I wouldn't like to rewrite TRIGGER function every time I add a partition ... a thousand lines function), but since they all must make a select query on CURRENT_ROUTE table, may be that will not be particularly effective? The TRIGGER function does a single query - may be it'll be faster? I was planning to generate some dummy data and run a simulation, but may be someone already has that experience? Or maybe the TRIGGER should look differently? Or the set of RULES? And on a similar token: Is there a way to RAISE an exception *within* RULE definition? The only way I could imagine to achieve that is to create a function raise_exception(), and make a SELECT raise_eception(); in such case. Within a RULE I cannot CREATE TABLE, EXECUTE, RAISE, etc or there is a way but I just don't know it without artificial use of SELECT function()? -R -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Getting affected rows in pgplsql
I'm writing some ETL procedures in pgplsql. After each insert/update/delete, I'd like to log how many rows were affected. I'm not finding anything on Google. Does anyone know how to get 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] Getting affected rows in pgplsql
Maybe this will be helpfull: http://www.postgresql.org/docs/8.3/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS Regards, Blazej Oleszkiewicz 2008/9/4 Artacus [EMAIL PROTECTED]: I'm writing some ETL procedures in pgplsql. After each insert/update/delete, I'd like to log how many rows were affected. I'm not finding anything on Google. Does anyone know how to get this? -- 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] Getting affected rows in pgplsql
On Thu, Sep 04, 2008 at 02:07:01AM -0700, Artacus wrote: I'm writing some ETL procedures in pgplsql. After each insert/update/delete, I'd like to log how many rows were affected. http://www.postgresql.org/docs/current/interactive/plpgsql-statements.html search for GET DIAGNOSTICS Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: [EMAIL PROTECTED] / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- 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? I have. http://asktom.oracle.com But I've had better luck with Tom. :) Jon -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] xml2 vs XMLFunctions
Hi,
I am currently using xml2 functionality in PostgreSQL 8.3.x and want to
substitute it by the newer API as mentioned here:
From PostgreSQL 8.3 on, there is XML-related functionality based on the
SQL/XML standard in the core server. That functionality covers XML syntax
checking and XPath queries, which is
what this module does, and more, but the API is not at all compatible. It
is planned that this module will be removed in PostgreSQL 8.4 in favor of
the newer standard API, so you are
encouraged to try converting your applications. If you find that some of
the functionality of this module is not available in an adequate form with
the newer API, please explain your
issue to [EMAIL PROTECTED] so that the deficiency can be
addressed.
The only function of xml2 I ever used was xpath_bool(document,query)
returns bool where document represents a table column. How can I do this
with the new API ?
I thought that 9.14.2. Processing XML: xpath(xpath, xml[, nsarray]) might
be the right function, but I don't know how to specify the column where the
xml is stored.
Can you please give me an example ? I only have to implement xpath functions
that return Boolean. I'd like to query - SELECT foo FROM bar AS x WHERE
xpath('xpath', x.xmlcolumn [,nsarray])
Cheers, Tobias
[GENERAL] xpath_bool_ns() and xml2
Hi, does anybody know how to use the xml2 function xpath_bool with namespaces. I have used this function successfully as long as my xml documents haven't contained namespaces. I searched with google and found some readme file where the function xpath_bool_ns was available that would probably resolve my namespace aware xml xpath issue, but this function isn't contained in the pqxml.dll that comes with postgres 8.3.3. Can anybody help me or point some other solution ? I just want to do some xpath queries on a table column that returns a Boolean as result. Cheers, Tobias
[GENERAL] Changes for version 8.4
Where can I find a list of changes for Version 8.4 of postgres? Paul Tilles -- 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] Changes for version 8.4
paul tilles wrote: Where can I find a list of changes for Version 8.4 of postgres? It's not officially written anywhere. As a starting point you can look here: http://wiki.postgresql.org/wiki/Category:CommitFest Then look at each Commitfest:2008:xx page, and see the list of committed patches. Also, note that a certain number of patches have gone in without being listed there (most notably, a huge improvement in how EXISTS queries are handled). The definitive place, of course, is the CVS logs. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Changes for version 8.4
Is there a date for the release of 8.4 ? joao On Thu, 2008-09-04 at 10:09 -0400, Alvaro Herrera wrote: paul tilles wrote: Where can I find a list of changes for Version 8.4 of postgres? It's not officially written anywhere. As a starting point you can look here: http://wiki.postgresql.org/wiki/Category:CommitFest Then look at each Commitfest:2008:xx page, and see the list of committed patches. Also, note that a certain number of patches have gone in without being listed there (most notably, a huge improvement in how EXISTS queries are handled). The definitive place, of course, is the CVS logs. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Changes for version 8.4
Joao Ferreira gmail escribió: Is there a date for the release of 8.4 ? http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Development_Plan -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] You need to rebuild PostgreSQL using --with-libxml.
Buenos Dias, necesito saber los pasos para recompilar con soporte para xml, en redhat 4 entreprise y postgres 8.3. Gracias, .
Re: [GENERAL] You need to rebuild PostgreSQL using --with-libxml.
Hi, On Thu, 2008-09-04 at 10:18 -0430, Ricardo Antonio Yepez Jimenez wrote: Buenos Dias, necesito saber los pasos para recompilar con soporte para xml, en redhat 4 entreprise y postgres 8.3. You cannot compile PostgreSQL 8.3 on RHEL 4 with xml support -- unless you install libxml2 from sources. RHEL ships 2.6.16 version of libxml, but PostgreSQL requires 2.6.23 at least. 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
Re: [GENERAL] Changes for version 8.4
On Thu, 2008-09-04 at 10:45 -0400, Alvaro Herrera wrote: Joao Ferreira gmail escribió: Is there a date for the release of 8.4 ? http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Development_Plan /me notes that noone responded like It will be released when it is ready. -- 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
Re: [GENERAL] Postgres does not start, gives no error
--- On Wed, 9/3/08, Akhtar Yasmin-B05532 [EMAIL PROTECTED] wrote: From: Akhtar Yasmin-B05532 [EMAIL PROTECTED] Subject: Re: [GENERAL] Postgres does not start, gives no error To: Tom Lane [EMAIL PROTECTED] Cc: Joshua Drake [EMAIL PROTECTED], pgsql-general@postgresql.org Date: Wednesday, September 3, 2008, 11:22 PM 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 check the log of the OS and the permissions of the directory /home/data/www/pg7/data -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 -- 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
* [EMAIL PROTECTED] (Richard Broersma) wrote: On Sun, Aug 31, 2008 at 1:50 PM, Kevin Hunter [EMAIL PROTECTED] wrote: 7. Though I don't personally buy it, I have heard others complain loudly that there is no print-version of Postgres documentation. This one should be taken off the list. The postgresql online reference manual is in print( volumes 1 - 3) http://www.amazon.com/PostgreSQL-Reference-Manual-SQL-Language/dp/0954612027/ref=pd_sim_b_1 Though I mostly use the online version, I'm considering buying these. However, I'm probably going to kill myself if I find a new edition for sale only a short while after I've bought them. Are there any plans to release an updated set in the near future? -- Lars Haugseth If anyone disagrees with anything I say, I am quite prepared not only to retract it, but also to deny under oath that I ever said it. -Tom Lehrer -- 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, 9/3/08, Akhtar Yasmin-B05532 [EMAIL PROTECTED] wrote: From: Akhtar Yasmin-B05532 [EMAIL PROTECTED] Subject: [GENERAL] Postgres does not start, gives no error To: pgsql-general@postgresql.org Date: Wednesday, September 3, 2008, 7:11 PM 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? exist the file postmaster.pid in your directory? 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to test something using ROLLBACK TRANSACTION
Coming from MS SQL server, if I ever change anything vital on a production system, or do any kind of major hackery on my own, I wrap it in a transaction first: BEGIN TRANSACTION; DELETE FROM vital_information WHERE primary_key = 10; ROLLBACK TRANSACTION; I then make sure that the result comes back and says 1 row(s) modified or something equally reassuring. I have horror stories where DBAs fat-fingered something and deleted data. But when I do this in pgadmin3, I get a dissatisfying result: Query returned successfully with no result in 15 ms. This response isn't wrong really... but it is not what I was hoping for. Any way to get the result of the commands that were inside the transaction? -- 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] How to test something using ROLLBACK TRANSACTION
On Thu, Sep 4, 2008 at 1:17 PM, William Garrison [EMAIL PROTECTED] wrote: Coming from MS SQL server, if I ever change anything vital on a production system, or do any kind of major hackery on my own, I wrap it in a transaction first: BEGIN TRANSACTION; DELETE FROM vital_information WHERE primary_key = 10; ROLLBACK TRANSACTION; I then make sure that the result comes back and says 1 row(s) modified or something equally reassuring. I have horror stories where DBAs fat-fingered something and deleted data. But when I do this in pgadmin3, I get a dissatisfying result: Have you tried psql? That's all I usually use. Here's what I get from inside psql: smarlowe=# begin; BEGIN smarlowe=# delete from test where i between 4 and 6; DELETE 3 smarlowe=# rollback; ROLLBACK -- 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
exist the file postmaster.pid in your directory? No the postmaster.pid does not exist in the directory, even when I start the server, it does not appear, I am assuming, that this file is present only when postmaster in running. check the log of the OS and the permissions of the directory /home/data/www/pg7/data I cannot check the logs, as we are not logging postgres, also the directory permissions for /home/data/www/pg7/data is 700 Thanks.. -- 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] How to test something using ROLLBACK TRANSACTION
On Thu, Sep 4, 2008 at 12:28 PM, Scott Marlowe [EMAIL PROTECTED] wrote: Have you tried psql? That's all I usually use. Here's what I get The only problem with psql is that it is addictive. Once your hooked, it is hard to use anything else. :o) -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Full outer join? Cross product? How to blend two queries into single row?
All, I want to do something simple and the terminology is slipping me. I want to execute two separate queries that should return 0 or 1 rows and I want to join the results of those queries into a single row. SELECT a.col1, a.col2 FROM mytable a WHERE a.uid = 'abc'; SELECT b.col3, b.col4 FROM mytable b WHERE b.uid = 'def'; But somehow get a result like this: SELECT a.col1, a.col2, b.col3, b.col4 FROM mytable a, mytable b WHERE a.uid = 'abc' AND b.uid = 'def'; That query works when both a.uid and b.uid match but I want to get results even when a.uid matches but b.uid does NOT match and vice versa. Just make a.col1 and a.col2 NULL when a.uid does not match or make b.col3 and b.col4 NULL when b.uid does not match. All 4 can be NULL or no rows returned if no matches. I came up with this query that works, but seems overly complicated: SELECT a.col1, a.col2, b.col3, b.col4 FROM (SELECT col1, col3, TRUE AS join_column FROM mytable WHERE uid = 'abc') a FULL OUTER JOIN (SELECT col3, col4, TRUE AS join_column FROM mytable WHERE uid = 'def') b ON (a.join_column = b.join_column); Is this how to do it, or is there a simpler syntax I'm missing? What's the formal terminology for what I'm doing here? Is there a way to specify a FULL OUTER JOIN without naming the join column since I don't need one? -- Dante -- D. Dante Lorenso [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
Re: [GENERAL] Postgres does not start, gives no error
On Thu, Sep 4, 2008 at 12:53 PM, Akhtar Yasmin-B05532 [EMAIL PROTECTED] wrote: exist the file postmaster.pid in your directory? No the postmaster.pid does not exist in the directory, even when I start the server, it does not appear, I am assuming, that this file is present only when postmaster in running. check the log of the OS and the permissions of the directory /home/data/www/pg7/data I cannot check the logs, as we are not logging postgres, also the directory permissions for /home/data/www/pg7/data is 700 Can you reconfigure postgresql to log to stderr for a minute and then try to start it in single user mode and see what it says? -- 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] Full outer join? Cross product? How to blend two queries into single row?
On Thu, Sep 04, 2008 at 03:43:33PM -0500, D. Dante Lorenso wrote: I came up with this query that works, but seems overly complicated: SELECT a.col1, a.col2, b.col3, b.col4 FROM (SELECT col1, col3, TRUE AS join_column FROM mytable WHERE uid = 'abc') a FULL OUTER JOIN (SELECT col3, col4, TRUE AS join_column FROM mytable WHERE uid = 'def') b ON (a.join_column = b.join_column); Is this how to do it, or is there a simpler syntax I'm missing? The ON clause is just a normal expression, so you can just put a TRUE in there if you want a cross join. I.e. the following is a minimal full outer cross join: SELECT * FROM foo FULL OUTER JOIN bar ON TRUE; This still seems a little nasty and I'd prefer to do something like: SELECT ((SELECT ROW(a,b) FROM foo)).*, ((SELECT ROW(c,d) FROM bar)).*; And have it do the same thing (if you have more than one row returned you'd get a nice error message and everything). But I can't seem to get the syntax right, anyone got a cluebat? Sam -- 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] Full outer join? Cross product? How to blend two queries into single row?
On Thu, Sep 4, 2008 at 3:22 PM, Sam Mason [EMAIL PROTECTED] wrote: On Thu, Sep 04, 2008 at 03:43:33PM -0500, D. Dante Lorenso wrote: I came up with this query that works, but seems overly complicated: SELECT a.col1, a.col2, b.col3, b.col4 FROM (SELECT col1, col3, TRUE AS join_column FROM mytable WHERE uid = 'abc') a FULL OUTER JOIN (SELECT col3, col4, TRUE AS join_column FROM mytable WHERE uid = 'def') b ON (a.join_column = b.join_column); Is this how to do it, or is there a simpler syntax I'm missing? The ON clause is just a normal expression, so you can just put a TRUE in there if you want a cross join. I.e. the following is a minimal full outer cross join: SELECT * FROM foo FULL OUTER JOIN bar ON TRUE; can't you just drop the on clause altogether? -- 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] Full outer join? Cross product? How to blend two queries into single row?
On Thu, Sep 4, 2008 at 2:22 PM, Sam Mason [EMAIL PROTECTED] wrote: This still seems a little nasty and I'd prefer to do something like: my idea seems nastiest of all: SELECT * FROM ( SELECT COUNT(*) AS rownbr, A1.col1, A1.col2 FROM Mytable AS A1 INNER JOIN Mytable AS A2 ON A1.pkey A2.pkey AND A1.uid = A2.uid WHERE A1.uid = 'abc' GROUP BY A1.col1, A1.col2 ) AS Y( rownbr, col1, col2 ) FULL JOIN ( SELECT COUNT(*) AS rownbr, B1.col3, B1.col4 FROM Mytable AS B1 INNER JOIN Mytable AS B2 ON B1.pkey B2.pkey AND B1.uid = B2.uid WHERE A1.uid = 'def' GROUP BY B1.col3, B1.col4 ) AS Z( rownbr, col3, col4 ) ON Y.rownbr = Z.rownbr; but I am pretty sure that this air code works. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Java class to manage a hstore?
Hey All, anybody happen to know if there is a java class 'somewhere' to insert/ update a hstore field in PostgreSQL? Ries -- 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] Full outer join? Cross product? How to blend two queries into single row?
On Thu, Sep 04, 2008 at 03:26:39PM -0600, Scott Marlowe wrote: On Thu, Sep 4, 2008 at 3:22 PM, Sam Mason [EMAIL PROTECTED] wrote: The ON clause is just a normal expression, so you can just put a TRUE in there if you want a cross join. I.e. the following is a minimal full outer cross join: SELECT * FROM foo FULL OUTER JOIN bar ON TRUE; can't you just drop the on clause altogether? I'm pretty sure you can't, what would be the point of a CROSS JOIN otherwise? This does raise the point that the SQL standard's CROSS JOIN is somewhat limited. It seems to make sense to allow the CROSS somewhere in any join type and meaning that an ON or USING isn't expected, making it valid to say: SELECT * FROM foo FULL OUTER CROSS JOIN bar; Admittedly, there aren't too many use cases for this! But it would make things a bit more regular. Sam -- 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] Full outer join? Cross product? How to blend two queries into single row?
On Thu, Sep 4, 2008 at 2:54 PM, Sam Mason [EMAIL PROTECTED] wrote: SELECT * FROM foo FULL OUTER CROSS JOIN bar; Admittedly, there aren't too many use cases for this! But it would make things a bit more regular. SELECT * FROM foo, bar; Isn't this have the same results, but with out the cross join? -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- 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] Full outer join? Cross product? How to blend two queries into single row?
On Thu, Sep 04, 2008 at 02:58:39PM -0700, Richard Broersma wrote: On Thu, Sep 4, 2008 at 2:54 PM, Sam Mason [EMAIL PROTECTED] wrote: SELECT * FROM foo FULL OUTER CROSS JOIN bar; Admittedly, there aren't too many use cases for this! But it would make things a bit more regular. SELECT * FROM foo, bar; Isn't this have the same results, but with out the cross join? I hope not! Try running: SELECT * FROM (SELECT 1 AS a WHERE FALSE) f, (SELECT 2 AS b) b; and then try: SELECT * FROM (SELECT 1 AS a WHERE FALSE) f FULL OUTER JOIN (SELECT 2 AS b) b ON TRUE; I hope you get zero rows back from the first query, and a single row back from the second with a NULL value in the a column. Sam -- 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
Maybe you have it configured to log to syslog? Look in postgresql.conf. regards, tom lane HI, This is regarding the postgres issue.. We are not maintaining any logs for the postgres stop/start. So it becomes a bit problematic, to debug.. These are the steps we are following and the results: postmaster -D /home/data/www/pg7/data Does not start postgress, and no msg given. pg_ctl start /home/data/www/pg7/data Does not start postgres but gives a msg that Poatgres is started successfully I als0 tried the following commands, postmaster -D /home/data/www/pg7/data logfile 21 It creates a file logfile. But does not log anything there. Does not start postgress, and no msg given. pg_ctl start /home/data/www/pg7/data -l logfile It creates a file logfile. But does not log anything there. Does not start postgres but gives a msg that Poatgres is started successfully I am really stuck here. And need to get a way thru all of this. Any suggestions will be really appreciated. Thanks. -- 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 Sep 4, 2008, at 11:34 AM, Akhtar Yasmin-B05532 wrote: I am really stuck here. And need to get a way thru all of this. Any suggestions will be really appreciated. Have you confirmed that the user that you are logged in as when you attempt to start Postgres has write access to /home/data/www/pg7/data? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Large Selects and cursors..
Hi, I frequently query PG for between 10k - 65k rows of data and was wondering if I should be considering usage of cursors. I’m not too well versed with it’s purpose but based on the docs, it is supposed to be more efficient and also gives the impression of responsiveness. Currently, when I do these large selects, there will be a few minutes (to tens of minutes) of unresponsiveness before the results come out. -- 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] offtopic, about subject prefix
2008/9/3 brian [EMAIL PROTECTED] 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 Thanks for the answer!
Re: [GENERAL] Postgres does not start, gives no error
On Thursday 04 September 2008 3:32:55 pm Christophe wrote: On Sep 4, 2008, at 11:34 AM, Akhtar Yasmin-B05532 wrote: I am really stuck here. And need to get a way thru all of this. Any suggestions will be really appreciated. Have you confirmed that the user that you are logged in as when you attempt to start Postgres has write access to /home/data/www/pg7/data? In these sort of situations I do a one step at a time approach. 1) Verify you have only one installation of Postgres. a) For instance use find to determine if there is more than one pg_ctl b) Look for multiple copies of postgresql.conf and pg_hba.conf 2) Verify that there is actually data in /home/data/www/pg7/data 3) Go through postgresql.conf and pg_hba.conf to check they are valid. a)If possible post the contents here to help with the troubleshooting. b) As was suggested turn on logging in postgresql.conf. 4) When you do this: pg_ctl start /home/data/www/pg7/data Does not start postgres but gives a msg that Poatgres is started successfully Check for a Postgres process running. I have not used Solaris, but I am thinking the equivalent of ps ax|grep post. 5) In your original post you started with: 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 In the version I got the stop command was not shown. What was used to stop Postgres? 6) If there is a startup script what does it contain? Hope this helps, -- 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
Re: [GENERAL] Oracle and Postgresql
On Wednesday 03 September 2008 09:17:54 Asko Oja wrote:
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.
It is not as simple as Oracles database link syntax. Setting up a connection
involves a couple of sql looking commands, and once you setup a connection to
a remote database, you can reference a table with something like select *
from [EMAIL PROTECTED] There's no way a function oriented solution can
match that imho. (BTW, if you want to see more, Lewis has a pretty good write
up;
http://it.toolbox.com/blogs/oracle-guide/database-links-a-definition-in-plain-english-7023)
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
--
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 silent install bug?
Hello everyone, I am trying to run a silent install of Postgres 8.3 using the following command line: MSIEXEC /i postgresql-8.3-int.msi /qb /log c:\mydir\logfile.txt ADDLOCAL=server,nls,psql,pgadmin,includefiles,libfiles,binfiles INTERNALLAUNCH=1 DOSERVICE=1 CREATESERVICEUSER=0 DOINITDB=1 SERVICEDOMAIN=ComputerName SERVICEACCOUNT=postgres SERVICEPASSWORD=randompass1 SERVICENAME=PostgreSQL Database Server 8.3 SUPERUSER=postgres SUPERPASSWORD=yaright LOCALE=C ENCODING=SQL_ASCII LISTENPORT=5432 PERMITREMOTE=1 PL_PGSQL=1 PL_PERL=1 PL_PERLU=1 PL_TCL=0 PL_TCLU=0 PL_PYTHONU=0 BASEDIR=c:\mydir\PostgreSQL\8.3 I get the following error shortly after I see the install window start and a small amount of progress: User account ¡¡¡s\¡W does not exist Has anyone encountered this with the postgresql-8.3-int.msi file and how can I get around it? Many thanks, Mike
Re: [GENERAL] Oracle and Postgresql
I'm running all 8.3. But I don't think it makes a difference. There is some geometry type cube function but its not at all like the OLAP cube that I'm talking about. What version of Postgres are you running? On Wed, Sep 3, 2008 at 10:21 PM, Artacus [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: 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 mailto: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] Oracle and Postgresql
So that is for real huh? I've been to that web site before and figured it was more marketing talk about what they wanted to do rather than a product that was already to market. 2008/9/4 Artacus [EMAIL PROTECTED]: 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. It is not true. First look at this: http://www.analyticsql.org/ In atachment is two snapshots from production system (Analytic SQL Server based on 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] Getting affected rows in pgplsql
That's just what I needed. Thanks guys! Artacus On Thu, Sep 04, 2008 at 02:07:01AM -0700, Artacus wrote: I'm writing some ETL procedures in pgplsql. After each insert/update/delete, I'd like to log how many rows were affected. http://www.postgresql.org/docs/current/interactive/plpgsql-statements.html search for GET DIAGNOSTICS -- 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 've finally been able to solve my problem. There were two errors, 1) The postgres in the data directory had gotten overwritten, so none of the scripts were running. I found out this, when I tried making another instance of data. The initdb was not working. As well asa other scipts. Had to fix this. 2) Also,the user which I was using had insufficient permissions. Thanks a lot for all you suggestions. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Adrian Klaver Sent: Thursday, September 04, 2008 6:51 PM To: pgsql-general@postgresql.org Cc: Christophe Subject: Re: [GENERAL] Postgres does not start, gives no error On Thursday 04 September 2008 3:32:55 pm Christophe wrote: On Sep 4, 2008, at 11:34 AM, Akhtar Yasmin-B05532 wrote: I am really stuck here. And need to get a way thru all of this. Any suggestions will be really appreciated. Have you confirmed that the user that you are logged in as when you attempt to start Postgres has write access to /home/data/www/pg7/data? In these sort of situations I do a one step at a time approach. 1) Verify you have only one installation of Postgres. a) For instance use find to determine if there is more than one pg_ctl b) Look for multiple copies of postgresql.conf and pg_hba.conf 2) Verify that there is actually data in /home/data/www/pg7/data 3) Go through postgresql.conf and pg_hba.conf to check they are valid. a)If possible post the contents here to help with the troubleshooting. b) As was suggested turn on logging in postgresql.conf. 4) When you do this: pg_ctl start /home/data/www/pg7/data Does not start postgres but gives a msg that Poatgres is started successfully Check for a Postgres process running. I have not used Solaris, but I am thinking the equivalent of ps ax|grep post. 5) In your original post you started with: 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 In the version I got the stop command was not shown. What was used to stop Postgres? 6) If there is a startup script what does it contain? Hope this helps, -- 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 -- 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] You need to rebuild PostgreSQL using --with-libxml.
Hi, Please CC your replies to the list, too: On Thu, 2008-09-04 at 12:20 -0430, Ricardo Antonio Yepez Jimenez wrote: Gracias Devrim , ya instale la librería libxml2, pero sigue sin funcional cuando ejecuto un select xmlelement, arroja el siguente error You need to rebuild PostgreSQL using --with-libxml. (I don't know Spanish or such -- I'm using Google to translate these into English ;) ) After installing libxml2 from sources, you will need to recompile PostgreSQL against these sources... If I were you, I would find a way to upgrade to RHEL 5 -- all problems would go away. 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] Seeking for vacuum advise
Hello, We are using postgres for one of our application. Currently using Postgres 8.3 with CentOS 5.3 x86_64 We are using inherited tables. Base table has 3 columns: entry_date timestamp with time zone nametext valuetext pk is timestamp data tables are derived from this base table with constraint on year month table_id daystable_id 1-71 8-14 2 15-213 = 224 data tables are created pre-created before start of month data_table_year_mm_table_id No updates or delete will ever occur on data table except during purge we will dump full table and drop it, inserting ~30 million per day (every couple second data is loaded using copy statement). What are the recommendation for vacuum / auto vacuum and statistics generation ? Does vacuum on postgres catalog tables is enough to overcome oid rollover or any other measures I have to take ? Thanks, YS -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
