Re: [GENERAL] Justifying a PG over MySQL approach to a project
Gauthier, Dave wrote: Hi Everyone: Tomorrow, I will need to present to a group of managers (who know nothing about DBs) why I chose to use PG over MySQL in a project, MySQL being the more popular DB choice with other engineers, and managers fearing things that are “different” (risk). I have a few hard tecnical reasons (check constraint, deferred constraint checking, array data type), but I’m looking for a “it’s more reliable” reasons. Again, the audience is managers. Is there an impartial, 3^rd party evaluation of the 2 DBs out there that identifies PG as being more reliable? It might mention things like fewer incidences of corrupt tables/indexes, fewer deamon crashes, better recovery after system crashes, etc... ? Thanks ! There is a current question about the survivability of MySQL right now with the potential sale of MySQL. I would not bank on MySQL for any long-term project. I am sure that MySQL will live in the long run, but they may well be turbulent times ahead if whomever comes to own MySQL decides to neglect or kill it and the source gets forked. Madi -- 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] Server Requirements
Christine Penner wrote: Hi, If we have clients that are going to buy new computers or upgrade current ones, what we can recommend to them for optimal system performance to run Postgres. These can be servers or desktop PCs. We can have from 1-10 users in at a time. At this point all of our database's are small but that can change of course. Hi Christine, The problem with this question is that it is far too vague to be able to answer in any meaningful way. You need to add some information to your request. Things like: - Data set size; How many tables, how many columns, how fast will it grow, what kind of data are in the columns? - Performance; are you using triggers, functions, a lot of complex or simple queries, lots of UPDATEs, INSERTs and DELETEs? - Redundancy; How do you plan to backup the data? What performance criteria do you have? What's your acceptable down time in the case of a failure? - Interface; Users is one thing, but how many transactions will these users incur? - Budget; How much is your client willing to invest? What about long-term maintenance or support contracts? - Environment; What operating system will postgres run on? Answer these questions and you will find the hardware requirements will likely begin to become self-evident. If not, ask here again with this info and we'll be much more able to help. :) Madi -- 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] I need a Postgres Admin $130K + 20K in NYC Any Ideas?
Martin Gainty wrote: lets assume you never take a cab anywhere and you pack enough PBJ for a week so we dont have to argue with Ed Koch anyone that has lived in NY knows you need 2500/month for any decent studio apt Also you need first,last and security to get the apt Making false statements to this group will get you a lawsuit .. Bye Martin Gainty I've been ignoring this until now, but as a list member I have to say it is very embarrassing to see this kind of thread on a list as respectable as PostgreSQL General. Threatening lawsuits over a disagreement on the cost of living somewhere? Seriously now, grow up. This whole discussion has nothing to do with PostgreSQL and should go off list, or better still, just go away. Madi -- 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] I need a Postgres Admin $130K + 20K in NYC Any Ideas?
Ed Koch wrote: How are you even IN the group when nobody here agrees with you Obviously you have nothing better to do, get a Hobby Gainty Ed, please, posts like this aren't helping. We're all adults here, can we all please start acting like one? Madi -- 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] A history procedure that prevents duplicate entries
Alban Hertroys wrote: On 16 Aug 2009, at 4:24, Madison Kelly wrote: Hi all, ... CREATE FUNCTION history_radical() RETURNS trigger AS $$ DECLARE hist_radical RECORD; BEGIN SELECT INTO hist_radical * FROM public.radical WHERE rad_id=new.rad_id; I assume you mean to only select an existing record here in case the trigger is fired on an update? You are in fact always selecting at least one record here because this is called from an AFTER INSERT OR UPDATE trigger; the record has already been inserted or updated, so the select statement will find the new (version of) the record. I'm also not entirely sure what the value is of calling your procedure on INSERT. If I interpreted you correctly the same data would be added to the history the first time it gets updated (except for the different timestamp and history id of course). I'd probably just call this procedure on UPDATE, and on DELETE too. If you do want to fire on INSERT I'd make it clear there was no data before that history entry, for example by filling the record with NULL values or by adding a column for the value of TG_OP to the history table. The INSERT is there mainly for my convenience. If I am going to the history schema to get data, it's convenient to know that is has a complete copy of the data in the public schema, too. Besides that, you don't need the SELECT statement or the RECORD-type variable as the data you need is already in the NEW and OLD records. But, you only have an OLD record when your trigger fired from an UPDATE, so you need to check whether your trigger fired from INSERT or UPDATE. So, what you need is something like: IF TG_OP = 'INSERT' THEN hist_radical := NEW; ELSE -- TG_OP = 'UPDATE' hist_radical := OLD; END IF; INSERT INTO history.radical (rad_id, rad_char, rad_name) VALUES (hist_radical.rad_id, hist_radical.rad_char, hist_radical.rad_name); Alban Hertroys To help me improve my understanding of procedures, how would this prevent an UPDATE from creating a new entry in the history schema when all the column values are the same as the last entry in history? Thanks!! Madi -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] A history procedure that prevents duplicate entries
Hi all,
I've been using a procedure to make a copy of data in my public
schema into a history schema on UPDATE and INSERTs.
To prevent duplicate entries in the history, I have to lead in the
current data, compare it in my program and then decided whether
something has actually changed or not before doing an update. This
strikes me as wasteful coding and something I should be able to do in my
procedure.
Given the following example tables and procedure, how could I go
about changing it to prevent duplicate/unchanged entries being saved to
the history schema? Even a pointer to a relevant section of the docs
would be appreciated... My knowledge of procedures is pretty weak. :)
Madi
CREATE TABLE radical
(
rad_id integer primary key
default(nextval('id_seq')),
rad_chartextnot null,
rad_nametext
);
CREATE TABLE history.radical
(
rad_id integer not null,
rad_chartextnot null,
rad_nametext,
hist_id integer not null
default(nextval('hist_seq')),
modified_date timestamp default now()
);
CREATE FUNCTION history_radical() RETURNS trigger
AS $$
DECLARE
hist_radical RECORD;
BEGIN
SELECT INTO hist_radical * FROM public.radical WHERE
rad_id=new.rad_id;
INSERT INTO history.radical
(rad_id, rad_char, rad_name)
VALUES
(hist_radical.rad_id, hist_radical.rad_char,
hist_radical.rad_name);
RETURN NULL;
END;$$
LANGUAGE plpgsql;
CREATE TRIGGER trig_radical AFTER INSERT OR UPDATE ON radical FOR EACH
ROW EXECUTE PROCEDURE history_radical();
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] now() + '4d' AT TIME ZONE issue
Hi all, I'm trying to select an offset timestamp at a given time zone, but I can't seem to get the syntax right. What I am *trying* to do, which doesn't work: SELECT now() AT TIME ZONE 'America/Toronto', now() + '4d' AS future AT TIME ZONE 'America/Toronto'; Which generates the error: ERROR: syntax error at or near AT LINE 1: ...ME ZONE 'America/Toronto', now() + '4d' AS future AT TIME ZO... I've tried using an embedded SELECT and CASTing it as a TIMESTAMP with no luck. SELECT now() AT TIME ZONE 'America/Toronto', CAST ((SELECT now() + '4d') AS TIMESTAMP) as future AT TIME ZONE 'America/Toronto'; ERROR: syntax error at or near AT LINE 1: ...ST ((SELECT now() + '4d') AS TIMESTAMP) as future AT TIME ZO... When I remove the 'AT TIME ZONE' from the offset now in either case the SELECT works. Someone mind beating me with a clue stick? Thanks! Madi -- 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] now() + '4d' AT TIME ZONE issue
Tom Lane wrote: Madison Kelly li...@alteeve.com writes: SELECT now() AT TIME ZONE 'America/Toronto', now() + '4d' AS future AT TIME ZONE 'America/Toronto'; You've got AS future in the wrong place. regards, tom lane Thank you both, Chris and Tom. That was indeed my oops. Madi -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Return LEFT JOINed tables when one has no matching column
Hi all, I've got a variation on a question I asked some time ago... I've got a table that is simply a collection of variable - value columns with a pointer to another table. I use this as little as possible, given how much of a headache it is, but I've run into a situation where I need to pull something from it in a JOIN query. Trick is, the column 'variable' may simply not exist, but I want the rest of the query to return and let it be NULL. Specifically, I've got a query like this: SELECT a.tbl1_name, b.tbl2_date, c.tbl3_value AS some_value FROM table_1 a LEFT JOIN table_2 b ON (a.tbl1_id=b.tbl2_tbl1_id) LEFT JOIN table_3 c ON (a.tbl1_id=c.tbl3_tbl1_id) WHERE c.tbl3_variable='some_variable' AND a.tbl1_id=123; I want the data from table_1 and table_2 to return and table_3 to return NULL when there is no matching c.tbl3_variable='some_variable'. Is this possible? Thanks as always! Madi -- 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] Return LEFT JOINed tables when one has no matching column
Harald Fuchs wrote: In article 4a425379.90...@alteeve.com, Madison Kelly li...@alteeve.com writes: SELECT a.tbl1_name, b.tbl2_date, c.tbl3_value AS some_value FROM table_1 a LEFT JOIN table_2 b ON (a.tbl1_id=b.tbl2_tbl1_id) LEFT JOIN table_3 c ON (a.tbl1_id=c.tbl3_tbl1_id) WHERE c.tbl3_variable='some_variable' AND a.tbl1_id=123; I want the data from table_1 and table_2 to return and table_3 to return NULL when there is no matching c.tbl3_variable='some_variable'. Is this possible? Move c.tbl3_variable='some_variable' from WHERE to c's ON clause. Bingo, thank you! Madi -- 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] Disaster recovery (server died)
Uwe C. Schroeder wrote: On Friday 19 June 2009, Scott Marlowe wrote: On Fri, Jun 19, 2009 at 8:43 PM, Miguel Mirandamiguel.miran...@gmail.com wrote: Well, i just didnt explain in detail, what i have is just the 16897 directory where i was storing the database, i tried just copying the files but it didnt work, should it be posible to import this database is any way? Nope, you need the whole data directory. What I don't get is this: you said your CPU died. For me that's the processor or maybe some interpret that as the main board. So why don't you grab the harddisk from that server and plug it into the new one? Maybe something might be corrupt due to the failure, but most of the data should be on the disk (unless you use disks which lie about fsync). Yep - another reason why one has at least a daily backup (in my case 2 replicas for every production server I run. I never had a major failure in over 15 years - knock on wood - but if that happens I don't lose a heck of a lot due to the backups and slony replicas) Uwe For smaller databases, I run nightly pg_dumps to a file with the day of the week number appended to the dump file. This way my nightly backups grab the day's changes and my database sits in a nice plain text file. For larger databases, I either stop PostgreSQL and rsync *all* the Postgres files then restart or, if stopping isn't an option, use LVM and make snapshots. A third option is to have PostgreSQL sit on a DRBD partition. However, if the DRBD link is only 1 GBit, it will be noticeably slower on writes, but if that's okay it can be a solution useful for more than just PostgreSQL. A few options for people who feel replication is not feasible. :) Of course, when you can, it is the best option. You never lose anything that way. Madi -- 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] Disaster recovery (server died)
Miguel Miranda wrote: Hi, the worst have ocurred, my server died (cpu), so i reinstalled another server with the same postgres version. I have the old data directory from the old server, how can i restore my databases from this directory to the new one? I dont have a backup (pg_dump,etc), just the main previus live data directory from the old server. best regards First, make a copy. DO NOT USE your backup until done. :) Now then, assumin *nix; stop the postgresql daemon, copy the data directories into place and restart the daemon. Be sure to restore your config files like pg_hba.conf and such while the daemon is stopped. If your backup is in a consistent (or recoverable) state, you should be golden. For more specific instructions, post your OS and PgSQL versions. Best of luck! Madi -- 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] Disaster recovery (server died)
Miguel Miranda wrote: Well, i just didnt explain in detail, what i have is just the 16897 directory where i was storing the database, i tried just copying the files but it didnt work, should it be posible to import this database is any way? the Os is Freebsd 6.2 and PG version is 8.1.3 thank you. I am not familiar with FreeBSD's directory structure, so if someone pipes up, take their word over mind. However; Your backups, what is the root directory(ies)? Ie: Did you backup '/var/lib/postgresql', '/etc/postgres*', ? You should be able to stop postgres, use a tool like 'rsync' to copy the data back into place, then restart postgres. Something like: /etc/init.d/postgresql stop rsync -av /backup/var/lib/postgresql /var/lib/ rsync -av /backup/etc/postgres* /etc/ /etc/init.d/postgresql start At this point, you should be golden. Not that it matters now, but why had you not been using pg_dump to do periodic backups? How big is the database? lastly, depending on the value of the database, you may want to look at hiring someone to help you. Also, make sure you are recovering to the same versions of the OS and PostgreSQL that you old server had. This is not the time to be doing an upgrade. :) Madi -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Adding the host name to the PgSQL shell
Hi all, I work on a development and production server, and I am always double-checking myself to make sure I am doing something on the right server. Is there a way, like in terminal shells, to change the PgSQL shell's prompt from 'db=' to something like 'h...@db='? I'm on PgSQL 8.1 (server on Debian) and 8.3 (devel on Ubuntu), in case it matters. Thanks all! Madi -- 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] Adding the host name to the PgSQL shell
Scott Mead wrote: On Mon, Jun 8, 2009 at 12:44 PM, Madison Kelly li...@alteeve.com mailto:li...@alteeve.com wrote: Hi all, I work on a development and production server, and I am always double-checking myself to make sure I am doing something on the right server. Is there a way, like in terminal shells, to change the PgSQL shell's prompt from 'db=' to something like 'h...@db='? I'm on PgSQL 8.1 (server on Debian) and 8.3 (devel on Ubuntu), in case it matters. You certainly can do this, very similar to PS1 on linux with bash: http://www.postgresql.org/docs/8.1/static/app-psql.html#APP-PSQL-PROMPTING --Scott That works like a charm, thank you! Next question though; How can I get it to save my custom prompt across sessions/server restarts? It there something equivalent to '.bash_profile'? Madi -- 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] Adding the host name to the PgSQL shell
Scott Mead wrote: On Mon, Jun 8, 2009 at 1:30 PM, Madison Kelly li...@alteeve.com mailto:li...@alteeve.com wrote: That works like a charm, thank you! No problem :) Next question though; How can I get it to save my custom prompt across sessions/server restarts? It there something equivalent to '.bash_profile'? ~/.psqlrc Thank you again! For the record, in case someone finds this in an archive somewhere and are a noob like me, you need to create this file in the home directory of the shell user you call 'psql' from, *not* the user account used when using the '-U user' switch. :) Madi -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Determining/Setting a server's time zone
Hi, How/Where does PostgreSQL set or determine the local time zone? On my server, I am seeing (+00): db= SELECT now(); now --- 2009-03-23 22:32:47.595491+00 (1 row) But on my workstation I am seeing (-04): db= SELECT now(); now --- 2009-03-23 18:16:36.591653-04 (1 row) The only thing I can think of is that my server is set to believe the BIOS time is UTC and my workstation is set to EDT. Does PostgreSQL check this from the host machine? For reference, my server is Debian Sarge (4.0) and my workstation is Ubuntu 8.10. Thanks! Madi -- 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] Determining/Setting a server's time zone
Tom Lane wrote: Madison Kelly li...@alteeve.com writes: How/Where does PostgreSQL set or determine the local time zone? Well, show timezone will tell you what PG is using. Where it came from is a bit harder to answer. The default is to use whatever zone is current according to the postmaster's startup environment, and that would depend on some factors you didn't tell us, like how you're starting the postmaster. Do your two machines report the same timezone when you run date as a shell command? The easy solution is to set the value you want in postgresql.conf. regards, tom lane Hi Tom, 'date' shows the same: Server (PostgreSQL 8.1): $ date Mon Mar 23 20:07:20 EDT 2009 db= show timezone; TimeZone -- GMT (1 row) Workstation (PostgreSQL 8.3): $ date Mon Mar 23 20:07:09 EDT 2009 db= show timezone; TimeZone --- localtime (1 row) Neither has the environment variable 'TZ' set (at least, 'echo $TZ' returns nothing). Also, 'cat /etc/postgresql/8.1/main/environment' has no values on either machine. In both cases, the postmaster is started by init.d. The only reference to time zone I could otherwise find was in the 'postgresql.conf' file. Both are commented out with the comment that timezone defaults to TZ. My concern with forcing a value in the postgresql.conf file is forgetting to update the conf file when EDT/EST changes... Thanks for the help so far! Madi -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Returning null for joined tables when one column non existant
Hi all, I've got a query that crosses a few tables. For example: SELECT a.foo, b.bar, c.baz FROM aaa a, bbb b, ccc c WHERE a.a_id=b.b_a_id AND a.a_id=c.c_a_id AND a.a_id=1; Obviously, if there is no match in 'bbb' or 'ccc' then nothing will be returned, even if there is a match in one or both of the other tables. Is there a way to say something like 'b.bar OR NULL' to make sure that the matching columns with data still show and ones without a match return NULL (or some string)? Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Odd duplicate database
Hi all, My devel server has some wierdness happening. I tried to drop the database (reload from a copy from the production server) and I got this weird error: pg_dump: query returned more than one (2) pg_database entry for database nexxia So I logged in as postgres and checked, and sure enough: template1=# \l List of databases Name| Owner | Encoding +--+-- deadswitch | digimer | UTF8 nexxia | digimer | UTF8 nexxia | digimer | UTF8 postgres | postgres | UTF8 template0 | postgres | UTF8 template1 | postgres | UTF8 (6 rows) So I tried to drop the database(s?) from the shell: template1=# DROP DATABASE nexxia ; DROP DATABASE template1=# \l List of databases Name| Owner | Encoding +--+-- deadswitch | digimer | UTF8 nexxia | digimer | UTF8 postgres | postgres | UTF8 template0 | postgres | UTF8 template1 | postgres | UTF8 (5 rows) template1=# DROP DATABASE nexxia ; ERROR: database nexxia does not exist So I still have a phantom DB there. This is still true after stopping and restarting the daemon, too. When I try to connect to the database I get this: template1=# \c nexxia FATAL: database nexxia does not exist Previous connection kept Does this mean a connection is still open somewhere? If so, how did it survive the daemon restarting? More specifically, how do I clear it? Thanks! Madi -- 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] Odd duplicate database
Forgot to mention, this is PostgreSQL 8.3.5 on Linux (Ubuntu 8.10, hey, it's a devel machine!). :) Madi Madison Kelly wrote: Hi all, My devel server has some wierdness happening. I tried to drop the database (reload from a copy from the production server) and I got this weird error: pg_dump: query returned more than one (2) pg_database entry for database nexxia So I logged in as postgres and checked, and sure enough: template1=# \l List of databases Name| Owner | Encoding +--+-- deadswitch | digimer | UTF8 nexxia | digimer | UTF8 nexxia | digimer | UTF8 postgres | postgres | UTF8 template0 | postgres | UTF8 template1 | postgres | UTF8 (6 rows) So I tried to drop the database(s?) from the shell: template1=# DROP DATABASE nexxia ; DROP DATABASE template1=# \l List of databases Name| Owner | Encoding +--+-- deadswitch | digimer | UTF8 nexxia | digimer | UTF8 postgres | postgres | UTF8 template0 | postgres | UTF8 template1 | postgres | UTF8 (5 rows) template1=# DROP DATABASE nexxia ; ERROR: database nexxia does not exist So I still have a phantom DB there. This is still true after stopping and restarting the daemon, too. When I try to connect to the database I get this: template1=# \c nexxia FATAL: database nexxia does not exist Previous connection kept Does this mean a connection is still open somewhere? If so, how did it survive the daemon restarting? More specifically, how do I clear it? Thanks! Madi -- 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] Odd duplicate database
Alvaro Herrera wrote: Madison Kelly wrote: Forgot to mention, this is PostgreSQL 8.3.5 on Linux (Ubuntu 8.10, hey, it's a devel machine!). :) Huh. Please send along select xmin, xmax, ctid, cmin, cmax, datname from pg_database; template1=# select xmin, xmax, ctid, cmin, cmax, datname from pg_database; xmin | xmax | ctid | cmin | cmax | datname --+--++--+--+ 383 |0 | (0,1) |0 |0 | template1 384 |0 | (0,2) |0 |0 | template0 386 |0 | (0,3) |0 |0 | postgres 659 |0 | (0,10) |0 |0 | deadswitch 3497 | 3625 | (0,35) |0 |0 | nexxia (5 rows) Madi -- 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] Odd duplicate database
Tom Lane wrote: Madison Kelly li...@alteeve.com writes: Alvaro Herrera wrote: Please send along select xmin, xmax, ctid, cmin, cmax, datname from pg_database; template1=# select xmin, xmax, ctid, cmin, cmax, datname from pg_database; xmin | xmax | ctid | cmin | cmax | datname --+--++--+--+ 383 |0 | (0,1) |0 |0 | template1 384 |0 | (0,2) |0 |0 | template0 386 |0 | (0,3) |0 |0 | postgres 659 |0 | (0,10) |0 |0 | deadswitch 3497 | 3625 | (0,35) |0 |0 | nexxia (5 rows) So the nexxia row did get updated at some point, and either that transaction failed to commit or we've got some glitch that made this row look like it didn't. Have you used any ALTER DATABASE commands against nexxia? regards, tom lane Nope. Beyond the occasional ALTER COLUMN (few and always completed), the only thing I do directly in the shell are pretty standard queries while working out my program. Even then, the database is dropped and recreated fairly regularly with backup copies from the server. Madi PS - If I've run into a PgSQL bug, is there anything I can provide to help? -- 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] Odd duplicate database
Tom Lane wrote: Madison Kelly li...@alteeve.com writes: PS - If I've run into a PgSQL bug, is there anything I can provide to help? A sequence that reproduces it would be the best thing ... regards, tom lane I guess the trick is, I have no idea what's happened or what I did to cause it to happen... Any ideas I can try? Madi -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] TIMESTAMP with a timezone offset
Hi all, I've got a database with a column I CAST as a TIMESTAMP. The data in the database is GMT. I want to say in my WHERE clause to offset the value I am giving by X number of hours and to display the column I've cast as a timestamp offset by the same X hours. I am sure this is possible, and probably fairly simple. :) Here a simplified query I am using that currently has no TZ data: 'bar' is a timestamp from the system, 'baz' is a string from an external source CAST as a timestamp. SELECT foo, bar, CAST (baz AS TIMESTAMP) AS last_state_change FROM history.table WHERE bar = '2008-12-15 14:01:09' AND foo=153; Thanks! Madi -- 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] TIMESTAMP with a timezone offset
Raymond O'Donnell wrote: On 16/12/2008 19:16, Madison Kelly wrote: I want to say in my WHERE clause to offset the value I am giving by X number of hours and to display the column I've cast as a timestamp offset by the same X hours. You could use AT TIME ZONE to shift it the required number of hours: http://www.postgresql.org/docs/8.3/static/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT I hope this helps. Ray. I was reading that before posting, but have not been able to get it to work. After looking at it again I think it's because I've cast the column I restricted in the SELECT as a 'TIMESTAMP WITHOUT TIME ZONE'. So I ALTERed the column to be 'TIMESTAMP WITH TIME ZONE' and tried again. However, it looks like it cast the time zone on each column to my current time zone instead of UTC. After ALTERing the column and using the AT TIME ZONE 'EST' it returns values five hours ahead. So now I have another question... How can I recast a column to specify that the current values are UTC timestamps? Thanks! Madi -- 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 DISTINCT ... ORDER BY problem
Grzegorz Jaśkiewicz wrote: On Mon, Dec 8, 2008 at 10:19 PM, Madison Kelly [EMAIL PROTECTED] wrote: Hi all, I've got a table that I am trying to SELECT DISTINCT on one column and ORDER BY on a second column, but am getting the error: SELECT DISTINCT ON expressions must match initial ORDER BY expressions try SELECT distinct, array_accum(bar) FROM table WHERE bar '2008-12-07 16:32:46' AND tbl_id=153 ORDER BY bar LIMIT 1; or even, when you change bar to proper type - that is, timestamp SELECT distinct foo, min(bar) as minbar, max(bar) as maxbar FROM table WHERE bar '2008-12-07 16:32:46' AND tbl_id=153 ORDER BY bar LIMIT 1; etc. Trick, is to use aggregate on other value(s). HTH Well shoot, I didn't realize I had the 'text' where I should have used 'timestamp'. _ I updated the column, but it took me some fiddling (on a test box!) to sort out the proper command. In case it helps someone else, here was the error I was getting when I tried ALTER without USING: ALTER TABLE table ALTER foo TYPE TIMESTAMP WITHOUT TIME ZONE; ERROR: column foo cannot be cast to type pg_catalog.timestamp The syntax I needed was: ALTER TABLE table ALTER foo TYPE TIMESTAMP WITHOUT TIME ZONE USING CAST (foo AS TIMESTAMP); I know it's a little off-topic, but maybe it'll help someone searching someday. :) When I try to use: SELECT distinct foo, min(bar) as minbar, max(bar) as maxbar FROM table WHERE bar '2008-12-07 16:32:46' AND tbl_id=153 ORDER BY bar LIMIT 1; I get the error: ERROR: column table.foo must appear in the GROUP BY clause or be used in an aggregate function Already a very big help though, thanks! Madi -- 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 DISTINCT ... ORDER BY problem
Grzegorz Jaśkiewicz wrote: On Tue, Dec 9, 2008 at 9:02 AM, Grzegorz Jaśkiewicz [EMAIL PROTECTED] wrote: or even, when you change bar to proper type - that is, timestamp SELECT distinct foo, min(bar) as minbar, max(bar) as maxbar FROM table WHERE bar '2008-12-07 16:32:46' AND tbl_id=153 ORDER BY bar LIMIT 1; but than (after sec of thinking), you could just do SELECT foo, min(bar) AS minbar, max(bar) AS maxbar FROM table WHERE ..blabla.. GROUP BY foo ORDER BY maxbar LIMIT 1; Woops, didn't see this. This actually solves a second problem I'd not asked about, too. Thanks!! Madi -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SELECT DISTINCT ... ORDER BY problem
Hi all,
I've got a table that I am trying to SELECT DISTINCT on one column
and ORDER BY on a second column, but am getting the error:
SELECT DISTINCT ON expressions must match initial ORDER BY expressions
I can't add the second column to the DISTINCT clause because every
row is unique. Likewise, I can't add the first column to my ORDER BY as
it'd not sort the way I need it to.
Here is a simplified version of my query:
\d table
Table table
Column | Type | Modifiers
-+-+
tbl_id | integer | not null default nextval('tbl_seq'::regclass)
foo | text|
bar | text|
SELECT DISTINCT ON (foo) foo, bar FROM table WHERE bar '2008-12-07
16:32:46' AND tbl_id=153 ORDER BY bar LIMIT 1;
I understand from:
http://archives.postgresql.org/pgsql-sql/2007-02/msg00169.php
That this is not really possible because the any given 'foo' column
could match multiple 'bar' columns, so what do you search by? However,
it's made some sort of decision as a value is shown in 'bar' for each 'foo'.
So my question is two-fold:
1. Can I not say, somehow, sort all results by 'bar', and return the
first/last 'bar' for each distinct 'foo'?
2. Can I somehow say Order the results using the value of 'bar' you
return, regardless of where it came from?
Thanks all!
Madi
--
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 DISTINCT ... ORDER BY problem
David Rowley wrote:
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-general-
[EMAIL PROTECTED] On Behalf Of Madison Kelly
Sent: 08 December 2008 22:19
To: pgsql-general@postgresql.org
Subject: [GENERAL] SELECT DISTINCT ... ORDER BY problem
Hi all,
I've got a table that I am trying to SELECT DISTINCT on one column
and ORDER BY on a second column, but am getting the error:
SELECT DISTINCT ON expressions must match initial ORDER BY expressions
I can't add the second column to the DISTINCT clause because every
row is unique. Likewise, I can't add the first column to my ORDER BY as
it'd not sort the way I need it to.
Here is a simplified version of my query:
\d table
Table table
Column | Type | Modifiers
-+-+--
--
tbl_id | integer | not null default
nextval('tbl_seq'::regclass)
foo | text|
bar | text|
SELECT DISTINCT ON (foo) foo, bar FROM table WHERE bar '2008-12-07
16:32:46' AND tbl_id=153 ORDER BY bar LIMIT 1;
To make the query valid you would have to ORDER BY foo,bar
DISTINCT ON in this case is only going to show the first bar value for each
foo.
Is tbl_id not your PK and only giving 1 row anyway?
I understand from:
http://archives.postgresql.org/pgsql-sql/2007-02/msg00169.php
That this is not really possible because the any given 'foo' column
could match multiple 'bar' columns, so what do you search by? However,
it's made some sort of decision as a value is shown in 'bar' for each
'foo'.
So my question is two-fold:
1. Can I not say, somehow, sort all results by 'bar', and return the
first/last 'bar' for each distinct 'foo'?
2. Can I somehow say Order the results using the value of 'bar' you
return, regardless of where it came from?
You can nest queries:
SELECT foo,bar
FROM (SELECT DISTINCT ON (foo) foo,
Bar
FROM table
WHERE bar '2008-12-07 16:32:46'
AND tbl_id=153 ORDER BY foo,bar
) AS t ORDER BY bar;
Notice that I'm only applying the final order by in the outer query.
David.
haha, darn...
I've even done embedded SELECTs before, I should have thought of
that! Thanks!
Madi
--
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 DISTINCT ... ORDER BY problem
David Fetter wrote:
On Mon, Dec 08, 2008 at 11:16:29PM -, David Rowley wrote:
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-general-
[EMAIL PROTECTED] On Behalf Of Madison Kelly
Sent: 08 December 2008 22:19
To: pgsql-general@postgresql.org
Subject: [GENERAL] SELECT DISTINCT ... ORDER BY problem
Hi all,
I've got a table that I am trying to SELECT DISTINCT on one column
and ORDER BY on a second column, but am getting the error:
SELECT DISTINCT ON expressions must match initial ORDER BY expressions
I can't add the second column to the DISTINCT clause because every
row is unique. Likewise, I can't add the first column to my ORDER BY as
it'd not sort the way I need it to.
Here is a simplified version of my query:
\d table
Table table
Column | Type | Modifiers
-+-+--
--
tbl_id | integer | not null default
nextval('tbl_seq'::regclass)
foo | text|
bar | text|
SELECT DISTINCT ON (foo) foo, bar FROM table WHERE bar '2008-12-07
16:32:46' AND tbl_id=153 ORDER BY bar LIMIT 1;
To make the query valid you would have to ORDER BY foo,bar
DISTINCT ON in this case is only going to show the first bar value for each
foo.
Is tbl_id not your PK and only giving 1 row anyway?
I understand from:
http://archives.postgresql.org/pgsql-sql/2007-02/msg00169.php
That this is not really possible because the any given 'foo' column
could match multiple 'bar' columns, so what do you search by? However,
it's made some sort of decision as a value is shown in 'bar' for each
'foo'.
So my question is two-fold:
1. Can I not say, somehow, sort all results by 'bar', and return the
first/last 'bar' for each distinct 'foo'?
2. Can I somehow say Order the results using the value of 'bar' you
return, regardless of where it came from?
You can nest queries:
SELECT foo,bar
FROM (SELECT DISTINCT ON (foo) foo,
Bar
FROM table
WHERE bar '2008-12-07 16:32:46'
AND tbl_id=153 ORDER BY foo,bar
) AS t ORDER BY bar;
Notice that I'm only applying the final order by in the outer query.
When we get windowing functions, a lot of this pain will go away :)
Cheers,
David.
Oh?
I can't say I've been keeping up with what is in the pipes. What is
windowing?
Madi
--
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] rollback
Adrian Moisey wrote: Hi I would like to be able to mark a point in my postgres database. After that I want to change a few things and rollback to that point. Does postgres support such a thing? Is it possible for me to do this? A crude way of doing it, which I've done in the past on test DBs, is take periodic dumps of the DB, do some work/development, then drop the DB and reload the dump to go back in time. Of course, this becomes less feasible as your DB grows in size. I've not played with savepoints myself, though if others are recommending it, it is probably more sane then my method. Madi -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Moving lock file (/tmp/.s.PGSQL.port)
Hi all, If there a ./configure switch (or config file/command line switch) to tell postgresql to put the lock file '.s.PGSQL.port.lock' and socket '.s.PGSQL.port' in a different directory? Thanks all! Madi ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] ALTER syntax question and usernames with hyphens
Hi all, What is the proper syntax/escape character when using 'ALTER ... OWNER TO user-name'? I've tried single quotes, backslashes, backticks and various others without luck. Is it at all possible? Thanks! Madi ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] PostgreSQL 8.2.5 compile problem
Hi all, I am trying to compile PgSQL 8.2.5 (on Debian Etch, in case it matters). This is a second install for a dedicated program, which is why I am not using the binaries in the apt repositories. My problem is, 'make' is failing with: previous stuff snipped make -C pl install make[2]: Entering directory `/home/digimer/projects/mizu-bu/pgsql/src/pl' make[3]: Entering directory `/home/digimer/projects/mizu-bu/pgsql/src/pl/plpgsql' make -C src install make[4]: Entering directory `/home/digimer/projects/mizu-bu/pgsql/src/pl/plpgsql/src' /bin/sh ../../../../config/install-sh -c -m 755 libplpgsql.so.1.0 '/usr/share/mizu-bu/pgsql//lib/plpgsql.so' make[4]: Leaving directory `/home/digimer/projects/mizu-bu/pgsql/src/pl/plpgsql/src' make[3]: Leaving directory `/home/digimer/projects/mizu-bu/pgsql/src/pl/plpgsql' make[3]: Entering directory `/home/digimer/projects/mizu-bu/pgsql/src/pl/plperl' gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fpic -shared -Wl,-soname,libplperl.so.0 plperl.o spi_internal.o SPI.o -L/usr/local/lib -L/usr/lib/perl/5.8/CORE -L../../../src/port /usr/lib/perl/5.8/auto/DynaLoader/DynaLoader.a -lperl -ldl -lm -lpthread -lc -lcrypt -Wl,-rpath,'/usr/lib/perl/5.8/CORE' -o libplperl.so.0.0 /usr/bin/ld: cannot find -lperl collect2: ld returned 1 exit status make[3]: *** [libplperl.so.0.0] Error 1 make[3]: Leaving directory `/home/digimer/projects/mizu-bu/pgsql/src/pl/plperl' make[2]: *** [install] Error 1 make[2]: Leaving directory `/home/digimer/projects/mizu-bu/pgsql/src/pl' make[1]: *** [install] Error 2 make[1]: Leaving directory `/home/digimer/projects/mizu-bu/pgsql/src' make: *** [install] Error 2 After this error, I searched for 'libplperl.so.0.0' but couldn't find it (I did updatedb first). I did find '/program_dir/pgsql/src/pl/plperl/libplperl.a' though... The 'configure' step seemed to be okay: ./configure --prefix=/usr/share/mizu-bu/pgsql/ --with-pgport=1062 --without-docdir --with-perl --without-tcl --without-python --without-krb5 --without-pam --without-ldap --without-bonjour --without-openssl --without-readline --without-zlib stuff snipped checking for perl... /usr/bin/perl checking for Perl archlibexp... /usr/lib/perl/5.8 checking for Perl privlibexp... /usr/share/perl/5.8 checking for Perl useshrplib... true checking for flags to link embedded Perl... -L/usr/local/lib /usr/lib/perl/5.8/auto/DynaLoader/DynaLoader.a -L/usr/lib/perl/5.8/CORE -lperl -ldl -lm -lpthread -lc -lcrypt Any idea why 'make' is failing? I am using GNU Make 3.81, as the docs require. Madi ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL 8.2.5 compile problem
Tom Lane wrote: Madison Kelly [EMAIL PROTECTED] writes: gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fpic -shared -Wl,-soname,libplperl.so.0 plperl.o spi_internal.o SPI.o -L/usr/local/lib -L/usr/lib/perl/5.8/CORE -L../../../src/port /usr/lib/perl/5.8/auto/DynaLoader/DynaLoader.a -lperl -ldl -lm -lpthread -lc -lcrypt -Wl,-rpath,'/usr/lib/perl/5.8/CORE' -o libplperl.so.0.0 /usr/bin/ld: cannot find -lperl After this error, I searched for 'libplperl.so.0.0' but couldn't find it (I did updatedb first). It's complaining about the lack of libperl.so ... the other one is what it wants to build. The 'configure' step seemed to be okay: AFAICT, our configure just believes what perl's ExtUtils::Embed and Config modules tell it ... it doesn't actually test the results. So I'm thinking either libperl.so isn't installed, or it isn't where those modules say it is --- which would be a Perl misconfiguration. regards, tom lane Doh! Was missing the libperl-dev package. Thanks! :) Madi ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Small dedicated install of PgSQL for a program
Hey all, I've got a program that uses PostgreSQL. In the past, one of the trickier parts of installation and design was supporting various versions and various types of SQL servers. So now that I am doing a ground-up rewrite of the program, I wanted to use a dedicated installation of PgSQL. I've got it compiling and installing nicely, but I am looking to optimize things some. Is there any guides for people like me looking for a minimal install/distribution size? I'd like to strip out all features I don't use. Also, I'd like to avoid needing the user to install foo-dev packages, so is it possible to get a list of all the library and include files PostgreSQL needs? So far, I've got this working; ./configure --prefix=/usr/share/mizu-bu/db/ --without-docdir \ --with-perl --with-pgport=1062 --without-tcl --without-python \ --without-krb5 --without-pam --without-ldap --without-bonjour \ --without-openssl make make install The '/usr/share/mizu-bu' directory is the root directory for my program and PlPerl is the only language I need. I don't need a lot of the stuff in contrib (ie: tsearch2 for example), but am unsure of how safe it is to just start deleting things. Hopefully others have created (relatively) small, stripped down distribution of PgSQL for their programs... Also, to make a source distribution as portable as possible, what precautions/gotcha's might I run into by trying to put together a fully independent list of libraries and includes for this install? I'm far from an expert programmer, so I am not sure what all I need to watch for. I know I need GNU make 3.81.1+, and I'd like to have readline support (50/50 of zlib). Thanks for any help/tips/pointers! Madi ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] subquery/alias question
Michael Glaesemann wrote: On Sep 25, 2007, at 17:30 , Alvaro Herrera wrote: Michael Glaesemann wrote: select dom_id, dom_name, usr_count from domains natural join (select usr_dom_id as dom_id, count(usr_dom_id) as usr_count from users) u where usr_count 0 order by dom_name; Maybe the usr_count should be tested in a HAVING clause instead of WHERE? And put the count(*) in the result list instead of a subselect. That feels more natural to me anyway. I believe you'd have to write it like select dom_id, dom_name, count(usr_dom_id) as usr_count from domains join users on (usr_dom_id = dom_id) having count(usr_dom_id) 0 order by dom_name; I don't know how the performance would compare. I think the backend is smart enough to know it doesn't need to perform two seq scans to calculate count(usr_dom_id), but I wasn't sure. Madison, how do the two queries compare with explain analyze? Thanks for your reply! Unfortunately, in both cases I get the error: nmc= SELECT dom_id, dom_name, COUNT(usr_dom_id) AS usr_count FROM domains JOIN users ON (usr_dom_id=dom_id) HAVING COUNT (usr_dom_id) 0 ORDER BY dom_name; ERROR: syntax error at or near COUNT at character 25 LINE 1: SELECT dom_id, dom_name COUNT(usr_dom_id) AS usr_count FROM ... I've been struggling with some deadlines, so for now I'm using just: SELECT d.dom_id, d.dom_name FROM domains d WHERE (SELECT COUNT(*) FROM users u WHERE u.usr_dom_id=d.dom_id) 0 ORDER BY d.dom_name ASC; Which gives me just the domains with at least one user under them, but not the count. This is not ideal, and I will have to come back to it next week. In the meantime, any idea what the GROUP BY error is? If not, I'll read through the docs on 'GROUP'ing once I get this deadline out of the way. Thank you all for your help! I am sure I will have more question(s) next week as soon as I can get back to this. Madi ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Solved! Was: (subquery/alias question)
Alvaro Herrera wrote: Madison Kelly wrote: Thanks for your reply! Unfortunately, in both cases I get the error: nmc= SELECT dom_id, dom_name, COUNT(usr_dom_id) AS usr_count FROM domains JOIN users ON (usr_dom_id=dom_id) HAVING COUNT (usr_dom_id) 0 ORDER BY dom_name; ERROR: syntax error at or near COUNT at character 25 LINE 1: SELECT dom_id, dom_name COUNT(usr_dom_id) AS usr_count FROM ... Try to avoid missing the comma before the COUNT (and do not cheat when cut'n pasting ...) Also it seems you will need a GROUP BY clause: GROUP BY dom_id, dom_name (placed just before the HAVING clause). Bingo! Now to answer the performance questions (using my actual queries, unedited so they are a little longer): -=-=-=-=-=- nmc= EXPLAIN ANALYZE SELECT dom_id, dom_name, dom_note, COUNT(usr_dom_id) AS usr_count FROM domains JOIN users ON (usr_dom_id=dom_id) GROUP BY dom_id, dom_name, dom_note HAVING COUNT (usr_dom_id) 0 ORDER BY dom_name; QUERY PLAN - Sort (cost=10.70..10.78 rows=31 width=72) (actual time=2.107..2.133 rows=17 loops=1) Sort Key: domains.dom_name - HashAggregate (cost=9.39..9.93 rows=31 width=72) (actual time=1.899..1.956 rows=17 loops=1) Filter: (count(usr_dom_id) 0) - Hash Join (cost=7.20..9.00 rows=31 width=72) (actual time=0.942..1.411 rows=96 loops=1) Hash Cond: (outer.dom_id = inner.usr_dom_id) - Seq Scan on domains (cost=0.00..1.31 rows=31 width=68) (actual time=0.227..0.321 rows=31 loops=1) - Hash (cost=6.96..6.96 rows=96 width=4) (actual time=0.673..0.673 rows=96 loops=1) - Seq Scan on users (cost=0.00..6.96 rows=96 width=4) (actual time=0.010..0.371 rows=96 loops=1) Total runtime: 2.454 ms (10 rows) -=-=-=-=-=- Versus: -=-=-=-=-=- nmc= EXPLAIN ANALYZE SELECT d.dom_id, d.dom_name, d.dom_note, (SELECT COUNT(*) FROM users u WHERE u.usr_dom_id=d.dom_id) AS usr_count FROM domains d WHERE (SELECT COUNT(*) FROM users u WHERE u.usr_dom_id=d.dom_id) 0 ORDER BY d.dom_name ASC; QUERY PLAN -- Sort (cost=297.37..297.39 rows=10 width=68) (actual time=10.171..10.196 rows=17 loops=1) Sort Key: dom_name - Seq Scan on domains d (cost=0.00..297.20 rows=10 width=68) (actual time=0.508..10.013 rows=17 loops=1) Filter: ((subplan) 0) SubPlan - Aggregate (cost=7.21..7.21 rows=1 width=0) (actual time=0.203..0.204 rows=1 loops=31) - Seq Scan on users u (cost=0.00..7.20 rows=1 width=0) (actual time=0.127..0.189 rows=3 loops=31) Filter: (usr_dom_id = $0) - Aggregate (cost=7.21..7.21 rows=1 width=0) (actual time=0.184..0.186 rows=1 loops=17) - Seq Scan on users u (cost=0.00..7.20 rows=1 width=0) (actual time=0.058..0.164 rows=6 loops=17) Filter: (usr_dom_id = $0) Total runtime: 10.593 ms (12 rows) -=-=-=-=-=- So using the JOIN you all helped me with, the query returns in 2.454 ms compared to my early query of 10.593 ms! I have not yet looked into any indexing either. I am waiting until the program is done and then will go back and review queries to look for bottlenecks. Thanks to all of you!! Madi ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] subquery/alias question
Gregory Stark wrote: Madison Kelly [EMAIL PROTECTED] writes: SELECT d.dom_id, d.dom_name FROM domains d WHERE (SELECT COUNT(*) FROM users u WHERE u.usr_dom_id=d.dom_id) 0 ORDER BY d.dom_name ASC; Which gives me just the domains with at least one user under them, but not the count. This is not ideal, and I will have to come back to it next week. In the meantime, any idea what the GROUP BY error is? If not, I'll read through the docs on 'GROUP'ing once I get this deadline out of the way. I think you just want simply: SELECT dom_id, dom_name, count(*) FROM users JOIN domains ON (usr_dom_id=dom_id) GROUP BY dom_id, dom_nmae ORDER BY dom_name You don't actually need the HAVING (though it wouldn't do any harm either) since only domains which match a user will come out of the join anyways. You can also write it using a subquery instead of a join SELECT * FROM ( SELECT dom_id, dom_name, (SELECT count(*) FROM users WHERE user_dom_id = dom_id) as nusers FROM domains ) as subq WHERE nusers 0 ORDER BY dom_name But that will perform worse in many cases. You are right, the 'HAVING' clause does seem to be redundant. I removed it and ran several 'EXPLAIN ANALYZE's on it with and without the 'HAVING' clause and found no perceivable difference. I removed the 'HAVING' clause anyway, since I like to keep queries as minimal as possible. Thank you! Madi ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Manually clearing database foo is being accessed by other users
Steve Crawford wrote: Sysadmin wrote: Hi all, I'm finding that routinely when I try to reload a database on a server where I know there are no connections to a given DB I get the error: $ dropdb foo createdb foo -O bar psql foo -f /path/to/db.out dropdb: database removal failed: ERROR: database foo is being accessed by other users This means I need to restart the postmaster, but the server contains many DBs, of which some may actually be in use. How can I tell postgres that the database 'foo' should be marked as not in use / clear or drop any open connections / etc? If you connect to foo and run select * from pg_stat_activity; what does it show? Have you tried changing pg_hba.conf (and reloading PG and waiting for current connections to terminate of course) to deny access to foo before running your commands? Is there a pg_dumpall (or anything else that might access that db at the PG superuser level) running at the time? Cheers, Steve Thanks, Steve! Turns out a daemon was indeed still connected to the database... that command pointed that out, and I assure you I gave myself a decent smack in the forehead for it. :) Madi ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] subquery/alias question
Hi all, I've read 7.2.1.3 (as short as it is) in the PgSQL docs, but don't see what I am doing wrong... Maybe you can help? I've got a query; SELECT d.dom_id, d.dom_name, (SELECT COUNT(*) FROM users u WHERE u.usr_dom_id=d.dom_id) AS usr_count FROM domains d ORDER BY d.dom_name ASC; Where 'usr_count' returns the number of entries in 'users' that point to a given entry in 'domains'. Pretty straight forward so far. The trouble is: SELECT d.dom_id, d.dom_name, (SELECT COUNT(*) FROM users u WHERE u.usr_dom_id=d.dom_id) AS usr_count FROM domains d WHERE usr_count 0 ORDER BY d.dom_name ASC; Causes the error: ERROR: column usr_count does not exist It works if I use: SELECT d.dom_id, d.dom_name, (SELECT COUNT(*) FROM users u WHERE u.usr_dom_id=d.dom_id) AS usr_count FROM domains d WHERE (SELECT COUNT(*) FROM users u WHERE u.usr_dom_id=d.dom_id) 0 ORDER BY d.dom_name ASC; This seems terribly inefficient (and ugly), and I can't see why the results from 'usr_count' can't be counted... I can use 'usr_count' to sort the results... Thanks all! Madi ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Return t/f on existence of a join
... Or something like that. :)
Sorry for so many questions! I have another how do I create this
query? question, if it's okay.
I've got three tables; 'foo', 'bar' and 'baz'.
In 'foo' I've got 'foo_id' which is a PK. I've also got a bunch of
other info, but in essence this is the parent table that all others
reference in some way.
In 'bar' I've got 'bar_id' which is also a PK. I also have
'bar_foo_id' which is a FK pointing to 'foo' - 'foo_id', to show what
'foo' row it (primarily) belongs to.
Lastly, I've got a table called 'baz' which has 'baz_id'. In it, there
are just two columns;
- 'baz_foo_id' which is a FK pointing to 'foo' - 'foo_id'.
- 'baz_bar_id' which is a FK pointing to 'bar' - 'bar_id'.
This last table, 'baz' is used as a way for saying 'bar *also* belongs
to a given 'foo' row,
So now my question;
I want to create a query that will allow me to say show me all 'foo'
rows and tell me if a specific 'baz_id' belongs to it. Normally, I
would do this:
SELECT foo_id FROM foo;
(for each returned row)
{
# Where '$foo_id' is the current 'foo_id' and '$bar_id' is
# the specific/static 'bar_id' we are checking.
SELECT COUNT(*) FROM baz z, bar r WHERE baz_foo_id=$foo_id AND
baz_bar_id=$bar_id;
( if count 0 ) { # TRUE } else { # FALSE }
}
This is pretty inefficient, obviously. How could I create a query that
returned a TRUE/FALSE column that checks if there is a 'baz' record for
a specified 'bar_id' in all 'foo_id's in one query?
I hope this isn't too muddy. I think part of my problem is I am having
trouble even visualizing my question...
Thanks as always!
Madi
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
[GENERAL] Solved! Was (Return t/f on existence of a join)
Thanks to both of you, Erik and Jon! I had to tweak your two replies to get what I wanted (all 'foo' rows returned, was only getting ones with a match in 'baz'). You two sent me on the right path though and I was able to work out the rest using the PgSQL docs on 'CASE' and 'JOIN'. Here is the working query (where 'bar_id'=2): SELECT CASE z.baz_bar_id WHEN 2 THEN TRUE ELSE FALSE END AS tf_col, f.foo_id FROM foo f LEFT OUTER JOIN baz z ON (f.foo_id=z.baz_foo_id) LEFT OUTER JOIN bar b ON (b.bar_id=z.baz_bar_id) AND b.bar_id=2; Thanks kindly to both! I honestly didn't expect to work this out before then end of the day. Cool! Madi ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Wait, not solved... Was (Return t/f on existence of a join)
Madison Kelly wrote: Thanks to both of you, Erik and Jon! I had to tweak your two replies to get what I wanted (all 'foo' rows returned, was only getting ones with a match in 'baz'). You two sent me on the right path though and I was able to work out the rest using the PgSQL docs on 'CASE' and 'JOIN'. Here is the working query (where 'bar_id'=2): SELECT CASE z.baz_bar_id WHEN 2 THEN TRUE ELSE FALSE END AS tf_col, f.foo_id FROM foo f LEFT OUTER JOIN baz z ON (f.foo_id=z.baz_foo_id) LEFT OUTER JOIN bar b ON (b.bar_id=z.baz_bar_id) AND b.bar_id=2; Thanks kindly to both! I honestly didn't expect to work this out before then end of the day. Cool! Madi Doh! It's returning a row from 'foo' for every entry in baz that has an entry pointing to foo (possibly same problem with each pointer to an entry in bar, not sure yet). The 'true/false' part is working though... Back to reading. *sigh* :) Madi ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Actually Solved! Was: (Return t/f on existence of a join)
Madison Kelly wrote: It's returning a row from 'foo' for every entry in baz that has an entry pointing to foo (possibly same problem with each pointer to an entry in bar, not sure yet). The 'true/false' part is working though... Back to reading. *sigh* :) Madi I'm sorry for all the line noise, but I like to post my solutions for the record. Reading up a bit more of the JOIN types I was finally able to get all rows in 'foo' returned just once with a t/f depending if a given baz_bar_id exists. The query is: (Where '2' is the 'baz_bar_id' I am checking on) SELECT f.foo_id, f.foo_name, CASE z.baz_foo_id WHEN 2 THEN TRUE ELSE FALSE END FROM foo f LEFT JOIN baz z ON f.foo_id=z.baz_foo_id AND z.baz_bar_id=2; Thanks again, both of you! Madi ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] SELECT question (splitting a field)
Hi all,
Hopefully a quick question...
Why does:
nmc= SELECT 'Y' AS local FROM domains WHERE dom_name='test.com';
local
---
Y
(1 row)
Work but:
nmc= SELECT 'Y' AS local FROM domains WHERE '@'||dom_name IN
('[EMAIL PROTECTED]');
local
---
(0 rows)
Not work?
I am sure I am missing something simple. :)
Thanks!!
Madi
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] SELECT question (splitting a field)
Rodrigo De León wrote: On 9/4/07, Madison Kelly [EMAIL PROTECTED] wrote: I am sure I am missing something simple. :) Yeah... '[EMAIL PROTECTED]' '@test.com' Well now, don't I feel silly. *sigh* Thanks! Madi ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] SELECT question (splitting a field)
Richard Huxton wrote:
Madison Kelly wrote:
nmc= SELECT 'Y' AS local FROM domains WHERE '@'||dom_name IN
('[EMAIL PROTECTED]');
local
---
(0 rows)
Not work?
I don't think IN does what you think it does. It's not a substring-test,
but a set test:
SELECT 1 WHERE 'x' IN ('a','b','c','x');
SELECT a FROM foo WHERE b IN (SELECT z FROM bar WHERE frozzledwamble);
You could mess around with substring() and length() or I'd use LIKE.
If it's just a domain you're looking for though, might be most efficient
to strip the leading part off your value with regexp_replace().
Yeah, that was my problem. I thought I was using the section following
the '@'. =/
I've been using Postgres for a while now, but only recently getting into
some of the fancier stuff. Until now, I've usually written the program
using PgSQL so I could manipulate the data as I needed. Now I am using
PgSQL as a backend for a few other applications so I am restricted to
using PgSQL to manipulate the data.
It's all left me feeling quite n00bish again. ;)
I did figure out a query that worked:
SELECT 'Y' AS local FROM domains d, users u WHERE u.usr_dom_id=d.dom_id
AND u.usr_email||'@'||d.dom_name IN ('[EMAIL PROTECTED]');
Though this may not be the most efficient. In my case, the 'usr_email'
is the LHS of the '@' sign and 'dom_name' is the domain name. If I
wanted to use (I)LIKE, how would I have matched just the domain section
of '[EMAIL PROTECTED]' in 'dom_name'?
I'll go read up, now that I've got some key words to search the docs on.
Thanks kindly!
Madi
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Select question
Merlin Moncure wrote: I seem to recall giving out a query about that in the IRC channel a while back...so if you got it from me, now I'll attempt to finish the job :-). If you can get postfix to look at a view, maybe you could CREATE VIEW email_v AS SELECT usr_email, dom_name, b.dom_name||'/'||a.usr_email||'/inbox' AS email_file FROM users a, domains b WHERE a.usr_dom_id=b.dom_id; AND a.usr_email='mkelly' AND b.dom_name='test.com'; and just select * from email_v where usr_email = 'mkelly' and dom_name = 'test.com'; merlin Hiya, Nope, wasn't me, but I was indeed able to solve the problem with a few (I posted the details in a follow up). It was pretty similar to your suggestion, so you were certainly onto something. :) Madi ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Select question
Hi all, I am pretty sure I've done this before, but I am drawing a blank on how I did it or even what commands I need. Missing the later makes it hard to search. :P I've got Postfix working using PostgreSQL as the backend on a small, simple test database where I have a simple table called 'users' with a column called 'usr_email' which holds, surprisingly, the user's email address (ie: '[EMAIL PROTECTED]'). To tell Postfix where the user's email inbox is (to write incoming email to) I tell it to do this query: SELECT substring(usr_email FROM '@(.*)')||'/'||substring(usr_email FROM '(.*)@')||'/inbox' AS email_file FROM users WHERE usr_email='[EMAIL PROTECTED]'; Which returns: email_file - feneon.com/mkelly/inbox Now I want to move to a more complex database where the email name comes from 'users' - 'usr_email' (ie: 'mkelly') and the domain suffix comes from 'domains' - 'dom_name' (ie: 'test.com'). The problem is, I am limited to how I can tell Postfix to generate the query. Specifically, I can't (or don't know how to) tell Postfix to create a join or split the email address. I can only tell Postfix what table to query, what the SELECT field to use, and what column to do the WHERE on. So, my question, Can I create a 'virtual table' table (or some such) that would take something like?: SELECT email_file FROM virtual_table WHERE email_addy='[EMAIL PROTECTED]'; Where the email_addy can be split to create this query: SELECT b.dom_name||'/'||a.usr_email||'/inbox' AS email_file FROM users a, domains b WHERE a.usr_dom_id=b.dom_id AND a.usr_email='mkelly' AND b.dom_name='test.com'; Which would still return: email_file -- alteeve.com/mkelly/inbox I hope I got the question across well enough. :) Thanks all! Madi ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Select question
Woops, I wasn't careful enough when I wrote that email, sorry. The results showed my real domains instead of 'test.com'. I had different domains in the test and real DBs. Madison Kelly wrote: email_file - feneon.com/mkelly/inbox and email_file -- alteeve.com/mkelly/inbox *sigh* Should have shown: email_file --- test.com/mkelly/inbox I'll go get a coffee and wake up some more. :) Madi ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Solved! Was: Re: [GENERAL] Select question
Madison Kelly wrote: Hi all, I am pretty sure I've done this before, but I am drawing a blank on how I did it or even what commands I need. Missing the later makes it hard to search. :P I've got Postfix working using PostgreSQL as the backend on a small, simple test database where I have a simple table called 'users' with a column called 'usr_email' which holds, surprisingly, the user's email address (ie: '[EMAIL PROTECTED]'). To tell Postfix where the user's email inbox is (to write incoming email to) I tell it to do this query: SELECT substring(usr_email FROM '@(.*)')||'/'||substring(usr_email FROM '(.*)@')||'/inbox' AS email_file FROM users WHERE usr_email='[EMAIL PROTECTED]'; Which returns: email_file - feneon.com/mkelly/inbox Now I want to move to a more complex database where the email name comes from 'users' - 'usr_email' (ie: 'mkelly') and the domain suffix comes from 'domains' - 'dom_name' (ie: 'test.com'). The problem is, I am limited to how I can tell Postfix to generate the query. Specifically, I can't (or don't know how to) tell Postfix to create a join or split the email address. I can only tell Postfix what table to query, what the SELECT field to use, and what column to do the WHERE on. So, my question, Can I create a 'virtual table' table (or some such) that would take something like?: SELECT email_file FROM virtual_table WHERE email_addy='[EMAIL PROTECTED]'; Where the email_addy can be split to create this query: SELECT b.dom_name||'/'||a.usr_email||'/inbox' AS email_file FROM users a, domains b WHERE a.usr_dom_id=b.dom_id AND a.usr_email='mkelly' AND b.dom_name='test.com'; Which would still return: email_file -- alteeve.com/mkelly/inbox I hope I got the question across well enough. :) Thanks all! Madi ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly I got the answer from an Ian Peterson from the GTALUG. Thought I'd post the answer here, for the record. -=-=-=- CREATE VIEW email_file AS SELECT u.usr_email || '@' || d.dom_name AS email, d.dom_name || '/' || u.usr_email || '/inbox' AS file FROM users u JOIN domains d ON u.usr_dom_id=d.dom_id; -=-=-=- Which allows the query: -=-=-=- SELECT file FROM email_file WHERE email='[EMAIL PROTECTED]'; -=-=-=- To return: -=-=-=- file --- test.com/mkelly/inbox -=-=-=- Perfect! :) Madi ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] OT? Courier + PgSQL problem
Hi all, I fully acknowledge that this may be off topic, but hopefully not too much. :) I am hoping some of you have used PgSQL this way and can help as I am not on any courier mail lists. I have a problem I can't seem to figure out. I am trying to get Courier to read email over POP3 using a PgSQL database as the backend. It's close, but not quite functional. The problem is, it doesn't seem to be looking for the mail file properly. It's not concatenating 'PGSQL_HOME_FIELD' and 'PGSQL_MAILDIR_FIELD' fields. In my case, email is stored as: '/email/domain/user' so the user '[EMAIL PROTECTED]' should end up looking for the mail file at '/email/feneon.com/mkelly'. The query being generated is: SELECT usr_email, '', usr_password, 1001, 1001, '/email', substring(usr_email FROM '@(.*)')||'/'||substring(usr_email FROM '(.*)@'), '', usr_name, '' FROM users WHERE usr_email = '[EMAIL PROTECTED]'; This returns: usr_email | ?column? | usr_password | ?column? | ?column? | ?column? | ?column? | ?column? | usr_name| ?column? ---+--+--+--+--+--+---+--+---+-- [EMAIL PROTECTED] | | foo | 1001 | 1001 | /email | feneon.com/mkelly | | Madison Kelly | The important columns are: ?column? | ?column? --+--- /email | feneon.com/mkelly But when I try to connect over telnet to port 25 I get this error: $ telnet cluster 110 Trying 192.168.2.10... Connected to cluster. Escape character is '^]'. +OK Hello there. USER [EMAIL PROTECTED] +OK Password required. PASS secret -ERR chdir feneon.com/mkelly failed Connection closed by foreign host. And in '/var/log/mail.info': Aug 28 11:59:13 nicole authdaemond: LOG: duration: 1.765 ms statement: SELECT usr_email, '', usr_password, 1001, 1001, '/email', substring(usr_email FROM '@(.*)')||'/'||substring(usr_email FROM '(.*)@'), '', usr_name, '' FROM users WHERE usr_email = '[EMAIL PROTECTED]' Aug 28 11:59:13 nicole courierpop3login: chdir feneon.com/mkelly: Not a directory Aug 28 11:59:13 nicole authdaemond: Authenticated: sysusername=null, sysuserid=1001, sysgroupid=1001, homedir=/email, [EMAIL PROTECTED], fullname=Madison Kelly, maildir=feneon.com/mkelly, quota=null, options=null Aug 28 11:59:13 nicole authdaemond: Authenticated: clearpasswd=secret, passwd=null Nothing relevant is printed in 'mail.err'. From what I see in '/etc/courier/authpgsqlrc' I have: ##NAME: PGSQL_HOME_FIELD:0 # PGSQL_HOME_FIELD'/email' ##NAME: PGSQL_NAME_FIELD:0 # # The user's name (optional) PGSQL_NAME_FIELDusr_name ##NAME: PGSQL_MAILDIR_FIELD:0 # # This is an optional field, and can be used to specify an arbitrary # location of the maildir for the account, which normally defaults to # $HOME/Maildir (where $HOME is read from PGSQL_HOME_FIELD). # # You still need to provide a PGSQL_HOME_FIELD, even if you uncomment this # out. # PGSQL_MAILDIR_FIELD substring(usr_email FROM '@(.*)')||'/'||substring(usr_email FROM '(.*)@') Should this not work? Thanks all! Madison ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Help creating a function
Hi all, I'm using ulogd with PostgreSQL which stores IP addresses as 32bit unsigned integers. So when I select some data I get something like: ulogd= SELECT id, ip_saddr, ip_daddr, raw_pktlen, ip_totlen, tcp_window FROM ulog LIMIT 20; id | ip_saddr | ip_daddr | raw_pktlen | ip_totlen | tcp_window ++++---+ 1 | 3232235874 | 1074534522 | 46 |46 | 25825 Where 'ip_saddr' and 'ip_daddr' are 'bigint'. I know I can convert these numbers to dotted-decimal in perl with a small script like: -=-=- #!/usr/bin/perl # This would be the number read from the DB my $num=3232235874; # Now do the math my $temp=$num/256; my $D=256*($temp-int($temp)); $temp=(int($temp))/256; my $C=256*($temp-int($temp)); $temp=(int($temp))/256; my $B=256*($temp-int($temp)); my $A=int($temp); my $ip=$A.$B.$C.$D; # Print the results print 'num': [$num] - 'IP': [$ip]\n; -=-=- What I would like to do is create a function that would do the same thing so I could read out the IP addresses as standard dotted-decimal format. Could anyone help me with this? I am quite the n00b when it comes to functions. :) Thanks all! Madi ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Help creating a function
Note: This is being sent again (in case it shows up later). It never seemed to have made it to the list. Hi all, I'm using ulogd with PostgreSQL which stores IP addresses as 32bit unsigned integers. So when I select some data I get something like: ulogd= SELECT id, ip_saddr, ip_daddr, raw_pktlen, ip_totlen, tcp_window FROM ulog LIMIT 20; id | ip_saddr | ip_daddr | raw_pktlen | ip_totlen | tcp_window ++++---+ 1 | 3232235874 | 1074534522 | 46 |46 | 25825 Where 'ip_saddr' and 'ip_daddr' are 'bigint'. I know I can convert these numbers to dotted-decimal in perl with a small script like: -=-=- #!/usr/bin/perl # This would be the number read from the DB my $num=3232235874; # Now do the math my $temp=$num/256; my $D=256*($temp-int($temp)); $temp=(int($temp))/256; my $C=256*($temp-int($temp)); $temp=(int($temp))/256; my $B=256*($temp-int($temp)); my $A=int($temp); my $ip=$A.$B.$C.$D; # Print the results print 'num': [$num] - 'IP': [$ip]\n; -=-=- What I would like to do is create a function that would do the same thing so I could read out the IP addresses as standard dotted-decimal format. Could anyone help me with this? I am quite the n00b when it comes to functions. :) Thanks all! Madi ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Linux distro
Ron Johnson wrote: Pardon me for being the contrarian, but why does a server need a GUI? Isn't that just extra RAM CPU overhead that could be more profitably put to use powering the application? What I do is install Gnome, just in case I need it for some reason (ie: opening many terminal windows at a higher res that I can alt+tab between). Then once the install is done I delete the '/etc/rc2.d/S??gdm' file, then '/etc/init.d/gdm stop'. Problem solved. :) This gives me the *option* of using a GUI without it wasting any resources besides some disk space. Madi ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Linux distro
[EMAIL PROTECTED] wrote: Hello, I bought a Dell server and I am going to use it for installing PostgrSQL 8.2.4. I always used Windows so far and I would like now to install a Linux distribution on the new server. Any suggestion on which distribution ? Fedora, Ubuntu server, Suse or others? Thanks in advance, Paolo Saudin First, let me echo Hannes; You do not want to go into production with a network operating system you are not familiar with! Doing so is just begging for down time. Unless this is a server you will have time to learn on and/or you have someone with a Linux background to help you, stick with MS for now. With that caveat out of the way, Linux as a server is amazing! I, too, was a MS-kids from way back (DOS5.2). I switched about five years ago to Linux (RH5.2, coincidently) and honestly have never looked back. It's my servers OS, my desktop OS and my laptop OS. It is very much worth the learning curve from a sysadmin and stability point of view. You just need to give yourself time to feel it out. As for which distro; that's a question you are likely to never get the same answer twice. :) /Personally/, I love Debian on servers. It's not quite as 'hardcore' as Gentoo (a great distro, but not one to start with!). It's the foundation of many of the popular distros (Ubuntu, Mepis, Knoppix, etc) and the Debian crew is very careful about what they put into the 'stable' repositories. I had been a Redhat/FC fan from when I first switched to Linux until v7.3 (the best version Redhat ever put out, in my opinion). After v8 though, things went south... Too many Redhatisms in the Redhat derivative distros (Fedora Core, RHEL, CentOS, etc) reminded me of the reasons why I left Windows. On desktops though I am a big fan of Ubuntu. Oddly though, I found the 6.x series less than great, and have found 7.04 to be *way* better. I run it on my desktops and my laptop. I also had the problem with my main desktop's widescreen, but that seems to be a Linux-wide issue. The fix is easy if you know how to edit '/etc/X11/xorg.conf' (in my case, change the '1440x1440' entries to '1440x900' and restart 'gdm'), but that would be troublesome for people new to Linux. Ubuntu is a great desktop... My boyfriend's 83yo grandma uses it with no problems. I've moved several people over to the recent Ubuntu versions and have yet to have any ask to go back to Windows. They've all had nothing but compliments for it. It's just not a great server OS, as Kenneth explained. IANAL, YMMV, etc... :) Madison ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Linux distro
Joseph S wrote: I just moved one of my desktops and my laptop from Fedora 6 to Unbuntu 7.04 because Fedora lacked hardware support that Unbuntu and my Fedora machines had all sorts of problems like sound dropping out and machines locking up. (Also the Fedora installers are terrible). My small gripes about Ubuntu are: 1) rpm, for all its faults, is still better than using apt Heh, see, this is what I meant by you won't get the same answer twice. :) Personally, one of the big selling features of Debian (and Ubuntu) was how much better /I/ found 'apt-get'/'aptitude'/'synaptic' over 'up2date'/'yum'. You may want to download all the popularly recommended distributions and play around with them to see which suits your fancy. The major distributions I would suggest (in no particular order) you play with: - RHEL (if you can afford it) - CentOS - Debian Ubuntu is not really appropriate as a server, ditto with FC. Their focus is too much on the desktop (not bad, just not appropriate here). SuSe is in the dog house with the OSS community right now and that could translate into serious support troubles down the road (when did you last see anyone use Caldera? :) ). I somewhat agree with Brian's argument of using enterprise-grade distros, however I think that his particular argument is a little strict. If you have a healthy budget, then definitely go with a backed-distro. However if, like many of us, you want very good reliability without a (heafty if any) price tag, versions like CentOS and my fav. Debian are mature, tried and tested. I would never have any qualms recommending some distros as servers that don't have direct commercial suppliers. It's like PostgreSQL vs MySQL... The formal has a very strong community that makes it viable, where MySQL has the added benefit of direct paid support, should you want it. (Ignoring technical differences, please). Play around and choose what you like. Madi ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] encrypting a query string
Hi all, Is it possible to take a string (ie: a user's password) and have postgres encrypt the string before performing the query? At the moment, I am using postgresql + postfix for email. I need to save the passwords in clear text in the DB and I don't feel safe doing that. I'd like to save the passwords as an SHA1 hash. Then when postfix checks the password it uses a query that converts the passed password into an SHA1 hash then performs the comparison. So a pseudo code of what I'd like is: SELECT foo FROM table WHERE passwd=sha1($password); I did a search on postgres's website and the only reference to SHA1 I saw was in the connection to psql. I hope this doesn't mean it's not possible. I'm much less familiar with postfix and am hoping to avoid mucking around with it. :P Thanks!! Madison ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Moved postgres, now won't start
Hi all, I've created a small 2-node (Debian Etch, PgSQL8.1) cluster using a (shared) DRBD8 partition formatted as ext3 running in Primary/Secondary mode. I shut down postgresql-8.1, moved '/etc/postgresql' and '/etc/postgres-commin' to '/ha/etc' (where '/ha' is the DRBD partitions mount point). Then I created symlinks to the directories under '/ha' and then restarted PostgreSQL. Everything *seemed* okay, until I tried to connect to a database (ie: 'template1' as 'postgres'). Then I get the error: $ psql template1 psql: FATAL: could not open file global/pg_database: No such file or directory When I tried connecting to another DB as a user with a (md5) password it recognizes if the password is right or not. Also, the file: # cat /var/lib/postgresql/8.1/main/global/pg_database postgres 10793 1663 499 499 template1 1 1663 499 499 template0 10792 1663 499 499 Exists, and is readable as you can see. Any idea what's wrong? Does it not like that '/var/lib/postgres - '/ha/var/lib/postgres'? Thanks! Madison ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Moved postgres, now won't start
Tom Lane wrote: I think that's the first actual file access that happens during the connect sequence (everything before that is done with in-memory caches in the postmaster). So what I'm wondering is whether you *really* shut down and restarted the postmaster, or whether you are trying to connect to the same old postmaster process that has now had all its files deleted out from under it. regards, tom lane Thank you for your reply! Before the move; # /etc/init.d/postgresql-8.1 status Version Cluster Port Status OwnerData directory Log file 8.1 main 5432 online postgres /var/lib/postgresql/8.1/main /var/log/postgresql/postgresql-8.1-main.log # /etc/init.d/postgresql-8.1 stop Stopping PostgreSQL 8.1 database server: main. nicole:/etc/postgresql/8.1/main# /etc/init.d/postgresql-8.1 status Version Cluster Port Status OwnerData directory Log file 8.1 main 5432 down postgres /var/lib/postgresql/8.1/main /var/log/postgresql/postgresql-8.1-main.log I hope that doesn't get too mangled. Unless I am misunderstanding stop, then I think it was stopped. I made the move/symlinks mentioned in my first post, then restarted. For double certainty, I switched to the slave node after shutting down postgres on the master node and doubled checked that it was still 'down' as well. Madison ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Moved postgres, now won't start
Zoltan Boszormenyi wrote: Do you use SELinux? Look for avc denied messages in the logs to see if it's the case. No, I don't (unless I missed it and Debian Etch uses it by default now). To be sure, I checked the log files and only say this: 2007-07-16 13:58:03 EDT LOG: incomplete startup packet 2007-07-16 13:58:04 EDT LOG: could not open temporary statistics file global/pgstat.tmp: No such file or directory 2007-07-16 13:59:03 EDT FATAL: could not open file global/pg_database: No such file or directory 2007-07-16 13:59:04 EDT LOG: could not open temporary statistics file global/pgstat.tmp: No such file or directory 2007-07-16 14:00:03 EDT FATAL: could not open file global/pg_database: No such file or directory Over and over again. I tried shutting down postgresql again and got this at the shell: # /etc/init.d/postgresql-8.1 stop Stopping PostgreSQL 8.1 database server: main* pg_ctl: postmaster does not shut down (does not shutdown gracefully, now stopping immediately)pg_ctl: could not send stop signal (PID: 19958): No such process Insecure dependency in kill while running with -T switch at /usr/bin/pg_ctlcluster line 370. (does not shutdown, killing the process) failed! And this in the logs: 2007-07-16 14:28:00 EDT LOG: received fast shutdown request 2007-07-16 14:28:00 EDT LOG: shutting down 2007-07-16 14:28:00 EDT PANIC: could not open control file global/pg_control: No such file or directory 2007-07-16 14:28:00 EDT LOG: background writer process (PID 19960) was terminated by signal 6 2007-07-16 14:28:00 EDT LOG: terminating any other active server processes 2007-07-16 14:28:00 EDT LOG: all server processes terminated; reinitializing 2007-07-16 14:28:00 EDT LOG: could not open file postmaster.pid: No such file or directory 2007-07-16 14:28:00 EDT PANIC: could not open control file global/pg_control: No such file or directory 2007-07-16 14:28:00 EDT LOG: could not open temporary statistics file global/pgstat.tmp: No such file or directory Lastly, to be very sure, I tried grep'ing for that string with no results: nicole:/var/log# grep avc denied * -Rni nicole:/var/log# Thanks for the reply! Madison ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Moved postgres, now won't start
Tom Lane wrote: Madison Kelly [EMAIL PROTECTED] writes: I've created a small 2-node (Debian Etch, PgSQL8.1) cluster using a (shared) DRBD8 partition formatted as ext3 running in Primary/Secondary mode. I shut down postgresql-8.1, moved '/etc/postgresql' and '/etc/postgres-commin' to '/ha/etc' (where '/ha' is the DRBD partitions mount point). Then I created symlinks to the directories under '/ha' and then restarted PostgreSQL. Everything *seemed* okay, until I tried to connect to a database (ie: 'template1' as 'postgres'). Then I get the error: $ psql template1 psql: FATAL: could not open file global/pg_database: No such file or directory I think that's the first actual file access that happens during the connect sequence (everything before that is done with in-memory caches in the postmaster). So what I'm wondering is whether you *really* shut down and restarted the postmaster, or whether you are trying to connect to the same old postmaster process that has now had all its files deleted out from under it. To test your idea, I rebooted both cluster nodes and it works now. How could I have done this without requiring a reboot? Is there a way to tell postgres to create an entirely new connection? Thanks!! Madison ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] PostgreSQL equivelant of this MySQL query
Hi all, I am reading through some docs on switching to Postfix with a SQL backend. The docs use MySQL but I want to use PgSQL so I am trying to adapt as I go. I am stuck though; can anyone help give me the PgSQL equiv. of: SELECT CONCAT(SUBSTRING_INDEX(usr_email,'@',-1),'/',SUBSTRING_INDEX(usr_email,'@',1),'/') FROM users WHERE usr_id=1; If the 'usr_email' value is '[EMAIL PROTECTED]' this should return 'domain.com/person'. Thanks for the help! Madison ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL equivelant of this MySQL query
Madison Kelly wrote: Hi all, I am reading through some docs on switching to Postfix with a SQL backend. The docs use MySQL but I want to use PgSQL so I am trying to adapt as I go. I am stuck though; can anyone help give me the PgSQL equiv. of: SELECT CONCAT(SUBSTRING_INDEX(usr_email,'@',-1),'/',SUBSTRING_INDEX(usr_email,'@',1),'/') FROM users WHERE usr_id=1; If the 'usr_email' value is '[EMAIL PROTECTED]' this should return 'domain.com/person'. Thanks for the help! Madison Bah, answered my own question after posting. :) For the record: SELECT substring(usr_email FROM '@(.*)')||'/'||substring(usr_email FROM '(.*)@') FROM users WHERE usr_id=1; Sorry for the line noise! Madi ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PostgreSQL equivelant of this MySQL query
Steve Atkins wrote: On Jul 13, 2007, at 6:39 PM, Madison Kelly wrote: Hi all, I am reading through some docs on switching to Postfix with a SQL backend. The docs use MySQL but I want to use PgSQL so I am trying to adapt as I go. I am stuck though; can anyone help give me the PgSQL equiv. of: SELECT CONCAT(SUBSTRING_INDEX(usr_email,'@',-1),'/',SUBSTRING_INDEX(usr_email,'@',1),'/') FROM users WHERE usr_id=1; If the 'usr_email' value is '[EMAIL PROTECTED]' this should return 'domain.com/person'. A direct conversion would be something like: select split_part(usr_email, '@', 2) || '/' || split_part(usr_email, '@', 1) from users where usr_id=1; You could also do this: select regexp_replace(usr_email, '(.*)@(.*)', '\2/\1') from users where usr_id=1; http://www.postgresql.org/docs/8.2/static/functions-string.html and http://www.postgresql.org/docs/8.2/static/functions-matching.html are the bits of the docs that cover these functions. Cheers, Steve Thanks Steve! Those look more elegant that what I hobbled together. :) Madi ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] High-availability
Chander Ganesan wrote: Madison Kelly wrote: Hi all, After realizing that 'clustering' in the PgSQL docs means multiple DBs behind one server, and NOT multple machines, I am back at square one, feeling somewhat the fool. :P Can anyone point me to docs/websites that discuss options on replicating in (as close as possible to) realtime? Ideally with load balancing while both/all servers are up, and failover/resyncing when a member fails and is restored. If you're interested in the less than ideal case (no load balancing, but synchronous replication in a warm standby type mode), there are several options, such as shared disk (two systems sharing a SAN or NAS with heartbeat-style fail over - shared disk scenario), or DRBD (where block level changes to one device are mirrored in real-time over to another, with heartbeat style fail over - this is a shared nothing type scenario). It's not too hard to put together a warm standby synchronous replication mechanism with overhead that isn't too much more than what you incur by enabling PITR... Such systems can also have very fast failover on failure detection (via heartbeat2), and be synchronous. I think you'll typically find that you can get one or the other - synchronous replication, or load balancing...but not both. On the other hand, if you were really serious about having close to both, you could have a three node setup - two (a provider and subscriber) that run using Slony-I (and async replication) and one that runs using one of the aforementioned methods (i.e., DRBD and warm-standby synchronous replication). In such cases a failover would mean switching to the synchronous replication system. You should even be able to get SLONY to continuing to avail you with load balancing in such a case, without having to re-sync - though I haven't tried this myself... You'd still have a potential query that got stale data (when it went to a Slony-I subscriber), but you would never lose a committed transaction. You'd have the added benefit of a shared nothing environment as well... As a side plug, we discuss and implement a few of these options in our PostgreSQL performance tuning course.. http://www.otg-nc.com/training-courses/coursedetail.php?courseid=47cat_id=8 Is this even possible on PostgreSQL? Being a quite small company, proprietary hardware and fancy software licenses are not possible (ie: 'use oracle' won't help). I've looked at slony, but it looks more like a way to push occasional copies to slaves, and isn't meant to be real time. Am I wrong by chance? Thanks for any help/tips/pointers! Chander Ganesan Open Technology Group, Inc. One Copley Parkway, Suite 210 Morrisville, NC 27560 Phone: 877-258-8987/919-463-0999 http://www.otg-nc.com *Expert PostgreSQL Training - On-Site and Public Enrollment* Madi ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Thank you for your reply! The more I learn, the more I am leaning towards the DRBD/shared-nothing setup. Our loads are not terribly heavy at this point. I hate the idea of having a nice server sitting there doing nothing 99% of the time, but it looks like the most viable way of setting up HA at this point. Given that I am learning as I go, I think the three-way setup you describe would be a bit too ambitious for me just now. That said, I do have a spare third server that I could use for just such a setup, should I feel comfortable enough down the road. Madi ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] High-availability
Lew wrote: Madison Kelly wrote: Being a quite small company, proprietary hardware and fancy software licenses are not possible (ie: 'use oracle' won't help). How much data do you put in the DB? Oracle has a free version, but it has size limits. (Ducking the slings and arrows of outraged PG fans: I prefer Postgre, I really do.) Hrm, it's hard to say as we're (hoping!) to grow. At the moment, a few hundred megs. If the company gets off the ground, possibly much more. also, we've got a few (dozen or so) side projects that each have their own DBs. I think the risk of running into a barrier like a size limit would be too much. Even if we get off the ground, the storage needs of the DB will outgrow our revenue. I'd hate to be in a position where I am dependent on a (potentially) very expensive invoice while we are still running on a shoe-string. Thanks for the suggestion though! I will poke at the free/trial version and, if I am unable to load-balance pgSQL and we run into performance problems, I will have a better idea of what options I have (ie: bigger iron vs. an oracle license). Thanks! Madi ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] High-availability
Alexander Staubo wrote: On 6/1/07, Madison Kelly [EMAIL PROTECTED] wrote: After realizing that 'clustering' in the PgSQL docs means multiple DBs behind one server, and NOT multple machines, I am back at square one, feeling somewhat the fool. :P I remember being similarly disappointed in this rampant co-opting of the word cluster back in 7.4 or so. :) A gaggle of geese, a murder of crows, a cluster of databases, I guess. Can anyone point me to docs/websites that discuss options on replicating in (as close as possible to) realtime? Ideally with load balancing while both/all servers are up, and failover/resyncing when a member fails and is restored. The PostgreSQL documentation gives a pretty good overview of the options: http://www.postgresql.org/docs/8.2/interactive/high-availability.html That said, there is to my knowledge no single, integrated product that will do all you ask. None are capable of anything near real-time, automatic failover tends to be left as an exercise for the reader, and there is a lot of work to get it up and running, and requires particular care in maintenance and monitoring once it's up. There are several commercial (Mammoth Replicator comes to mind) and several open-source projects. Among the open-source ones (Slony-I, pgpool, PGCluster), I believe Slony-I is the most mature. There are a few in-progress attempts (pgpool-II, PGCluster 2, PostgreSQL-R) that are not ready for prime time yet; of these, I believe pgpool-II is the most promising. As mentioned in a different thread today, work is being done to implement WAL-based master-slave replication, which I think should prove more scalable and more transparent than the current third-party products: http://archives.postgresql.org/pgsql-hackers/2007-03/msg00050.php I've looked at slony, but it looks more like a way to push occasional copies to slaves, and isn't meant to be real time. Am I wrong by chance? Slony is indeed intended for near-real-time replication; it's asynchronous, so slaves always lag behind the master. The amount of discrepancy depends on a bunch of factors -- individual node performance, network performance, and system load. Alexander. That was *exactly* the kind of link I was trying to find. Thank you! Madi ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] High-availability
Hi all, After realizing that 'clustering' in the PgSQL docs means multiple DBs behind one server, and NOT multple machines, I am back at square one, feeling somewhat the fool. :P Can anyone point me to docs/websites that discuss options on replicating in (as close as possible to) realtime? Ideally with load balancing while both/all servers are up, and failover/resyncing when a member fails and is restored. Is this even possible on PostgreSQL? Being a quite small company, proprietary hardware and fancy software licenses are not possible (ie: 'use oracle' won't help). I've looked at slony, but it looks more like a way to push occasional copies to slaves, and isn't meant to be real time. Am I wrong by chance? Thanks for any help/tips/pointers! Madi ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Query help
Richard Huxton wrote: Madison Kelly wrote: Hi all, I've got a query that looks through a table I use for my little search engine. It's something of a reverse-index but not quite, where a proper reverse index would have 'word | doc1, doc3, doc4, doc7' showing all the docs the keyword is in, mine has an entry for eac I've got a query like: SELECT sch_id, sch_for_table, sch_ref_id, sch_instances FROM search_index WHERE (sch_keyword LIKE '%digi%' OR sch_keyword LIKE '%madi%') AND sch_for_table!='client' AND ... (more restrictions) ORDER BY sch_instances DESC; This returns references to a data column (sch_ref_id) in a given table (sch_for_table) for each matched keyword. The problem I am having is that two keywords might reference the same table/column which would, in turn, give me two+ search results pointing to the same entry. What I would like to do is, when two or more results match the same 'sch_ref_id' and 'sch_for_table' to merge the results. Specifically, the 'sch_instances' column is the number of times the given keyword is found in the table/column. I'd like to add up the number in the duplicate results (to give it a higher accuracy and move it up the search results). You'll want something like: SELECT sch_id, sch_for_table, sch_ref_id, SUM(sch_instances) AS tot_instances ... GROUP BY sch_id, sch_for_table, sch_ref_id ORDER BY tot_instances DESC; The key word to search the manuals on is aggregates (sum(), count() etc). This is *exactly* the pointer I needed, thank you! Sad thing is that I even used GROUP BY before... had just forgotten about it. ^_^; Madison ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Query help
Hi all,
I've got a query that looks through a table I use for my little
search engine. It's something of a reverse-index but not quite, where a
proper reverse index would have 'word | doc1, doc3, doc4, doc7' showing
all the docs the keyword is in, mine has an entry for eac
I've got a query like:
SELECT
sch_id, sch_for_table, sch_ref_id, sch_instances
FROM
search_index
WHERE
(sch_keyword LIKE '%digi%' OR sch_keyword LIKE '%madi%')
AND
sch_for_table!='client'
AND
... (more restrictions)
ORDER BY
sch_instances DESC;
This returns references to a data column (sch_ref_id) in a given
table (sch_for_table) for each matched keyword.
The problem I am having is that two keywords might reference the same
table/column which would, in turn, give me two+ search results pointing
to the same entry.
What I would like to do is, when two or more results match the same
'sch_ref_id' and 'sch_for_table' to merge the results. Specifically, the
'sch_instances' column is the number of times the given keyword is found
in the table/column. I'd like to add up the number in the duplicate
results (to give it a higher accuracy and move it up the search results).
Is this possible or would I need to add this logic in my program? I'd
rather do it in PostgreSQL though, if I could.
Here is the 'search_index' table I am using:
db= \d search_index
Table public.search_index
Column | Type | Modifiers
---+-+---
sch_id| integer | not null default nextval('sch_seq'::regclass)
sch_keyword | text| not null
sch_instances | integer | not null default 1
sch_for_table | text| not null
sch_ref_id| integer | not null
Indexes:
search_index_pkey PRIMARY KEY, btree (sch_id)
Thanks in advance to any help you might be able to give me!
Madison
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Possibly odd question; diff results?
Merlin Moncure wrote: On 2/8/07, Madison Kelly [EMAIL PROTECTED] wrote: Hi all, I've got a 'history' schema that records changes in the public schema tables over time. I use a trigger and function to do this. What I would like to do though, and this may not even be possible, is say something like (pseudo-code) SELECT DIFF foo_name FROM history.foo WHERE foo_id=X; and have a *nix 'diff' style results shown (sort of like looking at diffs in CVS/SVN). you can start by using the 'except' boolean query operator; select * from foo except * from bar; This will give you rows in foo that are not exactly in bar (matching every field). If you want it in both directions you can: (select * from foo except select * from bar) union (select * from bar except select * from foo); you can then take the results of these queries and mark up the text however you want. Just a heads up: the boolean sql operators are famous for generating sequential scans. merlin Hi, Thanks for replying! I think this isn't much help for me though. For example; SELECT att_id, att_name, att_pt_id FROM history.attribute WHERE att_id=3; att_id | att_name| att_pt_id +---+-- 3 | Bay, Internal 3.5 drive | 44,7,8,1,26,39,40,41 3 | Bay, Internal 3.5 drive | 44,7,8,1,26,36 3 | Bay, Internal 3.5quot; drive | 44,7,8,1,26,36 SELECT att_id, att_name, att_pt_id FROM attribute WHERE att_id=3; att_id | att_name| att_pt_id +---+ 3 | Bay, Internal 3.5quot; drive | 44,7,8,1,26,36 SELECT att_id, att_name, att_pt_id FROM history.attribute WHERE att_id=3 EXCEPT SELECT att_id, att_name, att_pt_id FROM attribute WHERE att_id=3; att_id | att_name | att_pt_id +--+-- 3 | Bay, Internal 3.5 drive | 44,7,8,1,26,36 3 | Bay, Internal 3.5 drive | 44,7,8,1,26,39,40,41 This shows me the rows in the history schema that are not in the public schema, which is nice but it doesn't tell me which columns have changed in each version. What I would like would be results like (pseudo again): SELECT DIFF history_id, att_id, att_name, att_pt_id FROM history.attribute WHERE att_id=3 ORDER BY modified_date DESC; history_id | att_id | att_name| att_pt_id ++---+-- 86 || Bay, Internal 3.5quot; drive | 85 || | 44,7,8,1,26,36 82 | 3 | Bay, Internal 3.5 drive | 44,7,8,1,26,39,40,41 The first row being all new so all items return, the second row returns only the 'att_pt_id' which changed, and the third returning 'att_name' which changed. Thanks again!! Madi ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Possibly odd question; diff results?
Hi all, I've got a 'history' schema that records changes in the public schema tables over time. I use a trigger and function to do this. What I would like to do though, and this may not even be possible, is say something like (pseudo-code) SELECT DIFF foo_name FROM history.foo WHERE foo_id=X; and have a *nix 'diff' style results shown (sort of like looking at diffs in CVS/SVN). Has anyone done this or should I just look into writing a small program to do this outside of postgres? Thanks! Madi ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] How I can read-back a serial value just inserted?
dfx wrote:
Dear Sirs,
my question is very simple:
when I insert a row whith a serial field, a value is automatically
generated; how can I know this value, strictly of my row, without the risk
of to read the value of another subsequent insertion?
Thank you.
Domenico
Hiya,
Not sure if it will help you, but what I do is:
SELECT nextval('pt_seq'::regclass);
Take the returned value and use it in the INSERT statement. So for
example, with the sequence/table:
CREATE SEQUENCE pt_seq
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
ALTER TABLE pt_seq OWNER TO digimer;
CREATE TABLE part_type (
pt_id int primary key default(nextval('pt_seq')),
pt_name text,
...
);
ALTER TABLE part_type OWNER TO digimer;
I would do (in perl, but other languages should be similar enough):
my $pt_id=$dbh-selectrow_array(SELECT nextval('pt_seq'::regclass));
$dbh-do(INSERT INTO part_type (pt_id, pt_name...) VALUES ($pt_id,
'$name'...));
Hope that helps!
Madi
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] pg_dump question
Hi all,
I've created a database (pgsql 8.1 on Debian Etch) that uses
triggers/functions to keep all changes for various tables in a history
schema. This is the first time I've done this (captured and stored
changes in a different schema) so I was hoping for some backup/restore
advice.
As far as I can tell, you can only dump one schema at a time. Is this
true? If so, can I dump 'public' first and then append the dump of
'history' to the same file and be okay? Also, when I restore from this
file, can I prevent the triggers from running just during the reload of
the data?
I hope these aren't too junior questions. :)
Madi
PS - In case it helps, here's an example of a table/function I am using:
CREATE TABLE files (
file_id int default(nextval('id_seq')),
file_for_table textnot null,
file_ref_id int not null,
file_desc text,
file_name textnot null,
file_file_name textnot null,
file_type textnot null,
file_os textnot null,
file_vertext,
file_active boolean not nulldefault 't',
added_date timestamp without time zone not null
default now(),
added_user int not null,
modified_date timestamp without time zone not null
default now(),
modified_user int not null
);
ALTER TABLE files OWNER TO digimer;
CREATE TABLE history.files (
file_id int not null,
file_for_table textnot null,
file_ref_id int not null,
file_desc text,
file_name textnot null,
file_file_name textnot null,
file_type textnot null,
file_os textnot null,
file_vertext,
file_active boolean not null,
added_date timestamp without time zone not null,
added_user int not null,
modified_date timestamp without time zone not null,
modified_user int not null
);
ALTER TABLE history.files OWNER TO digimer;
CREATE FUNCTION history_files() RETURNS trigger
AS $$
DECLARE
hist_files RECORD;
BEGIN
SELECT INTO hist_files * FROM public.files WHERE
file_id=new.file_id;
INSERT INTO history.files
(file_id, file_for_table, file_ref_id, file_desc, file_name,
file_file_name, file_type, file_os, file_ver, file_active, added_user,
modified_date, modified_user)
VALUES
(hist_files.file_id, hist_files.file_for_table,
hist_files.file_ref_id, hist_files.file_desc, hist_files.file_name,
hist_files.file_file_name, hist_files.file_type, hist_files.file_os,
hist_files.file_ver, hist_files.file_active, hist_files.added_user,
hist_files.modified_date, hist_files.modified_user);
RETURN NULL;
END;$$
LANGUAGE plpgsql;
ALTER FUNCTION history_files() OWNER TO digimer;
CREATE TRIGGER trig_files AFTER INSERT OR UPDATE ON files FOR EACH ROW
EXECUTE PROCEDURE history_files();
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] pg_dump question
Richard Huxton wrote: As far as I can tell, you can only dump one schema at a time. Is this true? No, pg_dump dumps a whole database by default. You can dump just a single schema or table though. Hmm, I wonder why I thought this... Was this true in older versions or did I just imagine this? :) If so, can I dump 'public' first and then append the dump of 'history' to the same file and be okay? No, someone might have updated public in-between. Ah, of course. Also, when I restore from this file, can I prevent the triggers from running just during the reload of the data? Yes, there's a command-line setting when doing a data-only restore. When doing a full restore (schema+data) this is done for you. Try the page below or man pg_dump/man pg_restore for full details: http://www.postgresql.org/docs/8.1/static/reference-client.html I had read the man pages, but I re-read them and I apparently went on a mental vacation and missed a fair chunk of it. *sigh* Thanks kindly for your reply! Madi ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] MySQL drops support for most distributions
David Goodenough wrote: http://developers.slashdot.org/article.pl?sid=06/12/13/1515217from=rss MySQL quietly deprecated support for most Linux distributions on October 16, when its 'MySQL Network' support plan was replaced by 'MySQL Enterprise.' MySQL now supports only two Linux distributions — Red Hat Enterprise Linux and SUSE Linux Enterprise Server. We learned of this when MySQL declined to sell us support for some new Debian-based servers. Our sales rep 'found out from engineering that the current Enterprise offering is no longer supported on Debian OS.' We were told that 'Generic Linux' in MySQL's list of supported platforms means 'generic versions of the implementations listed above'; not support for Linux in general. I *really* hope this helps convince people to migrate to PostgreSQL. Every time I need to support MySQL I go that much more gray. :/ This could be good. Madi ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Male/female
Jorge Godoy wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Yes, but further I don't know of any country that recognizes anything but Male or Female. I haven't read the beginning of the thread, but will this table be used only for humans? There are animals that are hermafrodites (I hope I got the English correct...) or whose sex is only identifiable after a period of time (days or months, usually). So, for researchers it would be interesting to have more options. Also, if you're doing statistics on something where the sexual option (and transgerderness) is important, then there should be some way to point that. Some people argue that gender is a spectrum. If you want to be very inclusive. Maybe you could use a 'float' and stick with 0 = woman, 1 = man (self documenting after all) with the option of '0.1 - 0.9' for people who feel in between. How efficient is 'float'? This would also work for animals that fall outside then normal male/female designation. Madi ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Is there a PostgreSQL utility that is similiar to Oracles
Wm.A.Stafford wrote: We are trying to load our PostgreSQL DB with data that contains many corrupted rows. I recall that sql loader will skip corrupted rows and keep going. We are using the PostgreSQL copy command to load and it just gives up when the first corrupted row is encountered. Thanks in a advance, -=bill This might not help, but I don't know of a tool to do what you want (I am not an experts, others here may). How difficult would it be to write a script (perl?) to read in the dump file and translate all of the COPY... calls into individual INSERTs with autocommit on and just log the bad rows? Sorry if this doesn't help. :) Madi ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] A query planner that learns
AgentM wrote: Alvaro's advice is sound. If the patent holder can prove that a developer looked at a patent (for example, from an email in a mailing list archive) and the project proceeded with the implementation regardless, malice can been shown and damages can be substantially higher. You're screwed either way but your safest bet is to never look at patents. Disclaimer: I am not a lawyer- I don't even like lawyers. Nor am I a lawyer, but I still hold that hoping ignorance will be a decent defense is very, very risky. In the end I am not a pgSQL developer so it isn't in my hands either way. Madi ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] A query planner that learns
Brian Mathis wrote: I also am NAL, but I know enough about the patent system (in the US) to know that ignorance *IS* a defense. If you are ignorant of the patent, you only have to pay the damages. If you knew about the patent and did it anyway, you have to pay *triple* damages. Ignorance will save you lots of money. You may not like it, but that's the way it is. I got that part. :) If you _do_ end up in court, plausible deniability helps. My position though is that it is better, in the long term, to be aware of the patents and take the time to work around them so that *no* damages need to be paid. Or, as might be that chance in this case, to get a written okay from the patent holder for the use of the methods protected by the patent in a given program. Colour me funny, but wouldn't staying out of the courts in the first place not be the best option? Madi ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] A query planner that learns
Alvaro Herrera wrote: Yeah. I invite you to do all the extra (useless) development work required. But please do not charge other people with it. Whoever investigates patents and lets pgsql-hackers know about them, is charging the Postgres community with that work. We sure don't need it. As a developer (different project that uses pgsql), I am no more happy about the current status of the USPO than you are. I think it's a giant cess-pool of greed, ignorance and waste... BUT... it is currently the law in the USA. How fast would Oracle, Microsoft or others jump on a chance to tear PostgreSQL apart by dragging it through the courts and crushing it under any fines (regardless of the amount)? If that happened, *all* of the pgsql-hacker's time would be wasted, never mind the countless other projects that rely on PgSQL. As Scott said, there are 0 good answers to this problem. If PgSQL is going to be active in the US then it has to play by the (bent and twisted) rules. That is why I started my comments with call your congress(wo)man. Simply ignoring that which you don't like is just far to risky with the sharks in our waters. It's extra work, and it's unfair to the developers who already put their free time into this program, but IMHO it's the only safe way to go. Again though, not being a pgsql-hacker my comments here are just that. Madi ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] A query planner that learns
Alvaro Herrera wrote: Jochem van Dieten wrote: Scott Marlowe wrote: While all the talk of a hinting system over in hackers and perform is good, and I have a few queries that could live with a simple hint system pop up now and again, I keep thinking that a query planner that learns from its mistakes over time is far more desirable. Is it reasonable or possible for the system to have a way to look at query plans it's run and look for obvious mistakes its made, like being off by a factor of 10 or more in estimations, and slowly learn to apply its own hints? Technically it is very feasible. But I think you might want to check US Patent 6,763,359 before you start writing any code. I think it would be a very good idea if you guys stopped looking at the US patent database. It does no good to anyone. There's no way we can avoid stomping on a patent or another -- there are patents for everything. Hasn't IBM release a pile of it's patents for use (or at least stated they won't sue) to OSS projects? If so, is this patent covered by that amnesty? Simply ignoring patents because there is a patent for everything is a recipe for disaster. Companies like MS are running out of ways to tear open OSS and they are certainly not above (below?) suing the heck out of OSS projects for patent infringement. What's needed is reform in the USPO. Call you congress (wo)man and complain, but don't flaunt the law; you will lose. Madi ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] A query planner that learns
Alvaro Herrera wrote: Hasn't IBM release a pile of it's patents for use (or at least stated they won't sue) to OSS projects? If so, is this patent covered by that amnesty? This is useless as a policy, because we have plenty of companies basing their proprietary code on PostgreSQL, which wouldn't be subject to the grant (EnterpriseDB, Command Prompt, Fujitsu, SRA). We do support them. More specifically, it is then up to the 3rd party (non-OSS) developers to clear the patents. It's not the PgSQL team's problem in this case (assuming it's the case at all). Simply ignoring patents because there is a patent for everything is a recipe for disaster. Companies like MS are running out of ways to tear open OSS and they are certainly not above (below?) suing the heck out of OSS projects for patent infringement. It has been said that unknowingly infringing a patent is much less problematic than knowingly doing same. We don't have the manpower to implement the whole Postgres without infringing a single patent, so the best approach is to refrain from researching possible patents applying to us in the first place. If people comes here and points at patents that we infringe or may infringe, it will cause much more (useless) work for hackers which then have to search alternative ways of doing the same things. Plausible Deniability and all that jazz. There is another truism though; Ignorance of the law is no excuse. Besides, claiming ignorance doesn't keep you out of the courts in the first place. The people who would attack OSS applications generally have very, very deep pockets and can run a project out of money before the trial was over. They could do that non-the-less (SCO, hello?) but I still suggest NOT tempting fate. It's unfortunate that this legal mess causes the developers more headaches than they need, but it still can't be ignored, imho. What's needed is reform in the USPO. Call you congress (wo)man and complain, but don't flaunt the law; you will lose. I agree. However, I am not an US inhabitant in the first place, and bless my parents for that. Heck, I was even denied a visa -- twice. Please do us all a favor and write to your congresspeople. Heh, I'm not an American either, so I can't do anything but shake my head (and be equally glad that my own personal OSS program is here in Canada). American industry wonders why they are losing so many IT positions... It's such a difficult and unfriendly environment there for anyone but the biggest companies. Far too litigious. Madi ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] invalid data in PID file
Tom Lane wrote: J S B [EMAIL PROTECTED] writes: FATAL: could not open lock file /tmp/.s.PGSQL.5432.lock: Permission denied Can you please tell me what is this all about? It looks to me like you have, or had, another postmaster running under a different userid. Perhaps you should back up to the beginning and tell us what you've been doing with this installation. regards, tom lane IIRC, I had that happen on a botched upgrade of postgres. Even if I did a reinstall it wasn't enough. Under Debian I had to try upgrading again, then do a complete uninstall (or something similar), then a fresh reinstall and it finally worked again. It happened a couple of times and I never figured out why. Hasn't happened lately though. Madi ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Determining size of a database before dumping
Hi all, I am (re)writing a backup program and I want to add a section for backing up pSQL DBs. In the planning steps (making sure a given destination has enough space) I try to calculate how much space will be needed by a 'pg_dump' run *before* actually dumping it. Is there a relatively easy way to do that? Moreso, if it possible to do this from an unpriviledged account? If not, is there a way to add the permissions to a specific pg user to allow that user to perform this? Thanks in advance! Madison ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Determining size of a database before dumping
Steve Wampler wrote: Madison Kelly wrote: Hi all, I am (re)writing a backup program and I want to add a section for backing up pSQL DBs. In the planning steps (making sure a given destination has enough space) I try to calculate how much space will be needed by a 'pg_dump' run *before* actually dumping it. I suppose: pg_dump $PGD_OPTIONS | wc -c isn't efficient enough, right? Without knowing the options you plan to use with pg_dump (compression? dump just tables?, etc.) this is going to be hard to get a decent estimate from... For now, lets assume I am doing a raw dump (no compression) and no fancy switches. I would probably err of the side of caution and try dumping OIDs and all schema (plus whatever else is needed to insure a full restore to a clean DB). I could try piping the dump into something like 'wc' but with very large DBs I'd be worried about the (tremendous) disk I/O that would cause. This is also why I am hoping Pg keeps this info somewhere. Madison ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Determining size of a database before dumping
Alexander Staubo wrote: On Oct 2, 2006, at 22:17 , Madison Kelly wrote: I am (re)writing a backup program and I want to add a section for backing up pSQL DBs. In the planning steps (making sure a given destination has enough space) I try to calculate how much space will be needed by a 'pg_dump' run *before* actually dumping it. Is there a relatively easy way to do that? Moreso, if it possible to do this from an unpriviledged account? If not, is there a way to add the permissions to a specific pg user to allow that user to perform this? You could dump the database to /dev/null, piping it through wc to catch the size, but that would of course be wasteful. You could count the disk space usage of the actual stored tuples, though this will necessarily be inexact: http://www.postgresql.org/docs/8.1/static/diskusage.html Or you could count the size of the physical database files (/var/lib/postgresql or wherever). While these would be estimates, you could at least guarantee that the dump would not *exceed* the esimtate. Keep in mind that pg_dump can compress the dump and (iirc) will do so by default when you use the custom format (-Fc or --format=c). Alexander. Heh, that looks like just the article I would have wanted if I had properly RTFM. :D Many thanks!! Madi ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Logging queries
Hi all, I've got a machine I am setting up (read; low volume atm). I need a way to log all the queries made to Postgres (just for a short time). I've got a problem with a 3rd party program (OSS, but I'm not 'let' enough to trace it) and I am hoping to help fix the problem by being able to see what query it is exactly trying to do. I remember once long ago I did this but I'll be dumb-struck if I can remember how. As an aside; I've got a user named 'madison' who is in postgres and owns another database which is working fine with one of my programs. So I know the user is okay. I've created a new database and I am trying to tell this program to connect to the new database as this user (local machine) but I keep getting the error: 2006-02-02 23:07:36 [21958] LOG: connection received: host=127.0.0.1 port=33387 2006-02-02 23:07:36 [21958] FATAL: IDENT authentication failed for user madison 2 I've tried changing the 'pg_hba.conf' file from 'ident sameuser' to 'trust'' with no love, too. I'm hoping that seeing the query this program is using might help me solve this problem. Thanks!! Madison -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Madison Kelly (Digimer) TLE-BU; The Linux Experience, Back Up Main Project Page: http://tle-bu.org Community Forum:http://forum.tle-bu.org -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Logging queries
Madison Kelly wrote: Hi all, I've got a machine I am setting up (read; low volume atm). I need a way to log all the queries made to Postgres (just for a short time). I've got a problem with a 3rd party program (OSS, but I'm not 'let' enough to trace it) and I am hoping to help fix the problem by being able to see what query it is exactly trying to do. I remember once long ago I did this but I'll be dumb-struck if I can remember how. As an aside; I've got a user named 'madison' who is in postgres and owns another database which is working fine with one of my programs. So I know the user is okay. I've created a new database and I am trying to tell this program to connect to the new database as this user (local machine) but I keep getting the error: 2006-02-02 23:07:36 [21958] LOG: connection received: host=127.0.0.1 port=33387 2006-02-02 23:07:36 [21958] FATAL: IDENT authentication failed for user madison 2 I've tried changing the 'pg_hba.conf' file from 'ident sameuser' to 'trust'' with no love, too. I'm hoping that seeing the query this program is using might help me solve this problem. Thanks!! Madison Oops, should have mentioned that I'm running 7.4.7 on Debian Sarge stable. Madison -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Madison Kelly (Digimer) TLE-BU; The Linux Experience, Back Up Main Project Page: http://tle-bu.org Community Forum:http://forum.tle-bu.org -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Solved: Re: [GENERAL] Logging queries
Madison Kelly wrote: Madison Kelly wrote: Hi all, I've got a machine I am setting up (read; low volume atm). I need a way to log all the queries made to Postgres (just for a short time). I've got a problem with a 3rd party program (OSS, but I'm not 'let' enough to trace it) and I am hoping to help fix the problem by being able to see what query it is exactly trying to do. I remember once long ago I did this but I'll be dumb-struck if I can remember how. As an aside; I've got a user named 'madison' who is in postgres and owns another database which is working fine with one of my programs. So I know the user is okay. I've created a new database and I am trying to tell this program to connect to the new database as this user (local machine) but I keep getting the error: 2006-02-02 23:07:36 [21958] LOG: connection received: host=127.0.0.1 port=33387 2006-02-02 23:07:36 [21958] FATAL: IDENT authentication failed for user madison 2 I've tried changing the 'pg_hba.conf' file from 'ident sameuser' to 'trust'' with no love, too. I'm hoping that seeing the query this program is using might help me solve this problem. Thanks!! Madison Oops, should have mentioned that I'm running 7.4.7 on Debian Sarge stable. Madison For the love of... . I found the answer right after posting... Just had to set 'log_min_duration_statement' in 'postgres.conf' to '0'. Sorry for the line noise! ^.^; Madison -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Madison Kelly (Digimer) TLE-BU; The Linux Experience, Back Up Main Project Page: http://tle-bu.org Community Forum:http://forum.tle-bu.org -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Ident authentication failed
Lucky Leavell wrote: v8.0.3 OS: SuSE 10.0 Pro I am new to PostgreSQL but fairly familiar with other RDBMS' such as Ingres, Informix and MySQL. I am attempting to switch from using MySQL to PostgreSQL with postfix. I have created the postfix user using createuser with a password but denying the ability to create databases or other users. However when I attempt to login even using psql: psql -W -U postfix it prompts me for the password and I enter the one that user was created with. The resulting error is: psql: FATAL: Ident authentication failed for user 'postfix' I know I must be missing something simple but I cannot find it in the docs or FAQs. Please point me in the right direction. Thank you, Lucky By default, PostgreSQL won't let you connect unless your system user account matches the user account in postgres... You can either 'su' to the 'postfix' user or edit your 'pg_hba.conf' file to have the line: local all all trust Usually there will already be the line: local all all ident sameuser Be aware though that this essentially opens postgres access to anyone with shell access. There are a few other ways to deal with them but the PostgreSQL docs do a better job at explaining it than I can. HTH! Madison -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Madison Kelly (Digimer) TLE-BU; The Linux Experience, Back Up Main Project Page: http://tle-bu.org Community Forum:http://forum.tle-bu.org -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Check If PostgreSQL Table Exists
[EMAIL PROTECTED] wrote:
Dear All,
How To Check If PostgreSQL Table Exists in the Database Using Perl.
Dhilchrist
Here is what I do...
$DBreq=$DB-prepare(SELECT COUNT(*) FROM pg_tables WHERE
tablename='foo') || die $DBI::errstr;
$DBreq-execute();
my ($table_num)=$DBreq-fetchrow_array();
if ( $table_num 1 )
{
# Create the missing table
}
else
{
# It exists!
}
Hope that helps!
Madison
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] Getting a DB password to work without editing pg_hba.conf, possible?
Hi all,
I am working on an installer for my program that creates a postgres
database and user (the installer is written in perl and runs as 'root').
I want to find a way to let the user set the password on the new
database and have postgres actually ask for it without editing the
default 'pg_hba.conf' file, if at all possible.
I know how to set the password on the user:
CREATE USER foo WITH PASSWORD 'secret' CREATEDB NOCREATEUSER;
and from what I can tell there is no way to put a password on a
database. I create the database using:
CREATE DATABASE bar OWNER foo;
From what I read in the docs, this *should* limit access to the 'bar'
database to only the 'postgres' and 'foo' (I can't find now where in the
postgres docs I read that so I may be wrong).
The problems are:
- Connections are limited to the matching system account ('foo' in
this case) which is good, but it doesn't require the password to connect.
- A normal user connected to another database can switch to the 'bar'
database using '\c foo' without requiring a password.
When I have played with the 'pg_hba.conf' file by adding the line:
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
# Database administrative login by UNIX sockets
local bar foomd5
local all postgres ident sameuser
I find that when I try to connect to the DB 'bar' as the system user
'foo' I *do* get prompted for the password. However, when I try
connecting as another user I get in without being prompted for a
password at all.
Any help with this would be much appreciated!
Madison
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU; The Linux Experience, Back Up
Main Project Page: http://tle-bu.org
Community Forum:http://forum.tle-bu.org
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Getting a DB password to work without editing pg_hba.conf,
Tom Lane wrote: Madison Kelly [EMAIL PROTECTED] writes: I want to find a way to let the user set the password on the new database and have postgres actually ask for it without editing the default 'pg_hba.conf' file, if at all possible. There is no such animal as a database password in PG. There are user passwords. You can grant or deny a user access to a database altogether, but you can't predicate it on him supplying a password different from his (single) login password. regards, tom lane Thanks for the reply! May I ask then? What *is* considered best practices for securing a database in PostgreSQL? Assuming I leave the 'pg_hba.conf' file at it's default values, is there any real point to having a password on a postgresql user account? I've been reading the docs but I guess I am overthinking the problem or missing something obvious. :p Madison -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Madison Kelly (Digimer) TLE-BU; The Linux Experience, Back Up Main Project Page: http://tle-bu.org Community Forum:http://forum.tle-bu.org -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Getting a DB password to work without editing pg_hba.conf,
Martijn van Oosterhout wrote:
On Fri, Dec 16, 2005 at 02:09:52PM -0500, Madison Kelly wrote:
May I ask then? What *is* considered best practices for securing a
database in PostgreSQL? Assuming I leave the 'pg_hba.conf' file at it's
default values, is there any real point to having a password on a
postgresql user account? I've been reading the docs but I guess I am
overthinking the problem or missing something obvious. :p
If someone can login without being asked for a password, that generally
means the system is setup not to ask. I'm not sure what you mean by
default configuration, since you are probably using the one installed
by your distro.
It's very hard to see what the problem is unless you post your full
pg_hba.conf and the actual command-lines you used, including which UNIX
user you used. The two lines you gave would allow the postgres UNIX
user to login to any database as himself without a password, and allow
foo into bar with md5 authentication. If you are seeing something else
you should be explicit how you're logging in.
Have a nice day,
Oh shoot, I really wasn't very verbose, was I? Sorry about that.
I am running Debian Sarge with the debian-provided PostgreSQL 7.4 deb
pakage. The 'pg_hba.conf' file I am using (unedited from the one that
was installed with most comments removed) is:
# TYPE DATABASEUSERIP-ADDRESSIP-MASK METHOD
# Database administrative login by UNIX sockets
local all postgres
ident sameuser
#
# All other connections by UNIX sockets
local all all
ident sameuser
#
# All IPv4 connections from localhost
hostall all 127.0.0.1 255.255.255.255
ident sameuser
#
# All IPv6 localhost connections
hostall all ::1
:::::::ident sameuser
hostall all :::127.0.0.1/128
ident sameuser
#
# reject all other connection attempts
hostall all 0.0.0.0 0.0.0.0 reject
That is without the line I added there anymore.
After creating the database and the user this is what I have
(connected to 'template1' as 'postgres'):
template1=# SELECT * FROM pg_database;
datname | datdba | encoding | datistemplate | datallowconn |
datlastsysoid | datvacuumxid | datfrozenxid | datpath | datconfig |
datacl
---++--+---+--+---+--+--+-+---+--
tle-bu|100 |8 | f | t|
17140 | 735 | 3221226208 | | |
template1 | 1 |8 | t | t|
17140 | 735 | 3221226208 | | |
{postgres=C*T*/postgres}
template0 | 1 |8 | t | f|
17140 | 464 | 464 | | |
{postgres=C*T*/postgres}
(3 rows)
template1=# SELECT * FROM pg_shadow;
usename | usesysid | usecreatedb | usesuper | usecatupd |
passwd| valuntil | useconfig
--+--+-+--+---+-+--+---
postgres |1 | t | t| t |
| |
tle-bu | 100 | t | f| f |
md562c7c93e482292a88903ac6b65cdb34c | |
(2 rows)
You can see that I have created a password for the 'tle-bu' user. Now
when I try to connect I get the psql: FATAL: IDENT authentication
failed for user tle-bu error when I try to connect from the 'madison'
shell account using:
$ psql tle-bu -U tle-bu
Which is good. Though, if I add the user 'madison' to the database as
a user and create a database owned by her:
template1=# CREATE USER madison;
CREATE USER
template1=# CREATE DATABASE test OWNER madison;
CREATE DATABASE
And then connect to the 'test' database as the user 'madison' I can
then use '\c' to connect to the 'tle-bu' database:
$ psql test -U madison
Welcome to psql 7.4.7, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
test= \c tle-bu
You are now connected to database tle-bu.
tle-bu=
So ultimately my question becomes; How can I prevent other valid
postgres database users from connecting to the 'tle-bu' database
('postgres' being the obvious exception)? Can I do this with some
combination of GRANT and/or REVOKE? If so, does 'GRANT...' restrict
access to only the user(s) mentioned once it is used or do I need to
'REVOKE...' other users first and then 'GRANT...' the 'tle-bu' user?
Or am I missing a design of postgresql (always likely. :P )?
Thanks!!
Madison
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly
Re: [GENERAL] Getting a DB password to work without editing pg_hba.conf,
Tom Lane wrote: Madison Kelly [EMAIL PROTECTED] writes: May I ask then? What *is* considered best practices for securing a database in PostgreSQL? Assuming I leave the 'pg_hba.conf' file at it's default values, is there any real point to having a password on a postgresql user account? Well, if there were a single best practice then we'd not need to offer so many options ;-). It depends a lot on your needs and environment. On a single-user machine where you're not allowing any remote connections, you might as well use trust --- I tend to run all my development installations that way. Ident can be pretty convenient too for local users (I wouldn't trust it for remote connections though). Otherwise you probably need passwords. In any case, this just applies to whether you let someone connect or not. What they can do after they've connected is a different discussion. For that you use SQL privileges (GRANT/REVOKE). regards, tom lane In this case I can't predict what a given install's postgresql will be used for (outside of my program) because it is meant for general distribution (it's a backup program). This obviously makes things a lot more complicated. :p While I developed the program that is what I did, just changed from 'ident' to 'trust'. Now though I am trying to keep what the end user needs to do to a minimum because I've aimed the backup program at more novice users (though not excluively). That is the biggest reason why I am trying to work with the stock 'pg_hba.conf' file (understanding that it can change from one distro to the next). Generally though I've only seen the same 'local...' settings. In my program the database needs to reside on the local machine so as far as I am concerned my only worry is the 'local...' settings. There may be issues with connections coming in over 'host...' connections but I want to deal with one issue at a time. :p So what purpose does the password on the user account accomplish? Is it essentially useless in my scenario? Again, thanks for your help/time! Madison PS - I read about 'GRANT/REVOKE' but I have to admit the postgres docs on the topic didn't help me much. I need something a little more junior. :p -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Madison Kelly (Digimer) TLE-BU; The Linux Experience, Back Up Main Project Page: http://tle-bu.org Community Forum:http://forum.tle-bu.org -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Getting a DB password to work without editing pg_hba.conf,
Tom Lane wrote:
Madison Kelly [EMAIL PROTECTED] writes:
Oh shoot, I really wasn't very verbose, was I? Sorry about that.
[ default pg_hba.conf with only ident lines ]
Ah, that explains your question about whether passwords were good for
anything at all. With this pg_hba.conf they aren't --- the server will
never ask for one. You'd want to replace some of the ident sameuser
entries with password (or more likely md5) if you want password
challenges instead of checks on the user's Unix login identity. See
the PG administrator docs at
http://www.postgresql.org/docs/8.1/static/client-authentication.html
(adjust version as needed)
I've played with the MD5 and I think I will write a little howto or
something similar to explain the options to a user who wants more
security but for now I will default to leaving things as-is.
So ultimately my question becomes; How can I prevent other valid
postgres database users from connecting to the 'tle-bu' database
('postgres' being the obvious exception)? Can I do this with some
combination of GRANT and/or REVOKE?
At the moment you have to do that by adjusting the pg_hba.conf entries.
One possibility is to use sameuser in the database field, eg,
# TYPE DATABASEUSERIP-ADDRESSIP-MASK METHOD
# Database administrative login by UNIX sockets
local all postgresident
sameuser
#
# All other connections by UNIX sockets
local sameuserall ident
sameuser
This will let postgres connect to anything but other users can only
connect to the database named after them. If you need more flexibility
that that, consider setting up groups named for databases and using
samegroup --- then you grant or revoke group membership to let people
into databases or not.
It'd be an obvious extension to provide a direct LOGIN privilege
on databases and grant or revoke that, but given the samegroup
workaround it's not a real high-priority feature ...
regards, tom lane
Many thanks for your help clearing that up! If I can vote for the
extension being created, consider this it. Mainly for the reasons I've
mentioned; trying to handle security programatically instead of relying
on the end-user (who may be less technically enclined) doing it. I know
that I could have my program handle the editing of the 'pg_hba.conf'
file but I don't trust myself with doing that write given that order is
important and the wide number of possible configurations.
Madison
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU; The Linux Experience, Back Up
Main Project Page: http://tle-bu.org
Community Forum:http://forum.tle-bu.org
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
