Re: [GENERAL] Problem with volatile function

Tom Lane wrote:
> Craig Ringer <craig@postnewspapers.com.au> writes:
>> Personally I'd expect that to only evaluate once. It's saying "where
>> f_name.counter in this row is equal to some single random value
>> generated at the start of the query". The parameters of the random()
>> function do not depend on the input, so Pg evaluates it once rather than
>> with each row.
>
> No, that's not the model that we use. In my view, the semantics
> specified by the SQL standard are that
> SELECT ... FROM a, b, c WHERE ...
> are that you notionally form the Cartesian product of a X b X c
> and then evaluate the WHERE condition at each row of the product
> to find out which rows make it into the SELECT result. Obviously,
> the DBMS is going to try like heck to optimize the execution of that
> model, but its optimizations should not change the results.

Thanks for explaining that; I'd completely misunderstood the way Pg
handles evaluation of expressions that don't explicitly reference fields
in the records being scanned.

I think the workarounds I suggested should still work, despite the
incorrect reasoning on which they were based.

--
Craig Ringer

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[planet] Simon added to planet

$SUBJECT.

FYI.
--
Devrim GÜNDÜZ , RHCE
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/

Re: [GENERAL] Problem with volatile function

> You can force Pg to re-evaluate random() by adding a dummy parameter
> that depends on the input record, or (probably better) by writing a
> variant of it that tests the input against a randomly generated value
> and returns a boolean. Eg:
>
>
Thanks all. So here's the situation. I added a dummy parameter and
passed the id like you suggested. That had no effect. I still got one
name for males and one name for females. So I used the 3rd param in a
trivial way:

select round($2*random()*$1+($3/10000000))::int;

And that actually forced it to execute for every row. However, it
returned unpredictable results. There should have been only one match
for first_name and last_name for each person but it return from 1 to 5
rows for each person.

sis_id gender name name counter counter
--------- --------- ---------- ---------- ---------- ----------
105607 M Denis Weber 19 671
105666 M Javier Custodio 154 182
105666 M Javier Nelson 154 250
105839 M Johnnie Whicker 295 32
105847 F Trina Garcia 259 155
105847 F Dione Freeman 103 651
105847 F Dione Harden 103 897
105847 F Cruz Brannen 249 1240

So what I actually had to do was get the sis_id and the two random
numbers in a subselect.

SELECT stu.sis_id, stu.gender, f_name.name AS first_name, l_name.name AS
last_name
FROM usr_students stu
JOIN (
SELECT sis_id, random(1,300) AS f_cnt, random(1,1700) AS l_cnt
FROM usr_students s
) sub ON stu.sis_id = sub.sis_id
JOIN names f_name ON stu.gender = f_name.gender
AND f_name.counter = sub.f_cnt
JOIN names l_name ON l_name.gender IS NULL
AND l_name.counter = sub.l_cnt

So while that works, postgres isn't behaving how I'd expect (or how Tom
expects from the sounds of it)

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] PITR base backup -- stop server or not?

The docs for Making a Base Backup (tar) say that it can be done live
without stopping the server:
http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html#BACKUP-BASE-BACKUP
(step #3)

However, the docs for straight File System Level Backup (tar) say the
server must be shut down:
http://www.postgresql.org/docs/8.3/interactive/backup-file.html
(restriction #1)

Is this because replaying the WAL files will fix any of the issues
listed in the File System Level Backup restriction #1?

Thanks,
Rob Adams

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [pgsql-es-ayuda] EMPRESAS QUE USAN POSTGRES

2008/6/18 Franz Marin <frarimava@hotmail.com>:
> Buenas tardes
>
> quisiera saber que empresas reconocidas a nivel mundial usan PostgreSQL como
> motor de base de datos ...
>

Algunos ejemplos, aunque algunas no son empresas reconocidas sino
agencias gubernamentales...

* Agencias gubernamentales de Estados Unidos (City of Garden Grove,
CA; National Gallery;
Media Library project of the Library of Congress; US Army; Dept of Forestry;
State of California; NCSA; National Weather Hidrology Laboratory).

* The Oxford University Computing Services, Skype,Yahoo, Fujitsu, Red Hat,
Sun Microsystems.

* Ecuador:
o Universidad Politecnica Salesiana.
o PaloSanto Solutions (http://www.palosanto.com/) Sistemas
de información gerencial
o La SENACYT, Secretaría Nacional de Ciencia y Tecnología.
o La subsecretaria de informatica del Ecuador
(www.compraspublicas.gov.ec).
o Aerogal (http://www.aerogal.com.ec/), Línea Aerea sistema
de reservas de pasajes.

* Perú:
o Asociación de empleados del BCP.
o Americatel
o Camara de Comercio de Lima
o Prompyme atiende el portal de compras del estado.
o Grupo Carolina. Sistema de ordenes de trabajo.
o Gobierno Regional Lambayeque

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Guayaquil - Ecuador
Cel. (593) 87171157
--
TIP 10: no uses HTML en tu pregunta, seguro que quien responda no podrá leerlo

[NOVICE] yum repos problem

I'm actually have installed:
postgresql- 8.1.11-1.el5_1.1
pgadmin3.i686 0:1.8.4-1.rhel5

I added the Centos Repo as it saids on this page
http://yum.pgsqlrpms.org/howtoyum.php
to have the new releases.

And downloaded and installed the repo's key from
http://yum.pgsqlrpms.org/reporpms/repoview/pgdg-centos.html
with rpm -Uvh, then edited the repos Base and Update.

OK nothing wrong at this point:

But when I made a yum update, then:

Package postgis-1.3.3-1.rhel5.i686.rpm is not signed

Any suggestion?



pgsql-novice@postgresql.org

Re: [GENERAL] UTF8 encoding problem

On 18/giu/08, at 15:00, Michael Fuhr wrote:

> On Wed, Jun 18, 2008 at 08:25:07AM +0200, Giorgio Valoti wrote:
>> On 18/giu/08, at 03:04, Michael Fuhr wrote:
>>> Is the data UTF-8? If the error is 'invalid byte sequence for
>>> encoding "UTF8": 0xa3' then you probably need to set client_encoding
>>> to latin1, latin9, or win1252.
>>
>> Why?
>
> UTF-8 has rules about what byte values can occur in sequence;
> violations of those rules mean that the data isn't valid UTF-8.
> This particular error says that the database received a byte with
> the value 0xa3 (163) in a sequence of bytes that wasn't valid UTF-8.
>
> The UTF-8 byte sequence for the pound sign (£) is 0xc2 0xa3. If
> Garry got this error (I don't know if he did; I was asking) then
> the byte 0xa3 must have appeared in some other sequence that wasn't
> valid UTF-8. The usual reason for that is that the data is in some
> encoding other than UTF-8.
>
> Common encodings for Western European languages are Latin-1
> (ISO-8859-1), Latin-9 (ISO-8859-15), and Windows-1252. All three
> of these encodings use a lone 0xa3 to represent the pound sign. If
> the data has a pound sign as 0xa3 and the database complains that
> it isn't part of a valid UTF-8 sequence then the data is likely to
> be in one of these other encodings.

Much clearer now, thank you Michael.

--
Giorgio Valoti
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [PATCHES] Rewrite sinval messaging to reduce contention

I wrote:
> ... enabling LWLOCK_STATS shows that the contention
> rate on the sinval locks is now completely negligible --- one block
> per thousand acquisitions on SInvalWriteLock, and less than one in
> 10000 on SInvalReadLock. The vast majority of the LWLock contention
> now comes from WALInsertLock and the LockMgr locks:

> Lock # acquisitions # times blocked

> SInvalReadLock 6469840 380
> SInvalWriteLock 240567 163
> WALInsertLock 2388805 89142
> LockMgr partition locks 8253142 177715

For comparison's sake I rebuilt CVS HEAD with LWLOCK_STATS enabled
and repeated the same test. I got

SInvalLock 81090044 505750
WALInsertLock 2382254 62747
LockMgr locks 10657480 171799

The change in sinval numbers is gratifying, but for awhile I didn't
believe these results because of the discrepancy in LockMgr acquisition
figures. I think though that what we are seeing here is that CVS HEAD
has the reset-everyone-on-sinval-queue-overflow behavior, which results
in a whole lot of useless cache resets, which results in a lot of
unnecessary cache reloads, and every one of those requires taking
AccessShareLock on one or more system catalogs in order to suck the data
back in. So the reduction in LockMgr traffic is explained by not doing
so many cache resets.

regards, tom lane

--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

Re: [GENERAL] Problem with volatile function

Craig Ringer <craig@postnewspapers.com.au> writes:
> Personally I'd expect that to only evaluate once. It's saying "where
> f_name.counter in this row is equal to some single random value
> generated at the start of the query". The parameters of the random()
> function do not depend on the input, so Pg evaluates it once rather than
> with each row.

No, that's not the model that we use. In my view, the semantics
specified by the SQL standard are that
SELECT ... FROM a, b, c WHERE ...
are that you notionally form the Cartesian product of a X b X c
and then evaluate the WHERE condition at each row of the product
to find out which rows make it into the SELECT result. Obviously,
the DBMS is going to try like heck to optimize the execution of that
model, but its optimizations should not change the results.

We're not perfect about that, in a number of ways --- in particular,
we allow short-circuit evaluation of ANDed conditions, which means
that we might not bother to execute a volatile function for some
row of the Cartesian product if we're able to exclude that row from
the result based on other WHERE conditions.

The OP's complaint is a bit different from this, in that (if I
understand it correctly) he doesn't want the WHERE condition to
be dropped down to the individual relation scan, because that amounts
to improperly assuming that the volatile WHERE condition will produce
the same result at every row of the Cartesian product that included
that row from the base relation. This gripe isn't without foundation.
But I'm not seeing what properties you get from that that wouldn't
be broken by AND-shortcircuit evaluation.

Dropping the AND-shortcircuit optimization would mean that we revert
to the brain-dead "form the Cartesian product and then filter" approach
if even one volatile function appears anywhere in the WHERE conditions.
I doubt that either the OP or anyone else wants that.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[pgadmin-hackers] Building pgadmin3 against PostgreSQL 7.4

I got a few requests to push pgadmin3 to EPEL-4, and here is the build
log for i386:

http://buildsys.fedoraproject.org/logs/fedora-4-epel/39320-pgadmin3-1.8.4-1.el4/i386/build.log

How can this be fixed, or should we ignore 7.4 support?

--
Devrim GÜNDÜZ , RHCE
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/

Re: [GENERAL] Problem with volatile function

Artacus wrote:
> So my understanding of volatile functions is that volatile functions can
> return different results given the same input.
>
> I have a function random(int, int) that returns a random value between
> $1 and $2. I want to use it in a query to generate values. But it only
> evaluates once per query and not once per row like I need it to.
>
> -- This always returns the same value
> SELECT ts.sis_id, bldg_id, f_name.name, l_name.name
> FROM tmp_students ts
> JOIN names AS f_name ON
> ts.gender = f_name.gender
> WHERE f_name.counter = random(1,300)
>
> --As does this
> SELECT ts.sis_id, bldg_id, f_name.name, l_name.name
> FROM tmp_students ts
> JOIN names AS f_name ON
> ts.gender = f_name.gender
> AND ts.counter = random(1,100)
>
> -- This generates different numbers
> SELECT random(1,100), s.*
> FROM usr_students s
>
Are you sure it's volatile? (as opposed to stable)

postgres=# create or replace function random(int,int) returns int as
$$select round($2*random()*$1)::int;$$ language sql volatile;
CREATE FUNCTION
postgres=# select generate_series from generate_series(1,10,1) where
random(1,100) > 50;
generate_series
-----------------
1
3
4
6
7
9
(6 rows)

postgres=# select generate_series from generate_series(1,10,1) where
random(1,100) > 50;
generate_series
-----------------
3
7
8
9
(4 rows)

postgres=# select random(1,100), generate_series from
generate_series(1,10,1);
random | generate_series
--------+-----------------
56 | 1
23 | 2
80 | 3
57 | 4
16 | 5
99 | 6
9 | 7
41 | 8
90 | 9
88 | 10
(10 rows)

postgres=# create or replace function random(int,int) returns int as
$$select round($2*random()*$1)::int;$$ language sql stable;
CREATE FUNCTION
postgres=# select generate_series from generate_series(1,10,1) where
random(1,100) > 50;
generate_series
-----------------
1
2
3
4
5
6
7
8
9
10
(10 rows)

postgres=# select generate_series from generate_series(1,10,1) where
random(1,100) > 50;
generate_series
-----------------
(0 rows)

postgres=# select random(1,100), generate_series from
generate_series(1,10,1);
random | generate_series
--------+-----------------
72 | 1
90 | 2
53 | 3
47 | 4
53 | 5
33 | 6
10 | 7
56 | 8
78 | 9
87 | 10
(10 rows)

postgres=#

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Problem with volatile function

Artacus wrote:
> So my understanding of volatile functions is that volatile functions can
> return different results given the same input.
>
> I have a function random(int, int) that returns a random value between
> $1 and $2. I want to use it in a query to generate values. But it only
> evaluates once per query and not once per row like I need it to.
>
> -- This always returns the same value
> SELECT ts.sis_id, bldg_id, f_name.name, l_name.name
> FROM tmp_students ts
> JOIN names AS f_name ON
> ts.gender = f_name.gender
> WHERE f_name.counter = random(1,300)

Personally I'd expect that to only evaluate once. It's saying "where
f_name.counter in this row is equal to some single random value
generated at the start of the query". The parameters of the random()
function do not depend on the input, so Pg evaluates it once rather than
with each row. That's not to do with volatility; rather, it's the
semantics of your query.

It's like the difference between correlated and uncorrelated subqueries.
A subquery that doesn't reference outside context is only evaluated
once, ie it's uncorrelated. Only if the subquery contains references to
values in rows in the outside query is it evaluated once for each row.

If Pg re-evaluated your random() function for every record, how would
you then write "fetch all records that have f_name.counter equal to the
same randomly selected value" ?

You can force Pg to re-evaluate random() by adding a dummy parameter
that depends on the input record, or (probably better) by writing a
variant of it that tests the input against a randomly generated value
and returns a boolean. Eg:

SELECT ts.sis_id, bldg_id, f_name.name, l_name.name
FROM tmp_students ts
JOIN names AS f_name ON
ts.gender = f_name.gender
WHERE random_equals(1,300,f_name.counter)

random_equals would be called once per input record. It would generate a
random number between 1 and 300, and test the result to see if it was
equal to the argument value f_name.counter, then return the result of
the test.

Alternately, you could pair each result up with a random value and
re-check the results with a subquery (slower):

SELECT sis_id, bldg_id, fname, lname
FROM
(SELECT
ts.sis_id,
bldg_id,
f_name.name AS fname,
l_name.name AS lname,
f_name.counter AS counter,
random(1,300) AS r
FROM tmp_students ts
JOIN names AS f_name ON
ts.gender = f_name.gender
)
AS x
WHERE r = counter;

... but that's pretty ugly.

> --As does this
> SELECT ts.sis_id, bldg_id, f_name.name, l_name.name
> FROM tmp_students ts
> JOIN names AS f_name ON
> ts.gender = f_name.gender
> AND ts.counter = random(1,100)

Same deal as above.

>
> -- This generates different numbers
> SELECT random(1,100), s.*
> FROM usr_students s

... because you've used random() in the result list. That's where
VOLATILE and STABLE etc will make a difference.

--
Craig Ringer

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Problem with volatile function

Artacus <artacus@comcast.net> writes:
> I have a function random(int, int) that returns a random value between
> $1 and $2. I want to use it in a query to generate values. But it only
> evaluates once per query and not once per row like I need it to.

> -- This always returns the same value
> SELECT ts.sis_id, bldg_id, f_name.name, l_name.name
> FROM tmp_students ts
> JOIN names AS f_name ON
> ts.gender = f_name.gender
> WHERE f_name.counter = random(1,300)

You haven't really explained what your problem is, but offhand I'd
expect random() to be evaluated once per f_name row here. What are
you looking for?

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [HACKERS] sh -> pl

Bruce Momjian wrote:
> David Fetter wrote:
>
>> On Tue, Jun 17, 2008 at 10:19:59AM -0400, Andrew Dunstan wrote:
>>
>>> David Fetter wrote:
>>>
>>>> Folks,
>>>>
>>>> I've noticed that a big hunk of our build system has gratuitous
>>>> dependencies on some variety of shell and on tools like sed, none
>>>> of which makes Windows developers feel welcome. I know people are
>>>> working toward a cmake or other more cross-platform toolchain. My
>>>> proposal is a lot more modest, and doesn't conflict with the larger
>>>> one. I'd like to move the above stuff to self-contained perl would
>>>> help to make things more cross-platform and clean up, no offense to
>>>> the fine authors, some pretty crufty code in there.
>>>>
>>> Give us some examples.
>>>
>> That new version stamper calls out to sed, when perl is perfectly
>> capable of doing the same work itself and not spawning 30 shells in
>> the process.
>>
>
> This does remind me of the "Useless Use of Cat Award" from
> comp.unix.shell:
>
>

http://partmaps.org/era/unix/award.html
>

Well, yes. I was confused before. I thought David was referring to the
new add_cvs_markers script (which BTW does not spawn any useless shells
at all).

This script (version_stamp.pl) is already perl, and so David's criticism
of it is slightly justified, although it spawns sh and sed 5 times, not
30. It is at best a very minor inefficiency - something that mildly
annoys those of us well at home in perl and is blithely and not
unreasonably ignored by everyone else.

Note also that this script is not part of the build process either, so
the Windows developer argument hardly holds here.

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[GENERAL] Problem with volatile function

So my understanding of volatile functions is that volatile functions can
return different results given the same input.

I have a function random(int, int) that returns a random value between
$1 and $2. I want to use it in a query to generate values. But it only
evaluates once per query and not once per row like I need it to.

-- This always returns the same value
SELECT ts.sis_id, bldg_id, f_name.name, l_name.name
FROM tmp_students ts
JOIN names AS f_name ON
ts.gender = f_name.gender
WHERE f_name.counter = random(1,300)

--As does this
SELECT ts.sis_id, bldg_id, f_name.name, l_name.name
FROM tmp_students ts
JOIN names AS f_name ON
ts.gender = f_name.gender
AND ts.counter = random(1,100)

-- This generates different numbers
SELECT random(1,100), s.*
FROM usr_students s

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [NOVICE] Step 1 with Visual Basic

On Tue, Jun 17, 2008 at 4:58 PM, Gary <sql@4myad.tv> wrote:

> Is there a newsgroup with "training wheels"?

I haven't heard anything back. Were you able to connect to PostgreSQL
using Visual Basic?

--
Regards,
Richard Broersma Jr.

Visit the Los Angles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

Re: [GENERAL] Forcibly vacating locks

Laurent Birtz wrote:
> Hello,
>
> I am using Postgres in a high-availability environment and I'd like to
> know whether Postgres has provisions to kick off a misbehaving client
> that has obtained an advisory lock on the database and won't release it
> in a timely fashion. I am not worried about malicious clients, however I
> am concerned that a client may hang for a very long time in the middle of
> a transaction due to a programming error, an overloaded machine or
> another bizarre set of circumstances. TCP keepalive packets can improve
> the situation, but they won't prevent some problems from occurring.
>
> For this reason, it is the policy of my company to avoid using explicit
> locks in Postgres altogether. However, as you can imagine, it is hard at
> times to avoid race conditions with this programming model.
>
> Thus, I'd like Postgres to offer a function like set_watchdog(int nb_ms).
> I would call set_watchdog(10000) to enable the watchdog just before I
> obtained the lock, then I would call set_watchdog(0) to disable the
> watchdog after I released the lock. If a client froze, the watchdog would
> eventually trigger and drop the connection to the client, thereby
> preventing the whole system from freezing.
>
> I have three specific questions:
>
> 1) Does Postgres offer something like this already? I'm aware of
> statement_timeout, but it doesn't do exactly what I need. A possible
> kludge would be to parse the 'pg_locks' table and kill the offending
> Postgres backend, but I'd rather avoid doing this.

No. The closest thing we have is log_lock_waits in 8.3. I wonder if
you could hack up something to monitor the server logs for such messages
and cancel the queries.

> 2) Is there any hostility about the notion of implementing this feature
> into Postgres?

Probabably --- it seems like a narrow use case.

> 3) Would it be hard to implement it? After a brief code review, I think
> it would make sense to reuse the SIGALARM signal used by
> statement_timeout to forcibly close the Postgres connection when
> the watchdog triggers.

Not too hard.

--
Bruce Momjian <bruce@momjian.us>

http://momjian.us

EnterpriseDB

http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [PATCHES] Rewrite sinval messaging to reduce contention

I wrote:
> In this thread back in January
> http://archives.postgresql.org/pgsql-performance/2008-01/msg00001.php
> we saw evidence that contention for SInvalLock was a problem,
> and we (or at least I) concluded that the sinval messaging system
> needed a nontrivial rewrite to get rid of its habit of sending everyone
> to read the queue at the same time. Here is the proposed rewrite.

After further thought, I came up with two more improvements:

* It turns out not to be too hard to apply the idea of moving multiple
messages per LWLock acquisition on the writing side too, so I did that.

* There is not actually any good reason why writers have to block
readers or vice versa, except in the infrequent case that SICleanupQueue
is needed. Writers don't look at the ProcStates that the readers are
changing. The only point of overlap is that a writer will change
maxMsgNum which readers want to look at --- but we are already assuming
in several places that reading or writing an int is atomic, and if we
assume that here too, it seems to work fine. To implement this, I split
SInvalLock into SInvalReadLock and SInvalWriteLock.

This seems to be reaching a point of diminishing returns for the
particular test case I'm using --- the TPS rate only went up from 700
to 730 or so. However, enabling LWLOCK_STATS shows that the contention
rate on the sinval locks is now completely negligible --- one block
per thousand acquisitions on SInvalWriteLock, and less than one in
10000 on SInvalReadLock. The vast majority of the LWLock contention
now comes from WALInsertLock and the LockMgr locks:

Lock # acquisitions # times blocked

SInvalReadLock 6469840 380
SInvalWriteLock 240567 163
WALInsertLock 2388805 89142
LockMgr partition locks 8253142 177715

So I think this patch accomplishes the goal of making sinval not be a
cause of contention.

Patch version 2 attached.

regards, tom lane

Re: [GENERAL] HA best pratices with postgreSQL

On Wed, Jun 18, 2008 at 8:44 PM, Albretch Mueller <lbrtchx@gmail.com> wrote:
> ~
> I am developing a J2EE application that needs for users to only read
> DB tables. All queries are select ones, no updates, no inserts, no
> deletes for web users, so I keep this ro DB tables in certain
> partitions which I mount as ro
> ~
> For performance reasons I keet the DB in the same box as the server
> ~
> Now, the data in those tables need to actually be updated not by web
> users, but from the back end and not that often at all, say just once
> of twice a day in a totally controlled way and updates shouldn't take
> long. Just some insert stats in single tables

[very roundabout approach deleted]

> ~
> Has any of you guys heard of something like that or how to basically
> achieve the same thing by other, more standard means?

Create a user for the J2EE app, and configure that app to connect as
that user. Grant only SELECT privileges to that user on the tables it
needs to see. Create a second user for the backend app and grant it
SELECT, INSERT, UPDATE, and DELETE on those tables.

There should be no need to mess about with read-only partitions or
anything like that. SQL permissions should be all you need.

-Doug

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [HACKERS] sh -> pl

David Fetter wrote:
> On Tue, Jun 17, 2008 at 10:19:59AM -0400, Andrew Dunstan wrote:
> > David Fetter wrote:
> >> Folks,
> >>
> >> I've noticed that a big hunk of our build system has gratuitous
> >> dependencies on some variety of shell and on tools like sed, none
> >> of which makes Windows developers feel welcome. I know people are
> >> working toward a cmake or other more cross-platform toolchain. My
> >> proposal is a lot more modest, and doesn't conflict with the larger
> >> one. I'd like to move the above stuff to self-contained perl would
> >> help to make things more cross-platform and clean up, no offense to
> >> the fine authors, some pretty crufty code in there.
> >
> > Give us some examples.
>
> That new version stamper calls out to sed, when perl is perfectly
> capable of doing the same work itself and not spawning 30 shells in
> the process.

This does remind me of the "Useless Use of Cat Award" from
comp.unix.shell:

http://partmaps.org/era/unix/award.html

--
Bruce Momjian <bruce@momjian.us>

http://momjian.us

EnterpriseDB

http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [PERFORM] An "obvious" index not being used

Tom Lane ha scritto:
> Daniele Varrazzo <piro@develer.com> writes:
>> There is an index in the field "foo.account_id" but is not used. The resulting
>> query plan is:
>
>> Aggregate (cost=300940.70..300940.71 rows=1 width=0) (actual
>> time=13412.088..13412.089 rows=1 loops=1)
>> -> Hash IN Join (cost=11.97..299858.32 rows=432953 width=0) (actual
>> time=0.678..13307.074 rows=92790 loops=1)
>> Hash Cond: (foo.account_id = accounts.id)
>> -> Seq Scan on foo (cost=0.00..275591.14 rows=5313514 width=4)
>> (actual time=0.014..7163.538 rows=5313514 loops=1)
>
> Well, if the estimate of 432953 rows selected were correct, it'd be
> right not to use the index. Fetching one row in ten is not a chore
> for an indexscan. (I'm not sure it'd prefer an indexscan even with an
> accurate 92K-row estimate, but at least you'd be in the realm where
> tweaking random_page_cost would make a difference.)

Let me guess: because the account tables has an estimated (and correct) guess
of 22 records fetched out from 270 =~ 8%, it assumes that it will need to
fetch the 8% of 5.3M records (which... yes, it matches the estimate of 433K).
Well, this seems terribly wrong for this data set :(

> I'm not sure why that estimate is so bad, given that you said you
> increased the stats target on the table. Is there anything particularly
> skewed about the distribution of the account IDs?

Probably there is, in the sense that the relatively many accounts of 'abc'
type are referred by relatively few records. In the plan for the hardcoded
query the estimate is:

-> Bitmap Index Scan on ifoo_x1 (cost=0.00..4115.67 rows=178308
width=0) (actual time=89.766..89.766 rows=92790 loops=1)

which is actually more accurate.

I suspect the foo.account_id statistical data are not used at all in query:
the query planner can only estimate the number of accounts to look for, not
how they are distributed in the referencing tables. It seems the only way to
get the proper plan is to add a load of fake accounts! Well, I'd rather have
the query executed in 2 times, in order to have the stats correctly used: this
is the first time it happens to me.

--
Daniele Varrazzo - Develer S.r.l.
http://www.develer.com

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Fwd: [pgsql-es-ayuda] EMPRESAS QUE USAN POSTGRES

A la lista...

Saludos,

Juan

---------- Mensaje reenviado ----------
De: Franz Marin <frarimava@hotmail.com>
Fecha: 18 de junio de 2008 20:48
Asunto: Re: [pgsql-es-ayuda] EMPRESAS QUE USAN POSTGRES
Para: Juan Manuel Fernandez <jmfernandez@unlu.edu.ar>

buenas tardes!

la pregunta no es con nivel de sarcasmo... el caso es el siguiente
estamos presentando una propuesta para una empresa y sustentamos todo
de porque ladb deber ser postgres
pero nos dicen que necesitan referencias de empresas .... pero soy
gran defensor de postgres como db me parece exelente

--------------------------------------------------
From: "Juan Manuel Fernandez" <jmfernandez@unlu.edu.ar>
Sent: Wednesday, June 18, 2008 5:56 PM
To: "Lista - PostgreSQL" <pgsql-es-ayuda@postgresql.org>
Subject: Re: [pgsql-es-ayuda] EMPRESAS QUE USAN POSTGRES

> 2008/6/18 Ruben Guinez <rubenvive@gmail.com>:
>>
>> On Wed, Jun 18, 2008 at 6:09 PM, Javier Ochoa (uolsinectis)
>> <javier_8a@uolsinectis.com.ar> wrote:
>>>
>>> mmm me suena a ...dime con quien andas y te dire quien eres...
>>> ________________________________
>>> De: pgsql-es-ayuda-owner@postgresql.org
>>> [mailto:pgsql-es-ayuda-owner@postgresql.org] En nombre de Franz Marin
>>> Enviado el: Miércoles, 18 de Junio de 2008 18:42
>>> Para: lista de postgres
>>> Asunto: [pgsql-es-ayuda] EMPRESAS QUE USAN POSTGRES
>>>
>>> Buenas tardes
>>>
>>>
>>> quisiera saber que empresas reconocidas a nivel mundial usan PostgreSQL
>>> como motor de base de datos ...
>>
>> Creo que el tono de la pregunta no es el indicado !!!!. Huele a sarcasmo. Y
>> si no es el caso, creo que se mal entendió el sentido de la lista que es
>> para dar soporte, más no (creo yo) para mantener un índice de empresas de
>> "renombre" que usan PostgreSQL.
>
> Disiento totalmente con las dos respuestas ofrecidas a la pregunta
> planteada... Parece que nos cayera mal que se evalúe nuestro SGBD
> preferido (o al menos el mio) por las empresas que lo utilizan y no
> entiendo porque realmente...
>
> Es una simple opinion sin animo de ofender a los compañeros que
> contestaron anteriormente eh! :p
>
> La verdad es que realmente no estoy al tanto de las empresas que lo
> utilizan porque no es algo que me interese pero no por eso desacredito
> la pregunta...
> Lo que si te puedo decir es que acá en Argentina es muy usado a nivel
> estatal por su carácter de libre, licencia BSD y por las prestaciones
> que brinda...
>
>>>
>>>
>>>
>>> muchas gracias
>>
>
> De nada...
>
> Saludos,
>
> Juan
> --
> TIP 9: visita nuestro canal de IRC #postgresql-es en irc.freenode.net
>

________ Information from NOD32 ________
This message was checked by NOD32 Antivirus System for Linux Mail Server.
http://www.nod32.com
--
TIP 6: ¿Has buscado en los archivos de nuestra lista de correo?

http://archives.postgresql.org/pgsql-es-ayuda

Re: [pgsql-es-ayuda] Copia y restauracion de base de datos de GForge

El módulo de tsearch2, viene en un paquete de centos llamado postgresql-contrib, y lo he encontrado instalado en:
/usr/share/pgsql/contrib/tsearch2.sql
He estado mirando un manual acerca de la instalación de tsearch2 en postgresql, y he encontrado este: http://denisjtorresg.blogspot.com/2008/04/instalar-tsearsh2-en-postgresql-con.html
Tendría que insertar las tablas y datos del fichero /usr/share/pgsql/contrib/tsearch2.sql en la base de datos, antes de insertar todos los datos..., sería algo así (en mi caso):
psql -d $BBDD -f  /usr/share/pgsql/contrib/tsearch2.sql
Pero como todo no podía ser tan bonito, si inserto esas tablas y esos datos antes de restaurar la base de datos, cuando la restauro, lo primero que hace es borrar con un drop todos los datos, para dejar la bbdd vacía, así que también vacía los datos insertados de /usr/share/pgsql/contrib/tsearch2.sql.
Como necesito borrar todos los datos existentes anteriormente, he pensado en borrar la base de datos, crearla nueva, y sobre ahí hacer la restauración de la base de datos creada con el pg_dump, sin el -c...

dropdb -U $USERNAME $DBNAME
createdb -T template0 -U $USERNAME $DBNAME

Pero anteriormente cuando contemplé esa opción, leí que era recomendable que se usara la opción del -c al hacer la copia de la base de datos, pero por lo que me parece, no puedo hacerlo si necesito insertar también tsearch2...

Voy más perdido que un hijo ... en el día del padre... :S

Nunca me había dado tantos problemas la copia de una BBDD, también es cierto que nunca era tan compleja y tan poco documentada...

El día 17 de junio de 2008 19:05, Alvaro Herrera <alvherre@commandprompt.com> escribió:

Clemente López Giner escribió:

> psql:/backup/diarias/GForge/backup_17-06-2008/bbdd_GForge_17-06-2008.sql:590:
> ERROR: no existe el tipo «tsvector»

Instala el módulo contrib/tsearch2 en la base de datos antes de
restaurar el respaldo.

Con respecto a pg_dump -c, creo que es mala idea usarlo.  Mejor hacer el
dump sin el -c, y luego darle pg_restore -c pero sólo si los objetos ya
existen.  Si la base de datos está nueva, entonces no tiene sentido usar
-c porque obviamente los objetos no existen.

--

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

Re: [GENERAL] Controlling write access to a table

On Wed, Jun 18, 2008 at 1:11 PM, David <wizzardx@gmail.com> wrote:
> Hi Dave.
>
> Did you intentionally mail me off-list? On-list is generally better so
> other people can give suggestions.
No, sorry.

I've just joined the list and am a little unused to it's mechanics.

I appreciate that any solution offered (and any debate thereon) is
important and adds to the knowledgebase.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Database design questions

On Wed, Jun 18, 2008 at 12:25 PM, Jorge Godoy <jgodoy@gmail.com> wrote:
> On Wednesday 18 June 2008 05:43:25 David wrote:
>> * Should I split this into separate threads instead of 1 thread for
>> all my questions?
>
> I would submit all of the questions in separate messages. It is tiresome to
> read everything, you'll loose a lot of context after one or two messages
> levels or reply and people won't read the email because of its size.
>

Thanks for your reply.

Should I post a 'please disregard this thread' message to this thread,
and start some new threads instead?

David.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Controlling write access to a table

Hi Dave.

Did you intentionally mail me off-list? On-list is generally better so
other people can give suggestions.

On Wed, Jun 18, 2008 at 12:04 PM, Dave Coventry <dgcoventry@gmail.com> wrote:
> On Wed, Jun 18, 2008 at 11:33 AM, David <wizzardx@gmail.com> wrote:
>> How about setting up separate database users/groups (aka roles in
>> newer postgresql versions), and only granting update permissions to
>> the users/groups who should have it?
>>
>> David.
>
> Thanks, David.
>
> So would you advise a separate database?

Only if you need separate databases for a good reason (simple
permissions isn't a good reason). Splitting into other databases will
make existing apps more complicated (they need to connect to 2
databases instead of 1, etc).

>
> I have just found a reference work that suggests using
> views....http://www.archonet.com/pgdocs/chap-access.html
>

Views are good, if you want more fine-grained control over what data
users can view in the database.

Your request was for a way to limit what users can update. I assume
that in your case, if users are allowed to SELECT from a table, that
they are meant to be able to view all columns. If not, then views may
be a good idea.

> My understanding is that Postgres doesn't support pulling in data from
> multiple databases.

Not directly, but you there are automated data replication systems for
Postgresql if you need them. I don't think this is applicable in your
case.

>
> I'm a noob, so I could easily be wrong...
>

I assume that all users are currently logging in as the admin database
user,'postgres'? I also assume that you don't have full control
(ability to update source code, etc) over the software which users use
to manipulate the database. So students could update marks if you
don't change the postgresql permissions correctly.

You should do something like this:

1) Create new users, eg:

CREATE USER student WITH PASSWORD '<password here>';
CREATE USER teacher WITH PASSWORD '<another password>';

These users are by default locked down, and can't SELECT, UPDATE, etc
on any tables.

2) Grant permissions, eg:

For tables where all users are allowed to do anything:

GRANT ALL ON sandbox TO teacher, student;

For tables where teachers can do everything, but where students can select:

GRANT ALL ON marks TO teacher;
GRANT SELECT ON marks to student;

Whether students should be able to view marks of other students is a
separate issue.

3) Update pg_hba.conf so the new users can login over the network. eg,
add lines like this:

host student your_db_name <subnet> <netmask> md5
host teacher your_db_name <subnet> <netmask> md5

eg subnet: 192.168.0.0
eg netmask: 255.255.255.0

If you know that students and teachers will be connecting from
different IP ranges, then you should update the networking details
appropriately.

4) Clients use the new accounts instead of postgres.

eg: Update frontend configuration

eg: Tell users what their new logins are.

5) Lock down the postgresql account

eg: Edit your pg_hba.conf file, and make sure you have a line like this:

# Database administrative login by UNIX sockets
local all postgres ident sameuser

The above line means that to login as the postgres admin user, you
need to first login to the server (eg: with Putty), then change to the
postgres system user (eg: 'su postgres' under Linux) before you can
login to the database as postgres user (eg: 'psql your_database').

Also make sure that all network logins for postgres user are commented
out. Or, if you still need to login to the database over the network
then change the postgres password.

6) Restart the postgresql service, and make sure that everything still
works for the users

Also make sure that users can no longer login as the postgres user.

You will probably need to tweak some configuration and run a few more
GRANT lines. See your postgresql log files for more info.

I haven't tested the above steps, but they should work.

David.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [HACKERS] Reducing overhead for repeat de-TOASTing

On Mon, 2008-06-16 at 15:35 -0400, Tom Lane wrote:
> Recent discussions with the PostGIS hackers led me to think about ways
> to reduce overhead when the same TOAST value is repeatedly detoasted.
> In the example shown here
> http://archives.postgresql.org/pgsql-hackers/2008-06/msg00384.php
> 90% of the runtime is being consumed by repeated detoastings of a single
> datum. That is certainly an outlier case, but we've heard complaints of
> TOAST being slow before. The solution I'm about to propose should fix
> this, and as a bonus it will reduce the problem of memory leakage when
> a detoasted value doesn't get pfreed.
>
> What I am imagining is that the tuple toaster will maintain a cache of
> recently-detoasted values...

> Comments, better ideas? Anyone think this is too much trouble to take
> for the problem?

You've not covered the idea that we just alter the execution so we just
detoast once. If we tried harder to reduce the number of detoastings
then we would benefit all of the cases you mention, including internal
decompression. We would use memory, yes, but then so would a cache of
recently detoasted values.

If we see that the index scan key is toastable/ed then the lowest levels
of the plan can create an expanded copy of the tuple and pass that
upwards. We may need to do this in a longer lived context and explicitly
free previous tuples to avoid memory bloat, but we'd have the same
memory usage and same memory freeing issues as with caching. It just
seems more direct and more obvious, especially since it is just an
internal version of the workaround, which was to create a function to
perform early detoasting. Maybe this could be done inside the IndexScan
node when a tuple arrives with toasted values(s) for the scan key
attribute(s).

I presume there's various reasons why you've ruled that out, but with
such a complex proposal it seems worth revisiting the alternatives, even
if just to document them for the archives.

--
Simon Riggs

www.2ndQuadrant.com

PostgreSQL Training, Services and Support

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Overhauling GUCS

Greg Smith wrote:
> On Thu, 5 Jun 2008, Magnus Hagander wrote:
>
>> We really need a "proper API" for it, and the stuff in pgAdmin isn't
>> even enough to base one on.
>
> I would be curious to hear your opinion on whether the GUC overhaul
> discussed in this thread is a useful precursor to building such a proper
> API.

I'm getting back to this one now that the discussion has died down a bit.

As usual, the discussion spread out into these huge changes needed to be
done to satisfy everyone from day one. I don't think that's the way to
go - we have to do it piece by piece if that's ever going to be done.
IMHO the first thing to do is to create a stable API for modifying
config variables remotely. *at this time* it doesn't matter how this API
modifies the configuration, if it's in a file on in the db or whatever.
We can always change that later... Which is why I will be proceeding
with this one - make an API that requires the least possible change for
now, per discussions earlier this year (not in this thread) :-)

Now, this in itself is unrelated to the stuff Josh was talking about
which is collecting the information in one place and being able to
generate config files that don't contain everything, and being able to
auto-tune things. It would be nice for such a tool to be able to get the
full descriptions and such from the pg_settings view or such, which
means it needs to go in the code and not in a standalone file, but it's
not *as necessary*.

I still think the config file we ship today is horrible, I get lots of
questions around it, and I see lots of examples of people who tweak
settings they have no idea what they do, just because it's there. That's
a separate issue that could *either* be solved by shipping more than
one default config file, or it could be solved by the config file
generator Josh proposed.

//Magnus

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [PERFORM] Tsearch2 Initial Search Speed

>
> Actually, the index returns page numbers in the table on disc which
> may contain one or more rows that are relevant. Postgres has to fetch
> the whole row to find out the email_id and any other information,
> including whether the row is visible in your current transaction
> (concurrency control complicates it all). Just having a page number
> isn't much use to you!
>
> Matthew
>
Out of interest, if I could create a multicolumn index with both the
primary key and the fts key (I don't think I can create a multi-column
index using GIST with both the email_id and the fts field), would this
reduce access to the table due to the primary key being part of the index?

More importantly, are there other ways that I can improve performance on
this? I am guessing that a lot of the problem is that the email table is
so big. If I cut out some of the text fields that are not needed in the
search and put them in another table, presumably the size of the table
will be reduced to a point where it will reduce the number of disk hits
and speed the query up.

So I could split the table into two parts:

create table email_part2 (
email_id int8 references email_part1 (email_id),
fts ...,
email_directory_id ...,
)

create table email_part1(
email_id serial8 primary key,
cc text,
bcc text,
...
)

and the query will be
select email_id from email_part2 where to_tsquery('default', 'howard')
@@ fts;

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [GENERAL] Database design questions

On Wednesday 18 June 2008 05:43:25 David wrote:
> Hi list.
>
> There are some database design-related issues I've pondered about for some
> time.
>
> But first:
>
> * Is this the correct list to ask these questions on?
>
> * Should I split this into separate threads instead of 1 thread for
> all my questions?

I would submit all of the questions in separate messages. It is tiresome to
read everything, you'll loose a lot of context after one or two messages
levels or reply and people won't read the email because of its size.

Regards,
--
Jorge Godoy <jgodoy@gmail.com>

Re: [pgeu-general] Linux Live 2008 - UK

On Wed, Jun 18, 2008 at 11:17 AM, Richard Huxton <dev@archonet.com> wrote:
> Simon Riggs wrote:
>>
>> On Tue, 2008-06-17 at 14:29 +0100, Dave Page wrote:
>>>
>>> We have a space in the .Org Village at Linux Live which is being held
>>> on October 23 - 25 at Olympia in London.
>
> I'll put it in my diary too.

Thanks guys. Mark?? :-p

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

--
Sent via pgeu-general mailing list (pgeu-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgeu-general

Re: [pgeu-general] Linux Live 2008 - UK

Simon Riggs wrote:
> On Tue, 2008-06-17 at 14:29 +0100, Dave Page wrote:
>> We have a space in the .Org Village at Linux Live which is being held
>> on October 23 - 25 at Olympia in London.

I'll put it in my diary too.

--
Richard Huxton
Archonet Ltd

--
Sent via pgeu-general mailing list (pgeu-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgeu-general

Re: [GENERAL] problem with to_ascii() function in version 8.3.3

Pavel Arnošt wrote:
>>> insert into chartest (c) values ('á');
>>> select to_ascii(encode(convert_to(c,'LATIN9'),'escape'),'LATIN9') from chartest;
>>> to_ascii
>>> ----------
>>> \341
>>
>> What answer do you get to the following two SQL statements:
>>
>> SHOW server_encoding;
>> SHOW client_encoding;
>
> both commands shows "UTF8". Changing client encoding with "\encoding
> LATIN9" does not have any effect on to_ascii command (I don't know if
> it should have).

Strange; I have the same settings and it works here.

Could you run the following queries and compare with my results:

test=> select ascii(c) from chartest;
ascii
-------
225
(1 row)

test=> select encode(convert_to(c,'LATIN9'),'hex') from chartest;
encode
--------
e1
(1 row)

test=> select ascii(to_ascii(encode(convert_to(c,'LATIN9'),'escape'),'LATIN9')) from chartest;
ascii
-------
97
(1 row)

Yours,
Laurenz Albe

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Controlling write access to a table

Dave Coventry wrote:
> Hi.
>
> I have a database with all of the particulars of our students and I am
> adding a table that will contain all of the courses and the grades
> attained by the students.

[snip]

> The marks (or grades) of the students are a different matter and we
> want to restrict changes to this data to a very few people.
>
> I'm presuming that this is not a unique problem, but I have been
> unable to find anything helpful on google.

If your different classes of users log in with different usernames and
passwords to the database, then you can do this rather simply with roles
and table priveleges. See the GRANT and REVOKE statements.

http://www.postgresql.org/docs/current/static/sql-grant.html
http://www.postgresql.org/docs/current/static/sql-revoke.html

and ROLE management:

http://www.postgresql.org/docs/current/static/sql-createrole.html

If your users all log in with the same username and password via some
kind of web app, persistence layer, or whatever, then you will have to
write the access control yourself at the application level.

--
Craig Ringer

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Controlling write access to a table

On Wed, Jun 18, 2008 at 11:04 AM, Dave Coventry <dgcoventry@gmail.com> wrote:

[...]

> The marks (or grades) of the students are a different matter and we
> want to restrict changes to this data to a very few people.
>

How about setting up separate database users/groups (aka roles in
newer postgresql versions), and only granting update permissions to
the users/groups who should have it?

David.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[HACKERS] Postgres + Window manager

Good day,

Progress in office technologies stopped, and it has occurred because user
himself can not map arbitrary 3-dimensional composition _into_ a window.
In the same time, it's possible to keep composition in Postgres, to extract by
operator 'select', and to transfer got binary data without transformation into
Window manager.
Thus user will avoid OpenGL and DirectX, which he never master.

I stated details of proposal on page 2-13, 67-75 of pdf-document
http://sql50.euro.ru/sql5.16.4.pdf , and i ask to implement it.
All my proposals are public domain.

P.S.
XML is used only to clarify, what's happened in proprietary format,
used for communication between Postgres and its client.

Dmitry Turin
SQL5 (5.16.4)

http://sql50.euro.ru/sql5.16.4.pdf
HTML6 (6. 8.3)

http://html60.euro.ru
Unicode7 (7. 3.2)

http://unicode70.euro.ru
Computer2 (2. 7.0)

http://computer20.euro.ru

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [GENERAL] Error when trying to drop a tablespace

Cyril SCETBON wrote:
>>>>>>> I get the following error :
>>>>>>>
>>>>>>> postgres=# DROP TABLESPACE IF EXISTS my_tbs;
>>>>>>> ERROR: tablespace "my_tbs" is not empty
>>>>>>>
>>>>>>> I've searched in pg_class and I'm not able to find a relation which
>>>>>>> refers to my_tbs with :
>>>>
>>>> Find out the directory:
>>>>
>>>> SELECT oid, spclocation FROM pg_catalog.pg_tablespace WHERE spcname = 'my_tbs';
>>>>
>>>> is there anything in this directory?
>>>
>>> cd spclocation
>>>
>>> find .
>>> .
>>> ./100456
>>> ./100456/100738
>>> ./100456/102333
>>> ./100456/103442
>> [...]
>>
>> A tablespace does not belong to a specific database, so it could be that
>> these objects belong to another database that also uses this tablespace.
>>
>> Try to look for objects that depend on the tablespace in other databases.
>> I also forgot pg_shdepend which contains relationships to shared objects
>> such as roles - look there too.
>
> VALUES=`find /path/100456/ -type f -exec basename {} \;|xargs|sed -e 's/ /,/g'`
> for db in `/usr/lib/postgresql/8.2/bin/oid2name|grep -v template|tail -n
> +4|awk '{print $2}'`; do psql $db -c "select count(*) from pg_shdepend
> where objid in ($VALUES) or refobjid in ($VALUES)"; done
>
> nothing :-(

Did you also look in pg_depend in all the other databases?
You could run the following in all databases:

SELECT t.relname AS catalog, d.objid AS oid_dependent
FROM pg_catalog.pg_class t JOIN
pg_catalog.pg_depend d ON (t.oid = d.classid)
WHERE refobjid = 100456;

You could also try the following in all databases:

SELECT oid, relname, relkind FROM pg_catalog.pg_class
WHERE oid IN (100738, 102333, 103442);

(hoping that some of the objects are tables or views or sequences).

Yours,
Laurenz Albe

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [pgsql-www] Software catalog section improvements

On Wed, Jun 18, 2008 at 12:29 AM, Greg Sabino Mullane <greg@turnstep.com> wrote:
>
>> Most people will simply email webmaster@ no? We don't list a contact
>> address on any other forms on the site that I can see.
>
> I think there should be an email on all the form pages, or at the
> last on a clearly marked contact page.

Patches welcome :-) (well, it worked once, you can't blame a guy for
trying again!)

> I'll take "Patches" for $100, Alex.

Hah - bet you feel all dirty after playing in PHP!

Thanks, patch applied.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

--
Sent via pgsql-www mailing list (pgsql-www@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-www

[GENERAL] Controlling write access to a table

Hi.

I have a database with all of the particulars of our students and I am
adding a table that will contain all of the courses and the grades
attained by the students.

All this information is to be read by just about everybody, and the
bulk of the data will be written by several clerks, and, while we
wouldn't like just anybody altering these, we don't need terribly
stringent write security.

The marks (or grades) of the students are a different matter and we
want to restrict changes to this data to a very few people.

I'm presuming that this is not a unique problem, but I have been
unable to find anything helpful on google.

Can anyone assist me?

Would it be preferable to create a separate database for this information?

Regards,

Dave Coventry

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [pgadmin-hackers] Enabling SQL text field in the SQL tab of object dialog

On Tue, Jun 17, 2008 at 10:51 PM, Guillaume Lelarge
<guillaume@lelarge.info> wrote:
> Dave Page a écrit :
>>
>> When you open a properties dialogue, it gets passed a pointer to the
>> pgObject which it may use right up until it is closed. If you refresh
>> part of the tree, you delete and recreate all the pgObjects under the
>> node you refresh, so the dialogue can end up with a pointer to an
>> object that's been deleted.
>>
>
> OK, but this is already an issue.

Right, but my point is that this is going to make it more likely to occur.

I wonder if we need some reference counting on objects in the tree,
and only allow any kind of refresh if all child nodes have a ref count
of zero. I'm not entirely sure how we'd implement that.

> Here is patch revision 2. It creates a new textfield for the second SQL
> query, and it takes care of the refresh of the tree. This patch seems to
> resolve all issues I could find (apart from the one above).
>
> Comments?

- Can we only have the dual textboxes on the dialogues that actually
need them please?

- The 'do you want to lose your changes' prompt should only be shown
if changes have actually been made.

- There is a drawing artifact on Mac. I may have to look at that after
you commit if you have no access to suitable hardware (maybe you can
get JPA to spring for a Mac Mini - they're nice and cheap :-p )

Cheers, Dave.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

--
Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers

[GENERAL] Database design questions

Hi list.

There are some database design-related issues I've pondered about for some time.

But first:

* Is this the correct list to ask these questions on?

* Should I split this into separate threads instead of 1 thread for
all my questions?

Assuming there isn't a problem, here are my questions:

==========

Question 1: Storing app defaults.

If you have a table like this:

table1
- id
- field1
- field2
- field3

table2
- id
- table1_id
- field1
- field2
- field3

table1 & table2 are setup as 1-to-many.

If I want to start providing user-customizable defaults to the
database (ie, we don't want apps to update database schema), is it ok
database design to add a table2 record, with a NULL table1_id field?

In other words, if table1 has no matching table2 record, then the app
will use the table2 record with a NULL table1_id field to get
defaults.

This looks messy however. Is there a better way to do it?

A few other ways I can think of:

1) Have an extra table1 record (with string fields containing
'DEFAULT'), against which the extra table2 record is linked.

2) Have a new table, just for defaults, like this:

table2_defaults
- field1
- field2
- field3

Which is the cleanest way? Is there another method I should use instead?

==========

Question 2: Backwards-compatible field addition

If you have an existing table, and apps which use it, then how do you
add new fields to the table (for new apps), but which might affect
existing apps negatively?

eg: I start with a table like this:

table1
- id
- field1
- field2
- field3

Later, I want to add a use case, where there is new behaviour, if a
new field is set in the table, like this:

table1
- id
- field1
- field2
- field3
- field4 - NEW - if unset, do old behaviour. if set, do something else

The problem is, that existing apps (besides your new app) won't know
about field4, so they will keep using the old behaviour for new
records (where field4 is set), which you don't want.

The most obvious thing to do is to update all apps using table1, so
they also check the value of field4.

Is there another, more backwards-compatible way to add field4 for the
new behaviour, without having to update all the apps?

A few things I can think of:

1) table1 becomes a view of an updated table, with a 'WHERE field4 IS
NULL' clause.

Problem with this is that some RDBMS (Postgresql specifically) don't
let you run update statements on views.

2) Apps use stored procedures for all database access.

Maybe ok for new apps, not so much for existing apps which use regular SQL.

3) All apps use the same library for accessing database

Then you update the library and all apps automagically know about the
extra field. Again, maybe ok for new apps, not so much for existing
apps.

4) Make a new table (copy of the old one), with the extra field.

Then your app checks both tables, or just the new one if applicable.

This can work, but you may end up with a lot of app-specific tables,
where the main difference between the tables is extra columns, and
which apps use the tables.

5) Have a 'db version' column in the table. Older apps only operate on
records at or before the version the programmer knew about at the
time.

This can work, but it seems like a very non-standard, hackish way of
designing database tables. Also it's a pain for all apps to have to
hardcode a db version number.

6) Find a clever way to use table inheritance

I haven't thought it through, but here are some docs I've read on the subject:

http://www.postgresql.org/docs/8.1/static/ddl-inherit.html

Any other ideas?

==========

Question 3: Temporal databases

http://en.wikipedia.org/wiki/Temporal_database

I haven't used them before, but I like the idea of never
deleting/updating records so you have a complete history (a bit like
source code version control).

How well do temporal databases work? Do RDBMS (ie Postgresql) need
add-ons to make it effective, or can you just add extra temporal
columns to all your tables and add them to your app queries? Does this
increase app complexity and increase server load a lot?

Are there Python libraries which simplify this? (eg: add-ons for
Elixir or SQLAlchemy).

Or should apps all implement their own 'temporal data access' module,
which transparently uses the current date & time until queried for
historical data?

==========

Question 4: Data synchronization

2 cases I'm interested in:

1) Migrating data from one database to another

2) Distributing data over many databases, and later merging

In what ways can you design tables to easier facilitate the above cases?

I am aware of multi-master replication software, as described here:

http://en.wikipedia.org/wiki/Multi-master_replication

For this question, I'm more interested in schema design, so that a
home-brewed database synchronization can perform synchronization.

I have some experience with this. One of my previous projects was to
reliably migrate data from one database to another, where the 2
databases had problems like:

- Many foreign keys weren't enforced

- Some fields needed special treatment (eg: should be unique, or
behave like a foreign key ref, even if db schema doesn't specify it.
In other cases they need to be updated during the migration).

- Most auto-incrementing primary keys (and related foreign key
references) needed to be updated during migration, because they are
already used in the destination database for other records.

- Many tables are undocumented, some fields have an unknown purpose

- Some tables didn't have fields that can be used as a 'natural' key
for the purpose of migration (eg: tables which only exist to link
together other tables, or tables where there are duplicate records).

I wrote a Python script (using SQLAlchemy and Elixir) to do the above
for our databases.

Are there any existing migration tools which could have helped with
the above? (it would have required a *lot* of user help).

Are there recommended ways of designing tables so that synchronization
is easier?

The main thing I've read about is ensuring that all records have a
natural key of some kind, eg GUID. Also, your migration app needs to
have rules for conflict resolution.

==========

Thanks in advance for any suggestions :-)

David.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Clustering with minimal locking

On Wed, Jun 18, 2008 at 9:26 AM, Decibel! <decibel@decibel.org> wrote:

On Jun 17, 2008, at 11:37 AM, Scott Ribe wrote:

BOOM! Deadlock.

No more likely than with the current cluster command. Acquiring the lock is
the same risk; but it is held for much less time.

Actually, no (at least in 8.2). CLUSTER grabs an exclusive lock before it does any work meaning that it can't deadlock by itself. Of course you could always do something like

BEGIN;
SELECT * FROM a;
CLUSTER .. ON a;
COMMIT;

Which does introduce the risk of a deadlock

Really!!? Am I missing something? How can a single transaction, running synchronous commands, deadlock itself!

Best regards,

--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Re: [GENERAL] Error when trying to drop a tablespace

Albe Laurenz wrote:
> Cyril SCETBON wrote:
>
>>>>>> I get the following error :
>>>>>>
>>>>>> postgres=# DROP TABLESPACE IF EXISTS my_tbs;
>>>>>> ERROR: tablespace "my_tbs" is not empty
>>>>>>
>>>>>> I've searched in pg_class and I'm not able to find a relation which
>>>>>> refers to my_tbs with :
>>>>>>
>>>>> You can find the dependent objects with:
>>>>>
>>>>> SELECT t.relname AS catalog, d.objid AS oid_dependent
>>>>> FROM pg_catalog.pg_class t JOIN
>>>>> pg_catalog.pg_depend d ON (t.oid = d.classid)
>>>>> WHERE refobjid = 100456;
>>>>>
>>>>>
>>>> postgres=# SELECT t.relname AS catalog, d.objid AS oid_dependent
>>>> postgres-# FROM pg_catalog.pg_class t JOIN
>>>> postgres-# pg_catalog.pg_depend d ON (t.oid = d.classid)
>>>> postgres-# WHERE refobjid = 100456;
>>>> catalog | oid_dependent
>>>> ---------+---------------
>>>> (0 rows)
>>>>
>>>> nothing...
>>>>
>>> Find out the directory:
>>>
>>> SELECT oid, spclocation FROM pg_catalog.pg_tablespace WHERE spcname = 'my_tbs';
>>>
>>> is there anything in this directory?
>>>
>> cd spclocation
>>
>> find .
>> .
>> ./100456
>> ./100456/100738
>> ./100456/102333
>> ./100456/103442
>>
> [...]
>
> Good! that's a starting point!
>
> A tablespace does not belong to a specific database, so it could be that
> these objects belong to another database that also uses this tablespace.
>
> Try to look for objects that depend on the tablespace in other databases.
> I also forgot pg_shdepend which contains relationships to shared objects
> such as roles - look there too.
>
> Can you find tables or other objects with OID 100738, 102333 etc. in this
> or other databases?
>
VALUES=`find /path/100456/ -type f -exec basename {} \;|xargs|sed -e 's/
/,/g'`
for db in `/usr/lib/postgresql/8.2/bin/oid2name|grep -v template|tail -n
+4|awk '{print $2}'`; do psql $db -c "select count(*) from pg_shdepend
where objid in ($VALUES) or refobjid in ($VALUES)"; done

count
-------
0
(1 row)

count
-------
0
(1 row)

count
-------
0
(1 row)

nothing :-(

> Yours,
> Laurenz Albe
>

--
Cyril SCETBON - Ingénieur bases de données
AUSY pour France Télécom - OPF/PORTAILS/DOP/HEBEX

Tél : +33 (0)4 97 12 87 60
Jabber : cscetbon@jabber.org
France Telecom - Orange
790 Avenue du Docteur Maurice Donat
Bâtiment Marco Polo C2 - Bureau 202
06250 Mougins
France

***********************************
Ce message et toutes les pieces jointes (ci-apres le 'message') sont
confidentiels et etablis a l'intention exclusive de ses destinataires.
Toute utilisation ou diffusion non autorisee est interdite.
Tout message electronique est susceptible d'alteration. Le Groupe France
Telecom decline toute responsabilite au titre de ce message s'il a ete
altere, deforme ou falsifie.
Si vous n'etes pas destinataire de ce message, merci de le detruire
immediatement et d'avertir l'expediteur.
***********************************
This message and any attachments (the 'message') are confidential and
intended solely for the addressees.
Any unauthorised use or dissemination is prohibited.
Messages are susceptible to alteration. France Telecom Group shall not be
liable for the message if altered, changed or falsified.
If you are not recipient of this message, please cancel it immediately and
inform the sender.
************************************

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [SQL] order by when using cursors

Pavel Stehule wrote:
>> it's known problem - column and variable names collision, so when you
>> use any SQL statement inside procedure you have to be carefully about
>> using variable names.

Oh, I didn't took notice of that.

Now knowing it is not a bug and how it works, it makes things much easier!:

Thank you!

Patrick

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [JDBC] insert data into partitioned tables via Java application

cap20 wrote:
> I am trying to do an insert data into EDB partitioned tables via front end
> application (Java) but the application failing to do an insert. The problem
> I identified is since the insert happening into partitioned tables in the
> background when you do an insert into base table, so the insert returning
> returning Zero rows affected message back to application. So it is failing,
> is there any work-around for this.

This is somewhat of a frequently asked question, and should probably go
into the published FAQ. See, for example, the recent thread
"Partitioning: INSERT 0 0 but want INSERT 0 1".

As far as I can tell there's no way to alter Pg's behaviour so it
returns the number of rows inserted. This is because there really were
zero rows inserted into the master table; the rows were inserted into
other tables instead.

It'd be nice to be able to mark a rule so that it added the number of
rows affected to the total returned by the query, but this is AFAIK not
currently possible.

Your app needs to either insert into the real table rather than the
partition master table or it needs to ignore the number of rows
affected. If you're using a Java persistence layer it probably offers
the latter as a configuration option. Alternately, as described by Neil
Peter Braggio just recently on one of the Pg lists:

Neil Peter Braggio wrote:
> I have the same problem in PG 8.2
>
> To resolve this issue I had to create a new table with the same
> structure than the partitioned table with a trigger for insert and
> update. All the operations the application have to do are directed to
> this new table.
>
> When a new record is inserted in the new table the trigger insert a
> new record with the same values into the partitioned table and then
> delete all records from this new table. In updates operations the
> trigger redirect the operation to the partitioned table too.
>
> With this _not elegant_ solution our Java application is able to do its job.

--
Craig Ringer

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

Re: [GENERAL] Error when trying to drop a tablespace

Cyril SCETBON wrote:
>>>>> I get the following error :
>>>>>
>>>>> postgres=# DROP TABLESPACE IF EXISTS my_tbs;
>>>>> ERROR: tablespace "my_tbs" is not empty
>>>>>
>>>>> I've searched in pg_class and I'm not able to find a relation which
>>>>> refers to my_tbs with :
>>>>
>>>> You can find the dependent objects with:
>>>>
>>>> SELECT t.relname AS catalog, d.objid AS oid_dependent
>>>> FROM pg_catalog.pg_class t JOIN
>>>> pg_catalog.pg_depend d ON (t.oid = d.classid)
>>>> WHERE refobjid = 100456;
>>>>
>>> postgres=# SELECT t.relname AS catalog, d.objid AS oid_dependent
>>> postgres-# FROM pg_catalog.pg_class t JOIN
>>> postgres-# pg_catalog.pg_depend d ON (t.oid = d.classid)
>>> postgres-# WHERE refobjid = 100456;
>>> catalog | oid_dependent
>>> ---------+---------------
>>> (0 rows)
>>>
>>> nothing...
>>
>> Find out the directory:
>>
>> SELECT oid, spclocation FROM pg_catalog.pg_tablespace WHERE spcname = 'my_tbs';
>>
>> is there anything in this directory?
>
> cd spclocation
>
> find .
> .
> ./100456
> ./100456/100738
> ./100456/102333
> ./100456/103442
[...]

Good! that's a starting point!

A tablespace does not belong to a specific database, so it could be that
these objects belong to another database that also uses this tablespace.

Try to look for objects that depend on the tablespace in other databases.
I also forgot pg_shdepend which contains relationships to shared objects
such as roles - look there too.

Can you find tables or other objects with OID 100738, 102333 etc. in this
or other databases?

Yours,
Laurenz Albe

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [HACKERS] Cleaning up cross-type arithmetic operators

On Tue, 2008-06-17 at 13:29 -0400, Tom Lane wrote:

> What I'm inclined to do is remove the two % operators, which don't
> seem likely to be performance-critical

Can you discuss what you see as the benefit or trade-offs for doing
that? Removing things tends to have major potential for annoying users
following an upgrade.

We can put them back manually if they are performance critical, right?
Can we document this somehow/somewhere? Which operators were not thought
to be generally important? Thanks.

--
Simon Riggs

www.2ndQuadrant.com

PostgreSQL Training, Services and Support

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers