Re: [GENERAL] storing postgres data on dropbox

[Bruno Wolff III]

On Sun, Jun 18, 2017 at 13:16:16 +,
 Martin Mueller  wrote:

Why not a PostgreSQL-database somewhere in the cloud? Good question, but it's a question 
of money and performance. I used MySQL for many years and then moved a dataset to an 
instance on AWS. The performance was horribly slow. Then some kind soul at my institution 
hooked me up with "Aurora," which I take to be MySQL on steroids. That was 
great, and the performance was almost as good as on my desktopc. But it cost hundreds of 
dollars per month. I work at home with a machine that has 32 GB of memory. In order to 
get comparable performance from a cloud-based Postgres instance, I'd have to spend a lot 
of money that I don't have. Dropbox costs $120 a year for a terabyte of storage, which is 
very affordable.


You aren't going to be able to use copies of the raw files taken while the 
database is running, to restore the database. Storing compressed output 
from pg_dumpall is probably the best way to create backups you can restore 
from.



--
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] JSON to INT[] or other custom type

[Bruno Wolff III]

On Sun, Jun 11, 2017 at 22:35:14 +0100,
 Rory Campbell-Lange  wrote:


I'm hoping, in the plpgsql function, to unfurl the supplied json into a
custom type or at least an array of ints, and I can't work out how to do
that.

   select * from json_array_elements_text('[[0, 1], [1, 2]]');
value
   
[0, 1]
[1, 2]
   (2 rows)

works fine, but I can't seem to turn those values into actual ints or
anything else for that matter, apart from text via the
json_array_elements_text() function.


Does this example help?

area=> select (a->>0)::int, (a->>1)::int from json_array_elements('[[0, 1], [1, 
2]]') as s(a);
int4 | int4 
--+--

   0 |1
   1 |2
(2 rows)



--
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] Limiting DB access by role after initial connection?

[Bruno Wolff III]

On Fri, Jun 09, 2017 at 21:14:15 -0700,
 Ken Tanzer <ken.tan...@gmail.com> wrote:

On Fri, Jun 9, 2017 at 5:38 PM, Bruno Wolff III <br...@wolff.to> wrote:

Seems to me they are separate issues.   App currently has access to the
password for accessing the DB.  (Though I could change that to ident access
and skip the password.)  App 1) connects to the DB, 2) authenticates the
user (within the app), then 3) proceeds to process input, query the DB,
produce output.  If step 2A becomes irrevocably changing to a site-specific
role, then at least I know that everything that happens within 3 can't
cross the limitations of per-site access.  If someone can steal my password
or break into my backend, that's a whole separate problem that already
exists both now and in this new scenario.


In situations where a person has enough access to the app (e.g. it is a 
binary running on their desktop) to do spurious role changes, they likely 
have enough acces to hijack the database connection before privileges 
are dropped.



--
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] Limiting DB access by role after initial connection?

[Bruno Wolff III]

On Thu, Jun 08, 2017 at 22:37:34 -0700,
 Ken Tanzer  wrote:


My approach was to have the initial connection made by the owner, and then
after successfully authenticating the user, to switch to the role of the
site they belong to.  After investigation, this still seems feasible but
imperfect.  Specifically, I thought it would be possible to configure such
that after changing to a more restricted role, it would not be possible to
change back.  But after seeing this thread (


How are you keeping the credentials of the owner from being compromised? It 
seems if you are worried about role changing, adversaries will likely also 
be in a position to steal the owner's credentials or hijack the connection 
before privileges are dropped.



--
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] [OT] Help: stories of database security and privacy

[Bruno Wolff III]

On Tue, Apr 11, 2017 at 21:48:58 +0200,
 Lifepillar  wrote:


I'd like to take the opportunity to also engage students about the topic
of privacy (or lack thereof). So, I am here to ask if you have
interesting/(in)famous stories to share on database security/privacy
"gone wrong" or "done right"(tm), possibly with technical details (not
necessarily to share with the students, but for me to understand the
problems). I am asking to this list because I will use PostgreSQL, so
maybe I can collect ideas that I can implement or demonstrate in
practice.


"Translucent Databases" has some interesting ideas about providing privacy 
by operating directly on encrypted data (without decrypting it) so that 
information is kept private even from the database. The are major 
limitations on what you can do, but there may be some cases where the 
techniques can be used.



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

[GENERAL] Beta rpms for Fedora are missing (at this time)

[Bruno Wolff III]

This is probably a temporary build problem, but I thought mentioning 
here might get it fixed faster in case it hasn't already been noticed. 
https://download.postgresql.org/pub/repos/yum/testing/10/fedora/fedora-25-x86_64/ 
should have rpms but doesn't. I am using test rpms I got from there about 
a week ago, but wanted to switch to the prebuilt beta version.



--
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] Is there a point to having both a normal gist index and an exclude index?

[Bruno Wolff III]

On Wed, Apr 05, 2017 at 12:11:09 -0600,
 Rob Sargent <robjsarg...@gmail.com> wrote:



On 04/05/2017 12:04 PM, Bruno Wolff III wrote:

On Wed, Apr 05, 2017 at 00:05:31 -0400,
Tom Lane <t...@sss.pgh.pa.us> wrote:

Bruno Wolff III <br...@wolff.to> writes:

... I create both a normal gist index and an exclude index using the
following:
CREATE INDEX contains ON iplocation USING gist (network inet_ops);
ALTER TABLE iplocation
 ADD CONSTRAINT overlap EXCLUDE USING gist (network inet_ops WITH &&);



But I am wondering if it is useful to have the normal gist index for
finding netblocks containing a specific IP address, as it seems 
like the

exclude index should be usable for that as well.


No, that manually-created index is completely redundant with the
constraint index.


Thanks.

P.S. Using spgist with version 10 for the exclude index is much 
faster than using gist in 9.6. I have run the index creation for as 
long as 6 hours and it hasn't completed with 9.6. It took less than 
10 minutes to create it in 10. For this project using 10 isn't a 
problem and I'll be doing that.




That's an incredible difference.  Is it believable? Same resource, etc?


Same data, same load scripts other than spgist replacing gist and pointing 
to the 10 server instead of the 9.6 server.


If gist is scaling at n^2 because of bad splits, then with 3.5M records 
I could see that big of a difference if spgist is n log n. I don't know for 
sure if that was what is really going on. The index creation seems to 
be CPU bound rather than I/O bound as it is pegging a CPU.



--
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] Is there a point to having both a normal gist index and an exclude index?

[Bruno Wolff III]

On Wed, Apr 05, 2017 at 00:05:31 -0400,
 Tom Lane <t...@sss.pgh.pa.us> wrote:

Bruno Wolff III <br...@wolff.to> writes:

... I create both a normal gist index and an exclude index using the
following:
CREATE INDEX contains ON iplocation USING gist (network inet_ops);
ALTER TABLE iplocation
  ADD CONSTRAINT overlap EXCLUDE USING gist (network inet_ops WITH &&);



But I am wondering if it is useful to have the normal gist index for
finding netblocks containing a specific IP address, as it seems like the
exclude index should be usable for that as well.


No, that manually-created index is completely redundant with the
constraint index.


Thanks.

P.S. Using spgist with version 10 for the exclude index is much faster 
than using gist in 9.6. I have run the index creation for as long as 
6 hours and it hasn't completed with 9.6. It took less than 10 minutes 
to create it in 10. For this project using 10 isn't a problem and I'll 
be doing that.



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

[GENERAL] Is there a point to having both a normal gist index and an exclude index?

[Bruno Wolff III]

I am trying to load a database with about 3.5 million records relating 
netblocks to locations. I currently don't know whether or not any of the 
netblocks overlap. If they don't, then I can simplify queries that 
find the locations of IP addresses.


I create the table as follows:
DROP TABLE IF EXISTS iplocation;
 CREATE TABLE iplocation (
 network INET NOT NULL,
 geoname_id INT,
 registered_country_geoname_id INT,
 represented_country_geoname_id INT,
 is_anonymous_proxy BOOLEAN NOT NULL,
 is_satellite_provider BOOLEAN NOT NULL,
 postal_code TEXT,
 latitude DOUBLE PRECISION,
 longitude DOUBLE PRECISION,
 accuracy_radius DOUBLE PRECISION
);

Then I load the table with /copy.

Then I create both a normal gist index and an exclude index using the 
following:

DROP INDEX IF EXISTS contains;
CREATE INDEX contains ON iplocation USING gist (network inet_ops);
ANALYZE VERBOSE iplocation;
ALTER TABLE iplocation 
 ADD CONSTRAINT overlap EXCLUDE USING gist (network inet_ops WITH &&)

;

So far the exclude index hasn't finished being created.

But I am wondering if it is useful to have the normal gist index for 
finding netblocks containing a specific IP address, as it seems like the 
exclude index should be usable for that as well.



--
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] Unique constraint on field inside composite type.

[Bruno Wolff III]

On Wed, Aug 17, 2016 at 23:02:53 -0700,
 Silk Parrot  wrote:

Hi,

    I am trying to model a social login application. The application can 
support multiple login providers. I am thinking of creating a custom type for 
each provider. e.g.

CREATE TABLE user (
    uuid UUID PRIMARY KEY DEFAULT public.uuid_generate_v4(),
    google_user system.google_user,
    facebook_user system.facebook_user,
    UNIQUE (google_user.email)
);


Wouldn't it more sense to have a table you join to your user table that 
is more flexible and allows for multiple entries per person. You would 
need user, domain, foreign_user, auth_method. This would make it a lot 
easier to add other systems later or let users pick their own systems 
that you don't need to know about in advance.



--
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] endash not a graphic character?

[Bruno Wolff III]

On Sun, Aug 21, 2016 at 14:24:16 -0400,
 Tom Lane  wrote:


Unfortunately, these particular characters are U+2013 and U+2014 so you
lose.


Thanks for saving me some time, as it would have taken me quite a while 
to figure that out.


I'll adjust the constraint so that good strings aren't rejected. Which 
was my immediate problem. I'm not that worried about bad strings getting 
added, since the data also gets checked before trying to add it to 
the database.



Obviously there's room for improvement here, but so far nobody's been
motivated to work on it.  Last discussion about it (AFAIR) was this
thread:


One thing I would suggest is documenting this limitation under: 
https://www.postgresql.org/docs/9.6/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP


I might have missed it, but I did try reading that section to see if I was 
doing something wrong before asking on the list. In particular I would 
expect this limitation to be noted under:

9.7.3.6. Limits and Compatibility


--
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] endash not a graphic character?

[Bruno Wolff III]

On Sun, Aug 21, 2016 at 12:30:21 -0500,
 Bruno Wolff III <br...@wolff.to> wrote:


I should also try the equivalent test in perl to see if it is more 
likely tied to the unicode implementation on my system or if it 
appears to be Postgres specific.


It looks like my locale may not be being set the way I expect. I tried 
testing in perl and initially I got results consistent with Postgres, 
but when I added code to make sure perl was working in utf-8 mode I 
started getting the expected results.


I would have expected manually adding a collation to the queries would 
have worked even if the default was not what I expected. So pointers 
to what I am missing would still be appreciated.



--
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] endash not a graphic character?

[Bruno Wolff III]

On Sun, Aug 21, 2016 at 08:12:23 +1000,
 rob stone  wrote:


You can't use  (emdash) or  (endash)?
Or their hex equivalents. See the Unicode chart.


By the way, those aren't the correct codes. That only works if your 
code treats iso-5589-1 code points as windows 1252 code points. That 
may happen to work in many cases, but isn't a good thing to bet on.

(Single byte utf8 codes match iso-8859-1, not windows 1252.)


--
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] endash not a graphic character?

[Bruno Wolff III]

On Sun, Aug 21, 2016 at 08:12:23 +1000,
 rob stone  wrote:


You can't use  (emdash) or  (endash)?
Or their hex equivalents. See the Unicode chart.


I am not the source of the data, but I can special case them one way 
or the other.


However I am wondering about my use of [[:graph:]] to match characters 
that have glyphs. I was not expecting there to be characters that have 
glyphs to not be in the graph class. In the short term I might want to 
change the way I am testing that.


I should also try the equivalent test in perl to see if it is more likely 
tied to the unicode implementation on my system or if it appears to be 
Postgres specific.



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

[GENERAL] endash not a graphic character?

[Bruno Wolff III]

I was surprised to find endash and emdash were not graphic characters in 
en_US. I'm not sure if this is correct behavior, a bug in postgres or a 
bug in my OS' collation definitions?


For example:

Dash:
area=> select '-' ~ '[[:graph:]]' collate "en_US";
?column? 
--

t
(1 row)

Endash:
area=> select '–' ~ '[[:graph:]]' collate "en_US";
?column? 
--

f
(1 row)


Emdash:
area=> select '—' ~ '[[:graph:]]' collate "en_US";
?column? 
--

f
(1 row)


--
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] Postgresql-fdw

[Bruno Wolff III]

On Sun, May 22, 2016 at 23:38:43 -0700,
 John R Pierce  wrote:


If you want to use postgres to query this data efficiently, you really 
should import this data into postgres tables, properly indexed for the 
sorts of queries you wish to do.


And it isn't that hard to script this kind of thing. Postgres' copy command 
makes it easy to read csv files. You could trigger the scripts by hand (or 
as part of the script that runs the queries) just before running queries, run 
them scheduled at what are normally good times to pick up updates or trigger 
off file changes.



--
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] Using both ident and password in pg_hba.conf

[Bruno Wolff III]

On Mon, May 09, 2016 at 22:43:53 -0400,
 "D'Arcy J.M. Cain"  wrote:


Of course PHP scripts have to run as nobody so I have no choice other
than to have them store passwords in various config.php files but PHP
users are used to that.  I would like to fix that but that's a war for
another day.


You can use peer authentication if the php scripts run on the same machine 
as the database, though you'd probably want to use a different local user 
than 'nobody' to run under.



--
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] Using both ident and password in pg_hba.conf

[Bruno Wolff III]

On Mon, May 09, 2016 at 13:39:48 -0700,
 Adrian Klaver  wrote:


The above does not make sense to me. Maybe I am not understanding if 
you mean connect and login as the same thing or not? I could see 
connecting as 'nobody' and then doing SET ROLE as user. Or connect as 
'nobody' for the PHP script and have a separate connection as the 
database user. Otherwise you are going to have to explain more about 
what you are doing.


The mapping is between system and postgres users. So that the system user 
nobody is allowed to login as any of the postgres users a, b or c.



--
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] Recurring and non recurring events.

[Bruno Wolff III]

On Sat, Dec 26, 2015 at 23:03:30 +1100,
 Kevin Waterson  wrote:

Thanks, as I am new to postgres, I was unaware of this function.
To go with this, I guess I will need a table with which to store intervals,
start and end dates?


There is are built in range types that might be more efficiebt for 
indexing rather than using separate start and stop times. See: 
http://www.postgresql.org/docs/9.5/static/rangetypes.html#RANGETYPES-BUILTIN



--
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] earthdistance

[Bruno Wolff III]

On Tue, Aug 20, 2013 at 20:38:51 +0200,
  Olivier Chaussavoine olivier.chaussavo...@gmail.com wrote:

I also look at cube extension, but the built in type box - a couple of
points - does not require any extension and has a GIST index. It can be
used to represent a rectangle on the domain [-PI/2,+PI/2[*[-PI,PI[. If the
extension was providing a function get_rect_from_cap() giving the smallest
rectangle of this domain containing a spherical cap, this rectangle could
be used as you pointed out to reduce the set of rows where the earth
distance need to be computed to know if a point A belongs to the cap. The
operator  (box overlaps box) could be used if the point A is converted to
box(A,A). Do you think this function get_rect_from_cap() could be usefull?


Depending on how the sphere is represented, getting the minimum bounding 
cube is pretty simple and it might not be worth writing a function for 
this.



--
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] earthdistance

[Bruno Wolff III]

On Sat, Aug 10, 2013 at 12:18:48 +0200,
  Olivier Chaussavoine olivier.chaussavo...@gmail.com wrote:

I did not found any geographic indexing with earthdistance, and need it.


Some of the earthdistance stuff is based on cube which does have indexing. 
I don't know how well that indexing works and it might be pretty bad in 
practice.



The need I have is simple:
is the distance between two (lat,long) positions less than X km?
the model used for the shape of the earth should be related to the
precision of lat,lon, and most sources are imprecise. The spherical model
should be enough.


You might just be looking at this wrong. You don't have an index on the 
distance. What you want is to find points within a cube that is big enough 
to include all of the points of interest and then double check the returned 
points to make sure they are really within the expected range. You can 
calculate the size of the cube needed based on the distance and the 
radius of the earth. I don't remember if there was a built in function 
for that, since it's been such a long time since I looked at it.



--
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] seeking SQL book recommendation

[Bruno Wolff III]

On Wed, Jan 23, 2013 at 15:56:10 -0700,
  Scott Ribe scott_r...@elevated-dev.com wrote:

For a client who needs to learn how to query the db:

- No SQL knowledge at all; needs to start from square 1.

- Smart, capable person, who will be in this position for a long time, using 
this db for a long time.

- No chance in hell this db will be moved off PG, so PG-centric is fine ;-)


I found the postgresql documentation very useful for learning SQL.


--
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] noobie question

[Bruno Wolff III]

On Thu, Jan 24, 2013 at 14:03:33 -0500,
  Steve Clark scl...@netwolves.com wrote:


It is really called rule_num and relates to in what order firewall rules are 
applied. And it used
to allow the user to place the firewall rules where they want them in relation 
to other rules.


If you just need ordering, you could choose to use a string or numeric to 
give you ordering. That allows you to insert values in between existing 
records without having to renumber.


When displaying the data the application can number them based on ordering. 
And keep track of the current mapping between the number on the screen and 
the key in the database.



--
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] PostgreSQL training recommendations?

[Bruno Wolff III]

On Tue, Oct 16, 2012 at 16:24:08 -0300,
  Thalis Kalfigkopoulos tkalf...@gmail.com wrote:


Also IMHO another difficulty the manual poses is that the reader doesn't
have a way to confirm his level of understanding after reading a
chapter.


It isn't too hard to play with a toy database. I personally found (and still 
find) the Postgres manual to be a great resource for learning SQL.



--
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] Encryption - searching and sorting

[Bruno Wolff III]

On Thu, May 03, 2012 at 15:42:00 +0200,
  David Welton dav...@dedasys.com wrote:


Thoughts?


Peter Wayner wrote a book Translucent Databases that has some techniques
for helping solve problems like this. It won't magically solve your
problem, but might give you some more ideas on how you can do it.

--
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] GROUP BY or alternative means to group

[Bruno Wolff III]

On Mon, Mar 12, 2012 at 16:18:05 -0400,
  Michael Gould mgo...@isstrucksoftware.net wrote:

You need to include all columns that are not aggregrative columns in the group 
by.  Even though that is the standard it is a pain to list all columns even if 
you don't need them


In later versions of postgres this is relaxed a bit. If you are grouping
by a primary key, you don't need to group by columns that are fixed
by that key. For example the following query is accepted in 9.1 as gameid
is a key for games and hence we don't need to also group by ga,es.title.

SELECT games.gameid, games.title
  FROM games, crate
WHERE
  games.gameid = crate.gameid
  AND
  games.contact = 'BOB'
  AND
  crate.touched = current_timestamp + '4 year ago'
  GROUP BY games.gameid
  HAVING count(1)  30
  ORDER BY games.gameid
;

--
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] GROUP BY or alternative means to group

[Bruno Wolff III]

On Mon, Apr 09, 2012 at 13:55:04 -0400,
  Michael Gould mgo...@isstrucksoftware.net wrote:

Thanks that is a help. I would be nice if any key could be used as those are 
normally the things I would do group by's


This is what the 9.1 documentation says:
When GROUP BY is present, it is not valid for the SELECT list expressions to 
refer to ungrouped columns except within aggregate functions or if the 
ungrouped column is functionally dependent on the grouped columns, since 
there would otherwise be more than one possible value to return for an 
ungrouped column. A functional dependency exists if the grouped columns (or 
a subset thereof) are the primary key of the table containing the ungrouped 
column.


That implies you need to group by a primary key. I haven't tested if
that (other keys can't provide this) is actually the case.

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

[GENERAL] Bad link to beta2 source

[Bruno Wolff III]

The link Download 9.1 Beta 2 source code on 
http://www.postgresql.org/developer/beta
points to http://www.postgresql.org/ftp/source/v9.1beta1 instead of
http://www.postgresql.org/ftp/source/v9.1beta2 .

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

[GENERAL] Interesting comments about fsync on Linux

[Bruno Wolff III]

I was looking for some information on how write barriers interact with
software raid and ran across the following kernel thread referenced on LWN.
The suggestion is that fsync isn't really safe on Linux as it is currently
implented. (The thread was from February 2008, so it probably still
applies.)
http://lwn.net/Articles/270891/

-- 
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] Documentation fix regarding atan2

[Bruno Wolff III]

On Wed, Sep 05, 2007 at 10:37:18 +1000,
  Andrew Maclean [EMAIL PROTECTED] wrote:
 In Table 9.4 of the documentation atan2 is described as follows:
   atan2(*x*, *y*) inverse tangent of *x*/*y*
 
 I am sure it should read as:
   atan2(*y*, x) inverse tangent of y/x

Aren't those two statements sayiong the same thing?
You've just switched the names 'x' and 'y' and not changed their relationships.

 
 
 You can easily test this:
 If y = 2, x = 1, then degrees(atan(y/x)) =63.4 but if we proceed according
 to the documentation; degrees(atan2(x,y))=degrees(atan2(1,2))=25.6 which is
 not the same as degrees(atan(y/x)).

In this example you switched things around part way thorugh. atan2(1,2)
is the atan of (1/2), not atan(2/1) as used at the beginning of the example.

 So it must be degrees(atan2(y,x))=degrees(atan2(2,1))=63.4.

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

Re: [GENERAL] greatest/least semantics different between oracle and postgres

[Bruno Wolff III]

On Sat, Jun 30, 2007 at 09:29:23 +0200,
  Pavel Stehule [EMAIL PROTECTED] wrote:
 Hello,
 
 I have not Oracle, so I cannot test it, but PostgreSQL implementation
 respect Oracle:
 
 http://archives.postgresql.org/pgsql-patches/2005-06/msg00431.php

Maybe that reference was for an earlier version of Oracle and the definition
changed at some point? I only have access to version 9 and greatest and
lest are strict there.

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

[GENERAL] greatest/least semantics different between oracle and postgres

[Bruno Wolff III]

The following is just FYI.
I was recently doing some stuff with greatest() on oracle (9.2.0.8.0) and
noticed that it returned null if ANY of the arguments were null. Out of
curiosity I checked postgres' definition of that function and found that it
returns null only if ALL of the arguments are null.

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

Re: [GENERAL] greatest/least semantics different between oracle and postgres

[Bruno Wolff III]

On Sat, Jun 30, 2007 at 00:15:42 -0400,
  Tom Lane [EMAIL PROTECTED] wrote:
 Andrej Ricnik-Bay [EMAIL PROTECTED] writes:
  On 6/30/07, Bruno Wolff III [EMAIL PROTECTED] wrote:
  I was recently doing some stuff with greatest() on oracle (9.2.0.8.0) and
  noticed that it returned null if ANY of the arguments were null. Out of
  curiosity I checked postgres' definition of that function and found that it
  returns null only if ALL of the arguments are null.
 
  W/o knowing the SQL standard (just from what I'd perceive
  as sensible) I'd say Oracle is broken. :}
 
 Hmm ... I fear Oracle's behavior is more correct, because if any
 argument is null (ie, unknown), then who can say what the greatest or
 least value is?  It's unknown (ie, null).  But I suspect our behavior
 is more useful.  Comments?

In my case I would have prefered Postgres' behavior. I wanted to take
the max of values coming from two columns by taking the greatest of
two subselects. I ended up rewriting the query to take the max of a union.
The annoying thing was I didn't have a good way to use coalesce as I wanted
to get a null if both subselects were empty. Also what value should I have
used in a coalesce to guaranty still getting the maximum? I think having
it work like aggregates and ignoring null values is more convenient.
However if the feature was added for oracle compatibility then not working
the same is an issue.

I was just hoping that perhaps the fact that the semantics are different
between oracle and postgres would get noted somewhere so people porting
would have a better chance to become aware of the issue.

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

Re: [GENERAL] Fault Tolerant Postgresql (two machines, two postmasters, one disk array)

[Bruno Wolff III]

On Thu, May 10, 2007 at 20:43:20 -0500,
  John Gateley [EMAIL PROTECTED] wrote:
 Sorry if this is a FAQ, I did search and couldn't find much.
 
 I need to make my Postgresql installation fault tolerant.
 I was imagining a RAIDed disk array that is accessible from two
 (or multiple) computers, with a postmaster running on each computer.
 (Hardware upgrades could then be done to each computer at different
 times without losing access to the database).
 
 Is this possible?

You can't have two postmasters accessing the same data. Doing so will cause
corruption. You can have a failover system where another postmaster starts
after the normal one has stopped. But you need to be completely sure the
normal postmaster has stopped before starting the backup one.

 Is there another way to do this I should be looking at?

Depending on your needs replication might be useful.

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

   http://archives.postgresql.org/

Re: [GENERAL] simple coordinate system

[Bruno Wolff III]

On Fri, Mar 16, 2007 at 15:55:15 +0100,
  Robin Ericsson [EMAIL PROTECTED] wrote:
 On 3/16/07, Tom Lane [EMAIL PROTECTED] wrote:
 Robin Ericsson [EMAIL PROTECTED] writes:
  Yes, I've looked at those, I was thinking that point looked like a
  good type, but it's only 2d, so maybe I need a hint on how to use this
  in a 3d environment.
 
 Yeah, the built-in geometric types are all 2D.  If you need 3D, perhaps
 PostGIS can help --- otherwise you're on your own :-(.  But adding a new
 datatype to PG isn't hard, if you can hack C at all.
 
 My hope was that there was something between standard PostgreSQL and
 PostGIS as I didn't want to bring in the whole PostGIS into my
 application. But probably it's worth it anyways.

The cube contrib stuff might be useful for you.

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

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

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

[Bruno Wolff III]

On Mon, Mar 12, 2007 at 11:15:01 -0700,
  Stefan Berglund [EMAIL PROTECTED] wrote:
 
 I have an app where the user makes multiple selections from a list.  I
 can either construct a huge WHERE clause such as SELECT blah blah FROM
 foo WHERE (ID = 53016 OR ID = 27 OR ID = 292 OR ID = 512) or I could
 alternatively pass the string of IDs ('53016,27,292,512') to a table
 returning function which TABLE is then JOINed with the table I wish to
 query instead of using the unwieldy WHERE clause.  The latter strikes me
 as a far more scalable method since it eliminates having to use dynamic
 SQL to construct the ridiculously long WHERE clause which will no doubt
 ultimately bump up against parser length restrictions or some such.

How big is huge?
If the list of IDs is in the 1000s or higher, then it may be better to
load the data into a temp table and ANALYSE it before running your query.
Otherwise, for smaller lists the IN suggestion should work well in recent
versions.

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

Re: [GENERAL] SQL Question - Group By and % results per row

[Bruno Wolff III]

On Mon, Mar 12, 2007 at 12:53:11 -0700,
  Mike [EMAIL PROTECTED] wrote:
 
 How do I get access to the total of all clicks on per row basis so I
 can divide it? The only solution that comes to my mind is create a
 subquery that does a (select count(*) from... where... ) of the
 original grouped by sql statement.

That's how you do it.

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

Re: [GENERAL] security permissions for functions

[Bruno Wolff III]

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

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

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

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

Re: [GENERAL] Setting week starting day

[Bruno Wolff III]

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

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

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

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

[Bruno Wolff III]

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

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

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

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

Re: [GENERAL] Setting week starting day

[Bruno Wolff III]

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

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

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

   http://archives.postgresql.org/

Re: [GENERAL] Setting week starting day

[Bruno Wolff III]

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

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

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

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

Re: [GENERAL] Setting week starting day

[Bruno Wolff III]

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

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

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

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

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

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

Re: [GENERAL] Setting week starting day

[Bruno Wolff III]

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

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

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

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

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

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

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

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

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

Re: [GENERAL] Setting week starting day

[Bruno Wolff III]

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

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

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

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

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

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

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

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

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

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

Re: [GENERAL] Can I getting a unique ID from a select

[Bruno Wolff III]

On Mon, Mar 05, 2007 at 17:07:25 -0800,
  Timasmith [EMAIL PROTECTED] wrote:
 
 create view myview as
 select rownum, t1.field, t2.field
 from tableOne t1, tableTwo t2
 where t1.key = t2.fkey
 
 Multiple rows with the same key renders Hibernate useless as it caches
 the 'row object' and then returns the first row every time for that
 object.
 
 I think the sequence will work though, in reflection I guess it would
 as fast as pulling another field, and with the numbers would be a very
 long time before getting duplicates - even if you had thousands of
 users, returning 100s of rows every few minutes (I think...).

Based on the naming (t1.key vs t2.fkey) it looks like you may have a one
to many relationship. If so, can't you just bring in the primary key from
t2, as under the above assumption there will be only one matching row
from t1?

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

Re: [GENERAL] Can I getting a unique ID from a select

[Bruno Wolff III]

On Sat, Mar 03, 2007 at 16:46:45 -0800,
  Timasmith [EMAIL PROTECTED] wrote:
 On Mar 3, 7:12 pm, [EMAIL PROTECTED] (Bruno Wolff III) wrote:
  On Thu, Mar 01, 2007 at 06:16:02 -0800,
   Timasmith[EMAIL PROTECTED] wrote:
 
   create view myview as
   select rownum, t1.field, t2.field
   from tableOne t1, tableTwo t2
   where t1.key = t2.fkey
 
 
 Never heard of a 'join key' but that sounds very promising.  How do I
 select it?
 

The join key would be t1.key or t2.fkey from your example. However there
may be multiple rows returned with the same value depending on what you
are joining. If that is the case you, should be able to use the primary
keys of the underlying tables to make a new candidate key for the joined
rows.

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

Re: [GENERAL] Can I getting a unique ID from a select

[Bruno Wolff III]

On Sat, Mar 03, 2007 at 18:12:19 -0600,
  Bruno Wolff III [EMAIL PROTECTED] wrote:
 On Thu, Mar 01, 2007 at 06:16:02 -0800,
   Timasmith [EMAIL PROTECTED] wrote:
  I am using hibernate, using a view like a read only table and I need a
  primary key each time a select is issued.
  
  create view myview as
  select rownum, t1.field, t2.field
  from tableOne t1, tableTwo t2
  where t1.key = t2.fkey
  
  select * from myview
  
  But what I really need is
  
  select makemeauniquekey, t1.field, t2.field
  ...
 
 Is there some reason you can't use the join key?

To expand on this, if you are joining on fields that will return only
one record for each value, you should still be able to make a primary
key for the returned records using a combination of the primary keys
of both records being joined. If hibernate only works with primary keys
consisting of one column, than you can create a new field using a function
of the primary keys of the records being joined.

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

Re: [GENERAL] Can I getting a unique ID from a select

[Bruno Wolff III]

On Thu, Mar 01, 2007 at 06:16:02 -0800,
  Timasmith [EMAIL PROTECTED] wrote:
 I am using hibernate, using a view like a read only table and I need a
 primary key each time a select is issued.
 
 create view myview as
 select rownum, t1.field, t2.field
 from tableOne t1, tableTwo t2
 where t1.key = t2.fkey
 
 select * from myview
 
 But what I really need is
 
 select makemeauniquekey, t1.field, t2.field
 ...

Is there some reason you can't use the join key?

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

   http://archives.postgresql.org/

Re: [GENERAL] Why does group by need to match select fields?

[Bruno Wolff III]

On Wed, Feb 28, 2007 at 16:19:02 -0800,
  Omar Eljumaily [EMAIL PROTECTED] wrote:
 select max(amount), payee, id from checks group by payee;
 
 Why won't the above work?  Is there another way to get the id for the 
 record with the highest amount for each payee?

While the DISTINCT ON approach is probably best if you can live with a
Postgres specific solution, the general way to do this is use the group by
query to get a set of primary keys with aggregates and then you join this
back to the original table to get the other data.

Some databases will also recognize that you are grouping by a candidate
key and allow you to specify normal columns since they must all have the
same value for rows with the same candidate key value. Unfortunately Postgres
doesn't do that now.

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

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

Re: [GENERAL] Esay question, about the numeric format

[Bruno Wolff III]

On Thu, Feb 22, 2007 at 12:20:12 +0100,
  Rafa Comino [EMAIL PROTECTED] wrote:
 Hi every body
 I have this query
 SELECT 20.00::numeric(38,2)
 and postgre gives me 20, i need that postgre gives me 20.00
 What can i do? i suppose this must be easy, but i dont find how to do ir
 thanks every body

If the exact output format matters, you should probably use to_char to convert
the number to a string.

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

Re: [GENERAL] [HACKERS] urgent: upgraded to 8.2, getting kernel panics

[Bruno Wolff III]

On Fri, Feb 23, 2007 at 18:14:25 -0500,
  Tom Lane [EMAIL PROTECTED] wrote:
 Merlin Moncure [EMAIL PROTECTED] writes:
  On friday we upgraded a critical backend server to postgresql 8.2
  running on fedora core 4.
 
 Umm ... why that particular choice of OS?  Red Hat dropped update
 support for FC4 some time ago, and AFAIK the Fedora Legacy project
 is not getting things done.  How old is the kernel you're using?

The Fedora Legacy project is officially gone now.

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

Re: [GENERAL] [HACKERS] urgent: upgraded to 8.2, getting kernel panics

[Bruno Wolff III]

On Mon, Feb 26, 2007 at 15:57:02 +0200,
  Devrim GUNDUZ [EMAIL PROTECTED] wrote:
 
 Upgrading OS will probably solve your problem; since there is no way to
 upgrade FC4 kernel unless you want to compile kernel source on your
 system.

And good luck with that. Fedora still back patches stuff from later kernels
than the one you think you have based on the name. Building a Linus kernel
and getting the right mix of versions to work on a particular version of
Fedora might be hard to do. If you can find the patch that fixes the
problem, your best bet (assuming you have to use FC4) would be to try to apply
that fix to the latest Fedora kernel for FC4.

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

   http://archives.postgresql.org/

Re: [GENERAL] change data type int4 to serial

[Bruno Wolff III]

On Wed, Feb 21, 2007 at 11:20:38 -0600,
  Seb [EMAIL PROTECTED] wrote:
 
 Checking the results in pgadmin, this proceeded fine, but now that I want
 to specify the primary and foreign keys in the tables, I see that the
 columns needed for this were imported as int4 data type.  I would like
 these to be automatically sequenced, so need them to be 'serial'.  Going
 into properties for these columns in pgadmin, the 'serial' option is not
 available in the data type pull-down menu.  Is this not possible?  Can
 the 'serial' data type be specified during import.  Thanks in advance.

'serial' is a psuedo type that uses a DEFAULT that references a SEQUENCE.
In recent versions of postgres a dependency is set up so that you can't
delete the sequence while it is being referenced.

You can create the sequence manually, set an appropiate starting value and
use ALTER TABLE to change the default for the column to use nextval to
get the next sequence value.

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

Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?

[Bruno Wolff III]

On Sun, Feb 18, 2007 at 12:29:17 +0100,
  Karsten Hilbert [EMAIL PROTECTED] wrote:
 
 The date-of-birth field in our table holding patients is of
 type timestamp with time zone. One of our patient search
 queries uses the date-of-birth field to find matches. Since
 users enter day, month, and year but not hour, minute, and
 second of the DOB we run the query with

That seems like an odd choice. Is there some reason they didn't use a type
of date? Maybe you could get them to change it?

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

Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?

[Bruno Wolff III]

On Mon, Feb 19, 2007 at 20:48:07 +0100,
  Karsten Hilbert [EMAIL PROTECTED] wrote:
 
 What time of day were you born ?
 
   http://en.wikipedia.org/wiki/Apgar
 
 What is the technical reason that makes you wonder ?

Because it would make doing the queries simpler.
If you aren't collecting the data, it doesn't make sense to deal with the
extra headaches involved with pretending you know what time of day someone
was born.

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

Re: [GENERAL] requests / suggestions to help with backups

[Bruno Wolff III]

On Thu, Feb 15, 2007 at 22:39:13 -0500,
  Lou Duchez [EMAIL PROTECTED] wrote:
 
 1) grant select on database ... or, hypothetically, grant select on 
 cluster. The goal would be to create a read-only PostgreSQL user, one
 who can read the contents of an entire database (or even the entire
 cluster) but make no changes.  Currently, to do my cron job, I have to
 specify a trusted user, otherwise PostgreSQL will ask for a password;
 it sure would be nice if I could neuter my trusted user so he cannot
 do any damage. (Yes, I could set read-only privileges on a table-by-table
 basis. Obviously, that's a pain.)

You can use ident authentication instead of trust. That may make using the
postgres db account for the cronjob's connection an acceptible risk.

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

   http://archives.postgresql.org/

Re: [GENERAL] Recover anything from dropped database?

[Bruno Wolff III]

On Thu, Feb 15, 2007 at 10:53:48 -0500,
  John D. Burger [EMAIL PROTECTED] wrote:
 
 I presume from the near-deafening silence there's nothing else I can  
 do, which is no surprise, but I'd still like confirmation about how  
 to restore the backup.
 
 (It turns out I can recover the changes since the backup annother  
 way, since they all happened through interaction with CGI scripts,  
 luckily GET rather than POST - I can replay the relevant URLs  
 grepped from the web server log.)

It wasn't entirely clear what you wanted to accomplish. If you had mentioned
needing find at least some of the transactions that occured, then you might
have got some suggestions along the lines of imaging the disk to capture
data from teh recently freed blocks. There wouldn't be an automated way to
get the data back into the database, but you might have been able to find
some things out.

However, the web server logs are probably going to give you what you want
more reliably than grepping through the freed blocks, so there isn't any point
in going there.

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

Re: [GENERAL] temp tables in functions?

[Bruno Wolff III]

On Wed, Feb 07, 2007 at 20:40:09 -0800,
  jws [EMAIL PROTECTED] wrote:
 Having developed a complex query, I want to wrap it up as a function
 so that it can take a parameter and return a set of rows. This query
 is currently written as multiple sql statements that create a few
 interstitial temp tables that are then joined. If I put this into a
 function definition, do those temp tables get dropped automatically
 when the function returns?

See: http://www.postgresql.org/docs/8.2/interactive/sql-createtable.html
So, the answer is no.

Also note that currently Postgres will cache information about tables
used in functions and this may not work well when you are dropping and
recreating tables with the same name in the same session. For that kind
of thing you need to use EXECUTE to avoid caching.

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

Re: [GENERAL] ERROR: missing cache data for cache id 27

[Bruno Wolff III]

On Sun, Feb 04, 2007 at 23:43:48 -0800,
  David Fetter [EMAIL PROTECTED] wrote:
 On Sun, Feb 04, 2007 at 03:18:07PM -0200, Jorge Godoy wrote:
  Tom Lane [EMAIL PROTECTED] writes:
  
   Jorge Godoy [EMAIL PROTECTED] writes:
   I'm using PostgreSQL 8.1.4 and psql 8.1.4 as well. 
  
   This was fixed in 8.1.5 ... or at least the only known cause was
   fixed.
  
  Thanks!  I'll bug OpenSuSE guys to release an 8.1.5 package ;-)
 
 8.1.6 is the current one.

That is so yesterday.
You'll want to get 8.1.7 now.

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

   http://archives.postgresql.org/

Re: [GENERAL] How to allow users to log on only from my application not from pgadmin

[Bruno Wolff III]

On Fri, Feb 02, 2007 at 07:20:04 +0900,
  Paul Lambert [EMAIL PROTECTED] wrote:
 How?

Use a debugger.

 If it is encrypted within the source code then the only way to steal the 
 credentials would be to reverse engineer the application. And if someone 
 is going to do that then you can be relatively assured that they are 
 going to do anything and everything to get around whatever other 
 security you can offer. At which point you could send the law after them 
 for breach of copyright or other such law - at least that is the case 
 down here in Australia.
 
 We have an application which connects to a database in MySQL. Each user 
 has their own username/password to log onto the application which does 
 so through authenticating against a users table in the db. The 
 application itself has hard-coded within a username/password to get the 
 initial access to the database. With somewhere in the vicinity of 1,000 
 people using this particular application we've not seen a case of anyone 
 accessing it using anything other than our application.

I imagine most people's customers don't try to work around broken security.
The scheme you have described above is broken.

 You want to either run the app on a computer you control 
 
 It's not always feasible to host the application main on your own 
 server. Depending on network distance, traffic, size of application, 
 number of users etc, it could require some extremely high spec hardware 
 to host and beefed up network connections. This is not possible for a 
 lot of service providers out there, not to mention that those willing to 
 reverse engineer the software (or run packet sniffers and decrypt 
 network traffic) to get the password out of it would still find a way of 
 determining the password your hosted app is using.
 
 or have a contract
 with the customers prohibiting them from connecting to the database other 
 than
 by using the app.
 
 If customers access a database hosted by a service provider it is 
 generally the norm to have some clauses in the contract pertaining to 
 data protection and ownership making access to provider hosted data by 
 any means other than those authorised by the provider a breach of contract.

Well, then that is really your protection. The above security by obscurity
is just a way to help keep the honest people honest.

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

   http://archives.postgresql.org/

Re: [GENERAL] Defining and Using variables in a postgres function

[Bruno Wolff III]

On Fri, Feb 02, 2007 at 17:18:39 +0100,
  Alban Hertroys [EMAIL PROTECTED] wrote:
 
 You can do this:
 INSERT INTO tbl_email (option_public, agency, id)
 SELECT $1, $2, MAX(id) + 1
 FROM xyz;
 
 I just realize you don't so much need a lock, you need a serialized
 transaction. I can't say I know a lot about locks, I usually prevent
 needing them.

No, a serialized transaction isn't good enough. You need predicate locking,
which postgres doesn't have. So you need to use lock table to do effectively
the same thing with a more blunt instrument.

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

   http://archives.postgresql.org/

Re: [GENERAL] How to allow users to log on only from my application not from pgadmin

[Bruno Wolff III]

On Thu, Feb 01, 2007 at 10:24:51 +0900,
  Paul Lambert [EMAIL PROTECTED] wrote:
 
 If you hide the database username and password within your application 
 (i.e. encrypted within the source code) so they cannot see the 
 credentials that you connect to the database with internally then they 
 have no means by which to connect to it using any other programs.

This is not real security. Encrypting the data in the application only works
if the application is running on a computer you control. If the customer
can get their own copy of the client and run it on a computer they control
then they can steal or borrow the applications credentials.

You want to either run the app on a computer you control or have a contract
with the customers prohibiting them from connecting to the database other than
by using the app.

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

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

Re: [GENERAL] What's the best way to index this table for speed?

[Bruno Wolff III]

On Thu, Feb 01, 2007 at 10:42:30 -0800,
  Carl Lerche [EMAIL PROTECTED] wrote:
 
 How can I index 2 dimensional data (latitude / longitude) with a
 status_id column too (integer) so that I can perform the following
 query as fast as possible:
 
SELECT * FROM profiles WHERE status_id = 1 AND latitude BETWEEN
 y_1 AND y_2 AND longitude BETWEEN x_1 AND x_2;
 
 Obviously a btree index wouldn't work well and in the documentations
 it said an rtree index works for 2 dimensional queries, but I would
 like to filter first by status_id since that will probably eliminate
 50%+ of the rows in the table. There are currently over 600 000 rows
 in the table so far, and it will be growing to well over a million.

The earth distance contrib will use gist indexes based on the underlying
cube data type (also in contrib).

The the location is at all selective, then you probably don't need to worry
about status as it isn't very selective. If your data is clustered on the
disk mostly by location, it might not by you much at all since the data will
like be read from disk anyway.

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

Re: [GENERAL] How to allow users to log on only from my application not from pgadmin

[Bruno Wolff III]

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 allow users to login only from my application ?
 I think I must create server-side pgsql procedure for login validation.

Run the application on a machine you control. Then the application can
authenticate without the users being able to steal or piggyback on its
credentials.

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

Re: [GENERAL] Any Plans for cross database queries on the same server?

[Bruno Wolff III]

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

I remember when I first was exposed to man pages. My thoughts were that it
was great to have documentation on line, but too bad the total extent of
the documentation was the man pages. Actually there were a few more things
in the unix books, but I didn't easy access to them, unlike our VMS manuals
which I really liked. (I've also read a lot of exec 8 documentation and I
didn't like that system.)

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

Re: [GENERAL] Load balancing across disks

[Bruno Wolff III]

On Mon, Jan 29, 2007 at 11:50:35 +0900,
  Paul Lambert [EMAIL PROTECTED] wrote:
 
 In order to balance disk load and ensure faster data access, my current 
 SQL server setup has the data spread across 3 physical disk devices. One 
 question I would like to know which I can't find in the documentation 
 I've been reading is if Postgres has any similar data distribution 
 abilities.
 
 I.e. can I create a data file on D drive which holds tables a, b and e, 
 and a data file on E drive which holds tables c, d and f.
 
 If this is possible, could someone point me to some documentation so I 
 can experiment a little.

http://developer.postgresql.org/pgdocs/postgres/manage-ag-tablespaces.html

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

Re: [GENERAL] PostgreSQL 9.0

[Bruno Wolff III]

 On Jan 29, 2007, at 4:27 PM, Karen Hill wrote:
 
 I was just looking at all the upcoming features scheduled to make it
 into 8.3, and with all those goodies, wouldn't it make sense for this
 to be a 9.0 release instead of an 8.3?  It looks like postgresql is
 rapidly catching up to oracle if 8.3 branch gets every feature
 scheduled for it.

At one point there was discussion about using changes to the first digit
to indicate that a dump and restore was needed because of an on disk format
change and that changes to the second digit would indicate that only catalog
entries have changed and that an upgrade tool (that doesn't exist yet) could
be used to make the changes with minimal downtime.

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

Re: [GENERAL] PostgreSQL 9.0

[Bruno Wolff III]

On Mon, Jan 29, 2007 at 15:51:54 -0800,
  Rich Shepard [EMAIL PROTECTED] wrote:
 On Tue, 30 Jan 2007, Michael Glaesemann wrote:
 
 It was *discussed*. 8.1 to 8.2 (as does any move from M.x to M.y where x ­
 y) requires a dump and reload.
 
 Michael,
 
   That's what I thought. However, it never hurts to ask. :-)

I figured that mentionioning you need a tool that doesn't exist would make
it clear that this was proposed for the future and not current reality.

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

Re: [GENERAL] column limit

[Bruno Wolff III]

On Thu, Jan 25, 2007 at 10:47:50 -0700,
  Isaac Ben [EMAIL PROTECTED] wrote:
 
 The data is gene expression data with 20,000 dimensions. Part of the
 project I'm working on is to discover what dimensions are truly
 independent.   But to start with I need to have
 all of the data available in a master table to do analysis on.  After
 the analysis I hope to derive subsets of much lower dimensionality.

Are you actually planning to do the analysis in Postgres? This doesn't seem
like a real good fit for that kind of task. (Though I haven't played with
the R stuff, and that might be good for doing that kind of analysis.)

If you do put this in postgres, it seems the two most natural things are
to use arrays to store the dimension values or to have table with a key
of the gene and the dimension and have another column with the value of
that dimension for that gene.

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

Re: [GENERAL] column limit

[Bruno Wolff III]

On Thu, Jan 25, 2007 at 08:34:08 -0700,
  Isaac Ben [EMAIL PROTECTED] wrote:
 Hi,
 I'm trying to create a table with 20,000 columns of type int2, but I
 keep getting the error message that the limit is 1600.  According to
 this message http://archives.postgresql.org/pgsql-admin/2001-01/msg00199.php
 it can be increased, but only up to about 6400.  Can anyone tell me
 how to get 20,000 columns?

Can you explain what you are really trying to do? It is unlikely that using
2 columns is the best way to solve your problem. If we know what you are
really trying to do we may be able to make some other suggestions.
One thing you might start looking at is using an array or arrays.

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

Re: [GENERAL] sequence increment jumps?

[Bruno Wolff III]

On Thu, Jan 25, 2007 at 12:33:51 -0500,
  John Smith [EMAIL PROTECTED] wrote:
 guys,
 i inserted 1 record into my database (default
 nextval('sequencename'::regclass) where (start 1 increment 1)). then i
 tried to insert 1 other record twice but both those inserts failed
 because of a domain check (ERROR: value too long for type character
 varying(X). when i was finally able to insert that record the
 sequence jumped 2 places. seems like it counted the failed inserts?

That is how sequences work. All your are guaranteed globally is that they
are unique. You can't rely on getting a sequence without gaps. Within a single
session you can get a guaranty that the values increase monotonicly if you
disallow wrap around for the sequence.

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

Re: [GENERAL] Installing PostgreSQL under Cpanel

[Bruno Wolff III]

On Wed, Jan 24, 2007 at 02:17:53 +0800,
  Erick Papadakis [EMAIL PROTECTED] wrote:
 
 I was just looking at the ident/trust/etc authentication banter from
 pgsql docs. Couldn't make out what greek was on there. When I jostled
 a bit, and finally understood it, and really wanted to write it in
 plain English for the next simple user like myself who just wants to
 get cracking with the db and doesn't care about the admin intricasies,
 then how I should I write it?

That's really not a good idea. I strongly recommend at least skimming through
the entire postgres manual before trying to admin your own instance of it.
Spending some time up front to better understand what you are doing and what
options you have is going to save you much grief down the road.

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

Re: [GENERAL] Example of RETURNING clause to get auto-generated keys from INSERT

[Bruno Wolff III]

On Tue, Jan 23, 2007 at 23:19:47 -0600,
  Adam Rich [EMAIL PROTECTED] wrote:
 
 And your normal query would be this:
 
 INSERT into mytable (id,value) values (1,foo),(2,bar);
 
 Your new query would be like this:
 
 INSERT into mytable (id,value) values (1,foo),(2,bar)
 RETURNING id;

Note that you will want to be using single quotes not double quotes.

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

Re: [GENERAL] indexing primary and foreign keys w/lookup table

[Bruno Wolff III]

On Wed, Jan 24, 2007 at 20:14:07 -0800,
  Neal Clark [EMAIL PROTECTED] wrote:
 I was wondering...I currently have indexes on the primary key id and  
 foreign key id's for tables that resemble the following. Is this a  
 good idea/when would it benefit me? I don't want waste a lot of  
 unnecessary space on indexes.

Not exactly. Primary keys already result in an index being created to enforce
uniqueness, so the manually created indexes are redundant.
 
 CREATE TABLE stuff_by_account (
   account_id  BIGINT REFERENCES accounts(id),
   stuff_idBIGINT REFERENCES stuff(id)
 );
 CREATE INDEX stuff_by_account_account_id ON stuff_by_account 
 (account_id);
 CREATE INDEX stuff_by_account_stuff_id ON stuff_by_account(stuff_id);

For this last case, you most likely want to declare either account_id, stuff_id
or stuff_id, account_id as a primary key. You may want to create an index
just on the second column of the primary key, depending on your usage pattern.
You almost certainly wouldn't want to create an index on the first column
of the primary key.

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

   http://archives.postgresql.org/

Re: [GENERAL] Converting 7.x to 8.x

[Bruno Wolff III]

On Thu, Jan 25, 2007 at 15:43:19 +1100,
  Chris [EMAIL PROTECTED] wrote:
 Carlos wrote:
 What would be the faster way to convert a 7.4.x database into an 8.x 
 database?  A dump of the database takes over 20 hours so we want to 
 convert the database without having to do a dump and resptore.
 
 That's your only option as far as I know (I'm sure someone will correct 
 me if that's not the case).
 
 You can't do a binary conversion or anything like that because the 
 postgres internals are different between major versions.

People use slony to do this. You can ask on the slony list for more
details.

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

Re: [GENERAL] Password encryption method

[Bruno Wolff III]

On Tue, Jan 23, 2007 at 09:44:28 +0100,
  Bertram Scharpf [EMAIL PROTECTED] wrote:
 Hi Bruno,
 
 Am Montag, 22. Jan 2007, 23:11:41 -0600 schrieb Bruno Wolff III:
  If the web server is running on the same machine as the DB,
  then consider using ident authentication and connecting using domain 
  sockets.
 
 Ah, a good suggestion. Thanks!
 
 I found an exhaustive documentation on
 http://developer.postgresql.org/pgdocs/postgres/auth-methods.html.
 
  (This is available under Windows.)
 
 What is Windows?

It was supposed to say domain sockets are NOT available under windows.

Just in case you weren't being funny, I meant the OS sold by Microsoft.

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

Re: [GENERAL] Password encryption method

[Bruno Wolff III]

On Tue, Jan 23, 2007 at 09:01:56 -0800,
  Richard Troy [EMAIL PROTECTED] wrote:
 
 On Mon, 22 Jan 2007, Bruno Wolff III wrote:
  On Mon, Jan 22, 2007 at 20:25:48 +0100,
Bertram Scharpf [EMAIL PROTECTED] wrote:
  
   What I want to do is the following:
  
 1. Login in from a program on a client as a particualar user.
 
  For this case you shouldn't need to do anything tricky as long as the user
  is login in as themselves. Just prompt the user for their password and use 
  it
  when you open a connection to the database. If you are trying to have the
  program login without the user being able to steal or borrow the 
  credentials,
  then you have a serious design flaw.
 
 I'm quite certain I missed the start of this thread, but just looking at
 the above paragraph as it stands:
 
 Design flaw? Perhaps an _incomplete_ design, but it's only a design flaw
 if not finished off properly. One way to do this cleanly is to use a
 program that has the suid bit set so it runs as the program's file owner
 (optionally group), and this program accesses the password and provides
 the database access.

You are correct. I over generalized. I should have added :and you don't control
the computer the user is running the client program on. In the case where you
do control the computer, setuid can be used to do things securely.

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

   http://archives.postgresql.org/

Re: [GENERAL] Postgresql.conf

[Bruno Wolff III]

On Tue, Jan 23, 2007 at 10:12:13 -0500,
  Brandon Aiken [EMAIL PROTECTED] wrote:
 Out of curiosity, has the COUNT(*) with no WHERE clause slowness been
 fixed in 8.x?  Or is it still an issue of there's no solution that
 won't harm aggregates with WHERE clauses?

Probably not in the sense that you mean.

The underlying problem is that in MVCC there is no single global answer
to the question and the pain of maintaining the mutliple answers outweighs
the cost of doing so in normal usage.

People that need to run count(*) queries a lot may want to make a different
trade off and some ways of maintaining counts are covered in the archives.

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

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

Re: [GENERAL] Postgresql.conf

[Bruno Wolff III]

On Tue, Jan 23, 2007 at 14:15:23 -0500,
  Jeremy Haile [EMAIL PROTECTED] wrote:
 But there are ways that we could optimize count(*) queries for specific
 circumstances right?  Obviously this isn't trivial, but I think it would
 be nice if we could maintain a number of rows count that could be used
 when performing a count(*) on the whole table (no where clause).   

People can already do that. How to do it right (to avoid update contention)
is even described in the mailing list archives. There just isn't a nice
contrib or pgfoundry project to wrap it up for them. Of course if there was
people might install the project even though there was a net loss in
performance for them.

 I don't know if the overhead of keeping track of that number is worth
 the benefits - but I know that querying for the number of rows in a
 table is a common need and other RDBMSs do optimize for that special
 case.

That is debatable. Certainly a lot of people run adhoc unconstrained count(*)
queries. Whether they normally need exact counts or whether the number of such
queries is large enough compared to other queries being done to be considered
common is another matter.

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

Re: [GENERAL] Date Questions

[Bruno Wolff III]

On 01/23/07 17:22, Robert Sanford wrote:
 
 January 07 of 2007 is a Sunday. Based on the documentation I would
 expect that date to be the first day of the second week of the year
 2007. That's not what I'm getting. When I run:

Read the 'week' documentation carefully. ISO weeks start on Mondays. So
2007-01-07 would be the last day of the first week of 2007.

So in some sense dow is inconsistant with week, but both are working
consistant with the documentation.

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

Re: [GENERAL] Password encryption method

[Bruno Wolff III]

On Sun, Jan 21, 2007 at 15:16:37 +0200,
  Andrus [EMAIL PROTECTED] wrote:
 
 No, the tables would be on the server, the same as was already being done.
 Using a separate table makes it more future proof.
 
 To access tables in server, you need to login into server.
 To login into server, you need postresql user name and password sent by 
 client and thus stored in client computer.
 
 It is possible to obtain this information from client computer and use it 
 for unauthirized access to data.

This is the same problem as checking the password versus the native (to
postgres) password hashes. I suggested having private tables as an alternative
to that in order for the OP to not have problems with future upgrades, which
was the original question.

I didn't give an opinion on whether or not the whole approach was a good
idea or not, since there wasn't enough detail in the original question.

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

Re: [GENERAL] Password encryption method

[Bruno Wolff III]

On Mon, Jan 22, 2007 at 20:25:48 +0100,
  Bertram Scharpf [EMAIL PROTECTED] wrote:
 
 What I want to do is the following:
 
   1. Login in from a program on a client as a particualar user.

For this case you shouldn't need to do anything tricky as long as the user
is login in as themselves. Just prompt the user for their password and use it
when you open a connection to the database. If you are trying to have the
program login without the user being able to steal or borrow the credentials,
then you have a serious design flaw.

   2. Login from a series of scripts run by Apache on localhost
  ('trust' authentication method). Of course, I won't hand the
  password through web pages. Therefore I store something like a
  'session cookie' in a table. Next time I log in as a superuser,
  read the appropriate entry and immediately do a set session
  autorization. The first step can be done in two ways: (a) I write
  a special login routine, (b) I log in as any other script and do
  the password check against pg_authid using the function I proposed.

If you use trust, be sure to limit that authentication rule to expected
IP addresses and take steps to prevent spoofed packets from getting into
your network. If the web server is running on the same machine as the DB,
then consider using ident authentication and connecting using domain sockets.
(This is available under Windows.)

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

Re: [GENERAL] Password encryption method

[Bruno Wolff III]

On Fri, Jan 19, 2007 at 09:31:49 +0100,
  Bertram Scharpf [EMAIL PROTECTED] wrote:
 Hi,
 
 looking at the source code I find out that this works:
 
   sandbox=# create role joe login password 'verysecret';
   CREATE ROLE
   sandbox=# create function validate_user_8_1(text,text) returns boolean 
 immutable language 'sql' as $$ select 'md5'||md5($2||$1) = rolpassword from 
 pg_authid where rolname=$1; $$;
   CREATE FUNCTION
   sandbox=# select validate_user_8_1('joe','verysecret');
validate_user_8_1
   ---
t
   (1 Zeile)
 
 May I rely on this in future versions or are there more
 sophisticated ways to do it?

I don't know that I would 'rely' on it, but it doesn't seem like something
that is likely to change any time soon. But I could see there being alternate
hash functions being used eventually.

It might make more sense to use your own table of users and hashed passwords
rather than postgres'. This would depend somewhat on the overlap of users who
are using your application and those who connect directly to the database.
If there isn't much overlap, having a separate table is probably better.

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

Re: [GENERAL] Password encryption method

[Bruno Wolff III]

On Fri, Jan 19, 2007 at 18:24:32 +0200,
  Andrus [EMAIL PROTECTED] wrote:
  It might make more sense to use your own table of users and hashed 
  passwords
  rather than postgres'. This would depend somewhat on the overlap of users 
  who
  are using your application and those who connect directly to the database.
  If there isn't much overlap, having a separate table is probably better.
 
 Using own table requires storing Postgres user name and password in client 
 computer. Thus this information is available to virtually everyone haveing 
 access to client computer.
 So this is very bad idea and should avoided at all.

No, the tables would be on the server, the same as was already being done.
Using a separate table makes it more future proof.

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

Re: [GENERAL] Multiple column index question.

[Bruno Wolff III]

On Fri, Jan 19, 2007 at 15:22:12 -0500,
  Jan Muszynski [EMAIL PROTECTED] wrote:
 If I have an index that's composed of 2 columns:
 Index index1 on tableA (foo,bar)
 
 and I then:
 Select cola, colb from tableA where foo=value
 
 Will index1 still be used, or am I looking at a seqscan under all 
 circumstances in this case?

The planner can decide to use it. It may not always use it though depending
on what it thinks the costs of the various ways to do the query are.

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

Re: [GENERAL] Multiple column index usage question

[Bruno Wolff III]

On Fri, Jan 19, 2007 at 18:20:47 -0500,
  Jeremy Haile [EMAIL PROTECTED] wrote:
 That's interesting.  So if you have a composite index on two columns, is
 there much of a reason (usually) to create single indexes on each of the
 two columns?  I guess the single indexes might be slightly faster
 depending on the number of different values/combinations, so probably
 it depends eh?  

You are normal going to want an index on just the second column in the
index or you do things where you are selecting a small subset of rows
based on the value of that column.

In some situations it may even make sense to have a separate index on just
the first column, because it will be more compact which will speed up
searches. However, you have to pay for maintaining the extra index when
changing the data so it often isn't worth it.

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

Re: [GENERAL] [1/2 OFF] Varlena.com inaccessible from .br (Blocked?)

[Bruno Wolff III]

On Wed, Jan 17, 2007 at 07:54:55 -0200,
  Jorge Godoy [EMAIL PROTECTED] wrote:
 Bruno Wolff III [EMAIL PROTECTED] writes:
 
 The don't block a host I used to access.  And not on several different
 Brazilian networks from different carriers.  The traffic stops at speakeasy
 from my house (ADSL from GVT, it also happens from Brasil Telecom and using
 radio at the São Paulo state -- I'm in a different state, a partner lives in
 São Paulo and tested it for me).

Be sure you aren't blocking the return traffic. I did that once using a /8
instead of a /16 and it took me a while to realize that the reason I stopped
getting back packets from a certain point on in the route was that I was
blocking the return packets, not that they weren't being sent.

You might also check to see if the ip address on your end was recently
allocated. They might block unallocated IP space (which is not something I
recommend) and be behind on updating the list of unallocated IP addresses.

wolff.to is on Speakeasy's network and you can try pinging it to see if it
is a general block. If you use traceroute, tell it to use icmp, not udp as
wolff.to will drop udp packets that aren't destined for a public service
or part of an existing conversation.

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

   http://archives.postgresql.org/

Re: [GENERAL] How I can read-back a serial value just inserted?

[Bruno Wolff III]

On Mon, Jan 01, 2007 at 18:46:26 +0100,
  dfx [EMAIL PROTECTED] wrote:
 Dear Sirs,
 
 my question is very simple:
 when I insert a row whith a serial field, a value is automatically
 generated; how  can I know this value, strictly of my row, without the risk
 of to read the value of another subsequent insertion?

Use currval before calling nextval again (including implicitly) in the same
session. (What other sessions are doing won't change the value you see.)

In 8.2 you can use the RETURNING clause to get the value inserted.

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

Re: [GENERAL] Performance with very large tables

[Bruno Wolff III]

On Mon, Jan 15, 2007 at 11:52:29 +0100,
  Jan van der Weijde [EMAIL PROTECTED] wrote:
 Does anyone have a suggestion for this problem ? Is there for instance
 an alternative to LIMIT/OFFSET so that SELECT on large tables has a good
 performance ?

Depending on exactly what you want to happen, you may be able to continue
where you left off using a condition on the primary key, using the last
primary key value for a row that you have viewed, rather than OFFSET.
This will still be fast and will not skip rows that are now visible to
your transaction (or show duplicates when deleted rows are no longer visible
to your transaction).

Another option would be to do all of the selects in a single serializable
transaction. This will use the same snapshot for all of the selects, so
you won't have rows appear or disappear on you do to other concurrent
transactions.

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

Re: [GENERAL] Performance with very large tables

[Bruno Wolff III]

On Tue, Jan 16, 2007 at 12:06:38 -0600,
  Bruno Wolff III [EMAIL PROTECTED] wrote:
 
 Depending on exactly what you want to happen, you may be able to continue
 where you left off using a condition on the primary key, using the last
 primary key value for a row that you have viewed, rather than OFFSET.
 This will still be fast and will not skip rows that are now visible to
 your transaction (or show duplicates when deleted rows are no longer visible
 to your transaction).

I should have mentioned that you also will need to use an ORDER BY clause
on the primary key when doing things this way.

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

Re: [GENERAL] Coercion in PGSQL?

[Bruno Wolff III]

On Tue, Jan 16, 2007 at 04:14:26 -0800,
  Max Ueda [EMAIL PROTECTED] wrote:
 
 Some results made me think of coercion between int
 types. For example, atributing a int8 value into a
 int2 variable. Does it really happen (coercion)? Is
 the int8 value automatically converted into int2, or
 an error should be returned in that case?

area= select 999::int8::int2;
ERROR:  smallint out of range

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

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

Re: [GENERAL] [1/2 OFF] Varlena.com inaccessible from .br (Blocked?)

[Bruno Wolff III]

On Tue, Jan 16, 2007 at 19:47:28 -0200,
  Jorge Godoy [EMAIL PROTECTED] wrote:
 
 There's been a while since I could use the website for the last time because
 it looks like Brazilian networks are blocked somewhere after routers from
 speakeasy.net (220.ge-3-0.er1.sfo1.speakeasy.net from this network where I am
 now). 
 
 Is this blocking intentional?  Will it be suspended sometime in the near
 future?  I'd really like to continue using it and recommending it, but I can't
 proxy all the time or ssh to UNC all the time... 

I doubt Speakeasy is blocking anything. They are the ISP for geeks and say
up front that they don't block ports and the like.

Did you use traceroute or something similar to see how far the packets are
getting?
Be sure to check any fire wall rules you have.

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

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

Re: [GENERAL] Remove duplicate rows

[Bruno Wolff III]

On Thu, Jan 11, 2007 at 18:51:57 +0100,
  Jiří Němec [EMAIL PROTECTED] wrote:
 Hello,
 
 I need to remove duplicates rows from a subquery but order these
 results by a column what is not selected. There are logically two
 solutions but no works.
 
 SELECT DISTINCT sub.foo FROM (SELECT ...) AS sub ORDER BY sub.bar
 ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
 
 SELECT sub.foo FROM (SELECT ...) AS sub GROUP BY sub.foo ORDER BY sub.bar
 ERROR: column sub.bar must appear in the GROUP BY clause or be used
 in an aggregate function
 
 Does anybody know how to remove duplicate rows from a subquery and order
 these results by a column what is not selected but exists in a subquery?

Is that column dependent (just on) the column you are checking for duplicates
on? If so you can use GROUP BY on both columns, listing the column you want
to order by first. If not, you might want to take a look at DISTINCT ON.

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

Re: [GENERAL] generate_series with month intervals

[Bruno Wolff III]

On Thu, Jan 11, 2007 at 20:07:29 +0100,
  Marcus Engene [EMAIL PROTECTED] wrote:
 Hi list,
 
 I'd like to generate the latest year dynamically with generate_series.
 This select works day wise:
 
 This works but looks grotesque:
 
 select distinct date_trunc ('month', now()::date + s.a)::date
 from generate_series(0, 365) as s(a)
 
 Is there a way to do this more elegantly?

Are you just trying to get a list off the first of the month for the current
month and the next 11 months after that? For that you want to get the first
of the current month and then add s.a * '1 month' to it for 0 to 11.

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

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

Re: [GENERAL] SELECT INTO using Views?

[Bruno Wolff III]

On Tue, Jan 09, 2007 at 10:10:46 -0600,
  Jeanna Geier [EMAIL PROTECTED] wrote:
 
 If I cast the entire operation to an INT:
 (a.area * su.units_per_sqfoot::integer)::integer AS area_sq
  or by
 (a.area * su.units_per_sqfoot)::integer AS area_sq,
 I'm getting an 'ERROR:  integer out of range' error returned when I run my
 SELECT statement:

This suggests that the values are too large to be represented in int4. Can
you switch to using int8?

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

Re: [GENERAL] Questions about horizontal partitioning

[Bruno Wolff III]

On Tue, Jan 09, 2007 at 08:28:29 -0500,
  Chander Ganesan [EMAIL PROTECTED] wrote:
 It would.  A query that uses an inner join implies that a matching entry 
 must exist in both tables - so the join must occur, otherwise you could 
 be returning rows that don't satisfy the join condition.

While this might not be worth doing, a foreign key constraint could be used
to establish the existance of the matching row, so that you wouldn't actually
need to look in the other table to verify that if you didn't need any of the
other columns in the other table. There will be some tricky cases for this,
such as when there are deferred constraints or when updating the foreign key
field.

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

Re: [GENERAL] Newbie Constraint ?

[Bruno Wolff III]

On Mon, Jan 08, 2007 at 14:55:29 -0600,
  Jeanna Geier [EMAIL PROTECTED] wrote:
 
  Not quite sure what the: CONSTRAINT Relationship182 is exactly...  can
 anyone help me with this one?  Haven't seen this one yet...

It is the name of that particular constraint. You would use that if you
were going to delete the constraint. If it hadn't been explicitly named,
it would have gotten a default name.

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

   http://archives.postgresql.org/

Re: [GENERAL] COPY FROM and sequences

[Bruno Wolff III]

On Mon, Jan 08, 2007 at 20:20:42 -0500,
  Matthew Terenzio [EMAIL PROTECTED] wrote:
 Is it true that you can't use COPY FROM to fill a table with a SERIAL 
 type column?
 
 Or rather, how does one approach that situation most effectively?

In older versions of postgres you couldn't, in recent versions, you can
provide an explicit column list to COPY and the other columns will get
default values. You can't do this row by row though; there is no equivalent
to DEFAULT as used in INSERT statements for COPY.

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

Re: [GENERAL] Questions about horizontal partitioning

[Bruno Wolff III]

On Tue, Jan 09, 2007 at 10:33:52 -0500,
  Tom Lane [EMAIL PROTECTED] wrote:
 
 No, that's still not right.  With a LEFT JOIN you know that each row of
 the narrow table will produce at least one row in the join view.  What
 you don't know is whether the row could produce more than one join row
 --- ie, is there more than one wide-table row that joins to it?

Thanks for pointing that out. I only thought of half of the problem.

 To optimize away the join, the planner would have to find a unique
 constraint on the wide table's join column(s).  This is certainly doable
 in principle, though I find it questionable whether the planner should
 spend cycles on every join query checking for something that won't be
 true in the vast majority of real-world queries.  The main reason we

In this case the test would only be applied when no columns were being used
in a table being joined to. Since that is also an unusual case, if that case
could be quickly checked for, then it might conceivably be worth doing the
more expensive test for the proper not null foreign key relation and unique
constraint.

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