Hello,
I'm not a DBA nor an architect. I learn from the wise and by making
mistakes.
I'm in the process of developing a web application and need some advice from
you all,
Requirements:
==
* Application runs 24/7
* Application must support either PostgreSQL or another commercial
enterpris
On Fri, 26 Sep 2008, Chen, Dongdong (GE Healthcare, consultant) wrote:
I am a software engineer from GE. I am using JDBC to operate
PostgreSQL8.3 in Ubuntu8.04. The develop environment is Eclipse3.2 My
problem is:
There is a PostgreSQL table XX containing 5 fields: AA, BB, CC, DD,
EE, AA
For an alternative view of the security argument, which may be a little
off topic...
One consideration in regard to arguments for additional security,
whether column and row level security or the divergent thread on
obfuscated stored procedures is whether postgresql currently supports
PCI (in
i would recommend
dont publish in HTML/JS as with a simple View Page Source any browser client
can figure out what is doing what
also i would shy from Scripting macro languages as they are not compiled
modules and anyone with a text editor can easily see your code
Functions and procedure are ano
On Sep 25, 2008, at 4:59 PM, x asasaxax wrote:
Hi everyone,
I have this table:
create table cat(
cod integer,
cod_super integer,
constraint cod_super_fk Foreign Key(cod_super) references cat(cod),
constraint cod_pk Primary Key(cod)
);
insert into cat values(0, 1);
insert into cat val
which webserver does your client want to implement?
if webserver is Apache does your client favor any JavaScript Library
?
If JSLibrary = Dojo I would look at JSON (JavaScript Object Notation language)
http://today.java.net/pub/a/today/2006/04/27/building-ajax-with-dojo-and-json.html
if you wan
On Thu, Sep 25, 2008 at 5:38 PM, Dianne Yumul <[EMAIL PROTECTED]> wrote:
> Hello,
>
> I have some html forms that I save the settings into the database, things
> like which item was selected in the menu and if a checkbox was checked. The
> table looks like this:
>
> user_id | report_id |
Hi,
just to give an update on this:
Frederik Ramm wrote:
Every other night, the process aborts with some strange error message,
and never at the same position:
[...]
Turns out it *was* a RAM defect on one of the machines. memtest86 ran
for a day and didn't detect it, but when I started m
"Matt Magoffin" <[EMAIL PROTECTED]> writes:
>> Try the patch here:
>> http://archives.postgresql.org/pgsql-committers/2008-09/msg00159.php
> I've applied this patch now to our staging and production environments,
> and Postgres performed quite well after testing very large result sets
> that were
Hello,
I have some html forms that I save the settings into the database,
things like which item was selected in the menu and if a checkbox was
checked. The table looks like this:
user_id | report_id | info
-+---+
On Sep 25, 2008, at 1:16 PM, Christophe wrote:
Without getting into the argument as to the level of security
provided, it strikes me that a reasonable approach would be a non-
core pluggable language which accepts encrypted strings as
functions, decrypts them (using a key compiled into the la
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
The Right Way (tm) to do this would be something like
create temp table dates as select * from get_dates(...);
analyze dates;
... original select, but join against temp table ...
which would leave the
On Thu, 25 Sep 2008, Christophe wrote:
it strikes me that a reasonable approach would be a non-core pluggable
language which accepts encrypted strings as functions, decrypts them
(using a key compiled into the language module), and passes them on to
PL/pgSQL for execution...This would, of cour
Hi everyone,
I have this table:
create table cat(
cod integer,
cod_super integer,
constraint cod_super_fk Foreign Key(cod_super) references cat(cod),
constraint cod_pk Primary Key(cod)
);
insert into cat values(0, 1);
insert into cat values(1, 0);
insert into cat values(2, 0);
insert into
On Thu, Sep 25, 2008 at 01:25:25PM -0700, Casey Allen Shobe wrote:
> Gee, I wonder why companies that support these antics grow to insane
> sizes of employees?
Meetings. Lots and lots of meetings.
A
--
Andrew Sullivan
[EMAIL PROTECTED]
+1 503 667 4564 x104
http://www.commandprompt.com/
--
S
On Sep 25, 2008, at 1:14 PM, David Fetter wrote:
On Thu, Sep 25, 2008 at 01:05:26PM -0700, Casey Allen Shobe wrote:
On Sep 15, 2008, at 7:19 PM, Tom Lane wrote:
The problem is that the people who ask for this type of feature are
usually imagining that they can put their code on
customer-control
On Sep 25, 2008, at 11:16 AM, Asko Oja wrote:
What i see is lack of useless bells and whistles in PostgreSQL and i
like it.
Then you aren't paying attention very well. PostgreSQL comes with an
extremely rich and useful set of bells and whistles than most people
never use, in a non-detrime
> Huh, I was overthinking the problem. xml_out has a memory leak :-(
>
> More than one in fact: a large leak (equal to size of output) in
> the executor context (hence query lifespan) and a small one in
> LibxmlContext (hence transaction lifespan).
>
> Try the patch here:
> http://archives.postgre
On Sep 24, 2008, at 6:12 PM, Scott Ribe wrote:
the sort of person who
thinks re-using someone else's undocumented code is easier than
writing it from scratch is probably not going to be able to learn
the code
via debugging tools.
There are two distinct extremes here, and I think most people
On Sep 24, 2008, at 8:05 AM, David Fetter wrote:
C is not magic obfuscation gear. Anybody with a debugger can expose
what it's doing.
Yes, but you don't get original code, comments, etc. and it takes a
lot of effort to refine it back down into something maintainable.
People looking to pro
On Thu, 2008-09-25 at 18:25 +0200, A B wrote:
> My solution up till now has been a function with the
>
> BEGIN
> insert
> EXCEPTION WHEN OTHERS THEN
> update ...
> END;
Here is the appropriate documentation link, where they have an example:
http://www.postgresql.org/docs/8.3/static/
On Sep 16, 2008, at 6:39 AM, Jonathan Bond-Caron wrote:
After some research, I found this article that I believe will make a
stronger use case:
http://www.iosn.net/network/news/Managing%20the%20insider%20threat%20through
%20code%20obfuscation
I can tell without even clicking the link that it wo
On Sep 25, 2008, at 1:05 PM, Casey Allen Shobe wrote:
As for the expectation above - could pl/pgsql be made compilable?
Without getting into the argument as to the level of security
provided, it strikes me that a reasonable approach would be a non-
core pluggable language which accepts encr
On Thu, Sep 25, 2008 at 01:05:26PM -0700, Casey Allen Shobe wrote:
> On Sep 15, 2008, at 7:19 PM, Tom Lane wrote:
>> The problem is that the people who ask for this type of feature are
>> usually imagining that they can put their code on
>> customer-controlled machines and it will be safe from the
Casey Allen Shobe wrote:
> To an extent we *can* simulate row-level and column-level security
> through the use of very restricted data tables and more generally-
> available or specific-purpose views, but we cannot make PostgreSQL
> call a custom function to determine from it's output whether
On Thu, Sep 25, 2008 at 11:01:08AM -0400, Tom Lane wrote:
> Louis-David Mitterrand <[EMAIL PROTECTED]> writes:
> > critik=# select distinct pt.type_fr,sum(e2.id_event) from person_type
> > pt natural join person_to_event join event e using (id_event) LEFT JOIN
> > event e2 ON e.id_event = e2.
On Sep 15, 2008, at 7:19 PM, Tom Lane wrote:
The problem is that the people who ask for this type of feature are
usually
imagining that they can put their code on customer-controlled machines
and it will be safe from the customer's eyes.
That's a broken expectation. All that can realisticall
Thank you all!
Kynn
On Sep 15, 2008, at 2:40 PM, Scott Marlowe wrote:
Like MySQL has built in replication.
You know, I hear this particular example about MySQL's replication
implementation a lot against any sort of new feature, and it's
important to recognize the difference here.
Replication is *not* a funct
depends on Postgres support for Oracle java packages which is now available
thru PL/Java
http://my.safaribooksonline.com/0672327562/ch19lev1sec1
Martin
__
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to t
On Sep 15, 2008, at 1:15 PM, Scott Marlowe wrote:
But whether it's oracle or postgresql, this is just security through
obscurity. If you have root access on the server either method would
be trivial to hack.
You just contradicted yourself. If you have root access on the server
all bets are o
On Thu, Sep 25, 2008 at 10:25 AM, A B <[EMAIL PROTECTED]> wrote:
> Hello.
> I was just asked by a mysql-user how do you do
> insert . on duplicate key update
> (or however they have it in mysql) in postgresql, if you are going to
> Is it correct to assume that a function that is search
On Sep 15, 2008, at 1:04 PM, Christophe wrote:
More seriously, this is the issue with code-encryption on an open
source platform: Where do you keep the key? From my (admittedly
brief) research, it appears that Oracle bakes it into the server
binary, which isn't going to work for PG.
Just
On Thu, Sep 25, 2008 at 12:09 PM, zach cruise <[EMAIL PROTECTED]> wrote:
> on projects where i ended up selecting oracle, *my* main reasons were
> (1) clustering/replication
> (2) cross-database query
> (3) promise of drcp
> in that order
>
> for (1), actually more for synchronization/transfer, i g
I have tried plperl, but the following sample does not work:
CREATE FUNCTION "extract_field_as_varchar" (rec record, field varchar)
RETURNS varchar AS
$body$
...
$body$
LANGUAGE 'plperl' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
It says "ERROR: plperl functions cannot take type record".
S
On 25/09/2008 19:24, Fernando Moreno wrote:
> only have to grant select on the tables you want, and yes, one by one.
If you use pgAdmin, it has a wizard for doing a bunch of objects in one go.
Ray.
--
Raymond O'Donnell, Director of
On Sep 25, 2008, at 3:13 AM, Asko Oja wrote:
but why would you put part of your business logic into some
configuration tables while you could keep it in your own functions
Because as bad as my Not Invented Here syndrome might be at times, I
know that I would not be able to alone build as ele
Hi, first of all, a new role doesn't have any privilege on any table (every
type of database object has different default privileges), so you only have
to grant select on the tables you want, and yes, one by one.
You can also grant or revoke privileges this way: grant select on
table1,table2,table
On Thu, Sep 25, 2008 at 3:52 PM, Andrew Sullivan <[EMAIL PROTECTED]>wrote:
> On Thu, Sep 25, 2008 at 01:13:29PM +0300, Asko Oja wrote:
> >
> > but why would you put part of your business logic into some configuration
> > tables while you could keep it in your own functions
>
> Because the paramete
On Sep 1, 2008, at 12:42 AM, Henry wrote:
This is /finally/ being addressed, although (very) belatedly. The
Pg core
dev team always argued that replication was an add-on and should not
form
part of the core (ie, similar nonsense excuses the MySQL team used for
"add-ons" such as triggers, etc
On Sep 4, 2008, at 7:40 PM, Robert Treat wrote:
It is not as simple as Oracles database link syntax. Setting up a
connection
involves a couple of sql looking commands, and once you setup a
connection to
a remote database, you can reference a table with something like
select *
from [EMAIL PRO
On Sep 15, 2008, at 6:58 AM, David Fetter wrote:
Roles,
We have 'em.
We do NOT have secure application roles or anywhere near the level of
configurability in security aspects as Oracle. We've got a great
foundation, but we lack a lot of fine-grained granularity (e.g. an
Oracle SAR can
"Kynn Jones" <[EMAIL PROTECTED]> writes:
> Suppose I have a table T that has, among its columns, the fields X and Y,
> where Y is an integer, and multiple rows with the same value of X are
> possible. I want to select the rows corresponding to the greatest values of
> Y for each value of X.
You c
"Dmitry Koterov" <[EMAIL PROTECTED]> writes:
> I have a variable with a field name and want to extract this field
> value from NEW record:
> DECLARE
> field VARCHAR = 'some_field';
> BEGIN
> ...
> value := NEW.{field}; -- ???
> END;
> Is it possible in pl/pgsql?
No. Quite aside fro
on projects where i ended up selecting oracle, *my* main reasons were
(1) clustering/replication
(2) cross-database query
(3) promise of drcp
in that order
for (1), actually more for synchronization/transfer, i got a simple
suggestion:
while installing postgresql, why not ask the user to give ip o
uups, you need to specify the table in the select, so
select t.x,t.y,t.z
from
t join (select x, max(y) as my from t group by x) t1 on (t.x=t1.x and
t.y=t1.my)
On Thu, Sep 25, 2008 at 20:05, Harald Armin Massa
<[EMAIL PROTECTED]> wrote:
> select x,y,z
> from
> t join (select x, max(y) as
select x,y,z
from
t join (select x, max(y) as my from t group by x) t1 on (t.x=t1.x and t.y=t1.my)
best wishes
Harald
On Thu, Sep 25, 2008 at 20:01, Kynn Jones <[EMAIL PROTECTED]> wrote:
> Suppose I have a table T that has, among its columns, the fields X and Y,
> where Y is an integer, and mult
Louis-David Mitterrand <[EMAIL PROTECTED]> writes:
> critik=# select distinct pt.type_fr,sum(e2.id_event) from person_type
> pt natural join person_to_event join event e using (id_event) LEFT JOIN event
> e2 ON e.id_event = e2.id_event AND e2.id_event=219 join event_type et ON
> e.id_event
Suppose I have a table T that has, among its columns, the fields X and Y,
where Y is an integer, and multiple rows with the same value of X are
possible. I want to select the rows corresponding to the greatest values of
Y for each value of X. E.g. suppose that T is
X Y Z
a 1 eenie
a 3 meenie
a
Hello
try to look at
http://okbob.blogspot.com/2008/08/using-cursors-for-generating-cross.html
regards
Pavel Stehule
p.s. you should to use transaction
2008/9/25 Reg Me Please <[EMAIL PROTECTED]>:
> Hi all.
>
> I'm running PGSQL v.8.3.3
>
> I tried to adapt the examples from the friendly manua
Reg Me Please <[EMAIL PROTECTED]> writes:
> Unuckily
> MOVE LAST FROM curs1;
> won't work with
> GET DIAGNOSTICS cnt = ROW_COUNT;
Hmm, you're right that MOVE doesn't set row_count (because plpgsql
itself isn't fetching the rows), but it kinda seems like it should.
Hello.
I have a variable with a field name and want to extract this field
value from NEW record:
DECLARE
field VARCHAR = 'some_field';
BEGIN
...
value := NEW.{field}; -- ???
END;
Is it possible in pl/pgsql?
I have found one speed-inefficient solution: convert NEW to string and
then
Hi all.
I'm running PGSQL v.8.3.3
I tried to adapt the examples from the friendly manual (38.7.3.5) in order to
to have a function to create cursors based on a parametric query string:
CREATE SEQUENCE s_cursors;
CREATE OR REPLACE FUNCTION f_cursor( query text, out curs refcursor )
LANGUAGE PLPG
Richard Huxton <[EMAIL PROTECTED]> writes:
> Glyn Astill wrote:
>> Hi people,
>>
>> I'm setting us up a separate staging / test server and I want to read
>> in a pg_dump of our current origin stripping out all the slony stuff.
>>
>> I was thinking this could serve two purposes a) test out backups
>
Tom Lane wrote:
Marcus Engene <[EMAIL PROTECTED]> writes:
I would like to have a function like the above that returns "innan klas"
for this data. I would have expected it to as I use the non greedy version.
regression=# select
regexp_replace
('innan[noindex]apa[/noindex]klas[noind
"Scott Marlowe" <[EMAIL PROTECTED]> writes:
> On Thu, Sep 25, 2008 at 9:38 AM, Tom Lane <[EMAIL PROTECTED]> wrote:
>> The problem you've got here is that the planner has got absolutely no
>> visibility into the behavior of get_dates().
> Couldn't they make a simple immutable function and index on
Marcus Engene <[EMAIL PROTECTED]> writes:
> I would like to have a function like the above that returns "innan klas"
> for this data. I would have expected it to as I use the non greedy version.
regression=# select
regexp_replace
('innan[noindex]apa[/noindex]klas[noindex]banan[/noindex]',
Hi all.
Is there a way in PL/PgSQL to get the number of rows resulting from a:
OPEN curs1 SCROLL FOR EXECUTE query;
before actually fetching any?
Unuckily
MOVE LAST FROM curs1;
won't work with
GET DIAGNOSTICS cnt = ROW_COUNT;
Any hint?
--
Sent via pgsql-general mailing l
Hello.
I was just asked by a mysql-user how do you do
insert . on duplicate key update
(or however they have it in mysql) in postgresql, if you are going to
run commands from the command line?
My solution up till now has been a function with the
BEGIN
insert
EXCEPTION WHEN
thanks so much!
Tom Lane wrote:
Justin Yao <[EMAIL PROTECTED]> writes:
But the question is, why there's no column named "oid" and it still works?
\d doesn't show system columns.
http://www.postgresql.org/docs/8.3/static/ddl-system-columns.html
regards, tom lane
--
On Thu, Sep 25, 2008 at 9:38 AM, Tom Lane <[EMAIL PROTECTED]> wrote:
> The problem you've got here is that the planner has got absolutely no
> visibility into the behavior of get_dates(). In particular it doesn't
> realize that the values being generated are close to the end of the
> range of dat
On Thu, Sep 25, 2008 at 8:24 AM, Jeff Amiel <[EMAIL PROTECTED]> wrote:
> "PostgreSQL 8.2.4 on i386-pc-solaris2.10, compiled by GCC gcc (GCC)
> 3.4.3 (csl-sol210-3_4-branch+sol_rpath)"
> (test environment)
Update your pgsql version to 8.2.10 or whatever is latest. There was
some pathological plann
Justin Yao <[EMAIL PROTECTED]> writes:
> But the question is, why there's no column named "oid" and it still works?
\d doesn't show system columns.
http://www.postgresql.org/docs/8.3/static/ddl-system-columns.html
regards, tom lane
--
Sent via pgsql-general mailing list
So I have created a role
create role my_role with NOSUPERUSER NOCREATEDB NOCREATEROLE LOGIN
ENCRYPTED PASSWORD 'secret';
Now I wish to grant only select for this role on some tables.
So I do
revoke all privileges on table X from my_role;
and i do this for all my tables (X is table name) ?
Hi!
I'm using tsearch2 and sometimes there are blocks of text that shouldn't
be indexed.
There is a trigger function that gathers data from the usual suspects
and updates the index. in this trigger, I'd like to exclude thing in a
[noindex] tag:
select
regexp_replace
('innan[noindex]apa[
-Original Message-
From: Hoover, Jeffrey [mailto:[EMAIL PROTECTED]
change t.date2< dates.date to t.date2+0= dates.date) and t.date2+0http://www.postgresql.org/mailpref/pgsql-general
"Jeff Amiel" <[EMAIL PROTECTED]> writes:
>select sum(amount), dates.date as date
> from transaction t
>join (select get_dates as date from
> get_dates('09/17/08','09/24/08')) dates on
> (t.state='I' or t.date1 >= dates.date)and t.date2
forget it. I am really sorry about that.
it works for me, too.
when I did :
dbname=> \d pg_database
Table "pg_catalog.pg_database"
Column | Type| Modifiers
---+---+---
datname | name | not null
datdba| oid | not null
e
Justin Yao <[EMAIL PROTECTED]> writes:
> In PostgreSQL 7.x, I can use SQL:
> select datname, oid from pg_database
> to find out the numeric directory name under $PGDATA/base for each
> database. But it doesn't work for PostgreSQL 8.3.
It works for me ... what problem are you having?
nothing special, just curious about it.
I suppose it should be able to be located by SQL.
Justin
Tino Wildenhain wrote:
Hi,
Justin Yao wrote:
Hi,
In PostgreSQL 7.x, I can use SQL:
select datname, oid from pg_database
to find out the numeric directory name under $PGDATA/base for each
databas
Hi,
Justin Yao wrote:
Hi,
In PostgreSQL 7.x, I can use SQL:
select datname, oid from pg_database
to find out the numeric directory name under $PGDATA/base for each
database. But it doesn't work for PostgreSQL 8.3.
Is there any way I can do it for 8.3?
What would you do with that name once y
Hi,
In PostgreSQL 7.x, I can use SQL:
select datname, oid from pg_database
to find out the numeric directory name under $PGDATA/base for each
database. But it doesn't work for PostgreSQL 8.3.
Is there any way I can do it for 8.3?
Thanks,
--
Justin Yao
--
Sent via pgsql-general mailing list (p
"PostgreSQL 8.2.4 on i386-pc-solaris2.10, compiled by GCC gcc (GCC)
3.4.3 (csl-sol210-3_4-branch+sol_rpath)"
(test environment)
Picture a table called 'transaction' with 1 million rows.
most (> 99% of the records have date1 and date2 values in the past
(spread over 4 years)
99.99% of the records h
Hi,
Running this query:
critik=# select distinct pt.type_fr,sum(e2.id_event) from person_type
pt natural join person_to_event join event e using (id_event) LEFT JOIN event
e2 ON e.id_event = e2.id_event AND e2.id_event=219 join event_type et ON
e.id_event_type = et.id_event_type where
Bhavik wrote:
> I am newbie for pgsql. Im using Solaris.
>
> 1) Is it possible to have namespace like in OOP, in pgsql query.
> means as I know we can reference table or its column with fashion like
> ., but is it possible like
> .. ???
>
> 2) Is it fine if I store all data in one db
On Thu, Sep 25, 2008 at 01:13:29PM +0300, Asko Oja wrote:
>
> but why would you put part of your business logic into some configuration
> tables while you could keep it in your own functions
Because the parameters of the business logic should not be in the
code. The parameters should be part of
Hi,
Peter Eisentraut wrote:
> I understand how this use case ends up falling through the cracks. But
> the backports infrastructure is not set up for maintaining original
> packages (which PG 8.2 would be become, without a references package in
> testing).
Uh.. so you are proposing to keep (revi
Tommy Gildseth <[EMAIL PROTECTED]> writes:
> ... problem came at the end of the recovery, after the log line:
> [2008-09-23 15:33:14.764 CEST] [pgtest01] [:] [] [18393] [] LOG: archive
> recovery complete
> followed immediately after by this line:
> [2008-09-24 13:04:52.168 CEST] pgtest01] [:
Markus Wanner wrote:
So, please, either decide to backport a Postgres major version and
continue to update it even if it gets dropped from testing *or* don't
backport it at all.
I understand how this use case ends up falling through the cracks. But
the backports infrastructure is not set up
On Wed, Sep 24, 2008 at 11:13 PM, Casey Allen Shobe <[EMAIL PROTECTED]>wrote:
> On Sep 15, 2008, at 6:58 AM, David Fetter wrote:
>
>> Roles,
>>>
>>
>> We have 'em.
>>
>
> We do NOT have secure application roles or anywhere near the level of
> configurability in security aspects as Oracle. We've g
1) Is it possible to have namespace like in OOP, in pgsql query.
means as I know we can reference table or its column with fashion like
., but is it possible like
.. ???
As far as I know this is only possible with dblink which is a contrib
module.
2) Is it fine if I store all data in on
Bhavik <[EMAIL PROTECTED]> schrieb:
> Hi,
>
> I am newbie for pgsql. Im using Solaris.
>
> 1) Is it possible to have namespace like in OOP, in pgsql query.
> means as I know we can reference table or its column with fashion like
> ., but is it possible like
> .. ???
No, you can't ac
Hi,
I am newbie for pgsql. Im using Solaris.
1) Is it possible to have namespace like in OOP, in pgsql query.
means as I know we can reference table or its column with fashion like
., but is it possible like
.. ???
2) Is it fine if I store all data in one db ( well most of the table
Tommy Gildseth wrote:
I've recently been testing our backup/restore procedures, and discovered
a minor inconvenience.
Running 8.2.9 btw
--
Tommy Gildseth
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mail
I've recently been testing our backup/restore procedures, and discovered
a minor inconvenience.
I emptied out the data directory(on a test-box), and restored it from a
backup. I made sure that pg_xlog and pg_xlog/archive_status was empty.
I then set up the recovery.conf file in the root of the
On 2008-09-24 18:01, William Garrison wrote:
> Then I commented-out the constraints from the schema. Then I loaded
> the data.
Don't forget to restore these constraints back after loading data.
> I made a schema only dump, then a data only dump with --inserts. (...)
> Unfortunately, the INSERT
86 matches
Mail list logo
