Re: [GENERAL] noobie question
On Thu, Jan 24, 2013 at 14:03:33 -0500, Steve Clark wrote: It is really called rule_num and relates to "in what order firewall rules are applied". And it used to allow the user to place the firewall rules where they want them in relation to other rules. If you just need ordering, you could choose to use a string or numeric to give you ordering. That allows you to insert values in between existing records without having to renumber. When displaying the data the application can number them based on ordering. And keep track of the current mapping between the number on the screen and the key in the database. -- 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] seeking SQL book recommendation
On Wed, Jan 23, 2013 at 15:56:10 -0700, Scott Ribe wrote: For a client who needs to learn how to query the db: - No SQL knowledge at all; needs to start from square 1. - Smart, capable person, who will be in this position for a long time, using this db for a long time. - No chance in hell this db will be moved off PG, so PG-centric is fine ;-) I found the postgresql documentation very useful for learning SQL. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql error
On 01/23/2013 04:41 PM, MarkB wrote: I have written a program where 2 computers are connected to the same database. The first PC executes an update statement and then sends a notification. This makes the second PC execute a select statement on the same table. The second PC then gets an error: 'Field "Fieldname" not found' Is the above the exact error message? If not what is it? Which part of the software chain is reporting the error? By any chance are you dealing with case sensitivity? In other words are the fieldnames mixed case or upper case? I have no trouble executing this same select statement in other places in the code or from within PgAdmin. I wonder how this is possible as an update statement does not change anything to the tablefields. I added a delay (sleep) in the code and then the problem does not occur, but this is hardly a solution and makes me question the stability of other code as long as I am not certain why this occurs. I am fairly certain this has to do with locking. I assumed that the database would block the select statement while the table fields are not available. Generally speaking Postgres does not block reads. My hope is that someone can help me as I have searched for a solution for quite some time, but am no closer to finding it. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql error
MarkB wrote: > I have written a program where 2 computers are connected to the same > database. The first PC executes an update statement and then sends a > notification. This makes the second PC execute a select statement on the > same table. The second PC then gets an error: > > 'Field "Fieldname" not found' > > I have no trouble executing this same select statement in other places in > the code or from within PgAdmin. > > I wonder how this is possible as an update statement does not change > anything to the tablefields. I added a delay (sleep) in the code and then > the problem does not occur, but this is hardly a solution and makes me > question the stability of other code as long as I am not certain why this > occurs. You need to be more specific. Look in the PostgreSQL server log and copy/paste the exact message, with any related detail, context, hints, etc., into you next post here. Also please connect with psql and show the output from running \d for each table used by the query. -Kevin -- 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] SELinux users - Please consider testing SELinux/SEPostgreSQL patches
On 01/21/2013 03:47 PM, Craig Ringer wrote: > Hi all > > Anybody here who has particular interest in or skill with SELinux is > invited (begged?) to help test KaiGai Kohei's patches for enhancing > PostgreSQL's SELinux/SEPostgreSQL support. These changes are proposed > for 9.3, but have had relatively little interest from patch reviewers > and are in danger of slipping to a later release without somebody > interested in the area stepping up. There's been no response or review for the SEPostgreSQL patches, and they're now looking likely to slip until after 9.3. If you care about SELinux support in PostgreSQL, raise your hand. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
[GENERAL] Postgresql error
I have written a program where 2 computers are connected to the same database. The first PC executes an update statement and then sends a notification. This makes the second PC execute a select statement on the same table. The second PC then gets an error: 'Field "Fieldname" not found' I have no trouble executing this same select statement in other places in the code or from within PgAdmin. I wonder how this is possible as an update statement does not change anything to the tablefields. I added a delay (sleep) in the code and then the problem does not occur, but this is hardly a solution and makes me question the stability of other code as long as I am not certain why this occurs. I am fairly certain this has to do with locking. I assumed that the database would block the select statement while the table fields are not available. My hope is that someone can help me as I have searched for a solution for quite some time, but am no closer to finding it. Thanks! The program is written using Delphi 7 and PostgresDac 2.8.0 where I use the TPSQLQuery to execute the select and update statements and TPSQLNotify for the notification. My Postgresql version is 9.1. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Postgresql-error-tp5741971.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] main.log file not being updated
I though to do that w/ log_destination, but i left everything pretty much default except those params I mentioned earlier. Interestingly i have another DB server (same ver, etc...) w/ exact same config params w/ postgres and sys log config and that is writing to the "system postgres log". it's a head scratcher for me. On Thu, Jan 24, 2013 at 4:08 PM, Adrian Klaver wrote: > On 01/24/2013 12:44 PM, Anson Abraham wrote: > >> i've always had logging_collector off. it would still log query / >> transactions that failed to the "syslog" >> (var/log/postgres/postgres-9.**0-main.log). >> the only thing I've changed was turning log_duration back to off. But >> I've gone back and forth with what, and doing a postgres reload. >> >> > CCing list. > > Then shouldn't > > log_destination = 'stderr' > > be > > log_destination = 'syslog' > > You may also want to check that the syslog configuration has not changed > and it is actually configured to catch the Postgres output. > > Would not hurt to check the init.d script to see if it is overriding the > logging settings. > > > Adrian Klaver > adrian.kla...@gmail.com >
Re: [GENERAL] main.log file not being updated
On 01/24/2013 01:38 PM, Anson Abraham wrote: I though to do that w/ log_destination, but i left everything pretty much default except those params I mentioned earlier. Interestingly i have another DB server (same ver, etc...) w/ exact same config params w/ postgres and sys log config and that is writing to the "system postgres log". it's a head scratcher for me. Well something is not the same. Did you check the start up script and the syslog configuration? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] main.log file not being updated
On 01/24/2013 12:44 PM, Anson Abraham wrote: i've always had logging_collector off. it would still log query / transactions that failed to the "syslog" (var/log/postgres/postgres-9.0-main.log). the only thing I've changed was turning log_duration back to off. But I've gone back and forth with what, and doing a postgres reload. CCing list. Then shouldn't log_destination = 'stderr' be log_destination = 'syslog' You may also want to check that the syslog configuration has not changed and it is actually configured to catch the Postgres output. Would not hurt to check the init.d script to see if it is overriding the logging settings. Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Optimizing select count query which often takes over 10 seconds
On Thu, 2013-01-24 at 15:45 +0100, Alexander Farber wrote: > Hello - > > On Thu, Jan 24, 2013 at 3:39 PM, Alban Hertroys wrote: > > On 24 January 2013 10:57, Alexander Farber > > wrote: > >> > >> # explain analyze select count(id) from ( > >> select id, > >>row_number() over(partition by yw order by money > >> desc) as ranking > >> from pref_money > >> ) x > >> where x.ranking = 1 and id='OK452217781481'; > >> QUERY PLAN > >> > >> --- > >> Aggregate (cost=63694.22..63694.23 rows=1 width=82) (actual > >> time=4520.719..4520.719 rows=1 loops=1) > >>-> Subquery Scan x (cost=48519.10..63694.19 rows=11 width=82) > >> (actual time=4470.620..4520.710 rows=6 loops=1) > >> Filter: ((x.ranking = 1) AND ((x.id)::text = > >> 'OK452217781481'::text)) > >> -> WindowAgg (cost=48519.10..57190.58 rows=433574 width=26) > >> (actual time=4293.315..4491.652 rows=429803 loops=1) > >>-> Sort (cost=48519.10..49603.03 rows=433574 > >> width=26) (actual time=4293.306..4352.544 rows=429803 loops=1) > >> Sort Key: pref_money.yw, pref_money.money > >> Sort Method: external sort Disk: 15856kB > > > > > > It's sorting on disk. That's not going to be fast. Indeed, it's taking > > nearly all the time the query takes (4.4s for this step out of 4.5s for the > > query). > > I've noticed that too, but what > does "sorting on disk" mean? > > I have a lot of RAM (32 GB) , > should I increase work_mem even more? > (it is currenlty 32 MB) > You should better create an index on pref_money(yw, money). It could help you get rid of the seqscan and sort operations. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] noobie question
On 01/24/2013 01:06 PM, Chris Angelico wrote: On Fri, Jan 25, 2013 at 4:45 AM, Steve Clark wrote: Thanks All, This is for a few very small tables, less 100 records each, that a user can delete and insert records into based on the "id" which is displayed in a php generated html screen. The tables are rarely updated and when they are updated only one person is accessing them at a time. I have seen several answers on inserting what about deleting? Deleting works exactly the same way; you just subtract instead of adding. And thanks Jeff, I forgot about that requirement. Still, searched update is the easiest solution. However, do seriously rethink your design. At very least, the "id" field is misnamed; it's not the record's identity if it changes. If your only two operations are "insert" and "delete" (with inserts permitted at either end of the list as well as in the middle), one way you could do it is to have a serially-numbered ID, and a 'pos'. Adding to the end means inserting a row with a pos one higher than the current highest. Inserting a record before another one means inserting a row with the same pos - no renumbering needed. Deleting a row is done by its id, not its position. And when you query the table, just ask for them "ORDER BY POS, ID DESC" - this will show them in the right order. This doesn't, however, handle arbitrary reordering of records. For that, you will ultimately need to renumber the positions. ChrisA Hi Chris, It is really called rule_num and relates to "in what order firewall rules are applied". And it used to allow the user to place the firewall rules where they want them in relation to other rules. This is an old design, of which I had no input, but am now maintaining. Like I said initially I have php, bash or C code to do the reordering and was just wondering if there was a slick way to do it without having to resort to some external mechanism. Thanks to all who responded. -- Stephen Clark -- 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] main.log file not being updated
On 01/24/2013 10:36 AM, Anson Abraham wrote: #log_destination = 'stderr' #logging_collector = off It was when it was restarted that this didn't start logging. To restart is an option, but one I'd like to avoid. It's 9.0 on debian squeeze. init.d/postgres start It was writing before. Just stopped after the last restart or rather reload of the config file. Also when I did an LSOF of the file, nothing was locking it ... ie usually postgres would be the one, but not in this case. http://www.postgresql.org/docs/9.0/interactive/runtime-config-logging.html logging_collector (boolean) This parameter captures plain and CSV-format log messages sent to stderr and redirects them into log files. This approach is often more useful than logging to syslog, since some types of messages might not appear in syslog output (a common example is dynamic-linker failure messages). This parameter can only be set at server start. Would seem something changed the config file to turn off the collector. You will need to set on and restart. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] main.log file not being updated
#log_destination = 'stderr' #logging_collector = off It was when it was restarted that this didn't start logging. To restart is an option, but one I'd like to avoid. It's 9.0 on debian squeeze. init.d/postgres start It was writing before. Just stopped after the last restart or rather reload of the config file. Also when I did an LSOF of the file, nothing was locking it ... ie usually postgres would be the one, but not in this case. On Thu, Jan 24, 2013 at 12:39 PM, Adrian Klaver wrote: > On 01/24/2013 09:29 AM, Anson Abraham wrote: > >> my postgresql-9.0-main.log log file is 0 bytes. Postgres user has perms >> to write to it. And and postgresql.conf file shows to log, but it's >> not. Not sure why. >> >> I have the defaults set in except for these changes: >> >> log_connections = on >> log_disconnections = on >> log_duration = off >> >> log_line_prefix = '%t %p %h %d ' >> log_lock_waits = on >> log_statement = 'none' >> >> any ideas? I used to write log entries, but now, not so much. >> > > What do the following show?: > > log_destination = > logging_collector > > Have you restarted the server since making the changes? > > How was Postgres installed and what platform/distrubution? > How is Postgres started? > > >> Thanks. >> > > > -- > Adrian Klaver > adrian.kla...@gmail.com >
Re: [GENERAL] noobie question
On Fri, Jan 25, 2013 at 4:45 AM, Steve Clark wrote: > Thanks All, > > This is for a few very small tables, less 100 records each, that a user can > delete and insert records into based on the "id" > which is displayed in a php generated html screen. The tables are rarely > updated and when they are updated only one person > is accessing them at a time. > > I have seen several answers on inserting what about deleting? Deleting works exactly the same way; you just subtract instead of adding. And thanks Jeff, I forgot about that requirement. Still, searched update is the easiest solution. However, do seriously rethink your design. At very least, the "id" field is misnamed; it's not the record's identity if it changes. If your only two operations are "insert" and "delete" (with inserts permitted at either end of the list as well as in the middle), one way you could do it is to have a serially-numbered ID, and a 'pos'. Adding to the end means inserting a row with a pos one higher than the current highest. Inserting a record before another one means inserting a row with the same pos - no renumbering needed. Deleting a row is done by its id, not its position. And when you query the table, just ask for them "ORDER BY POS, ID DESC" - this will show them in the right order. This doesn't, however, handle arbitrary reordering of records. For that, you will ultimately need to renumber the positions. ChrisA -- 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] noobie question
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Steve Clark Sent: Thursday, January 24, 2013 12:46 PM To: Jeff Janes Cc: Chris Angelico; pgsql-general@postgresql.org Subject: Re: [GENERAL] noobie question On 01/24/2013 12:36 PM, Jeff Janes wrote: > On Thu, Jan 24, 2013 at 8:53 AM, Chris Angelico wrote: >> On Fri, Jan 25, 2013 at 3:47 AM, Steve Clark wrote: >>> Say I have a table that has 2 columns like create table "foo" ( >>>id integer not null, >>>name text >>> ); >>> CREATE UNIQUE INDEX "foo_pkey" on "foo" using btree ( "id" >>> "int4_ops" ); >>> >>> with 10 rows of data where id is 1 to 10. >>> >>> Now I want to insert a new row ahead of id 5 so I have to renumber >>> the rows from 5 to 10 increasing each by one. >>> >>> Or the opposite I want to delete a row an renumber to close up the gap. >>> >>> Is there an easy way to do this in postgresql without resulting to >>> some external language? >> This is sounding, not like an ID, but like a "position" marker or >> something. It's most certainly possible; all you need is a searched >> update: >> >> UPDATE foo SET id=id+1 WHERE id>=5; >> INSERT INTO foo VALUES (5,'new item at pos 5'); > To do this reliably, you would have to set the unique constraint to > DEFERRABLE INITIALLY DEFERRED, otherwise you will get errors due to > transient duplicates. > > If his design requires that this kind of update be done regularly, he > should probably reconsider that design. > > Cheers, > > Jeff > > Thanks All, This is for a few very small tables, less 100 records each, that a user can delete and insert records into based on the "id" which is displayed in a php generated html screen. The tables are rarely updated and when they are updated only one person is accessing them at a time. I have seen several answers on inserting what about deleting? -- Stephen Clark -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general --- delete from mytable where id = 4; update mytable set id = id-1 where id > 4; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] date_trunc to aggregate by timestamp?
I am trying to some up with an approach that uses "date_truc" to aggregate 15 minute time series data to hourly bins. My current query which utilizes a view, does performs a join after which I use a series a WHERE statements to specify which of the 15 minute records I want to look at. I think what I need to do is to add a date_truc function to this query which would aggregate the 15 minute records to hourly means by plot. In other words each of the bolded records listed below (the four records from plot e2 with a timestamp from hour 15 would get averaged to a single record. I can see in the docs that date_trunc uses a select statement to grab the specified time unit. SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40'); Result: 2001-02-16 20:00:00 However, I am unclear as to how I need to implement this in my query in such a way that the aggregation averages "value" by "plot". Any suggestions would be appreciated. SELECT data_key.site, data_key.canopy, data_key.interval, data_key.treatment_code, data_key.treatment_abbr, data_key.plot, fifteen_min_stacked_proper.* FROM data_key, fifteen_min_stacked_proper WHERE data_key.variable_channel = fifteen_min_stacked_proper.variable AND data_key.block_name = fifteen_min_stacked_proper.block_name AND 2012 = EXTRACT(YEAR FROM time2) --AND fifteen_min_stacked_proper.block ~ 'b4warm_[ace]' AND fifteen_min_stacked_proper.value IS NOT NULL AND fifteen_min_stacked_proper.variable ~ 'scld' Here is a snip of the query output: sitecanopy intervalplotrowid time2 block variable value cfc open0:00:15 e2 2009-03-19_15:00:00_b4warm_e3/19/09 15:00 b4warm_escldout_avg10 cfc open0:00:15 e8 2009-03-19_15:00:00_b4warm_e3/19/09 15:00 b4warm_escldout_avg20 cfc open0:00:15 e1 2009-03-19_15:00:00_b4warm_e3/19/09 15:00 b4warm_escldout_avg30 cfc open0:00:15 e5 2009-03-19_15:00:00_b4warm_e3/19/09 15:00 b4warm_escldout_avg40 cfc open0:00:15 e2 2009-03-19_15:15:00_b4warm_e3/19/09 15:15 b4warm_escldout_avg10 cfc open0:00:15 e8 2009-03-19_15:15:00_b4warm_e3/19/09 15:15 b4warm_escldout_avg20 cfc open0:00:15 e1 2009-03-19_15:15:00_b4warm_e3/19/09 15:15 b4warm_escldout_avg30 cfc open0:00:15 e5 2009-03-19_15:15:00_b4warm_e3/19/09 15:15 b4warm_escldout_avg40 cfc open0:00:15 e2 2009-03-19_15:30:00_b4warm_e3/19/09 15:30 b4warm_escldout_avg17999 cfc open0:00:15 e8 2009-03-19_15:30:00_b4warm_e3/19/09 15:30 b4warm_escldout_avg27999 cfc open0:00:15 e1 2009-03-19_15:30:00_b4warm_e3/19/09 15:30 b4warm_escldout_avg33579 cfc open0:00:15 e5 2009-03-19_15:30:00_b4warm_e3/19/09 15:30 b4warm_escldout_avg43579 cfc open0:00:15 e2 2009-03-19_15:45:00_b4warm_e3/19/09 15:45 b4warm_escldout_avg17999 cfc open0:00:15 e8 2009-03-19_15:45:00_b4warm_e3/19/09 15:45 b4warm_escldout_avg27999 cfc open0:00:15 e1 2009-03-19_15:45:00_b4warm_e3/19/09 15:45 b4warm_escldout_avg34000 cfc open0:00:15 e5 2009-03-19_15:45:00_b4warm_e3/19/09 15:45 b4warm_escldout_avg44000 cfc open0:00:15 e2 2009-03-19_16:00:00_b4warm_e3/19/09 16:00 b4warm_escldout_avg17999 cfc open0:00:15 e8 2009-03-19_16:00:00_b4warm_e3/19/09 16:00 b4warm_escldout_avg27999 cfc open0:00:15 e1 2009-03-19_16:00:00_b4warm_e3/19/09 16:00 b4warm_escldout_avg34000 cfc open0:00:15 e5 2009-03-19_16:00:00_b4warm_e3/19/09 16:00 b4warm_escldout_avg44000 cfc open0:00:15 e2 2009-03-19_16:15:00_b4warm_e3/19/09 16:15 b4warm_escldout_avg17999 cfc open0:00:15 e8 2009-03-19_16:15:00_b4warm_e3/19/09 16:15 b4warm_escldout_avg27999 cfc open0:00:15 e1 2009-03-19_16:15:00_b4warm_e3/19/09 16:15 b4warm_escldout_avg34000 cfc open0:00:15 e5 2009-03-19_16:15:00_b4warm_e3/19/09 16:15 b4warm_escldout_avg44000 cfc open0:00:15 e2 2009-03-19_16:30:00_b4warm_e3/19/09 16:30 b4warm_escldout_avg17999 cfc open0:00:15 e8 2009-03-19_16:30:00_b4warm_e3/19/09 16:30 b4warm_escldout_avg27999 cfc open0:00:15 e1 2009-03-19_16:30:00_b4warm_e3/19/09 16:30 b4warm_escldout_avg34000 cfc open0:00:15 e5 2009-03-19_16:30:00_b4warm_e3/19/
Re: [GENERAL] noobie question
On 01/24/2013 12:36 PM, Jeff Janes wrote: On Thu, Jan 24, 2013 at 8:53 AM, Chris Angelico wrote: On Fri, Jan 25, 2013 at 3:47 AM, Steve Clark wrote: Say I have a table that has 2 columns like create table "foo" ( id integer not null, name text ); CREATE UNIQUE INDEX "foo_pkey" on "foo" using btree ( "id" "int4_ops" ); with 10 rows of data where id is 1 to 10. Now I want to insert a new row ahead of id 5 so I have to renumber the rows from 5 to 10 increasing each by one. Or the opposite I want to delete a row an renumber to close up the gap. Is there an easy way to do this in postgresql without resulting to some external language? This is sounding, not like an ID, but like a "position" marker or something. It's most certainly possible; all you need is a searched update: UPDATE foo SET id=id+1 WHERE id>=5; INSERT INTO foo VALUES (5,'new item at pos 5'); To do this reliably, you would have to set the unique constraint to DEFERRABLE INITIALLY DEFERRED, otherwise you will get errors due to transient duplicates. If his design requires that this kind of update be done regularly, he should probably reconsider that design. Cheers, Jeff Thanks All, This is for a few very small tables, less 100 records each, that a user can delete and insert records into based on the "id" which is displayed in a php generated html screen. The tables are rarely updated and when they are updated only one person is accessing them at a time. I have seen several answers on inserting what about deleting? -- Stephen Clark -- 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] main.log file not being updated
On 01/24/2013 09:29 AM, Anson Abraham wrote: my postgresql-9.0-main.log log file is 0 bytes. Postgres user has perms to write to it. And and postgresql.conf file shows to log, but it's not. Not sure why. I have the defaults set in except for these changes: log_connections = on log_disconnections = on log_duration = off log_line_prefix = '%t %p %h %d ' log_lock_waits = on log_statement = 'none' any ideas? I used to write log entries, but now, not so much. What do the following show?: log_destination = logging_collector Have you restarted the server since making the changes? How was Postgres installed and what platform/distrubution? How is Postgres started? Thanks. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] noobie question
On Thu, Jan 24, 2013 at 8:53 AM, Chris Angelico wrote: > On Fri, Jan 25, 2013 at 3:47 AM, Steve Clark wrote: >> Say I have a table that has 2 columns like >> create table "foo" ( >> id integer not null, >> name text >> ); >> CREATE UNIQUE INDEX "foo_pkey" on "foo" using btree ( "id" "int4_ops" ); >> >> with 10 rows of data where id is 1 to 10. >> >> Now I want to insert a new row ahead of id 5 so I have to renumber the rows >> from 5 to 10 increasing each by one. >> >> Or the opposite I want to delete a row an renumber to close up the gap. >> >> Is there an easy way to do this in postgresql without resulting to some >> external language? > > This is sounding, not like an ID, but like a "position" marker or > something. It's most certainly possible; all you need is a searched > update: > > UPDATE foo SET id=id+1 WHERE id>=5; > INSERT INTO foo VALUES (5,'new item at pos 5'); To do this reliably, you would have to set the unique constraint to DEFERRABLE INITIALLY DEFERRED, otherwise you will get errors due to transient duplicates. If his design requires that this kind of update be done regularly, he should probably reconsider that design. Cheers, Jeff -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] main.log file not being updated
my postgresql-9.0-main.log log file is 0 bytes. Postgres user has perms to write to it. And and postgresql.conf file shows to log, but it's not. Not sure why. I have the defaults set in except for these changes: log_connections = on log_disconnections = on log_duration = off log_line_prefix = '%t %p %h %d ' log_lock_waits = on log_statement = 'none' any ideas? I used to write log entries, but now, not so much. Thanks.
Re: [GENERAL] noobie question
On 01/24/2013 08:47 AM, Steve Clark wrote: Hi list, This may be really simple - I usually do it using a procedural language such as php or a bash script. Say I have a table that has 2 columns like create table "foo" ( id integer not null, name text ); CREATE UNIQUE INDEX "foo_pkey" on "foo" using btree ( "id" "int4_ops" ); with 10 rows of data where id is 1 to 10. Now I want to insert a new row ahead of id 5 so I have to renumber the rows from 5 to 10 increasing each by one. Or the opposite I want to delete a row an renumber to close up the gap. Is there an easy way to do this in postgresql without resulting to some external language? I can see this taking a lot of overhead as the table increases. I guess it comes down to what you are trying to achieve? Do you want a gapless sequence? Do you want a ROWNUM? Something else? Thanks for your consideration. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] noobie question
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Steve Clark Sent: Thursday, January 24, 2013 11:47 AM To: pgsql Subject: [GENERAL] noobie question Hi list, This may be really simple - I usually do it using a procedural language such as php or a bash script. Say I have a table that has 2 columns like create table "foo" ( id integer not null, name text ); CREATE UNIQUE INDEX "foo_pkey" on "foo" using btree ( "id" "int4_ops" ); with 10 rows of data where id is 1 to 10. Now I want to insert a new row ahead of id 5 so I have to renumber the rows from 5 to 10 increasing each by one. Or the opposite I want to delete a row an renumber to close up the gap. Is there an easy way to do this in postgresql without resulting to some external language? Thanks for your consideration. -- Stephen Clark -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general - I think you could do this in plpgsql, but what I see is a lot of updates that "may" not be needed. If your table has a million records, 1-100, and you want to insert a record between positions 1 & 2, you're basically updating the whole table. That's a lot of thrashing! I don't know what you are trying to do with this, but if what you really care about is just ordering the records, insert "1.5" (a float of course). If the id has to be an integer, maybe you could define a (materialized) view to do that. But if this table's going to have a lot of records in it, and expecially if it has indexes on it, you may want to avoid sweeping updates like this. If nothign else, it may just take a long time. -- 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] noobie question
On Fri, Jan 25, 2013 at 3:47 AM, Steve Clark wrote: > Say I have a table that has 2 columns like > create table "foo" ( > id integer not null, > name text > ); > CREATE UNIQUE INDEX "foo_pkey" on "foo" using btree ( "id" "int4_ops" ); > > with 10 rows of data where id is 1 to 10. > > Now I want to insert a new row ahead of id 5 so I have to renumber the rows > from 5 to 10 increasing each by one. > > Or the opposite I want to delete a row an renumber to close up the gap. > > Is there an easy way to do this in postgresql without resulting to some > external language? This is sounding, not like an ID, but like a "position" marker or something. It's most certainly possible; all you need is a searched update: UPDATE foo SET id=id+1 WHERE id>=5; INSERT INTO foo VALUES (5,'new item at pos 5'); Be aware that this can have nasty consequences with concurrency. I strongly recommend having your ID be autonumbered and never changed (eg 'ID SERIAL PRIMARY KEY'), and have your reordered field called 'position' or whatever makes sense; then you can select a set of records by their IDs and move them more safely. Alternatively, if these sorts of reordering operations are rare, you could just lock the whole table, but that's a major concurrency killer. (Though not as bad as simply throwing back a serialization error at the end. I was working with a system yesterday that did exactly that... along with taking, I kid you not, over 900ms to perform a single operation. So concurrency was desperately needed and not an option.) ChrisA -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] noobie question
Hi list, This may be really simple - I usually do it using a procedural language such as php or a bash script. Say I have a table that has 2 columns like create table "foo" ( id integer not null, name text ); CREATE UNIQUE INDEX "foo_pkey" on "foo" using btree ( "id" "int4_ops" ); with 10 rows of data where id is 1 to 10. Now I want to insert a new row ahead of id 5 so I have to renumber the rows from 5 to 10 increasing each by one. Or the opposite I want to delete a row an renumber to close up the gap. Is there an easy way to do this in postgresql without resulting to some external language? Thanks for your consideration. -- Stephen Clark -- 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] Optimizing select count query which often takes over 10 seconds
> > It's sorting on disk. That's not going to be fast. Indeed, it's taking > > nearly all the time the query takes (4.4s for this step out of 4.5s for > the > > query). > > I've noticed that too, but what > does "sorting on disk" mean? > > I have a lot of RAM (32 GB) , > should I increase work_mem even more? > (it is currenlty 32 MB) > You can try increasing the amount of work_mem in your psql session only and see what amount helps. That way you don't need to permanently increase it for all your queries. I'd start with 48 MB and increase in increments of 16 MB (as that's the size the sort operation claims to require on disk). -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
Re: [GENERAL] How to identify the source of a deadlock?
Stefan Froehlich wrote: > There is an application A doing some things in a database. In the middle > of the program, application B is called which does some other things. > Now for some reason application B hangs for certain inputs and I have to > find out the reason for this. The sequence is: > > | A: BEGIN > | A: [does some things] > | A: [calls B] > | > | B: BEGIN > | B: [does some things] > | B: update bmeproduct set manufacturerpid='40913', leadtime='1' where > idproduct=9681 > | B: [waits forever] > > When I look at pg_stat_activity, I can see the update statement with > "waiting == 't'" for process B, which would not change for at least > several hours. And I can see " in transaction" for process A > which, of course, I know by the nature of the program. > > However, to eliminate the problem I would need the actual source of the > lock. Program A does not ever touch the table "bmeproduct" (it does only > things in a completely different part of the database - or at least: it > _should_ do), so the lock must be introduces via some foreign key. But: > is there any chance to find out WHICH table (or even record) is the > cause of the trouble? This would be so helpful... The pg_locks catalog has the information you want. Maybe the Wiki article can help you: http://wiki.postgresql.org/wiki/Lock_Monitoring Yours, Laurenz Albe -- 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] DB alias ?
Dave Gauthier wrote: > I would have suggested to use pg_services file as documented at > > http://www.postgresql.org/docs/9.1/static/libpq-pgservice.html > http://www.postgresql.org/docs/9.1/static/libpq-connect.html > > You can think of this as tnsnames replacement. > > but I am unable to make it work. I don't know what is wrong with this. > > shridhar@bheem ~$ cat ~/.pg_service.conf > [test1] > host=localhost > dbname=test > > shridhar@bheem ~$ strace -o psql.strace psql test1 That's wrong. It should have been psql "service=test1" as has been pointed out. > psql: FATAL: database "test1" does not exist > > shridhar@bheem ~$ grep -i pg_service psql.strace > > shridhar@bheem ~$ psql test > psql (9.2.2) > Type "help" for help. > > test=# \q > > shridhar@bheem ~$ psql --version > psql (PostgreSQL) 9.2.2 > The services file looked/looks interesting, but there are far too many > clients, and at multiple sites, > to manage this. I really need something on the server side, a single place > to manage this for all > connections regardless of where they are coming from. It also looks like a C > lib based file, > something I won't have access to except, maybe, through an app designed for > DBAs to edit this file. > If I am mistaken, and the services file is in the DB root area (the area > specified after the "-D" in > commands like pg_ctl), then maybe this is still viable. But I don't see a > services file there :-( The service file is on the client side. In a scenario like yours, use LDAP lookup: http://www.postgresql.org/docs/current/static/libpq-ldap.html Yours, Laurenz Albe -- 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 identify the source of a deadlock?
There is an application A doing some things in a database. In the middle of the program, application B is called which does some other things. Now for some reason application B hangs for certain inputs and I have to find out the reason for this. The sequence is: | A: BEGIN | A: [does some things] | A: [calls B] | | B: BEGIN | B: [does some things] | B: update bmeproduct set manufacturerpid='40913', leadtime='1' where idproduct=9681 | B: [waits forever] When I look at pg_stat_activity, I can see the update statement with "waiting == 't'" for process B, which would not change for at least several hours. And I can see " in transaction" for process A which, of course, I know by the nature of the program. However, to eliminate the problem I would need the actual source of the lock. Program A does not ever touch the table "bmeproduct" (it does only things in a completely different part of the database - or at least: it _should_ do), so the lock must be introduces via some foreign key. But: is there any chance to find out WHICH table (or even record) is the cause of the trouble? This would be so helpful... Stefan -- Grenzenlos bleibt grenzenlos: Trotz Stefan! http://www.sloganizer.de/ -- 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] Optimizing select count query which often takes over 10 seconds
Hello - On Thu, Jan 24, 2013 at 3:39 PM, Alban Hertroys wrote: > On 24 January 2013 10:57, Alexander Farber > wrote: >> >> # explain analyze select count(id) from ( >> select id, >>row_number() over(partition by yw order by money >> desc) as ranking >> from pref_money >> ) x >> where x.ranking = 1 and id='OK452217781481'; >> QUERY PLAN >> >> --- >> Aggregate (cost=63694.22..63694.23 rows=1 width=82) (actual >> time=4520.719..4520.719 rows=1 loops=1) >>-> Subquery Scan x (cost=48519.10..63694.19 rows=11 width=82) >> (actual time=4470.620..4520.710 rows=6 loops=1) >> Filter: ((x.ranking = 1) AND ((x.id)::text = >> 'OK452217781481'::text)) >> -> WindowAgg (cost=48519.10..57190.58 rows=433574 width=26) >> (actual time=4293.315..4491.652 rows=429803 loops=1) >>-> Sort (cost=48519.10..49603.03 rows=433574 >> width=26) (actual time=4293.306..4352.544 rows=429803 loops=1) >> Sort Key: pref_money.yw, pref_money.money >> Sort Method: external sort Disk: 15856kB > > > It's sorting on disk. That's not going to be fast. Indeed, it's taking > nearly all the time the query takes (4.4s for this step out of 4.5s for the > query). I've noticed that too, but what does "sorting on disk" mean? I have a lot of RAM (32 GB) , should I increase work_mem even more? (it is currenlty 32 MB) Regards Alex -- 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] Optimizing select count query which often takes over 10 seconds
On 24 January 2013 10:57, Alexander Farber wrote: > # explain analyze select count(id) from ( > select id, >row_number() over(partition by yw order by money > desc) as ranking > from pref_money > ) x > where x.ranking = 1 and id='OK452217781481'; > QUERY PLAN > > --- > Aggregate (cost=63694.22..63694.23 rows=1 width=82) (actual > time=4520.719..4520.719 rows=1 loops=1) >-> Subquery Scan x (cost=48519.10..63694.19 rows=11 width=82) > (actual time=4470.620..4520.710 rows=6 loops=1) > Filter: ((x.ranking = 1) AND ((x.id)::text = > 'OK452217781481'::text)) > -> WindowAgg (cost=48519.10..57190.58 rows=433574 width=26) > (actual time=4293.315..4491.652 rows=429803 loops=1) >-> Sort (cost=48519.10..49603.03 rows=433574 > width=26) (actual time=4293.306..4352.544 rows=429803 loops=1) > Sort Key: pref_money.yw, pref_money.money > Sort Method: external sort Disk: 15856kB > It's sorting on disk. That's not going to be fast. Indeed, it's taking nearly all the time the query takes (4.4s for this step out of 4.5s for the query). > -> Seq Scan on pref_money (cost=0.00..7923.74 > rows=433574 width=26) (actual time=0.006..41.907 rows=429803 loops=1) > And then it's doing a sequential scan to sort the data. I suspect that's because it's sorting on disk. Then again, this only takes 42ms, just once (loops=1), so perhaps a seqscan is indeed the fastest approach here (actually, wow, it scans 1 records/ms - rows are 26 bytes wide, so that's 260MB/s! I'm doubting my math here...). Total runtime: 4525.662 ms > (9 rows) > > Thank you for any hints > Alex > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
Re: [GENERAL] DB alias ?
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Shridhar Daithankar Sent: Wednesday, January 23, 2013 10:32 PM To: pgsql-general@postgresql.org Cc: Gauthier, Dave; Rob Sargent Subject: Re: [GENERAL] DB alias ? On Wednesday, January 23, 2013 09:39:43 PM Gauthier, Dave wrote: > Then someone who wants to look at old JAN data will have the same problem > :-( > > If I recall, Oracle enables something like this. Multiple tnsfilenames (or > something like that). There was a connect layer on the server side that > the DBA had access to where you could do stuff like this. > >> proposed new SQL command: > >>READ USERS MIND; > : > :-) > > Actually, read the DBA's mind. > > How about... > > postgres=# create db_alias FEB to db JAN; > postgres=# drop db_alias FEB; I would have suggested to use pg_services file as documented at http://www.postgresql.org/docs/9.1/static/libpq-pgservice.html http://www.postgresql.org/docs/9.1/static/libpq-connect.html You can think of this as tnsnames replacement. but I am unable to make it work. I don't know what is wrong with this. shridhar@bheem ~$ cat ~/.pg_service.conf [test1] host=localhost dbname=test shridhar@bheem ~$ strace -o psql.strace psql test1 psql: FATAL: database "test1" does not exist shridhar@bheem ~$ grep -i pg_service psql.strace shridhar@bheem ~$ psql test psql (9.2.2) Type "help" for help. test=# \q shridhar@bheem ~$ psql --version psql (PostgreSQL) 9.2.2 -- Regards Shridhar - The services file looked/looks interesting, but there are far too many clients, and at multiple sites, to manage this. I really need something on the server side, a single place to manage this for all connections regardless of where they are coming from. It also looks like a C lib based file, something I won't have access to except, maybe, through an app designed for DBAs to edit this file. If I am mistaken, and the services file is in the DB root area (the area specified after the "-D" in commands like pg_ctl), then maybe this is still viable. But I don't see a services file there :-(
Re: [GENERAL] Logging successful SELECTS?
2013/1/24 Matthew Vernon : > Hi, > > I can get postgres to log unsuccessful queries, including the user who > wrote them, but I'm missing how to get postgres to log the successful > queries too (I don't need a store of the answer, just the query > itself). How do I do this? use log_min_duration_statement = 0 Regards Pavel Stehule > > Thanks, > > Matthew > > -- > Matthew Vernon > Quantitative Veterinary Epidemiologist > Epidemiology Research Unit, SAC Inverness > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Logging successful SELECTS?
2013/1/24 Matthew Vernon : > I can get postgres to log unsuccessful queries, including the user who > wrote them, but I'm missing how to get postgres to log the successful > queries too (I don't need a store of the answer, just the query > itself). How do I do this? You can use either log_min_duration_statement or log_statement. http://www.postgresql.org/docs/current/interactive/runtime-config-logging.html I would recommend using the first one, logging all statements that took longer then, say, 300ms. Otherwise logs might become too huge to manage. -- Victor Y. Yegorov -- 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] Optimizing select count query which often takes over 10 seconds
On Thu, Jan 24, 2013 at 4:57 AM, Alexander Farber < alexander.far...@gmail.com> wrote: > select count(id) from ( > select id, >row_number() over(partition by yw order by money > desc) as ranking > from pref_money > ) x > where x.ranking = 1 and id='OK471018960997' > > Does anybody please have an idea > how could I optimize it or should > I introduce a hourly job and a "medals" > column (that would make my players > stats less "live")? > One idea is to have a new ranking column to cache every player's ranking for every weekly tournament. However, instead of updating it hourly with a cron job, you could have a trigger on the table, such that when any row is updated/inserted, you recalculate the rankings for only those rows having the same "yw" value. Then, you might want to create an index on the ranking column as well as the yw column, which you already have indexed. Moshe -- Moshe Jacobson Nead Werx, Inc. | Senior Systems Engineer 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com
[GENERAL] Logging successful SELECTS?
Hi, I can get postgres to log unsuccessful queries, including the user who wrote them, but I'm missing how to get postgres to log the successful queries too (I don't need a store of the answer, just the query itself). How do I do this? Thanks, Matthew -- Matthew Vernon Quantitative Veterinary Epidemiologist Epidemiology Research Unit, SAC Inverness -- 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] Jobs for a Oracle/Postgres DBAs in Australia
Hello, It is great to see the Australian market pick up. However, this really belongs in pgsql-jobs. Sincerely, JD On 01/23/2013 09:21 PM, Cameron Shorter wrote: I'm hoping this opportunity will be of interest to some of you on this list: LISAsoft [0] has expanded our Australian/New Zealand Open Source Support offerings to include dedicated Postgres commercial support, migrations to Postgres, and training, through our partnership with EnterpriseDB [1]. (LISAsoft is the sole Australian and New Zealand distributor for EnterpriseDB products). If you are a DBA with experience with Postgres and Oracle, and would like to capitalise on this experience by joining a DBA team within an Open Source friendly company, then please have a look at our job descriptions for Sydney [2] and Melbourne [3] in Australia. [0] http://lisasoft.com [1] http://www.enterprisedb.com/ [2] http://www.seek.com.au/Job/oracle-postgres-dba/in/sydney-cbd-inner-west-eastern-suburbs/23849321 [3] http://www.seek.com.au/Job/oracle-postgres-dba/in/melbourne-cbd-inner-suburbs/23849953 -- 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] DB alias ?
Guillaume Lelarge wrote: > On Thu, 2013-01-24 at 09:01 +0530, Shridhar Daithankar wrote: > > On Wednesday, January 23, 2013 09:39:43 PM Gauthier, Dave wrote: > > > Then someone who wants to look at old JAN data will have the same problem > > > :-( > > > > > > If I recall, Oracle enables something like this. Multiple tnsfilenames > > > (or > > > something like that). There was a connect layer on the server side that > > > the DBA had access to where you could do stuff like this. > > I would have suggested to use pg_services file as documented at > > > > http://www.postgresql.org/docs/9.1/static/libpq-pgservice.html > > http://www.postgresql.org/docs/9.1/static/libpq-connect.html > > > > You can think of this as tnsnames replacement. > > > > but I am unable to make it work. I don't know what is wrong with this. > > > > shridhar@bheem ~$ cat ~/.pg_service.conf > > [test1] > > host=localhost > > dbname=test > > > > shridhar@bheem ~$ strace -o psql.strace psql test1 > > psql: FATAL: database "test1" does not exist > > Well, you need to tell psql to use a service: > > psql service=test1 > > or > > PGSERVICE=test1 > psql In addition, to return to the example from http://www.postgresql.org/message-id/0ad01c53605506449ba127fb8b99e5e16112d...@fmsmsx105.amr.corp.intel.com you can centralize the name resolution on an LDAP server: http://www.postgresql.org/docs/current/static/libpq-ldap.html That saves you from having to change the pg_service.conf file on each client if you have a lot of clients. Yours, Laurenz Albe -- 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] DB alias ?
On Thu, 2013-01-24 at 09:01 +0530, Shridhar Daithankar wrote: > On Wednesday, January 23, 2013 09:39:43 PM Gauthier, Dave wrote: > > Then someone who wants to look at old JAN data will have the same problem > > :-( > > > > If I recall, Oracle enables something like this. Multiple tnsfilenames (or > > something like that). There was a connect layer on the server side that > > the DBA had access to where you could do stuff like this. > > >> proposed new SQL command: > > >>READ USERS MIND; > > : > > :-) > > > > Actually, read the DBA's mind. > > > > How about... > > > > postgres=# create db_alias FEB to db JAN; > > postgres=# drop db_alias FEB; > > I would have suggested to use pg_services file as documented at > > http://www.postgresql.org/docs/9.1/static/libpq-pgservice.html > http://www.postgresql.org/docs/9.1/static/libpq-connect.html > > You can think of this as tnsnames replacement. > > but I am unable to make it work. I don't know what is wrong with this. > > shridhar@bheem ~$ cat ~/.pg_service.conf > [test1] > host=localhost > dbname=test > > shridhar@bheem ~$ strace -o psql.strace psql test1 > psql: FATAL: database "test1" does not exist > Well, you need to tell psql to use a service: psql service=test1 or PGSERVICE=test1 psql -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general