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
Hi,
Peter Eisentraut wrote:
As a matter of policy, backports are made from Debian testing. Continued
maintenance of PG 8.2 packages is not really backporting, since there is
nothing to backport from.
While that's certainly true, I think there's enough of a reason for an
exception. Otherwise
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
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
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:
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
schema.table_name, but is it possible like
db_name.schema.table_name ???
2) Is it fine if I store all
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
schema.table_name, but is it possible like
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
schema.table_name, but is it possible like
db_name.schema.table_name ???
As far as I know this is only possible with dblink which is a contrib
module.
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 got a great
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
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] [:] []
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 (revive)
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
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
schema.table_name, but is it possible like
db_name.schema.table_name ???
2) Is it
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
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
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
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
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
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?
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
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
-Original Message-
From: Hoover, Jeffrey [mailto:[EMAIL PROTECTED]
change t.date2 dates.date to t.date2+0dates.date, this will prevent
the query from trying to use the index on date2 because the where clause
now references an expression and not the column itself:
explain analyze
select
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
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)
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
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 planner
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 dates
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
--
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
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
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]',
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 that?
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
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
restore
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
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
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
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 manual
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
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_type
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
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 my
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
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 from the lack
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 could
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
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
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,
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 parameters of the
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
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
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
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.
So,
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 got a
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 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 searching
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
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
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
Thank you all!
Kynn
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
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.id_event
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 or
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
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
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
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:
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
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
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:
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
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
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/
--
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
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
-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 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
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
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 previously
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
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 |
which webserver does your client want to implement?
if webserver is Apache does your client favor any JavaScript Library
Dojo/Scriptaculous/Flex ?
If JSLibrary = Dojo I would look at JSON (JavaScript Object Notation language)
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
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
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
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,
87 matches
Mail list logo
