[GENERAL] pg_restore questions
I have been optimizing my pg_restores (postgres 8.2.9 on Windows) and I am confused by some of the results I get when combining various command-line options. The -c option for clean does not do DROP IF EXISTS statements, it just does DROP. This results in an error if the object does not exist. So the -c option creates the requirement that the schema must already exist. Was that intentional? This means that -c is incompatible with -1 (single transaction) unless the existing matches the schema of the database that was dumped since because it won't ignore errors if -1 is specified. Which means I lose my optimizations (a prior thread concluded that -1 is necessary for the COPY optimization during restores) The -C option for create does not work with -1 (single transaction), since it results in an error stating that CREATE DATABASE commands cannot be part of a transaction. It seems to me that the pg_restore command should know this, and create the database first, then start the transaction. Another problem with -C is that if I haven't created the database already, it gives an error that it doesn't exist. I thought that -C was supposed to create the database for me. It seems like it checks if the database exists first. Is that because I am using the -d option? (Didn't try removing that, and my restore is now running...) Maybe -d checks for the database before -C can create it? In that case, -C should have complained when it tried to create a database that was already there. Either way, I seem to have to manually create the database before running pg_restore. Example: Z:\Program Files\PostgreSQL\8.2\binpg_restore -d SpareFiles -v -C -s -U postgres z:\teb01-bck01_sprfil_091808.backup pg_restore: connecting to database for restore pg_restore: [archiver (db)] connection to database SpareFiles failed: FATAL: database SpareFiles does not exist pg_restore: *** aborted because of error I realized that I need to do the restore in two steps: one to create the schema, and another to restore the data. This will allow me to create the database from scratch, without relying on -c to drop things, then after it creates the schema I can load the data using -1 for speed. So I manually created the database, and did a pg_restore with -C -s. But when I tried to do a data-only restore with -a, it complained about the foreign key constraints: (I removed the table names and stuff since it is under NDA) Z:\Program Files\PostgreSQL\8.2\binpg_restore -d SpareFiles -v -1 -a -U postgres z:\teb01-bck01_sprfil_091808.backup pg_restore: connecting to database for restore pg_restore: executing SEQUENCE SET scrubbed_some_sequence pg_restore: executing SEQUENCE SET scrubbed_some_sequence pg_restore: executing SEQUENCE SET scrubbed_some_sequence pg_restore: executing SEQUENCE SET scrubbed_some_sequence pg_restore: executing SEQUENCE SET scrubbed_some_sequence pg_restore: restoring data for table scrubbed pg_restore: restoring data for table scrubbed pg_restore: restoring data for table scrubbed pg_restore: restoring data for table scrubbed pg_restore: restoring data for table scrubbed_final_table pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 1811; 0 16640 TABLE DATA scrubbed_final_table postgres pg_restore: [archiver (db)] COPY failed: ERROR: insert or update on table scrubbed_final_table violates foreign key constraint scrubbed_fkey_to_another_table DETAIL: Key (scrubbed_column_name)=(some_value) is not present in table scrubbed_table_name.pg_restore: *** aborted because of error This dump was done on postgres 8.2.9, and old forum posts indicate that 8.0 and beyond order data so that foreign key constraints are not a problem in data-only restores. Is this a bug? There are no circular dependencies in this database. I got around that with the --disable-triggers option. That is very useful, but it wasn't clear from the documentation that this option also affected foreign key constraints. The documentation does say referential integrity checks but I assumed that meant checks that I created through triggers, not checks done with normal foreign keys. So right now, I have my restore going on. But I thought it was a bit more difficult than it should have been. I think that: 1) The behavior of the tool could be improved so that these conflicting options won't conflict 2) If there is no way around it, the tool should state what conflicting options have been selected, and they should be documented 3) I suspect the data-only restore issue with the foreign-keys is a bug in the tool. Any thoughts on this? Thanks for reading this verbose message. Can anyone clarify any of this? - Bill -- 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] Synchronize two similar tables: recursive triggers
The INSERT and DELETE TG_OPs are straightforward (the simplest solution for these is that the existence of the primary key can be checked in the other table), however the UPDATE handler is really confusing. Is it possible for a trigger function to know where an UPDATE originated (user vs trigger)? I'm not sure how a trigger could know the first to be fired, or how many times it has passed between. Any other ideas? Thanks again. -Mike Pavel Stehule wrote: Hello ad colum that will contains info about source of value like create table a(a integer, from_trigger bool); create table b(a integer, from_trigger bool); create or replace function synchronize_handler_a() returns trigger as $$ begin if not new.from_trigger then new.from trigger := true; insert into b values(new.*); end if; return new; end; $$ language plpgsql; this is protection under resursive triggers regards Pavel Stehule 2008/9/18 Michael Toews [EMAIL PROTECTED]: Hi all, I need to have two tables that are mostly synchronized in my database, such that an edit to a row in one is made to the other, and vice versa. Normally, this is done using views with rules, however my situation does not allow editable views (http://trac.osgeo.org/fdo/ticket/346). So, I need to have two database tables. The other thing is that the two tables are not identical, as I need to omit columns with advanced data types in one of the tables (another bug: http://trac.osgeo.org/fdo/ticket/394). The two tables also need to be isolated in different schemata. Here are some example tables: CREATE SCHEMA prim; CREATE SCHEMA second; CREATE TABLE prim.mytable ( id integer, fname character varying, num real, timestmp timestamp with time zone, -- not in second.mytable CONSTRAINT mytable_pkey PRIMARY KEY (id) ) WITH (OIDS=FALSE); CREATE TABLE second.mytable ( id integer, fname character varying, num real, CONSTRAINT mytable_pkey PRIMARY KEY (id) ) WITH (OIDS=FALSE); To synchronized the two tables, I plan to use a trigger function to handle INSERT, UPDATE and DELETE events, using TG_OP and TG_TABLE_SCHEMA. (If there are better solutions that don't use triggers, stop me here and fill me in). What I'm having difficulty designing is how to deal with recursive triggers, since I require two-way communication. For example: 1. change on prim.mytable fires trigger to sync change on second.mytable 2. change from (1) on second.mytable fires trigger to sync change on prim.mytable 3. change from (2) on prim.mytable fires trigger ... etc. This behaviour is mentioned in the documentation: http://www.postgresql.org/docs/8.3/interactive/trigger-definition.html (search for recurs) however, it doesn't offer an example nor solution. Some possible solutions may involve using trigger functions with parameters (I'm yet to see an example of this), or disable the second trigger from the first trigger while updating the other table, etc. Perhaps there is a global variable somewhere that could indicate the level of recursion. Or, possibly, a version column could be kept in each column, which is incremented on the first trigger fire, and returns NULL if OLD.version=NEW.version. Any suggestions or references to other examples would be much appreciated. Thanks in advance. -Mike -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how to return the first record from the sorted records which may have duplicated value.
hi all: I have a table with columns(2) named query, pop, dfk. what I want is: when I do some select, if the column query in result records have duplicate value, I only want the record which have the maximum value of the pop. for example, the content of table: query pop dfk --- abc30 1 --max foo 20 lk --max def 16 kj --max foo 15 fk --discard abc 10 2 --discard bar 8are --max the result should be: query pop dfk --- abc30 1 foo 20 lk def 16 kj bar 8are now, I do it like this(plpgsql) declare hq := ''::hstore; begin for rc in execute 'select * from test order by pop desc' loop if not defined(hq, rc.query) then hq := hq || (rc.query = '1')::hstore; return next rc; end if; end loop; --- language sql/plpgsql will be ok. ps: I try to use group by or max function, because of the multi-columns(more than 2), I failed. thanks, any answer is appreciated. regards, -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_restore questions
William Garrison wrote: I have been optimizing my pg_restores (postgres 8.2.9 on Windows) and I am confused by some of the results I get when combining various command-line options. The -c option for clean does not do DROP IF EXISTS statements, it just does DROP. This results in an error if the object does not exist. So the -c option creates the requirement that the schema must already exist. Was that intentional? This means that -c is incompatible with -1 (single transaction) unless the existing matches the schema of the database that was dumped since because it won't ignore errors if -1 is specified. Which means I lose my optimizations (a prior thread concluded that -1 is necessary for the COPY optimization during restores) Never tried combining the two. In fact, I'm not sure I've ever used the clean option in anger. You could check if that's still the case in 8.3 and if so either (a) raise a bug report or (b) raise a bug report and supply a patch :-) The -C option for create does not work with -1 (single transaction), since it results in an error stating that CREATE DATABASE commands cannot be part of a transaction. It seems to me that the pg_restore command should know this, and create the database first, then start the transaction. That's reasonable. Another problem with -C is that if I haven't created the database already, it gives an error that it doesn't exist. I thought that -C was supposed to create the database for me. It seems like it checks if the database exists first. Is that because I am using the -d option? (Didn't try removing that, and my restore is now running...) Maybe -d checks for the database before -C can create it? In that case, -C should have complained when it tried to create a database that was already there. Either way, I seem to have to manually create the database before running pg_restore. The -d option is the database to connect to, so if you're using -C too you need to do something like: pg_restore -U postgres -d existing_db -C new_db It is covered in the manuals, but there are a lot of options, so it would be easy to miss. I realized that I need to do the restore in two steps: one to create the schema, and another to restore the data. This will allow me to create the database from scratch, without relying on -c to drop things, then after it creates the schema I can load the data using -1 for speed. So I manually created the database, and did a pg_restore with -C -s. But when I tried to do a data-only restore with -a, it complained about the foreign key constraints: (I removed the table names and stuff since it is under NDA) You're better off just creating an empty db and doing the schema+data at the same time. Doing the schema first will mean all your indexes, triggers etc. are in place. Doing both together lets it build indexes in one go after the data is in place. Try the combination of -d/-C as described above, I think that's what you're after. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to return the first record from the sorted records which may have duplicated value.
Yi Zhao [EMAIL PROTECTED] schrieb: hi all: I have a table with columns(2) named query, pop, dfk. what I want is: when I do some select, if the column query in result records have duplicate value, I only want the record which have the maximum value of the pop. for example, the content of table: query pop dfk --- abc30 1 --max foo 20 lk --max def 16 kj --max foo 15 fk --discard abc 10 2 --discard bar 8are --max the result should be: query pop dfk --- abc30 1 foo 20 lk def 16 kj bar 8are test=*# select * from d; query | pop | dfk ---+-+- abc | 30 | 1 foo | 20 | lk def | 16 | kj foo | 15 | fk abc | 10 | 2 bar | 8 | are (6 Zeilen) Zeit: 0,213 ms test=*# select distinct on (query) * from d order by query, pop desc; query | pop | dfk ---+-+- abc | 30 | 1 bar | 8 | are def | 16 | kj foo | 20 | lk (4 Zeilen) Hint: distinct on isn't standard-sql, it's an PG-extension. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to return the first record from the sorted records which may have duplicated value.
now, I do it like this(plpgsql) --- this methold have low efficiency, when the records is large, it will become slow, so someone can tell me some high efficiency way??? thanks. On Fri, 2008-09-19 at 16:51 +0800, Yi Zhao wrote: hi all: I have a table with columns(2) named query, pop, dfk. what I want is: when I do some select, if the column query in result records have duplicate value, I only want the record which have the maximum value of the pop. for example, the content of table: query pop dfk --- abc30 1 --max foo 20 lk --max def 16 kj --max foo 15 fk --discard abc 10 2 --discard bar 8are --max the result should be: query pop dfk --- abc30 1 foo 20 lk def 16 kj bar 8are now, I do it like this(plpgsql) declare hq := ''::hstore; begin for rc in execute 'select * from test order by pop desc' loop if not defined(hq, rc.query) then hq := hq || (rc.query = '1')::hstore; return next rc; end if; end loop; --- language sql/plpgsql will be ok. ps: I try to use group by or max function, because of the multi-columns(more than 2), I failed. thanks, any answer is appreciated. regards,
[GENERAL] Stop trigger fireing for a specific user?
I've a trigger written in C and I want to stop it fireing for a specific user. Is ther any way to do this outside of the C code in the trigger function? The only way I can think so far is to do a select SESSION_USER in the trigger function and skip the action if it matched my user. -- 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] Synchronize two similar tables: recursive triggers
hello 2008/9/19 Michael Toews [EMAIL PROTECTED]: The INSERT and DELETE TG_OPs are straightforward (the simplest solution for these is that the existence of the primary key can be checked in the other table), however the UPDATE handler is really confusing. Is it possible for a trigger function to know where an UPDATE originated (user vs trigger)? I'm not sure how a trigger could know the first to be fired, or how many times it has passed between. Any other ideas? Thanks again. in 8.3 you should to analyze pg_stat_activity Pavel Stehule -Mike Pavel Stehule wrote: Hello ad colum that will contains info about source of value like create table a(a integer, from_trigger bool); create table b(a integer, from_trigger bool); create or replace function synchronize_handler_a() returns trigger as $$ begin if not new.from_trigger then new.from trigger := true; insert into b values(new.*); end if; return new; end; $$ language plpgsql; this is protection under resursive triggers regards Pavel Stehule 2008/9/18 Michael Toews [EMAIL PROTECTED]: Hi all, I need to have two tables that are mostly synchronized in my database, such that an edit to a row in one is made to the other, and vice versa. Normally, this is done using views with rules, however my situation does not allow editable views (http://trac.osgeo.org/fdo/ticket/346). So, I need to have two database tables. The other thing is that the two tables are not identical, as I need to omit columns with advanced data types in one of the tables (another bug: http://trac.osgeo.org/fdo/ticket/394). The two tables also need to be isolated in different schemata. Here are some example tables: CREATE SCHEMA prim; CREATE SCHEMA second; CREATE TABLE prim.mytable ( id integer, fname character varying, num real, timestmp timestamp with time zone, -- not in second.mytable CONSTRAINT mytable_pkey PRIMARY KEY (id) ) WITH (OIDS=FALSE); CREATE TABLE second.mytable ( id integer, fname character varying, num real, CONSTRAINT mytable_pkey PRIMARY KEY (id) ) WITH (OIDS=FALSE); To synchronized the two tables, I plan to use a trigger function to handle INSERT, UPDATE and DELETE events, using TG_OP and TG_TABLE_SCHEMA. (If there are better solutions that don't use triggers, stop me here and fill me in). What I'm having difficulty designing is how to deal with recursive triggers, since I require two-way communication. For example: 1. change on prim.mytable fires trigger to sync change on second.mytable 2. change from (1) on second.mytable fires trigger to sync change on prim.mytable 3. change from (2) on prim.mytable fires trigger ... etc. This behaviour is mentioned in the documentation: http://www.postgresql.org/docs/8.3/interactive/trigger-definition.html (search for recurs) however, it doesn't offer an example nor solution. Some possible solutions may involve using trigger functions with parameters (I'm yet to see an example of this), or disable the second trigger from the first trigger while updating the other table, etc. Perhaps there is a global variable somewhere that could indicate the level of recursion. Or, possibly, a version column could be kept in each column, which is incremented on the first trigger fire, and returns NULL if OLD.version=NEW.version. Any suggestions or references to other examples would be much appreciated. Thanks in advance. -Mike -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Query planner issue
Hi there, I am currently running Postgres 8.3.1. I've got a table called DETAILS, the primary key is : DE_ID char(12), there is another field CO_ID char (12). DE_ID and CO_ID are indexed with a btree. This table is about 140 millions of records. If I execute an explain select * from details where co_id = '010076015372'; it uses the index. Here follows the plan: Index Scan using idx_co_id on details (cost=0.00..34.37 rows=2 width=741) Index Cond: ((co_id)::bpchar = '010076015372'::bpchar) If I run explain analyze select * from details where co_id || co_id = '0100760153722324445'; it runs a sequential scan not using the index (which takes about 100 times than using the index): Seq Scan on details (cost=0.00..8755139.52 rows=819131 width=741) Filter: (((co_id)::text || (co_id)::text) = '010076015372010076015372'::text) Same thing if I try to trick it using a view or a stored procedure. Query planner is not easy to trick! My question is : is there a way to use the index or isn't it possible? Thanks in advance. Andrea Moretto Andrea Moretto [EMAIL PROTECTED] --- CONFIDENTIALITY NOTICE This message and its attachments are addressed solely to the persons above and may contain confidential information. If you have received the message in error, be informed that any use of the content hereof is prohibited. Please return it immediately to the sender and delete the message. --- -- 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] Query planner issue
Andrea Moretto wrote: I've got a table called DETAILS, the primary key is : DE_ID char(12), there is another field CO_ID char (12). If I run explain analyze select * from details where co_id || co_id = '0100760153722324445'; it runs a sequential scan not using the index (which takes about 100 times than using the index): That query can never return any rows. You're appending co_id to itself then testing against a non-repeating string. Furthermore it's 19 chars long rather than 24. Is this really what you're trying to do? Read up on indexes on expressions in chapter 11 of the manuals, for indexing this sort of thing. I don't think it will help you here though. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Stop trigger fireing for a specific user?
Glyn Astill wrote: I've a trigger written in C and I want to stop it fireing for a specific user. Is ther any way to do this outside of the C code in the trigger function? The only way I can think so far is to do a select SESSION_USER in the trigger function and skip the action if it matched my user. That's exactly it. I'm not sure it will ever be a built-in feature, everyone's requirements will be slightly different. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query planner issue
Dear Richard, you are right. So Postgres takes a lot of time to get out an impossible result! The query I wrote is just a test, the concatenation is only a dummy. On 19/set/08, at 12:33, Richard Huxton wrote: Andrea Moretto wrote: I've got a table called DETAILS, the primary key is : DE_ID char(12), there is another field CO_ID char (12). If I run explain analyze select * from details where co_id || co_id = '0100760153722324445'; it runs a sequential scan not using the index (which takes about 100 times than using the index): That query can never return any rows. You're appending co_id to itself then testing against a non-repeating string. Furthermore it's 19 chars long rather than 24. Is this really what you're trying to do? Read up on indexes on expressions in chapter 11 of the manuals, for indexing this sort of thing. I don't think it will help you here though. -- Richard Huxton Archonet Ltd Andrea Moretto [EMAIL PROTECTED] --- CONFIDENTIALITY NOTICE This message and its attachments are addressed solely to the persons above and may contain confidential information. If you have received the message in error, be informed that any use of the content hereof is prohibited. Please return it immediately to the sender and delete the message. --- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Autovacuum, too often?
Hi chaps, Our legacy apps have some permanent tables that they use for tempory data and constantly clear out, I've kicked the developers and I intend to eradicate them eventually (the tables, not the developers). These tables are constantly being autovacuumed, approximately once a minute, it's not causing any problem and seems to be keeping them vacuumed. But I'm constantly re-assessing our autovacuum settings to make sure they're adequate, and no matter how much I read up on autovacuum I still feel like I'm missing something. I just wondered what peoples opinions were on handling this sort of vacuuming? Is that too often? The general autovaccum settings set more for our central tables are threshold 500, scale_factor 0.2. I guess I could set specific settings for the tables in pg_autovacuum, or I could exclude them in there and run a vacuum from cron once a day or something. Here's a typical log message: 2008-09-19 11:40:10 BST [12917]: [1-1]: [user=]: [host=]: [db=]:: LOG: automatic vacuum of table TEMP.reports.online: index scans: 1 pages: 21 removed, 26 remain tuples: 2356 removed, 171 remain system usage: CPU 0.00s/0.00u sec elapsed 0.08 sec Any comments would be appreciated. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Stop trigger fireing for a specific user?
From: Richard Huxton [EMAIL PROTECTED] The only way I can think so far is to do a select SESSION_USER in the trigger function and skip the action if it matched my user. That's exactly it. I'm not sure it will ever be a built-in feature, everyone's requirements will be slightly different. Cool, thanks Richard -- 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] Autovacuum, too often?
Hello Glyn, Our legacy apps have some permanent tables that they use for tempory data and constantly clear out, I've kicked the developers and I intend to eradicate them eventually (the tables, not the developers). and what is the problem with this usage? That is a perfectly valid thing to do; PostgreSQL can handle that for centuries; no need to kick the developers :) These tables are constantly being autovacuumed, approximately once a minute, it's not causing any problem and seems to be keeping them vacuumed. That is the right thing to do. pages: 21 removed, 26 remain tuples: 2356 removed, 171 remain system usage: CPU 0.00s/0.00u sec elapsed 0.08 sec As you described, that temp-tables get filled and cleared regularly ... that is insert a lot of stuff delete the same stuff again; so there are lots of unused i.e. deleted tuples, which get recycled by your vacuuming. And that with nearly no CPU usage. Sounds fine to me :) Best wishes, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - EuroPython 2009 will take place in Birmingham - Stay tuned!
Re: [GENERAL] Autovacuum, too often?
From: Harald Armin Massa [EMAIL PROTECTED] Hello Glyn, Our legacy apps have some permanent tables that they use for tempory data and constantly clear out, I've kicked the developers and I intend to eradicate them eventually (the tables, not the developers). and what is the problem with this usage? That is a perfectly valid thing to do; PostgreSQL can handle that for centuries; no need to kick the developers :) In some cases yes, but most of the time it's because they can't be bothered to sort a list of 100 items in their application... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to change log file language?
Hi all, I installed 8.3.3 on an english WinXP. The database cluster was initialized with server encoding UTF8 and the locale was set to 'German, Germany'. Now all messages in the log and everywhere else are showing up in German (as expected). However I want to see those messages in English. I tried to alter lc_messages in the postgresql.conf file ( '', 'C' and 'English_United States'), but this seems to have no effect (yes, I restarted the server). Searching the archives, I found someone with a similar problem: http://archives.postgresql.org/pgsql-bugs/2008-05/msg00106.php Could anybody tell me what I am doing wrong? Rainer -- 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] Autovacuum, too often?
Glyn Astill [EMAIL PROTECTED] wrote: From: Harald Armin Massa [EMAIL PROTECTED] Hello Glyn, Our legacy apps have some permanent tables that they use for tempory data and constantly clear out, I've kicked the developers and I intend to eradicate them eventually (the tables, not the developers). and what is the problem with this usage? That is a perfectly valid thing to do; PostgreSQL can handle that for centuries; no need to kick the developers :) In some cases yes, but most of the time it's because they can't be bothered to sort a list of 100 items in their application... *shrug* Our experience has been that PostgreSQL is much better at sorting than anything we could write with our high-pressure deadlines. Additionally, information sometimes needs to be truncated (with LIMIT) after it's sorted, so having PG do all the work results in less network bandwidth and less memory usage by the application. Maybe that's not _always_ the right answer, but it seems to be a good answer 99% of the time. Sounds like your developers are using the database for what it was intended for, instead of just doing single row selects like a lot of amateurs I've come across. -- Bill Moran Collaborative Fusion Inc. [EMAIL PROTECTED] Phone: 412-422-3463x4023 -- 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] Autovacuum, too often?
From: Bill Moran [EMAIL PROTECTED] Maybe that's not _always_ the right answer, but it seems to be a good answer 99% of the time. Sounds like your developers are using the database for what it was intended for, instead of just doing single row selects like a lot of amateurs I've come across. In some places I agree it's totally valid, but in a lot of cases here it's just unnecessary. They have a set of really flexible pre written routines to sort datasets in the application, and I'm talking really stupid use here - like having a small list of items in an array where an array sort could be done, but instead writing it all back and reading it again. constantly. -- 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] Query planner issue
As Richard already pointed out the query issues, I'll point out the low-hanging fruit. At 6:17am -0400 on Fri, 19 Sep 2008, Andrea Moretto wrote: I am currently running Postgres 8.3.1. ^ The current minor release is 8.3.3 and 8.3.4 is expected Monday. Upgrade. You gain absolutely nothing staying at an out-of-date minor revision. Kevin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] setting Postgres client
My message didn't get through the 1st time. -Original Message- From: Markova, Nina Sent: September 17, 2008 17:33 To: pgsql-general@postgresql.org Subject: Help on setting Postgres client I have setup a Postgres server (8.2.4) on Solaris 10, for now in the global zone, with FS : /pg_db /pg_log /pg_data Now I need to set up a client in a non-global zone on the same machine or on anoter machine. Not much luck so far. What I did: === - on the server in $PGDATA/pg_hub.conf I added a line for the client hostall all 192.XXX.XX.XXXtrust - On the client I tried: psql sta psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? In my understanding: - the data and the log file should exist on the server only. - I should have postgres configuration file somewhere on the client to at least specify Postgres server ip (PGHOSTTADDR and/or PGHOST), or this should be set different way? Questions: == - where to find steps for setting up postgres client? - what directories and files should I have on the client side? - should I have local log file on the client? - how to start postgres on the client - should I specify $PGDATA? - Should $PGDATA be shared, i.e. mounted by the client, or I have choice not to? I actually prefer not to be mounted. - I read that for the client side I should have only 2 of the packages - SUNWpostgr-libs and SUNWpostgr. All 24 packges for postgres are there - should I remove the rest? - where to specify PGHOSTTADDR and/or PGHOST? Thanks in advance, Nina Any practical advices -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] is not distinct from any(...)
I'm trying to write a SQL statement to determine whether a value is an an array, but I want the comparison to be done using IS NOT DISTINCT FROM rather than =. My first thought was that instead of writing: SELECT value = ANY(array) ...I could simply write: SELECT value IS NOT DISTINCT FROM ANY(array) That doesn't seem to work, because IS NOT DISTINCT FROM is not an operator. So then I tried creating an operator === (anyelement, anyelement) that just does IS NOT DISTINCT FROM and writing: select 1 === any(array[1]); which got me: ERROR: could not find array type for data type anyelement Grr... any suggestions? ...Robert -- 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] Query planner issue
On Fri, Sep 19, 2008 at 4:17 AM, Andrea Moretto [EMAIL PROTECTED] wrote: Hi there, I am currently running Postgres 8.3.1. I've got a table called DETAILS, the primary key is : DE_ID char(12), there is another field CO_ID char (12). DE_ID and CO_ID are indexed with a btree. This table is about 140 millions of records. If I execute an explain select * from details where co_id = '010076015372'; it uses the index. Here follows the plan: Index Scan using idx_co_id on details (cost=0.00..34.37 rows=2 width=741) Index Cond: ((co_id)::bpchar = '010076015372'::bpchar) If I run explain analyze select * from details where co_id || co_id = '0100760153722324445'; it runs a sequential scan not using the index (which takes about 100 times than using the index): So, let's assume you have a real use case not this test on, like select * from db where field1||field2 = 'abc123'; where field1=abc and field2=123 (and they're all text). create index myinsaneindex on table ((field1||field2)); now if you use a where clause like the above you should be able to get an index scan. Look up functional and partial indexes. PostgreSQL isn't real good at getting you out of your own created bad situations, but it is very good at providing you with the tools to do it yourself. :) -- 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] setting Postgres client
Markova, Nina [EMAIL PROTECTED] wrote: - On the client I tried: psql sta psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? The client tries to connect to the unix domain socket by default (as can be seen by the error message) If your client system and server don't share the same /tmp filesystem, then that is going to fail, which is expected. Either specify the IP address/hostname you want to connect to with -h or put options in your .psqlrc file or set appropriate environment variables. More details here: http://www.postgresql.org/docs/8.2/static/app-psql.html -- Bill Moran Collaborative Fusion Inc. [EMAIL PROTECTED] Phone: 412-422-3463x4023 -- 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] setting Postgres client
Thanks Richard. I specified the host IP ( I use the default 5432 port), got error: psql: could not connect to server: Connection refused Is the server running on host 192.168.XX.XXX and accepting TCP/IP connections on port 5432? The only tcp lines in my postgres.conf are #tcp_keepalives_idle = 0# TCP_KEEPIDLE, in seconds; # 0 selects the system default #tcp_keepalives_interval = 0# TCP_KEEPINTVL, in seconds; # 0 selects the system default #tcp_keepalives_count = 0 # TCP_KEEPCNT; # 0 selects the system default Should I change something here? Nina -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: September 19, 2008 10:15 To: Markova, Nina Subject: Re: [GENERAL] setting Postgres client Markova, Nina wrote: Now I need to set up a client in a non-global zone on the same machine or on anoter machine. Not much luck so far. What I did: === - on the server in $PGDATA/pg_hub.conf I added a line for the client hostall all 192.XXX.XX.XXXtrust - On the client I tried: psql sta psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? In my understanding: - the data and the log file should exist on the server only. - I should have postgres configuration file somewhere on the client to at least specify Postgres server ip (PGHOSTTADDR and/or PGHOST), or this should be set different way? You can provide a hostname on the command-line too psql -h 192.168.1.2 , in an environment variable or in a connection service file. Without that, it defaults to trying to connect by unix domain sockets (on unix) which I'm guessing doesn't work because your server is in a different zone. Try an explicit IP address, as above. Questions: == - where to find steps for setting up postgres client? If it runs, it's set up. - what directories and files should I have on the client side? none - should I have local log file on the client? No. Well, you get a .psql_history file if you have readline enabled. - how to start postgres on the client - should I specify $PGDATA? No - Should $PGDATA be shared, i.e. mounted by the client, or I have choice not to? I actually prefer not to be mounted. No - I read that for the client side I should have only 2 of the packages - SUNWpostgr-libs and SUNWpostgr. All 24 packges for postgres are there - should I remove the rest? Can't help sorry. - where to specify PGHOSTTADDR and/or PGHOST? You can do it in your shell profile on a per-user basis, or for the whole machine. I tend to do it no the command-line or in an shell alias myself though. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] is not distinct from any(...)
Robert Haas [EMAIL PROTECTED] writes: That doesn't seem to work, because IS NOT DISTINCT FROM is not an operator. Yah :-( So then I tried creating an operator === (anyelement, anyelement) that just does IS NOT DISTINCT FROM and writing: select 1 === any(array[1]); which got me: ERROR: could not find array type for data type anyelement FWIW, it seems to work in 8.3. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] setting Postgres client
Markova, Nina wrote: Thanks Richard. I specified the host IP ( I use the default 5432 port), got error: psql: could not connect to server: Connection refused Is the server running on host 192.168.XX.XXX and accepting TCP/IP connections on port 5432? The only tcp lines in my postgres.conf are #tcp_keepalives_idle = 0# TCP_KEEPIDLE, in seconds; # 0 selects the system default #tcp_keepalives_interval = 0# TCP_KEEPINTVL, in seconds; # 0 selects the system default #tcp_keepalives_count = 0 # TCP_KEEPCNT; # 0 selects the system default Should I change something here? Nina -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: September 19, 2008 10:15 To: Markova, Nina Subject: Re: [GENERAL] setting Postgres client Markova, Nina wrote: Now I need to set up a client in a non-global zone on the same machine or on anoter machine. Not much luck so far. What I did: === - on the server in $PGDATA/pg_hub.conf I added a line for the client hostall all 192.XXX.XX.XXXtrust - On the client I tried: psql sta psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? In my understanding: - the data and the log file should exist on the server only. - I should have postgres configuration file somewhere on the client to at least specify Postgres server ip (PGHOSTTADDR and/or PGHOST), or this should be set different way? You can provide a hostname on the command-line too psql -h 192.168.1.2 , in an environment variable or in a connection service file. Without that, it defaults to trying to connect by unix domain sockets (on unix) which I'm guessing doesn't work because your server is in a different zone. Try an explicit IP address, as above. Questions: == - where to find steps for setting up postgres client? If it runs, it's set up. - what directories and files should I have on the client side? none - should I have local log file on the client? No. Well, you get a .psql_history file if you have readline enabled. - how to start postgres on the client - should I specify $PGDATA? No - Should $PGDATA be shared, i.e. mounted by the client, or I have choice not to? I actually prefer not to be mounted. No - I read that for the client side I should have only 2 of the packages - SUNWpostgr-libs and SUNWpostgr. All 24 packges for postgres are there - should I remove the rest? Can't help sorry. - where to specify PGHOSTTADDR and/or PGHOST? You can do it in your shell profile on a per-user basis, or for the whole machine. I tend to do it no the command-line or in an shell alias myself though. -- Richard Huxton Archonport = 5432 et Ltd port = 5432 -- 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] setting Postgres client
Markova, Nina wrote: Thanks Richard. I specified the host IP ( I use the default 5432 port), got error: psql: could not connect to server: Connection refused Is the server running on host 192.168.XX.XXX and accepting TCP/IP connections on port 5432? The only tcp lines in my postgres.conf are #tcp_keepalives_idle = 0# TCP_KEEPIDLE, in seconds; # 0 selects the system default #tcp_keepalives_interval = 0# TCP_KEEPINTVL, in seconds; # 0 selects the system default #tcp_keepalives_count = 0 # TCP_KEEPCNT; # 0 selects the system default Should I change something here? Check listen_addresses and port look OK. You're probably only listening to localhost. You can test by telnet-ing to port 5432 or using lsof / netstat to see what connections you have open in that zone. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to return the first record from the sorted records which may have duplicated value.
--- On Fri, 9/19/08, Yi Zhao [EMAIL PROTECTED] wrote: From: Yi Zhao [EMAIL PROTECTED] Subject: [GENERAL] how to return the first record from the sorted records which may have duplicated value. To: pgsql-general pgsql-general@postgresql.org Date: Friday, September 19, 2008, 8:51 AM hi all: I have a table with columns(2) named query, pop, dfk. what I want is: when I do some select, if the column query in result records have duplicate value, I only want the record which have the maximum value of the pop. for example, the content of table: query pop dfk --- abc30 1 --max foo 20 lk --max def 16 kj --max foo 15 fk --discard abc 10 2 --discard bar 8are --max the result should be: query pop dfk --- abc30 1 foo 20 lk def 16 kj bar 8are now, I do it like this(plpgsql) declare hq := ''::hstore; begin for rc in execute 'select * from test order by pop desc' loop if not defined(hq, rc.query) then hq := hq || (rc.query = '1')::hstore; return next rc; end if; end loop; --- language sql/plpgsql will be ok. ps: I try to use group by or max function, because of the multi-columns(more than 2), I failed. thanks, any answer is appreciated. regards, this query work for me select distinct max(pop),query from test group by query please reply your results thanks... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to change log file language?
On Friday 19. September 2008, Rainer Bauer wrote: I installed 8.3.3 on an english WinXP. The database cluster was initialized with server encoding UTF8 and the locale was set to 'German, Germany'. Now all messages in the log and everywhere else are showing up in German (as expected). However I want to see those messages in English. I tried to alter lc_messages in the postgresql.conf file ( '', 'C' and 'English_United States'), but this seems to have no effect (yes, I restarted the server). I don't know how this is handled in Windows, but on a Linux computer you can enter the directory /usr/local/share/locale/de/LC_MESSAGES/ and just rename or delete the file psql.mo. I fixed the issue permanently on my Gentoo system by disabling nls support for PostgreSQL. I hate localized messages. They are distracting, hard to figure out, or even downright silly, and you can't do efficient searches on Google in problem situations. -- Leif Biberg Kristensen | Registered Linux User #338009 Me And My Database: http://solumslekt.org/blog/ -- 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] setting Postgres client
YES! Done - my listen addresses was the default. Thanks Richard! Nina -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: September 19, 2008 11:57 To: Markova, Nina Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] setting Postgres client Markova, Nina wrote: Thanks Richard. I specified the host IP ( I use the default 5432 port), got error: psql: could not connect to server: Connection refused Is the server running on host 192.168.XX.XXX and accepting TCP/IP connections on port 5432? The only tcp lines in my postgres.conf are #tcp_keepalives_idle = 0# TCP_KEEPIDLE, in seconds; # 0 selects the system default #tcp_keepalives_interval = 0# TCP_KEEPINTVL, in seconds; # 0 selects the system default #tcp_keepalives_count = 0 # TCP_KEEPCNT; # 0 selects the system default Should I change something here? Check listen_addresses and port look OK. You're probably only listening to localhost. You can test by telnet-ing to port 5432 or using lsof / netstat to see what connections you have open in that zone. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to change log file language?
Leif B. Kristensen wrote: I don't know how this is handled in Windows, but on a Linux computer you can enter the directory /usr/local/share/locale/de/LC_MESSAGES/ and just rename or delete the file psql.mo. Thanks for the tipp: After renaming folder C:\Program Files\PostgreSQL\8.3\share\locale\de to de_ I have the english texts. But I cannot imagine that the language cannot be altered after the cluster was initialized. Any other suggestions? Rainer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
