Re: [GENERAL] Moving from 32 to 64 bit builds on Solaris

[Tom Lane]

Dan Sugalski <[EMAIL PROTECTED]> writes:
> I assume I'll have to do a 64 bit build to use more than a few gig of 
> shared buffers. If I do that, though, am I going to have to do a 
> database dump and reload,

Yes, most likely, because you'll have changed MAXALIGN and therefore the
data alignment rules.

You should first ask yourself whether you will get any performance
benefit from having "more than a few gig of shared buffers".  If anyone
has proven such a benefit I haven't seen it.

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] query ... returned 4 columns

[Tom Lane]

Sorin Schwimmer <[EMAIL PROTECTED]> writes:
> Having the same structure, I put
> INSERT INTO archive.expected_stuff VALUES(o);
> but it doesn't work. Instead, I had to rewrite as
> INSERT ... VALUES (o.source,o.warehouse...);

Of course.  The former command implies that you are inserting a
composite value into a single composite-type column of expected_stuff,
which you are not.

The right way to express this IMHO is
INSERT INTO archive.expected_stuff VALUES(o.*);
which should expand into the longhand notation "o.source,o.warehouse..."
in the same way that "SELECT o.* FROM ..." would do.  This does actually
work in 8.2 (and maybe 8.1, I forget).  In older releases you gotta
write it out longhand :-(

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Is This A Set Based Solution?

[Tom Lane]

Stefan Berglund <[EMAIL PROTECTED]> writes:
> Below is a small test case that illustrates what I'm attempting which is
> to provide a comma separated list of numbers to a procedure which
> subsequently uses this list in a join with another table.

> My questions are is this a set based solution and is this the best
> approach in terms of using the data types and methods afforded by
> PostgreSQL?  I'm mostly inquiring about the double FOR loop which just
> doesn't feel right to me ...

It looks pretty ugly to me too, but you haven't explained your problem
clearly enough for anyone to be able to recommend a better solution path.
Why do you feel you need to do this?  What is the context?

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Tsearch2 / Create rule on select

[Oleg Bartunov]

On Fri, 9 Mar 2007, Jean-Michel Pour? wrote:


Le vendredi 09 mars 2007 ЪЪ 10:58 +0100, Magnus Hagander a ЪЪcrit :

No idea. Assuming you want to do it beforehand. otherwise, just create
the index and see how large it got?


Thank you for your comments. I will add TSeach2 support to phpBB 3.x
soon.

I had incredible response time on simple queries on more than plain-text
200.000 row. About 1 millisecond! Incredible but true.

explain analyse verbose SELECT * FROM phpbb_posts_text WHERE idxfti @@
'jmp'::tsquery limit 100;

Limit  (cost=0.00..444.47 rows=100 width=934) (actual time=0.046..0.824
rows=100 loops=1)
  ->  Index Scan using idxfti_idx on phpbb_posts_text
(cost=0.00..1053.38 rows=237 width=934) (actual time=0.040..0.418
rows=100 loops=1)
Index Cond: (idxfti @@ '''jmp'''::tsquery)
[color=red]Total runtime: 1.068 ms

TSearch2 will be used by wikimedia shortly (it is supported in beta
version).


I've already seen tsearch2 in 1.9.X release.



IMHO, it would be better if PostgreSQL parser was able to find Tseach
indexes alone, rewritting the query automatically. Maybe it is on the
radar list of hackers.


btw, we're working on new FTS feature of PostgreSQL, it's basically
tsearch integrated into the pg core. But it has some new features and 
new SQL commands for configuring of FTS, so if the matter is not pressing I'd recommend

to check http://mira.sai.msu.su/~megera/pgsql/ftsdoc




Kind regards,
Jean-Michel




---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster



Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] index bloat problem

[Tom Lane]

Monika Cernikova <[EMAIL PROTECTED]> writes:
> Can you help me how to stop index growing or reindex database if I CAN'T 
> STOP writing records?

I think you have max_fsm_pages set too small.

> max_fsm_pages = 40  

That corresponds to about 3Gb, or a tenth the size of your DB.  Not enough.

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Is This A Set Based Solution?

[Oleg Bartunov]

I don't know if you could change your schema. but I'd consider your
problem as a overlapping arrays task and use contrib/intarray for that.

Oleg
On Fri, 9 Mar 2007, Stefan Berglund wrote:


Hi-

Below is a small test case that illustrates what I'm attempting which is
to provide a comma separated list of numbers to a procedure which
subsequently uses this list in a join with another table.

My questions are is this a set based solution and is this the best
approach in terms of using the data types and methods afforded by
PostgreSQL?  I'm mostly inquiring about the double FOR loop which just
doesn't feel right to me and I'd also like to feel that I'm generally on
the right track before converting the other 400 procedures from SQL
Server 2000 to PostgreSQL.

CREATE TYPE fn_return_int4 AS (N int);

CREATE TABLE test_table (
 id SMALLINT not null,
 tname varchar(50) not null);

INSERT INTO test_table
 SELECT 1, 'Adams'
UNION SELECT 2, 'Baker'
UNION SELECT 3, 'Chrysler'
UNION SELECT 4, 'Douglas'
UNION SELECT 5, 'Everyman';

CREATE OR REPLACE FUNCTION fn_Split_List (
 pList TEXT) RETURNS SETOF fn_return_int4 AS $fn_Split_List$

DECLARE
 v_row fn_return_int4%rowtype;
 v_list alias for $1;
 v_delim text := ',';
 v_arr text[];

BEGIN
 v_arr := string_to_array(v_list, v_delim);
 FOR i IN array_lower(v_arr, 1)..array_upper(v_arr, 1) LOOP
   FOR v_row IN SELECT v_arr[i] LOOP
 RETURN NEXT v_row;
   END LOOP;
 END LOOP;
RETURN;
END;
$fn_Split_List$ LANGUAGE plpgsql;

SELECT *
FROM
 fn_Split_List('5,1,3') SL INNER JOIN
 test_table T ON SL.N=T.ID;

I did discover that I was able to define the function with a native type
but then the usage looked a little odd:

SELECT *
FROM
 fn_Split_List('5,1,3') SL INNER JOIN
 test_table T ON SL=T.ID;

Stefan Berglund
www.horseshowtime.com
Online Show Entry - Instant Internet Horse Show Schedules and Results
[EMAIL PROTECTED]
tel  714.968.9112   fax  714.968.5940

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster



Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

[GENERAL] Recommendations for postgres upgrade of database with lobs

[CAJ CAJ]

Hello,

We have several independent database servers with ~50GB+ databases running
postgres 8.0.x. We are planning to upgrade these databases to postgres
8.2.xover the weekend

We plan to use the following steps to upgrade each server,

1. Dump the 8.0.x database cluster using 8.2.x pg_dumpall
% ./pg_dumpall > pgdumpall_backup.sql

2.Dump the 8.0.x database  including large objects in  compressed custom
format using 8.2.x pg_dump
% ./pg_dump -Fc -b -Z9 dbname > pgdump_lobs_backup


Restoring database
1. Initialize 8.2.x darabase
% initdb -D /data/pgdata

2. Restore template1 database from cluster dump
% ./psql -d template1 < pgdumpall_backup.sql

3. Delete database dbname else restoring will give error about existing
dbname
% dropdb dbname

4. Create fresh dbname
% createdb -O dbowner dbname

5. Restore database with lobs
% ./pg_restore -v -Fc -d dbname -e -U dbowner < pgdumpall_lobs_backup

Some of the problems we have are,
1. We are not sure if all of the data will be available after dump/restore
with above process
2. The dump and restore process is very very slow to be complete over the
weekend (takes approx 1GB/hr to dump on a dual G5 PPC 2Ghz with 1GB RAM and
RAID 1 disks)

What is the fastest way to upgrade postgres for large databases that has
binary objects?

Thanks for all your help.

[GENERAL] Moving from 32 to 64 bit builds on Solaris

[Dan Sugalski]

I've got an install of Postgres 8.2.3 on a Sun box that's ticking 
over nicely -- I'm pretty happy with it and how it's performing. It's 
a 32 bit build, and the machine I'm running it on has a lot of extra 
memory.


I assume I'll have to do a 64 bit build to use more than a few gig of 
shared buffers. If I do that, though, am I going to have to do a 
database dump and reload, or will the disk files be compatible and it 
just a matter of shutting down the 32 bit server and firing up the 64 
bit one?

--
Dan

--it's like this---
Dan Sugalski  even samurai
[EMAIL PROTECTED] have teddy bears and even
  teddy bears get drunk

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Data validation - was Re: [GENERAL] OT: Canadian Tax Database

[Ted Byers]

- Original Message - 
From: "omar" <[EMAIL PROTECTED]>

To: 
Sent: Friday, March 09, 2007 10:40 PM
Subject: Re: [GENERAL] OT: Canadian Tax Database


Tom, I promise this isn't a political statement, even though it's on the 
same thread.
I'm curious what people think about the following statement considering 
the database typing talk being brought up here.  My experience is that 
more times than not I have to put data validation in my client code even 
when it's available on the server, if for no other reason that users don't 
understand what foreign key violation, etc messages mean.  It begs the 
question of whether it's really necessary on the server or not.  SQLite 
seems to take the position that it isn't since there is no referential 
integrity and the following.  To be honest, there's a lot of power in the 
ability to view everything as a string, with of course proper data 
validation.


This risk of this is far too high.  Treating everything as a string is, 
IMHO, a very bad idea.


There are, especially for a web application, numerous forms of attack, so I 
routinely provide code for client side validation, server side validation 
(in a web app or in filters that process the data before providing it to 
whatever is going to do something useful with the data.  this includes 
designing stored procedures to receive, and validate, data before the data 
is stored in the database.  On the client side, the main benefit is to 
ensure the user doesn't miss anything that is necessary and that he enters 
valid data.   If the user is malicious, and wants to try a SQL injection 
attack, nothing you do on the client side can prevent him from creating his 
own version of your page bypassing all of your client side validation code. 
And it is possible for a scoundrel to try a man in the middle attack 
(intercepting a transaction mid stream and trying, e.g., a SQL injection 
attack).  So even with client side validation, server side validation is 
absolutely essential.  I like Perl for that, but it can be done in your 
favourite programming language.  And it can be done in .NET also, if you 
prefer.


Maybe I am paranoid, but whether I am writing code to be run at the very 
back end, or the very front end, or anywhere between the two, my preference 
is to validate the data that specific object has received before I do 
anything with it.  That is key in secure application development.  You 
generally assume that your system, and any component therein, can been 
compromised so you program on the assumption that it can be compromised 
somewhere and write code that minimizes or eliminates the damage that can be 
done if some component anywhere else in the system has been compromised. 
Just 'coz I'm paranoid doesn't mean they're not out to get me.  ;-)   I 
value really good system administrators who go the extra mile to make 
intranets and systems as secure as humanly possible, but as an application 
developer, I never assume they have not overlooked something.  Instead, I 
assume the opposite and that therefore, they got everything wrong and that 
the intranet and every server in it either has been compromised or will soon 
be compromised, and I therefore try to minimize the risk of damage or 
violation of data confidentiality or security in a network or on a system 
that has been compromised.  I know perfection is not possible, but I hope we 
can make it too expensive for a cyber criminal to get what he wants 
illegally.  If we make his cost greater than his potential return, he should 
rationally move on to easier targets.


Cheers

Ted 




---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] Trigger for Audit Table

[Bill Moseley]

On Fri, Mar 09, 2007 at 01:27:51PM -0800, [EMAIL PROTECTED] wrote:
> You can/should create it as an AFTER UPDATE trigger.  The OLD row will
> contain the previous values.

Curiously, also works with a BEFORE UPDATE.

Off to review the docs

-- 
Bill Moseley
[EMAIL PROTECTED]


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] OT: Canadian Tax Database

[omar]

Tom, I promise this isn't a political statement, even though it's on the 
same thread. 

I'm curious what people think about the following statement considering 
the database typing talk being brought up here.  My experience is that 
more times than not I have to put data validation in my client code even 
when it's available on the server, if for no other reason that users 
don't understand what foreign key violation, etc messages mean.  It begs 
the question of whether it's really necessary on the server or not.  
SQLite seems to take the position that it isn't since there is no 
referential integrity and the following.  To be honest, there's a lot of 
power in the ability to view everything as a string, with of course 
proper data validation.


http://www.sqlite.org/datatypes.html

>>>SQLite is "typeless". This means that you can store any kind of data 
you want in any column of any table, regardless of the declared datatype 
of that column. (See the one exception to this rule in section 2.0 
below.) This behavior is a feature, not a bug. A database is suppose to 
store and retrieve data and it should not matter to the database what 
format that data is in. The strong typing system found in most other SQL 
engines and codified in the SQL language spec is a misfeature - it is an 
example of the implementation showing through into the interface. SQLite 
seeks to overcome this misfeature by allowing you to store any kind of 
data into any kind of column and by allowing flexibility in the 
specification of datatypes.<<<



Patrick TJ McPhee wrote:

To be fair, this is not "the tax system". It's a staging database
used for electronic filing, and it's pretty common to use typeless
databases in the first stage of that sort of application.
  



---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Setting week starting day

[Bruno Wolff III]

On Sat, Mar 10, 2007 at 00:03:04 -0300,
  Jorge Godoy <[EMAIL PROTECTED]> wrote:
> 
> If I run this query:
> 
>select date_trunc('week', '2007-03-08'::date + 5);
> 
> it fails even for that date.  The correct answer, would be 2007-03-07 and not
> 2007-03-12.  I want the first day of the week to be Wednesday and hence I want
> the Wednesday for the week the date is in.  (Wednesday was arbitrarily chosen,
> it could be Thursday, Tuesday, Friday, etc.)

If for some reason you actually need to display the date of the first day
of the week, rather than just group by it, then subtract the number of
days that were added inside, on the outside. Because date_trunc returns
a timestamp with timezone, you need to subtract an interval (or cast
back to date and subtract an integer). If you are getting the '5' from
somewhere hard coded you might want to use (5 * '1 day'::interval) rather
than '5 days'::interval .

So you would use:
select date_trunc('week', '2007-03-08'::date + 5) - '5 days'::interval;

postgres=# select date_trunc('week', '2007-03-08'::date + 5) - '5 
days'::interval;
?column?

 2007-03-07 00:00:00-06
(1 row)

postgres=# select date_trunc('week', '2007-03-07'::date + 5) - '5 
days'::interval;
?column?

 2007-03-07 00:00:00-06
(1 row)

postgres=# select date_trunc('week', '2007-03-06'::date + 5) - '5 
days'::interval;
?column?

 2007-02-28 00:00:00-06
(1 row)

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Setting week starting day

[Jorge Godoy]

Omar Eljumaily <[EMAIL PROTECTED]> writes:

> But you're always returning Monday, right?  Your grouping will be correct, but
> to get the actual truncation date, you have to subtract back.
>
> select (date_trunc('week', '2007-03-07'::date + 5)::date-5);
> select (date_trunc('week', '2007-03-06'::date + 5)::date-5);
> select (date_trunc('week', '2007-03-08'::date + 5)::date-5);

Indeed.  This gives the correct result.  So, we can change '5' for:  

 7 - ('dow desired' - 1)

Replacing the above queries, then:

# select (date_trunc('week', '2007-03-07'::date + (7 - (3 - 1::date - (7 - 
(3 - 1));
  ?column?  

 2007-03-07
(1 row)

# select (date_trunc('week', '2007-03-06'::date + (7 - (3 - 1::date - (7 - 
(3 - 1));
  ?column?  

 2007-02-28
(1 row)

# select (date_trunc('week', '2007-03-08'::date + (7 - (3 - 1::date - (7 - 
(3 - 1));
  ?column?  

 2007-03-07
(1 row)



Parameterizing the desired day shouldn't be hard. ;-)


We subtract one from the desired day because PostgreSQL returns '1' for the
date_part('week') considering Mondays as the first day of the week. 



Thanks, Omar.  This makes the function easier to write.  I hope it also solves
your problem.



Be seeing you,
-- 
Jorge Godoy  <[EMAIL PROTECTED]>

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] OT: Canadian Tax Database

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/09/07 00:12, Patrick TJ McPhee wrote:
[snip]
> 
> To be fair, this is not "the tax system". It's a staging database
> used for electronic filing, and it's pretty common to use typeless
> databases in the first stage of that sort of application.

Why?  Why not filter out the obvious errors AEAP[*] in the data stream?

[*] As Early As Possible


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF8h/MS9HxQb37XmcRAuF2AKCL7qA4NW6O5foRtQW3uDzHtg1FOQCgwnYh
BowoxdVktlw9VoqBSXON9MU=
=RNHI
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Setting week starting day

[Jorge Godoy]

Bruno Wolff III <[EMAIL PROTECTED]> writes:

> On Fri, Mar 09, 2007 at 23:07:26 -0300,
>   Jorge Godoy <[EMAIL PROTECTED]> wrote:
>> 
>> But how to get the date if the first day of the week is a Wednesday?  This
>> example is like the ones I've sent with separate queries that needed being
>> combined -- in a function, probably -- to get the desired result. 
>
> If you want to group on weeks that start on Wednesdays add 5.

I believe you either missed my post with several queries showing what I wanted
or you didn't understand the point.

If I run this query:

   select date_trunc('week', '2007-03-08'::date + 5);

it fails even for that date.  The correct answer, would be 2007-03-07 and not
2007-03-12.  I want the first day of the week to be Wednesday and hence I want
the Wednesday for the week the date is in.  (Wednesday was arbitrarily chosen,
it could be Thursday, Tuesday, Friday, etc.)


> postgres=# select date_trunc('week', '2007-03-07'::date + 5);
>date_trunc
> 
>  2007-03-12 00:00:00-05
> (1 row)

This should be 2007-03-07 since 2007-03-07 *is* a Wednesday and that's when
the week starts. 

> postgres=# select date_trunc('week', '2007-03-06'::date + 5);
>date_trunc
> 
>  2007-03-05 00:00:00-06
> (1 row)

This should be 2007-02-28 since this is the first day of the week for the week
that starts on Wednesday 2007-02-28 and ends on 2007-03-06. 

> postgres=# select date_trunc('week', '2007-03-08'::date + 5);
>date_trunc
> 
>  2007-03-12 00:00:00-05
> (1 row)

This should return the same date as the first query (2007-03-07).  2007-03-12
is a Monday, and weeks should always start on Wednesday on my arbitrary
question. 


This is why I can't envision a simple query for that but it is easy with a
function.

Again, the function should do something like:

   - make the date calculation (e.g. add some interval or nothing at all...)

   - get the resulting 'dow'

   - if it is > than the arbitrary day that was determined to be the first
 day of the week (Wednesday on my example), then return
 date_trunc('week') + 2 days (2 for moving from Monday to Wednesday,
 for different first days the shift should be different)

   - if it is < than the arbitrary day that was determined to be the first
 day of the week (Wednesday, again), then return date_trunc('week') -
 5 days (-5 for moving from Monday to the previous Wednesday)


The result when asked for the first day should always be the Wednesday that is
equal to the date or that ocurred right before it.  It is the same idea that
is implemented today that returns Monday, but instead of Monday I want another
day that in my posts happened to be exemplified by Wednesday.



I don't want you to expend your time.  It was just a question that got
answered indirectly with a "there's no way to do that without using a
function" due to the complexity above and the lack of such feature in
PostgreSQL.  It is simple to have it as a function, though.

I don't know any RDBMS that implements that.  All of them require some
operations to get the desired result.



Be seeing you,
-- 
Jorge Godoy  <[EMAIL PROTECTED]>

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Tsearch2 / Create rule on select

[Jean-Michel Pouré]

Le vendredi 09 mars 2007 à 10:58 +0100, Magnus Hagander a écrit :
> No idea. Assuming you want to do it beforehand. otherwise, just create
> the index and see how large it got? 

Thank you for your comments. I will add TSeach2 support to phpBB 3.x
soon.

I had incredible response time on simple queries on more than plain-text
200.000 row. About 1 millisecond! Incredible but true.

explain analyse verbose SELECT * FROM phpbb_posts_text WHERE idxfti @@
'jmp'::tsquery limit 100;

Limit  (cost=0.00..444.47 rows=100 width=934) (actual time=0.046..0.824
rows=100 loops=1)
   ->  Index Scan using idxfti_idx on phpbb_posts_text
(cost=0.00..1053.38 rows=237 width=934) (actual time=0.040..0.418
rows=100 loops=1)
 Index Cond: (idxfti @@ '''jmp'''::tsquery)
 [color=red]Total runtime: 1.068 ms

TSearch2 will be used by wikimedia shortly (it is supported in beta
version).

IMHO, it would be better if PostgreSQL parser was able to find Tseach
indexes alone, rewritting the query automatically. Maybe it is on the
radar list of hackers.

Kind regards,
Jean-Michel




---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] index bloat problem

[Monika Cernikova]

I use Postgres 8.1 on linux
I have several tables to which I need insert about 200-500 records per 
minute.
Records contains timestamp (actual time), and this timestamp is part of 
primary key and index.
I need to keep data for 1 month.  I daily delete data older than 1 month 
and than run vacuum analyze.

Size of primary keys and indexes still grows.
(After 2 months database size is about 35G and vacuum runs about 20 hours.
When I dumped and restored database, new size was 25G. Indexes and 
constraints had half size)


Can you help me how to stop index growing or reindex database if I CAN'T 
STOP writing records?


Thanx,

Monika

Here are my settings in postgresql.conf, that differs from defaults

shared_buffers = 2000  

max_fsm_pages = 40  
(it isn't much, but there isn't logged in vacuum log that it needs more)


vacuum_cost_delay = 100   
vacuum_cost_page_hit = 6   
vacuum_cost_limit = 100

wal_buffers = 16
checkpoint_segments = 8

autovacuum = off



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] query ... returned 4 columns

[Sorin Schwimmer]

Thank you, indeed

SELECT * INTO o ...

solves it.

One last question, if I may:
both expected_stuff and archive.expected_stuff are
defined as:

( source CHAR(2);
  warehouse CHAR(1);
  stuff SMALLINT;
  packslip CHAR(12)
);

and o is expected_stuff%ROWTYPE

Having the same structure, I put
INSERT INTO archive.expected_stuff VALUES(o);

but it doesn't work. Instead, I had to rewrite as
INSERT ... VALUES (o.source,o.warehouse...);

Is the short version not supposed to work, or am I
using the wrong syntax?

Thanks again,
Sorin


 

Get your own web address.  
Have a HUGE year through Yahoo! Small Business.
http://smallbusiness.yahoo.com/domains/?p=BESTDEAL

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] Is This A Set Based Solution?

[Stefan Berglund]

Hi-

Below is a small test case that illustrates what I'm attempting which is
to provide a comma separated list of numbers to a procedure which
subsequently uses this list in a join with another table.

My questions are is this a set based solution and is this the best
approach in terms of using the data types and methods afforded by
PostgreSQL?  I'm mostly inquiring about the double FOR loop which just
doesn't feel right to me and I'd also like to feel that I'm generally on
the right track before converting the other 400 procedures from SQL
Server 2000 to PostgreSQL.

CREATE TYPE fn_return_int4 AS (N int);

CREATE TABLE test_table (
  id SMALLINT not null,
  tname varchar(50) not null);

INSERT INTO test_table
  SELECT 1, 'Adams'
UNION SELECT 2, 'Baker'
UNION SELECT 3, 'Chrysler'
UNION SELECT 4, 'Douglas'
UNION SELECT 5, 'Everyman';

CREATE OR REPLACE FUNCTION fn_Split_List (
  pList TEXT) RETURNS SETOF fn_return_int4 AS $fn_Split_List$

DECLARE
  v_row fn_return_int4%rowtype;
  v_list alias for $1;
  v_delim text := ',';
  v_arr text[];

BEGIN
  v_arr := string_to_array(v_list, v_delim);
  FOR i IN array_lower(v_arr, 1)..array_upper(v_arr, 1) LOOP
FOR v_row IN SELECT v_arr[i] LOOP
  RETURN NEXT v_row;
END LOOP;
  END LOOP;
RETURN;
END;
$fn_Split_List$ LANGUAGE plpgsql;

SELECT *
FROM
  fn_Split_List('5,1,3') SL INNER JOIN
  test_table T ON SL.N=T.ID;

I did discover that I was able to define the function with a native type
but then the usage looked a little odd:

SELECT *
FROM
  fn_Split_List('5,1,3') SL INNER JOIN
  test_table T ON SL=T.ID;

Stefan Berglund
www.horseshowtime.com
Online Show Entry - Instant Internet Horse Show Schedules and Results
[EMAIL PROTECTED]
tel  714.968.9112   fax  714.968.5940

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Solaris and Ident

[D Unit]

I can't get the .pgpass file working. I think the problem may have to do with
the fact that the user's home directory is '/'. Is there a way to specify a
different location for .pgpass other than '~/.pgpass'?



-- 
View this message in context: 
http://www.nabble.com/Solaris-and-Ident-tf3371429.html#a9400075
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] OT: Canadian Tax Database

[Patrick TJ McPhee]

In article <[EMAIL PROTECTED]>,
Richard Huxton  wrote:
% http://www.thestar.com/News/article/189175
% 
% "For instance, in some cases the field for the social insurance number 
% was instead filled in with a birth date."
% 
% Unbelievable. Sixty years of electronic computing, fifty years use in 
% business and the "professionals" who built the tax system for a wealthy 
% democratic country didn't use data types.

To be fair, this is not "the tax system". It's a staging database
used for electronic filing, and it's pretty common to use typeless
databases in the first stage of that sort of application.
-- 

Patrick TJ McPhee
North York  Canada
[EMAIL PROTECTED]

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Trigger for Audit Table

[[EMAIL PROTECTED]]

You can/should create it as an AFTER UPDATE trigger.  The OLD row will
contain the previous values.

eg:
  INSERT INTO template_history
  ( template_id, path, content, last_updated_time, person )
  values
  (OLD.id, OLD.path, OLD.content, OLD.last_updated_time, OLD.person);

On Mar 9, 2:45 pm, [EMAIL PROTECTED] (Bill Moseley) wrote:

> My trigger is very simple:
>
> CREATE OR REPLACE FUNCTION audit_template() RETURNS TRIGGER AS '
> BEGIN
> INSERT INTO template_history
> ( template_id, path, content, last_updated_time, 
> person )
> select
> id, path, content, last_updated_time, person
> from
> template where id = 1;
>
> RETURN NEW;
> END'
> language 'plpgsql';
>
> CREATE TRIGGER template_history_add BEFORE UPDATE ON template
> for each row execute procedure audit_template();
>
> I realize this is a *BEFORE* UPDATE trigger, but I have this vague
> memory of seeing a post stating that you can't be sure the existing
> row has not been updated yet. Perhaps that was just a concern if
> another trigger was to modify the row.  But, I can't seem to find that
> post now which is why I'm asking for the sanity check.
>
> Are there potential problems with this setup?
>
> --
> Bill Moseley
> [EMAIL PROTECTED]
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] Re: Anyone know a good opensource CRM that actually installs with Posgtres?

[lneves]

Hello,

Bradley Kieser wrote:
I hope that someone has cracked this one because I have run into a brick 
wall the entire week and after 3 all-nighters with bad installations, I 
would appreciate hearing from others!




[...]


Compiere doesn't support PG.


You could checkout Adempiere wich is a fork of Compiere:


They claim to support PG and they are actively improving on that:


Download location:



I have tried numerous other CRMs but all the same - either don't run on 
PG, claim to but in reality don't or are simply pre-Alpha and not ready 
for production use.


On the surface Adempiere seems to meet your requirements but I never used it so I 
can't tell if its another dead end.


--
Luis Neves


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Setting week starting day

[Omar Eljumaily]

But you're always returning Monday, right?  Your grouping will be 
correct, but to get the actual truncation date, you have to subtract back.


select (date_trunc('week', '2007-03-07'::date + 5)::date-5);
select (date_trunc('week', '2007-03-06'::date + 5)::date-5);
select (date_trunc('week', '2007-03-08'::date + 5)::date-5);


Bruno Wolff III wrote:

On Fri, Mar 09, 2007 at 23:07:26 -0300,
  Jorge Godoy <[EMAIL PROTECTED]> wrote:
  

But how to get the date if the first day of the week is a Wednesday?  This
example is like the ones I've sent with separate queries that needed being
combined -- in a function, probably -- to get the desired result. 



If you want to group on weeks that start on Wednesdays add 5.

postgres=# select date_trunc('week', '2007-03-07'::date + 5);
   date_trunc

 2007-03-12 00:00:00-05
(1 row)

postgres=# select date_trunc('week', '2007-03-06'::date + 5);
   date_trunc

 2007-03-05 00:00:00-06
(1 row)

postgres=# select date_trunc('week', '2007-03-08'::date + 5);
   date_trunc

 2007-03-12 00:00:00-05
(1 row)

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
  



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] Setting week starting day

[Bruno Wolff III]

On Fri, Mar 09, 2007 at 23:07:26 -0300,
  Jorge Godoy <[EMAIL PROTECTED]> wrote:
> 
> But how to get the date if the first day of the week is a Wednesday?  This
> example is like the ones I've sent with separate queries that needed being
> combined -- in a function, probably -- to get the desired result. 

If you want to group on weeks that start on Wednesdays add 5.

postgres=# select date_trunc('week', '2007-03-07'::date + 5);
   date_trunc

 2007-03-12 00:00:00-05
(1 row)

postgres=# select date_trunc('week', '2007-03-06'::date + 5);
   date_trunc

 2007-03-05 00:00:00-06
(1 row)

postgres=# select date_trunc('week', '2007-03-08'::date + 5);
   date_trunc

 2007-03-12 00:00:00-05
(1 row)

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Setting week starting day

[Jorge Godoy]

Bruno Wolff III <[EMAIL PROTECTED]> writes:

> On Fri, Mar 09, 2007 at 20:13:11 -0300,
>   Jorge Godoy <[EMAIL PROTECTED]> wrote:
>> Bruno Wolff III <[EMAIL PROTECTED]> writes:
>> 
>> > No, it has to be inside the function so that the modular arithmetic is
>> > applied to it.
>> 
>> Then there's the error I've shown from your command.  Can you give me a
>> working one?  This was with PostgreSQL 8.2.3.
>
> postgres=# select date_trunc('week', current_date + 1);
>date_trunc
> 
>  2007-03-05 00:00:00-06
> (1 row)
>
> It turns out DOW isn't available for date_trunc. You can probably use
> extract to get what you want. You probably should check that it works
> at DST transitions, since the date value is cast to a timestamp and
> if DST transitions happen at  in your time zone, you might get an
> unexpected answer.
>
> postgres=# select extract(dow from current_date + 1);
>  date_part
> ---
>  6
> (1 row)

But how to get the date if the first day of the week is a Wednesday?  This
example is like the ones I've sent with separate queries that needed being
combined -- in a function, probably -- to get the desired result. 

-- 
Jorge Godoy  <[EMAIL PROTECTED]>

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: HIPPA (was Re: [GENERAL] Anyone know ...)

[Tom Lane]

Kenneth Downs <[EMAIL PROTECTED]> writes:
> The biggest security limitation we have is actually a weakness in 
> Postgres - the inability to restrict the abilities of a user with 
> CREATUSER rights, they can make somebody who can do anything.  For 
> higher security this requires no ability for public registration of 
> accounts.  This would be solved if we could restrict a CREATUSER user to 
> only GRANTing to roles they themselves are in.

I thought about this for awhile, but I think you are missing the reason
why it's designed the way it is.  The point of CREATEROLE privilege is
to be a slightly safer form of superuser: that is, to allow the DBA to
do all his day-to-day management of user accounts without being a real
superuser who can corrupt the database arbitrarily badly.  If we
restricted CREATEROLE as you suggest, then either DBAs would have to
make their CREATEROLE account a member of every role they manage, or
they'd have to run as real superusers.  Either choice represents a
significant increase in the capabilities of the CREATEROLE account and
thus more chance for mistakes.  So while a miscreant with CREATEROLE
can certainly avail himself of any database privilege short of
superuserness, in the intended use of the feature it is actually
possible for DBAs to operate with *fewer* privileges than they would
need to get useful work done if we adopted your suggestion.

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Setting week starting day

[Bruno Wolff III]

On Fri, Mar 09, 2007 at 20:13:11 -0300,
  Jorge Godoy <[EMAIL PROTECTED]> wrote:
> Bruno Wolff III <[EMAIL PROTECTED]> writes:
> 
> > No, it has to be inside the function so that the modular arithmetic is
> > applied to it.
> 
> Then there's the error I've shown from your command.  Can you give me a
> working one?  This was with PostgreSQL 8.2.3.

postgres=# select date_trunc('week', current_date + 1);
   date_trunc

 2007-03-05 00:00:00-06
(1 row)

It turns out DOW isn't available for date_trunc. You can probably use
extract to get what you want. You probably should check that it works
at DST transitions, since the date value is cast to a timestamp and
if DST transitions happen at  in your time zone, you might get an
unexpected answer.

postgres=# select extract(dow from current_date + 1);
 date_part
---
 6
(1 row)

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Trigger for Audit Table

[Tom Lane]

Bill Moseley <[EMAIL PROTECTED]> writes:
> On Fri, Mar 09, 2007 at 06:50:39PM -0500, Tom Lane wrote:
>> This is not going to work because the row's not there yet.

> This is a BEFORE *UPDATE* trigger, not a BEFORE INSERT, so the row is
> there.  The audit table is written when the primary record changes
> and the old version is written to the audit table, not the new
> version.

Well, if you want to write the old data, it's still a silly way to do
it: write the OLD.* tuple instead of forcing a fresh search of the
table.

> Ok, but as the id is a sequence.  I need to test if NEW.id is set after
> the insert -- seems like not, IIRC, and  I'd need to use curval().

You're confusing rules with triggers.  In a trigger, NEW.* and OLD.*
are physical rows and you don't need to worry about multi evaluation
or anything like that.

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Statistics

[Jeff Davis]

On Fri, 2007-03-09 at 14:40 -0300, Ezequias Rodrigues da Rocha wrote:
> 
> On Fri, 2007-03-09 at 14:22 -0300, Ezequias Rodrigues da Rocha wrote:
>  Does someone have statistcs from PostgreSQL ? Numbers from the list,
>  performance statistics. I must argue with another person the idea of
> 
>  do not put Oracle in our organization.
> 
>  We are quite well with postgresql and I have no plans to change my
>  plataform.
> 

If PostgreSQL is working well, then continue using it.

If you (or another person) are concerned about scalability for the
future, call one of the commercial PostgreSQL support companies listed
here:

http://www.postgresql.org/support/professional_support

They can guide you through the process to make sure that your
implementation scales according to your needs.

Or, just ask more specific questions on this list, and people will be
glad to help.

Regards,
Jeff Davis


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Trigger for Audit Table

[Bill Moseley]

On Fri, Mar 09, 2007 at 06:50:39PM -0500, Tom Lane wrote:
> Bill Moseley <[EMAIL PROTECTED]> writes:
> > I'm asking for a sanity check:
> 
> > And then an audit table:
> 
> > create table template_history (
> > id  SERIAL PRIMARY KEY,
> > template_id integer NOT NULL REFERENCES template ON DELETE 
> > CASCADE,
> > pathtext NOT NULL,
> > content text NOT NULL,
> > last_updated_time   timestamp(0) with time zone NOT NULL
> > );
> 
> Why would you want ON DELETE CASCADE?  Or for that matter to have a
> foreign key here at all?  Surely the point of an audit table is to
> remember history.  If the audit entries all disappear the instant
> the main-table entry is deleted, it's not much of an audit tool.

In this case the templates are short lived so only want to track
history while the "live" record is around.  That is, it's expected
that the template and all its history will get wiped out once in a while.

Still, I agree with you that it would be better to use a different
approach and not cascade delete.

The foreign key is there so can find the history related to the primary record.
That's what ties the history records together (the path can change
during the life of the template).

> 
> > My trigger is very simple:
> 
> > CREATE OR REPLACE FUNCTION audit_template() RETURNS TRIGGER AS '
> > BEGIN
> > INSERT INTO template_history
> > ( template_id, path, content, last_updated_time, 
> > person )
> > select
> > id, path, content, last_updated_time, person
> > from
> > template where id = 1;
> > RETURN NEW;
> > END'
> > language 'plpgsql';
> 
> This is not going to work because the row's not there yet.

This is a BEFORE *UPDATE* trigger, not a BEFORE INSERT, so the row is
there.  The audit table is written when the primary record changes
and the old version is written to the audit table, not the new
version.

Yes, it's more common to write the audit for every insert and update
(so the most recent version is also in the audit table).


> (I won't bother pointing out the thinko in the WHERE clause);

Darn cut-n-paste errors


> and even if it did
> work it'd be unnecessarily inefficient.  Just use the NEW row that's
> passed to the trigger:
> 
>   INSERT INTO template_history(...) VALUES(NEW.id, NEW.path, ...)

Ok, but as the id is a sequence.  I need to test if NEW.id is set after
the insert -- seems like not, IIRC, and  I'd need to use curval().


> If you have other BEFORE triggers on this table that can change the
> NEW row, then it might be better to make this an AFTER trigger so it can
> be sure the NEW row it sees won't change anymore.  But AFTER triggers
> are distinctly less efficient, so if you're not intending to add more
> triggers then using a BEFORE trigger is probably the way to go.

It's just that pesky sequence I need access to after the insert
happens.


Thanks for the tips, Tom.

-- 
Bill Moseley
[EMAIL PROTECTED]


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Trigger for Audit Table

[Tom Lane]

Bill Moseley <[EMAIL PROTECTED]> writes:
> I'm asking for a sanity check:

> And then an audit table:

> create table template_history (
> id  SERIAL PRIMARY KEY,
> template_id integer NOT NULL REFERENCES template ON DELETE 
> CASCADE,
> pathtext NOT NULL,
> content text NOT NULL,
> last_updated_time   timestamp(0) with time zone NOT NULL
> );

Why would you want ON DELETE CASCADE?  Or for that matter to have a
foreign key here at all?  Surely the point of an audit table is to
remember history.  If the audit entries all disappear the instant
the main-table entry is deleted, it's not much of an audit tool.

> My trigger is very simple:

> CREATE OR REPLACE FUNCTION audit_template() RETURNS TRIGGER AS '
> BEGIN
> INSERT INTO template_history
> ( template_id, path, content, last_updated_time, 
> person )
> select
> id, path, content, last_updated_time, person
> from
> template where id = 1;
> RETURN NEW;
> END'
> language 'plpgsql';

This is not going to work because the row's not there yet (I won't
bother pointing out the thinko in the WHERE clause); and even if it did
work it'd be unnecessarily inefficient.  Just use the NEW row that's
passed to the trigger:

INSERT INTO template_history(...) VALUES(NEW.id, NEW.path, ...)

If you have other BEFORE triggers on this table that can change the
NEW row, then it might be better to make this an AFTER trigger so it can
be sure the NEW row it sees won't change anymore.  But AFTER triggers
are distinctly less efficient, so if you're not intending to add more
triggers then using a BEFORE trigger is probably the way to go.

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Beginner's Questions

[Damian C]

We produce and sell a Java desktop app, distributed in an office
(~1-10 Users), with Postgres as the central data store.  The users are
"technically illiterate", and they often have very low spec hardware.
It does all work very well.

Note that for postgres you will need NTFS (WIN32 is not possible).

Our user interface is Java, built with Netbeans IDE, and is based on
the Netbeans "Platform" (to provide lots of lifty infrastructure). We
are Object Oriented developers, so we are nearly "SQL illiterate", so
we do all our Postgres interaction via Hibernate (Object Relational
Mapper"), and it works really well.

All our tools and deliverables are free open source or built on free
open source. This is necessary as we are an self-funded startup who
eventually hope to sell this system into a thousand sites, so paying
license fees for DB (Oracle anyone?) or other deliverables per site
really changes the economics of our offering.

I must say the software stack we have (including postgres) is awesome,
although it does take a lot of effort to learn. The system (Java and
Postgres server) is OK on a 500MHz PIII, but the issue is that it
really does need 500MB RAM to work properly.

All the best.
-Damian

On 3/10/07, Don Lavelle <[EMAIL PROTECTED]> wrote:

Hi, all,

Thank you all for your help!  From what I've gathered, similarly
sized projects run on 100 MB of disk space and a 450 MHz processor.
My GUI and application logic aren't going to need much more than
that, so I should be good to go!

PostgreSQL it is!  I'm sure I will have many, many more questions as
I continue the development process.

Cheers!

 Don

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings



---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Setting week starting day

[Jorge Godoy]

"Ted Byers" <[EMAIL PROTECTED]> writes:

> I  don't buy the suggestion that server side code is less error prone that
> client side code, but be that as it may, we're talking about a function that
> has one line of code.  And given what you just said, you don't want the day of
> the week, you want a function that returns the week of the year.  This can be
> had from the same Perl functions I mentioned before, with a minor alteration
> in how you call it.  my suggestion would be to create that one line function
> that invokes the relevant Perl function, which can then be invoked in your
> select statement (presumably with a group clause to avoid mixing data from
> different years).  It should take about ten to fifteen minutes to write and
> test?


There's no need to use Perl. 

neo=# select extract('week' from now());
 date_part 
---
10
(1 registro)

neo=# 


Today is a day at the tenth week of the year.




-- 
Jorge Godoy  <[EMAIL PROTECTED]>

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Setting week starting day

[Jorge Godoy]

Omar Eljumaily <[EMAIL PROTECTED]> writes:

> Ted, my reason for asking the question that I believe precipitated this thread
> was that I wanted a single sql statement that aggregated time data by week.
> Yes, I could do the aggregation subsequently in my own client side code, but
> it's easier and less error prone to have it done by the server.

If you work closer to the data you have more efficiency.  To do what you want
you can write a function using plpgsql -- I've posted some ideas -- and that
is not hard at all.

But if there was something to make it easier to write this it would be great ;-)

-- 
Jorge Godoy  <[EMAIL PROTECTED]>

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Setting week starting day

[Jorge Godoy]

"Ted Byers" <[EMAIL PROTECTED]> writes:

> Out of curiosity, why does the database need to know this, or to be able to
> calculate it?  There are lots of things that would be useful to me, if the

It was a curiosity.  But it would make working with some dates easier.  I've
given some examples but if you really want I may search for the messages and
repost them for you. 

> RDBMS I'm using at the time supported them (particularly certain statistical
> functions - ANOVA, MANOVA, nonlinear least squares regression, time series
> analysis, &c.), but given that I can readily obtain these from other software
> I use, and can if necessary put the requisite code in a middleware component,
> I would rather have the PostgreSQL developer's focus on issues central to

You can have those using R and plR inside the database. ;-)

> having a good DB, such as ANSI standard compliance for SQL, or robust pooling,
> &c. and just leave me a mechanism for calling functions that are external to
> the database for the extra stuff I need.  I would prefer a suite of
> applications that each does one thing well than a single application that does
> a mediocre job on everything it allegedly supports. What would be 'nice' and
> what is practical are often very different things. I know what you're after is
> simple, but remember the good folk responsible for PostgreSQL have only finite
> time available to work on it, and thus, when they're making choices about
> priorities, I'd rather they ignore even simple ancillary stuff and focus on
> what really matters.

If I have to do calculations with dates inside the database the worst thing
I'd like to do was retrieving part of it, going to some external code, coming
back to the database and so on.

If there was something inside the database then I'd really like to know and
use it.  I don't see how worse it would be when compared to other non-ANSI
extensions that are already available.

> I just recently finished a project in which the data processing needed
> information similar to what you're after, but instead of doing it in the
> database, we opted to do it in the Perl script I wrote that fed data to the
> database.  In fact, it wasn't so much the day of the week that mattered to the

There's no feeding here.  Imagine that I'm filtering huge selects to be
processed externaly.  I wouldn't like to get some millions of rows instead of
hundreds or a few thousands of them.

> processing algorithm but the resulting dates for the immediately preceding
> business day and the immediately following business day.  It was those dates
> we fed to the database rather than the weekday.  There are several Perl
> packages (see CPAN) supporting this kind of calculation.  These are generally

I know Perl.  I have already thought it for IBM... ;-)

> outstanding (and would probably be useful if you want to create your own
> stored function implemented in Perl), but you may have to customize them by
> providing additional configuration information such as timezone and statutory
> and religious holidays if you need to determine business days in addition to
> just the day of the week.  the day of the week can be obtained in Perl with a
> single function call!

As in several other languages.  Even in plpgsql, with simple calculations like
I've shown.  As I said, writing a function for that is simple enough and I
just wanted to know if there was anything that could be done by the database.

I never asked for any new implementation.

> I just took a quick break to read about the date functions available within
> PostgreSQL, and while apparently nice, you have much greater flexibility, and
> many more functions, in these Perl packages I mentioned.  If you just want a
> function call, I'd suggest you create a function that just dispatches a call
> to the Perl function that best meets your needs.  In a sense, you are not
> really rolling your own.  You're just dispatching the call to a function in a
> Perl package.

And to do that you have to write a function...


-- 
Jorge Godoy  <[EMAIL PROTECTED]>

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Setting week starting day

[Jorge Godoy]

Bruno Wolff III <[EMAIL PROTECTED]> writes:

> No, it has to be inside the function so that the modular arithmetic is
> applied to it.

Then there's the error I've shown from your command.  Can you give me a
working one?  This was with PostgreSQL 8.2.3.

-- 
Jorge Godoy  <[EMAIL PROTECTED]>

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Setting week starting day

[Ted Byers]

- Original Message - 
From: "Omar Eljumaily" <[EMAIL PROTECTED]>

To: "Ted Byers" <[EMAIL PROTECTED]>
Cc: 
Sent: Friday, March 09, 2007 5:00 PM
Subject: Re: [GENERAL] Setting week starting day


Ted, my reason for asking the question that I believe precipitated this 
thread was that I wanted a single sql statement that aggregated time data 
by week.  Yes, I could do the aggregation subsequently in my own client 
side code, but it's easier and less error prone to have it done by the 
server.


I  don't buy the suggestion that server side code is less error prone that 
client side code, but be that as it may, we're talking about a function that 
has one line of code.  And given what you just said, you don't want the day 
of the week, you want a function that returns the week of the year.  This 
can be had from the same Perl functions I mentioned before, with a minor 
alteration in how you call it.  my suggestion would be to create that one 
line function that invokes the relevant Perl function, which can then be 
invoked in your select statement (presumably with a group clause to avoid 
mixing data from different years).  It should take about ten to fifteen 
minutes to write and test?


Ted 




---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] Weird behaviour on a join with multiple keys

[Tom Lane]

Charlie Clark <[EMAIL PROTECTED]> writes:
> Am 09.03.2007 um 16:15 schrieb Tom Lane:
>> There's your problem right there.  The string comparison routines are
>> built on strcoll(), which is going to expect UTF8-encoded data because
>> of the LC_COLLATE setting.  If there are any high-bit-set LATIN1
>> characters in the database, they will most likely look like invalid
>> encoding to strcoll(), and on most platforms that causes it to behave
>> very oddly.  You need to keep lc_collate (and lc_ctype) in sync with
>> server_encoding.

> That does indeed seem to have been the problem even though the  
> examples I was looking at were all using plain ASCII characters. Glad  
> to know it wasn't a bug and to have learned something new.

Well, it *is* a bug: we really shouldn't let you select incompatible
locale and encoding settings.  This gotcha has been known for a long
time, but it's not clear that there's a bulletproof, portable way to
determine which encoding a particular locale setting implies ...

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Beginner's Questions

[Don Lavelle]

Hi, all,

Thank you all for your help!  From what I've gathered, similarly  
sized projects run on 100 MB of disk space and a 450 MHz processor.   
My GUI and application logic aren't going to need much more than  
that, so I should be good to go!


PostgreSQL it is!  I'm sure I will have many, many more questions as  
I continue the development process.


Cheers!

Don

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] Yet another PostgreSQL C++ binding

[Phil Endecott]

Dear All,

I've written a C++ PostgreSQL interface library which I use in a couple 
of open-source applications, and I thought that I would mention it here 
in case it could be of use to anyone.  Yes, I know there are already 
several such libraries, but I believe mine has a unique feature: 
queries are functors that use prepared statements.  Here's an example:


  Database db("dbname=foo, username=blah");
  Query insert_thing(db,"insert into things(name,num) 
values ($1,$2)");
  SingletonQuery count_things(db,"select sum(num) from 
things where name=$1");


  Transaction t(db);
  insert_thing("table",1);
  insert_thing("chair",4);
  int n = count_things("bed");
  t.commit();

Note how the queries are declared using the $n placeholder syntax for 
parameters.  Once the queries are declared the fact that they are 
queries can be almost forgotten - they can be used as functions.


I have written up some basic documentation here:

http://svn.chezphil.org/libpbe/trunk/doc/Database

Do let me know if you find this useful.

Regards,

Phil.

(I encourage you to Cc: me in any replies.)





---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Weird behaviour on a join with multiple keys

[Charlie Clark]

Am 09.03.2007 um 16:15 schrieb Tom Lane:


psytec=# show lc_collate;
lc_collate
-
de_DE.UTF-8
(1 row)



psytec=# show server_encoding;
server_encoding
-
LATIN1
(1 row)


There's your problem right there.  The string comparison routines are
built on strcoll(), which is going to expect UTF8-encoded data because
of the LC_COLLATE setting.  If there are any high-bit-set LATIN1
characters in the database, they will most likely look like invalid
encoding to strcoll(), and on most platforms that causes it to behave
very oddly.  You need to keep lc_collate (and lc_ctype) in sync with
server_encoding.


That does indeed seem to have been the problem even though the  
examples I was looking at were all using plain ASCII characters. Glad  
to know it wasn't a bug and to have learned something new.


Charlie
--
Charlie Clark
Helmholtzstr. 20
Düsseldorf
D- 40215
Tel: +49-211-938-5360
GSM: +49-178-782-6226




---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Setting week starting day

[Omar Eljumaily]

Ted, my reason for asking the question that I believe precipitated this 
thread was that I wanted a single sql statement that aggregated time 
data by week.  Yes, I could do the aggregation subsequently in my own 
client side code, but it's easier and less error prone to have it done 
by the server.



Ted Byers wrote:

It is not hard to calculate, as you can see... but it would be nice if
"date_trunc('week', date)" could do that directly.  Even if it became
"date_trunc('week', date, 4)" or "date_trunc('week', date, 
'Wednesday')" it

would be nice...  :-)  And that is what I was trying to ask ;-)


Use date_trunc('week', current_day + 1) and date_trunc('dow', 
current_day + 1)

to have a one day offset from the standard first day of the week.



I believe there's more than that...  Probably the "+1" should be 
outside the

date_trunc, anyway.  It might help, but I still see the need to to do
calculations...  Specially if it was Tuesday today...


Out of curiosity, why does the database need to know this, or to be 
able to calculate it?  There are lots of things that would be useful 
to me, if the RDBMS I'm using at the time supported them (particularly 
certain statistical functions - ANOVA, MANOVA, nonlinear least squares 
regression, time series analysis, &c.), but given that I can readily 
obtain these from other software I use, and can if necessary put the 
requisite code in a middleware component, I would rather have the 
PostgreSQL developer's focus on issues central to having a good DB, 
such as ANSI standard compliance for SQL, or robust pooling, &c. and 
just leave me a mechanism for calling functions that are external to 
the database for the extra stuff I need.  I would prefer a suite of 
applications that each does one thing well than a single application 
that does a mediocre job on everything it allegedly supports. What 
would be 'nice' and what is practical are often very different things. 
I know what you're after is simple, but remember the good folk 
responsible for PostgreSQL have only finite time available to work on 
it, and thus, when they're making choices about priorities, I'd rather 
they ignore even simple ancillary stuff and focus on what really matters.


I just recently finished a project in which the data processing needed 
information similar to what you're after, but instead of doing it in 
the database, we opted to do it in the Perl script I wrote that fed 
data to the database.  In fact, it wasn't so much the day of the week 
that mattered to the processing algorithm but the resulting dates for 
the immediately preceding business day and the immediately following 
business day.  It was those dates we fed to the database rather than 
the weekday.  There are several Perl packages (see CPAN) supporting 
this kind of calculation.  These are generally outstanding (and would 
probably be useful if you want to create your own stored function 
implemented in Perl), but you may have to customize them by providing 
additional configuration information such as timezone and statutory 
and religious holidays if you need to determine business days in 
addition to just the day of the week.  the day of the week can be 
obtained in Perl with a single function call!


I just took a quick break to read about the date functions available 
within PostgreSQL, and while apparently nice, you have much greater 
flexibility, and many more functions, in these Perl packages I 
mentioned.  If you just want a function call, I'd suggest you create a 
function that just dispatches a call to the Perl function that best 
meets your needs.  In a sense, you are not really rolling your own.  
You're just dispatching the call to a function in a Perl package.


Cheers

Ted


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings



---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Setting week starting day

[Ted Byers]

It is not hard to calculate, as you can see... but it would be nice if
"date_trunc('week', date)" could do that directly.  Even if it became
"date_trunc('week', date, 4)" or "date_trunc('week', date, 'Wednesday')" 
it

would be nice...  :-)  And that is what I was trying to ask ;-)


Use date_trunc('week', current_day + 1) and date_trunc('dow', current_day 
+ 1)

to have a one day offset from the standard first day of the week.



I believe there's more than that...  Probably the "+1" should be outside 
the

date_trunc, anyway.  It might help, but I still see the need to to do
calculations...  Specially if it was Tuesday today...


Out of curiosity, why does the database need to know this, or to be able to 
calculate it?  There are lots of things that would be useful to me, if the 
RDBMS I'm using at the time supported them (particularly certain statistical 
functions - ANOVA, MANOVA, nonlinear least squares regression, time series 
analysis, &c.), but given that I can readily obtain these from other 
software I use, and can if necessary put the requisite code in a middleware 
component, I would rather have the PostgreSQL developer's focus on issues 
central to having a good DB, such as ANSI standard compliance for SQL, or 
robust pooling, &c. and just leave me a mechanism for calling functions that 
are external to the database for the extra stuff I need.  I would prefer a 
suite of applications that each does one thing well than a single 
application that does a mediocre job on everything it allegedly supports. 
What would be 'nice' and what is practical are often very different things. 
I know what you're after is simple, but remember the good folk responsible 
for PostgreSQL have only finite time available to work on it, and thus, when 
they're making choices about priorities, I'd rather they ignore even simple 
ancillary stuff and focus on what really matters.


I just recently finished a project in which the data processing needed 
information similar to what you're after, but instead of doing it in the 
database, we opted to do it in the Perl script I wrote that fed data to the 
database.  In fact, it wasn't so much the day of the week that mattered to 
the processing algorithm but the resulting dates for the immediately 
preceding business day and the immediately following business day.  It was 
those dates we fed to the database rather than the weekday.  There are 
several Perl packages (see CPAN) supporting this kind of calculation.  These 
are generally outstanding (and would probably be useful if you want to 
create your own stored function implemented in Perl), but you may have to 
customize them by providing additional configuration information such as 
timezone and statutory and religious holidays if you need to determine 
business days in addition to just the day of the week.  the day of the week 
can be obtained in Perl with a single function call!


I just took a quick break to read about the date functions available within 
PostgreSQL, and while apparently nice, you have much greater flexibility, 
and many more functions, in these Perl packages I mentioned.  If you just 
want a function call, I'd suggest you create a function that just dispatches 
a call to the Perl function that best meets your needs.  In a sense, you are 
not really rolling your own.  You're just dispatching the call to a function 
in a Perl package.


Cheers

Ted 




---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: HIPPA (was Re: [GENERAL] Anyone know ...)

[Karsten Hilbert]

On Fri, Mar 09, 2007 at 12:22:19PM -0500, Kenneth Downs wrote:

> >My interest was more towards the "we get an email" part.
> >What level do you send that from ? A trigger ?
> >
> The web framework does that.
I see. IOW if a violation happens below the web layer the
e-mail doesn't get send. I thought you had maybe written a
trigger to do it in, say, pl/Perl or so.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Setting week starting day

[Bruno Wolff III]

On Fri, Mar 09, 2007 at 16:44:57 -0300,
  Jorge Godoy <[EMAIL PROTECTED]> wrote:
> Bruno Wolff III <[EMAIL PROTECTED]> writes:
> 
> > On Fri, Mar 09, 2007 at 14:59:35 -0300,
> >   Jorge Godoy <[EMAIL PROTECTED]> wrote:
> >> It is not hard to calculate, as you can see... but it would be nice if
> >> "date_trunc('week', date)" could do that directly.  Even if it became
> >> "date_trunc('week', date, 4)" or "date_trunc('week', date, 'Wednesday')" it
> >> would be nice...  :-)  And that is what I was trying to ask ;-)
> >
> > Use date_trunc('week', current_day + 1) and date_trunc('dow', current_day + 
> > 1)
> > to have a one day offset from the standard first day of the week. 
> 
> 
> I believe there's more than that...  Probably the "+1" should be outside the
> date_trunc, anyway.  It might help, but I still see the need to to do
> calculations...  Specially if it was Tuesday today...

No, it has to be inside the function so that the modular arithmetic is
applied to it.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] "oracle to postgresql" conversion

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/09/07 14:53, Chris Fischer wrote:
> All of Oracle's (non-float) number types are variable size
> numbers with an ordinal and a mantissa.  This makes Oracle number
> very efficient for smaller values as compared to fixed size
> integers, but less efficient with larger values.  NUMBER has a
> maximum precision of 38 digits with a scale of -84 to +127.
> NUMBER consumes between 1 and 22 bytes on disk.  It is typical to
> specify a NUMBER with (p, s).  In the absence of definition,
> precision of 38 and scale indeterminate will be assumed.
> 
> The exception to this are IEEE floating point number types which
> are a fixed size regardless of value.
> 
> Summary: Oracle has no fixed length equivlents to tinyint,
> smallint, int or bigint from other databases and can either store
> these values more or less efficiently than those databases with
> fixed length integer types.

Wow  Didn't believe you (Oracle couldn't be *that* lame, could
it?), so I Googled.

According to Table 12-1 of this web page, Oracle will silently
truncate your numbers.  There are no scalar data types
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c13datyp.htm


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF8dDjS9HxQb37XmcRArCMAKDAFuUM2V804Zjdurr6eemqPyHHOwCg1oGk
8RxOTImJVBUqdBhHK6tezkA=
=ibbT
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] "oracle to postgresql" conversion

[Chris Fischer]

All of Oracle's (non-float) number types are variable size numbers with an 
ordinal and a mantissa.  This makes Oracle number very efficient for smaller 
values as compared to fixed size integers, but less efficient with larger 
values.  NUMBER has a maximum precision of 38 digits with a scale of -84 to 
+127.  NUMBER consumes between 1 and 22 bytes on disk.  It is typical to 
specify a NUMBER with (p, s).  In the absence of definition, precision of 38 
and scale indeterminate will be assumed.

The exception to this are IEEE floating point number types which are a fixed 
size regardless of value.

Summary: Oracle has no fixed length equivlents to tinyint, smallint, int or 
bigint from other databases and can either store these values more or less 
efficiently than those databases with fixed length integer types.

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Scott Marlowe
Sent: Friday, March 09, 2007 1:58 PM
To: Devrim GÜNDÜZ
Cc: Shane Ambler; Kevin Hunter; [EMAIL PROTECTED]; PostgreSQL General List
Subject: Re: [GENERAL] "oracle to postgresql" conversion

On Thu, 2007-03-08 at 16:05, Devrim GÜNDÜZ wrote:
> Hi,
> 
> On Fri, 2007-03-09 at 05:21 +1030, Shane Ambler wrote:
> > NUMBER is Oracle's version of NUMERIC - Oracle will use both but 
> > probably only Oracle will use NUMBER.
> 
> Really? I thought Oracle's NUMBER ~ PostgreSQL's (BIG)INT?

Not sure.  It let me assign a precision to it, so I figured it wasn't int 
based.  In fact, it accepts precision up to 38, just like numeric, and it 
accepts non-decimal portions, i.e.:

number(20,4);

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Anyone know a good opensource CRM that actually installs with Posgtres?

[Steve Atkins]

On Mar 9, 2007, at 11:35 AM, Brandon Aiken wrote:


Why is running on PG so important?  Why not look for the best CRM
application for your user's needs?


There can be many reasons - mostly related to the fact that the
business needs are at least as important, if not more so, than
the user needs.

Running a second corporate database doubles DBA
and sysadmin overhead (let alone the amount of learning needed
to support it effectively, including maintenance, tuning, backup,
disaster recovery, security, updates...).

Also, I wouldn't want any business-critical CRM data on a database
I didn't trust to not lose or corrupt it. I'm still undecided on whether
I trust databases beginning with "M" that much, and I'm certainly
not as confident in them as I am in PG. I quite like SugarCRM, but
I'm not ready to rely on it, mostly for this reason.

Because I may want to modify it to meet my needs (which also
answers the "why open source rather than proprietary?" question),
which requires an even more intimate knowledge of the quirks of
the database than basic maintenance.

Cheers,
  Steve


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] Anyone know a good opensource CRM that actually installs with Posgtres?

[Merlin Moncure]

On 3/9/07, Brandon Aiken <[EMAIL PROTECTED]> wrote:

Why is running on PG so important?  Why not look for the best CRM
application for your user's needs?


probably because he wants to interface his own systems on it that are
already running on postgresql.

merlin

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

[GENERAL] Trigger for Audit Table

[Bill Moseley]

I'm asking for a sanity check:


This is a very simple audit table setup where I use a BEFORE UPDATE
trigger to save an existing record.

The table stores templates (for a CMS) and looks something like this:

create table template (
id  SERIAL PRIMARY KEY,
pathtext UNIQUE NOT NULL,
content text NOT NULL,
last_updated_time   timestamp(0) with time zone NOT NULL default now()
);

And then an audit table:

create table template_history (
id  SERIAL PRIMARY KEY,
template_id integer NOT NULL REFERENCES template ON DELETE 
CASCADE,
pathtext NOT NULL,
content text NOT NULL,
last_updated_time   timestamp(0) with time zone NOT NULL
);

(The "path" is not the primary key because the template's path
might get renamed (moved), but I still want to track its history.)


My trigger is very simple:

CREATE OR REPLACE FUNCTION audit_template() RETURNS TRIGGER AS '
BEGIN
INSERT INTO template_history
( template_id, path, content, last_updated_time, person 
)
select
id, path, content, last_updated_time, person
from
template where id = 1;

RETURN NEW;
END'
language 'plpgsql';


CREATE TRIGGER template_history_add BEFORE UPDATE ON template
for each row execute procedure audit_template();


I realize this is a *BEFORE* UPDATE trigger, but I have this vague
memory of seeing a post stating that you can't be sure the existing
row has not been updated yet. Perhaps that was just a concern if
another trigger was to modify the row.  But, I can't seem to find that
post now which is why I'm asking for the sanity check.

Are there potential problems with this setup?


-- 
Bill Moseley
[EMAIL PROTECTED]


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Setting week starting day

[Jorge Godoy]

Bruno Wolff III <[EMAIL PROTECTED]> writes:

> On Fri, Mar 09, 2007 at 14:59:35 -0300,
>   Jorge Godoy <[EMAIL PROTECTED]> wrote:
>> It is not hard to calculate, as you can see... but it would be nice if
>> "date_trunc('week', date)" could do that directly.  Even if it became
>> "date_trunc('week', date, 4)" or "date_trunc('week', date, 'Wednesday')" it
>> would be nice...  :-)  And that is what I was trying to ask ;-)
>
> Use date_trunc('week', current_day + 1) and date_trunc('dow', current_day + 1)
> to have a one day offset from the standard first day of the week. 


I believe there's more than that...  Probably the "+1" should be outside the
date_trunc, anyway.  It might help, but I still see the need to to do
calculations...  Specially if it was Tuesday today...



neo=# select date_trunc('dow', current_date + 1);
ERRO:  unidades de timestamp with time zone "dow" são desconhecidas
neo=# select date_part('dow', current_date + 1);
 date_part 
---
 6
(1 row)

neo=# select date_trunc('week', current_date + 1);
   date_trunc   

 2007-03-05 00:00:00-03
(1 row)

neo=# select date_trunc('week', current_date);
   date_trunc   

 2007-03-05 00:00:00-03
(1 row)

neo=# 




-- 
Jorge Godoy  <[EMAIL PROTECTED]>

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Anyone know a good opensource CRM that actually installs with Posgtres?

[Brandon Aiken]

Why is running on PG so important?  Why not look for the best CRM
application for your user's needs?

--
Brandon Aiken
CS/IT Systems Engineer

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Bradley Kieser
Sent: Thursday, March 08, 2007 8:22 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Anyone know a good opensource CRM that actually
installs with Posgtres?

I hope that someone has cracked this one because I have run into a brick

wall the entire week and after 3 all-nighters with bad installations, I 
would appreciate hearing from others!

I am looking for a decent OpenSource CRM system that will run with 
Postgres. SugarCRM seems to be the most popular but it's MySQL-centric 
and its opensource parts are very restricted.

vTiger is also mySQL-centric.

I thought that I had a corker of a system with "centricCRM" but when it 
came to actually installing it, I am 48 hours down and hacking through 
screen after screen of installation errors. Basically, it relies way too

much on ant and Java tools. Nothing against Java but my experience with 
ant used for installing PG schemas is a dismal track record of error and

frustration. centric CRM is no exception. Frankly, it just doesn't work 
and after trying to hack out the ant into a PG script I have decided to 
give it up as a bad job.

XRMS promises to run on PG but... it doesn't. The core system is fine, 
but useless without the plugins. The Plugins are mySQL-specific again, I

spent several all-nighters previously hacking through installation 
screens attempting to convert mysql to PG, making software patches... 
you get the picture.

XLSuite looks very promising. Awesome interface, looks great... only 
it's just not ready yet. It is a year away from being at full PG 
production level.

Compiere doesn't support PG.

OpenTAPS the demo won't even work. And it's US-centric whereas we are in

the UK. A pity that it's so very much tied to the US as it could be very

good.

I have tried numerous other CRMs but all the same - either don't run on 
PG, claim to but in reality don't or are simply pre-Alpha and not ready 
for production use.

So if anyone has actually cracked this, please let me know! I really 
need a good CRM.

It has to be OpenSource, not just out of principle, but we need to 
integrate it into an existing business with established inhouse software

so we need to be able to customise the code.


Thanks,

Brad

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match



** LEGAL DISCLAIMER **
Statements made in this e-mail may or may not reflect the views and 
opinions of Wineman Technology, Inc. or its employees.

This e-mail message and any attachments may contain legally privileged, 
confidential or proprietary information. If you are not the intended 
recipient(s), or the employee or agent responsible for delivery of 
this message to the intended recipient(s), you are hereby notified 
that any dissemination, distribution or copying of this e-mail 
message is strictly prohibited. If you have received this message in 
error, please immediately notify the sender and delete this e-mail 
message from your computer.

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] [SQL] PostgreSQL to Oracle

[Ezequias Rodrigues da Rocha]

Thank you Jonah,

That isn't a decision taken but I will need to argue with the new team of my
new company. I can't see why but I will see how the things occurs.

Thank you again
Ezequias
2007/3/9, Jonah H. Harris <[EMAIL PROTECTED]>:


On 3/9/07, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote:
> Is it a simple action to convert a database from PostgreSQL to Oracle ?

Yes, relatively.

> Has someone any idea ?

There's a couple ways to do this, but I'd recommend first using
pg_dump to export schema only.

Your functions and triggers would need to be rewritten, but assuming
they're in PL/pgSQL, it's a fairly trivial task to translate them into
PL/SQL.

As far as the views and sequences are concerned, pull them out of the
pg_dump export and re-run them in TOAD, SQL*Plus, or your favorite
tool.

As far as the type goes, I'm not quite sure what you're doing with it
or how it's used, but it should also be easy to migrate.

To copy the data and table definitions, I'd use a database link (on
the Oracle side) with hsodbc connecting to your PostgreSQL system via
ODBC.

Now that my advice is done with, could you explain why you need to
move to Oracle from PostgreSQL?

--
Jonah H. Harris, Software Architect | phone: 732.331.1324
EnterpriseDB Corporation| fax: 732.331.1301
33 Wood Ave S, 3rd Floor| [EMAIL PROTECTED]
Iselin, New Jersey 08830| http://www.enterprisedb.com/





--
Ezequias Rodrigues da Rocha
http://ezequiasrocha.blogspot.com/
use Mozilla Firefox:http://br.mozdev.org/firefox/

Re: [GENERAL] Sw to generate ER model

[Oleg Bartunov]

I'm happy with dbwrench (http://www.dbwrench.com/) !
btw, it'd be quite useful to have this list on www.postgresql.org

On Fri, 9 Mar 2007, Richard Broersma Jr wrote:


Hi, where can I find a SW that can connect to a postgres DB and create
the ER model?


Shoaib, put together a really nice list of such software on this thread:

http://archives.postgresql.org/pgsql-general/2006-11/msg00721.php

Hope this helps.

Regards,
Richard Broersma Jr.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/



Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Anyone know a good opensource CRM that actually installs with Posgtres?

[Sven Willenberger]

On Fri, 2007-03-09 at 01:22 +, Bradley Kieser wrote:
> I hope that someone has cracked this one because I have run into a brick 
> wall the entire week and after 3 all-nighters with bad installations, I 
> would appreciate hearing from others!
> 
> I am looking for a decent OpenSource CRM system that will run with 
> Postgres. SugarCRM seems to be the most popular but it's MySQL-centric 
> and its opensource parts are very restricted.
> 
> vTiger is also mySQL-centric.
> 
> I thought that I had a corker of a system with "centricCRM" but when it 
> came to actually installing it, I am 48 hours down and hacking through 
> screen after screen of installation errors. Basically, it relies way too 
> much on ant and Java tools. Nothing against Java but my experience with 
> ant used for installing PG schemas is a dismal track record of error and 
> frustration. centric CRM is no exception. Frankly, it just doesn't work 
> and after trying to hack out the ant into a PG script I have decided to 
> give it up as a bad job.
> 
> XRMS promises to run on PG but... it doesn't. The core system is fine, 
> but useless without the plugins. The Plugins are mySQL-specific again, I 
> spent several all-nighters previously hacking through installation 
> screens attempting to convert mysql to PG, making software patches... 
> you get the picture.
> 
> XLSuite looks very promising. Awesome interface, looks great... only 
> it's just not ready yet. It is a year away from being at full PG 
> production level.
> 
> Compiere doesn't support PG.
> 
> OpenTAPS the demo won't even work. And it's US-centric whereas we are in 
> the UK. A pity that it's so very much tied to the US as it could be very 
> good.
> 
> I have tried numerous other CRMs but all the same - either don't run on 
> PG, claim to but in reality don't or are simply pre-Alpha and not ready 
> for production use.
> 
> So if anyone has actually cracked this, please let me know! I really 
> need a good CRM.
> 
> It has to be OpenSource, not just out of principle, but we need to 
> integrate it into an existing business with established inhouse software 
> so we need to be able to customise the code.
> 

Stumbled across this one: http://www.hipergate.org/ which appears to be
crm and groupware that works with postgresql (requires version 8.x so it
appears to be relatively up to date development-wise). It is java based
apparently so it may not be so palatable for you.


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Setting week starting day

[Bruno Wolff III]

On Fri, Mar 09, 2007 at 14:59:35 -0300,
  Jorge Godoy <[EMAIL PROTECTED]> wrote:
> It is not hard to calculate, as you can see... but it would be nice if
> "date_trunc('week', date)" could do that directly.  Even if it became
> "date_trunc('week', date, 4)" or "date_trunc('week', date, 'Wednesday')" it
> would be nice...  :-)  And that is what I was trying to ask ;-)

Use date_trunc('week', current_day + 1) and date_trunc('dow', current_day + 1)
to have a one day offset from the standard first day of the week. 

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] "oracle to postgresql" conversion

[Scott Marlowe]

On Thu, 2007-03-08 at 16:05, Devrim GÜNDÜZ wrote:
> Hi,
> 
> On Fri, 2007-03-09 at 05:21 +1030, Shane Ambler wrote:
> > NUMBER is Oracle's version of NUMERIC - Oracle will use both but 
> > probably only Oracle will use NUMBER. 
> 
> Really? I thought Oracle's NUMBER ~ PostgreSQL's (BIG)INT?

Not sure.  It let me assign a precision to it, so I figured it wasn't
int based.  In fact, it accepts precision up to 38, just like numeric,
and it accepts non-decimal portions, i.e.:

number(20,4);

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: HIPPA (was Re: [GENERAL] Anyone know ...)

[Kenneth Downs]

Kevin Hunter wrote:

What about an SQL injection bug that allows for increased privileges?


Um, web programming 101 is that you escape quotes on user-supplied 
inputs.  That ends SQL injection.


Pardon my naivete (I'm fairly new to web/DB programming) . . . is this 
the current standard method of protection from SQL injection?  How 
does it compare to SQL preparation with bound variables?


When you use SQL Prepared statements it is normal for the db driver to 
escape out the variables for you.  Well at least it is in PHP, I can't 
say for other systems.




Kevin



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Sw to generate ER model

[Richard Broersma Jr]

> Hi, where can I find a SW that can connect to a postgres DB and create
> the ER model?

Shoaib, put together a really nice list of such software on this thread:

http://archives.postgresql.org/pgsql-general/2006-11/msg00721.php

Hope this helps.

Regards,
Richard Broersma Jr.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: HIPPA (was Re: [GENERAL] Anyone know ...)

[Kevin Hunter]

What about an SQL injection bug that allows for increased privileges?


Um, web programming 101 is that you escape quotes on user-supplied 
inputs.  That ends SQL injection.


Pardon my naivete (I'm fairly new to web/DB programming) . . . is this 
the current standard method of protection from SQL injection?  How does 
it compare to SQL preparation with bound variables?


Kevin

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] PostgreSQL to Oracle

[Martin Gainty]

Ezequias-

first you will need to pg_dump everything.. schema..tables..functions then your 
Data into a text format which has no whitespace characters
There are 2 bulk loaders available from Oracle
1)Brand new DataPump
2)Tried and true sqlldr (which I recommend)
I would highly recommend reading and understanding the Extract / Transform 
/Load document located at
http://www.oracle.com/technology/obe/obe10gdb/bidw/etl2/etl2.htm
Also triple check the extents you are allocating for the tablespace for tables 
to be inserted into that tablespace will guaranteed to be sufficient size
(If you dont know the size of a block ask..as this isnt something you can fix 
later..unless you want to spend a 3day weekend doing a rollback..)
Become familiar with LOAD DATA INFILE command links available from either 
Stanford (or Oracle)
http://infolab.stanford.edu/~ullman/fcdb/oracle/or-load.html
Be mindful that Oracle DB is used by ALL of the major players
so inserting nulls which could violate any constraint (constraints being unique 
or foreign key) are forbidden
In fact inserting nulls into any column is discouraged as it may violate the 
relationship of dependent table records to primary table
If you have time and or diskspace constraint you may want to look at Direct 
Path Loading which creates preformatted data blocks
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c21dlins.htm
Caveat emptor with this option as the logger is turned off and if there are any 
burps along the way you wont know which record it burped on

Feel free to ping me if you have any questions/

Saludos Cordiales,
Martin --
--- 
This e-mail message (including attachments, if any) is intended for the use of 
the individual or entity to which it is addressed and may contain information 
that is privileged, proprietary , confidential and exempt from disclosure. If 
you are not the intended recipient, you are notified that any dissemination, 
distribution or copying of this communication is strictly prohibited.
--- 
Le présent message électronique (y compris les pièces qui y sont annexées, le 
cas échéant) s'adresse au destinataire indiqué et peut contenir des 
renseignements de caractère privé ou confidentiel. Si vous n'êtes pas le 
destinataire de ce document, nous vous signalons qu'il est strictement interdit 
de le diffuser, de le distribuer ou de le reproduire.
  - Original Message - 
  From: Ezequias Rodrigues da Rocha 
  To: pgsql general ; PostgreSQL 
  Sent: Friday, March 09, 2007 12:54 PM
  Subject: [GENERAL] PostgreSQL to Oracle


  Hi list,

  Is it a simple action to convert a database from PostgreSQL to Oracle ?

  I mean a simple database with 

  33 tables
  8 functions
  31 sequencies
  2 triggers
  1 type
  3 views

  Has someone any idea ? 

  -- 
  Ezequias Rodrigues da Rocha
  http://ezequiasrocha.blogspot.com/
  use Mozilla Firefox:http://br.mozdev.org/firefox/ 

Re: [GENERAL] [SQL] PostgreSQL to Oracle

[Jonah H. Harris]

On 3/9/07, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote:

Is it a simple action to convert a database from PostgreSQL to Oracle ?


Yes, relatively.


Has someone any idea ?


There's a couple ways to do this, but I'd recommend first using
pg_dump to export schema only.

Your functions and triggers would need to be rewritten, but assuming
they're in PL/pgSQL, it's a fairly trivial task to translate them into
PL/SQL.

As far as the views and sequences are concerned, pull them out of the
pg_dump export and re-run them in TOAD, SQL*Plus, or your favorite
tool.

As far as the type goes, I'm not quite sure what you're doing with it
or how it's used, but it should also be easy to migrate.

To copy the data and table definitions, I'd use a database link (on
the Oracle side) with hsodbc connecting to your PostgreSQL system via
ODBC.

Now that my advice is done with, could you explain why you need to
move to Oracle from PostgreSQL?

--
Jonah H. Harris, Software Architect | phone: 732.331.1324
EnterpriseDB Corporation| fax: 732.331.1301
33 Wood Ave S, 3rd Floor| [EMAIL PROTECTED]
Iselin, New Jersey 08830| http://www.enterprisedb.com/

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] Sw to generate ER model

[bcochofel]

Hi, where can I find a SW that can connect to a postgres DB and create
the ER model?


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Setting week starting day

[Jorge Godoy]

Alvaro Herrera <[EMAIL PROTECTED]> writes:

> Jorge Godoy escribió:
>
>> Just to repeat my question:
>> 
>> (I don't want to write a function, I can do that pretty easily...  And I was
>> asking if there existed some feature on the database that...  It's just a
>> curiosity)
>> 
>>   Given a date X it would return me the first day of the week so that I can
>>   make this first day an arbitrary day, e.g. Friday or Wednesday.
>
> When you say "it would return", what's the "it"?

The function that came with the database, the feature, the something. :-)

> I wasn't proposing to use any function, just putting a simple expression
> in the SELECT's result list (and maybe the GROUP BY, etc).

So I'm blind on how to do that.  Maybe some "CASE"?


Here's what I was asking for (Sunday=0, Saturday=6, to remember ;-)):


testdb=# select current_date;
date

 2007-03-09
(1 row)

testdb=# select current_date + '3 weeks'::interval;
  ?column?   
-
 2007-03-30 00:00:00
(1 row)

testdb=# select date_trunc('week', current_date + '3 weeks'::interval);
 date_trunc  
-
 2007-03-26 00:00:00
(1 row)

testdb=# select date_part('dow', date_trunc('week', current_date + '3 
weeks'::interval));
 date_part 
---
 1
(1 row)

testdb=# 



This is the standard behavior.  It returns me the first monday.  Now, if I had
the week starting on Wednesdays, I should get 2007-03-28 instead of
2007-03-26.

I can check in a function to see if the returned date is before or after my
desired week-start-day (as in Wednesdays, for example) and if date_part('dow',
date) is bigger than it return the value for Monday + 2 days, if it is lower
then return Monday - 5 days. 

For example, again:


testdb=# select date_part('dow', current_date + '3 weeks'::interval);
 date_part 
---
 5
(1 row)

testdb=# select date_trunc('week', current_date + '3 weeks'::interval) + '2 
days'::interval;
  ?column?   
-
 2007-03-28 00:00:00
(1 row)

testdb=# 


That would be the "first day" of the week in three weeks from now, with weeks
starting on Wednesdays. 

If I had asked for this 3 days ago:


testdb=# select date_trunc('week', current_date - '3 days'::interval + '3 
weeks'::interval);
 date_trunc  
-
 2007-03-26 00:00:00
(1 row)

testdb=# select date_part('dow', current_date - '3 days'::interval + '3 
weeks'::interval);
 date_part 
---
 3
(1 row)

testdb=# select date_trunc('week', current_date - '3 days'::interval + '3 
weeks'::interval) - '5 days'::interval;
  ?column?   
-
 2007-03-21 00:00:00
(1 row)

testdb=# 


Then if it was Tuesday, the week three weeks from now would have started on
Wednesday, 2007-03-21.


It is not hard to calculate, as you can see... but it would be nice if
"date_trunc('week', date)" could do that directly.  Even if it became
"date_trunc('week', date, 4)" or "date_trunc('week', date, 'Wednesday')" it
would be nice...  :-)  And that is what I was trying to ask ;-)



Thanks for your attention, Alvaro. :-)


-- 
Jorge Godoy  <[EMAIL PROTECTED]>

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

[GENERAL] PostgreSQL to Oracle

[Ezequias Rodrigues da Rocha]

Hi list,

Is it a simple action to convert a database from PostgreSQL to Oracle ?

I mean a simple database with

33 tables
8 functions
31 sequencies
2 triggers
1 type
3 views

Has someone any idea ?

--
Ezequias Rodrigues da Rocha
http://ezequiasrocha.blogspot.com/
use Mozilla Firefox:http://br.mozdev.org/firefox/

Re: [GENERAL] one-to-one schema design question and ORM

[Bruno Wolff III]

On Fri, Mar 09, 2007 at 10:06:52 -0500,
  Rick Schumeyer <[EMAIL PROTECTED]> wrote:
> 
> From a business rules perspective:
>   Some users are not employees (like an admin user)
>   Some employees are not users
> 
> I can think of two ways to do this:
> 
> 1) a 1-1 relationship where the user table contains a FK to the employee 
> table.  Since not all users will be employees, the FK will sometimes be 
> null.
> In rails, the user class would "belong_to employee" while employee 
> "has_one user".
> 
> 2) Create a link table that has FKs to both the user and employee 
> table.  This make sense because I'm not sure that the concept of "there 
> might be a linked employee" belongs in the user table.  This moves it to 
> a separate table designed for that purpose.  But then again, it may just 
> be a needless extra table.
> 
> Would you prefer one solution over the other?

I think you need a linking table to properly represent the business rule
above. You can use unique constraints on each key in the link table,
to enforce a 1 to 1 link for the users that are employees.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] Statistics

[Ezequias Rodrigues da Rocha]

On Fri, 2007-03-09 at 14:22 -0300, Ezequias Rodrigues da Rocha wrote:
Does someone have statistcs from PostgreSQL ? Numbers from the list,
performance statistics. I must argue with another person the idea of

do not put Oracle in our organization.

We are quite well with postgresql and I have no plans to change my
plataform.

Ezequias

Re: HIPPA (was Re: [GENERAL] Anyone know ...)

[Kenneth Downs]

Bill Moran wrote:
If a user has not logged in, that is, if they are an anonymous visitor, 
the web framework will connect to the database as the default "public" 
user.  Our system is deny-by-default, so this user cannot actually read 
from any table unless specifically granted permission.  In the case 
being discussed, the public user is given SELECT permission on some 
columns of the insurance carriers table, and on the schedules table.
  
Huh.  Does that imply that the web framework still holds a number of 
different DB credentials?  Or does each user need to supply their 
specific DB credentials as their authentication so the web framework is 
merely a proxy to the DB?


(Having recently discovered a major security oversight in one of my 
employer's webapps, my mind's hot on this kind of thing.)



What's hot in my mind is "how do you securely maintain the database connection
information between page loads?"

  



I suppose we could ask JP Morgan Chase bank what they do.  As I 
mentioned to Kevin, sooner or later the security implementation comes 
down to sessions, the user's protection of their password, whether to 
use certificates, whether to use dongles, etc.

Re: HIPPA (was Re: [GENERAL] Anyone know ...)

[Kenneth Downs]

Kevin Hunter wrote:


If a user has not logged in, that is, if they are an anonymous 
visitor, the web framework will connect to the database as the 
default "public" user.  Our system is deny-by-default, so this user 
cannot actually read from any table unless specifically granted 
permission.  In the case being discussed, the public user is given 
SELECT permission on some columns of the insurance carriers table, 
and on the schedules table.


Huh.  Does that imply that the web framework still holds a number of 
different DB credentials?  Or does each user need to supply their 
specific DB credentials as their authentication so the web framework 
is merely a proxy to the DB?


Yes, exactly, the web framework can be thought of as a proxy, it is 
connecting to the DB using credentials provided by the user.


Which, I will take pains to point out, is far far superior to having it 
connect as a super-user and then trusting that the code is bug-free.  
Ouch, I don't even want to think about that one.


But anyway, once we arrive at this point you arrive at the standard 
questions surrounding session security and the possible use of 
certificates.  The system is now as secure as your user's password 
habits and your server's general security. 




---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: HIPPA (was Re: [GENERAL] Anyone know ...)

[Martin Gainty]

Karsten-
You would need some manner of DML operation to take place (in this way the DB 
trigger could sense the change in DB state to activate e-mail)
Otherwise you could do so at your Webapp login
Does this answer your question?
Tak
Martin--
--- 
This e-mail message (including attachments, if any) is intended for the use of 
the individual or entity to which it is addressed and may contain information 
that is privileged, proprietary , confidential and exempt from disclosure. If 
you are not the intended recipient, you are notified that any dissemination, 
distribution or copying of this communication is strictly prohibited.
--- 
Le présent message électronique (y compris les pièces qui y sont annexées, le 
cas échéant) s'adresse au destinataire indiqué et peut contenir des 
renseignements de caractère privé ou confidentiel. Si vous n'êtes pas le 
destinataire de ce document, nous vous signalons qu'il est strictement interdit 
de le diffuser, de le distribuer ou de le reproduire.
- Original Message - 
From: "Karsten Hilbert" <[EMAIL PROTECTED]>
To: 
Sent: Friday, March 09, 2007 11:45 AM
Subject: Re: HIPPA (was Re: [GENERAL] Anyone know ...)


> On Fri, Mar 09, 2007 at 11:02:45AM -0500, Kenneth Downs wrote:
> 
>> >>First, security is defined directly in terms of tables, it is not 
>> >>arbitrated by code.  The "public" group has SELECT access to the 
>> >>articles table and the schedules tables, that's it.  If a person figures 
>> >>out how our links work and tries to access the "claims" table it will 
>> >>simply come up blank (and we get an email).
>> 
>> If a user has not logged in, that is, if they are an anonymous visitor, 
>> the web framework will connect to the database as the default "public" 
>> user.  Our system is deny-by-default, so this user cannot actually read 
>> >from any table unless specifically granted permission.  In the case 
>> being discussed, the public user is given SELECT permission on some 
>> columns of the insurance carriers table, and on the schedules table.
>> 
>> The column-level security is important, as you don't want anybody seeing 
>> the provider id!
>> 
>> If the user figures out our URL scheme, they might try something like 
>> "?gp_page=patients" and say "Wow I'm clever I'm going to look at the 
>> patients table", except that the public user has no privilege on the 
>> table.  The db server will throw a permission denied error.
> 
> My interest was more towards the "we get an email" part.
> What level do you send that from ? A trigger ?
> 
> Karsten
> -- 
> GPG key ID E4071346 @ wwwkeys.pgp.net
> E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346
> 
> ---(end of broadcast)---
> TIP 2: Don't 'kill -9' the postmaster
>
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Setting week starting day

[Omar Eljumaily]

I think you can coax the date_trunc function to give you a proper start 
day.  I think it's more than adding an integer to your date, though.  
You also have to do some mod work after the function returns, I think.  
I agree that the point isn't that you can't do it with some effort, 
however.  It's mainly that it's a bit linguistically unintuitive.  It 
would be nice to have a start date as an argument to the function.


Having said that, my own personal use of it will definitely be inside 
another "wrapper" function because I need database platform 
independence, so I need to abstract the function to look the same on all 
of my platforms.



Jorge Godoy wrote:

Bruno Wolff III <[EMAIL PROTECTED]> writes:

  

On Thu, Mar 08, 2007 at 20:32:22 -0300,
  Jorge Godoy <[EMAIL PROTECTED]> wrote:


Alvaro Herrera <[EMAIL PROTECTED]> writes:

As I said, it is easy with a function. :-)  I was just curious to see if we
had something like Oracle's NEXT_DAY function or something like what I
described (SET BOW=4; -- makes Thursday the first day of week):
  

If you are actually using "date" you can get the effect you want by adding
a constant integer to the date in the date_trunc function. That seems
pretty easy.




I couldn't see where to specify that integer.  Or, if it to sum it up to the
date, something that calculates it automatically.

http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC

Adding an integer I'd still have to write the verifications (such as the one I
mention below for Oracle's NEXT_DATE()) to get the desired result.


Just to repeat my question:

(I don't want to write a function, I can do that pretty easily...  And I was
asking if there existed some feature on the database that...  It's just a
curiosity)

  Given a date X it would return me the first day of the week so that I can
  make this first day an arbitrary day, e.g. Friday or Wednesday.


Oracle's NEXT_DAY() gets closer to that, but would still require a few
operations (checking if the returned date is before the given date or if after
then subtract one week from this returned value, kind of a
"PREVIOUS_DATE()"...).


With a function I could make it easily, but then I'd have to wrap all
calculations with that...  It was just something to make life easier.  From
the answers I'm getting I see that there's no way to do that without a
function and that I'm not missing any feature on PG with regards to that ;-)

  



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: HIPPA (was Re: [GENERAL] Anyone know ...)

[Kenneth Downs]

Ron Johnson wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/09/07 10:02, Kenneth Downs wrote:
  

Karsten Hilbert wrote:


On Fri, Mar 09, 2007 at 08:08:11AM -0500, Kenneth Downs wrote:

 
  

First, security is defined directly in terms of tables, it is not
arbitrated by code.  The "public" group has SELECT access to the
articles table and the schedules tables, that's it.  If a person
figures out how our links work and tries to access the "claims" table
it will simply come up blank (and we get an email).



How ?

Karsten
  
  

If a user has not logged in, that is, if they are an anonymous visitor,
the web framework will connect to the database as the default "public"
user.  Our system is deny-by-default, so this user cannot actually read
from any table unless specifically granted permission.  In the case
being discussed, the public user is given SELECT permission on some
columns of the insurance carriers table, and on the schedules table.

The column-level security is important, as you don't want anybody seeing
the provider id!

If the user figures out our URL scheme, they might try something like
"?gp_page=patients" and say "Wow I'm clever I'm going to look at the
patients table", except that the public user has no privilege on the
table.  The db server will throw a permission denied error.



What about an SQL injection bug that allows for increased privileges?
  


Um, web programming 101 is that you escape quotes on user-supplied 
inputs.  That ends SQL injection.


After that, as stated above, anything the user attempts is executed at 
his privilege level.  For an anonymous user, that's the lowest.


The biggest security limitation we have is actually a weakness in 
Postgres - the inability to restrict the abilities of a user with 
CREATUSER rights, they can make somebody who can do anything.  For 
higher security this requires no ability for public registration of 
accounts.  This would be solved if we could restrict a CREATUSER user to 
only GRANTing to roles they themselves are in.

Re: [GENERAL] Setting week starting day

[Alvaro Herrera]

Jorge Godoy escribió:

> Just to repeat my question:
> 
> (I don't want to write a function, I can do that pretty easily...  And I was
> asking if there existed some feature on the database that...  It's just a
> curiosity)
> 
>   Given a date X it would return me the first day of the week so that I can
>   make this first day an arbitrary day, e.g. Friday or Wednesday.

When you say "it would return", what's the "it"?

I wasn't proposing to use any function, just putting a simple expression
in the SELECT's result list (and maybe the GROUP BY, etc).

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: HIPPA (was Re: [GENERAL] Anyone know ...)

[Kenneth Downs]

Karsten Hilbert wrote:
If the user figures out our URL scheme, they might try something like 
"?gp_page=patients" and say "Wow I'm clever I'm going to look at the 
patients table", except that the public user has no privilege on the 
table.  The db server will throw a permission denied error.



My interest was more towards the "we get an email" part.
What level do you send that from ? A trigger ?


  


The web framework does that.  The web framework decodes the HTTP request 
and executes any SQL it thinks the user wants.  If there is a 
permissions error then it sends an email to the administrator.


The underlying idea is that the GET/POST parameters are pretty standard 
and easy to decode and convert into SQL commands.  For instance, by 
default we assume a page = a table, and lacking any code that overrides 
that assumption, a request for a page becomes a search request in the 
table of the same name.  This is the first thing a cracker would depend 
upon if he were trying to pry.

Re: HIPPA (was Re: [GENERAL] Anyone know ...)

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/09/07 10:02, Kenneth Downs wrote:
> Karsten Hilbert wrote:
>> On Fri, Mar 09, 2007 at 08:08:11AM -0500, Kenneth Downs wrote:
>>
>>  
>>> First, security is defined directly in terms of tables, it is not
>>> arbitrated by code.  The "public" group has SELECT access to the
>>> articles table and the schedules tables, that's it.  If a person
>>> figures out how our links work and tries to access the "claims" table
>>> it will simply come up blank (and we get an email).
>>> 
>> How ?
>>
>> Karsten
>>   
> 
> 
> If a user has not logged in, that is, if they are an anonymous visitor,
> the web framework will connect to the database as the default "public"
> user.  Our system is deny-by-default, so this user cannot actually read
> from any table unless specifically granted permission.  In the case
> being discussed, the public user is given SELECT permission on some
> columns of the insurance carriers table, and on the schedules table.
> 
> The column-level security is important, as you don't want anybody seeing
> the provider id!
> 
> If the user figures out our URL scheme, they might try something like
> "?gp_page=patients" and say "Wow I'm clever I'm going to look at the
> patients table", except that the public user has no privilege on the
> table.  The db server will throw a permission denied error.

What about an SQL injection bug that allows for increased privileges?


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF8ZHlS9HxQb37XmcRAjTMAKDSBDYYmTt9/ivGtl59YtITz5Lb4ACffLzQ
MlCCcfGd5sS3aNhtgDrd+rA=
=cwTh
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: HIPPA (was Re: [GENERAL] Anyone know ...)

[Karsten Hilbert]

On Fri, Mar 09, 2007 at 11:02:45AM -0500, Kenneth Downs wrote:

> >>First, security is defined directly in terms of tables, it is not 
> >>arbitrated by code.  The "public" group has SELECT access to the 
> >>articles table and the schedules tables, that's it.  If a person figures 
> >>out how our links work and tries to access the "claims" table it will 
> >>simply come up blank (and we get an email).
> 
> If a user has not logged in, that is, if they are an anonymous visitor, 
> the web framework will connect to the database as the default "public" 
> user.  Our system is deny-by-default, so this user cannot actually read 
> >from any table unless specifically granted permission.  In the case 
> being discussed, the public user is given SELECT permission on some 
> columns of the insurance carriers table, and on the schedules table.
> 
> The column-level security is important, as you don't want anybody seeing 
> the provider id!
> 
> If the user figures out our URL scheme, they might try something like 
> "?gp_page=patients" and say "Wow I'm clever I'm going to look at the 
> patients table", except that the public user has no privilege on the 
> table.  The db server will throw a permission denied error.

My interest was more towards the "we get an email" part.
What level do you send that from ? A trigger ?

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: HIPPA (was Re: [GENERAL] Anyone know ...)

[Bill Moran]

In response to Kevin Hunter <[EMAIL PROTECTED]>:

> >>> First, security is defined directly in terms of tables, it is not 
> >>> arbitrated by code.  The "public" group has SELECT access to the 
> >>> articles table and the schedules tables, that's it.  If a person 
> >>> figures out how our links work and tries to access the "claims" table 
> >>> it will simply come up blank (and we get an email).
> 
> > If a user has not logged in, that is, if they are an anonymous visitor, 
> > the web framework will connect to the database as the default "public" 
> > user.  Our system is deny-by-default, so this user cannot actually read 
> > from any table unless specifically granted permission.  In the case 
> > being discussed, the public user is given SELECT permission on some 
> > columns of the insurance carriers table, and on the schedules table.
> 
> Huh.  Does that imply that the web framework still holds a number of 
> different DB credentials?  Or does each user need to supply their 
> specific DB credentials as their authentication so the web framework is 
> merely a proxy to the DB?
> 
> (Having recently discovered a major security oversight in one of my 
> employer's webapps, my mind's hot on this kind of thing.)

What's hot in my mind is "how do you securely maintain the database connection
information between page loads?"

-- 
Bill Moran
http://www.potentialtech.com

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Setting week starting day

[Jorge Godoy]

Bruno Wolff III <[EMAIL PROTECTED]> writes:

> On Thu, Mar 08, 2007 at 20:32:22 -0300,
>   Jorge Godoy <[EMAIL PROTECTED]> wrote:
>> Alvaro Herrera <[EMAIL PROTECTED]> writes:
>> 
>> As I said, it is easy with a function. :-)  I was just curious to see if we
>> had something like Oracle's NEXT_DAY function or something like what I
>> described (SET BOW=4; -- makes Thursday the first day of week):
>
> If you are actually using "date" you can get the effect you want by adding
> a constant integer to the date in the date_trunc function. That seems
> pretty easy.


I couldn't see where to specify that integer.  Or, if it to sum it up to the
date, something that calculates it automatically.

http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC

Adding an integer I'd still have to write the verifications (such as the one I
mention below for Oracle's NEXT_DATE()) to get the desired result.


Just to repeat my question:

(I don't want to write a function, I can do that pretty easily...  And I was
asking if there existed some feature on the database that...  It's just a
curiosity)

  Given a date X it would return me the first day of the week so that I can
  make this first day an arbitrary day, e.g. Friday or Wednesday.


Oracle's NEXT_DAY() gets closer to that, but would still require a few
operations (checking if the returned date is before the given date or if after
then subtract one week from this returned value, kind of a
"PREVIOUS_DATE()"...).


With a function I could make it easily, but then I'd have to wrap all
calculations with that...  It was just something to make life easier.  From
the answers I'm getting I see that there's no way to do that without a
function and that I'm not missing any feature on PG with regards to that ;-)

-- 
Jorge Godoy  <[EMAIL PROTECTED]>

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: HIPPA (was Re: [GENERAL] Anyone know ...)

[Kevin Hunter]

First, security is defined directly in terms of tables, it is not 
arbitrated by code.  The "public" group has SELECT access to the 
articles table and the schedules tables, that's it.  If a person 
figures out how our links work and tries to access the "claims" table 
it will simply come up blank (and we get an email).


If a user has not logged in, that is, if they are an anonymous visitor, 
the web framework will connect to the database as the default "public" 
user.  Our system is deny-by-default, so this user cannot actually read 
from any table unless specifically granted permission.  In the case 
being discussed, the public user is given SELECT permission on some 
columns of the insurance carriers table, and on the schedules table.


Huh.  Does that imply that the web framework still holds a number of 
different DB credentials?  Or does each user need to supply their 
specific DB credentials as their authentication so the web framework is 
merely a proxy to the DB?


(Having recently discovered a major security oversight in one of my 
employer's webapps, my mind's hot on this kind of thing.)


Kevin

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] OT: Canadian Tax Database

[Guy Fraser]

Sorry everyone, my bad, but I should have expected it.

I was not denigrating anyone, if you actually read what I 
said you can not conclude that I was. My entire point was 
that the Government does not hire the best qualified hardest
working people regardless of their sex, culture, origin
or any other non work related issue. I personally hire 
and work with very qualified people who are very diverse 
in non work related factors, and I feel it is advantageous.

Like Ted I have also been told that due to my non work 
related features, that I need not apply. I have also been 
in a position to be asked to resolve issues created by the 
person who was hired, while working elsewhere for a company
providing services to that department. Not even a government
employee gets paid the $120/Hr it cost to have me resolve 
the problem, so wouldn't it be more efficient to just hire 
qualified staff, not necessarily me, but someone who was 
actually the best person for the job?

Government Hiring = Quotas = Suck

On Thu, 2007-03-08 at 11:06 -0800, Omar Eljumaily wrote:
> Since this thread has already degraded, I'll offer my two cents.  The 
> biggest screw ups in US history have been instigated by groups of 
> privileged White men.  I know my name may sound otherwise, but I'm a 
> White American male, so I'm not pointing the finger at another group.  
> Let's see, Enron, Arthur Anderson, the entire Bush Administration and 
> its fiascos in Iraq, Katrina, foreign policy in general, etc.  I've 
> worked for large, major IT providers and I can tell you that 
> incompetency shows no racial or ethnic boundaries.  It tends to exist in 
> large, politically connected, no bid contractors, not low bid 
> contractors or ones who benefited from affirmative action.
> 
> 
> Ted Byers wrote:
> >>> > Richard Huxton wrote:
> >>> >> http://www.thestar.com/News/article/189175
> >>> >>
> >>> >> "For instance, in some cases the field for the social insurance 
> >>> number
> >>> >> was instead filled in with a birth date."
> >>> >>
> >>> >> Unbelievable. Sixty years of electronic computing, fifty years 
> >>> use in
> >>> >> business and the "professionals" who built the tax system for a 
> >>> >> wealthy
> >>> >> democratic country didn't use data types.
> >>> >
> >>> > This is Unbelievable? This is commonplace.
> >>> >
> >>> And due at least in part to government (and other institutions 
> >>> operated by
> >>> damned fools) opting for the least expensive provider rather than 
> >>> paying for
> >>> someone who actually knows what they're doing.  Just as buying cheap 
> >>> junk
> >>> always comes back to get you, hiring incompetent fools that don't 
> >>> know their
> >>> ass from a hole in the ground will come back to get you too.
> >>>
> >> Come on, they don't hire incompetent fools. The hire the people
> >
> > You CAN'T be serious!  Have you ever dealt with them or with the 
> > consequences of their incompetence?
> >
> >> they need to fill their quota regardless of how well trained
> >> and experienced they are. I am not saying that non white males
> >> are in any way less competent than white males, but by removing
> >> them from the pool does not make things better. The biggest
> >> problem with quotas is not hiring less qualified staff, it is
> >> that less qualified staff know why they were hired and know that
> >> they are very unlikely to be fired, so they have little incentive
> >> to work hard or attempt to do their best, they can always fail
> >> upwards.
> >>
> > What does this have to do with anything?  No one here, except you, has 
> > said anything about the profile of the people involved WRT race, 
> > gender, religion, &c.  Nor has anyone said anything about 
> > "qualifications".  The only thing that has been said is that, based on 
> > what is seen in the "work", the people responsible for that work must 
> > be incompetent.  It is an inference based on what is seen in what has 
> > been done and has nothing to do with any of the prohibited grounds for 
> > discrimination used as excuses for affirmative action.  And yes, I 
> > have seen cases where less qualified, even unqualified, people have 
> > been hired as a result of these affirmative action initiatives (and I 
> > have been told, by HR personelle in government, that certain favoured 
> > groups are deemed to be superior to white men, even if the favoured 
> > party has no education nor experience and the latter have earned 
> > doctorates and decades of experience), but no one has said anything 
> > about such people being employed on the projects to which I referred.  
> > But this is an aspect of our present society that is bound to 
> > degenerate into a flame war, launched by the politically correct, so 
> > we ought to say little, or even leave it alone.  Those in power tend 
> > to be vicious, especially when there are no effective checks on their 
> > conduct and no consequences for what they do.
> >
> > Cheers
> >
> > Ted
> >
> >
> > --

Re: [GENERAL] Beginner's Questions

[Michael Schmidt]

Don,
It sounds like your project is similar to mine.  My app is a scientific 
database that contains journal articles and data related to neuropsychological 
assessment.  The goal is to support evidence-based clinical practice as well as 
to serve as a basis for research and a book I am working on.  The database has 
30 tables with up to three layers (parent-child-child).  The ultimate size of 
the database will be in the 1000's of records.

I originally started the project in Paradox several years ago.  Paradox was 
good for its day but it was deprecated and so I had to migrate.  After much 
study (reading reviews, comparisons, mailing lists) I chose PostgreSQL.  It is 
popular (so it won't be deprecated), has excellent documentation, the support 
group is super, and it is very reliable.  It has some pretty important 
features; transactions and built-in referential integrity.  I also develop in a 
Windows XP environment - without problems.  

Regarding resources, my install of PostgreSQL takes up 100M of disk, with 57M 
used by the data directory (my database is only about 11M).  I don't think 
that's too big of a footprint for modern programs.

I programmed my user interface in Java, using Eclipse.  I found the learning 
curve for Eclipse to be pretty steep and, unlike PostgreSQL, the documentation 
and community support are uneven.  Now that the interface is done, I'm pretty 
happy with it.  The basic interface - without custom features needed for a 
specific purpose - is available on pgFoundry (the Komo project) and you can use 
it to get started.  Have a look!

For output, I added JasperReports to my GUI and that works fine.  Note that 
PostgreSQL, Eclipse, and JasperReports are all open source.

Good luck!
Michael Schmidt

Re: [GENERAL] Setting week starting day

[Bruno Wolff III]

On Thu, Mar 08, 2007 at 20:32:22 -0300,
  Jorge Godoy <[EMAIL PROTECTED]> wrote:
> Alvaro Herrera <[EMAIL PROTECTED]> writes:
> 
> As I said, it is easy with a function. :-)  I was just curious to see if we
> had something like Oracle's NEXT_DAY function or something like what I
> described (SET BOW=4; -- makes Thursday the first day of week):

If you are actually using "date" you can get the effect you want by adding
a constant integer to the date in the date_trunc function. That seems
pretty easy.

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] security permissions for functions

[Bruno Wolff III]

On Fri, Mar 09, 2007 at 01:07:23 -0500,
  Tom Lane <[EMAIL PROTECTED]> wrote:
> 
> Certainly --- the point here is merely that that isn't the *default*
> behavior.  We judged quite some time ago that allowing public execute
> access was the most useful default.  Perhaps that was a bad choice, but
> I think we're unlikely to change it now ...

At the time this choice was being made it was realized there was going to
be a lot of pain for people updating, as the previous releases didn't
limit access to functions. So it was unlikely to change then, for the same
reasons it is unlikely to change now.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: HIPPA (was Re: [GENERAL] Anyone know ...)

[Kenneth Downs]

Karsten Hilbert wrote:

On Fri, Mar 09, 2007 at 08:08:11AM -0500, Kenneth Downs wrote:

  
First, security is defined directly in terms of tables, it is not 
arbitrated by code.  The "public" group has SELECT access to the 
articles table and the schedules tables, that's it.  If a person figures 
out how our links work and tries to access the "claims" table it will 
simply come up blank (and we get an email).


How ?

Karsten
  



If a user has not logged in, that is, if they are an anonymous visitor, 
the web framework will connect to the database as the default "public" 
user.  Our system is deny-by-default, so this user cannot actually read 
from any table unless specifically granted permission.  In the case 
being discussed, the public user is given SELECT permission on some 
columns of the insurance carriers table, and on the schedules table.


The column-level security is important, as you don't want anybody seeing 
the provider id!


If the user figures out our URL scheme, they might try something like 
"?gp_page=patients" and say "Wow I'm clever I'm going to look at the 
patients table", except that the public user has no privilege on the 
table.  The db server will throw a permission denied error.

Re: [GENERAL] one-to-one schema design question and ORM

[Jorge Godoy]

Rick Schumeyer <[EMAIL PROTECTED]> writes:

> I can think of two ways to do this:
>
> 1) a 1-1 relationship where the user table contains a FK to the employee
> table.  Since not all users will be employees, the FK will sometimes be null.
> In rails, the user class would "belong_to employee" while employee "has_one
> user".
>
> 2) Create a link table that has FKs to both the user and employee table.  This
> make sense because I'm not sure that the concept of "there might be a linked
> employee" belongs in the user table.  This moves it to a separate table
> designed for that purpose.  But then again, it may just be a needless extra
> table.
>
> Would you prefer one solution over the other?

It all depends on what you'll be doing and how often.  Remember that ORMs
usually "select *", so you might end up using more memory / resources than
you'd be willing to.

If the RoR mapper can do lazy loadings, then this might not be too bad...

Anyway, you might also add the extra table to make it a place to gather more
information that will be relevant to your system only.

-- 
Jorge Godoy  <[EMAIL PROTECTED]>

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

R: [GENERAL] Error code associated to "could not open relation with OID ..."

[Galantucci Giovanni]

I don't use temporary tables in my application.
>From a previous post I've understood that this error could happen if I'm 
>trying to access a table whose corresponding row in the pg_class table is 
>being modified. So I wanted to catch the error code of such exception to retry 
>a second insert into the table.

Giovanni Galantucci

-Messaggio originale-
Da: Richard Huxton [mailto:[EMAIL PROTECTED] 
Inviato: venerdì 9 marzo 2007 14.51
A: Galantucci Giovanni
Cc: pgsql-general@postgresql.org
Oggetto: Re: [GENERAL] Error code associated to "could not open relation with 
OID ..."

Galantucci Giovanni wrote:
> Hi all,
> 
> I'm using jdbc to connect to a remote database and this morning I've
> found an SQLException on my log file with this error description:
> 
> "could not open relation with OID ...".
> 
> Does anyone know which error code is associated to such exception?

It's usually down to a cached plan referring to a temporary table that's 
being replaced. Could be a normal table being dropped/recreated though.

It could be a query-plan cached by your jdbc stuff or perhaps a plpgsql 
function using TEMP tables?

-- 
   Richard Huxton
   Archonet Ltd


Internet Email Confidentiality Footer
-
La presente comunicazione, con le informazioni in essa contenute e ogni 
documento o file allegato, e' rivolta unicamente alla/e persona/e cui e' 
indirizzata ed alle altre da questa autorizzata/e a riceverla. Se non siete i 
destinatari/autorizzati siete avvisati che qualsiasi azione, copia, 
comunicazione, divulgazione o simili basate sul contenuto di tali informazioni 
e' vietata e potrebbe essere contro la legge (art. 616 C.P., D.Lgs n. 196/2003 
Codice in materia di protezione dei dati personali). Se avete ricevuto questa 
comunicazione per errore, vi preghiamo di darne immediata notizia al mittente e 
di distruggere il messaggio originale e ogni file allegato senza farne copia 
alcuna o riprodurne in alcun modo il contenuto. 

This e-mail and its attachments are intended for the addressee(s) only and are 
confidential and/or may contain legally privileged information. If you have 
received this message by mistake or are not one of the addressees above, you 
may take no action based on it, and you may not copy or show it to anyone; 
please reply to this e-mail and point out the error which has occurred. 
-


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] Tracking disk writes?

[Erik Jones]

I've seen that I can get the total number of blocks read from disk  
over the lifetime of a database via the pg_stat_database view, and by  
taking successive readings I can track reads over time.  How can I  
track disk writes?



erik jones <[EMAIL PROTECTED]>
sofware developer
615-296-0838
emma(r)

Re: HIPPA (was Re: [GENERAL] Anyone know ...)

[Karsten Hilbert]

On Fri, Mar 09, 2007 at 08:08:11AM -0500, Kenneth Downs wrote:

> First, security is defined directly in terms of tables, it is not 
> arbitrated by code.  The "public" group has SELECT access to the 
> articles table and the schedules tables, that's it.  If a person figures 
> out how our links work and tries to access the "claims" table it will 
> simply come up blank (and we get an email).
How ?

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] Bitmap AND multicolumn index used !

[Arnaud Lesauvage]

Hi list !

I have a quite large table with a PostGIS-geometry field (~25M rows) 
representing road segments.
The segments are classified in 9 classes (from 0 to 8), based on their 
importance.
I am trying some different methods for optimizing queries on this table.
I decided to try with a multicolumn gist index (geometry first, the the road 
class), and to cluster the table on this index.
I also added a simple btree index on the road class. 
I thought that this index would be quite useless becauses there are only 9 different values for it, but I created it anyway.


To my surprise, a query with a criteria on the geometry AND on the class did 
not only use the multicolumn index, but also did a bitmap with the btree index !
It did not even use the second column of the multicolumn index !

Why is it so ? Should I simply remove this second column ?


EXPLAIN analyze
SELECT nw_geometry 
FROM nw

WHERE frc=0
AND nw_geometry && GeomFromText('POLYGON((50 500,50 5100,60 
510,60 500,50 500))', 32631)


"Bitmap Heap Scan on nw  (cost=48355.46..70461.85 rows=5831 width=153) (actual 
time=690.933..806.038 rows=11029 loops=1)"
"  Recheck Cond: (frc = 0)"
"  Filter: (nw_geometry && 
'010320777F0100050080841E41D012534180841E4196518841804F224178745341804F2241D012534180841E41D0125341'::geometry)"
"  ->  BitmapAnd  (cost=48355.46..48355.46 rows=5831 width=0) (actual 
time=688.743..688.743 rows=0 loops=1)"
"->  Bitmap Index Scan on nw_frc_btree  (cost=0.00..2493.09 rows=134651 
width=0) (actual time=52.358..52.358 rows=146683 loops=1)"
"  Index Cond: (frc = 0)"
"->  Bitmap Index Scan on nw_geometry_frc_gist  (cost=0.00..45859.21 
rows=1166186 width=0) (actual time=609.883..609.883 rows=1248343 loops=1)"
"  Index Cond: (nw_geometry && 
'010320777F0100050080841E41D012534180841E4196518841804F224178745341804F2241D012534180841E41D0125341'::geometry)"
"Total runtime: 809.338 ms"


Thanks a lot for clarifying this !

Regards
--
Arnaud

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

   http://www.postgresql.org/about/donate

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] Weird behaviour on a join with multiple keys

[Tom Lane]

Charlie Clark <[EMAIL PROTECTED]> writes:
> psytec=# show lc_collate;
> lc_collate
> -
> de_DE.UTF-8
> (1 row)

> psytec=# show server_encoding;
> server_encoding
> -
> LATIN1
> (1 row)

There's your problem right there.  The string comparison routines are
built on strcoll(), which is going to expect UTF8-encoded data because
of the LC_COLLATE setting.  If there are any high-bit-set LATIN1
characters in the database, they will most likely look like invalid
encoding to strcoll(), and on most platforms that causes it to behave
very oddly.  You need to keep lc_collate (and lc_ctype) in sync with 
server_encoding.

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] one-to-one schema design question and ORM

[Rick Schumeyer]

I'm developing a system using Ruby on Rails (with ActiveRecord) and 
postgres.  (Although I think my question is still relevant for, say, 
java with hibernate.)


I have two classes (tables): users and employees.   A user is an account 
that can logon to the system, while an employee is...umm...an employee.


When someone is logged in, they will want to run queries like, "give me 
a list of my accounts".  This means I need to link the users table with 
the employees table.

From a business rules perspective:
  Some users are not employees (like an admin user)
  Some employees are not users

I can think of two ways to do this:

1) a 1-1 relationship where the user table contains a FK to the employee 
table.  Since not all users will be employees, the FK will sometimes be 
null.
In rails, the user class would "belong_to employee" while employee 
"has_one user".


2) Create a link table that has FKs to both the user and employee 
table.  This make sense because I'm not sure that the concept of "there 
might be a linked employee" belongs in the user table.  This moves it to 
a separate table designed for that purpose.  But then again, it may just 
be a needless extra table.


Would you prefer one solution over the other?

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] Anyone know a good opensource CRM that actually installs with Posgtres?

[Walter Vaughan]

Bradley Kieser wrote:
I am looking for a decent OpenSource CRM system that will run with 
Postgres. 


OpenTAPS the demo won't even work. And it's US-centric whereas we are in 
the UK. A pity that it's so very much tied to the US as it could be very 
good.


What actually didn't work with OpenTaps? "OpenTaps" is additional modules that 
work with the Apache Open For Business project core product.


What functions that SugarCRM supply that you need in OpenTaps? We are funding 
($$,$$$ USD) a lot of changes right now in OpenTaps and OFBiz, and perhaps you 
could let me know where the feature gap is so that I don't end up with a "I 
can't believe we forgot about that".


--
Walter

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] Error code associated to "could not open relation with OID ..."

[Richard Huxton]

Galantucci Giovanni wrote:

Hi all,

I'm using jdbc to connect to a remote database and this morning I've
found an SQLException on my log file with this error description:

"could not open relation with OID ...".

Does anyone know which error code is associated to such exception?


It's usually down to a cached plan referring to a temporary table that's 
being replaced. Could be a normal table being dropped/recreated though.


It could be a query-plan cached by your jdbc stuff or perhaps a plpgsql 
function using TEMP tables?


--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] Beginner's Questions

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/09/07 06:03, Don Lavelle wrote:
> Hey, All,
> 
> I'm working on a project (for a friend and for self-education) and want
> to learn a little more about what sorts of applications PostgreSQL is
> used for.  I'm currently looking at a single-computer desktop
> application that may be scaled to a client-server model with multiple
> desktop clients and a centralized server.
> 
> My database is quite small (only 13 lucky tables, though that may expand
> a little) and will not hold a great amount of data.  (There will be at
> most records in the thousands for the single-user or tens of thousands
> for the multi-user.)  I will either use Java or C++ for the project.  I
> would run PostgreSQL as a child process.
> 
> Is PostgreSQL overkill for such a project?  My other choices are to go
> with a flat-file format or to use an embedded SQL server.  The reason to
> go with PostgreSQL are that I don't have to write as much code, it's
> known to be reliable for what I'm doing, and it's known to be reliable
> for what I might be doing.  The reason to not go with PostgreSQL is that
> it might be too much for a modest personal computer; I don't know that
> the computers running this will even have XP.  (I'm not a MS Windows
> user, myself, unless I have to be.  My development boxes are a 1.1 GHz
> Athlon with 512 RAM with XP and a dual-processor G4 with MacOS 10.4.)
> 
> Are there ballpark requirements for what such a database will need to run?

As others have said, PG will certainly, happily, run on this system.

It might be overkill, though, regarding complexity.  Will you be
using triggers, stored procedures, foreign keys, etc?  And how many
users in the multi-user version?  If it'll just be a few people
doing SELECT/INSERT/UPDATE/DELETE and the queries are fast, SQLite
may be adequate to the task.


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF8WW1S9HxQb37XmcRApssAJ9fGUlZcEkVvhU1eNMMGN00ZVvRywCcC9mS
qgM91a6nCHBF3412UK8Tqb8=
=2RXq
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[GENERAL] Error code associated to "could not open relation with OID ..."

[Galantucci Giovanni]

Hi all,

I'm using jdbc to connect to a remote database and this morning I've
found an SQLException on my log file with this error description:

"could not open relation with OID ...".

Does anyone know which error code is associated to such exception?

Thanks in advance

 

Giovanni Galantucci


Internet Email Confidentiality Footer
-
La presente comunicazione, con le informazioni in essa contenute e ogni 
documento o file allegato, e' rivolta unicamente alla/e persona/e cui e' 
indirizzata ed alle altre da questa autorizzata/e a riceverla. Se non siete i 
destinatari/autorizzati siete avvisati che qualsiasi azione, copia, 
comunicazione, divulgazione o simili basate sul contenuto di tali informazioni 
e' vietata e potrebbe essere contro la legge (art. 616 C.P., D.Lgs n. 196/2003 
Codice in materia di protezione dei dati personali). Se avete ricevuto questa 
comunicazione per errore, vi preghiamo di darne immediata notizia al mittente e 
di distruggere il messaggio originale e ogni file allegato senza farne copia 
alcuna o riprodurne in alcun modo il contenuto. 

This e-mail and its attachments are intended for the addressee(s) only and are 
confidential and/or may contain legally privileged information. If you have 
received this message by mistake or are not one of the addressees above, you 
may take no action based on it, and you may not copy or show it to anyone; 
please reply to this e-mail and point out the error which has occurred. 
-

Re: HIPPA (was Re: [GENERAL] Anyone know ...)

[Kenneth Downs]

Ron Johnson wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/08/07 20:38, Kenneth Downs wrote:
[snip]
  

Management and we are about to add the CRM to it so that the
scheduling/billing database also serves the doctor's public website,



Is that wise?  One bug and a cracker is poking around some very
private stuff!!
  


We use the "Spartan" security model rather than perimeter defense, which 
gives us the confidence to do things that others may not.


The general outline is as follows.

First, security is defined directly in terms of tables, it is not 
arbitrated by code.  The "public" group has SELECT access to the 
articles table and the schedules tables, that's it.  If a person figures 
out how our links work and tries to access the "claims" table it will 
simply come up blank (and we get an email).  The "staff" group has 
read/write access to scheduling, and so forth.


Second, the security assignments to tables are generated by a builder, 
which revokes and grants all priveleges to all groups on all tables.  
This is to prevent  the possibility of error or omission if a person 
were to implement it table-by-table.


Third, as you probably have guessed, we do not connect to the database 
as a super-user and then arbitrate in code.  Regular staff connect with 
real database accounts and their security in the database is limited to 
those accounts.  Bugs in code that try to provide unauthorized 
information will return server errors.  A person with no account, such 
as the public user, has the lowest security priveleges, as mentioned above.


Finally, our URL parameter scheme is really very simple and easy to 
figure out, we are not hiding it.  Anybody trying to get something they 
can't would not take long to work it out (especially as the underlying 
tools are GPL), they would simply find it did them no good. 

It is good to be very concerned about security, especially HIPPA, where 
there are legal ramifications.  It is also very good to be able to 
provide convenience and security in one package.

Re: [GENERAL] security permissions for functions

[Bill Moran]

In response to "Ted Byers" <[EMAIL PROTECTED]>:
> >
> > Functions are controlled by the same ACL mechanism that tables and 
> > everything
> > else follows.  Thus you have the idea of "user id X may do Y with object 
> > Z"
> > i.e. "user "barbara" may "execute" function "somefunction()".
> >
> > But there's no real way to alter those permissions outside of changing the
> > user ID context.
> 
> So, I should be able to have "user "barbara" "execute" function 
> "somefunction()", but, though barbara must not have access of object alpha 
> lets say for data security reasons (and user sarah does), I could have 
> function somefunction invoke another function that stores information about 
> barbara's action to object alpha by changing user context temporarily and 
> without barbara's knowledge; basically saying within function 
> "somefunction()" something like "execute function 'someotherfunction()' 
> impersonating sarah and stop impersonating sarah once someotherfunction 
> returns.  Much like the way I can log in to Windows or Linux as one user and 
> temporarily impersonate another while executing a particular program or 
> administrative function (e,g, log into Linux as a mere mortal, start a bash 
> shell providing credentials for an admin account, do my admin type stuff and 
> then close the shell).
> 
> Or have I misunderstood you here WRT user ID context?

No, you're on track.  Have a look at the docs for CREATE FUNCION:
http://www.postgresql.org/docs/8.1/static/sql-createfunction.html

Specifically the section on SECURITY INVOKER and SECURITY DEFINER.
SECURITY DEFINER gives you the equivalent of "setuid" capability

-- 
Bill Moran
http://www.potentialtech.com

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Beginner's Questions

[Jorge Godoy]

Don Lavelle <[EMAIL PROTECTED]> writes:

> My database is quite small (only 13 lucky tables, though that may expand a
> little) and will not hold a great amount of data.  (There  will be at most
> records in the thousands for the single-user or tens  of thousands for the
> multi-user.)  I will either use Java or C++ for  the project.  I would run
> PostgreSQL as a child process.

What do you mean by "a child process"?  PostgreSQL is run as a server and then
you connect to it (either through TCP or using sockets).  Or you were talking
about something you'll do with your code?

> Is PostgreSQL overkill for such a project?  My other choices are to go with a
> flat-file format or to use an embedded SQL server.  The  reason to go with
> PostgreSQL are that I don't have to write as much  code, it's known to be
> reliable for what I'm doing, and it's known to  be reliable for what I might
> be doing.  The reason to not go with  PostgreSQL is that it might be too much
> for a modest personal  computer; I don't know that the computers running this
> will even have  XP.  (I'm not a MS Windows user, myself, unless I have to be.
> My  development boxes are a 1.1 GHz Athlon with 512 RAM with XP and a
> dual-processor G4 with MacOS 10.4.)
>
> Are there ballpark requirements for what such a database will need to run?

You have the required hardware.  I have PostgreSQL running on worse conditions
and performing very well.


-- 
Jorge Godoy  <[EMAIL PROTECTED]>

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Beginner's Questions

[tom]

I'm running my database on a Pentium 2 with 450MHz CPU.
It runs dbmail and spamassassin's Bayes and has overhead available.
You'll find it works well enough for your database size.

As for being it overkill.  I think you've answered your own questions:
I don't have to write as much code -- less bugs, less development time.
It's know to be reliable for what I'm doing -- less bugs, less  
testing time.


So, if it *can* run on your machine why wouldn't you run it on your  
machine?


I don't limit this thinking to just postgresql...  You've identified  
postgresql as something that can provide you with a simpler solution  
to the architecture, you have sufficient hardware for it.  And if for  
some reason your estimates are wrong about the size of the project  
you can easily grow for sometime with minor modifications, if any, to  
your application.


On Mar 9, 2007, at 7:03 AM, Don Lavelle wrote:


Hey, All,

I'm working on a project (for a friend and for self-education) and  
want to learn a little more about what sorts of applications  
PostgreSQL is used for.  I'm currently looking at a single-computer  
desktop application that may be scaled to a client-server model  
with multiple desktop clients and a centralized server.


My database is quite small (only 13 lucky tables, though that may  
expand a little) and will not hold a great amount of data.  (There  
will be at most records in the thousands for the single-user or  
tens of thousands for the multi-user.)  I will either use Java or C+ 
+ for the project.  I would run PostgreSQL as a child process.


Is PostgreSQL overkill for such a project?  My other choices are to  
go with a flat-file format or to use an embedded SQL server.  The  
reason to go with PostgreSQL are that I don't have to write as much  
code, it's known to be reliable for what I'm doing, and it's known  
to be reliable for what I might be doing.  The reason to not go  
with PostgreSQL is that it might be too much for a modest personal  
computer; I don't know that the computers running this will even  
have XP.  (I'm not a MS Windows user, myself, unless I have to be.   
My development boxes are a 1.1 GHz Athlon with 512 RAM with XP and  
a dual-processor G4 with MacOS 10.4.)


Are there ballpark requirements for what such a database will need  
to run?


Thanks!

 Don

---(end of  
broadcast)---

TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/




---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq