Personally I'm missing two things, which were discussed in the
past, but would be nice to have:
* more efficient storage of varlen data -- some time ago there were
ideas to get rid of constant 4-bytes for length and use more elastic
approach. Smaller tables, bigger performance.
* updatable views
Hi,
I've noticed that any user who can logon to a db cluster can read the schema
of all databases in it, including the code of all plpgsql functions. Even in
schema's he/she doesn't have access to. For tables it just says 'access
denied for schema bla', after which the structure is still shown
_
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Willy-Bas Loos
Sent: dinsdag 30 januari 2007 9:41
To: pgsql-general@postgresql.org
Subject: [GENERAL] Unauthorized users can see db schema and read
functions
Hi,
I've noticed that any user who can logon to a db
Willy-Bas Loos wrote:
Hi,
I've noticed that any user who can logon to a db cluster can read the
schema
of all databases in it, including the code of all plpgsql functions.
Even in
schema's he/she doesn't have access to.
[snip]
o Why is schema information not restricted?
o Is there any
Dawid Kuroczko [EMAIL PROTECTED] writes:
* updatable views [ or am I missing something? ] -- it seems to me
they were close to be completed, but I don't remember if they were
completed and committed or not.
Something different than rules?
Jorge Godoy wrote:
Dawid Kuroczko [EMAIL PROTECTED] writes:
* updatable views [ or am I missing something? ] -- it seems to me
they were close to be completed, but I don't remember if they were
completed and committed or not.
Something different than rules?
Jorge Godoy wrote:
Dawid Kuroczko [EMAIL PROTECTED] writes:
* updatable views [ or am I missing something? ] -- it seems to me
they were close to be completed, but I don't remember if they were
completed and committed or not.
Something different than rules?
I want to retrieve the primary key, which is a SERIAL, of a row I just
inserted.
In 8.2, I could use RETURNING, but I'm using 8.1.
Reading around, I've seen the following methods discussed:
(1) Within a transation, do the INSERT, and then do a SELECT CURVAL
(2) Not necessarily within a
In 8.2 the CONNECT priviledge was introducted on the database
Wow it works! :D
However, you can use pg_hba.conf to restrict access to a database entirely.
That works too!
You can even allow all databases for all, and reject some databases to some,
as long as the reject lines are above the all
Le mardi 30 janvier 2007 12:19, woger151 a écrit :
(3) Use LASTVAL
for this one : look at
http://people.planetpostgresql.org/xzilla/index.php?/archives/169-Is-lastval-evil.html
My questions:
* Are any of these methods flawed?
* Is there any reason to prefer (1) to (2)?
* I'm not sure
Here's something I've just noticed:
CREATE TABLE foo (f INTEGER PRIMARY KEY);
INSERT INTO foo VALUES (1);
CREATE TABLE bar (b INTEGER REFERENCES foo);
CREATE TABLE bar1 () INHERITS (bar);
INSERT INTO bar1 VALUES (1);
This is quite correct:
TRUNCATE foo;
ERROR: cannot truncate a table
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 01/30/07 00:32, Hakan Kocaman wrote:
Hi,
you can find a nice virtual folder implementation in the
Opera-Mailclient M2. Not sure if this also works with IMAP (don't
use IMAP yet).
Virtual folders are based on regexes over various fields of
Florian Weimer wrote:
Here's something I've just noticed:
CREATE TABLE foo (f INTEGER PRIMARY KEY);
INSERT INTO foo VALUES (1);
CREATE TABLE bar (b INTEGER REFERENCES foo);
CREATE TABLE bar1 () INHERITS (bar);
INSERT INTO bar1 VALUES (1);
This is quite correct:
No, it isn't; try
woger151 [EMAIL PROTECTED] writes:
Reading around, I've seen the following methods discussed:
(1) Within a transation, do the INSERT, and then do a SELECT CURVAL
(2) Not necessarily within a transaction, get a candidate for the pk using
SELECT NEXTVAL, then INSERT the row.
(3) Use LASTVAL
On Mon, 29 Jan 2007, Denis Lussier wrote:
Korry Douglas has an EDB sponsored project called pg_migrator on
pgfoundry. I believe it works for upgrading from 8.1 to 8.2 except for
tables that use the ip address datatype. It works by just replacing the
8.1 system catalogs with the 8.2 system
Hi,
My problem is that if I try to update more than one row in a table like
UPDATE mytable SET something = 84 WHERE not_unique_col = 41;
in two concurrent transactions, it can result in a deadlock if the two
UPDATEs visit the rows in a different order.
The same applies, if I try to
SELECT *
=?ISO-8859-2?Q?D=E1niel_D=E9nes?= [EMAIL PROTECTED] writes:
But what if I try like
SELECT * FROM mytable
WHERE not_unique_col = 41 ORDER BY pri_key ASC FOR UPDATE;
and do the UPDATE after this? It should never lead to a deadlock,
assuming the rows selected FOR UPDATE are locked in the order
Tom Lane [EMAIL PROTECTED] wrote:
Daniel Denes [EMAIL PROTECTED] writes:
But what if I try like
SELECT * FROM mytable
WHERE not_unique_col = 41 ORDER BY pri_key ASC FOR UPDATE;
and do the UPDATE after this? It should never lead to a deadlock,
assuming the rows selected FOR UPDATE
Seems as though I've gotten myself into something of a pickle:
I wound up with a fkey constraint and an index on the same table having the
same name ('rs_fkey').
The result is an error message when I try to drop the table (cascade) or
even drop the constraint:
# alter table
Dblink is nice, but should it really be needed for databases on the same
physical server?
What would be cool is to allow a double dot notation i.e.
database1..schema1.table1
Just a idea. Comments?
--
Tony
---(end of broadcast)---
TIP 9: In
On Tue, 30 Jan 2007, Erik Jones wrote:
I had the same problem: searching for 'pg_migrator' found nothing.
However, searching for 'migrator' got it.
A-ha! I didn't try that, just fell back to Google. :-)
Thanks, Erik,
Rich
--
Richard B. Shepard, Ph.D. |The Environmental
Hi,
My problem is that if I try to update more than one row in a table like
UPDATE mytable SET something = 84 WHERE not_unique_col = 41;
in two concurrent transactions, it can result in a deadlock if the two
UPDATEs visit the rows in a different order.
The same applies, if I try to
SELECT *
Doesn't pg_hba.conf just deal with user connections? If you denied via
pg_hba.conf, wouldn't you also deny access for the application? Can
pg_hba.conf authenticate based on a per application basis? I wasn't aware
of anything like that. I'm not an expert on this, so I could be wrong.
My application implements field and row level security.
I have custom table of users where user privileges are described.
However user can login directly to database using pgAdmin. This bypasses
the security.
How to allow users to login only from my application ?
I think I must create
I've done a bit more digging into this, here's what I've found --
The text db dump file is much too big to edit by hand (~37GB), so I ran the
import in single-step mode:
psql -U bdu -s bdu_01_21_07 bduprod_2-01-21-07
Here's the first error I run across:
***(Single step mode: verify
Rich Shepard wrote:
On Mon, 29 Jan 2007, Denis Lussier wrote:
Korry Douglas has an EDB sponsored project called pg_migrator on
pgfoundry. I believe it works for upgrading from 8.1 to 8.2 except for
tables that use the ip address datatype. It works by just replacing the
8.1 system catalogs
On Tue, 2007-01-30 at 02:35 -0800, Joshua D. Drake wrote:
Something different than rules?
(http://www.postgresql.org/docs/8.2/interactive/rules.html) (They exist for
a
while, I've just linked the latest released docs...)
Quite. Rules are not updateable views. Rules are a hacked up
This has been discussed about ten thousand times, and the answer is
still no.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
What is the delimiter between id and created_at? I believe they're
supposed to be tabs with \t used for tabs inside a field. The data you
give here is all whitespaces.
Mason Hale wrote:
I've done a bit more digging into this, here's what I've found --
The text db dump file is much too big
On Sun, Jan 28, 2007 at 23:46:27 +0200,
Andrus [EMAIL PROTECTED] wrote:
My application implements field and row level security.
I have custom table of users where user privileges are described.
However user can login directly to database using pgAdmin. This bypasses
the security.
How to
Peter Eisentraut wrote:
This has been discussed about ten thousand times, and the answer is
still no.
Why? Seems to me if it was discussed that much it must be a very sought
after feature.
How come it's not on the TO Do list for the future at least?
Is it because of some limitation of
Am 30.01.2007 um 12:11 schrieb Tony Caduto:
Why? Seems to me if it was discussed that much it must be a very
sought after feature.
How come it's not on the TO Do list for the future at least?
Is it because of some limitation of the core engine or something?
I always assumed the general argument is if you need to query different
databases on the same server with the same application, they ought not
to be separate databases because they're clearly related data.
It's kinda like why isn't there a way to do an exactly one to exactly
one relationship
I received a response from the development coordinator of an OSS business
application I'd really like to use, but it works only with MySQL. The
two reasons the one interested developer isn't devoting more time to the
port are a lack of priority and paying sponsor.
However, what puzzles me is
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Panther
Sent: dinsdag 30 januari 2007 7:07
To: pgsql-general@postgresql.org
Subject: [GENERAL] SELECT FOR UPDATE with ORDER BY to avoid
row-level deadlock?
Hi,
My problem is that if I try to update more
In response to Rich Shepard [EMAIL PROTECTED]:
I received a response from the development coordinator of an OSS business
application I'd really like to use, but it works only with MySQL. The
two reasons the one interested developer isn't devoting more time to the
port are a lack of
Does the developer offer any hard evidence for his statement? I mean
like benchmark tests and a side by side list of features?
My impression is that Mysql is set up very narrowly for a typical ISP
offering LAMP and not much else. Once you start going into corporate
installations on private
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 01/30/07 13:33, Brandon Aiken wrote:
I always assumed the general argument is if you need to query different
databases on the same server with the same application, they ought not
to be separate databases because they're clearly related data.
Ron Johnson wrote:
be separate databases because they're clearly related data.
Just because they are related, doesn't mean that it's always wise to
lump it all in the same database. Mainly for scalability and
performance reasons.
I would tend to agree, there are numerous times being
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
For a select array(...) as col1, col2, col3 from table I'd like the DBI
driver to output col1 as a perl array instead of a scalar
{res1,res2,etc.} representation of it.
Is that somehow possible? I looked at the docs without finding
On Jan 29, 11:06 pm, [EMAIL PROTECTED] (Dawid Kuroczko) wrote:
* updatable views [ or am I missing something? ] -- it seems to me
they were close to be completed, but I don't remember if they were
completed and committed or not.
PostgreSQL has updatable views via the rules system. I use
Tony Caduto [EMAIL PROTECTED] writes:
Considering all these other DBs can do it, doesn't it make sense to at
least put it on the radar for Postgresql?
It's already in the TODO list.
regards, tom lane
---(end of
It's interesting that this is yet another issue of where exactly you
want to place your business logic. Do you do it as much as you can on
your sql server or do you bias it towards your client application. It's
obvious that you can do cross database linking in your application
layer, but if
Is it possible to have a pl/pgsql function take another pl/pgsql
function as one of the parameters?
regards,
karen
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your
Mark Walker wrote:
It's sort of a matter of taste, but there are lots of people who like
to keep there logic on the server or at least within sql statements,
so there's probably a good sized market that your not reaching if you
ignore it.
That is a good point, I and many developers I know
On Tue, 30 Jan 2007, Bill Moran wrote:
Consider the source. If he chose to write for MySQL instead of PostgreSQL,
he probably isn't up to speed on what's going on with PostgreSQL.
Bill,
It's 'they' rather than 'he,' but your point is still valid.
PostgreSQL is anything but behind on
On Tue, 30 Jan 2007, Mark Walker wrote:
Does the developer offer any hard evidence for his statement? I mean like
benchmark tests and a side by side list of features?
Mark,
No. And I've read this excuse from them before when I asked about a port.
The application is written in php and they
On Tue, 30 Jan 2007, Rich Shepard wrote:
business sense. However, this seems to be what every CRM/SFA[1]
Oops!
[1] Customer Relations Management/Sales Force Automation.
Rich
--
Richard B. Shepard, Ph.D. |The Environmental Permitting
Applied Ecosystem Services, Inc.
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 01/30/07 14:41, Tony Caduto wrote:
Mark Walker wrote:
It's sort of a matter of taste, but there are lots of people who like
to keep there logic on the server or at least within sql statements,
so there's probably a good sized market that your
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 01/30/07 14:50, Rich Shepard wrote:
On Tue, 30 Jan 2007, Mark Walker wrote:
[snip]
At last year's at O'Reilly's OSCON here in Portland I had this discussion
with the booth babes sales droids from Sugar-CRM. They said that they heard
Ron Johnson wrote:
Who would they target anyways?
There's no one company
They could buy out CommandPrompt and EnterpriseDB...
The buyouts wouldn't *kill* pg, but they would wound it mightily.
I don't think so. High-profile and high priced buyouts
of CommandPrompt and EnterpriseDB
In response to Ron Johnson [EMAIL PROTECTED]:
On 01/30/07 14:50, Rich Shepard wrote:
On Tue, 30 Jan 2007, Mark Walker wrote:
[snip]
At last year's at O'Reilly's OSCON here in Portland I had this discussion
with the booth babes sales droids from Sugar-CRM. They said that they heard
On 30 Jan 2007 12:15:17 -0800, Karen Hill [EMAIL PROTECTED] wrote:
On Jan 29, 11:06 pm, [EMAIL PROTECTED] (Dawid Kuroczko) wrote:
* updatable views [ or am I missing something? ] -- it seems to me
they were close to be completed, but I don't remember if they were
completed and committed or
On 1/30/07, Ron Mayer [EMAIL PROTECTED] wrote:
Ron Johnson wrote:
Who would they target anyways?
There's no one company
They could buy out CommandPrompt and EnterpriseDB...
The buyouts wouldn't *kill* pg, but they would wound it mightily.
I don't think so. High-profile and high
Dawid Kuroczko wrote:
On 1/30/07, Ron Mayer [EMAIL PROTECTED] wrote:
Ron Johnson wrote:
Who would they target anyways?
There's no one company
They could buy out CommandPrompt and EnterpriseDB...
The buyouts wouldn't *kill* pg, but they would wound it mightily.
I don't
On Tue, 2007-01-30 at 12:32 -0800, Karen Hill wrote:
Is it possible to have a pl/pgsql function take another pl/pgsql
function as one of the parameters?
Not directly, but it could take a text string as a parameter and then
EXECUTE the text string after passing it to quote_ident().
Regards,
On 01/30/07 14:41, Tony Caduto wrote:
Mark Walker wrote:
It's sort of a matter of taste, but there are lots of people who like
to keep there logic on the server or at least within sql statements,
so there's probably a good sized market that your not reaching if you
ignore it.
That
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 01/30/07 15:55, Richard Troy wrote:
On 01/30/07 14:41, Tony Caduto wrote:
Mark Walker wrote:
[snip]
These days with good open source choices, things are a bit
different, but that doesn't mean it's always good to go hog wild
with any particular
On Tue, 30 Jan 2007, Ron Johnson wrote:
The company might not have the resources to maintain 2 backends, or modify
the whole system so that it is backend neutral. Maybe they use lots of
MySQL-specific features that would make re-engineering it an
arduous/imposible/expensive task, and thus not
However user can login directly to database using pgAdmin. This bypasses
the security.
If only certain privileged users are supposed to use pgAdmin, can you
arrange so that only they have access to it in the first place? - such
as granting execute permissions on pgAdmin only to the privileged
Dawid Kuroczko [EMAIL PROTECTED] writes:
My point is, its not about throwing money at a problem. PostgreSQL
seems to be having right people at the right place and benefits from
it. They do the hard work, they do it well, hence 8.0, 8.1, 8.2 and
upcoming 8.3 release. If you buy these people
LOL, I remember those days. Uh, can you hold on? My computer just
went down. or you need to fill out form 1203-B, send us $25 and we'll
get you the information you need in six weeks. Just kidding, but
certainly reliability standards and information demands are much higher
these days, aren't
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 01/30/07 16:35, Mark Walker wrote:
LOL, I remember those days. Uh, can you hold on? My computer just
went down. or you need to fill out form 1203-B, send us $25 and we'll
get you the information you need in six weeks. Just kidding, but
On Tue, 30 Jan 2007, Mark Walker wrote:
LOL, I remember those days. Uh, can you hold on? My computer just
went down. or you need to fill out form 1203-B, send us $25 and we'll
get you the information you need in six weeks. Just kidding, but
certainly reliability standards and information
On 1/30/07, Peter Eisentraut [EMAIL PROTECTED] wrote:
This has been discussed about ten thousand times, and the answer is
still no.
How did we go from this?
To this:
It's already in the TODO list.
regards, tom lane
Perhaps we should be more diplomatic in our
Debian Linux. Have always built from scratch with no problem.
This is 8.2.1 from postgresql.org.
Conf line is:
--prefix=/local/pgsql82 --enable-depend --enable-cassert --enable-debug
--with-tcl --with-python --with-perl --with-pgport=5432
Build error is:
gcc -O2 -Wall -Wmissing-prototypes
I don't know. My customers expect 24/7 reliability. They expect to be
able to access their info anywhere in the world over a variety of
different devices. I can remember times when people would just go home
because computer networks were down. I haven't seen that happen in a
long time.
On Tue, 30 Jan 2007, Mark Walker wrote:
I don't know. My customers expect 24/7 reliability. They expect to be
able to access their info anywhere in the world over a variety of
different devices. I can remember times when people would just go home
because computer networks were down. I
Mark Walker [EMAIL PROTECTED] writes:
Maybe that's just my experience with my customers. I have seen signs of
dysfunctional computer systems lately. I was in a fast food restaurant
in San Francisco a few months back and they were manually taking
orders. I think the only reason they
I realize this thread is old, but I just conducted an experiment with pg
8.0.10 and a transaction with a SERIALIZABLE isolation level does
prevent VACUUM from reclaiming rows that were created and then obsoleted
in a subsequent transaction.
Martijn van Oosterhout wrote:
On Thu, Oct 19, 2006
Joseph S wrote:
I realize this thread is old, but I just conducted an experiment with pg
8.0.10 and a transaction with a SERIALIZABLE isolation level does
prevent VACUUM from reclaiming rows that were created and then obsoleted
in a subsequent transaction.
Right. This is expected.
Peter Eisentraut wrote:
This has been discussed about ten thousand times, and the answer is
still no.
Actually the answer is: Check the TODO list. It is listed under Exotic
features, so the answer is, no we can't yes we would like to.
That being said, I think it is a dumb feature. If you
The serializable transaction *can't* see those rows, they were created
and obsoleted after the start of the transaction. The point of make the
transaction serializable in the first place was to allow VACUUM to
reclaim those rows.
Alvaro Herrera wrote:
Joseph S wrote:
I realize this thread
Alvaro Herrera wrote:
Joseph S wrote:
I realize this thread is old, but I just conducted an experiment with pg
8.0.10 and a transaction with a SERIALIZABLE isolation level does
prevent VACUUM from reclaiming rows that were created and then obsoleted
in a subsequent transaction.
Right.
I actually disagree, mildly.
Our system uses two variants of two types of data.
Client data has a presence in the billing database, but has an incarnation in
our runtime servers to allow for authentication. Not the same databases, since
we can't afford the extra time for the hop, which might
Gregory S. Williamson wrote:
I actually disagree, mildly.
Keep in mind that I was speaking generally and to that note, I generally
agree with what you suggest below. The point I was trying to make
and wasn't be clear enough about is most people that want the feature,
want it for the wrong
Added to TODO:
* Add REINDEX CONCURRENTLY, like CREATE INDEX CONCURRENTLY
This is difficult because you must upgrade to an exclusive table lock
to replace the existing index file. CREATE INDEX CONCURRENTLY does
not
have this complication. This
Alvaro Herrera [EMAIL PROTECTED] writes:
Right. This is expected. VACUUM cannot remove them because the
serializable transaction might still want to see those rows.
Joseph S wrote:
The serializable transaction *can't* see those rows, they were created
and obsoleted after the start of the
I found this thread quite depressing because I had forgotten the VACUUM
FULL only reclaims totally empty pages. I have applied the following
documentation patch to recommend periodic REINDEX, and backpatched to
8.2.X docs. I also added some TODO items so hopefully at least we will
keep track of
Joshua D. Drake [EMAIL PROTECTED] writes:
Actually the answer is: Check the TODO list. It is listed under Exotic
features, so the answer is, no we can't yes we would like to.
That being said, I think it is a dumb feature.
FWIW, the SQL committee thinks it's a fine idea --- the SQL-MED
Tom Lane wrote:
Bill Moran [EMAIL PROTECTED] writes:
The entire database was around 28M prior to the upgrades, etc. Immediately
after the upgrades, it was ~270M. Following a vacuum full, it dropped to
165M. Following a database-wide reindex, it dropped to 30M.
As Alvaro said, vacuum
On Tue, Jan 30, 2007 at 06:15:01PM -0800, Joshua D. Drake wrote:
Peter Eisentraut wrote:
This has been discussed about ten thousand times, and the answer is
still no.
Actually the answer is: Check the TODO list. It is listed under
Exotic features, so the answer is, no we can't yes we
On Tue, Jan 30, 2007 at 04:43:14PM -0800, Richard Troy wrote:
On Tue, 30 Jan 2007, Mark Walker wrote:
I don't know. My customers expect 24/7 reliability. They expect
to be able to access their info anywhere in the world over a
variety of different devices. I can remember times when
On Tue, Jan 30, 2007 at 16:43:14 -0800,
Richard Troy [EMAIL PROTECTED] wrote:
be better - and once were. (Example, anyone who thinks man pages are
great has obviously got a very limited experience from which to base their
opinion!) ... As a practical matter today we mostly have a choice of
David Fetter [EMAIL PROTECTED] writes:
On Tue, Jan 30, 2007 at 04:43:14PM -0800, Richard Troy wrote:
... different in my opinion if only Unix didn't have this asenine view
that the choice between a memory management strategy that kills
random processes and turning that off and accepting that
In addition to the other good suggestions, modify you program to record a
plain old text log of dangerous actions confirmed by users. These kinds of
people usually shut up pretty quickly when you tell them the date, time, IP
address of the machine, and login name of the user who did it.
--
Scott
[EMAIL PROTECTED] writes:
Seems as though I've gotten myself into something of a pickle:
I wound up with a fkey constraint and an index on the same table having the
same name ('rs_fkey').
That shouldn't be a problem particularly.
The result is an error message when I try to drop the table
David Fetter wrote:
That being said, I think it is a dumb feature. If you have data in
one database, that requires access to another database within the
same cluster. You designed your database incorrectly and should be
using schemas.
I would have to disagree, it's a feature that has
Richard Troy wrote:
[snip]
My observation is that we have a real shortage of quality operating
systems today, and what few exist/remain don't enjoy much market share
because they're not based on Unix, so they're largely missing out on the
Open Source activity. What may be worse, young people
On Tue, 2007-01-30 at 23:45 -0600, Tony Caduto wrote:
David Fetter wrote:
That being said, I think it is a dumb feature. If you have data in
one database, that requires access to another database within the
same cluster. You designed your database incorrectly and should be
using schemas.
Thanks for taking a look Tom:
I am running postgres 8.1.4 on RedHet (CentOS) v4.0. Here is the
description of the purchase_record table (somewhat abbreviated with
uninvolved columns omitted):
# \d purchase_record
Table public.purchase_record
Column
91 matches
Mail list logo
