Re: [GENERAL] PL/R download

[jc9121]

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?

[Albe Laurenz]

> 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

[Craig Ringer]

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?

[Peter Geoghegan]

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

[Adrian Schreyer]

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

[David Hartveld]

> -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 
> 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

[Simon Riggs]

On Thu, Jul 7, 2011 at 1:12 PM, David Hartveld
 wrote:

>>
>> On Wed, Jul 6, 2011 at 4:54 PM, David Hartveld 
>> 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?

[Adrian Klaver]

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-
full&extra=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

[Alexander Farber]

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

[akp geek]

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

[salah jubeh]

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

[David Hartveld]

> > 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

[vincent dephily]

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

[Craig Ringer]

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

[casp]

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

[David Johnston]

-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

[Andrew Sullivan]

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

[salah jubeh]

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 
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

[Simon Riggs]

On Thu, Jul 7, 2011 at 2:59 PM, David Hartveld
 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

[Jeff Davis]

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

[Simon Riggs]

On Thu, Jul 7, 2011 at 3:37 PM, David Hartveld
 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

[Tom Lane]

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.git&a=commitdiff&h=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

[Karsten Hilbert]

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.git&a=commitdiff&h=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

[Jacqui Caren-home]

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

[Lennin Caro]

--- On Thu, 7/7/11, Craig Ringer  wrote:

From: Craig Ringer 
Subject: Re: [GENERAL] Oracle to Postgres migration open source tool
To: "akp geek" 
Cc: "pgsql-general" 
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

[akp geek]

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  wrote:

> --- On *Thu, 7/7/11, Craig Ringer * wrote:
>
>
> From: Craig Ringer 
>
> Subject: Re: [GENERAL] Oracle to Postgres migration open source tool
> To: "akp geek" 
> Cc: "pgsql-general" 
> 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] Trying to find miss and mister of the last month with highest rating

[Alexander Farber]

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

[Alexander Farber]

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

[Tom Lane]

Karsten Hilbert  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.git&a=commitdiff&h=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

[Alexander Farber]

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

[Andrew Sullivan]

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.

[Dave Coventry]

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.

[Rick Genter]

On Thu, Jul 7, 2011 at 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?
>

>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.

[Dave Coventry]

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  wrote:
>
>
> On Thu, Jul 7, 2011 at 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?
>
> 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.

[Rick Genter]

On Thu, Jul 7, 2011 at 10:26 AM, Dave Coventry  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

[Rich Shepard]

  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

[mike beeper]

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

[Alan Hodgson]

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

[Rich Shepard]

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

[Alan Hodgson]

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

[Rich Shepard]

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

[Alan Hodgson]

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

[David Johnston]

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.

[John R Pierce]

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

[French, Martin]

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

[John R Pierce]

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

[Rich Shepard]

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.

[Dave Coventry]

Hi John,

Thanks.

On 7 July 2011 21:48, John R Pierce  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

[Rich Shepard]

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

[John R Pierce]

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

[Guillaume Lelarge]

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

[Guillaume Lelarge]

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

[David Johnston]

-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

[Rich Shepard]

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

[David Johnston]

-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

[Dr. Tingrong Lu]

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" 
Sent: Friday, July 08, 2011 1:40 AM
To: 
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

[Joe Lester]

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

[Darren Duncan]

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

[Jeff Davis]

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

Re: [HACKERS] [GENERAL] Creating temp tables inside read only transactions

[Darren Duncan]

Jeff Davis wrote:

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 ;)


What I said before was a simplification; below I present my real proposal.

I think an even better way to support this is would be based on Postgres having 
support for directly using multiple databases within the same SQL session at 
once, as if namespaces were another level deep, the first level being the 
databases, the second level the schemas, and the third level the schema objects.


Kind of like what the SQL standard defines its catalog/schema/object namespaces.

This instead of needing to use federating or that contrib module to use multiple 
Pg databases of the same cluster at once.


Under this scenario, we make the property of a database being read-only or 
read-write for the current SQL session associated with a database rather than 
the whole SQL session.  A given transaction can read from any database but can 
only make changes to the ones not read-only.


Also, the proper way to do temporary tables would be to put them in another 
database than the main one, where the whole other database has the property of 
being temporary.


Under this scenario, there would be separate system catalogs for each database, 
and so the ones for read-only databases are read-only, and the ones for other 
databases aren't.


Then the system catalog itself fundamentally isn't more complicated, per 
database, and anything extra to handle cross-database queries or whatever, if 
anything, is a separate layer.  Code that only deals with a single database at 
once would be an optimized situation and perform no worse than it does now.


Furthermore, federating databases is done with the same interface, by adding 
remote/foreign databases as extra databases at the top level namespace.


Fundamentally, a SQL session would be associated with a Pg server, not a 
database managed by such.  When one starts a SQL session, there are initially no 
databases visible to them, and the top-level namespace is empty.


They then "mount" a database, similarly to how one mounts an OS filesystem, by 
providing appropriate connection info, either just the database name or also 
user/pass or also remote host etc as is applicable, these details being the 
difference between using a local/same-Pg-cluster db or a remote/federated one, 
and the details also say whether it is temporary or initially read-only etc.


See also how SQLite works; this "mount" being analogous to their "attach".

Such a paradigm is also how my Muldis D language interfaces databases; this is 
the most flexible, portable, extensible, optimizable, and elegant approach I can 
think of.


-- 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