[GENERAL] dbsamples from pgfoundry
Hi, I would like some pointers as to how to install the dbsamples so that I can use them in Postgres. These .tar.qz files seem to contain *.sql files. These seem to be text files but I do not understand how to import to postgres or if there is some command line command to run. These samples are on http://www.postgresql.org/ftp/projects/pgFoundry/dbsamples/ Tommy.
Re: [JDBC] [GENERAL] need help of getting PK after insertRow in JDBC
Martin,
Have you tried using the RETURNING function in your SQL insert statement
to immediately return the auto-generated key value? For example, how about
something like this:
String insert = INSERT INTO \schema_name\.\XX\ (\BB\, \CC\,
\DD\, \EE\) VALUES (?, ?, ?, ?) RETURNING \AA\
PreparedStatment ps = con.prepareStatement(insert);
ps.setString(1, b);
ps.setString(2, c);
ps.setString(3, d);
ps.setString(4, e);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
int index = rs.getInt(AA);
}
Hope this helps,
Greg
_
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Martin Gainty
Sent: Monday, September 29, 2008 2:16 PM
To: Chen, Dongdong (GE Healthcare, consultant);
pgsql-general@postgresql.org; [EMAIL PROTECTED]
Subject: Re: [JDBC] [GENERAL] need help of getting PK after insertRow in
JDBC
Good Afternoon
add the CLOSE_CURSORS_AT_COMMIT Option to the Statements capabilities e.g.
Statement st = db.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE.ResultSet.CLOSE_CURSORS_AT_COMMIT);
//then tell the connection handle to commit the DML to the DB
db.commit();
Martin
Control your own destiny or someone else will -- Jack Welch
__
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official
business of Sender. This transmission is of a confidential nature and Sender
does not endorse distribution to any party other than intended recipient.
Sender does not necessarily endorse content contained within this
transmission.
_
Subject: [GENERAL] need help of getting PK after insertRow in JDBC
Date: Fri, 26 Sep 2008 10:14:01 +0800
From: [EMAIL PROTECTED]
To: pgsql-general@postgresql.org; [EMAIL PROTECTED]
Hi:
I am a software engineer from GE. I am using JDBC to operate
PostgreSQL8.3 in Ubuntu8.04. The develop environment is Eclipse3.2 My
problem is:
There is a PostgreSQL table XX containing 5 fields: AA, BB, CC, DD, EE,
AA is primary key and auto-generated type, BB, CC, DD and EE is string type.
I want to get the value of AA immediately after insert a row into the
table. the code is like this:
Statement st = db.creatStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs=st.executeQuery(SELECT * FROM XX);
rs.moveToInsertRow();
rs.updateString(BB, b);
rs.updateString(CC, c);
rs.updateString(DD, d);
rs.updateString(EE, e);
rs.insertRow();
rs.moveToCurrentRow();
int index = rs.getInt(AA);
System.out.println(index);
in the last sentence I always get 0 no matter how many records I insert.
I tried other ways of moving the cursor including next(), last() and so on,
and also cannot get the correct value. I tried the drivers both
postgresql-8.3-603.jdbc3.jar and postgresql-8.3-603.jdbc4.jar.
But when I use pdadminIII to check the table XX, the AA field is already
auto-generated with the correct value.
I found a way to solve this: close resultset and statement after
moveToCurrentRow() and re-open them, and rs.last(), then run int
index=rs.getInt(AA), I can get the correct value. I think this method is
sort of awkward, anyone knows a better way and the correct operations?
I am not sure it is proper to send this mail to this mail list. Sorry if
bring you any inconvenience.
Thanks a lot!
Best Regards
Kevin Chen/ChenDongdong
+8613810644051
_
See how Windows connects the people, information, and fun that are part of
your life. See Now
http://clk.atdmt.com/MRT/go/msnnkwxp1020093175mrt/direct/01/
image001.jpg
Re: [GENERAL] dbsamples from pgfoundry
am Mon, dem 29.09.2008, um 22:52:52 +0100 mailte Tommy Gibbons folgendes: Hi, I would like some pointers as to how to install the dbsamples so that I can use them in Postgres. These .tar.qz files seem to contain *.sql files. These seem to be text files but I do not understand how to import to postgres or if there is some command line command to run. Use psql for that, either within psql with \i or call psql with option -f file.sql HTH, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- 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] Replication using slony-I
Dear All, I worked again from very start and faced the same problem which encountered earlier. In order to perform replication. I am following the steps present in http://people.planetpostgresql.org/dpage/index.php?/archives/51-Setting-up-Slony-I-with-pgAdmin.html and successfully reached to step # 13. I have Slony-I pgAdmin-III included in postgresql-8.2 and the platform is WindowsXp. But here I am not getting the NewSubscription option when do right click on Subscription Set. --- On Tue, 9/30/08, Abdul Rahman [EMAIL PROTECTED] wrote: From: Abdul Rahman [EMAIL PROTECTED] Subject: Re: [GENERAL] Replication using slony-I To: pgsql-general@postgresql.org Cc: [EMAIL PROTECTED], [EMAIL PROTECTED] Date: Tuesday, September 30, 2008, 9:55 AM Thanks a lot for replying! Here is complete information for consideration: In order to perform replication. I am following the steps present in http://people.planetpostgresql.org/dpage/index.php?/archives/51-Setting-up-Slony-I-with-pgAdmin.html and successfully reached to step # 13. I have Slony-I pgAdmin-III included in postgresql-8.2 and the platform is WindowsXp. But here I am not getting the NewSubscription option when do right click on Subscription Set. I also worked and found that the command in step # 4 psql -U postgres slave1 schema.sql is not working because I am not getting the tables in either slave. So, I suppose I have to run the command in step # 2 pgbench -i -U postgres master for slave1 and slave2 instead of making sql script and running it. I am going to restart my work in this way. Glyn Astill you may explore slonik in detail that I may use it now. So many Thanks Again.
[GENERAL] Free Cache Memory (Linux) and Postgresql
Hi. I'm evaluating to issue the drop_caches kernel command (echo 3 /proc/sys/vm/drop_caches) in order to free unused pagecache, directory entries and inodes. I'm thinking to schedule the command during low load moments after forcing a sync command. I wonder if this can cause pgsql problems of any kind. Any idea? Thank you in advance, Denis Gasparin Edistar SRL -- 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] Replication using slony-I
From: Abdul Rahman [EMAIL PROTECTED] Dear All, I worked again from very start and faced the same problem which encountered earlier. In order to perform replication. I am following the steps present in http://people.planetpostgresql.org/dpage/index.php?/archives/51-Setting-up-Slony-I-with-pgAdmin.html and successfully reached to step # 13. I have Slony-I pgAdmin-III included in postgresql-8.2 and the platform is WindowsXp. But here I am not getting the NewSubscription option when do right click on Subscription Set. Are all the slons running, and do you see the connections on each node. Under the pgAdmin replication-nodes tab on every node do you see every node listed allong with it's paths and listens. -- 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] Replication using slony-I
How will I get know that all slons are running? However in the replication pgbench properties the value of Slon PID is Not Running in each node. Yes, every node is listed with its path and listen. And what do you mean by do you see the connections on each node. Thanks Glyn Astill. --- On Tue, 9/30/08, Glyn Astill [EMAIL PROTECTED] wrote: From: Glyn Astill [EMAIL PROTECTED] Subject: Re: [GENERAL] Replication using slony-I To: pgsql-general@postgresql.org, [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Date: Tuesday, September 30, 2008, 1:36 PM From: Abdul Rahman [EMAIL PROTECTED] Dear All, I worked again from very start and faced the same problem which encountered earlier. In order to perform replication. I am following the steps present in http://people.planetpostgresql.org/dpage/index.php?/archives/51-Setting-up-Slony-I-with-pgAdmin.html and successfully reached to step # 13. I have Slony-I pgAdmin-III included in postgresql-8.2 and the platform is WindowsXp. But here I am not getting the NewSubscription option when do right click on Subscription Set. Are all the slons running, and do you see the connections on each node. Under the pgAdmin replication-nodes tab on every node do you see every node listed allong with it's paths and listens.
Re: [GENERAL] pg_start_backup() takes too long
On Mon, Sep 29, 2008 at 2:12 PM, Simon Riggs [EMAIL PROTECTED] wrote: On Mon, 2008-09-29 at 13:39 +0400, Ivan Zolotukhin wrote: This is all not about checkpoints. As I've mentioned in the first message, even right after manual run of CHECKPOINT command in psql pg_start_backup() takes same time (~10 minutes). As explained, there's not very much going on apart from the checkpoint and that can be problematic. What version are you running? What are your checkpoint_timeout and checkpoint_completion_target settings? My guesses are 8.3, 20 minutes, and default. You're dead right: 8.3.3, 30 minutes (it's intended), and default. pg_start_backup() doesn't do an immediate checkpoint, it does a smooth one, so doing a CHECKPOINT beforehand should make no difference in 8.3. Aham, now I see. Just a few points on pg_start_backup() from user point of view. I personally would prefer to have some control over the process, e.g. it would be nice to have proposed pg_start_backup(label text, immediate_chkpt boolean). But if that's not what you want by some other reason, it would be nice to add at least log notice telling that system is going to make a spreaded checkpoint and it's gonna take this approximate time (one can estimate a time limit from config constants, right?). -- 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] Replication using slony-I
From: Abdul Rahman [EMAIL PROTECTED] Subject: Re: [GENERAL] Replication using slony-I To: pgsql-general@postgresql.org Cc: [EMAIL PROTECTED] Date: Tuesday, 30 September, 2008, 9:51 AM How will I get know that all slons are running? However in the replication pgbench properties the value of Slon PID is Not Running in each node. Yes, every node is listed with its path and listen. And what do you mean by do you see the connections on each node. Do you see connections into postgres for your slony user from each other node. If you're on windows you'll be able to see them in task manager and in the status tab of pgAdmin Server Status. Is there anything odd in the slony logs? I presume you hace created a replication set with some tables in it, and you're right clicking on the origin and selecting new object, art the other objects in the list or are you just missing subscribe? -- 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] Replication using slony-I
Dear all, Due to personal reason (to celebrate EID) I will rejoin my work from 06-Oct-2008. Regards, Abdul Rehman
Re: [GENERAL] ODBC driver crash
Craig Ringer wrote: Hi The crash occurs whether a file, system, or user DSN is being used. I can reproduce this on two different machines. It happens with or without SSL in use. It affects any Access 2007 database with a PostgreSQL ODBC connection in use, including a newly created blank database with a freshly linked table. [snip] I'm at a bit of a loss. I'm used to debugging problems on platforms with luxuries like symbol names in binaries, or at least my own code on win32 where I can build it for debugging. Anybody else seeing, or seen, similar issues? The closest I've had with PHP that has some of the same symptoms is in http://archives.postgresql.org/pgsql-bugs/2008-06/msg00143.php That bug only occurs when SSL is enabled. So it may not be related. But it wouldn't surprise me if it's related. Russell. -- 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] database question
So the answer is you've got something that's gone hog-wild on creating large objects and not deleting them; or maybe the application *is* deleting them but pg_largeobject isn't getting vacuumed. regards, tom lane Hi all, thanks for the advice. I ran the script for largefiles and the largest is 3Gb followed by 1Gb then followed by another 18 files that total about 3Gb between them. So about 7Gb in total of a 100Gb partition that has 99Gb used. All this is in the data/base/16450 directory in these large 1Gb files. If I look in the logs for Postgres I can see a vacuum happening every 20 minutes, in that it says autovacuum: processing database db name but nothing else. How do I know if the vacuum is actually doing anything? What is pg_largeobjects and what can I check with it (sorry did say I was a real novice). Really appreciate your help guys. John -- 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] Counting unique rows as an aggregate.
On Sep 29, 11:25 pm, [EMAIL PROTECTED] (Scott Marlowe) wrote: However, this is starting to become too slow (as there are about 10 of these queries), and therefore I need to write an aggregate function which lets me do: SELECT count_unique(make), count_unique(color) from table WHERE criteria; After reading about aggregate functions, this should be possible, as long as I can use a dictionary/hashmap type for the stateSTYPE argument. This might be a nice fit for materialized views. While they're not built in, PostgreSQL's extensibility allows you to build them prettily easily. http://jonathangardner.net/tech/w/PostgreSQL/Materialized_Views Materialized views won't work as the criteria used to narrow the search is very arbritrary and rarely repeated, and as the count's depend on the current result set, they would offer a miniscule increase in speed, only as the row width would be a little smaller. -- 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] Counting unique rows as an aggregate.
On Sep 30, 2:36 am, [EMAIL PROTECTED] (Tom Lane) wrote: SELECT count_unique(make), count_unique(color) from table WHERE criteria; I must be missing something, because I don't see why you couldn't do SELECT count(distinct make), count(distinct color) from table WHERE criteria; I didn't explain well, I want the count of each distinct value in a column, eg, if the color column has 50 rows, 20x'red', 10x'green', 20x'blue' - it will give me those results. SELECT count(distinct color) would return 3 - which is the count of distinct values, which is not what I want. -- 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] Free Cache Memory (Linux) and Postgresql
On Tue, Sep 30, 2008 at 3:33 AM, Denis Gasparin [EMAIL PROTECTED] wrote: Hi. I'm evaluating to issue the drop_caches kernel command (echo 3 /proc/sys/vm/drop_caches) in order to free unused pagecache, directory entries and inodes. I'm thinking to schedule the command during low load moments after forcing a sync command. I wonder if this can cause pgsql problems of any kind. Any idea? Yes, it can. Postgres relies heavily on the OS' file system cache, if you wipe it out, you're going to have quite an I/O storm on a large database. What are you trying to accomplish? By itself, sync will flush all dirty file system blocks to disk and leave them in memory. -- Jonah H. Harris, Senior DBA myYearbook.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 Cache
On Mon, Sep 29, 2008 at 05:53:02PM -0400, Greg Smith wrote: On Mon, 29 Sep 2008, Sam Mason wrote: echo 3 | sudo tee /proc/sys/vm/drop_caches As an aside, it would be nicer if there was a more appropriate program than tee but I've yet to find one. What are you trying to accomplish here that tee isn't quite right for? tee works for how it's being used for here; it's just wired into my brain for tasks other than writing to files with different privileges. I ended up with a similar invocation when I was solving a similar problem a couple of months ago (not sure how I'd not hit it before then) and kept searching for something more natural. I've just done a search and using tee here appears to be somewhat standard, so I guess that answers my question. Sorry for the noise! Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Free Cache Memory (Linux) and Postgresql
Denis Gasparin [EMAIL PROTECTED] writes: I'm evaluating to issue the drop_caches kernel command (echo 3 /proc/sys/vm/drop_caches) in order to free unused pagecache, directory entries and inodes. Why in the world would you think that's a good idea? 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] Re: Is there a parameter to tell postgresql to not attempt to open an IPv6 socket?
On Mon, Sep 29, 2008 at 06:41:33AM -0700, [EMAIL PROTECTED] wrote: On 16 sep, 23:04, [EMAIL PROTECTED] (Andrew Sullivan) wrote: Specify the specific TCP/IP interfaces in the postmaster.conf file. I have the same pb. I have looked for a postmaster.conf file but there Doh! Sorry, that should have been postgresql.conf. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.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] Counting unique rows as an aggregate.
I must be missing something, because I don't see why you couldn't do SELECT count(distinct make), count(distinct color) from table WHERE criteria; I didn't explain well, I want the count of each distinct value in a column, eg, if the color column has 50 rows, 20x'red', 10x'green', 20x'blue' - it will give me those results. SELECT count(distinct color) would return 3 - which is the count of distinct values, which is not what I want. maybe this simple example helps you: # create table colors (color text); CREATE TABLE # insert into colors select 'red'; INSERT 0 1 # insert into colors select 'red'; INSERT 0 1 # insert into colors select 'green'; INSERT 0 1 # insert into colors select 'yellow'; INSERT 0 1 # insert into colors select 'yellow'; INSERT 0 1 # insert into colors select 'yellow'; INSERT 0 1 # select count(1), color from colors group by color; count | color ---+ 2 | red 3 | yellow 1 | green (3 rows) Jan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Alias name from subquery
Hi, I would like to set an alias name for a column from a subquery, i.e. something like this: SELECT entry AS (SELECT name from colnames WHERE id=1) from entries ; Obviously it doesn't work _this_ way, but is there _any_ way to do it? Kind regards, Felix -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Standalone Windows Installation
Hi. I want to use Postgres without installation. The problem is the dependencies on the Visual C dll (msvcrt). As far as I know it would be possible to include a manifest file (only for libpq.dll?) and bundle the dlls from c:\windows\WinSxS into the bin directory of Postgres. Is this correct? Does anybody have (tested) such a manifest file? Jörn -- 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] Free Cache Memory (Linux) and Postgresql
Tom Lane ha scritto: Denis Gasparin [EMAIL PROTECTED] writes: I'm evaluating to issue the drop_caches kernel command (echo 3 /proc/sys/vm/drop_caches) in order to free unused pagecache, directory entries and inodes. Why in the world would you think that's a good idea? regards, tom lane We see cached memory growing on constant base, even if there are no connections to database. We have some tables that are truncated and reloaded with updated data on regular basis (3,4 days). It seems like postgres or the operating system (linux) is keeping in cache that old data even if it has been deleted. We're searching a way to free that memory without shutting down pgsql. Thank you for your help, Denis -- 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] Free Cache Memory (Linux) and Postgresql
On Tue, Sep 30, 2008 at 7:51 AM, Denis Gasparin [EMAIL PROTECTED] wrote: Tom Lane ha scritto: Denis Gasparin [EMAIL PROTECTED] writes: I'm evaluating to issue the drop_caches kernel command (echo 3 /proc/sys/vm/drop_caches) in order to free unused pagecache, directory entries and inodes. Why in the world would you think that's a good idea? regards, tom lane We see cached memory growing on constant base, even if there are no connections to database. We have some tables that are truncated and reloaded with updated data on regular basis (3,4 days). It seems like postgres or the operating system (linux) is keeping in cache that old data even if it has been deleted. We're searching a way to free that memory without shutting down pgsql. You're fixing a problem that isn't there. The OS allocates cache (the cache shown under top). And it uses all free memory it can get its hands on to do so. The second postgres or any other program asks for memory, the kernel throws away the oldest bits of cache to provide memory to the application. What you are doing is counter-productive. -- 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] Free Cache Memory (Linux) and Postgresql
If its the OS cache the kernel ought to free the memory when there is something else worth caching. Its not a big deal if the cache is full so long as the system still performs well. On Tue, Sep 30, 2008 at 9:51 AM, Denis Gasparin [EMAIL PROTECTED] wrote: Tom Lane ha scritto: Denis Gasparin [EMAIL PROTECTED] writes: I'm evaluating to issue the drop_caches kernel command (echo 3 /proc/sys/vm/drop_caches) in order to free unused pagecache, directory entries and inodes. Why in the world would you think that's a good idea? regards, tom lane We see cached memory growing on constant base, even if there are no connections to database. We have some tables that are truncated and reloaded with updated data on regular basis (3,4 days). It seems like postgres or the operating system (linux) is keeping in cache that old data even if it has been deleted. We're searching a way to free that memory without shutting down pgsql. Thank you for your help, Denis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] MySQL to Postgresql schema conversion
There are a number of mysql to postgresql converters available, but many of them have significant shortcomings. Has anyone found a tool that works well? I am trying to convert a couple of relatively large, public schema to postgresql. Thanks, Sean -- 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] Alias name from subquery
Hi Jeffrey, Thanks for your quick response! Hoover, Jeffrey wrote: select (SELECT name from colnames WHERE id=1) as entry from entries; I think, I should have been a bit clearer in what I need: I've got two tables, colnames and entries: test=# SELECT * from colnames; id | name +-- 1 | col1 (1 row) test=# SELECT entry from entries; entry first second third (3 rows) I would like to get the column name entry replaced by an alias col1, just like this: test=# SELECT entry as col1 from entries; col1 first second third (3 rows) _But_, I don't want to give the alias explicitely, instead it should be taken from a second table 'colnames', i.e. something like the line I sent in my initial mail. Any idea? Thanks again, Felix BTW, here's what I get from your command: test=# select (SELECT name from colnames WHERE id=1) as entry from entries; entry --- col1 col1 col1 (3 rows) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Alias name from subquery
On 30/09/2008 14:21, Felix Homann wrote: I would like to set an alias name for a column from a subquery, i.e. something like this: SELECT entry AS (SELECT name from colnames WHERE id=1) from entries ; select entry from (select name from colnames where id = 1) as entry_with_different_name; ...maybe? Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Alias name from subquery
Raymond O'Donnell wrote: select entry from (select name from colnames where id = 1) as entry_with_different_name; ...maybe? Thanks Ray! No, entry_with_different_name should be the result of SELECT name FROM colnames WITH id=1. Kind regards, Felix -- 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] Alias name from subquery
On Tue, Sep 30, 2008 at 03:21:53PM +0200, Felix Homann wrote: I would like to set an alias name for a column from a subquery, i.e. something like this: SELECT entry AS (SELECT name from colnames WHERE id=1) from entries ; Obviously it doesn't work _this_ way, but is there _any_ way to do it? Generate the SQL correctly in the first place? :) I think you may be trying to solve the wrong problem, what are you really trying to do? Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Alias name from subquery
On Tue, Sep 30, 2008 at 8:29 AM, Felix Homann [EMAIL PROTECTED] wrote: _But_, I don't want to give the alias explicitely, instead it should be taken from a second table 'colnames', i.e. something like the line I sent in my initial mail. Any idea? Then you'll have to build a query in plpgsql and execute it to get what you want. You can't do what you're trying to do in normal SQL. At least I don't think it's possible. -- 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] Alias name from subquery
From what i know, Aliases are literals, they are not variables, hence they cannot be derived from something. you can derive them outside the scope of normal SQL by using functions or shell scripts, but probably not inside an SQL context. rgds, dotyet On Tue, Sep 30, 2008 at 12:10 PM, Scott Marlowe [EMAIL PROTECTED]wrote: On Tue, Sep 30, 2008 at 8:29 AM, Felix Homann [EMAIL PROTECTED] wrote: _But_, I don't want to give the alias explicitely, instead it should be taken from a second table 'colnames', i.e. something like the line I sent in my initial mail. Any idea? Then you'll have to build a query in plpgsql and execute it to get what you want. You can't do what you're trying to do in normal SQL. At least I don't think it's possible. -- 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] MySQL to Postgresql schema conversion
On Tue, Sep 30, 2008 at 10:08 AM, Sean Davis [EMAIL PROTECTED] wrote: There are a number of mysql to postgresql converters available, but many of them have significant shortcomings. Has anyone found a tool that works well? I am trying to convert a couple of relatively large, public schema to postgresql. It can for the most part be done with text replacement with a good editor, or use sed. It's not that difficult. The data is even easier...a lot of times you can import a mysql (data only) dump directly into postgresql if you pass the right options to mysqldump. There are several tools that automate this process. One such tool is DTS from microsoft. Lately though I see less and less value in things like this when the same thing can be done with regex/sed. merlin -- 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] Can't cast from char to integer...
On Mon, Sep 29, 2008 at 8:02 PM, Mike Diehl [EMAIL PROTECTED] wrote: That fixed it. If you are ever in Albuquerque, NM., let me know. I'll be happy to buy you a beer. Tom probably has enough beers coming to him that he could found a new software company with money from returning the bottles for the deposit. merlin -- 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] MySQL to Postgresql schema conversion
On Tue, Sep 30, 2008 at 10:08 AM, Sean Davis [EMAIL PROTECTED] wrote: There are a number of mysql to postgresql converters available, but many of them have significant shortcomings. Has anyone found a tool that works well? I am trying to convert a couple of relatively large, public schema to postgresql. I started playing with sqlalchemy (python) which can reflect a schema to python objects. Those objects can then be used to instantiate another schema in a different database dialect. Works like a charm after modifying a couple of column names. It mirrors about 4000 tables in about 45 seconds (of course, without the data). Sean -- 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] MySQL to Postgresql schema conversion
On Tue, Sep 30, 2008 at 12:48 PM, Sean Davis [EMAIL PROTECTED] wrote: On Tue, Sep 30, 2008 at 10:08 AM, Sean Davis [EMAIL PROTECTED] wrote: There are a number of mysql to postgresql converters available, but many of them have significant shortcomings. Has anyone found a tool that works well? I am trying to convert a couple of relatively large, public schema to postgresql. I started playing with sqlalchemy (python) which can reflect a schema to python objects. Those objects can then be used to instantiate another schema in a different database dialect. Works like a charm after modifying a couple of column names. It mirrors about 4000 tables in about 45 seconds (of course, without the data). Does it get all the various constraints and stuff (if any)? Simple field to field copy techniques only tends to work if the database only uses a small subset of common features. Great for you if it works though. merlin -- 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] MySQL to Postgresql schema conversion
On Tue, Sep 30, 2008 at 1:18 PM, Merlin Moncure [EMAIL PROTECTED] wrote: On Tue, Sep 30, 2008 at 12:48 PM, Sean Davis [EMAIL PROTECTED] wrote: On Tue, Sep 30, 2008 at 10:08 AM, Sean Davis [EMAIL PROTECTED] wrote: There are a number of mysql to postgresql converters available, but many of them have significant shortcomings. Has anyone found a tool that works well? I am trying to convert a couple of relatively large, public schema to postgresql. I started playing with sqlalchemy (python) which can reflect a schema to python objects. Those objects can then be used to instantiate another schema in a different database dialect. Works like a charm after modifying a couple of column names. It mirrors about 4000 tables in about 45 seconds (of course, without the data). Does it get all the various constraints and stuff (if any)? Simple field to field copy techniques only tends to work if the database only uses a small subset of common features. Great for you if it works though. To the extent that the MySQL databases used anything interesting (defaults, basically), it seems to, yes. I have used it for other projects as an ORM and it seems to support pretty much anything I can dream up on the postgres side for DDL. Sean -- 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] Can't cast from char to integer...
On Tue, Sep 30, 2008 at 10:46 AM, Merlin Moncure [EMAIL PROTECTED] wrote: On Mon, Sep 29, 2008 at 8:02 PM, Mike Diehl [EMAIL PROTECTED] wrote: That fixed it. If you are ever in Albuquerque, NM., let me know. I'll be happy to buy you a beer. Tom probably has enough beers coming to him that he could found a new software company with money from returning the bottles for the deposit. Tom will never lack for pizza or beer when he's visiting Denver... -- 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] Alias name from subquery
Hi Sam,
Sam Mason wrote:
I think you may be trying to solve the wrong problem, what are you
really trying to do?
Here you go. I have some tables created like this:
CREATE TABLE player(
id INTEGER PRIMARY KEY,
name varchar(20)
);
CREATE TABLE transfer(
id SERIAL PRIMARY KEY,
fromID INTEGER REFERENCES player(id),
toID INTEGER REFERENCES player(id),
amount numeric
);
Now, let's fill them with some data:
INSERT INTO player VALUES ('1', 'Peter'), ('2','David'), ('3','Fritz');
INSERT INTO transfer(fromID, toID, amount) VALUES ('1','2','3'), ('1',
'3', '1'), ('2','1','60');
I would now like to have something like a view that transforms the table
transfer from this:
test=# SELECT * from transfer;
id | fromid | toid | amount
++--+
1 | 1 |2 | 3
2 | 1 |3 | 1
3 | 2 |1 | 60
into this:
id | Peter | David | Fritz | ...even more Names from player table
---+---+---+---+-
1 |-3 | 3 | 0 | 0
2 |-1 | 0 | 1 | 0
3 |60 | -60 | 0 | 0
In other words, I would like to have a named column for every Name in
the player table. I _can_ create such a view manually if I know each
player.name beforehand, but I don't know how to automate it for any
given number of players. (Think of something like a sparse interaction
matrix representation.)
Maybe it's the wrong problem I'm trying to solve, but at least I would
like to know whether it's possible or not.
Kind regards,
Felix
--
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] Alias name from subquery
On Tue, Sep 30, 2008 at 11:45 AM, Felix Homann [EMAIL PROTECTED] wrote: Hi Sam, In other words, I would like to have a named column for every Name in the player table. I _can_ create such a view manually if I know each player.name beforehand, but I don't know how to automate it for any given number of players. (Think of something like a sparse interaction matrix representation.) Ahhh, you might do better with crosstab functions then. look up the tablefunc contrib module. -- 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] Can't cast from char to integer...
On Tuesday 30 September 2008 10:46:46 am Merlin Moncure wrote: On Mon, Sep 29, 2008 at 8:02 PM, Mike Diehl [EMAIL PROTECTED] wrote: That fixed it. If you are ever in Albuquerque, NM., let me know. I'll be happy to buy you a beer. Tom probably has enough beers coming to him that he could found a new software company with money from returning the bottles for the deposit. merlin Yes, and rightfully so. Offer still stands. Take care and have fun. -- Mike Diehl -- 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] Alias name from subquery
I think you should look at crosstab contrib module.
Regards,
Taras Kopets
On 9/30/08, Felix Homann [EMAIL PROTECTED] wrote:
Hi Sam,
Sam Mason wrote:
I think you may be trying to solve the wrong problem, what are you
really trying to do?
Here you go. I have some tables created like this:
CREATE TABLE player(
id INTEGER PRIMARY KEY,
name varchar(20)
);
CREATE TABLE transfer(
id SERIAL PRIMARY KEY,
fromID INTEGER REFERENCES player(id),
toID INTEGER REFERENCES player(id),
amount numeric
);
Now, let's fill them with some data:
INSERT INTO player VALUES ('1', 'Peter'), ('2','David'), ('3','Fritz');
INSERT INTO transfer(fromID, toID, amount) VALUES ('1','2','3'), ('1',
'3', '1'), ('2','1','60');
I would now like to have something like a view that transforms the table
transfer from this:
test=# SELECT * from transfer;
id | fromid | toid | amount
++--+
1 | 1 |2 | 3
2 | 1 |3 | 1
3 | 2 |1 | 60
into this:
id | Peter | David | Fritz | ...even more Names from player table
---+---+---+---+-
1 |-3 | 3 | 0 | 0
2 |-1 | 0 | 1 | 0
3 |60 | -60 | 0 | 0
In other words, I would like to have a named column for every Name in
the player table. I _can_ create such a view manually if I know each
player.name beforehand, but I don't know how to automate it for any
given number of players. (Think of something like a sparse interaction
matrix representation.)
Maybe it's the wrong problem I'm trying to solve, but at least I would
like to know whether it's possible or not.
Kind regards,
Felix
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent from Gmail for mobile | mobile.google.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Why Does UPDATE Take So Long?
Working with PG 8.1 I'm trying to update a char(4) column, and it's taking a very long time; 15 minutes so far and no end in sight. From the explain, it doesn't seem like it should take that long, and this column is not indexed. Sure, there's 2.7 million records but it only takes a few minutes to scan the whole file. Is there some special overhead I should be aware of with an UPDATE? I VACUUMed and ANALYZEd first, too. Or am I just expecting too much? Here's the explain: explain UPDATE farms SET prog_year='2007'; QUERY PLAN Seq Scan on farms (cost=0.00..59144.07 rows=2772207 width=54) (1 row) TIA, - Bill Thoen -- 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] Why Does UPDATE Take So Long?
On Tuesday 30 September 2008, Bill Thoen [EMAIL PROTECTED] wrote: Working with PG 8.1 I'm trying to update a char(4) column, and it's taking a very long time; 15 minutes so far and no end in sight. From the explain, it doesn't seem like it should take that long, and this column is not indexed. Sure, there's 2.7 million records but it only takes a few minutes to scan the whole file. Is there some special overhead I should be aware of with an UPDATE? I VACUUMed and ANALYZEd first, too. update creates new rows for all affected rows. If the table is indexed, it creates new index rows for all affected rows in every index. Slow updates is a common PostgreSQL complaint. -- Alan -- 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] Why Does UPDATE Take So Long?
Bill Thoen [EMAIL PROTECTED] schrieb: Working with PG 8.1 I'm trying to update a char(4) column, and it's taking a very long time; 15 minutes so far and no end in sight. From the explain, it doesn't seem like it should take that long, and this column is not indexed. Sure, there's 2.7 million records but it only takes a few minutes to scan the whole file. Is there some special overhead I should be aware of with an UPDATE? I VACUUMed and ANALYZEd first, too. Or am I just expecting too much? Here's the explain: explain UPDATE farms SET prog_year='2007'; QUERY PLAN Seq Scan on farms (cost=0.00..59144.07 rows=2772207 width=54) (1 row) Please provide us an EXPLAIN ANALYSE. But without a WHERE-condition a seq-scan are logical, and PG has to rewrite the whole table and the transaction-log. 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] Alias name from subquery
Thanks to Scott and Taras for pointing me to the crosstab functions. I only had a quick look but they seem very helpful! Kind regards, Felix -- 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] Why Does UPDATE Take So Long?
On Tue, 2008-09-30 at 13:00 -0600, Bill Thoen wrote: Working with PG 8.1 I'm trying to update a char(4) column, and it's taking a very long time; 15 minutes so far and no end in sight. From the explain, it doesn't seem like it should take that long, and this column is not indexed. Sure, there's 2.7 million records but it only takes a few minutes to scan the whole file. Is there some special overhead I should be aware of with an UPDATE? I VACUUMed and ANALYZEd first, too. In PostgreSQL, think of an UPDATE as an INSERT+DELETE. It's got to write the new versions of the tuples, and it has to keep the old versions until there are no more transactions that might reference those old versions. Imagine if you canceled the query halfway through, for example. Also, it has to create new index entries for the same reason, which is expensive. There are some optimizations in 8.3 for when the same tuple gets updated many times, but that won't help you in this case. Regards, Jeff Davis -- 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] Why Does UPDATE Take So Long?
On Tue, Sep 30, 2008 at 1:00 PM, Bill Thoen [EMAIL PROTECTED] wrote: Working with PG 8.1 I'm trying to update a char(4) column, and it's taking a very long time; 15 minutes so far and no end in sight. From the explain, it doesn't seem like it should take that long, and this column is not indexed. Sure, there's 2.7 million records but it only takes a few minutes to scan the whole file. Is there some special overhead I should be aware of with an UPDATE? I VACUUMed and ANALYZEd first, too. Or am I just expecting too much? The problem is that on older versions of pgsql, the db had to update each index for each row updated as well as the rows. The latest version, with a low enough fill factor, can update non-indedexed fields by using the free space in each page and not have to hit the indexes. But on 8.1 you don't get that optimization. -- 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] Why Does UPDATE Take So Long?
Doesn't look like that's the problem. I moved my table over to another Linux box running PG 8.3 and update performance was pretty bad there as well. In the time that PG 8.3 was struggling with update there I created a copy of my table on my PG 8.1 machine and inserted all columns with one containing the altered values I wanted and that took less than two minutes. Meanwhile, a half-hour later, my PG 8.3 machine was still thrashing away trying to update that one column that's not even part of any index.. Something is really wrong with UPDATE in PostgreSQL I think. Scott Marlowe wrote: On Tue, Sep 30, 2008 at 1:00 PM, Bill Thoen [EMAIL PROTECTED] wrote: Working with PG 8.1 I'm trying to update a char(4) column, and it's taking a very long time; 15 minutes so far and no end in sight. From the explain, it doesn't seem like it should take that long, and this column is not indexed. Sure, there's 2.7 million records but it only takes a few minutes to scan the whole file. Is there some special overhead I should be aware of with an UPDATE? I VACUUMed and ANALYZEd first, too. Or am I just expecting too much? The problem is that on older versions of pgsql, the db had to update each index for each row updated as well as the rows. The latest version, with a low enough fill factor, can update non-indedexed fields by using the free space in each page and not have to hit the indexes. But on 8.1 you don't get that optimization. -- 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] Why Does UPDATE Take So Long?
In response to Bill Thoen [EMAIL PROTECTED]: Doesn't look like that's the problem. I moved my table over to another Linux box running PG 8.3 and update performance was pretty bad there as well. In the time that PG 8.3 was struggling with update there I created a copy of my table on my PG 8.1 machine and inserted all columns with one containing the altered values I wanted and that took less than two minutes. Meanwhile, a half-hour later, my PG 8.3 machine was still thrashing away trying to update that one column that's not even part of any index.. Something is really wrong with UPDATE in PostgreSQL I think. That's an interesting theory, although it's completely wrong and founded in ridiculosity. If something were really wrong with UPDATE in every version of PostgreSQL, you'd be reading about it on the mailing lists, and you won't. What I suspect is that the typical tuning advice applies here. I don't see any information about your configuration or your hardware setup. * What are shared_buffers set at? * What do the checkpoint configs look like? * In general, what does your postgresql.conf look like, how much tuning have you done? * What is your hardware setup? You're not running RAID 5 are you? -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [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] Why Does UPDATE Take So Long?
Bill Moran wrote: What I suspect is that the typical tuning advice applies here. I don't see any information about your configuration or your hardware setup. * What are shared_buffers set at? * What do the checkpoint configs look like? * In general, what does your postgresql.conf look like, how much tuning have you done? * What is your hardware setup? You're not running RAID 5 are you? Also, how many indexes does this table have? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] Why Does UPDATE Take So Long?
Sorry for the hyperbole; I should have qualified that ridiculous statement with ...on my machines. No doubt the problem has something to do with configuration, because I don't know much about that. One of my machines is running PG 8.1 on Linux Fedora Core 5. It's got an AMD 64bit CPU with a GB RAM and plenty of normal disk space (not running RAID 5). The other machine is running Linux FC9 and PG 8.3. It's got a i686 cpu with a GB RAM and also not using RAID. Since I don't understand much about configuring PostgreSQL, both of these machines use the default PostgreSQL configuration. I figured that it was optimized for general use but maybe since my files are large-ish (in the low multi-million record ranges) mayb ethta doesn't qualify as general use. Anyway, here's the configuration settings you mentioned. Shared_buffers are = 1000 #checkpoint_segments = 3 #checkpoint_timeout = 300 #checkpoint_warning = 30 What should I be looking for in the configuration to improve UPDATE performance? Thanks, - Bill Thoen Bill Moran wrote: In response to Bill Thoen [EMAIL PROTECTED]: Doesn't look like that's the problem. I moved my table over to another Linux box running PG 8.3 and update performance was pretty bad there as well. In the time that PG 8.3 was struggling with update there I created a copy of my table on my PG 8.1 machine and inserted all columns with one containing the altered values I wanted and that took less than two minutes. Meanwhile, a half-hour later, my PG 8.3 machine was still thrashing away trying to update that one column that's not even part of any index.. Something is really wrong with UPDATE in PostgreSQL I think. That's an interesting theory, although it's completely wrong and founded in ridiculosity. If something were really wrong with UPDATE in every version of PostgreSQL, you'd be reading about it on the mailing lists, and you won't. What I suspect is that the typical tuning advice applies here. I don't see any information about your configuration or your hardware setup. * What are shared_buffers set at? * What do the checkpoint configs look like? * In general, what does your postgresql.conf look like, how much tuning have you done? * What is your hardware setup? You're not running RAID 5 are you? -- 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] Why Does UPDATE Take So Long?
On Tue, Sep 30, 2008 at 2:51 PM, Bill Thoen [EMAIL PROTECTED] wrote: Doesn't look like that's the problem. I moved my table over to another Linux box running PG 8.3 and update performance was pretty bad there as well. In the time that PG 8.3 was struggling with update there I created a copy of my table on my PG 8.1 machine and inserted all columns with one containing the altered values I wanted and that took less than two minutes. Meanwhile, a half-hour later, my PG 8.3 machine was still thrashing away trying to update that one column that's not even part of any index.. Something is really wrong with UPDATE in PostgreSQL I think. You'll remember I mentioned a low fill factor. With a 100% fillfactor you'll get no advantage from 8.3 The default tuning in postgresql allows it to run reasonably well on things like laptops and desktops. It's impossible to deliver it ready for a 32 CPU 200 drive megaserver with the same configuration file you'd use for a laptop. Do a quick google search on postgresql performance tuning and you'll turn up quite a few sites and wikis on it. The 5 minute version: set shared_buffers = 1/4 memory. set work_mem to something like 8 megs. Turn on the autovacuum daemon -- 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] Free Cache Memory (Linux) and Postgresql
On Tue, Sep 30, 2008 at 03:51:44PM +0200, Denis Gasparin wrote: It seems like postgres or the operating system (linux) is keeping in cache that old data even if it has been deleted. Just remember: free memory is memory you paid for and are not using == wasted memory. The OS knows damn well it's not important and will throw it out if necessary, but it costs nothing to keep it. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] Why Does UPDATE Take So Long?
On Tuesday 30 September 2008, Bill Thoen [EMAIL PROTECTED] wrote: Sorry for the hyperbole; I should have qualified that ridiculous statement with ...on my machines. No doubt the problem has something to do with configuration, because I don't know much about that. One of my machines is running PG 8.1 on Linux Fedora Core 5. It's got an AMD 64bit CPU with a GB RAM and plenty of normal disk space You'll have to expand on the disk space thing ... the problem with updates is all the random I/O when adding tuples to all the indexes. A good RAID controller with write-back cache makes updates a lot less painful. -- Alan -- 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] Why Does UPDATE Take So Long?
Alvaro Herrera wrote: Bill Moran wrote: What I suspect is that the typical tuning advice applies here. I don't see any information about your configuration or your hardware setup. * What are shared_buffers set at? * What do the checkpoint configs look like? * In general, what does your postgresql.conf look like, how much tuning have you done? * What is your hardware setup? You're not running RAID 5 are you? Also, how many indexes does this table have? Two, but the column I'm updating isn't included in either one of them. -- 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] Why Does UPDATE Take So Long?
On Tue, 2008-09-30 at 16:34 -0600, Bill Thoen wrote: Also, how many indexes does this table have? Two, but the column I'm updating isn't included in either one of them. Even if the column is not indexed, when a new row is created (which is the case with UPDATE) a new index entry must be made in each index to point to the new row. Regards, Jeff Davis -- 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] Why Does UPDATE Take So Long?
On Tue, Sep 30, 2008 at 4:37 PM, Jeff Davis [EMAIL PROTECTED] wrote: On Tue, 2008-09-30 at 16:34 -0600, Bill Thoen wrote: Also, how many indexes does this table have? Two, but the column I'm updating isn't included in either one of them. Even if the column is not indexed, when a new row is created (which is the case with UPDATE) a new index entry must be made in each index to point to the new row. Unless you're: running 8.3 or later AND have enough free space for the new tuple to go in the same page. for instance here's a sample from my db at work: select n_tup_upd, n_tup_hot_upd from pg_stat_user_tables where schemaname='public' order by n_tup_hot_upd desc limit 20; n_tup_upd | n_tup_hot_upd ---+--- 52872193 | 5665884 4635216 | 3876594 264194 |261693 159171 |153360 242383 | 75591 97962 | 72665 86800 | 66914 57300 | 56013 284929 | 50079 43411 | 37527 43283 | 33285 30657 | 28132 31705 | 22572 26358 | 18495 19296 | 18411 22299 | 17065 16343 | 15981 23311 | 15748 13575 | 13330 12808 | 12536 If you notice some of those tables have well over 75% of the updates are HOT.Our load dropped from 15 or 20 to 1 or 2 going to 8.3. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Free Cache Memory (Linux) and Postgresql
On Tue, Sep 30, 2008 at 4:02 PM, Martijn van Oosterhout [EMAIL PROTECTED] wrote: On Tue, Sep 30, 2008 at 03:51:44PM +0200, Denis Gasparin wrote: It seems like postgres or the operating system (linux) is keeping in cache that old data even if it has been deleted. Just remember: free memory is memory you paid for and are not using == wasted memory. The OS knows damn well it's not important and will throw it out if necessary, but it costs nothing to keep it. free total used free sharedbuffers cached Mem: 33031252 249018248129428 0 380492 21991100 sh. don't tell me boss we've got 8 gig free in the db servers, he'll want to re-purpose it. Still 20+ Gig of cache is awfully nice. -- 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] MySQL to Postgresql schema conversion
Sean Davis wrote: There are a number of mysql to postgresql converters available, but many of them have significant shortcomings. Has anyone found a tool that works well? I am trying to convert a couple of relatively large, public schema to postgresql. I couldn't find anything either but ended up using a pretty simple approach: - table only dump from mysql (ie no data) - convert is using sed/perl/whatever takes your fancy - do a csv type dump from mysql (select into outfile) (1/3 of the way down on http://dev.mysql.com/doc/refman/5.0/en/select.html). - use copy to import the data into postgres (http://www.postgresql.org/docs/8.3/interactive/sql-copy.html) That of course assumes you don't have to do any data munging in the middle (eg different formats for date/time fields). -- Postgresql php tutorials http://www.designmagick.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: [ADMIN] [GENERAL] Functions
Hello, If you want to get the value of a autogenerated column it's better to use RETURNING insert into something returning primary key... Best Regards, Rafael Domiciano Postgres DBA 2008/9/15 Scott Marlowe [EMAIL PROTECTED] On Mon, Sep 15, 2008 at 11:53 AM, c k [EMAIL PROTECTED] wrote: If I have a function having: begin insert into something ... select max(primary key) from something.. end; does the second statement within a function can view the results after execution of first statement to get max(P.K.)? Yep. -- Sent via pgsql-admin mailing list ([EMAIL PROTECTED]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[GENERAL] Has anyone built pgbash-7.3 against postgreSQL-8.3?
I'm curious about pgbash. I've taken a look at the website here: http://www.psn.co.jp/PostgreSQL/pgbash/index-e.html According to the history on the main home page, the pgbash package was last updated in 2003, ie: 2003.02.11 : pgbash-7.3 released (for PostgreSQL-7.3 and bash-2.05a). I've made a start to build that package against postgresql-8.3 (on macports). The build failed with: cc -O2 -I/opt/local/include/postgresql83 -c exec_sql_main.c exec_sql_main.c:130: error: static declaration of 'sqlca' follows non-static declaration sqlca.h:44: error: previous declaration of 'sqlca' was here make[1]: *** [exec_sql_main.o] Error 1 make: *** [../exec_sql/exec_sql_init.o] Error 1 It would be nice to debug this, if anyone can help? I wonder about the general status of pgbash among the postgres community - is it useful and is it still used? Perhaps something else replaced it and the development work stopped in 2003 in favor of something else? Thanks, Darren -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Running 2 versions of postgres on the same server at the same time ???
Hi: I have v8.2.5 running on my server and serving my DB to my customers. I also have v8.3.4. I created a v8.3.4 instance (initdb) but haven't started anything yet (no pg_ctl start yet). I want to test v8.3.4 on the server while keeping v8.2.5 running and serving my customers at the same time. Wil lthere be any problems if I start v8.3.4 while v8.2.5 is running? Thanks -dave
Re: [GENERAL] Running 2 versions of postgres on the same server at the same time ???
On Tue, Sep 30, 2008 at 8:21 PM, Gauthier, Dave [EMAIL PROTECTED] wrote: Hi: I have v8.2.5 running on my server and serving my DB to my customers. I also have v8.3.4. I created a v8.3.4 instance (initdb) but haven't started anything yet (no pg_ctl start yet). I want to test v8.3.4 on the server while keeping v8.2.5 running and serving my customers at the same time. Wil lthere be any problems if I start v8.3.4 while v8.2.5 is running? I've got 8.1, 8.2 and 8.3 running on my laptop all at the same time. If you're running debian it's super simple, the setup is fully automagic. Since you've already got it initdbed, all you need to do is make sure it's set to come up on a different port and you're set. Since the space around 5432 is pretty empty for reserved services, it's typical to just use the next port and so on. 5433, 5434, 5435 and so on. Also, you might have to increase your shm settings in your kernel to provide enough shared memory for both instances. It's actually a pretty common practice to install two versions for migration purposes. -- 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] Counting unique rows as an aggregate.
--- On Tue, 9/30/08, r_musta [EMAIL PROTECTED] wrote: From: r_musta [EMAIL PROTECTED] Subject: Re: [GENERAL] Counting unique rows as an aggregate. To: pgsql-general@postgresql.org Date: Tuesday, September 30, 2008, 6:55 AM On Sep 30, 2:36 am, [EMAIL PROTECTED] (Tom Lane) wrote: SELECT count_unique(make), count_unique(color) from table WHERE criteria; I must be missing something, because I don't see why you couldn't do SELECT count(distinct make), count(distinct color) from table WHERE criteria; I didn't explain well, I want the count of each distinct value in a column, eg, if the color column has 50 rows, 20x'red', 10x'green', 20x'blue' - it will give me those results. SELECT count(distinct color) would return 3 - which is the count of distinct values, which is not what I want. SELECT count(color),color from table group by color -- 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] Standalone Windows Installation
Jörn Heid wrote: Hi. I want to use Postgres without installation. The problem is the dependencies on the Visual C dll (msvcrt). As far as I know it would be possible to include a manifest file (only for libpq.dll?) and bundle the dlls from c:\windows\WinSxS into the bin directory of Postgres. That should work fine according to my understanding of the Windows dynamic linker's behavour. I'm actually not even sure you'll need the manifest, in that IIRC it should be embedded in the DLL if it was built with a recent VC++. You should also be able to bundle a private copy of the required VC++ runtime in the bin directory; it does not need to be installed in WinSxS. I've done this with small Windows binaries I've distributed at various points with no issues. If you do this, make sure you do NOT put the PostgreSQL bin directory on the PATH, as you may confuse any parallel installation of the official PostgreSQL distribution. You should also use a different port to the default, and if practical not listen on non-loopback TCP/IP interfaces. -- Craig Ringer -- 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] Running 2 versions of postgres on the same server at the same time ???
Scott Marlowe [EMAIL PROTECTED] writes: It's actually a pretty common practice to install two versions for migration purposes. Also, all the core developers routinely run multiple versions for the purpose of testing back-branch bug fixes. The machine I'm typing this on has, hm [ ... ps | grep ... ] ten postmasters running. They don't have enough shared memory apiece to perform super-well ... but the point is that you can be entirely sure that it works. Just install each version in its own directory, point it at its own PGDATA directory, and assign it its own port number. 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] Standalone Windows Installation
Jörn Heid wrote: Hi. I want to use Postgres without installation. The problem is the dependencies on the Visual C dll (msvcrt). As far as I know it would be possible to include a manifest file (only for libpq.dll?) and bundle the dlls from c:\windows\WinSxS into the bin directory of Postgres. Is this correct? Does anybody have (tested) such a manifest file? Jörn You just need to copy the contents of the redist/x86 under the %VCINSTALLDIR% in your distribution directory, where all dlls/executables are present. VCINSTALLDIR should be similar to C:\Program Files\Microsoft Visual Studio 8\VC, depending on your Visual Studio Installation. This directory contains: * Microsoft.VCxx.CRT * Microsoft.VCxx.ATL ..., etc No need to copy the c:\windows\WinSxS contents. Hope this should solve your problem. Regards, Ashesh -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
