Re: [GENERAL] failed archive command
This message has been digitally signed by the sender. Re___GENERAL__failed_archive_command.eml Description: Binary data - Hi-Tech Gears Ltd, Gurgaon, India -- 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] PL/R download
Hi Joe, I think your website is down again - I'm trying to install PLR on my windows machine and your instructions can come in handy :) thanks James -- View this message in context: http://postgresql.1045698.n5.nabble.com/PL-R-download-tp1903585p4559990.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] ERROR: malformed record literal: ,DETAIL: Missing left parenthesis?
A question about: ERROR: malformed record literal:
DETAIL: Missing left parenthesis.
Can someone tell me what cause the error?
Table z_drop;
Column| Type
-+
run_date| character varying(128)
adm_year| character varying(4)
adm_sess| character varying(1)
faculty | character varying(128)
ac_cycle| character varying(128)
deg_code| character varying(128)
discipline | character varying(128)
thesis | character varying(128)
elig_stype | character varying(128)
stud_source | character varying(128)
applied | numeric
reviewed| numeric
accepted| numeric
confirmed | numeric
registered | numeric
hold| numeric
forward | numeric
refused | numeric
cancelled | numeric
other | numeric
pending | numeric
PREPARE test(z_drop) AS INSERT INTO z_drop VALUES ($1, $2, $3,
$4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17,
$18, $19, $20, $21) ;
EXECUTE test('', '1', '1', '1', '1', '1', '1', '1', '1', '', 1, 1,
'1', '0', '0', '0', '0', '0', '0', '0', '0') ;
Your EXECUTES expects one argument of type z_drop, so it goes ahead
and tries to convert '' (the first argument) into something of type
z_drop. This is a row type, so its string representation would have
to start with (. It doesn't, hence the error message.
A correct (simplified) example would be:
CREATE TABLE z_drop (id integer PRIMARY KEY, val text);
PREPARE x(z_drop) AS INSERT INTO z_drop VALUES ($1.id, $1.val);
(there is only one argument of type z_drop)
EXECUTE x(ROW(1, 'mama'));
(with an explicit row constructor)
or
EXECUTE x((2, 'papa'));
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] failed archive command
On 7/07/2011 12:39 PM, Joe Lester wrote: DETAIL: The failed archive command was: /usr/bin/scp -B /Volumes/DataDrive/data/pg_xlog/000100740086 postgres@172.20.204.55:/Volumes/DataDrive/wals_from_master/000100740086 LOG: archiver process (PID 17771) exited with exit code 1 unknown user 502 That tells you what's wrong. Use ID 502 will be the user postgres, most likely. It works from your user account because you'll be running it under your own user ID. If you ran it from the postgres user ID using: sudo -u postgres /usr/bin/scp .. it'd probably fail the same way. Make sure you can successfully scp from the postgres user account and you should be fine. This may require accepting an interactive prompt about an unknown host key or manually adding the target server to the $HOME/.ssh/known_hosts file of the postgres user account. You may also have to add any SSH private keys required to the postgres account's .ssh directory. -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/ -- 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] Request for help - Does anyone in Seattle need a Postgres job?
It is in the wrong place. There is a jobs mailing list though. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services
[GENERAL] Latency problems with simple queries
I randomly get latency/performance problems even with very simple queries, for example fetching a row by primary key from a small table. Since I could not trace it back to specific queries, I decided to give LatencyTOP (http://www.latencytop.org/) a go. Soon after running a couple of queries, I saw this in latencytop whilst a query was hanging in postgres: Cause Maximum Percentage Writing a page to disk19283.9 msec99.7 the disk configuration is as follows: RAID controller: LSI MegaRAID 9261 tablespace is on a dedicated RAID10 volume, xlog on its own RAID1 and another disk for temporary data. Volumes are mounted with noatime,errors=remount-ro. This are the sysctl.conf changes I made (machine has 48GB memory) kernel.shmmax = 25344188416 kernel.shmall = 6187546 vm.swappiness = 0 vm.overcommit_memory = 2 vm.dirty_background_ratio = 1 vm.dirty_ratio = 2 vm.zone_reclaim_mode = 0 Maybe someone has seen this before and can give me some advice. Adrian -- 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] Streaming replication on 9.1-beta2 after pg_restore is very slow
-Oorspronkelijk bericht- Van: Simon Riggs [mailto:si...@2ndquadrant.com] Verzonden: donderdag 7 juli 2011 01:07 Aan: David Hartveld CC: pgsql-general@postgresql.org Onderwerp: Re: [GENERAL] Streaming replication on 9.1-beta2 after pg_restore is very slow On Wed, Jul 6, 2011 at 4:54 PM, David Hartveld david.hartv...@mendix.com wrote: Is there possibly a known issue with the beta, or do I have to configure my cluster differently for 9.1? Thanks for trying 9.1beta No known bugs, no differences in configuration. You haven't enabled any of the new 9.1 features either so they aren't likely to be at issue. So there's something pretty badly screwed up somewhere, though that looks like pilot error at the moment, sorry. I'd suggest starting again and see if you can get a reproduceable bug. I'd be very grateful if you can narrow things down to produce a tight bug report. I've just submitted bug report 6094, with a complete description of what I have done. The replication stream is reproducibly very slow. If you need more information, let me know. -- 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] Streaming replication on 9.1-beta2 after pg_restore is very slow
On Thu, Jul 7, 2011 at 1:12 PM, David Hartveld david.hartv...@mendix.com wrote: On Wed, Jul 6, 2011 at 4:54 PM, David Hartveld david.hartv...@mendix.com wrote: Is there possibly a known issue with the beta, or do I have to configure my cluster differently for 9.1? Thanks for trying 9.1beta No known bugs, no differences in configuration. You haven't enabled any of the new 9.1 features either so they aren't likely to be at issue. So there's something pretty badly screwed up somewhere, though that looks like pilot error at the moment, sorry. I'd suggest starting again and see if you can get a reproduceable bug. I'd be very grateful if you can narrow things down to produce a tight bug report. I've just submitted bug report 6094, with a complete description of what I have done. The replication stream is reproducibly very slow. If you need more information, let me know. Bug 6094 contains no additional information and there is not yet a confirmed bug. Your output indicates that there is a problem in your replication setup and this is why the slave does not catch up. This is not a performance issue. It is either a bug in replication, or a user configuration issue. Since few things have changed in 9.1 in this area, at the moment the balance of probability is towards user error. If you can provide a more isolated bug report we may be able to investigate. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Request for help - Does anyone in Seattle need a Postgres job?
On Wednesday, July 06, 2011 4:00:11 pm Brendan Prouty wrote: Greetings Postgres Community, I am certain that I am signing my own death certificate by emailing to a general address that is out of place, but I was referred to this list by a Postgres advocate here in Seattle, who suggested I give it a shot...so here I am. I don't know if anyone would be interested, but I have a great client here in Seattle, WA that is looking to hire a Sr. Postgres Dev/DBA type of guy/gal...and I could really use some help finding the right folkswould there be a good place to post these types of opening to your group? Thanks so much, sorry if this correspondence is in the wrong place! Try here: http://mail.postgresql.org/mj/mj_wwwusr/domain=postgresql.org?func=lists-long- fullextra=pgsql-jobs Cheers, Brendan Prouty Technical Recruiter 2101 4th Ave -- 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
[GENERAL] Trying to find miss and mister of the last month with highest rating
Hello, at my website users can rate each other: # select id, nice, last_rated from pref_rep where nice=true order by last_rated desc limit 7; id | nice | last_rated +--+ OK152565298368 | t| 2011-07-07 14:26:38.325716 OK452217781481 | t| 2011-07-07 14:26:10.831353 OK524802920494 | t| 2011-07-07 14:25:28.961652 OK348972427664 | t| 2011-07-07 14:25:17.214928 DE11873| t| 2011-07-07 14:25:05.303104 OK335285460379 | t| 2011-07-07 14:24:39.062652 OK353639875983 | t| 2011-07-07 14:23:33.811986 And I know their gender: # select id, female from pref_users limit 7; id | female + OK351636836012 | f OK366097485338 | f OK251293359874 | t OK7848446207 | f OK335478250992 | t OK355400714550 | f OK146955222542 | t I'm trying to construct 2 queries - one to find the female user with highest count of ratings for the last month (not just for the last 30 days - and this condition is already killing me) and the same for non-female users. Any help please? SQL is so hard sometimes. Regards Alex, using PostgreSQL 8.4.8 / CentOS 5.6 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Oracle to Postgres migration open source tool
Hi all - Are there any open source tools available for migrating from oracle to postgres. We have 20 tables in oracles that we needed to get to postgres. Appreciate your help Regards
[GENERAL] Documentation issue
Hello, In http://www.postgresql.org/docs/8.4/static/xfunc-c.html, there is a missing include to the utils/geo_decls.h which leads to compilation errors. i.e #include utils/geo_decls.h needs to be added to the code. Kind regards
Re: [GENERAL] Streaming replication on 9.1-beta2 after pg_restore is very slow
Your output indicates that there is a problem in your replication
setup and this is why the slave does not catch up.
This is not a performance issue. It is either a bug in replication, or
a user configuration issue. Since few things have changed in 9.1 in
this area, at the moment the balance of probability is towards user
error. If you can provide a more isolated bug report we may be able to
investigate.
Apologies for the double post, I thought to have understood that in your
previous message.
I've read the online 9.1 manual and configured the clusters based on that
information (and on the defaults provided by debian). I've attached the
postgresql.conf files I'm using for master and slave. Do you need other
information from my final setup? Log files, configuration files, the SQL
script fed
to psql, which shows the slow replication...?
I've been looking at my log files on master and slave a bit better, after
having set log_min_messages = debug5. I can see that somehow the master and
slave don't properly work together: the slave attempts to send some data
('sending write/flush/apply') (I'm assuming this is the slaves current location
in the WAL?) and then 'terminates process due to administrator command', while
the master is sending data ('write/flush/apply') (the next part of the WAL?),
and then 'could not send data to the client: Connection reset by peer', after
which the server process exits. I'm hoping this provides you with more
information on what is going on. Do point me in the right direction if you need
me to investigate further. I have attached two pieces of the master and slave
log files, which should correspond w.r.t. their interaction, where you can see
the above behavior.
Hoping that this will bring me a bit closer to a solution or a proper bug
report,
David Hartveld
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] DELETE taking too much memory
Hi,
I have a delete query taking 7.2G of ram (and counting) but I do not
understant why so much memory is necessary. The server has 12G, and
I'm afraid it'll go into swap. Using postgres 8.3.14.
I'm purging some old data from table t1, which should cascade-delete
referencing rows in t2. Here's an anonymized rundown :
# \d t1
Table public.t1
Column |Type | Modifiers
---+-+-
t1id | integer | not null default
nextval('t1_t1id_seq'::regclass)
(...snip...)
Indexes:
message_pkey PRIMARY KEY, btree (id)
(...snip...)
# \d t2
Table public.t2
Column |Type |Modifiers
-+-+-
t2id| integer | not null default
nextval('t2_t2id_seq'::regclass)
t1id| integer | not null
foo | integer | not null
bar | timestamp without time zone | not null default now()
Indexes:
t2_pkey PRIMARY KEY, btree (t2id)
t2_bar_key btree (bar)
t2_t1id_key btree (t1id)
Foreign-key constraints:
t2_t1id_fkey FOREIGN KEY (t1id) REFERENCES t1(t1id) ON UPDATE
RESTRICT ON DELETE CASCADE
# explain delete from t1 where t1id in (select t1id from t2 where
foo=0 and bar '20101101');
QUERY PLAN
-
Nested Loop (cost=5088742.39..6705282.32 rows=30849 width=6)
- HashAggregate (cost=5088742.39..5089050.88 rows=30849 width=4)
- Index Scan using t2_bar_key on t2 (cost=0.00..5035501.50
rows=21296354 width=4)
Index Cond: (bar '2010-11-01 00:00:00'::timestamp
without time zone)
Filter: (foo = 0)
- Index Scan using t1_pkey on t1 (cost=0.00..52.38 rows=1 width=10)
Index Cond: (t1.t1id = t2.t1id)
(7 rows)
Note that the estimate of 30849 rows is way off : there should be
around 55M rows deleted from t1, and 2-3 times as much from t2.
When looking at the plan, I can easily imagine that data gets
accumulated below the nestedloop (thus using all that memory), but why
isn't each entry freed once one row has been deleted from t1 ? That
entry isn't going to be found again in t1 or in t2, so why keep it
around ?
Is there a better way to write this query ? Would postgres 8.4/9.0
handle things better ?
Thanks in advance.
--
Vincent de Phily
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Oracle to Postgres migration open source tool
On 7/07/2011 9:55 PM, akp geek wrote: Hi all - Are there any open source tools available for migrating from oracle to postgres. We have 20 tables in oracles that we needed to get to postgres. Appreciate your help One avenue you may wish to investigate is ETL tools like Talend. See Google. EnterpriseDB offer an enhanced version of PostgreSQL with add-on Oracle compatibility features to ease porting. This might be worth looking into. Numerous companies offer consulting services for PostgreSQL, some of which will cover Oracle migrations/conversions. See: http://www.postgresql.org/support/professional_support As for specific oracle to PostgreSQL migration tools: Tried Google yet? -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Oracle to Postgres migration open source tool
Good truth migration between Oracle and PostgreSQL are not things difficult: Indicates that your database has: Tables: 20 Shema:? functions:? Views:? They need help to migrate ... Or what are you suggestions if you need a tool that already does this task for you good people have a tool interprisedb postgres'm not recommending eye but no way they already have. : s El jue, 07-07-2011 a las 09:55 -0400, akp geek escribió: We have 20 tables in oracles that we needed to get to postgres -- __ Ing. Cesar A. Sulbaran P. Junior Web Developer. (RUBY AND RUBY ON RAILS) User 100% Open Sources. Postgresql dba. Kernel: 2.6.32-5--bigmen http://www.google.com/profiles/cesulbaran -- 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] Trying to find miss and mister of the last month with highest rating
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Alexander Farber Sent: Thursday, July 07, 2011 9:54 AM To: pgsql-general Subject: [GENERAL] Trying to find miss and mister of the last month with highest rating # select id, nice, last_rated from pref_rep where nice=true order by last_rated desc limit 7; id | nice | last_rated +--+ OK152565298368 | t| 2011-07-07 14:26:38.325716 OK452217781481 | t| 2011-07-07 14:26:10.831353 OK524802920494 | t| 2011-07-07 14:25:28.961652 OK348972427664 | t| 2011-07-07 14:25:17.214928 DE11873| t| 2011-07-07 14:25:05.303104 OK335285460379 | t| 2011-07-07 14:24:39.062652 OK353639875983 | t| 2011-07-07 14:23:33.811986 And I know their gender: # select id, female from pref_users limit 7; id | female + OK351636836012 | f OK366097485338 | f I'm trying to construct 2 queries - one to find the female user with highest count of ratings for the last month (not just for the last 30 days - and this condition is already killing me) and the same for non-female users. Any help please? SQL is so hard sometimes. For the dates you basically need to figure out the correct year, month and day values to represent the prior month using the current month as a base (then build a date string and cast it to an actual date). Hint; the last day of the prior month is one day before the first day of the current month. Use a WITH or sub-query to select only ratings between the dates while joining the gender table. You can also perform your COUNT(*) at this level and group by ID, Gender. In the main query try to use the RANK() window function with an ORDER BY on the count field and partitioned by gender. You can probably put this in the HAVING clause and check for (RANK(*) OVER ...) = 1 Not totally sure on the syntax and don't have time to load up some test data and try different permutations but this should at least get you headed in the right direction if no-one else comes along and provides a more detailed explanation. David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Oracle to Postgres migration open source tool
On Thu, Jul 07, 2011 at 09:55:45AM -0400, akp geek wrote: Hi all - Are there any open source tools available for migrating from oracle to postgres. We have 20 tables in oracles that we needed to get to postgres. Appreciate your help I have used ora2pg. It was not seamless -- I had to do some whacking around of the produced files, and I had a pretty good idea of what changes were needed in the ora2pg tool to improve things, but I didn't have time to implement them. It was still a lot easier than trying to do it all by hand. A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Oracle to Postgres migration open source tool
Hello, I do no have any experience with oracle, try to dump the oracle database in plain format and then try to execute the DDL and DML statements. It might be cumbersome to fix all the errors you might get. But as an initial solution give it a shot. Regards From: casp cesulba...@gmail.com To: pgsql-general@postgresql.org Cc: cesulba...@gmail.com Sent: Thu, July 7, 2011 4:05:32 PM Subject: Re: [GENERAL] Oracle to Postgres migration open source tool Good truth migration between Oracle and PostgreSQL are not things difficult: Indicates that your database has: Tables: 20 Shema:? functions:? Views:? They need help to migrate ... Or what are you suggestions if you need a tool that already does this task for you good people have a tool interprisedb postgres'm not recommending eye but no way they already have. : s El jue, 07-07-2011 a las 09:55 -0400, akp geek escribió: We have 20 tables in oracles that we needed to get to postgres -- __ Ing. Cesar A. Sulbaran P. Junior Web Developer. (RUBY AND RUBY ON RAILS) User 100% Open Sources. Postgresql dba. Kernel: 2.6.32-5--bigmen http://www.google.com/profiles/cesulbaran -- 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] Streaming replication on 9.1-beta2 after pg_restore is very slow
On Thu, Jul 7, 2011 at 2:59 PM, David Hartveld
david.hartv...@mendix.com wrote:
I've been looking at my log files on master and slave a bit better, after
having set log_min_messages = debug5. I can see that somehow the master and
slave don't properly work together: the slave attempts to send some data
('sending write/flush/apply') (I'm assuming this is the slaves current
location in the WAL?) and then 'terminates process due to administrator
command', while the master is sending data ('write/flush/apply') (the next
part of the WAL?), and then 'could not send data to the client: Connection
reset by peer', after which the server process exits. I'm hoping this
provides you with more information on what is going on. Do point me in the
right direction if you need me to investigate further. I have attached two
pieces of the master and slave log files, which should correspond w.r.t.
their interaction, where you can see the above behavior.
Ah, so synchronous_standby_names is set on the standby.
Please reset that so we are operating asynchronously, then rerun tests
to see if that avoids the error. You'll probably need to fully
re-generate the standby server before doing this.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
--
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] Latency problems with simple queries
On Thu, 2011-07-07 at 12:13 +0100, Adrian Schreyer wrote: I randomly get latency/performance problems even with very simple queries, for example fetching a row by primary key from a small table. Since I could not trace it back to specific queries, I decided to give LatencyTOP (http://www.latencytop.org/) a go. Soon after running a couple of queries, I saw this in latencytop whilst a query was hanging in postgres: Cause Maximum Percentage Writing a page to disk19283.9 msec99.7 What IO scheduler and filesystem are you using? I think that CFQ has some problems for database workloads. It would be easy to test: just switch to deadline and/or noop for a while and see if the problem persists. Also, I have heard of a few strange things with ext4, but they have probably fixed those issues and it would be much harder for you to test. But it might be worth searching for issues/bugs with your particular version of the filesystem. 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] Streaming replication on 9.1-beta2 after pg_restore is very slow
On Thu, Jul 7, 2011 at 3:37 PM, David Hartveld david.hartv...@mendix.com wrote: Other suggestions? If speed is your concern, a little performance tuning might help, judging from your configs. http://www.2ndQuadrant.com/books/ or other sources will help. I'm interested in seeing some ERROR messages from either system, showing their sequence between master/standby. At the moment its not clear what the first error is. Subsequent messages are less interesting. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Streaming replication on 9.1-beta2 after pg_restore is very slow
Looking back, I notice that you built with gcc 4.6.0. At least on Red Hat machines, that gcc has a rather nasty optimization bug that breaks WAL replay, with symptoms that seem to match what you have here --- namely, the replay process quits and has to be restarted every few pages. I'm betting Debian hasn't fixed that bug yet either and so you need this post-beta2 patch: http://git.postgresql.org/gitweb/?p=postgresql.gita=commitdiffh=c2ba0121c73b7461331104a46d140156e847572a 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] Streaming replication on 9.1-beta2 after pg_restore is very slow
On Thu, Jul 07, 2011 at 11:14:05AM -0400, Tom Lane wrote: Looking back, I notice that you built with gcc 4.6.0. At least on Red Hat machines, that gcc has a rather nasty optimization bug that breaks WAL replay, with symptoms that seem to match what you have here --- namely, the replay process quits and has to be restarted every few pages. I'm betting Debian hasn't fixed that bug yet either and so you need this post-beta2 patch: http://git.postgresql.org/gitweb/?p=postgresql.gita=commitdiffh=c2ba0121c73b7461331104a46d140156e847572a Do they know about this gcc bug ? Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Oracle to Postgres migration open source tool
On 07/07/2011 15:21, salah jubeh wrote: Hello, I do no have any experience with oracle, try to dump the oracle database in plain format and then try to execute the DDL and DML statements. It might be cumbersome to fix all the errors you might get. But as an initial solution give it a shot. I have migrated mysql, oracle, ingres and SqlSvr databases to Postgres and in pretty much all cases around 30% of the activity has been application specific code changes or replacing Oracle or SS7 specific optimisations with pg equivalents. An example is the insert then update order issue on a unique keyed table. The order does make a differenet in processing time and if within a stored procedure I often find the procedure has to be redone to ensure it performs as expected. Things like this tend to be missed by automated Ora-Pg toolsets. I have never used the commercial Oracle portability layers but have heard good things about them. If you have no procedural code or triggers then migration is usually a few days application review work... Jacqui -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Oracle to Postgres migration open source tool
--- On Thu, 7/7/11, Craig Ringer cr...@postnewspapers.com.au wrote: From: Craig Ringer cr...@postnewspapers.com.au Subject: Re: [GENERAL] Oracle to Postgres migration open source tool To: akp geek akpg...@gmail.com Cc: pgsql-general pgsql-general@postgresql.org Date: Thursday, July 7, 2011, 2:02 PM On 7/07/2011 9:55 PM, akp geek wrote: Hi all - Are there any open source tools available for migrating from oracle to postgres. We have 20 tables in oracles that we needed to get to postgres. Appreciate your help One avenue you may wish to investigate is ETL tools like Talend. See Google. EnterpriseDB offer an enhanced version of PostgreSQL with add-on Oracle compatibility features to ease porting. This might be worth looking into. Numerous companies offer consulting services for PostgreSQL, some of which will cover Oracle migrations/conversions. See: http://www.postgresql.org/support/professional_support As for specific oracle to PostgreSQL migration tools: Tried Google yet? -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general i have used ora2pg migrate oracle database to postgresql http://pgfoundry.org/projects/ora2pg
Re: [GENERAL] Oracle to Postgres migration open source tool
Thank you all for the responses. All we have is just table migrations, no procedures, triggers involved. I will try to do the migration using ora2pg. I downloaded it, while installing it I am getting an error. Will try to resolve that and proceed Writing /export/home/postgres/perl_5_10_0/lib/perl5/site_perl/5.10.0/sun4-solaris/auto/Ora2Pg/.packlist sh install_all.sh install_all.sh: test: argument expected gmake: *** [install_all] Error 1 Thanks again Regards On Thu, Jul 7, 2011 at 11:51 AM, Lennin Caro lennin.c...@yahoo.com wrote: --- On *Thu, 7/7/11, Craig Ringer cr...@postnewspapers.com.au* wrote: From: Craig Ringer cr...@postnewspapers.com.au Subject: Re: [GENERAL] Oracle to Postgres migration open source tool To: akp geek akpg...@gmail.com Cc: pgsql-general pgsql-general@postgresql.org Date: Thursday, July 7, 2011, 2:02 PM On 7/07/2011 9:55 PM, akp geek wrote: Hi all - Are there any open source tools available for migrating from oracle to postgres. We have 20 tables in oracles that we needed to get to postgres. Appreciate your help One avenue you may wish to investigate is ETL tools like Talend. See Google. EnterpriseDB offer an enhanced version of PostgreSQL with add-on Oracle compatibility features to ease porting. This might be worth looking into. Numerous companies offer consulting services for PostgreSQL, some of which will cover Oracle migrations/conversions. See: http://www.postgresql.org/support/professional_support As for specific oracle to PostgreSQL migration tools: Tried Google yet? -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.orghttp://mc/compose?to=pgsql-general@postgresql.org ) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general i have used ora2pg migrate oracle database to postgresql http://pgfoundry.org/projects/ora2pg
Re: [GENERAL] Trying to find miss and mister of the last month with highest rating
Hello, I will do 2 queries - one for female users (to find the miss of last month) and one for males (the mister of last month). Here I can fetch all females rated nicely in June: # select r.id, nice, r.last_rated from pref_rep r, pref_users u where r.nice=true and to_char(current_timestamp - interval '1 month', 'IYYY-MM') = to_char(r.last_rated, 'IYYY-MM') and u.female=true and r.id=u.id; OK475705800909 | t| 2011-06-15 09:34:29.527786 DE8890 | t| 2011-06-21 14:27:12.442744 OK332253578018 | t| 2011-06-01 01:13:06.767902 OK147226095421 | t| 2011-06-21 11:01:58.151309 VK56919399 | t| 2011-06-25 10:47:52.057593 VK4123791 | t| 2011-06-17 22:44:38.763625 OK259892905389 | t| 2011-06-04 20:12:43.54472 MR13003057189952933403 | t| 2011-06-13 21:38:16.935786 Do you think it's a good query? (takes few seconds here) What to do next to pick up the person having most ratings? (if there are several persons having same amount - then I'd like to pick 1 random - my website is so obscure, that the users won't notice :-) And I understand that it would be most effective to run this query just once on the 1st of the month, but I'm too lazy to maintain the cache files/data, so I want to re-run query every time the script (actually going to be a Drupal 7.4 block) runs (I've switched the hourly block caching on). Thank you 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] Trying to find miss and mister of the last month with highest rating
Do you think this query is good? (or is it allocating loads of strings for the month comparisons?) # select r.id, count(r.id) from pref_rep r, pref_users u where r.nice=true and to_char(current_timestamp - interval '1 month', 'IYYY-MM') = to_char(r.last_rated, 'IYYY-MM') and u.female=true and r.id=u.id group by r.id order by count desc limit 7; id | count +--- OK348033534186 |49 OK145143239265 |46 OK4087658302 |41 DE11370|36 DE11467|36 OK351488505084 |35 OK524565727413 |33 (7 rows) (I'll just change limit 7 to limit 1 above to pick the miss of ls month) And why can't I add u.name, u.avatar to fetch all the info I need in 1 pass? # select r.id, count(r.id), u.name, u.avatar, u.city from pref_rep r, pref_users u where r.nice=true and to_char(current_timestamp - interval '1 month', 'IYYY-MM') = to_char(r.last_rated, 'IYYY-MM') and u.female=true and r.id=u.id group by r.id order by count desc limit 7; ERROR: column u.* must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select r.id, count(r.id), u.name, u.avatar, u.city ^ Is there a way to workaround it? 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] Streaming replication on 9.1-beta2 after pg_restore is very slow
Karsten Hilbert karsten.hilb...@gmx.net writes: On Thu, Jul 07, 2011 at 11:14:05AM -0400, Tom Lane wrote: I'm betting Debian hasn't fixed that bug yet either and so you need this post-beta2 patch: http://git.postgresql.org/gitweb/?p=postgresql.gita=commitdiffh=c2ba0121c73b7461331104a46d140156e847572a Do they know about this gcc bug ? Can't say about Debian in particular, but upstream gcc certainly knows about it. https://bugzilla.redhat.com/show_bug.cgi?id=712480 http://gcc.gnu.org/bugzilla/show_bug.cgi?id=49390 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] Trying to find miss and mister of the last month with highest rating
This seems to work, but I wonder if my query for the miss of the last month could be improved # select r.id, count(r.id), u.first_name, u.avatar, u.city from pref_rep r, pref_users u where r.nice=true and to_char(current_timestamp - interval '1 month', 'IYYY-MM') = to_char(r.last_rated, 'IYYY-MM') and u.female=true and r.id=u.id group by r.id , u.first_name, u.avatar, u.city order by count desc limit 1; id | count | first_name | avatar | city +---++--- -+--- OK348033534186 |49 | Елена | http://i398.odnoklassniki.ru/getImage?photoId=194373317258 photoType=0 | Хабаровск (1 row) (I'm sorry, I'm probably asking same questions again and again and not even not noticing it. SQL is a tough language for me) Should I maybe better use date_trunc( 'month', now() ) - '1 MONTH'::INTERVAL instead of comparing to_char() results? Thank you 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] Oracle to Postgres migration open source tool
On Thu, Jul 07, 2011 at 12:01:56PM -0400, akp geek wrote: Thank you all for the responses. All we have is just table migrations, no procedures, triggers involved. I will try to do the migration using ora2pg. I downloaded it, while installing it I am getting an error. Will try to resolve that and proceed Writing /export/home/postgres/perl_5_10_0/lib/perl5/site_perl/5.10.0/sun4-solaris/auto/Ora2Pg/.packlist sh install_all.sh install_all.sh: test: argument expected gmake: *** [install_all] Error 1 It tries to test using a bunch of Oracle modules, and those modules install with some sort of fake dependency (ISTR it was $ORACLE_HOME, but it's a dim memory now -- there was something about installing client-only libraries IIRC). So you have to bodge up your environment to get the Perl modules installed, then you need to _re_-bodge to make sure you're actually pointing at the actual Oracle installation you want to use. A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Insufficient privileges.
I am getting the following error message in my Drupal install. PDOException: SQLSTATE[42501]: Insufficient privilege: 7 ERROR: permission denied for sequence currenttest_id_seq: INSERT INTO currentTest (score) VALUES (:db_insert_placeholder_0); This is a table that I created using the postgres super user. I have tried to grant the drupal user (drupaluser) privileges to the table with: GRANT ALL ON currentTest to drupaluser; but this fails to resolve the issue. Can anyone suggest a way forward? Dave Coventry -- 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] Insufficient privileges.
On Thu, Jul 7, 2011 at 10:03 AM, Dave Coventry dgcoven...@gmail.com wrote: I am getting the following error message in my Drupal install. PDOException: SQLSTATE[42501]: Insufficient privilege: 7 ERROR: permission denied for sequence currenttest_id_seq: INSERT INTO currentTest (score) VALUES (:db_insert_placeholder_0); This is a table that I created using the postgres super user. I have tried to grant the drupal user (drupaluser) privileges to the table with: GRANT ALL ON currentTest to drupaluser; but this fails to resolve the issue. Can anyone suggest a way forward? From the message I'd say that the drupal user doesn't have access to the sequence, which is a separate object from the table. -- Rick Genter rick.gen...@gmail.com
Re: [GENERAL] Insufficient privileges.
Hi Rick, Thanks for the response. What is the sequence? and how do I grant the privileges needed to insert data into the database? Is it a postgres issue? ~ Dave On 7 July 2011 19:05, Rick Genter rick.gen...@gmail.com wrote: On Thu, Jul 7, 2011 at 10:03 AM, Dave Coventry dgcoven...@gmail.com wrote: I am getting the following error message in my Drupal install. PDOException: SQLSTATE[42501]: Insufficient privilege: 7 ERROR: permission denied for sequence currenttest_id_seq: INSERT INTO currentTest (score) VALUES (:db_insert_placeholder_0); This is a table that I created using the postgres super user. I have tried to grant the drupal user (drupaluser) privileges to the table with: GRANT ALL ON currentTest to drupaluser; but this fails to resolve the issue. Can anyone suggest a way forward? From the message I'd say that the drupal user doesn't have access to the sequence, which is a separate object from the table. -- Rick Genter rick.gen...@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] Insufficient privileges.
On Thu, Jul 7, 2011 at 10:26 AM, Dave Coventry dgcoven...@gmail.com wrote: Hi Rick, Thanks for the response. What is the sequence? and how do I grant the privileges needed to insert data into the database? Is it a postgres issue? Yes. I don't know drupal, so I don't know the correct way to fix this. My guess is that something wasn't installed/configured correctly. -- Rick Genter rick.gen...@gmail.com
[GENERAL] Add Foreign Keys To Table
I'm having difficulty finding the correct syntax to modify an existing table. The modification is to add two columns, each a foreign reference to the two key columns of another table. The other table: CREATE TABLE station_type ( sta_type VARCHAR(50), secondary_type VARCHAR(50), natural_indic BOOL, PRIMARY KEY (sta_type, secondary_type) ); When I try to alter the other table to add columns: sta_type VARCHAR(50) REFERENCES station_type(sta_type) and secondary_type VARCHAR(50) REFERENCES station_type(secondary_type) I get syntax errors. For example, alter table station_information add column sta_type varchar(50) references station_type(sta_type); ERROR: there is no unique constraint matching given keys for referenced table station_type alter table station_information add column sta_type varchar(50) not null references station_type(sta_type); ERROR: there is no unique constraint matching given keys for referenced table station_type alter table station_information add column sta_type varchar(50) unique not null references station_type(sta_type); NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index station_information_sta_type_key for table station_information ERROR: there is no unique constraint matching given keys for referenced table station_type Reading the alter table document page for 9.x does not show me what I'm doing incorrectly. Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Creating temp tables inside read only transactions
I have a function that creates a temp table, populate it with results during intermediate processing, and reads from it at the end. When the transaction is marked as read only, it does not allow creation of temp table, even though there are no permanent writes to the db. Are there any workarounds? The following block errors out. SET TRANSACTION ISOLATION LEVEL READ COMMITTED READ ONLY; create temp table test(test int);
Re: [GENERAL] Add Foreign Keys To Table
On July 7, 2011 10:40:11 AM Rich Shepard wrote: alter table station_information add column sta_type varchar(50) unique not null references station_type(sta_type); NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index station_information_sta_type_key for table station_information ERROR: there is no unique constraint matching given keys for referenced table station_type Reading the alter table document page for 9.x does not show me what I'm doing incorrectly. You need a unique index on station_type.sta_type -- 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] Add Foreign Keys To Table
On Thu, 7 Jul 2011, Alan Hodgson wrote: You need a unique index on station_type.sta_type Alan, station_type(sta_type) is part of a composite primary key. Doesn't primary key automatically imply unique and not null? Thanks, Rich -- 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] Add Foreign Keys To Table
On July 7, 2011 11:55:25 AM Rich Shepard wrote: On Thu, 7 Jul 2011, Alan Hodgson wrote: You need a unique index on station_type.sta_type Alan, station_type(sta_type) is part of a composite primary key. Doesn't primary key automatically imply unique and not null? It implies the composite is unique. Not sta_type. -- 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] Add Foreign Keys To Table
On Thu, 7 Jul 2011, Alan Hodgson wrote: It implies the composite is unique. Not sta_type. OK. Now I understand. How, then, do I add a unique constraint to each component of the composite key so I can add them as foreign keys to the station_information table? Or, is there another way to add those two columns to station_information and then add the foreign key constraints? Thanks for clarifying, Rich -- 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] Add Foreign Keys To Table
On July 7, 2011 12:30:35 PM Rich Shepard wrote: On Thu, 7 Jul 2011, Alan Hodgson wrote: It implies the composite is unique. Not sta_type. OK. Now I understand. How, then, do I add a unique constraint to each component of the composite key so I can add them as foreign keys to the station_information table? Or, is there another way to add those two columns to station_information and then add the foreign key constraints? Thanks for clarifying, create unique index index_name on table (column). Or I think you can create a foreign key on a composite like foreign key (column1,column2) references table (column1,column2) which probably makes more sense if that is a natural key. -- 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] Add Foreign Keys To Table
To do what you want to do look up CREATE INDEX in the documentation. You may wish to provide the PK/FK schema for the tables in questions as it appears - at first take - that you are doing something wrong If you have a compound Primary Key with component fields that are also UNIQUE. You probably need to add BOTH fields to station_information and then say something like. FOREIGN KEY (field1, field2) REFERENCES table (field1, field2) ... David J. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Rich Shepard Sent: Thursday, July 07, 2011 3:31 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Add Foreign Keys To Table On Thu, 7 Jul 2011, Alan Hodgson wrote: It implies the composite is unique. Not sta_type. OK. Now I understand. How, then, do I add a unique constraint to each component of the composite key so I can add them as foreign keys to the station_information table? Or, is there another way to add those two columns to station_information and then add the foreign key constraints? Thanks for clarifying, Rich -- 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] Insufficient privileges.
On 07/07/11 10:03 AM, Dave Coventry wrote: I am getting the following error message in my Drupal install. PDOException: SQLSTATE[42501]: Insufficient privilege: 7 ERROR: permission denied for sequence currenttest_id_seq: INSERT INTO currentTest (score) VALUES (:db_insert_placeholder_0); This is a table that I created using the postgres super user. I have tried to grant the drupal user (drupaluser) privileges to the table with: GRANT ALL ON currentTest to drupaluser; but this fails to resolve the issue. Can anyone suggest a way forward? Dave Coventry I recommend dropping your drupal database (since I doubt its worked right if the objects are owned by postgres), and recreate it owned by the drupaluser, then let the drupaluser populate it during the initial install. or, if I'm misreading your problem, and drupal itself is running but this is an extra non-drupal table you manually created, then ALTER TABLE OWNER drupaluser; drupal doesn't really interface very well to non-drupal data... the drupal approach is to define a new content type with the fields you need, then populate it via create content, choosing that new type, or use the various drupal APIs from your custom PHP modules. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] [PERFORM] DELETE taking too much memory
How up to date are the statistics for the tables in question?
What value do you have for effective cache size?
My guess would be that planner thinks the method it is using is right
either for its current row number estimations, or the amount of memory
it thinks it has to play with.
-Original Message-
From: pgsql-performance-ow...@postgresql.org
[mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of vincent
dephily
Sent: 07 July 2011 14:34
To: pgsql-general@postgresql.org; pgsql-performa...@postgresql.org
Subject: [PERFORM] DELETE taking too much memory
Hi,
I have a delete query taking 7.2G of ram (and counting) but I do not
understant why so much memory is necessary. The server has 12G, and
I'm afraid it'll go into swap. Using postgres 8.3.14.
I'm purging some old data from table t1, which should cascade-delete
referencing rows in t2. Here's an anonymized rundown :
# \d t1
Table public.t1
Column |Type | Modifiers
---+-+--
---
t1id | integer | not null default
nextval('t1_t1id_seq'::regclass)
(...snip...)
Indexes:
message_pkey PRIMARY KEY, btree (id)
(...snip...)
# \d t2
Table public.t2
Column |Type |Modifiers
-+-+
-
t2id| integer | not null default
nextval('t2_t2id_seq'::regclass)
t1id| integer | not null
foo | integer | not null
bar | timestamp without time zone | not null default now()
Indexes:
t2_pkey PRIMARY KEY, btree (t2id)
t2_bar_key btree (bar)
t2_t1id_key btree (t1id)
Foreign-key constraints:
t2_t1id_fkey FOREIGN KEY (t1id) REFERENCES t1(t1id) ON UPDATE
RESTRICT ON DELETE CASCADE
# explain delete from t1 where t1id in (select t1id from t2 where
foo=0 and bar '20101101');
QUERY PLAN
-
Nested Loop (cost=5088742.39..6705282.32 rows=30849 width=6)
- HashAggregate (cost=5088742.39..5089050.88 rows=30849 width=4)
- Index Scan using t2_bar_key on t2 (cost=0.00..5035501.50
rows=21296354 width=4)
Index Cond: (bar '2010-11-01 00:00:00'::timestamp
without time zone)
Filter: (foo = 0)
- Index Scan using t1_pkey on t1 (cost=0.00..52.38 rows=1
width=10)
Index Cond: (t1.t1id = t2.t1id)
(7 rows)
Note that the estimate of 30849 rows is way off : there should be
around 55M rows deleted from t1, and 2-3 times as much from t2.
When looking at the plan, I can easily imagine that data gets
accumulated below the nestedloop (thus using all that memory), but why
isn't each entry freed once one row has been deleted from t1 ? That
entry isn't going to be found again in t1 or in t2, so why keep it
around ?
Is there a better way to write this query ? Would postgres 8.4/9.0
handle things better ?
Thanks in advance.
--
Vincent de Phily
--
Sent via pgsql-performance mailing list
(pgsql-performa...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
___
This email is intended for the named recipient. The information contained
in it is confidential. You should not copy it for any purposes, nor
disclose its contents to any other party. If you received this email
in error, please notify the sender immediately via email, and delete it from
your computer.
Any views or opinions presented are solely those of the author and do not
necessarily represent those of the company.
PCI Compliancy: Please note, we do not send or wish to receive banking, credit
or debit card information by email or any other form of communication.
Please try our new on-line ordering system at http://www.cromwell.co.uk/ice
Cromwell Tools Limited, PO Box 14, 65 Chartwell Drive
Wigston, Leicester LE18 1AT. Tel 0116 2888000
Registered in England and Wales, Reg No 00986161
VAT GB 115 5713 87 900
__
--
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] Add Foreign Keys To Table
On 07/07/11 10:40 AM, Rich Shepard wrote: I'm having difficulty finding the correct syntax to modify an existing table. The modification is to add two columns, each a foreign reference to the two key columns of another table. The other table: CREATE TABLE station_type ( sta_type VARCHAR(50), secondary_type VARCHAR(50), natural_indic BOOL, PRIMARY KEY (sta_type, secondary_type) ); When I try to alter the other table to add columns: sta_type VARCHAR(50) REFERENCES station_type(sta_type) and secondary_type VARCHAR(50) REFERENCES station_type(secondary_type) I get syntax errors. Since your PK of station_type is a composite, your foreign key must also be composite. CREATE TABLE stuffed ( id serial; otherestuffs text; sta varchar(50), sec varchar(50), FOREIGN KEY (sta, sec) REFERENCES station_type(sta_type, secondary_type) ; ); -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] Add Foreign Keys To Table
On Thu, 7 Jul 2011, Alan Hodgson wrote: create unique index index_name on table (column). Alan, This worked like a charm. Many thanks for the lesson, Rich -- 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] Insufficient privileges.
Hi John, Thanks. On 7 July 2011 21:48, John R Pierce pie...@hogranch.com wrote: I recommend dropping your drupal database (since I doubt its worked right if the objects are owned by postgres), and recreate it owned by the drupaluser, then let the drupaluser populate it during the initial install. :~) Well that's a little drastic at this stage! In actual fact the database drupaldb is owned by the drupaluser, so it's not really necessary. or, if I'm misreading your problem, and drupal itself is running but this is an extra non-drupal table you manually created, then ALTER TABLE OWNER drupaluser; Yes, that's what I was trying to do. Using the Drupal Nodes seems awfully cumbersome for what I'm trying to achieve so I added a sort of scratch table that I was hoping to manipulate. I have gone back to using the prescribed Drupal method as time was starting to run out and I needed a working prototype. I do intend to return to using the scratch table after the rush is over and I'll give your suggestion a try: it looks as though it may very well do the trick. drupal doesn't really interface very well to non-drupal data... the drupal approach is to define a new content type with the fields you need, then populate it via create content, choosing that new type, or use the various drupal APIs from your custom PHP modules. Yes, I'm finding that out. Thanks very much for your input. Regards, Dave Coventry -- 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] Add Foreign Keys To Table
On Thu, 7 Jul 2011, John R Pierce wrote: Since your PK of station_type is a composite, your foreign key must also be composite. CREATE TABLE stuffed ( id serial; otherestuffs text; sta varchar(50), sec varchar(50), FOREIGN KEY (sta, sec) REFERENCES station_type(sta_type, secondary_type) ); Thanks, John, for showing me how to apply this approach. Rich -- 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] Add Foreign Keys To Table
On 07/07/11 1:02 PM, Rich Shepard wrote: On Thu, 7 Jul 2011, Alan Hodgson wrote: create unique index index_name on table (column). Alan, This worked like a charm. Many thanks for the lesson, Rich if your original table has Primary Key of (sta_type, secondary_type) I would not expect EITHER of those fields to be unique by themselves Surely there can be more than one of the same sta_type with different secondary_type's, just as there could be more than one secondary_type with different sta_types if either of these fields is unique of and by itself, it doesn't make sense to use the combined primary key. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] Creating temp tables inside read only transactions
On Thu, 2011-07-07 at 16:01 +, mike beeper wrote: I have a function that creates a temp table, populate it with results during intermediate processing, and reads from it at the end. When the transaction is marked as read only, it does not allow creation of temp table, even though there are no permanent writes to the db. Are there any workarounds? The following block errors out. SET TRANSACTION ISOLATION LEVEL READ COMMITTED READ ONLY; create temp table test(test int); When you create a temporary table, PostgreSQL needs to add rows in pg_class, pg_attribute, and probably other system catalogs. So there are writes, which aren't possible in a read-only transaction. Hence the error. And no, there is no workaround. -- 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] DELETE taking too much memory
On Thu, 2011-07-07 at 15:34 +0200, vincent dephily wrote:
Hi,
I have a delete query taking 7.2G of ram (and counting) but I do not
understant why so much memory is necessary. The server has 12G, and
I'm afraid it'll go into swap. Using postgres 8.3.14.
I'm purging some old data from table t1, which should cascade-delete
referencing rows in t2. Here's an anonymized rundown :
# \d t1
Table public.t1
Column |Type | Modifiers
---+-+-
t1id | integer | not null default
nextval('t1_t1id_seq'::regclass)
(...snip...)
Indexes:
message_pkey PRIMARY KEY, btree (id)
(...snip...)
# \d t2
Table public.t2
Column |Type |Modifiers
-+-+-
t2id| integer | not null default
nextval('t2_t2id_seq'::regclass)
t1id| integer | not null
foo | integer | not null
bar | timestamp without time zone | not null default now()
Indexes:
t2_pkey PRIMARY KEY, btree (t2id)
t2_bar_key btree (bar)
t2_t1id_key btree (t1id)
Foreign-key constraints:
t2_t1id_fkey FOREIGN KEY (t1id) REFERENCES t1(t1id) ON UPDATE
RESTRICT ON DELETE CASCADE
# explain delete from t1 where t1id in (select t1id from t2 where
foo=0 and bar '20101101');
QUERY PLAN
-
Nested Loop (cost=5088742.39..6705282.32 rows=30849 width=6)
- HashAggregate (cost=5088742.39..5089050.88 rows=30849 width=4)
- Index Scan using t2_bar_key on t2 (cost=0.00..5035501.50
rows=21296354 width=4)
Index Cond: (bar '2010-11-01 00:00:00'::timestamp
without time zone)
Filter: (foo = 0)
- Index Scan using t1_pkey on t1 (cost=0.00..52.38 rows=1 width=10)
Index Cond: (t1.t1id = t2.t1id)
(7 rows)
Note that the estimate of 30849 rows is way off : there should be
around 55M rows deleted from t1, and 2-3 times as much from t2.
When looking at the plan, I can easily imagine that data gets
accumulated below the nestedloop (thus using all that memory), but why
isn't each entry freed once one row has been deleted from t1 ? That
entry isn't going to be found again in t1 or in t2, so why keep it
around ?
Is there a better way to write this query ? Would postgres 8.4/9.0
handle things better ?
Do you have any DELETE triggers in t1 and/or t2?
--
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] Add Foreign Keys To Table
-Original Message- From: Rich Shepard [mailto:rshep...@appl-ecosys.com] Sent: Thursday, July 07, 2011 4:05 PM To: David Johnston Subject: RE: [GENERAL] Add Foreign Keys To Table On Thu, 7 Jul 2011, David Johnston wrote: To do what you want to do look up CREATE INDEX in the documentation. David, Now I understand this. You probably need to add BOTH fields to station_information and then say something like. FOREIGN KEY (field1, field2) REFERENCES table (field1, field2) ... And this would be for each of the two added fields? Same syntax? I've not used this approach before. Thanks, Rich You would HAVE to do it at the TABLE level since a column-level constraint can only reference that single column. David J. -- 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] Add Foreign Keys To Table
On Thu, 7 Jul 2011, John R Pierce wrote: if your original table has Primary Key of (sta_type, secondary_type) I would not expect EITHER of those fields to be unique by themselves Surely there can be more than one of the same sta_type with different secondary_type's, just as there could be more than one secondary_type with different sta_types if either of these fields is unique of and by itself, it doesn't make sense to use the combined primary key. John, Ah, yes. Of course. That's why the compound primary key is required. I'll redo the table the proper way. Thanks, Rich -- 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] Trying to find miss and mister of the last month with highest rating
-Original Message- And why can't I add u.name, u.avatar to fetch all the info I need in 1 pass? # select r.id, count(r.id), u.name, u.avatar, u.city from pref_rep r, pref_users u where r.nice=true and to_char(current_timestamp - interval '1 month', 'IYYY-MM') = to_char(r.last_rated, 'IYYY-MM') and u.female=true and r.id=u.id group by r.id order by count desc limit 7; ERROR: column u.* must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select r.id, count(r.id), u.name, u.avatar, u.city ^ Is there a way to workaround it? I believe you need to put 'name' in quotes ( like u.name ) The fact that the warning indicates u.* where you didn't use u.* anywhere in your literal syntax means that PostgreSQL is interpreting something funny. Trial and error should have narrowed down the options if you didn't catch that name is so common as to likely be utilized by the database. Going from memory here... David J. -- 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] Add Foreign Keys To Table
You have a wrong concept of foreign keys. sta_type is not a key of table station_type, which cannot be referened as a foreign key. -- From: Rich Shepard rshep...@appl-ecosys.com Sent: Friday, July 08, 2011 1:40 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Add Foreign Keys To Table I'm having difficulty finding the correct syntax to modify an existing table. The modification is to add two columns, each a foreign reference to the two key columns of another table. The other table: CREATE TABLE station_type ( sta_type VARCHAR(50), secondary_type VARCHAR(50), natural_indic BOOL, PRIMARY KEY (sta_type, secondary_type) ); When I try to alter the other table to add columns: sta_type VARCHAR(50) REFERENCES station_type(sta_type) and secondary_type VARCHAR(50) REFERENCES station_type(secondary_type) I get syntax errors. For example, alter table station_information add column sta_type varchar(50) references station_type(sta_type); ERROR: there is no unique constraint matching given keys for referenced table station_type alter table station_information add column sta_type varchar(50) not null references station_type(sta_type); ERROR: there is no unique constraint matching given keys for referenced table station_type alter table station_information add column sta_type varchar(50) unique not null references station_type(sta_type); NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index station_information_sta_type_key for table station_information ERROR: there is no unique constraint matching given keys for referenced table station_type Reading the alter table document page for 9.x does not show me what I'm doing incorrectly. Rich -- 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] failed archive command
I tried to use only %p to specify the path, but it does not seem to output the full path according to the server log. It only starts at /pg_xlog: archive_command = '/usr/bin/scp -B %p postgres@172.20.204.55:/Volumes/DataDrive/wals_from_master/%f' DETAIL: The failed archive command was: /usr/bin/scp -B pg_xlog/000100740086 postgres@172.20.204.55:/Volumes/DataDrive/wals_from_master/000100740086 Also, if I specify the full path (like I was doing before) and execute the scp command as the postgres user on the master, it works (see output below). So I don't understand why it's not working when the postgres server tries to execute the same command. mybox:~ admin$ su postgres Password: bash-3.2$ /usr/bin/scp -B /Volumes/DataDrive/data/pg_xlog/000100740086 postgres@172.20.204.55:/Volumes/DataDrive/wals_from_master/000100740086 000100740086 100% 16MB 16.0MB/s 00:01 bash-3.2$ %p is expanded to the *full* path, so /Volumes/DataDrive/data/%p might not be the correct. I'd use just %p instead of it. I'm trying to setup wal archiving to a secondary computer on OSX 10.6.5 using postgres 9.0.3. Here are my settings in postgresql.conf on the primary box: wal_level = archive archive_mode = on max_wal_senders = 1 archive_command = '/usr/bin/scp -B /Volumes/DataDrive/data/%p postgres@172.20.204.55:/Volumes/DataDrive/wals_from_master/%f' The problem is that I keep getting the following message over and over again in the postgres log: FATAL: archive command failed with exit code 255 DETAIL: The failed archive command was: /usr/bin/scp -B /Volumes/DataDrive/data/pg_xlog/000100740086 postgres@172.20.204.55:/Volumes/DataDrive/wals_from_master/000100740086 LOG: archiver process (PID 17771) exited with exit code 1 unknown user 502 The archive command works if I copy and paste it into the Terminal under the postgres user. Any pointers as to what I'm doing wrong? Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Creating temp tables inside read only transactions
Guillaume Lelarge wrote [on pgsql-general]: On Thu, 2011-07-07 at 16:01 +, mike beeper wrote [on pgsql-general]: I have a function that creates a temp table, populate it with results during intermediate processing, and reads from it at the end. When the transaction is marked as read only, it does not allow creation of temp table, even though there are no permanent writes to the db. Are there any workarounds? The following block errors out. SET TRANSACTION ISOLATION LEVEL READ COMMITTED READ ONLY; create temp table test(test int); When you create a temporary table, PostgreSQL needs to add rows in pg_class, pg_attribute, and probably other system catalogs. So there are writes, which aren't possible in a read-only transaction. Hence the error. And no, there is no workaround. That sounds like a deficiency to overcome. It should be possible for those system catalogs to be virtual, defined like union views over similar immutable tables for the read-only database plus mutable in-memory ones for the temporary tables. Are there any plans in the works to do this? On the other hand, if one can have lexical-scope tables (table-typed routine variables), and I know Pg 8.4+ has named subqueries which handle a lot of cases where temp tables would otherwise be used, I would certainly expect those to work when you're dealing with a readonly database. -- Darren Duncan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Creating temp tables inside read only transactions
On Thu, 2011-07-07 at 20:56 -0700, Darren Duncan wrote: When you create a temporary table, PostgreSQL needs to add rows in pg_class, pg_attribute, and probably other system catalogs. So there are writes, which aren't possible in a read-only transaction. Hence the error. And no, there is no workaround. That sounds like a deficiency to overcome. It should be possible for those system catalogs to be virtual, defined like union views over similar immutable tables for the read-only database plus mutable in-memory ones for the temporary tables. Ideally, yes, from a logical standpoint there are catalog entries that are only interesting to one backend. But that doesn't mean it's easy to do. Remember that catalog lookups (even though most go through a cache) are a path that is important to performance. Also, more complex catalog interpretations may introduce some extra bootstrapping challenges. Are there any plans in the works to do this? I don't think so. It sounds like some fairly major work for a comparatively minor benefit. Suggestions welcome, of course, to either make the work look more minor or the benefits look more major ;) 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
